SQL基本语法练习

此文档是数据分析课程中的数据库第一阶段课程知识点的复习文档，本阶段课程目标是通过实战练习巩固常用的SQL语句；

基于个人实际及工作需要

本阶段主要关注：

        数据查询语言（DQL：DataQueryLanguage）

本阶段基本了解：

        数据操作语言（DML：Data Manipulation Language）-- 操作数据表

        数据定义语言（DDL：Data Definition Language）-- 操作数据库

本阶段暂不学习：

         数据控制语言（DCL：Data Control Language）-- 用户权限控制

        事务控制语言（TCL ：Transaction Control Language）

课程环境：

• win10系统
• mysql community 8.0.23.0
• Navicat Premium 12.0.29

本次课程作业主要涉及的知识点：

• SQL中的数据类型
• SQL中的常用约束
• 数据表的创建与删除
• 表数据的增、删、改操作
• SQL查询的语法结构和执行顺序
• 单表查询：常用的聚合函数、分组查询、条件过滤、排序
• 多表查询：表结构与表关系的设计、多表连接、子查询（数据量少，未涉及到union合并查询）
• 视图、with as、开窗函数第一阶段课程暂时不涉及

---

一阶段复习总结：

关于SQL书写tips:

当列名与列名之间的逗号放在列名之后时，很容易被忽视，忽视就会导致程序报错。建议把列与列之间的逗号要放在列名前，而不是放在列名后，可以减少代码错误率

中英文逗号报错，部分软件报错提示会第几行第几个字符开始有语法错误，由于字段长短不一，通过计算字符数量来排除是耗时耗力的，逗号放在前面更容易排查

• 在UPDATE或DELETE语句使用WHERE子句前，应该先用SELECT进行测试，保证它过滤的是正确的记录，以防编写的WHERE子句不正确

• 关于表设计的一些建议

• 对已有表做更改既复杂又不统一，理想情况下，不要在表中包含数据时对其进行更新。应该在表的设计过程中充分考虑未来可能的需求，避免今后对表的结构做大改动

• MySQL 在 Windows 系统下不区分大小写，但在 Linux 系统下默认区分大小写。因此，数据库名、表名和字段名，建议统一使用小写字母，避免节外生枝

• 在UPDATE或DELETE语句使用WHERE子句前，应该先用SELECT进行测试，保证它过滤的是正确的记录，以防编写的WHERE子句不正确

• 关于分组聚合的注意事项

1. 分组的目的就是为了统计，所以一般分组会和聚合函数一起使用，单独对某个字段分组，是没有意义的
2. Group by 的字段必须出现在select后面
3. select后面除了分组聚合的字段和聚合函数，不能出现其他字段
4. 如果需要在分组后对数据进行过滤（比如对count的结果进行过滤），需要使用having关键字
5. 分组操作中的having子语句，是用于在分组后对数据进行过滤的，作用类似于where条件

• 关于多表查询实现思路

1. 确定需要的数据来自哪几张表
2. 分析表结构，表与表之间是什么关系，通过什么关键字连接
3. 查询的限定条件是什么
4. 需要查询哪些字段，各来自哪个表

• 关于子查询的简单总结

• 子查询如果查出的是一个字段(单列), 那就在where后面作为条件使用
• 子查询如果查询出的是多个字段(多列), 就当做一张表使用(要起别名)

一阶段复习重点关注：

• SQL语句的语法规范，目标是能熟练、完整地写出SQL语句的框架结构
• SQL语句的执行顺序，关注执行顺序主要是区别哪些地方可以使用别名，哪些地方使用原始字段名；

SQL语法结构和执行顺序

语句	执行步骤
select 字段1 as 别名1 , 字段2 as 别名2	6
from 表A as a	1
[left|right|inner] join  表B as b on  a.字段1 = b.字段1	2
[left|right|inner] join  表C as c on  a.字段1 = c.字段1
where 筛选条件	3
group by 分组的字段	4
having 聚合之后的筛选，比如avg(字段) >=60	5
order by 排序字段	7
limit 从第m条记录开始展示，一共展示n条记录	8

复习过程中的薄弱点与易错点：

1、约束，特别是外键约束不够熟练，不熟悉基本的语法结构：

设置外键约束的语法：

constraint 约束名 foreign key(列名) references 参照的表名(参照的列名)

2、多表查询语句的书写

连接条件应该紧跟着join连接，在完成作业过程中错误的写法：

-- 语法结构错误，运行报错
student as t1 
left JOIN course as t2 
left JOIN score as t3  
on t1.clsid = t2.clsid  and t2.cid =t3.course_id

3、语句执行顺序掌握

需要特别注意的是：having在select之前执行，在作业完成过程中的一个典型错误：将聚合函数的字段放到having后面筛选；虽然没有报错，但是也没有实现筛选需求

-- 此代码没有报错，但查询结果中也出现了≥60分的记录
SELECT t1.sid as "学生编号"
			,sname as "学生姓名"
			,ROUND(avg(number),2) as "平均成绩"
FROM  student as t1 
left JOIN score as t2  on t1.sid =t2.sid
GROUP BY  sname,t1.sid
HAVING "平均成绩" < 60
ORDER BY avg(number) asc 

4、关于分组聚合的一点补充说明

如果有Group By，那么Select后面查询的字段，除了group by的字段、聚合函数，不能出现其他字段，否则可能报错也可能生成非预期的查询结果

---

本阶段练习题的完成情况：

第一部分：根据给定的结构数据，进行表的创建和数据新增

1、创建表classes，clsid设置为主键并自增

-- 1、创建表classes，clsid设置为主键并自增
create table classes(
	clsid int auto_increment -- 设置字段自增
	,primary key(clsid)	-- 设置cls为主键
	,clsname char(3)
)

-- 插入数据
INSERT into classes VALUES(1,"超越班"),(2,"火箭班"),(3,"先驱班")

课程表结构和数据

clsid	clsname
1	超越班
2	火箭班
3	先驱班

2、创建表student，sid设置为主键并自增，clsid设置为外键，参照表为classes表，参照列为clsid，外键名为pk_cls

-- 2、创建表student，sid设置为主键并自增，clsid设置为外键，参照表为classes表，参照列为clsid，外键名为pk_cls
create table student(
	 sid      int  				auto_increment
	,primary  key(sid)
	,sname    varchar(4)
	,sgender  char(1)
	,sbrithday date
	,clsid int
	-- 设置外键约束： constraint 约束名 foreign key(列名) references 参照的表名(参照的列名)
	,constraint pk_cls foreign key(clsid) references classes(clsid) 
	)

INSERT into student 
VALUES(1,"张三丰","男","1890-01-01",1),(2,"张无忌","男","1992-12-12",1)
		,(3,"周芷若","女","1992-10-10",2),(4,"赵敏",  "女","1992-07-07",2)
		,(5,"蛛儿",  "女","1994-06-06",3),(6,"韦一笑","男","1972-08-19",3)		

3、创建表teacher，tid为主键并自增

-- 3、创建表teacher,tid为主键并自增
create table teachers(
	 tid int auto_increment
	,primary key(tid)
	,tname varchar(32)
)

INSERT into teachers VALUES(1,"Oldlu"),(2,"Admin"),(3,"Kenvin");

4、创建表course,为course添加tid的外键约束，参照表为teacher表，外键名为pk_tea.,cid为主键并自增

-- 4、创建表course,为course添加tid的外键约束，参照表为teacher表，外键名为pk_tea.,cid为主键并自增
drop table if exists course;
create table course(
	 cid int auto_increment
	,PRIMARY key(cid)
	,cname varchar(32)
	,tid int
	,constraint pk_tea foreign key(tid) references teachers(tid)
);

INSERT into course value(1,"Java",1),(2,"Python",2),(3,"前端",3);

5、 通过sql语句，创建表score，sid为主键并自增，student_id为外键，参照表student，参照列为sid，外键名自定义，coursed_id为外键，参照表为course，参照列为cid，外键名自定义。

-- 5、创建表score，sid为主键并自增，student_id为外键，参照表student，参照列为sid，外键名自定义，
-- coursed_id为外键，参照表为course，参照列为cid，外键名自定义
drop table if exists score;
create table score(
	sid int auto_increment  -- sid为主键并自增
	,primary key (sid)
	,student_id int
	,constraint sc_stu foreign key(student_id) references student(sid)
	,course_id int
	,constraint sc_co  foreign key(course_id)  references course(cid)
	,number int
);

-- 由于分数表已经设置了主键自增所以不再插入sid，而是使用自增方式让数据库自行添加
insert into score(student_id,course_id,number) 
value(1,1,90),(1,2,80),(1,3,60)
,(2,1,100),(2,3,100)
,(3,2,100)
,(4,3,59)
,(5,1,40),(5,2,60)
,(6,2,60),(6,3,80);
select * from score;

6、使用sql语句为学生表添加三条数据

-- 6、使用sql语句为学生表添加三条数据
insert into student(sname,sgender,sbrithday,clsid) 
values("金毛狮王","男","1960-09-06",1)
	 ,("杨逍","男","1970-06-06",2)
	 ,("灭绝师太","女","2003-10-19",3);
select * from student;

第二部分：根据给定的条件，对表中的数据进行增删改操作

7、将姓名为灭绝师太的性别修改为‘女’，生日改为‘1970-02-02’

-- 7、将姓名为灭绝师太的性别修改为‘女’，生日改为‘1970-02-02’
update student 
set sgender = "女" ,sbrithday ="1979-02-02"
where sname = "灭绝师太";

8、删除杨逍

-- 8、删除杨逍
delete from student where sname = "杨逍"

第三部分：数据查询常用操作：分组聚合、多表连接、子查询

9、 查询学生表显示所有数据

-- 9、查询学生表显示所有数据
select * from student;

10、 求每个学生的名字，列别名为“姓名”

-- 10、求每个学生的名字，列别名为“姓名”
select sname as "姓名" from student;

11、求名字为张无忌的个人信息

-- 11、求名字为张无忌的个人信息
select * from student where sname = "张无忌";

12、求班级为3的所有学生的信息

-- 12、求班级为3的所有学生的信息
select * from student where clsid = 3;

13、查询每个学生对应的班级

-- 13、查询每个学生对应的班级
select t1.sname as "学生姓名" 
			,t2.clsname  as "对应班级"
from  student as t1 
left join classes as t2 
on t1.clsid = t2.clsid;

14、 查询每个学生对应的课程和成绩

-- 14、查询每个学生对应的课程和成绩（方法：使用三表连接实现）
select t1.sname as "学生姓名" 
			,t3.cname  as "课程名称"
			,t2.number as "课程成绩"
from  student as t1 
left join score as t2  on t1.sid =t2.student_id
left join course as t3 on t2.course_id = t3.cid;

-- 上面的t2和t3 也可以用innner实现
select t1.sname as "学生姓名" 
			,t3.cname  as "课程名称"
			,t2.number as "课程成绩"
from  student as t1 
left join score as t2  on t1.sid =t2.student_id
inner join course as t3 on t2.course_id = t3.cid;

-- 也可以用子查询实现
select sname as "学生姓名"
			,cname as "课程名称"
			,number as "课程成绩"
from student as t1 left join 
			(select sid,cname,number 
			from score as t2 
			left join course as t3 
			on t2.course_id = t3.cid) as temp
on t1.sid = temp.sid

15、查询"1"课程比"2"课程成绩高的学生的信息及课程分数

本题回答过程中思路不太清晰，百度之后答出来的

多表查询进行步骤拆分

-- 15、查询"1"课程比"2"课程成绩高的学生的信息及课程分数
-- 拆分实现
-- step1 先分别求出课程1的分数，和课程2的分数
select student_id,number as  sc1 from score where course_id =1;
select student_id,number as  sc2 from score where course_id =2;

-- step2 将step1的2个结果进行内连接，查询出2门课都学习的sid
select * from (
	(select student_id,number as sc1 from score where course_id =1) as num1
	inner  join
	(select student_id,number as sc2 from score where course_id =2) as num2
	on num1.student_id = num2.student_id);

-- step3 在step2的基础上，使用where进行过滤，找出sc1 > sc2的学生id
select num1.student_id from (
	(select student_id,number as sc1 from score where course_id =1) as num1
	inner  join
	(select student_id,number as sc2 from score where course_id =2) as num2
	on num1.student_id = num2.student_id)
where sc1 > sc2;

-- 最后实现：把step3的结果作为一个子查询，作为student查询的过滤条件
select * from student where sid in(
	select num1.student_id from (
		(select student_id,number as sc1 from score where course_id =1) as num1
		 inner  join
        (select student_id,number as sc2 from score where course_id =2) as num2
		 on num1.student_id = num2.student_id)
		 where sc1 > sc2);

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

-- 16、查询平均成绩大于等于60分的同学的学生编号和学生姓名和平均成绩
select t1.sid as "学生编号"
			,sname as "学生姓名"
			,round(avg(number),2) as "平均成绩"
from  student as t1 
left join score as t2  on t1.sid =t2.student_id
group by  sname,t1.sid
having avg(number) >= 60
order by avg(number) asc;

17、 查询平均成绩小于60分的同学的学生编号和学生姓名和平均成绩

本题回答过程中没有注意语句的执行顺序，产生结果出现非预期错误：

在having 后面写了聚合运算后的字段名，实际查询结果一开始并没有过滤掉60分及以上的记录

另外：答到17题注意到之前的几处连接都是将成绩表中的sid和学生表中的sid进行连接，查询没有报错但实际非预期结果，要注意数据结构

-- 注意：这是错误的写法！！！！！

select t1.sid as "学生编号"
			,sname as "学生姓名"
			,round(avg(number),2) as "平均成绩"
from  student as t1 
left join score as t2  on t1.sid =t2.sid  -- 这里的连接条件有误
group by  sname,t1.sid
-- 注意：having是select之前执行的，语句执行到这里其实还没有生成“平均成绩”字段
having "平均成绩" < 60  
order by avg(number) asc 

-- 17、查询平均成绩小于60分的同学的学生编号和学生姓名和平均成绩
select t1.sid as "学生编号"
			,sname as "学生姓名"
			,round(avg(number),2) as "平均成绩"
from  student as t1 
left join score as t2  on t1.sid =t2.student
group by  sname,t1.sid
having avg(number) < 60
order by avg(number) asc;

18、查询"张"姓学生的数量

-- 18、查询"张"姓学生的数量
select * from student where sname like "张%"; -- 明细里有张三丰和张无忌2条数据
select count(1) as "姓张的学生数" from student where sname like "张%"; 

19、查询没学过"admin"老师授课的同学的信息

-- 19、查询没学过"admin"老师授课的同学的信息
select *
from  student as t1 
WHERE sid not in (
		SELECT student_id 
		from score as s 
		left join course as c 
		on s.course_id = c.cid 
		WHERE tid  =  (SELECT tid FROM teachers 
						WHERE tname = "admin")  -- 查询出admin老师的tid
);

20、 查询学过“1”并且也学过编号“2”课程的同学的学号、姓名；

-- 20、查询学过“1”并且也学过编号“2”课程的同学的学号、姓名；
select sid as "学生编号"
	  ,sname as "学生姓名"
from student where sid in(
			select student_id
			from score 
			where course_id =1 or course_id =2 
			group by student_id
			having count(course_id) =2
);

21、查询“Java”课程比“前端”课程成绩高的所有学生的学号

-- 21、查询“java”课程比“前端”课程成绩高的所有学生的学号
-- 实现思路与15题类似，多了一部成绩表内联课程表的步骤
select num1.student_id from (
		(select student_id, cname, number as sc1 from score 
		inner join course on score.course_id = course.cid where cname ="java") as num1
	inner  join
		(select student_id, cname, number as sc2 from score 
		inner join course on score.course_id = course.cid where cname ="前端") as num2
	on num1.student_id = num2.student_id)
where sc1 > sc2;

22、查询有课程成绩小于60分的同学的学号、姓名；

-- 先在成绩表中查询出分数小于60分的学号
select student_id from score where number <60;

-- 然后把第一步的结果作为学生表查询的过滤条件
select sid as "学号",sname as "学生姓名" from student where sid in (
	select student_id from score where number <60
);

23、查询所有同学的学号、姓名、选课数、总成绩；

-- 先在成绩表中进行分组查询,求出学号对应的选课数和成绩
select student_id
			,count(course_id) as course_num
			,sum(number) as sum_num 
from score  
group by student_id;

-- 把第一步的结果作为一个结果表与学生版进行连接
select sid as "学号"
    ,sname as "学生姓名" 
    ,course_num as "选课数" 
    ,sum_num as "总成绩"
from student as t1 left join 
						(select student_id
						,count(course_id) as course_num
						,sum(number) as sum_num
						from score  
						group by student_id) as temp
on  t1.sid = temp.student_id;

24、查询至少学过学号为“1”同学所学课程中任意一门课的其他同学学号和姓名；

-- step1 查询出学号1的同学学过的课程id（查询结果是1，2，3）
select course_id from score where student_id =1;

-- step2 查询出学过上述课程的学生学号,由于一个学生可能学习了多门课程，此处需要对学号进行去重
select distinct student_id from score where  course_id in (
		select course_id from score where student_id =1
);

-- 最后实现：把上面的学号作为学生表查询的过滤条件
select sid as "学号",sname as "学生姓名"  from student where sid in(
			select distinct student_id from score where  course_id in (
							select course_id from score where student_id =1)
);

25、 查询男生、女生的人数；

select sgender as "性别"
			,count(sgender)	 as "人数"
from student group by sgender;

课程参考内容

视频教程：尚硅谷数据库基础教程（李玉婷老师）、拉勾教育Mysql数据分析实战（应癫老师）

书籍参考：《SQL必知必会（第5版）》By：本·福达