mysql5.7从零开始学_《从零开始学习Mysql5.7》笔记

操作命令集

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

FROM

JOIN

ON

WHERE

GROUP BY

HAVING

ORDER BY

LIMIT

执行顺序

1)FORM: 对FROM左边的表和右边的表计算笛卡尔积，产生虚表VT1。

2)ON: 对虚表VT1进行ON过滤，只有那些符合的行才会被记录在虚表VT2中。

3)JOIN： 如果指定了OUTER JOIN(比如left join、 right join)，那么保留表中未匹配的行就会作为外部行添加到虚拟表VT2中，产生虚拟表VT3。

4)WHERE： 对虚拟表VT3进行WHERE条件过滤。只有符合的记录才会被插入到虚拟表VT4中。

5)GROUP BY: 根据group by子句中的列，对VT4中的记录进行分组操作，产生VT5。

6)HAVING： 对虚拟表VT5应用having过滤，只有符合的记录才会被 插入到虚拟表VT6中。

7)SELECT： 执行select操作，选择指定的列，插入到虚拟表VT7中。

8)DISTINCT： 对VT7中的记录进行去重。产生虚拟表VT8.

9)ORDER BY: 将虚拟表VT8中的记录按照进行排序操作，产生虚拟表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'   开启慢查询日志记录，执行完立即开启

参考地址：https://www.cnblogs.com/jevo/p/3281139.html

Mysql优化

查看sql性能：explain/desc +查询语句；