SQL分类
- DDL 数据定义语言
- DML 数据操作语言
- DQL 数据查询语言
- DCL 数据控制语言
DDL
查询 语句
1. 查询所有数据库 SHOW DATABASE;
2. 查询当前数据库 SELECT DATABASE();
3. 创建 CREATE DABABASE [IF NOT EXISTS] 数据库名 [DEFAULT CHARSET 字符集] [COLLATE 排序规则];
4. 删除 DROP DATABASE [IF EXISTS] 数据库名;
5. 使用 USE 数据库名;
表操作 查询
1. 查询当前数据库所有表 SHOW TABLES;
2. 查询表结构 DESC 表名;
3. 查询指定表的建表语句 SHOW CREATE TABLE 表名;
表操作 创建
CREATE TABLE 表名(
字段1 字段1类型 [COMMENT 字段1注释],
.......
字段n 字段n类型 [COMMENT 字段n注释]
) [COMMENT 表注释];
-- 最后一个字段后没有逗号
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-iBBldxlO-1662515156721)(C:\Users\fanvil\AppData\Roaming\Typora\typora-user-images\1661949715898.png)]
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-21mrLC1C-1662515156722)(C:\Users\fanvil\AppData\Roaming\Typora\typora-user-images\1661949756632.png)]
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-W2ARPel9-1662515156722)(C:\Users\fanvil\AppData\Roaming\Typora\typora-user-images\1661949805528.png)]
表操作 修改
1. 添加字段 ALTER TABLE 表名 ADD 字段名(长度) [COMMENT 注释] [约束];
2. 修改字段名和字段类型 ALTER TABLE 表名 CHANGE 旧字段名 新字段名 类型(长度) [COMMENT 注释] [约束];
3. 删除字段 ALTER TABLE 表名 DROP 字段名;
4. 修改表名 ALTER TABLE 表名 RENAME TO 新表名;
表操作 删除
1. 删除表 DROP TABLE [IF EXISTS] 表名;
2. 删除指定表,并重新创建该表 TRUNCATE TABLE 表名;
DML
1. 添加数据
1. 给指定字段添加数据 INSERT INTO 表名(字段名1, 字段名2,...) VALUES(值1, 值2,...);
2. 给全部字段添加数据 INSERT INTO 表名 VALUES(值1, 值2,...);
3. 批量添加数据
1. INSERT INTO 表名 (字段名1, 字段名2,...) VALUES(值1, 值2,...), (值1, 值2,...), (值1, 值2,...);
2. INSERT INTO 表名 VALUES(值1, 值2,...), (值1, 值2,...), (值1, 值2,...);
4. **字符串和日期类型数据应包含在引号中**
2. 修改数据
UPDATE 表名 SET 字段名1=值1, 字段名2=值2,...[WHERE 条件];
3. 删除数据
1. DELETE FROM 表名 [WHERE 条件]
2. **不能删除某一个字段的值,可以使用UPDATE**
DQL
SELECT
字段列表
FROM
表名列名
WHERE
条件类表
GROUP BY
分组字段列表
HAVING
分组后条件列表
ORDER BY
排序字段列表
LIMIT
分页参数
基本查询
1. SELECT 字段1, 字段2,...FROM 表名;
2. SELECT * FROM 表名;
3. 设置别名 SELECT 字段1 [AS 别名], 字段2[AS 别名2] ... FROM 表名;
4. 去除重复记录 SELECT DISTINCT 字段列表 FROM 表名;
条件查询
SELECT 字段列表 FROM 表名 WHERE 条件列表;
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-BAdmBEDX-1662515156722)(C:\Users\fanvil\AppData\Roaming\Typora\typora-user-images\1661997897878.png)]
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-cSgWZjt9-1662515156723)(C:\Users\fanvil\AppData\Roaming\Typora\typora-user-images\1661997924226.png)]
聚合函数
-
将一列作为一个整体,进行纵向计算
-
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-9Z1ebBqT-1662515156723)(C:\Users\fanvil\AppData\Roaming\Typora\typora-user-images\1661998468873.png)]
-
SELECT 聚合函数(字段列表) FROM 表名; -- NULL值不参与聚合运算
分组查询
SELECT 字段列名 FROM 表名 [WHERE 条件] GROUP BY 分组字段名 [HAVING 分组后过滤条件]
根据性别分组,统计男性员工 和 女性员工的数量
SELECT 性别字段名, COUNT(*) FROM 表名 GROUP BY 性别字段名;
查询年龄小于45的员工,并根据工作地址分组,获取员工数量大于等于3的工作地址
SELECT 工作地址字段名, COUNT(*) FROM 表名 WHERE 年龄字段名<45 GROUP BY 工作地址字段名 HAVING COUNT( *) >= 3;
/*
WHERE与HAVING区别
1.执行时机不同:WHERE是分组之间进行过滤,不满足WHERE条件,不参与分组;而HAVING是分组之后对结果进行过滤
2.判断条件不同:WHERE不能对聚合函数进行判断,而HAVING可以
*
/*
执行顺序:WHERE > 聚合函数 > HAVING
分组后之后,查询字段一般为聚合函数和分组字段,查询其他字段无意义
*/
分组查询
1. SELECT 字段列表 FROM 表名 ORDER BY 字段1 排序方式1, 字段2 排序方式2;
2. 排序方式
1. ASC 升序(默认)
2. DESC 降序
-- 若为多字段排序,当第一个字段值相同时,才会根据第二个字段进行排序
分页查询
SELECT 字段列表 FROM 表名 LIMIT 起始索引, 查询记录数;
/*
1.起始索引从0开始,起始索引 = (查询页码-1) * 每页显示记录数
2.分页查询是数据方言,不同数据库有不同的实现,MySQL中是LIMIT
3.如果查询的是第一页数据,起始索引可以省略,直接简写为LIMIT 10
*/
执行顺序
SELECT 4
字段列表
FROM 1
表名列表
WHERE 2
条件类表
GROUP BY 3-1
分组字段列表
HAVING 3-2
分组后条件列表
ORDER BY 5
排序字段列表
LIMIT 6
分页参数
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-KCJlJT92-1662515156723)(C:\Users\fanvil\AppData\Roaming\Typora\typora-user-images\1662004481608.png)]
DCL
管理用户
1. 查询用户
USE mysql;
SELECT * FROM user;
2. 创建用户 CREATE USER '用户名'@'主机名' IDENTIFIED BY '密码';
3. 修改用户密码 ALTER USER '用户名'@'主机名' IDENTIFIED WITH mysql_native_password BY '新密码'
4. 删除用户 DROP USER '用户名'@'主机名';
-- 主机名可以使用%通配
权限控制
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-LdBtlS1z-1662515156723)(C:\Users\fanvil\AppData\Roaming\Typora\typora-user-images\1662005446061.png)]
1. 查询权限 SHOW GRANTS FOR '用户名'@'主机名';
2. 授予权限 GRANT 权限列表 ON 数据库名.表名 TO '用户名'@'主机名';
3. 撤销权限 REVOKE 权限列表 ON 数据库名.表名 FROM '用户名'@'主机名';
函数
字符串函数
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-aVLlyhMq-1662515156724)(C:\Users\fanvil\AppData\Roaming\Typora\typora-user-images\1662010756906.png)]
数值函数
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-kELjsT00-1662515156724)(C:\Users\fanvil\AppData\Roaming\Typora\typora-user-images\1662010980440.png)]
日期函数
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-5Oguyfhh-1662515156724)(C:\Users\fanvil\AppData\Roaming\Typora\typora-user-images\1662011108736.png)]
流程函数
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-Gv9jkG2Y-1662515156725)(C:\Users\fanvil\AppData\Roaming\Typora\typora-user-images\1662011267481.png)]
约束
-
用户显示存储数据
-
保证数据库中数据的正确性、有效性和完整性
-
分类
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-UXDFzHKl-1662515156725)(C:\Users\fanvil\AppData\Roaming\Typora\typora-user-images\1662011632449.png)]
主键约束自增:AUTO_INCREMENT
-
可在创建表/修改表时添加约束
-
外键约束
1. 添加外键 1. CREATE TABLE 表名( 字段名 数据类型, ... [CONSTRAINT] [外键名称] FOREING KEY(外键字段名) REFERENCES 主表 (主表列名)); 2. ALTER TABLE 表名 ADD CONSTRAINT 外键名称 FOREIGN KEY(外键字段名) REFERENCES 主表 (主表列名); 2. 删除外键 ALTER TABLE 表名 DROP FOREIGN KEY 外键名称;
-
外键删除、更新行为
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-DDa4UCVI-1662515156725)(C:\Users\fanvil\AppData\Roaming\Typora\typora-user-images\1662012566037.png)]
-- 在更新的时候级联,在删除的时候级联 ALTER TABLE 表名 ADD CONSTRAINT 外键名称 FOREIGN KEY (外键字段) REFERENCES 主表名(主表字段名) ON UPDATE CASCADE ON DELETE CASCADE
多表查询
多表关系
-
一对多或多对一
实现:在多的一方建立外键,指向一的一方的主键
-
多对多
实现:建立中间表,中间表至少包含两个外键,分别关联两方主键
-
一对一
- 关系:多用于单表拆分,将一张表基础字段放在一张表中,其他详情字段放在另一张表中,以提升操作效率
- 实现: 在任意一方加入外键,关联另一方的主键,并设置外键为唯一的(UNIQUE)
多表查询
连接查询
1. 内连接
1. 隐式连接 SELECT 字段列表 FROM 表1, 表2 WHERE 连接条件 ...;
2. 显示连接 SELECT 字段列表 表1 [INNER] JOIN 表2 ON 连接条件... WHERE 查询条件...;
-- 内连接查询的是两张表的交集部分
2. 外连接
1. 左外连接 SELECT 字段列表 FROM 表1 LEFT [OUTER] JOIN 表2 ON 条件...;
-- 相当于查询表1(左表)的所有数据包含表1和表2交集部分的数据
2. 右外连接 SELECT 字段列表 FROM 表1 RIGNT [OUTER] JOIN 表2 ON 条件...;
-- 相当于查询表2(右表)的所有数据包含表1和表2交集部分的数据
3. 自连接 SELECT 字段列表 FROM 表名 别名A JOIN 表名 别名B ON 条件...;
-- 自连接查询,可以是内连接查询,也可以是外连接查询
联合查询
1. UNION, UNION ALL
2. 对于UNION查询,就是把多次查询的结果合并起来,形成一个新的查询结果集
3. SELECT 字段列表 FROM 表A...
UNION [ALL]
SELECT 字段列表 FROM 表B...;
-- 对于联合查询的多张表的列数必须保持一致,字段类型也需要保持一致
-- UNION ALL 会将全部的数据直接合并在一起,UNION会对合并之后的数据去重
子查询
-
SQL语句找那个嵌套SELECT语句,称为嵌套查询,又称子查询
SELECT * FROM T1 WHERE column1 = (SELECT column1 FROM t2) -- 子查询外部的语句可以是INSERT/UPDATE/DELETE/SELECT的任何一个
-
根据子查询的结果,分为
-
标量子查询:子查询的结果为单个值
常用操作符:= <> > >= < <=
-
列子查询:子查询结果为一列
常用操作符:IN NOT IN ANY SOME ALL
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-9lL9ChGC-1662515156726)(C:\Users\fanvil\AppData\Roaming\Typora\typora-user-images\1662017258903.png)]
-
行子查询:子查询的结果为一行,可以为多列
常用操作符: = <> IN NOT IN
-
表子查询:子查询结果为多行多列
常用操作符:IN
-
-
根据子查询的位置,分为:WHERE之后、FROM之后、SELECT之后
事务
-
是一组操作的集合,是一个不可分割的工作单位,事务会把所有的操作作为一个整体一起向系统提交或撤销操作请求,即这些操作要么同时成功,要么同时失败
- 开启事务
- 抛异常,回滚事务
- 提交事务
-
默认MySQL的事务是自动提交的,也及时说,当执行一条DML语句,MySQL会理解隐式提交事务
-
事务操作
1. 查看/设置事务提交方式 1. SELECT @@autocommit; 2. SET @@autocmmit = 0; -- 手动提交 2. 提交事务 COMMIT; 3. 回滚事务 ROLLBACK;
-
事务四大特性
- 原子性:事务是不可分割的最小单元,要么全成功,要么全失败
- 一致性:事务完成时,必须使所有数据都保持一致状态
- 隔离性:数据库系统提供的隔离机制,保证事务在不受外部并发操作影响的独立换进下运行
- 持久性:事务一旦提交或回滚,它对数据库中的数据的该表是永久的
-
并发事务问题
- 脏读:一个事务读到另外一个事务还没有提交的数据
- 不可重复度:一个事务先后读取同一条记录,但两次读取的数据不同,称之为不可重复读
- 幻读:一个事务按照条件查询数据时,没有对应的数据行,但是在插入数据时,又发现了这行数据已经存在,好像出现了“幻影”
-
事务隔离级别
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-czlJJTtx-1662515156726)(C:\Users\fanvil\AppData\Roaming\Typora\typora-user-images\1662022478913.png)]
1. 查看事务隔离级别 SELECT @@TRANSACTION_ISOLATION 2. 设置事务隔离级别 SET [SESSION | GLOBAL] TRANSACTION ISOLATION LEVEL [READ UNCOMMITTED | READ COMMITTED | REPATABLE READ | SERIALIZABLE]