操作命令集
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 +查询语句；
扫一扫
1797
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
