目录
一、前言
接触MySQL也有一段时间了,经常记不住指令,但是有一些指令又经常用,如果不记录下来,很容易忘记,有时候把各种指令放到各个文档里面,很容易就混乱,要找一条指令找了半天找不到,就非常的低效率,所以干脆整理成一个文档,把项目过程中遇到的指令全部放到这个文档里,方便后续查阅。
二、指令
2.1、用户管理部分
这一部分的主要内容就是创建用户和赋予权限,假如你已经安装好了MySQL并且使用MySQLWorkbench管理工具,使用root账户登录进入了MySQL,现在要求你创建一个远程账户,用于其他人远程登录,并且要求有指定的权限。
1、显示当前有哪些用户
用户信息存在mysql.user表里,查询user这个字段即可得到有哪些用户:
select user from mysql.user;
上面这个是最简单的,不过我们有时候想知道用户的连接类型,比如是否支持远程连接,就增加一个查询字段host:
select user,host from mysql.user;
如果我们想知道用户的认证方式,就再增加一个字段plugin:
select user,plugin,host from mysql.user;
一般来说如果你之前没有创建过,就会显示红框部分的用户。
![](https://i-blog.csdnimg.cn/blog_migrate/18249d293030a2be5b0511049fb0c12b.png)
如果你想知道mysql.user表里有哪些字段和数据,直接查询整张表即可:
select * from mysql.user;
2、创建用户
create user username@'%' identified by 'password';
%表示可以从任意地址登录(网络要打通)。
也可以指定地址,比如:
create user username@'192.168.1.1' identified by 'password';
或者只允许本地登录:
create user username@'localhost' identified by 'password';
这里说明一下,用户名这里可以加单引号也可以不加,也可以用【`】这个符号,都可以,没有影响。
但是@后面的地址,必须加单引号或者【`】。
![](https://i-blog.csdnimg.cn/blog_migrate/b3b8e71de94554af531a3aeb16f85fef.png)
3、删除用户
drop user username@'%';
![](https://i-blog.csdnimg.cn/blog_migrate/db228423674168ba91ece26591f1a1c9.png)
4、给用户分配权限
注意,8.0分配权限不需要密码,也就是不需要identified by 'password';这一部分,否则会报错。
a.分配增删改查权限,并且显示它的权限
grant select,delete,update,insert on *.* to `guest3`@`%`;
show grants for guest3@'localhost';
![](https://i-blog.csdnimg.cn/blog_migrate/d9fa77efc06594ee0e5888035285d397.png)
其中的*.*可以改定指定数据库的指定表。
b.分配所有权限:
GRANT all privileges on *.* to guest3@'localhost';
![](https://i-blog.csdnimg.cn/blog_migrate/993aa2d473e0ef544267a8e24fb3f774.png)
5、回收权限
回收增删改查权限:
revoke select,delete,update,insert,grant option on *.* from guest3@'localhost';
回收所有权限:
revoke all privileges,grant option from guest3@'localhost';
注意两个语句是不一样的,后面一句没有 on *.*。
![](https://i-blog.csdnimg.cn/blog_migrate/22766f7e2c3bc05eefeab46d35a9bbe4.png)
6、查看和修改用户的plugin
这一步是为了解决部分用户使用一些客户端登录会提示1251 - Client does not support authentication protocal requested by server,说明客户端不支持8.0新的认证方式,将caching_sha2_password改为mysql_native_password即可。
ALTER USER guest3@'%' IDENTIFIED WITH mysql_native_password BY '123456';
![](https://i-blog.csdnimg.cn/blog_migrate/97c4e4b05651e065e2550ce246425e1e.png)
由于我创建出来的用户就是mysql_native_password,所以不需要修改。
补充:
如果在修改插件的时候出现错误,可现将插件改为 mysql_old_password,然后再升级成mysql_native_password。
2.2、库表设计
2.2.1、数据库操作
1、显示数据库
SHOW DATABASES;
一般来说会显示如下数据库
2、创建数据库
创建数据库名称有规范,不能用#和.等符号,可以用下划线和数字
CREATE DATABASE aaa_bbb_123;
![](https://i-blog.csdnimg.cn/blog_migrate/8e7bcff3373d594576f42a7b7d58ccc8.png)
这里说明一下,库名建议全部用小写,原因是虽然MySQL对指令大小写不敏感,但是对库名表名大小写是敏感的,如果用大小写混合很容易出错。
3、删除数据库
drop DATABASE 数据库名称;
![](https://i-blog.csdnimg.cn/blog_migrate/f546a8b561e2f6535e4b42ad67d72a1d.png)
确定这个库不要才能删!
2.2.2、表格操作
1、显示表格
需要先选择数据库,否则显示出来的表格会非常多:
use aaa_bbb_123;
然后输入显示表格
show tables;
![](https://i-blog.csdnimg.cn/blog_migrate/c49696fa86a555bf20a3ed2cef016f11.png)
可以发现没有任何表。
2、创建表格
创建一个名称为ccc_123的表格,里面有两个字段,分别为dd和ee,其中dd为int类型,4个字节长度,ee为varchar类型(可变字符串类型),长度为10个字节:
create table ccc_123 (dd int(4),ee varchar(10));
![](https://i-blog.csdnimg.cn/blog_migrate/c71c9b9c674730fbb25821150207cf91.png)
如果想查询这个表有哪些数据,可以使用:
select * from ccc_123;
![](https://i-blog.csdnimg.cn/blog_migrate/0a16955fbe47aea5587a33fe433cb729.png)
可以发现没有数据。
也可以查询表结构
desc ccc_123;
如果你之前没有输入use aaa_bbb_123;的话,可能会提示找不到这个表,所以可以改成如下:
select * from aaa_bbb_123.ccc_123;
desc aaa_bbb_123.ccc_123;
3、创建表格的时候也可以指定主键
create table ccc_456 (dd int(4) auto_increment primary key,ee varchar(10));
这样就把dd字段设置为自动增长的主键。
4、设置原本没有主键的表格中的某个字段为主键
先设置这个字段不能为空
alter table ccc_123 modify dd int(4) not null;
然后设置它为主键
alter table ccc_123 add primary key(dd);
然后查看表结构
desc ccc_123;
![](https://i-blog.csdnimg.cn/blog_migrate/1c142c58c0523d16452bdbb78156de32.png)
5、改表名
把表格ccc_123名字改为ccc_666:
alter table aaa_bbb_123.ccc_123 rename to aaa_bbb_123.ccc_666;
改完以后show tables;看一下:
![](https://i-blog.csdnimg.cn/blog_migrate/5b42380d81eb58f698642a0107d28569.png)
6、增加字段
alter table ccc_666 add ff varchar(10);
alter table ccc_666 add gg varchar(10);
增加了字段ff,gg
![](https://i-blog.csdnimg.cn/blog_migrate/521130071b1ad3fb5765381c2bf3984f.png)
7、修改字段名称
alter table ccc_666 change gg hh char(20);
将字段gg名称改为hh,并将类型改为char(20)。如果你不想修改字段类型,可以用原来的类型,但是不能省略。
![](https://i-blog.csdnimg.cn/blog_migrate/529e21d2ceb1cee0c962cd24a80907c1.png)
8、修改字段类型
alter table ccc_666 modify hh varchar(10);
将字段hh类型改为varchar(10);
![](https://i-blog.csdnimg.cn/blog_migrate/d8d9168e746e87a276e4e2209d0edd26.png)
9、删除字段
alter table ccc_666 drop hh;
![](https://i-blog.csdnimg.cn/blog_migrate/26519f1ca4ab905ea87294b628a7fd52.png)
10、删除表格
DROP TABLE table_name;
注意这个指令是删除表格,如果只是想清空数据而保留表格,可以使用以下语句:
delete from table_name;
2.2.3、索引
索引的作用和主键类似,主要用来区分两行不同的数据,如果两行数据的主键或者联合索引相同,那么就会被认为是同一条数据,后面使用插入语句的时候就会忽略或替换。
1、查询索引
show index from 表名;
![](https://i-blog.csdnimg.cn/blog_migrate/2cff77122411df5660f2b49c82b7e03e.png)
可以看到,目前的索引只有主键。
2、创建索引
![](https://i-blog.csdnimg.cn/blog_migrate/a55d8cea3d6e0bfde789abef4fc9a9a0.png)
创建了一个索引名称为suoyin1的索引,索引包含字段为ee,也就是说,ee字段相同的数据会被认为同一份数据,插入会被忽略或替换。
如果创建失败,说明表中有重复数据,需要找出来并删除。
3、创建联合索引
alter table ccc_666 add unique index suoyin2(dd,ee);
注意如果index后面不写索引名称,就会将第一个字段作为索引名称。
![](https://i-blog.csdnimg.cn/blog_migrate/97a3d97976f4e55fb2046e49430b0987.png)
创建了这条索引,dd,ee字段数据都相同的作为同一条数据库,插入时会忽略或替换。
如果创建失败,说明表中有重复数据,需要找出来并删除。
4、删除索引
alter table ccc_666 drop index suoyin1;
![](https://i-blog.csdnimg.cn/blog_migrate/56fef17c64a400c21df8f37cb27290b7.png)
可以看到索引1已经被删除。
2.3、数据操作
2.3.1、增
使用insert和replace都可以插入新的数据,其中replace还可以修改原本已有的数据。
1、直接插入数据
insert into aaa_bbb_123.ccc_666 value(1,'测试');
这种方式需要插入的内容和字段顺序一一对应
![](https://i-blog.csdnimg.cn/blog_migrate/aa0d4f78f2adea89f9c04b593a0ee567.png)
2、指定字段插入
insert into aaa_bbb_123.ccc_666(ee,dd) value('测试2',2);
我故意调换了字段位置,就是为了说明,这种插入方式对应前面的顺序即可。
![](https://i-blog.csdnimg.cn/blog_migrate/5925a9154f8e7a00b53146d5fdf67a9d.png)
3、批量插入
insert into aaa_bbb_123.ccc_666(ee,dd) value('测试4',4),('测试3',3);
![](https://i-blog.csdnimg.cn/blog_migrate/d232e724564e949af82fa6278f0aeeb4.png)
4、插入但忽略主键相同的数据
insert ignore into aaa_bbb_123.ccc_666(ee,dd) value('测试6',4),('测试5',5);
主键为4的数据行已有数据,所以新数据忽略,主键为5的正常插入。
使用这种方法可以避免插入重复数据。
![](https://i-blog.csdnimg.cn/blog_migrate/60e6469dad8e142cd2109c5cf491985b.png)
5、插入数据,当主键相同时替换
replace into aaa_bbb_123.ccc_666(ee,dd) value('测试6',4),('测试5',5);
6、表数据迁移
先创建一个空表
create table ccc_456 (dd int(4) auto_increment primary key,ee varchar(10));
![](https://i-blog.csdnimg.cn/blog_migrate/ff3bb4691482ee0d942280ba1bb0077c.png)
然后
insert into ccc_456(dd,ee)select dd,ee from ccc_666;
把这条语句拆分成两部分,后半部分就是查询ccc_666表里dd,ee字段的数据
select dd,ee from ccc_666;
前半部分就是插入语句,将数据插入到ccc_456的dd,ee字段里,注意字段顺序匹配
insert into ccc_456(dd,ee)
与前面的区别就是,原本的数据来源value('测试6',4),('测试5',5);被替换成了查询语句返回的数据。
![](https://i-blog.csdnimg.cn/blog_migrate/c5c02addd52315a01204d165616b2ae9.png)
可以发现ccc_456的数据跟ccc_666的数据一样了。
2.3.2、删
1、删除表格所有数据
delete from ccc_666;
可以看到数据都已经清空
![](https://i-blog.csdnimg.cn/blog_migrate/d2c38a9ea7297da3bd55ea266eb970d1.png)
2、删除指定数据
delete from ccc_666 where ee = '测试2';
删除字段ee中包含测试2的数据。
![](https://i-blog.csdnimg.cn/blog_migrate/6d5c3c8aaed2fd0308c3470e2f4af2ed.png)
2.3.3、改
update ccc_666 set ee='测试100' where dd=5;
将dd为5的那一行数据的ee修改为测试100
![](https://i-blog.csdnimg.cn/blog_migrate/578f241dec11ebfe92ac78f2e0aad0e9.png)
2.3.4、查
查询是一个比较复杂的语句,可以有多种组合,不过还是从最简单的开始。
1、查询所有
select * from 表名;
这个就是之前一直在用的,查询所有数据的语句,在数据量少或者测试的时候,可以使用这种方式来查询,但是数据量多,就必须做筛选。
2、指定字段查询
select dd,ee from ccc_666;
![](https://i-blog.csdnimg.cn/blog_migrate/d1624c64c3af54f5be564c0d7180390f.png)
3、指定类型查询
为了方便说明,先随便插点数据进去
![](https://i-blog.csdnimg.cn/blog_migrate/6b4e17924cbc46e021987543dda5b40b.png)
然后查询ff=f7的ee字段数据
select ee from ccc_666 where ff = 'f7';
![](https://i-blog.csdnimg.cn/blog_migrate/9a03062fc212ba3a8440aae6682cdfb4.png)
可以精确的查找到某一行某一列。
4、查询并排序
select ee from ccc_666 order by ee;
![](https://i-blog.csdnimg.cn/blog_migrate/88c34532e263012dab742f31b15a7b56.png)
可以看到ee这一列按照数字顺序排列(100是1开头所以排前面)。
也可以倒序
select * from ccc_666 order by dd desc;
![](https://i-blog.csdnimg.cn/blog_migrate/40286201dc1d8f5075f015aa1b4893e7.png)
还可以按照两个字段进行排序
select * from ccc_666 order by ff,ee;
![](https://i-blog.csdnimg.cn/blog_migrate/82f64888d800a206af45e6907243cd59.png)
可以看到字段首先按照ff的顺序进行排列,null在前,之后是f4和f7,然后再按照ee后面的数字排列。
5、查找符合两个数据的内容并按照两个字段进行排序
select * from ccc_666 where ff = 'f4' or ff = 'f7' order by dd,ee;
![](https://i-blog.csdnimg.cn/blog_migrate/17adbbd296bb1405bb68fbd5ca8d0590.png)
先找出字段中包含f4和f7的内容,然后按照dd和ee进行排序。
6、查询总数据量
select count(*) from ccc_666;
![](https://i-blog.csdnimg.cn/blog_migrate/a34d20e66d679f545d8ca774b48cf039.png)
7、查询指定字段的数据量
select count(*) from ccc_666 where ff = 'f4';
![](https://i-blog.csdnimg.cn/blog_migrate/21a6959b11eaae93433b61d04ace3de3.png)
8、查询重复数据
为了更好的说明,决定给原始数据做一些修改和增加一些数据,如下
![](https://i-blog.csdnimg.cn/blog_migrate/b86792b769195e588ecff2d994751f9b.png)
然后我们想查询出ee相同的数据
select * from ccc_666 where ee in
(select ee from ccc_666 group by ee having count(*) > 1) order by ee;
![](https://i-blog.csdnimg.cn/blog_migrate/d83f82a728a8324474596c2b22f61407.png)
别看这个语句这么复杂,其实很简单,拆分成几个部分来看,首先是括号中的内容
select ee from ccc_666 group by ee having count(*) > 1;
这个语句是查询表格中ee字段数据相同的数据,group by的作用是对数据进行分组,数量大于1的就筛选出来,这条语句的执行结果是
![](https://i-blog.csdnimg.cn/blog_migrate/a16ff0bf967294f899d9132fd4823ac6.png)
所以前面的语句就很好理解了
select * from ccc_666 where ee in '测试3','测试4' order by ee;
也就是:寻找ee='测试3'或'测试4'的数据,并按照ee排序。
9、根据多个字段查找重复数据
这个语句是需要查询出ee和ff相同的数据,查询方式如下
select * from ccc_666 where(ee,ff) in
(select ee,ff from ccc_666 group by ee,ff having count(*) > 1)
order by ee;
![](https://i-blog.csdnimg.cn/blog_migrate/5ec0d39c74993abc05fd53e6bb1bb3f5.png)
可以看到,ee和ff都相同的重复数据一共有四条,重复的类型有两种。