MySQL学习笔记

1.if （not） exists的用法

#如果不存在yyfs这个库时，则可以创建
create database if not exists yyfs; 
#如果不存在yyfs这个库时，则可以创建
drop database if exists yyfs; 

2.ALTER的用法

• 重命名数据表

ALTER TABLE old_table RENAME new_table;

• 改变表属性

#直接赋值即可
ALTER TABLE yyfs_table RENAME CHARSET=utf8mb4;
ALTER TABLE yyfs_table RENAME ENGINE=MyISAM;

• 修改字段

#change可以修改字段名字
ALTER TABLE yyfs_table CHANGE id id int;
ALTER TABLE yyfs_table MODIFY id int;
#修改字段位置
#（1）FIRST：指定位置为表的第一个位置  
#（2）AFTER 字段名2：指定“字段名1”插入在“字段名2”之后。
ALTER TABLE yyfs_table MODIFY 字段名1 int FIRST ｜ AFTER 字段名2;

• 增加字段

ALTER TABLE yyfs_table ADD id int;

• 删减字段

ALTER TABLE yyfs_table DROP id;

• 增加索引

ALTER TABLE yyfs_table ADD INDEX idx_union(dept);
CREATE FULLTEXT INDEX ft_name ON `student` (`name`(20))

• 删减索引

ALTER TABLE yyfs_table DROP INDEX index_name;
DROP INDEX index_name ON yyfs_table;

3.备份数据库

• 备份数据库
  mysqldump -h主机名 -P端口 -u用户名 -p密码 –database 数据库名 > 文件名.sql
  mysqldump -h主机名 -P端口 -u用户名 -p密码 –database 数据库名 -r 文件名.sql
• 恢复数据库
  （1）mysqldump -h主机名 -P端口 -u用户名 -p密码 要恢复的数据库名 < 文件名.sql
  （2）在登录状态下，source 文件地址;

4.常用数据类型

• 整型

类型	占用字节	从	到
tinyint	1	-128	127
smallint	2	-32768	32767
mediumint	3	-8388608	8388607
int	4	-2147483648	2147483647
bigint	8	-9223372036854775808	9223372036854775807

• 浮点型
  float（M,D）：单精度浮点数，可以精确到小数点后7位，4个字节，M代表总宽度，D代表小数位
  double（M,D）：双精度浮点数，可以精确到小数点后15位，8个字节
• 字符型

类型	大小(字节)	用途
CHAR	0-255	定长字符串
VARCHAR	0-65535	变长字符串
TEXT	0-65 535	长文本数据
BLOB	0-65 535	二进制形式的长文本数据

• 日期时间型

类型	大小（字节）	用途	格式
DATE	3	1000-01-01/9999-12-31	YYYY-MM-DD
TIME	3	‘-838:59:59’/‘838:59:59’	HH:MM:SS
YEAR	1	1901/2155	YYYY
DATETIME	8	1000-01-01 00:00:00/9999-12-31 23:59:59	YYYY-MM-DD HH:MM:SS
TIMESTAMP	4	1970-01-01 00:00:00/2038结束时间是第 2147483647 秒，北京时间 2038-1-19 11:14:07，格林尼治时间 2038年1月19日 凌晨 03:14:07	时间戳

5.注释

• 单行注释：#和- -
• 多行注释：/**/

6.delimiter定义mysql结束符

delimiter 新的结束符

7.索引

• 索引类型：key：普通索引、unique key：唯一索引、primary key：主键索引、fulltext key全文索引、foreign key（字段名） references 其他表名（对应其他表的字段名）：外键索引
• 索引使用原则：
  （1）独立的条件字段
  select * from product where id=111;//走索引
  select * from product where id+2=111;//不走索引
  （2）左原则：用于模糊查询like的%和_
  　　 xxxx%、yyy_ 走索引
  　 　%xxxx、%yyy%、_ yyy 不走索引
  （3）复合索引：最左原则
  （4）OR原则：OR左右的关联条件必须都具备索引，才可以使用索引
• 前缀索引：alter table 表名 add index(字段(位数))
  位数才是记录的唯一标识
• 全文索引：match和aganist配合使用

#如果搜索多个词，请用空格或者逗号隔开
select * from articles where match(title,body) against('MySQL,Database')

8.视图

• 创建语句

#create view 视图名 as select语句;
create view v1 select id,name,age,email from table1 where id between 8 and 100;

• 使用语句

#直接当一个表使用
select * from v1 limit 0,10;

• 删除语句

drop view [if exists] 视图名;

9.any（some）、all

• any（some）：any和some一样，只要有一个条件满足，就算是满足
• all：全部都满足，才能算满足

#查找非高价商品
select * from product where price < any(select price from product);
#查找最高价商品
select * from product where price >= all(select price from product);

10.exists/not exists

• 指定一个子查询，检测行的存在。遍历循环外表，然后看外表中的记录有没有和内表的数据一样的。匹配上就将结果放入结果集中
  注意：强调的是是否有返回集，不需知道具体返回的是什么
  　　　查询条件需要与外部查询条件有关
• 结论：1. in()适合B表比A表数据小的情况
  　　　2. exists()适合B表比A表数据大的情况
  当A表数据与B表数据一样大时,in与exists效率差不多,可任选一个使用.但理论上exists比in快，因为in不走索引，exists走索引

#exists查询子句不包含外部条件，返回true，返回全部结果集
select art_id from blog_article a where exists(select art_id from blog_artic
le c where c.art_id=1);
#exists查询子句包含外部条件，返回a.art_id=1的结果集
select art_id from blog_article a where exists(select art_id from blog_artic
le c where a.art_id=1);
#not exists查询子句不包含外部条件，返回true，返回空结果集
select art_id from blog_article a where not exists(select art_id from blog_artic
le c where c.art_id=1);
#not exists查询子句包含外部条件，返回除了a.art_id=1的结果集
select art_id from blog_article a where not exists(select art_id from blog_artic
le c where a.art_id=1);

11.数据控制语言-DCL

（1）用户管理

• 创建用户
  形式：create user ‘用户名’@‘允许登录的地址/服务器’ identified by ‘密码’;
  　　　“%”代表“任何地址”

create user 'yyfs'@'localhost' identified by 'root';

• 删除用户
  形式：drop user ‘用户名’@‘允许登录的地址/服务器’;
• 修改用户密码
  修改自己的密码：set password = password(‘密码’);
  修改他人的密码（必须有修改权限）：set password for ‘用户名’@‘允许登录的地址/服务器’ = password(‘密码’);

（2）权限管理

• 授予权限
  1.形式：grant 权限列表 on 某库.某个对象 to ‘用户名’@‘允许登录的地址/服务器’ [identified by ‘密码’];
  2.说明：
  a.权限列表就是多个权限的名词，相互之间逗号隔开，如select、insert、update……也可以是all，表示全部权限
  b.某库.某对象表示给指定的某个数据库中的某个“下级单位”赋权，下级单位有：表名、视图名、存储过程名、存储函数名.如下有2个特殊的写法：
  　　　*.*：代表所有数据库中的所有下级单位
  　　　某库.*：代表某库中的所有下级单位
  c.[identified by ‘密码’]如果不省略，即可改密码，但如果用户不存在时，则会创建一个新用户，此时不能省略
• 剥夺权限
  1.形式：revoke 权限列表 on 某库.某个对象 to ‘用户名’@‘允许登录的地址/服务器’;
• flush privileges
  刷新MySQL的系统权限相关表，在完成改密码/权限之后需要进行刷新或重启MySQL才能使用

12.事务控制语言-DTL

• 事务模式：是否开启“一条语句就是一个事务”的开关
  默认：开启，称为“自动提交模式”，每条增删改语句都会立即执行
  　　　set autocommit = 1
  关闭：称“伪提交模式”，需要人为commit提交
  　　　set autocommit = 0

set autocommit = 0;
insert into student values(1,'yyfs');
commit;

• 事务执行流程
  （1）开启一个事务：start transaction;
  （2）执行各种增删改语句
  （3）判断情况，提交或回滚

if(没有出错){
	commit;	#提交事务
}else{
	rollback; //回滚事务
}

13.存储过程

• 创建：说明：
  1、in：用于设定该变量是用来“接收实参数据”的，即“传入”，默认不写，就是in
  2、out：用于设定该变量是用来“存储存储过程中的数据”，即“传出”，函数中如存在，必须对它赋值
  3、inout：是in和out的结合，具有双向作用

#先改结束字符
delimiter ///
#定义形式
create procedure 存储过程名([in|out|inout]形参 类型,[in|out|inout]形参 类型,……)#如 IN name varchar
begin
	#写完整的过程中的语句
	#可以有各种流程控制的
	#查询语句会作为存储过程中调用的结果
end
///
delimiter ;

• 调用：应在“非编程环境”中调用，cmd
  call 存储过程名[(传实参)];
• 删除：drop procedure 存储过程名;

14.触发器

• 不能调用，而是某个表发生某个事件（增删改）时，会自动发生
• 说明：
  1、触发时机：before（在…之前）、after（在…之后）
  2、触发事件：insert、update、delete
  3、在触发器内部有new和old关键字，获取有关数据
  （1）new：代表当前正要执行的insert、update的时候的“新行”数据，可以获取这一条新行数据的任意字段的值
  　　 形式为：set @v1 = new.id; 获得新插入或update行的id字段的值
  （2）old：代表当前正要执行的delete的时候的“旧行”数据，可以获取这一条旧行数据的任意字段的值
  　　 形式为：set @v1 = old.id;

#先改结束字符
delimiter ///
#定义形式
create trigger 触发器名 触发时机 触发事件 on 表明 for each row
begin
	#触发器内部sql语句
end
///
delimiter ;

15.游标

• 使用游标也可以轻易的取出在检索出来的行中前进或后退一行或多行的结果，只适用于存储过程或函数
• 1.定义游标：declare 游标名 cursor for select语句;
  2.打开游标：open 游标名;
  获取结果：fetch 游标名 into 变量名[,变量名];
  关闭游标:close 游标名;

create procedure p()
begin
    declare id int;
    declare name varchar(15);
    -- 声明游标
    declare mc cursor for select * from class;
    -- 打开游标
    open mc;
    -- 获取结果
    fetch mc into id,name;
    -- 这里是为了显示获取结果
    select id,name;
    -- 关闭游标
    close mc; 
end;

16.查询缓存

• 查看参数：
  （1）show variables like ‘%query_cache%’;可以看到关于查询缓存的相关参数，其中重要的参数为
  1.query_cache_type：查询缓存类型，取值：0：不使用查询缓存、1：使用查询缓存、2：特定条件下可使用查询缓存
  2.query_cache_size ：为查询缓存预留的内存
  （2）show variables like ‘%Qcache%’;查看缓存空间状态
  1.Qcache_free_memory：剩余缓存空间
• 开启查询缓存：

#########    1    ##########
#默认情况下是1，必须设置为1或2
#当query_cache_type设置为1时
set global query_cache_type = 1;#默认使用查询缓存
select SQL_NO_CACHE * FROM yyfs;#不使用查询缓存
#当query_cache_type设置为2时
#默认不使用查询缓存
select SQL_CACHE * FROM yyfs;#使用查询缓存
#########    2    ##########
#若设得太小则不会生效
set global query_cache_size = 64*1024*1024;#64M

• 清理查询缓存：
  （1）FLUSH QUERY CACHE; //清理查询缓存内存碎片
  （2）RESET QUERY CACHE; // 从查询缓存中移出所有查询
  （3）FLUSH TABLES; //关闭所有打开的表，同时该操作将会清空查询缓存中的内容
• 说明：
  （1）缓存失效：数据表或数据有变动时，则缓存失效
  （2）不适用缓存：查询语句中包含变动的信息，如时间now()，随机数()等
  （3）生成多个缓存：相同的sql语句，如果空格，大小写等内容不同，则会分别进行缓存

17.分区/分表

（1）分区

• 查看是否支持分区：show variables like “%part%”;
  have_partitioning=yes：支持分区
• 分区类型：四种，仅支持int类型或返回的值是int类型，partition by xx(字段/表达式)
  （1）key()：类似哈希分区，不可自定义
  （2）hash()：哈希分区，可自定义
  （3）range()常用：范围分区
  （4）list()：列表分区
• 分区路径选择：
  数据文件路径：DATA DIRECTORY
  索引文件：INDEX DIRECTORY
  MYISAM存储引擎的可以定义数据文件路径和索引文件路径，INNODB存储引擎只能定义数数据文件路径

########  key分区  ########
#分10个区
create table yyfs_table(
	id int auto_increment primary key,
	name varchar(32) not null default '',
	recdate timestamp not null default CURRENT_TIMESTAMP
)ENGINE=Myisam charset=utf8 partition by key(id) partitions 10;
########  hash分区  ########
#按月份分12个区
partition by hash(month(recdate)) partitions 10;
########  range分区  ########
partition by range(year(recdate))( 
　　partition p2007 values less than (2008)
　　	#定义文件路径
　　	DATA DIRECTORY = '/data/data'
　　	INDEX DIRECTORY = '/data/idx', 
　　partition p2008 values less than (2009), 
　　partition p2009 values less than (2010) 
　　partition p2010 values less than MAXVALUE  
　　#MAXVALUE 表示最大的可能的整数值,否则插入超过2010会出错
);
########  list分区  ########
#按照月份分成4个季节
partition by list(month(recdate))( 
　　partition spring values in (3,4,5), 
　　partition summer values in (6,7,8), 
　　partition fall values in (9,10,11), 
　　partition winter values in (12,1,2), 
);

• COLUMNS分区：range columns和list columns，支持整数(tinyint到bigint， 不支持decimal 和float)、日期(date、datetime)、字符串(char、varchar、binary、varbinary)三大数据类型。支持一个或者多个字段，但不支持表达式分区

########  range columns分区  ########
partition by range columns(year((a,b))( 
	PARTITION p0 VALUES LESS THAN (5, 12),
    PARTITION p1 VALUES LESS THAN (MAXVALUE, MAXVALUE)
);
########  list columns分区  ########
partition by list columns(city) (
    PARTITION hunan VALUES IN('长沙', '张家界'),
    PARTITION fujian VALUES IN('厦门', '福州'),
    PARTITION gaungdong VALUES IN('广州', '深圳', '汕头')
);

• 子分区（subpartition）：是分区表中对每个分区的再次分割，又被称为复合分区，支持对range和list进行子分区，子分区即可以使用hash分区也可以使用key分区。复合分区适用于保存非常大量的数据记录。
  说明：
  　1.如果一个分区中创建了子分区，其他分区也要有子分区
  　2.如果创建了了分区，每个分区中的子分区数必有相同
  　3.同一分区内的子分区，名字不相同，不同分区内的子分区名子可以相同（5.1.50不适用）

#range根据年份分区
#子分区根据天数再hash分区，分成两个
#总分区：3*2=6个分区
partition by range(year(recdate))
subpartition by hash(to_days(recdate)) subpartitions 2 
(
    partition p0 values less than (1990),
    partition p1 values less than (2018),
    partition p1 values less than maxvalue
);

• 分区管理：
  （1）删除分区
  ALTER TABLE yyfs_table DROP PARTITION 分区名称;
  （2）增加分区
  　1.RANGE分区：ALTER TABLE yyfs_table ADD PARTITION (partition user_3 values less than maxvalue);
  　2.LIST分区：ALTER TABLE yyfs_table ADD PARTITION (PARTITION p4 VALUES IN (16,17,18,19));
  　3.HASH/KEY分区：ALTER TABLE yyfs_table ADD PARTITION PARTITIONS 8;
  （3）重建分区
  　1.RANGE分区：
  　　将原来的 p0,p1 分区合并起来，放到新的 p0 分区中
  　　ALTER TABLE yyfs_table REORGANIZE PARTITION p0,p1 INTO (PARTITION p0 VALUES LESS THAN (6000000));
  　2.LIST分区：
  　　将原来的 p0,p1 分区合并起来，放到新的 p0 分区中
  　　ALTER TABLE yyfs_table REORGANIZE PARTITION p0,p1 INTO (PARTITION p0 VALUES IN(0,1,4,5,8,9,12,13));
  　3.HASH/KEY分区：
  　　重建分区的数量变成2，在这里数量只能减少不能增加
  　　ALTER TABLE yyfs_table REORGANIZE PARTITION COALESCE PARTITION 2;
• 缺点：
  （1）主键或者唯一索引必须包含分区字段，如primary key (id,username)
  （2）使用分区一般不要再使用主键了，否则可能影响性能
  （3）每个表最多1024个分区，而且多分区会大量消耗内存
  （4）分区的表不支持外键
  （5）分区后，可能会造成索引失效

（2）分表

• 水平分表：物理分表，对记录进行分割，存储到许多不同的表上，如
  根据姓氏分表，student_yao,student_chen,student_cai
• 垂直分表：对字段进行分割，根据三范式定义

18.排序

• order by null：强制对查询结果禁止排序
  在GROUP BY时会自动对分组进行排序，可以使用order by null提升效率，仅分组，不排序
• order by rand()：随机取出数据
• 按照指定顺序排序：
  （1）FIELD()：参数之间不用加引号
  （2）FIND_IN_SET()：全部参数用一个引号包住

SELECT user_id FROM blog_user WHERE user_id in (1,2,3,4,5) ORDER BY FIELD(user_id,5,4,3,1,2); // 纯数字字段不用处理引号，比较好拼接
SELECT user_name FROM blog_user WHERE user_id in (1,2,3,4,5) ORDER BY FIND_IN_SET(user_name,'yyfs,wldfeq,hhfs,yy-fs'); // 一个引号全包住，搞定字符值字段

• PS：FIND_IN_SET：查找某一值在字段中是否存在，数据量大时，尽量别用

SELECT * from good_number where FIND_IN_SET(4,attr_list);

19.慢查询日志

• 说明：把查询速度很慢的sql语句统计出来，可以进行优化分析
• 开启慢查询日志：
  （1）查询属性配置：show variables like ‘%slow_query%’;
  （2）开启：set global slow_query_log = 1;
  （3）设置时间阈值：set global long_query_time =2;
  （4）设置存储路径：set global slow_query_log_file =/usr/slow_query.log;

20.EXPLAIN执行计划

• 说明：利用explain可以查看sql语句的执行计划，是否有命中索引，是否有做全表扫描等
  执行后返回十列参数，id、select_type、table、type、possible_keys、key、key_len、ref、rows、Extra

mysql> explain select * from user where user_id in (select user_id from user where user_id>6);
+----+--------------------+-------+-----------------+---------------+---------+---------+------+------+--------------------------+
| id | select_type        | table | type            | possible_keys | key     | key_len | ref  | rows | Extra                    |
+----+--------------------+-------+-----------------+---------------+---------+---------+------+------+--------------------------+
|  1 | PRIMARY            | user  | ALL             | NULL          | NULL    | NULL    | NULL |    6 | Using where              |
|  2 | DEPENDENT SUBQUERY | user  | unique_subquery | PRIMARY       | PRIMARY | 4       | func |    1 | Using index; Using where |
+----+--------------------+-------+-----------------+---------------+---------+---------+------+------+--------------------------+
2 rows in set (0.00 sec)

• id：SQL执行顺序的标识，从大到小的执行
  （1）当id相同时，执行顺序从上往下，id值越高，优先级越高
  如果是子查询，id需要会递增
• select_type：查询中每个select子句的类型
  （1）SIMPLE：简单SELECT,不使用UNION或子查询等
  （2）PRIMARY：查询中若包含任何复杂的子部分,最外层的select被标记为PRIMARY
  （3） UNION：UNION中的第二个或后面的SELECT语句
  （4） DEPENDENT UNION：UNION中的第二个或后面的SELECT语句，取决于外面的查询
  （5）UNION RESULT：UNION的结果
  （6）SUBQUERY：子查询中的第一个SELECT
  （7）DEPENDENT SUBQUERY：子查询中的第一个SELECT，取决于外面的查询
  （8）DERIVED：派生表的SELECT, FROM子句的子查询
  （9）UNCACHEABLE SUBQUERY：一个子查询的结果不能被缓存，必须重新评估外链接的第一行)
• table：显示此sql语句关于哪张表，有可能不是真实表名，而是某一步的执行结果，则显示derived2，数字2表示第2步的执行结果
• type：数据库在表中找到所需行的方式，又称“访问类型”
  常用的类型有（从左到右，性能从差到好）：ALL > index > range > ref > eq_ref > const > system > NULL
  （1）ALL：全表扫描，MySQL将遍历全表以找到匹配的行
  （2）index：索引全扫描，index与ALL区别为index类型只遍历索引树
  （3）range：只检索给定范围的行，使用一个索引来选择行
  （4）ref：表示上述表的连接匹配条件，即哪些列或常量被用于查找索引列上的值
  （5）eq_ref：类似ref，区别就在使用的索引是唯一索引，对于每个索引键值，表中只有一条记录匹配，简单来说，就是多表连接中使用primary key或者 unique key作为关联条件
  （6）const、system：当MySQL对查询某部分进行优化，并转换为一个常量时，使用这些类型访问。如将主键置于where列表中，MySQL就能将该查询转换为一个常量,system是const类型的特例，当查询的表只有一行的情况下，使用system
  （7）NULL：MySQL在优化过程中分解语句，执行时甚至不用访问表或索引，例如从一个索引列里选取最小值可以通过单独索引查找完成
• possible_keys：指出MySQL能使用哪个索引去查询，查询涉及到的字段上若存在索引，则该索引将被列出，但不一定被查询使用
• key：指出MySQL实际使用的索引
• key_len：表示索引中使用的字节数
• ref：表示上述表的连接匹配条件，即哪些列或常量被用于查找索引列上的值
• rows：估算的找到所需的记录所需要读取的行数
• Extra：包含MySQL解决查询的详细信息,有以下几种情况：
  （1）Using where:列数据是从仅仅使用了索引中的信息而没有读取实际的行动的表返回的，这发生在对表的全部的请求列都是同一个索引的部分的时候，表示mysql服务器将在存储引擎检索行后再进行过滤
  （2）Using temporary：表示MySQL需要使用临时表来存储结果集，常见于排序和分组查询
  （3）Using filesort：MySQL中无法利用索引完成的排序操作称为“文件排序”
  （4）Using join buffer：强调了在获取连接条件时没有使用索引，并且需要连接缓冲区来存储中间结果。如果出现了这个值，那应该注意，根据查询的具体情况可能需要添加索引来改进性能
  （5）Impossible where：强调了where语句会导致没有符合条件的行
  （6）Select tables optimized away：意味着仅通过使用索引，优化器可能仅从聚合函数结果中返回一行

21.union 和 union all

• union：将多个select语句的查询结果合并输出，并删除重复行
• union all：……，不删除重复行

MYSQL 内置函数

• YEAR：年份　　MONTH：月份　　DAY：日期
• group_concat([DISTINCT] 要连接的字段 [Order BY ASC/DESC 排序字段] [Separator ‘分隔符’])
• sum([all|distinct] expression)：汇总　　avg([all|distinct] expression)：求平均值
  min([all|distinct] expression)：最大　 　max([all|distinct] expression)：求最小
  count([all|distinct] expression)：记录数
• first(字段)：第一条记录　　last(字段)：最后一条记录
• number_format(float number [ , decimals])：格式化，第二个参数为保留的位数
• UPPER/UCASE：将字母都转为大写　　LOWER/LCASE：将字母都转为大写
• CURTIME()：可获取当前时间，格式为HH:MM:SS
  NOW()：可获取当前时间，格式为Y-m-d HH:MM:SS
  DATE_FORMAT(date,format)：格式化时间

select DATE_FORMAT(NOW(),'%m-%d-%Y');   # 01-20-2019

MYSQL 特定语句

• 查看引擎信息
  show ENGINES;
• 显示某表的所有字段信息
  show FULL FIELDS from xx表;
• DDL：数据定义语言，建改表
• DML：数据处理语言。增删改查