基础篇
通用语法及分类
- DDL: 数据定义语言,用来定义数据库对象(数据库、表、字段)
- DML: 数据操作语言,用来对数据库表中的数据进行增删改
- DQL: 数据查询语言,用来查询数据库中表的记录
- DCL: 数据控制语言,用来创建数据库用户、控制数据库的控制权限
DDL(数据定义语言)
数据定义语言
数据库操作
查询所有数据库:
SHOW DATABASES;
查询当前数据库:
SELECT DATABASE();
创建数据库:
CREATE DATABASE [ IF NOT EXISTS ] 数据库名 [ DEFAULT CHARSET 字符集] [COLLATE 排序规则 ];
删除数据库:
DROP DATABASE [ IF EXISTS ] 数据库名;
使用数据库:
USE 数据库名;
注意事项
- UTF8字符集长度为3字节,有些符号占4字节,所以推荐用utf8mb4字符集
表操作
查询当前数据库所有表:
SHOW TABLES;
查询表结构:
DESC 表名;
查询指定表的建表语句:
SHOW CREATE TABLE 表名;
创建表:
<span class="pln">CREATE TABLE <span class="pun">表名(</span></span>
<span class="pln"> <span class="pun">字段<span class="lit">1<span class="pln"> <span class="pun">字段<span class="lit">1<span class="pun">类型<span class="pln"> <span class="pun">[<span class="pln">COMMENT <span class="pun">字段<span class="lit">1<span class="pun">注释],</span></span></span></span></span></span></span></span></span></span></span></span></span>
<span class="pln"> <span class="pun">字段<span class="lit">2<span class="pln"> <span class="pun">字段<span class="lit">2<span class="pun">类型<span class="pln"> <span class="pun">[<span class="pln">COMMENT <span class="pun">字段<span class="lit">2<span class="pun">注释],</span></span></span></span></span></span></span></span></span></span></span></span></span>
<span class="pln"> <span class="pun">字段<span class="lit">3<span class="pln"> <span class="pun">字段<span class="lit">3<span class="pun">类型<span class="pln"> <span class="pun">[<span class="pln">COMMENT <span class="pun">字段<span class="lit">3<span class="pun">注释],</span></span></span></span></span></span></span></span></span></span></span></span></span>
<span class="pln"> <span class="pun">...</span></span>
<span class="pln"> <span class="pun">字段<span class="pln">n <span class="pun">字段<span class="pln">n<span class="pun">类型<span class="pln"> <span class="pun">[<span class="pln">COMMENT <span class="pun">字段<span class="pln">n<span class="pun">注释]</span></span></span></span></span></span></span></span></span></span></span></span>
<span class="pun">)[<span class="pln"> COMMENT <span class="pun">表注释<span class="pln"> <span class="pun">];</span></span></span></span></span>
最后一个字段后面没有逗号
添加字段:
ALTER TABLE 表名 ADD 字段名 类型(长度) [COMMENT 注释] [约束];
例: ALTER TABLE emp ADD nickname varchar(20) COMMENT '昵称';
修改数据类型:
ALTER TABLE 表名 MODIFY 字段名 新数据类型(长度);
修改字段名和字段类型:
ALTER TABLE 表名 CHANGE 旧字段名 新字段名 类型(长度) [COMMENT 注释] [约束];
例:将emp表的nickname字段修改为username,类型为varchar(30)
ALTER TABLE emp CHANGE nickname username varchar(30) COMMENT '昵称';
删除字段:
ALTER TABLE 表名 DROP 字段名;
修改表名:
ALTER TABLE 表名 RENAME TO 新表名
删除表:
DROP TABLE [IF EXISTS] 表名;
删除表,并重新创建该表:
TRUNCATE TABLE 表名;
DML(数据操作语言)
添加数据
指定字段:
INSERT INTO 表名 (字段名1, 字段名2, ...) VALUES (值1, 值2, ...);
全部字段:
INSERT INTO 表名 VALUES (值1, 值2, ...);
批量添加数据:
INSERT INTO 表名 (字段名1, 字段名2, ...) VALUES (值1, 值2, ...), (值1, 值2, ...), (值1, 值2, ...);
INSERT INTO 表名 VALUES (值1, 值2, ...), (值1, 值2, ...), (值1, 值2, ...);
注意事项
- 字符串和日期类型数据应该包含在引号中
- 插入的数据大小应该在字段的规定范围内
更新和删除数据
修改数据:
UPDATE 表名 SET 字段名1 = 值1, 字段名2 = 值2, ... [ WHERE 条件 ];
例:
UPDATE emp SET name = 'Jack' WHERE id = 1;
删除数据:
DELETE FROM 表名 [ WHERE 条件 ];
DQL(数据查询语言)
语法:
<span class="pln">SELECT</span>
<span class="pln"> <span class="pun">字段列表</span></span>
<span class="pln">FROM</span>
<span class="pln"> <span class="pun">表名字段</span></span>
<span class="pln">WHERE</span>
<span class="pln"> <span class="pun">条件列表</span></span>
<span class="pln">GROUP BY</span>
<span class="pln"> <span class="pun">分组字段列表</span></span>
<span class="pln">HAVING</span>
<span class="pln"> <span class="pun">分组后的条件列表</span></span>
<span class="pln">ORDER BY</span>
<span class="pln"> <span class="pun">排序字段列表</span></span>
<span class="pln">LIMIT</span>
<span class="pln"> <span class="pun">分页参数</span></span>
基础查询
查询多个字段:
SELECT 字段1, 字段2, 字段3, ... FROM 表名;
SELECT * FROM 表名;
设置别名:
SELECT 字段1 [ AS 别名1 ], 字段2 [ AS 别名2 ], 字段3 [ AS 别名3 ], ... FROM 表名;
SELECT 字段1 [ 别名1 ], 字段2 [ 别名2 ], 字段3 [ 别名3 ], ... FROM 表名;
去除重复记录:
SELECT DISTINCT 字段列表 FROM 表名;
转义:
SELECT * FROM 表名 WHERE name LIKE '/_张三' ESCAPE '/'
/ 之后的_不作为通配符
条件查询
语法:
SELECT 字段列表 FROM 表名 WHERE 条件列表;
条件:
比较运算符功能 > 大于 >= 大于等于 < 小于
例子:
<span class="pun">--<span class="pln"> <span class="pun">年龄等于<span class="lit">30</span></span></span></span>
<span class="kwd">select<span class="pln"> <span class="pun">*<span class="pln"> <span class="kwd">from<span class="pln"> employee <span class="kwd">where<span class="pln"> age <span class="pun">=<span class="pln"> <span class="lit">30<span class="pun">;</span></span></span></span></span></span></span></span></span></span></span></span>
<span class="pun">--<span class="pln"> <span class="pun">年龄小于<span class="lit">30</span></span></span></span>
<span class="kwd">select<span class="pln"> <span class="pun">*<span class="pln"> <span class="kwd">from<span class="pln"> employee <span class="kwd">where<span class="pln"> age <span class="pun"><<span class="pln"> <span class="lit">30<span class="pun">;</span></span></span></span></span></span></span></span></span></span></span></span>
<span class="pun">--<span class="pln"> <span class="pun">小于等于</span></span></span>
<span class="kwd">select<span class="pln"> <span class="pun">*<span class="pln"> <span class="kwd">from<span class="pln"> employee <span class="kwd">where<span class="pln"> age <span class="pun"><=<span class="pln"> <span class="lit">30<span class="pun">;</span></span><!--=<span--></span></span></span></span></span></span></span></span></span>
<span class="pun">--<span class="pln"> <span class="pun">没有身份证</span></span></span>
<span class="kwd">select<span class="pln"> <span class="pun">*<span class="pln"> <span class="kwd">from<span class="pln"> employee <span class="kwd">where<span class="pln"> idcard <span class="kwd">is<span class="pln"> <span class="kwd">null<span class="pln"> <span class="kwd">or<span class="pln"> idcard <span class="pun">=<span class="pln"> <span class="str">''<span class="pun">;</span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span>
<span class="pun">--<span class="pln"> <span class="pun">有身份证</span></span></span>
<span class="kwd">select<span class="pln"> <span class="pun">*<span class="pln"> <span class="kwd">from<span class="pln"> employee <span class="kwd">where<span class="pln"> idcard<span class="pun">;</span></span></span></span></span></span></span></span></span>
<span class="kwd">select<span class="pln"> <span class="pun">*<span class="pln"> <span class="kwd">from<span class="pln"> employee <span class="kwd">where<span class="pln"> idcard <span class="kwd">is<span class="pln"> <span class="kwd">not<span class="pln"> <span class="kwd">null<span class="pun">;</span></span></span></span></span></span></span></span></span></span></span></span></span></span>
<span class="pun">--<span class="pln"> <span class="pun">不等于</span></span></span>
<span class="kwd">select<span class="pln"> <span class="pun">*<span class="pln"> <span class="kwd">from<span class="pln"> employee <span class="kwd">where<span class="pln"> age <span class="pun">!=<span class="pln"> <span class="lit">30<span class="pun">;</span></span></span></span></span></span></span></span></span></span></span></span>
<span class="pun">--<span class="pln"> <span class="pun">年龄在<span class="lit">20<span class="pun">到<span class="lit">30<span class="pun">之间</span></span></span></span></span></span></span>
<span class="kwd">select<span class="pln"> <span class="pun">*<span class="pln"> <span class="kwd">from<span class="pln"> employee <span class="kwd">where<span class="pln"> age between <span class="lit">20<span class="pln"> <span class="kwd">and<span class="pln"> <span class="lit">30<span class="pun">;</span></span></span></span></span></span></span></span></span></span></span></span></span></span>
<span class="kwd">select<span class="pln"> <span class="pun">*<span class="pln"> <span class="kwd">from<span class="pln"> employee <span class="kwd">where<span class="pln"> age <span class="pun">>=<span class="pln"> <span class="lit">20<span class="pln"> <span class="kwd">and<span class="pln"> age <span class="pun"><=<span class="pln"> <span class="lit">30<span class="pun">;</span></span><!--=<span--></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span>
<span class="pun">--<span class="pln"> <span class="pun">下面语句不报错,但查不到任何信息</span></span></span>
<span class="kwd">select<span class="pln"> <span class="pun">*<span class="pln"> <span class="kwd">from<span class="pln"> employee <span class="kwd">where<span class="pln"> age between <span class="lit">30<span class="pln"> <span class="kwd">and<span class="pln"> <span class="lit">20<span class="pun">;</span></span></span></span></span></span></span></span></span></span></span></span></span></span>
<span class="pun">--<span class="pln"> <span class="pun">性别为女且年龄小于<span class="lit">30</span></span></span></span>
<span class="kwd">select<span class="pln"> <span class="pun">*<span class="pln"> <span class="kwd">from<span class="pln"> employee <span class="kwd">where<span class="pln"> age <span class="pun"><<span class="pln"> <span class="lit">30<span class="pln"> <span class="kwd">and<span class="pln"> gender <span class="pun">=<span class="pln"> <span class="str">'女'<span class="pun">;</span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span>
<span class="pun">--<span class="pln"> <span class="pun">年龄等于<span class="lit">25<span class="pun">或<span class="lit">30<span class="pun">或<span class="lit">35</span></span></span></span></span></span></span></span>
<span class="kwd">select<span class="pln"> <span class="pun">*<span class="pln"> <span class="kwd">from<span class="pln"> employee <span class="kwd">where<span class="pln"> age <span class="pun">=<span class="pln"> <span class="lit">25<span class="pln"> <span class="kwd">or<span class="pln"> age <span class="pun">=<span class="pln"> <span class="lit">30<span class="pln"> <span class="kwd">or<span class="pln"> age <span class="pun">=<span class="pln"> <span class="lit">35<span class="pun">;</span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span>
<span class="kwd">select<span class="pln"> <span class="pun">*<span class="pln"> <span class="kwd">from<span class="pln"> employee <span class="kwd">where<span class="pln"> age <span class="kwd">in<span class="pln"> <span class="pun">(<span class="lit">25<span class="pun">,<span class="pln"> <span class="lit">30<span class="pun">,<span class="pln"> <span class="lit">35<span class="pun">);</span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span>
<span class="pun">--<span class="pln"> <span class="pun">姓名为两个字</span></span></span>
<span class="kwd">select<span class="pln"> <span class="pun">*<span class="pln"> <span class="kwd">from<span class="pln"> employee <span class="kwd">where<span class="pln"> name like <span class="str">'__'<span class="pun">;</span></span></span></span></span></span></span></span></span></span>
<span class="pun">--<span class="pln"> <span class="pun">身份证最后为<span class="pln">X</span></span></span></span>
<span class="kwd">select<span class="pln"> <span class="pun">*<span class="pln"> <span class="kwd">from<span class="pln"> employee <span class="kwd">where<span class="pln"> idcard like <span class="str">'%X'<span class="pun">;</span></span></span></span></span></span></span></span></span></span>
聚合查询(聚合函数)
常见聚合函数:
函数功能 count 统计数量 max 最大值 min 最小值 avg 平均值 sum 求和
语法:
SELECT 聚合函数(字段列表) FROM 表名;
例:
SELECT count(id) from employee where workaddress = "广东省";
分组查询
语法:
SELECT 字段列表 FROM 表名 [ WHERE 条件 ] GROUP BY 分组字段名 [ HAVING 分组后的过滤条件 ];
where 和 having 的区别:
- 执行时机不同:where是分组之前进行过滤,不满足where条件不参与分组;having是分组后对结果进行过滤。
- 判断条件不同:where不能对聚合函数进行判断,而having可以。
例子:
<span class="pun">--<span class="pln"> <span class="pun">根据性别分组,统计男性和女性数量(只显示分组数量,不显示哪个是男哪个是女)</span></span></span>
<span class="kwd">select<span class="pln"> count<span class="pun">(*)<span class="pln"> <span class="kwd">from<span class="pln"> employee <span class="kwd">group<span class="pln"> <span class="kwd">by<span class="pln"> gender<span class="pun">;</span></span></span></span></span></span></span></span></span></span></span>
<span class="pun">--<span class="pln"> <span class="pun">根据性别分组,统计男性和女性数量</span></span></span>
<span class="kwd">select<span class="pln"> gender<span class="pun">,<span class="pln"> count<span class="pun">(*)<span class="pln"> <span class="kwd">from<span class="pln"> employee <span class="kwd">group<span class="pln"> <span class="kwd">by<span class="pln"> gender<span class="pun">;</span></span></span></span></span></span></span></span></span></span></span></span></span>
<span class="pun">--<span class="pln"> <span class="pun">根据性别分组,统计男性和女性的平均年龄</span></span></span>
<span class="kwd">select<span class="pln"> gender<span class="pun">,<span class="pln"> avg<span class="pun">(<span class="pln">age<span class="pun">)<span class="pln"> <span class="kwd">from<span class="pln"> employee <span class="kwd">group<span class="pln"> <span class="kwd">by<span class="pln"> gender<span class="pun">;</span></span></span></span></span></span></span></span></span></span></span></span></span></span></span>
<span class="pun">--<span class="pln"> <span class="pun">年龄小于<span class="lit">45<span class="pun">,并根据工作地址分组</span></span></span></span></span>
<span class="kwd">select<span class="pln"> workaddress<span class="pun">,<span class="pln"> count<span class="pun">(*)<span class="pln"> <span class="kwd">from<span class="pln"> employee <span class="kwd">where<span class="pln"> age <span class="pun"><<span class="pln"> <span class="lit">45<span class="pln"> <span class="kwd">group<span class="pln"> <span class="kwd">by<span class="pln"> workaddress<span class="pun">;</span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span>
<span class="pun">--<span class="pln"> <span class="pun">年龄小于<span class="lit">45<span class="pun">,并根据工作地址分组,获取员工数量大于等于<span class="lit">3<span class="pun">的工作地址</span></span></span></span></span></span></span>
<span class="kwd">select<span class="pln"> workaddress<span class="pun">,<span class="pln"> count<span class="pun">(*)<span class="pln"> address_count <span class="kwd">from<span class="pln"> employee <span class="kwd">where<span class="pln"> age <span class="pun"><<span class="pln"> <span class="lit">45<span class="pln"> <span class="kwd">group<span class="pln"> <span class="kwd">by<span class="pln"> workaddress having address_count <span class="pun">>=<span class="pln"> <span class="lit">3<span class="pun">;</span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span>
注意事项
- 执行顺序:where > 聚合函数 > having
- 分组之后,查询的字段一般为聚合函数和分组字段,查询其他字段无任何意义
排序查询
语法:
SELECT 字段列表 FROM 表名 ORDER BY 字段1 排序方式1, 字段2 排序方式2;
排序方式:
- ASC: 升序(默认)
- DESC: 降序
例子:
<span class="pun">--<span class="pln"> <span class="pun">根据年龄升序排序</span></span></span>
<span class="pln">SELECT <span class="pun">*<span class="pln"> FROM employee ORDER BY age ASC<span class="pun">;</span></span></span></span>
<span class="pln">SELECT <span class="pun">*<span class="pln"> FROM employee ORDER BY age<span class="pun">;</span></span></span></span>
<span class="pun">--<span class="pln"> <span class="pun">两字段排序,根据年龄升序排序,入职时间降序排序</span></span></span>
<span class="pln">SELECT <span class="pun">*<span class="pln"> FROM employee ORDER BY age ASC<span class="pun">,<span class="pln"> entrydate DESC<span class="pun">;</span></span></span></span></span></span>
注意事项
如果是多字段排序,当第一个字段值相同时,才会根据第二个字段进行排序
分页查询
语法:
SELECT 字段列表 FROM 表名 LIMIT 起始索引, 查询记录数;
例子:
<span class="pun">--<span class="pln"> <span class="pun">查询第一页数据,展示<span class="lit">10<span class="pun">条</span></span></span></span></span>
<span class="pln">SELECT <span class="pun">*<span class="pln"> FROM employee LIMIT <span class="lit">0<span class="pun">,<span class="pln"> <span class="lit">10<span class="pun">;</span></span></span></span></span></span></span></span>
<span class="pun">--<span class="pln"> <span class="pun">查询第二页</span></span></span>
<span class="pln">SELECT <span class="pun">*<span class="pln"> FROM employee LIMIT <span class="lit">10<span class="pun">,<span class="pln"> <span class="lit">10<span class="pun">;</span></span></span></span></span></span></span></span>
注意事项
- 起始索引从0开始,起始索引 = (查询页码 – 1) * 每页显示记录数
- 分页查询是数据库的方言,不同数据库有不同实现,MySQL是LIMIT
- 如果查询的是第一页数据,起始索引可以省略,直接简写 LIMIT 10
DQL执行顺序
FROM -> WHERE -> GROUP BY -> SELECT -> ORDER BY -> LIMIT
DCL
管理用户
查询用户:
<span class="pln">USE mysql<span class="pun">;</span></span>
<span class="pln">SELECT <span class="pun">*<span class="pln"> FROM user<span class="pun">;</span></span></span></span>
创建用户:
CREATE USER '用户名'@'主机名' IDENTIFIED BY '密码';
修改用户密码:
ALTER USER '用户名'@'主机名' IDENTIFIED WITH mysql_native_password BY '新密码';
删除用户:
DROP USER '用户名'@'主机名';
例子:
<span class="pun">--<span class="pln"> <span class="pun">创建用户<span class="pln">test<span class="pun">,只能在当前主机<span class="pln">localhost<span class="pun">访问</span></span></span></span></span></span></span>
<span class="pln">create user <span class="str">'test'<span class="pun">@<span class="str">'localhost'<span class="pln"> identified <span class="kwd">by<span class="pln"> <span class="str">'123456'<span class="pun">;</span></span></span></span></span></span></span></span></span>
<span class="pun">--<span class="pln"> <span class="pun">创建用户<span class="pln">test<span class="pun">,能在任意主机访问</span></span></span></span></span>
<span class="pln">create user <span class="str">'test'<span class="pun">@<span class="str">'%'<span class="pln"> identified <span class="kwd">by<span class="pln"> <span class="str">'123456'<span class="pun">;</span></span></span></span></span></span></span></span></span>
<span class="pln">create user <span class="str">'test'<span class="pln"> identified <span class="kwd">by<span class="pln"> <span class="str">'123456'<span class="pun">;</span></span></span></span></span></span></span>
<span class="pun">--<span class="pln"> <span class="pun">修改密码</span></span></span>
<span class="pln">alter user <span class="str">'test'<span class="pun">@<span class="str">'localhost'<span class="pln"> identified <span class="kwd">with<span class="pln"> mysql_native_password <span class="kwd">by<span class="pln"> <span class="str">'1234'<span class="pun">;</span></span></span></span></span></span></span></span></span></span></span>
<span class="pun">--<span class="pln"> <span class="pun">删除用户</span></span></span>
<span class="pln">drop user <span class="str">'test'<span class="pun">@<span class="str">'localhost'<span class="pun">;</span></span></span></span></span>
注意事项
- 主机名可以使用 % 通配
权限控制
常用权限:
权限说明 ALL, ALL PRIVILEGES 所有权限 SELECT 查询数据 INSERT 插入数据 UPDATE 修改数据 DELETE 删除数据 ALTER 修改表 DROP 删除数据库/表/视图 CREATE 创建数据库/表
更多权限请看权限一览表
查询权限:
SHOW GRANTS FOR '用户名'@'主机名';
授予权限:
GRANT 权限列表 ON 数据库名.表名 TO '用户名'@'主机名';
撤销权限:
REVOKE 权限列表 ON 数据库名.表名 FROM '用户名'@'主机名';
注意事项
- 多个权限用逗号分隔
- 授权时,数据库名和表名可以用 * 进行通配,代表所有
函数
- 字符串函数
- 数值函数
- 日期函数
- 流程函数
字符串函数
常用函数:
函数功能 CONCAT(s1, s2, …, sn) 字符串拼接,将s1, s2, …, sn拼接成一个字符串 LOWER(str) 将字符串全部转为小写 UPPER(str) 将字符串全部转为大写 LPAD(str, n, pad) 左填充,用字符串pad对str的左边进行填充,达到n个字符串长度 RPAD(str, n, pad) 右填充,用字符串pad对str的右边进行填充,达到n个字符串长度 TRIM(str) 去掉字符串头部和尾部的空格 SUBSTRING(str, start, len) 返回从字符串str从start位置起的len个长度的字符串
使用示例:
<span class="pun">--<span class="pln"> <span class="pun">拼接</span></span></span>
<span class="pln">SELECT CONCAT<span class="pun">(<span class="str">'Hello'<span class="pun">,<span class="pln"> <span class="str">'World'<span class="pun">);</span></span></span></span></span></span></span>
<span class="pun">--<span class="pln"> <span class="pun">小写</span></span></span>
<span class="pln">SELECT LOWER<span class="pun">(<span class="str">'Hello'<span class="pun">);</span></span></span></span>
<span class="pun">--<span class="pln"> <span class="pun">大写</span></span></span>
<span class="pln">SELECT UPPER<span class="pun">(<span class="str">'Hello'<span class="pun">);</span></span></span></span>
<span class="pun">--<span class="pln"> <span class="pun">左填充</span></span></span>
<span class="pln">SELECT LPAD<span class="pun">(<span class="str">'01'<span class="pun">,<span class="pln"> <span class="lit">5<span class="pun">,<span class="pln"> <span class="str">'-'<span class="pun">);</span></span></span></span></span></span></span></span></span></span>
<span class="pun">--<span class="pln"> <span class="pun">右填充</span></span></span>
<span class="pln">SELECT RPAD<span class="pun">(<span class="str">'01'<span class="pun">,<span class="pln"> <span class="lit">5<span class="pun">,<span class="pln"> <span class="str">'-'<span class="pun">);</span></span></span></span></span></span></span></span></span></span>
<span class="pun">--<span class="pln"> <span class="pun">去除空格</span></span></span>
<span class="pln">SELECT TRIM<span class="pun">(<span class="str">' Hello World '<span class="pun">);</span></span></span></span>
<span class="pun">--<span class="pln"> <span class="pun">切片(起始索引为<span class="lit">1<span class="pun">)</span></span></span></span></span>
<span class="pln">SELECT SUBSTRING<span class="pun">(<span class="str">'Hello World'<span class="pun">,<span class="pln"> <span class="lit">1<span class="pun">,<span class="pln"> <span class="lit">5<span class="pun">);</span></span></span></span></span></span></span></span></span></span>
数值函数
常见函数:
函数功能 CEIL(x) 向上取整 FLOOR(x) 向下取整 MOD(x, y) 返回x/y的模 RAND() 返回0~1内的随机数 ROUND(x, y) 求参数x的四舍五入值,保留y位小数
日期函数
常用函数:
函数功能 CURDATE() 返回当前日期 CURTIME() 返回当前时间 NOW() 返回当前日期和时间 YEAR(date) 获取指定date的年份 MONTH(date) 获取指定date的月份 DAY(date) 获取指定date的日期 DATE_ADD(date, INTERVAL expr type) 返回一个日期/时间值加上一个时间间隔expr后的时间值 DATEDIFF(date1, date2) 返回起始时间date1和结束时间date2之间的天数
例子:
<span class="pun">--<span class="pln"> DATE_ADD</span></span>
<span class="pln">SELECT DATE_ADD<span class="pun">(<span class="pln">NOW<span class="pun">(),<span class="pln"> INTERVAL <span class="lit">70<span class="pln"> YEAR<span class="pun">);</span></span></span></span></span></span></span></span>
流程函数
常用函数:
函数功能 IF(value, t, f) 如果value为true,则返回t,否则返回f IFNULL(value1, value2) 如果value1不为空,返回value1,否则返回value2 CASE WHEN [ val1 ] THEN [ res1 ] … ELSE [ default ] END 如果val1为true,返回res1,… 否则返回default默认值 CASE [ expr ] WHEN [ val1 ] THEN [ res1 ] … ELSE [ default ] END 如果expr的值等于val1,返回res1,… 否则返回default默认值
例子:
<span class="kwd">select</span>
<span class="pln"> name<span class="pun">,</span></span>
<span class="pln"> <span class="pun">(<span class="kwd">case<span class="pln"> <span class="kwd">when<span class="pln"> age <span class="pun">><span class="pln"> <span class="lit">30<span class="pln"> <span class="kwd">then<span class="pln"> <span class="str">'中年'<span class="pln"> <span class="kwd">else<span class="pln"> <span class="str">'青年'<span class="pln"> <span class="kwd">end<span class="pun">)</span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span>
<span class="kwd">from<span class="pln"> employee<span class="pun">;</span></span></span>
<span class="kwd">select</span>
<span class="pln"> name<span class="pun">,</span></span>
<span class="pln"> <span class="pun">(<span class="kwd">case<span class="pln"> workaddress <span class="kwd">when<span class="pln"> <span class="str">'北京市'<span class="pln"> <span class="kwd">then<span class="pln"> <span class="str">'一线城市'<span class="pln"> <span class="kwd">when<span class="pln"> <span class="str">'上海市'<span class="pln"> <span class="kwd">then<span class="pln"> <span class="str">'一线城市'<span class="pln"> <span class="kwd">else<span class="pln"> <span class="str">'二线城市'<span class="pln"> <span class="kwd">end<span class="pun">)<span class="pln"> <span class="kwd">as<span class="pln"> <span class="str">'工作地址'</span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span>
<span class="kwd">from<span class="pln"> employee<span class="pun">;</span></span></span>
约束
分类:
约束描述关键字 非空约束 限制该字段的数据不能为null NOT NULL 唯一约束 保证该字段的所有数据都是唯一、不重复的 UNIQUE 主键约束 主键是一行数据的唯一标识,要求非空且唯一 PRIMARY KEY 默认约束 保存数据时,如果未指定该字段的值,则采用默认值 DEFAULT 检查约束(8.0.1版本后) 保证字段值满足某一个条件 CHECK 外键约束 用来让两张图的数据之间建立连接,保证数据的一致性和完整性 FOREIGN KEY
约束是作用于表中字段上的,可以再创建表/修改表的时候添加约束。
常用约束
约束条件关键字 主键 PRIMARY KEY 自动增长 AUTO_INCREMENT 不为空 NOT NULL 唯一 UNIQUE 逻辑条件 CHECK 默认值 DEFAULT
例子:
<span class="pln">create table user<span class="pun">(</span></span>
<span class="pln"> id <span class="kwd">int<span class="pln"> primary key auto_increment<span class="pun">,</span></span></span></span>
<span class="pln"> name varchar<span class="pun">(<span class="lit">10<span class="pun">)<span class="pln"> <span class="kwd">not<span class="pln"> <span class="kwd">null<span class="pln"> unique<span class="pun">,</span></span></span></span></span></span></span></span></span></span>
<span class="pln"> age <span class="kwd">int<span class="pln"> check<span class="pun">(<span class="pln">age <span class="pun">><span class="pln"> <span class="lit">0<span class="pln"> <span class="kwd">and<span class="pln"> age <span class="pun"><<span class="pln"> <span class="lit">120<span class="pun">),</span></span></span></span></span></span></span></span></span></span></span></span></span></span></span>
<span class="pln"> status <span class="kwd">char<span class="pun">(<span class="lit">1<span class="pun">)<span class="pln"> <span class="kwd">default<span class="pln"> <span class="str">'1'<span class="pun">,</span></span></span></span></span></span></span></span></span></span>
<span class="pln"> gender <span class="kwd">char<span class="pun">(<span class="lit">1<span class="pun">)</span></span></span></span></span>
<span class="pun">);</span>
外键约束
添加外键:
<span class="pln">CREATE TABLE <span class="pun">表名(</span></span>
<span class="pln"> <span class="pun">字段名<span class="pln"> <span class="pun">字段类型,</span></span></span></span>
<span class="pln"> <span class="pun">...</span></span>
<span class="pln"> <span class="pun">[<span class="pln">CONSTRAINT<span class="pun">]<span class="pln"> <span class="pun">[外键名称]<span class="pln"> FOREIGN KEY<span class="pun">(外键字段名)<span class="pln"> REFERENCES <span class="pun">主表(主表列名)</span></span></span></span></span></span></span></span></span></span>
<span class="pun">);</span>
<span class="pln">ALTER TABLE <span class="pun">表名<span class="pln"> ADD CONSTRAINT <span class="pun">外键名称<span class="pln"> FOREIGN KEY <span class="pun">(外键字段名)<span class="pln"> REFERENCES <span class="pun">主表(主表列名);</span></span></span></span></span></span></span></span>
<span class="pun">--<span class="pln"> <span class="pun">例子</span></span></span>
<span class="pln">alter table emp add constraint fk_emp_dept_id foreign key<span class="pun">(<span class="pln">dept_id<span class="pun">)<span class="pln"> references dept<span class="pun">(<span class="pln">id<span class="pun">);</span></span></span></span></span></span></span></span>
删除外键:
ALTER TABLE 表名 DROP FOREIGN KEY 外键名;
删除/更新行为
行为说明 NO ACTION 当在父表中删除/更新对应记录时,首先检查该记录是否有对应外键,如果有则不允许删除/更新(与RESTRICT一致) RESTRICT 当在父表中删除/更新对应记录时,首先检查该记录是否有对应外键,如果有则不允许删除/更新(与NO ACTION一致) CASCADE 当在父表中删除/更新对应记录时,首先检查该记录是否有对应外键,如果有则也删除/更新外键在子表中的记录 SET NULL 当在父表中删除/更新对应记录时,首先检查该记录是否有对应外键,如果有则设置子表中该外键值为null(要求该外键允许为null) SET DEFAULT 父表有变更时,子表将外键设为一个默认值(Innodb不支持)
更改删除/更新行为:
ALTER TABLE 表名 ADD CONSTRAINT 外键名称 FOREIGN KEY (外键字段) REFERENCES 主表名(主表字段名) ON UPDATE 行为 ON DELETE 行为;
多表查询
多表关系
- 一对多(多对一)
- 多对多
- 一对一
一对多
案例:部门与员工
关系:一个部门对应多个员工,一个员工对应一个部门
实现:在多的一方建立外键,指向一的一方的主键
多对多
案例:学生与课程
关系:一个学生可以选多门课程,一门课程也可以供多个学生选修
实现:建立第三张中间表,中间表至少包含两个外键,分别关联两方主键
一对一
案例:用户与用户详情
关系:一对一关系,多用于单表拆分,将一张表的基础字段放在一张表中,其他详情字段放在另一张表中,以提升操作效率
实现:在任意一方加入外键,关联另外一方的主键,并且设置外键为唯一的(UNIQUE)
查询
合并查询(笛卡尔积,会展示所有组合结果):
select * from employee, dept;
笛卡尔积:两个集合A集合和B集合的所有组合情况(在多表查询时,需要消除无效的笛卡尔积)
消除无效笛卡尔积:
select * from employee, dept where employee.dept = dept.id;
内连接查询
内连接查询的是两张表交集的部分
隐式内连接:
SELECT 字段列表 FROM 表1, 表2 WHERE 条件 ...;
显式内连接:
SELECT 字段列表 FROM 表1 [ INNER ] JOIN 表2 ON 连接条件 ...;
显式性能比隐式高
例子:
<span class="pun">--<span class="pln"> <span class="pun">查询员工姓名,及关联的部门的名称</span></span></span>
<span class="pun">--<span class="pln"> <span class="pun">隐式</span></span></span>
<span class="kwd">select<span class="pln"> e<span class="pun">.<span class="pln">name<span class="pun">,<span class="pln"> d<span class="pun">.<span class="pln">name <span class="kwd">from<span class="pln"> employee <span class="kwd">as<span class="pln"> e<span class="pun">,<span class="pln"> dept <span class="kwd">as<span class="pln"> d <span class="kwd">where<span class="pln"> e<span class="pun">.<span class="pln">dept <span class="pun">=<span class="pln"> d<span class="pun">.<span class="pln">id<span class="pun">;</span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span>
<span class="pun">--<span class="pln"> <span class="pun">显式</span></span></span>
<span class="kwd">select<span class="pln"> e<span class="pun">.<span class="pln">name<span class="pun">,<span class="pln"> d<span class="pun">.<span class="pln">name <span class="kwd">from<span class="pln"> employee <span class="kwd">as<span class="pln"> e inner join dept <span class="kwd">as<span class="pln"> d on e<span class="pun">.<span class="pln">dept <span class="pun">=<span class="pln"> d<span class="pun">.<span class="pln">id<span class="pun">;</span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span>
外连接查询
左外连接:
查询左表所有数据,以及两张表交集部分数据
SELECT 字段列表 FROM 表1 LEFT [ OUTER ] JOIN 表2 ON 条件 ...;
相当于查询表1的所有数据,包含表1和表2交集部分数据
右外连接:
查询右表所有数据,以及两张表交集部分数据
SELECT 字段列表 FROM 表1 RIGHT [ OUTER ] JOIN 表2 ON 条件 ...;
例子:
<span class="pun">--<span class="pln"> <span class="pun">左</span></span></span>
<span class="kwd">select<span class="pln"> e<span class="pun">.*,<span class="pln"> d<span class="pun">.<span class="pln">name <span class="kwd">from<span class="pln"> employee <span class="kwd">as<span class="pln"> e left outer join dept <span class="kwd">as<span class="pln"> d on e<span class="pun">.<span class="pln">dept <span class="pun">=<span class="pln"> d<span class="pun">.<span class="pln">id<span class="pun">;</span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span>
<span class="kwd">select<span class="pln"> d<span class="pun">.<span class="pln">name<span class="pun">,<span class="pln"> e<span class="pun">.*<span class="pln"> <span class="kwd">from<span class="pln"> dept d left outer join emp e on e<span class="pun">.<span class="pln">dept <span class="pun">=<span class="pln"> d<span class="pun">.<span class="pln">id<span class="pun">;<span class="pln"> <span class="pun">--<span class="pln"> <span class="pun">这条语句与下面的语句效果一样</span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span>
<span class="pun">--<span class="pln"> <span class="pun">右</span></span></span>
<span class="kwd">select<span class="pln"> d<span class="pun">.<span class="pln">name<span class="pun">,<span class="pln"> e<span class="pun">.*<span class="pln"> <span class="kwd">from<span class="pln"> employee <span class="kwd">as<span class="pln"> e right outer join dept <span class="kwd">as<span class="pln"> d on e<span class="pun">.<span class="pln">dept <span class="pun">=<span class="pln"> d<span class="pun">.<span class="pln">id<span class="pun">;</span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span>
左连接可以查询到没有dept的employee,右连接可以查询到没有employee的dept
自连接查询
当前表与自身的连接查询,自连接必须使用表别名
语法:
SELECT 字段列表 FROM 表A 别名A JOIN 表A 别名B ON 条件 ...;
自连接查询,可以是内连接查询,也可以是外连接查询
例子:
<span class="pun">--<span class="pln"> <span class="pun">查询员工及其所属领导的名字</span></span></span>
<span class="kwd">select<span class="pln"> a<span class="pun">.<span class="pln">name<span class="pun">,<span class="pln"> b<span class="pun">.<span class="pln">name <span class="kwd">from<span class="pln"> employee a<span class="pun">,<span class="pln"> employee b <span class="kwd">where<span class="pln"> a<span class="pun">.<span class="pln">manager <span class="pun">=<span class="pln"> b<span class="pun">.<span class="pln">id<span class="pun">;</span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span>
<span class="pun">--<span class="pln"> <span class="pun">没有领导的也查询出来</span></span></span>
<span class="kwd">select<span class="pln"> a<span class="pun">.<span class="pln">name<span class="pun">,<span class="pln"> b<span class="pun">.<span class="pln">name <span class="kwd">from<span class="pln"> employee a left join employee b on a<span class="pun">.<span class="pln">manager <span class="pun">=<span class="pln"> b<span class="pun">.<span class="pln">id<span class="pun">;</span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span>
联合查询 union, union all
把多次查询的结果合并,形成一个新的查询集
语法:
<span class="pln">SELECT <span class="pun">字段列表<span class="pln"> FROM <span class="pun">表<span class="pln">A <span class="pun">...</span></span></span></span></span></span>
<span class="pln">UNION <span class="pun">[<span class="pln">ALL<span class="pun">]</span></span></span></span>
<span class="pln">SELECT <span class="pun">字段列表<span class="pln"> FROM <span class="pun">表<span class="pln">B <span class="pun">...</span></span></span></span></span></span>
注意事项
- UNION ALL 会有重复结果,UNION 不会
- 联合查询比使用or效率高,不会使索引失效
子查询
SQL语句中嵌套SELECT语句,称谓嵌套查询,又称子查询。
SELECT * FROM t1 WHERE column1 = ( SELECT column1 FROM t2);
子查询外部的语句可以是 INSERT / UPDATE / DELETE / SELECT 的任何一个
根据子查询结果可以分为:
- 标量子查询(子查询结果为单个值)
- 列子查询(子查询结果为一列)
- 行子查询(子查询结果为一行)
- 表子查询(子查询结果为多行多列)
根据子查询位置可分为:
- WHERE 之后
- FROM 之后
- SELECT 之后
标量子查询
子查询返回的结果是单个值(数字、字符串、日期等)。
常用操作符:- < > > >= <
Original: https://www.cnblogs.com/Cxxxd/p/16727741.html
Author: Cxxxd
Title: mysql
原创文章受到原创版权保护。转载请注明出处:https://www.johngo689.com/713909/
转载文章受原作者版权保护。转载请注明原作者出处!