1、查询选修了课程的学生的学号。(DISTINCT)
select distinct sno from SC;
查询结果
2、查询选修C1或C2且分数大于等于85分学生的学号、课程号和成绩。
# 逻辑运算符的优先级由高到低为：NOT、AND、OR，可以使用括号改变优先级。
select sno, cno, score from SC
where (cno = 'C1' or cno = 'C2') and (score >= 85);
查询结果
3、查询工资在1000元～1500元之间的教师的教师号、姓名及职称。(BETWEEN...AND... 包含等号)
select tno, tn, prof from T
where sal between 1000 and 1500;
# 等价于：
select tno, tn, prof from T
where sal >= 1000 and sal <= 1500;
查询结果
4、查询工资不在1000元～1500元间的教师的教师号、姓名及职称。(NOT BETWEEN...AND...)
select tno, tn, prof from T
where sal not between 1000 and 1500;
查询结果
5、查询选修C1或C2的学生的学号、课程号和成绩。(IN/OR)
select sno, cno, score from SC
where cno in('C1', 'C2');
# 也可使用逻辑运算符 OR 实现
select sno, cno, score from SC
where cno = 'C1' or cno = 'C2';
查询结果
6、查询没有选修C1，也没有选修C2的学生的学号、课程号和成绩。(NOT IN)
select sno, cno, score from SC
where sno not in (select sno from SC where cno in('C1', 'C2'));
查询结果
7、查询姓名中第二个汉字是“力”的教师号和姓名。
# 通配符："%"代表0个或多个字符，"_"代表一个字符，"[]"代表在某一范围的字符，"[^]"代表不在某一范围的字符。
select tno, tn from T
where tn like '_力%';
查询结果
8、查询没有考试成绩的学生的学号和相应的课程号。(空值：NULL)
# 某个字段没有值称为空值(NULL)。空值不同于零和空格，它不占任何存储空间。
select sno, cno from SC
where score is null;
查询结果
9、求学号为S1的学生的总分和平均分。(SUM、AVG)
# 函数SUM和AVG只能对数值型字段进行计算。保留两位小数：ROUND(avg(score),2)
select sum(score), avg(score) from SC
where sno = 'S1';
查询结果
10、求选修C1号课程的最高分、最低分及之间相差的分数。(MAX、MIN)
select max(score), min(score), max(score)-min(score) as diff_score from SC
where cno = 'C1';
查询结果
11、求计算机系学生的总数。(COUNT)
# COUNT(*)用来统计元组的个数，不消除重复行，不允许使用DISTINCT。
select count(sno) from S
where dept = '计算机';
查询结果
12、求学校中共有多少个系。(DISTINCT)
# COUNT 函数对空值不计算，但对0计算。
select count(distinct dept) from S;
查询结果
13、统计有成绩同学的人数。
select count(distinct sno) from SC
where score is not null;
查询结果
14、查询各个教师的教师号及其任课的门数。(GROUP BY)
select tno, count(cno) from TC
group by tno;
查询结果
15、查询选修两门以上（含两门）课程的学生的学号和选课门数。(GROUP BY...HAVING...)
# 当在一个SQL查询中同时使用WHERE子句，GROUP BY子句和HAVING子句时，其顺序是：WHERE、GROUP BY、HAVING。
# WHERE与HAVING子句的根本区别在于作用对象不同。
# WHERE子句作用于基本表或视图，从中选择满足条件的元组；HAVING子句作用于组，选择满足条件的组，必须用在GROUP BY子句之后。
select sno, count(cno) from SC
group by sno
having count(cno) >= 2;
查询结果
16、查询选修C1的学生学号和成绩，并按成绩降序排列。(ORDER BY...DESC降序/ASC升序，缺省时为升序)
select sno, score from SC
where cno = 'C1'
order by score desc;
查询结果
17、查询选修C2，C3，C4或C5课程的学号、课程号和成绩，查询结果按学号升序排列，学号相同再按成绩降序排列。(ORDER BY...DESC降序/ASC升序，缺省时为升序)
select sno, cno, score from SC
where cno in ('C2', 'C3', 'C4', 'C5')
order by sno, score desc;
查询结果
18、查询“刘伟”老师所讲授的课程，要求列出教师号、教师姓名和课程号。(INNER JOIN...ON...)
# 当将JOIN关键字放于FROM子句中时，应有关键字ON与之对应，以表明连接的条件。
# 引用列名Tno时要加上表名前缀，这是因为两个表中的列名相同，必须用表名前缀来确切说明所指列属于哪个表，以避免二义性。如果列名是唯一的(如Tn)就不必加前缀。
select T.tno, tn, cno
from T inner join TC on T.tno = TC.tno
where tn = '刘伟';
# 方法2：
select T.tno, tn, cno from T, TC
where T.tno = TC.tno and tn = '刘伟';
查询结果
19、查询所有选课学生的学号、姓名、选课名称及成绩。(当有两个以上的表进行连接时，称为多表连接。)
select S.sno, sn, cn, score from S, C, SC
where S.sno = SC.sno and SC.cno = C.cno
查询结果
20、查询每门课程的课程号、课程名和选课人数。(GROUP BY)
select C.cno, cn, count(sno) from C, SC
where C.cno = SC.cno
group by cno;
查询结果
21、查询所有学生的学号、姓名、选课名称及成绩(没有选课的同学的选课信息显示为空)。(LEFT JOIN...ON...)
# 在外部连接中，参与连接的表有主从之分，以主表的每行数据去匹配从表的数据列。
# 符合连接条件的数据将直接返回到结果集中；对那些不符合连接条件的列，将被填上NULL值后，再返回到结果集中。
# (由于bit数据类型不允许有NULL值，因此对bit类型的列将会被填上0值，再返回到结果中。)
select S.sno, sn, cn, score
from S left join SC on S.sno = SC.sno left join C on SC.cno = C.cno
查询结果
22、查询所有比“刘伟”工资高的教师姓名、工资和刘伟的工资。(自连接查询)
# 当一个表与其自身进行连接操作时，称为表的自身连接。
# 本题将T表起两个别名分别为T1和T2，将T1、T2中满足比刘伟工资高的行连接起来。这实际上时同一表T的大于连接。
select T1.tn, T1.sal as sal1, T2.sal as sal2 from T as T1, T as T2
where T1.sal > T2.sal and T2.tn = '刘伟';
# 方法2：
select T1.tn, T1.sal as sal1, T2.sal as sal2
from T as T1 inner join T as T2 on T1.sal > T2.sal
where T2.tn = '刘伟';
查询结果
23、检索所有学生姓名，年龄和选课名称。
select sn, age, cn from S, C, SC
where S.sno = SC.sno and C.cno = SC.cno
查询结果
24、查询与“刘伟”老师职称相同的教师号、姓名。(返回一个值的普通子查询)
# 在WHERE子句中包含一个形如SELECT-FROM-WHERE的查询块，此查询称为子查询或嵌套查询，包含子查询的语句称为父查询或外部查询。
# 嵌套查询在执行时由里向外处理，每个子查询是在上一级外部查询处理之前完成的，父查询要用到子查询的结果。
# 普通子查询的执行顺序是：首先执行子查询，然后把子查询的结果作为父查询的查询条件的值。
# 普通子查询只执行一次，而父查询所涉及的所有记录行都与其查询结果进行比较以确定查询结果集。
# 当子查询的返回值只有一个时，可以使用比较运算符(=、>、<、>=、<=、!=)将父查询和子查询连接起来。
select tno, tn from T
where prof = (select prof from T where tn = '刘伟');
查询结果
25、查询讲授课程号为C5的教师姓名。(返回一组值的普通子查询：ANY 任意一个)
# 如果子查询的返回值不止一个，而是一个集合时，则不能直接使用比较运算符，可以在比较运算符和子查询之间插入ANY或ALL。
select tn from T
where tno = ANY (select tno from TC where cno = 'C5');
# 可以使用连接操作来实现，具体可根据自己学习习惯任意选用。
select tn from T, TC
where T.tno = TC.tno and cno = 'C5';
查询结果
26、查询其他系中比计算机系某一教师工资高的教师的姓名和工资。(返回一组值的普通子查询：ANY 任意一个)
select tn, sal from T
where (sal > ANY (select sal from T where dept = '计算机')) and (dept <> '计算机'); # "<>"表示不等于
# 方法2：利用库函数MIN找到计算机系中所有教师的最低工资
select tn, sal from T
where sal > (select min(sal) from T where dept = '计算机') and dept != '计算机'; # "!="表示不等于
查询结果
27、查询讲授课程号为C5的教师姓名（使用IN）。(可以使用IN代替"=ANY")
select tn from T
where tno in (select tno from TC where cno = 'C5');
查询结果
28、查询其他系中比计算机系所有教师工资都高的教师的姓名和工资。(返回一组值的普通子查询：ALL 全部)
select tn, sal from T
where (sal > All (select sal from T where dept = '计算机')) and (dept <> '计算机'); # "<>"表示不等于
# 方法2：利用库函数MAX找到计算机系中所有教师的最高工资
select tn, sal from T
where sal > (select max(sal) from T where dept = '计算机') and dept != '计算机'; # "!="表示不等于
查询结果
29、查询不讲授课程号为C5的教师姓名。(相关子查询)
# 子查询的查询条件需要引用父查询表中的属性值，这类查询称为相关子查询。
# 相关子查询的执行顺序是：首先选取父查询表中第一行记录，内部的子查询利用此行中相关的属性值进行查询，然后父查询根据子查询返回的结果判断此行是否满足查询条件。
# 如果满足条件，则把该行放入父查询的查询结果集合中。重复执行这一过程，直到处理完父查询表中的每一行数据。
# 由此可见，相关子查询的执行次数是由父查询表的行数决定的。
select distinct tn from T
where 'C5' <> All (select cno from TC where tno = T.tno); # 子查询解析：select cno from tc where tno='T1';其中tno的值由父查询表中数据决定
# "<>ALL"表示不等于子查询结果中的任意一个值，也可以用NOT IN代替。
select distinct tn from T
where 'C5' not in (select cno from TC where tno = T.tno);
查询结果
30、用含有EXISTS的语句完成第25题的查询，即查询讲授课程号为C5的教师姓名。(相关子查询：EXISTS)
# EXISTS是表示存在的量词，带有EXISTS的子查询不返回任何实际数据，它只得到逻辑值"真"或"假"。
# 当子查询的查询结果集合为 非空 时，外层的WHERE子句返回真值，否则返回假值。
# 此题当子查询TC表存在一行记录满足其WHERE子句中的条件时，父查询便得到一个TN值，重复执行以上过程，直到得出最后结果。
select tn from T
where EXISTS (select * from TC where tno = T.tno and cno = 'C5');
查询结果
31、查询没有讲授课程号为C5的教师姓名。(相关子查询：NOT EXISTS)
# NOT EXISTS：当子查询的查询结果 为空 时，外层的WHERE子句返回真值，否则返回假值。
select tn from T
where NOT EXISTS (select * from TC where tno = T.tno and cno = 'C5');
查询结果
32、查询选修所有课程的学生姓名。(相关子查询：NOT EXISTS)
# 此题可以理解为：选出这样一些学生名单，在SC表中不存在他们没有选修课程的记录。
select sn from S
where NOT EXISTS (select * from C where NOT EXISTS ( select * from SC where sno = S.sno and cno = C.cno));
# 方法2：
select sn from S, SC
where S.sno = SC.sno
group by S.sno
having count(distinct SC.cno) = (select count(cno) from C);
查询结果
33、从SC数据表中查询出学号为“S1”同学的学号和总分，再从SC数据表中查询出学号为“S5”的同学的学号和总分，然后将两个查询结果合并成一个结果集。(UNION)
# 合并查询时使用UNION操作符将来自不同查询的数据组合起来，形成一个具有综合信息的查询结果。
# UNION操作会自动将重复的数据行剔除。
# 必须注意的是：参加合并查询的各子查询的使用的表结构应该相同，即各子查询中的数据数目和对应的数据类型都必须相同。
select sno, sum(score) from SC where sno = 'S1'
UNION
select sno, sum(score) from SC where sno = 'S5'
group by sno;
查询结果
34、从SC数据表中查询出所有同学的学号和总分，并将查询结果存放到一个新的数据表Cal_Table中。(SELECT...INTO...)
# 使用SELECT...INTO...语句可以将查询结果存储到一个新建的数据库表或临时表。
# 临时表只存储在内存中，并不存储在数据库中，所以其存在的时间非常短。
select sno, sum(score) into Cal_Table
from SC
group by sno;
35、在S表中添加一条学生记录(学号：S7，姓名：郑冬，性别：女，年龄：21，系别：计算机)。(添加一行新纪录：INSERT INTO)
# 列名的排列顺序不一定要和表定义时的顺序一致，但当指定列名时，VALUES子句中值的排列顺序必须和列名表中的列名排列顺序一致，个数相等，数据类型一一对应。
# 必须用逗号将各个数据分开，字符型数据要用单引号括起来。
insert into S (sno, sn, sex, age, dept)
values ('S7', '郑冬', '女', 21, '计算机');
# 如果INTO子句中没有指定列名，则新添加的记录必须在每个属性列上均有值，且VALUES子句中值的排列顺序要和表中各属性列的排列顺序一致。
insert into S
values ('S7', '郑冬', '女', 21, '计算机');
36、在SC表中添加一条选课记录('S7', 'C1')。(添加一行记录的部分数据值：INSERT INTO)
# 将VALUES子句中的值按照INTO子句中指定列名的顺序添加到表中，对于INTO子句中没有出现的列，则新添加的记录在这些列上将赋NULL值。
# 如果在表定义时有NOT NULL约束的属性列不能取NULL值，插入时必须给其赋值。
insert into SC (sno, cno)
values ('S7', 'C1'); # score将被赋NULL值
37、求出各系教师的平均工资，把结果存放在新表AvgSal中。(用子查询添加多行记录)
# 添加多行记录用于表间的复制，即将一个表中的数据抽取数行添加到另一个表中，可以通过子查询来实现。
# INSERT INTO <表名> [(<列名1>[,<列名2>…])] 子查询
# 首先，建立新表AvgSal，用来存放系名和各系的平均工资。
create table AvgSal (
Department Varchar(20),
Average Smallint
);
# 然后，利用子查询求出T表中各系的平均工资，把结果存放在新表AvgSal中。
insert into AvgSal
select dept, avg(sal) from T group by dept;
38、把刘伟老师转到信息系。(修改一行数据)
# SET子句给出要修改的列及其修改后的值，WHERE子句指定待修改的记录应当满足的条件，WHERE子句省略时，则修改表中的所有记录。
# UPDATE <表名> SET <列名>=<表达式> [,<列名>=<表达式>]… [WHERE <条件>]
update T set dept = '信息'
where tn = '刘伟';
39、将所有学生的年龄增加1岁。(修改多行数据)
update S set age = age + 1;
40、把教师表中工资小于或等于1000元的讲师的工资提高20%。(修改多行数据)
update T set sal = sal * 1.2
where prof = '讲师' and sal <= 1000;
41、把讲授C5课程的教师的岗位津贴增加100元。(用子查询选择要修改的行)
update T set comm = comm + 100
where tno in (select T.tno from T, TC where T.tno = TC.tno and cno = 'C5'); # 子查询的作用是得到讲授C5课程的教师号
42、把所有教师的工资提高到平均工资的1.2倍。(用子查询提供要修改的值)
update T set sal = (select avg(sal) * 1.2 from T); # 子查询的作用是得到所有教师的平均工资的1.2倍
43、删除刘伟老师的记录。(删除一行数据)
# WHERE子句指定待删除的记录应当满足的条件，WHERE子句省略时，则删除表中的所有记录。
# DELETE FROM <表名> [WHERE <条件>]
delete from T
where tn = '刘伟';
44、删除刘伟老师授课的记录。(利用子查询选择要删除的行)
delete from TC
where tno = (select tno from T where tn = '刘伟');
网友评论