数据插入准备：
- 查看表的结构
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
