数据库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语句的总结

向表中添加字段

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

删除字段

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

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

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

添加主键

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

添加唯一约束

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

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

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

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

添加外键约束

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

删除约束

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

重命名表

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

重命名列名

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

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

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='马朝阳')

来源地址：https://blog.csdn.net/weixin_69884785/article/details/130585743