数据库的简单查询和连接查询实验

数据插入准备：

• 查看表的结构

sp_help S1 

• 查看表头

select *from S1

– 查看数据库中自己建立的表

select table_name from stu_db.information_schema.TABLES where TABLE_TYPE='base table'
select *from information_schema.tables 

• sql 插入完整的行

insert into S values('201215121','李勇','男','20','CS')
insert into S(sno,sname,ssex,sage,sdept) values ('2012115122','刘晨','女','19','CS')
select *from S

• sql 一次插入多条数据

写法一：
insert into S values ('201215123','王敏','女','18','MA'),('201215125','张立','男','19','IS')

写法二：
insert into S select '201215124','刘妮','女','18','DS'
union all select '201215128','何宝','男','21','IS'
union all select '201215126','平凡','男','24','IS'

数据库简单查询和连接查询实验3

select *from information_schema.tables
select *from C
insert into C(cno,cname,cpno,credit) select '1','数据库','5','4'
union all select '2','数学','null','2'
union all select '3','信息系统','1','4'
union all select '4','操作系统','6','3'
union all select '5','数据结构','7','4'
union all select '6','数据处理','null','2'
union all select '7','C语言','6','4'
select *from SC
insert into SC values ('201215124','3','null')
insert into SC values ('201215124','2','100'),('201215124','1','99'),('201215128','4','100'),('201215126','3','88')
select *from S

• 求数学系学生的学号和姓名

insert into S values('201215129','小何子','男','24','数学'),('201215120','嘻嘻','女','22','英语')
insert into S values('201215132','张三丰','男','100','计算机系')
select sno,sname from S where sdept='数学'

• 求选修了课程的学生学号

select distinct sno from SC 

• 求选修课程号为‘C1’的学生号和成绩， 并要求对查询结果按成绩的降序排列，如果成绩相同按学号的升序排列。

select sno,grage from SC where cno='1' order by grage desc,sno

• 求选修课程号为‘C1’且成绩在80~100之间的学生学号和成绩，并成绩乘以0.8输出

select sno 学号,grage*0.8 成绩 from SC where cno='1' and grage between 80 and 100

• 求数学系或计算机系姓张的学生的信息

select *from S  where  sname like '张%' and sdept in ('数学','计算机')

• 求缺少了成绩的学生的学号和课程号

select sno,cno from SC where grage is null

• 求缺少了成绩的学生的学号和课程号

select sno,cno from SC where grage is null

• 查询每个学生的情况以及他所选修的课程

select S.*,C.*,grage from S left join SC on S.sno=SC.sno left join C on C.cno=SC.cno

• 求学生的学号、姓名、选修的课程及成绩

select S.sno,s.sname,C.cname,SC.grage from S 
left join SC on S.sno=SC.cno
left join C on C.cno=SC.cno

• 求选修课程号为‘C1’且成绩在90以上的学生学号、姓名和成绩

select S.sno,S.sname,SC.grage
from S,SC,C
where S.sno=SC.sno and C.cno=sc.cno and C.cno='1' and grage>90

• 查询每一门课程的间接先行课（即先行课的先行课）

select C1.cno,C2.cpno from C C1,C C2 where C1.cpno=C2.cno
select *from C
select *from S
select *from SC