最近想要在回去复习mysql语句,就在网上找了一些案例练习,起初找得都是零零散散的,后面参考这篇博客做出了一个实操案例。Eric_Squirrel:mysql学生表经典案例50题。
首先是建表,我用的是mysql5.7,基本上没有问题
建库
创建alibaba数据库
切换到alibaba数据库
建表
创建学生表student表=
- create table student(s_id varchar(10),s_name varchar(10),s_age date,s_sex varchar(10)) engine=InnoDB default charset utf8;
- insert into student(s_id,s_name,s_age,s_sex)
- values('01' , '赵雷', '1990-01-01' , '男'),
- ('02' , '钱电' , '1990-12-21' , '男')
- ,('03' , '孙风' , '1990-05-20' , '男')
- ,('04' , '李云' , '1990-08-06' , '男')
- ,('05' , '周梅' , '1991-12-01' , '女')
- ,('06' , '吴兰' , '1992-03-01' , '女')
- ,('07' , '郑竹' , '1989-07-01' , '女')
- ,('08' , '王菊' , '1990-01-20' , '女');
查询 student 表中的数据
创建课程表course
- create table course(c_id varchar(10),c_name varchar(10),t_id varchar(10)) engine=InnoDB default charset utf8;
- insert into course values('01' , '语文' , '02'),
- ('02' , '数学' , '01'),
- ('03' , '英语' , '03');
查询 course 表中的数据
创建教师表teacher
- create table teacher(t_id varchar(10),t_name varchar(10)) engine=InnoDB default charset utf8;
- insert into teacher values('01' , '张三'),('02' , '李四'),('03' , '王五');
查看 teacher 表中的数据
创建学生成绩表stu_sco
- create table stu_sco(s_id varchar(10),c_id varchar(10),score decimal(18,1)) engine=InnoDB default charset utf8;
- insert into stu_sco values ('01' , '01' , 80), ('01' , '02' , 90), ('01' , '03' , 99), ('02' , '01' , 70), ('02' , '02' , 60), ('02' , '03' , 80), ('03' , '01' , 80), ('03' , '02' , 80), ('03' , '03' , 80), ('04' , '01' , 50), ('04' , '02' , 30), ('04' , '03' , 20), ('05' , '01' , 76), ('05' , '02' , 87), ('06' , '01' , 31), ('06' , '03' , 34), ('07' , '02' , 89), ('07' , '03' , 98);
查看 stu_sco 表中的数据
效果图

接下来就是对sql的操作;
完成如下查询语句
1、查询"01"课程比"02"课程成绩高的学生信息及课程分数
- SELECT a.*, b.score AS sc1, c.score AS sc2
- FROM student a
- JOIN stu_sco b
- ON a.s_id = b.s_id
- AND b.c_id = 1
- JOIN stu_sco c
- ON a.s_id = c.s_id
- AND c.c_id = 2
- WHERE b.score > c.score;

sql解析:给student表起个别名 a ,给stu_sco表起个别名b,给stu_sco表起个别名c 。注意:b表和c表本质上是同一张表,名字不同而已。然后把这三张表用JOIN连接起来,很显然我们要的不是三张表的全部数据,所以我们要在ON后面加上我们想要过滤的条件。Where后面在加上限制条件,就能得到我们想要的数据。

MySQL中 join的用法:join具有 连接的作用,即当两个或者两个以上的表有关系时,需要用join来连接这些相关的表,来处理或分析数据。
举个例子,我用teacher表course表进行连接


执行select * from teacher JOIN course;之后,等到得新表为

course表和teacher表一一匹配,得出一个新表
新表的列名是两个表列名加起来的,可能会产生相同的列名,如t_id
先用表course中的一行数据和表teacher中的每一行数据不断的拼接,产生新的行
再用表course的第二行去和表teacher中的每一行数据拼接,以此类推
表chourse是3行,表teacher是2=3行,所以按照上面的规律会产成3*3 = 9行的新的表

一般我们join后的表,并不是我们想要的,这时,可以用 ON 来加一些条件:
例如:teacher join course on teacher.t_id = course.t_id ,on后面就是我们加的条件,我们想要teacher.t_id这一列数据和course.t_id这一列的数据相等的数据,这里注意一下,join后的表列名是有重复的,所以ON后面的条件语句中我们要加上原来的表名。
- select * from teacher join course on teacher.t_id = course.t_id;

2、查询平均成绩大于等于60分的同学的学生编号和学生姓名和平均成绩
- SELECT student.s_id AS 学生编号, student.s_name AS 学生姓名, AVG(score) AS 平均成绩
- FROM student
- JOIN stu_sco ON student.s_id = stu_sco.s_id
- GROUP BY student.s_id, student.s_name
- HAVING 平均成绩 >= 60;

sql解析:从student表和stu_sco表中查询出学生编号,学生姓名,学生平均成绩,并且学生平均成绩要大于60

MySQL中 GROUP BY的用法:
1、当聚集函数和非聚集函数出现在一起时,需要将非聚集函数进行group by
2、当只做聚集函数查询时候,就不需要进行分组了。
MySQL中的聚合函数用来对已有数据进行汇总,如求和、平均值、最大值、最小值等。
count(col): 表示求指定列的总行数
max(col): 表示求指定列的最大值
min(col): 表示求指定列的最小值
sum(col): 表示求指定列的和
avg(col): 表示求指定列的平均值
简单的说,就是先看select 后面的字段,看他要查询那些字段。

其中数据表中有的字段叫非聚集函数,比如上面的student.s_name和student.s_id;这两个字段是student表中有的字段

而数据表中没有的字段叫聚集函数,比如平均成绩是通过聚合函数AVG(score)出来的
比如张三的各科成绩的score为语文:100,数学:98
AVG(score)之后就是99,要区别开:score是非聚集函数,但是AVG(score)是聚集函数。
对于上面的sql,我们就要用
GROUP BY student.s_id, student.s_name
对这两个非聚集函数分组,如果不用GROUP BY分组,就会出现下面的错误

MySQL中 HAVING的用法:
having一般和group by 配合使用。
误区:不要错误的认为having和group by 必须配合使用。
例如:没有group by也能用having

对于非聚集函数,having和where的用法是一样的

但是,如果select后面没有那个字段,但是数据表中有那个字段,此时只能用where(大家可以仔细看看两张截图中sql语句的区别)

对于聚集函数,只能用having

3、查询所有同学的学生编号、学生姓名、选课总数、所有课程的总成绩
- SELECT student.s_id AS 学生编号, student.s_name AS 学生姓名, COUNT(c_id) AS 选课总数
- , SUM(score) AS 总成绩
- FROM student
- JOIN stu_sco ON student.s_id = stu_sco.s_id
- GROUP BY student.s_id, student.s_name;

4、查询"李"姓老师的数量
- SELECT COUNT(*) AS 李姓老师的数量
- FROM teacher
- WHERE t_name LIKE '李%';

MySQL LIKE 语法:
LIKE运算符用于WHERE表达式中,以搜索匹配字段中的指定内容,语法如下:
WHERE column LIKE 参数
WHERE column NOT LIKE 参数
在LIKE全面加上NOT运算符时,表示与LIKE相反的意思,即选择行不包含参数的数据记录
LIKE通常与通配符%一起使用,而不加通配符%的LIKE语法,表示精确匹配,其实际效果等同于 = 等于运算符
MySQL中 %的用法:
%在sql语句中表示通配符,在模糊查询中用到 如查询姓名以 李开头的 就写成 like ‘李%’ 如 姓名以 李结尾的 写成 like ‘%李’。 姓名中包含 李的 写成 like '%李%'
5、查询学过"张三"老师授课的同学的信息
- SELECT *
- FROM student
- WHERE s_id IN (
- SELECT s_id
- FROM stu_sco
- WHERE c_id IN (
- SELECT c_id
- FROM course
- WHERE c_id IN (
- SELECT t_id
- FROM teacher
- WHERE t_name = '张三'
- )
- )
- );

sql解析:查询student表中s_id的范围在 ( 查询stu_sco表中c_id的范围在 ( 查询teacher表中的t_id限制条件是t_name=’张三' ) )中的数据

详细信息如下

MySQL中 IN 的用法:
in常用于where表达式中,其作用是查询某个范围内的数据。
例如:查询student表中,s_id的值的范围是01,02,03的数据

PS: not in与in作用相反
例如:查询student表中,s_id的值的范围不是01,02,03的数据

6、查询学过编号为"01"并且也学过编号为"02"的课程的同学的信息
- SELECT *
- FROM student
- WHERE s_id IN (
- SELECT a.s_id
- FROM (
- SELECT *
- FROM stu_sco
- WHERE c_id = '01'
- ) a
- JOIN (
- SELECT *
- FROM stu_sco
- WHERE c_id = '02'
- ) b
- ON a.s_id = b.s_id
- );

sql解析:查询学生表中的全部信息,限制条件是s_id在(查询stu_sco表中s_id为两个stu_sco表的数据,其中a的c_id为01,b的c_id为02并且两个表中的s_id要相等)范围内的数据

7、查询学过编号为"01"但是没有学过编号为"02"的课程的同学的信息
- SELECT student.*
- FROM student, stu_sco sc1
- WHERE student.s_id = sc1.s_id
- AND sc1.c_id = '01'
- AND student.s_id NOT IN (
- SELECT s_id
- FROM stu_sco sc2
- WHERE sc2.c_id = '02'
- );

sql解析:根据student表和stu_sco表来查询student表中的所有数据,限制条件是student表中的s_id和stu_sco表中的s_id相等,并且stu_sco表中的c_id等于01,和学生表中的s_id不在(根据stu_sco表查询s_id,限制条件是c_id不等于02)范围内。
8、查询没有学全所有课程的学生信息
- SELECT student.*
- FROM student
- JOIN stu_sco ON student.s_id = stu_sco.s_id
- GROUP BY student.s_id, student.s_name, student.s_age, student.s_sex
- HAVING COUNT(stu_sco.c_id) < (
- SELECT COUNT(DISTINCT c_id)
- FROM course
- );
(未完,待补充)