操作命令集
- cd C:\Program Files\MySQL\MySQL Server 5.7\bin 进入到Mysql的bin目录
- mysql -h localhost -u root -p 输入密码后进入mysql>命令行模式
- mysql> show databases[\G]; 显示数据库列表
- mysql> create database test123; 创建数据库名为test123的数据库
- mysql> drop database test123; 删除数据库名为test123的数据库（不可恢复，谨慎操作）
- mysql> show engines[\G]; 显示当前数据库支持的存储引擎
- mysql> use sys; mysql> show tables; 切换到某个数据库，然后显示该数据库下的全部表
- mysql> select * from bs_field [ limit 1]; 显示某个表的数据【一条】
- mysql> desc bs_field; 查看某个表的结构
- mysql> show create table bs_field[ \G]； 查看表的详细信息
- mysql> select version(); 查询服务器版本号
- mysql> select connection_id(); 查询当前连接数
- mysql> show processlist; 输出当前用户的连接信息
- mysql> select database(); 显示当前使用的数据库
- mysql> show index from table [\G]; 查询table表的索引情况
- mysql> CREATE USER 'james'@'localhost' IDENTIFIED BY 'tiger'; 创建用户james，密码tiger
存储引擎
1）支持的存储引擎
- InnoDB
- MyISAM
- MEMORY
- ARCHIVE
- CSV
- BLACKHOLE
InnoDB：
①Mysql5.5.5版本之后默认引擎；②支持事务，支持行锁定，支持外键；③创建3个文件，ibdate1（10M自动拓展的数据文件），ib_logfield0、ib_logfield1（5M大小的日志文件）
MyISAM：
①Mysql5.5.5版本之前默认引擎；②不支持事务；③拥有较高的查询和插入速度；④每张表最大索引数64，每个索引最大列数16；⑤索引可以作用在BLOB和TEXT上；⑥创建数据库产生三个文件，frm文件存储定义表，数据文件拓展名为.MYD，索引拓展名为.MYI；
MEMORY：
①将表中的数据存储到内存中；②存储引擎执行HASH和BTREE；③不支持TEXT和BLOB；
其他
数据类型
1）INT(11)中数字11表示该数据类型指定的显示长度，其实际长度最长可到取值范围最大数值；
2）金钱存储用DECIMAL，浮点数（FLOAT和DOUBLE）和定点数（DECIMAL）类型都可以用（M,N）表示，M为精度，N为标度；DECIMAL和DOUBLE取值范围一样，DECIMAL以串的形式存放，
3）TIMESTAMP和DATETIME显示格式相同，宽度固定在19个字符，TIMESTAMP的取值范围小于DATETIME，取值范围是1970-01-01 00:00:01 UTC~2038-01-19 03:14:07 UTC,UTC为世界标准时间；TIMESTAMP存储的时候按照世界标准时间（UTC）进行存储，存储时对当前时区进行转换；检索时会转换会当前时区；
4）CHAR和VARCHAR后者时可变字符，检索的时候，CHAR会将首位空格去掉再返回，VARCHAR则不会；
5）BLOB是二进制字符串，可以存储图片、音频等信息，TEXT只能存储纯文本；
运算符
运算符优先级
sql语句
1）IFNULL(V1,V2) 如果V1不为空，返回V1，否则返回V2
2）between A and B , 该查询条件包含开始值和结束值
3）and的优先级大于or的优先级
4）多列排序 ... order by A desc , B desc
5）group by 子句中使用 with rollup 显示记录数 （select lan,count(*) from tableA group by lan with rollup;最后一行显示总记录数），注
：使用rollup时不能使用order by
6）count(*)和count(字段名)区别，count(*)计算空值，后者不计算空值，空值即显示null的值
7）limit [m,] n
- limit 2 ； 显示前两条数据
- limit 3,5； 从第3+1条数据开始，一共显示5条
8）max(字符串) 比较字符串的ASCII码
9）exists拓展(后续补充)
10）合并查询，union：去重、排序，union all：全集
11）条件更新：update person set info='学生' where age between 19 and 26;
完整查询语句
SELECT DISTINCT <select_list>
FROM <left_table>
<join_type> JOIN <right_table>
ON <join_condition>
WHERE <where_condition>
GROUP BY <group_by_list>
HAVING <having_condition>
ORDER BY <order_by_condition>
LIMIT <limit_number>
执行顺序
1）FORM: 对FROM左边的表和右边的表计算笛卡尔积，产生虚表VT1。
2）ON: 对虚表VT1进行ON过滤，只有那些符合<join-condition>的行才会被记录在虚表VT2中。
3）JOIN： 如果指定了OUTER JOIN（比如left join、 right join），那么保留表中未匹配的行就会作为外部行添加到虚拟表VT2中，产生虚拟表VT3。
4）WHERE： 对虚拟表VT3进行WHERE条件过滤。只有符合<where-condition>的记录才会被插入到虚拟表VT4中。
5）GROUP BY: 根据group by子句中的列，对VT4中的记录进行分组操作，产生VT5。
6）HAVING： 对虚拟表VT5应用having过滤，只有符合<having-condition>的记录才会被 插入到虚拟表VT6中。
7）SELECT： 执行select操作，选择指定的列，插入到虚拟表VT7中。
8）DISTINCT： 对VT7中的记录进行去重。产生虚拟表VT8.
9）ORDER BY: 将虚拟表VT8中的记录按照<order_by_list>进行排序操作，产生虚拟表VT9.
10）LIMIT：取出指定行的记录，产生虚拟表VT10, 并将结果返回。
索引
索引是一个单独的、存储在磁盘上的数据库结构，包含着对数据库表里所有记录的引用指针；
- 优点：①加快查询速度；②分组和排序子句进行查询时，可以显著减少查询中分组和排序的时间；
- 缺点：①创建和维护索引耗费时间；②索引占用磁盘空间，如果有大量索引，索引文件可能比数据文件更快达到最大尺寸；③增加、删除和修改的时候要维护索引，降低数据的维护速度；
分类：
- 普通索引和唯一索引
- 单列索引和组合索引
- 全文索引
- 空间索引
全文索引：只有MyISAM引擎支持，可以作用在char、varchar、text上，类型为FULLTEXT；
空间索引：只有MEMERY引擎支持，空间索引数据类型有4种GEOMETRY、POINT、LINESTRING和POLYGON。使用SPATIAL进行拓展，创建空间索引的列必须声明为NOT NULL；
设计原则
1）索引不是越多越好，影响增删改性能，占磁盘空间
2）避免对经常更新的表增加过多索引
3）数据量小的表最好不要使用索引
4）在经常分组排序的列上面创建索引
5）尽量选择较少数量的列作，减少磁盘空间和维护的开销
6）尽可能让一个索引覆盖较多的查询
7）尽量采用短索引，能用10个长度的索引不要用30长度的索引
创建索引sql
- 普通索引：建表语句，index（列）
- 唯一索引：建表语句，unique index(列)
- 单列索引：建表语句，index SingleIdx（name（20））；对name列前20字母索引
- 组合索引：建表语句，index MultiIdx（id，name，age（100）） 命中原则：最左前缀原则
关键字 explain
查看索引是否命中
句法：explain + DQL
视图
语法：CREATE VIEW view_name as SELECT lie_a,lie_b,lie_c FROM table;
查看表是否是视图：SHOW TABLE STATUS LIKE '视图名'；如果Comment：VIEW；即为视图
查看视图字段来自哪张表：SHOW CREATE VIEW view_name [\G];
修改视图语句：ALTER VIEW view_name AS SELECT ....FROM table_name;
说明：视图的增删改查和正常表一样，修改和新增的内容直接作用在源表，可以在视图中创建索引；
权限管理
权限表说明
user表：Mysql最重要的权限表，记录允许连接到服务器的账号信息，里面的权限是全局的；
db表：存储了用户对某个数据库的操作权限，决定用户能从哪个足迹存取哪个数据库；
host表：存储了某个主机对数据库的操作权限，配合db表对给定主机上数据库级的操作权限做更细致的控制；
tables_priv表：用来对表设置操作权限；
columns_priv表：用来对表的某一列设置权限；
proces_priv表：对存储过程和存储函数设置操作权限；
请求核实过程
用户向mysql发出操作请求==》user表==》db+host表==》tables_priv表==》columns_priv表==》全通过后方可进行操作；
操作用户sql
查看用户权限：SHOW GRANTS FOG 'username'@'localhost';
删除指定用户：DEOP USER 'username'@'localhost';
创建用户：mysql> CREATE USER 'james'@'localhost' IDENTIFIED BY 'tiger';
数据的备份与恢复
数据备份：mysqldump
对表备份语法：mysqldump -u用户名 -p密码 -h地址 -P端口号 dbname [tbname [,tbname...]] > 路径+文件名
示例：mysqldump -uroot -proot -h192.168.200.128 -P3306 bookDB book > C:/db/book_20201010.sql
对库备份语法：mysqldump -u用户名 -p密码 -h地址 -P端口号 --databases [dbname [,dbname]] > 路径+文件名
示例：mysqldump -uroot -proot -h192.168.200.128 -P3306 --databases bookDB , bookDB2 > C:/db/book_20201010.sql
示例2备份系统中所有数据库：mysqldump -uroot -proot -h192.168.200.128 -P3306 --all-databases > C:/db/book_20201010.sql
语法：mysqldump --opt -u用户名 -p密码 -hIP地址 -P端口号 --default-character-set=utf8 -B 数据库名 > 文件地址+文件名称
将host1上的sourceDb复制到host2的targetDb，前提是host2主机上已经创建targetDb数据库：mysqldump --host=host1 --opt sourceDb| mysql --host=host2 -C targetDb
注意：只有安装Mysql Client端才能执行命令
参数说明
--opt：①建表语句包含drop table if exists tableName；② insert之前包含一个锁表语句lock tables tableName write，insert之后包含unlock tables
--all-databases：备份某个MySQL主机上的所有数据库
数据恢复：mysql
语法：mysql -u用户名 -p密码 -hIP地址 -P端口号 --default-character-set=utf8 -B 数据库名 < 文件地址+文件名称
示例：mysql -uroot -proot -h192.168.200.128 -P3306 --default-character-set=utf8 -B testuser < ./conf_sql/recover_db/conf.sql
Mysql日志
mysql>show variables like 'log_%'; 查看是否启用了日志
mysql> show variables like 'log_error'; 获取错误日志的详细位置
mysql> show variables like 'log_bin'; 确认你日志是否启用了二进制日志
mysql> show binary logs; 查看二进制日志文件名
mysql> show binlog events; 回放日志事件
bin> mysqlbinlog d:\programfiles\mysql\logbin.000001; 二进制日志查看，日志里面记录的所有的DDL和DML语句，但select语句除外
慢查询日志
mysql> show variables like 'long%'; 查看慢查询时间设定， long_query_time变量是定义慢于多少秒的才算“慢查询”
mysql> set long_query_time=1; 设置慢查询时间，注： 设置1, 也就是执行时间超过1秒的都算慢查询。
mysql> show variables like 'slow%'; 慢查询设置详情
mysql> set global slow_query_log='ON' 开启慢查询日志记录，执行完立即开启
Mysql优化
查看sql性能：explain/desc +查询语句；
