系统学习Mysql的SQL语法

1.select语句

关键字执行顺序：

1.from 2.where 3.group by 4.select 5.having 6.order by 7.limit

SQL 语句执行顺序如下：

1. FROM: 指定要查询的表或子查询，可以包含 JOIN、WHERE 子句过滤等。

2. WHERE: 对 FROM 子句指定的表或子查询进行限制和过滤。

3. GROUP BY: 将查询结果按照指定列进行分组，并可以对每个分组计算聚合函数。

4. SELECT: 选择要查询的列，并应用聚合函数，如果有的话。

5. HAVING: 对 GROUP BY 分组后的结果进行过滤。

6. ORDER BY: 根据指定的一列或多列对查询结果进行排序。

7. LIMIT: 指定查询结果的最大行数。

需要注意的是，该顺序并没有严格的规定，不同的 SQL 数据库可能会有不同的实现，但是大多数情况下遵循这个顺序。此外，有些 SQL 语句中可能并不需要用到所有这些子句，比如没有聚合函数时就不需要 GROUP BY 和 HAVING 子句。

2.where和group by

select ...from...where...group by...having...order by...limit

where后面添加查询条件

group by用于分组

3.group by+聚集函数（count)

建立测试数据：

执行SQL效果：

不使用group by

 4.count + distinct

修改数据库为：

执行SQL：

select count(distinct name),classid from stu group by classid

select count(distinct name),classid from stu

再修改数据库：name为NULL

执行SQL：NULL值在distinct中不参加作用

5.聚集函数

包含：count（计数）、sum（求和）、max（最大值）、min（最小值）、avg（平均值）、group-concat(将每个分组内的多个值连接起来，形成一个包含所有值的字符串）

函数基本语法：

SELECT column1, GROUP_CONCAT(column2)
FROM table
GROUP BY column1;
其中，column1 是用于分组的列名，column2 是要合并的列名，table 是要查询的表名。

以下是一个示例：

假设有一个名为 orders 的表，包含订单号（order_id）和商品名称（product_name）

SELECT order_id, GROUP_CONCAT(product_name) AS products
FROM orders
GROUP BY order_id;
执行以上查询后，会按照订单号进行分组，并将每个订单中的商品名称以逗号分隔的形式合并为一个字符串，存储在名为 products 的列中。

6.建库、建表、插入语句

创建数据库：create database XXX;

建表前需要先选择刚才新建的数据库XXX：

use XXX;

create table stu (id int primary key, name char(10), classid int) default charset=utf8mb4;

插入数据：

INSERT INTO stu (id, name, classid) VALUES(1,‘贾南风’,20);

INSERT INTO stu (id, name, classid) VALUES(2, ‘冯仙’, 20);

7.having、order by、limit

数据库数据如下：

执行SQL：

select * from stu group by classid

结果：

having过滤作用：发生在select 、group by等作用后面

执行SQL：

select count(1) as n,classid from stu group by classid having n >1

 结果如下：

修改执行SQL：

select count(1) as n,classid from stu group by classid having n >2

结果如下：

order by 排序：作用域也在select之后

原始SQL执行效果：

select count(1) as n,classid from stu group by classid 

修改SQL：

select count(1) as n,classid from stu group by classid order by n

 

 order by 多个字段排序

原则是按第一个字段排序后，相同行内部，再按照第二个字段进行排序

 limit:设置取几条结果，可以设置从第几行开始取几条结果

修改SQL：

8.1.Case When 

和代码关系式对比理解

预期实现目标：

编写SQL如下实现

select id,name, 
  case 
    when classid =1 then 2
    when classid=2 then 1
    else classid 
  end 
from stu

8.2.Case When + 聚集函数

目标：将紫色数据整合成绿色数据输出

中间理解过程：

通过三个case when then else制造三列临时数据，然后用max和 as生成需要的数据

最终运行SQL：

select name,
max(CASE WHEN stage='基' THEN score ELSE NULL END) as '基',
max(CASE WHEN stage='爬' THEN score ELSE NULL END) as '爬',
max(CASE WHEN stage='SQL' THEN score ELSE NULL END) as 'SQL'
FROM scores GROUP BY name;

9.1.引入join

整理数据：

使用带join的SQL语句：

select * from stu join class

调整SQL语句的表顺序：

select * from class join stu

9.2.join和on条件

在SQL语句中增加一个on条件

select * from stu join class on stu.classid = class.id

得到结果：

9.3.left join和right join

left join和join的区别：

1.left join必须有on条件

2.会检查左边表的数据是否包含在新生成的表中。如果在新表中，则和join效果一样。如果不在新表中，则用NULL和不在的数据组成新行，加入新表中。

使用SQL语句如下

select * from stu left join class on stu.classid = class.id

 输出结果：

左边表stu本来不在新表中的两条记录，通过补NULL，进入了新表的结果集中。

left join和right join关系： A left join B = B right join A

9.4.多表的连接情况

表a和表b连接完，再和表c连接，再和表d连接。

select
    ...
from
    表a
join
    表b
on
    表a和表b的连接条件
join
    表c
on
    表a和表c的连接条件
right join
    表d
on
    表d和表a的连接条件

9.5.left join应用案例介绍

测试数据

学生表：

班级表：

需求:找出学生表中没有班级的学生

select * from stu left join class on classid = class.id where class.id is NULL

输出结果：

9.6.join应用案例介绍 

测试数据：

需求：编写SQL获取收入比自己经理高的员工姓名

select e1.name from employee as e1 join employee as e2 on e1.managerid = e2.id where e1.salary > e2.salary

执行结果：

10.where里面的子查询

原始数据：

class表：

stu表：

需求：查询狂狮管理班级的学生姓名

select name from stu where classid = (select id from class where manager = '狂狮')

结果：

注意点：子查询返回结果超过1条如何处理

（select id from class where manager = '狂狮')结果不止一条，会出现报错：Subquery returns more than 1 row

解决办法(使用in)：将语句修改成

select name from stu where classid in (select id from class where manager = '狂狮')

 11.select里面的子查询

数据同10

需求：查询学生id，同时查询狂狮的班级号

select id, (select id from class where manager = '狂狮') as classid from stu;

12.from里面的子查询

数据同10、11

需求：使用from子查询，实现查出manager = '狂狮'的学生姓名

select stu.name from stu join (select * from class where manager = '狂狮') As C on stu.classid = C.id

注意：from里面子查询结果一定要用AS生成别名

结果：

 13.工作中子查询和join对比

在不想使用子查询的时候，有些情况可以使用join实现同等效果，以下为我实际项目的例子

如下，子查询语句

SELECT id 
FROM dev 
WHERE id_level = (SELECT id_level FROM dev WHERE id = '11-22-33-44-55-11');
 

同样效果的join使用

SELECT d1.id 
FROM dev d1
JOIN dev d2 ON d1.id_level = d2.id_level
WHERE d2.id = '11-22-33-44-55-11';

14.1.半连接1（semi-join)

在子查询内，有访问父查询中表的信息的，就叫做半连接。

举例-测试数据：

在stu表中，找到每个classid，各自对应的最大的id的那行信息

select * from stu s1 where (id = (select max(id) from stu s2
where classid = s1.classid))

 输出结果：

14.2.半连接2

测试数据：

需求：找到每个班级学生的最大学号对应的学生信息

1.不使用半连接如何做：

select * from stu 
where id in (select max(id)
from stu group by classid)

结果：

2.使用半连接如何做：

select * from stu s1 where id = (select max(id) from stu s2
where classid = s1.classid)

select * from stu s1 
where s1.id = (select max(id) 
from stu s2 where s2.classid = s1.classid)
​

from stu s1是先执行的，然后where 后面是一条条的执行， 

from stu s1取出来5行数据，第一行数据  1    A    1

半连接替换S1相关数据，SQL语句变成

select * from stu s1

where 1 = (select max(id)

from stu s2 where s2.classid = 1)

变成select * from stu s1

where 1 = 2

所以第一行数据不满足