【Mysql】我的实践笔记~ + 一些例题 ~ + 持续更新~

目录

实践笔记

1、执行顺序

2、关于各种join

3、关于group by + select

4、关于select内子查询（行方向进行）

5、一个特殊场景（聚合函数+case when）

6、一些经验

题目实操

1、数据表介绍

2、问题

---

 

实践笔记

 

1、执行顺序

FROM -- WHERE -- GROUP BY -- SELECT -- HAVING -- ORDER  BY -- LIMIT

（Mysql关键字尽量写大写）

 

2、关于各种join

笛卡尔积 + where = join + where/on（join后的where和on没区别）

A right join B = B left join A

left/right join 一定要带on（可找出左表那些与右表连不上）

 

3、关于group by + select

一道例题：(两种解法)

SELECT
    class_id 
FROM
    S
GROUP BY
    class_id
HAVING COUNT(id) > 2

SELECT 
    T.class_id 
FROM
    (
        SELECT
            class_id,COUNT(id) n
        FROM
            S
        GROUP BY
            class_id
    ) as T
WHERE
    T.n > 2

一个问题：为什么有时候group by后组之间还有顺序？

因为对于group by + 数字类型，mysql要分组前先排序更易于分组

可以 SELECT * FROM S GROUP BY class_id DESC;

 

4、关于select内子查询

select是选择结果集中要输出的字段列,如果字段列存在则直接输出

如果字段列不存在则新建,如果字段列不存在且依赖于其他的字段.则新建列,然后微观上是一行一行填值取出的

 

5、关于半连接

子查询中有引用父查询的数据。

执行过程：

比如来了个同学是一班，id是1，然后我们找到这个同学所在班的最大id，用这个同学的id和所在最大班级最大id相比，看这个同学的id是不是他班级里最大id（父查询where：看这个同学的id是不是=他所在班级的最大id，不是就过滤掉）

需求：

找出每个班级的最大id的学生的信息

普通写法

SELECT
    *
FROM
    stu
WHERE
    id IN
    (
        SELECT
            MAX(id)
        FROM
            stu
        GROUP BY
            classid
    )

半连接写法

 

6、一个特殊场景（聚合函数+case when）

SELECT
    NAME,
    MAX(
        CASE WHEN 学科='基' THEN 分数 ELSE NULL END
        ) AS '基'
    MAX(
            CASE WHEN 学科='基' THEN 分数 ELSE NULL END
        ) AS '爬'
    MAX(
            CASE WHEN 学科='基' THEN 分数 ELSE NULL END
        ) AS 'SQL'
FROM
    S
GROUP BY NAME

 

7、一些经验

不同行之间比较可以使用自连接

子查询（from里，where里，select里）

类二维表可以用聚合+case when 

窗口函数

变量

where (id,name) in (select id,name from s)

某分类的最什么：可以用半连接

 

题目实操

1、数据表介绍

（1）学生表
Student(SId,Sname,Sage,Ssex)
SId 学生编号,Sname 学生姓名,Sage 出生年月,Ssex 学生性别

create table Student(SId varchar(10),Sname varchar(10),Sage datetime,Ssex varchar(10));
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' , '女');

（2）课程表
Course(CId,Cname,TId)
CId 课程编号,Cname 课程名称,TId 教师编号

create table Course(CId varchar(10),Cname nvarchar(10),TId varchar(10));
insert into Course values('01' , '语文' , '02');
insert into Course values('02' , '数学' , '01');
insert into Course values('03' , '英语' , '03');

（3）教师表
Teacher(TId,Tname)
TId 教师编号,Tname 教师姓名

create table Teacher(TId varchar(10),Tname varchar(10));
insert into Teacher values('01' , '张三');
insert into Teacher values('02' , '李四');
insert into Teacher values('03' , '王五');

（4）成绩表
SC(SId,CId,score)
SId 学生编号,CId 课程编号,score 分数

create table SC(SId varchar(10),CId varchar(10),score decimal(18,1));
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);

2、问题

1.查询课程编号为“01”的课程比“02”的课程成绩高的所有学生的学号  (下面前三种写法都是等价的，合并为解法一)

解法一：自连接

SELECT
    A.SID
FROM
    SC A JOIN SC B ON A.CID = '01' AND B.CID = '02' AND A.SID = B.SID
WHERE
    A.SCORE > B.SCORE
 

SELECT
    A.SID
FROM
    SC A JOIN SC B 
WHERE
    A.CID = '01' AND B.CID = '02' AND A.SID = B.SID AND A.SCORE > B.SCORE 
 

SELECT
    A.SID
FROM
    SC A,SC B 
WHERE
    A.CID = '01' AND B.CID = '02' AND A.SID = B.SID AND A.SCORE > B.SCORE 
 

 解法二：聚合+case when

SELECT
    T.NAME
FROM 
    (SELECT
        NAME,
        MAX(
            CASE WHEN CID='01' THEN SCORE ELSE NULL END
        ) AS C1_SCORE,
        MAX(
            CASE WHEN CID='02' THEN SCORE ELSE NULL END
        ) AS C2_SCORE
    FROM
        SC
    GROUP BY
        NAME) AS T
WHERE
    T.C1_SCORE > T.C2_SCORE

这种方法应该也行，就是先把不同行的转换成同一行的，然后同一行的不同列比较就行了

解法三：两个子查询 + 连接

SELECT 
    A.SID
FROM
    (
        SELECT
            SID,SCORE
        FROM
            SC
        WHERE
            CID = '01'
    ) AS a JOIN 
    (
        SELECT
            SID,SCORE
        FROM
            SC
        WHERE
            CID = '02'
    ) AS B
    ON A.SID = B.SID
WHERE
    A.SCORE > B.SCORE
    

 

2.  解法思想是自连接

 

3、查询同时存在" 01 “课程和” 02 "课程的学生情况（子查询一般可用join来替代）

 

4、

 

5、

 

6、体育馆人流量

解答思路一：三天是连续的且三天的人流量都大于100，输出第一天，第二天，第三天（UNION）

解答思路二：以a为锚点，如果要输出a，有可能三种情况

① a 是第一天

② a 是第二天

③ a 是第三天