数据类型
1.1 作用

 控制数据的规范性，让数据有具体含义，在列上进行控制

1.2 种类

1) 字符串
 char(32)
 定长长度为32的字符串。存储数据时，一次性提供32字符长度的存储空 
 间，存不满，用空格填充。
varchar(32)：
 可变长度的字符串类型。存数据时，首先进行字符串长度判断，按需分 
配存储空间
 会单独占用一个字节来记录此次的字符长度
 超过255之后，需要两个字节长度记录字符长度。

2)enum 类型
 enum('bj','sh','sz','cq','hb',......)
 数据行较多时，会影响到索引的应用
 注意：数字类禁止使用enum类型

 3)数字
 1. tinyint 
 2. int

 4) 时间
 1. timestamp
 2. datetime

 4) 表属性 
 存储引擎 ：engine =  InnoDB
 字符集   ：charset = utf8mb4

 utf8    中文  三个字节长度
 utf8mb4 中文  四个字节长度    才是真正的utf8 
 支持emoji字符

 5)排序规则（校对规则） collation 
 针对英文字符串大小写问题
 
 
 6.)列的属性和约束
 1 主键： primary key (PK)
 说明：
 唯一
 非空

 2 非空： Not NULL 
 说明：
 我们建议，对于普通列来讲，尽量设置not null 
 默认值 default ： 数字列的默认值使用0 ,字符串类型，设置为一个nil null

 3 唯一：unique 
 不能重复

 4 自增 auto_increment
 针对数字列，自动生成顺序值

 5 无符号 unsigned
 针对数字列  

 6 注释 comment 

用户管理

创建用户
mysql> create user oldguo@'10.0.0.%' identified by '123';
查询用户
mysql> select user,host ,authentication_string from mysql.user;
删除用户
mysql> drop user oldguo@'10.0.0.%';
修改用户
mysql> alter user root@'localhost' identified by '123';

用户权限

作用
控制用户登录之后能对MySQL对象做哪些命令。

3.1 权限的定义

  MySQL的权限定义就是SQL语句。

   ALL:
  SELECT,INSERT, UPDATE, DELETE, CREATE, DROP, RELOAD,SHUTDOWN, PROCESS, FILE,
  REFERENCES,  INDEX,ALTER, SHOW DATABASES, SUPER,  CREATE TEMPORARY TABLES, 
  LOCK TABLES, EXECUTE, REPLICATION SLAVE,  REPLICATION CLIENT,  CREATE VIEW, 
  SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE,CREATE USER, EVENT, TRIGGER, CREATE TABLESPACE
  ALL : 以上所有权限，一般是普通管理员拥有的
 with grant option：超级管理员才具备的，给别的用户授权的功能

授权管理
4.1 语法

grant ALL    on     wordpress.* to    wordpress@'10.0.0.%'   identified by 
'123';
grant 权限   on   范围   to   用户    identified by '密码'                                          
grant select,update,insert,delete    on   范围   to   用户    identified by '密 
码'
范围：
*.* 
wordpress.*
wordpress.t1

4.2 例子：

1. 从windows中的navicat软件使用root管理mysql数据库
grant all on *.* to root@'10.0.0.%' identified by '123';

2. 创建 zhihu业务用户能够对zhihu业务库进行业务操作
grant select,update,delete ,insert on zhihu.* to zhihu@'10.0.0.%' 
identified by '123';

查看用户权限

mysql> show grants for root@'localhost';

回收权限

revoke delete on zhihu.* from 'zhihu'@'10.0.0.%';

库
建库

mysql> create database oldguo charset utf8mb4;
mysql> show databases;
mysql> show create database oldguo;

改库

mysql> alter database oldguo1 charset utf8mb4;

删库

mysql> drop database oldguo1;

表

   注意建表建库规范：
   1、库名和表名是小写字母
  为啥？
   开发和生产平台可能会出现问题。
   2、不能以数字开头
   3、不支持-  支持_
   4、内部函数名不能使用
   5、名字和业务功能有关（his,jf,yz,oss,erp,crm...）

建表

   use 库名；#进入某库下建表
   例子： 
    create table oldguo (
    ID int not null primary key AUTO_INCREMENT comment '学号',
    name varchar(255) not null comment '姓名', 
    age tinyint unsigned not null default 0 comment '年龄',
    gender enum('m','f','n') NOT null default 'n' comment '性别'
    )charset=utf8mb4 engine=innodb;

改库

 -- 例子：
 -- 在上表中添加一个手机号列15801332370.（重点*****）
 -- alter table oldguo add telnum char(11) not null unique comment '手机号';

 -- 练习：
 -- 添加一个状态列
 ALTER TABLE oldguo ADD state TINYINT  UNSIGNED NOT NULL 
 DEFAULT 1 COMMENT '状态列';
 -- 查看列的信息
 DESC  oldguo;
 -- 删除state列(不代表生产操作)
 ALTER TABLE oldguo DROP state;
 -- online-DDL : pt-osc (自己研究下***)

 -- 在name后添加 qq 列 varchar(255)
 ALTER TABLE oldguo ADD qq VARCHAR(255) NOT NULL UNIQUE  
 COMMENT 'qq' AFTER NAME;

 -- 练习 在name 之前添加wechat列 
 ALTER TABLE oldguo ADD wechat VARCHAR(255) NOT NULL 
 UNIQUE COMMENT '微信' AFTER ID;

 -- 在首列上添加 学号列：sid(linux58_00001)
 ALTER TABLE oldguo ADD sid VARCHAR(255) NOT NULL UNIQUE 
 COMMENT '学生号' FIRST;

 -- 修改name数据类型的属性
 ALTER TABLE oldguo  MODIFY NAME VARCHAR(128)  NOT NULL ;

 DESC oldguo;
 -- 将gender 改为 gg 数据类型改为 CHAR 类型
 ALTER TABLE oldguo  CHANGE gender gg CHAR(1) NOT NULL 
 DEFAULT 'n' ;

删库

 mysql> drop database oldguo1;

（DML数据操作语言）
9.1 插入数据行

INSERT 
--- 最简单的方法插入数据
DESC oldguo;
INSERT INTO oldguo VALUES(1,'oldguo','22654481',18);
--- 最规范的方法插入数据(重点记忆)
INSERT INTO oldguo(NAME,qq,age) VALUES ('oldboy','74110',49);
--- 查看表数据（不代表生产操作）
SELECT * FROM oldguo;

9.2 修改数据行

 UPDATE (注意谨慎操作！！！！)
 UPDATE oldguo SET qq='123456' WHERE id=5 ;

9.3 删除数据行

 DELETE (注意谨慎操作！！！！)
 DELETE FROM oldguo WHERE id=5;

9.4 生产需求：将一个大表全部数据清空

   DELETE FROM oldguo;
   TRUNCATE TABLE oldguo;

  DELETE 和 TRUNCATE 区别
 1. DELETE 逻辑逐行删除，不会降低自增长的起始值。
 效率很低，碎片较多，会影响到性能
 2. TRUNCATE ，属于物理删除，将表段中的区进行清空，不会产生碎 
 片。性能较高。

9.5 生产需求：使用update替代delete，进行伪删除

 1. 添加状态列state (0代表存在，1代表删除)
 ALTER TABLE oldguo ADD state TINYINT NOT NULL DEFAULT 0 ;

 2. 使用update模拟delete 
 DELETE FROM oldguo WHERE id=6; 
 替换为
 UPDATE oldguo SET state=1 WHERE id=6;
 SELECT * FROM oldguo ;

select 的用法
10.1 作用

获取MySQL中的数据行

10.2 单独使用select

select @@xxxx;获取参数信息。
mysql> select @@port;     #获取端口号
mysql> show variables like '%innodb%'; #查看存储引擎

10.3 select 函数()

mysql> select database();  #查看当前所在库
mysql> select now();   #显示当前时间
mysql> select version(); #查看当前数据库版本
mysql> select user(); #查看当前数据库版本

10.4 使用语法

select语法执行顺序（单表）
select开始 ----> 
from子句 --->
where子句--->    #条件哪一列或哪一列等于(> < >= <=  <>)
group by子句--->  #哪一列进行分组
select后执行条件--->
having子句 ---->  #与where一样
order by子句 ---->#哪一列的排序      加desc是倒序
limit   #LIMIT M,N     跳过M行，显示N行
        #LIMIT X OFFSET Y 跳过Y行，显示X行

GROUP BY 配合聚合函数应用
常用聚合函数（在select后使用）：
AVG()   #哪一列的平均值
COUNT() #（）内可增加需要的字符串
SUM(）  # 哪一列的综合
MAX()   #哪一列中的最大值
MIN()    #哪一列的最小值
GROUP_CONCAT()    #将哪一列的值分组写成一行数据

多表连接查询
11.1 传统的连接：基于where条件

 1. 找表之间的关系列 
 2. 排列查询条件

11.2 两个表连接

A表  B表  
方法一：select  A.列名,B.列名  from A,B  where A.关系列 = B.关系列;
方法二：select A.列名,B.列名 
               from  A  
                        join  B on A.关系列=B.关系列
              where 条件 
              group by  哪一列分组 
              order by 哪一列排序 
               limit   查看几行

11.3 多表连接

 select A.列名,B.列名  
 from  A 
          join  B on A.关系列=B.关系列 
          on A.关系列=C.关系列
          on C.关系列=D.关系列
         ...
 where     条件 
 group by  哪一列分组 
 order by  哪一列排序 
 limit      查看几行 

别名

表别名   #别名在from后设置
SELECT a.tname  ,GROUP_CONCAT(d.sname) 
FROM teacher AS a   
JOIN course AS b 
ON a.tno = b.tno
JOIN sc as c
ON b.cno = c.cno
JOIN student AS d
ON c.sno = d.sno
WHERE a.tname='oldguo' AND c.score<60
GROUP BY a.tno;

列别名  #在select后设置
select count(distinct(name)) as 个数  from world.city;

外连接

左外连接
SELECT a.name,b.name ,b.surfacearea 
FROM city AS a 
LEFT JOIN country AS b     #增加一个left，表示显示a表中，除了某一列交集的值，进行显示，b表显示成了null。
ON a.countrycode=b.code 
WHERE   a.population<100

右外连接
SELECT a.name,b.name ,b.surfacearea 
FROM city AS a 
RIGHT JOIN country AS b     #增加一个right，表示显示a表中，除了某一列交集的值，进行显示，b表显示成了null。
ON a.countrycode=b.code 
WHERE   a.population<100

索引
14.1 作用

优化查询，类似于书中的目录

14.2 算法分类

Btree 
Rtree
Hash 
fulltext
gis

14.3 辅助索引细分

单列
多列（联合索引）
唯一

14.4 索引树高度的影响因素

1. 数据行 分表
2. 索引列值较长 前缀索引 
3. 数据类型 

14.5 索引的管理操作

1、设置索引
alter table 表 add index idx_列名(列名); 
alter table 表 add index idx_列名1_列名2(列名1,列名2); #联合索引

2、删除索引
alter table 表 drop index idx_列名1_列名2;

3、前缀索引
alter table city add index idx_列名（5个字符）(列名(5));  #列名很长，取前五个字符当索引名称

4、查看语句使用的索引
desc  select 列名 from 表 where 条件 ;
drop index 索引名 from 表；

5、查看表的索引
show index from 表;
desc 表;

6、查看索引时列的含义
 type: ref             索引的应用级别
possible_keys:         可能会使用到的索引  
key:                   实际上使用的索引
key_len:               联合索引覆盖长度
rows:                  查询的行数（越少越好）
Extra:                 额外的信息

14.6 执行计划的分析

type的索引的应用级别
1、ALL :   全表扫描，不走索引
  1） 没建立索引
  2）建立索引不走的()
        1、查找表内所有的数据
        2、条件列无索引
        3、条件内出现了不等于、like‘%n%’、（<、>、=、not in不或者）的列未设置索引的情况
        4、设置列得属性是字符串，查询sql语句条件是数字（未加单引号）
        5、表中数据增删改查比较频繁，导致索引失效   
        6、where条件后有group by、oerder by 、hiving也会出现不走索引，应当增加where条件列和排序列的联合索引。      

2、Index ：全索引扫描
     一般情况：查找某列下所有的的值

3、range ：索引范围扫描
  出现的情况：辅助索引 : > < >= <= like ,   in   or    
             主键： != 
4、ref : 辅助索引等值查询
5、eq_ref ：在多表连接查询是on的条件列是唯一索引或主键
6、const,system ： 主键或唯一键等值查询
7、Extra: NULL              额外的信息
    出现：using filesort的字符串，基本都是sql语句中出现group by或oerder  by 等，未设置联合索引，将where条件列和group by或oerder  by 等后的值设立联合索引。

show full processlist;   #具体查看连接数据库的用户、host、还有具体的语句等

存储引擎

15.1 使用 SELECT 确认会话存储引擎

SELECT @@default_storage_engine;
show variables like '%engine%';

15.2 默认存储引擎设置(不代表生产操作)

会话级别:
set default_storage_engine=myisam;

全局级别(仅影响新会话):
set global default_storage_engine=myisam;

重启之后,所有参数均失效.
如   要永久生效:
写入配置文件
vim /etc/my.cnf
[mysqld]
default_storage_engine=myisam
存储引擎是表级别的,每个表创建时可以指定不同的存储引擎,但是我们建议统 
一为innodb.

15.3 show确认每个表的存储引擎

1）单个表
show create table 表；
2）确认每个表的存储引擎
select table_schema table_name ,ebgnie from infomation_schema.tables where table_schema not in ('sys','mysql','information_schema','performnance_schema' );

Master [world]>show table status; #当前库下所有表的存储引擎
Master [world]>show create table city;  #建表语句

15.4 修改一个表的存储引擎

单表修改：alter table t1 engine innodb;

生产需求： 
将oldboy数据库下的所有1000表，存储引擎从MyISAM替换为innodb 
select concat("alter table ",table_name," engine innodb;")
from information_schema.tables 
where table_schema='oldboy'
into outfile '/tmp/alter.sql';

15.5 平常处理过的MySQL问题--碎片处理

环境:centos7.4,MySQL 5.7.20,InnoDB存储引擎
业务特点:数据量级较大,经常需要按月删除历史数据.
问题:磁盘空间占用很大,不释放
处理方法:
以前:将数据逻辑导出,手工drop表,然后导入进去
现在:
对表进行按月进行分表(partition,中间件)
业务替换为truncate方式

定期执行：
alter table t1 engine='innodb';

InnoDB存储引擎物理存储结构

1.最直观的存储方式(/data/mysql/data)
ibdata1：系统数据字典信息(统计信息)，UNDO（回滚）表空间等数据
ib_logfile0 ~ ib_logfile1: REDO（重做日志）日志文件，事务日志文件。
ibtmp1： 临时表空间磁盘位置，存储临时表
frm：存储表的列信息
ibd：表的数据行和索引

2. 表空间(Tablespace)
   共享表空间
需要将所有数据存储到同一个表空间中 ，管理比较混乱
5.5版本出现的管理模式，也是默认的管理模式。（数据字典，undo，临时 
表，索引，表数据）
5.6版本以，共享表空间保留，只用来存储:数据字典信息,undo,临时表。
5.7 版本,临时表被独立出来了
8.0版本,undo也被独立出去了

  共享表空间设置
共享表空间设置(在搭建MySQL时，初始化数据之前设置到参数文件中)
[(none)]>select @@innodb_data_file_path;
[(none)]>show variables like '%extend%';
innodb_data_file_path=ibdata1:512M:ibdata2:512M:autoextend
innodb_autoextend_increment=64 

  独立表空间
从5.6，默认表空间不再使用共享表空间，替换为独立表空间。
主要存储的是用户数据
存储特点为：一个表一个ibd文件，存储数据行和索引信息
基本表结构元数据存储：

3.InnoDB 核心特性
1、事务（Transaction）
2、MVCC（Multi-Version Concurrency Control多版本并发控制）
3、行级锁(Row-level Lock)
4、ACSR（Auto Crash Safey Recovery）自动的故障安全恢复
5、支持热备份(Hot Backup)
6、复制Replication: Group Commit , GTID (Global Transaction ID) ,多线程 
(MTS,Multi-Threads-SQL ) 

4.查看存储引擎
show engines;   #查看当前数据库支持的存储引擎
select table_schema,table_name ,engine from information_schema.tables 
where engine='innodb';   #所有库和表的存储引擎

5.事务的ACID特性
Atomic（原子性）、Consistent（一致性）、Isolated（隔离性）、Durable（持久性）
影响了DML语句（insert  update   delete  一部分select）
  5.1 手动事务开始
  begin;     #开始事务
  commit：#提交事务

  5.2  自动提交策略
  select @@autocommit;   #查看事务提交策略
  set global autocommit=0;  #0代表不自动提交，1代表自动提交策略属于默认。
  5.3 事务的隐式控制
  导致提交的非事务语句：
   DDL语句： （ALTER、CREATE 和 DROP）
   DCL语句： （GRANT、REVOKE 和 SET PASSWORD）
   锁定语句：（LOCK TABLES 和 UNLOCK TABLES）
  
  导致隐式提交的语句示例：
  TRUNCATE TABLE
  LOAD DATA INFILE
  SELECT FOR UPDATE

 6.  如何指定和修改存储引擎
 (1) 通过参数设置默认引擎
 (2) 建表的时候进行设置
 (3) alter table t1 engine=innodb;
 表空间
 12.2.1 共享表空间
 innodb_data_file_path
 一般是在初始化数据之前就设置好
 例子:
 innodb_data_file_path=ibdata1:512M:ibdata2:512M:autoextend
 innodb_buffer_pool_size
生产一般调整为物理内存的50%-80%左右（你的系统中只有一个mysql实例）

7. 双一标准
 7.1  select @@innodb_flush_log_at_trx_commit;  # 查看其中之一的默认
  配置文件写入：innodb_flush_log_at_trx_commit=1
 1：每次事务提交，都会立即刷下redo到磁盘（redo buffer --每事务-->os buffer --每事务--磁盘）
 0：表示当事务提交时，不立即做日志写入操作（redo buffer --每秒-->os buffer --每秒--磁盘）
 2：每次事务提交引起写入文件系统缓存（redo buffer --每事务-->os buffer --每秒--磁盘）

7.2 Innodb_flush_method=(O_DIRECT, fdatasync) #其中另一
    控制的是,log buffer 和data buffer,刷写磁盘的时候是否经过文件系统缓存
    show variables like '%innodb_flush%';   # 查看另一双一默认
    O_DIRECT  :数据缓冲区写磁盘,不走OS buffer
    fsync :日志和数据缓冲区写磁盘,都走OS buffer
    O_DSYNC  :日志缓冲区写磁盘,不走 OS buffer
 7.3使用建议
 最高安全模式
 innodb_flush_log_at_trx_commit=1
 Innodb_flush_method=O_DIRECT
 最高性能:
 innodb_flush_log_at_trx_commit=0
 Innodb_flush_method=fsync

8.  redo日志有关的参数
 innodb_log_buffer_size= 128M   业务系统CPU压力有关
 innodb_log_file_size=256            一般是1-2倍
 innodb_log_files_in_group = 3     3-4组