MySQL:关系型数据库管理系统_SQL语言分类_执行流程_子查询_存储引擎

一、SQL语言分类

1.DDL

1.创建数据库表

create table 表名（
        字段名（列名）数据类型，
        字段名（列名）数据类型，
        ...
        ）
-- 创建数据库
create database 数据库名;

字段后还有其他字段，必须有 “，”

在最后一个字段后不允许有“ ，”

表名：字段名多个单词之间使用_链接

2.查看数据库表

-- 查看所有的数据库
show databases;
-- 选择数据库
use 数据库名;
-- 查看所有的表
show tables;
-- 查看表结构（查看表中字段等）
desc student;
​-- 查看建表语句
show create table student;

3.删除数据库表

-- 删除表
drop table 表名;
-- 删除数据库
drop database 数据库名;

4.修改数据库表

修改表名：rename table 旧表名 to 新表名；
向表中添加字段：alter table 表名 add 字段名称 字段类型；
修改表中字段：alter table 表名 modify 字段名 字段类型；
            或alter table 表名 change 旧字段名 新字段名 类型（长度）；
删除表中字段：alter table 表名 drop 字段名；

 2.DML操作

1.插入数据

-- 语法格式：
-- 1. 插入部分或全部字段 
insert into 表名(字段名1,字段名2,...,字段名n) values(值1,值2,...,值n);
-- 2. 插入全部字段 
insert into 表名 values(值1,值2,...,值n);
-- 一次添加多条数据
insert into 表名 values(值1,值2,...,值n),(值1,值2,...,值n),(值1,值2,...,值n);

-- 案例：向学生表中插入数据
-- 插入全部字段，将所有字段名都写出来
insert into stu(sid,sname,sex,age,createdate,clazz,email) values(1,'张飞','男',50,'1990-05-05','1班','zhangfei@qq.com');

注意事项

1. 值与字段必须要对应，个数相同、数据类型相同

2. 值的数据大小，必须在字段指定的长度范围内

3. varchar char date类型的值使用单引号或者双引号包裹

4. 如果要插入空值，可以忽略不写或者写null

5. 如果插入指定字段的值，必须要上写列名

6. 字符串日期类型支持 "2000-12-23" "2000/12/23" "2000.12.23"

 2.修改数据

-- 语法格式
update 表名 set 字段1=值1,字段2=值2,...,字段n=值n where 条件;
update 表名 set 字段1=值1,字段2=值2,...,字段n=值n; -- 慎用：不加条件会修改所有的数据

-- 案例1：将sid=1的学生性别改为女
update stu set sex='女' where sid=1;

3.删除数据

-- 语法格式
delete from 表名 where 条件;
delete from 表名; -- 慎用：删除表中的所有数据
truncate table 表名; -- 慎用：删除表中的所有数据

-- 案例1：删除sid=5的学生信息
delete from stu where sid=5;

3.DQL查询表中数据

1.简单查询

         查询不会对数据库中的数据进行修改, 只是一种显示数据的方式。

-- 语法格式：
select 字段名1,字段名2,... from 表名;

-- 将查询的字段全部列出
select eid ,ename, sex, salary, hire_date, dept_name from emp;

-- 查询所有的字段可以使用 * , * 代表所有的字段
select * from emp;

2.条件查询

-- 语法格式：
select 列名 from 表名 where 条件;

 比较运算符

运算符	说明
> < <= >= = <> !=	大于 小于 小于等于 大于等于 等于 不等于
between...and...	显示在某一区间的值
in(集合)	集合表示多个值，使用逗号分隔，例如name in (悟空,八戒); in中的每个数据都会作为一次条件，只要满足条件就会显示
like '%张%'	模糊查询
is null	查询某一列为null的值，注意，不能写 = null

逻辑运算符

运算符	说明
and &&	多个条件同时成立
or ||	多个条件任一成立
not	取反

3.排序

单行排序

-- 语法格式：
select 字段名 from 表名 [where 条件] order by 字段名[asc | desc];
-- asc  升序(默认)
-- desc 降序

组合排序

-- 组合排序
select * from emp order by salary, hire_date desc;

 4.函数

单行函数：单行函数是指对每一条记录的值进行计算，并得到相应的计算结果，然后返回给用户

使用单行函数, 是对行中字段的操作, 操作多少行, 返回多少行数据。

 字符处理函数

LENGTH(s)	返回字符串 s 的长度	返回字符串oldlu的字符数SELECT LENGTH("oldlu"); -- 5;
CONCAT(s1,s2...sn)	字符串 s1,s2 等多个字符串合并为一个字符串	合并多个字符串SELECT CONCAT("sxt ", "teacher ", "oldlu"); -- sxt teacher oldlu;
LPAD(s1,len,s2)	在字符串 s1 的开始处填充字符串 s2，使字符串长度达到 len	将字符串 x 填充到 oldlu字符串的开始处：SELECT LPAD('oldlu',8,'x'); -- xxxoldlu
LTRIM(s)	去掉字符串 s 开始处的空格	去掉字符串 oldlu开始处的空格：SELECT LTRIM(" oldlu") ;-- oldlu
REPLACE(s,s1,s2)	将字符串 s2 替代字符串 s 中的字符串 s1	将字符串 oldlu 中的字符 o 替换为字符 O：SELECT REPLACE('oldlu','o','O'); -- Oldlu
REVERSE(s)	将字符串s的顺序反过来	将字符串 abc 的顺序反过来：SELECT REVERSE('abc'); -- cba
RPAD(s1,len,s2)	在字符串 s1 的结尾处添加字符串 s2，使字符串的长度达到 len	将字符串 xx填充到 oldlu字符串的结尾处：SELECT RPAD('oldlu',8,'x'); -- oldluxxx
RTRIM(s)	去掉字符串 s 结尾处的空格	去掉字符串 oldlu 的末尾空格：SELECT RTRIM("oldlu "); -- oldlu
SUBSTR(s, start, length)	从字符串 s 的 start 位置截取长度为 length 的子字符串	从字符串 OLDLU中的第 2 个位置截取 3个 字符：SELECT SUBSTR("OLDLU", 2, 3); -- LDL
SUBSTRING(s, start, length)	从字符串 s 的 start 位置截取长度为 length 的子字符串	从字符串 OLDLU中的第 2 个位置截取 3个 字符：SELECT SUBSTRING("OLDLU", 2, 3); --LDL
TRIM(s)	去掉字符串 s 开始和结尾处的空格	去掉字符串 oldlu 的首尾空格： SELECT TRIM(' oldlu ');-- oldlu

 数值函数

函数	描述	实例
ABS(num)	返回num的绝对值	SELECT ABS(-1) -- 返回1
CEIL(num)	返回大于num的最小整数（向上取整）	SELECT CEIL(1.5) -- 返回2
FLOOR(num)	返回小于num的最大整数（向下取整）	SELECT FLOOR(1.5) -- 返回1
MOD(num1, num2)	返回num1/num2的余数（取模）	SELECT MOD(5,2) -- 1
PI()	返回圆周率的值	SELECT MOD(5,2) -- 1
POW(num,n)/POWER(num, n)	返回num的n次方	SELECT POW(2,3) -- 8
RAND(num)	返回0~1之间的随机数	SELECT RAND() --0.93099315644334
ROUND(num, n)	返回x四舍五入后的值，该值保留到小数点后n位	SELECT ROUND(1.23456,3) --1.235
TRUNCATE(num, n)	返回num被舍去至小数点后n位的值（与 ROUND 最大的区别是不会进行四舍五入）	SELECT TRUNCATE(1.23456,3) -- 1.234

日期函数

CURDATE()	返回当前日期	SELECT CURDATE(); -- 2022-07-20
CURTIME()	返回当前时间	SELECT CURTIME(); -> 19:59:02
NOW()	返回当前日期和时间	SELECT NOW() -> 2022-07-20 20:57:43

流程控制函数

IF(condition, t, f)	如果条件condition为真，则返回t，否则返回f
IFNULL(value1, value2)	如果value1不为null，则返回value1，否则返回value2
NULLIF(value1, value2)	如果value1等于value2，则返回null，否则返回value1
CASE WHEN [condition1] THEN result1 [WHEN [condition2] THEN result2 ...] [ELSE result] END	如果条件condition1为真，则返回result1，···，否则返回result

 多行函数：多行函数查询是纵向查询，它是对某一列的值进行计算，然后返回一个单一的值。

使用多行函数，是对某一列的值进行计算，然后返回一个单一的值

多行函数	作用
count(字段)	统计指定列不为null的记录行数
sum(字段)	计算指定列的数值和
max(字段)	计算指定列的最大值
min(字段)	计算指定列的最小值
avg(字段)	计算指定列的平均值

5.分组

select 分组字段/聚合函数 from 表名 group by 分组字段 [having 条件];
# 按照性别分组查询
select sex from emp group by sex;  -- 正确操作

6.limit关键字

作用

• limit是限制的意思, 限制返回的查询结果的函数(通过limit函数，控制查询返回多少行数据)

• limit 语法是 MySql的方言, 用来完成分页

 语法格式

select 字段1, 字段2 ... from 表名 limit offset, length;
#1 查询emp表中的前5条数据
select * from emp limit 5;   -- 不指定从哪行还是, 默认从0开始
select * from emp limit 0, 5;
#2 查询emp表中 从第4条开始, 查询6条
select * from emp limit 3, 6; -- 从0开始, 所以第四条数据为3

7.注意事项：

where和having的区别。

过滤方式	特点
where	分组之前的过滤后边不能写多行函数
having	分组之后的过滤后边可以写多行函数

select语句的执行顺序 from -- where -- group by -- having –- select -- order by

8.经典面试题

MySQL行列转置/MySQL行列转换(笔试题)

转换成下面效果

create table student(
	id int(11) primary key auto_increment,
	name varchar(20),
	subject varchar(20),
	score double
);

insert into student values(1,'张三','语文',20);
insert into student values(2,'张三','数学',30);
insert into student values(3,'张三','英语',40);
insert into student values(4,'李四','语文',50);
insert into student values(5,'李四','数学',60);
insert into student values(6,'李四','英语',70);
-- 方式1
select 
	max(if(subject='语文',score,0)) 语文,
	max(if(subject='数学',score,0)) 数学,
	max(if(subject='英语',score,0)) 英语,
	name 
from student group by name;
-- 方式2
select 
	max(case subject when '语文' then score else 0 end) 语文,
	max(case subject when '数学' then score else 0 end)  数学,
	max(case subject when '英语' then score else 0 end)  英语,
	name 
from student group by name;

 4.TCL数据库事务控制

1.事务简介

事务是一个整体，由一条或者多条SQL语句组成，这些SQL语句要么都执行成功，要么就失败，只要有一条SQL出现异常，整个操作就会回滚。

回滚: 就是事务运行的过程中发生了某种故障，或者SQL出现了异常，事务不能继续执行，系统将事务中对数据库的所有已完成的操作全部取消，回滚到事务开始时的状态。

手动控制事务的案例

• 手动开启事务 start transaction

  手动开启事务后，事务不再是自动提交

• 提交事务 commit

  没有出现任何问题，提交事务 数据就会持久化到本地磁盘

• 回滚事务 rollback

  出现问题回滚事务，回滚事务要在提交事务之前操作,一旦事务完成了提交，就不能 再回滚. 如出现宕机等情况事务会自动回滚

-- 开启事务
start transaction;

-- tom账户 -500
update account set money = money-500 where name = 'tom';
-- jack账户 +500
update account set money = money+500 where name = 'jack';

-- 提交事务(没发生任何问题提交)
commit;

-- 回滚事务(出现问题回滚，如果出现宕机等原因事务会自动回滚)
rollback;

2.事务四大特性

• 原子性（Atomicity） 一个事务要么全部提交成功，要么全部失败回滚，不能只执行其中的一部分操作，这就是事务的原子性

• 一致性（Consistency） 指事物必须是数据库从一个一致性状态到另一个一致性状态。也就是说一个事物执行之前和执行之后都必须处于一致性状态

• 隔离性（Isolation） 事务的隔离性是指在并发环境中，并发的事务时相互隔离的，一个事务的执行不被其他事务干扰。即并发执行的各个事务之间不相互干扰

• 持久性（Durability） 一旦事务提交，那么它对数据库中的对应数据的状态的变更就会永久保存到数据库中。即使发生系统崩溃或机器宕机等故障，只要数据库能够重新启动，那么一定能够将其恢复到事务成功结束的状态

 3.事务的并发访问产生的问题和隔离级别

        事务在操作时的理想状态：所有的事务之间保持隔离，互不影响。因为并发操作，多个用户同时访问同一个数据，可能引发并发访问的问题。

        隔离级别越高，越能保证数据的完整性和一致性，但是对并发性能的影响也越大。

        MVCC多版本控制机制，读取时采用快照读，读取的内容来源于快照，解决不可重复读，幻读。

多次读取过程中例如变更全部数据，读取最新的数据，造成幻读。

√会出现问题，×不会出现问题

查看隔离级别

select @@transaction_isolation;

设置隔离级别

-- 设置隔离级别语法格式
set session transaction isolation level 隔离级别名称;
-- 如: 设置为读未提交
set session transaction isolation level read uncommitted;

-- read uncommitted 	读未提交 
-- read committed   	读已提交 
-- repeatable read  	可重复读 
-- serializable      	串行化

5.DCL数据控制语言

1.用户管理

MySQL中可创建不同的用户，并分配不同的权限，保证MySQL中数据的安全性

MySQL用户主要包括两种：

1. root用户为超级管理员：拥有MySQL提供的所有权限

2. 普通用户:

权限取决于该用户在创建时被赋予的权限有哪些. 没有赋予任何权限，只有可以登录mysql的权限

用户表存在于mysql库中的user表中，要先选择mysql库再进行操作

-- 查询所有用户
use mysql;
select * from user;

-- 创建用户
create user '用户名'@'主机名' identified by '密码'; 
-- 主机名: localhost 只能本机连接  % 所有都可以连接

-- 修改用户密码
alter user '用户名'@'主机名' identified by '密码';

-- 删除用户
drop user '用户名'@'主机名'; 

2.权限管理

• MySQL通过权限管理机制可以给不同的用户授予不同的权限，从而确保数据库中数据的安全性

• 只能给存在的用户授权

• 新创建的用户只有登录的权限(USAGE)

语法格式

-- 为某个用户授权
grant 权限1，权限2 ... on 数据库名.表名 to '用户名'@'主机名';

-- 查看某个用户有哪些权限
show grants for '用户名'@'主机名';

-- 撤销用户权限
revoke 权限1，... on 数据库名.表名 from '用户名'@'主机名';

-- 刷新权限(添加，撤销授权之后一定要刷新权限)
flush privileges;

授权操作

#1 给用户zs1授予对bjsxt02 中的products表查询的权限
grant select on bjsxt02.products to 'zs1'@'localhost';

#2 给用户zs2 授予所有库所有表的所有权限
grant all on *.* to 'zs2'@'localhost';

#3刷新权限(授权操作之后一定要刷新权限)
flush privileges;

#4查看用户有哪些权限
show grants for 'zs1'@'localhost';
show grants for 'zs2'@'localhost';

3.角色管理

        MySQL 8.0 为了用户权限管理更容易，提供了一个角色管理的新功能。角色是指定的权限集合，和用户帐户一样可以对角色进行权限的授予和撤销。   如果用户被授予角色，则该用户就拥有了该角色的权限。

语法格式

-- 创建角色
create role '角色名称'，...;

-- 为角色授权
grant 权限1，权限2，... on 数据库名.表名 to '角色名称';

-- 为用户分配角色
grant '角色名称' to '用户名'@'主机名';

-- 撤销角色的权限
revoke 权限1，... on 数据库名.表名 from '角色名称';

具体

#1 创建两个角色
create role 'role1'，'role2';  -- 角色名字自定义
#2 为这角色role1 role2授权
grant select on bjsxt02.products to 'role1'; -- bjsxt02库products表 查询权限
grant all on *.* to 'role2'; -- 所有库所有表全部权限
#3 查看角色的权限
show grants for 'role1';
show grants for 'role2';
#4 创建两个用户 ls1 ls2
create user 'ls1'@'localhost' identified by '111111';
create user 'ls2'@'localhost' identified by '222222';
#5 用户分配角色
grant 'role1' to 'ls1'@'localhost';
grant 'role2' to 'ls2'@'localhost';
#6 查看用户的角色
show grants for 'ls1'@'localhost'; -- 拥有了该角色，便拥有了该角色的权限
show grants for 'ls2'@'localhost';
#7 使角色在账号登陆后自动被激活
set global activate_all_roles_on_login=ON;

 4开启远程连接

语法格式

# 创建用户
create user 'cy'@'%' identified by '123456';

#授权
#grant all on *.* to 'cy'@'%';
grant select on *.* to 'cy'@'%';

#刷新权限
flush privileges;

二、SQL执行流程

 

执行流程：

1. 客户端向服务器端发送SQL命令和连接参数

2. 服务器端连接模块连接并验证

3. 缓存模块解析SQL为Hash并与缓存中Hash表对应。如果有结果直接返回结果，如果没有对应继续向下执行。如果是MySQL 8 是没有查询缓存的。

4. 解析器解析SQL为解析树，检查关键字相关问题，如果出现错误，报SQL解析错误。如果正确，继续执行

5. 预处理器对解析树继续处理检查表、列别名等，处理成功后生成新的解析树。

6. 优化器根据开销自动选择最优执行计划，生成执行计划

7. 执行器执行执行计划，访问存储引擎接口

8. 存储引擎访问物理文件并返回结果

9. 如果开启查询缓存，缓存管理器把结果放入到查询缓存中。

10. 返回结果给客户端

 三、子查询

1.什么是子查询

子查询概念

        一条select查询语句的结果作为另一条select语句的一部分

特点

子查询一般作为查询条件使用

使用子查询， 必须将子查询放在小括号中使用

一条SQL语句含有多个select，一般是先执行子查询，再执行外查询

2.单行子查询

         单行子查询: 查询出的结果为一列一行(一个数据) 如: 最高，最低，平均等，可以使用判断符号 如: > | < | = | != 等

-- 语法格式:
select 字段 from 表 where 字段 判断符号 (子查询);

#1 查询价格最高的商品信息
-- 1.查询出商品最高的价格
select max(price) from products;  -- 5000
select * from products where price = 5000;
-- 2.将查询出的最高价格作为条件查询，获取商品信息 
select * from products where price = (select max(price) from products);

3.多行子查询

多行子查询: 查询出的结果为一列多行(多个数据) 如: 化妆品类别下的商品pid，可以使用判断符号 如: in all any

• in : 等于任意一个

  使用方式:

  in(值1，值2 ...)

• all: 所有

  使用方式:

  如: 字段 > | < all(值1，值2 ...) 大于所有的值

• any: 任意一个

  使用方式

  如: 字段 > | < any(值1，值2 ...) 大于任意一个值

  如: 字段 = any(值1，值2 ...) 等于任意一个值 效果等同于 in

 四、存储引擎

1.概述

        数据库存储引擎：是数据库管理系统中的重要组成部分。数据库管理系统（DBMS）使用存储引擎进行创建、查询、更新和删除数据。

MySQL的核心就是插件式存储引擎。

MySQL 可以通过 show engines 查看所有支持的存储引擎

2. 各个引擎的介绍

1. InnoDB 默认的存储引擎，也是所有存储引擎中唯一支持事务、XA协议的存储引擎。

2. MyISAM 基于ISAM（Indexed Sequential Access Method目前已经废弃）的存储引擎，特点是查询效率较高。但不支持事务和容错性。

3. MEMORY 纯内存型型存储引擎。所有数据都在内存中，硬盘只存储.frm文件。所以当MySQL宕机或非法关闭时只生效表结构。所有数据都在内存上，相对来说性能较高。

4. MRG_MYISAM 以前也叫MERGE，简单理解就是对MyISAM表做了特殊的封装，对外提供单一访问入口，减少程序的复杂性。

5. ARCHIVE存储引擎主要用于通过较小的存储空间来存放过期的很少访问的历史数据。ARCHIVE表不支持索引，通过一个.frm的结构定义文件，一个.ARZ的数据压缩文件还有一个.ARM的meta信息文件。由于其所存放的数据的特殊性，ARCHIVE表不支持删除，修改操作，仅支持插入和查询操作。

6. BLACKHOLE 俗称“黑洞”存储引擎。是一个非常有意思的存储引擎。所有的数据都是有去无回。

7. CSV存储引擎实际上操作的就是一个标准的CSV文件，不支持索引。起主要用途就是大家有些时候可能会需要通过数据库中的数据导出成一份报表文件，而CSV文件是很多软件都支持的一种较为标准的格式，所以我们可以通过先在数据库中建立一张CSV表，然后将生成的报表信息插入到该表，即可得到一份CSV报表文件了。

8. PERFORMANCE_SCHEMA 从MySQL 5.6新增的存储引擎。主要用于收集一些系统参数。

 五、count(*),count(列),count(1)的区别（面试题）

对于InnoDB引擎count(*)和count(1)没有性能的区别，都是自动寻找主键列或唯一索引且非空约束。统计的时候都是统计包含null的行，所以效果都是返回表中数据的行数。 而count(列) 统计列中包含多少行数据，不包含NULL值。

统计说明：

• 在InnoDB引擎中count(*)和count(1)性能没有什么差别

• count(列)需要看列和count(*)优化后的列情况，如果count(列)使用了非索引列，而表中包含索引列则count(*)更快。如果count（列）和count(*)优化后的是同一个列则性能没有什么差别。如果表中没有索引则count（列）和count(*)性能也没有什么差别。