SQL 查询练习（共9题）

一、要求

1、数据情况

1）Student：

2）Course：

3）Teacher：

4）SC：

2、查询要求

请书写实现以下查询的SQL脚本：

1. 查询“01”课程比“02”课程成绩高的所有学生的学号；
2. 查询平均成绩大于60分的同学的学号和平均成绩；
3. 查询所有同学的学号、姓名、选课数、总成绩；
4. 查询课程编号“01”的成绩比课程编号“02”课程低的所有同学的学号、姓名；
5. 查询没学过“张三”老师课的同学的学号、姓名；
6. 合并4个表中的所有信息生成一个新表，且新表中的Sid列的值是唯一的；
7. 在student表中增加一列年龄字段，列出每位学生的年纪；
8. 将sc复制到新表，命名为：sc_new；
9. 在sc_new表中添加学生未选课程的成绩为0（关键词：存储过程、遍历）；

二、代码

1、创建表的脚本

# 1.Table_Info
create table Student(sid varchar(10),sname varchar(10),sage datetime,ssex nvarchar(10)); 
insert into Student values('01' , '赵雷' , '1990-01-01' , '男'); 
insert into Student values('02' , '钱电' , '1990-12-21' , '男'); 
insert into Student values('03' , '孙风' , '1990-05-20' , '男'); 
insert into Student values('04' , '李云' , '1990-08-06' , '男'); 
insert into Student values('05' , '周梅' , '1991-12-01' , '女'); 
insert into Student values('06' , '吴兰' , '1992-03-01' , '女'); 
insert into Student values('07' , '郑竹' , '1989-07-01' , '女'); 
insert into Student values('08' , '王菊' , '1990-01-20' , '女'); 

create table Course(cid varchar(10),cname varchar(10),tid varchar(10)); 
insert into Course values('01' , '语文' , '02'); 
insert into Course values('02' , '数学' , '01'); 
insert into Course values('03' , '英语' , '03'); 

create table Teacher(tid varchar(10),tname varchar(10)); 
insert into Teacher values('01' , '张三'); 
insert into Teacher values('02' , '李四'); 
insert into Teacher values('03' , '王五'); 

create table SC(sid varchar(10),cid varchar(10),score decimal(18,1)); 
insert into SC values('01' , '01' , 80); insert into SC values('01' , '02' , 90); 
insert into SC values('01' , '03' , 99); insert into SC values('02' , '01' , 70);
insert into SC values('02' , '02' , 60); insert into SC values('02' , '03' , 80);
insert into SC values('03' , '01' , 80); insert into SC values('03' , '02' , 80);
insert into SC values('03' , '03' , 80); insert into SC values('04' , '01' , 50);
insert into SC values('04' , '02' , 30); insert into SC values('04' , '03' , 20);
insert into SC values('05' , '01' , 76); insert into SC values('05' , '02' , 87); 
insert into SC values('06' , '01' , 31); insert into SC values('06' , '03' , 34); 
insert into SC values('07' , '02' , 89); insert into SC values('07' , '03' , 98); 

# 2.Table_Relation
Student.sid = SC.sid >> SC.cid = Course.cid >> Course.tid = Teacher.tid

2、实现查询的脚本

1、查询“01”课程比“02”课程成绩高的所有学生的学号；
select 
sid_1
from
	(select sid  as sid_1, cid, score from SC where cid = '01') as a
	INNER JOIN
	(select sid  as sid_2, cid, score from SC where cid = '02') as b
	on a.sid_1 = b.sid_2
where a.score>b.score;
 
2、查询平均成绩大于60分的同学的学号和平均成绩；
SELECT sid,AVG(score)
FROM sc 
GROUP BY sid 
HAVING AVG(score)>60
 
3、查询所有同学的学号、姓名、选课数、总成绩
方式一：
select a.sid,a.sname,count(b.cid) as count_course, sum(b.score) as sum_score
from Student a, SC b
where a.sid = b.sid
group by a.sid, a.sname

方式二：
SELECT sid, sname, COUNT(cid) AS count_course, SUM(score) AS sum_score
FROM(
	SELECT b.sid, b.sname, a.cid, a.score
	FROM sc a, student b
	WHERE a.sid = b.sid
	) AS z
GROUP BY sid

4、查询课程编号“01”的成绩比课程编号“02”课程低的所有同学的学号、姓名；
select sid, sname
from Student
where sid in(
	select a.sid
	from
		(select sid, cid, score from SC where cid = '01') as a
		INNER JOIN
		(select sid, cid, score from SC where cid = '02') as b
		on a.sid = b.sid
	where a.score<b.score
)
 
5、查询没学过“张三”老师课的同学的学号、姓名；
select sid, sname
from Student
where sid not in(
	select distinct a.sid
	from Student a, Course b, Teacher c, SC d
	where a.sid = d.sid and d.cid = b.cid and b.tid = c.tid and c.tname <> '张三'
)
 
6、合并4个表中的所有信息生成一个新表，且新表中的Sid列的值是唯一的；
1）合并
select a.sid,a.sname,a.sage,a.ssex,b.cid,b.cname,b.tid,c.tname,d.score
from Student a, Course b, Teacher c, SC d
where a.sid = d.sid and d.cid = b.cid and b.tid = c.tid
2）透视
SELECT
   sid, sname, sage, ssex,
   SUM(CASE WHEN (cname='语文') THEN score ELSE NULL END) AS '语文',
   SUM(CASE WHEN (cname='数学') THEN score ELSE NULL END) AS '数学',
   SUM(CASE WHEN (cname='英语') THEN score ELSE NULL END) AS '英语',
   
   SUM(CASE WHEN (tname='李四') THEN score ELSE NULL END) AS '李四',
   SUM(CASE WHEN (tname='张三') THEN score ELSE NULL END) AS '张三',
   SUM(CASE WHEN (tname='王五') THEN score ELSE NULL END) AS '王五'
FROM(
	select a.sid,a.sname,a.sage,a.ssex,b.cid,b.cname,b.tid,c.tname,d.score
	from Student a, Course b, Teacher c, SC d
	where a.sid = d.sid and d.cid = b.cid and b.tid = c.tid
	) z
GROUP BY 
	sid, sname, sage, ssex
 
7、在student表中增加一列年龄字段，列出每位学生的年纪；
1）sql server上脚本：
alter table student add age int
UPDATE student SET age = DATEDIFF(year,sage,getdate())
2）MySQL上脚本：
ALTER TABLE student ADD COLUMN age INT;
UPDATE student SET age = TIMESTAMPDIFF(YEAR, sage, CURDATE());
3）删除新增加的age字段
ALTER TABLE student DROP COLUMN age

8、将sc复制到新表，命名为：sc_new
MySQL中的脚本（与SQL server不同）：
CREATE TABLE sc_new AS (SELECT * FROM sc)

9、在sc_new表中添加学生未选课程的成绩为0；
1）示例-Mysql实现for循环遍历
-- (1)创建存储过程
-- 创建存储过程前先检查是否存在，存在就删除，这个可以忽略
DROP PROCEDURE IF EXISTS staff_zt_test;
-- 存储过程
CREATE PROCEDURE staff_zt_test()
BEGIN
	-- 该变量用于标识是否还有数据需遍历
	DECLARE flag INT DEFAULT 0;
	-- 创建一个变量用来存储遍历过程中的值
	DECLARE id BIGINT(40);
	-- 查询出需要遍历的数据集合
	DECLARE idList CURSOR FOR (SELECT id FROM table WHERE a = 1);
	-- 查询是否有下一个数据，没有将标识设为1，相当于hasNext
	DECLARE CONTINUE HANDLER FOR NOT FOUND SET flag = 1;
	-- 打开游标
	OPEN idList;
		-- 取值设置到临时变量中
		FETCH idList INTO id;  -- 注意：在循环外，相当于给id设一个初始值
		-- 遍历未结束就一直执行
		WHILE flag != 1 DO
				-- targetSQL --想要执行的目标功能，这里可以写多个SQL
				# ----------
				-- 1/例如打印临时变量 id
				SELECT id；
				-- 2/也可以打印循环外的变量
				SELECT flag;
				-- 3/也可以拼接字符串
				SELECT CONCAT('id = ',id,', flag = ',flag);
				# ----------
				/*  注意:
				这里有一个坑，目标语句引用临时变量，实测发现不需要加@符号，但是搜索到的结果都是例如：@id ，这样来使用，实测发现无法取到数据 */

				update XXX from table2 where key_id = id;
				-- 一定要记得把游标向后移一位，这个坑替各位踩过了，不需要再踩了
				FETCH idList INTO id;
		END WHILE;
	CLOSE idList;
END;

-- (2)调用存储过程
CALL staff_zt_test();

2）本案例的实现
-- (1)创建存储过程
DROP PROCEDURE IF EXISTS add_score;

CREATE PROCEDURE add_score()
BEGIN
	DECLARE flag INT DEFAULT 0;
	DECLARE id VARCHAR(10);
	-- 查询出需要遍历<存在未选课程的学生>sid的集合
	DECLARE idList CURSOR FOR (SELECT sid FROM sc_new GROUP BY sid having COUNT(cid)<3); 
	-- 查询是否有下一个数据，没有将标识设为1，相当于hasNext
	DECLARE CONTINUE HANDLER FOR NOT FOUND SET flag = 1;
	OPEN idList;
		-- 取值设置到临时变量中
		FETCH idList INTO id;
		-- 遍历未结束就一直执行
		WHILE flag != 1 DO  
		
				-- IF ELSEIF ELSE语句 多条件判断，不存在则插入，存在则跳过
				IF NOT EXISTS (SELECT 1 FROM sc_new WHERE sid = id AND cid = '01') THEN
					INSERT INTO sc_new VALUES (id,'01',0);
				ELSEIF NOT EXISTS (SELECT 1 FROM sc_new WHERE sid = id AND cid = '02') THEN
					INSERT INTO sc_new VALUES (id,'02',0);
				ELSEIF NOT EXISTS (SELECT 1 FROM sc_new WHERE sid = id AND cid = '03') THEN 
					INSERT INTO sc_new VALUES (id,'03',0);
				END IF;
				
				-- 打印添加成功的sid
				SELECT id AS '添加成功的sid';
				-- 一定要记得把游标向后移一位
				FETCH idList INTO id;
		END WHILE;
	CLOSE idList;
END;

--  (2)调用存储过程
CALL add_score()

———————————————————————— 

附注：

1、MySQL实现循环的三种方式：

• while 循环：先判断，后执行循环
• repeat 循环：先循环，后执行判断
• Loop循环：比较灵活，可以在循语句任何位置执行判断，跳出循环

-- 1、while 循环
    while done != 1 do
        fetch cur into id;
          if done != 1 then  -- 注意：此时这里不加判断，会多循环一次
             set total = total + 1;
          end if;    
    end while;

-- 2、repeat 循环
    repeat
        fetch cur into id;
           if done != 1 then  -- 注意：此时这里不加判断，会多循环一次
              set total = total + 1;
           end if;
        until done = 1
    end repeat;

-- 3、Loop循环
	lebal_end:LOOP
	    -- 取值设置到临时变量中
		FETCH idList INTO id;
		
            -- 判断是否需要跳出循环
		if flag = 1 then
			leave lebal_end;
		end if;	
			
            -- 执行循环体	
		SELECT id;

	END LOOP;

2、关于游标使用的参考网址：

• https://www.cnblogs.com/oukele/p/10684639.html
• https://www.it610.com/article/1281024937112387584.htm