Select

SELECT FROM

# 在 mysql 命令行中执行
SELECT 1+1,2*3;
# 伪表中查询
SELECT 1+1,2*3 FROM DUAL; 

# 查询全部内容
SELECT * FROM employees; 
# 查询部分内容
SELECT employee_id,last_name,salary FROM employees;

1
2
3
4
5
6
7
8
9
10

列的别名

1. 可以用空格
2. 可以用AS
3. 可以用一对""双引号引起来（）

SELECT employee_id emp_id,salary sary from employees;
SELECT employee_id AS emp_id,salary AS sary from employees;
SELECT employee_id "emp_id",salary "sary" from employees;
1
2
3

去除重复行 | DISTINCT

# 加上 DISTINCT 去重
SELECT DISTINCT id AS "ID" FROM employees;
1
2

空值参与运算

空值参与运算无论加减乘除都是nul

SELECT id*1000 "ID",name FROM employees;
1

是null的地方参与运算之后还是null

着重号

用于区分关键字与表名

SELECT * FROM `ORDER`;
1

查询常数

SELECT '尚硅谷',id,name FROM employees;
SELECT '尚硅谷' AS "公司",id,name FROM employees;
1
2

显示表结构

DESC employees;
DESCRIBE employees;
1
2

使用WHERE 过滤数据

SELECT * FROM t_admin WHERE id=5;
SELECT * FROM employees WHERE name="Mask";
1
2

常用运算符

+、-、*、/

加减 ｜+、-｜隐式运算

SELECT 100+'1' FROM DUAL;
SELECT 100+'a' FROM DUAL;
SELECT 100+null FROM DUAL;
1
2
3

结果分别为：101、100、null

当数字放在单引号之内做加减乘除的时候会出发隐式运算，所以结果是101，但是'a'不是一个数字，无法做转换

乘除 ｜*、/｜ 除法默认带小数点

SELECT 100,100*1,100*1.0,100/1.0,100/2,100+2*5/2,100/3,100 DIV 0 FROM DUAL;
1

SQL做除法默认带小数位

取模运算 ｜ 结果仅与被模数有关

SELECT 12%3,12%5,12 MOD -5,-12%5,-12%-5 FROM DUAL;
1

若是a % b，则结果是正还是负数与a有关，与b无关

运算符

比较运算符

字符串与字符之间做比较，那么也会触发隐式转换。

SELECT 1=2,1!=2,1<=>2,1<>2,1='1',1='a',0='a' FROM DUAL;
1

结果：0 1 0 1 1 0 1

SELECT 'a'='a','ab'='ab','a'='b' FROM DUAL;
1

结果：1 1 0

select 1=NULL,NULL=NULL from DUAL;
1

结果：null null

SELECT * FROM employees WHERE id=null;
1

❌ 错误的写法 ❌

SELECT * FROM employees WHERE id<=>null;
1

✅ 正确的写法 ✅ 使用安全等于<=> ✅

关键字

IS NULL

SELECT * FROM employees WHERE ISNULL(id) AND name IS NOT NULL;
SELECT * FROM employees WHERE id IS NULL AND name IS NOT NULL;
SELECT * FROM employees WHERE id <=> NULL AND name IS NOT NULL;
1
2
3

LEAST / GREATEST

SELECT LEAST('A','B','C','D','E'),GREATEST('A','B','C','D','E');
# ↑ A E
SELECT LEAST(id,name) FROM employees;
1
2
3

BETWEEN AND

BETWEEN 0 AND 5的范围是[0,5] 而非 [0,5)或者其他。

SELECT id  FROM employees WHERE id BETWEEN 1002 AND 1003;
SELECT id FROM employees WHERE id >= 1002 AND ID <=1003;
1
2

IN

SELECT id  FROM employees WHERE id IN(1002,1003);
SELECT id  FROM employees WHERE id = 1002 OR id = 1003;
1
2

NOT IN

SELECT id  FROM employees WHERE id NOT IN(1002,1003);
SELECT id  FROM employees WHERE id != 1002 OR id != 1003;
SELECT id  FROM employees WHERE NOT id = 1002 OR NOT id = 1003;
1
2
3

LIKE

# Jack Mask
SELECT * FROM employees WHERE name LIKE '%a%';

# 以 a 开头
SELECT * FROM employees WHERE name LIKE 'a%';
# 以 a 结尾
SELECT * FROM employees WHERE name LIKE '%a';

### 包含 a 或者包含 e
SELECT * FROM employees WHERE name LIKE '%a%' AND name LIKE '%c%';
### a 必须在 e 前面
SELECT * FROM employees WHERE name LIKE '%a%e%';

### 查询第二个字符是 a 的名字
SELECT * FROM employees WHERE name LIKE '_a%';

### 查询第二个字符是下划线且第三个字符是 a 的信息 使用转义字符
SELECT * FROM employees WHERE name LIKE '_\_a%';
SELECT * FROM employees WHERE name LIKE '_$_a%' ESCAPE '$';
# ESCAPE 意思是将`$`作为转义字符
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20

一些字符

占位符

%：0 个或多个字符

_：一个字符

转义字符

\：普通转义字符

关键字ESCAPE定义转义字符

正则表达式 REGEXP、RLIKE

正则表达式跳转连接

逻辑运算符

XOR逻辑异或：只要 A 与 B 一真一假，那么结果就为真，一样则为假

AND可以与OR一起参与运算，但是AND的优先级要高于OR;

## 先运算 A AND B 与 C AND D，然后再 OR
A AND B OR C AND D;
1
2

位运算符

排序与分页

排序 ORDER BY | ASC | DESC

默认升序 （ASC） | DESC 是降序

## DESC 降序排列
SELECT * FROM employees ORDER BY id;
SELECT * FROM employees ORDER BY id DESC;
1
2
3

分页 | LIMIT

可以用在 MySQL | PGSQL | MariaDB | SQLite

SELECT * FROM employees LIMIT 0,3;
SELECT * FROM employees ORDER BY id LIMIT 4,3;
1
2

多表查询

笛卡尔积错误，每个数据都跟另一个表里的每一个数据做了组合。

SELECT empid,depname FROM employees,dep WHERE dep.depid = employees.empid;
# 两个表的链接条件
SELECT id,name FROM employees,department WHERE employees.id = department.id;
# 报错 解决方案
SELECT employees.id,department.name FROM employees,department WHERE employees.id = department.id;
1
2
3
4
5

从SQL优化的角度出发，建议每个字段前都指明他所在的表

可以在SELECT和WHERE中给表去别名。

SELECT emp.id,dep.name 
FROM employees emp,department dep
WHERE emp.id = dep.id;
1
2
3

注意，取别名之后要全部都用别名

连接方式

等值连接 vs 非等值连接 BETWEEN AND

自连接 vs 非自连接

• 自己连接自己SELECT * FROM employees;
• 自己连接别人

内连接 vs 外连接

• 内连接：合并具有同一列的两个以上的表的行，结果集中不包含一个表与另一个表不匹配的行
• 外连接：两个表在连接过程中除了返回满足连接条件的行以外还返回左（或右） 表中不满足条件的行，这种连接称为左（或右） 外连接。没有匹配的行时，结果表中相应的列为空(NULL)。
• 如果是左外连接，则连接条件中左边的表也称为 主表，右边的表称为 从表。
  如果是右外连接，则连接条件中右边的表也称为主表，左边的表称为 从表。

右外连接

# 左外连接
SELECT empid,depname 
FROM employees e,dep d
WHERE e.`empid`=d.depid;

# MySQL 不支持的做法 ()
SELECT empid,depname 
FROM employees e,dep d
WHERE e.`empid`=d.depid(+);
1
2
3
4
5
6
7
8
9

左外连接

## SQL99 内连接
SELECT last_name,department_name,city 
FROM employees e JOIN department d
ON e.`employee_id` = d.`department_id`
JOIN locations l 
ON d.`location_id` = l.`location_id`;

SELECT last_name,department_name,city 
FROM employees e LEFT OUTER  JOIN department d
ON e.`employee_id` = d.`department_id`
JOIN locations l 
ON d.`location_id` = l.`location_id`;

SELECT last_name,department_name,city 
FROM employees e RIGHT OUTER  JOIN department d
ON e.`employee_id` = d.`department_id`
JOIN locations l 
ON d.`location_id` = l.`location_id`;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18

如何实现满外连接？ UNION

合并查询结果

利用UNION关键字，可以给出多条SELECT语句，并将它们的结果组合成单个结果集。合并时，两个表对应的列数和数据类型必须相同，并且相互对应。各个SELECT语句之间使用UNION或UNION ALL关键宇分隔。

语法格式：

SELECT COLUMN ... FROM table1 
UNION(ALL)
SELECT COLUMN ... FROM table12;
1
2
3

中间图 | 内连接

SELECT empid,depname 
FROM employees e JOIN dep d
WHERE e.`empid`=d.`depid`;
1
2
3

左上图 | 左外连接

SELECT empid,depname 
FROM employees e LEFT JOIN dep d
WHERE e.`empid`=d.`depid`;
1
2
3

右上图 | 右外连接

SELECT empid,depname 
FROM employees e RIGHT JOIN dep d
WHERE e.`empid`=d.`depid`;
1
2
3

左中图

SELECT empid,depname 
FROM employees e LEFT JOIN dep d
WHERE e.`empid`=d.`depid`;
WHERE d.`depid` IS NULL;
1
2
3
4

右中图

SELECT empid,depname 
FROM employees e RIGHT JOIN dep d
WHERE e.`empid`=d.`depid`;
WHERE e.`depid` IS NULL;
1
2
3
4

左下图 | 满外连接

方式一 | 左上图 UNION ALL 右中图

SELECT empid,depname 
FROM employees e LEFT JOIN dep d
WHERE e.`empid`=d.`depid`

UNION ALL

SELECT empid,depname 
FROM employees e RIGHT JOIN dep d
WHERE e.`empid`=d.`depid`;
WHERE e.`depid` IS NULL;
1
2
3
4
5
6
7
8
9
10

方式二 | 左中图 UNION ALL 右上图

SELECT empid,depname 
FROM employees e LEFT JOIN dep d
WHERE e.`empid`=d.`depid`;
WHERE d.`depid` IS NULL
UNION ALL
SELECT empid,depname 
FROM employees e RIGHT JOIN dep d
WHERE e.`empid`=d.`depid`;
1
2
3
4
5
6
7
8

右下图

左中图 UNION ALL 右中图

SELECT empid,depname 
FROM employees e LEFT JOIN dep d
WHERE e.`empid`=d.`depid`;
WHERE d.`depid` IS NULL
UNION ALL 
SELECT empid,depname 
FROM employees e RIGHT JOIN dep d
WHERE e.`empid`=d.`depid`;
WHERE e.`depid` IS NULL;
1
2
3
4
5
6
7
8
9

SQL99 语法新特性

自然连接

$QL99 在SQL92 的基础上提供了一些特殊语法，比如 NATURAL JOIN 用来表示自然连接。我们可以把自然连接理解为 SQL92 中的等值连接。它会帮你自动查询两张连接表中 所有相同的字段，然后进行等值连接。

SELECT empid,depname 
FROM employees e NATURAL JOIN dep d;
1
2

USING

当两个表中字段名字一样的时候，可以直接用 USING( )

SELECT e.id,d.name 
FROM employees e JOIN dep d
USING(id);
1
2
3