mysql的增删改查,mysql常用命令

  • A+

mysql的增删改查,mysql常用命令

编译安装mysql/mariadb文档:mysql编译/mariadb编译
yum安装mysql 8.0

  1. vi /etc/yum.repos.d/mysql.repo
  2. [mysql]
  3. name=mysql8.0
  4. baseurl=https://mirrors.tuna.tsinghua.edu.cn/mysql/yum/mysql-8.0-community-el7-x86_64/
  5. gpgcheck=0
  6. yum -y install mysql-community-server
  7. systemctl start mysqld
  8. systemctl enable mysqld
  9. mysql 8.0需要手动查看密码,然后修改密码后才能登录
  10. cat /var/log/mysqld.log |grep password
  11. mysql -u root -p'<qdmCZnRs6WV'
  12. alter user 'root'@'localhost'IDENTIFIED BY 'Test12#$'; 修改密码,重新登录后即可使用

1、CREATE语句

1.1 CREATE 和DATABASE数据库

官网文档:CREATE-DATABASE

  1. HELP CREATE 查看帮助
  2. HELP CREATE DATABASE
  3. CREATE DATABASE test1; 创建数据库,(按数据库默认字符集和排序创建)
  4. CREATE DATABASE test2 CHARACTER SET utf8mb4 collate utf8mb4_0900_ai_ci; 指定数据库字符集为utf8mb4,排序规则为utf8mb4_0900_ai_ci

mysql的增删改查,mysql常用命令

1.2 CREATE和TABLE(表格)

官网文档:CREATE-TABLE
表格的字段类型:字段类型

  1. HELP CREATE TABLE
  2. 进入数据库才能创建表格
  3. use test1;
  4. 创建student表,字段(列)为id,nameagegender,后面的是字段类型和属性
  5. create table student (id int unsigned primary key auto_increment,name varchar(10),age tinyint unsigned,gender enum('M','F') default 'M');
  6. unsigned 不设置符号
  7. primary key 设置为主键,不能为空
  8. auto_increment 自动增长
  9. tinyint 单个字节整数
  10. int 整数
  11. enum('M','F') 单选项
  12. default 'M' 默认值

mysql的增删改查,mysql常用命令

1.3 CREATE和USER(用户)

官网文档:CREATE-USER

  1. HELP CREATE USER
  2. 创建账户aa,密码为Test12#$,访问IP段为192.168.116.%
  3. CREATE USER 'aa'@'192.168.116.%' IDENTIFIED BY 'Test12#$';
  4. mysql 8.0的密码插件默认是caching_sha2_password,指定老版本的密码插件方式为mysql_native_password
  5. CREATE USER 'bb'@'192.168.116.%' IDENTIFIED WITH mysql_native_password BY 'Test12#$';

mysql的增删改查,mysql常用命令

1.4 CREATE和VIEW

  1. SELECT user,host,plugin FROM mysql.user; 查询某个表格的部分数据
  2. use test2; 进入数据库
  3. 将查询的部分表格数据保存为视图(其实相当于另存为表格,不过格式有所不同)
  4. CREATE VIEW t_view AS SELECT user,host,plugin FROM mysql.user;
  5. SHOW TABLES; 查看视图生成
  6. SELECT * FROM t_view; 查看视图的内容

mysql的增删改查,mysql常用命令

1.5 CREATE和FUNCTION(自定义函数)

创建一个表格

  1. CREATE TABLE t1 (tid int,name varchar(20));
  2. INSERT t1(tid,name)VALUES('1','aa'),('2','bb'),('3','cc');

mysql的增删改查,mysql常用命令
自定义一个函数,当函数的值=表格的某一列的值,删除行

  1. mysql 8.0版本需要开启这个才能自定义函数
  2. set global log_bin_trust_function_creators=ON;
  3. use test2; 进入数据库
  4. 创建函数 //开始,//结尾
  5. DELIMITER //
  6. CREATE FUNCTION deleteT1(id SMALLINT UNSIGNED) RETURNS VARCHAR(20)
  7. BEGIN
  8. DELETE FROM t1 WHERE tid = id;
  9. RETURN (SELECT COUNT(*) FROM t1);
  10. END//
  11. DELIMITER ;
  12. SELECT deleteT1(1); 调用函数,删除t1tid1的行
  13. SELECT * FROM t1; 再次查看t1表格
  14. 函数解析:
  15. DELIMITER // 表示把原本的结束符;改为//
  16. 下面表示创建一个函数deleteT1,里面有2个字段
  17. CREATE FUNCTION deleteT1(id SMALLINT UNSIGNED) RETURNS VARCHAR(20)
  18. BEGIN 这个表示函数开始
  19. DELETE FROM t1 WHERE tid = id; 定义当t1表格的tid=函数的id的值时,删除行
  20. RETURN (SELECT COUNT(*) FROM t1); 返回t1表格剩下的行数
  21. END// 结束符
  22. DELIMITER ; 将//还原为;

mysql的增删改查,mysql常用命令

1.6 CREATE和PROCEDURE(存储过程)

  1. mysql 8.0版本需要开启这个才能自定义函数
  2. set global log_bin_trust_function_creators=ON;
  3. use test2; 进入数据库
  4. 创建表格
  5. CREATE TABLE t1(id int auto_increment primary key,name char(10),age int);
  6. 创建存储过程
  7. delimiter $$
  8. create procedure PRt1()
  9. begin
  10. declare i int;
  11. set i = 1;
  12. while i < 1001
  13. do insert into t1(name,age) values (concat('aa',i),i);
  14. set i = i +1;
  15. end while;
  16. end$$
  17. delimiter ;
  18. 存储过程解析
  19. delimiter $$ #将结束符;转换为$$
  20. create procedure PRt1() #创建存储过程PRt1
  21. begin #开始存储过程
  22. declare i int; #定义变量i,类型为整数型
  23. set i = 1; #设置i的初始值
  24. while i < 1001 #定义循环,当i<1001
  25. #当i<1001时,不断插入数据到t1表格
  26. do insert into t1(name,age) values (concat('aa',i),i);
  27. set i = i +1; #定义i=i+1,不断递增
  28. end while; #结束while循环
  29. end$$ #结束存储过程
  30. delimiter ; #将结束符$$还原为;
  31. call PRt1; 调用存储过程
  32. SELECT count(*) FROM t1; 查看插入了1000条数据

mysql的增删改查,mysql常用命令

1.7 CREATE和TRIGGER(触发器)

触发器:当某个条件发生时,触发另外一个条件

创建2个表格

  1. CREATE TABLE t1(id int,name varchar(20)); 创建t1表,2个字段
  2. CREATE TABLE t2(t1_count int DEFAULT 0); 创建t2
  3. INSERT INTO t2(t1_count)VALUES(0); 插入t2的第一行的值为0
  4. SELECT * FROM t1; 查看t1表为空
  5. SELECT * FROM t2; 查看t2表的行值为0

mysql的增删改查,mysql常用命令
创建触发器

  1. CREATE TRIGGER t1_insert
  2. AFTER INSERT
  3. ON t1 FOR EACH ROW
  4. UPDATE t2 SET t1_count=t1_count+1;
  5. 触发器解析
  6. CREATE TRIGGER t1_insert #创建触发器t1_insert
  7. AFTER INSERT #通过插入触发
  8. ON t1 FOR EACH ROW #当t1表格每个行增加时
  9. UPDATE t2 SET t1_count=t1_count+1; #更新t2表格的值+1
  10. INSERT t1(id,name)VALUES('1','aa'),('2','bb'); t1表格插入2条数据
  11. SELECT * FROM t1; 查看数据生成
  12. SELECT * FROM t2; 因为触发器,所以变成了2

mysql的增删改查,mysql常用命令

1.8 CREATE和INDEX(索引)

官网文档:CREATE-INDEX

创建普通索引
普通索引:列中的值可以为空,可以重复。

  1. 创建表格
  2. CREATE TABLE i1(id int,name char(10));
  3. i1表格的id列做索引,名字为i1idx
  4. CREATE INDEX i1idx ON i1(id);
  5. SHOW INDEXES FROM i1\G 查看表格全部索引和属性
  6. 这里的索引属性是B树索引的意思,mysql默认是B+树索引
  7. Index_type: BTREE

mysql的增删改查,mysql常用命令
创建唯一索引
唯一索引:列中的值不能相同,必须唯一值,但是可以为空

  1. CREATE UNIQUE INDEX i1 ON i1(name); 创建唯一索引
  2. SHOW INDEXES FROM i1\G

mysql的增删改查,mysql常用命令
创建表格时指定索引

  1. 创建表格
  2. CREATE TABLE i2(id int,name char(10),INDEX i2idx(id));
  3. INDEX i2idx(id) 表示创建索引,名字叫i2idx,索引的字段是id
  4. SHOW INDEXES FROM i2\G

mysql的增删改查,mysql常用命令
创建复合索引(多个列做成一个索引)

  1. CREATE TABLE test1(id int,name char(10),age int,extra varchar(20));
  2. CREATE INDEX i1idname ON test1(id,name); 指定id,name做成复合索引i1idname
  3. SHOW INDEXES FROM test1\G 查看列的索引名字都是一样

mysql的增删改查,mysql常用命令

2、SHOW 语句

2.1 SHOW和DATABASES(全部数据库)

官网SHOW文档:SHOW

  1. SHOW DATABASES; 查看全部数据库名字

mysql的增删改查,mysql常用命令

2.2 SHOW和TABLES(数据库的全部表格)

  1. use test1; 进入数据库
  2. SHOW TABLES; 查看数据中的全部表格名字

mysql的增删改查,mysql常用命令

2.3 SHOW和CREATE

查看创建数据库时的属性

  1. SHOW CREATE DATABASE test1; 查看test1数据库创建时的字符集和排序规则等属性

mysql的增删改查,mysql常用命令
查看创建表格时的属性

  1. use test1; 进入数据库
  2. SHOW CREATE TABLE student; 查看创建student表格时设置的字段属性和其他属性

mysql的增删改查,mysql常用命令

2.5 SHOW和BINLOG

  1. SHOW BINARY LOGS; 查看二进制日志的全部文件
  2. SHOW BINLOG EVENTS IN 'binlog.000002'; 指定二进制文件名查看文件内容

mysql的增删改查,mysql常用命令

2.5 SHOW和GRANT

查看账户权限

  1. SHOW GRANTS FOR aa@localhost;

mysql的增删改查,mysql常用命令

2.6 SHOW和其他

查看mysql进程

  1. SHOW PROCESSLIST; 查看mysql进程

mysql的增删改查,mysql常用命令
查看视图

  1. use test2; 进入数据库
  2. SHOW TABLE STATUS LIKE 't_view'\G 查看视图信息
  3. 这个Comment: VIEW 定义了这个表格为视图

mysql的增删改查,mysql常用命令
查看自定义函数

  1. SHOW FUNCTION STATUS\G 查看全部自定义函数
  2. SHOW CREATE FUNCTION deleteT1\G; 查看指定自定义函数创建信息

mysql的增删改查,mysql常用命令

mysql的增删改查,mysql常用命令
查看存储过程

  1. SHOW PROCEDURE STATUS\G; 查看全部存储过程
  2. SHOW CREATE PROCEDURE PRt1; 查看指定的存储过程创建方式

mysql的增删改查,mysql常用命令
mysql的增删改查,mysql常用命令
查看触发器

  1. SHOW TRIGGERS\G; 查看全部触发器
  2. SHOW CREATE TRIGGER t1_insert\G; 查看指定触发器的创建方式

mysql的增删改查,mysql常用命令
mysql的增删改查,mysql常用命令
查看全部变量

  1. SHOW VARIABLES\G

mysql的增删改查,mysql常用命令

查看全部全局变量

  1. SHOW GLOBAL VARIABLES\G

mysql的增删改查,mysql常用命令
查看表格索引

  1. SHOW INDEXES FROM i1\G 查看表格全部索引和属性

mysql的增删改查,mysql常用命令

3、INSERT 语句(增加内容到表格)

官网文档:INSERT

  1. use test1; 进入数据库
  2. 插入一条数据到student表格,前面指定字段(列),后面输入内容
  3. INSERT student(id,name,age,gender)VALUES('1','aa','18','M');
  4. *代表所有,表示查看表格所有内容,表格少量数据可以使用这个,大量不用使用,要不然可能会卡死
  5. SELECT * FROM student;

mysql的增删改查,mysql常用命令
插入多个数据,中间用逗号隔开

  1. use test1; 进入数据库
  2. INSERT student(id,name,age,gender)VALUES('2','bb','18','F'),('3','cc','17','M');
  3. SELECT * FROM student;

mysql的增删改查,mysql常用命令

4、DELETE 语句(删除表格内容)

官网文档:DELETE

增加qq这一行

  1. use test1; 进入数据库
  2. INSERT student(id,name,age,gender)VALUES('4','qq','18','M');
  3. SELECT * FROM student;

mysql的增删改查,mysql常用命令
删除qq这一行

  1. DELETE FROM student WHERE id='4';
  2. SELECT * FROM student;

mysql的增删改查,mysql常用命令
清空表格,但是不删除表格内容

  1. 创建一个表格
  2. use test1;
  3. CREATE TABLE t1 (id int primary key auto_increment,name varchar(20));
  4. INSERT t1(id,name)VALUES('1','AA');
  5. INSERT t1(id,name)VALUES('2','BB');
  6. SELECT * FROM t1;
  7. 清空表格,但是不删除表格
  8. DELETE FROM t1;
  9. SELECT * FROM t1; 可以看到数据不见了
  10. SHOW TABLES; 可以看到表格还在

mysql的增删改查,mysql常用命令

5、UPDATE (更改表格内容)

官网文档:UPDATE

  1. use test1; 进入数据库
  2. 指定更新id1name字段内容
  3. SET 指定数据更新的列和值,WHERE指定老的列和值
  4. UPDATE student SET name='dd' WHERE id='1';
  5. SELECT * FROM student;

mysql的增删改查,mysql常用命令
UPDATE和USER(修改账户密码),这种只适合mysql 5.7以下版本修改密码

  1. CREATE USER 'bb'@'localhost' IDENTIFIED BY 'Test12#$'; 创建账户
  2. 指定账户信息,通过修改表格方式修改密码
  3. UPDATE mysql.user set password=password('BBbb12#$') where user='bb' AND host='localhost';
  4. FLUSH PRIVILEGES; 指定表格方式修改密码需要刷新权限
  5. exit
  6. mysql -u bb -p'BBbb12#$' 测试登录

mysql的增删改查,mysql常用命令

6、SELECT 语句(查询表格内容)

官网文档:SELECT

6.1 SELECT和WHERE

  1. 查询mysql数据库中user表的user,host字段(列)的内容
  2. SELECT user,host FROM mysql.user;

mysql的增删改查,mysql常用命令
指定条件查询字段内容

  1. SELECT name,age FROM student WHERE age<=20;

mysql的增删改查,mysql常用命令

6.2 SELECT和WHERE-AND/BETWEEN(指定范围)

指定范围查询内容,默认前后都包括

  1. SELECT name,age FROM student WHERE age>=18 AND age<=20;
  2. SELECT name,age FROM student WHERE age BETWEEN 18 AND 20;

mysql的增删改查,mysql常用命令

6.3 SELECT和WHERE-IN(自定义查询范围)

  1. SELECT name,age FROM student WHERE age in (17,18);

mysql的增删改查,mysql常用命令

6.4 SELECT和WHERE-IS(查看空值和非空)

  1. INSERT student(id,name,gender)VALUES('4','dd','M'); 插入一条包括空值数据
  2. SELECT name,age FROM student WHERE age is null; 查看空值数据
  3. SELECT name,age FROM student WHERE age is not null; 查看非空值数据

mysql的增删改查,mysql常用命令

6.5 SELECT和WHERE-字段=值(指定固定值查询)

  1. SELECT name,age FROM student WHERE age='18';

mysql的增删改查,mysql常用命令

6.5 SELECT和WHERE-字段 LIKE 值%(模糊查询)

  1. INSERT student(id,name,age,gender)VALUES('5','daa','19','M');
  2. SELECT name,age FROM student WHERE name like 'd%';

mysql的增删改查,mysql常用命令

6.6 SELECT和内置函数(count,avg,max,min)

  1. SELECT count(id) FROM student; 统计字段出现有多少行
  2. SELECT avg(age) FROM student; 统计字段的平均值
  3. SELECT max(age) FROM student; 统计字段的最大值
  4. SELECT min(age) FROM student; 统计字段的最小值

mysql的增删改查,mysql常用命令
查看当前登录账户

  1. SELECT CURRENT_USER(); 查看当前登录账户

mysql的增删改查,mysql常用命令

6.7 SELECT和GROUP BY(分组查询)

指定age,统计age相同的人数

  1. SELECT age 年龄,count(*) 数量 FROM student GROUP BY age;
  2. age 年龄 后面的年龄是为了输出时显示,不会修改原本表格数据
  3. count(*) 数量 count(*)内置函数,可以统计列的全部值

mysql的增删改查,mysql常用命令

6.7 SELECT和ORDER(排序查询)

正序和倒序
表格创建时会带有排序规则,所以具体要看排序规则的方式排序,不一定都是按照从A-Z这种方式排序

  1. SELECT * FROM student ORDER BY id; 默认是从小到大,正序排序
  2. SELECT * FROM student ORDER BY id desc; 指定desc为倒序排序

mysql的增删改查,mysql常用命令
指定固定行查询

  1. SELECT * FROM student; 默认是以id排序
  2. SELECT * FROM student ORDER BY id LIMIT 3; 指定以id排序,输出前3

mysql的增删改查,mysql常用命令
跳序查询
表格创建时会带有排序规则,所以具体要看排序规则的方式排序,不一定都是按照从A-Z这种方式排序

  1. SELECT * FROM student ORDER BY age;
  2. 跳过前面3个,输出第4,52
  3. SELECT * FROM student ORDER BY age LIMIT 3,2;

mysql的增删改查,mysql常用命令

6.8 SELECT和其他

查看mysql版本号

  1. SELECT VERSION();

mysql的增删改查,mysql常用命令
查看当前登录账户

  1. SELECT CURRENT_USER();

mysql的增删改查,mysql常用命令
查看当前所在数据库

  1. SELECT DATABASE();

mysql的增删改查,mysql常用命令

7、ALTER语句(主要修改数据库、表格、用户等属性)

7.1 ALTER和DATABASE(数据库)

官网文档:ALTER-DATABASE
修改数据库属性最好是空的数据库,要不然原本数据库的表格可能会发生乱码错误

  1. SHOW CREATE DATABASE test2; 查看数据库创建时的字符集和排序规则等属性
  2. ALTER DATABASE test2 character set gbk; 修改test2数据库的字符集
  3. ALTER DATABASE test2 COLLATE=gbk_chinese_ci; 修改test2数据库的排序规则
  4. SHOW CREATE DATABASE test2; 再次查看数据库属性

mysql的增删改查,mysql常用命令

7.2 ALTER和TABLE(表格)

官网文档:ALTER-TABLE

修改表格字符集和排序规则
创建一个表格,新的表格的字符集和排序规则由创建表格的数据库的创建数据定义,也就是说表格的数据库的字符集和排序规则是什么,表格的属性就是什么。

  1. use test3;
  2. CREATE TABLE t1 (id int primary key auto_increment,name varchar(20));
  3. INSERT t1(id,name)VALUES('1','AA');
  4. INSERT t1(id,name)VALUES('2','BB');
  5. SELECT * FROM t1;
  6. SHOW CREATE TABLE t1; 查看表格属性
  7. 一般表格和数据库的字符集和排序规则一致,最好不要修改
  8. ALTER TABLE t1 character set gbk; 修改t1表格字符集
  9. ALTER TABLE t1 COLLATE=gbk_chinese_ci; 修改t1表格的排序规则

mysql的增删改查,mysql常用命令
增加表格字段(列)

  1. SELECT * FROM t1; 原本只有2个字段()idname
  2. 指定在id后面增加新的列age
  3. ALTER TABLE t1 add age int AFTER id;
  4. SELECT * FROM t1;

mysql的增删改查,mysql常用命令
删除字段(列)

  1. SELECT * FROM t1; 查看原本表格字段
  2. ALTER TABLE t1 DROP COLUMN age; 指定删除age字段
  3. SELECT * FROM t1; 确认删除age字段

mysql的增删改查,mysql常用命令
修改字段名字

  1. 指定将name字段修改为mingzi字段
  2. 注意:修改字段需要注意字段类型,要不然可能会出现乱码
  3. ALTER TABLE t1 CHANGE COLUMN name mignzi char(12);
  4. SELECT * FROM t1;

mysql的增删改查,mysql常用命令

7.3 ALTER和USER(用户)

官网文档:ALTER-USER
创建账户

  1. 创建账户aa,设置密码和登录方式
  2. CREATE USER 'aa'@'localhost' IDENTIFIED BY 'Test12#$';
  3. mysql -u aa -p'Test12#$'
  4. exit

mysql的增删改查,mysql常用命令
修改用户密码

  1. ALTER USER aa@'localhost' identified by 'AAaa12#$'; 修改aa@'localhost'的密码
  2. mysql -u aa -p'Test12#$' 重新用老密码不能登录
  3. mysql -u aa -p'AAaa12#$' 新密码可以登录

mysql的增删改查,mysql常用命令
mysql 5.7以下老版本修改密码另外方式

  1. SET PASSWORD FOR 'bb'@'localhost' = PASSWORD('Test12#$'); 修改密码
  2. mysql -u bb -p'Test12#$' 重新登录,可以看到密码修改成功

mysql的增删改查,mysql常用命令
修改当前登录账户密码

  1. SELECT CURRENT_USER(); 查看当前登录账户
  2. ALTER user user() identified by 'Test12#$'; 默认没有root权限不能修改别人密码,但是可以用这个命令修改自己密码
  3. mysql -u aa -p'AAaa12#$' 老密码不能登录
  4. mysql -u aa -p'Test12#$' 新密码可以,修改密码成功

mysql的增删改查,mysql常用命令
锁定和解锁账户

  1. ALTER USER 'aa'@'localhost' ACCOUNT LOCK; 锁定账户'aa'@'localhost'
  2. ALTER USER 'aa'@'localhost' ACCOUNT UNLOCK; 解锁账户

mysql的增删改查,mysql常用命令
修改账户密码插件

  1. SELECT user,host,plugin FROM mysql.user; 查看老的账户信息
  2. ALTER USER 'aa'@'localhost' IDENTIFIED WITH mysql_native_password BY 'Test12#$'; 修改插件
  3. SELECT user,host,plugin FROM mysql.user; 查看账户信息

mysql的增删改查,mysql常用命令

7.4 ALTER和INDEX(索引)

对表格添加索引

  1. CREATE TABLE A1(id int,name char(10)); 创建表格
  2. ALTER TABLE A1 ADD INDEX a1idx(id); A1表格的字段id添加索引a1idx
  3. SHOW INDEX FROM A1\G

mysql的增删改查,mysql常用命令
删除表格索引

  1. ALTER TABLE A1 DROP INDEX a1idx; 删除表格索引a1idx
  2. SHOW INDEX FROM A1\G

mysql的增删改查,mysql常用命令

7.5 ALTER和MODIFY(修改数据长度)

  1. CREATE TABLE M1(id int,name char(10)); 创建表格,name字段长度为10
  2. ALTER TABLE M1 MODIFY name char(15); 修改name字段长度为15
  3. SHOW CREATE TABLE M1; 确认修改成功

mysql的增删改查,mysql常用命令

7.6 ALTER和PRIMARY KEY(主键)

添加主键时,主键对应的列的数据不能为空,一个表只能有一个主键

  1. CREATE TABLE Z1(id int,name char(10)); 创建表格
  2. ALTER TABLE Z1 MODIFY id int NOT NULL; 设置主键不为空
  3. ALTER TABLE Z1 ADD PRIMARY KEY (id); 创建主键
  4. 因为主键也是索引的一种,所以这样查看
  5. SHOW INDEX FROM Z1; 查看表格主键信息

mysql的增删改查,mysql常用命令
删除主键

  1. ALTER TABLE Z1 DROP PRIMARY KEY; 删除主键,只有一个,不用指定字段名
  2. SHOW INDEX FROM Z1; 查看表格主键信息

mysql的增删改查,mysql常用命令

8、DROP 语句

8.1 DROP和DATABASE(数据库)

官网文档:DROP-DATABASE

  1. DROP DATABASE test1; 删除数据库test1

mysql的增删改查,mysql常用命令

8.2 DROP和TABLE(表格)

官网文档:DROP-TABLE
创建一个表格

  1. use test2;
  2. CREATE TABLE t2 (id int primary key auto_increment,name varchar(20));
  3. INSERT t2(id,name)VALUES('1','AA');
  4. INSERT t2(id,name)VALUES('2','BB');
  5. SELECT * FROM t2;
  6. SHOW TABLES;

mysql的增删改查,mysql常用命令
删除表格

  1. DROP TABLE t2;
  2. SHOW TABLES; 可以看到表格已经被删除

mysql的增删改查,mysql常用命令

8.3 DROP和USER(用户)

  1. SELECT user,host,plugin FROM mysql.user; 查看全部账户
  2. DROP USER bb@'localhost'; 删除指定账户
  3. SELECT user,host,plugin FROM mysql.user; 再次查看

mysql的增删改查,mysql常用命令

8.4 DROP和VIEW

  1. use test2;
  2. SHOW TABLE STATUS LIKE 't_view'\G 查看视图信息
  3. 通过这个Comment: VIEW 可以看到这个表格实际是视图,不是平常的表格
  4. DROP VIEW t_view; 删除视图

mysql的增删改查,mysql常用命令

8.5 DROP和其他

删除自定义函数

  1. use test2;
  2. DROP FUNCTION deleteT1; 删除自定义函数

mysql的增删改查,mysql常用命令
删除存储过程

  1. use test2;
  2. SHOW CREATE PROCEDURE PRt1\G; 查看指定的存储过程创建方式
  3. DROP PROCEDURE PRt1; 删除存储过程

mysql的增删改查,mysql常用命令
删除触发器

  1. SHOW CREATE TRIGGER t1_insert\G; 查看触发器
  2. DROP TRIGGER t1_insert; 删除触发器

mysql的增删改查,mysql常用命令

9、GRANT 授权语句

官网文档:GRANT

创建账户和数据库

  1. 创建t1账户
  2. CREATE USER 't1'@'localhost' IDENTIFIED BY 'Test12#$';
  3. CREATE DATABASE t1; 创建t1数据库
  4. 授权t1账户对t1数据库有所有权限
  5. GRANT ALL ON t1.* TO t1@'localhost';
  6. 创建t2数据库
  7. CREATE DATABASE t2;
  8. 授权t1账户对t2账户具有查询权限
  9. GRANT SELECT ON t2.* TO t1@'localhost';

mysql的增删改查,mysql常用命令
用root账户先在t2数据库创建表格

  1. CREATE TABLE t2(id int,name char(10));
  2. INSERT t2(id,name)VALUES('1','aa');
  3. SELECT * FROM t2;

mysql的增删改查,mysql常用命令

测试权限是否授权成功

查看t1账户对t1数据库的权限

  1. mysql -u t1 -p'Test12#$' t1账户登录
  2. use t1; 进入t1数据库
  3. CREATE TABLE t1(id int,name char(10)); 创建表格
  4. INSERT t1(id,name)VALUES('1','aa'); 插入数据到表格
  5. SELECT * FROM t1; 查看表格数据
  6. DROP TABLE t1; 删除表格

mysql的增删改查,mysql常用命令
查看t1账户对t2数据库的权限

  1. mysql -u t1 -p'Test12#$' t1账户登录t2数据库
  2. CREATE TABLE t2(id int,name char(10)); 没有新建权限
  3. INSERT t2(id,name)VALUES('2','bb'); 没有插入权限
  4. SELECT * FROM t2; 只有查询权限

mysql的增删改查,mysql常用命令

10、REVOKE 撤销(删除)权限语句

官网文档:REVOKE

  1. REVOKE ALL PRIVILEGES,GRANT OPTION FROM t1@'localhost'; 撤销全部权限
  2. SHOW GRANTS FOR t1@localhost; 查看权限列表
  3. 这个是默认账户登录权限,必须要有才能使用账户
  4. GRANT USAGE ON *.* TO `t1`@`localhost`
  5. mysql -u t1 -p'Test12#$' 重新登录
  6. use t1; 可以看到没有t1数据库的权限

mysql的增删改查,mysql常用命令
撤销部分权限方式

  1. GRANT ALL ON t1.* TO t1@'localhost'; 先授权t1账户对t1数据库有全部权限
  2. SHOW GRANTS FOR t1@localhost; 查看权限
  3. REVOKE INSERT ON t1.* FROM t1@'localhost'; 撤销对t1数据库的插入权限
  4. SHOW GRANTS FOR t1@localhost; 查看权限

mysql的增删改查,mysql常用命令
用t1账户登录,测试权限

  1. mysql -u t1 -p'Test12#$' 登录
  2. use t1; 进入数据库
  3. CREATE TABLE t1(id int,name char(10)); 有新建权限
  4. INSERT t1(id,name)VALUES('1','aa'); 没有插入权限
  5. SELECT * FROM t1; 有查询权限
  6. SHOW TABLES; 表格也有生成

mysql的增删改查,mysql常用命令

11、SET语句

SET设置变量文档:SET

自定义临时变量

  1. SET @var_t1=11; 设置变量@var_t1,值为11
  2. SELECT @var_t1; 查看变量
  3. mysql -u tt1 -p'Test12#$' 退出登录后,自定义变量不会保存到数据库
  4. SELECT @var_t1; 因为没保存,所以值为空

mysql的增删改查,mysql常用命令
系统变量
文档:SET-VARIABLE
Option File 这里的Yes表示这个内容是选项,需要去配置文件修改
System Var 这里的Yes表示这个内容是变量,可以直接在mysql客户端修改值

如果这2个都是Yes,代表可以在配置文件修改,也可以在mysql客户端修改值
mysql的增删改查,mysql常用命令
查看默认系统字符集的变量

  1. SELECT @@character_set_results;
  2. set character_set_results="utf8mb3"; 当前终端修改默认字符集值,退出登录自动恢复
  3. SELECT @@character_set_results;

mysql的增删改查,mysql常用命令
全局变量(不会因为退出登录或重启而失效)

  1. SHOW GLOBAL VARIABLES LIKE 'log_bin_trust_function_creators';
  2. set global log_bin_trust_function_creators=OFF; 设置全局变量
  3. SHOW GLOBAL VARIABLES LIKE 'log_bin_trust_function_creators';

mysql的增删改查,mysql常用命令

12、EXPLAIN语句(一般用于查询索引是否执行)

官网文档:EXPLAIN
EXPLAIN输出内容文档:EXPLAIN-OUT
主要关注列:
type:表格的连接类型,通过这个判断索引是否被用到(从最优到最差):

  • system:一般不会出现,只会出现在mysql的内置系统表中才会显示。
  • const:一般需要只有一个匹配的行并且这行的其他值可以被mysql当为常量才能出现这种情况。如果索引是主键也会出现这个。
  • eq_ref:一般出现在关联表查询过程中,并且当使用的索引是主键或唯一非 NULL 索引时, 才会出现这个。
  • ref:一般查询时最优结果,出现这个类型,优化算是很好的了。如果是使用非唯一性索引情况下,就会出现这种结果。
  • fulltext:使用FULLTEXT索引会出现这个。
  • ref_or_null:在ref类型的情况下,另外搜索包括NULL值的行会出现这个。一般用于表格的子查询。
  • index_merge:此连接类型表明使用了索引合并优化。在这种情况下,key输出行中的列包含所用索引的列表,并 key_len包含所用索引的最长键部分的列表。
  • unique_subquery:此类型替换这种形式eq_ref的某些 IN子查询:value IN (SELECT primary_key FROM single_table WHERE some_expr)
  • index_subquery:主要用于替换IN子查询语句,适用于这种形式的子查询中的非唯一索引。value IN (SELECT key_column FROM single_table WHERE some_expr)
  • range:使用索引时,查询的语句指定范围则会出现这个。比如SELECT * FROM tbl_name WHERE key_column BETWEEN 10 and 20;
  • index:当Extra列还显示Using index时,证明这次查询只使用了索引表的索引,没有进行全表扫描。当Extra列没有Using index时,证明此次查询按照索引的顺序进行了全表扫描。
  • ALL:最差的查询结果,表格会对每个行组合进行全表扫描。

possible_keys:可能使用到的索引,但是实际不一定使用到。为空则没有使用索引。

key:一定使用到的索引,为空则没有使用索引。

ref:(该ref列显示将哪些列或常量与列中指定的索引进行比较以 key从表中选择行。)

  • const:常见结果,表示使用到了常见的索引类型。
  • func:表示使用的值是某个函数的结果。
  • NULL:一般没有使用到索引会出现这个

rows:此次查询扫描了多少行

Extra:(额外信息,通过这个对索引进一步优化)
具体看官网文档:EXPLAIN-Extra

注意:以下结果有时候会同时出现。

常见输出:

  • Using index:使用了覆盖索引,最优结果
  • Using index condition:使用条件加索引方式。比如已经知道了行的范围或内容,但是还是需要索引查找,如EXPLAIN SELECT * FROM tbl_name where id in (1,11);
  • Using where:使用到了where语句,但是不一定使用到了索引,要和type配合查看。
  • Using filesort:一般使用到了ORDER BY语句就会出现这个。
  • Using index for group-by:一般使用GROUP BY语句出现这个。
  • NULL:一般这个表示索引使用了,但是还可以优化,要和type配合查看。

ORDER BY语句和索引优化:索引-ORDER BY
GROUP BY语句和索引优化:索引-GROUP BY

创建表格和索引

  1. CREATE TABLE e1(id int,name char(10)); 创建表格
  2. CREATE INDEX e1idx ON e1(id); 创建索引
  3. 插入数据
  4. INSERT e1(id,name)VALUES('1','aa'),('2','bb'),('11','aa1'),('12','bb1');
  5. SELECT * FROM e1;

mysql的增删改查,mysql常用命令
使用EXPLAIN查看使用索引和没有使用索引

  1. EXPLAIN SELECT id FROM e1 WHERE id='1'; 使用了索引
  2. 可以看到typerefkeye1idx
  3. EXPLAIN SELECT * FROM e1 WHERE id LIKE 'a%'; 没有使用到索引
  4. 可以看到typeALLkeyNULL,通过rows4行,也可以看出进行了全表扫描
  5. Using where表示使用到了where语句,但是不一定使用到索引

mysql的增删改查,mysql常用命令
查看Using index condition和Using filesort

  1. EXPLAIN SELECT * FROM e1 WHERE id in (1,11); 使用了索引
  2. typerangekeye1idx证明使用到了索引,Using index condition表示使用了索引和条件
  3. EXPLAIN SELECT * FROM e1 ORDER BY id;
  4. typeNULLkeyNULLUsing filesort表示是一个ORDER BY语句

mysql的增删改查,mysql常用命令
索引最优选择

  1. EXPLAIN SELECT id,name FROM e1 WHERE id='1'; 使用了索引,但是不是最优
  2. 可以看到WHERE已经定义了只查找id1的行,就不需要在写name
  3. EXPLAIN SELECT id FROM e1 WHERE id='1'; 最优使用索引方式

mysql的增删改查,mysql常用命令
Extra同时出现不同结果

  1. EXPLAIN SELECT id FROM e1 WHERE id in (1,11);
  2. 可以看到使用到了索引和wehere条件

mysql的增删改查,mysql常用命令

13、DESCRIBE(简写DESC,主要用于查看表或视图结构)

  1. create table student (id int unsigned primary key auto_increment,name varchar(10),age tinyint unsigned,gender enum('M','F') default 'M');
  2. DESC student; 2种都可以查看表结构
  3. DESCRIBE student;

mysql的增删改查,mysql常用命令
因为视图相当于另外一种方式的表格,所以可以用DESC命令

  1. CREATE VIEW t_view AS SELECT user,host,plugin FROM mysql.user; 创建视图
  2. DESC t_view; 查看视图结构

mysql的增删改查,mysql常用命令

14、RENAME语句

RENAME 修改账户名字

  1. 把账户名字从t1@'localhost' 改为tt1@'localhost'
  2. RENAME USER t1@'localhost' TO tt1@'localhost';
  3. SELECT user,host FROM mysql.user;

mysql的增删改查,mysql常用命令