MySQL SQL Statement

官方测试数据：https://dev.mysql.com/doc/index-other.html & https://dev.mysql.com/doc/employee/en/sakila-structure.html

DQL：数据查询语言

https://dev.mysql.com/doc/refman/8.3/en/select.html

单表查询

-- 常用格式
SELECT selection_list /*要查询的列名称*/
  FROM table_list /*要查询的表名称*/
  WHERE condition /*行条件*/
  GROUP BY grouping_columns /*对结果分组*/
  HAVING condition /*分组后的行条件*/
  ORDER BY sorting_columns /*对结果排序*/
  LIMIT offset_start, row_count /*结果限定*/

Expressions 和 Functions and Operators

-- 逻辑运算符：AND、OR、NOT
WHERE NOT t_man.Mage > 30

-- 比较运算符：=、<>、!=、>、>=、!>、<、<=、!<

-- LIKE，用于模糊查询
-- % 后面可以跟零个或多个字符
-- _ 匹配任意单个字符
-- [ ] 查询一定范围内的单个字符，包括两端数据
WHERE t_man.Mname LIKE '[周李]%'
-- [^] [!]: 表示不在一定范围内的单个字符，包括两端数据

-- BETWEEN
between xx and xx
WHERE t_man.Mage BETWEEN 30 AND 31 (等同于 t_man.Mage >= 30 AND t_man.Mage <= 31)
not between xx and xx

-- is (not) null，判空不能使用 =。例如 2 != null 为 false
WHERE t_man.Mage is not null

-- in 多条件
WHERE t_man.Mage IN (30,31)

-- ALL SOME ANY
-- Some 和 any 等效，all 是大于最大者，any 是小于最小者
WHERE t_man.Mage > ALL(SELECT t_man.Mage FROM t_man WHERE t_man.Mname LIKE '张%')

-- exists 和 no exists
WHERE exists (select * from t_man where t_man.Mid = 8001)

-- Group by 分组
SELECT AVG(t_man.Mage) FROM t_man GROUP BY t_man.Msex

-- Having 分组后条件
SELECT AVG(t_man.Mage) AS mk,t_man.Msex FROM t_man GROUP BY t_man.Msex HAVING mk > 30

-- ORDER BY 排序 ASC,DESC
SELECT * FROM t_man ORDER BY t_man.Mid ASC

-- DISTINCT 去重
SELECT DISTINCT(t_man.Msex) FROM t_man

-- LIMIT 分页（显示第一行数据）
SELECT * FROM t_man LIMIT 0,1

去除表中重复数据，保留 id 最大的一条

CREATE TABLE `person` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `email` varchar(255) COLLATE utf8mb4_bin DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;

DELETE FROM person WHERE id NOT IN (
    SELECT intermediateTable.id FROM (
        SELECT max( id ) AS id, email FROM person GROUP BY email -- 按照 email 分组，取出分组中 id 最大的一条
    ) AS intermediateTable -- 生成中间临时表，MySQL 修改或者删除数据时不能以当前表作为条件
)

-- 错误写法
-- DELETE FROM person  WHERE id NOT IN (
--     SELECT max( id ) AS id FROM person GROUP BY email
-- )

判断：https://dev.mysql.com/doc/refman/8.3/en/case.html

SELECT 
    CASE id
       WHEN 1 THEN '大'
       WHEN 2 THEN '中'
       WHEN 3 THEN '小'
       ELSE '其它'
    END AS type
FROM weixin_case_info

Window Functions(窗口函数)：https://dev.mysql.com/doc/refman/8.3/en/window-functions.html

select (
  select distinct salary from (
    select salary, dense_rank() over(order by salary desc) r
    from Employee
  ) t where t.r = 2 -- 查第二名
) SecondHighestSalary -- 空转 NULL

行转列

select
  product_id,
  sum(if(store='store1', price, NULL)) store1,
  sum(if(store='store2', price, NULL)) store2,
  sum(if(store='store3', price, NULL)) store3
from (
  select product_id, 'store1' store, store1 price from Products where store1 is not null
  union all
  select product_id, 'store2', store2 price from Products where store2 is not null
  union all
  select product_id, 'store3', store3 price from Products where store3 is not null
) t group by product_id

列转行

-- union all 只需要第一行有别名
select product_id, 'store1' store, store1 price from Products where store1 is not null
union all
select product_id, 'store2', store2 from Products where store2 is not null
union all
select product_id, 'store3', store3 from Products where store3 is not null

多表查询

-- 常用格式
SELECT selection_list /*要查询的列名称*/
  FROM table_list /*要查询的表名称*/
  JOIN table_list /*要查询的表名称*/
  ON condition /*连接条件*/
  WHERE condition /*行条件*/
  GROUP BY grouping_columns /*对结果分组*/
  HAVING condition /*分组后的行条件*/
  ORDER BY sorting_columns /*对结果分组*/
  LIMIT offset_start, row_count /*结果限定*/

例子

-- 交叉连接（Cross Join），没有链接条件的表查询会出现笛卡儿积
SELECT * FROM t_man, t_dept
SELECT * FROM t_man JOIN t_dept
SELECT * FROM t_man INNER JOIN t_dept

-- 内连接（inner Join 或 Join），两表中都有才显示，即两表的交集
SELECT * FROM t_man, t_dept WHERE t_man.Mid = t_dept.Mid
SELECT * FROM t_man JOIN t_dept ON t_man.Mid = t_dept.Mid
SELECT * FROM t_man INNER JOIN t_dept ON t_man.Mid = t_dept.Mid

-- 左外连接（Left outer Join），以左边表为主，左表全部显示，没有对应的就显示空，即左并集
SELECT * FROM t_man LEFT JOIN t_dept ON t_man.Mid = t_dept.Mid

-- 右外连接（Right outer Join），与左外连接相反
SELECT * FROM t_man RIGHT JOIN t_dept ON t_man.Mid = t_dept.Mid

-- 全连接（Full outer Join），默认不支持，但也其他方式可以实现。
SELECT * FROM t_man LEFT JOIN t_dept ON t_man.Mid = t_dept.Mid
UNION
SELECT * FROM t_man RIGHT JOIN t_dept ON t_man.Mid = t_dept.Mid

-- UNION ALL 与 UNION 区别是允许重复

-- 自然连接 NATURAL，MySQL 自动把表中相同的字段做为连接条件
SELECT * FROM t_man NATURAL JOIN t_dept

中间表查询

https://dev.mysql.com/doc/refman/8.3/en/with.html

WITH
temp1 AS (
  SELECT selection_list FROM table_list
),
temp2 AS (
  SELECT selection_list FROM temp1
)
SELECT selection_list FROM temp2

 

DML：数据操作语言，主要有三种形式

插入：INSERT

https://www.liaoxuefeng.com/wiki/1177760294764384/1246617682185952

CREATE TABLE `flume_meta` (
  `source_tab` varchar(255) COLLATE utf8_bin DEFAULT NULL UNIQUE,
  `current_index` bigint(255) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;
-- 向数据库中插入一条记录，若该数据的主键值（UNIQUE KEY）已经在表中存在，则执行后面的 UPDATE 操作。否则执行前面的 INSERT 操作
INSERT INTO `flume_meta` VALUES('user',1)
ON DUPLICATE KEY
UPDATE source_tab='user',current_index=5;

更新：UPDATE

删除：DELETE

 

DDL：数据定义语言，用来创建数据库中的各种对象(表、视图、索引、同义词、聚簇等)

创建：CREATE TABLE / VIEW / INDEX / SYN / CLUSTER

修改：ALTER TABLE

删除：DROP TABLE

清空：TRUNCATE TABLE

DDL 操作是隐性提交的，不能 rollback

 

DCL：数据控制语言，用来授予或回收访问数据库的某种特权，并控制数据库操纵事务发生的时间及效果，对数据库实行监视等

GRANT：授权。

ROLLBACK [WORK] TO [SAVEPOINT]：回退到某一点。

 

TCL：事务控制语言

 

---

https://dev.mysql.com/doc/refman/8.3/en/sql-statements.html

https://www.ruanyifeng.com/blog/2019/01/table-join.html

https://dataschool.com/how-to-teach-people-sql/sql-join-types-explained-visually