mysql常见不常见的所有命令

-- ##############################   数据库操作：#############################

-- 登录到mysql数据库
mysql –u  用户名  -p密码
-- 创建数据库
create database 数据库名;
-- 查看，创建数据库的指令
show create databse 数据库名;
-- 显示数据库
show databases;
-- 删除数据库:
drop database 数据库名;
-- 使用某个数据库
use 数据库名;
-- 查看隔离级别
select @@tx_isolation;
-- 查看警告
show warnings;
-- #################### 备份数据库: ##################
-- 备份数据库	在doc控制台下直接执行
mysqldump –u 用户名 –p密码 数据名 > 存放路径 文件名.sql
--恢复数据库:
1.创建一个数据库 mydb2 ,但是这个数据库目前是空.
2.use 数据名
3.在mysql 控制台下 使用 source 备份文件路径
source  文件路径.sql

-- ############################## 表操作： #############################

-- 创建表
create table if not exists users9(
-- 键操作
id1 int primary key comment '学号',	-- comment注释
id2 primary key auto_increment,	-- auto_increment自增
id3 int unique,	-- 唯一	(但是允许有多个NULL)
-- 小数类数据
_float float(5,2) zerofill,	-- 共5位精度，其中2位小数
money decimal(10,2),	-- 定点，无精度损失，超出的小数部分遵循四舍五入，但是不可进位(整数部分多一位)，存放整数或小数
-- 日期类数据
birthday date,	-- (年-月-日)	插入数据时加'...'单引号
hiredate1 datetime, -- (年-月-日 : 时:分:秒)
hiredate2 timestamp, -- update时,自动更新。格式同上
-- 如果你的表中有两个timestamp字段，只要你更新任何非timestamp字段的值，则第一个timestamp列会更新成当前时间,而第二人timestamp列的值不变。
year1 year,	-- 默认year(4) 可填二位数：插入70为1970，69为2069。1970~2069
year2 year(2),	-- 插入70为70
-- int类数据
int_1 tinyint unsigned,	-- 1字节 -128~127 unsigned：0-255
int_2 smallint,	-- 2字节 -2^15 ~ 2^15 - 1 unsigned：2^16 - 1
int_3 int,		-- 4字节
int_4 bigint,
-- 其他类数据
name varchar(64) default NULL,
gender enum('男','女','保密') not null,	-- 1,2,3...
_bit bit(64),	-- 最大64，默认1 二进制存放，查询select _bit+0;
content text,	-- 文本字符串
bool boolean	-- 波尔类型
)character set utf8, collate utf8_general_ci, comment='表的注释';

-- 复制表的结构
create table 新表 like 原表;
-- 复制表结构和数据(无约束)
create table 新表
as 
select 源表字段1 新表字段1, 源表字段2 新表字段2, ...
from 源表;
-- 蠕虫复制(批量复制数据)
insert into 新表 (新表字段) select 源表字段 from 原表;

-- 查看所有表
show tables;
-- 查看以x结尾的表
show tables like '%x';
-- 查看表结构
desc(describe) 表名;
show columns from student;
show create table test2;
-- 修改表名
rename table 原表名 to 新表名;
-- 修改表的字符集
alter table 表名 character set(=) 字符集名;
-- 修改表的注释
alter table test1 comment '修改后的表的注释';
-- 修改字段的注释
alter table test1 modify column id int comment '修改后的字段注释';
-- 查看表注释
show create table test1;
-- 查看字段注释
show full columns from test;
-- 删除表
drop table 表名;
truncate table 表名;	-- (不可回滚,但速度快)

-- ############################## 事务： #############################

-- 开启事务
start transaction;
-- 回滚事务(回到开启事务前的状态)
rollback;
-- 提交事务(提交后不可回滚)
commit;
------------------------
-- 方法二：
-- (1)mysql 控制台是默认自动提交事务(dml)
-- (2)如果我们要在控制台使用事务，应该这样
set autocommit=false;
savepoint 保存点名称;
-- //经过操作后...（例如删除）...	注意回滚之前不要设置立即提交事务
rollback to 保存点名称;-- 回滚后，数据还在		但是使用truncate删除表后，没有该数据
commit;	-- 提交事务

-- ############################## 字段操作： #############################

-- 添加新的列
alter table 表名 add 字段名 数据类型;
-- 添加tinyint宽度为2，zero填充格式。如：01，02
alter table 表名 add  字段名 tinyint(2) zerofill;
-- 添加新的列到第一个位置
alter table 表名 add 字段名 数据类型 first;
-- 删除某列
alter table 表名 drop 字段名;
-- 修改列数据类型
alter table 表名 modify 字段名 新的数据类型;
-- 修改列数据类型(且放到id之后)
alter table 表名 modify 字段名 新的数据类型 (after id);
-- 修改字段名和数据类型
alter table 表名 change /*column)*/ 原名 改后名 数据类型;
-- 建立索引
alter table 表名 add index 索引别名 (字段名 /* asc desc 降序升序*/);
-- 删除索引
drop index 索引别名 on 表名;


-- ############################## 增删改： #############################

-- 插入数据
insert into 表名(字段名) values(值);
-- 蠕虫复制(作用2.可以迅速的让表中的数据膨胀到一定的数量级: 测试表的压力以及效率)
insert into 表名 select * from 表名;
-- 修改(更新)数据	可以使用limit
update 表名 set 字段名 = 值;
-- 删除行	可以使用limit
delete from 表名 where 条件;	-- (默认：不可回滚)
-- 复制表数据
create table 新表表名(字段1 字段1类型,字段2 字段2类型);
insert into 新表表名 (select 字段1, 字段2 from 原表表名);
-- 主键冲突(重复)时修改主键对应数据(内部实现：若发生主键冲突，1删除该行数据，2插入新数据)
insert into 表名 values('重复的主键值', '新数据') on duplicate key update 字段名 = '新数据';


-- ################################ 查询操作：####################################

-- distinct 剔除重复数据
select distinct * from 表名 where 条件;
-- 模糊查询	'_'单字 	'%'多字
select * from 表名 where 字段名 like 'a%';	-- 查询以a开头的
-- NULL值使用指定值替换显示
select ifnull(字段名, 0)  from 表名;
-- 查询结果使用别名显示
select english as '英语', math as 数学 from 表名;
-- 查询时另起别名进行查询
select 别名.* from 表名 as 别名;
select * from 表名 as 别名 where 别名.字段名 = 数据;
select * from 表名1 as 别名1 inner join 表名2 as 别名2 on 别名1.字段名 = 别名2.字段名;
-- in多匹配查询
select * from 表名 where 字段名 in (值1, 值2, ...);
-- 计数count(当*改为字段名时，NULL值不参与同类函数的计算)
select count(*) from 表名;

-- 分页查询limit: 从m行查询n条数据(包含m行; n可以超出表长; 一个参数表示从0下标开始查n条数据)
select * from student limit m-1, n-1;

-- 联合查询, 取并集, 默认去除重复行; [all]: 不去重
select * from 表名
union [all]
select * from 表名;
-- 显示的字段名取决于字段名1、2	且要求字段名1、2和字段名3、4类型完全一致
select 字段名1, 字段名2 from 表名1
union
select 字段名3, 字段名4 from 表名2;
-- 联合查询中使用order by: 加括号且使用limit限定各个查询数量
(select 字段名1, 字段名2 from 表名1 order by 字段名 desc limit 999)-- 降序
union
(select 字段名3, 字段名4 from 表名2 order by 字段名 limit 999);-- 升序

-- ################################ 连接查询：####################################

-- 交叉连接(原则：条件数量至少是表的个数-1)
select sno from 表1 ,/*cross join*/ 表2 where 条件;

-- #外连接:	显示数据左表一定在左边，右表一定在右边
-- 左连接(左表有的，而右表没的置为NULL)	查询结果一定 >= 左表数量
select 字段 from 表1 
left join 表2 
on 表1.字段名1 = 表2.字段名1;

-- 右连接(与上面相反)
select 字段 from 表1 
right [outer] join 表2 
on 表1.字段名1 = 表2.字段名1;

-- 内连接（左右连接的交集）
select 字段 from 表1 
inner join 表2 
on 表1.字段名1 = 表2.字段名1;

-- 自动匹配同名字段并合并显示(也就是自然连接↓)	所有连接都可以使用
select 字段 from 表1 inner join 表2 using(sno);

-- 内外自然连接（了解）:自动匹配同名字段并合并显示
select 字段 from 表1 
natural left join 表2;

-- ################################ 视图操作：####################################

-- 创建视图(视图：表的映射)当所依据表更新数据，视图也跟着更新
create view 视图名 as 
select ...;
-- 修改视图
alter view 视图名 
as
select ...;
-- 删除视图
drop view 视图名;
-- 单表插入数据(如果有基表的非空或者无默认值字段，必须在此视图中含有)
insert into 视图名 values(...);
-- 单表删除数据		
delete from 视图名 where 条件;
-- 多表视图更新数据
update 视图名 set 条件;
-- 创建视图(限制字段更新)
create view 视图名 
as 
select ... where age > 30 with check option;-- 视图中不能出现小于30岁的 此时更新视图数据受到限制
-- 创建视图指定算法为临时表
create algorithm = temptable view 视图名 
as
select ...;

-- ################################ 约束：####################################

-- mysql中key 、primary key 、unique key 与index区别
https://blog.youkuaiyun.com/nanamasuda/article/details/52543177

-- ############## 主键：################

-- 创建单一主键
create table test9(
id int primary key,
name char
);
-- 创建复合主键
create table test8(
id int,
name char,
primary key(id, name)
);
-- 添加主键	前提：无重复数据
alter table 表名 add primary key(字段名，字段名);
alter table 表名 modify 字段名 列数据类型 primary key;	-- 不建议
-- 删除主键
alter table 表名 drop primary key;	-- 删除所有主键

-- ############## 唯一键：################

-- 创建唯一键
create table unique1(
id int unique key,
name char
);
create table unique2(
id int,
name char,
unique(id, name)
);
-- 添加唯一键
alter table 表名 add unique /*(key)*/(字段名，字段名);
-- 删除唯一键
alter table unique1 drop index id;

-- ####### 自增长(一张表最多只能有一个)：#######

-- 创建自增长
create table my_auto(id int /*auto_increment*/ primary key auto_increment, num int);
-- 添加自增长
alter table my_auto2 add primary key auto_increment(id);
-- 插入数据
insert into my_auto values(NULL,11);			-- 1 11
insert into my_auto values(default,12);			-- 2 12
insert into my_auto values(4,14);				-- 4 14
insert into my_auto (num) values(15);			-- 5 15
insert into my_auto values(3,13);				-- 3 13
-- 修改下次自增长值	修改值必须大于当前值
alter table my_auto auto_increment = 8;
-- 删除自增长	
alter table my_auto modify id int;
alter table my_auto drop primary key;	-- 两步(需要维持主键依然存在)
-- 查看自增长对应的变量: 
show variables like 'auto_increment%';
-- 修改自增步长	修改不仅此张表(修改是会话级)
set auto_increment_increment = 5; 
-- 修改自增初始值
set auto_increment_increment = 100;
-- 清空表并初始化自增长值
truncate 表名;

-- ############## 外键：################

-- 创建指定模式外键：删除置空,更新级联（默认严格模式）
-- 删除置空的前提条件: 外键字段允许为空(如果不满足条件,外键无法创建)
create table student(
id int primary key comment '学生id'
)；
create table class1(
class_id int primary key comment '班级id',
class_name varchar(20) not null,
id int comment '学生id',
foreign key(id) references student(id) -- 方式1	(默认别名为：表名_ibfk_1)
constraint 别名 foreign key(id) references student(id/*primary key/unique*/)on delete set null on update cascade -- 方式2
);
-- 添加外键：(子表的增改受父表约束，父表的删改受子表约束)
alter table 子表名 add [constraint 别名] foreign key(子表字段名) references 父表名(父表字段名);
-- 删除外键:	(查看别名：show create table 表名; 默认：表名_ibfk_1)
alter table 表名 drop foreign key 别名;
-- ################################# 函数：####################################

-- ############## 日期和时间函数：################

-- 显示日期
select day(now());
select current_date();
-- 显示时间
select time(now());
select current_time();
-- 显示时间戳
select now();
select current_timestamp();
-- 显示秒...
select second(now());
select second(current_time());
-- 显示365天后的日期
select date_add(now(), interval + 365 day);
select date_sub(now(), interval - 365 day);
-- 查询两个小时内的消息				(该日期         +       2小时 >=  现在日期)
select * from 表名 where date_add(字段名(时间类型), interval 2 hour) >= now();
-- 计算日期相差几天	datediff(date1, date2)
-- 应用：查询入职大于100天的职工
select * from 表名 where datediff(now(), 字段名) > 100;
-- 计算时间相差多少，时:分:秒	timediff(date1, date2)

-- ############## 字符串函数：################

-- 查询某列字符集
select charset(字段名) from 表名;
-- 连接字符串
select concat('ab', 'c', 'd'...);	-- abcd
select concat(字段名, 'abc') from 表名;
-- 返回子串在主串中出现的位置,没有返回0
select instr('abcdef', 'a'); 	-- 1
-- 转换为大写(字符串的位置都可用字符串类型的字段名代替)
select ucase('abc');
-- 转换为小写
select lcase('ABC');
-- 截头
select left('abcde', 3);	-- abc
-- 截尾
select left('abcde', 3);	-- cde
-- 截区间
select substring('abcd', 2, 2);		-- bc
-- 返回字符串长度
select length('abc');	-- 3
-- 替换
select replace('abca', 'a', 'x');	-- xbcx
-- 比较大小		>返回1， <返回-1， =返回0
select strcmp('b', 'a');	-- 1
-- 删除前端空格	
ltrim(string)
-- 删除后端空格	
rtrim(string)
-- 删除字符串首尾中多余的空格	
trim(string)

-- ############## 数学函数：################

-- 转换二进制
select bin(255);	-- 11111111
-- 转换十六进制
select hex(255);	-- FF
-- 向上取整
select ceiling(2.0000001);	-- 3
-- 向下取整
select floor(2.999999);	-- 2 
-- 取小数位（四舍五入）
select format(2.345, 2);	-- 2.35
-- 取模
select mod(3,2); -- 1
-- 随机数	范围[0, 1]
select rand();

-- ################################### 其他：##################################


-- delimiter 更改结束标志
delimiter ||	-- sql语句以||结束
delimiter ;		-- 还原

-- begin与end语句 之间可执行的多个语句
begin
-- 执行语句;...; 这里需要用上delimiter
end


-- ################################ 权限操作：####################################

-- 创建用户并设置权限(记得刷新)	创建用户名和密码为 normal, 只能查询的用户, localhost 只能在本机IP地址登录 
grant select on sport.* to 'normal'@'localhost' identified by password '*F3F91B23FC1DB728B49B1F22DEE3D7A839E10F0E' with grant option;	-- with grant option 允许授权给其他用户
-- 查询出你的密码对应的字符串，如："normal" 对应上面的 'F3F91B23FC1DB728B49B1F22DEE3D7A839E10F0E'
select password('你想输入的密码');
-- 刷新权限
flush privileges;
-- 查看当前用户的权限
show grants;
-- 查看某个用户的权限
show grants for 'normal'@'localhost';
-- 授权，把更新权限给 normal'@'localhost 用户(需要在root账户下操作)
grant update on sport.* to 'normal'@'localhost' with grant option;
-- 回收权限
revoke update on sport.* from 'normal'@'localhost';
-- 被授权 用户拥有 授权的权限(在授权时候后面加上)
with grant option;
--  创建用户
create user 用户名@ip地址 identified by '密码';
--  删除用户
drop user 用户名@IP地址;

-- ################################ 触发器操作：####################################

-- 创建
create trigger 
trigger_name 	-- 触发器名称
trigger_time	-- 触发时机: before 或 after
trigger_event 	-- 触发事件: insert 、 update 或 delete
ON tbl_name		-- 建立触发器的表名
for each row	
trigger_stmt	-- 触发器程序体(有多行)

-- ############################# 存储过程和函数操作：#################################

-- 创建存储过程和函数
create procedure 函数名(in 参数名 参数类型)	-- in代表输入
select * from 表名 where 字段名 = 参数名;

-- 调用函数
call 函数名(参数);

-- 删除存储过程和函数
drop procedure 函数名;