导航：

【黑马Java笔记+踩坑汇总】JavaSE+JavaWeb+SSM+SpringBoot+瑞吉外卖+SpringCloud/SpringCloudAlibaba+黑马旅游+谷粒商城

目录

一、概念

二、下载安装配置卸载

三、关系型数据库

四、SQL语句

4.1 概述

4.2 DDL数据定义语言

4.2.0 mysql自带数据库

4.2.1 常用语法

4.2.2 DDL查询表

4.2.3 DDL创建表

4.2.4 三类数据类型，数值、日期、字符串

4.2.5 DDL删除表

4.2.6 DDL修改表

4.3 Navicat的使用

4.4 数据操作语言DML

4.4.1 DML添加数据

4.4.2 修改数据

4.4.3 删除数据

4.5 数据查询语言DQL

4.5.1 查询的完整语法

4.5.2 创建练习查询的表

4.5.3 基础查询

4.5.4 条件查询（包括模糊查询）

4.5.5 排序查询

4.5.6 聚合函数

4.5.7 分组查询

4.5.8 分页查询

五、约束

5.1 概念

5.2 常用约束

5.3 增删约束

5.4 外键约束

5.4.1 概述 

5.4.2 练习

六、数据库设计

6.1 概念

6.2 表关系

6.2.1 一对多

6.2.2 多对多

6.2.3 一对一

七、多表查询

7.1 创建练习的表

7.2 连接查询

7.2.1 概念 

7.2.2 内连接查询

7.2.3 自连接

7.2.4 递归查询

7.2.5 外连接查询

7.3 子查询

7.4 多表查询练习题：

八、事务

8.1 概念

8.2 语法

8.3 事务的四大特征

一、概念

常用关系型数据库管理系统： 

二、下载安装配置卸载

第一步：去官网下载安装
第二步：先解压，然后在mysql下创建一个my.ini文件，更改my.ini文件里面的前两行安装目录，第二行加上\data，my.ini文件不能多一个符号或者少一个符号，在path（环境变量里面）加上mysql路径（;E:\mysql\mysql-8.0.25-winx64\bin）
(填写自己的mysql安装路径)
第三步：进入命令指示符（在bin目录下运行cmd）,
输入mysqld --initialize-insecure --user=mysql,初始化数据库，并设置默认root为空，初始化完成后，在mysql根目录中会自动生成data文件

mysqld --initialize-insecure --user=mysql

再输入mysqld -install,为windows安装mysql服务，默认服务名为mysql

mysqld -install

出现service successfully installed.表示配置完成
启动数据库

net start mysql

,

输入mysql -u root -p进行登录 ，其中mysql是安装目录bin下的mysql.exe与服务mysql间进行通信，-u后跟账户名root，-p后先不设置密码,不用输入密码直接回车

mysql -u root -p

出现mysql>配置完成
修改密码：

alter user user() identified by "密码";

mysql退出 mysql>quit;

exit;

输入net stop mysql关闭数据库

net stop mysql

视频教程(全网最详细)Mysql下载安装和配置方法(看了必成功)_哔哩哔哩_bilibili

卸载

cmd先停止服务

net stop mysql

再卸载服务

mysqld -remove mysql

最后删除目录和环境变量

三、关系型数据库

例如下面关系模型的二维表： 

数据库在mysql的data目录下。 

四、SQL语句

4.1 概述

 注意单行注释--后有空格。

4.2 DDL数据定义语言

4.2.0 mysql自带数据库

information_schema 是信息数据库。

其中保存着关于MySQL服务器所维护的所有其他数据库的信息。如数据库名，数据库的表，表栏的数据类型与访问权 限等。在INFORMATION_SCHEMA中，有数个只读表。它们实际上是视图，而不是基本表，因此，你将无法看到与之相关的任何文件。

mysql核心数据库，存储MySQL数据库里最核心的信息，例如权限、安全。

sys：系统数据库。

performance_schema主要用于收集数据库服务器性能参数（研究性能调优要用到）

4.2.1 常用语法

4.2.2 DDL查询表

查询当前数据库下所有表名称

SHOW TABLES;

查询表结构

DESC 表名称;        #desc是describe缩写，译为描述

4.2.3 DDL创建表

CREATE TABLE 表名 (字段名1  数据类型1,字段名2  数据类型2,…字段名n  数据类型n
);

 注意：字段名是列名。

最后一行末尾，不能加逗号

create table tb_user (id int,username varchar(20),    #sql语句中字符串是char和varchar类型password varchar(32)
);

4.2.4 三类数据类型，数值、日期、字符串

• 数值

  tinyint : 小整数型，占一个字节
  int ： 大整数类型，占四个字节eg ： age int
  double ： 浮点类型使用格式： 字段名 double(总长度,小数点后保留的位数)eg ： score double(5,2)   

• 日期

  date ： 日期值要带引号。只包含年月日eg ：birthday date 
  ​​​​​​​time :  时间值或持续时间
  year :  年分值
  datetime ： 混合日期和时间值。包含年月日时分秒

• 字符串。要带引号

  char ： 定长字符串。优点：存储性能高缺点：浪费空间eg ： name char(10)  如果存储的数据字符个数不足10个，也会占10个的空间，汉字占1个字符
  varchar ： 变长字符串。优点：节约空间缺点：存储性能底eg ： name varchar(10) 如果存储的数据字符个数不足10个，那就数据字符个数是几就占几个的空间    

4.2.5 DDL删除表

• 删除表

DROP TABLE 表名;

• 删除表时判断表是否存在

DROP TABLE IF EXISTS 表名;

4.2.6 DDL修改表

关键字rename，add，modify，change，drop

• 修改表名

ALTER TABLE 表名 RENAME TO 新的表名;
​
-- 将表名student修改为stu
alter table student rename to stu;

• 添加一列

ALTER TABLE 表名 ADD 列名 数据类型;
​
-- 给stu表添加一列address，该字段类型是varchar(50)
alter table stu add address varchar(50);

• 修改数据类型

ALTER TABLE 表名 MODIFY 列名 新数据类型;
​
-- 将stu表中的address字段的类型改为 char(50)
alter table stu modify address char(50);

• 修改列名和数据类型

ALTER TABLE 表名 CHANGE 列名 新列名 新数据类型;
​
-- 将stu表中的address字段名改为 addr，类型改为varchar(50)
alter table stu change address addr varchar(50);

• 删除列

ALTER TABLE 表名 DROP 列名;
​
-- 将stu表中的addr字段 删除
alter table stu drop addr;

• 删除外键约束

ALTER TABLE 表名 DROP FOREIGN KEY 外键名称;

4.3 Navicat的使用

正版下载链接：Navicat for MySQL | MySQL 数据库管理和开发工具

可在网上找教程，注意注册时候要管理员运行和断网。

主机那里填localhost或127.0.0.1，端口3306。

可以通过“美化sql”格式化代码：

4.4 数据操作语言​​​​​​​DML

4.4.1 DML添加数据

• 给指定列添加数据

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

• 给全部列添加数据

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

• 批量添加数据

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

注意添加字符串时候要加引号

-- 给指定列添加数据
INSERT INTO stu (id, NAME) VALUES (1, '张三');
-- 给所有列添加数据，列名的列表可以省略的
INSERT INTO stu (id,NAME,sex,birthday,score,email,tel,STATUS) VALUES (2,'李四','男','1999-11-11',88.88,'lisi@itcast.cn','13888888888',1);INSERT INTO stu VALUES (2,'李四','男','1999-11-11',88.88,'lisi@itcast.cn','13888888888',1);-- 批量添加数据
INSERT INTO stu VALUES (2,'李四','男','1999-11-11',88.88,'lisi@itcast.cn','13888888888',1),(2,'李四','男','1999-11-11',88.88,'lisi@itcast.cn','13888888888',1),(2,'李四','男','1999-11-11',88.88,'lisi@itcast.cn','13888888888',1);

4.4.2 修改数据

​​​​​​​​​​​​​​

UPDATE 表名 SET 列名1=值1,列名2=值2,… [WHERE 条件] ;

注意：

1. 修改语句中如果不加条件，则将所有记录都修改！

2. 像上面的语句中的中括号，表示在写sql语句中可以省略这部分

update stu set sex = '女' where name = '张三';

4.4.3 删除数据

• 删除数据

DELETE FROM 表名 [WHERE 条件] ;

• 练习

-- 删除张三记录
delete from stu where name = '张三';
​
-- 删除stu表中所有的数据
delete from stu;

注意：

1. 和上面一样，删除语句中如果不加条件，所有记录都将被删除，慎重！

2. 中括号，表示在写sql语句中可以省略的部分

4.5 数据查询语言DQL

4.5.1 查询的完整语法

查询最重要，最常用。

SELECT 字段列表
FROM 表名列表 
WHERE 条件列表
GROUP BY分组字段
HAVING分组后条件
ORDER BY排序字段
LIMIT分页限定
​​​​​​​

4.5.2 创建练习查询的表

-- 删除stu表
drop table if exists stu;-- 创建stu表
CREATE TABLE stu (id int, -- 编号name varchar(20), -- 姓名age int, -- 年龄sex varchar(5), -- 性别address varchar(100), -- 地址math double(5,2), -- 数学成绩english double(5,2), -- 英语成绩hire_date date -- 入学时间
);-- 添加数据
INSERT INTO stu(id,NAME,age,sex,address,math,english,hire_date) 
VALUES 
(1,'马运',55,'男','杭州',66,78,'1995-09-01'),
(2,'马花疼',45,'女','深圳',98,87,'1998-09-01'),
(3,'马斯克',55,'男','香港',56,77,'1999-09-02'),
(4,'柳白',20,'女','湖南',76,65,'1997-09-05'),
(5,'柳青',20,'男','湖南',86,NULL,'1998-09-01'),
(6,'刘德花',57,'男','香港',99,99,'1998-09-01'),
(7,'张学右',22,'女','香港',99,99,'1998-09-01'),
(8,'德玛西亚',18,'男','南京',56,65,'1994-09-02');

4.5.3 基础查询

• 示例

SELECT DISTINCT name AS '名字',age AS '年龄' FROM student;

• 查询多个字段

SELECT 字段列表 FROM 表名;
SELECT * FROM 表名; -- 查询所有数据

• 查询字段并去除重复记录

SELECT DISTINCT 字段列表 FROM 表名;

​​​​​​​

  ​​​​​​​去重后

• 起别名

AS: AS 也可以省略

4.5.4 条件查询（包括模糊查询）

SELECT 字段列表 FROM 表名 WHERE 条件列表;

举例： 

SELECT DISTINCT name AS '名字',age AS '年龄' FROM student WHERE age>23 && age<=40;

  模糊查询：

SELECT  * FROM student WHERE name LIKE '_iao%ming';

模糊查询替换符： 

下划线是必须一个字符，百分号替换0-多个字符

• 条件

注意：

1.  null不能和等号运算，要 IS NULL或 IS NOT NULL，而不是=null
2. SQL语句没有==，相等是=，没有赋值的概念。

4.5.5 排序查询

SELECT 字段列表 FROM 表名 ORDER BY 排序字段名1 [排序方式1],排序字段名2 [排序方式2] …;

• 查询学生信息，按照数学成绩降序排列，如果数学成绩一样，再按照英语成绩升序排列

  select * from stu order by math desc , english asc ;
  

上述语句中的排序方式有两种，分别是：

• ASC ： 升序排列 （默认值）ascending  /əˈsendɪŋ/ 

• DESC ： 降序排列，descending /dɪˈsendɪŋ/ 

注意：如果有多个排序条件，当前边的条件值一样时，才会根据第二条件进行排序

4.5.6 聚合函数

SELECT 聚合函数名(列名) FROM 表;

示例：

select count(id) from stu;    #统计id字段非null的记录数量
select count(*) from stu;# 统计“存在非null字段”的记录数量，* 表示所有字段数据，只要某行有一个非空数据，就会被统计在内

 聚合函数：

注意：null 值不参与所有聚合函数运算

4.5.7 分组查询

常常和聚合函数一起用。 

SELECT 字段列表 FROM 表名 [WHERE 分组前条件限定] GROUP BY 分组字段名 [HAVING 分组后条件过滤];

注意：分组之后，查询的字段为聚合函数和分组字段，查询其他字段无任何意义

练习：

查询男同学和女同学各自的数学平均分

select sex, avg(math) from stu group by sex;

查询男同学和女同学各自的数学平均分，以及各自人数，要求：分数低于70分的不参与分组，分组之后人数大于2个的

select sex, avg(math),count(*) from stu where math > 70 group by sex having count(*)  > 2;

where 和 having 区别：

• 执行时机不一样：where 是分组之前进行限定，不满足where条件，则不参与分组，而having是分组之后对结果进行过滤。

• 可判断的条件不一样：where 不能对聚合函数进行判断，having 可以。执行顺序where>聚合函数>having，不可能判断后面执行的条件。

4.5.8 分页查询

SELECT 字段列表 FROM 表名 LIMIT  起始索引 , 查询条目数;

练习：

起始索引 = (当前页码 - 1) * 每页显示的条数

• 从0开始查询，查询3条数据

  select * from stu limit 0 , 3;

• 每页显示3条数据，查询第1页数据

  select * from stu limit 0 , 3;

五、约束

5.1 概念

• 约束是作用于表中列上的规则，用于限制加入表的数据

  例如：我们可以给id列加约束，让其值不能重复，不能为null值。

• 添加约束可以在添加数据的时候就限制不正确的数据。例如把年龄是3000，数学成绩是-5分这样无效的数据限制掉，继而保障数据的完整性。

5.2 常用约束

5.3 增删约束

 示例：

5.4 外键约束

5.4.1 概述 

-- 创建表时添加外键约束
CREATE TABLE 表名(列名 数据类型,…[CONSTRAINT] [外键取名名称] FOREIGN KEY(外键列名) REFERENCES 主表(主表列名) 
); -- 创建表时添加外键约束，constraint译作限制，束缚；references译作关联，参考，提及
create table 表名(列名 数据类型,…[constraint] [外键取名名称] foreign key(外键列名) references 主表(主表列名) 
); 

-- 建完表后添加外键约束
ALTER TABLE 表名 ADD CONSTRAINT 外键名称 FOREIGN KEY (外键字段名称) REFERENCES 主表名称(主表列名称);-- 建完表后添加外键约束
alter table 表名 add constraint 外键名称 foreign key (外键字段名称) references 主表名称(主表列名称);

• 删除外键约束

ALTER TABLE 表名 DROP FOREIGN KEY 外键名称;

5.4.2 练习

-- 删除表
DROP TABLE IF EXISTS emp;
DROP TABLE IF EXISTS dept;-- 部门表
CREATE TABLE dept(id int primary key auto_increment,dep_name varchar(20),addr varchar(20)
);
-- 员工表 
CREATE TABLE emp(id int primary key auto_increment,name varchar(20),age int,dep_id int,-- 添加外键 dep_id,关联 dept 表的id主键CONSTRAINT fk_emp_dept FOREIGN KEY(dep_id) REFERENCES dept(id)	
);

添加数据

-- 添加 2 个部门
insert into dept(dep_name,addr) values
('研发部','广州'),('销售部', '深圳');
​
-- 添加员工,dep_id 表示员工所在的部门
INSERT INTO emp (NAME, age, dep_id) VALUES 
('张三', 20, 1),
('李四', 20, 1),
('王五', 20, 1),
('赵六', 20, 2),
('孙七', 22, 2),
('周八', 18, 2);

此时删除 研发部 这条数据，会发现无法删除。

删除外键

alter table emp drop FOREIGN key fk_emp_dept;

重新添加外键

alter table emp add CONSTRAINT fk_emp_dept FOREIGN key(dep_id) REFERENCES dept(id);

右键逆向到表模型，可以查看关系:

六、数据库设计

6.1 概念

• 软件的研发步骤

• 数据库设计概念

  • 设计方向：有哪些表？表里有哪些字段？表和表之间有什么关系？

  • 数据库设计就是根据业务系统的具体需求，结合我们所选用的DBMS，为这个业务系统构造出最优的数据存储模型。

  • 建立数据库中的表结构以及表与表之间的关联关系的过程。

• 数据库设计的步骤

  • 需求分析（数据是什么? 数据具有哪些属性? 数据与属性的特点是什么）

  • 逻辑分析（通过ER图对数据库进行逻辑建模，不需要考虑我们所选用的数据库管理系统）

    如下图就是ER(Entity/Relation)图：​​​​​​​

• • 物理设计（根据数据库自身的特点把逻辑设计转换为物理设计）

  • 维护设计（1.对新的需求进行建表；2.表优化）

6.2 表关系

6.2.1 一对多

如：部门 和 员工

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

实现方式

在多的一方建立外键，指向一的一方的主键

 建表语句：

-- 删除表
DROP TABLE IF EXISTS tb_emp;
DROP TABLE IF EXISTS tb_dept;-- 部门表
CREATE TABLE tb_dept(id int primary key auto_increment,dep_name varchar(20),addr varchar(20)
);
-- 员工表 
CREATE TABLE tb_emp(id int primary key auto_increment,name varchar(20),age int,dep_id int,-- 添加外键 dep_id,关联 dept 表的id主键CONSTRAINT fk_emp_dept FOREIGN KEY(dep_id) REFERENCES tb_dept(id)	
);

6.2.2 多对多

如：商品 和 订单

一个商品对应多个订单，一个订单包含多个商品。

实现方式：

建立第三张中间表，中间表至少包含两个外键，分别关联两方主键。

案例：

建表语句：

-- 删除表
DROP TABLE IF EXISTS tb_order_goods;
DROP TABLE IF EXISTS tb_order;
DROP TABLE IF EXISTS tb_goods;-- 订单表
CREATE TABLE tb_order(id int primary key auto_increment,payment double(10,2),payment_type TINYINT,status TINYINT
);-- 商品表
CREATE TABLE tb_goods(id int primary key auto_increment,title varchar(100),price double(10,2)
);-- 订单商品中间表
CREATE TABLE tb_order_goods(id int primary key auto_increment,order_id int,goods_id int,count int
);-- 建完表后，添加外键
alter table tb_order_goods add CONSTRAINT fk_order_id FOREIGN key(order_id) REFERENCES tb_order(id);
alter table tb_order_goods add CONSTRAINT fk_goods_id FOREIGN key(goods_id) REFERENCES tb_goods(id);

 表结构模型：

6.2.3 一对一

如：用户 和 用户详情

一对一关系多用于表拆分，将一个实体中经常使用的字段放一张表，不经常使用的字段放另一张表，用于提升查询性能。

实现方式：

在任意一方加入外键，关联另一方主键，并且设置外键为唯一(UNIQUE)

案例：

 而在真正使用过程中发现 id、photo、nickname、age、gender 字段比较常用，此时就可以将这张表查分成两张表：

建表语句如下：

create table tb_user_desc (id int primary key auto_increment,city varchar(20),edu varchar(10),income int,status char(2),des varchar(100)
);
​
create table tb_user (id int primary key auto_increment,photo varchar(100),nickname varchar(50),age int,gender char(1),desc_id int unique,-- 添加外键CONSTRAINT fk_user_desc FOREIGN KEY(desc_id) REFERENCES tb_user_desc(id)    
);

查看表结构模型图：

七、多表查询

7.1 创建练习的表

DROP TABLE IF EXISTS emp;
DROP TABLE IF EXISTS dept;# 创建部门表CREATE TABLE dept(did INT PRIMARY KEY AUTO_INCREMENT,dname VARCHAR(20));# 创建员工表CREATE TABLE emp (id INT PRIMARY KEY AUTO_INCREMENT,NAME VARCHAR(10),gender CHAR(1), -- 性别salary DOUBLE, -- 工资join_date DATE, -- 入职日期dep_id INT,FOREIGN KEY (dep_id) REFERENCES dept(did) -- 外键，关联部门表(部门表的主键));-- 添加部门数据INSERT INTO dept (dNAME) VALUES ('研发部'),('市场部'),('财务部'),('销售部');-- 添加员工数据INSERT INTO emp(NAME,gender,salary,join_date,dep_id) VALUES('孙悟空','男',7200,'2013-02-24',1),('猪八戒','男',3600,'2010-12-02',2),('唐僧','男',9000,'2008-08-08',2),('白骨精','女',5000,'2015-10-07',3),('蜘蛛精','女',4500,'2011-03-14',1),('小白龙','男',2500,'2011-02-14',null);	

员工表：

部门表：

7.2 连接查询

7.2.1 概念 

• 内连接查询 ：相当于查询AB交集数据

• 外连接查询

  • 左外连接查询 ：相当于查询A表所有数据和交集部门数据

  • 右外连接查询 ： 相当于查询B表所有数据和交集部分数据

7.2.2 内连接查询

相当于查询AB交集数据。

语句：

-- 隐式内连接。没有JOIN关键字，条件使用WHERE指定。书写简单，多表时效率低
SELECT 字段列表 FROM 表1,表2… WHERE 条件;-- 显示内连接。使用INNER JOIN ... ON语句, 可以省略INNER。书写复杂，多表时效率高
SELECT 字段列表 FROM 表1 [INNER] JOIN 表2 ON 条件;

隐式连接好理解好书写，语法简单，担心的点较少。

但是显式连接可以减少字段的扫描，有更快的执行速度。这种速度优势在3张或更多表连接时比较明显 

 示例：

#隐式内连接
SELECTemp. NAME,emp.gender,dept.dname
FROMemp,dept
WHEREemp.dep_id = dept.did;

#显式内连接
select * from emp inner join dept on emp.dep_id = dept.did;

员工表：

部门表：

7.2.3 自连接

自连接是一种特殊的内连接，它是指相互连接的表在物理上为同一张表，但可以在逻辑上分为两张表。

注意：自连接查询的列名必须是“表名.*”，而不是直接写“*”

案例：

要求检索出学号为20210的学生的同班同学的信息

SELECT stu.*        #一定注意是stu.*，不是*FROM stu JOIN stu AS stu1 ON stu.grade= stu1.gradeWHERE stu1.id='20210'

7.2.4 递归查询

with语法：

   WITH [RECURSIVE]cte_name [(col_name [, col_name] ...)] AS (subquery)[, cte_name [(col_name [, col_name] ...)] AS (subquery)] ...

recurslve译为递归。

with：在mysql中被称为公共表达式,可以作为一个临时表然后在其他结构中调用.如果是自身调用那么就是后面讲的递归.

cte_name :公共表达式的名称,可以理解为表名,用来表示as后面跟着的子查询

col_name :公共表达式包含的列名,可以写也可以不写

例子：使用MySQL临时表遍历1~5

with RECURSIVE t1  AS    #这里t1函数名，也是临时表的表名
(SELECT 1 as n        #n是列的别名，1是初始记录UNION ALL        #把递归结果（2,3,4,5）合并到t1表中SELECT n + 1 FROM t1 WHERE n < 5    #n+1是参数，t1是函数名，n<5是遍历终止条件
)
SELECT * FROM t1;        #正常查询t1这个临时表，相当于调用这个函数。

 ​​

说明：

t1 相当于一个表名

select 1 相当于这个表的初始值，这里使用UNION ALL 不断将每次递归得到的数据加入到表中。

n<5为递归执行的条件，当n>=5时结束递归调用。

案例，递归查询课程多级分类：

with recursive t1 as (        #t1是函数名、临时表名
select * from  course_category where  id= '1'   #初始记录，也就是根节点
union all         #把递归结果合并到t1表中select t2.* from course_category as t2 inner join t1 on t1.id = t2.parentid    #递归，用分类表t和临时表t1内连接查询
)select *  from t1 order by t1.id, t1.orderby    #查t1表，相当于调用这个函数。

​

mysql递归特点，对比Java递归的优势

mysql递归次数限制：

mysql为了避免无限递归默认递归次数为1000，可以通过设置cte_max_recursion_depth参数增加递归深度，还可以通过max_execution_time限制执行时间，超过此时间也会终止递归操作。

对比Java递归的优势：

mysql递归相当于在存储过程中执行若干次sql语句，java程序仅与数据库建立一次链接执行递归操作。相比之下，Java递归性能就很差，每次递归都会建立一次数据库连接。

7.2.5 外连接查询

语句：

-- 左外连接
SELECT 字段列表 FROM 表1 LEFT [OUTER] JOIN 表2 ON 条件;-- 右外连接
SELECT 字段列表 FROM 表1 RIGHT [OUTER] JOIN 表2 ON 条件;

一般都用左外连接，因为右外连接可用左外连接实现，可读性更好。 

示例：

查询emp表所有数据和对应的部门信息（左外连接）

select * from emp left join dept on emp.dep_id = dept.did;

​

查询dept表所有数据和对应的员工信息（右外连接）

select * from emp right join dept on emp.dep_id = dept.did;

 ​

7.3 子查询

查询中嵌套查询，称嵌套查询为子查询。

注意：子语句没有分号。

子查询根据查询结果不同，作用不同，可分为：

• 子查询语句结果是单行单列，子查询语句作为条件值，使用 = != > < 等进行条件判断

​

 示例：

查询比猪八戒薪水高的员工：

SELECT * FROM emp WHERE salary >(SELECT salary FROM emp WHERE name='猪八戒');

• 子查询语句结果是多行单列，子查询语句作为条件值，使用 in 等关键字进行条件判断

​

 示例：

查询 '财务部' 和 '市场部' 所有的员工信息：

SELECT * FROM emp WHERE dep_id in (SELECT did FROM dept WHERE dname IN ('财务部','市场部'));

• 子查询语句结果是多行多列，子查询语句作为虚拟表

​

示例：

 查询入职日期是 '2011-11-11' 之后的员工信息和部门信息：

select * from (select * from emp where join_date > '2011-11-11' ) AS t1, dept where t1.dep_id = dept.did;

7.4 多表查询练习题：

DROP TABLE IF EXISTS emp;
DROP TABLE IF EXISTS dept;
DROP TABLE IF EXISTS job;
DROP TABLE IF EXISTS salarygrade;-- 部门表
CREATE TABLE dept (did INT PRIMARY KEY PRIMARY KEY, -- 部门iddname VARCHAR(50), -- 部门名称loc VARCHAR(50) -- 部门所在地
);-- 职务表，职务名称，职务描述
CREATE TABLE job (id INT PRIMARY KEY,jname VARCHAR(20),description VARCHAR(50)
);-- 员工表
CREATE TABLE emp (id INT PRIMARY KEY, -- 员工idename VARCHAR(50), -- 员工姓名job_id INT, -- 职务idmgr INT , -- 上级领导joindate DATE, -- 入职日期salary DECIMAL(7,2), -- 工资bonus DECIMAL(7,2), -- 奖金dept_id INT, -- 所在部门编号CONSTRAINT emp_jobid_ref_job_id_fk FOREIGN KEY(job_id) REFERENCES job(id),CONSTRAINT emp_deptid_ref_dept_id_fk FOREIGN KEY(dept_id) REFERENCES dept(did)
);
-- 工资等级表
CREATE TABLE salarygrade (grade INT PRIMARY KEY,   -- 级别losalary INT,  -- 最低工资hisalary INT -- 最高工资
);-- 添加4个部门
INSERT INTO dept(did,dname,loc) VALUES 
(10,'教研部','北京'),
(20,'学工部','上海'),
(30,'销售部','广州'),
(40,'财务部','深圳');-- 添加4个职务
INSERT INTO job (id, jname, description) VALUES
(1, '董事长', '管理整个公司，接单'),
(2, '经理', '管理部门员工'),
(3, '销售员', '向客人推销产品'),
(4, '文员', '使用办公软件');-- 添加员工
INSERT INTO emp(id,ename,job_id,mgr,joindate,salary,bonus,dept_id) VALUES 
(1001,'孙悟空',4,1004,'2000-12-17','8000.00',NULL,20),
(1002,'卢俊义',3,1006,'2001-02-20','16000.00','3000.00',30),
(1003,'林冲',3,1006,'2001-02-22','12500.00','5000.00',30),
(1004,'唐僧',2,1009,'2001-04-02','29750.00',NULL,20),
(1005,'李逵',4,1006,'2001-09-28','12500.00','14000.00',30),
(1006,'宋江',2,1009,'2001-05-01','28500.00',NULL,30),
(1007,'刘备',2,1009,'2001-09-01','24500.00',NULL,10),
(1008,'猪八戒',4,1004,'2007-04-19','30000.00',NULL,20),
(1009,'罗贯中',1,NULL,'2001-11-17','50000.00',NULL,10),
(1010,'吴用',3,1006,'2001-09-08','15000.00','0.00',30),
(1011,'沙僧',4,1004,'2007-05-23','11000.00',NULL,20),
(1012,'李逵',4,1006,'2001-12-03','9500.00',NULL,30),
(1013,'小白龙',4,1004,'2001-12-03','30000.00',NULL,20),
(1014,'关羽',4,1007,'2002-01-23','13000.00',NULL,10);-- 添加5个工资等级
INSERT INTO salarygrade(grade,losalary,hisalary) VALUES 
(1,7000,12000),
(2,12010,14000),
(3,14010,20000),
(4,20010,30000),
(5,30010,99990);

需求

1. 查询所有员工信息。查询员工编号，员工姓名，工资，职务名称，职务描述

   /*分析：1. 员工编号，员工姓名，工资 信息在emp 员工表中2. 职务名称，职务描述 信息在 job 职务表中3. job 职务表 和 emp 员工表 是 一对多的关系 emp.job_id = job.id
   */
   -- 方式一 ：隐式内连接
   SELECTemp.id,emp.ename,emp.salary,job.jname,job.description
   FROMemp,job
   WHEREemp.job_id = job.id;
   ​
   -- 方式二 ：显式内连接
   SELECTemp.id,emp.ename,emp.salary,job.jname,job.description
   FROMemp
   INNER JOIN job ON emp.job_id = job.id;

2. 查询员工编号，员工姓名，工资，职务名称，职务描述，部门名称，部门位置

   /*分析：1. 员工编号，员工姓名，工资 信息在emp 员工表中2. 职务名称，职务描述 信息在 job 职务表中3. job 职务表 和 emp 员工表 是 一对多的关系 emp.job_id = job.id
   ​4. 部门名称，部门位置 来自于 部门表 dept5. dept 和 emp 一对多关系 dept.id = emp.dept_id
   */
   ​
   -- 方式一 ：隐式内连接
   SELECTemp.id,emp.ename,emp.salary,job.jname,job.description,dept.dname,dept.loc
   FROMemp,job,dept
   WHEREemp.job_id = job.idand dept.id = emp.dept_id
   ;
   ​
   -- 方式二 ：显式内连接
   SELECTemp.id,emp.ename,emp.salary,job.jname,job.description,dept.dname,dept.loc
   FROMemp
   INNER JOIN job ON emp.job_id = job.id
   INNER JOIN dept ON dept.id = emp.dept_id

3. 查询员工姓名，工资，工资等级

   /*分析：1. 员工姓名，工资 信息在emp 员工表中2. 工资等级 信息在 salarygrade 工资等级表中3. emp.salary >= salarygrade.losalary  and emp.salary <= salarygrade.hisalary
   */
   SELECTemp.ename,emp.salary,t2.*
   FROMemp,salarygrade t2
   WHEREemp.salary >= t2.losalary
   AND emp.salary <= t2.hisalary

4. 查询员工姓名，工资，职务名称，职务描述，部门名称，部门位置，工资等级

   /*分析：1. 员工编号，员工姓名，工资 信息在emp 员工表中2. 职务名称，职务描述 信息在 job 职务表中3. job 职务表 和 emp 员工表 是 一对多的关系 emp.job_id = job.id
   ​4. 部门名称，部门位置 来自于 部门表 dept5. dept 和 emp 一对多关系 dept.id = emp.dept_id6. 工资等级 信息在 salarygrade 工资等级表中7. emp.salary >= salarygrade.losalary  and emp.salary <= salarygrade.hisalary
   */
   SELECTemp.id,emp.ename,emp.salary,job.jname,job.description,dept.dname,dept.loc,t2.grade
   FROMemp
   INNER JOIN job ON emp.job_id = job.id
   INNER JOIN dept ON dept.id = emp.dept_id
   INNER JOIN salarygrade t2 ON emp.salary BETWEEN t2.losalary and t2.hisalary;

八、事务

8.1 概念

数据库的事务（Transaction）是一种机制、一个操作序列，包含了一组数据库操作命令。

事务把所有的命令作为一个整体一起向系统提交或撤销操作请求，即这一组数据库命令要么同时成功，要么同时失败。

事务是一个不可分割的工作逻辑单元。

示例：

​

 在转账前开启事务，如果出现了异常回滚事务，三步正常执行就提交事务，这样就可以完美解决问题。

8.2 语法

• 开启事务

  START TRANSACTION;        --transaction译为事务，业务，交易
  或者  
  BEGIN;

• 提交事务

  commit;

示例：

-- 开启事务
BEGIN;
-- 转账操作
-- 1. 查询李四账户金额是否大于500-- 2. 李四账户 -500
UPDATE account set money = money - 500 where name = '李四';出现异常了...  -- 此处不是注释，在整体执行时会出问题，后面的sql则不执行
-- 3. 张三账户 +500
UPDATE account set money = money + 500 where name = '张三';-- 提交事务
COMMIT;-- 回滚事务
ROLLBACK;

上面sql中的执行成功进选择执行提交事务，而出现问题则执行回滚事务的语句。以后我们肯定不可能这样操作，而是在java中进行操作，在java中可以抓取异常，没出现异常提交事务，出现异常回滚事务。  

8.3 事务的四大特征

• 原子性（Atomicity）: 事务是不可分割的最小操作单位，要么同时成功，要么同时失败

• 一致性（Consistency） :事务完成时，必须使所有的数据都保持一致状态

• 隔离性（Isolation） :多个事务之间，操作的可见性

• 持久性（Durability） :事务一旦提交或回滚，它对数据库中的数据的改变就是永久的

说明：

mysql中事务是自动提交的。

也就是说我们不添加事务执行sql语句，语句执行完毕会自动的提交事务。

可以通过下面语句查询默认提交方式：

SELECT @@autocommit;

查询到的结果是1 则表示自动提交，结果是0表示手动提交。当然也可以通过下面语句修改提交方式

set @@autocommit = 0;