MySQL命令大全

一、简单命令

1.创建数据库

1
2
create database my_database;
-- charset 字符集 collation 校对集

2.显示数据库

1
show databases;

3.显示当前在哪个数据库

1
select database();

4.显示部分数据库

1
2
3
show databases like'my%';
-- 匹配模式:
--   %为多个未知字符，_为单个未知字符

5.显示数据库创建语句

1
show create database mydatabase;

6.进去数据库

1
2
3
-- use 数据库名
-- 出现Database changed表示已经进入指定数据库
use mydatabase;

7.修改数据库

1
2
-- alter 数据库名 ...
alter mydatabase;

8.修改数据库字符集

1
alter database mydatabase charset utf-8;

9.删除数据库

1
2
3
-- (删除前确保数据库中的数据不会被用到)
-- drop database 数据库名
drop database mydatabase;

10.创建数据表

1
2
3
4
5
6
-- create table 表名(字段名 字段类型[字段属性],....)[表选项];
-- 表选项 Engine: 存储引擎  Charset: 字符集  Collate: 校对集
-- 这里可以先使用use 数据库 ，也可以在表名前加 数据库.
create table class (
	name varchar(10)
);

11.创建结果一样的表

1
create table 表名 like 已有表的表名;//可以复制其他数据库的表  like XX.XXX

12.显示所有表

1
show tables;

13.显示部分表

1
show tables like'my%';

14.查看表结构

1
2
desc 表名
show columns from 表名

15.查看表创建语句

1
2
show create table 表名;
-- ; \g \G是结束符 \G更直观显示

16.修改表名

1
rename table 表名 to 新表名;

17.修改表选项

1
alter table 表名 表选项 [=]新值

18.增加字段

1
alter table 表名 add column `字段` 字段属性(int...);/*  注意``必须加*/

19.修改字段名

1
alter table 表名 change 旧字段名 新字段名 字段属性;

20.修改字段属性

1
alter table 表名 modify 字段名 字段新属性;

21.删除字段

1
alter table 表名 drop 字段名;

22.删除表

1
drop table 表名[1,2....]--可多个

23.插入数据

1
2
insert into 表名[(字段列表)] values(对应字段列表)
insert into 表名 values(对应字段列表)

24.查询数据

1
2
3
select * from 表名
select 字段 from 表名
select 字段 from 表名 where 字段名=值

25.删除数据

1
delete from 表名 [where 条件];/*如果没有where条件，系统会自动删除表中所有数据*/

26.修改数据

1
update 表名 set 字段名 = 新值 [where 条件] /*如果没有where条件，系统会修改那个字段的所有值*/

27.字符集

1
2
3
set names gbk;
-- 修改字符集
set 系统名 = 字符集(gbk)

28.查看系统保存的三种关系处理字符集

1
show variables like 'character_set%';

29.mysql备份

1
2
3
4
5
6
7
-- SQL备份
mysqldump -u -p 数据库[表1 [表二]] > 备份文件地址
-- 如：mysqldump.exe -hlocalhost -P3306 -uroot -proot mydatabase > C:/server
-- 数据还原
mysql.exe -u-p 数据库 <文件位置 
source SQL 文件位置
-- 如：mysql -hlocalhost -P3306 -uroot -proot mydatabase < C:/server

二、查询语句

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
完整语句：
select select选项 字段列表 from 数据表 where 条件 group by 分组 having 条件
order by 排序 limit 限制
--select选项 
all /*默认= * */
distinct /*去重*/
--动态数据
select from (select 字段列表 from 表) as 别名;

--where
where 条件
where 字段 between 数值 and 数值
--逻辑
and
or
in(值,值,..)
is null/is not null/*判断是否为空*/
like "匹配模式"/* _单个字符 %多个字符 */

--group by
group by 字段
group by 字段1 [asc|desc],字段2 [asc|desc]...
group by 字段 with rollup;/*对分组进行统计汇总*/
count()
avg()
sum()
max()
min()

--having条件
group by 字段 having 条件/*对分组后的数据进行再筛选*/
--order by排序
order by 字段[asc|desc]/*默认升序|降序 */
order by 字段1 [asc|desc] 字段2 [asc|desc]..../*可多字段排序先按第一个*/
--limit分页
limit offset,length /*offset偏移量 从哪开始,length具体取几条*/

--联合查询
select 语句
UNION [union选项]/*all,默认distinct去重*/
select 语句;
/*联合查询的order by查询时需要将select语句()括起来
还必须使用limit使用较大的值*/

--交叉连接/*笛卡尔积无实际意义*/
select * from 表1 cross join 表2;
--内连接/*通常在对数据精确要求的地方使用*/
select * from 表1 [inner] join 表2 on 匹配条件/*值1=值2*/;

--外连接/*主表记录必须保存若从表条件不匹配结果为null*/
主表 left join 从表 on 条件/*左外连接 左边为主表*/常用方式
主表 right join 从表 on 条件/*右外连接 右边为主表*/
--自然连接
select *from 表1 natural join 表2
--using关键字/*用于替代on 效果如自然连接*/
表1 join 表2 using(同名字段列表);
--子查询
--where子查询
--标量子查询
select *from 表1 where 条件判断=/<>(select 字段名 from 表2 where 条件判断);
--列子查询
select 字段 from 表1 where 条件 in (select 字段名 from 表2)
--行子查询
/*select * from my_student where (stu_age,stu_height)=(select max(stu_age),max(stu_height)from my_student) */
select 字段 from 表1 where 多字段条件=(select 字段名 from 表2)
--from子查询
--表子查询
select 字段列表 from (表子查询)as 别名 [where] [group by] [having] [order by] [limit];
--exists子查询/*返回0 或1 若返回1执行主查询*/
主查询语句 where exists子查询(查询语句);
select  * from classes where cid in (select  cno from student where birthday>'1991-01-01');/*查看生日大于**的学生的班级信息*/

三、权限管理

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
--创建用户
create user 用户名 identified by '明文密码';
用户名：用户名@主机地址
主机地址：'/%'
如：create user 'user1'@'%' identified by '1234';
--简化创建
create user user2;
--删除用户
drop user 用户名;
--用户权限管理
1.数据权限 增删查改(select\update\delete\insert)
2.结构权限 结构操作(create\drop)
3.管理权限 权限管理(create user\grant\revoke)
--授予权限
grant 权限列表 on 数据库.[.表名] to 用户名
这里：可以单表数据库.表名 可以某个数据库 数据库.* 也可以整库 *.*
权限列表： 用逗号隔开 all privileges
--删除权限
revoke 权限列表 on 数据库.[.表名] from 用户名
--刷新权限
flush privileges;
--密码丢失找回
1.停止服务 net stop mysql;
2.mysqld --console --skip-grant-tables --shared-memory
3.新开客服端 直接使用mysql进入
4.修改密码 
			update mysql.user set authentication_string=''  where user='root';/*设置无需密码登录*/
			ALTER USER 'root'@'localhost' IDENTIFIED BY '123456';
5.关闭mysqld

四、字段相关约束

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
--字段属性 
NULL属性/*尽量不要让数据为空 */
设置 NOT NULL
Default默认值/*不设置为null */
设置default 值
comment列描述/*一种维护时的注释说明查看创建表语句可看*/
设置 comment'字段描述'
--主键（1.不能为空 2.数据不能有任何重复）
--分类 业务主键(学生id..) 逻辑主键 一般自增的整型
primary key/*在字段后添加*/
primary key(字段)
alter table 表名 add primary key(字段)/*表后添加*/
alter table 表名 drop primary key /*删除主键*/
--复合主键
primary key(字段1,字段2..)/*不常用*/
--唯一键
unique key /*唯一但可以为空null可以多个*/
unique key/*在字段后添加*/
unique key(字段)
alter table 表名 add unique key(字段)/*表后添加*/
alter table 表名 drop index 唯一键名 unique key /*删除唯一键unique key可省*/
--复合主键
unique key(字段1,字段2..)
--自动增长(配合主键使用)
字段之后添加 auto_increment/*用于逻辑主键 但只适用于数值*/
--修改自增长的值
alter table 表名 auto_increment = 值;
--删除增加自增长/*重置字段属性即可*/
alter table 表名 modify 字段 属性;
--查看自增长属性
show variables like 'auto_increment%';

--主键冲突更新
insert into 表名 values(值列表)on update 字段=新值
--主键冲突替换
replace into 表名 values(值列表)
--蠕虫复制
insert into 表名 select */字段列表 from 表名
--更新数据
update 表名 set 字段名-新值 where 判断条件
update 表名 set 字段名=新值 limit 数量
--重置自增长 = 删除表中数据;
truncate table 表名;
--重置自增长
ALTER TABLE 表名 AUTO_INCREMENT= 1; /*保留数据*/
/*尽量少使用外键*/
--增加外键
foreign key(字段) references 主表(字段)
alter table 从表 add constraint '表_ibfk_1' foreign key(字段) references 主表(字段) on 约束模式
约束模式：1.district 严格模式默认 不允许操作 
		  2.cascade:级联模式,一起操作 主表变化,从表跟着变化

   		  3. set null:置空模式 主表变化,从表对应记录设置为空
	常用约束模式：on update cascade on delete set null;
	--删除外键
	alter table 从表 drop foreign key 'constraint名字';

五、进阶部分

1.视图

1
2
3
4
5
6
7
8
9
--创建视图
create view 视图名字 as select指令;
--查看视图
desc 视图名
--使用视图/*可以当表操作*/
--修改视图
alter view 视图名字 as 新select指令
--删除视图
drop view 视图名字;

2.手动事务

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
1.start transaction //此句开始之后,所有语句不会直接写入数据库(保存在事务日志中)
2.事务处理,多条语句构成
3.事务提交：commit/rollback
--设置回滚点
set savepoint 回滚点名字;
--回到回滚到
rollback to 回滚点名字;
--事务隔离级别/*安全性从低到高  效率从高到低*/
read uncommitted
read committed  /*只避免脏读*/
repeatable read  /*只会发生幻读 */
serializable /*都可以隔离*/
--设置隔离级别
set session transaction isolation level 隔离级别;
--查看隔离级别
select @@transaction_isolation;

3.系统变量

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
--查看系统变量
show variables;
set @@变量名;
--修改变量名
--局部修改不影响其他客户端
set 变量名 = 新值
--全局修改 需要重新打开客户端才生效
set global 变量名 = 值;
--会话变量/自定义变量
set @变量 = 值;
--通过查询语句为变量赋值
select @name :=stu_name,@age := stu_age from my_student limit 1;
select stu_name,stu_age from my_student order by stu_height desc limit 1 into @name,@age;
--查看变量
select @变量名;
--局部变量
--复杂语句中使用(函数/存储过程/触发器)
declare 变量名 类型 [default 默认值];

4.if 分支

1
2
3
4
5
6
7
8
--查询语句中使用
select *,if(stu_age>20,'符合','不符合') as judge from my_student;
--复杂语句中使用(函数/存储过程/触发器)
if 条件表达式 then 
	满足条件执行的语句;
else
	不满足条件执行的语句;
end if;

5.while语句

1
2
3
4
5
6
7
标识名字:while 条件 do
	[if 条件判断 then 
		lterate/leave 标识名字//循环控制
	end if;
	]
	循环体
end while;

6.内置函数

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
--字符串函数
char_length() : 判断字符串的字符数
length() : 判断字符串的字节数
concat() : 连接字符串
instr() : 判断字符在目标字符串中是否存在,若存在返回其位置,不存在返回0
lcase() : 全部小写
left() : 从左侧指定位置开始截取字符串
ltrim() : 消除左边对应的空格
mid() : 从中间指定位置开始截取,若不指定直接到最后
--时间函数
now() : 返回当前时间
curdate() : 返回当前日期
curtime() : 返回当前时间
datediff() : 判断两个日期之间的天数
date_add() : 进行时间的增加/减少
--数学函数
abs()
ceiling()
floor()
pow()
found()
--其他函数
Md5() : 对数据进行md5加密
version() : 获得版本
database() : 显示当前数据库
uuid() : 生成一个唯一的标识符

7.自定义函数

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
--自定义函数
--修改语句结束符
delimiter $$
create function 函数名(参数名 数据类型,...) return 数据类型
begin
	函数语句
	return 值;
end

delimiter ;

--查看自定义函数
show function status;

--查看函数创建语句
show create function 函数名;
--调用函数
select 函数();
--删除函数
drop function 函数名;



8.存储过程

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
-- 存储过程
-- 创建过程
如：创建一个求1到100之间的和的过程
delimiter $$
create procedure 过程名([参数列表])
begin 
	declare i int default 1;
	set @sum=0;
	while i<=100 do
		set @sum=@sum+i;
		set i = i+1;
	end while;
	select@sum;
end

delimiter ;
--查看过程
show procedure status;
--查看创建过程的语句
show create procedure 过程名;
--调用过程
call 过程名([参数列表])
--删除过程
drop procedure 过程名;
--过程参数列表
过程类型 变量名 数据类型//in int_1 int;
--过程类型
in /*提供给过程使用 相当于值传递*/
out /*给外部使用 相当于引用传递 内部也可以改变外部的值*/
inout /*给内外部使用 相当于引用传递 内部也可以改变外部的值*/

9.索引

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
--创建索引
create [unique]index indexName on 表名(字段)

--开窗函数
--分组排序
--1 2 3 4
select
    empid,
    ename,
    deptid,
    salary,
    ROW_NUMBER() over (partition by deptid ORDER BY salary desc) as row_number1
from employee;
-- 1 2 2 4
select
    empid,
    ename,
    deptid,
    salary,
    rank() over (partition by deptid ORDER BY salary desc) as row_number1
from employee;
-- 1 2 2 3
select
    empid,
    ename,
    deptid,
    salary,
    dense_rank() over (partition by deptid ORDER BY salary desc) as row_number1
from employee;