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
);

 

 sql解析：从student表中查询student的全部信息，把student表和stu_sco表做关联，关联条件是student.s_id = stu_sco.id，把字段进行分组，限制条件是stu_sco.c_id的数据量小于从course表中查询出来的c_id的数量。

mysql中 distinct 的用法:

distinct 关键字是去重的，如果查询的是单个字段的就是去掉单个字段中重复的数据，如果查询的是多个字段，那么去重的是多个字段中完全相同的数据。

9、查询至少有一门课与学号为"01"的同学所学相同的同学的信息

select student.*
from student
where s_id in (
    select distinct s_id
    from stu_sco
    where c_id in (
            select c_id
            from stu_sco
            where s_id = '01'
        )
        and s_id <> '01'
);

 

 sql解析：从学生表查询学生信息，限制条件是s_id在（从stu_sco中查询s_id，并且s_不等于 '01' ，限制条件是c_id在（从stu_sco表中查询c_id，限制条件是s_id = '01'）范围内）范围内

mysql中 <> 的用法:

!=，<> 两者都是不等于的意思，!= 是以前sql标准，<> 是现在使用的sql标准，推荐使用 <>。

10、查询和"01"号的同学学习的课程完全相同的其他同学的信息

select student.*
from student
where s_id in (
    select s_id
    from stu_sco
    where s_id <> '01'
    group by s_id
    having count(1) = (
        select count(c_id)
        from stu_sco
        where s_id = '01'
    )
);

 

 sql解析：从student表中查询student全部信息，限制条件是s_id在（从stu_sco表中查询s_id，限制条件是s_id不等于 '01' 并且用s_id做分组，统计s_id中每个s_id的数量等于从stu_sco表中查询c_id的数量，限制条件是s_id等于 '01'）范围内

 

（未完，待补充）