Mysql-基础

数据模型

1. 可以建立多个数据库，各数据库隔离
2. 数据库中可以建立多各关系型数据表

SQL分类

DDL

数据定义语言，用来定义数据库对象（数据库、表、字段）

数据库操作

查询

查询所有数据库

SHOW DATABSE;

查询当前数据库

SELECT DATABASE();

创建

创建数据库

CREATA DATABASE [IF NOT EXISTS] 数据库名 [DEFAULT CHARSET 字符集] [COLLATE 排序规则];

删除

DROP DATABASE [IF EXISTS] 数据库名;

使用

USE 数据库名;

数据表操作

查询

查询当前数库所有表

SHOW TABLES;

查询表结构

DESC 表名;

查询指定表的建表语句

SHOW CREATE TABLE 表名;

创建

创建表结构

CREATE TABLE 表名(
	FIELDNAME1 FIELDTYPE [COMMENT 字段1注释],
	FIELDNAME2 FIELDTYPE [COMMENT 字段2注释],
	FIELDNAME3 FIELDTYPE [COMMENT 字段3注释],
	...
	FIELDNAMEn FIELDTYPE [COMMENT 字段n注释],
	PRIMARY KEY (FIELDNAME1)
)[COMMENT 表注释];

数据类型

注意：字符串类型中指定的字符串长度n限制字符串长度，并不是实际占用的字符串位数，不区分中、英文。

修改

操作表

---

修改表名称

RENAME TABLE 旧表名称 to 新表名称;

删除表

DROP TABLE 表名称;

删除表，并重新创建-用于清空表数据

TRUNCATE TABLE 表名;

操作字段

---

添加字段

ALTER TABLE 表名 ADD 字段名 类型(长度) [COMMENT 字段描述] [约束];

修改字段类型

ALTER TABLE 表名 MODIFY 字段名 新数据类型(长度);

修改字段名和字段类型

ALTER TABLE 表名 CHANGE 旧字段名 新字段名 数据类型(长度) [COMMENT 字段描述] [约束];

删除字段

ALTER TABLE 表名 DROP 字段名;

DML

数据操作语言，用来对数据表中的数据进行增、删、改

添加数据

给指定的字段添加数据

INSERT INTO 表名(字段名1,字段名1,字段名1,...) VALUES(值1,值2,值3,...);

给全部字段添加数字据

INSERT INTO 表名 VALUES(值1,值2,值3,...);

批量添加数据

INSERT INTO 表名(字段名1,字段名1,字段名1,...) VALUES
(值1,值2,值3,...),
(值1,值2,值3,...),
(值1,值2,值3,...),
...
(值1,值2,值3,...);

修改数据

UPDATE 表名 SET 字段1=值1,字段2=值2,... [WHERE 条件语句];

删除数据

DELETE FROM 表名 [WHERE 条件语句];

DQL

数据查询语言，用来查询数据表的记录
基本语法结构

SELECT 
	字段列表
FROM
	表名列表
WHERE 
	条件
GROUP BY
	分组字段-配合SUM,COUNT,MAX,MIN,AVG等聚集函数使用
HAVING
	分组条件-对使用了聚集函数的字段进行过滤
ORDER BY
	排序字段-需要同时使用分组和排序时，必须先分组再排序
LIMIT
	分页数量
OFFSET 
	分页起始行 LIMIT与OFFSET一般搭配使用
;

条件语句关键字

针对NOT、BETWEEN、IN、LINKE关键字不会走索引，考虑到性能问题谨慎针对索引字段使用

DCL

数据控制语言，用来创建数据库用户、控制数据表的访问权限
用户管理

---

查询用户

USE mysql;
SELECT * FROM user;

创建用户

CREATE USER '用户名'@'主机名' IDENTIFIED BY '密码';

修改用户密码

ALTER USER '用户名'@'主机名' IDENTIFIE WITH mysql_native_password BY '新密码';

删除用户

DROP USER '用户名'@'主机名';

权限管理

---

查询权限

SHOW GRANTS FOR '用户名'@'主机名';

授予权限

GRANT 权限列表 ON 数据库名.表名 TO '用户名'@'主机名';

撤销权限

REVOKE 权限列表 ON 数据库名.表名 FROM '用户名'@'主机名';

函数

内置函数

约束

主键约束不可以为空，唯一约束能为空。

多表查询

关联关系

• 一对多（多对一）
  针对主外键表结构，从表中以主表的主键为外键，主表中的多条数据唯一对应主表的一条数据
• 多对多
  针对多业务表关联，如职位表与职员表，一个职位包含多个职员，一个职员可以拥有多个职位
• 一对一
  一般用于大业务表的垂直拆分，如用户表包含基本个人信息（姓名、性别、年龄、联系方式）和教育背景的辅助信息（学历、专业、毕业时间），可以将这些信息分别拆为个人信息表和教育背景表，两张表数据一对一。

关联查询

内连接

隐式内连接

SELECT * FROM TABLE1,TABLE2 WHERE CONDIION;

显示内连接

SELECT * FROM TABLE1 [INNER] JOIN TABLE2 ON 连接条件;

使用条件时查询两张表的交集，只查询笛卡尔积中关联匹配成功的数据

不使用条件时查询两张表的并集

外连接

左外连接

---

SELECT * FROM TABLE1 LEFT [OUTER] JOIN TABLE2 ON 条件;

完全包含左表数据，关联查出右表数据（对于左:右=1:n，查询数量可能会大于左表数据集）

右外连接

---

SELECT * FROM TABLE1 RIGHT [OUTER] JOIN TABLE2 ON 条件;

完全包含右表数据，关联查出左表数据（对于左:右=n:1，查询数量可能会大于右表数据集）

自连接

对同一张表做连接查询，一般用于树形结构的递归查询，如组织架构

SELECT * FROM TABLE1 AS T1,TABLE1 T2 WHERE T1.parent_id = T2.id;

子查询

查询语句的条件语句需要嵌套其它查询结果集

标量子查询

子查询中只会查询一条记录中的一列

SELECT * FROM TABLE1 WHERE COLUMN1 = (SELECT COLUMN1 FROM TABLE2 WHERE CONDITION);
SELECT * FROM TABLE1 WHERE COLUMN1 <> (SELECT COLUMN1 FROM TABLE2 WHERE CONDITION);
SELECT * FROM TABLE1 WHERE COLUMN1 > (SELECT COLUMN1 FROM TABLE2 WHERE CONDITION);
SELECT * FROM TABLE1 WHERE COLUMN1 < (SELECT COLUMN1 FROM TABLE2 WHERE CONDITION);

列子查询

子查询中会返回多条记录，每条记录只包含一列

SELECT * FROM TABLE1 WHERE COLUMN1 IN (SELECT COLUMN1 FROM TABLE2 WHERE CONDITION);

行子查询

子查询中返回的一条记录包含多列

SELECT * FROM TABLE1 WHERE (COLUMN1,COLUMN2) = (SELECT COLUMN1,COLUMN2 FROM TABLE2 WHERE CONDITION);

表子查询

查询表为子表结果集

SELECT * FROM (SELECT COLUMN1,COLUMN2 FROM TABLE2 WHERE CONDITION) T1 WHERE CONDITION;

联合查询

UNION\UNION ALL

对多各查询结果集做并集，UNION ALL不支持去重，要求多个结果集的查询列数和字段类型一致。

## 去重
SELECT field1,field2 FROM table1
UNION 
SELECT field1,field2 FROM table2;

## 不去重
SELECT field1,field2 FROM table1
UNION ALL
SELECT field1,field2 FROM table2;

INTERSECT

对多各查询结果集做交集，要求多个结果集的查询列数和字段类型一致。

SELECT field1,field2 FROM table1 
INTERSECT 
SELECT field1,field2 FROM table2;

EXCEPT

对多各查询结果集做差集，要求多个结果集的查询列数和字段类型一致。

SELECT field1,field2 FROM table1 
EXCEPT 
SELECT field1,field2 FROM table2;

事务

事务是一组操作的集合，它是一个不可分割的工作单位，事务会把所有的操作性作为一个整体一起向系统提交或撤销。即这些操作要么同时成功，要么同时失败。

操作

mysql全局事务操作

• 查看/设置事务提交方式

SELECT @@autocommit;
SET @@autocommit=0; //1-开启自动提交，0-关闭自动提交，使用手动提交

• 提交事务

COMMIT;

• 回滚事务

ROLLBACK;

客户端会话级事务操作

• 开启事务

start TRANSACTION;

• 提交事务

COMMIT;

• 回滚事务

ROLLBACK;

事务的四大特性

事务的四大特性是ACID.

• 原子性（Atomicity）:事务是不可分割的最小操作单元，要么全部成功，要么全部失败。
• 一致性（Consistency）:事务完成时，必须使所有的数据都保持一致。（比如转账，付款方和收款放余额的更新结果必须一致）
• 隔离性（Isolation）:数据库系统提供的隔离机制，保证事务在不受外部并发操作影响的独立环境下运行（不同事务不允许同时更新同一条数据）。
• 持久性（Durability）:事务一旦提交或回滚成功，它对数据库中数据的改变是永久的。

并发事务问题

• 脏读：-当前事务读取到了其它事务已修改未提交的数据。
  一个事务读取到另一个事务还没提交的数据。A事务更新某一条数据未提交，B事务读取到了A事务更新的结果。
• 不可重复读：-同一事务内多次读取同一条数据的结果不一致。
  一个事务先后读取同一条记录，当两次读取的数据不同。A事务更新某一条数据未提交，B事务读取的数据为A更新前的数据，此时A事务提交，B事务再次读取的数据为A事务更新后的数据；B事务在事务内两次读取同一条数据的结果不一致。
• 幻读：-同一事务读取数据时不存在，插入同一数据时发现数据已存在。
  一个事务按照条件查询数据时，没有对应的数据行，但是在插入数据时，又发现这行数据已经存在了，好像出现了“幻影”。
  如A，B两个事务均要对数据表做先根据id查询数据是否存在，不存在则将id对应的数据插入表中的操作；

1. A,B同时根据id读取数据，均返回空；
2. A事务将id对应的数据插入数据表，并插入成功，提交事务；
3. B事务将id对应的数据插入数据表，此时出现“主键冲突”的异常，B事务回滚；
   该规程中B事务读取id为空的过程为幻读。

事务隔离级别-解决事务并发问题

隔离级别分类

根据事务不同的隔离级别，解决脏读、不可重复读、幻读问题。

mysql默认-可重复读 Oracle默认-读已提交

• 读已提交-不能解决可重复读过程模拟：

1. 事务A读取数据
2. 事务B修改数据并提交
3. 事务A再次读取数据

• 读已提交-不能解决不可重复读过程模拟：

1. 事务A读取数据
2. 事务B修改数据并提交
3. 事务A再次读取数据

• 读已提交-不能解决幻读过程模拟：

1. 事务A读取数据
2. 事务B插入数据并提交
3. 事务A再次插入数据出现“唯一键冲突”

• 可重复读实现原理
  在可重复读的隔离级别中，mysql会为每个事务单独建立一个数据快照，各事务之间的快照相互隔离，其它事务提交也不会当前事务快照中的数据，因此解决了脏读和不可重复读的问题。
  对数据进行update和insert操作时，会添加表数据共享锁，其它事务只能读，不能写；当写操作事务提交后，其它事务的数据快照会更新。

隔离级别操作

• 查看事务隔离级别

SELECT @@TRANSACTION_ISOLATION;

• 设置事务隔离级别

SET [SESSION|GLOBAL] TRANSACTION ISOLATION LEVEL {READ UNONMMITED|READ COMMITED|REPEATABLE READ|SERIALIZABLE}