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;
列的别名
- 可以用空格
- 可以用
AS
- 可以用一对
""
双引号引起来()
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;
去除重复行 | DISTINCT
# 加上 DISTINCT 去重
SELECT DISTINCT id AS "ID" FROM employees;
空值参与运算
空值参与运算无论加减乘除都是nul
SELECT id*1000 "ID",name FROM employees;
是null
的地方参与运算之后还是null
着重号
用于区分关键字与表名
SELECT * FROM `ORDER`;
查询常数
SELECT '尚硅谷',id,name FROM employees;
SELECT '尚硅谷' AS "公司",id,name FROM employees;
显示表结构
DESC employees;
DESCRIBE employees;
使用WHERE
过滤数据
SELECT * FROM t_admin WHERE id=5;
SELECT * FROM employees WHERE name="Mask";
常用运算符
+
、-
、*
、/
加减 |+
、-
|隐式运算
SELECT 100+'1' FROM DUAL;
SELECT 100+'a' FROM DUAL;
SELECT 100+null FROM DUAL;
结果分别为: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;
SQL做除法默认带小数位
取模运算 | 结果仅与被模数有关
SELECT 12%3,12%5,12 MOD -5,-12%5,-12%-5 FROM DUAL;
若是a % b
,则结果是正还是负数与a有关,与b无关
运算符
比较运算符
字符串与字符之间做比较,那么也会触发隐式转换
。
运算符 | 内容 | 运算符 | 内容 |
---|---|---|---|
= | 等于 | <=> | 安全等于 |
!= | 不等于 | <> | 不等于 |
< | 小于 | <= | 小于等于 |
> | 大于 | >= | 大于等于 |
SELECT 1=2,1!=2,1<=>2,1<>2,1='1',1='a',0='a' FROM DUAL;
结果:0 1 0 1 1 0 1
SELECT 'a'='a','ab'='ab','a'='b' FROM DUAL;
结果:1 1 0
select 1=NULL,NULL=NULL from DUAL;
结果:
null
null
SELECT * FROM employees WHERE id=null;
❌ 错误的写法 ❌
SELECT * FROM employees WHERE id<=>null;
✅ 正确的写法 ✅ 使用安全等于<=>
✅
关键字
运 算 符 | 名 称 | 作 用 | 示 例 |
---|---|---|---|
IS NULL | 为空运算符 | 判断值、字符串或表达式是否为空 | SELECT B FROM TABLE WHERE A IS NULL |
IS NOTNULL | 不为空运算符 | 判断值、祖父穿或表达式是否不为空 | SELECT B FROM TABLE WHERE A IS NOT NULL |
LEAST | 最小运算符 | 在多个值中返回最小值 | SELECT D FROM TABLE WHERE C LEAST(A,B ) |
GREATEST | 最大运算符 | 在多个值中返回最大值 | SELECT D FROM TABLE WHERE C GREATEST(A,B ) |
BETWEEN AND | 两值之间运算符 | 判断一个值是否有在两个值之间 | SELECT D FROM TABLE WHERE C BETWEEN A AND B |
ISNULL | 为空运算符 | 判断一个值、字符串或表达式是否为空 | SELECT B FROM TABLE WHERE ISNULL |
IN | 属于运算符 | 判断一个值是否为列表中的任意一个值 | SELECT D FROM TABLE WHERE C IN (A,B) |
NOT IN | 不属于运算符 | 判断一个值是否不是列表中的任意一个值 | SELECT D FROM TABLE WHERE C NOT IN (A,B) |
LIKE | 模糊匹配运算符 | 判断一个值是否符合模糊匹配规则 | SELECT C FROM TABLE WHERE A LIKE B |
REGEXP | 正则表达式运算符 | 判断一个值是否符合正则表达式的规则 | SELECT C FROM TABLE WHERE A REGEXP B |
RLIKE | 正则表达式运算符 | 判断一个值是否符合正则表达式的规则 | SELECT C FROM TABLE WHERE A RLIKE B |
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;
LEAST / GREATEST
SELECT LEAST('A','B','C','D','E'),GREATEST('A','B','C','D','E');
# ↑ A E
SELECT LEAST(id,name) FROM employees;
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;
IN
SELECT id FROM employees WHERE id IN(1002,1003);
SELECT id FROM employees WHERE id = 1002 OR id = 1003;
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;
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 意思是将`$`作为转义字符
一些字符
占位符
%
:0 个或多个字符
_
:一个字符
转义字符
\
:普通转义字符
关键字ESCAPE
定义转义字符
正则表达式 REGEXP、RLIKE
逻辑运算符
运算符 | 作用 | 示例 |
---|---|---|
NOT 或 ! | 逻辑非 | SELECT NOT A |
AND 或 && | 逻辑与 | SELECT A AND B; SELECT A && B; |
OR 或 ` | ` | |
XOR | 逻辑异或 | SELECT A XOR B; |
XOR
逻辑异或:只要 A 与 B 一真一假,那么结果就为真,一样则为假
AND
可以与OR
一起参与运算,但是AND
的优先级要高于OR
;
## 先运算 A AND B 与 C AND D,然后再 OR
A AND B OR C AND D;
位运算符
运算符 | 作 用 | 示 例 |
---|---|---|
& | 按位与 | SELECT A & B |
| | 按位或 | SELECT A | B |
^ | 按位异或 | SELECT A ^ B |
~ | 按位取反 | SELECT ~ B |
>> | 按位右移 | SELECT A >> 2 |
<< | 按位左移 | SELECT A << 2 |
排序与分页
排序 ORDER BY | ASC | DESC
默认升序 (ASC) | DESC 是降序
## DESC 降序排列
SELECT * FROM employees ORDER BY id;
SELECT * FROM employees ORDER BY id DESC;
分页 | LIMIT
可以用在 MySQL | PGSQL | MariaDB | SQLite
SELECT * FROM employees LIMIT 0,3;
SELECT * FROM employees ORDER BY id LIMIT 4,3;
多表查询
笛卡尔积错误,每个数据都跟另一个表里的每一个数据做了组合。
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;
从SQL优化的角度出发,建议每个字段前都指明他所在的表
可以在SELECT
和WHERE
中给表去别名。
SELECT emp.id,dep.name
FROM employees emp,department dep
WHERE emp.id = dep.id;
注意,取别名之后要全部都用别名
连接方式
等值连接 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(+);
左外连接
## 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`;
如何实现满外连接? UNION
合并查询结果
利用UNION关键字,可以给出多条SELECT语句,并将它们的结果组合成单个结果集。合并时,两个表对应的列数和数据类型必须相同,并且相互对应。各个SELECT语句之间使用UNION或UNION ALL关键宇分隔。
语法格式:
SELECT COLUMN ... FROM table1
UNION(ALL)
SELECT COLUMN ... FROM table12;
中间图 | 内连接
SELECT empid,depname
FROM employees e JOIN dep d
WHERE e.`empid`=d.`depid`;
左上图 | 左外连接
SELECT empid,depname
FROM employees e LEFT JOIN dep d
WHERE e.`empid`=d.`depid`;
右上图 | 右外连接
SELECT empid,depname
FROM employees e RIGHT JOIN dep d
WHERE e.`empid`=d.`depid`;
左中图
SELECT empid,depname
FROM employees e LEFT JOIN dep d
WHERE e.`empid`=d.`depid`;
WHERE d.`depid` IS NULL;
右中图
SELECT empid,depname
FROM employees e RIGHT JOIN dep d
WHERE e.`empid`=d.`depid`;
WHERE e.`depid` IS NULL;
左下图 | 满外连接
方式一 | 左上图 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;
方式二 | 左中图 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`;
右下图
左中图 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;
SQL99 语法新特性
自然连接
$QL99 在SQL92 的基础上提供了一些特殊语法,比如 NATURAL JOIN 用来表示自然连接。我们可以把自然连接理解为 SQL92 中的等值连接。它会帮你自动查询两张连接表中 所有相同的字段,然后进行等值连接。
SELECT empid,depname
FROM employees e NATURAL JOIN dep d;
USING
当两个表中字段名字一样的时候,可以直接用 USING( )
SELECT e.id,d.name
FROM employees e JOIN dep d
USING(id);