• mysql的增删改查，mysql常用命令

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

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

    vi /etc/yum.repos.d/mysql.repo

[mysql]
name=mysql8.0
baseurl=https://mirrors.tuna.tsinghua.edu.cn/mysql/yum/mysql-8.0-community-el7-x86_64/
gpgcheck=0

yum -y install mysql-community-server

systemctl start mysqld
systemctl enable mysqld

mysql 8.0需要手动查看密码，然后修改密码后才能登录
cat /var/log/mysqld.log |grep password
mysql -u root -p'<qdmCZnRs6WV'
alter user 'root'@'localhost'IDENTIFIED BY 'Test12#$';  修改密码，重新登录后即可使用

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16

    1、CREATE语句

    1.1 CREATE 和DATABASE数据库

    官网文档：CREATE-DATABASE

    HELP CREATE   查看帮助
HELP CREATE DATABASE

CREATE DATABASE test1;   创建数据库，（按数据库默认字符集和排序创建）
CREATE DATABASE test2 CHARACTER SET utf8mb4 collate utf8mb4_0900_ai_ci; 指定数据库字符集为utf8mb4，排序规则为utf8mb4_0900_ai_ci

    • 1
    • 2
    • 3
    • 4
    • 5

    在这里插入图片描述

    1.2 CREATE和TABLE（表格）

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

    HELP CREATE TABLE

进入数据库才能创建表格
use test1;

创建student表，字段（列）为id,name，age，gender，后面的是字段类型和属性
create table student (id int unsigned primary key auto_increment,name varchar(10),age tinyint unsigned,gender enum('M','F') default 'M');

unsigned     不设置符号
primary key  设置为主键，不能为空
auto_increment  自动增长
tinyint   单个字节整数
int  整数
enum('M','F')   单选项
default 'M'  默认值

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15

    在这里插入图片描述

    1.3 CREATE和USER（用户）

    官网文档：CREATE-USER

    HELP CREATE USER

创建账户aa，密码为Test12#$，访问IP段为192.168.116.%
CREATE USER 'aa'@'192.168.116.%' IDENTIFIED BY 'Test12#$';

mysql 8.0的密码插件默认是caching_sha2_password，指定老版本的密码插件方式为mysql_native_password
CREATE USER 'bb'@'192.168.116.%' IDENTIFIED WITH mysql_native_password BY 'Test12#$'; 

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7

    在这里插入图片描述

    1.4 CREATE和VIEW

    SELECT user,host,plugin FROM mysql.user;  查询某个表格的部分数据
use test2;  进入数据库

将查询的部分表格数据保存为视图（其实相当于另存为表格，不过格式有所不同）
CREATE VIEW t_view AS SELECT user,host,plugin FROM mysql.user;
SHOW TABLES;                  查看视图生成
SELECT * FROM t_view;         查看视图的内容

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7

    在这里插入图片描述

    1.5 CREATE和FUNCTION（自定义函数）

    创建一个表格

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

    • 1
    • 2

    在这里插入图片描述
    自定义一个函数，当函数的值=表格的某一列的值，删除行

    mysql 8.0版本需要开启这个才能自定义函数
set global log_bin_trust_function_creators=ON; 

use test2;                进入数据库
创建函数    //开始，//结尾
DELIMITER //
CREATE FUNCTION deleteT1(id SMALLINT UNSIGNED) RETURNS VARCHAR(20)
BEGIN
DELETE FROM t1 WHERE tid = id;
RETURN (SELECT COUNT(*) FROM t1);
END//

DELIMITER ; 
SELECT deleteT1(1);  调用函数，删除t1中tid为1的行
SELECT * FROM t1;    再次查看t1表格

函数解析：
DELIMITER //  表示把原本的结束符;改为//
下面表示创建一个函数deleteT1，里面有2个字段
CREATE FUNCTION deleteT1(id SMALLINT UNSIGNED) RETURNS VARCHAR(20)
BEGIN  这个表示函数开始
DELETE FROM t1 WHERE tid = id;  定义当t1表格的tid=函数的id的值时，删除行
RETURN (SELECT COUNT(*) FROM t1); 返回t1表格剩下的行数
END//   结束符
DELIMITER ;  将//还原为;

    • 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

    在这里插入图片描述

    1.6 CREATE和PROCEDURE（存储过程）

    mysql 8.0版本需要开启这个才能自定义函数
set global log_bin_trust_function_creators=ON; 
use test2;        进入数据库

创建表格
CREATE TABLE t1(id int auto_increment primary key,name char(10),age int);

创建存储过程
delimiter $$

create procedure  PRt1() 
begin  
declare i int;
set i = 1; 
while i < 1001
do  insert into t1(name,age) values (concat('aa',i),i); 
set i = i +1; 
end while; 
end$$

delimiter ;

存储过程解析
delimiter $$   #将结束符;转换为$$

create procedure  PRt1()    #创建存储过程PRt1
begin                       #开始存储过程
declare i int;              #定义变量i，类型为整数型
set i = 1;                  #设置i的初始值
while i < 1001              #定义循环，当i<1001
#当i<1001时，不断插入数据到t1表格
do  insert into t1(name,age) values (concat('aa',i),i); 
set i = i +1;               #定义i=i+1,不断递增
end while;                  #结束while循环
end$$                       #结束存储过程

delimiter ;        #将结束符$$还原为;


call PRt1;                 调用存储过程
SELECT count(*) FROM t1;   查看插入了1000条数据

    • 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

    在这里插入图片描述

    1.7 CREATE和TRIGGER（触发器）

    触发器：当某个条件发生时，触发另外一个条件

    创建2个表格

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

    • 1
    • 2
    • 3
    • 4
    • 5

    在这里插入图片描述
    创建触发器

    CREATE TRIGGER t1_insert
AFTER INSERT
ON t1 FOR EACH ROW
UPDATE t2 SET t1_count=t1_count+1;

触发器解析
CREATE TRIGGER t1_insert           #创建触发器t1_insert
AFTER INSERT                       #通过插入触发
ON t1 FOR EACH ROW                 #当t1表格每个行增加时
UPDATE t2 SET t1_count=t1_count+1; #更新t2表格的值+1


INSERT t1(id,name)VALUES('1','aa'),('2','bb');  对t1表格插入2条数据
SELECT * FROM t1;                               查看数据生成
SELECT * FROM t2;                               因为触发器，所以变成了2

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15

    在这里插入图片描述

    1.8 CREATE和INDEX（索引）

    官网文档：CREATE-INDEX

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

    创建表格
CREATE TABLE i1(id int,name char(10));

对i1表格的id列做索引，名字为i1idx
CREATE INDEX i1idx ON i1(id);
SHOW INDEXES FROM i1\G   查看表格全部索引和属性

这里的索引属性是B树索引的意思，mysql默认是B+树索引
   Index_type: BTREE

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9

    在这里插入图片描述
    创建唯一索引
    唯一索引：列中的值不能相同，必须唯一值，但是可以为空

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

    • 1
    • 2

    在这里插入图片描述
    创建表格时指定索引

    创建表格
CREATE TABLE i2(id int,name char(10),INDEX i2idx(id));

INDEX i2idx(id)  表示创建索引，名字叫i2idx，索引的字段是id

SHOW INDEXES FROM i2\G

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6

    在这里插入图片描述
    创建复合索引（多个列做成一个索引）

    CREATE TABLE test1(id int,name char(10),age int,extra varchar(20));

CREATE INDEX i1idname ON test1(id,name);  指定id,name做成复合索引i1idname

SHOW INDEXES FROM test1\G   查看列的索引名字都是一样

    • 1
    • 2
    • 3
    • 4
    • 5

    在这里插入图片描述

    2、SHOW 语句

    2.1 SHOW和DATABASES（全部数据库）

    官网SHOW文档：SHOW

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

    • 1

    在这里插入图片描述

    2.2 SHOW和TABLES（数据库的全部表格）

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

    • 1
    • 2

    在这里插入图片描述

    2.3 SHOW和CREATE

    查看创建数据库时的属性

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

    • 1

    在这里插入图片描述
    查看创建表格时的属性

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

    • 1
    • 2

    在这里插入图片描述

    2.5 SHOW和BINLOG

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

    • 1
    • 2

    在这里插入图片描述

    2.5 SHOW和GRANT

    查看账户权限

    SHOW GRANTS FOR aa@localhost;

    • 1

    在这里插入图片描述

    2.6 SHOW和其他

    查看mysql进程

    SHOW PROCESSLIST;         查看mysql进程

    • 1

    在这里插入图片描述
    查看视图

    use test2;                           进入数据库
SHOW TABLE STATUS LIKE 't_view'\G    查看视图信息

这个Comment: VIEW 定义了这个表格为视图

    • 1
    • 2
    • 3
    • 4

    在这里插入图片描述
    查看自定义函数

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

    • 1
    • 2

    在这里插入图片描述

    在这里插入图片描述
    查看存储过程

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


    • 1
    • 2
    • 3

    在这里插入图片描述
    在这里插入图片描述
    查看触发器

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

    • 1
    • 2

    在这里插入图片描述
    在这里插入图片描述
    查看全部变量

    SHOW VARIABLES\G

    • 1

    在这里插入图片描述

    查看全部全局变量

    SHOW GLOBAL VARIABLES\G

    • 1

    在这里插入图片描述
    查看表格索引

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

    • 1

    在这里插入图片描述

    3、INSERT 语句（增加内容到表格）

    官网文档：INSERT

    use test1; 进入数据库
插入一条数据到student表格，前面指定字段（列），后面输入内容
INSERT student(id,name,age,gender)VALUES('1','aa','18','M');

*代表所有，表示查看表格所有内容，表格少量数据可以使用这个，大量不用使用，要不然可能会卡死
SELECT * FROM student;  

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6

    在这里插入图片描述
    插入多个数据，中间用逗号隔开

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

    • 1
    • 2
    • 3

    在这里插入图片描述

    4、DELETE 语句（删除表格内容）

    官网文档：DELETE

    增加qq这一行

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

    • 1
    • 2
    • 3

    在这里插入图片描述
    删除qq这一行

    DELETE FROM student WHERE id='4';
SELECT * FROM student;

    • 1
    • 2

    在这里插入图片描述
    清空表格，但是不删除表格内容

    创建一个表格
use test1;
CREATE TABLE t1 (id int primary key auto_increment,name varchar(20));
INSERT t1(id,name)VALUES('1','AA');
INSERT t1(id,name)VALUES('2','BB');
SELECT * FROM t1;

清空表格，但是不删除表格
DELETE FROM t1;
SELECT * FROM t1;   可以看到数据不见了
SHOW TABLES;        可以看到表格还在

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11

    在这里插入图片描述

    5、UPDATE （更改表格内容）

    官网文档：UPDATE

    use test1; 进入数据库

指定更新id为1的name字段内容
SET 指定数据更新的列和值，WHERE指定老的列和值

UPDATE student SET name='dd' WHERE id='1';
SELECT * FROM student;

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7

    在这里插入图片描述
    UPDATE和USER（修改账户密码），这种只适合mysql 5.7以下版本修改密码

    CREATE USER 'bb'@'localhost' IDENTIFIED BY 'Test12#$'; 创建账户

指定账户信息，通过修改表格方式修改密码
UPDATE mysql.user set password=password('BBbb12#$') where user='bb' AND host='localhost';
FLUSH PRIVILEGES;  指定表格方式修改密码需要刷新权限

exit
mysql -u bb -p'BBbb12#$'  测试登录

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8

    在这里插入图片描述

    6、SELECT 语句（查询表格内容）

    官网文档：SELECT

    6.1 SELECT和WHERE

    查询mysql数据库中user表的user,host字段（列）的内容

SELECT user,host FROM mysql.user;

    • 1
    • 2
    • 3

    在这里插入图片描述
    指定条件查询字段内容

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

    • 1

    在这里插入图片描述

    6.2 SELECT和WHERE-AND/BETWEEN（指定范围）

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

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

    • 1
    • 2

    在这里插入图片描述

    6.3 SELECT和WHERE-IN（自定义查询范围）

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

    • 1

    在这里插入图片描述

    6.4 SELECT和WHERE-IS（查看空值和非空）

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

    • 1
    • 2
    • 3

    在这里插入图片描述

    6.5 SELECT和WHERE-字段=值（指定固定值查询）

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

    • 1

    在这里插入图片描述

    6.5 SELECT和WHERE-字段 LIKE 值%（模糊查询）

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


    • 1
    • 2
    • 3

    在这里插入图片描述

    6.6 SELECT和内置函数（count,avg,max,min）

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

    • 1
    • 2
    • 3
    • 4

    在这里插入图片描述
    查看当前登录账户

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

    • 1

    在这里插入图片描述

    6.7 SELECT和GROUP BY（分组查询）

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

    SELECT age 年龄,count(*) 数量 FROM student GROUP BY age;

age 年龄   后面的年龄是为了输出时显示，不会修改原本表格数据
count(*) 数量 count(*)内置函数，可以统计列的全部值

    • 1
    • 2
    • 3
    • 4

    在这里插入图片描述

    6.7 SELECT和ORDER（排序查询）

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

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

    • 1
    • 2

    在这里插入图片描述
    指定固定行查询

    SELECT * FROM student;        默认是以id排序
SELECT * FROM student ORDER BY id LIMIT 3;  指定以id排序，输出前3个

    • 1
    • 2

    在这里插入图片描述
    跳序查询
    表格创建时会带有排序规则，所以具体要看排序规则的方式排序，不一定都是按照从A-Z这种方式排序

    SELECT * FROM student ORDER BY age;

跳过前面3个，输出第4,5这2个
SELECT * FROM student ORDER BY age LIMIT 3,2;

    • 1
    • 2
    • 3
    • 4

    在这里插入图片描述

    6.8 SELECT和其他

    查看mysql版本号

    SELECT VERSION();

    • 1

    在这里插入图片描述
    查看当前登录账户

    SELECT CURRENT_USER();  

    • 1

    在这里插入图片描述
    查看当前所在数据库

    SELECT DATABASE();

    • 1

    在这里插入图片描述

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

    7.1 ALTER和DATABASE（数据库）

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

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

    • 1
    • 2
    • 3
    • 4

    在这里插入图片描述

    7.2 ALTER和TABLE（表格）

    官网文档：ALTER-TABLE

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

    use test3;
CREATE TABLE t1 (id int primary key auto_increment,name varchar(20));
INSERT t1(id,name)VALUES('1','AA');
INSERT t1(id,name)VALUES('2','BB');
SELECT * FROM t1;
SHOW CREATE TABLE t1;  查看表格属性

一般表格和数据库的字符集和排序规则一致，最好不要修改
ALTER TABLE t1 character set gbk;       修改t1表格字符集
ALTER TABLE t1 COLLATE=gbk_chinese_ci;  修改t1表格的排序规则

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10

    在这里插入图片描述
    增加表格字段（列）

    SELECT * FROM t1;     原本只有2个字段(列)id和name

指定在id后面增加新的列age
ALTER TABLE t1 add age int AFTER id;
SELECT * FROM t1;

    • 1
    • 2
    • 3
    • 4
    • 5

    在这里插入图片描述
    删除字段（列）

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

    • 1
    • 2
    • 3

    在这里插入图片描述
    修改字段名字

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

    • 1
    • 2
    • 3
    • 4

    在这里插入图片描述

    7.3 ALTER和USER（用户）

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

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

    • 1
    • 2
    • 3
    • 4

    在这里插入图片描述
    修改用户密码

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

    • 1
    • 2
    • 3

    在这里插入图片描述
    mysql 5.7以下老版本修改密码另外方式

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

    • 1
    • 2

    在这里插入图片描述
    修改当前登录账户密码

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

    • 1
    • 2
    • 3
    • 4

    在这里插入图片描述
    锁定和解锁账户

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

    • 1
    • 2

    在这里插入图片描述
    修改账户密码插件

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

    • 1
    • 2
    • 3

    在这里插入图片描述

    7.4 ALTER和INDEX（索引）

    对表格添加索引

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

    • 1
    • 2
    • 3

    在这里插入图片描述
    删除表格索引

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

    • 1
    • 2

    在这里插入图片描述

    7.5 ALTER和MODIFY（修改数据长度）

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

    • 1
    • 2
    • 3

    在这里插入图片描述

    7.6 ALTER和PRIMARY KEY（主键）

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

    CREATE TABLE Z1(id int,name char(10));   创建表格
ALTER TABLE Z1 MODIFY id int NOT NULL;   设置主键不为空
ALTER TABLE Z1 ADD PRIMARY KEY (id);     创建主键

因为主键也是索引的一种，所以这样查看
SHOW INDEX FROM Z1;                      查看表格主键信息

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6

    在这里插入图片描述
    删除主键

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

    • 1
    • 2

    在这里插入图片描述

    8、DROP 语句

    8.1 DROP和DATABASE（数据库）

    官网文档：DROP-DATABASE

    DROP DATABASE test1;   删除数据库test1

    • 1

    在这里插入图片描述

    8.2 DROP和TABLE（表格）

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

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

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6

    在这里插入图片描述
    删除表格

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

    • 1
    • 2

    在这里插入图片描述

    8.3 DROP和USER（用户）

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

    • 1
    • 2
    • 3

    在这里插入图片描述

    8.4 DROP和VIEW

    use test2;

SHOW TABLE STATUS LIKE 't_view'\G   查看视图信息
通过这个Comment: VIEW 可以看到这个表格实际是视图，不是平常的表格

DROP VIEW t_view;   删除视图

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6

    在这里插入图片描述

    8.5 DROP和其他

    删除自定义函数

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

    • 1
    • 2

    在这里插入图片描述
    删除存储过程

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

    • 1
    • 2
    • 3

    在这里插入图片描述
    删除触发器

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

    • 1
    • 2

    在这里插入图片描述

    9、GRANT 授权语句

    官网文档：GRANT

    创建账户和数据库

    创建t1账户
CREATE USER 't1'@'localhost' IDENTIFIED BY 'Test12#$';
CREATE DATABASE t1;                     创建t1数据库

授权t1账户对t1数据库有所有权限
GRANT ALL ON t1.* TO t1@'localhost';

创建t2数据库
CREATE DATABASE t2;

授权t1账户对t2账户具有查询权限
GRANT SELECT ON t2.* TO t1@'localhost';

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12

    在这里插入图片描述
    用root账户先在t2数据库创建表格

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

    • 1
    • 2
    • 3

    在这里插入图片描述

    测试权限是否授权成功

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

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

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6

    在这里插入图片描述
    查看t1账户对t2数据库的权限

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

    • 1
    • 2
    • 3
    • 4

    在这里插入图片描述

    10、REVOKE 撤销（删除）权限语句

    官网文档：REVOKE

    REVOKE ALL PRIVILEGES,GRANT OPTION FROM t1@'localhost'; 撤销全部权限
SHOW GRANTS FOR t1@localhost;          查看权限列表

这个是默认账户登录权限，必须要有才能使用账户
GRANT USAGE ON *.* TO `t1`@`localhost`

mysql -u t1 -p'Test12#$'    重新登录
use t1;                     可以看到没有t1数据库的权限

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8

    在这里插入图片描述
    撤销部分权限方式

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

    • 1
    • 2
    • 3
    • 4

    在这里插入图片描述
    用t1账户登录，测试权限

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

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6

    在这里插入图片描述

    11、SET语句

    SET设置变量文档：SET

    自定义临时变量

    SET @var_t1=11;              设置变量@var_t1，值为11
SELECT @var_t1;              查看变量

mysql -u tt1 -p'Test12#$'    退出登录后，自定义变量不会保存到数据库
SELECT @var_t1;              因为没保存，所以值为空

    • 1
    • 2
    • 3
    • 4
    • 5

    在这里插入图片描述
    系统变量
    文档：SET-VARIABLE
    Option File 这里的Yes表示这个内容是选项，需要去配置文件修改
    System Var 这里的Yes表示这个内容是变量，可以直接在mysql客户端修改值

    如果这2个都是Yes，代表可以在配置文件修改，也可以在mysql客户端修改值
    在这里插入图片描述
    查看默认系统字符集的变量

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

    • 1
    • 2
    • 3

    在这里插入图片描述
    全局变量（不会因为退出登录或重启而失效）

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

    • 1
    • 2
    • 3

    在这里插入图片描述

    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

    创建表格和索引

    CREATE TABLE e1(id int,name char(10));  创建表格
CREATE INDEX e1idx ON e1(id);   创建索引

插入数据
INSERT e1(id,name)VALUES('1','aa'),('2','bb'),('11','aa1'),('12','bb1');
SELECT * FROM e1;

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6

    在这里插入图片描述
    使用EXPLAIN查看使用索引和没有使用索引

    EXPLAIN SELECT id FROM e1 WHERE id='1';       使用了索引
可以看到type为ref，key为e1idx

EXPLAIN SELECT * FROM e1 WHERE id LIKE 'a%';  没有使用到索引
可以看到type为ALL和key为NULL，通过rows为4行，也可以看出进行了全表扫描
Using where表示使用到了where语句，但是不一定使用到索引

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6

    在这里插入图片描述
    查看Using index condition和Using filesort

    EXPLAIN SELECT * FROM e1 WHERE id in (1,11);  使用了索引
type为range，key为e1idx证明使用到了索引，Using index condition表示使用了索引和条件

EXPLAIN SELECT * FROM e1 ORDER BY id;
type为NULL，key为NULL，Using filesort表示是一个ORDER BY语句

    • 1
    • 2
    • 3
    • 4
    • 5

    在这里插入图片描述
    索引最优选择

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

EXPLAIN SELECT id FROM e1 WHERE id='1';       最优使用索引方式

    • 1
    • 2
    • 3
    • 4

    在这里插入图片描述
    Extra同时出现不同结果

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

    • 1
    • 2

    在这里插入图片描述

    13、DESCRIBE（简写DESC，主要用于查看表或视图结构）

    create table student (id int unsigned primary key auto_increment,name varchar(10),age tinyint unsigned,gender enum('M','F') default 'M');

DESC student;         2种都可以查看表结构
DESCRIBE student;

    • 1
    • 2
    • 3
    • 4

    在这里插入图片描述
    因为视图相当于另外一种方式的表格，所以可以用DESC命令

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

    • 1
    • 2

    在这里插入图片描述

    14、RENAME语句

    RENAME 修改账户名字

    把账户名字从t1@'localhost' 改为tt1@'localhost'

RENAME USER t1@'localhost' TO tt1@'localhost';
SELECT user,host FROM mysql.user;

    • 1
    • 2
    • 3
    • 4

    在这里插入图片描述

  • 相关阅读:
    工业读写器如何选型？
    【云原生&微服务九】SpringCloud之Feign实现声明式客户端负载均衡详细案例
    Springboot的Controller中的参数接收以及@Mapper和@Repository的区别
    OpenCV 01(图像加载与显示)
    软件测试技术之单元测试—工程师 Style 的测试方法（2）
    Pikachu靶场——越权访问漏洞(over permission)
    Linux11-权限的介绍 rwx详解 修改权限 修改文件目录所有者 修改文件目录所在组 一个实践和两个练习
    typescript：声明文件
    【Verilog基础】【总线协议】AHB和AHB-Lite的区别？AMBA2.0和AMBA3.0的区别？
    java 归档版本的下载入口
  • 原文地址：https://blog.csdn.net/DLWH_HWLD/article/details/125183867