SQLserver总结03（DDL、DML、DCL、查询、top、distinct、order by、聚合函数）

SQLserver总结（DDL、DML、DCL、查询、top、distinct、order by、聚合函数）

 

 

use [wx105];

create table Dep

(

id int primary key,

DepName nvarchar(100) not null

);

create table Emp

(

id int,

name nvarchar(50) ,

age int ,

gender nchar(1),

DepId int

);

--DDL:数据定义语言。包括：alter、drop、add、create

--DML：数据操作语言。包括：insert、update、delete

--DCL：数据控制语言。

1、--修改表的结构，将age列删除

alter table Emp drop column age;

2、--修改表的结构，增加age列

alter table Emp add age int;

3、--修改表的结构，给Emp中的id列加一个非空约束

alter table Emp alter column id int not null;

4、--修改表的结构，给Emp表的id列加一个主键约束

alter table Emp add constraint PK_Emp_id primary key(id);

5、--修改表的结构，给Emp表的name列加一唯一约束

alter table Emp add constraint UQ_Emp_name unique(name);

6、--修改表的结构，给Emp表的age列加一个检查约束，只允许年龄介于0到120之间

alter table Emp add constraint CK_Emp_age check(age>=0 and age<=120);

7、--修改表的结构，给Emp表的gender列加一个检查约束，只允许输入'男'或'女'

alter table Emp add constraint CK_Emp_gender check(gender='男' or gender='女');

8、--修改表的结构，给Emp表的DepId加一个外键约束（为什么要加？加了后这个列就不能随便输入，必须要是主键表中的主键列存在的数据）

alter table Emp add constraint FK_Emp_DepId foreign key(DepId) references Dep(id);

9、--修改表的结构，给Emp表的gender列加一个默认约束，默认为'女';

alter table Emp add constraint DF_Emp_gender default('女') for gender;

10、--修改表的结构，将Emp表中的gender列的所有约束删掉（目的是为下面的修改gender数据类型做准备）

alter table Emp drop constraint DF_Emp_gender;

alter table Emp drop constraint CK_Emp_gender;

A、--修改表的结构，将Emp表中的gender修改为bit类型

alter table Emp alter column gender bit;

B、--修改表的结构，将Emp表中的所有约束一块删除（一个sql全部删除）

alter table Emp drop

constraint PK_Emp_id,

constraint FK_Emp_DepId,

constraint UQ_Emp_name,

constraint CK_Emp_age;

--修改表的结构，一次性给Emp表加一堆约束

alter table Emp add

constraint PK_Emp_id primary key(id),

constraint FK_Emp_DepId foreign key(DepId) references Dep(id),

constraint UQ_Emp_name unique(name),

constraint CK_Emp_age check(age>=0 and age<=120);

 

--机器--汇编--面向过程的语言（C语言）-- 面对对象的语言（java、c++、php）--自然语言（给我添加一个员工）

 

C、--修改表的结构，给Emp表设置一个外键约束，并且是级联删除

alter table Emp add

constraint FK_Emp_DepId foreign key(DepId) references Dep(id) on delete cascade;

D、--以上都是在表创建完之后修改表的结构，下面需要在创建表的同时（也就是说这个表正在设计还没保存）增加约束

E、--创建一个员工表，同时增加以上的约束

create table Emp2

(

id int identity(1,1)  constraint PK_Emp2_id primary key ,

name nvarchar(100)  constraint UQ_Emp2_name unique not null,

age int constraint CK_Emp2_age check(age>=18 and age<=60) not null,

gender nchar(1) constraint CK_Emp2_gender check(gender='男' or gender='女') constraint DF_Emp2_gender default('女'),

DepId int constraint FK_Emp2_DepId foreign key references Dep(id)

);

 

--------------------------------------------查询----------------------------------------

1、--别名：1:as  2：=  3： 直接写后面

select name as 姓名 from stuInfo;

select name 姓名 from stuInfo;

select 姓名=name from stuInfo;

2、--select 可以不跟from 吗？可以，select后面可以跟单独的常量或者函数而没有from

select 真的='我们都是好样的',GETDATE() 哈哈;

3、--查询所有的男同学的姓名和当前时间

select name,GETDATE() as 当前时间 from stuInfo where gender='男';

4、--查询多个列，如果列数比较多，强烈建议分行

select * from stuInfo;

select

id as 编号,

name as 姓名,

gender as 性别,

score as 成绩,

phone as 手机号,

婚否='是'

 from stuInfo;

 

5、--查询当前时间

select GETDATE() as 当前时间;

6、--随手写几个

select '班花'='高帅', '陈梦莹' as 班草,'郭梦瑜' 校草;

-------------------------top-----------------------------

1、--查询班级前十名同学的所有信息

select top(10) * from stuInfo order by score desc;

select top 10 * from stuInfo order by score desc;

2、--查询班级前十名同学的姓名

select top 10 name from stuInfo order by score desc;

3、--查询班级成绩前百分之十的同学姓名(注意：百分比的结果如果是小数，会向上取整)

select top 10 percent name from stuInfo order by score desc;

insert into stuInfo values(20160888,'呵呵','男',null,'20086');

 

 

--------------------------------------distinct-----------------------------------------

select * from stuInfo;

1、--查询所有人的信息，不包含重复的，只保留一行

select distinct * from stuInfo;

 

--------------------------------------order by 排序----------------------------------------

1、--按照成绩排序（默认为升序）

select * from stuInfo order by score;

select * from stuInfo order by score asc;

2、--按照成绩升序

select * from stuInfo order by score desc;

3、--按字符串类型来排序，会按照字符串从左到右每个字符对比

select * from stuInfo order by phone desc;

4、--查询成绩前10名的同学，要求你的sql语句中不能有10这个数字

delete from stuInfo where id = -111;

select top (3+7) * from stuInfo order by score desc;--top后面可以跟表达式，但是表达式必须要被小括号括起来

 

-----------------------------------聚合函数（分组函数、组函数）-------------------------------

1、--MAX()

--求成绩最大值

select MAX(score) from stuInfo;

2、--求成绩最小值

select MIN(score) from stuInfo;

3、--求成绩平均值(注意：成绩如果是null，会被过滤掉)

select AVG(score) from stuInfo;

4、--求成绩之和

select SUM(score) from stuInfo;

5、--求平均值，要求不允许使用AVG()

select SUM(score)/COUNT(*) from stuInfo;