[大数据]MySQL笔记

参考资料：MySQL 5.7 从入门到实战 作者：张婷
b站视频：老杜带你学_mysql入门基础（mysql基础视频+数据库实战)

所有的操作都是通过命令去做的

连接 mysql

service mysql start  # 启动
service mysql stop  # 停止
service mysql restart  # 重启
service mysql status  # 服务状态
mysql -u root -p	# 进入 MySQL

数据库操作

注意：SQL 命令结束时都要带有 ; 符号。

查看数据库

SHOW DATABASES;

创建数据库

CREATE DATABASE 数据库名;

• 创建 utf8 编码的数据库（待测试）

CREATE DATABASE 数据库名 DEFAULT CHARSET utf8;

选择数据库！！！

操作数据表前需要选择指定数据库；

USE 数据库名;

例子：

mysql> USE db_name;
Database changed

删除数据库

DROP DATABASE 数据库名;

数据表操作

创建表

格式：
CREATE TABLE 表名(
字段名 数据类型 约束条件,
字段名 数据类型 约束条件,
字段名 数据类型 约束条件,
...
字段名 数据类型 约束条件,
);

例子：

mysql> create table t_class(
    -> classno int,
    -> cname varchar(20),
    -> loc varchar(40),
    -> stucount int);
Query OK, 0 rows affected		出现这行才是创建成功。

查看表结构

DESCRIBE 表名;

查看表详情定义

SHOW CREATE TABLE 表名;

删除表

DROP TABLE 表名;

修改表

修改表名

ALTER TABLE 原表名 RENAME 新表名;

例子：

mysql> alter table t_class rename ccclass;
Query OK, 0 rows affected		输出这个就是成功了。

增加字段

在表最后一个位置增加

ALTER TABLE 表名 ADD 字段名 数据类型;

例子：在 ccclass 表最后新增 zhushi 字段。

mysql> ALTER TABLE CCCLASS ADD zhushi varchar(10);
Query OK, 0 rows affected		成功。
Records: 0  Duplicates: 0  Warnings: 0

在表的第一个位置增加

ALTER TABLE 表名 ADD 字段名 数据类型 FIRST;

在表得指定字段之后增加

ALTER TABLE 表名 ADD 字段名 数据类型 AFTER 已存在的字段;

然后就会在已存在的字段后面新增字段了。

例子：在 ccclass 表的 loc 字段后面新增 hasi 字段，int 类型。

mysql> ALTER TABLE ccclass add hasi int after loc;
Query OK, 0 rows affected		成功。
Records: 0  Duplicates: 0  Warnings: 0

删除字段

ALTER TABLE 表名 DROP 字段名;

修改字段数据类型

ALTER TABLE 表名 MODIFY 需要修改的字段名 需要修改的数据类型;

例子：修改 ccclass 表的 hasi 字段为 varchar(10) 类型。

mysql> ALTER TABLE ccclass MODIFY hasi varchar(10);
Query OK, 0 rows affected		成功。
Records: 0  Duplicates: 0  Warnings: 0

修改字段名

ALTER TABLE 表名 CHANGE 原字段名 新字段名 原字段名的数据类型;

同时修改字段的名称和类型

ALTER TABLE 表名 CHANGE 原字段名 新字段名 新字段类型;

你没看错，这行命令和上一行是一样的。

修改字段的顺序

ALTER TABLE 表名 MODIFY 需要调整的字段 字段的数据类型 FIRST;			将字段调整到表的第一个位置。
ALTER TABLE 表名 MODIFY 需要调整的字段 字段的数据类型 AFTER xx;		将字段调整到 xx 字段后面。

表的约束

约束一般是在创建表的时候用到的。

设置字段为非空（NOT NULL）

CREATE TABLE 表名(
字段名 字段类型 NOT NULL,
...
);

设置字段默认值（DEFAULT）

CREATE TABLE 表名(
字段名 字段类型 DEFAULT '默认值',
);

注意：默认值的值是有引号包着的。

设置字段唯一（UNIQUE）

CREATE TABLE 表名(
字段名 字段类型 UNIQUE,
...
);

设置主键（PRIMARY KEY）

单字段主键：

CREATE TABLE 表名(
字段名 字段类型 PRIMARY KEY,
...
);

多字段主键：

CREATE TABLE 表名(
字段名1 字段类型,
字段名2 字段类型,
...
CONSTRAINT pk_主键名1_主键名2_主键名3 PRIMARY KEY(字段名1,字段名2,字段名3)
);

设置字段自增（AUTO_INCREMENT）

CREATE TABLE 表名(
字段名 字段类型 AUTO_INCREMENT,
...
);

设置表外键（FOREIGN KEY）

• 外键可以为 null 吗？答：可以。
• 外键的字段必须是关联表的主键吗？答：不一定是主键，但必须是唯一性的东西（unique约束）。

CREATE TABLE 表名(
字段名 字段类型,
cc INT,
...
CONSTRAINT fk_cc FOREIGN KEY(要设置外键的字段)
REFERENCES 父表名(父表的主键名)
);

提示：设置外键时，子表中所设的外键字段的数据类型必须和父表中所关联的字段的数据类型一样，否则就会报错。

比如，两个字段都是 INT 类型。

例子：亲测

-- 父表
CREATE TABLE t_class(
	cid INT AUTO_INCREMENT PRIMARY KEY,  -- 自增主键
	name VARCHAR(100)
);

-- 子表
CREATE TABLE t_student(
	sid INT AUTO_INCREMENT PRIMARY KEY ,  -- 自增主键
	classid INT,  -- 外键字段
	name VARCHAR(100),
	FOREIGN KEY(classid) REFERENCES t_class(cid)
	-- 用 FOREIGN KEY 将外键字段classid和t_class表的那个cid主键关联。
);

数据表数据操作

增（INSERT INTO）

插入一条记录

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

注意：上面的那些值需要和指定的字段一 一对应。

插入多条记录

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

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

改（UPDATE）

修改指定数据

UPDATE 表名
SET key1=value1,key2=value2,key3=value3,key4=value4
WHERE 条件;

条件是表里面满足的这个条件的数据记录都修改。这个到后面的 查 会学到。这个条件是可以用到一些算数运算符的。

如果修改单个的话，那么一般是指定 id 主键就可以了。例子：更新 id=1 的数据。

UPDATE 表名
SET key1=value1,key2=value2,key3=value3,key4=value4
WHERE id=1;

修改所有数据

UPDATE 表名
SET key1=value1,key2=value2,key3=value3,key4=value4;

删（DELETE FROM）

删除指定数据

DELETE FROM 表名 WHERE 条件;

条件一般写 id 主键。如WHERE id=1。这个条件是可以用到一些算数运算符的。

删除所有数据（清空）

DELETE FROM 表名;

单表查询

列出数据的指定字段

SELECT 字段1,字段2,字段3... FROM 表名;

比如：当有表有 6 个字段，那么可以让他只显示所有数据的 3 个字段。

列出所有数据

SELECT * FROM 表名;

注意：当数据量庞大的时候，会非常卡。

去重查询

SELECT DISTINCT 字段1 字段2... FROM 表名;

比如 字段1 有重复的数据，那么写 SELECT DISTINCT 字段1 FROM 表名; 就会对字段1进行去重并将数据列出。

条件查询

SELECT 字段1,字段2,字段3 FROM 表名 WHERE 条件;

查询指定记录

SELECT field1, field2... FROM 表名 WHERE 条件;

field 是需要显示的字段，也可以写 * 来显示所有字段。条件那里可以写各种各样的条件，在条件查询符号表里有。

例子：查询 table 表的 name 等于 muyi 字段。

SELECT * FROM table WHERE name='muyi';

IN （是否在容器里）

SELECT * FROM 表名 WHERE 判断的字段 IN(value1,value2...);

例子：将 table 表里 id 字段的值等于 10、20、30的数据显示出来。

SELECT * FROM table WHERE id IN(10,20,30)

例子：将 table 表里 id 字段的值不等于的 10、20、30的数据显示出来。

SELECT * FROM table WHERE id NOT IN(10,20,30)

如果没查到数据，那么也不会返回任何结果。

BETWEEN AND (范围条件)

• 注意：该条件值对数字类型的有效。

SELECT * FROM 表名 WHERE 字段 BETWEEN 起 AND 始;

例子：查询 table 表里的 id 字段 10 ~ 20 里的数据。

SELECT * FROM table WHERE id BATWEEN 10 AND 20;

例子：查询 table 表里的 id 字段不在 10 ~ 20 里的数据。

SELECT * FROM table WHERE id NOT BATWEEN 10 AND 20;

LIKE（模糊查询）

• 注意：MySQL 是不区分大小写的。

SELECT * FROM 表 WHERE 字段 LIKE value;

value = 需要匹配的字符串。

例子：匹配 table 表里的 name 字段以 L 开头的数据。

SELECT * FROM table WHERE name LIKE 'L%';

例子：匹配 table 表里的 name 字段里的值包含了 L 的数据。

SELECT * FROM table WHERE name LIKE '%L%';

例子：匹配 table 表里的 name 字段里的值 不包含 L 的数据。

SELECT * FROM table WHERE NOT name LIKE '%L%';

例子：匹配 table 表里的 name 字段里的值 第二个字母为 L 的数据记录。

SELECT * FROM table WHERE name LIKE '_L%';

IS NULL（空值查询）

SELECT * FROM 表 WHERE 需要判断的字段 IS NULL;

例子：查询 table 表的 name 字段是 NULL 的所有数据。

SELECT * FROM table WHERE name IS NULL;

例子：查询 table 表的 name 字段不是 NULL 的所有数据。

SELECT * FROM table WHERE name IS NOT NULL;

AND（多条件查询 与）

SELECT * FROM 表 WHERE 条件1 AND 条件2 【后面还可以有条件3条件4等等】

查询表中的满足条件1和条件2的数据。

OR（多条件查询 或）

SELECT * FROM 表 WHERE 条件1 OR 条件2 【后面还可以有更多的 OR】

查询表中满足条件1或者条件2的数据。

ORDER BY（对查询结果排序）

SELECT * FROM 表 WHERE ORDER BY 根据哪个字段排序 [ASC|DESC];

SELECT * FROM 表 WHERE 一些条件 ORDER BY 根据哪个字段排序 [ASC|DESC];

ASC 是升序，DESC 是倒序。默认情况下是 ASC 进行排序。

例子：根据 table 表的 name 字段进行 倒序排序。

SELECT * FROM table WHERE ORDER BY name DESC;

LIMIT（限制数据记录查询数量）重要！

格式：
SELECT * FROM 表 WHERE 条件 LIMIT 起始偏移量 显示的行数;

例子：翻页，从第1条数据开始，每页显示2条数据。

SELECT * FROM NAME LIMIT 0,2;    第一页数据

SELECT * FROM NAME LIMIT 2,2;    第二页数据

SELECT * FROM NAME LIMIT 4,2;    第三页数据

SELECT * FROM NAME LIMIT 6,2;    第四页数据

将 （当前页的起始偏移量 + 显示的行数 = 下一页的起始偏移量）。

如果超出了页数，会返回 Empty set。

索引

必看理论！！！

注意：这个重要！！！

注意：这个重要！！！

• MySQL ID主键会自动添加索引，所以主键查询才会那么快。
• 如果有 unique 唯一约束的话，也会自动创建索引。

什么是索引？索引是在数据库表的字段上添加的，是为了提高查询效率存在的一种机制。

举例子：索引相当于书的目录，通过目录查到大概的位置，这样就大大缩小了搜索范围。

再举个例子：第一种方式，通过全表扫描找出一条数据。第二种方式，通过索引定位到大概的一堆数据，然后再从里面搜索数据，这样可以大大缩小搜索范围，提高效率。

例子：

表数据：
id       name         age
1 	     xiaoming	  18
2        xiaohong      19
3        xiaowen       20

如果找出里面name=xiaowen的数据，那么执行：
>select * from 表 where name = "xiaowen";
如果name没有创建索引的情况下，这条sql语句会进行全表扫描。就一条一条的搜索。效率会比较低。

索引在每个字段都可以加，索引也可以有联合索引（就是两个字段当索引）。

MySQL 查找方式就两种：

• 全表扫描
• 索引

什么情况下才需要用索引

什么情况下才会考虑给字段创建索引呢？

1. 数据量庞大。（根据硬件配置来决定）
2. 该字段经常出现在where的后面，也就是说经常查询的条件使用的这个字段来过滤。
3. 该字段的值很少需要修改、删除、新增等操作，因为每次修改删除新增之后（这里的增、改、删指的是这个字段值，而不是整条数据），索引需要重新排序（索引用的是二叉树数据结构）。

索引不宜太多，因为索引也需要维护的，太多反而降低系统性能。建议使用主键或unique约束查询。

实现原理

MySQL 的索引是一个 B+Tree（二叉树）数据结构。遵循左小右大的原则存放，又采用中序遍历方式遍历取数据。

例子使用的表

-- 例子：t_student表
CREATE TABLE t_student(
	sid INT AUTO_INCREMENT PRIMARY KEY,
	name VARCHAR(100),
	age INT,
	phone INT
);

创建索引

• 单字段索引

-- 给 t_student 表的 name 字段创建索引，起名叫 t_student_name_index。
>create index t_student_name_index on t_student(name);
-- 试试用 name 字段过滤数据，就会快很多了。
>select * from t_student where name = "xiaowen";

• 联合索引、复合索引

-- 给 t_student 表的 name和age 字段创建联合索引，起名叫 t_student_name_age_index。
>create index t_student_name_age_index on t_student(name,age);
-- 注意：如果该字段创建了联合索引，查单个字段的时候，如果用了右边的字段来查，那么索引就会失效。
>select * from t_student where age = 18;  -- 索引失效
>select * from t_student where name = "xiaowen";  -- 索引有效
>select * from t_student where name = "xiaowen" or age = 18;  -- 索引有效

删除索引

-- 删除 t_student 表的 t_student_name_index 索引。
>drop index t_student_name_index on t_student;

验证索引（explain）

怎么样知道 sql 语句中是否使用了索引进行检索数据？

>explain select * from t_student where name = "xiaowen";

返回的内容解释：

id:选择标识符
select_type:表示查询的类型。
table:输出结果集的表
partitions:匹配的分区
type:表示表的连接类型
possible_keys:表示查询时，可能使用的索引
key:表示实际使用的索引
key_len:索引字段的长度
ref:列与索引的比较
rows:扫描出的行数(估算的行数)
filtered:按表条件过滤的行百分比
Extra:执行情况的描述和说明

索引失效原因

• 使用了模糊搜索（%开头）的，索引会失效，不会走索引检索。

select * from tablename where name like '%T';

• 使用了 or 的时候也会失效。但是，使用了 or 两边的字段都有索引，才会用索引。如果两边字段中其中有一边没有用到索引，那么另一个带有索引的字段，也会失效。

• 使用复合索引的时候，没有使用左侧的列查找，索引也会失效。

  • 什么叫复合索引：两个或以上的字段创建一个索引，叫复合索引。
  • 直接上例子易懂：

  -- 给 t_student 表的 name和age 字段创建联合索引，起名叫 t_student_name_age_index。
  >create index t_student_name_age_index on t_student(name,age);
  -- 注意：如果该字段创建了联合索引，查单个字段的时候，如果用了右边的字段来查，那么索引就会失效。
  >select * from t_student where age = 18;  -- 索引失效
  >select * from t_student where name = "xiaowen";  -- 索引有效
  >select * from t_student where name = "xiaowen" or age = 18;  -- 索引有效
  

• 在 where 当中索引的字段使用了运算，那么索引就会失效。

  >select * from t_student where age+1 = 18;  -- 索引失效
  -- 你没看错，就是 age+1了之后再判断该值是否=18.
  -- 字段使用了运算，就会失效。但是用来过滤字段的值加了运算，就不会失效。
  >select * from t_student where age = 18+1;  -- 索引有效
  

• 在 where 当中索引字段使用了函数，索引也会失效。

事务（transaction）（重要！）

事务的作用是保持多个操作的原子性，要么全部成功，要么全部失败。事务具有四个特征 ACID。

1. 原子性（Atomicity）

   整个事务中的所有操作，必须作为一个单元全部完成（或者全部失败）
   

2. 一致性（Consistency）

   在事务开始之前与结束之后，数据都保持一致状态。
   

3. 隔离性（Isolation）

   一个事务不会影响其他事务的运行。
   

4. 持久性（Durability）

   在事务完成以后，该事务对数据库所做的更改将永久保存到数据库中，并不会再被回滚。
   

只有 增 insert、删 delete、改 update 才有事务用，其他操作都没有。

开启事务：start transaction；
提交事务：commit；
回滚事务：rollback；

事务，是默认自动开启的，但是每次都是自动提交的，所以不能进行数据回滚。所以想组织他自动提交事务，需要先输入：start transaction;

为了保证数据的安全，切忌，一定要所有的操作完成了之后才提交事务。

操作流程

第一步：开启事务
>start transaction;
第二步：可以开始进行增删改了，注意，这里面的操作，虽然你都可以看见成果了，但是该事务再提交之前，都是可以回滚的。
...
...
...
第三步：如果操作没问题了，可以提交事务了，就算完成一个单元了。
>commit;
注意：如果操作出了问题，想回滚数据的情况下，输入：
>rollback;

使用场景

• 拿django来举例，当一个请求需要操作多次数据表数据时，比如删除了数据，然后又新增数据，然后又修改另一个表的数据，如果，在最后一步修改另一个表的数据异常时，这时事务就起作用了，可以将整个请求的操作数据回滚，这样就可以达到原子性了。要么全部成功，要么全部不做。
• 如果请求只执行一个操作（增、删、改）时，可以不用事务，但是，还是建议用上事务吧，避免后期加了哪些东西而忘记加事务了。

事务隔离级别（重要！！！）

• 读未提交：（read uncommitted）最低隔离级别。

  什么是读未提交？
  答：事务A可以读取到事务B未提交的脏数据。
  意思就是说，A可以看到B操作的数据但又尚未提交事务的数据。
  
  缺点：会有脏数据。
  

• 读已提交：（read committed）。

  什么是读已提交？
  答：事务A只能读取事务B提交了的数据。这样就不会存在脏数据了。
  
  存在的问题：
  A事务开启之后，读取到的数据每次都是最新的。
  

• 可重复读：（repeatable read）。mysql 默认的

  什么叫可重复读：
  答：事务A开启之后，在提交之前这段时间，每次查询出的数据都是一致的，意思就是说不会获取别的事务修改的新数据。
  
  缺点：
  每次读取的数据，都可能不是真实的，因为在这其中，有可能别的事务已经改过数据了，但是你在事务没提交之前又看不到。
  

• 序列化\串行化：（serializable）最高隔离级别

  事务最高级别，效率最低。解决了所有的问题。
  这种隔离级别表示排队，不能并发。
  

操作事务隔离级别

-- 查看隔离级别
>SELECT @@tx_isolation;

-- 设置全局事务隔离级别为读未提交(read uncommitted)；
>set global transaction isolation level read uncommitted;
-- 设置完之后需要退出当前的连接，重新连接一下mysql。

-- 设置全局事务隔离级别为读已提交(read committed)；
>set global transaction isolation level read committed;
-- 设置完之后需要退出当前的连接，重新连接一下mysql。

-- 设置全局事务隔离级别为可重复读(repeatable read)；
>set global transaction isolation level repeatable read;
-- 设置完之后需要退出当前的连接，重新连接一下mysql。