总纲
MySQL概述
相关概念
- 数据库:存储数据的仓库,数据是有组织的进行存储
- 数据库管理系统:操纵和管理数据库的大型软件
- SQL:操作关系型数据库的编程语言,定义了一套操作关系型数据库统一标准
- 关系型数据库:建立在关系模型基础上,由多张相互连接的二维表组成的数据库
数据模型
SQL
通用语法
- 可以单行也可以多行,以分号结尾
- 可以使用空格缩进来增强语句的可读性
- 不区分大小写,关键字建议用大写
- 单行注释 – 或 #
- 多行注释 /* */
分类
- DDL:Data Definition Language 数据定义语言,用来定义数据库、表、字段
- DML:Data Manipulation Language 数据操作语言,用来对数据库表中的数据进行增删改
- DQL:Data Query Language数据查询语言,用来查询数据表中的记录
- DCL:Data Control Language数据控制语言,用来创建数据库用户、控制数据库的访问权限
DDL
- 查询所有数据库
show databases; - 查询当前数据库
select database(); - 创建数据库
create database databasename; - 删除数据库
drop database databasename; - 使用数据库
use databaseName - 查询当前数据库所有表
show tables; - 查询表结构
desc 表名; - 查询指定表的建表语句
show create table 表名; - 创建表结构
CREATE TABLE 表名(
字段1 字段1类型 [ COMMENT 字段1注释 ],
字段2 字段2类型 [COMMENT 字段2注释 ],
字段3 字段3类型 [COMMENT 字段3注释 ],
…
字段n 字段n类型 [COMMENT 字段n注释 ]
) [ COMMENT 表注释 ] ;
注意: […] 内为可选参数,最后一个字段后面没有逗号 - 数据类型
数值类型
字符串类型
日期类型
- 添加字段
ALTER TABLE 表名 ADD 字段名 类型(长度) [COMMENT 注释][约束]; - 修改数据类型
ALTER TABLE 表名 MODIFY 字段名 新数据类型 (长度); - 修改字段名和字段类型
ALTER TABLE 表名 CHANGE 旧字段名 新字段名 类型 (长度) [ 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 条件 ] ;
- 修改语句的条件可以有也可以没有,如果没有条件,则会修改整张表的所有数据
删除数据
- DELETE FROM 表名 [ WHERE 条件 ] ;
- 条件可以有也可以没有,没有条件会删除整张表所有数据
- 不能删除某一个字段的值,可以使用update将该字段设为null
- 当进行删除全部数据时,datagrip会提示我们,询问是否确认删除
DQL
基础查询
- SELECT 字段1, 字段2, 字段3 … FROM 表名 ;
- SELECT * FROM 表名 ;
- *号代表查询所有字段,在实际开发中尽量少用,不直观、影响效率
- 字段设置别名
- SELECT 字段1 [ AS 别名1 ] , 字段2 [ AS 别名2 ] … FROM 表名;
- SELECT 字段1 [ 别名1 ] , 字段2 [ 别名2 ] … FROM 表名;
- 去除重复记录
- SELECT DISTINCT 字段列表 FROM 表名;
条件查询
- 语法
select 字段列表 from 表名 where 条件列表; - 条件
聚合函数
- 将一列数据作为一个整体,进行纵向计算
- 语法
SELECT 聚合函数(字段列表) FROM 表名 ; - 注意:NULL值不参与所有聚合函数的计算
分组查询
- 语法
SELECT 字段列表 FROM 表名 [ WHERE 条件 ] GROUP BY 分组字段名 [ HAVING 分组
后过滤条件 ]; - where和having的区别
- 执行时机不同,where分组之前进行过滤,不满足where条件,不参与分组,而having是分组之后进行对分组结果进行过滤
- 判断条件不同,where不能对聚合函数进行判断,而having可以
- 注意事项
- 分组之后,查询的字段一般为聚合函数和分组字段,查询其他字段没有意义
- 执行顺序 where > 聚合函数 > having
- 支持多字段分组
排序查询
- order by 字段名 asc 升序,默认是升序,可以不写
- order by 字段名 desc,降序
- 可以多个字段排序 order by 字段1 asc, 字段2 desc
分页查询
- 语法
SELECT 字段列表 FROM 表名 LIMIT 起始索引, 查询记录数 ; - 起始索引从0开始,起始索引(查询页码 - 1) * 每页显式的记录数
- 分页查询是数据库的方言,不同的数据库实现方式不同
- 如果查询是第一页数据,起始索引可以省略
执行顺序
1.from
2.where
3.group by
4.having
5.select
6.order by
7.limit
DCL
管理用户
- 查询用户
select * from mysql.user; - 创建用户
CREATE USER ‘用户名’@‘主机名’ IDENTIFIED BY ‘密码’; - 删除用户
DROP USER ‘用户名’@‘主机名’ ; - 需要用用户名@主机名的方式,来唯一标识一个用户
- 主机名可以使用%通配
权限控制
- 查询权限
SHOW GRANTS FOR ‘用户名’@‘主机名’ ; - 授予权限
GRANT 权限列表 ON 数据库名.表名 TO ‘用户名’@‘主机名’; - 撤销权限
REVOKE 权限列表 ON 数据库名.表名 FROM ‘用户名’@‘主机名’;
函数
字符串函数
数值函数
日期函数
流程函数
约束
概述
- 约束是作用于表中字段上的规则,用于限制存储在表中的数据
- 目的是保证数据库中的数据正确性、有效性、完整性
- 分类
外键约束
- 用来让两张表的数据之间建立连接,从而保证数据的一致性和完整性
- 新增外键约束
ALTER TABLE 表名 ADD CONSTRAINT 外键名称 FOREIGN KEY (外键字段名)
REFERENCES 主表 (主表列名) ; - 删除外键约束
ALTER TABLE 表名 DROP FOREIGN KEY 外键名称;
删除/更新行为
- 语法
alter table emp add constraint fk_emp_dept_id foreign key (dept_id) references
dept(id) on update cascade on delete cascade ;
多表查询
多表关系
- 一对多(多的一方建立外键,指向一的一方的主键)
- 多对多(建立第三张表,中间表至少包含两个外键,分别关联两方主键)
- 一对一 (用于单表拆分,在任意一方加入外键,关联另一方的主键,并且设置外键为唯一的(UNIQUE))
概述
- 连接查询
- 内连接:相当于查询A、B交集部分数据
- 外连接:
- 左外连接:查询左表所有数据,以及两张表交集部分
- 右外连接:查询右表所有数据,以及两张表交集部分
- 自连接:当前表与自身表连接的查询,自连接必须使用表别名
- 子查询
内连接
- 内连接查询的是两张表交集部分的数据,也就是绿色部分的数据
- 隐式内连接
SELECT 字段列表 FROM 表1 , 表2 WHERE 条件 … ; - 显式内连接
SELECT 字段列表 FROM 表1 [ INNER ] JOIN 表2 ON 连接条件 … ;
外连接
- 左外连接
SELECT 字段列表 FROM 表1 LEFT [ OUTER ] JOIN 表2 ON 条件 … ; - 右外连接
SELECT 字段列表 FROM 表1 RIGHT [ OUTER ] JOIN 表2 ON 条件 … ;
自连接
- SELECT 字段列表 FROM 表A 别名A JOIN 表A 别名B ON 条件 … ;
- 自连接可以是内连接也可以是外连接
联合查询
- union查询就是把多次查询的结果合并起来,形成一个新的查询结果集
- 对于联合查询的多张表的列数必须保持一致,字段类型也必须保持一致
- union all会将全部的数据直接合并在一起,union会对合并之后的数据去重
子查询
- SQL语句中嵌套SELECT语句
- SELECT * FROM t1 WHERE column1 = ( SELECT column1 FROM t2 );
- 子查询外部的语句可以是INSERT/ UPDATE/ DELETE/SELECT
- 根据子查询结果不同,分为:
- 标量子查询(子查询结果为单个值)
- 列子查询(子查询结果为一列)
- 行子查询(子查询结果为一行)
- 表子查询(子查询结果为多行多列)
- 根据子查询位置分为:
- where之后
- from之后
- select之后
标量子查询
- 子查询返回的结果是单个值(数字、字符串、日期)
- 常用操作符 = <> > >= < <=
列子查询
- 子查询返回的结果是一列(可以是多行)
- 常用操作符 IN, NOT IN, ALL, SOME, ANY
行子查询
- 子查询返回的结果是一行(可以是多列)
- 常用操作符 = ,<>, IN, NOT IN
表子查询
- 子查询返回的结果是多行多列的
- 常用操作符 in
- 还会将子查询作为一张表与其他表连接,子查询放在from之后
事务
事务简介
- 一组操作的组合,是一个不可分割的工作单位,事务会把所有的操作作为一个整体一起向系统提交或撤销操作请求,即这些操作要么同时成功,要么同时失败
- Mysql的事务是自动提交的,当支持完一条DML语句时,MySQL会立即隐式的提交事务
事务操作
- 查看、设置事务提交方式
- select @@autocommit;
- set @@autocommit = 0;
- 提交事务
- commit;
- 回滚事务
- rollback;
- 开启事务
- start transaction 或 begin
事务四大特性
并发事务问题
事务隔离级别
- 查看事务隔离级别
- select @@TRANSACTION_ISOLATION;
- 设置隔离级别
- SET [ SESSION | GLOBAL ] TRANSACTION ISOLATION LEVEL { READ UNCOMMITTED |
READ COMMITTED | REPEATABLE READ | SERIALIZABLE }
- SET [ SESSION | GLOBAL ] TRANSACTION ISOLATION LEVEL { READ UNCOMMITTED |