关于javascript:mySQL

第三章：多表联查

1. 束缚：

1.1 概念：

• 什么是束缚呢？束缚就是不能够有反复值，也不能够有空值
• (官方语言：束缚是作用于表中列上的规定，用于限度退出表的数据，束缚的存在保障了数据库中数据的正确性、有效性和完整性)
• 例

增加束缚能够在增加数据的时候就限度不正确的数据，年龄是 3000，数学问题是 - 5 分这样有效的数据，继而保障数据的完整性

1.2 束缚分类

束缚有主键束缚，非空束缚，惟一束缚，默认束缚，查看束缚

1.2.1 主键束缚：关键字（PRIMARY KEY）

主键束缚是一行数据中的惟一的数据，要求非空，惟一，就是说不能是空值也不能反复

在 mysql 中勾选主键束缚会默认勾选惟一束缚

-- 创立表时增加束缚
CREATE TABLE 表名(列名 数据类型 PRIMARY KEY)
-- 例
CREATE TABLE user(id int PRIMARY KEY)
-- 建表后增加束缚
ALTER TABLE 表名 ADD PRIMARY KEY(字段名)
-- 删除主键束缚
ALTER TABLE 表名 DROP PRIMARY KEY(字段名)

1.2.3 惟一束缚：关键字(UNIQUE)

惟一束缚是不能反复，例表中的值要不同

-- 增加表时增加惟一束缚
CREATE TABLE  表名(列名 数据类型 UNIQUE)
-- 例
CREATE TABLE user(id int UNIQUE)
-- 建表后增加束缚
ALTER TABLE 表名 MODIFY 字段名 数据类型 UNIQUE
-- 例
ALTER TABLE user MODIFY id int UNIQUE
-- 删除主键束缚
ALTER TABLE 表名 DROP INDEX 字段名
-- 例
ALTER TABLE user DROP INDEX id

1.2.4 非空束缚：关键字(not null)

非空束缚要保障列表中的值不能是空值

-- 增加表时增加非空束缚
CREATE TABLE 表名(列名 数据类型 not null)
-- 例
CREATE TABLE user (id int not null)
-- 增加表后增加束缚
ALTER TABLE MODIFY 字段名 数据类型

1.2.5 默认束缚：关键字(DEFAULT)

保留数据时，未指定值则采纳默认值

-- 创立表时增加查看束缚
CREATE TABLE 表名(列名 数据类型 DEFAULT)
-- 例
CREATE TABLE user(id int DEFAULT)
-- 增加表后增加束缚
ALTER TABLE 表名 ALTER 列名 SET DEFAULT 默认值;

1.2.6 查看束缚：关键字(CHECK)

保障列表中的值满足任意一条条件

留神留神：MySQL 不反对查看束缚。

这样是不是就没方法保障年龄在指定的范畴内了？从数据库层面不能保障，当前能够在 java 代码中进行限度，一样也能够实现要求。

1.2.7 外键束缚：

外键用来让两个表的数据之间建设链接，保证数据的一致性和完整性。

然而外键束缚必须要求其余表要有主键，惟一，非空束缚

看如何了解下面的概念呢？如下图有两张表，员工表和部门表：

员工表中的 dep_id 字段是部门表的 id 字段关联，也就是说 1 号学生张三属于 1 号部门研发部的员工。你如果想间接删除部门表则会报错以下，而咱们下面说的两张表的关系只是咱们认为它们有关系，此时须要通过外键让这两张表产生数据库层面的关系，这样你要删除部门表中的 1 号部门的数据将无奈删除。要想删除部门表中的 1 号部门的数据先删除 emp 员工表的数据，能力删除 1 号部门

你如果想间接删除部门表则会报错以下

-- 1 语法
-- 创立表时外键束缚
CREATE TABLE EMP (
列名 数据类型，[CONSTRAINT] [外键名称] FOREIGN KEY(外键列名) REFERENCES 主表(主表列名) 
)
-- 建表后增加束缚
ALTER TABLE 表名 ADD CONSTRAINT 外键名称 FOREIGN KEY (外键主动名称) REFERENCES 主表名称(列表名称)
-- 删除外键束缚
ALTER TABLE 表名 DROP FOREIGN KEY 外键名称;

1.2.8 束缚练习

依据需要，为表增加适合的束缚

-- 员工表
CREATE TABLE emp (
    id INT,  -- 员工 id，主键且自增长
    ename VARCHAR(50), -- 员工姓名，非空且惟一
    joindate DATE,  -- 入职日期，非空
    salary DOUBLE(7,2),  -- 工资，非空
    bonus DOUBLE(7,2)  -- 奖金，如果没有将近默认为 0
);

下面肯定给出了具体的要求，咱们能够依据要求创立这张表，并为每一列增加对应的束缚。建表语句如下：

DROP TABLE IF EXISTS emp;

-- 员工表
CREATE TABLE emp (
  id INT PRIMARY KEY, -- 员工 id，主键且自增长
  ename VARCHAR(50) NOT NULL UNIQUE, -- 员工姓名，非空并且惟一
  joindate DATE NOT NULL , -- 入职日期，非空
  salary DOUBLE(7,2) NOT NULL , -- 工资，非空
  bonus DOUBLE(7,2) DEFAULT 0 -- 奖金，如果没有奖金默认为 0
);

通过下面语句能够创立带有束缚的 emp 表，束缚能不能发挥作用呢。接下来咱们一一进行验证，先增加一条没有问题的数据

INSERT INTO emp(id,ename,joindate,salary,bonus) values(1,'张三','1999-11-11',8800,5000);

• 验证主键束缚，非空且惟一

INSERT INTO emp(id,ename,joindate,salary,bonus) values(null,'张三','1999-11-11',8800,5000);

执行后果如下：

<img src=”D:/ 百度网盘 /day02-MySQL 高级 /day02-MySQL 高级 /ppt/assets/image-20210724114548170.png” alt=”image-20210724114548170″ style=”zoom:80%;” />

从下面的后果能够看到，字段 id 不能为 null。那咱们从新增加一条数据，如下：

INSERT INTO emp(id,ename,joindate,salary,bonus) values(1,'张三','1999-11-11',8800,5000);

执行后果如下：

<img src=”D:/ 百度网盘 /day02-MySQL 高级 /day02-MySQL 高级 /ppt/assets/image-20210724114805350.png” alt=”image-20210724114805350″ style=”zoom:80%;” />

从下面后果能够看到，1 这个值反复了。所以主键束缚是用来限度数据非空且惟一的。那咱们再增加一条符合要求的数据

INSERT INTO emp(id,ename,joindate,salary,bonus) values(2,'李四','1999-11-11',8800,5000);

执行后果如下：

<img src=”D:/ 百度网盘 /day02-MySQL 高级 /day02-MySQL 高级 /ppt/assets/image-20210724115024106.png” alt=”image-20210724115024106″ style=”zoom:80%;” />

• 验证非空束缚

INSERT INTO emp(id,ename,joindate,salary,bonus) values(3,null,'1999-11-11',8800,5000);

执行后果如下：

<img src=”D:/ 百度网盘 /day02-MySQL 高级 /day02-MySQL 高级 /ppt/assets/image-20210724115149415.png” alt=”image-20210724115149415″ style=”zoom:80%;” />

从下面后果能够看到，ename 字段的非空束缚失效了。

• 验证惟一束缚

INSERT INTO emp(id,ename,joindate,salary,bonus) values(3,'李四','1999-11-11',8800,5000);

执行后果如下：

<img src=”D:/ 百度网盘 /day02-MySQL 高级 /day02-MySQL 高级 /ppt/assets/image-20210724115336916.png” alt=”image-20210724115336916″ style=”zoom:80%;” />

从下面后果能够看到，ename 字段的惟一束缚失效了。

• 验证默认束缚

INSERT INTO emp(id,ename,joindate,salary) values(3,'王五','1999-11-11',8800);

执行完下面语句后查问表中数据，如下图能够看到王五这条数据的 bonus 列就有了默认值 0。

<img src=”D:/ 百度网盘 /day02-MySQL 高级 /day02-MySQL 高级 /ppt/assets/image-20210724115547951.png” alt=”image-20210724115547951″ style=”zoom:80%;” />

== 留神：默认束缚只有在不给值时才会采纳默认值。如果给了 null，那值就是 null 值。==

如下：

INSERT INTO emp(id,ename,joindate,salary,bonus) values(4,'赵六','1999-11-11',8800,null);

执行完下面语句后查问表中数据，如下图能够看到赵六这条数据的 bonus 列的值是 null。

<img src=”D:/ 百度网盘 /day02-MySQL 高级 /day02-MySQL 高级 /ppt/assets/image-20210724115826516.png” alt=”image-20210724115826516″ style=”zoom:80%;” />

• 验证主动增长：auto_increment 当列是数字类型 并且惟一束缚

从新创立 emp 表，并给 id 列增加主动增长

-- 员工表
CREATE TABLE emp (
  id INT PRIMARY KEY auto_increment, -- 员工 id，主键且自增长
  ename VARCHAR(50) NOT NULL UNIQUE, -- 员工姓名，非空并且惟一
  joindate DATE NOT NULL , -- 入职日期，非空
  salary DOUBLE(7,2) NOT NULL , -- 工资，非空
  bonus DOUBLE(7,2) DEFAULT 0 -- 奖金，如果没有奖金默认为 0
);

接下来给 emp 增加数据，别离验证不给 id 列增加值以及给 id 列增加 null 值，id 列的值会不会主动增长：

INSERT INTO emp(ename,joindate,salary,bonus) values('赵六','1999-11-11',8800,null);
INSERT INTO emp(id,ename,joindate,salary,bonus) values(null,'赵六 2','1999-11-11',8800,null);
INSERT INTO emp(id,ename,joindate,salary,bonus) values(null,'赵六 3','1999-11-11',8800,null);

执行后果如下

id 主动增长了

2. 多表联查

什么是多表联查呢？

多表查问顾名思义就是从多张表中一次性的查问出咱们想要的数据。先将一下表关系

2.1. 表关系（一对多）：

• 如：部门和员工
• 一个部门对应多个员工，看下图

• 实现形式

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

• 案例

  我还是用 tb_emp 表和 tb_dept 表举例

通过剖析发现，员工表属于多的一方，而部门表属于一的一方，此时咱们会在员工表中增加一列（dep_id），指向于部门表的主键（id）：

建表语句如下：

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 
    emp_name varchar(20)
    age varchar(20)
-- 增加外键 dept_id, 关联 dept 表的 id 主键
CONSTRAINT fk_emp_dept FOREIGN KEY(dept_id) REFERENCES tb_dept(id)
)

查看表构造模型图：

2.2 表关系（多对多）：

• 什么是多对多呢：

比方一个商品对应多个订单，一个订单蕴含多个商品

• 实现形式

建设第三张表，两头表必须蕴含两个表的主键，别离关联两方主键

• 案例

以订单表和商品表为例

订单表和商品表都属于多的一方，此时须要创立一个两头表，在两头表中增加订单表的外键和商品表的外键指向两张表的主键：

-- 删除表
DROP TABLE IF EXISTS tb_order_goods;
DROP TABLE IF EXISTS tb_ordr
DROP TABLE IF EXISTS tb_goods
-- 订单表
CREATL TABLE tb_order(
id  int PRIMARY key AUTO_INCREMENT
    payment double(10,2)
    payment_type TINYINT
    status TINYINT
)
-- 商品表
CREATE TABLE tb_order_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);

看表构造模型图：

2.3 表关系(一对一)：

• 什么是一对一呢：

一对一就是将一个表拆分开来，将一个实体中常常应用的字段放一张表，不常常应用的字段放另一张表，用于晋升查问性能

• 实现办法：

在任意一方退出外键，关联另一方主键，并且设置外键为惟一(UNIQUE)

• 案例：

以用户表举例

看下面，咱们在应用的过程中只用失去 id，photo，nickname，age，gendercity，此时咱们能够将这一张表分成两张表，来晋升查问性能

-- 建表语句
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)    
);

查看表构造：

2.4 多表联查：

先创立一些数据

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);    

2.4.1：笛卡尔积

• 什么是笛卡尔积呢：

看以下后果

创立好了之后先做个练习：执行上面的多表查问语句

select * from emp , dept;  -- 从 emp 和 dept 表中查问所有的字段数据

看后果

看完后果咱们得悉孙悟空他跟部门表所有得部门都有关系，如果孙悟空整个员工属于 1 号部门得，但他还关联者 2，3，4 部门，得只这都是一些无线得数据，这就叫做笛卡尔积

• 笛卡尔积又是怎么来得呢：

笛卡尔积是穿插连贯得来得

看图

• 怎么防止出现笛卡尔积呢？

咱们要通过限度员工表中的 dep_id 字段的值和部门表 did 字段的值相等来打消这些有效的数据，

select * from emp , dept where emp.dep_id = dept.did;

成果如下

下面语句就是连贯查问，那么多表查问都有哪些呢？

• 连贯查问

  • 内连贯查问：相当于查问 AB 交加数据

  • 外连贯查问

    • 左外连贯查问：相当于查问 A 表所有数据和交加部门数据
    • 右外连贯查问：相当于查问 B 表所有数据和交加局部数据
• 子查问

2.4.2：内连贯

• 概念：内连贯相当与查问 ab 表得交加数据

内连贯又分为两种内连贯

2.4.2.1：隐式内连贯

• 语法

select 字段列表 from 表名 1，表名 2 where 条件

• 案例

SELECT * FROM emp , dept WHERE emp.dep_id = dept.did;

• 后果如下

• 查问 emp 的 name ,gender,dept 表的dname

select emp.name,emp.gender,dept.dname where emp.dep_id=dept.did

• 后果如下

2.4.2.2：显式内连贯

• 语法

select * from 表名 1 inner join 表名 2 on 条件
-- 例
select * from emp inner join dept on emp.dep_id = dept.did;
-- 下面语句中的 inner 能够省略，能够书写为如下语句
select * from emp  join dept on emp.dep_id = dept.did;

2.4.3：外连贯

2.4.3.1 左外连贯

• 语法

select * from 表名 1 LEFT OUTER JOIN 表名 2 on 条件

• 案例

  查问 emp 表所有数据和对应的部门信息

SELECT * FROM emp LEFT JOIN dept ON emp.`dep_id` = dept.`did`;

• 后果如下

• 后果先显示了左表的所有数据 (包含为 null 值) 及与右表关联的数据

2.4.3.2 右外连贯

• 语法

select * from 表 1 right join 表 2 on 条件
select * from emp right join dept on empdep_id =dept.did

• 案例

查问 dept 表所有数据和对应的员工信息

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

看后果他先查问到了右表的数据及右边的数据

3 子查问

• 概念：查问中嵌套查问，称嵌套查问为子查问。

什么是嵌套查问呢？先举个例子

查问工资高于猪八戒的员工信息

咱们要先查问出猪八戒的员工信息

select salary from emp where name='猪八戒'

再查问出高于猪八戒的工资的员工信息

select * from emp where salary >3600

第二步中的 3600 能够通过第一步的 sql 查问进去，所以将 3600 用第一步的 sql 语句进行替换

select * from emp where salary >select salary from emp where name='猪八戒'

这就是嵌套查问

• 子查问依据查问后果不同，作用不同

  • 子查问语句后果是单行单列，子查问语句作为条件值，应用 = != > < 等进行条件判断
  • 子查问语句后果是多行单列，子查问语句作为条件值，应用 in 等关键字进行条件判断
  • 子查问语句后果是多行多列，子查问语句作为虚构表

3.1 标量子查问

• 概念：

  • 子查问返回的后果是单个值(数子，字符串，日期等)，最简略的模式，这种子查问就称为标量子查问
  • 常见的操作符有：= ,<>,>,>=,<,<=

• 案例

  • 查问 ‘ 财务部 ’ 和 ‘ 市场部 ’ 所有的员工信息

    -- 查问 '财务部' 或者 '市场部' 所有的员工的部门 did
    select did from dept where dname = '财务部' or dname = '市场部';
    
    select * from emp where dep_id in (select did from dept where dname = '财务部' or dname = '市场部');

  • 查问入职日期是 ‘2011-11-11’ 之后的员工信息和部门信息

    -- 查问入职日期是 '2011-11-11' 之后的员工信息
    select * from emp where join_date > '2011-11-11' ;
    -- 将下面语句的后果作为虚构表和 dept 表进行内连贯查问
    select * from (select * from emp where join_date > '2011-11-11') t1, dept where t1.dep_id = dept.did;

• 案例

-- 查问销售部的所有员工信息
select did from detp where dname = '销售部'
select * from emp where did=(select did from detp where dname = '销售部')
-- 查问在 '方东白' 入职之后的员工信息
select entrydate from emp where name ='方东白'
select * from emp where entrdate > (select entrydate from emp where name ='方东白')

3.2 列子查问

• 概念

  • 子查问返回的后果是一列或者多行，这种子查问就叫列子查问
  • 常见的操作符有：in（在指定的范畴之内，多选一），not in（不在指定的范畴之内），any(在子查问返回列表中，只有满足列表中任意一个)，some（跟 any 一样），all（在子查问返回列表中，要满足所有条件）
• 案例

-- 查问销售部和市场部的所有员工信息
select did from detp where dname = '销售部' or  ='市场部'
select * from emp where did in (select did from detp where dname = '销售部' or  ='市场部')
-- 查问比财务部所有人工资都高的员工信息
select did from dept where dname ='财务部'
select salary from emp where did = (select did from dept where dname ='财务部')
select * from emp where did >all(select salary from emp where did = (select did from dept where dname ='财务部'))
-- 查问出比研发部其中任意一个员工工资高的所有员工信息
一
select did from dept where dname ='研发部'
select salary from emp where did=(select did from dept where dname ='研发部')
select * from emp where did > ANY (select salary from emp where did=(select did from dept where dname ='研发部'))
二
select did from dept where dname ='研发部'
select salary from emp where did=(select did from dept where dname ='研发部')
select * from emp where did > some (select salary from emp where did=(select did from dept where dname ='研发部'))

3.3 行子查问

• 概念

  • 子查问返回的后果是一行或者多列，就叫行子查问
  • 罕用的操作符有：=,in,not in
• 案例

-- 查问与张无忌 的薪资及直属领导雷同的员工信息
select salary, managerid from emp where name ='张无忌'
select * from emp where (salary,managerdid) =(select salary, managerid from emp where name ='张无忌')
-- 留神：因为是多列的，所以将字段写成汇合模式

3.4 表子查问

• 概念

  • 子查问返回的后果是多行多列，这种叫做表子查问
  • 常见的操作符有 in

  -- 查问与鹿杖客，宋远桥的职位和薪资雷同的员工信息
  select job，salary from emp where name ='鹿杖客' or name= '宋远桥'
  select * from emp (job,salary) =(select job，salary from emp where name ='鹿杖客' or name= '宋远桥')

3.5 案例

• 环境筹备：

-- 部门表
CREATE TABLE dept (
  did INT PRIMARY KEY PRIMARY KEY, -- 部门 id
  dname 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, -- 员工 id
  ename VARCHAR(50), -- 员工姓名
  job_id INT, -- 职务 id
  mgr 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 (id)
);
-- 工资等级表
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
     */
     -- 形式一：隐式内连贯
     SELECT
         emp.id,
         emp.ename,
         emp.salary,
         job.jname,
         job.description
     FROM
         emp,
         job
     WHERE
         emp.job_id = job.id;
     
     -- 形式二：显式内连贯
     SELECT
         emp.id,
         emp.ename,
         emp.salary,
         job.jname,
         job.description
     FROM
         emp
     INNER JOIN job ON emp.job_id = job.id;

  2. 查问员工编号，员工姓名，工资，职务名称，职务形容，部门名称，部门地位

     /*
         剖析：1. 员工编号，员工姓名，工资 信息在 emp 员工表中
             2. 职务名称，职务形容 信息在 job 职务表中
             3. job 职务表 和 emp 员工表 是 一对多的关系 emp.job_id = job.id
     
             4. 部门名称，部门地位 来自于 部门表 dept
             5. dept 和 emp 一对多关系 dept.id = emp.dept_id
     */
     
     -- 形式一：隐式内连贯
     SELECT
         emp.id,
         emp.ename,
         emp.salary,
         job.jname,
         job.description,
         dept.dname,
         dept.loc
     FROM
         emp,
         job,
         dept
     WHERE
         emp.job_id = job.id
         and dept.id = emp.dept_id
     ;
     
     -- 形式二：显式内连贯
     SELECT
         emp.id,
         emp.ename,
         emp.salary,
         job.jname,
         job.description,
         dept.dname,
         dept.loc
     FROM
         emp
     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
     */
     SELECT
         emp.ename,
         emp.salary,
         t2.*
     FROM
         emp,
         salarygrade t2
     WHERE
         emp.salary >= t2.losalary
     AND emp.salary <= t2.hisalary

  4. 查问员工姓名，工资，职务名称，职务形容，部门名称，部门地位，工资等级

     /*
         剖析：1. 员工编号，员工姓名，工资 信息在 emp 员工表中
             2. 职务名称，职务形容 信息在 job 职务表中
             3. job 职务表 和 emp 员工表 是 一对多的关系 emp.job_id = job.id
     
             4. 部门名称，部门地位 来自于 部门表 dept
             5. dept 和 emp 一对多关系 dept.id = emp.dept_id
             6. 工资等级 信息在 salarygrade 工资等级表中
             7. emp.salary >= salarygrade.losalary  and emp.salary <= salarygrade.hisalary
     */
     SELECT
         emp.id,
         emp.ename,
         emp.salary,
         job.jname,
         job.description,
         dept.dname,
         dept.loc,
         t2.grade
     FROM
         emp
     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;

  5. 查问出部门编号、部门名称、部门地位、部门人数

     /*
         剖析：1. 部门编号、部门名称、部门地位 来自于部门 dept 表
             2. 部门人数: 在 emp 表中 依照 dept_id 进行分组，而后 count(*)统计数量
             3. 应用子查问，让部门表和分组后的表进行内连贯
     */
     -- 依据部门 id 分组查问每一个部门 id 和员工数
     select dept_id, count(*) from emp group by dept_id;
     
     SELECT
         dept.id,
         dept.dname,
         dept.loc,
         t1.count
     FROM
         dept,
         (
             SELECT
                 dept_id,
                 count(*) count
             FROM
                 emp
             GROUP BY
                 dept_id
         ) t1
     WHERE
         dept.id = t1.dept_id

4. 事务

• 概念

  • 事务是一组操作的汇合，他是一个不可分隔的工作单位，事务会把所有的操作作为一个整体一起向零碎提交或撤销操作申请，即这些操作要么同时胜利，要么同时生效
  • 数据库的事务（Transaction）是一种机制、一个操作序列，蕴含了 == 一组数据库操作命令 ==。

这些概念不好了解，接下来举例说明，如下图有一张表

张三和李四账户中各有 2000 块钱，现李四须要转换 1000 块钱给张三，具体的转账操作为

• 第一步：查问张三账户余额

SELECT * FROM account WHERE NAME='张三';

• 第二步：从张三账户金额 -1000

UPDATE account SET money=money-1000 WHERE NAME='张三';

• 第三步：给李四账户金额 +1000

UPDATE account SET money=money+1000 WHERE NAME='李四';

4.1 语法

• 开启事务

  START TRANSACTION;
  或者  
  BEGIN;

• 提交事务

  commit;

• 回滚事务

  rollback;

• 不增加事务演示

  SELECT * FROM account WHERE NAME='张三';
  UPDATE account SET money=money-1000 WHERE NAME='张三';
  出现异常了...  -- 此处不是正文，在整体执行时会出问题，前面的 sql 则不执行
  UPDATE account SET money=money+1000 WHERE NAME='李四';

  后果：

  看转账过程中第二步实现后呈现了异样第三步没有执行，就会造成李四账户金额少了 1000，而张三金额并没有多 1000；这样的零碎是有问题的。如果解决呢？应用事务能够解决上述问题

• 开启事务代码

第一种 -- 开启事务
select TRANSACTION
UPDATE account SET money=money-1000 WHERE NAME='张三';
出现异常了...  -- 此处不是正文，在整体执行时会出问题，前面的 sql 则不执行
UPDATE account SET money=money+1000 WHERE NAME='李四';
-- 回滚事务
rollback;
-- 提交事务
commit;

二 -- 开启事务
BEGIN
UPDATE account SET money=money-1000 WHERE NAME='张三';
出现异常了...  -- 此处不是正文，在整体执行时会出问题，前面的 sql 则不执行
UPDATE account SET money=money+1000 WHERE NAME='李四';
-- 回滚事务
rollback;
-- 提交事务
commit;
三 -- 改提交办法
-- mysql 中默认提交的形式是主动提交
SELECT @@autocommit =0 -- 将主动提交形式改为手动提交

• 后果

• 事务处理图

下面 sql 中的执行胜利进抉择执行提交事务，而呈现问题则执行回滚事务的语句。当前咱们必定不可能这样操作，而是在 java 中进行操作，在 java 中能够抓取异样，没出现异常提交事务，出现异常回滚事务。

4.2 事务的四大个性

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

原子性就像方才的银行转账一样，如果有一步出错，就会全副错了

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

一致性就是张三和李四的账户都得是一样得

• 隔离性（Isolation）: 多个事务之间，操作的可见性
• 持久性（Durability）: 事务一旦提交或回滚，它对数据库中的数据的扭转就是永恒的