mysql - 思维导图

数据库

数据库是什么

存储和操作数据的

类似excel、wps表格数据。但是它没有图形界面。

数据库只存储文本格式数据。文件存储在网盘（对象存储）里，数据库存储文件的访问路径（链接）。

数据库功能相对单一，运行效率高，安全...

注意：数据库一般不存储图片视频之类的文件，只存储对应文件的地址。

文件用对象存储，也就是直接存放在硬盘上的。

分类

SQL（关系型）

概述

关系型数据库操作方式基本通用，只有部分方言

单机

概述

不能联网的，仅用于本地操作

举例

sqlite

案例

你的手机断网后，还能看到聊天记录...

网络

概述

可以联网，远程操作

举例

mysql

oracle

sqlServer

案例

登录qq账号密码后，腾讯的数据库会把你的对应信息发送给你。

子主题

NoSQL（非关系型）

概述

一般用内存方式计算，键值对方式存储操作，操作方式不通用，根据需要去学习

种类

分析型、数据仓库（不生产数据）

ClickHouse

实时存储型（生产数据）

redis

后端开发必需要知道的。内存操作。

mongodb

硬盘操作。

分布式、数据仓库

Hbase

分析、搜索引擎

Elasticsearch（简称ES）

简称ES

solar

子主题

消息队列

RabbitMQ

后端开发必需要知道的

Kafka

为什么选择mysql

安全

数据安全性

效率

运行效率高

跨平台

各种各样的版本

通用

采用SQL标准类型

拓展

分布式

集群

API接口多，功能强大

开源

源代码公开，免费使用

市场

市场选择，主流

学习

社区活跃，学习成本低

方便

能连接网络，使用方便

学什么

基础

账号密码，远程登录，权限...

库操作

增

删

改

查

表字段（列）操作

增

删

改

查

表内容操作

增

DML

删

DML

改

DML

查

DQL

mysql软件组成

服务端Server

需要启动mysql服务端软件

客户端Client

用客户端连接服务端才能进行操作，官方自带的客户端是命令行，操作不友好，所以除了部分操作，其他大部分操作都用第三方图形化工具操作。

图形化客户端工具

因为mysql自带的客户端连接工具不方便我们使用，所以我们需要借助第三方连接工具

DataGrip

功能强大，体积大，收费，需破解

SQLyog

海豚，功能简单，体积小，使用简单，免费

Navicat

国产，需要破解，一般般

数据类型

有无符号

SIGNED

有符号，可以负数（默认）

UNSIGNED

无符号，只能整数

数值

tinyint

数值很小时

bigint

常用的数值

decimal

小数点，钱相关

其他

float、double、int、integer、smallint

小数点规定

double(x,y)

x表示字符总长度（也就是多少个数字，不包括小数点） y表示小数点后面几位

案例

1). 年龄字段 -- 只能正数, 而且人的年龄不会太大 age tinyint unsigned 2). 分数 -- 总分100分, 最多出现一位小数 score double(4,1)

字符串

CHAR

定长字符串(需要指定长度)

varchar

可变长度，需要指定长度

--定义名字长度最长为20个 name VARCHAR(20)

text

长文本

其他

LONGTEXT、LONGBLOB、

字符串

案例

1). 用户名 username ------> 长度不定, 最长不会超过50 username varchar(50) 2). 性别 gender ---------> 存储值, 不是男,就是女 gender char(1) 3). 手机号 phone --------> 固定长度为11 phone char(11)

日期

不建议用，直接用字符串即可。日期是有范围的。

DATE

YYYY-MM-DD

TIME

HH:MM:SS

YEAR

YYYY

DATETIME

YYYY-MM-DD HH:MM:SS

TIMESTAMP

YYYY-MM-DD HH:MM:SS

...

函数

详情请参考https://www.runoob.com/mysql/mysql-functions.html

聚合函数

字符串函数

数值函数

子主题

日期时间函数

子主题

高级函数

if

SELECT if(money=2000,'两千元','不是两千') '是否是两千' FROM account;

case then

SELECT (CASE WHEN a = 1 THEN '一' WHEN a = 2 THEN '两' END) '多少' FROM account;

ifnull

SELECT IFNULL(money,0) FROM account;

mysql8新增的函数

子主题

基础

语法

不区分大小写，一般关键字用大写，有无;分号结尾得看具体的客户端

注释

单行注释

--或#

多行注释

/**/

服务

linux

启动mysql服务

停止mysql服务

查看mysql状态

查看mysql端口

win

启动mysql服务

net start mysql80

停止mysql服务

net stop mysql80

查看mysql状态

查看mysql端口

其他

查看版本

卸载

安装

子主题

路径

查看数据库存放路径

show variables like '%datadir%';

显示支持的编码

SHOW CHARSET

显示默认字符集

SHOW VARIABLES LIKE '%character_set%'

显示支持的引擎

SHOW ENGINES

显示系统变量

SHOW GLOBAL VARIABLES

函数

聚合函数

请看group by分组

其他函数

字符串函数

CONCAT(S1,S2,...Sn)：字符串拼接，将S1，S2，... Sn拼接成一个字符串 LOWER(str)：将字符串str全部转为小写 UPPER(str) ：将字符串str全部转为大写 LPAD(str,n,pad)：左填充，用字符串pad对str的左边进行填充，达到n个字符串长度 RPAD(str,n,pad)：右填充，用字符串pad对str的右边进行填充，达到n个字符串长度 TRIM(str)：去掉字符串头部和尾部的空格 SUBSTRING(str,start,len)：返回从字符串str从start位置起的len个长度的字符串

数值函数

CEIL(x)：向上取整 FLOOR(x)：向下取整 MOD(x,y)：返回x/y的模 RAND()：返回0~1内的随机数 ROUND(x,y)：求参数x的四舍五入的值，保留y位小数

日期函数

CURDATE()：返回当前日期 CURTIME()：返回当前时间 NOW()：返回当前日期和时间 YEAR(date)：获取指定date的年份 MONTH(date)：获取指定date的月份 DAY(date)：获取指定date的日期 DATE_ADD(date, INTERVAL exprtype)：返回一个日期/时间值加上一个时间间隔expr后的时间值 DATEDIFF(date1,date2)：返回起始时间date1 和结束时间date2之间的天数

流程函数

IF(value , t , f)：如果value为true，则返回t，否则返回f IFNULL(value1 , value2)：如果value1不为空，返回value1，否则返回value2 CASE WHEN [ val1 ] THEN [res1] ... ELSE [ default ] END：如果val1为true，返回res1，... 否则返回default默认值 CASE [ expr ] WHEN [ val1 ] THEN [res1] ... ELSE [ default ] END：如果expr的值等于val1，返回res1，... 否则返回default默认值

--if： select if(false, 'Ok', 'Error'); --ifnull： select ifnull('Ok','Default'); select ifnull('','Default'); select ifnull(null,'Default');

--case when then else end： --查询emp表的员工姓名和工作地址 (北京/上海 ----> 一线城市 , 其他 ----> 二线城市) select name, ( case workaddress when '北京' then '一线城市' when '上海' then '一线城市' else '二线城市' end ) as '工作地址' from emp;

约束

概论

约束简单说就是规定数据的要求。是作用于表中字段上的，可以在创建表/修改表的时候添加约束。

分类

非空约束：限制该字段的数据不能为，null NOT NULL 唯一约束：保证该字段的所有数据都是唯一、不重复的，UNIQUE 主键约束：主键是一行数据的唯一标识，要求非空且唯一，PRIMARY KEY 默认约束：保存数据时，如果未指定该字段的值，则采用默认值，DEFAULT 检查约束(8.0.16版本之后)：保证字段值满足某一个条件，CHECK 外键约束：不建议用，学习成本增加，开发测试维护麻烦，运行效率低。用来让两张表的数据之间建立连接，保证数据的一致性和完整性，FOREIGN KEY

案例

CREATE TABLE tb_user( id int AUTO_INCREMENT PRIMARY KEY COMMENT 'ID唯一标识', name varchar(10) NOT NULL UNIQUE COMMENT '姓名' , age int check (age > 0 && age <= 120) COMMENT '年龄' , status char(1) default '1' COMMENT '状态', gender char(1) COMMENT '性别' );

远程登录权限

mysql5.7

用root账号操作

# 创建用户，其中user和password是自己指定的

CREATE USER 'user'@'%' IDENTIFIED BY 'password';

# 赋权

GRANT ALL PRIVILEGES ON *.* TO 'user'@'%' WITH GRANT OPTION;

# 刷新权限

FLUSH PRIVILEGES;

# 退出MySQL

quit;

DCL

账户

注意

1、MySQL中需要通过用户名@主机名的方式，来唯一标识一个用户。 2、主机名可以使用 % 通配。 3、这类SQL开发人员操作的比较少，主要是DBA（ Database Administrator 数据库管理员、专业运维、技术总监等管理者）使用。

登录

mysql [-h 地址 -P 端口] -u 账号 -p 密码

mysql -uroot -p

mysql -uroot -proot

增

不指定密码

CREATE USER 账号

创建用户

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

删

删除用户

DROP USER '用户名'@'主机名' ;

收回用户某些特定权限

REVOKE <权限类型> [(<列名>)][,<权限类型>[(<列名>)]]... ON <对象类型><权限名> FROM <用户1> [,<用户2>]

收回特定用户的所有权限

REVOKE ALL PRIVILEGES,GRANT OPTION FROM user<用户1>[,<用户2>]...

改

修改用户密码

ALTER USER '用户名'@'主机名' IDENTIFIED WITH mysql_native_password BY '新密码' ;

查

显示所有账户

select * from mysql.user;

Host代表当前用户访问的主机, 如为localhost, 仅代表只能够在当前本机访问，是不可以远程访问。 User代表的是访问该数据库的用户名。在MySQL中通过Host和User来唯一标识一个用户。

查看指定账户权限

SHOW GRANTS FOR 账号;

案例

--创建用户user1, 只能够在当前主机localhost访问, 密码123456; create user 'user1'@'localhost' identified by '123456'; --创建用户user1, 可以在任意主机访问该数据库, 密码123456; create user 'user1'@'%' identified by '123456'; --修改用户heima的访问密码为1234; alter user 'user1'@'%' identified with mysql_native_password by '1234'; --删除 itcast@localhost 用户 drop user 'user1'@'localhost';

权限

注意

注意事项：操作权限的前提是需要是管理员账户 • 多个权限之间，使用逗号分隔 • 授权时，数据库名和表名可以使用 * 进行通配，代表所有。

权限类型

所有权限：ALL, ALL PRIVILEGES 查询数据：SELECT 插入数据：INSERT 修改数据：UPDATE 删除数据：DELETE 修改表：ALTER 删除数据库/表/视图：DROP 创建数据库/表：CREATE

操作权限

GRANT 参数1 ON 参数2 TO 参数3

参数1

<权限类型> [(<列名>)][,<权限类型>[(<列名>)]]

参数2

<对象><权限级别>

参数3

<用户>

增

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

删

REVOKE 权限列表 ON 数据库名.表名 FROM '用户名'@'主机名';

查

SHOW GRANTS FOR '用户名'@'主机名' ;

案例

远程登录权限

mysql8.0、mysql5.7版本好像需要配置远程登录

--查询 'heima'@'%' 用户的权限 show grants for 'heima'@'%'; --授予 'heima'@'%' 用户itcast数据库所有表的所有操作权限 grant all on itcast.* to 'heima'@'%'; --撤销 'heima'@'%' 用户的itcast数据库的所有权限 revoke all on itcast.* from 'heima'@'%';

DDL

库

概论

一个库类似一个excel表格的文件

操作

使用

使用一个库

USE 库名;

增

语法

CREATE DATABASE [IF NOT EXISTS] 数据库名 [DEFAULT CHARSET 字符集] [COLLATE 排序规则]

案例

CREATE DATABASE 库名

CREATE DATABASE IF NOT EXISTS 库名

删

语法

DROP DATABASE [IF NOT EXISTS] 库名

案例

DROP DATABASE 库名

DROP DATABASE IF NOT EXISTS 库名

改

改库名

ALTER DATABASE 旧库名 RENAME TO 新库名;

改编码

ALTER DATABASE test DEFAULT CHARACTER SET gb2312 DEFAULT COLLATE gb2312_chinese_ci

查

显示所有库

SHOW DATABASES;

查看库信息

SHOW CREATE DATABASE 库名

查询当前使用的库名

select database()

表

概论

一个表类似excel表格文件里面的sheet

操作

增

创建一个表

基本

CREATE TABLE 表名( 字段1 类型 [COMMENT 字段1注释 ], 字段2 类型 [COMMENT 字段2注释 ], 字段3 类型 [COMMENT 字段3注释 ] ) [COMMENT 表注释 ]

案例

create table tb_user( id int comment '编号', name varchar(50) comment '姓名', age int comment '年龄', gender varchar(1) comment '性别' ) comment '用户表';

复杂

CREATE TABLE student3( id BIGINT PRIMARY KEY, NAME VARCHAR(20), age INT )

把id设置为主键

复制表

create table 表名 like 被复制的表名;

创建一个表

CREATE TABLE 表名 (字段相关) [表选项] [表分区]

(字段相关)

列名类型备注

[表选项]

[表分区]

删

drop table 表名; drop table if exists 表名 ;

注意: 在删除表的时候，表中的全部数据也都会被删除。

改

改表名

ALTER TABLE 表名 RENAME TO 新表名;

--将emp表的表名修改为 employee ALTER TABLE emp RENAME TO employee;

改编码

改表字符集

alter table 表名 character set 字符集名称;

改全部的字符集

--(通过查看表编码可获得，例如utf8_general_ci) alter table 表名 convert to character set 字符集名 collate mysql里的字符集名;

更改主键

ALTER TABLE <数据表名> ADD PRIMARY KEY(<列名>);

查

显示所有表

SHOW TABLES;

查看表结构信息

DESC 表名

其他

某表字符集、创建结构：show create table 表名; 查看表的编码：show full columns from 表名;

字段（列）

概论

专业来说叫字段，通俗来说叫列，列标题啥的

操作

增

增加字段

ALTER TABLE 表名 ADD 字段名类型 (长度) [ COMMENT 注释 ] [ 约束 ];

--为emp表增加一个新的字段”昵称”为nickname，类型为varchar(20) ALTER TABLE emp ADD nickname varchar(20) COMMENT '昵称';

删

删除字段

ALTER TABLE 表名 DROP 字段名;

--将emp表的字段username删除 ALTER TABLE emp DROP username;

改

修改字段类型

ALTER TABLE 表名 MODIFY 字段名新数据类型 (长度);

修改字段类型和字段名

alter table 表名 change 列名新列名新类型;

ALTER TABLE 表名 CHANGE 旧字段名新字段名类型 (长度) [ COMMENT 注释 ] [ 约束 ];

--将emp表的nickname字段修改为username，类型为varchar(30) ALTER TABLE emp CHANGE nickname username varchar(30) COMMENT '昵称';

改类型

alter table 表名 modify 列名新类型;

查

DML(增删改)

概论

DML为增删改，DQL为查询操作

操作

增

一般方式

添加一条（必学）

给所有列添加

insert into 表名 values(值1,值2...);

给指定列添加

insert into 表名(列名1,列名2,...) values(值1,值2,...);

案例

除了数字类型，其他类型需要使用引号(单双都可以)引起来。列名和值要对应。

--给employee表所有的字段添加数据 insert into employee(id,workno,name,gender,age,idcard,entrydate) values(1,'1','Itcast','男',10,'123456789012345678','2000-01-01');

批量添加（非重点）

INSERT INTO 表名 (字段名1, 字段名2, ...) VALUES (值1, 值2, ...), (值1, 值2, ...), (值 1, 值2, ...) ;

INSERT INTO 表名 VALUES (值1, 值2, ...), (值1, 值2, ...), (值1, 值2, ...) ;

套娃方式

子主题

文件方式

load

子主题

删（物理删除）

不推荐

delete from 表名 [where 条件]

如果不加条件，则删除表中所有记录!!!

有多少条记录就会执行多少次删除操

delete from employee where gender = '女';

DELETE 语句不能删除某一个字段的值(可用UPDATE，将该字段值置为NULL即可)。

推荐用

TRUNCATE TABLE 表名

效率高先删表，后再创建一样的表。

改（逻辑删除）

update 表名 set 列名1 = 值1,列名2 = 值2... [where 条件];

update employee set name = 'itheima' where id = 1;

子主题

DQL（查）

基本语法顺序

select 列 from 表名 where 条件 group by 分组字段 having 分组之后的条件 order by 排序 limit 分页限定

条件查询

类型

运算符

>、<、<= 、>= 、= 、<>、!=、...between...and...、in(集合)、not int(集合)

模糊查询

like：模糊查询、占位符：_单个任意字符，%多个任意字符

布尔

is null、is not null、(and、&&)、(or、||)、not、!

案例

-- 查询年龄大于20岁：SELECT * FROM student WHERE age > 20; -- 查询年龄20、30之间的： SELECT * FROM student WHERE age >= 20 && age <=30; SELECT * FROM student WHERE age BETWEEN 20 AND 30; -- 查询年龄22岁，18岁的信息： SELECT * FROM student WHERE age IN (22,18); SELECT * FROM student WHERE age = 22 OR age = 18; -- 查询英语成绩为是否null时不能用=或!=判断,应该用is null或is not null。 SELECT * FROM student WHERE english IS NULL; -- 查询姓马的有哪些？SELECT * FROM student WHERE uname LIKE '马%'; -- 查询姓名是3个字的人：SELECT * FROM student WHERE uname LIKE '___'; -- 查询姓名中包含德的人：SELECT * FROM student WHERE uname LIKE '%德%'; -- 查询姓名第二个字是化的：select * from student where uname LIKE "_化%";

代码执行顺序

1、from 2、where 3、先select别名，然后group by 4、having 5、select、distinct 6、order by 7、limit

这里部分顺序有误

基础查询

1.查询所有列：select * from 表名 2.查询指定列：select 列名1，列名2... from 表名; 3.去除重复数据：select distinct 列名 from 表名; 4.计算列：可用四则运算计算列的值。（一般只进行数值型的计算） ifnull(需要判断的列,替换值)：null参与的运算，计算结果都为null 5.起别名as：as也可以省略，表和列都可以取。有时候别名可以不用加双引号。 select name "名字"，age as "年龄" from student st;

聚合查询

语法：select 聚合函数 from 表名; 1.count(列名)：计算行数，一般选择非空且唯一的，如主键。表示算所有时用count(*) 2.max(列名)：最大值 3.min(列名)：最小值 4.sum(列名)：求和 5.avg(列名)：平均值聚合函数计算排除null值。所以应选择不包含非空的列进行计算或用IFNULL函数，如果为null则用0替代，一般做法如下：select count(ifnull(列名,0)) from student;

分组查询

语法：select 需要查询的数据 from 表名 group by 按哪个列分；注意1：分组之后需要查询的数据只能是分组字段、聚合函数。 select class,sum(age) from student group by class 注意2：select查询的字段用了别名，那么group by 后面也应该用别名，而且不用再加引号 select class "班级",sum(age) "总年龄" from student group by 班级

分组后筛选

注意：having执行在group by之后，order by之前 where 和 having 的区别？ 1.where在分组之前进行限定，不满足条件的不参与分组。having在分组之后进行限定，不满足结果，则不会被查询出来 2.where后不可以跟聚合函数，having可以进行聚合函数的判断。 --按性别分组。分别查询男女同学的平均分,人数 select sex , avg(math),count(id) from student group by sex; --按性别分组。分别查询男女同学的平均分。分数低于70分的人，不参与分组 select sex , avg(math) from student where math > 70 group by sex; --按性别分组。分别查询男女同学的平均分/人数，分低于70且人数小于2不参与分组 select sex , avg(math),count(id) from student where math > 70 group by sex having count(id) > 2; select sex , avg(math),count(id) 人数 from student where math>70 group by sex having 人数>2;

排序查询

注意：order by执行在having之后，limit之前。语法：select * from student order by 排序字段1 排序方式1,...; 排序方式： 1、ASC：升序。默认值。 2、DESC：降序。 3、有多个排序条件，则当前边的条件值一样时，才会判断第二条件。按年龄排序(两种结果是一样的)： select * from student order by age; select * from student order by age asc; 先排序年龄，再排序分数： select * from student order by age asc , score desc;

分页查询

注意：limit是最后的执行 1.语法：limit 开始的索引,从索引开始往后查多少行; limit是个"方言" 2.公式： limit x,y x：从那一行开始并减1。(专业来说表示开始的索引，从0开始)。开始的索引 =（当前的页码 - 1）* 每页显示的条数 y：查询多少条数据。（专业来说表示从当前索引开始，往后查多少条数据。） --每页显示3条记录， SELECT * FROM student LIMIT 0,3; -- 第1页，查询1-3行数据。 SELECT * FROM student LIMIT 3,3; -- 第2页，查询4-6行数据。 SELECT * FROM student LIMIT 3; --只查前面3行数据。

套娃查询

标量子查询

返回结果是一个值

列子查询

返回结果是一列，也可以是多行

常用操作符

IN

在指定的集合范围之内，多选1。

NOT IN

不在指定的集合范围之内。

ANY

子查询返回的列表中，有任意一个满足即可。

SOME

与any相同，使用SOME的地方都可以使用ANY。

ALL

子查询返回列表的所有值必须满足。

行子查询

返回结果是一行一列（或一行多列）

常用操作符

=、<>、in、not in

表子查询

返回多行多列，也就是一个表

常用操作符

in

DQL（多表）

多表关系

一对一

一个学生对应一张身份证信息

主键放哪张表看情况而定

一对多（多对一）

一个学生可以有多个手机，多个手机只能是一个学生的

一个部门对应多个员工，一个员工对应一个部门

一个账单一个用户，一个用户有多个账单

在多的那张表添加指向另一张表的主键

多对多

一个学生可以对应多门专科课，一门专业课可以对应多个学生

建立一个中间表，表上存放两个相关表的主键

内连接（一对一，一对多）

相当于查询A、B表的交集部分数据，where和join的功能差不多。

隐式where

SELECT 字段 FROM 表1 , 表2 WHERE 表1.字段=表2.字段 ... ;

显示join

[inner] join 表名on 条件

SELECT 字段 FROM 表1 JOIN 表2 ON 表1.字段=表2.字段 ... ;

案例

--查询每一个员工的姓名 , 及关联的部门的名称 (隐式内连接实现) select e.name,d.name from emp e , dept d where e.dept_id = d.id; --查询每一个员工的姓名 , 及关联的部门的名称 (显式内连接实现) select e.name,d.name from emp e join dept d on e.dept_id = d.id;

外连接

一般用左外连接就行了。

左外

left [outer] join

取两个表的交集部分，再加上第一个表（左表）的全部内容

SELECT 字段 FROM 表1 LEFT JOIN 表2 ON 条件 ... ;

右外

right [outer] join

取两个表的交集部分，再加上第二个表（右表）的全部内容

SELECT 字段 FROM 表1 RIGHT JOIN 表2 ON 条件 ... ;

案例

子主题

自连接

自己连接自己，当前表与自身的连接查询，自连接必须使用表别名。但其实而对于自连接查询，它可以是内连接查询，也可以是外连接查询。

案例

--查询员工及其所属领导的名字 select a.name , b.name from emp a , emp b where a.managerid = b.id; --查询所有员工 emp 及其领导的名字 emp , 如果员工没有领导, 也需要查询出来 select a.name '员工', b.name '领导' from emp a left join emp b on a.managerid = b.id;

联合查询

对于union查询，就是把多次查询的结果合并起来，形成一个新的查询结果集。对于联合查询的多张表的列数必须保持一致，字段类型也需要保持一致。

UNION

取两个表的并集部分，并去掉重复

UNION ALL

取两个表的并集部分，不去掉重复

案例

--将薪资低于 5000 的员工 , 和年龄大于 50 岁的员工全部查询出来. --当前对于这个需求，还可以直接使用多条件查询，逻辑运算符 or 连接。 select * from emp where salary < 5000 union all select * from emp where age > 50;

套娃（子查询）

概论

子查询外部的语句可以是INSERT / UPDATE / DELETE / SELECT 的任何一个。

分类方式1

标量子查询（子查询结果为单个值）

子查询返回的结果是单个值（数字、字符串、日期等）。操作符：=、<>、 >、 >=、 <、 <=

--查询 "销售部" 的所有员工信息 select * from emp where dept_id = (select id from dept where name = '销售部'); --查询在 "张三" 入职之后的员工信息 select * from emp where entrydate > (select entrydate from emp where name = '张三');

列子查询(子查询结果为一列)

子查询返回的结果是一列（可以是多行）。操作符： IN：在指定的集合范围之内，多选一 NOT IN：不在指定的集合范围之内 ANY：子查询返回列表中，有任意一个满足即可 SOME：与ANY等同，使用SOME的地方都可以使用ANY ALL：子查询返回列表的所有值都必须满足

--查询 "销售部" 和 "市场部" 的所有员工信息 select * from emp where dept_id in (select id from dept where name = '销售部' or name = '市场部'); --查询比财务部所有人工资都高的员工信息 select * from emp where salary > all ( select salary from emp where dept_id = (select id from dept where name = '财务部') ); --查询比研发部其中任意一人工资高的员工信息 select * from emp where salary > any ( select salary from emp where dept_id = (select id from dept where name = '研发部') );

行子查询(子查询结果为一行)

子查询返回的结果是一行（可以是多列）。常用的操作符：= 、<> 、IN 、NOT IN

--查询与 "张三" 的薪资及直属领导相同的员工信息 ; select * from emp where (salary,managerid) = (select salary, managerid from emp where name = '张三');

表子查询(子查询结果为多行多列)

子查询返回的结果是多行多列，常用的操作符：IN

--查询与 "张三" , "李四" 的职位和薪资相同的员工信息 select * from emp where (job,salary) in ( select job, salary from emp where name = '张三' or name = '李四' ); --查询入职日期是 "2006-01-01" 之后的员工信息 , 及其部门信息 select e.*, d.* from (select * from emp where entrydate > '2006-01-01') e left join dept d on e.dept_id = d.id ;

分类方式2

WHERE之后

子主题

FROM之后

子主题

SELECT之后

子主题

案例

数据环境准备

create table salgrade( grade int, losal int, hisal int ) comment '薪资等级表'; insert into salgrade values (1,0,3000); insert into salgrade values (2,3001,5000); insert into salgrade values (3,5001,8000); insert into salgrade values (4,8001,10000); insert into salgrade values (5,10001,15000); insert into salgrade values (6,15001,20000); insert into salgrade values (7,20001,25000); insert into salgrade values (8,25001,30000);

--emp员工表、dept部门表、salgrade薪资等级表 1）查询员工的姓名、年龄、职位、部门信息（隐式内连接） select e.name , e.age , e.job , d.name from emp e , dept d where e.dept_id = d.id; 2）查询年龄小于30岁的员工的姓名、年龄、职位、部门信息（显式内连接） select e.name , e.age , e.job , d.name from emp e inner join dept d on e.dept_id = d.id where e.age < 30; 3）查询拥有员工的部门ID、部门名称 select distinct d.id , d.name from emp e , dept d where e.dept_id = d.id; 4）查询所有年龄大于40岁的员工, 及其归属的部门名称; 如果员工没有分配部门, 也需要展示出来(外连接) select e.*, d.name from emp e left join dept d on e.dept_id = d.id where e.age > 40 ; 5）查询所有员工的工资等级 -- 方式一 select e.* , s.grade , s.losal, s.hisal from emp e , salgrade s where e.salary >= s.losal and e.salary <= s.hisal; -- 方式二 select e.* , s.grade , s.losal, s.hisal from emp e , salgrade s where e.salary between s.losal and s.hisal; 6）查询 "研发部" 所有员工的信息及工资等级 select e.* , s.grade from emp e , dept d , salgrade s where e.dept_id = d.id and ( e.salary between s.losal and s.hisal ) and d.name = '研发部'; 7）查询 "研发部" 员工的平均工资 select avg(e.salary) from emp e, dept d where e.dept_id = d.id and d.name = '研发部'; 8)查询工资比 "张三" 高的员工信息。 select * from emp where salary > ( select salary from emp where name = '张三' ); 9）查询比平均薪资高的员工信息 select * from emp where salary > ( select avg(salary) from emp ); 10). 查询低于本部门平均工资的员工信息 select * from emp e2 where e2.salary < ( select avg(e1.salary) from emp e1 where e1.dept_id = e2.dept_id ); 11). 查询所有的部门信息, 并统计部门的员工人数 select d.id, d.name , ( select count(*) from emp e where e.dept_id = d.id ) '人数' from dept d; 12). 查询所有学生的选课情况, 展示出学生名称, 学号, 课程名称 select s.name , s.no , c.name from student s , student_course sc , course c where s.id = sc.studentid and sc.courseid = c.id ;

子主题

事务

概论

事务是一组操作的集合，它是一个不可分割的工作单位，事务会把所有的操作作为一个整体一起向系统提交或撤销操作请求，即这些操作要么同时成功，要么同时失败。

问题产生

张三给李四转账100，张三账户的钱减少100，而李四账户的钱要增加 100。这一组操作就必须在一个事务的范围内，要么都成功，要么都失败。

正常情况

转账这个操作, 需要分为以下这么三步来完成 , 三步完成之后, 张三减少100, 而李四增加100, 转账成功

异常情况

转账这个操作, 也是分为以下这么三步来完成 , 在执行第三步是报错了, 这样就导致张三减少100块钱, 而李四的金额没变, 这样就造成了数据的不一致, 就出现问题了。

事务操作

mysql事务失败原因

1. 不是所有引擎都支持事务不是所有的存储引擎都支持事务。例如MyISAM引擎不支持事务。可用SHOW ENGINES;命令查看引擎是否支持。InnoDB支持事务。 2. 事务未提交或回滚事务一旦开始，那么必须被提交或者回滚，否则所有操作都将被撤销。没有显式地提交或回滚事务，操作都会失效。用COMMIT和ROLLBACK命令来提交或回滚事务。 3.事务嵌套 MySQL不支持嵌套事务，在一个事务之内再次开启了一个事务，第二个事务不会生效。用SAVEPOINT和ROLLBACK TO SAVEPOINT命令来模拟嵌套事务。

方式一

查看事务提交方式

select @@autocommit;

默认为1自动提交

设置事务提交方式

set @@autocommit=0;

设置为0手动提交

提交事务

commit;

回滚事务

rollback;

方式二

开启事务（必须二选一）

begin;

start transaction;

执行代码

-- 1、张三账户减少1000 update account set money = money - 1000 where NAME = '张三'; -- 2、李四账户增加1000 代码报错了 update account set money = money + 1000 where NAME = '李四';

提交或回滚（必须二选一）

commit;

事件（事务，事情，任务）执行成功，提交事务

rollback;

事件（事务，事情，任务）执行失败，回滚事务

其他

如果在java代码上操作，给对应的代码添加注解。

子主题

四大特性

原子性（Atomicity）：事务是不可分割的最小操作单元，要么全部成功，要么全部失败。一致性（Consisstency）：事务完成时，必须使所有的数据都保持一致状态。隔离性（Isolation）：数据库系统提供的隔离机制，保证事务在不受外部并发操作影响的独立环境下运行。持久性（Durability）：事务一旦提交或回滚，它对数据库中的数据的改变就是永久的（物理修改）。

并发问题

脏读

一个事务读到另一个事务还没提交的数据

不可重复读

一个事务先后读取同一条数据，但两次读取得到的数据不同

幻读

一个事务按照条件查询时，没有对应的数据行，但是在插入数据时，又发现这个数据（id）已经存在

并发问题解决方案

查看事务

子主题

隔离级别

read uncommitted

不能解决任何问题

read committed

只能解决脏读

repeatable read

不能解决幻读（默认）

serialzable

可以解决所有问题，但是效率低

命令

查看隔离级别

select @@transaction_isolation

设置隔离级别

当前会话页面

set session transaction isolation level 隔离级别

全局设置

set global transaction isolation level 隔离级别

存储引擎

InnoDB引擎

子主题

高级

索引

子主题

变量

子主题

SQL优化

SQL注入

视图

概论

当字段过多的时候，以利用视图虚拟表去创建个新表暂存，也不影响实际的表内容，还能提高查询效率。

临时表

存储过程

参数

in

子主题

out

子主题

inout

子主题

注意，这里和if函数、case函数的使用有差异

选择

if

case

子主题

循环

while

子主题

repeat

子主题

loop

子主题

游标

cursor

条件存储

子主题

存储函数

子主题

触发器

子主题

锁

子主题

引擎

子主题

Mysql管理

运维

主从复制

子主题

分库分表

子主题

读写分离

子主题

约束

子主题

ZUD

合格品

出格品

不合格

等外

让步接收

超标

数据字典

正则表达式

主题

子主题