MySQL学习笔记（3）：DQL（数据查询语言）

一、基础查询

（1）基本语法

SELECT 查询列表
【FROM  表名】；             //【】表示可加可不加（但查询某些内容必须加）

（2）特点：
1.查询列表可以是：表中的字段、常量值、表达式、函数
2.查询的结果是一个虚拟的表格

1.查询表中的单个字段

• 一定要加FROM，指明查询来源。
• 举例如下

SELECT last_name
FROM employees;

• 运行结果（按F9执行查询）
  

2.查询表中的多个字段

• 不同字段之间用,隔开。
• 举例如下

SELECT last_name,salary,email
FROM employees;

• 运行结果
  

3.查询表中的所有字段

• 举例如下

SELECT *
FROM employees;

• 运行结果
  

注： 其实想查询哪个字段直接在左侧的数据库列表中双击该字段。

4.查询常量值

（1）查询数字

SELECT 100;

（2）查询字符（不区分字符和字符串）

SELECT 'john';

5.查询表达式

• 举例如下

SELECT 100%98;

• 运行结果
  

6.查询函数

查询一个函数相当于调用该函数，得到该函数的返回值并显示。

• 举例如下

SELECT VERSION();

• 运行结果
  

7.起别名

（1）作用：
1.便于理解
2.若要查询的字段有重名的情况，使用别名可以区分开来

（2）两种方法
方式一：使用AS

• 举例如下

SELECT last_name AS 姓,salary AS 工资,email AS 邮箱
FROM employees;

• 运行结果
  

方式二：使用空格

SELECT last_name 姓,salary 工资,email 邮箱
FROM employees;

特殊情况： 若要取的别名中带有关键字，则该别名应该用双引号括起来。

SELECT salary AS "out put"
FROM employees;

8.去重

• 使用关键字DISTINCT

SELECT DISTINCT salary FROM employees;      //这样就可以去除重复的

9.+（加号）的作用

MySQL中的+只有运算符的功能
MySQL中的+的用法：

1.SELECT 100+90;           //两个操作数都为数值型，则做加法运算，结果为190
2.SELECT '123'+90;    	  //只要其中一方为字符型，试图将字符型转换成数值型，若转换成功，则继续做加法运算；若转换失败，则将字符型的数值转换成0,在做加法运算。（如SELECT '123'+90;   的结果为213，SELECT 'john'+90;  的结果为90）
3.SELECT null+10; 			//只要其中一方为null，则结果肯定为null

10.连接两个字段的函数（CONCAT）

• 举例如下（查询员工的名和姓并连接成一个字段，显示为姓名）

SELECT CONCAT(last_name,first_name ) AS 姓名
FROM employees;

• 运行结果
  

二、条件查询

（1）基本语法

SELECT 查询列表
FROM 表名
WHERE 筛选条件;

（2）条件种类

（3）注意事项（模糊查询的特点）：
1.like的特点:一般和通配符%（任意多个字符，包含0个字符）或_（任意单个字符）使用。可以判断字符型或数值型。
2.between and的特点：包含临界值，两个临界值不能调换顺序
3.in的特点：判断某字段的值是否属于in列表中的某一项；in列表中的值类型必须统一。
4.is null的特点：=或<>不能用于判断null，但是is null和is not null可以

（4）举例

• 举例如下1（查询部门编号不是在90到110之间，或者工资高于15000的员工信息）

SELECT *
FROM employees
WHERE NOT(department_id>=90 AND department_id<=110) OR salary>15000;

• 运行结果
  

• 举例如下2（查询员工名中第三个字符为n，第五个字符为l的员工名；且员工编号在100到120之间；且工作名是否为it_prog）（不区分大小写）

SELECT 
  last_name,
  employee_id,
  job_id 
FROM
  employees 
WHERE last_name LIKE '__n_l%' 
  AND employee_id BETWEEN 100 
  AND 120 
  AND job_id IN ('it_prog') ;

• 运行结果
  

三、排序查询

（1）基本语法

SELECT 查询列表
FROM 表名
【WHERE 筛选条件】
ORDER BY 排序列表 【ASC或者DESC】        //ASC代表升序排列，DESC代表升序排列

（2）特点

1. ASC代表升序排列，DESC代表升序排列，如果不写，则默认是升序。

2. ORDER BY语句可以支持单个字段，多个字段，表达式，函数，别名。

3. ORDER BY语句一般放在查询语句的最后面。（limit子句除外）
   （3）举例

• 举例如下（查询员工信息，按工资升序，再按员工编号降序，即多个字段的排序）

SELECT *
FROM employees
ORDER BY salary ASC,employee_id DESC;

• 运行结果
  
• 结果分析
  整体的salary是升序的，但整体的employee_id不是降序，但当salary相同时，employee_id是降序的。

三、分组查询

（1）基本语法

select 分组函数(查询列表)，列（同时出现在group by的后面）
from 表名
【where 筛选条件】
group by 列
【order by 子句】

注： 查询列表必须特殊。

（2）特点

1.分组查询中的筛选条件

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

2.分组函数做条件肯定是放在having子句中

3.能用分组筛选的，优先考虑使用分组前筛选。

4.group by子句支持单个字段分组，也支持多个字段分组（多个字段之间用逗号隔开，没有先后顺序），也支持表达式或函数。

（3）举例如下

• 查询每个工种的最高工资

SELECT MAX(salary),job_id
FROM employees
GROUP BY job_id;

• 运行结果
  

四、连接查询

1.交叉连接，又称笛卡尔积（无连接条件）

笛卡尔积，表1有m行，表2有n行，则结果为m×n行。

语句1：
SELECT * FROM beauty;        //第一张表（美女）

语句2：
SELECT * FROM boys;  		//第二张表（男孩）

语句3：
SELECT beautyname,boyname FROM beauty CROSS JOIN boys;           //交叉连接查询（又称笛卡尔积），查出来的是两张表的乘积，这个乘积结果是没有意义的

注： 语句3出现笛卡尔积的原因是没有连接条件，所以第一张表beauty的每一行都会与第二张的表boys的所有行匹配成功。

2.内连接查询（99）

2.1 等值连接（连接条件用的=）

（1）用法（INNER可以省略）

SELECT 查询列表
FROM 表1 别名
INNER JOIN 表2 别名
ON 连接条件
INNER JOIN 表3 别名
ON 连接条件
...
【后面可以继续加入其他的语句】

举例：
SELECT beautyname,boyname 
FROM beauty INNER JOIN boys;
ON beauty.beauty_id = boys.boys_id
WHERE beauty.beauty_age = boys.boys_age ;             //这里要用boys.boys_id，不能用boys_id，防止出现两个不同的表有相同的列名称。

（2）特点

• 多表等值连接的连接结果为多表的交集部分。
• n表连接，至少需要n-1个连接条件。
• FROM后的多表顺序没有要求。
• 一般需要为表起别名（简化书写）。
• 可以搭配很多子句使用，比如分组，排序，筛选等。

2.2 非等值连接（连接条件用的非=）
用法和特点与等值连接类似。

2.3 自连接
（1）自连接的定义

比如一个表中有所有员工的名字和他的上级（当然也是一种员工啦）的编号，那么，要想知道每个员工的上级是谁，相当与先从表中查出每个员工的上级ID，然后拿着这个ID再查一次这个表找到他的上级名字。

（2）用法

SELECT E.employee_id,E.last_name,M.employee_id,M.last_name
FROM employees E
INNER JOIN employees M
ON E.manager_id = M.employee_id;

其中两次用到了employees这个表，两次用到时分别命名为E和M。

（3）运行结果

3.外连接查询（99）

• 左外连接（下图是左外连接，右外连接同理）
  
• 另一种左外连接
  

（1）应用场景
用于查询一个表中有，而另一个表中没有的记录

（2）特点

• 用于查询一个表中有，而另一个表中没有的记录。
• 外连接的查询结果为主表中的所有记录。若从表中有和它（主表中的所有记录），则显示匹配值，若无，则显示null。
• 外连接的查询结果 = 内连接的结果 + 主表中有而从表中没有的记录
• 全外连接的结果 = 内连接结果 + 表1中有但表2中没有的 + 表2中有表1中没有的
• 左外连接，LEFT JOIN左边的是主表。右外连接，RIGHT JOIN右边的是主表

（3）举例

SELECT D.*,e.`employee_id`
FROM departments D
LEFT OUTER JOIN employees E
ON D.`department_id` = E.`department_id`
WHERE e.`employee_id` IS NULL;

运行结果

（4）全外连接
MySQL不支持全连接。以下是全连接的两种情况。

五、子查询

（1）定义

• 子查询（内查询）：出现在其他语句内部的select语句。
• 主查询（外查询）：内部嵌套其他select语句的查询。

（2）特点

1. 子查询放在小括号内。
2. 子查询一般放在条件的右侧。
3. 标量子查询（结果集只有一行一列）一般搭配着单行操作符使用，如>、<、>=、<=、=、<>。
4. 列子查询（结果集只有一列多行）一般搭配着多行操作符使用，如in、any、some、all。
5. 主查询的条件用到了子查询的结果，所以子查询的执行优先于主查询的执行。

（3）标量子查询举例

• 举例：
  返回job_id与141号员工相同，salary比143号员工多的员工的姓名、job_id和工资。

• 分析
  先查询141号员工的job_id（子查询1）；再查询143号员工的salary（子查询2）；最后进行主查询。

• 代码实现

SELECT last_name,job_id,salary
FROM employees
WHERE job_id=(
	SELECT job_id
	FROM employees
	WHERE employee_id=141
)AND salary>(
	SELECT salary
	FROM employees
	WHERE employee_id=143
);

• 运行结果
  

（4）列子查询举例

• 举例
  返回location_id是1400或1700的部门中的所有员工姓名

• 分析
  先查询location_id是1400或1700的部门编号（子查询），再执行主查询

• 代码实现

SELECT last_name
FROM employees
WHERE department_id IN (
	SELECT DISTINCT department_id
	FROM departments
	WHERE location_id IN(1400,1700)
);

• 运行结果
  

六、分页查询

（1）应用场景
当要显示的数据一页显示不全，需要分页提交SQL请求。

（2）语法

select 字段|表达式,...
	from 表
	【where 条件】
	【group by 分组字段】
	【having 条件】
	【order by 排序的字段】
	limit 【offset,】size;                         //offset是要显示的条目的起始索引（从0开始）；size是要显示的条目个数

（3）特点

1. 起始条目索引从0开始

2. limit子句放在查询语句的最后

3. 公式：select * from 表 limit （page-1）*size,size;
   其中，每页显示条目数为size，要显示的页数为page。

七、union联合查询

（1）作用
将多条查询语句的结果合并成一个结果。

（2）语法

查询语句1
union
查询语句2
union
...

（3）注意事项

1. 多条查询语句的查询的列数必须是一致的。
2. 多条查询语句的查询的每列的类型几乎相同。
3. union代表去重，union all代表不去重。