MySQL DQL(查询语言)

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

-- 按条件查询指定列
SELECT t_man.Mname, t_man.Mage FROM t_man WHERE t_man.Mage > 30

-- --------------查询条件----------------------------

-- 1.逻辑运算符
-- NOT : 取反
WHERE NOT t_man.Mage > 30
-- AND : 逻辑与
WHERE t_man.Mage > 30 AND t_man.Mname LIKE '_'
-- OR : 逻辑或
WHERE t_man.Mage > 30 OR t_man.Mname LIKE '_'

-- 2.比较运算符
=、<>、!=、>、>=、!>、<、<=、!<

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

-- 4.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

-- 5.is (not) null
-- 在 where 子句中，需要用 is (not) null 判断空值，不能使用 = 判断空值
WHERE t_man.Mage is not null

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

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

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

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

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

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

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

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

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

SELECT
    b.agency_name `name`,
    IFNULL(ROUND(a.a / b.b, 2), 0) `value`,
    IFNULL(ROUND(1 - a.a / b.b, 2), 0) complementary
FROM
    (SELECT COUNT(*) b, agency_name FROM weixin_case_info GROUP BY agency_name) AS b
LEFT JOIN
    (SELECT COUNT(*) a, agency_name FROM weixin_case_info WHERE process_status = 2 GROUP BY agency_name) AS a
ON a.agency_name = b.agency_name 
ORDER BY `value` DESC LIMIT 3

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

-- （1）先排序在分组，并且在子查询中使用 limit，其中 18446744073709551615 是无符号 64 位整数的最大值，即 2 的 64 次方减 1
select * from (
    select * from t1 order by a2 desc limit 18446744073709551615
) t
group by t.a1

-- （2）group by + having
select * from t1 u group by a1, a2
having a2 = (
    select max(a2) from t1 where a1 = u.a1
)

-- （3）inner join
select u.* from t1 u
inner join (
    select a1, max(a2) as max_a2 from t1 group by a1
) v on u.a1 = v.a1 and u.a2 = v.max_a2

-- 格式
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

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