简单复习数据库的基础命令

（1）创建表及维护表

①创建表：

create table A(id int, name char(20),age int)

A为表名，id、name、age为字段，int、char(20)为字段类型。

 ②输入定义数据

交互式方法录入：

 用SQL命令录入：

insert into t(tno,tn,sex,age,prof,sal,comm,dept)values('t3','张小明','男','39','教授','1200','2000','信息')

 ③删除：

删除表：

DROP TABLE dbo.stu

（dbo是每个数据库的默认用户）

删除表的字段：

alter table t drop dept;

④修改：

修改表中字段类型

alter table t modify tn varchar(12);

修改表中现有字段的字段名和类型

alter table t change tn name char(18);

（2）表及数据操作

①更新：

update t

set prof='副教授' where tn='张小明'

②删除一些行：（删除之前请先备份，以便出错后恢复）

delete from t

where tn='张小明'

③增加字段：（另外一个s表了，不是上面的t表，后面有些命令也可能是应用在别的表）

alter table s add age char(10);

填充字段：

update s set age=datediff(year,birthday,getdate())

select * from s

 按score填充grade：（100~90为A，80以上为B，70分以上为C，60分以上为D，60分以下为E）

update sc set grade = 

case

        when score<=100 and score>=90 then 'A'

        when score<=90 and score>=80 then 'B'

        when score<=80 and score>=70 then 'C'

        when score<=70 and score>=60 then 'D'

        when score<60 then 'E'

else grade end

select * from sc        

 更新字段：

将所有年龄增加一岁

update s set age=age+1

select * from s

将不及格的成绩修改为0分

update sc set score = 0 where score<60 

将低于总平均分成绩的女同学的成绩提高5% ，这里是因为sex在s表里，成绩在sc表里，sno表示学号

update sc set score = score *1.05 from sc left join s on sc.sno=s.sno

where s.sex='' and sc.score<(select AVG(score) from sc);

关于left join、right join、inner join在这篇文章有详细示例：

https://blog.csdn.net/weixin_41796956/article/details/85044152

将部分信息从两个表中删除：（使用两个DELETE语句，因为sc表没有sn姓名这一栏，如果要删除张小明的信息，就找到两个表相同的字段）

delete from s where sn='张小明'

delete sc from sc left join s on sc.sno=s.sno where s.sn='张小明'

（3）简单查询

查询所有女同学的姓名，年龄

select sn.age from s where sex='女'

查询姓'赵'的所有学生的姓名、年龄和性别

select sn.sge, sex from s

where sn like '赵%'

试算所有“数据库”成绩统一增加10%后（超过100分按100计算），全班平均分是多少？（注意：请不要修改原始成绩）

select avg(score) as 全班平均分 from sc

where score in

(select sc.score form sc inner join c on sc.cno=c.cno where cn='数据库')

（4）多表查询

（还有很多查询方式，之后会详细复习，最近要面试简单复习下）

查询选修了“高等数学”或“普通物理”的学生姓名

select s.sn from s

inner join sc on sc.cno=s.sno

inner join c on c.cno=sc.cno

where c.cn='高等数学' or c.cn ='普通物理'

查询选修了王平老师所讲授所有课程的学生的学号和成绩

select sc.sno,sc.score form sc

where sno in (select sno from sc, t)

where sc.tno=t.tno and t.tn='王平'

group by sno

having count(sc.cno)>=(select count(tc.cno) form tc

inner join c on c.cno=tc.cno

inner join t on t.tno=tc.tno

where t.tn = '王平'))

（5）统计查询

查询男、女同学各有多少人

select sex, count (*) from s group by sex

查询年龄大于女同学平均年龄的学生的姓名和年龄

select sn.age from s where age>(select avg(age) from s where sex='女')

查询各科成绩等级分布情况，即看每门课程A等多少人、B等多少人...

select sc.cno

, sum(case when sc.grade = 'A' then 1 else 0 end) as A

, sum(case when sc.grade = 'B' then 1 else 0 end) as B

, sum(case when sc.grade = 'C' then 1 else 0 end) as C

, sum(case when sc.grade = 'D' then 1 else 0 end) as D

, sum(case when sc.grade = 'E' then 1 else 0 end) as E

from sc

group by sc.cno;

（6）视图操作

view_s: 只允许看到学号，姓名，性别，院系这四列

create view view_s

as (select s.sno as 学号, s.sn as 姓名, s.sex as 性别, s.dept as 院系 from s);

将视图view_s中的性别列删除。并且增加年龄列，且只能看到年龄在18岁及以下的学生，并且确保用户基于视图的修改不会导致记录脱离本视图

view_s as

(select s.sno as 学号, s.sn as 姓名, s.sex as 性别, s.dept as 院系, s.age as 年龄 from s where s.age<=18)

基于视图view_s插入新的记录，然后删除刚插入的记录

insert into view_s values('s7','','',18)

delete view_s where 学号='s7'

（7）数据完整性

将S表在学号创建为主键（均先执行修改列属性非空，再定义主键）

alter table s alter column sno nchar(10) not null;

alter table s

ass constraint PK1 primary key(sno);

将T表在教师号上创建主键，C表以课程号为主键，SC以学号与课程号为主键

alter table t add constraint PK2 primary key(tno);

alter table c add constraint PK3 primary key(cno);

alter table sc add constraint PK4 primary key(sno,cno);

为TC表的教师号与课程号创建唯一约束

alter table tc add constraint unique_tc unique(tno,cno)

为TC表增加ID_TC列，设置自动增加，种子为1，增量为1，并设置此列为主键

alter table tc add ID_TC int identity(1,1) primary key;

创建S表和SC之间的参照关系（如：SC中的学号引用S中的学号）

alter table sc add constraint FK_学号

foreign key(sno)

references s(sno)

完整性约束操作：（选课表设置成绩必须在0～100之间）

alter table sc add constraint sc_limit check(score>=0 and score <=100)

（8）索引及关系图

创建包含S，T，C，SC，TC全部表的关系图

为S表在姓名列上按升序创建非聚集索引

create nonclustered index NO_S_1 on S(sn asc)

通过设置索引确保教室不会冲突（即：同教室不会同一天的同一节安排多门课）

create unique nonclustered index non_tc_1 on tc(weekday,room,preriod)

with ignore_dup_key

（9）数据库完整性约束高级设置 

修改前面的学生选课表SC与课程表C的联系（表示学生与课程的多对多联系），以便删除学生的同时，自动删除该生所有选课信息

alter table sc add constraint PK_1

foreign key

(cno)references c(cno)

给课程表添加限员personLimt和选课人数selectCnt字段

alter table c add personList int,selectCnt int

设置所有课程的选课人数不得超过限员人数

alter table c add constraint 选课人数检查

check(selectCnt<=personList)