数据库

--学生表
create table Student(
Sno char(7) primary key, --列级完整性约束
Sname varchar(10) not null,
Ssex char(1),
Sage tinyint,
Sdept varchar(20) --所在系
)
--课程表
create table Course(
Cno char(6) not null,
Cname varchar(20) not null,
Credit tinyint, --学分
Semester tinyint, --学期
primary key(cno) --表级完整性约束
)
--选课表
create table SC(
Sno char(7) not null,
Cno char(6) not null,
Grade smallint, --成绩
primary key(Sno,Cno), --表级完整性约束
foreign key(Sno) references Student(Sno),
foreign key(Cno) references Coures(Cno)
)

--单表查询
--（1）查询全体学生的学号和姓名
select Sno,Sname from Student
--(2)查询全体学生的详细信息
select *from Student
--(3)查询全体学生的姓名和出生年份(要通过计算时间差)
select Sname,2024-Sage from Student
--(4)增加常量列，查询全体学生的姓名和出生年份
select Sname,'出生年份',2024-Sage from Student
--(5)用AS取别名，增加常量列，查询全体学生的姓名和出生年份
select Sname AS 姓名,'出生年份' AS 常量列,2024-Sage AS 年份 from Student
--(6)用distinct去重复的学生选课
select distinct Sno from SC
--(7)用where实现条件查询，查询计算机系全体学生的姓名
select Sname from Student where Sdept='计算机系'
--(8)查询考试成绩不及格的考生的学号(一个考生可能有多科不及格要去重)
select distinct Sno from SC where Grade<60
--(9)用between...and确定范围,查询年龄不在在20-23岁的学生的姓名，所在系和年龄
select Sname,Sage,Sdept from Student where Sage not between 20 and 23
--等价于
select Sname,Sage,Sdept from Student where Sage<20 or Sage>23
--(10)用in查看是否在一定的范围内，查询信息系，数学系和计算机系学生的姓名和性别
select Sname,Ssex from Student where Sdept in('信息系','数学系','计算机系')
--等价于
select Sname,Ssex from Student where Sdept in '信息系' or Sdept in '数学系' or in '计算机系'
--(11)查询不属于信息系，数学系和计算机系学生的姓名和性别
select Sname,Ssex from Student where Sdept not in('信息系','数学系','计算机系')
--(12)用like字符串匹配
--四种通配符
--1._(下划线)：匹配任意一个字符
--2.%(百分号)：匹配0个或者多个字符
--3.[]：匹配[]里任意一个字符，如[acdg]就是匹配a,c,d,g里任何一个，连续字符串匹配就写为[a-d]
--4.[^]：不匹配[]里任意一个字符，如[^acdg]就是不匹配a,c,d,g里任何一个
--(13)查询姓"张"的学生的详细信息
select * from Student where Sname like '张%'
--(14)查询姓"张",姓"李"，姓"刘"的学生的详细信息
select * from Student where Sname like '[张李刘]%'
--(15)查询名字中第二个字是"小"或"大"的学生的姓名和学号
select Sname,Sno from Student where Sname like '_小%' or '_大%'
--等价为
select Sname,Sno from Student where Sname like '_[小大]%'
--(16)查询所有不姓'王'也不姓'张'的学生姓名
select Sname from Student where Sname not like '[王张]%'
--(17)查询所有姓'王'且名字是2个字的学生姓名
select Sname from Student where Sname like '王_'
--(18)查询所有姓'王'且名字是3个字的学生姓名
select Sname from Student where Sname like '王__'
--(19)用rtrim():右去空格 ltrim():左去空格
select Sname from Student where rtrim(Sname) like '王__'
--(20)涉及null空值的查询，查询没有考试成绩的学生的相应学号和课程号
select Sno,Cno from SC where Grade is null
--(21)查询有考试成绩的学生的相应学号和课程号
select Sno,Cno from SC where Grade is not null
--(22)多重条件查询，在where字句中用and和or来组成多条件查询，and条件全部满足则为true,or则是有
--一个满足则为true,and的优先级高于or
--查询计算机系年龄在20岁以下的学生的姓名和年龄
select Sname,Sage from Student where Sdept='计算机系' and Sage<=20
--(23)查询计算机系和信息系年龄大于等于20岁的学生姓名，所在系和年龄
select Sname,Sage,Sdept from Student where (Sdept='计算机系' or Sdept='信息系') and Sage>=20
--(24)用order by对查询结果进行排序，关键字ASC升序，关键字DESC降序，默认升序
--将学生按年龄升序排列
select * from Student order by Sage asc
--(25)查询选修了'c002'课程的学生的学号及成绩，查询结果按成绩降序排列
select Sno,Grade from SC where Cno='c002' order by Grade desc
--(26)查询所有学生信息，结果按所在系的系名升序排列，同一系的学生按年龄降序
select * from Student order by Sdept,Sage desc
--(27)使用聚合函数汇总数据，注意：聚合函数不能出现在where字句中
--COUNT(*)：统计表中元组的个数
--COUNT([distinct]列名)：计算那一列有多少个非空的数据
--SUM(列名)：对一列求和
--AVG(列名)：对一列求平均值
--MAX(列名)：求列最大值
--MIN(列名)：求列最小值
--(28)统计学生总人数
select count(*) as 学生人数 from Student
--(29)统计选修了课程的学生人数
select count(distinct Sno)选课人数 from SC
--(30)计算学号为'1512101'学生的选课门数和考试总成绩
select count(*) as '选课门数', sum(Grade) as '总成绩' from SC where Sno='1512101'
--(31)查询学号为'1512101'学生的选课门数，已考试科目门数，以及考试最高分，最低分，平均分
select count(*) as '选课门数', sum(Grade) as '考试门数',max(Grade) as '最高分' ,min(Grade) as '最低分',avg(Grade) as '平均分'from SC where Sno='1512101'
--(32)用group by对查询结果进行分组，注意：分组类型不能是text,image类型,一般跟在where后面
--统计每门学科的选课人数，列出课程号和选课人数
select Cno as '课程号',count(*) as '选课人数' from SC group by Cno
--(33)统计每个学生的选课门数和平均成绩
select count(*) as '选课门数',avg(Grade) as '平均成绩' from SC group by Sno
--(34)统计每个系的女生人数
select count(*) as '女生人数' from Student where Ssex='女' group by Sdept
--(35)统计每个系的男生人数和女生人数，以及男生的最大年龄和女生的最大年龄，结果按按系名升序排列
select count(*) as '男生人数', count(*) as '女生人数',max(Sage) as '最大年龄' from Student group by Sdept,Ssex order by Sdept
--(36)使用having语句进行在筛选，一般和group by 一起用
--查询选修了三门以上的课程的学生的学号和选课门数
select Sno,count(*) as '选课门数' from SC group by Sno having count(*)>3
--(37)查询考试平均成绩超过80的学生的学号，选课门数，和平均成绩
select Sno,count(*) as '选课门数',avg(Grade) as '平均成绩' from SC group by Sno having avg(Grade)>=80
--(38)统计每个系的男生人数，只列出男生人数大于等于2人的系
select Sdept,count(*) as '男生人数' from Student where Ssex='男' group by Sdept having count(*)>=2
--(39)多表查询-默认内连接
--内连接语法,每连接一个表就要增加一个join字句
--from 表1 inner join 表2 on <连接条件>
--查询每个学生及其选课的详细信息(学生表在Student里，选课表在SC里，所以涉及两个表)
select * from Student inner join SC on Student.Sno=Sc.Sno
--(40)因为在（39）中结果会出现两列重复的Sno,所以去重
select Student.Sno,Sname,Ssex,Sage,Sdept,Cno,Grade from Student join SC on Student.Sno=Sc.Sno
--(41)可以为表取别名，但是如果起了别名，后面就只能用别名了。修改(40)
select Student.Sno,Sname,Ssex,Sage,Sdept,Cno,Grade from Student S join SC on S.Sno=Sc.Sno
--(42)查询信息系选修了计算机文化学课程的学生的成绩，列出学生姓名，课程名和成绩
select Sname,Cname,Grade from Student s
join SC on s.sno=SC.sno
join Course c on c.sno=Sc.sno
where Sdept='信息系'and Cname='计算机文化学'
--(43)统计计算机系每门课程的选课人数，平均成绩，最高成绩和最低成绩
select Sdept,count(*) as 'Total',avg(Grade) as 'AvgGrade' max(Grade) as 'MaxGrade',min(Grade) as 'MinGrade' from Student s
join SC on s.sno=sc.cno
where Sdept='计算机系' group by Cno
--(45)自连接物理上是一个表，但是逻辑上看成两个表所以一定要取别名
--查询和刘晨在同一个系学习的学生的姓名和所在系
select S2.name,S2.dept from Student S1 join Student S2
on s1.sdept=s2.sdept --是同一个系的学生
where S1.Sname='刘晨' --S1作为查询条件表
and S2.Sname!='刘晨' --S2作为结果表，从中删掉刘晨本人
--(46)外连接，分为左连接和右连接 from 表1 left|right join 表2 on<连接条件>
--左连接是限制表2的数据必须满足条件，不管表1，最后输出表1的内容
--右连接是限制表1的数据必须满足条件，不管表2，最后输出表2的内容
--(47)查询计算机系没选课的学生，列出学生姓名和性别
SELECT S.Sname, S.Ssex --左连接的方法
FROM Student S
LEFT JOIN SC SC ON S.Sno = SC.Sno
WHERE S.Sdept = '计算机系' AND SC.Sno IS NULL;
SELECT Sname, Ssex --子查询的方法
FROM Student
WHERE Sdept = '计算机系'
AND Sno NOT IN (SELECT Sno FROM SC);
--(48)利用TOP限制结果集,Top n:表示取查询结果的前n项数据，Top n percent:表示取查询结果的前n
--项数据,top谓词跟在select后面，有distinct就在distinct后面,with ties包含并列的
--(49)查询年龄最大的三名学生的姓名，年龄和所在系
select top 3 Sname,Sage,Sdept from Student order by Sage DESC
--(50)如果年龄包含并列的
select top 3 with ties Sname,Sage,Sdept from Student order by Sage DESC
--(51)子查询、where 表达式 in(子查询),顺序是先执行子查询，在执行外层查询，
--(52)查询与刘晨在同一个系实习的学生
select Sno,Sname,Sdept from Student where Sdept in(select Sdept from Student where Sname='刘晨')
--(53)如果不希望表中出现刘晨，则加一句
select Sno,Sname,Sdept from Student where Sdept in(select Sdept from Student where Sname='刘晨') and Sname!='刘晨'
--(54)查询考试成绩大于90分的学生的学号和姓名
select Sno,Sname from Student where Sno in(select Sno from SC where Grade>=90)
--(55)查询选修了Java课程的学生的学号姓名
select Sno,Sname from Student where Sno in(select Sno from SC where Cno in(select Cno from Course where Cname='Java'))
也可以用多表连接实现
select Student.Sno,Sname from Student
join SC on Student.Sno=SC.sno
join Course on Course.cno=Sc.cno
where Cname='Java'
--(56)查询选修了c005课程且考试成绩高于此课程的平均成绩的学生的学号和成绩
--使用子查询来测试 语法：where 比较符(子查询)
select Sno,Grade from SC where Cno='c005'and Grade >(select avg(Grade) from SC where Cno='c005')
--(57)使用子查询进行存在性测试 where [not] exists(子查询)，有exists关键字则先执行外层后内
--(58)查询选了c001课程的学生姓名
select Sname from Student where exists(select * from SC where Sno=Student.Sno and Cno='c001')