MySQL基础

2. MySQL

2.1. 数据库的基本概念

• 数据库的英文单词：DataBase，简称DB

2.1.1. 什么是数据库？

• 用于存储和管理数据的仓库

2.1.2. 数据库的特点

• 持久化存储数据的。其实数据库就是一个文件系统
• 方便存储和管理数据
• 使用了统一的方式操作数据库——SQL

2.1.3. 常见的数据库软件

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-sztMJ9cM-1572262272843)(images/常见的数据库.png)]

• MySQL：开源免费的数据库，小型的数据库。已经被Oracle收购了，MySQL6.x版本也开始收费。
• Oracle：收费的大型数据库，Oracle公司的产品。Oracle收购SUN公司，收购MySQL。
• DB2：IBM公司的数据库产品，收费的。常应用在银行系统中。
• SQL Server：MicroSoft公司收费的中型的数据库。C#，.net等语言常用。
• SQLite：嵌入式的小型数据库，应用在手机端。
• 常用的数据库：MySQL，Oracle

在web应用中，使用最多的就是MySQL数据库，原因如下：

1.开源、免费

2.功能足够强大，

2.2. MySQL数据库软件

2.2.1. 安装

• MySQL的安装

2.2.2. 卸载

• MySQL的卸载

2.2.3. 配置

• MySQL服务启动

  • 手动/cmd–>services.msc
  • 使用管理员权限打开cmd
    • net start mysql：启动MySQL的服务
    • net stop mysql：关闭MySQL的服务

• MySQL登录

  • mysql -u root -p：mysql -u 用户名(root) -p
  • mysql -hip -u root -p：mysql -h ip地址 -u 用户名(root) -p
  • mysql --host=ip --user=root --password=密码：mysql --host=ip地址 --user=用户名 --password=密码

• MySQL退出

  • exit
  • quit

• MySQL的目录结构

  • MySQL的安装目录

    [外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-YX07TAjs-1572262272850)(images/MySQL数据库的安装目录.png)]

    • 配置文件：my.ini

  • MySQL的数据目录

    [外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-NS4UKJQB-1572262272854)(images/MySQL数据库中表和数据的关系.png)]

    • 数据库：文件夹
    • 表：文件
    • 数据：文件中存储的一条记录

2.3. SQL

2.3.1. 什么是SQL？

• Structured Query Language：结构化查询语言。其实就是定义了操作所有关系型数据库的规则。每一种数据库操作的方式存在不一样的地方，称为“方言”。

2.3.2. SQL通用语法

• SQL语句可以单行或多行书写，以分号进行结尾。

• 可以使用空格或缩进来增强语句的可读性。

• MySQL数据库的SQL语句不区分大小写，关键字建议使用大写。

  SELECT * FROM student;
  

• 三种注释：

  • 单行注释

    -- 注释内容
    # 注释内容（MySQL特有的）
    

  • 多行注释

    /*
    	多行注释
    */
    

2.3.3. SQL分类

• DDL（Data Definition Language）数据定义语言：用来定义数据库对象：数据库、表、列等。关键字：CREATE，DROP，ALTER等

• DML（Data Manipulation Language）数据操作语言：用来对数据库中表的数据进行增删改。关键字：INSERT，DELETE，UPDATE等

• DQL（Data Query Language）数据查询语言：用来查询数据库中表的记录（数据）。关键字：SELECT，WHERE等

• DCL（Data Control Language）数据控制语言【了解】：用来定义数据库的访问权限和安全级别，及创建用户。关键字：GRANT，REVOKE等

  [外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-HHACspQX-1572262272856)(images/数据库中四类SQL语句.png)]

2.4. DDL（数据定义语言）

2.4.1. 操作数据库：CRUD

• C（CREATE）：创建

  # 创建数据库
  CREATE DATABASE 数据库名;
  
  # 判断一个数据库是否存在，不存在则创建
  CREATE DATABASE IF NOT EXISTS 数据库名;
  
  # 创建数据库是指定所使用的字符集
  CREATE DATABASE 数据库名称 CHARSET 字符集名称;
  CREATE DATABASE 数据库名称 CHARACTER SET 字符集名称;
  
  # 创建数据库同时判断其是否存在并指定字符集
  CREATE DATABASE IF NOT EXISTS 数据库名称 CHARACTER SET 字符集名称;
  

• R（RETRIEVE)：查询

  # 查询所有数据库的名称
  SHOW DATABASES;
  
  # 查询创建mysql数据库的语法以及所使用的字符集
  SHOW CREATE DATABASE 数据库名称;
  

• U（UPDATE）：修改

  # 修改数据库的字符集
  ALTER DATABASE 数据库名称 CHARACTER SET 字符集名称;
  

• D（DELETE）：删除

  # 删除数据库
  DROP DATABASE 数据库名称;
  
  # 判断一个数据库是否存在，存在则删除
  DROP DATABASE IF EXISTS 数据库名称;
  

• 使用数据库

  # 查询当前正在使用的数据库名称
  SELECT DATABASE();
  
  # 使用某一个数据库
  USE 数据库名称;
  

2.4.2. 操作表：CRUD

• C（CREATE）：创建

  -- 语法
  CREATE TABLE 表名(
      列名1 数据类型1,
      列名2 数据类型2,
      ...
      列名n 数据类型n
  );
  # 注意：最后一列，不需要逗号（,）
  

  -- 数据类型
  int : 整数类型
  	age int,
  double : 小数类型
  	score double(5,2),
  date : 日期，只包含年月日，yyyy-MM-dd
  datetime : 日期，包含年月日时分秒，yyyy-MM-dd HH:mm:ss
  timestamp : 时间戳类型，包含年月日时分秒，yyyy-MM-dd HH:mm:ss
  	区别：如果将来不给这个字段赋值，或赋值为null，则默认使用当前的系统时间，来自动赋值
  varchar : 字符串
  	name varchar(20), -- 姓名最大20个字符
  	zhangsan 8个字符	张三 2个字符
  

  # 创建表
  CREATE TABLE 表名 (
      id INT,
      name VARCHAR(32),
      age INT,
      score DOUBLE(4,1),
      birthday date,
      insert_time TIMESTAMP
  );
  
  # 复制一张表
  CREATE TABLE 表名 LIKE 被复制的表名;
  

• R（RETRIEVE)：查询

  # 查询某个数据库中所有的表的名称
  SHOW TABLES;
  
  # 查询表结构
  DESC 表名;
  

• U（UPDATE）：修改

  # 修改表名
  ALTER TABLE 表名 RENAME TO 修改后的表名;
  
  # 查看表的属性以及表所使用的字符集
  SHOW CREATE TABLE 表名;
  # 修改表的字符集
  ALTER TABLE 表名 CHARACTER SET 编码名称;
  
  # 添加一列
  ALTER TABLE 表名 ADD 列名 数据类型;
  
  # 修改列名称 类型
  ALTER TABLE 表名 CHANGE 列名 修改后的列名 修改后的数据类型;
  # 只修改数据类型
  ALTER TABLE 表名 MODIFY 列名 数据类型;
  
  # 删除列
  ALTER TABLE 表名 DROP 列名;
  

• D（DELETE）：删除

  # 删除表
  DROP TABLE IF EXISTS 表名;
  

2.5. DML（数据操作语言）

2.5.1. 添加数据

# 语法
INSERT INTO 表名(列名1, 列名2,...列名n) VALUES(值1, 值2,...值n);
# 示例
INSERT INTO student(id, name, age) VALUES(1, '张无忌', 18);
INSERT INTO student VALUES(2, "赵敏", 17, 99.9, null, null);
INSERT INTO student VALUES(3, '张三丰', 17, 99.9, '1893-11-11', null);

# 注意
1.列名和值要一一对应
2.如果表名后，不定义列名，则默认给所有列添加值
3.除了数字类型，其他类型需要使用引号（单双都可以）引起来

2.5.2. 删除数据

# 语法
DELETE FROM 表名 [WHERE 条件];
# 删除表中所有数据
DELETE FROM 表名;
# 删除表，然后再创建一个一模一样的空表
TRUNCATE TABLE 表名;
# 示例
DELETE FROM student WHERE id=1;

# 注意
1.如果不加条件，则删除表中所有记录
2.如果要删除所有记录
	1.DELETE FROM 表名：-- 不推荐使用。有多少条记录就会执行多少次删除操作
	2.TRUNCATE TABLE 表名：-- 推荐使用，效率更高。先删除表，然后再创建一张一模一样的表

2.5.3. 修改数据

# 语法
UPDATE 表名 SET 列名1 = 值1, 列名2 = 值2,... [WHERE 条件];
# 示例
UPDATE student SET age = 117 WHERE id = 3;

# 注意
1.如果不加任何条件，则会将表中所有记录全部修改

2.6. DQL（数据查询语言）

2.6.1. 基础查询

• 语法

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

• 基础查询

  # 多个字段的查询
  -- 查询：姓名和年龄
  SELECT
  		name,-- 姓名
  		age-- 年龄
  FROM
  		student;-- 学生表
  		
  # 去除重复的结果集
  SELECT DISTINCT address FROM student;
  # 当两条记录完全一样时，才会去重
  SELECT DISTINCT name, address FROM student;
  
  # 计算math和english的分数之和
  SELECT name, math, english, math + english FROM student;
  # 如果有NULL参与的计算结果都为NULL
  SELECT name, math, english, math + IFNULL(english,0) FROM student;
  -- IFNULL(表达式1, 表达式2);
  -- 表达式1：哪个字段需要去判断是否为NULL
  -- 表达式2：如果该字段为NULL后的替换值
  
  # 起别名
  SELECT name, math, english, math + IFNULL(english,0) AS 总分 FROM student;
  -- AS也可以省略
  

2.6.2. 条件查询

• WHERE子句后跟条件

• 运算符

  -- 1.>、<、>=、<=、=、<>
  # 查询年龄大于20岁
  SELECT * FROM student WHERE age > 20;
  # 查询年龄等于20岁
  SELECT * FROM student WHERE age = 20;
  # 查询年龄不等于20岁
  SELECT * FROM student WHERE age <> 20;
  
  -- 2.BETWEEN...AND
  # 查询年龄大于等于20	小于等于30
  SELECT * FROM student WHERE age BETWEEN 20 AND 30;
  
  -- 3.IN(集合)
  # 查询年龄22岁，19岁，25岁的信息
  SELECT * FROM student WHERE age IN (22, 18, 25);
  
  -- 4.LIKE：模糊查询
  -- 占位符：
  -- _：单个任意字符
  -- %：多个任意字符
  # 查询班级里面姓马的同学
  SELECT * FROM student WHERE name LIKE '马%';
  # 查询姓名中第二个字是化的同学
  SELECT * FROM student WHERE name LIKE '_化%';
  # 查询姓名是三个字的同学
  SELECT * FROM student WHERE name LIKE '___';
  # 查询姓名中有德的同学
  SELECT * FROM student WHERE name LIKE '%德%';
  
  -- 5.IS NULL
  # 查询英语成绩为NULL的学生
  SELECT * FROM student WHERE english = null;-- 不对的。NULL值不能用=（!=）来判断
  SELECT * FROM student WHERE english IS NULL;
  SELECT * FROM student WHERE english IS NOT NULL;
  
  -- 6.and 或 &&
  # 查询年龄大于等于20	小于等于30
  SELECT * FROM student WHERE age >= 20 AND age <=30;
  
  -- 7.or 或 ||
  # 查询年龄22岁，19岁，25岁的信息
  SELECT * FROM student WHERE age = 22 or age = 18 or age = 25;
  
  -- 8.not 或 !
  # 查询年龄不等于20岁
  SELECT * FROM student WHERE age != 20;
  

2.6.3. 排序查询

• 语法：ORDER BY 子句

  # 语法
  ORDER BY 排序字段1 排序方式1, 排序字段2 排序方式2,...
  
  # 示例
  # 排序查询
  SELECT * FROM student ORDER BY math;-- 排序方式，默认为ASC，升序
  SELECT * FROM student ORDER BY math DESC;-- 排序方式，DESC，降序
  -- 按照数学成绩排名，如果数学成绩一样，则按照英语成绩排名
  SELECT * FROM student ORDER BY math ASC, english ASC;
  
  # 排序方式
  1.ASC：升序，默认的
  2.DESC：降序
  
  # 注意
  如果有多个排序条件，则当前边的条件值一样时，才会判断第二条件
  

2.6.4. 聚合函数

• 将一列数组作为一个整体，进行纵向的计算

• count：计算个数

  # 计算个数
  SELECT COUNT(列名) FROM 表名;
  
  # 示例
  # 计算个数
  SELECT COUNT(NAME) FROM student;
  SELECT COUNT(english) FROM student;
  # 如果是NULL值，替换为0
  SELECT COUNT(IFNULL(english, 0)) FROM student;
  

• max：计算最大值

  # 计算最大值
  SELECT MAX(列名) FROM 表名;
  

• min：计算最小值

  # 计算最小值
  SELECT MIN(列名) FROM 表名;
  

• sum：计算和

  # 计算和
  SELECT SUM(列名) FROM 表名;
  

• avg：计算平均值

  # 计算平均值
  SELECT AVG(列名) FROM 表名;
  

• 注意：

  • 聚合函数的计算，排除null值。

    1. 选择不包含非空的列进行计算

    2. IFNULL函数

2.6.5. 分组查询

• 语法：GROUP BY 分组字段;

  -- 按照性别分组，分别查询男、女同学的平均分
  SELECT sex, AVG(math) FROM student GROUP BY sex;
  
  -- 按照性别分组，分别查询男、女同学的平均分，以及男女人数
  SELECT sex, AVG(math), COUNT(id) FROM student GROUP BY sex;
  
  -- 按照性别分组，分别查询男、女同学的平均分，以及男女人数		要求：分数低于70分的人，不参与分组
  SELECT sex, AVG(math), COUNT(id) 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) AS 人数 FROM student WHERE math > 70 GROUP BY sex HAVING 人数 > 2;
  

• 注意：

  • 分组之后查询的字段：分组字段、聚合函数
  • WHERE和HAVING的区别？
    • WHERE在分组之前进行限定，如果不满足条件，则不参与分组；HAVING在分组之后进行限定，如果不满足条件，则不会被查询出来。
    • WHERE后不可以跟聚合函数；HAVING可以进行

2.6.6. 分页查询

• 语法：limit 开始的索引,每页查询的条数;

  -- 每页显示3条记录
  SELECT * FROM student LIMIT 0,3; -- 第1页
  SELECT * FROM student LIMIT 3,3; -- 第2页
  -- 公式：开始的索引 = （当前的页码 - 1） * 每页显示的条数
  -- limit操作是一个MySQL的“方言”
  

2.7.约束

• 概念：对表中的数据进行限定，保证数据的正确性、有效性和完整性。

2.7.1. 主键约束（PRIMARY KEY）

1.非空且唯一

2.一张表只能有一个字段为主键

3.主键就是表中记录的唯一标识

• 创建表时添加主键约束

  CREATE TABLE stu (
  		id INT PRIMARY KEY, -- 给id添加主键约束
  		name VARCHAR(20)
  );
  

• 创建表完成后添加主键约束

  ALTER TABLE stu MODIFY id INT PRIMARY KEY;
  

• 删除主键约束

  -- ALTER TABLE stu MODIFY id INT;-- 修改失败
  ALTER TABLE stu DROP PRIMARY KEY;
  

• 自动增长

  • 概念：如果某一列是数值类型的，使用AUTO_INCREMENT可以来完成值的自动增长

  • 创建表时，添加主键约束，并且完成主键自增长

    CREATE TABLE stu (
    	id INT PRIMARY KEY AUTO_INCREMENT, -- 给id添加主键约束，并且完成自动增长
    	name VARCHAR(20)
    );
    

  • 创建表完成后，添加自动增长

    ALTER TABLE stu MODIFY id INT AUTO_INCREMENT;
    

  • 删除自动增长

    ALTER TABLE stu MODIFY id INT;
    

2.7.2. 非空约束（NOT NULL）

值不能为NULL

• 创建表时添加非空约束

  CREATE TABLE stu (
  	id INT,
  	name VARCHAR(20) NOT NULL -- name为非空
  );
  

• 创建表完成后添加非空约束

  ALTER TABLE stu CHANGE name name VARCHAR(20) NOT NULL;
  ALTER TABLE stu MODIFY name VARCHAR(20) NOT NULL;
  

• 删除非空约束

  ALTER TABLE stu MODIFY NAME VARCHAR(20);
  

2.7.3. 唯一约束（UNIQUE）

值不能重复

• 创建表时添加唯一约束

  CREATE TABLE stu (
  	id INT,
  	phone_number VARCHAR(20) UNIQUE -- 添加了唯一约束
  );
  -- 注意MySQL中，唯一约束限定的列的值可以有多个NULL
  

• 创建表完成后添加唯一约束

  ALTER TABLE stu MODIFY phone_number VARCHAR(20) UNIQUE;
  

• 删除唯一约束

  -- ALTER TABLE stu MODIFY phone_number VARCHAR(20);-- 删除失败
  ALTER TABLE stu DROP INDEX phone_number;
  

2.7.4. 外键约束（FOREIGN KEY）

• 在创建表时，可以添加外键。（让表与表产生关系）

  CREATE TABLE 表名 (
  	...
      外键列
      CONSTRAINT 外键名称 FOREIGN KEY （外键列名称） REFERENCES 主表名称（主表列名称）
  );
  
  # 示例
  CREATE TABLE employee (
  	id INT PRIMARY KEY AUTO_INCREMENT,
  	name VARCHAR(20),
  	age INT,
  	dep_id INT,-- 外键对应主表的主键
  	CONSTRAINT emp_depart_fk FOREIGN KEY (dep_id) REFERENCES department(id)
  );
  

• 创建表完成后，添加外键约束

  ALTER TABLE employee ADD CONSTRAINT emp_depart_fk FOREIGN KEY (dep_id) REFERENCES department(id);
  

• 删除外键约束

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

• 级联操作

  # 语法
  ALTER TABLE 表的名称 ADD CONSTRAINT 外键名称 FOREIGN KEY (外键列名称) REFERENCES 主表名称(主表列名称) ON UPDATE CASCADE ON DELETE CASCADE;
  
  # 添加外键时，设置级联更新
  ALTER TABLE employee ADD CONSTRAINT emp_depart_fk FOREIGN KEY (dep_id) REFERENCES department(id) ON UPDATE CASCADE;
  
  # 添加外键时，设置级联更新，设置级联删除
  ALTER TABLE employee ADD CONSTRAINT emp_depart_fk FOREIGN KEY (dep_id) REFERENCES department(id) ON UPDATE CASCADE ON DELETE CASCADE;
  

2.8. 数据库的设计

2.8.1. 多表之间的关系

• 分类：
  • 一对一：
    • 人和身份证
    • 分析：一个人只有一个身份证，一个身份证只能对应一个人
  • 一对多（多对一）：
    • 部门和员工
    • 分析：一个部门有多个员工，一个员工只能对应一个部门
  • 多对多：
    • 学生和课程
    • 分析：一个学生可以选择很多门课程，一个课程也可以被很多学生选择
• 实现关系：
  • 一对多（多对一）：
    • 部门和员工
    • 实现方式：在多的一方建立外键，指向一的一方的主键
  • 多对多：
    • 学生和课程
    • 实现方式：多对多关系实现需要借助第三张中间表。中间表至少包含两个字段，这两个字段作为第三张表的外键，分别指向两张表的主键
  • 一对一：
    • 人和身份证
    • 实现方式：可以在任意地方添加唯一外键来指向另一方的主键
• 案例：多表关系案例

2.8.2. 数据库设计的范式

• 概念：设计数据库时，需要遵循的一些规范。要遵循后边的范式要求，必须先遵循前边的所有范式要求
• 分类：
  • 第一范式（1NF）：每一列都是不可分割的原子数据项
  • 第二范式（2NF）：在1NF的基础上，非码属性必须完全依赖与码（在1NF基础上消除非主属性对主码的部分函数依赖）
    • 函数依赖：A–>B，如果通过A属性（属性组）的值，可以确定唯一B属性的值，则称B依赖A
      • 学号–>姓名；（学号，课程名称）–>分数
    • 完全函数依赖：A–>B，如果A是一个属性组，则B属性值的确定需要依赖于A属性组中所有的属性值
      • （学号，课程名称）–>分数
    • 部分函数依赖：A–>B，如果A是一个属性组，则B属性值的确定只需要依赖于A属性组中某一些值即可
      • （学号，课程名称）–>姓名
    • 传递函数依赖：A–>B，B–>C，如果通过A属性（属性组）的值，可以确定唯一B属性的值，再通过B属性（属性组）的值可以确定唯一C属性的值，则称C传递函数依赖于A
      • 学号–>系名，系名–>系主任
    • 码：如果一个属性或属性组，被其他所有属性所完全依赖，则称这个属性（属性组）为该表的码
      • 该表中码为：（学号，课程名称）
      • 主属性：码属性组中的所有属性
      • 非主属性：除过码属性组的属性
  • 第三范式（3NF）：在2NF的基础上，任何非主属性不依赖于其它的非主属性（在2NF基础上消除传递依赖）
  • 三范式详解：三范式详解

2.9. 数据库的备份和还原

2.9.1. 命令行

• 备份：mysqldump -u用户名 -p密码 数据库名称 > 保存的路径
• 还原：
  • 登录数据库
  • 创建数据库
  • 使用数据库
  • 执行文件：source 文件路径

2.9.2. 图形化工具【省略】

2.10. 多表查询

2.10.1. 查询语法

SELECT
	列名列表
FROM
	表名列表
WHERE...

# 多表查询
SELECT * FROM emp, dept;-- 笛卡尔积

2.10.2. 分类

• 内连接查询：

  • 隐式内连接：

    # 查询所有员工信息和对应的部门信息
    SELECT * FROM emp, dept WHERE emp.dept_id = dept.id;
    # 查询员工表的名称，性别，部门表的名称
    SELECT emp.name, gender, dept.name FROM emp, dept WHERE emp.dept_id = dept.id;
    
    SELECT
    	t1.NAME,-- 员工表的姓名
    	gender,-- 员工表的性别
    	t2.NAME-- 部门表的名称
    FROM
    	emp t1,-- 员工表
    	dept t2-- 部门表
    WHERE
    	t1.dept_id = t2.id;
    

  • 显式内连接：

    语法：
    SELECT 字段列表 FROM 表名1 inner join 表名2 on 条件;
    

    # 查询所有的员工信息和对应的部门信息
    SELECT * FROM emp INNER JOIN dept ON emp.dept_id = dept.id;
    SELECT * FROM emp JOIN dept ON emp.dept_id = dept.id;
    

  • 注意事项：

    1.从哪些表中查询数据
    2.条件是什么
    3.查询那些字段
    

• 外连接查询：

  • 左外连接：查询的是左表所有数据以及其交集部分

    语法：
    SELECT 字段列表 FROM 表1 LEFT [OUTER] JOIN 表2 ON 条件; 
    

    # 查询所有员工信息，如果员工有部门则查询部门名称，没有部门，则不显示部门名称
    SELECT
    	t1.*,
    	t2.NAME 
    FROM
    	emp t1 
    LEFT JOIN
    	dept t2
    ON
    	t1.dept_id = t2.id;
    

  • 右外连接：查询的是右表所有数据以及其交集部分

    语法：
    SELECT 字段列表 FROM 表1 RIGHT [OUTER] JOIN 表2 ON 条件; 
    

    # 查询所有员工信息，如果员工有部门则查询部门名称，没有部门，则不显示部门名称
    SELECT
    	t1.*,
    	t2.NAME 
    FROM
    	dept t2 
    RIGHT JOIN
    	emp t1
    ON
    	t1.dept_id = t2.id;
    

• 子查询：查询中嵌套查询，称嵌套查询为子查询

  • 基本子查询

    # 查询工资最高的员工信息
    -- 1.查询最高的工资是多少？9000
    SELECT MAX(salary) FROM emp;
    -- 2.查询员工信息，并且工资等于9000
    SELECT * FROM emp WHERE salary = 9000;
    -- 一条sql就完成这个操作
    SELECT * FROM emp WHERE salary = (SELECT MAX(salary) FROM emp);
    

  • 子查询的结果是单行单列的：子查询可以作为条件，使用运算符去判断。运算符：>、<、=、>=

    # 查询员工工资小于平均工资得人
    SELECT * FROM emp WHERE salary < (SELECT AVG(salary) FROM emp);
    

  • 子查询的结果是多行单列的：子查询可以作为条件，使用运算符IN来判断

    # 查询'财务部'和'市场部'所有的员工信息
    SELECT * FROM dept WHERE name = '财务部' OR name = '市场部';
    SELECT * FROM emp WHERE dept_id = 3 OR dept_id = 2;
    -- 一条sql语句
    SELECT * FROM emp WHERE dept_id IN (2, 3);
    SELECT * FROM emp WHERE dept_id IN (SELECT id FROM dept WHERE name IN ('财务部', '市场部'));
    

  • 子查询的结果是多行多列的：子查询可以作为一张虚拟表，进行一个表的查询

    # 查询员工的入职日期是'2011-11-11'之后的员工信息和部门信息
    SELECT * FROM emp WHERE join_date > '2011-11-11';
    SELECT * FROM dept t1, (SELECT * FROM emp WHERE join_date > '2011-11-11') t2 WHERE t1.id = t2.dept_id;
    # 普通内连接
    SELECT * FROM emp t1, dept t2 WHERE t1.dept_id = t2.id AND t1.join_date > '2011-11-11';
    

2.10.3. 练习

# 创建数据库
CREATE DATABASE db3;

# 部门表
CREATE TABLE dept (
    id INT PRIMARY KEY,-- 部门id
    dname VARCHAR(50),-- 部门名称
    loc VARCHAR(50)-- 部门所在地
);

# 添加4个部门
INSERT INTO dept (id, dname, loc) VALUES
(10, '教研部', '北京'),
(20, '学工部', '上海'),
(30, '销售部', '广州'),
(40, '财务部', '深圳');

# 职务表，职务名称，职务描述
CREATE TABLE job (
    id INT PRIMARY KEY,
    jname VARCHAR(20),
    description VARCHAR(50)
);

# 添加4个职务
INSERT INTO job (id, jname, description) VALUES
(1, '董事长', '管理整个公司，接单'),
(2, '经理', '管理部门员工'),
(3, '销售员', '向客人推销产品'),
(4, '文员', '使用办公软件');

# 员工表
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_reg_job_id_fk FOREIGN KEY (dept_id) REFERENCES dept (id)
);

# 添加员工
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);

# 工资等级表
CREATE TABLE salarygrade (
    grade INT PRIMARY KEY,-- 级别
    losalary INT,-- 最低工资
    hisalary INT-- 最高工资
);

# 添加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表    职务名称，职务描述 需要查询job表
	2.条件：emp.job_id  = job.id
*/
SELECT 
    t1.`id` AS 员工编号,-- 员工编号
    t1.`ename` AS 员工姓名,-- 员工姓名
    t1.`salary` AS 工资,-- 工资
    t2.`jname` AS 职务名称,-- 职务名称
    t2.`description` AS 职务描述-- 职务描述
FROM
    emp t1, job t2
WHERE 
    t1.`job_id` = t2.`id`;

# 2.查询员工编号，员工姓名，工资，职务名称，职务描述，部门名称，部门位置
/*
    分析
	1.员工编号，员工姓名，工资 需要查询emp表    职务名称，职务描述 需要查询job表    部门名称，部门位置 需要查询dept表
	2.条件：emp.job_id = job.id  and emp.dept_id = dept.id
*/
SELECT
    t1.`id` AS 员工编号,-- 员工编号
    t1.`ename` AS 员工姓名,-- 员工姓名
    t1.`salary` AS 工资,-- 工资
    t2.`jname` AS 职务名称,-- 职务名称
    t2.`description` AS 职务描述,-- 职务描述
    t3.`dname` AS 部门名称,-- 部门名称
    t3.`loc` AS 部门位置-- 部门位置
FROM
    emp t1, job t2, dept t3
WHERE
    t1.`job_id` = t2.`id` AND t1.`dept_id` = t3.`id`;

# 3，查询员工姓名，工资，工资等级
/*
    分析：
	1.员工姓名，工资 需要查询emp表    工资等级 需要查询salarygrade表
	2.条件：emp.salary >= salarygrade.losalary and emp.salary <= salarygrade.hisalary
		emp.salary between salarygrade.losalary and salarygrade.hisalary
*/
SELECT
    t1.`ename` AS 员工姓名,-- 员工姓名
    t1.`salary` AS 工资,-- 工资
    t2.`grade` AS 工资等级-- 工资等级
FROM
    emp t1, salarygrade t2
WHERE
    t1.`salary` BETWEEN t2.`losalary` AND t2.`hisalary`;

# 4.查询员工姓名，工资，职务名称，职务描述，部门名称，部门位置，工资等级
/*
    分析：
	1.员工姓名，工资 需要查询emp表    职务名称，职务描述 需要查询job表    部门名称，部门位置 需要查询dept表    工资等级 需要查询salarygrade表
	2.条件：emp.jog_id = job.id and emp.dept_id = dept.id and emp.salary between salarygrade.losalary and salarygrade.hisalary
*/
SELECT
    t1.`ename` AS 员工姓名,-- 员工姓名
    t1.`salary` AS 工资,-- 工资
    t2.`jname` AS 职务名称,-- 职务名称
    t2.`description` AS 职务描述,-- 职务描述
    t3.`dname` AS 部门名称,-- 部门名称
    t3.`loc` AS 部门位置,-- 部门位置
    t4.`grade` AS 工资等级-- 工资等级
FROM
    emp t1, job t2, dept t3, salarygrade t4
WHERE
    t1.`job_id` = t2.`id`
    AND t1.`dept_id` = t3.`id`
    AND t1.`salary` BETWEEN t4.`losalary` AND t4.`hisalary`;

# 5.查询出部门编号，部门名称，部门位置，部门人数
/*
    分析：
	1.部门编号，部门名称，部门位置 需要查询dept表    部门人数 需要查询emp表
	2.使用分组查询，按照emp.dept_id完成分组，查询count(id)
	2.使用子查询将第2步的查询结果和dept表进行关联查询
*/
SELECT
    t1.`id` AS 部门编号,-- 部门编号
    t1.`dname` AS 部门名称,-- 部门名称
    t1.`loc` AS 部门位置,-- 部门位置
    t2.`total` AS 部门人数-- 部门人数
FROM
    dept t1,
    (SELECT
    dept_id, COUNT(id) total
	FROM
    emp
	GROUP BY dept_id) t2
WHERE
    t1.`id` = t2.`dept_id`;
    
# 6.查询出所有员工的姓名及其直接上级的姓名，没有领导的员工也要查询
/*
    分析：
	1.员工姓名 需要查询emp表    直接上级的姓名 需要查询emp表
	    emp表的id和mgr是自关联
	2.条件：emp.id = emp.mgr
	3.查询左表的所有数据，和交集数据
	    使用左外连接
*/
SELECT
    t1.`ename` AS 员工姓名,
    t1.`mgr` AS 上级id,
    t2.`id` AS 上级id,
    t2.`ename` AS 上级名称
FROM
    emp t1
LEFT JOIN
    emp t2
ON t1.`mgr` = t2.`id`;

2.11. 事务

2.11.1. 事务的基本介绍

• 概念：如果一个包含多个步骤的业务操作，被事务管理，那么这些操作要么同时成功，要么同时失败。

• 操作：

  • 开启事务：START TRANSACTION;

  • 回滚：ROLLBACK;

  • 提交：COMMIT;

    -- 张三给李四转账500元
    # 0.开启事务
    START TRANSACTION;
    # 1.张三账户-500
    UPDATE account SET balance = balance - 500 WHERE name = 'zhangsan';
    # 2.李四账户+500
    -- 出错了...
    UPDATE account SET balance = balance + 500 WHERE name = 'lisi';
    # 发现出问题了，回滚事务
    ROLLBACK;
    # 3.提交事务
    COMMIT;
    

• 在MySQL数据库中事务默认自动提交

  • 事务提交的两种方式：
    • 自动提交：
      • MySQL就是自动提交的
      • 一条DML（增删改）语句会自动提交一次事务
    • 手动提交：
      • Oracle数据库默认就是手动提交事务的
      • 需要先开启事务（START TRANSACTION），再提交事务（COMMIT）
  • 修改事务的默认提交方式：
    • 查询事务的默认提交方式：SELECT @@AUTOCOMMIT;-- 1代表自动提交，0代表手动提交
    • 修改默认提交方式：SET @@AUTOCOMMIT = 1;

2.11.2. 事务的四大特征【面试题】

• 原子性：是不可分隔的最小操作单位，要么同时成功，要么同时失败
• 持久性：当事务提交或回滚后，数据库会持久化的保存数据
• 隔离性：多个事务之间相互独立
• 一致性：事务操作前后，数据总量不变

2.11.3. 事物的隔离级别【了解】

• 概念：多个事务之间隔离的，相互独立的。但是如果多个事务操作同一批数据，则会引发一些问题，设置不同的隔离级别就可以解决这些问题

• 存在问题：

  • 脏读：一个事务，读取到另一个事务中没有
  • 不可重复读（虚读）：在同一个事务中，两次读取到的数据不一样
  • 幻读：一个事务操作（DML）数据表中所有记录，另一个事务添加了一条数据，则第一个事务查询不到自己的修改

• 隔离级别：

  • READ UNCOMMITTED：读未提交
    • 产生的问题：脏读、不可重复读、幻读
  • READ COMMITTED：读已提交（Oracle默认）
    • 产生的问题：不可重复读、幻读
  • REPEATABLE READ：可重复读（MySQL默认）
    • 产生的问题：幻读
  • SERIALIZABLE：串行化
    • 可以解决所有的问题

  注意：隔离级别从小到大安全性越来越高，但是效率越来越低

• 数据库查询隔离级别：

  • SELECT @@TX_ISOLATION;

• 数据库设置隔离级别：

  • SET GLOBAL TRANSACTION ISOLATION LEVEL 级别字符串;

2.11.4. 事务隔离级别的演示

• 脏读

  SET GLOBAL TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
  START TRANSACTION;
  -- 转账操作SELECT @@TX_ISOLATION;
  UPDATE account SET balance = balance - 500 WHERE id = 1;
  UPDATE account SET balance = balance + 500 WHERE id = 2;
  

• 设置隔离级别为READ UNCOMMITTED

  [外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-g57kN2ok-1572262272864)(images/脏读、不可重复读.png)]

• 设置隔离级别为READ COMMITTED

  [外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-k1e10tFF-1572262272867)(images/不可重复读.png)]

• 设置隔离级别为REPEATABLE READ

  [外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-iIhHwZjc-1572262272869)(images/可重复读.png)]

• 设置隔离级别为SERIALIZABLE

  [外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-zWUEXr6Z-1572262272883)(images/解决所有问题.png)]

2.12. DCL（数据控制语言）【了解】

2.12.1. 管理用户

• 添加用户：

  CREATE USER '用户名'@'主机名' IDENTIFIED BY '密码';
  # 示例：
  CREATE USER 'zhangsan'@'localhost' IDENTIFIED BY 'xiaoer';
  CREATE USER 'lisi'@'%' IDENTIFIED BY 'xiaoer';
  

• 删除用户：

  DROP USER '用户名'@'主机名';
  # 示例：
  DROP USER 'zhangsan'@'localhost';
  DROP USER 'lisi'@'%';
  

• 修改用户：

  UPDATE USER SET PASSWORD =  PASSWORD('新密码') WHERE USER = '用户名';
  SET PASSWORD FOR '用户名'@'主机名' = PASSWORD('新密码');
  # 示例：
  UPDATE USER SET PASSWORD =  PASSWORD('1234') WHERE USER = 'lisi';
  SET PASSWORD FOR 'lisi'@'%' =  PASSWORD('1234');
  

  MySQL中忘记了root用户的密码：

  1. 管理员权限打开cmd–>net stop mysql：停止mysql服务
  2. 使用无验证方式启动mysql服务：mysqld --skip-grant-tables
  3. 重新打开一个cmd窗口，直接输入mysql，使用mysql数据库，修改user表中的root用户密码
  4. 打开任务管理器结束mysqld.exe的进程
  5. 管理员权限打开cmd–>net start mysql：打开mysql服务
  6. 使用新密码登录

• 查询用户：

  # 1.切换到mysql数据库
  USE mysql;
  # 2.查询user表
  SELECT * FROM user;
  

2.12.2. 授权

• 查询权限：

  # 语法：
  SHOW GRANTS FOR '用户名'@'主机名';
  # 示例：
  SHOW GRANTS FOR 'root'@'%';
  

• 授予权限：

  # 语法：
  GRANT 权限列表 ON 数据库名.表名 TO '用户名'@'主机名';
  # 示例：
  GRANT SELECT, DELETE, UPDATE ON db1.account TO 'lisi'@'%';
  GRANT ALL *.* TO 'lisi'@
  

• 撤销权限：

  # 语法：
  REVOKE 权限列表 ON 数据库名.表名 FROM '用户名'@'主机名';
  # 示例：
  REVOKE ALL ON *.* FROM 'lisi'@'%';
  REVOKE SELECT, UPDATE, DELETE ON db1.account FROM 'lisi'@'%';