SQL习题集_详细注释版答案

目录

一、准备工作

二、MySQL语句执行顺序

三、题目

四、附录

---

一、准备工作

1 环境

MySQL 5.7.28 + Ubuntu18.04.4

2 登录MySQL

-- Terminal下输入
mysql -uroot -p

3 数据库操作

-- 创建数据库 practice
-- character set 用来指定编码格式，方便之后插入中文
create database practice character set utf8;

-- 查看数据库
show databases like 'practice';

-- 选择数据库
use pratice;

4 创建数据表

4.1 Student表（sid 学生编号,sname 学生姓名,sage 出生年月,ssex 学生性别）

-- default charset指定编码格式
create table Student(sid varchar(10), sname varchar(10), sbirthday date, ssex varchar(10)) default charset = utf8;
insert into Student values('01' , '赵雷' , '1990-01-01' , '男');
insert into Student values('02' , '钱电' , '1990-12-21' , '男');
insert into Student values('03' , '孙风' , '1990-12-20' , '男');
insert into Student values('04' , '李云' , '1990-12-06' , '男');
insert into Student values('05' , '周梅' , '1991-12-01' , '女');
insert into Student values('06' , '吴兰' , '1992-01-01' , '女');
insert into Student values('07' , '郑竹' , '1989-01-01' , '女');
insert into Student values('09' , '张三' , '2017-12-20' , '女');
insert into Student values('10' , '李四' , '2017-12-25' , '女');
insert into Student values('11' , '李四' , '2012-06-06' , '女');
insert into Student values('12' , '赵六' , '2013-06-13' , '女');
insert into Student values('13' , '孙七' , '2014-06-01' , '女');

4.2 修改数据库、数据表、字段编码

insert into Student values('01' , '赵雷' , '1990-01-01' , '男');

-- 如果创建数据库或数据表时没有指定编码，则会提示如下错误：
ERROR 1366 (HY000): Incorrect string value: '\xE8\xB5\xB5\xE9\x9B\xB7' for column 'sname' at row 1
-- Incorrect string value 不正确的字符串，定位到是中文编码的问题

查看和修改数据库编码

-- 查看数据库编码 
show variables like 'character_set_database';

-- 查看数据表编码
-- show create table <表名>;
show create table Student;

-- 修改数据库编码
-- alter database <数据库名> character set utf8;
alter database pratice character set utf8;

-- 修改数据表编码
-- alter table <表名> character set utf8;
alter table Student character set utf8;

-- 修改字段编码
-- alter table <表名> change <字段名> <字段名> <类型> character set utf8;
alter table Student change sname sname varchar(10) character set utf8;
alter table Student change ssex ssex varchar(10) character set utf8;

4.3 Course表（cId 课程编号,cname 课程名称,tid 教师编号）

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

4.4 Teacher表（tid 教师编号,tname 教师姓名）

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

4.5 SC表（sid 学生编号,cid 课程编号,score 分数）

-- decimal(M,D) 数据类型用于要求非常高的精确计算中
-- M 指定小数点左边和右边可以存储的十进制数字的最大个数，最大精度为38
-- D 指定小数点右边可以存储的十进制数字的最大个数。小数位数必须是从0~M之间的值，默认小数位数是0. 
-- decimal(5,2) 规定了存储的值将不会超过五位数字 ，而且小数点后面有两位数字。
create table SC(sid varchar(10),cid varchar(10), score decimal(18,1)) default charset = utf8;
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);

二、MySQL语句执行顺序

(1)  FROM <left_table>
(2)  ON <join_condition>
(3)  <join_type> JOIN <right_table>
(4)  WHERE <where_condition>
(5)  GROUP BY <group_by_list>
(6)  HAVING <having_condition>
(7)  SELECT
(8)  DISTINCT <select_list>
(9)  ORDER BY <order_by_condition>
(10) LIMIT <limit_number>

三、题目

官方文档：https://dev.mysql.com/doc/refman/5.7/en/func-op-summary-ref.html

在网传50题的基础上，删除了10道冗余的，最终保留了40道

1    查询" 01 "课程比" 02 "课程成绩高的学生的信息及课程成绩

-- 方法一
-- 构建01课程子表和02课程子表筛选出01课程比02课程成绩高的学生，然后再跟Student表进行连接
select Student.*, socre_01, socre_02 from
(select * from
(select sid as sid_01, score as socre_01 from SC where cid = '01') A
left join
(select sid as sid_02, score as socre_02 from SC where cid = '02') B
on A.sid_01 = B.sid_02
where socre_01 > socre_02) C
left join Student
on C.sid_01 = Student.sid;

-- 方法二
-- 构建01课程子表、02课程子表，并与Student表进行连接，然后再进行相应的筛选
select s.*, a.score as score_01, b.score as score_02
from Student s,
     (select sid, score from SC where cid=01) a,
     (select sid, score from SC where cid=02) b
where a.sid = b.sid and a.score > b.score and s.sid = a.sid;

+------+--------+------------+------+----------+----------+
| sid  | sname  | sbirthday  | ssex | socre_01 | socre_02 |
+------+--------+------------+------+----------+----------+
| 02   | 钱电   | 1990-12-21 | 男   |     70.0 |     60.0 |
| 04   | 李云   | 1990-12-06 | 男   |     50.0 |     30.0 |
+------+--------+------------+------+----------+----------+

交叉连接
select * from A, B, C
假设表A有5条记录，表B有6条记录，表C有7条记录，则会生成 5*6*7=210 条记录（笛卡尔积）

2    查询同时存在" 01 "课程成绩和" 02 "课程成绩的学生信息及课程分数

select * from 
     (select sid, score from SC where cid=01) A,
     (select sid, score from SC where cid=02) B
where A.sid = B.sid;

+------+-------+------+-------+
| sid  | score | sid  | score |
+------+-------+------+-------+
| 01   |  80.0 | 01   |  90.0 |
| 02   |  70.0 | 02   |  60.0 |
| 03   |  80.0 | 03   |  80.0 |
| 04   |  50.0 | 04   |  30.0 |
| 05   |  76.0 | 05   |  87.0 |
+------+-------+------+-------+

3    查询存在" 01 "课程成绩但可能不存在" 02 "课程成绩的学生ID(不存在时显示为 null )

select * from 
(select sid as sid_01 from SC where cid=01) A
left join
(select sid as sid_02 from SC where cid=02) B 
on A.sid_01 = B.sid_02;

+--------+--------+
| sid_01 | sid_02 |
+--------+--------+
| 01     | 01     |
| 02     | 02     |
| 03     | 03     |
| 04     | 04     |
| 05     | 05     |
| 06     | NULL   |
+--------+--------+

4    查询存在" 01 "课程成绩但不存在" 02 "课程成绩的学生ID

select * from 
(select sid as sid_01 from SC where cid=01) A
left join
(select sid as sid_02 from SC where cid=02) B 
on A.sid_01 = B.sid_02
where sid_02 is NULL;

+--------+--------+
| sid_01 | sid_02 |
+--------+--------+
| 06     | NULL   |
+--------+--------+

5    查询平均成绩大于等于 60 分的同学的学生编号和学生姓名和平均成绩

-- 先再SC表中筛选出平均分大于60的sid，然后跟Student表的sname链接
select A.sid, Student.sname, A.score_avg from
(select sid, avg(s