mysql 基础知识
数据定义语言分类
| 类型 | 解释 | 备注 |
|---|---|---|
| DDL | 数据定义语言 | 创建修改库、表、视图、索引、约束等 CREATE、DROP、ALTER 等 |
| DML | 数据操作语言 | 数据行的增删改 INSERT 、 DELETE 、 UPDATE 等 |
| DQL | 数据查询语言 | SELECT 语句 |
| DCL | 数据控制语言 | 安全控制，比如创建用户，给用户分配权限，回滚数据等 GRANT 、 REVOKE 、 COMMIT 、 ROLLBACK 、 SAVEPOINT 等 |
数据类型
| 类型 | 解释 | 备注 |
|---|---|---|
| float(m, d)、double(m, d) | 单精度(4字节)和双精度(8字节)的浮点数 m：整数位+小数位，d：小数位 |
存储时，整数位超出会报错 小数位超出会四舍五入 |
| decimal(m, d) | 浮点数可能会不精确，decimal 一定是精确的 m：整数位+小数位，d：小数位 |
当超出范围也会四舍五入 |
| year、time、date datetime、timestamp |
年、时间、日期、年月日日期、时间戳 | |
| char(int)、varchar | 存储字符串 char 必须指定长度(固定长度) varcahr 可以不指定长度(可变长字符串) |
如果指定长度，超出会报错 varchar 默认 255，最大65535 5.0 之后，varchar 指字符长度 |
| text | tinytext：0 <= L <= 255 text：0 <= L <= 65535 mediumtext：0 <= L <= 16777215 longtext：0 <= L<= 4294967295 |
longtext 最大可存大约 4 GB |
| blob | 二进制对象，也对用了 4 种blob，长度和 text 一致 | java 对应类型为 byte[] |
运算符
| 符号 | 解释 | 备注 |
|---|---|---|
| 算术运算符 | +、-、*、/、%=、<、>、>=、<= |
|
<==> |
安全等于 | 和 = 的区别是可以和 null 做对比效果和 IS NULL 相同 |
<> |
不等于 | 和!= 没区别，不能和 null 做对比 |
IS NULL、ISNULL |
为空运算符 | |
IS NOT NULL |
不为空运算符 | |
LEAST |
最小值运算符 | SELECT LEAST(1,2,3) |
GREATEST |
最大值运算符 | |
BETWEEN...AND... |
范围运算符 | 包含临界值 |
IN |
属于运算符 | |
NOT IN |
不属于运算符 | |
LIKE |
模糊匹配 | |
REGEXP |
正则运算符 | |
RLIKE |
判断正则是否合法 |
别名
-- USER_AGE 指定别名，要不要 AS 都行
SELECT USER_AGE AS AGE FROM USER;
-- 对运算结果起别名要加双引号
SELECT USER_AGE+10 "AGE" FROM USER;
去重
使用 DISTINCT，只能对行去重
排序和分页
ASC：顺序；DESC：倒叙
多列排序：先以第一个字段排序，然后根据排序的结果再次以第二个字段排序
分页：LIMIT [偏移量] 行数，偏移量公式：(pageNum-1)*pageSize。limit 2,10 从下标2开始，查10条
- select...where … group by … having … order by … limit
- GROUP BY 中的字段必须在 SELECT 中
连接查询
联表查询，分为两个版本：sql92 和 sql95。支持的查询方式有内连接，外连接（左右外连接），全连接（MySql 不支持标准的语法，但是有提代方案）
- 内连接：取交集，关联字段的值在主表和从表中都有的才保留
-- sql92(用逗号关联表)
select * from emp,dept where emp.deptno=dept.deptno
-- sql99(inner join 等价于 join)
select * from emp innner join dept on emp.deptno=dept.deptno
- 外连接：取并集，以主表为准，从表中不匹配的数据用 null 代替。MySql 不支持 sql92 外连接，也不支持 sql95 全外连接，即 MySql 只支持 sql95 的左右外连接
-- sql92 左外连接（左表为主，右边不够的补null，所以右表用+号标识）--- Mysql 不支持
select * from emp, dept where emp.deptno=dept.depetno(+)
-- sql95 左外连接（left outer join 等价于 left join）
select * from emp left outer join dept on emp.deptno=dept.deptno
-- sql92 全外连接 --- Mysql 不支持
select * from emp,dept where epm.deptno(+) = dept.dpetno(+)
-- sql95 全外连接 --- Mysql 不支持
select * from emp full outer join dept on emp.deptno=dept.deptno;
- 联合查询：UNION，UNION ALL
- 要联合的结果集必须一样（个数，顺序，类型），不一样也能出结果但是不准确，也不是我们预期的结果
- UNION 会去重，UNION ALL 不会去重（意味着效率会高于 UNION）
- MySQL 不支持 sql95 全外连接（不支持FULL JOIN），可以使用联合查询代替
- 自然连接 和 USING（sql99新特性）
-- sql95 内连接
select * from emp innner join dept on emp.deptno=dept.deptno;
-- sql99 自然连接（效果和上面一致，自动根据两个表相同的字段去关联，不用显示指定字段）
select * from emp NATURAL join dept;
-- sql99 USING 指定字段（效果和上面一样，不用 ON 指定字段，使用 USING）
select * from emp NATURAL join dept USING(dept_code);
子查询
- 单行子查询（单字段）：子查询的结果集只有一行数据，主表和子表的某个字段值相等
-- student 表的 u_id 和 user 表的 id 相等
SELECT * FROM user WHERE ID = (
SELECT u_id FROM student WHERE ID = 123
);
- 单行子查询（多字段）：子查询的结果集只有一行数据，主表和子表的多个字段值相等
-- sku.item_id 等于 spu.id，并且 sku.code = spu.code
select * from spu where (id,`code`) = (
select item_id, code from sku
where item_id = 1300949751678957570 and code = 'test02'
);
- 多行子查询（多字段）：主表和子表都能是多行数据（单字段直接用 in 来匹配）
-- user 表和 stu表中 code 和 name 相等的
select * from user u where EXISTS (
-- 这里 select 什么不重要
-- 只要能匹配上，user表中的记录就保留，跟子查询没关系
select 1
from stu s
where u.code = s.code and u.name = s.name
);
sql 执行顺序
完整的顺序：SELECT ... FROM ... WHERE ... GROUP BY ... HAVING ... ORDER BY ... LIMIT ，执行顺序如下：
SELECT ... -- 6，过滤查询字段（字段、别名、聚合、distinct）
FROM ... -- 1，确定主表
INNER JOIN ... on ... -- 2，确定从表
WHERE ... -- 3，根据条件过滤结果集
GROUP BY ... -- 4，查询结果分组
HAVING ... -- 5，根据条件过滤分组结果集
ORDER BY ... -- 7，排序（这里才能使用别名）
LIMIT ... ; -- 8，分页
