MySQL入门常用指令总结

目录

 

一、前言

二、指令

2.1、用户管理部分

2.2、库表设计

2.3、数据操作

---

一、前言

接触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;

 

一般来说如果你之前没有创建过，就会显示红框部分的用户。

 

如果你想知道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';

 

这里说明一下，用户名这里可以加单引号也可以不加，也可以用【`】这个符号，都可以，没有影响。

但是@后面的地址，必须加单引号或者【`】。

 

3、删除用户

drop user username@'%';

4、给用户分配权限

注意，8.0分配权限不需要密码，也就是不需要identified by 'password';这一部分，否则会报错。

a.分配增删改查权限，并且显示它的权限

grant select,delete,update,insert on *.* to `guest3`@`%`;

show grants for guest3@'localhost';

其中的*.*可以改定指定数据库的指定表。

 

b.分配所有权限：

GRANT all privileges on *.* to guest3@'localhost';

 

5、回收权限

回收增删改查权限：

revoke select,delete,update,insert,grant option on *.*  from guest3@'localhost';

回收所有权限：

revoke all privileges,grant option from guest3@'localhost';

注意两个语句是不一样的，后面一句没有 on *.*。

 

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';

由于我创建出来的用户就是mysql_native_password，所以不需要修改。

补充：

如果在修改插件的时候出现错误，可现将插件改为 mysql_old_password，然后再升级成mysql_native_password。

 

2.2、库表设计

2.2.1、数据库操作

1、显示数据库

SHOW DATABASES;

一般来说会显示如下数据库

 

2、创建数据库

创建数据库名称有规范，不能用#和.等符号，可以用下划线和数字

CREATE DATABASE aaa_bbb_123;

这里说明一下，库名建议全部用小写，原因是虽然MySQL对指令大小写不敏感，但是对库名表名大小写是敏感的，如果用大小写混合很容易出错。

 

3、删除数据库

drop DATABASE 数据库名称;

确定这个库不要才能删！

 

2.2.2、表格操作

1、显示表格

需要先选择数据库，否则显示出来的表格会非常多：

use aaa_bbb_123;

然后输入显示表格

show tables;

可以发现没有任何表。

2、创建表格

创建一个名称为ccc_123的表格，里面有两个字段，分别为dd和ee，其中dd为int类型，4个字节长度，ee为varchar类型（可变字符串类型），长度为10个字节：

create table ccc_123 (dd int(4),ee varchar(10));

 

如果想查询这个表有哪些数据，可以使用：

select * from ccc_123;

可以发现没有数据。

也可以查询表结构

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;

 

5、改表名

把表格ccc_123名字改为ccc_666：

alter table aaa_bbb_123.ccc_123 rename to aaa_bbb_123.ccc_666;

改完以后show tables;看一下：

 

6、增加字段

alter table ccc_666 add ff varchar(10);

alter table ccc_666 add gg varchar(10);

增加了字段ff,gg

7、修改字段名称

alter table ccc_666 change gg hh char(20);

将字段gg名称改为hh，并将类型改为char(20)。如果你不想修改字段类型，可以用原来的类型，但是不能省略。

8、修改字段类型

alter table ccc_666 modify hh varchar(10);

将字段hh类型改为varchar(10);

9、删除字段

alter table ccc_666 drop hh;

10、删除表格

DROP TABLE table_name;

注意这个指令是删除表格，如果只是想清空数据而保留表格，可以使用以下语句：

delete from  table_name;

 

 

 

2.2.3、索引

索引的作用和主键类似，主要用来区分两行不同的数据，如果两行数据的主键或者联合索引相同，那么就会被认为是同一条数据，后面使用插入语句的时候就会忽略或替换。

1、查询索引

show index from 表名;

可以看到，目前的索引只有主键。

2、创建索引

创建了一个索引名称为suoyin1的索引，索引包含字段为ee，也就是说，ee字段相同的数据会被认为同一份数据，插入会被忽略或替换。

如果创建失败，说明表中有重复数据，需要找出来并删除。

3、创建联合索引

alter table ccc_666 add unique index suoyin2(dd,ee);

注意如果index后面不写索引名称，就会将第一个字段作为索引名称。

创建了这条索引，dd,ee字段数据都相同的作为同一条数据库，插入时会忽略或替换。

如果创建失败，说明表中有重复数据，需要找出来并删除。

4、删除索引

alter table ccc_666 drop index suoyin1;

可以看到索引1已经被删除。

2.3、数据操作

2.3.1、增

使用insert和replace都可以插入新的数据，其中replace还可以修改原本已有的数据。

1、直接插入数据

insert into aaa_bbb_123.ccc_666 value(1,'测试');

这种方式需要插入的内容和字段顺序一一对应

2、指定字段插入

insert into aaa_bbb_123.ccc_666(ee,dd) value('测试2',2);

我故意调换了字段位置，就是为了说明，这种插入方式对应前面的顺序即可。

3、批量插入

insert into aaa_bbb_123.ccc_666(ee,dd) value('测试4',4),('测试3',3);

4、插入但忽略主键相同的数据

insert ignore into aaa_bbb_123.ccc_666(ee,dd) value('测试6',4),('测试5',5);

主键为4的数据行已有数据，所以新数据忽略，主键为5的正常插入。

使用这种方法可以避免插入重复数据。

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));

然后

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);被替换成了查询语句返回的数据。

可以发现ccc_456的数据跟ccc_666的数据一样了。

 

2.3.2、删

1、删除表格所有数据

delete from ccc_666;

可以看到数据都已经清空

2、删除指定数据

delete from ccc_666 where ee = '测试2';

删除字段ee中包含测试2的数据。

2.3.3、改

update ccc_666 set ee='测试100' where dd=5;

将dd为5的那一行数据的ee修改为测试100

 

2.3.4、查

查询是一个比较复杂的语句，可以有多种组合，不过还是从最简单的开始。

1、查询所有

select * from 表名;

这个就是之前一直在用的，查询所有数据的语句，在数据量少或者测试的时候，可以使用这种方式来查询，但是数据量多，就必须做筛选。

 

2、指定字段查询

select dd,ee from ccc_666;

 

3、指定类型查询

为了方便说明，先随便插点数据进去

然后查询ff=f7的ee字段数据

select ee from ccc_666 where ff = 'f7';

可以精确的查找到某一行某一列。

 

4、查询并排序

select ee from ccc_666 order by ee;

可以看到ee这一列按照数字顺序排列（100是1开头所以排前面）。

也可以倒序

select * from ccc_666 order by dd desc;

还可以按照两个字段进行排序

select * from ccc_666 order by ff,ee;

可以看到字段首先按照ff的顺序进行排列，null在前，之后是f4和f7，然后再按照ee后面的数字排列。

 

5、查找符合两个数据的内容并按照两个字段进行排序

select * from ccc_666 where ff = 'f4' or ff = 'f7' order by dd,ee;

先找出字段中包含f4和f7的内容，然后按照dd和ee进行排序。

 

6、查询总数据量

select count(*) from ccc_666;

 

7、查询指定字段的数据量

select count(*) from ccc_666 where ff = 'f4';

 

8、查询重复数据

为了更好的说明，决定给原始数据做一些修改和增加一些数据，如下

然后我们想查询出ee相同的数据

select * from ccc_666 where ee in

(select ee from ccc_666 group by ee having count(*) > 1) order by ee;

别看这个语句这么复杂，其实很简单，拆分成几个部分来看，首先是括号中的内容

select ee from ccc_666 group by ee having count(*) > 1;

这个语句是查询表格中ee字段数据相同的数据，group by的作用是对数据进行分组，数量大于1的就筛选出来，这条语句的执行结果是

所以前面的语句就很好理解了

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;

可以看到，ee和ff都相同的重复数据一共有四条，重复的类型有两种。