基础格式

  1. SELECT 字段名
  2. FROM 表名
  3. ;
  4. # 从 表名 表中 无条件 查询 所有字段

运算符

运算符 和其他语言大致相同 常见有

  1. = # 等于 不同于java语言的=在java语言中=代表赋值而==代表等于 即 sql的=相当于java的==
  3. > # 大于
  5. < # 小于
  7. <= # 小于等于
  9. >= # 大于等于
  11. != 或 <> # 不等于
  13. <=> # 安全等于 ： 在sql中使用=判断两值相等时会出现 `NULL`值 的情况 如 : 
  14. SELECT 1 = NULL as l1, 1 <=> NULL as l2, NULL = NULL as l3, NULL <=> NULL as l4;
  15. # 结果: l1->null , l2->0 , l3->null , l4->1   即  当 null 值参与到 = 计算中时 结果必定为 null
  16. # 而 <=> 解决了对应 null 值不适用的问题 即 : 字段<=>NULL 相当于 字段 IS NULL , NOT 字段<=>NULL
  17. # 相当于 字段 IS NOT NULL
  19. # 注意 ---> NULL 参与 = > < <= >=  <> 的计算都返回 null

运算相关 关键字

  1. LIKE 相似 # 字段 LIKE '%A%' 将查找出 字段中包含A的数据  % 代表多个字符 ， 在字符左侧的%意味着字符左侧无论有多少个字符只要出现了这个字符即可成功匹配反之亦然
  2. # 字段 LIKE '_A%' 将查找出字段中第二字符为A的数据       _ 代表一个字符 , 在字符左侧_意味着字符左侧有一个字符后出现了这个字符即可成功匹配反之亦然
  4. \ 转义 # \_ -> 字符_  , \% -> 字符%
  6. REGEXP 正则 # 字段 REGEXP '正则' 表示  判断字段是否符合正则
  8. BETWEEN A AND B # 在 A 和 B 之间 ， 
  9. #  字段 BETWEEN 5000 AND 12000;  即  字段 >= 5000 AND 字段 <= 12000
  11. IN () # 在()内的值之中  例如 : 
  12. # 字段 IN (1,2,3,4) 即 字段值 在 1,2,3,4 中可以找到 任意一个值与之相等 即可

逻辑运算符 

  1. and # 逻辑 和、与
  3. or # 逻辑 或
  5. ! 或 not # 逻辑非
  7. xor # 逻辑异或 《俩边值不同则为true》  例如 :
  8. SELECT TRUE XOR FALSE  AS txf, -> TRUE
  9.        TRUE XOR TRUE   AS txt, -> FALSE
  10.        FALSE XOR FALSE AS fxf, -> FALSE
  11.        1 XOR 0         AS 1x0; -> TRUE

排序，分页

  1. ORDER BY 字段 # 排序 根据字段 进行排序 可指定排序顺序
  2. # ORDER BY A ASC, B DESC; 即 根据 字段A正序 后 根据字段B倒叙
  3. # ASC正序 默认可不写 , DESC 倒叙
  5. LIMIT N1,N2;  # 截取一定数量的数据 
  6. # LIMIT 1,10  即 从数据下标 1 往后取 10 条  
  7. # ---> 注意 数据下标是从 0 开始的
  8. # 分页时 当前页:pageNo , 页面大小:pageSize    即 LIMIT (pageNo - 1) * pageSize, pageSize;
  9. # 在MySQL 8 之后 也可使用 LIMIT 10 OFFSET 1;来分页 相当于 LIMIT 1, 10;

连接查询

  1. # sql92
  2. sql92 左连接在 右条件后加(+) 反之相反 不写 则默认为 内连接 <注意：mysql并不支持(+)的写法>
  3. SELECT e.employee_id,e.manager_id,e.last_name, e2.employee_id AS mid,e2.last_name AS  mname
  4. FROM employees AS e,employees AS  e2
  5. WHERE e.manager_id = e2.employee_id(+)
  6. ;
  8. # sql99
  9. 使用 XXXX JOIN
  10. SELECT e.employee_id, e.manager_id, e.last_name, e2.employee_id AS mid,
  11.              e2.last_name AS mname
  12. FROM employees AS e
  13.          LEFT JOIN employees AS e2 ON e.manager_id = e2.employee_id
  14. ;
  15. # 关键字 LEFT JOIN , RIGHT JOIN, INNER JOIN 或 JOIN , FULL JOIN  相当于左连接的值UNION ALL右链接的值 
  16. #       左连接       右连接              内连接          全连接 MySQL 不支持全连接
  17. # 表1 XXXX JOIN 表2 ON 表1.A = 表2.B ; 表1 连接 表2 条件是 表1.A = 表2.B

各个连接的情况如下（笛卡尔积）:

MySQL Query 基础 - 图2

  1. # 不常用的链接写法
  3. # *** 自然连接  当链接的两张表中的 字段名和类型完全相同时 自然连接将自动使用此字段连接查询 
  4. SELECT e.last_name, e.department_id, d.department_id AS did, d.department_name, d.location_id
  5. FROM employees e
  6.          NATURAL LEFT JOIN departments d
  7. ;
  8. -- 相当于
  9. SELECT e.last_name, e.department_id, d.department_id AS did, d.department_name, d.location_id
  10. FROM employees e
  11.          LEFT JOIN departments d ON e.department_id = d.department_id 
  12.                                                              AND e.manager_id = d.manager_id
  13. ;
  16. # *** 连接字段代替
  17. SELECT e.last_name, e.department_id, d.department_id AS did, d.department_name, d.location_id
  18. FROM employees e
  19.          LEFT JOIN departments d ON e.department_id = d.department_id 
  20.                                                              AND e.manager_id = d.manager_id
  21. ;-- 相当于
  22. SELECT e.last_name, e.department_id, d.department_id AS did, d.department_name, d.location_id
  23. FROM employees e
  24.          LEFT JOIN departments d USING (department_id, manager_id)
  25. ;

特别注意：当链接的多张表中出现了相同的字段时必须指明select列表中的重名字段来自哪一张表，否则将报错

单行函数

函数过多仅列举一点点进行举例:

  1. NOW() 获取当前时间
  2. YEAR() 获取时间中的年份 等 ....
  3. # ***
  4. SELECT hire_date,
  5.        DATEDIFF(NOW(), hire_date)                  AS day1,
  6.        # DATEDIFF() 返回 expr1 - expr2，表示为从一个日期到另一个日期的天数。 expr1 和 expr2 是日期或日期和时间表达式。 计算中仅使用值的日期部分。
  7.        TO_DAYS(NOW()) - TO_DAYS(hire_date)         AS day2,
  8.        # 给定日期日期，返回天数（自 0000-00-00 00:00:00 年以来的天数）
  9.        YEAR(NOW()),
  10.        YEAR(hire_date),
  11.        YEAR(NOW()) - YEAR(hire_date)               AS year1,
  12.        DATEDIFF(NOW(), hire_date) / 365            AS year2,
  13.        (TO_DAYS(NOW()) - TO_DAYS(hire_date)) / 365 AS year3
  15. FROM employees
  16. ORDER BY year1 DESC
  17. ;
  19. ### 可见 单行函数 可以 嵌套
  21. BENCHMARK(1000000, COUNT(1)) # 使 函数 COUNT(1) 执行 1000000次 用于测试效率

多行函数(聚合)

函数

  1. avg() # 平均数
  3. sum() # 总合
  5. min() # 最小
  7. max() # 最大
  9. count() # 统计个数
  12. # ---> *** avg sum min max count 忽略null  即  不统计 不求和 不计数 不比较
  13. # 即  avg(commission_pct) = sum(commission_pct) / count(commission_pct)
  14. #     !=  sum(commission_pct) / count(*) = avg(ifnull(commission_pct,0))

分组

  1. GROUP BY 字段  # 根据 字段分组 可多个字段 例如 GROUP BY A,B; 根据A分组后在根据B分组
  3. # 分组函数常常配合 聚合函数使用
  4. # 例如 查询各个部门的员工最低 最高 平均 总 工资
  5. SELECT MAX(salary), MIN(salary), AVG(IFNULL(salary, 0)), SUM(salary)
  6. FROM employees
  7. GROUP BY employees.department_id
  8. ;
  10. # AVG(IFNULL(salary, 0)) 当工资为NULL时 看作0 计工资 并 加入个数统计 否则 将在统计个数时忽略
  11. # null的数据 导致结果偏大
  13. # *** 查询的字段除了聚合函数外 必须出现再group by 后面
  14. # 即 
  15. SELECT employees.department_id,
  16.              MAX(salary)
  17. FROM employees
  18. GROUP BY employees.department_id
  19. ;
  20. # ------<>> 否则 查询的字段若不在分组中会出现问题 MySQL中部分版本可能不会报错 但是在其他库中会报错
  22. # 若要对 聚合值 进行判断过滤 需使用 HAVING  其他值判断不要写在 HAVING 后 会影响效率 
  23. # 因为HAVING的执行是位于WHERE之后的如果把WHERE的过滤条写到了HAVING后面
  24. # 会导致无法提前过滤掉大部分数据导致数据操作行数增大查询速度变慢
  25. SELECT employees.department_id,
  26.              MAX(salary)
  27. FROM employees
  28. WHERE department_id <=> NULL
  29. GROUP BY employees.department_id
  30. HAVING MAX(salary) > 10
  31. ;
  33. # WITH ROLLUP将所有数据作为一组进行计算 
  34. # 会让所有分组结束后多分一组（即多了一行数据）代表整个查询分为一组
  35. SELECT department_id, AVG(salary)
  36. FROM employees
  37. GROUP BY department_id
  38. WITH ROLLUP 
  39. ;

执行顺序

FROM -> ON -> [XXX] JOIN -> WHERE -> GROUP BY -> HAVING => SELECT -> DISTINCT => ORDER BY -> LIMIT

由于MySQL的底层在HAVING前产生了临时表所以可以使用SELECT列表的字段别名 在其他库中是不支持的 不建议在HAVING 后使用别名

在WHERE中也不能使用别名

在 ORDER BY 中可以使用别名

子查询

增加关键字

  1. ANY/SOME(任一 有一个就行) ALL(所有)
  2. EXISTS 存在， 会将父查询中的每一行带入到EXISTS的子查询中验证是否存在，存在则展示否则将剔除

常见子查询例

  2. # 查询 和 员工id = 104 的员工 的 工资 和 jobId 相同的 员工 信息 
  3. SELECT *
  4. FROM employees
  5. WHERE (salary, job_id) = (
  6.     SELECT salary, job_id
  7.     FROM employees
  8.     WHERE employee_id = 104
  9. )
  10. ;
  11. # 查询 与 jobId = IT 的任意一个员工 的工资相等的 员工信息 相当于 IN 
  12. SELECT *
  13. FROM employees
  14. WHERE salary = ANY (
  15.        SELECT salary
  16.         FROM employees
  17.         WHERE job_id = 'IT'
  18. )
  19. ;
  21. # 查询有员工的部门信息
  22. SELECT *
  23. FROM departments
  24. WHERE EXISTS(
  25.               SELECT department_id
  26.               FROM employees
  27.               WHERE departments.id = employees.department_id
  28.           )
  29. ;
  30. # 内查询可以使用外查询的字段
  33. # 查询比自己部门平均工资高的员工信息
  34. SELECT we.*
  35. FROM employees AS we
  36. WHERE we.salary > (
  37.     SELECT AVG(IFNULL(ie.salary, 0))
  38.     FROM employees ie
  39.     WHERE ie.department_id = we.department_id
  40. )
  41. ;

进阶格式

  1. SELECT 子查询/其他字段
  2. FROM 子查询/其他表
  3. XXXX JOIN 子查询/其他表
  4. WHERE
  5. 非聚合函数值普通条件/含有子查询的条件
  6. GROYP BY N个字段
  7. HAVING 聚合函数值条件
  8. ORDER BY N个字段
  9. LIMIT 起始,结束

[

](https://www.qiqimv.com/)