net start mysql80  # 启动服务
net stop mysql80   # 关闭服务

mysql [-h IP地址] [-P 3306] -u 用户名 -p

-h ：Mysql服务所在的主机IP
-u ：Mysql数据库用户名
-P ：Mysql服务端口号，默认3306
-p ：数据库用户对应的密码

# 查看数据库存储路径，进行更改或查看自己的数据库内容
show variables like '%datadir%'
select @@'datadir'

/*  查询所有数据库  */
show databases;
/*  查询当前数据库  */
select database();

/*  创建数据库  */
create database [if not exists] 数据库名 
[default charset 字符集] [collate 排序规则]

/*  创建表   */
create table 表名(字段1[field] 数据类型[type] comment "注释");  //注释可省略

/*  删除指定数据库  */
drop database [if exists] 数据库名;

use 数据库名;

show tables;

desc 表名;

show create table 表名;

/*1、增加字段*/
alter table 表名 add 字段名 类型(长度) [comment 注释][约束];
/*2、修改数据类型*/
alter table 表名 modify 字段名 新数据类型(长度);
/*3、修改字段名和字段类型*/
alter table 表名 change 旧字段名 新字段名 类型(长度) [注释][约束];
/*4、删除字段*/
alter table 表名 drop 字段名;
/*5、修改表名*/
alter table 表名 rename to 新表名;

/*     数字类型      */        
tinyint :字节
smallint : 短整型
mediumint :占用3字节
int / integer : 整型4字节
float : 单精度4字节
double : 双精度8字节
decimal(小数的,十进制数的) : 

/*    字符串类型   */
char : 1字节 (0-255)
varchar : 0-65535 ( 变长字符串)

# 日期时间类型字段
date 只包含年月日
datetime  包含年月日 时分秒 

in  and  or not  <>  like [%:匹配0或多个字符]  [between and]   

update 表名 set [字段] where ...
delete from [表名] where 条件
insert into [表名](字段列表,可省略代表全字段插入记录) value(记录值)  # 插入单条记录
insert into [表名](字段列表,可省略代表全字段插入记录) values(记录值) # 插入多条记录

select distinct……[as 别名] from 表名 where …… group by 分组字段列表 having 分组后条件列表
order by 排序方式 (asc升序 desc 降序) limit 分页参数(起始索引,每页展示记录数)
-- 起始索引 = （页码 - 1）* 每页展示的记录数

# 去重查询
select distinct [字段列表] from 表名;

# union all 不会删除重复记录
select 字段列表 from 表1……
union [all] 
select 字段列表 from 表2……; 

select 字段列表 from 表名 limit 起始索引,查询记录数;
从第一页开始查询，起始索引可以省略

# 查询前多少条记录
select 字段列表 from 表名 limit 要取的数量 offset 要跳过的数量

# 查询薪资 和 领导id一致的员工信息
select * from emp where (salary,managerid) = (select salary,managerid from emp)

1、隐式内连接
 SELECT  字段列表   FROM   表1 , 表2 [别名]  WHERE   条件 ... ;
2、显示内连接
 SELECT  字段列表   FROM   表1  [ INNER ]  JOIN 表2  ON  连接条件 ... ;

select 字段列表 from 表1 left [outer] join 表2 on 条件……; #左外连接
select 字段列表 from 表1 right [outer] join 表2 on 条件……; #右外连接

#1、where 之后出现的子查询
select name, age, job from emp where salary > (select avg(salary) from emp);

#2、from 之后出现的子查询
-- 查询入职日期在 2006/1/1 之后的员工信息，及其部门信息
select e.*,d.*
from (select * from emp where entrydate > '2006/1/1') e left join
    dept d on e.dept_id = d.id;
    
#3、select 之后出现的子查询 
select (select name from emp where salary > 10000) from emp……;

/*  创建用户 */
主机名默认是localhost
create User '用户名'@'主机名' identified by '密码';
任意主机可以访问该用户
create user '用户名'@'%' ……
修改用户密码
alter user '用户名' @'主机名' identified with '新密码';
删除用户： drop user '用户名' @'主机名';

1、授予权限
grant 权限列表  on 数据库名.表名 to '用户名' @'主机名';
2、查询权限 show grants for '用户名' @'主机名';
3、撤销权限 remove 权限列表 on 数据库名.表名 to '用户名' @'主机名';
/*  授权时，可用*代替数据库名和表名  */

/*字符串函数*/
拼接 concat(s1,s2……);
左填充 lpad(str,n,pad); 用pad对str的左边进行填充，达到n个字符串长度
去掉字符串头部和尾部空格 trim(str);
substring(str,start,len);  返回字符串从start开始的len个字符长度
substring_index(要截取的字段名, '分割符' , pos截取位置) # pos:1左侧开头开始，-1右侧开始

/*数值函数 */
ceil(x) 向上取整
floor(x) 向下取整
round(值,保留小数位数);   

/*日期函数*/
day(date);  获取日期
month(date) : 获取月份 year(date):年份
curdate() : 返回当前日期
now()    ： 返回当前日期和时间
#向日期添加指定的时间间隔
date_add(date,interval expr type) date：日期表达式，expr：时间间隔 ,type:时、秒、分、日、月……

datediff(date1,date2); 返回d1和d2之间的日期差

/*流程函数*/
if(value,t,f); 正确返回t，错误返回f
ifnull(v1,v2); 不为空返回v1，否则v2
case 字段 when 过滤 then 否则是... 

# if(value,t,f) : 如果value为true 返回t，否则返回f
# ifnull(value1,value2) : 如果value 不为空返回value1，否则value2 【类似三目运算符】

# case when then语句
-- (1)
case 
	when age is null then '其他'   # 每一次选择when后无 ,
	when age < 20 then '...'
	when ……
end as age_cnt [将上述语句 判断完记录的值新建字段存入]

-- (2)
case 字段
	when result1 then ''
	when result2 then ''
	else ''
end as '' 别名字段

select [count(字段名) | max() | avg() | sum()……] from 表名

# 基本语法
函数名([expr]) over子句

函数名([expr]) over(partition by <要分的组> order by <要排序的列> rows betewwn <数据范围>)

# 对于数据取值的解释，演示
rows between 2 proceding and current row  # 代表取当前行和前2行
rows between unbounded prodeding and current row   # 取当前行和之前所有行 unbounded:无限的
rows between n proceding and m follwing     # 取前n行，下m行，共m+n行

-- 若 缺少窗口子句，默认 rows between unbounded prodeding and current row
-- 若少 order by 和 窗口子句，默认 rows between unbounded proceding and unbounded follwing

-- 1、专有窗口函数
rank() 、dense_rank() 、row_number()、ntile(n)
-- row_number()，生成行的编号
-- rank() ,      生成排名，根据生成的编号，比如前3条数据排名一致，则为1，第4条排名则为4(跳跃排序)
-- dense_rank(), 生成排名，根据实际的排名1，2，3……（连续排序）
-- ntile(n)  , n为切分的片数，即将所有记录平均分成n片，适用于 将各个阶段的记录编号，或者取前xxx的数据

-- 2、聚合类窗口函数
sum() 、 count() avg() ……

/* 约束的类型 */
1、 not null # 非空约束
2、 unique   # 唯一约束
3、 primary key #主键约束
4、 check     # 检查约束
5、 foreign key # 外键约束

/*外键约束 -- 操作  */
# 1、已建表，添加外键约束
alter table 表名 add constraint(约束，限制) 外键名称 foreign key references(关联，提及) 主表字段名
# 2、创建表时：
create table 表名{
	字段 数据类型,
	……
	[constraint] 外键名称 foreign key 外键字段名 references 主表字段
};

1、no action   /*当在父表中删除/更新对应记录时，首先检查该记录是否有对应外键，
                     如果有则不允许删除/更新。 (与 RESTRICT 一致) 默认行为  */
                     
2、restrict	  /*  当在父表中删除/更新对应记录时，首先检查该记录是否有对应外键，如果有则不
                 允许删除/更新。 (与 NO ACTION 一致) 默认行为 */
                 
3、cascade   /*当在父表中删除/更新对应记录时，首先检查该记录是否有对应外键，如果有，则
						也删除/更新外键在子表中的记录。*/   
4、set null      /* 当在父表中删除对应记录时，首先检查该记录是否有对应外键，如果有则设置子表
				中该外键值为null（这就要求该外键允许取null）。 */
5、set default     /* 父表有变更时，子表将外键列设置成一个默认的值 (Innodb不支持) */

-- 事务操作方式
1、是否开启事务操作
2、更改事务提交方式 

#事务操作
-- 1、查看/设置事务提交方式
select @@autocommit;
set @@autocommit=0;   设置为手动提交
-- 2、开启事务
start transaction(begin);
begin ;     # 另一种简便方式
-- 3、提交事务
commit;
-- 4、回滚事务
rollback;

-- A事务还没完全执行完成，未提交，B读取到了A还没有提交的数据(脏读)
-- A事务正在执行select语句，B事务同时更新数据，并进行提交，然后就发生A前后读取不一致
-- A事务先进行查询id为1的语句，未查找到，B事务同时进行插入id为1的数据，然后A事务再次进行插入id=1数据，插入失败，再次进行查询id=1数据仍无法找到

-- (RU) Read uncommitted 读未提交 脏读、不可重复读、幻读d都会出现
-- (RC) Read committed   不可重复读、幻读会出现
-- (RR) Repeatable Read(Mysql默认隔离级别) 幻读会出现
-- (S)  Serializable     都不会出现

1、查看事务隔离级别
select @@transaction_isolation;  查看@@（系统变量信息）
2、设置事务隔离级别
set [session(会话级别，仅在当前窗口有效)/ global] transaction isolation level
[read ……]
# 事务隔离级别越高，数据越安全，但是性能越低。

# 创建用户
create user username identified by 'password';

# 查看用户
select user ,host from mysql.user;

grant  权限 on 数据库.表 to 用户名@主机名 identified by 'password'

# 示例：为 u_sxt 用户分配只能查询 tlias库中的emp表，并且只能在本机登录的权限，密码为：yyy
grant select on tlias.emp to u_sxt@'localhost' identified by 'yyy';

flush privileges;

drop user username@localhost;

# 查询建表语句
show create table dept;
# 建表时指定存储引擎
engine = InnoDB;

# 通过sql查询变量，看是否一张表对应一个表空间文件
show variables like 'innodb_file_per_table';

#由于ibd文件是二进制存储，无法直接查看内容，利用指令查看
ibd2sdi 文件名

# 1、创建索引
create [unique | primary] index [index_name] on [表名]
# 2、查看索引
show index from [表名]
# 3、删除索引
drop index [index_name] on [表名]
# eg 为user表创建关于 email、age的联合索引
create index in_union on user(email,age)

# 查看当前数据库执行 增删改 的执行次数
--  session 查看当前会话    global   查询全局会话
show [session | global] status

show global status like 'Com%';

# profiles 能在SQL优化时帮我们了解 时间在哪里耗时大，have_profiles参数，查看当前MySQL是否支持
# 具体语法
# 1、查看当前数据库是否支持profiles
select @@have_profiling
# 2、默认情况profiling是关闭的（数值为0），需要开启，可以指定会话或者全局级别
set [session | global] profiling = 1

# 3、进行一系列查询，然后查看耗时
select * from tb_user;
select count(*) from tb_user;
show profiles;

show profile for query [id];  # 查看指定id的SQL语句耗时
show profile cpu for query [id]  # 查看指定id的SQL 语句 cpu使用情况

# 在select 语句之前加上关键字explain / desc
explain select 字段列表 from 表名 where 条件;

load data load infile [本地路径]
into table [表名]
fields terminated by ','
lines terminated by '\n';
# terminated：结束  filed：字段

# 根据id（主键）字段进行查询
select * from tb_sku where id = 1;  # 测试耗时 11ms
# 根据 无索引字段 sn 进行查询
select * from tb_sku where name = 'xxx';  # 测试耗时3s 669ms
# 为 sn 字段添加索引，再次查询查看耗时
create index idx_sku_sn on tb_sku(sn);
select * from tb_sku where name = 'xxx';   # 耗时如下图 5ms

# 假设现在存在一个联合索引 idx_pro_age_stu 
select * from tb_sku where profession = 'xxx' and age = 18 and stus = 'x';  # 使用了索引
select * from tb_sku where age = 18;   # 联合索引失效，因为跳过了最左侧的profession字段
select * from tb_sku where profession = 'xxx' and stus = 'x'  # 仅stus索引部分失效，因为跳过了age字段

select * from tb_sku where profession = 'xxx' and age > 30 and stus = 'x';  # 索引stus失效
# 使用 >= 、<= 、 between 可以避免索引失效
select * from tb_sku where profession = 'xxx' and age >= 30 and stus = 'x';

# 1、使用函数进行计算导致索引失效
select * from tb_sku where substring(sn,10,2) = '15';
# 2、写错类型导致索引失效
select * from tb_sku where sn = xxx;    # 未加引号索引失效

# 1、使用模糊查询
-- 模糊尾部索引不失效，大数据量时避免头部模糊
explain select * from tb_sku where sn like '%11';    # 模糊查询首部模糊会索引失效
select * from tb_sku where sn like '110%';           # 索引不失效，type = range
# 2、or 连接条件
-- or 前后必须都存在索引，否则都不会使用索引
select * from tb_sku where id = 10 or num = 300;

-- use index  使用索引
explain select * from tb_user use index(idx_user_pro) where profession='xxx';
-- ignore index 忽略索引
explain select * from tb_user ignore index(idx_user_pro) where profession='xxx';
-- force index 必须使用的索引
explain select * from tb_user force index(idx_user_pro) where profession='xxx';

一张表, 有四个字段(id, username, password, status), 由于数据量大, 需要对 以下SQL语句进行优化该如何进行才是最优方案: select id,username,password from tb_user where username =  'itcast';

# 前缀索引的语法
create index idx_name on 表名(字段名(n))   -- n为截取该字段的前缀长度

# 选择性：该字段不重复的值占所有记录的比值
count(distinct 字段名) / count(*)      -- 完整字段选择性为1
count(distinct substring(字段,开始,结束)) / count(*)   -- 截取前缀，计算选择性值

# 插入少量数据
insert into [表名] values(),()……

# 手动控制事务提交
start transaction;
insert into ……
commit;

# 主键顺序插入
乱序： 8 1 9 12 77……
顺序： 1 2 3 4  5……

# 插入大量数据 100w+
-- 1、连接服务端时
mysql --local-infile -uroot -p 
-- 2、设置全局参数开关
set global local_infile=1;
-- 3、加载大量数据脚本文件
load data load infile 'xxx'
into table xxx
fields terminated by 'x'
lines terminated by 'x';

create index idx_user_age_phone on tb_user(age,phone);
explain select id,age,phone from tb_user order by age,phone;   -- 索引生效，且排序方式 using index 
explain select id,age,phone from tb_user order by phone,age;  -- 索引失效，全表扫描
# Backward index scan，这个代表反向扫描索
explain select id,age,phone from tb_user order by age,phone desc;  

# 在没有索引情况下执行，出现 using temporary 使用了临时表
explain select profession,count(*) from tb_user group by profession;

# 创建联合索引之后，using index
create index isx_user_profession_age on tb_user(profession,age);
explain select profession,count(*) from tb_user group by age;   -- 仍使用临时表，因为不满足最左前缀法则

# 举个例子，现在需要查询第 200w 索引开始的10条数据，怎样优化

-- 子查询
select id from tb_sku order by id limit(2000000,10);  -- 查询到的所需临时表
-- 完整查询（使用了自连接查询，隐式）
select s.* from tb_sku s, ( select id from tb_user order by id limit 2000000,10 ) a 
where s.id = a.id;   

select * from tb_sku where id > 3500000 limit 1000;  # 查询速度极快QAQ

update course set name = 'xxx' where id = 1

-- 当前的 name 字段未建立索引
update course set name = 'xxx' where name = 'xxx'  

create [or replace] view 视图名称[列名列表] as [select 语句]   with [ cascaded  |  local ]  check  option ]
-- or replace : 替换视图，可省略
-- with cascaded check option ： 检查选项，用于通过视图往表中插入修改数据是否满足视图查找条件

# 1、查看创建视图语句
show create view 视图名称;
# 2、查看视图数据：可以当作查看表数据
select * from 视图名称 ……;

# 两种修改方式
create or replace  view 视图名称[列名列表] as select语句 with [ cascaded  |  local ]  check  option ]
alter view 视图名称[列名列表] as select语句 with [ cascaded  |  local ]  check  option ]

drop view[if exists] 视图名称

# 创建视图使用的 SQL 语句，注意在出现两张表中字段名一致时，需要起别名
select s.name student_name,s.no,c.name course_name from tb_student s,tb_course c,tb_student_course sc
where s.id = sc.student_id and  sc.course_id = c.id;
# 创建视图
create view tb_stu_course_v [上述sql查询语句];
# 查看视图
select * from tb_stu_course_v;

create procedure 名称([参数列表])
begin
	-- SQL 语句
end;

call 存储过程名称([参数]);

select * from information_schema.routines where routine_schema = 'xxx' -- 查询指定的存储过程信息
show create procedure 存储过程名称;    -- 查询某存储过程定义

drop procedure [if exists] 存储过程名称;

# 1、查看系统变量
show [session | global] variables [like ‘'xxx'];  -- 可以通过模糊查询
select @@[session | global] 系统变量名;
# 2、设置
set [session | global] 系统变量名 = 值;  -- 也可以使用@@方式

# 1、赋值（可以使用 = 或者 :=）
set @var_name = 'xxx' , @var_name1 = xxx ……
# 2、使用
select @var_name;

# 声明，变量类型即为数据库字段的类型，default为初始化值
declare 变量名 变量类型 [default ...];  
# 赋值
select 字段名 into 变量名 from 表名;

# 示例：
create procedure p1()
begin
	declare stu_count int default 0;         
	select count(*) into stu_count from tb_stu;      
	select stu_count;
end;
call p1();

-- elseif 、 else 均为可选
if 条件1 then
	...
elseif 条件2 then
	...
else
	...
end if;

# 语法
create procedure 存储过程名称([in/out/inout 参数名 参数类型],[]……)
begin 
	-- SQL
end;

# 根据传入分数，返回等级
create procedure p1([in score int],[out result varchar(10)])
begin
	if score >= 80 then
		set result = '优秀';
    elseif score >= 60 thens
    	set result = '及格';
    else set result = '不及格';
    end if;
end;
-- 定义用户变量，@result来接受返回数据
set @score = 99,result = '';
call p1(@score,@result);

-- 当case_val 值匹配，执行对应语句
case case_val
	when [val1] then [],
	when [val2] then [],
	……
	else []
end case;

-- 当条件成立时，执行对应语句
case
	when [condition1] then [],
	when [condition2] then [],
	……
	else []
end case;

while 条件 do
	-- SQL语句
end while;

--  先执行一次逻辑，然后判定UNTIL条件是否满足，如果满足，则退出。如果不满足，则继续下一次循环
repeat
	-- SQL语句
	until 条件
end repeat;

# begin_label、end_label 是自定义的两个标记
[begin_label:] loop
	-- SQL
end loop [end_label];

leave label;     -- 退出指定标记的循环体
iterate label;  -- 直接进入下一次循环

# 例：计算1+……+n 值，n为传入参数
create procedure p1(in n int)
begin 
	declare tol int default 0;
	sum:loop      -- 循环开始标志
		if n<= 0 then
			leave sum;
         end if;
         set tol = tol + n;
         set n = n - 1;   
     end loop sum;   -- 循环结束标志
     select tol;
 end;

# 声明游标
declare 游标名称 cursor for 查询语句;
# 打开游标
open 游标名称;
# 获取游标记录，一个变量可以接收一个字段的一个值
fetch 游标名称 into [变量1,变量2……];
# 关闭游标
close 游标名称;

DECLARE  handler_action handler for [condition_val……] [statement];

-- handler_action 的取值：  
	CONTINUE: 继续执行当前程序
	EXIT: 终止执行当前程序
-- condition_value 的取值： 
	SQLSTATE  sqlstate_value: 状态码，如 02000
	SQLWARNING: 所有以01开头的SQLSTATE代码的简写
	NOT FOUND: 所有以02开头的SQLSTATE代码的简写
	SQLEXCEPTION: 所有没有被SQLWARNING 或 NOT FOUND捕获的SQLSTATE代码的简写   

create function 存储函数名称(参数列表)
returns type [characteristic]
begin
	-- SQL语句
	return ……;
end;
-- characteristic说明
-- deterministic：相同的输入参数产生相同的结果
-- no SQL：不包含SQL语句
-- reads SQL data：包含读取数据的语句，但不包含写入数据的语句

# 触发器创建
create trigger 触发器名称
[before | after] [insert|update|delete]
on 表名 for each row    -- 行级触发器
begin
	-- 执行的SQL语句
end;

# 查看触发器
show triggers;

# 删除触发器
drop trigger [schema_name.]触发器名称;  -- 不加数据库名称，默认删除当前数据库的触发器

-- 示例：通过触发器记录 tb_user 表的数据变更日志，将变更日志插入到日志表user_logs中，insert
-- 准备工作 : 日志表 user_logs
create table user_logs(
	id int(11) not null auto_increment,
	operation varchar(20) not null comment '操作类型, insert/update/delete',
	operate_time datetime not null comment '操作时间',	
	operate_id int(11) not null comment '操作的ID',
	operate_params varchar(500) comment '操作参数',
	primary key(`id`)
)engine=innodb default charset=utf8;

-- 变更日志，insert触发，update，delete大致相同
create trigger tb_user_add_trigger after insert on tb_user for each row
begin
	insert into user_logs values(
    	null,'insert',now(),new.id,
        concat('新插入的数据为：id=',new.id,',name=',new.name,',phone=',new.phone,',email=',new.email
              ',profession=',new.profession)
    );
end;

# 1、锁定数据库
flush tables with read lock;
# 2、备份数据（注意该工具不是sql语句，在命令行下执行即可）
mysqldump -u root -p xxx [数据库名] > [备份的数据库文件.sql]
# 3、解锁
unlock tables;

# 加锁
lock tables 表名…… read/write
# 释放锁
unlock tables;   -- 或者客户端断开连接

# 比如现在执行,这里只需要读取元数据，所以加的是 MDL_SHARED
create table t2 like t1;
-- MDL_SHARED_READ，需要读取元数据，表数据
select ……
-- MDL_SHARED_WRITE，需要读取元数据，修改表数据
insert | update | delete
-- MDL_EXCLUSIVE，需要修改元数据，修改表数据
DDL 语句

# 查看元数据锁
select object_type,object_schema,object_name,lock_type,lock_duration from 
	performance_schema.metadata_locks;

-- 添加 IS
select …… lock in share mode;
-- 添加 IX
insert | update | delete | select …… for update;

# 以学生表(id,name,no)表为例，表中id为主键索引且无其他索引字段，执行update 加行锁的操作
-- 会话1
begin;
update tb_user set name = 'xxx' where name = 'yyy';
-- 会话2
begin;
update tb_user set name='23' where name = 'sdf';   -- 此时会话2中的update语句被阻塞，因为会话1对tb_user加了表锁

mysql [option] [database]
--
  -u : username
  -p : password
  -h : hostname （域名或 IP地址）
  -P : port （端口号）
  -e : execute  （执行SQL语句并退出）

mysqladmin [option] command
--
   -u : username
   -p : password
   -e : execute (执行SQL语句并退出)

mysqlbinlog log-file1 log-file2 ……
--
   -d : database
   -o : offset (忽略掉日志中的前n行命令)
   -r : result (将输出的文本格式日志输出到指定文件)
   -s : short  (显示简单格式，忽略一些信息)

mysqlshow -uroot -pxxx 
--
   -count   (显示数据库及表的统计信息，可以不指定数据库、表)

mysqldump [option] database(待备份数据库) > 备份路径及备份文件名 
连接选项：
	-u : username
	-p : password
	-h
	-P
输出选项：
	-T : --tab=name （自动生成两个文件，一个.sql（表结构语句）,另一个.txt（数据文件））

mysqlimport [option] db_name testfile testfile2 ……