数据查询语言 DQL

基础查询

基础查询语法：select 查询列表 from 表名。

查询列表可以是表中的字段、常量值、表达式、函数。最后查询出的结果是一个虚拟的表格。

• 查询表中的单个字段
  SELECT last_name FROM employees
• 查询表中多个字段
  SELECT last_name,salary,email FROM employees
• 查询表中所有字段
  SELECT * FROM employees
• 查询常量值
  SELECT 100
• 查询表达式
  SELECT 100%98
• 查询函数返回值
  SELECT VERSION()

取别名：我们可以在查询时给表和列来取别名，是为了方便理解方便查看。写法：表名 [AS] 别名和列名 [AS] 别名，没错AS可以省略。我们还可以使用CONCAT()将多列拼接成同一列展示，最后使用别名作为查询结果新列名。

# 取别名，带上AS，别名最好是加上双引号，因为可能是多单词组成
SELECT 100%98 AS "结果";
# 取别名，带上AS，别名最好是加上双引号，因为可能是多单词组成
SELECT last_name AS "姓", first_name AS "名" FROM employees;
# 取别名，省略AS，别名最好是加上双引号，因为可能是多单词组成
SELECT salary "out put" FROM employees;
# CONCAT()搭配别名使用
SELECT CONCAT(last_name,first_name) AS "姓名" FROM employees;

去重：在某些表里，在查询一些字段的值时结果可能会出现重复，那么我们可以使用DISTINCT关键字了。

# 使用DISTINCT关键字进行查询结果去重
SELECT DISTINCT department_id FROM employees;

+加号：+在 MYSQL 中的作用是加号运算

• 如果两个都是数值型，就只加做加法运算 SELECT 10+90 AS "结果"
• 只有其中一个为NULL，那结果就是NULLSELECT NULL+90 AS "结果"
• 如果其中一个是数值型，另一个是数值型字符，那就会将数值型字符转为对应数值，再做加法运算。 SELECT '10'+90 AS "结果"
• 如果其中一个是数值型，另一个是非数值字符型，那就会将非数值字符型转为数值0，再做加法运算。 SELECT 'john'+90 AS "结果"

条件查询

条件查询就是字面意思，按照某些条件进行查询。

写法：select 查询列表 from 表名 where 条件。

普通条件查询

普通条件查询一般是按照“条件”进行查询，它会用到条件运算符：>、<、=、<=、>=、<>、!=。（“不等于”建议使用<>）

一旦“条件”很复杂，满足所有条件、满足其中一个条件、相反条件等，就会用到逻辑运算符：and、or、not、&&、||、!。（建议使用前三个）

# >、<：查询工资大于12000的员工信息
SELECT * FROM employees WHERE salary > 12000;
# <>、!=：查询部门编号不等于90号的员工名和部门编号。`<>`也可以用`!=`，最好用前者
SELECT last_name,department_id FROM employees WHERE department_id<>90;
# AND、&&：查询工资在10000到20000之间的员工名、工资。`AND`也可以用`&&`，最好用前者
SELECT last_name,salary FROM employees WHERE salary>=10000 AND salary<=20000;
# OR：查询部门编号不是在90到110之间，或者工资高于15000的员工信息
SELECT * FROM employees WHERE department_id<90 OR department_id>110 OR salary>15000;
# NOT、AND、OR：同上例
SELECT * FROM employees WHERE NOT(department_id>=90 AND department_id<=110) OR salary>15000;

模糊查询

模糊查询是“条件”很简略，可能是字段值的某一部分，也可能是在一个范围内。它有LIKE、BETWEEN AND、IN、IS NULL、IS NOT NULL几种模糊查询类型。

1. LIKE:一般和通配符搭配使用，通配符%表示任意多个字符，_表示任意单个字符。如果需要区分大小写，可以在LIKE后追加BINARY关键字。如果需要查_本身这个符号，可以使用转译\_（或者用ESCAPE关键字指定某个字符代表\，该字符没有字符原来意义了而是具有了转译功能）。

   # %为任意多个字符：查询员工名中包含字符a的员工信息
   SELECT * FROM employees WHERE last_name LIKE '%a%';
   # _为任意单个字符：查询员工名中第三个字符是e第五个字符是n的员工信息
   SELECT * FROM employees WHERE last_name LIKE '__e_n%';
   # 加上BINARY可区分大小写：查询员工名中第四个字符是大写H的员工信息
   SELECT * FROM employees WHERE last_name LIKE BINARY '___H%';
   # 使用\进行转译：查询员工名中第二个字符是_的员工信息
   SELECT * FROM employees WHERE last_name LIKE '_\_%';
   # 使用ESCAPE指定某字符具有转译功能，该字符本身已经不是它原来的意思了。本例同'_\_%'一样。
   SELECT * FROM employees WHERE last_name LIKE '_a_%' ESCAPE 'a';

2. BETWEEN AND：范围查询，它相当于xx >= 下限 AND xx <= 上限，只是BETWEEN 下限 AND 上限更简洁，BETWEEN AND会包含上下限这两个临界值。

   # BETWEEN AND（包含临界值）：查询工资在10000到20000之间的员工名、工资
   SELECT last_name,salary FROM employees WHERE salary BETWEEN 10000 AND 20000;

3. IN：相当于xx=值1 OR xx=值2 OR xx=值3，用IN的写法会更简洁，IN(值1,值2,值3)，只是“值 123”的类型要一致或者兼容（可隐式转换），还有“值 123”不要使用通配符，因为IN实际是=形式的，不是LIKE所以不能用通配符。

   # IN：查询员工工种编号是IT_PROG或者AD_VP或者ST_MAN的员工信息
   SELECT * FROM employees WHERE job_id IN('IT_PROG','AD_VP','ST_MAN');

4. IS NULL和IS NOT NULL：判断字段值是否为NULL的。

   # IS NULL和IS NOT NULL：查询奖金为空的员工信息
   SELECT * FROM employees WHERE commission_pct IS NULL;

   说到IS NULL，其实它可以用安全等于<=>实现。IS NULL只能用在NULL的场景，而<=>可以适用于NULL也可以适用于具体值场景。

   # <=>是安全等于，用于判断是否等于NULL，也可用于是否等于一个具体值，但是用得很少
   SELECT * FROM employees WHERE commission_pct <=> NULL;
   SELECT * FROM employees WHERE salary <=> 12000;

排序查询

排序：我们有时候需要把查询结果按照一个字段值的升序或降序进行排列展示，那我们可以使用ORDER BY关键字了。ASC 升序（默认），DESC 是降序。ORDER BY支持单字段、多字段、表达式、函数、别名。ORDER BY字句一般放在查询语句的最后面（但LIMIT字句比它更靠后）。

1. 普通排序

   # 使用ORDER BY进行排序，ASC升序（默认），DESC是降序
   SELECT * FROM employees ORDER BY salary;
   # 加上WHERE条件进行排序查询
   SELECT * FROM employees WHERE salary>=12000 ORDER BY salary;

2. 按表达式排序

   # 按表达式排序：按年薪的高低显示员工信息和年薪
   SELECT *, salary*12*(1+IFNULL(commission_pct,0))
   FROM employees
   ORDER BY salary*12*(1+IFNULL(commission_pct,0)) DESC;

3. 按别名排序

   # 按别名排序：按年薪的高低显示员工信息和年薪
   SELECT *, salary*12*(1+IFNULL(commission_pct,0)) 年薪
   FROM employees
   ORDER BY 年薪 DESC;

4. 按函数排序

   # 按函数排序：按姓名的长度显示员工的姓名和工资
   SELECT LENGTH(last_name) 字节长度,last_name,salary
   FROM employees
   ORDER BY LENGTH(last_name) DESC;

5. 按多个字段排序

   # 按多个字段排序：查询员工信息，先按工资降序排序，再按员工编号升序排序
   SELECT * FROM employees ORDER BY salary DESC, employee_id ASC;

常见函数

常见函数一般分为：单行函数和分组函数。单行函数是处理数据用的，分组函数是统计数据用的，所以分组函数还叫统计函数、聚合函数或者组函数。

单行函数

1. 字符函数

   • length()：获取参数值的字节个数。要注意不是字符长度。
   • concat()：拼接字符串。
   • upper()和lower()：前者是将字母都变为大写，后者是将字母都变为小写。
   • substr()或substring()：这两个一样，截取字符串。
   • instr()：查询子串在字符串的索引位置（类似 js 的 indexOf()）。
   • trim()：去掉字符串前后空格。如果使用FORM可以去掉前后指定字符。
   • lpad()和rpad()：用指定字符进行左填充（rpad 是右填充），最后达到指定字符长度。
   • replace()：替换字符串中的指定的字符。

   # length函数：获取参数值的字节个数。
   SELECT LENGTH(last_name) FROM employees;
   # concat函数：拼接字符串。
   SELECT CONCAT(last_name,'_',first_name) FROM employees;
   # upper和lower函数：前者是将字母都变为大写，后者是将字母都变为小写。
   SELECT UPPER(last_name),LOWER(first_name) FROM employees;
   # substr或substring函数：截取字符串，第二个参数是起始点，第三个参数是截取长度
   SELECT last_name, SUBSTR(last_name,1,3) FROM employees; # mysql索引是从1开始的
   # instr函数：查询子串在字符串的索引位置
   SELECT INSTR('akldmfasdfabodfng','mfa');
   # trim函数：去掉前后空格（或指定字符）
   SELECT TRIM('  A  S    ');
   SELECT TRIM('G' FROM 'GGGGGAGGGGSGGGGG');
   # lpad函数：用指定字符进行左填充（rpad是右填充），最后达到指定字符长度
   SELECT LPAD('abc',10,'*'), RPAD('abc',10,'*');
   # replace函数：替换字符串中的指定的字符
   SELECT REPLACE('abcabcabc','abc','def');

2. 数学函数

   • round()：四舍五入，第二个参数是指定保留小数点后的位数。
   • ceil()：向上取整，返回>=该参数的最小整数。
   • floor()：向下取整，返回<=该参数的最大整数。
   • truncate()：截断小数位，会指定截断到小数点后的位数。
   • mod()：取余，mod(a,b)相当于a - a/b*b。

   # round函数：四舍五入，第二个参数是指定保留小数点后的位数
   SELECT ROUND(1.42), ROUND(-1.56), ROUND(1.567,2);
   # ceil函数：向上取整，返回>=该参数的最小整数
   SELECT CEIL(1.00), CEIL(1.02), CEIL(-1.02);
   # floor函数：向下取整，返回<=该参数的最大整数
   SELECT FLOOR(2.00), FLOOR(2.98), FLOOR(-2.98);
   # truncate函数：截断小数位，会指定截断到小数点后的位数
   SELECT TRUNCATE(1.6999, 0), TRUNCATE(1.6999, 2);
   # mod函数：取余，`mod(a,b)`相当于`a - a/b*b`
   SELECT MOD(10,3),MOD(10,-3),MOD(-10,3),MOD(-10,-3);

3. 日期函数

   • now()：返回当前系统日期+时间。
   • curdate()：返回当前系统日期，不包括时间。
   • curtime()：返回当前系统时间，不包括日期。
   • str_to_date()：按照指定格式来解析日期+时间。
   • date_format()：将日期按照指定格式来转换。
   • year()：获取参数值的年部分。
   • month()和monthname()：获取参数值的月部分。
   • day()：获取参数值的日部分（一个月的第几天）。
   • hour：获取参数值的小时部分。
   • minute：获取参数值的分钟部分。
   • second：获取参数值的秒部分。
   • date()：获取参数值的整体日期部分，不包括时间。

   str_to_date()和date_format()中会涉及的“格式”，经常会用到的一些格式符如下表：

   格式符	功能
   %Y	四位年份
   %y	两位年份
   %m	月份（01,02,03...12）
   %c	月份（1,2,3...12）
   %d	月份中的第几天（01,02,03...31）
   %e	月份中的第几天（1,2,3...31）
   %H	小时（24 小时制，00,02,03...23）
   %k	小时（24 小时制，0,1,2,3...23）
   %h	小时（12 小时制，01,02,03...12）
   %l	小时（12 小时制，1,2,3...12）
   %i	分钟（01,02,03...59）
   %s	秒（01,02,03...59）
   %p	AM 或者 PM
   %w	星期几（0=星期日.. 6=星期六）
   %W	星期几（Sunday.. Saturday）

   # now函数返回当前系统日期+时间，curdate只返回日期，curtime只返回时间
   SELECT NOW(),CURDATE(),CURTIME();
   # str_to_date函数：按照指定格式来解析日期+时间
   SELECT STR_TO_DATE('1992-8-15','%Y-%c-%d'),STR_TO_DATE('1992-08-15','%Y-%m-%d');
   # 查询入职日期为1992-4-3的员工信息
   SELECT * FROM employees WHERE hiredate = STR_TO_DATE('4-3 1992','%c-%d %Y');
   # date_format函数：将日期按照指定格式来转换
   SELECT DATE_FORMAT(NOW(), '%y年%c月%e日');
   # 查询有奖金的员工名和入职日期（xx月/xx日 xx年）
   SELECT last_name AS 员工名,DATE_FORMAT(hiredate, '%m月/%d日 %y年') 入职时间
   FROM employees WHERE commission_pct IS NOT NULL;
   # year和day函数：获取年和日
   SELECT YEAR(NOW()),MONTH(NOW()),MONTHNAME(NOW()),
   DAY(NOW()),HOUR(NOW()),MINUTE(NOW()),SECOND(NOW());
   # date获取整体的日期，不包括时间
   SELECT DATE(NOW());

4. 流程控制函数

   • if(expr1,expr2,expr3)：如果 expr1 为 true，就返回 expr2，否则返回 expr3。

   • case()有两种写法：

     # 这种写法的逻辑类似于`switch case`等值判断
     case 要判断的字段或表达式
     when 常量1 then 要显示的值1（在存储过程中是语句）
     when 常量2 then 要显示的值2（在存储过程中是语句）
     ...
     else 要显示的值n（在存储过程中是语句）
     end

     # 这种写法的逻辑类似于`if else if else`这种区间判断
     case
     when 条件1 then 要显示的值1（在存储过程中是语句）
     when 条件2 then 要显示的值2（在存储过程中是语句）
     ...
     else 要显示的值n（在存储过程中是语句）
     end

   • 例子：

     # if(expr1,expr2,expr3)：如果expr1为true，就返回expr2，否则返回expr3
     SELECT IF(10>5,'真的','假的');
     # 使用if给员工奖金加备注
     SELECT last_name, IF(commission_pct IS NULL, '没有奖金', '有奖金') AS 奖金 FROM employees;
     # case函数：类似于js中switch和if else。
     /*
     查询员工的工资，要求
     部门号=30，显示的工资为1.1倍
     部门号=40，显示的工资为1.2倍
     部门号=50，显示的工资为1.3倍
     其他部门，显示的工资为原工资
     */
     SELECT salary AS 原工资, department_id,
     CASE department_id
     WHEN 30 THEN salary*1.1
     WHEN 40 THEN salary*1.2
     WHEN 50 THEN salary*1.3
     ELSE salary
     END AS 新工资 FROM employees;
     /*
     查询员工工资情况
     如果工资>20000，显示A级别
     如果工资>15000，显示B级别
     如果工资>10000，显示C级别
     否则，显示D级别
     */
     SELECT salary 工资,
     CASE
     WHEN salary>20000 THEN 'A'
     WHEN salary>15000 THEN 'B'
     WHEN salary>10000 THEN 'C'
     ELSE 'D'
     END AS 工资级别 FROM employees;

分组函数

分组函数用作统计使用，又称为聚合函数或统计函数或组函数。

分组函数常见的有：sum求和、avg平均值、max最大值、min最小值、count计算个数。

# SUM函数：求和。求所有员工工资总和
SELECT SUM(salary) FROM employees;
# AVG函数：求平均值。求员工工资的平均值
SELECT AVG(salary) FROM employees;
# MIN函数：求最小值。求员工中工资最小的
SELECT MIN(salary) FROM employees;
# MAX函数：求最大值。求员工中工资最大的
SELECT MAX(salary) FROM employees;
# COUNT函数：有多少个非空数据行。求有多少员工有工资信息
SELECT COUNT(salary) FROM employees;

分组函数使用特点：

1. sum、avg一般用于处理数值型；max、min、count可以处理任何类型。

   # SUM函数和AVG函数一般处理数值型，处理其他的虽然不报错但没什么意义
   SELECT SUM(last_name),AVG(last_name) FROM employees;
   SELECT SUM(hiredate),AVG(hiredate) FROM employees;
   # MIN函数、MAX函数和COUNT函数可以处理任何类型
   SELECT MAX(last_name),MIN(last_name) FROM employees;
   SELECT MAX(hiredate),MIN(hiredate) FROM employees;
   # COUNT函数只统计非空的数据
   SELECT COUNT(last_name) FROM employees;
   SELECT COUNT(commission_pct) FROM employees;

2. 分组函数都会忽略 Null 值。

   # SUM函数和AVG函数都会忽略null值。null+任何值都是null。平均值实际除以的是35，不包括null行
   SELECT SUM(commission_pct),AVG(commission_pct),SUM(commission_pct)/35 FROM employees;
   # MAX函数和MIN函数都会忽略null值。不然最大最小值就会有null。
   SELECT MAX(commission_pct),MIN(commission_pct) FROM employees;
   # COUNT函数只统计非空的数据
   SELECT COUNT(last_name) FROM employees;
   SELECT COUNT(commission_pct) FROM employees;

3. 分组函数可以搭配DISTINCT使用，一般是count函数和DISTINCT搭配使用的多。

   # 分组函数可搭配DISTINCT使用，主要是count函数搭配它。查询工资有几种
   SELECT COUNT(DISTINCT salary),COUNT(salary) FROM employees;

4. 统计表的行数，可以用count(*)、count(常量)、count(列名)，其中count(*)用得最多。

   # 用COUNT统计表的函数，有`count(*)`、`count(常量)`、`count(列名)`三种
   SELECT COUNT(employee_id) FROM employees;
   # COUNT(*)表示这一行里只有一列的值不为Null就统计上
   SELECT COUNT(*) FROM employees;
   # COUNT(1)其实是临时加了一列，然后让这一列所有行的值都为1，并统计总行数
   SELECT COUNT(1) FROM employees;

5. 和分组函数一同查询的字段有限制（一般是group by后的字段）

   # 和分组函数一同查询的字段有限制。这个employee_id查出来就没有意义了
   SELECT AVG(salary), employee_id FROM employees;

分组查询

分组查询是对表按照某个列进行分组，再对每组的情况进行一个统计（每组的某列的平均值、最大最小值等）。

分组查询简单使用

写法：

select 分组函数, 列（要求出现在group by的后面）
from 表名
【where 筛选条件】
group by 分组的列表
【order by 字句】

我们可以看到上面写法中的查询列表是“分组函数”和“列”。这个“列”就是group by后面的字段，表示按照什么字段进行分组；这个“分组函数”就是对组里的什么字段进行一个什么样的统计。

# 查询每个工种的最高工资。
SELECT MAX(salary), job_id  # 统计每组的最高的salary
FROM employees
GROUP BY job_id;            # 按照job_id进行分组
# 查询每个位置上的部门个数
SELECT COUNT(*),location_id
FROM departments
GROUP BY location_id;

添加筛选条件

分组查询中的筛选条件分为两类：

	数据源	位置	关键字
分组前筛选	原始表	group by 子句的前面	where
分组后筛选	分组后的结果集	group by 子句的后面	having

使用注意点：

• 分组函数做条件肯定是放在 having 字句中的，也就是“分组后筛选”
• 能用分组前筛选的，就优先考虑使用分组前筛选。

添加分组前筛选例子：

# 查询邮箱中包含a字符的，每个部门的平均工资
SELECT AVG(salary), department_id
FROM employees
WHERE email LIKE '%a%'
GROUP BY department_id;
# 查询有奖金的，每个领导手下员工的最高工资
SELECT MAX(salary),manager_id
FROM employees
WHERE commission_pct IS NOT NULL
GROUP BY manager_id;

添加分组后筛选（使用HAVING关键字）例子：

# 查询哪个部门的员工个数>2（分组后的刷选）
SELECT COUNT(*), department_id
FROM employees
GROUP BY department_id
HAVING COUNT(*) > 2;
# 查询每个工种有奖金的员工的最高工资>12000的工种编号和最高工资
SELECT MAX(salary), job_id
FROM employees
WHERE commission_pct IS NOT NULL
GROUP BY job_id
HAVING MAX(salary) > 12000;

连接查询

子查询

分页查询

联合查询

查询总结