[TOC]

Mysql基础查询

掌握Mysql查询的基本用法

基本语法

示例

SELECT * FROM table_name

与

SELECT column1,column2,... FROM table_name

小结：

• *号为通配符，表示所有列（对应表的所有字段）
• column1，column2：要查询的字段名
• table_name：要查询的表名称
• SQL 对大小写不敏感：SELECT 与 select 是相同的，FROM 与 from 是相同的，其它也一样

去重查询

示例

SELECT DISTINCT column1,colum2,... FROM table_name

小结

• distinct：去除column列重复的记录，选取列中唯一不同的值

字段别名

示例

SELECT column1 as c1 FROM table_name

小结

• 字段名 as 别名，为字段名提供临时名称，仅查询时可用

where子句

示例

SELECT * FROM table_name where condition

小结

• 字段别名不能在where中使用
• where是跟在from数据源后，对数据进行条件condition筛选匹配的
• where是在磁盘读取后，进入内存前进行筛选的，筛选过后合适的的才会进入到内存

比较运算查询

• >（大于）、<（小于）、=（等于）、>=（大于等于）、<=（小于等于）、<>或者!=（不等于）

示例

SELECT * FROM Employee WHERE Age = 33;

SELECT * FROM Employee WHERE Age <> 33;

SELECT * FROM Employee WHERE Age != 33;

SELECT * FROM Employee WHERE Age > 30;

SELECT * FROM Employee WHERE Age >= 30;

SELECT * FROM Employee WHERE Age < 30;

SELECT * FROM Employee WHERE Age <= 30;

逻辑运算查询

• and（与）、or（或）、not（非）

示例

SELECT * FROM Employee WHERE Age >= 40 AND Salary > 20000;

SELECT * FROM Employee WHERE Age >= 40 AND Salary > 20000 AND Gender = '男';

SELECT * FROM Employee WHERE Age >= 40 OR Salary > 20000;

SELECT * FROM Employee WHERE NOT ( Age >= 40 AND Salary > 20000 );

范围查询

• in（数据1,数据2,...数据N）：在列举的数据中
• not in：不在列举的数据中
• between A and B：A和B之间（A小于B），包括A和B本身（数值比较）；一种>= A and <= B的便捷写法

示例

SELECT * FROM Employee WHERE Age IN ( 32, 33, 34 );

SELECT * FROM Employee WHERE JobPosition NOT IN ( '开发人员', '测试' );

SELECT * FROM Employee WHERE Age BETWEEN 32 AND 34;

空值查询

• is null（检查列是否为空）、is not null（检查列是否不为空）

示例

SELECT * FROM Employee WHERE Age IS NULL;

SELECT * FROM Employee WHERE Age IS NOT NULL;

模糊查询

SELECT 字段 FROM 表 WHERE 某某字段 LIKE 条件

• 通配符经常与like一起使用，在like后条件内

  • %：匹配当前位置（往后）任意数量（0个、1个或多个）任意字符（ab%：ab开头+任意数量任意字符，匹配abc，ab1，abcd）；有些情况下若是匹配中文，请使用两个百分号（%%）表示

  示例

  -- 匹配Z开头的任意字符
  SELECT * FROM Employee WHERE ContactName LIKE 'Z%';
  
  -- 配置名称为朱
  SELECT * FROM Employee WHERE ContactName LIKE '%朱%';

  • _：单下划线，匹配对应位置的一个任意字符（ab_：ab开头+一个字符，匹配的是abc，ab1，但是不匹配abcd）

  示例

  -- 匹配Z开头+任意一个字符
  SELECT * FROM Employee WHERE ContactName LIKE 'Z_';

  • []：中括号，匹配括号内的任意单个字符

  示例

  -- 匹配8或者6
  SELECT telephone FROM student WHERE telephone LIKE'[8,6]%';

  • [^]：表示不在括号内的任意字符，不匹配[]内任意字符

  示例

  SELECT * FROM myTable WHERE myColumn LIKE '[^ ]%';

exists操作符

• 用于检查子查询中是否返回至少一行数据的操作符，返回值为TRUE和FALSE

示例

-- 查询有订单的客户
SELECT customer_name
FROM customers c
WHERE EXISTS (
    SELECT 1
    FROM orders o
    WHERE o.customer_id = c.id
);

all和any操作符

• ALL和ANY都是用于判断某个值与子查询返回的所有/任意值直接的关系
• ALL：用于判断主查询的值是否满足子查询返回的所有值的比较条件（与子查询返回的所有值进行比较）
• ANY：用于判断主查询的值是否满足子查询返回的所有值的比较条件（与子查询返回的任意一个值进行比较）

示例

-- 查询薪资高于部门所有员工平均薪资的员工
SELECT employee_name, salary
FROM employees
WHERE salary > ALL (
    SELECT AVG(salary) 
    FROM employees 
    GROUP BY department_id
);

-- 查询薪资高于任意一个部门平均薪资的员工
SELECT employee_name, salary
FROM employees
WHERE salary > ANY (
    SELECT AVG(salary) 
    FROM employees 
    GROUP BY department_id
);

group by 子句

示例

SElECT count(*),class_name FROM t_40 group by class_name;

SELECT JobPosition, AVG(Salary) FROM Employee WHERE EmployeeID <= 10 GROUP BY JobPosition;

SELECT * FROM stu ORDER BY age DESC;

小结

• group by分组统计，根据某个字段将所有的结果分类，并进行数据统计分析
• group by 子句一定是出现在where子句之后（where可以没有）
• 分组统计可进行统计细分：先分大组，然后大组分小组

• group by 子句通常与聚合函数（COUNT、MAX、MIN、SUM、AVG）一起使用

  • count()：求对应分组的记录数量
    • count(字段名)：统计某个字段值的数量（NULL不统计）
    • count(*)：统计整个记录的数量（较多）
  • sum()：求对应分组中某个字段是和
  • max()/min()：求对应分组中某个字段的最大/最小值
  • avg()：求对应分组中某个字段的平均值

• group by 子句可以根据分组字段进行升序或者降序显示数据

  • group by 字段名 ASC：升序排序（默认）
  • group by 字段名 DESC：降序排序

having子句

示例

SELECT department, AVG(salary) as avg_salary
FROM employee
GROUP BY department
HAVING AVG(salary) > 50000;

小结

• having子句本身是针对分组统计结果进行条件筛选的
• 类似where，但WHERE是针对表数据进行过滤，HAVING是针对分组后数据进行过滤
• 能用where解决问题的地方绝不使用having
• 使用HAVING子句时，需要确保它只用于已经使用GROUP BY子句的查询中，并且其中的条件表达式只包含分组后的列和聚合函数，以及比较运算符

order by子句

示例

SELECT name, age FROM person ORDER BY age DESC;
SELECT employee_id, first_name, last_name, salary, department_id
FROM employees
ORDER BY department_id ASC, salary DESC;

小结

• order by子句在having子句字后（如果同时存在）,用于对查询结果进行排序
• 排序分为升序和降序：默认是升序
  • order by 字段 ASC：升序
  • order by 字段 DESC：降序
• 多字段排序：如果指定了多个列，那么结果集将按照第一个列的值进行排序，如果第一个列的值相同，则按照第二个列的值进行排序，依此类推

limit子句

示例

-- 获取前5行记录
SELECT * FROM emp LIMIT 5;

-- 跳过前3行记录，取5条数量记录，等同于获取第4-8条记录
SELECT * FROM emp LIMIT 3, 5;

小结

• 用来限定查询结果的起始行，以及总行数。
• limit子句必须在order by子句之后（如果同时存在）
• limit限制数量的方式有两种
  • limit 数量：限制获取的数量（不保证一定能获取到指定数量）
  • limit 起始位置,数量：限制数据获取的位置以及获取的总数量（分页）

查询语句执行顺序

示例

SELECT
Age, ROUND(AVG(Salary),0) AS AVG_Salary
FROM Employee
WHERE JobPosition = '开发人员' GROUP BY Age
HAVING AVG_Salary >= 21000
ORDER BY Age DESC
LIMIT 3;

语句执行顺序

• FROM > WHERE >【GROUP BY > 聚合函数 > HAVING】> SELECT > DISTINCT > ORDER BY > LIMIT

1. 获取表数据

SELECT * FROM Employee; -- 通过WHERE条件进行过滤
SELECT * FROM Employee WHERE JobPosition = '开发人员';

1. 针对过滤后数据进行分组，执行聚合函数

SELECT ROUND(AVG(Salary),0) AS AVG_Salary 
FROM Employee 
WHERE JobPosition = '开发人员' 
GROUP BY Age;

1. 针对分组后数据，进行HAVING过滤

SELECT ROUND(AVG(Salary),0) AS AVG_Salary 
FROM Employee 
WHERE JobPosition = '开发人员' 
GROUP BY Age 
HAVING AVG_Salary >= 21000;

1. 过滤分组数据后，进行查询，再排序

SELECT Age, ROUND(AVG(Salary),0) AS AVG_Salary 
FROM Employee 
WHERE JobPosition = '开发人员' 
GROUP BY Age 
HAVING AVG_Salary >= 21000 
ORDER BY Age;

1. 针对排序结果，限制展示行数

SELECT Age, ROUND(AVG(Salary),0) AS AVG_Salary 
FROM Employee 
WHERE JobPosition = '开发人员' 
GROUP BY Age 
HAVING AVG_Salary >= 21000 
ORDER BY Age DESC 
LIMIT 3;