mysql练习案例(实操)
最近想要在回去复习mysql语句，就在网上找了一些案例练习，起初找得都是零零散散的，后面参考这篇博客做出了一个实操案例。Eric_Squirrel：mysql学生表经典案例50题。
首先是建表，我用的是mysql5.7,基本上没有问题
建库
创建alibaba数据库
create database alibaba;切换到alibaba数据库
use 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 表中的数据
select * from 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 表中的数据
select * from 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 表中的数据
select * from 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 表中的数据
select * from 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
);（未完，待补充）
免责声明：
① 本站未注明“稿件来源”的信息均来自网络整理。其文字、图片和音视频稿件的所属权归原作者所有。本站收集整理出于非商业性的教育和科研之目的，并不意味着本站赞同其观点或证实其内容的真实性。仅作为临时的测试数据，供内部测试之用。本站并未授权任何人以任何方式主动获取本站任何信息。
② 本站未注明“稿件来源”的临时测试数据将在测试完成后最终做删除处理。有问题或投稿请发送至: 邮箱/279061341@qq.com QQ/279061341
![[mysql]mysql8修改root密码](https://static.528045.com/imgs/31.jpg?imageMogr2/format/webp/blur/1x0/quality/35)
