MySql学习笔记

2019-10-21补充：
什么是数据库三大范式？

每一列都是不可分割的原子数据项
实体的属性完全依赖于主关键字
任何非主属性不依赖于其它非主属性（在2NF基础上消除传递依赖）

2019-07-18补充下图：（感觉之前对这个连接查询理解不到位，特补充，略略略）

SQL（Structured Query Language）结构化查询语言。

• DML(Data Manipulation Language statements)数据操作语言（处理数据等操作）（使用者角度）；
• DDL(Data Definition Language statements)数据定义语言（建表，建库，建视图等）（建设者角度）；
• DCL(Data Control Language statements)数据控制语言（用来授予或回收访问数据库的某种特权，并控制数据库操纵事务发生的时间及效果，对数据库实行监视等）（管理员角度）。

入门最基本语句

1. 库级知识：
   1.1 显示数据库: show databases;
   1.2 选择数据库: use dbname;
   1.3 创建数据库: create database dbname charset utf8;
   1.4 删除数据库: drop database dbname;

数据库名不能改变。
2. 表级操作
2.1 查看库下面的所有表: show tables（包括视图）;
2.2 查看表的创建过程: show create table tableName（查看视图创建过程：show create view 视图名）;
2.3 删除表: drop table 表名;（删除视图：drop view 视图名）
2.4 清空表数据: truncate 表名
2.5 建表：

　create table tbName (
列名称1　列类型　[列参数]　[not null default ],
....列2...
....
列名称N　列类型　[列参数]　[not null default ]
)engine myisam/innodb charset utf8/gbk

例子：

create table user (
    id int(10) auto_increment primary key,
    name varchar(20) not null default '',
    age tinyint unsigned not null default 0
   )engine=innodb charset=utf8;

2.6 修改表名:rename table oldname to newname;
2.7 查看表结构: desc tableName;
2.8 查看表详细信息：show table status;
（查看某一张表：show table status where name=‘表名’）

2.9 操作数据

• 增
  Insert 3问:
  1: 插入哪张表?
  2: 插入哪几列?
  3: 这几列分别插入什么值?

Insert into TableName
(列1,列2.... 列n)
Values
(值1,值2,....值n)

注意：如果没有声明列明,则默认插入所有列.因此,值应该与全部列,按顺序一一对应.

• 删
  Delete 2问
  从哪张表删除数据?
  要删除哪些行?

Delete from 表名 where  expr

注意：以行为单位进行删除，不能只删除某一列。

• 改
  Update 4问
  1: 改哪张表?
  2: 改哪几列的值?
  3:分别改为什么值?
  4:在哪些行生效?

Update 表名 
Set 
列1 = 新值 1,
列2 = 新值2,
列n = 新值n.....
Where  expr

• 查
  查询3问
  1:查哪张表的数据?
  2:查哪些列的数据?
  3:查哪些行的数据?

Select 列1, 列2, 列3,...列n
From 表名
Where expr;

注释：

1. 列是变量
2. 变量可以计算
   
3. where是表达式，值为真或假：比如where 1时，对应表中每一行都是真，所以会取出全部行数据，where 0 时，对应表中每行数据都为假，所以取出0行数据。

注意：

• 解决字符集问题:
  默认建表一般用utf8, 而我们在windows下窗口是GBK的,因此,需要声明字符集： Set names gbk;

• 打\c,退出本语句

• 关于查询：
  
  注释： 比较运算符和逻辑运算符可以组合在一起。
  例如：select goods_id,cat_id,goods_name,shop_price from ecs_goods where cat_id not in (3,11);
  like模糊匹配：%通配任意字符，_通配单一字符。

• group分组与统计函数
  avg() 平均值
  max() 最大值
  min() 最小值
  sum() 总和
  count() 数量
  注意： count(字段)不会统计null值，使用count(*)时会计算null

SELECT COUNT(class_id) FROM stu;

• distinct用于去除结果集中的重复记录
  获取所有班级编号

SELECT DISTINCT class_id AS class FROM stu WHERE class_id IS NOT NULL;

获取学生数，同班同名的算一个

SELECT COUNT(DISTINCT class_id,sname) FROM stu WHERE class_id IS NOT NULL;

• having筛选
  where having区别：
  下面以一个例子来具体的讲解：

1. where和having都可以使用的场景

1）select addtime,name from dw_users where addtime> 1500000000

2）select addtime,name from dw_users having addtime> 1500000000

解释：上面的having可以用的前提是我已经筛选出了addtime字段，在这种情况下和where的效果是等效的，但是如果我没有select addtime就会报错！！因为having是从前面筛选的字段再筛选，而where是从数据表中的字段直接进行的筛选的。

2. 只可以用where，不可以用having的情况

1） select addtime,name from dw_users where addtime> 1500000000

2） select phone,name from dw_users having addtime> 1500000000 //报错！！！因为前面并没有筛选出addtime字段

3. 只可以用having，不可以用where情况

查询每种category_id商品的价格平均值，获取平均价格大于100元的商品信息

1）select category_id , avg(price) as ag from dw_goods group by goods_category having ag > 100

2）select category_id , avg(price) as ag from dw_goods where ag>100 group by goods_category //报错！！因为from dw_goods 这张数据表里面没有ag这个字段

注意:where 后面要跟的是数据表里的字段，如果我把ag换成avg(price)也是错误的！因为表里没有该字段。而having只是根据前面查询出来的是什么就可以后面接什么。

• order by 排序
  1 asc 升序（默认）
  desc 降序
  2 多列排序：select * from result order by name ,score desc;
  3 随机获取学生SELECT * FROM stu ORDER BY RAND() LIMIT 1;
  4 对别名字段可排序bash SELECT birthday,MONTH(birthday) as m FROM stu ORDER BY m ASC;
  5 排序受校对规则影响
  6 使用field()函数实现自定义排序order by field（str，str1，str2，str3，str4……）
  str与str1，str2，str3，str4比较，其中str指的是字段名字，
  意为：字段str按照字符串str1，str2，str3，str4的顺序返回查询到的结果集。如果表中str字段值不存在于str1，str2，str3，str4中的记录，放在结果集最前面返回。加desc倒序排列。
• limit限制取出条数
  用法：limit 偏移量，条数。
  注意： where group by having order by limit 几个子句顺序不能变。
• where 型子查询
  内层查询的结果作为外层查询的条件。

例子：查询每个栏目下最新的商品
select goods_id,goods_name,cat_id from goods where goods_id in(select max(goods_id) from goods group by cat_id);

• from 型子查询
  把先from查询的内容当成一张表来看。
• exists 型子查询
  例子：查询所有有商品的栏目：

 select * from category where exists(select * from goods where goods.cat_i
d=category.cat_id);

• 内连接查询

select xxxx from table1 inner  join table2 on table1.xx=table2.xx;

连接多张表

SELECT c.id ,count(*) FROM stu as s 
INNER JOIN class as c
INNER JOIN article as a
ON s.class_id = c.id AND s.id = a.stu_id
GROUP BY c.id;

• 左右连接查询
  查不到的数据补NULL
  获取没有设置QQ的用户

SELECT s.sname FROM stu AS s LEFT JOIN user_info as i
ON s.id = i.stu_id
WHERE i.qq is null;

• SELF JOIN自连接查询

SELECT s2.* FROM stu as s1 INNER JOIN stu AS s2
ON year(s1.birthday) = year(s2.birthday)
WHERE s1.sname ='后盾人' AND s2.sname !='后盾人';

• union 查询
  1 要求各语句取出的列数必须相同，
  2 列名称未必要一致，以第一条sql的列名为准。
  注意： 完全相等的行将会被合并，合并比较耗时，一般不让union进行合并，使用union all可以避免合并。
  union 的子句中不用写order by ，sql合并后得到的总的结果可以order by

(SELECT sname,birthday FROM stu ORDER BY birthday DESC LIMIT 1)
UNION
(SELECT sname,birthday from stu ORDER BY birthday ASC LIMIT 1)
ORDER BY birthday DESC;

• 多对多查询
  查找某人学习的课程
  user_lesson如下所示：
  

SELECT sname,l.name FROM stu AS s
INNER JOIN user_lesson AS ul
ON s.id = ul.stu_id
INNER JOIN lesson AS l
ON ul.lesson_id = l.id
WHERE s.sname = 某人';

建表

建表就是声明列的过程。

列类型

1. 整型列
   
   （一个字节=8位：位上填充·0或1，例如：0000 0000）
   整型列的可选参数：

• unsigned :无符号，列的值从0开始，不为负。
• zerofill 适合用于学号，编码等，固定宽度的数字，(在查询时)可以用0填充至固定宽度M。
  注意： zerofill 属性默认决定了列为unsigned！
  如果不是为了使用zerofill填充至指定的长度，填写长度M将毫无意义，因为它无法限制填入的位数。
  例如：

mysql> alter test add t1 tinyint(1);

mysql> insert into table test values(1,2,3,40);
mysql> select * from test;
+------+------+-------+------+
| id   | num  | sn    | t1   |
+------+------+-------+------+
|  127 | NULL |  NULL | NULL |
|    0 |  255 |  NULL | NULL |
|    9 |  222 | 00002 | NULL |
|    1 |    2 | 00003 |   40 |
+------+------+-------+------+
4 rows in set (0.00 sec)

2. 浮点列与定列

• float(M,D),M是精度（总位数），D是标度，小数点后面的位数。
• double存的位数比float更大。
• decimal
  float的精度为6~7位有效数字
  double的精度为15~16位
  decimal(m,d) 参数m<65 是总个数，d<30且 d<m 是小数位
  对货币等对精度敏感的数据，应该用定点数decimal存储

注意： float/double浮点型，有精度损失；decimal 定点型，更精确。
例如：

t2为float,t3为decimal

mysql> insert into  test values(1,2,3,40,1234567.23,1234567.23);
Query OK, 1 row affected (0.00 sec)

mysql> select * from test;
+------+------+-------+------+------------+------------+
| id   | num  | sn    | t1   | t2         | t3         |
+------+------+-------+------+------------+------------+
|  127 | NULL |  NULL | NULL |       NULL |       NULL |
|    0 |  255 |  NULL | NULL |       NULL |       NULL |
|    9 |  222 | 00002 | NULL |       NULL |       NULL |
|    1 |    2 | 00003 |   40 |       NULL |       NULL |
|    1 |    2 | 00003 |    4 |     555.00 |       NULL |
|    1 |    2 | 00003 |   40 |     666.67 |       NULL |
|    1 |    2 | 00003 |   40 |     666.23 |     666.23 |
|    1 |    2 | 00003 |   40 | 1234567.25 | 1234567.23 |
+------+------+-------+------+------------+------------+

3. 字符型列

• char（M）定长类型 0<=M<=255
  （当保存CHAR值时，在它们的右边填充空格以达到指定的长度。当检索到CHAR值时，尾部的空格被删除掉）

mysql> create table tt
    -> (t1 char(10),
    -> t2 varchar(10));
Query OK, 0 rows affected (0.14 sec)

mysql> insert into tt values (' hello ',' hello ');
Query OK, 1 row affected (0.03 sec)

mysql> select * from tt;
+--------+---------+
| t1     | t2      |
+--------+---------+
|  hello |  hello  |
+--------+---------+
1 row in set (0.02 sec)

mysql> select concat('!',t1,'!'),concat('!',t2,'!') from tt;
+--------------------+--------------------+
| concat('!',t1,'!') | concat('!',t2,'!') |
+--------------------+--------------------+
| ! hello!           | ! hello !          |
+--------------------+--------------------+
1 row in set (0.07 sec)

• varchar（M）变长类型 0<=M<=65535
  
  注意： 开发过程中，M比较小例如20，用char检索速度更快。M为字符数，不是字节！（一个utf8汉字占3个字节）
• text最大长度为65,535(2^16–1)字符。适合用来存储论文。
• blob是二进制类型，用来存储图像、音频等二进制信息。blob在于防止因为字符集的问题导致信息丢失。
• enum 检举型，是定义好的，值就在某几个枚举范围内
• set 跟enum区别：enum一次只能选一个值，set一次能选好几个。一个 SET 类型最多可以包含 64 项元素，写入SET类型字段时，多个值用半角逗号分隔。

4. 日期时间型

• year（单字节）‘YYYY’
• date ‘YYYY-MM-DD’
• time ‘HH:MM:SS’
• datetime ‘YYYY-MM-DD HH:MM:SS’
• timestamp 从1970-01-01 08:00:01开始计算，不适用于存储出生日期
  当执行添加与更新时字段将自动为当前时间：

ALTER TABLE stu ADD updated_at 
TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP 
ON UPDATE CURRENT_TIMESTAMP;

• 使用列的默认值原因：NULL查询不便，NULL索引效果不高。声明默认值方法：NOT NULL DEFAULT 默认值;
• 主键(primary key)与自增(auto_increment)
  提高查询速度技巧： 定长与变长分离；常用与不常用列分离

列的增删改

1. 增：alter table 表名 add 列名 列类型 列属性… 默认添加在表的结尾。
   alter table 表名 add 列名 列类型 列属性… after 列名（将增加到指定列后）
2. 删：alter table 表名 drop column 列名。
3. 改：alter table 表名 change 旧列名 新列名 【列类型 列属性】
   修改列属性：ALTER TABLE tb_article MODIFY COLUMN NAME CHAR(50);

视图

• algorithm :
  对于简单查询形成的view ,再对view查询时，如where ,order 等等，可以把建视图物理句+查视图的语句合并成查物理的语句，这种视图的算法叫merge（合并）；
  也有可能，视图的语句本身比较复杂，很难再和查询视图的语句合并成mysql，可以先执行视图的创建语句把结果集形成内存中的临时表，然后再去查临时表。
• view
  view有被称为虚拟表，是sql的查询结果，
  用处：

1. 权限控制：通过视图，开放其中一列或几列，从而起到权限控制作用。
2. 简化复杂的查询。
   注意： 问：视图能否更新、删除、添加？
   如果视图的每一行是与物理表一一对应，则可以，并且可以影响到物理表；但是如果视图的行是由物理表多行经过计算得到的结果，则不可以。

存储引擎

字符集与乱码问题

字符集的继承规则

表不设置字符集继承数据库，字段不设置字符集继承表的。
此外，修改表字符集，对表的原字段将不影响，只对新增字段产生影响。

乱码

乱码是因为文字本来的字符集与展示的字符集不一致。

索引

索引是数据的目录，能快速定位行数据的位置。
索引提高了查询的速度，但是降低了增删改的速度。
一般在查询频率高的列上加，而且在重复度低的列上加效果更好。

1. 索引类型：

• key 普通索引
• unique key 唯一索引
• primary key 主键索引
• fulltext 全文索引（中文环境下，全文索引无效，要分词+索引，一般用第三方解决方案，如sphinx）
  注释： 建索引时，可以只索引列的前一部分内容（比如前十个字符）。

2. 多列索引：把两列或多列的值看成一个整体，然后建立索引
   冗余索引：就是在某个列上可能有多个索引。
3. 索引操作
   查：show index from 表名;
   删：drop index 索引名 on 表名（或alter table 表名 drop index 索引名）；
   增：alter table 表名 add [fulltext|unique] index 索引名（列名）；
   添加主键索引：alter table 表名 add primary key(列名)（不需要加索引名）

事务

储存引擎：InnoDB
开启事务：start transaction或者begin
结束：commit
回滚：rollback
特点：隔离性、原子性、一致性、持久性
全局事务
适用于所有sql都使用事务操作

-- 关闭自动提交
SET AUTOCOMMIT = 0;

INSERT INTO stu (class_id,sname,sex)VALUES(2,'李清','女');
COMMIT;

-- 开启自动提交
SET AUTOCOMMIT = 1;

隔离级别

查询与设置

查询隔离级别

select @@tx_isolation;

设置隔离级别

set session transaction isolation level read uncommitted;

脏读

脏读是一个事务没有提交时可被其他事务读取到。

• 使用了最低级别read uncommitted，事务B在事务A没有提交时就可以看到更新的数据。

不可重复读

• 使用了级别read committed，事务B在事务A没有提交时不可以看到更新的数据。但当事务A提交了事务后，事务B再读取时得到了最新的数据。

可重复读

• 使用 REPEATABLE READ，无论事务A是否提交，都不会对事务B读取数据造成影响。

幻读

• 幻读和不可重复读都是读取了另一条已经提交的事务（这点就脏读不同）。
  也就是说，在事务A没有提交时，事务B能插入数据，从而影响到事务A对数据的读取。
• 切换隔离级别为 serializable 后，在事务A没有提交时，事务B是不能插入数据的（表现形式为等待）。
• 这种情况是小概率事件，使用隔离级别为 serializable在高并发的情况下会导致锁表情况，用户等待时间过长，一般使用第三种隔离级别即可。

校对规则

用于字符比较和排序的一套规则，以 _ci 结束的为大小写不敏感、_bin结束的为区分大小写。

比较

如果使用utf8_bin 校对规则时，下面的查询将匹配不到大写的PHP

select * from class WHERE cname = 'php';

排序

当使用不区分大小写的校对规则时A与a是相同的，否则则不相同，这会影响到排序与比对。

此外，修改表校对规则，对表的原字段将不影响，只对新增字段产生影响。

锁机制

储存引擎

• InnoDB 是主流储存引擎并支持行级锁的，有更高的并发处理性能

非索引阻塞

• 行锁是针对索引加的锁，使用非索引字段筛选时，将造成全表锁定即表级锁，应该避免这种情况发生，提升数据库的并发性能。

范围锁

• 例如：

事务A筛选时使用了范围区间，将会造成表锁

BEGIN;
UPDATE goods SET num=200 WHERE id>1 AND id<3; 

事务B将不能修改表中的ID大于2的记录

BEGIN;
update goods set num =1 where id=2;
-- 阻塞中...

但可以更改ID为1的记录

update goods set num =1 where id=1;

执行添加时因为不在id为 1~3的范围内所以可以添加，但如果添加时指定ID为2将会阻塞。

insert into goods (name,num) values('西瓜',200);

悲观锁

事务A执行悲观锁操作后，其他事务执行同一代码时将阻塞

BEGIN;
SELECT * FROM goods WHERE id=1 FOR UPDATE;
UPDATE goods SET num=num-2 WHERE id=1; 
...

事务B执行以下代码将不能查询库存，必须等事务A提交或回滚事务

BEGIN;
SELECT * FROM goods WHERE id=1 FOR UPDATE;
-- 阻塞中...

事务A提交后，事务B会得到事务A操作后的结果

...
COMMIT;
...

乐观锁

在提交更新的时候会判断在此期间数据是否被更改，如果被更改则提交失败。

事务A查询商品库存，获取了商品记录，记录中有VERSION字段用于记录版本号（目前为0）

BEGIN;
SELECT * FROM goods WHERE id = 1;

事务B同时查询，也获取了版本号为0的记录

BEGIN;
SELECT * FROM goods WHERE id = 1;

事务A更改库存，并修改了版本号

UPDATE goods SET num=num-10,VERSION =VERSION+1 WHERE VERSION=0;

事务B更改数据，但使用的是事务B查询到的0号版本，因为事务A已经提交版本号为1，造成事务B修改失败，保证了数据的完整性。

UPDATE goods SET num=num-10,VERSION =VERSION+1 WHERE VERSION=0;

表锁机制

针对一些不支持事务的处理引擎可以使用锁表的方式控制业务。

• 读锁
  为表设置读锁后，当前会话和其他会话都不可以修改数据，但可以读取表数据。

会话A对表goods设置了读锁，将不能修改该表，也不能操作其他表

LOCK TABLE goods READ;
UPDATE goods SET num=300 WHERE id=1;
SELECT * FROM stu;

因为会话A对表goods设置了读锁，所以会话B也不能修改

update goods set num=200 where id=1;
-- 阻塞

会话A解锁表后，其他会话又可以继续操作表了

UNLOCK TABLES;

• 写锁
  为表设置了写锁后，当前会话可以修改，查询表，其他会话将无法操作。

会话A对表goods设置写锁，本会话可以正常操作表， 并不能操作其他表

LOCK TABLE goods WRITE;
INSERT INTO goods (name,num )VALUES('后盾人教程',300);

会话B读取/写入/写入表数据都将阻塞

select * from goods

会话A解锁表数据后，其他会话都可以正常操作了

UNLOCK TABLES;

外键约束

• 外键表示一个表中的字段被另一个表中的一个字段引用。外键对相关表中的数据造成了限制，使MySQL能够保持参照完整性。
  父表和子表储存引擎要一致
  使用InnoDB引擎支持外键约束
  外键要与主表列类型一致
  外键列使用索引（数据库会自动添加索引)
• 新建表
  下面创建班级表与学生表，并定义学生表与班级表建立外键约束。

-- 班级表
CREATE TABLE class (id int PRIMARY KEY AUTO_INCREMENT,name varchar(50));

-- 学生表
CREATE TABLE stu (
  `id` int(11) NOT NULL AUTO_INCREMENT PRIMARY KEY,
  `sname` varchar(30) DEFAULT NULL,
  `class_id` int(11) DEFAULT NULL,
  CONSTRAINT `stu_class`
  FOREIGN KEY (`class_id`) 
  REFERENCES `class` (`id`) 
  ON DELETE CASCADE 
  ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8

• 选项说明
  
• 处理动作
  ON DELETE
  
  ON UPDATE
  

索引优化

基础知识

1. 选择相对简单的数据类型。
   数字类型相对字符串类型要简单的多，尤其是在比较运算时。

所以保存数值类型最好不要用字符串数据类型，这样存储的空间显然是会更大，而且在排序时字符串的9是大于22的。如果进行运算时mysql会将字符串转换为数值类型，这种转换是不会走索引的。

如果明确数据在一个完整的集合中如男，女，那么可以使用set或enum数据类型，这种数据类型在运算及储存时以数值方式操作，所以效率要比字符串更好，同时空间占用更少。
2. 索引弊端
创建索引会使查询操作变得更加快速，但是会降低增加、删除、更新操作的速度，因为执行这些操作的同时会对索引文件进行重新排序或更新
创建过多列的索引会大大增加磁盘空间开销
不要盲目的创建索引，只为查询操作频繁的列创建索引
3. 索引类型

4. 表的维度
数据列中不重复值出现的个数，维度的最大值是数据行的数量
如数据表中存在8行数据a ,b ,c,d,a,b,c,d这个表的维度为4
要为维度高的列创建索引
性别这样的列不适合创建索引，因为维度过低！
5. 添加索引
对where，on或group by 及order by 中出现的列使用索引
6. 表过式的影响

• 下面SQL语句不会使用索引

//索引列参与了计算
explain select * from stu where status+1=1;

//使用了函数运算
explain select * from stu where left(sname,1)='后盾人'

//索引列是%开头的模糊匹配（以_开头的也会导致索引失效）
explain select * from stu where sname like '%向军大叔%'

• 下面SQL会使用索引，因为不是模糊匹配

explain select * from stu where sname like '后盾人%'

7. （不）相同类型比较时
   相同类型比较时走索引

//sname为varchar类型
explain select * from stu where sname="1";

下面的查询不走索引
explain select * from stu where sname=1;

慢查询

• 当Mysql性能下降时，通过开启慢查询来获得哪条SQL语句造成的响应过慢进行分析处理。当然开启慢查询会带来CPU损耗与日志记录的IO开销，所以我们要间断性的打开慢查询日志来查看Mysql运行状态。

慢查询能记录下所有执行超过long_query_time时间的SQL语句, 用于找到执行慢的SQL, 方便我们对这些SQL进行优化。

• 会话配置

查看开启慢查询状态
 show variables like 'slow_query%';

查看慢查询设置的时间
show variables like "long_query_time"

开启当前会话的慢查询
set session slow_query_log='ON';

设置当前会话慢查询时间为1妙
set session long_query_time=1;

• 全局配置

通过修改配置mysql配置文件 my.cnf 来开启全局慢查询配置，在配置文件中修改以下内容

slow_query_log = ON
slow_query_log_file = /usr/local/mysql/data/slow.log
long_query_time = 1

重起MYSQL服务

service mysqld restart