MSQL使用sql大全（完整+细节）

╰+哭是因爲堅強的太久メ 2024-04-24 14:45 3阅读 0赞

> **小聊** ：小白本来只想记录一些不是特别常用的，但是要用的时候一下子记不太清楚的sql语句。但是觉得这样的sql不是很多，干脆整理一份比较全的MSQL的sql语句使用语法。其中除了特别简单的sql句子，其它的都有使用示例，每个例子都测试过。内容丰富而精炼，用于复习和使用时快速查找很方便。建议收藏哦，大佬见笑。

*  1. Sql 分类
 *  2. DDL（数据定义语言）
    
     *  2.1. 数据库操作
     *  2.2. 表结构操作
     *  2.3. 外键级联
 *  3. DML（数据操纵语言）
    
     *  3.1. 表数据增删改
 *  4. DQL（数据查询语言）
    
     *  4.1. 基础查询
     *  4.2. 条件查询
     *  4.3. 聚合查询
     *  4.4. 分组查询
     *  4.5. 分页查询
     *  4.6. DQL语句执行顺序
     *  4.7. 内连接
     *  4.8. 外连接
     *  4.9. 自连接
     *  4.10. 子查询
 *  5. DCL（数据控制语言）
    
     *  5.1. 创建用户
     *  5.2. 用户授权
     *  5.3. 撤销授权
     *  5.4. 查看权限
     *  5.5. 删除用户
     *  5.6. 修改密码
 *  6. 其它SQL
 *  随笔

## 1. Sql 分类 ##

<table> 
 <thead> 
  <tr> 
   <th>SQL分类</th> 
   <th>全称</th> 
   <th>说明</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>DDL（数据定义语言）</td> 
   <td>Data Definition Language</td> 
   <td>如：建库，建表</td> 
  </tr> 
  <tr> 
   <td>DML（数据操纵语言）</td> 
   <td>Data Manipulation Language</td> 
   <td>如：对表中的记录操作增删改</td> 
  </tr> 
  <tr> 
   <td>DQL（数据查询语言）</td> 
   <td>Data Query Language</td> 
   <td>如：对表中的查询操作</td> 
  </tr> 
  <tr> 
   <td>DCL（数据控制语言）</td> 
   <td>Data Control Language</td> 
   <td>如：对用户权限的设置</td> 
  </tr> 
 </tbody> 
</table>

## 2. DDL（数据定义语言） ##

#### 2.1. 数据库操作 ####

*  **查询所有数据库**

show databases;

*  **查询当前数据库**

select database();

*  **创建数据库**

create database [ if not exists ] 数据库名 [ default charset 字符集 ] [ collate 排序规则 ];

*  **删除数据库**

drop database [ if exists ] 数据库名;

*  **切换数据库**

use 数据库名;

*  **查询指定数据库的建库语句**

show create database 数据库名;

#### 2.2. 表结构操作 ####

*  **查询当前数据库所有表**

show tables;

*  **查看指定表结构**

desc 表名;

*  **查询指定表的建表语句**

show create table 表名;

*  **创建表结构**

create table 表名(
        字段1 字段1类型 [ default 默认值 ] [ comment 字段1注释 ],
        字段2 字段2类型 [ default 默认值 ] [ comment 字段2注释 ],
        字段3 字段3类型 [ default 默认值 ] [ comment 字段3注释 ],
        ......
        字段n 字段n类型 [ default 默认值 ] [ comment 字段n注释 ],
        primary key(列名),
        [ constraint ] [ 外键约束名称 ] foreign key(外键字段名) references 主表名(主键字段名)
    ) [ comment 表注释 ] ;

<table> 
 <thead> 
  <tr> 
   <th>字段属性</th> 
   <th>说明</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>unsigned</td> 
   <td>无符号的整数，声明了该列不能声明为负数</td> 
  </tr> 
  <tr> 
   <td>zerofill</td> 
   <td>0填充的，不足的位数，使用O来填充，如：int (3) , 5 — 005</td> 
  </tr> 
  <tr> 
   <td>auto_increment</td> 
   <td>通常理解为自增，自动在上一条记录的基础上＋1(默认)，通常用来设计唯一的主键，必须是整数类型，可以自定义设计主键自增的起始值和步长</td> 
  </tr> 
  <tr> 
   <td>null / not null</td> 
   <td>如果设置为 not null ，不给它赋值就会报错；如果设置默认就是 null，字段可为空</td> 
  </tr> 
  <tr> 
   <td>default</td> 
   <td>设置默认的值</td> 
  </tr> 
  <tr> 
   <td>comment</td> 
   <td>默认为空，常在添加字段时备注中文信息</td> 
  </tr> 
  <tr> 
   <td>primary key</td> 
   <td>主键</td> 
  </tr> 
  <tr> 
   <td>unique</td> 
   <td>唯一</td> 
  </tr> 
  <tr> 
   <td>foreign key</td> 
   <td>外键</td> 
  </tr> 
 </tbody> 
</table>

创建表建议添加的字段属性：每一个表，建议存在以下五个字段。未来做项目用的，表示一个记录存在意义

id				-- 主键
    `version`		-- 乐观锁
    is_delete 		-- 伪删除
    created_at		-- 创建时间
    updated_at		-- 修改时间

*  **快速创建一个表结构相同的表**

create table [ if exists ] 新表名 like 旧表名;

*  **删除表**

drop table [ if exists ] 表名;

*  **删除指定表，并重新创建表**

truncate table 表名;

*  **修改表结构**

1.  添加表列 add

alter table 表名 add 列名 类型;
    -- 使用示例
    -- alter table student add age int(4);

1.  在已有表中添加/删除主键

alter table 表名 add primary key(字段名);  -- 添加
    alter table 表名 drop primary key;        -- 删除
    -- 使用示例
    -- alter table student add primary key(id);
    -- alter table student drop primary key;

1.  在已有表中添加/删除外键

alter table 表名 add [ constraint ] [ 外键约束名称 ] foreign key(外键字段名) references 主表名(主键字段名);  -- 添加
    alter table 从表 drop foreign key 外键约束名称;  -- 删除
    -- 使用示例
    -- alter table employee add constraint dep_empid foreign key(dep_id) references department(id);
    -- alter table employee drop foreign key dep_empid;

1.  修改列类型 modify

alter table 表名 modify 列名 新的类型;
    -- 使用示例
    -- alter table student modify age long;

1.  修改列名 change

alter table 表名 change 旧列名 新列名 类型;
    -- 使用示例
    -- alter table student change age high long;

1.  删除列 drop

alter table 表名 drop 列名;
    -- 使用示例
    -- alter table student drop high;

1.  修改表名 rename… to

rename table 表名 to 新表名;
    -- 使用示例
    -- rename table teacher to doctor;

1.  修改表的字符集 character set

alter table 表名 character set 字符集;
    -- 使用示例
    -- alter table doctor character set gbk;

#### 2.3. 外键级联 ####

在修改和删除主表的主键时，同时更新或删除副表的外键值，称为级联操作。如果存在外键绑定主表但是没有定义相关级联操作，不可以对主表的主键进行修改和删除操作。  
创建外键的时候同时定义级联关系。  
但是在业务系统中一般不会去修改主表的主键值。

<table> 
 <thead> 
  <tr> 
   <th>级联操作语法</th> 
   <th>描述</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>ON UPDATE CASCADE</td> 
   <td>级联更新，更新主表中的主键，从表中的外键列也自动同步更新</td> 
  </tr> 
  <tr> 
   <td>ON DELETE CASCADE</td> 
   <td>级联删除</td> 
  </tr> 
 </tbody> 
</table>

*  **理解和使用举例**

-- 创建部门表
    create table department(
    	id int auto_increment,
    	name varchar(20) not null,
    	primary key(id)
    );
    -- 添加数据到部门表
    insert into department values(null, '侦查部'),(null, '指挥部');
    -- 创建角色表时添加级联更新和级联删除
    create table role(
    id int primary key auto_increment,
    name varchar(20),
    age int,
    dep_id int, -- 外键对应主表的主键
    constraint emp_depid_fk foreign key (dep_id) references department(id) on update cascade on delete cascade	-- 创建外键约束和级能关系
    );
    -- 添加数据到员工表
    INSERT INTO role (NAME, age, dep_id) VALUES ('鸢一折纸', 18, 1);
    INSERT INTO role (NAME, age, dep_id) VALUES ('时崎狂三', 18, 1);
    INSERT INTO role (NAME, age, dep_id) VALUES ('夜刀神十香', 19, 1);
    INSERT INTO role (NAME, age, dep_id) VALUES ('五河琴里', 16, 2);
    INSERT INTO role (NAME, age, dep_id) VALUES ('崇宫澪', 20, 2);
    INSERT INTO role (NAME, age, dep_id) VALUES ('星空六喰', 19, 2);
    
    -- 数据操作示例：
    -- 删除部门表？能不能直接删除？当然不可以！报错！
    drop table department;
    -- 把部门表中id等于1的部门改成id等于10，role表数据也会修改
    update department set id = 10 where id = 1;
    -- 查看两个表的数据修改情况
    select * from role;
    select * from department;
    -- 删除部门号是 2 的部门，role表数据也会被删除
    delete from department where id = 2;
    -- 查看两个表的数据删除情况
    select * from role;
    select * from department;

*  **表存在时设计级联关系**

alter table role add constraint emp_depid_fk foreign key(dep_id) references department(id) on update cascade on delete cascade;	-- 如果已经有了同名的外键约束将无法成功，需要先删除那个同名的外键

*  **删除级联关系**

-- 不能直接去除外键的级联关系，只能先删除外键约束在重新定义外键约束；
    alter table role drop foreign key emp_depid_fk;		
    alter table role add constraint emp_depid_fk foreign key(dep_id) references department(id);

## 3. DML（数据操纵语言） ##

#### 3.1. 表数据增删改 ####

*  **插入数据**

1.  方式一：不写字段名（表示所有属性全部添加值）

insert into 表名 values (值1, 值 2, 值3…)

1.  方式二：写字段名，表示选择性赋值

insert into 表名 (字段名1, 字段名2, 字段名3…) values (值1, 值2, 值3);

1.  方式三：一次性添加多条数据

insert into 表名 (字段名1, 字段名2, 字段名3…) values (值1, 值 2, 值3…), (值1, 值 2, 值3…), (值1, 值 2, 值3…) .....;

*  **蠕虫复制添加列数据**

1.  复制所有列及其数据

insert into 表名1 select * from 表名2;

1.  复制部分列及其数据

insert into 表名1(列1, 列2) select 列1, 列2 from 表名2;

*  **更新数据**

update 表名 set 列名=值 [ where 条件表达式 ];

*  **删除数据**

delete from 表名 [ where 条件表达式 ];

## 4. DQL（数据查询语言） ##

#### 4.1. 基础查询 ####

*  **简单查询**

1.  查询所有列数据

select * from 表名 [ where 条件表达式 ];

1.  查询指定列的数据

select 字段名1, 字段名2, 字段名3, ... from 表名 [ where 条件表达式 ];

*  **as 指定别名进行查询**

-- 对列和表分别指定别名（as可省略不写，以空格隔开），一旦为表起了别名，就不能再使用表名来指定对应的字段了，此时只能够使用别名来指定字段。
    select 字段名1 [ as ] 别名, 字段名2 [ as ] 别名... from 表名 [ as ] 表别名 [ where 条件表达式 ];
    -- 使用示例
    -- select name AS '名字', sex '性别' from student AS stu where stu.name = '鸢一折纸';

*  **查询指定列并且结果不出现重复数据**

select distinct 字段名 from 表名;

*  **查询结果参与运算**

1.  某列数据和固定值运算

select 列名1 + 固定值 from 表名;
    -- 使用示例
    -- select sorce + 10 from student;

1.  某列数据和其他列数据参与运算

select 列名1 + 列名2 from 表名;
    -- 使用示例
    -- select sorce * weight from student;

#### 4.2. 条件查询 ####

*  **简单条件查询**

select 字段名 from 表名 where 条件;

<table> 
 <thead> 
  <tr> 
   <th>比较运算符</th> 
   <th>说明</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>&gt; 、&lt; 、&lt;= 、&gt;= 、= 、&lt;&gt;、!=</td> 
   <td>&lt;&gt;在 SQL 中表示不等于，在 mysql 中也可以使用 != <strong>没有 ==</strong></td> 
  </tr> 
  <tr> 
   <td>BETWEEN…AND</td> 
   <td>在一个范围之内，如：between 100 and 200相当于条件在 100 到 200 之间，包头又包尾</td> 
  </tr> 
  <tr> 
   <td>IN(…)</td> 
   <td>… 表示多个值，使用逗号分隔，包头又包尾</td> 
  </tr> 
  <tr> 
   <td>LIKE ’ 张%’</td> 
   <td>模糊匹配查询</td> 
  </tr> 
  <tr> 
   <td>IS [NOT] NULL</td> 
   <td>查询某一列为 NULL 的值</td> 
  </tr> 
 </tbody> 
</table>

-- 使用示例
    SELECT * FROM student WHERE sorce >= 30;
    SELECT * FROM student WHERE sorce BETWEEN 34 AND 65;
    SELECT * FROM student WHERE sorce in(34, 65);
    SELECT * FROM student WHERE sorce IS NOT NULL;

<table> 
 <thead> 
  <tr> 
   <th>逻辑运算符</th> 
   <th>说明</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>and 或 &amp;&amp;</td> 
   <td>与，SQL 中建议使用前者，后者并不通用。</td> 
  </tr> 
  <tr> 
   <td>or 或 ||</td> 
   <td>或</td> 
  </tr> 
  <tr> 
   <td>not 或 !</td> 
   <td>非</td> 
  </tr> 
 </tbody> 
</table>

-- 使用示例
    SELECT * FROM student WHERE name = '张三' AND sex = '男';
    SELECT * FROM student WHERE name = '张三' or '如花';
    SELECT * FROM student WHERE name = '张三' or sex = '男';
    SELECT * FROM student WHERE  sex != '男';

*  **in 关键字**

-- in 里面的每个数据都会作为一次条件，只要满足条件的就会显示
    select 字段名 from 表名 where 字段 in ( 数据1, 数据2...);
    -- 使用示例
    -- select * from student where sorce in(60, 61, 62);

*  **between … and … 范围查询**

-- 表示从值1 到值2 范围，包头又包尾
    select 字段名 from 表名 where 字段 between 值1 and 值2;
    -- 使用示例
    -- select * from student where sorce between 60 and 89;

*  **like 关键字**

-- LIKE 表示模糊查询，%代表未知，_代表占位
    select 字段名 from 表名 where 字段名 like '通配符字符串';
    -- 使用示例
    -- select * from student where  name like '%忆%';
    -- select * from student where  name like '_忆_';

*  **order by 排序**

1.  单列排序：只按某一个字段进行排序

-- asc：升序，默认的；desc：降序
    select 字段名 from 表名 where 字段 = 值 order by 字段名 [ asc|desc ];

1.  组合排序：同时对多个字段进行排序，如果第 1 个字段相等，则按第 2 个字段排序，依次类推。

select 字段名 from 表名 where 字段 = 值 order by 字段名 1 [ asc|desc ], 字段名 2 [ asc|desc ];

#### 4.3. 聚合查询 ####

<table> 
 <thead> 
  <tr> 
   <th>SQL的聚合函数</th> 
   <th>作用</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>max(列名)</td> 
   <td>求这一列的最大值</td> 
  </tr> 
  <tr> 
   <td>min(列名)</td> 
   <td>求这一列的最小值</td> 
  </tr> 
  <tr> 
   <td>avg(列名)</td> 
   <td>求这一列的平均值</td> 
  </tr> 
  <tr> 
   <td>count(列名)</td> 
   <td>统计这一列有多少条记录</td> 
  </tr> 
  <tr> 
   <td>sum(列名)</td> 
   <td>对这一列求总和</td> 
  </tr> 
 </tbody> 
</table>

*  查询聚合结果值

select 聚合函数(列名) from 表名;
    -- 使用示例
    -- select count(*) as 总人数 from student;		-- 查询学生总人数
    -- select count(sorce) as 总人数 from student;		-- 查询学生拥有分数（为null的查不出来）的总人数
    -- select count(ifnull(sorce, 0)) from student; 	-- 查询学生拥有分数（包括了曾为NULL现为0的人）的总人数
    
    -- select sum(math) 总分 from student;		 -- 查询数学成绩总分
    -- select avg(math) 平均分 from student;		-- 查询数学成绩平均分
    -- select max(math) 最高分 from student;		-- 查询数学成绩最高分
    -- select min(math) 最低分 from student;		-- 查询数学成绩最低分

#### 4.4. 分组查询 ####

*  **group by 分组查询**

select 字段 1, 字段 2... from 表名 [ where 条件 ] group by 分组字段名 [ having 条件 ];
    -- 使用示例
    -- select sex,name from student group by sex;		-- group by 将分组字段结果中相同内容作为一组，并且返回每组的第一条数据，所以单独分组没什么用处。分组的目的就是为了统计，一般分组会跟聚合函数一起使用。
    -- select sex,count(*) from student group by sex;		-- 统计每种性别的总人数
    -- select sex,count(*) from student where sorce > 30 group by sex;	-- 查询年龄大于25岁的人，按性别分组，统计每组的人数
    -- select sex,count(*) from student where sorce > 30 group by sex having count(*) > 1; -- 当每个分组人数大于1个时

<table> 
 <thead> 
  <tr> 
   <th>字段</th> 
   <th>作用</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>where 子句</td> 
   <td>1) 对查询结果进行分组前，将不符合 <strong>where</strong> 条件的行去掉，即在分组之前过滤数据，即先过滤再分组。<br>2) <strong>where</strong> 后面不可以使用聚合函数</td> 
  </tr> 
  <tr> 
   <td>having 子句</td> 
   <td>1) <strong>having</strong> 子句的作用是筛选满足条件的组，即在分组之后过滤数据，即先分组再过滤。<br>2) <strong>having</strong> 后面可以使用聚合函数</td> 
  </tr> 
 </tbody> 
</table>

**注意：当我们使用某个字段分组，在查询的时候也必须将这个字段查询出来，否则会执行错误，看不到数据属于哪组的**

#### 4.5. 分页查询 ####

*  **limit 分页查询**

-- 起始索引值：从0开始计数，如果省略默认就是0；查询记录数：不够的话有多少显示多少
    select 字段列表 from 表名 limit 起始索引值, 查询记录数;
    -- 使用示例
    -- select * from student limit 2, 5;  -- 从第三条开始显示最多5条数据
    -- select * from student limit 5;     -- 如果第一个参数是0可以省略写

**注意：起始索引 = （查询页码 - 1）\* 每页显示记录数。**

#### 4.6. DQL语句执行顺序 ####

> **from > where > group by > having > select > order by > limit**

#### 4.7. 内连接 ####

两张表的交集部分，显示符合条件的。如：从表.外键=主表.主键

*  **隐式内连接**

-- 看不到 JOIN 关键字，条件使用 WHERE 指定
    select 字段名 from 左表, 右表 where 条件;
    -- 使用示例
    -- select role.*,department.name dep_name from role,department where role.dep_id = department.id;

*  **显式内连接**

-- 使用 INNER JOIN ... ON 语句, 可以省略 INNER
    select 字段名 from 左表 [ inner ] join 右表 on 条件 [ where 查询条件 ]
    -- 使用示例
    -- select r.*,d.name dep_name from role r inner join department d on r.dep_id = d.id;

#### 4.8. 外连接 ####

*  **左外连接**

用左边表的记录去匹配右边表的记录，如果符合条件的则显示；否则，显示 NULL  
可以理解为：在内连接的基础上保证左表的数据全部显示的情况下，补充右表数据，没有则显示null（比如：左表是部门，右表角色，有的部门可以没有角色）

-- outer 可以省略
    select 字段名 from 左表 left [ outer ] join 右表 on 条件;
    -- 使用示例
    -- insert into department(name) values ('后勤部');		-- 在部门表中增加一个后勤部，不添加角色绑定这个部门
    -- 对比使用左外连接查询和内连接查询
    -- select d.name dep_name,r.* from  department d left join role r on d.id = r.dep_id;		-- 使用左外连接查询
    -- select d.name dep_name,r.* from  department d inner join role r on d.id = r.dep_id;		-- 使用内连接查询

*  **右外连接**

用右边表的记录去匹配左边表的记录，如果符合条件的则显示；否则，显示 NULL  
可以理解为：在内连接的基础上保证右表的数据全部显示的情况下，补充右表数据，没有则显示null

select 字段名 from 左表 left [ outer ] join 右表 on 条件;
    -- 使用示例
    -- 对比使用右外连接查询和内连接查询
    -- select r.*,d.name dep_name from role r right join department d on r.dep_id = d.id;		-- 使用右外连接查询
    -- select r.*,d.name dep_name from role r inner join department d on r.dep_id = d.id;		-- 使用内连接查询

*  **注意**

左外连接和右外连接是可以相互替换的，只需要调整在连接查询SQL中，表结构的先后顺序。在日常开发使用时，更偏向于左外连接。

#### 4.9. 自连接 ####

*  **自连接查询**

-- 自己连接自己，把一张表连接查询多次
    select 字段列表 from 表1 别名1 join 表2 别名2 on 条件 ...;
    -- 使用示例
    -- 需要删除重置部门表和角色表
    -- alter table role add leader_id int comment '设置上下属关系，对应其它角色id值；0的话表示上级为自己';   -- 给role表添加一个父级leader_id属性
    -- update role set leader_id = 0 where id = 1;        -- 将id为1的角色的上级设置成自己
    -- update role set leader_id = 1 where id in (2, 3);  -- 将id为2和3的上级设置为id为1角色
    -- select a.id 成员id,a.name 成员,b.name 上级,b.id 上级id from role a join role b where a.leader_id = b.id;  -- 可用于查询角色及其上级角色

*  **union 联合查询**

-- 把多次查询的结果合并起来，形成一个新的查询结果集。union all会将全部的数据直接合并在一起，union会对合并之后的数据去重。“字段列表”需要一致。
    select 字段列表 from 表1 where ...
    union [all]
    select 字段列表 from 表2 where ...;
    -- 使用示例
    -- select * from role where id in (1, 2, 3) union all select * from role where id >= 2;
    -- select * from role where id in (1, 2, 3) union select * from role where id >= 2; -- 没有 all，查询结果会去重

#### 4.10. 子查询 ####

一个查询的结果做为另一个查询的条件；有查询的嵌套，内部的查询称为子查询；子查询要使用括号

<table> 
 <thead> 
  <tr> 
   <th>种类</th> 
   <th>说明</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>标量子查询</td> 
   <td>子查询结果为单个值，常用的操作符：=、&lt;&gt;、&gt;、&gt;=、&lt;、&lt;=</td> 
  </tr> 
  <tr> 
   <td>列子查询</td> 
   <td>子查询结果为一列（可以是多行），常用的操作符：in、not in 、any、some、all</td> 
  </tr> 
  <tr> 
   <td>行子查询</td> 
   <td>子查询结果为一行（可以是多列），常用的操作符：=、&lt;&gt;、in、not in</td> 
  </tr> 
  <tr> 
   <td>表子查询</td> 
   <td>子查询结果为多行多列，常用的操作符：in</td> 
  </tr> 
 </tbody> 
</table>

*  **行子查询**

select 查询字段 from 表 where 字段=(子查询);
    -- 使用示例
    -- select * from role where dep_id = (select id from department where id = 1); -- 查询部门id为1的所有角色

*  **列子查询**

select 查询字段 from 表 where 字段 in (子查询);
    -- 使用示例
    -- select * from role where dep_id in (select id from department where id = 1 or id = 2); -- 查询部门id为1和2的所有角色

*  **行子查询**

select 查询字段 from 表 where (字段1, 字段2) = (子查询);
    -- 使用示例
    -- select * from role where (id, leader_id) = (select id, leader_id from role where id = 2 and leader_id = 1);

*  **表子查询**

select 查询字段 from 表 where (字段1, 字段2) in (子查询);
    -- select * from role where (id, leader_id) in (select id, leader_id from role where id <= 3 and leader_id = 1);

## 5. DCL（数据控制语言） ##

#### 5.1. 创建用户 ####

CREATE USER '用户名'@'主机名' IDENTIFIED BY '密码';

<table> 
 <thead> 
  <tr> 
   <th>关键字</th> 
   <th>说明</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>‘用户名’</td> 
   <td>将创建的用户名</td> 
  </tr> 
  <tr> 
   <td>‘主机名’</td> 
   <td>指定该用户在哪个主机上可以登陆，如果是本地用户可用 localhost，如果想让该用户可以从任意远程主机登陆，可以使用通配符%</td> 
  </tr> 
  <tr> 
   <td>‘密码’</td> 
   <td>该用户的登陆密码，密码可以为空，如果为空则该用户可以不需要密码登陆服务器</td> 
  </tr> 
 </tbody> 
</table>

*  **创建 user1 用户，只能在 localhost 这个服务器登录 mysql 服务器，密码为 123**

create user 'user1'@'localhost' identified by '123';

*  **创建 user2 用户可以在任何电脑上登录 mysql 服务器，密码为 123**

create user 'user2'@'%' identified by '123';

注意：创建的用户名都在 **mysql** 数据库中的 **user** 表中可以查看到，但是密码经过了加密。

select * from mysql.user; -- 查看所有用户

#### 5.2. 用户授权 ####

用户创建之后，没什么权限，需要给用户授权

GRANT 权限 1, 权限 2... ON 数据库名.表名 TO '用户名'@'主机名';

<table> 
 <thead> 
  <tr> 
   <th>关键字</th> 
   <th>说明</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>GRANT…ON…TO</td> 
   <td>授权关键字</td> 
  </tr> 
  <tr> 
   <td>权限</td> 
   <td>授予用户的权限，如 CREATE、ALTER、SELECT、INSERT、UPDATE 等。如果要授予所有的权限则使用 ALL</td> 
  </tr> 
  <tr> 
   <td>数据库名. 表名</td> 
   <td>该用户可以操作哪个数据库的哪些表。如果要授予该用户对所有数据库和表的相应操作权限则可用*表示，如 * . *</td> 
  </tr> 
  <tr> 
   <td>‘用户名’@‘主机名’</td> 
   <td>给哪个用户授权，注：有 2 对单引号</td> 
  </tr> 
 </tbody> 
</table>

*  **给 user1 用户分配对 test 这个数据库操作的权限：创建表，修改表，插入记录，更新记录，查询**

grant create,alter,insert,update,select on test.* to 'user1'@'localhost';	-- 用户名和主机名要与上面创建的相同，要加单引号

*  **给 user2 用户分配所有权限，对所有数据库的所有表**

grant all on *.* to 'user2'@'%';

#### 5.3. 撤销授权 ####

REVOKE 权限 1, 权限 2... ON 数据库. 表名 revoke all on test.* from '用户名'@'主机名';

<table> 
 <thead> 
  <tr> 
   <th>关键字</th> 
   <th>说明</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>REVOKE…ON…FROM</td> 
   <td>撤销授权的关键字</td> 
  </tr> 
  <tr> 
   <td>权限</td> 
   <td>用户的权限，如 CREATE、ALTER、SELECT、INSERT、UPDATE 等，所有的权限则使用 ALL</td> 
  </tr> 
  <tr> 
   <td>数据库名.表名</td> 
   <td>对哪些数据库的哪些表，如果要取消该用户对所有数据库和表的操作权限则*可用表示，如 * . *</td> 
  </tr> 
  <tr> 
   <td>‘用户名’@‘主机名’</td> 
   <td>给哪个用户撤销</td> 
  </tr> 
 </tbody> 
</table>

*  **撤销 user1 用户对 test 数据库所有表的操作的权限**

revoke all on test.* from 'user1'@'localhost';  -- 用户名和主机名要与创建时相同，各自要加上单引号

#### 5.4. 查看权限 ####

show grants for '用户名'@'主机名';

*  **查看 user1 用户的权限**

show grants for 'user1'@'localhost';

usage 是指连接（登陆）权限，建立一个用户，就会自动授予其 usage 权限（默认授予）。

#### 5.5. 删除用户 ####

drop user '用户名'@'主机名';

*  **删除user2**

drop user 'user2'@'%';

#### 5.6. 修改密码 ####

alter user '用户名'@'主机名' identified by '密码'; -- MYSQL8.0

## 6. 其它SQL ##

*  **查看系统变量**

show [ session | global ] variables;                 -- 查看所有系统变量
    show [ session | global ] variables like '......';   -- 可以通过LIKE模糊匹配方式查找变量
    select @@[ session | global ] 系统变量名;              -- 查看指定变量的值

*  **设置系统变量**

set [ session | global ] 系统变量名 = 值;
    set @@[ session | global ] 系统变量名 = 值;

如果没有指定session/global，默认是session ，会话变量。mysql服务重新启动之后，所设置的全局参数会失效，要想不失效，可以在/etc/my.cnf 中配置。

<table> 
 <thead> 
  <tr> 
   <th>参数</th> 
   <th>说明</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>全局变量(global)</td> 
   <td>全局变量针对于所有的会话</td> 
  </tr> 
  <tr> 
   <td>会话变量(session)</td> 
   <td>会话变量针对于单个会话，在另外一个会话窗口就不生效了。</td> 
  </tr> 
 </tbody> 
</table>

*  **查看MySQL内部设置的编码**

show variables like 'character%';  -- 查看包含 character 开头的全局变量

*  **查询当前数据库支持的存储引擎**

show engines;

*  **查询当前数据库的各类SQL的访问频次（执行性能分析）**

session：是查看当前会话；global：是查询全局数据；

<table> 
 <thead> 
  <tr> 
   <th>参数</th> 
   <th>说明</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>Com_insert</td> 
   <td>插入次数</td> 
  </tr> 
  <tr> 
   <td>Com_update</td> 
   <td>更新次数</td> 
  </tr> 
  <tr> 
   <td>Com_delete</td> 
   <td>删除次数</td> 
  </tr> 
  <tr> 
   <td>Com_select</td> 
   <td>查询次数</td> 
  </tr> 
 </tbody> 
</table>

show global status like 'Com_______';  -- 查询所有语句各自次数

*  **查询MySQL的慢查询日志开关**

show variables like 'slow_query_log';

如果要开启慢查询日志，需要在MySQL的配置文件（/etc/my.cnf）中配置如下信息：

# 开启MySQL慢日志查询开关
    slow_query_log=1
    # 设置慢日志的时间为2秒，SQL语句执行时间超过2秒，就会视为慢查询，记录慢查询日志
    long_query_time=2

配置完毕之后，通过以下指令重新启动MySQL服务器进行测试，查看慢日志文件中记录的信息：/var/lib/mysql/localhost-slow.log

重启mysql服务

systemctl restart mysqld

再次查看开关情况，慢查询日志就已经打开了

*  **profile 查询sql执行详情（sql优化时间消耗）**

1.  查看是否支持profile操作

select @@have_profiling; -- mysql 支持

1.  查看profile操作开关是否打开

select @@profiling;  -- mysql 默认开启（1表示开启）

我们所执行的SQL语句，都会被MySQL记录，并记录执行时间消耗到哪儿去了。 你可以随便执行几条SQL。然后查看他们的时间消耗：

-- 查看每一条SQL的耗时基本情况
    show profiles;

-- 查看指定query_id的SQL语句各个阶段的耗时情况
    show profile for query query_id;

-- 查看指定query_id的SQL语句CPU的使用情况
    show profile cpu for query query_id;

*  **explain 获取MySQL如何执行select语句的信息**

-- 直接在select语句之前加上关键字 explain / desc
    explain select 字段列表 from 表名 where 条件;

Explain 语句查询结果的表格中各个字段的含义：

<table> 
 <thead> 
  <tr> 
   <th>字段</th> 
   <th>含义</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>id</td> 
   <td>select查询的序列号，表示查询中执行select子句或者是操作表的顺序 (id相同，执行顺序从上到下；id不同，值越大，越先执行)。</td> 
  </tr> 
  <tr> 
   <td>select_type</td> 
   <td>表示 SELECT 的类型，常见的取值有 SIMPLE（简单表，即不使用表连接或者子查询）、PRIMARY（主查询，即外层的查询）、UNION（UNION 中的第二个或者后面的查询语句）、SUBQUERY（SELECT/WHERE之后包含了子查询）等</td> 
  </tr> 
  <tr> 
   <td>type</td> 
   <td>表示连接类型，性能由好到差的连接类型为NULL、system、const、eq_ref、ref、range、 index、all 。</td> 
  </tr> 
  <tr> 
   <td>possible_key</td> 
   <td>显示可能应用在这张表上的索引，一个或多个。</td> 
  </tr> 
  <tr> 
   <td>key</td> 
   <td>实际使用的索引，如果为NULL，则没有使用索引。</td> 
  </tr> 
  <tr> 
   <td>key_len</td> 
   <td>表示索引中使用的字节数， 该值为索引字段最大可能长度，并非实际使用长度，在不损失精确性的前提下， 长度越短越好 。</td> 
  </tr> 
  <tr> 
   <td>rows</td> 
   <td>MySQL认为必须要执行查询的行数，在innodb引擎的表中，是一个估计值，可能并不总是准确的。</td> 
  </tr> 
  <tr> 
   <td>filtered</td> 
   <td>表示返回结果的行数占需读取行数的百分比， filtered 的值越大越好。</td> 
  </tr> 
  <tr> 
   <td>Extra</td> 
   <td>使用索引情况：<br>Using where; Using index：查找使用了索引，但是需要的数据都在索引列中能找到，所以不需要回表查询数据<br>Using index condition：查找使用了索引，但是需要回表查询数据</td> 
  </tr> 
 </tbody> 
</table>

## 随笔 ##

![在这里插入图片描述][7e3b7d03bc1e409d839dfabfd7f416b0.jpeg_pic_center]

[7e3b7d03bc1e409d839dfabfd7f416b0.jpeg_pic_center]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/24/015e36acadfa48f89c0b2dd7ea6d9ad1.jpeg