[大数据]Java程序猿必学第二十六篇——MySQL入门(2)

7.12 子查询（作为条件判断）

SELECT 列名 FROM 表名 Where 条件 (子查询结果)

7.12.1 查询工资大于Bruce 的员工信息

#1.先查询到 Bruce 的工资（一行一列）
SELECT SALARY FROM t_employees WHERE FIRST_NAME = 'Bruce';#工资是 6000
?
#2.查询工资大于 Bruce 的员工信息
SELECT * FROM t_employees WHERE SALARY > 6000;
?
#3.将 1、2 两条语句整合
SELECT * FROM t_employees WHERE SALARY > （SELECT SALARY FROM t_employees WHERE FIRST_NAME = 'Bruce' ）;

• 注意：将子查询 ”一行一列“的结果作为外部查询的条件，做第二次查询

• 子查询得到一行一列的结果才能作为外部查询的等值判断条件或不等值条件判断

7.13 子查询（作为枚举查询条件）

SELECT 列名 FROM 表名 Where 列名 in(子查询结果);

7.13.1 查询与名为'King'同一部门的员工信息

#思路：
#1. 先查询 'King' 所在的部门编号(多行单列)
SELECT department_id
FROM t_employees
WHERE last_name = 'King'; //部门编号：80、90
?
#2. 再查询80、90号部门的员工信息
SELECT employee_id , first_name , salary , department_id
FROM t_employees
WHERE department_id in (80,90); 
?
#3.SQL：合并
SELECT employee_id , first_name , salary , department_id
FROM t_employees
WHERE department_id in (SELECT department_id cfrom t_employees WHERE last_name = 'King'); #N行一列
?

• 将子查询 ”多行一列“的结果作为外部查询的枚举查询条件，做第二次查询

7.13.2 工资高于60部门所有人的信息

#1.查询 60 部门所有人的工资（多行单列）
SELECT SALARY from t_employees WHERE DEPARTMENT_ID=60;
?
#2.查询高于 60 部门所有人的工资的员工信息（高于所有）
select * from t_employees where SALARY > ALL(select  SALARY from t_employees WHERE DEPARTMENT_ID=60);
?
#。查询高于 60 部门的工资的员工信息（高于部分）
select * from t_employees where SALARY > ANY(select  SALARY from t_employees WHERE DEPARTMENT_ID=60);
?

• 注意：当子查询结果集形式为多行单列时可以使用 ANY 或 ALL 关键字

7.14 子查询（作为一张表）

SELECT 列名 FROM（子查询的结果集）WHERE 条件;

7.14.1 查询员工表中工资排名前 5 名的员工信息

#思路：
#1. 先对所有员工的薪资进行排序（排序后的临时表）
select employee_id , first_name , salary
from t_employees
order by salary desc
?
#2. 再查询临时表中前5行员工信息
select employee_id , first_name , salary
from (临时表) 
limit 0,5;
?
#SQL：合并
select employee_id , first_name , salary
from (select employee_id , first_name , salary from t_employees order by salary desc) as temp
limit 0,5;

• 将子查询 ”多行多列“的结果作为外部查询的一张表，做第二次查询。

• 注意：子查询作为临时表，为其赋予一个临时表名

7.15 合并查询（了解）

• SELECT * FROM 表名1 UNION SELECT * FROM 表名2

• SELECT * FROM 表名1 UNION ALL SELECT * FROM 表名2

7.15.1 合并两张表的结果（去除重复记录）

#合并两张表的结果，去除重复记录
SELECT * FROM t1 UNION SELECT * FROM t2;

• 注意：合并结果的两张表，列数必须相同，列的数据类型可以不同

7.15.2 合并两张表的结果（保留重复记录）

#合并两张表的结果，不去除重复记录（显示所有）
SELECT * FROM t1 UNION ALL SELECT * FROM t2;

• 经验：使用 UNION 合并结果集，会去除掉两张表中重复的数据

7.16 表连接查询

SELECT 列名 FROM 表1 连接方式 表2 ON 连接条件

7.16.1 内连接查询（INNER JOIN ON）

#1.查询所有有部门的员工信息（不包括没有部门的员工） SQL 标准
SELECT * from t_employees e INNER JOIN t_departments d ON e.DEPARTMENT_ID=d.DEPARTMENT_ID;
?
#2.查询所有有部门的员工信息（不包括没有部门的员工） MYSQL
SELECT * from t_employees e,t_departments d where e.DEPARTMENT_ID=d.DEPARTMENT_ID;

• 经验：在 MySql 中，第二种方式也可以作为内连接查询，但是不符合 SQL 标准

• 而第一种属于 SQL 标准，与其他关系型数据库通用

7.16.2 三表连接查询

#查询所有员工工号、名字、部门名称、部门所在国家ID
SELECT * FROM t_employees e 
INNER JOIN t_departments d 
on e.department_id = d.department_id
INNER JOIN t_locations l
ON d.location_id = l.location_id

7.16.3 左外连接（LEFT JOIN ON）

#查询所有员工信息，以及所对应的部门名称（没有部门的员工，也在查询结果中,部门名称以NULL 填充）
SELECT e.employee_id , e.first_name , e.salary , d.department_name FROM t_employees e
LEFT JOIN t_departments d 
ON e.department_id = d.department_id;

• 注意：左外连接，是以左表为主表，依次向右匹配，匹配到，返回结果

• 匹配不到，则返回 NULL 值填充

7.16.4 右外连接（RIGHT JOIN ON）

#查询所有部门信息，以及此部门中的所有员工信息（没有员工的部门，也在查询结果中，员工信息以NULL 填充）
SELECT e.employee_id , e.first_name , e.salary , d.department_name FROM t_employees e 
RIGHT JOIN t_departments d 
ON e.department_id = d.department_id;

• 注意：右外连接，是以右表为主表，依次向左匹配，匹配到，返回结果

• 匹配不到，则返回 NULL 值填充

八、 DML 操作【重点】

8.1 新增（INSERT）

INSERT INTO 表名(列 1，列 2，列 3....) VALUES(值 1，值 2，值 3......);

8.1.1 添加一条信息

#添加一条工作岗位信息
INSERT INTO t_jobs(JOB_ID,JOB_TITLE,MIN_SALARY,MAX_SALARY) VALUES('JAVA_Le','JAVA_Lecturer',2500,9000);

#添加一条员工信息
INSERT INTO `t_employees`
（EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE_NUMBER,HIRE_DATE,JOB_ID,SALARY,COMMISSION_PCT,MANAGER_ID,DEPARTMENT_ID）
VALUES 
('194','Samuel','McCain','SMCCAIN', '650.501.3876', '1998-07-01', 'SH_CLERK', '3200', NULL, '123', '50');

• 注意：表名后的列名和 VALUES 里的值要一一对应（个数、顺序、类型）

8.2 修改（UPDATE）

UPDATE 表名 SET 列 1=新值 1 ,列 2 = 新值 2,.....WHERE 条件;

8.2.1 修改一条信息

#修改编号为100 的员工的工资为 25000
UPDATE t_employees SET SALARY = 25000 WHERE EMPLOYEE_ID = '100';

#修改编号为135 的员工信息岗位编号为 ST_MAN，工资为3500
UPDATE t_employees SET JOB_ID='ST_MAN',SALARY = 3500 WHERE EMPLOYEE_ID = '135';

• 注意：SET 后多个列名=值，绝大多数情况下都要加 WHERE 条件，指定修改，否则为整表更新

8.3 删除（DELETE）

DELETE FROM 表名 WHERE 条件；

8.3.1 删除一条信息

#删除编号为135 的员工
DELETE FROM t_employees WHERE EMPLOYEE_ID='135';

#删除姓Peter，并且名为 Hall 的员工
DELETE FROM t_employees WHERE FIRST_NAME = 'Peter' AND LAST_NAME='Hall';

• 注意：删除时，如若不加 WHERE条件，删除的是整张表的数据

8.4 清空整表数据（TRUNCATE）

TRUNCATE TABLE 表名;

8.4.1 清空整张表

#清空t_countries整张表
TRUNCATE TABLE t_countries;

• 注意：与 DELETE 不加 WHERE 删除整表数据不同，TRUNCATE 是把表销毁，再按照原表的格式创建一张新表

九、数据表操作

9.1 数据类型

MySQL支持多种类型，大致可以分为三类：数值、日期/时间和字符串(字符)类型。对于我们约束数据的类型有很大的帮助

9.1.1 数值类型

9.1.2 日期类型

9.1.3 字符串类型

• CHAR和VARCHAR类型类似，但它们保存和检索的方式不同。它们的最大长度和是否尾部空格被保留等方面也不同。在存储或检索过程中不进行大小写转换。

• BLOB是一个二进制大对象，可以容纳可变数量的数据。有4种BLOB类型：TINYBLOB、BLOB、MEDIUMBLOB和LONGBLOB。它们只是可容纳值的最大长度不同。

9.2 数据表的创建（CREATE）

CREATE TABLE 表名（

列名 数据类型 [约束],

列名 数据类型 [约束],

....

列名 数据类型 [约束] //最后一列的末尾不加逗号

）[charset=utf8] //可根据需要指定表的字符编码集

9.2.1 创建表

#依据上述表格创建数据表，并向表中插入 3 条测试语句
CREATE TABLE subject(
    subjectId INT,
  subjectName VARCHAR(20),
  subjectHours INT
)charset=utf8;
?
INSERT INTO subject(subjectId,subjectName,subjectHours) VALUES(1,'Java',40);
INSERT INTO subject(subjectId,subjectName,subjectHours) VALUES(2,'MYSQL',20);
INSERT INTO subject(subjectId,subjectName,subjectHours) VALUES(3,'JavaScript',30);

9.3 数据表的修改（ALTER）

ALTER TABLE 表名 操作;

9.3.1 向现有表中添加列

#在课程表基础上添加gradeId 列
ALTER TABLE subject ADD gradeId int;

9.3.2 修改表中的列信息

#修改课程表中课程名称长度为10个字符
ALTER TABLE subject MODIFY subjectName VARCHAR(10);

• 注意：修改表中的某列时，也要写全列的名字，数据类型，约束

9.3.3 删除表中的列

#删除课程表中 gradeId 列
ALTER TABLE subject DROP gradeId;

• 注意：删除列时，每次只能删除一列

9.3.4 修改列名

#修改课程表中 subjectHours 列为 classHours
ALTER TABLE subject CHANGE subjectHours classHours int ;

• 注意：修改列名时，在给定列新名称时，要指定列的类型和约束

9.3.5 修改表名

#修改课程表的subject 为 sub
ALTER TABLE subject rename sub;

9.4 数据表的删除（DROP）

DROP TABLE 表名

9.4.1 删除学生表

#删除学生表
DROP TABLE subject;

十、约束

• 问题：在往已创建表中新增数据时，可不可以新增两行相同列值得数据？

• 如果可行，会有什么弊端？

10.1 实体完整性约束

表中的一行数据代表一个实体（entity），实体完整性的作用即是标识每一行数据不重复、实体唯一。

10.1.1 主键约束

PRIMARY KEY 唯一，标识表中的一行数据，此列的值不可重复，且不能为 NULL

#为表中适用主键的列添加主键约束
CREATE TABLE subject(
    subjectId INT PRIMARY KEY,#课程编号标识每一个课程的编号唯一，且不能为 NULL
  subjectName VARCHAR(20),
  subjectHours INT
)charset=utf8;
?
INSERT INTO subject(subjectId,subjectName,subjectHours) VALUES(1,'Java',40);
INSERT INTO subject(subjectId,subjectName,subjectHours) VALUES(1,'Java',40);#error 主键 1 已存在

10.1.2 唯一约束

UNIQUE 唯一，标识表中的一行数据，不可重复，可以为 NULL

#为表中列值不允许重复的列添加唯一约束
CREATE TABLE subject(
    subjectId INT PRIMARY KEY,
  subjectName VARCHAR(20) UNIQUE,#课程名称唯一！
  subjectHours INT
)charset=utf8;
?
INSERT INTO subject(subjectId,subjectName,subjectHours) VALUES(1,'Java',40);
INSERT INTO subject(subjectId,subjectName,subjectHours) VALUES(2,'Java',40);#error 课程名称已存在

10.1.3 自动增长列

AUTO_INCREMENT 自动增长，给主键数值列添加自动增长。从 1 开始，每次加 1。不能单独使用，和主键配合。

#为表中主键列添加自动增长，避免忘记主键 ID 序号
CREATE TABLE subject(
    subjectId INT PRIMARY KEY AUTO_INCREMENT,#课程编号主键且自动增长，会从 1 开始根据添加数据的顺序依次加 1
  subjectName VARCHAR(20) UNIQUE,
  subjectHours INT
)charset=utf8;
?
INSERT INTO subject(subjectName,subjectHours) VALUES('Java',40);#课程编号自动从 1 增长
INSERT INTO subject(subjectName,subjectHours) VALUES('JavaScript',30);#第二条编号为 2

10.2 域完整性约束

限制列的单元格的数据正确性。

10.2.1 非空约束

NOT NULL 非空，此列必须有值。

#课程名称虽然添加了唯一约束，但是有 NULL 值存在的可能,要避免课程名称为NULL
CREATE TABLE subject(
    subjectId INT PRIMARY KEY AUTO_INCREMENT,
  subjectName VARCHAR(20) UNIQUE NOT NULL,
  subjectHours INT
)charset=utf8;
?
INSERT INTO subject(subjectName,subjectHours) VALUES(NULL,40);#error，课程名称约束了非空

10.2.2 默认值约束

DEFAULT 值 为列赋予默认值，当新增数据不指定值时，书写DEFAULT，以指定的默认值进行填充。

#当存储课程信息时，若课程时长没有指定值，则以默认课时 20 填充
CREATE TABLE subject(
    subjectId INT PRIMARY KEY AUTO_INCREMENT,
  subjectName VARCHAR(20) UNIQUE NOT NULL,
  subjectHours INT DEFAULT 20
)charset=utf8;
?
INSERT INTO subject(subjectName,subjectHours) VALUES('Java',DEFAULT);#课程时长以默认值 20 填充

10.2.3 引用完整性约束

• 语法：CONSTRAINT 引用名 FOREIGN KEY（列名） REFERENCES 被引用表名(列名)

• 详解：FOREIGN KEY 引用外部表的某个列的值，新增数据时，约束此列的值必须是引用表中存在的值。

#创建专业表
CREATE TABLE Speciality(
    id INT PRIMARY KEY AUTO_INCREMENT,
    SpecialName VARCHAR(20) UNIQUE NOT NULL
)CHARSET=utf8;
?
#创建课程表(课程表的SpecialId 引用专业表的 id)
CREATE TABLE subject(
    subjectId INT PRIMARY KEY AUTO_INCREMENT,
  subjectName VARCHAR(20) UNIQUE NOT NULL,
  subjectHours INT DEFAULT 20,
  specialId INT NOT NULL,
 ?CONSTRAINT fk_subject_specialId ?FOREIGN KEY(specialId) REFERENCES Speciality(id) ?#引用专业表里的 id 作为外键，新增课程信息时，约束课程所属的专业。
)charset=utf8;
?
#专业表新增数据
INSERT INTO Speciality(SpecialName) VALUES('Java');
INSERT INTO Speciality(SpecialName) VALUES('C#');
#课程信息表添加数据
INSERT INTO subject(subjectName,subjectHours) VALUES('Java',30,1);#专业 id 为 1，引用的是专业表的 Java
INSERT INTO subject(subjectName,subjectHours) VALUES('C#MVC',10,2);#专业 id 为 2，引用的是专业表的 C#

• 注意：当两张表存在引用关系，要执行删除操作，一定要先删除从表（引用表），再删除主表（被引用表）

10.3 约束创建整合

创建带有约束的表。

10.3.1 创建表

CREATE TABLE Grade(
    GradeId INT PRIMARY KEY AUTO_INCREMENT,
    GradeName VARCHAR(20) unique NOT NULL
)CHARSET=UTF8;

CREATE TABLE student(
  student_id varchar(50) PRIMARY KEY,
  student_name varchar(50) NOT NULL,
  sex CHAR(2) DEFAULT '男',
  borndate date NOT NULL,
  phone varchar(11),
  gradeId int not null,
 ?CONSTRAINT fk_student_gradeId ?FOREIGN KEY(gradeId) REFERENCES Grade(GradeId) ?#引用Grade表的GradeId列的值作为外键，插入时约束学生的班级编号必须存在。
);

• 注意：创建关系表时，一定要先创建主表，再创建从表

• 删除关系表时，先删除从表，再删除主表。

十一、事务【重点】

11.1 模拟转账

生活当中转账是转账方账户扣钱，收账方账户加钱。我们用数据库操作来模拟现实转账。

11.1.1 数据库模拟转账

#A 账户转账给 B 账户 1000 元。
#A 账户减1000 元
UPDATE account SET MONEY = MONEY-1000 WHERE id=1;
?
#B 账户加 1000 元
UPDATE account SET MONEY = MONEY+1000 WHERE id=2;

• 上述代码完成了两个账户之间转账的操作。

11.1.2 模拟转账错误

#A 账户转账给 B 账户 1000 元。
#A 账户减1000 元
UPDATE account SET MONEY = MONEY-1000 WHERE id=1;
#断电、异常、出错...
?
#B 账户加 1000 元
UPDATE account SET MONEY = MONEY+1000 WHERE id=2;

• 上述代码在减操作后过程中出现了异常或加钱语句出错，会发现，减钱仍旧是成功的，而加钱失败了！

• 注意：每条 SQL 语句都是一个独立的操作，一个操作执行完对数据库是永久性的影响。

11.2 事务的概念

事务是一个原子操作。是一个最小执行单元。可以由一个或多个SQL语句组成，在同一个事务当中，所有的SQL语句都成功执行时，整个事务成功，有一个SQL语句执行失败，整个事务都执行失败。

11.3 事务的边界

• 开始：连接到数据库，执行：STRAT TRANSACTION; 即开启事务。然后输入DML操作

• 结束：

1). 提交：

a. 显示提交：commit;

b. 隐式提交：一条创建、删除的语句，正常退出（客户端退出连接）;

2). 回滚：

a. 显示回滚：rollback;

b. 隐式回滚：非正常退出（断电、宕机），执行了创建、删除的语句，但是失败了，会为这个无效的语句执行回滚。

11.4 事务的原理

数据库会为每一个客户端都维护一个空间独立的缓存区(回滚段)，一个事务中所有的增删改语句的执行结果都会缓存在回滚段中，只有当事务中所有SQL 语句均正常结束（commit），才会将回滚段中的数据同步到数据库。否则无论因为哪种原因失败，整个事务将回滚（rollback）。

11.5 事务的特性

• Atomicity(原子性)

　　　　表示一个事务内的所有操作是一个整体，要么全部成功，要么全部失败

• Consistency(一致性)

　　　　表示一个事务内有一个操作失败时，所有的更改过的数据都必须回滚到修改前状态

• Isolation(隔离性)

　　　　事务查看数据操作时数据所处的状态，要么是另一并发事务修改它之前的状态，要么是另一事务修改它之后的状态，事务不会查看中间状态的数据。

• Durability(持久性)

　　　　持久性事务完成之后，它对于系统的影响是永久性的。

11.6 事务应用

应用环境：基于增删改语句的操作结果（均返回操作后受影响的行数），可通过程序逻辑手动控制事务提交或回滚

11.6.1 事务完成转账

#A 账户给 B 账户转账。
#1.开启事务
START TRANSACTION;
#2.事务内数据操作语句
UPDATE ACCOUNT SET MONEY = MONEY-1000 WHERE ID = 1;
UPDATE ACCOUNT SET MONEY = MONEY+1000 WHERE ID = 2;
#3.事务内语句都成功了，执行 COMMIT；
COMMIT;
#4.事务内如果出现错误，执行 ROLLBACK;
ROLLBACK;

• 注意：开启事务后，执行的语句均属于当前事务，成功再执行 COMIIT，失败要进行 ROLLBACK