mysql常用命令（一）
MySQL常用命令大全
1.常用部分
创建数据库
create database dbname;
删除数据库
drop database dbname;
选择数据库
use dbname;
创建表
CREATE TABLE IF NOT EXISTS book(
id INT UNSIGNED AUTO_INCREMENT,
title VARCHAR(100) NULL,
author VARCHAR(40) NOT NULL,
date DATE,
INDEX title_index(title(100)),
PRIMARY KEY ( id )
)ENGINE=InnoDB DEFAULT CHARSET=utf8;;
AUTO_INCREMENT 定义列为自增的属性，默认自增1，一般用作主键
NOT NULL 指定该字段不能为空，在操作数据库时如果输入该字段的数据为NULL就会报错
PRIMARY KEY 关键字用于定义列为主键
ENGINE 设置存储引擎
CHARSET 设置编码
INDEX 设置列为普通索引，也可以使用UNIQUE指定唯一索引，FULLTEXT指定全文索引
添加表字段
alter table book add press varchar(100);
删除表字段
alter table book drop press;
修改表字段
alter table book modify press char;
查看表结构
show columns from book;
desc book;
修改表名
alter table book RENAME TO book1;
修改表字段名
ALTER TABLE table_name CHANGE NAME name VARCHAR(10);
删除表
drop table book;
创建索引
create index title_index ON book(title(100));
alter table book ADD INDEX title_index(title);
删除索引
drop index title_index ON book;
插入数据
insert into book values(值1，值2，....);#值的顺序与字段在表中的顺序一致
insert into book（title,author）
values('Java'，'Java');#为指定字段赋值
insert into book（title,author）
select title,author from test;#copy另一张表的数据
修改数据
update table set title='学习笔记',date='2019-05-21' where id = 1
删除整表数据，直接摧毁表中数据，保留结构，速度更快
truncate table book;
删除符合条件的数据
delete from book where xx=yy;
查询数据
select * from book;
创建触发器
CREATE <触发器名> < BEFORE | AFTER >
<INSERT | UPDATE | DELETE >
ON <表名> FOR EACH Row<触发器主体>
例子：创建一个名为 SumOfSalary 的触发器，触发的条件是向数据表 tb_emp8 中插入数据之前，对新插入的 salary 字段值进行求和计算。
CREATE TRIGGER SumOfSalary
BEFORE INSERT ON tb_emp8
FOR EACH ROW
SET @sum=@sum+NEW.salary;
说明：
1) 触发器名
触发器的名称，触发器在当前数据库中必须具有唯一的名称。如果要在某个特定数据库中创建，名称前面应该加上数据库的名称。
2) INSERT | UPDATE | DELETE
触发事件，用于指定激活触发器的语句的种类。
注意：三种触发器的执行时间如下。
- INSERT：将新行插入表时激活触发器。例如，INSERT 的 BEFORE 触发器不仅能被 MySQL 的 INSERT 语句激活，也能被 LOAD DATA 语句激活。
- DELETE： 从表中删除某一行数据时激活触发器，例如 DELETE 和 REPLACE 语句。
- UPDATE：更改表中某一行数据时激活触发器，例如 UPDATE 语句。
3) BEFORE | AFTER
BEFORE 和 AFTER，触发器被触发的时刻，表示触发器是在激活它的语句之前或之后触发。若希望验证新数据是否满足条件，则使用 BEFORE 选项；若希望在激活触发器的语句执行之后完成几个或更多的改变，则通常使用 AFTER 选项。
4) 表名
与触发器相关联的表名，此表必须是永久性表，不能将触发器与临时表或视图关联起来。在该表上触发事件发生时才会激活触发器。同一个表不能拥有两个具有相同触发时刻和事件的触发器。例如，对于一张数据表，不能同时有两个 BEFORE UPDATE 触发器，但可以有一个 BEFORE UPDATE 触发器和一个 BEFORE INSERT 触发器，或一个 BEFORE UPDATE 触发器和一个 AFTER UPDATE 触发器。
5) 触发器主体
触发器动作主体，包含触发器激活时将要执行的 MySQL 语句。如果要执行多个语句，可使用 BEGIN…END 复合语句结构。
6) FOR EACH ROW
一般是指行级触发，对于受触发事件影响的每一行都要激活触发器的动作。例如，使用 INSERT 语句向某个表中插入多行数据时，触发器会对每一行数据的插入都执行相应的触发器动作。
注意：每个表都支持 INSERT、UPDATE 和 DELETE 的 BEFORE 与 AFTER，因此每个表最多支持 6 个触发器。每个表的每个事件每次只允许有一个触发器。单一触发器不能与多个事件或多个表关联。
另外，在 MySQL 中，若需要查看数据库中已有的触发器，则可以使用 SHOW TRIGGERS 语句。
2.函数部分
字符串title的字符数
character_length("title")
将字符串title和author合并为一个字符串
concat("title","author")
将字符串title和author合并为一个字符串，合并时使用逗号作为分隔符
concat_ws(",""title","author")
将字符串title的内容转为小写
lower("title")
将字符串title的内容转为大写
upper("title")
反转字符串title
reverse("title")
求price的绝对值
abs("price")
求price的平均值
avg("price")
求price的总记录数
count("price")
求price的最大值
max("price")
求price的最小值
min("price")
求price的和
sum("price")
返回0-1之间的随机数
rand()
date加上n天的时间
adddate("date",n)
date加上n秒的时间
addtime("date",n)
当前日期
curdate()
当前时间
current_time()
当前日期时间
current_timestamp()
d1和d2相隔的天数
datediff(d1,d2)
d1和d2相隔的月数
period_diff(d1,d2)
d减去n天的日期
subdate(d,n)
d减去n秒的时间
subtime(d,n)
服务器当前连接数
connection_id()
条件选择 case when
CASE expression WHEN condition1 THEN result1 WHEN condition2 THEN result2 .. WHEN conditionN THEN resultN ELSE result END #CASE 表示函数开始 #END 表示函数结束 #如果 condition1 成立 #则返回 result1 #如果 condition2 成立 #则返回 result2 #当全部不成立则返回 result #而当有一个成立之后，后面的就不执行了
case when
IF(expr,v1,v2)#如果表达式 expr 成立，返回结果 v1；否则，返回结果 v2。
IF
IFNULL(v1,v2)#如果 v1 的值不为 NULL，则返回 v1，否则返回 v2
IFNULL
去除结果集中的重复元素
select distinct(title) from book;
模糊查询
select * from book where title="Java%";#查询以Java开头的数据，只有这样使用索引以下两种不使用索引
select * from book where title="%Java";#查询以Java结尾的数据
select * from book where title="%Java%";#查询包含Java的数据
合并结果集
select title from book1 union select title from book2；
左连接/右连接/内连接
连接(left jion/right jion/jion/逗号)
select * from book1 left jion book2 on xx=yy #获取左表所有记录，即使右表没有对应匹配的记录
select * from book1 right jion book2 on xx=yy #获取右表所有记录，即使左表没有对应匹配的记录
select * from book1 jion book2 where xx=yy #获取两个表中字段匹配关系的记录
select * from book1 , book2 where xx=yy#同jion
分组
select count(*) from book group by author ='fangdada' #查询fangdda写了多少
排序
select * from book order by date ASC#默认就是ASC 可省略，按date升序排列
select * from book order by date DESC#按date降序排列
分页
select * from table limit 5; #返回前5行
select * from table limit 0,5; #同上，返回前5行
select * from table limit 5,10; #返回6-15行
3.性能优化部分
3.1慢查询分析
是否开启慢查询 1和ON表示开启，2和OFF表示关闭
show variables like 'slow_query_log';
查看慢日志目录
show variables like 'slow_query_log_file';
查看慢查询时间阈值
show variables like 'long_query_time';
设置慢查询时间阈值
set global long_query_time=4;
查看慢查询日志存储方式：有两种：TABLE,FILE(一般采用这种)
show variables like 'log_output';
查看没有使用索引写入慢查询日志状态
show variables like 'log_queries_not_using_indexes';
mac下安装慢日志查询工具
brew install percona-toolkit
慢查询日志分析统计
pt-query-digest /usr/local/mysql/data/slow.log
3.2 剖析单条查询
开启profile功能
set profileing = 1;
查询记录的profile,结果表格中有字段query_id
show profiles;
剖析query_id 为1的查询执行每个步骤的时间
show profile for query 1;
显示计数器，既有服务器级别的计数器，也有每个连接的会话级别的计数器
show status;
