MySQL基础篇
MySQL概述
| 名称 | 解释 | 简称 |
|---|---|---|
| 数据库 | 将数据有组织的存储的仓库 | DataBase(DB) |
| 数据库管理系统 | 操纵和管理数据库的大型软件 | DBMS |
| SQL | 操作关系型数据库的编程语言，定义了一套操作关系型数据库的统一标准 | Structured Query Language |
工程师通过SQL操作DBMS，间接管理数据库中的数据
Oracle、MySQL等系统是关系型数据库管理系统，支持使用SQL语言进行数据库操作。
关系型数据库：建立在关系模型基础上，由多张相互连接的二维表组成的数据库。
使用MySQL
安装：这里推荐安装Mysql8版本，可以参考这篇文章安装MySQL
启动与停止：
net start mysql80
net stop mysql80
客户端连接：mysql [-h 127.0.0.1] [-P 3306] -u root -p
SQL
- SQL语句可以单行或多行书写，以分号结尾
- MySQL中的SQL语句不区分大小写，但是关键字推荐使用大写
- 注释：
- 单行注释：
-- 注释内容或# 注释内容(MySQL特有) - 多行注释：
/*注释内容*/
- 单行注释：
SQL数值类型
数字类型
无符号数TINYINT UNSIGNED，DECIMAL类型DECIMAL(x,y)，x代表数字的整体长度=整数长度+小数长度，y代表小数的长度
字符串类型
CHAR为定长字符串，开多少空间就占用多少空间，VARCHAR为变长字符串，根据存储的数据动态计算空间。
长文本数据分为BLOB和TEXT类型，BLOB表示二进制数据，可以是音频、视频、图像等数据，可以将其存储在数据库中，但是在开发时很少这么做，一般采用专门的文件服务器进行存储。
日期类型
SQL分类
| 分类 | 全称 | 说明 |
|---|---|---|
| DDL | Data Definition Language | 数据定义语言，定义数据库对象（数据库，表，字段） |
| DML | Data Manipulation Language | 数据操作语言，对数据库表中的数据进行增删改 |
| DQL | Data Query Language | 数据查询语言，查询表中的记录 |
| DCL | Data Control Language | 数据控制语言，创建数据库用户、控制访问权限 |
DDL语句
[...]部分为可选参数
数据库操作
- 查询所有数据库：
SHOW DATABASES; - 查询当前数据库：
SELECT DATABASE(); - 创建：
CREATE DATABASE [IF NOT EXISTS] 数据库名 [DEFAULT CHARSET 字符集] [COLLATE 排序规则]; - 删除：
DROP DATABASE [IF EXISTS] 数据库名; - 使用：
USE 数据库名;
表操作
- 查询数据库所有表：
SHOW TABLES; - 查询表结构：
DESE 表名; - 查询指定表的建表语句：
SHOW CREATE TABLE 表名; - 创建表：
CREATE TABLE 表名(
字段1 字段1类型[COMMENT 字段1注释],
字段2 字段2类型[COMMENT 字段2注释],
…..,
字段n 字段n类型[COMMENT 字段n注释]
)[COMMENT 表注释];
- 添加字段：
ALTER TABLE 表名 ADD 字段名 类型 [comment] [约束]; - 修改数据类型：
ALTER TABLE 表名 MODIFY 字段名 新类型; - 修改字段名和类型：
ALTER TABLE 表名 CHANGE 旧字段名 新字段名 类型 [comment] [约束]; - 修改表名：
ALTER TABLE 表名 RENAME TO 新表名; - 删除字段：
ALTER TABLE 表名 DROP 字段名; - 删除表：
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…）…;
INSERT INTO 表名 VALUES（值1，值2…）,（值1，值2…）…;
- 修改数据：
UPDATE 表名 SET 字段1=值1, 字段2=值2...[WHERE 条件]; - 删除数据：
DELETE FROM 表名 [WHERE 条件];
DQL语句
查询多个字段：
SELECT 字段1,字段2,... FROM 表名;查询全部字段：
SELECT * FROM 表名;设置别名：
SELECT 字段1 AS 别名1,字段2 AS 别名2,... FROM 表名;去重：
SELECT DISTINCT 字段列表 FROM 表名;条件查询：
SELECT 字段列表 FROM WHERE 条件列表;此处的条件列表与其他编程语言的条件判断基本一致，个别不一样的中常用的有
LIKE 占位符表示模糊匹配（_匹配单个字符，%匹配任意字符）使用聚合函数查询：
SELECT 聚合函数(字段列表) FROM 表名;
常用的聚合函数有：count(),max(),min(),avg(),sum(),按列进行计算
分组查询：
SELECT 字段列表 FROM 表名 [WHERE 分组前过滤条件] GROUP BY 分组字段名 [HAVING 分组后的过滤条件];（其中having中可以使用聚合函数过滤，where中不可以，并且分组之后查询的字段一般为分组字段和聚合函数）排序查询：
SELECT 字段列表 FROM 表名 ORDER BY 字段1 排序方式1,字段2 排序方式2;排序方式有两种：ASC：升序（默认），DESC：降序。如果是多字段排序，当且仅当第一个字段相同时按照第二个排。
分页查询：
SELECT 字段列表 FROM 表名 LIMIT 起始索引,查询记录数;- 索引从0开始编号，起始索引=（查询页面-1）*每页的记录数
- 分页查询时数据库的方言，不同的数据库中实现语句不同，MySQL中是LIMIT
- 如果查的是第一页的数据，起始索引可以省略
DQL语句编写顺序与执行顺序
DCL语句
- 查询用户：
SELECT * FROM user; - 创建用户：
CREATE USER '用户名'@'主机名' IDENTIFIED BY '密码'; - 修改用户密码：
ALTER USER '用户名'@'主机名' IDENTIFIED WITH mysql_native_password '新密码'; - 删除用户：
DROP USER '用户名'@'主机名'; - 查询用户权限：
SHOW GRANTS FOR '用户名'@'主机名'; - 授予权限：
GRANT 权限列表 ON 数据库名.表名 TO '用户名'@'主机名'; - 撤销权限：
REVOKE 权限列表 ON 数据库名.表名 FROM '用户名'@'主机名';
常用的权限
| 权限 | 说明 |
|---|---|
| all/all privileges | 所有权限 |
| select | 查询数据 |
| insert | 插入数据 |
| update | 修改数据 |
| delete | 删除数据 |
| alter | 修改表 |
| drop | 删除数据库、表、视图 |
| create | 创建数据库、表 |
函数
在数据库中，函数指的是可以直接被另一段程序调用的程序或代码。
字符串函数
| 函数 | 功能 |
|---|---|
| CONCAT(S1,S2,…,Sn) | 字符串拼接，将给定的字符串拼成一个 |
| LOWER(str) | str全部字符转成小写 |
| UPPER(str) | str全部字符转成大写 |
| LPAD(str,n,pad) | 左填充，用字符串pad对str的左边进行填充直到长度到达n |
| RPAD(str,n,pad) | 右填充，用字符串pad对str的右边进行填充直到长度到达n |
| TRIM(str) | 去掉收尾空格 |
| SUBSTRING(str,start,len) | 返回字符串str从start开始len长度的字符串，索引从1开始 |
| LENGTH(str) | 返回字符串长度 |
数值函数
| 函数 | 功能 |
|---|---|
| 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之间间隔的天数，date1-date2 |
流程控制函数
| 函数 | 功能 |
|---|---|
| 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,…否则返回默认值 |
| CASE [expr] WHEN [val1] THEN [res1]…ELSE[default]END | 如果expr的值等于val1，返回res1,…否则返回默认值 |
约束
约束是作用于表中字段上的规则，在创建表或修改表的时候添加约束，用于限制存储在表中的数据。可以保证数据的正确，有效性和完整性。
约束的分类
添加约束时只需要在后面直接添加关键字即可，不同约束使用空格隔开
外键约束
外键用来让两张表的数据之间建立连接，从而保证数据的一致性和完整性，具有外键的称为子表/从表，外键所关联的表称为父表/主表。
- 建立外键关联
# 可以在创建表时直接指明外键 |
- 删除外键：
ALTER TABLE 表名 DROP FOREIGN KEY 外键名称;
外键约束
使用时在外键命令后面添加ON UPDATE 行为 ON DELETE 行为
多表查询
多表查询即从多张表中查询数据。可以分为连接查询和子查询
连接查询
内连接
外连接
左外连接
右外连接
自连接
子查询
多表关系
一对一：
- 案例：个人与个人信息
- 关系：每个人有每个人自己的个人信息
- 实现：将基础字段放在一张表中，详情信息放在另一张表中，在任意一方加入外键，关联另一方的主键，并设置外键为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 表1 别名1 JOIN 表2 别名2 ON 条件;
子查询
SQL语句中嵌套SELECT语句称为嵌套查询，也叫子查询，子查询外部的语句可以是增删改查任意一个
例如：SELECT 字段列表 FROM 表1 WHERE 字段1=(SELECT 字段1 FROM 表2);
根据子查询结果不同，分为：
标量子查询（子查询结果为单个值）：直接嵌套查询即可
列子查询：
常用的操作符：IN(指定范围内),NOT IN(不在指定范围内),ANY(返回列表中任意一个满足条件即可),SOME(同ANY),ALL(条件必须全部满足)
行子查询:
常用的操作符：=,<>,IN,NOT IN
表子查询（多行多列）:
常用的操作符：IN
联合查询
联合查询使用union(合并后去重)，union all(合并)命令，可以把多次查询的结果合并起来，形成一个新的查询结果集
SELECT 字段列表 FROM 表1 ... |
联合查询中多张表的列数必须保持一致，字段类型也要一致。
事务
事务是一组操作的集合，是不可分割的工作单位，事务会把所有的操作作为一个整体一起向系统提交或者撤销操作请求。默认MySQL的事务是自动提交的，即执行DML语句时，MySQL会立即隐式的提交事务。
事务操作
操作事务有两种方式：1.关闭事务的自动提交 2.通过指令显式开启事务
- 查看事务提交方式：
SELECT @@autocommit; - 设置事务提交方式(1为自动提交，0为手动提交）：
SET @@autocommit=0; - 开启事务：
START TRANSACTION; - 提交事务：
COMMIT; - 回滚事务：
ROLLBACK;
事务四大特性ACID
- 原子性A：事务不可分割，事务中的操作要么同时成功，要么同时失败。
- 一致性C：事务完成时，必须使所有的数据都保持一致状态
- 隔离性I：数据库系统提供的隔离机制，保证事务在不收外部并发操作影响的独立环境下运行
- 持久性D：事务一旦提交或回滚，对数据的改变就是永久的
并发事务问题
多个并发事务在执行过程当中所出现的问题
| 问题 | 描述 |
|---|---|
| 脏读 | 一个事务读取到另一个事务还没有提交的数据 |
| 不可重复读 | 一个事物先后读取到同一条数据，但两次读取的数据不同 |
| 幻读 | 一个事物按照条件查询数据时，没有对应的数据行，但是在插入数据时，又发现这行数据已经存在 |
事务隔离级别
√表示该隔离级别会出现对应问题，从上到下隔离级别由低到高，性能由高到低
| 隔离级别 | 脏读 | 不可重复读 | 幻读 |
|---|---|---|---|
| Read uncommitted | √ | √ | √ |
| Read committed | x | √ | √ |
| Repeatable Read | x | x | √ |
| Serializable | x | x | x |
MySQL中的默认隔离级别是Repeatable Read，Oracle是Read committed
- 查看事务隔离级别：
SELECT @@transaction_isolation; - 设置事务隔离级别：
SET [SESSION|GLOBAL] TRANSACTION ISOLATION LEVEL {隔离级别};