数据库sql语句（视图的创建）

**例题： **

建表：要注意各表之间的索引联系，建表先后顺序很重要，不然建不了，例如先建dept，在建其他表，先在dept插入数据，再在其他表插入数据

（1）

 create table week8student(SNO int primary key,
 SNAME char(8) not null unique,SEX char(2),
 DEPTNO int foreign key references week8dept(DEPTNO)) 

**（2） **

 create table week8course(CNO int,
 CNAME char(20) not null,TNO int,CREDIT int,
 primary key(CNO,TNO),
 foreign key (TNO) references week8teacher(TNO)) 

**（3） **

 create table week8sc(SNO int,CNO int,GRADE int,
 primary key(SNO,CNO),
 foreign key (SNO) references week8student(SNO))

（4）

 create table week8teacher(TNO int primary key,
 TNAME char(8) not null, DEPTNO int
 foreign key references week8dept(DEPTNO)) 

**（5） **

 create table week8dept(DEPTNO int primary key,
 DEPTNAME char(20) not null) 

插入数据如下图

sql语句：

在Student表中加入属性SAGE(INT型)

alter table week8student add sage int

将Student表中的属性SAGE类型改为SMALLINT型

alter table week8student alter column sage smallint

关于alter语句的总结

1：向表中添加字段

Alter table [表名] **add **[列名] 类型

2: 删除字段

Alter table [表名]** drop **column [列名]

3: 修改表中字段类型 （可以修改列的类型，是否为空）

Alter table [表名]** alter column** [列名] 类型

4：添加主键

Alter table [表名] add** constraint **[ 约束名] primary key( [列名])

5：添加唯一约束

Alter table [表名] add** constraint **[ 约束名] unique([列名])

6：添加表中某列的默认值

Alter table [表名] add constraint [约束名] default(默认值) for [列名]
7：添加约束

Alter table [表名] add constraint [约束名] check (内容)

8:添加外键约束

Alter table [表名] add constraint [约束名] foreign key(列名) referencese 另一表名(列名)

9:删除约束

Alter table [表名] drop constraint [约束名]

10:重命名表

exec sp_rename ‘[原表名]’,’[新表名]’

11：重命名列名

exec sp_rename ‘[表名].[列名]’,’[表名].[新列名]’

12：删除主键，以及主键上的索引

alter table table_name drop constraint clusteredName
————————————————

原文链接：https://blog.csdn.net/slb190623/article/details/117199931

在Course表上建立关于CNO降序的唯一索引

create unique index cno_index on week8course(CNO desc)

查询数据结构这门课的平均成绩

 select avg(sc.GRADE)'数据结构的平均成绩' 
 from week8sc sc,week8course c
 where sc.CNO = c.CNO and c.CNAME='数据结构'
 group by sc.CNO

为计算机系的学生记录建立一个视图CS_STUDENT(create view....as)

补充：

create table：建表

create index：建立索引

 create view CS_STUDENT as
 select s.SNO,s.SNAME,s.SEX,s.DEPTNO,d.DEPTNAME
 from week8student s,week8dept d
 where s.DEPTNO=d.DEPTNO and d.DEPTNAME='计算机'

**利用视图，列出所有计算机学生的姓名，选课名和成绩 **

 select cs.SNAME 姓名,c.CNAME 课程名,sc.GRADE 成绩
 from CS_STUDENT cs,week8sc sc,week8course c
 where c.CNO=sc.CNO and cs.SNO = sc.SNO

查询各个系的学生人数

 select count(s.DEPTNO)'人数',d.DEPTNAME'系名'
 from week8student s,week8dept d
 where s.DEPTNO=d.DEPTNO group by s.DEPTNO,d.DEPTNAME

查询选修总学分在10学分以上的学生姓名

 select s.SNAME 选修总学分在10分以上
 from week8course c,week8sc sc,week8student s
 where c.CNO=sc.CNO and s.SNO=sc.SNO 
 group by s.SNAME 
 having sum(c.CREDIT)>10

查询各门课程取得最高成绩的课程号、学生姓名及其成绩

 select m.CNO,s.SNAME,m.maxG
 from week8sc sc2,week8student s,
 (select sc1.CNO,max(sc1.GRADE)'maxG'
 from week8sc sc1 group by sc1.CNO)m
 where m.CNO=sc2.CNO and sc2.GRADE=m.maxG and sc2.SNO=s.SNO

其中构建了一个新表m，里面包含的元素有cno，maxG

查询选修了学号为1002的学生选修的全部课程的学生学号

 select distinct SNO from week8sc sc1
 where not exists(
   select * from week8sc sc2
   where sc2.SNO='1002'
   and not exists(
     select * from week8sc sc3
     where sc3.SNO = sc1.SNO
     and sc3.CNO = sc2.CNO))

查询选修了张星老师开设的全部课程的学生姓名（双重否定，没有哪一门课，张星老师开设了，学生x没有选）

select s.SNAME from week8student s
 where not exists (
   select * from week8course c
   where c.TNO in(
     select t.TNO from week8teacher t
     where t.TNAME='张星'
     and not exists(
       select * from week8sc sc
       where sc.SNO=s.SNO and sc.CNO=c.CNO))
 )

查询选修张星老师数据结构课的学生的姓名和成绩

 select s.SNAME,sc1.GRADE from week8sc sc1,week8student s
 where sc1.CNO in 
 (select c.CNO from week8teacher t,week8course c
 where t.TNAME='张星' and t.TNO=c.TNO and c.CNAME='数据结构')
 and sc1.SNO = s.SNO

**将张星老师数据结构课的学生成绩全部加2分 **

 update week8sc set GRADE = GRADE+2 where SNO in (
 select sc1.SNO from week8sc sc1,week8student s
 where sc1.CNO in 
 (select c.CNO from week8teacher t,week8course c
 where t.TNAME='张星' and t.TNO=c.TNO and c.CNAME='数据结构')
 and sc1.SNO = s.SNO)

两个条件：张星老师，数据结构

从内往外看

select c.CNO from week8teacher t,week8course c
 where t.TNAME='张星' and t.TNO=c.TNO and c.CNAME='数据结构'
 

检索课程名称为数据结构，老师为张星的课程号

select sc1.SNO from week8sc sc1,week8student s
 where sc1.CNO in 
 (select c.CNO from week8teacher t,week8course c
 where t.TNAME='张星' and t.TNO=c.TNO and c.CNAME='数据结构')
 and sc1.SNO = s.SNO

检索该课程号对应的学生学号，就是上这门课的学生学号

update week8sc set GRADE = GRADE+2 where SNO in (
 select sc1.SNO from week8sc sc1,week8student s
 where sc1.CNO in 
 (select c.CNO from week8teacher t,week8course c
 where t.TNAME='张星' and t.TNO=c.TNO and c.CNAME='数据结构')
 and sc1.SNO = s.SNO)

最后为这个学号执行grade+2操作

再次查询选修张星老师数据结构课的学生的姓名和成绩

select s.SNAME,sc1.GRADE from week8sc sc1,week8student s where sc1.CNO in  (
select c.CNO 
from week8teacher t,week8course c 
where t.TNAME='张星' and t.TNO=c.TNO and c.CNAME='数据结构') 
and sc1.SNO = s.SNO

删除马朝阳同学的所有选课记录（delete from）

 delete from week8sc where SNO in
 (select SNO 
 from week8student 
 where SNAME='马朝阳')