MySQL

目录

• MySQL
  • • 1. 数据库软件安装
    • 2. 为什么要用数据库
    • 3. 什么是数据库
    • 4. 数据库管理系统 DBMS
    • 5. MySQL 介绍
    • 6. SQL
      • 6.1 SQL 语句概述
      • 6.2 SQL 基本操作
    • 7. 表结构操作
      • 7.1 创建数据表
      • 7.2 查看当前表结构信息
      • 7.3 修改数据表
        • 7.3.1 添加字段
        • 7.3.2 修改字段数据类型
        • 7.3.3 修改字段名称和数据类型
        • 7.3.4 删除指定字段
        • 7.3.5 修改数据表名称
      • 7.4 删除数据表
    • 8. 添加数据 insert
    • 9. 修改数据 update
    • 10. 删除数据 delete
    • 11. 事务操作
    • 12. 查询操作
      • 12.1 基本格式
        • 12.1.1 基本格式 字段 和 表名
        • 12.1.2 基本格式 字段别名
        • 12.1.3 计算字段数据结果
        • 12.1.4 去重 distinct
      • 12.2 排序查询 order by
        • 12.2.1 单一条件排序
        • 12.2.2 多条件排序
      • 12.3 条件查询 where
        • 12.3.1 基本格式
        • 12.3.2 = 等值判断
        • 12.3.3 不等值判断( > < >= <= != <>)
        • 12.3.4 逻辑判断(and &&, or ||, not !)
        • 12.3.5 区间判断(between and)
        • 12.3.6 Null值判断
        • 12.3.7 枚举查询 in
        • 12.3.8 模糊查询 like
        • 12.3.9 分支结构查询
      • 12.4 分组查询
      • 12.5 分组过滤查询
      • 12.6 限定查询
      • 12.7 内置函数数学相关
      • 12.8 字符串函数
      • 12.9 时间函数
    • 13.数据约束
      • 13.1 默认值 default
      • 13.2 非空 not null
      • 13.3 唯一 unique
      • 13.4 主键 primary key
      • 13.5 自增长 auto_increment
      • 13.6 外键约束
      • 13.7 级联约束 cascade
    • 14. 高阶查询
      • 14.1 子查询
        • 14.1.1 基本格式
        • 14.1.2 子查询结果作为条件判断约束
        • 14.1.3 子查询结果作为枚举 in 操作限制
        • 14.1.4 子查询结果作为一张表，从当前子查询结果临时表中完成二次查询
      • 14.2 合并查询【了解】
      • 14.3 表连接查询
        • 14.3.1 基本格式
        • 14.3.2 笛卡尔乘积 【错误演示】
        • 14.3.3 内连接查询 两张表
        • 14.3.4 内连接查询 三张表
        • 14.3.5 内连接查询 四张表
        • 14.3.6 内连接查询 五张表
        • 14.3.7 左外连接查询
        • 14.3.8 右外连接查询

MySQL

1. 数据库软件安装

XXXX项目文档
	1. 环境要求
		JDK 1.8.0_241
		IDEA 2020.2.3
		MySQL 5.7.29
		项目默认编码集 UTF-8
	2. 测试服务器:
		IP地址 XXX.XXX.XXX.XXX
		用户名: 张三
		密码: admin

详细的看一下:
	<<Windows下安装MySQL5.7解压缩版>>

程序员必须掌握的能力:
	1. 必要的文档阅读能力
	2. 必要的动手能力
	3. 必要的搜索能力和解决问题能力

2. 为什么要用数据库

	Java程序运行过程中，数据存储目前依赖的是变量，对象，集合，数组，双边队列Map, 在程序运行过程中，内存临时存储，如果需要持久化数据存储方式，目前可以采用的方式是【IO流】文件保存。
	文件存储目前没有标准化的格式，虽然现在有设定的一些规范，但是不够用！！！后期会学习一些标准化规范化数据文件，配置文件，XML文件，JSON文件，Properties属性文件。但是数据安全无法保证，数据存储读取操作规范性不够，方法不完善，配套不完全。文件操作需要整个文件在内存中存在，才可以完成完整的读取和写入操作，对于内存的使用率不友好，同时资源浪费。
	
还有其他问题:
	1. 文件保存数据没有数据类型区分，有且只有字符串。
	2. 数据存储量较小，有一定的限制。
	3. 安全问题，限制手段较少。
	4. 文件操作没有备份机制，回滚机制，事务操作

3. 什么是数据库

	数据库是一个按照特定是结构，提供数据存储，查询，修改，删除一系列操作的专业数据管理软件。配套完善，数据备份，数据恢复，回滚机制，事务操作...数据类型也可以明确约束，操作更加方便。
	数据库分类:
		1. 关系型数据库
			MySQL Oracle SQL Server DB2
			通过表结构数据形式来管理存储数据 ==> Excel 表格

		2. 非关系型数据库
			MongoDB Redis[重点]
			使用哈希表结构进行数据存储，数据形式 Key-Value 键值对模型

4. 数据库管理系统 DBMS

数据库管理系统:
	Database Management System
	DBMS
	操作，管理数据库大型软件，用于管理，创建，使用和查询数据操作。

目前市面上流行的数据库
	Oracle
		甲骨文公司主要产品！！！核心产品
		稳定，安全，复杂，容量大。大型项目都会使用 Oracle 数据库 阿里巴巴 腾讯 抖音
		【贵】
	DB2
		IBM公司，目前不多见了
	SQL Server
		MS(微软/巨硬) 公司，关系型数据库，在国内还是有很大市场的
	SQLite
		轻量级数据库，原码 1W C语言。大量用于手机通讯录！！！

5. MySQL 介绍

MySQL默认编码集是 Latin1 ==> 西欧，MySQL起源于西欧，瑞典公司研发的数据库软件 AB 公司。
AB公司研发的 MySQL 数据库在刚刚起步阶段，免费开源。
	【社区】
    	社区的大牛，各方力量开始融入MySQL，各式各样的插件，各种各样辅助。
    	InnoDB 跨时代的存储引擎。
    MySQL性能还是非常不错的！！！
    	大中小企业使用 MySQL 数据都是完全 OK 的。
    	美国小鹰号常规动力航母数据管理使用的就是 MySQL 数据库
    	
    MySQL 隶属于 Oracle 公司，Oracle 目前维护的版本 5.5 5.6 5.7 8.0
    8.0 性能 5.7 两倍！！！
    实际开发中，需要考虑项目的稳定性，兼容性，普适性

6. SQL

6.1 SQL 语句概述

	SQL语句是数据库通用操作语句(查询，创建，删除，修改...)，不管是 MySQL， Oracle， SQL Server 都是支持 SQL 语句。数据库语句分类
		C Create
		R Read 【重点 select 查询】
		U Update
		D Delete

核心:
	数据库设计
	数据库查询语句

6.2 SQL 基本操作

# 数据库连接操作
# cmd > 命令在 终端/命令提示符操作
cmd > mysql -hlocalhost -uroot -p123456 
# mysql 明确告知当前操作连接的数据库是哪一个
# -h host 数据库所在 ip地址，域名，主机名，后期会连接远端/服务器端数据库
# localhost 本地域名/主机名 127.0.0.1 通常情况下如果连接的是本机数据库，可以省略 localhost
# -uroot -u user 用户 root 是用户名
# -p password 密码 对应当前用户的密码

# 常用连接方式
cmd > mysql -uroot -p
Enter password: ******

# 展示当前数据库服务器中有多少个数据库
show databases;

# 创建数据库
# create database dbName;
create database javaee_2203;
# Query OK, 1 row affected (0.00 sec)
# Query OK, 当前 SQL 语句目标执行 OK，1 row affected 当前数据库有一行受到影响。

# 展示创建数据库过程中一些信息
show create database javaee_2203;
# +-------------+----------------------------------------------------------------------+
# | Database    | Create Database                                                      |
# +-------------+----------------------------------------------------------------------+
# | javaee_2203 | CREATE DATABASE `javaee_2203` /*!40100 DEFAULT CHARACTER SET utf8 */ |
# +-------------+----------------------------------------------------------------------+
# DEFAULT CHARACTER SET utf8 当前 javaee_2203 数据库对应编码集为 UTF-8

# 删除数据库【慎用，而且一般情况下，你没有权限删除】
drop database javaee_2203;

# 创建数据库过程中可以选择编码集
create database javaee_2301 character set gbk;
create database javaee_2302 character set latin1;

# 修改已存在数据库对应编码集
alter database javaee_2301 character set utf8; 

# 选择使用哪一个数据库键操作，后期的针对于【数据表的操作】内容是在指定数据库
use javaee_2203;

# 查询当前数据库服务器操作的数据库是哪一个
select database();

7. 表结构操作

7.1 创建数据表

-- 基本格式
create table tbName
(
    字段名1 数据类型 约束操作,
    字段名2 数据类型 约束操作,
    字段名3 数据类型 约束操作
);

use javaee_2203;
create table person 
(
    # 字段(field)名为 id，字段对应的数据类型为 int 类型 对应 Java中的 int 类型
    id int,
    # 字段(field)名为 name，数据类型是 varchar 类型。MySQL 数据库可变长字符串，最大允许的字符个数为32
    # 字符个数为 5，MySQL 给予当前数据分配的字符个数为 5。对应 Java 中的 String 类型
    name varchar(32),
    # 字段(field)名为 age，数据类型为 int 类型
    age int,
    # 字段(field)名为 gender，数据类型为 tinyint ==> Java 中 boolean or byte
    gender tinyint
);

7.2 查看当前表结构信息

# 当前操作的数据库中有多少张数据表
show tables;
# +-----------------------+
# | Tables_in_javaee_2203 |
# +-----------------------+
# | person                |
# +-----------------------+

# 查看指定表结构信息
desc tabName;
desc person;
# +--------+-------------+------+-----+---------+-------+
# | Field  | Type        | Null | Key | Default | Extra |
# +--------+-------------+------+-----+---------+-------+
# | id     | int(11)     | YES  |     | NULL    |       |
# | name   | varchar(32) | YES  |     | NULL    |       |
# | age    | int(11)     | YES  |     | NULL    |       |
# | gender | tinyint(4)  | YES  |     | NULL    |       |
# +--------+-------------+------+-----+---------+-------+

7.3 修改数据表

7.3.1 添加字段

# 默认在整个数据表的最后
alter table person add information text;

# +-------------+-------------+------+-----+---------+-------+
# | Field       | Type        | Null | Key | Default | Extra |
# +-------------+-------------+------+-----+---------+-------+
# | id          | int(11)     | YES  |     | NULL    |       |
# | name        | varchar(32) | YES  |     | NULL    |       |
# | age         | int(11)     | YES  |     | NULL    |       |
# | gender      | tinyint(4)  | YES  |     | NULL    |       |
# | information | text        | YES  |     | NULL    |       |
# +-------------+-------------+------+-----+---------+-------+

# 可以指定在某一个字段之后添加新的字段
# 在 gender 字段之后，添加 salary 字段，要求数据类型为 float(8, 2)
# float(8, 2) 要求数据的总位数最大为 8 位，且保留两位小数 -999999.99 ~ 999999.99
alter table person add salary float(8, 2) after gender;

# +-------------+-------------+------+-----+---------+-------+
# | Field       | Type        | Null | Key | Default | Extra |
# +-------------+-------------+------+-----+---------+-------+
# | id          | int(11)     | YES  |     | NULL    |       |
# | name        | varchar(32) | YES  |     | NULL    |       |
# | age         | int(11)     | YES  |     | NULL    |       |
# | gender      | tinyint(4)  | YES  |     | NULL    |       |
# | salary      | float(8,2)  | YES  |     | NULL    |       |
# | information | text        | YES  |     | NULL    |       |
# +-------------+-------------+------+-----+---------+-------+

7.3.2 修改字段数据类型

# 修改 person 表中的字段 name 数据类型为 varchar(256)
alter table person modify name varchar(256);

# | name        | varchar(32) 
# 仅修改数据类型
# | name        | varchar(256)

7.3.3 修改字段名称和数据类型

# 修改 person 表中 information 字段改为 description 同时数据类型改为  varchar(256)
alter table person change information description varchar(256);

# | information | text
# 修改字段名和数据类型之后
# | description | varchar(256)

7.3.4 删除指定字段

# 删除 person 表中 description 字段
alter table person drop description;

7.3.5 修改数据表名称

# 重命名数据表名 将原本的 person 表改为 people 表
rename table person to people;

rename table people to person;

7.4 删除数据表

# 删除指定名称的数据表
drop table tbName;
drop table person;

8. 添加数据 insert

# +--------+--------------+------+-----+---------+-------+
# | Field  | Type         | Null | Key | Default | Extra |
# +--------+--------------+------+-----+---------+-------+
# | id     | int(11)      | YES  |     | NULL    |       |
# | name   | varchar(256) | YES  |     | NULL    |       |
# | age    | int(11)      | YES  |     | NULL    |       |
# | gender | tinyint(4)   | YES  |     | NULL    |       |
# | salary | float(8,2)   | YES  |     | NULL    |       |
# +--------+--------------+------+-----+---------+-------+

# 1. 规规矩矩，有啥是啥
# SQL 语句中字符串使用的都是 英文单引号 '' 包含!!! 
insert into 
person(id, name, age, gender, salary) 
values(1, '苟磊', 16, 0, 10000.55);

# 2. 指哪打哪
insert into 
person(id, name, age)
values(2, '王乾', 66);

# 3. 规规矩矩偷懒，可以省略 into 可以省略表名之后所有字段声明，但是必须按照表结构字段顺序，数据类型提供对应的
# 具体数据。
insert
person
values(3, '王迟平', 76, 0, -500);

insert
person
values('王迟平', 76, 0);
# ERROR 1136 (21S01): Column count doesn't match value count at row 1
# 错误情况，给予当前数据表的数据情况和表结构要求的数据数量不匹配

insert into person values(4, '栋哥', 36, 0, 2000); 
insert into person values(5, '杨仔', 30, 0, 1000); 
insert into person values(6, '熊大', 60, 0, 5000); 
insert into person values(7, '博哥', 35, 0, 9000); 
insert into person values(8, '高老师', 35, 0, 5000); 
insert into person values(9, '彭于晏', 36, 0, 999000); 
insert into person values(10, '吴彦祖', 40, 0, 100000); 
insert into person values(11, '光头强', 55, 0, -5);
insert into person values(12, '王冰冰', 18, 1, 19000); 
insert into person values(13, '李沁', 21, 1, 129000); 
insert into person values(14, '鞠婧祎', 22, 1, 4000); 
insert into person values(15, '高圆圆', 35, 1, 6000); 
insert into person values(16, '张天爱', 30, 1, 19000); 
insert into person values(17, '迪丽热巴', 28, 1, 91000); 
insert into person values(18, '古力娜扎', 29, 1, 92000); 
insert into person values(19, '欧阳娜娜', 25, 1, 9999); 
insert into person values(20, '周淑怡', 29, 1, 50);

9. 修改数据 update

# 没有任何约束的修改操作有可能会所有的数据行全部修改。导致数据错误！！！
update person set name = '王迟平';
# Query OK, 19 rows affected (0.00 sec)
# Rows matched: 20  Changed: 19  Warnings: 0
# 执行操作成功，19行受到了影响
# 匹配行数:20 更新/更换:19 警告:0

# 有效修改，约束条件修改
# 修改数据表 person 中 id 为 3 的数据行其中的 name 字段数据改为'王总迟平' 
update person set name = '王总迟平' where id = 3;
# Query OK, 1 row affected (0.00 sec)
# Rows matched: 1  Changed: 1  Warnings: 0
# 执行操作成功，1行受到了影响
# 匹配行数:1 更新/更换:1 警告:0

10. 删除数据 delete

# 未加入任何的条件限制，当表所有数据行被删除
delete from person;
# Query OK, 20 rows affected (0.00 sec)
# 执行操作成功，20 行受到了影响

# 有效修改，约束条件删除
delete from person where id = 10;
# Query OK, 1 row affected (0.00 sec)
# 执行操作成功，1 行受到了影响

11. 事务操作

# 关闭自动提交 SQL 语句，开启事务操作
set autocommit = 0;

# 开启自动提交 SQL 语句，关闭事务操作
set autocommit = 1; # MySQL 数据库默认状态

# 从 set autocommit = 0; 或者 commit 操作之后 到 rollback 所有 SQL 语句操作撤销。【回滚】
# 【不会终止事务 不会开启自动提交】
rollback;

# 从 set autocommit = 0; 或者 rollback 操作之后 到 commit 所有 SQL 语句确定执行，手动提交。
# 【不会终止事务 不会开启自动提交】
commit;

12. 查询操作

12.1 基本格式

select 查询内容 -- 明确当前查询目标字段内容
from
从哪里查; -- 数据来自哪一张表

12.1.1 基本格式 字段 和 表名

# * 表示任意字段， from person 会从整个 person 中找出所有数据行，所有字段名称，没有任何的限制
# 数据量小还行，但是如果是一个特别恐怖的数据表。。。。效率会极低。 
select * from person;

# 指定字段名和数据表名查询目标数据
select id, name from person;
select id, name, salary from person;
select name, gender, salary from person;

12.1.2 基本格式 字段别名

# 在字段之后使用 as 后面是当前字段别名 使用不多
select name as '姓名', gender as '性别', salary as '工资' from person;

12.1.3 计算字段数据结果

# 取出某一个字段对应的数据通过计算操作得到其他内容
select name as '姓名', gender as '性别', salary * 12 as '年薪' from person;

12.1.4 去重 distinct

# 相同数据仅保留一个，去重查询
select distinct age from person;

12.2 排序查询 order by

排序规则	效果
asc	升序排序【缺省属性】
desc	降序排序

12.2.1 单一条件排序

# 查询目标字段为 员工 id号，名字，工资，并且按照工资进行【升序】排序
select employee_id, first_name, salary
from t_employees
order by salary asc;

select employee_id, first_name, salary
from t_employees
order by salary; # asc 可以省略， order by 默认就是按照指定的字段进行升序排序


# 查询目标字段为 员工 id号，名字，工资，并且按照工资进行【降序】排序
select employee_id, first_name, salary
from t_employees
order by salary desc;

12.2.2 多条件排序

# 查询目标字段为 员工 id号，名字，工资，首先按照工资【升序】排序，如果工资一致按照工号【降序】排序
select employee_id, first_name, salary
from t_employees
order by salary, employee_id desc;

12.3 条件查询 where

12.3.1 基本格式

select fieldName # 查询目标的字段名称
from tbName # 明确字段对应的数据表
where condition; # 查询所约束的条件，条件是一个 boolean 类型 true or false

12.3.2 = 等值判断

# Java 中使用的 == or equals 方法来完成等值判断
# 数据库中使用的 = 在条件约束中来完成等值判断

# 找出员工 id，名字，工资，要求工资 = 10000 元
select employee_id, first_name, salary -- 明确字段
from t_employees -- 字段对应的数据表
where salary = 10000; -- 限制的 = 判断条件

# 找出员工 id，名字, 工资 要求 manager_id = 100
# 查询的目标字段是没有 manager_id 字段，但是在查询条件约束中可以使用 manager_id
# 目前查询对应【数据源】中有对应字段存在的。
select employee_id, first_name, salary
from t_employees
where manager_id = 100;

select employee_id, first_name, salary, manager_id
from t_employees
where manager_id = 100;

12.3.3 不等值判断( > < >= <= != <>)

# 【补充说明】
# <> SQL 当中的不等于，早期用法，后期允许使用 !=
# 阅读性 != 更好
# 如果是考虑老版本的 SQL ，<> 移植性更好

# 查询员工 id，名字，部门id号， 要求工资 大于 10000   rows:15
select employee_id, first_name, department_id
from t_employees
where salary > 10000;

# 查询员工 id，名字，部门id号， 要求工资 小于 3000  rows:24
select employee_id, first_name, department_id
from t_employees
where salary < 3000;

# 查询员工 id，名字，部门id号， 要求工资 大于等于 10000  rows:19
select employee_id, first_name, department_id
from t_employees
where salary >= 10000;

# 查询员工 id，名字，部门id号， 要求工资 小于等于 3000  rows:26
select employee_id, first_name, department_id
from t_employees
where salary <= 3000;

# 查询员工 id，名字，部门id号， 要求工资 不等于 10000
select employee_id, first_name, department_id
from t_employees
where salary != 10000; # 阅读性更好，和开发语言要求一致 Java C C++ PHP JS

select employee_id, first_name, department_id
from t_employees
where salary <> 10000; # 移植性更好，适配各版本 SQL

12.3.4 逻辑判断(and &&, or ||, not !)

# 查询员工 id，名字，部门id号，salary 要求部门id 为80 并且工资大于 10000
select employee_id, first_name, department_id, salary
from t_employees
where department_id = 80 and salary > 10000;

# 查询员工 id，名字，部门id号，salary 要求部门id 为80 并且工资大于 10000
select employee_id, first_name, department_id, salary
from t_employees
where department_id = 80 && salary > 10000;

# 查询员工 id，名字，部门id号，salary 要求部门id 为80 或者 工资大于 10000
select employee_id, first_name, department_id, salary
from t_employees
where department_id = 80 or salary > 10000;

# 查询员工 id，名字，部门id号，salary 要求部门id 为80 或者 工资大于 10000
select employee_id, first_name, department_id, salary
from t_employees
where department_id = 80 || salary > 10000;

# 查询员工 id，名字，部门id号，salary 要求部门id 不为 80
select employee_id, first_name, department_id, salary
from t_employees
where department_id != 80; # 正确

# select employee_id, first_name, department_id, salary
# from t_employees
# where department_id not = 80; # 语法报错，不允许这样完成

select employee_id, first_name, department_id, salary
from t_employees
where not department_id = 80; # 正确

select employee_id, first_name, department_id, salary
from t_employees
where ! department_id = 80; # 语法不报错，但是没有结果 not 和 ! 不能直接替换

12.3.5 区间判断(between and)

# 查询员工id，名字，工资，要求工资在 5000 ~ 10000 之间

# where + and 方式 ，逻辑表达式拼接
select employee_id, first_name, salary
from t_employees
where salary >= 5000 && salary <= 10000;

# between 5000 and 10000; 完成一个闭区间限制 5000 <= salary <= 10000;
#【要求】  between 5000 and 10000; 必须前小后大
select employee_id, first_name, salary
from t_employees
where salary between 5000 and 10000;

# where + and 方式 ，逻辑表达式拼接
select employee_id, first_name, salary
from t_employees
where salary <= 10000 && salary >= 5000;

# between 5000 and 10000; 完成一个闭区间限制 5000 <= salary <= 10000;
# 如果 between and 限制条件，前大后小 无数据产出
select employee_id, first_name, salary
from t_employees
where salary between 10000 and 5000;

12.3.6 Null值判断

# is null 判断指定字段数据对应的内容是否为 null ，如果是返回 true 如果不是返回 false
# is not null 判断指定字段数据对应的是否不为 null，如果不是 返回 true 如果是 返回 false

# 查询员工id，名字，提成，工资，找出提成不为 null 的
select employee_id, first_name, commission_pct, salary
from t_employees
where commission_pct is not null;


# 查询员工id，名字，提成，工资，找出提成为 null 的
select employee_id, first_name, commission_pct, salary
from t_employees
where commission_pct is null;

12.3.7 枚举查询 in

# in (数据值1, 数据值2) 根据指定的数据值1, 数据值2 匹配对应的数据行内容
# not in (数据值1, 数据值2) 根据指定的数据值1, 数据值2 找出不匹配对应的数据行内容

# 查询员工id，名字，工资，要求部门id 为 80 和 50
# in 查询效率偏低，在 SQL 优化过程中，会考虑将 in 替换成其他语句。
select employee_id, first_name, salary
from t_employees
where department_id in (50, 80);

select employee_id, first_name, salary
from t_employees
where department_id = 50 || department_id = 80;

# 查询员工id，名字，工资，要求部门id 不为 80 和 50
select employee_id, first_name, salary
from t_employees
where department_id not in (50, 80);

select employee_id, first_name, salary
from t_employees
where department_id != 50 && department_id != 80;

select employee_id, first_name, salary
from t_employees
where not (department_id = 50 || department_id = 80);

12.3.8 模糊查询 like

# % 匹配任意个数字符
#       案例 %a% 要求当前查询匹配规则中必须有 a 字符  a 之前不限制 a 之后也不限制
# _ 表示匹配一个字符
#       案例 _a__ 要求当前查询匹配规则中 a 之前必须有一个字符，a之后必须有两个字符

# 查询员工 id ，名字，工资，要求 名字中必须包含 V 【不区分大小写】
select employee_id, first_name, salary
from t_employees
where first_name like '%V%';

# 查询员工 id ，名字，工资，要求 名字中要求 V 开头【不区分大小写】
select employee_id, first_name, salary
from t_employees
where first_name like 'V%';

# 查询员工 id ，名字，工资，要求 名字中要求 n 结尾【不区分大小写】
select employee_id, first_name, salary
from t_employees
where first_name like '%n';

# 查询员工 id ，名字，工资，要求 名字中要求 a 在第二个字符
select employee_id, first_name, salary
from t_employees
where first_name like '_a%';

# 查询员工 id ，名字，工资，要求 名字中要求 a 在第二个字符，a 之后有 4 个字符 字符总长在 6 个
select employee_id, first_name, salary
from t_employees
where first_name like '_a____';

12.3.9 分支结构查询

# case
#     when condition1 then ret1
#     when condition2 then ret2
#     when condition3 then ret3
# end as '别名'
# 分支结构从 case 开始，到 end 结束，满足一个条件对应一个结果，类似于 Java 中 switch case

# 查询员工id，名字和工资等级
# >= 10000 A
# >= 8000 B
# >= 6000 C
# >= 4000 D
# else E
# 类似于 switch case 或者是 if - else if 结构，条件罗列需要注意细节和约束
select employee_id,
       first_name,
       salary,
       case
           when salary >= 10000 then 'A'
           when salary >= 8000 then 'B'
           when salary >= 6000 then 'C'
           when salary >= 4000 then 'D'
           else 'E'
           end as 'level'
from t_employees;

# 不推荐方式
select employee_id,
       first_name,
       salary,
       case
           when salary < 4000 then 'E'
           when salary >= 4000 and salary < 6000 then 'D'
           when salary >= 6000 and salary < 8000 then 'C'
           when salary >= 8000 and salary < 10000 then 'B'
           when salary >= 10000 then 'A'
           end as 'level'
from t_employees;

12.4 分组查询

# select fieldName
# from tbName
# where condition
# group by 分组条件;

# 查询每一个部门有多少人
# 1. 分组条件是 部门编号 例如 按照 30 分组，
# 2. 分组之后可以计数 部门员工的 id 号，得到部门人数
select department_id, count(employee_id)
from t_employees
group by department_id;

# 查询每一个部门有平均工资
# 1. 分组条件是 部门编号 例如 按照 30 分组，
# 2. 分组之后按照部门进行 salary 平均数计算
select department_id, avg(salary)
from t_employees
group by department_id;

# 查询每一部门，每一个岗位有多少人
# 1. 首先按照部门分组
# 2. 按照岗位分组
# 3. count 计数员工 id
select department_id, job_id, count(employee_id)
from t_employees
group by department_id, job_id;

# [42000][1055] Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated
# column 'company.t_employees.department_id' which is not functionally dependent on columns in GROUP BY clause;
# this is incompatible with sql_mode=only_full_group_by
# 分组对应的字段必须在查询目标字段中存在，如果没有执行报错！！！
# 分组数据源是【查询字段】
# 分组操作是在查询操作之后完成的！！！
select department_id
from t_employees
group by job_id;

12.5 分组过滤查询

# select fieldName
# from tbName
# where condition
# group by 分组条件
# having 分组之后结果限制/条件;
# having 是在分组之后使用的条件约束。

# 查询部门id 在 100, 50. 80的最高工资
# 1. 需要按照部门 ID 分组
# 2. 最高工资
# 3. 分组之后需要将部门限制在 100 50 80
select department_id, max(salary)
from t_employees
group by department_id -- 部门分组
having department_id in (100, 50, 80); -- 分组之后的条件 限制在部门 id 100 50 80

# 查询部门人数大于 20 的部门
# 1. 按照部门分组
# 2. 统计人数
# 3. 限制条件为 人数大于 20 的
select department_id, count(employee_id)
from t_employees
group by department_id -- 部门分组
having count(employee_id) > 20; -- 限制条件为部门人数 大于 20

# 查询部门员工工资高于 5000 的，并且部门人数大于 20 的部门数据
# 1. 限制工资大于 5000
# 2. 按照部门分组
# 3. 统计各部门人数
# 4. 分组之后的部门人数 大于 20 作为约束条件
select department_id, count(employee_id)
from t_employees
where salary > 5000 -- 限制查询数据结果工资在 5000 以上 可以有效降低分组数据行数
group by department_id -- 按照部门分组
having count(employee_id) > 20 -- 根据部门的人数限制部门人数在 20 以上;

12.6 限定查询

# limit
# select fieldName
# from tbName
# limit 限制;

# 查询数据表中前十的员工信息，员工ID和名字
select employee_id, first_name
from t_employees
limit 10;

# 查询数据中首先按照工资降序排序，找出工资前十的员工信息，员工ID，名字和工资
# 1. 首先需要进行排序操作，按照工资降序
# 2. 排序之后的结果，我们找出前十员工
select employee_id, first_name, salary
from t_employees
order by salary desc
limit 10;

# 查询数据表中的员工ID，名字，从下标 5 开始查询，查询的数据行数为 10 行
select employee_id, first_name
from t_employees
limit 5, 10; -- limit offset, length/count

# 显示从 0 下标开始，10条数据
select employee_id, first_name
from t_employees
limit 0, 10;

# 显示从 10 下标开始，10条数据
select employee_id, first_name
from t_employees
limit 10, 10;

# 显示从 20 下标开始，10条数据
select employee_id, first_name
from t_employees
limit 20, 10;

# pageCount 页码 1 2 3 4 5 or 0 1 2 3 4
# rowsCount 每一页数据行数 10 15 20
# 公式
# 如果 pageCount 从 1 开始
# limit (pageCount - 1) * rowsCount, rowsCount

12.7 内置函数数学相关

方法	功能描述
sum()	查询数据行指定字段所有数据之和
avg();	查询数据行指定字段所有数据的平均数
count();	查询数据行指定字段所有数据个数
max();	查询数据行指定字段所有数据最大值
min();	查询数据行指定字段所有数据最小值

# count
# 查询各部门员工人数
select department_id, count(employee_id)
from t_employees
group by department_id;

# 找出部门为 60 的员工人数
select department_id, count(employee_id)
from t_employees
where department_id = 60;

# 全公司人数
select count(employee_id)
from t_employees;

# avg
# 全公司的平均工资
select avg(salary)
from t_employees;

# 找出 manager_id = 100 的员工平均工资
select avg(salary)
from t_employees
where manager_id = 100;

# 找出各个部门的平均工资
select department_id, avg(salary)
from t_employees
group by department_id;

# sum
# 查询各部门工资支出
select department_id as '部门编号', sum(salary) * 12 as '年支出'
from t_employees
group by department_id;

# 公司月人力支出
select sum(salary)
from t_employees;

# max
# 公司中工资最高的员工信息
select employee_id, first_name, salary
from t_employees
where salary = (
    select max(salary)
    from t_employees
);

# min
# 公司中工资最低的员工信息
select employee_id, first_name, salary
from t_employees
where salary = (
    select min(salary)
    from t_employees
);

12.8 字符串函数

方法	功能描述
concat(str1, str2, str3...)	拼接多个字符串
insert(str, pos, len, newStr)	在指定字符串 str，指定下标位置 pos，长度限制 len，插入newStr
lower(str)	大写转小写
upper(str)	小写转大写
substring(str, pos, len)	指定字符串 str，指定下标位置 pos，长度限制 len 获取字符串

select concat('北平在不在? ', '平曰: 不在，你能咋地  ', '宿舍小伙伴: 昨天晚上喝假酒了吧...');
select insert('0123456789', 2, 5, '北平'); # SQL 中下标从 1 开始， 5 相当于被替换的字符串长度
select upper('abcdefgADFSDF');
select lower('ABCDEFGfdafsdadfs');
select substring('0123456789', 2, 5);

12.9 时间函数

方法	功能描述
sysdate()	当前系统时间(年月日时分秒)
current_timestamp();	当前系统时间(年月日时分秒)
curdate();	当前日期(年月日)
curtime();	当前时间(时分秒)
month()	指定日期是这一年的第几月
week()	指定日期是这一年的第几周
hour();	指定日期是这一天的第几个小时
minute();	指定日期是这一小时的第几分钟
second();	指定日期是这一分钟的第几秒

select sysdate();
select current_timestamp();
select current_timestamp;

select curdate();
select curtime();

select month(sysdate());
select week(sysdate());
select hour(sysdate());
select minute(sysdate());
select second(sysdate());

13.数据约束

13.1 默认值 default

# default 修饰的字段，在没有给予对应字段数据情况下，会采用默认值方式进行赋值操作。
create table person1
(
    id      int,
    name    varchar(32),
    country varchar(32) default 'PRC'
);
# +---------+-------------+------+-----+---------+-------+
# | Field   | Type        | Null | Key | Default | Extra |
# +---------+-------------+------+-----+---------+-------+
# | id      | int(11)     | YES  |     | NULL    |       |
# | name    | varchar(32) | YES  |     | NULL    |       |
# | country | varchar(32) | YES  |     | PRC     |       |
# +---------+-------------+------+-----+---------+-------+
# 插入数据，给予 country 字段赋值数据内容。default 约束的默认值，可以在插入数据操作中给予数据赋值
insert into person1
values (1, '周董', '中国');

# 插入数据，country 字段没有提供对应数据
insert into person1
values (2, '北平');
# [21S01][1136] Column count doesn't match value count at row 1
# 虽然 字段有默认 default 修饰，在使用省略指定字段名称插入数据过程中，缺少字段，SQL 报错。

# 插入数据，插入数据内容通过指定字段名称约束，country 选择默认值赋值方式
insert into person1(id, name)
VALUES (2, '北平');

# 插入数据，country 字段赋值 null
insert into person1
VALUES (3, '王乾', null);

select *
from person1;

13.2 非空 not null

# 约束字段不允许为 null
# 例如注册平台要求手机号必须有，健康码必须有身份证号
create table person2
(
    id      int,
    name    varchar(32) not null,     -- name 非空 不允许为 null
    country varchar(32) default 'PRC' -- 默认值
);
# +---------+-------------+------+-----+---------+-------+
# | Field   | Type        | Null | Key | Default | Extra |
# +---------+-------------+------+-----+---------+-------+
# | id      | int(11)     | YES  |     | NULL    |       |
# | name    | varchar(32) | NO   |     | NULL    |       |
# | country | varchar(32) | YES  |     | PRC     |       |
# +---------+-------------+------+-----+---------+-------+

# 插入数据
insert into person2(id, name)
values (1, '北平');

# 插入数据，name 字段不赋值
insert into person2(id)
values (2);
# [HY000][1364] Field 'name' doesn't have a default value
# name 字段没有默认值

# 插入数据，name 字段赋值 null
insert into person2(id, name)
values (2, null);
#  [23000][1048] Column 'name' cannot be null
# name 不可以为 null

select *
from person2;

13.3 唯一 unique

# 约束字段表内不可以重复
# 性别可以用 unique ??? age 是否可以用唯一 unique???
# 都不可以
# id 可以使用 unique 唯一??? Ok
create table person3
(
    id      int unique,                         -- 唯一约束
    name    varchar(32)               not null, -- 非空约束
    country varchar(32) default 'PRC' not null  -- 非空约束，同时有默认值约束
);

# +---------+-------------+------+-----+---------+-------+
# | Field   | Type        | Null | Key | Default | Extra |
# +---------+-------------+------+-----+---------+-------+
# | id      | int(11)     | YES  | UNI | NULL    |       |
# | name    | varchar(32) | NO   |     | NULL    |       |
# | country | varchar(32) | NO   |     | PRC     |       |
# +---------+-------------+------+-----+---------+-------+

# 插入数据测试
insert into person3(id, name)
values (1, '北平');

# 插入数据，id 字段对应数据为 1 会因为之前有字段对应数据存在，无法添加。
insert into person3(id, name)
values (1, '王乾');
# [23000][1062] Duplicate entry '1' for key 'id'
# ID 是唯一修饰，1 在字段 id 对应的数据中已存在。不可以重复

# 插入数据，给予 id 字段 null 赋值
# 仅使用唯一约束的字段，允许字段对应数据为 null
insert into person3(id, name)
VALUES (null, '王乾');

# 仅使用唯一约束的字段，字段数据 null 不作为唯一约束校验数据
insert into person3(id, name)
VALUES (null, '王某');

select *
from person3;

13.4 主键 primary key

# 唯一 + 非空
# 主键一般约束的都是数据行唯一性质数据，例如 id 订单号，支付流水号，物流编号
create table person4
(
    id      int primary key,                    -- 主键约束
    name    varchar(32)               not null, -- 非空约束
    country varchar(32) default 'PRC' not null  -- 非空约束，同时有默认值约束
);
# +---------+-------------+------+-----+---------+-------+
# | Field   | Type        | Null | Key | Default | Extra |
# +---------+-------------+------+-----+---------+-------+
# | id      | int(11)     | NO   | PRI | NULL    |       |
# | name    | varchar(32) | NO   |     | NULL    |       |
# | country | varchar(32) | NO   |     | PRC     |       |
# +---------+-------------+------+-----+---------+-------+

# 插入数据
insert into person4(id, name)
values (1, '王某平');

# 插入数据 id 和之前的数据行重复
insert into person4(id, name)
values (1, '王某');
# [23000][1062] Duplicate entry '1' for key 'PRIMARY'
# id 为 primary key 主键修饰，不允许数据重复

# 插入数据，id 字段给予数据内容为 null
insert into person4(id, name)
values (null, '王某');
# [23000][1048] Column 'id' cannot be null
#  id 字段不可以为 null， primary key 主键修饰

13.5 自增长 auto_increment

# 1. 要求约束的数据必须是一个键(key) Unique Primary key
# 2. 自增长数据默认从 1 开始

create table person5
(
    id      int primary key auto_increment,     -- 主键约束 自增长约束
    name    varchar(32)               not null, -- 非空约束
    country varchar(32) default 'PRC' not null  -- 非空约束，同时有默认值约束
);
# +---------+-------------+------+-----+---------+----------------+
# | Field   | Type        | Null | Key | Default | Extra          |
# +---------+-------------+------+-----+---------+----------------+
# | id      | int(11)     | NO   | PRI | NULL    | auto_increment |
# | name    | varchar(32) | NO   |     | NULL    |                |
# | country | varchar(32) | NO   |     | PRC     |                |
# +---------+-------------+------+-----+---------+----------------+

# 插入数据 id 字段在不赋值的情况下，由自增长赋值
insert into person5(name)
values ('郭德纲');
insert into person5(name)
values ('于谦');
insert into person5(name)
values ('高峰');
insert into person5(name)
values ('栾云平');

# 指定 id 添加 id 重复
insert into person5(id, name)
values (3, '岳云鹏');
# [23000][1062] Duplicate entry '3' for key 'PRIMARY'
# id primary key 主键修饰，不可以重复

# 指定 id 添加 自增长会受到 指定 字段数据影响，如果 字段数据 >  自增长数据， 自增长数据直接赋值为当前字段
insert into person5(id, name)
values (6, '孙越');
# 岳云鹏 id = 7
insert into person5(name)
values ('岳云鹏');

# 该数据是否可以存入数据表???
# 可以存入，主键没有重复，也非 null，可以使用！！！
insert into person5(id, name)
values (5, '郭麒麟');

# 张云雷 id 为 8
insert into person5(name)
values ('张云雷');

# 闫云达 id 为 9
insert into person5(name)
values ('闫云达');

delete
from person5
where id = 9;

# 孔云龙 id 为 10
# delete 操作不影响累加继续，累加计数器不受到 delete 操作影响。
insert into person5(name)
values ('孔云龙');

# 朱云峰 id =  9
# 可以存入，主键没有重复，也非 null，可以使用！！！
insert into person5(id, name)
values (9, '朱云峰');

# 陶云圣 id = 11
insert into person5(name)
values ('陶云圣');

# 清空表数据内容
# truncate 会直接讲自增长计数器恢复到默认值 1
truncate person5;

# 郭德纲 id = 1
insert into person5(name)
values ('郭德纲');

select *
from person5;

insert into person5(id, name) values (101, '杨九郎');
insert into person5(name) values ('周九良');

13.6 外键约束

# 设计员工表
create table employee
(
    id       int primary key auto_increment, -- 主键约束，自增长
    name     varchar(32) not null,           -- 非空约束 员工名
    deptName varchar(32) not null            -- 非空约束 部门名
);

insert into employee(name, deptName)
values ('苟磊', 'JavaEE学科部');
insert into employee(name, deptName)
values ('老黑', 'JavaEE学科部');
insert into employee(name, deptName)
values ('博哥', 'JavaEE学科部');
insert into employee(name, deptName)
values ('阳哥', 'JavaEE学科部');
insert into employee(name, deptName)
values ('大哥', '品质保障部');
insert into employee(name, deptName)
values ('二贝', '品质保障部');
insert into employee(name, deptName)
values ('萍姐', '品质保障部');
insert into employee(name, deptName)
values ('瑞姐', '企业合作部');
insert into employee(name, deptName)
values ('晓明老师', '企业合作部');
insert into employee(name, deptName)
values ('瑞哥', 'JavaEE学科部');
insert into employee(name, deptName)
values ('杨仔', 'JavaEE学科部');
insert into employee(name, deptName)
values ('老万', 'JavaEE学科部');
insert into employee(name, deptName)
values ('栋哥', 'JavaEE学科部');
insert into employee(name, deptName)
values ('东哥', 'JavaEE学科部');
insert into employee(name, deptName)
values ('小单', 'JavaEE学科部');

select *
from employee;

# 数据冗余是否存在???
# 数据冗余存在，因为部门数据在整个表中有重复情况下，并且一定程度导致资源浪费
# 分表操作！！！员工表和部门表

# 删除原员工表
drop table employee;

# 部门表
create table dept
(
    id       int primary key auto_increment, -- 部门 ID 号 PRI AI
    deptName varchar(32) not null            -- 部门名称 NN
);

# 员工表
create table employee
(
    id     int primary key auto_increment, -- 员工ID PRI AI
    name   varchar(32) not null,           -- 员工名 NN
    deptId int         not null            -- 部门ID号 NN
);

# 首先准备部门表数据
insert into dept(deptName)
values ('JavaEE学科部');
insert into dept(deptName)
values ('品质保障部');
insert into dept(deptName)
values ('企业合作部');

select *
from dept;

# 插入数据到员工表中，需要明确的给予部门 ID 号，部门ID号理论应该和 dept 对应的 id 号有对应关系
insert into javaee_2203.employee(name, deptId)
values ('苟磊', 1);
insert into javaee_2203.employee(name, deptId)
values ('老黑', 1);
insert into javaee_2203.employee(name, deptId)
values ('老万', 1);
insert into javaee_2203.employee(name, deptId)
values ('栋哥', 1);
insert into javaee_2203.employee(name, deptId)
values ('博哥', 1);
insert into javaee_2203.employee(name, deptId)
values ('杨仔', 1);
insert into javaee_2203.employee(name, deptId)
values ('瑞哥', 1);
insert into javaee_2203.employee(name, deptId)
values ('东哥', 1);
insert into javaee_2203.employee(name, deptId)
values ('阳哥', 1);
insert into javaee_2203.employee(name, deptId)
values ('小单', 1);
insert into javaee_2203.employee(name, deptId)
values ('大哥', 2);
insert into javaee_2203.employee(name, deptId)
values ('二贝', 2);
insert into javaee_2203.employee(name, deptId)
values ('萍姐', 2);
insert into javaee_2203.employee(name, deptId)
values ('瑞姐', 3);
insert into javaee_2203.employee(name, deptId)
values ('晓明老师', 3);

select *
from employee;

# 联表查询，从部门表中取出部门名称数据
select employee.id, employee.name, dept.deptName
from javaee_2203.employee
         inner join dept on employee.deptId = dept.id;

select e.id, e.name, d.deptName
from javaee_2203.employee e
         left join dept d on d.id = e.deptId;

# 插入数据
# 插入数据对应的部门 id 号在部门表中不存在，但是我们添加成功了！！！
# 可以插入，但是没有意义
insert into javaee_2203.employee(name, deptId)
values ('蒋玮豪', 10);

select *
from javaee_2203.employee;

# 删除部门表指定 id 数据，对于整个数据库是有危害的，不应该删除！！！
delete
from dept
where id = 1;

# 删除员工表和部门表
drop table dept;
drop table javaee_2203.employee;

# 设计 t_dept 部门表
create table t_dept
(
    id       int primary key auto_increment, -- 部门ID号，PRI AI
    deptName varchar(32) not null            -- 部门名 NN
);

# 设计 t_employee 员工表
# 同时需要加入【外键约束】
create table t_employee
(
    id     int primary key auto_increment, -- 员工ID号 PRI AI
    name   varchar(32) not null,           -- 员工名 NN
    deptId int         not null,           -- 部门ID号 NN

    constraint fk_emp_dept                 -- 声明外键，外键名称 fk_emp_dept
        foreign key (deptId)               -- 选择当前表中作为外键约束的数据 deptId
            references t_dept (id)         -- 选择连接作为外键约束的主表和对应字段名 主表 t_dept 字段 id
);

# 插入员工表数据尝试
insert into javaee_2203.t_employee(name, deptId)
values ('苟磊', 1);
# [23000][1452] Cannot add or update a child row: a foreign key constraint fails
# (`javaee_2203`.`t_employee`, CONSTRAINT `fk_emp_dept` FOREIGN KEY (`deptId`) REFERENCES `t_dept` (`id`))
# 直接插入员工表数据，但是外键约束的部门表没有对应字段数据内容
# 当前部门中没有 id = 1 的数据，直接报错！！！

# 插入部门表数据
insert into t_dept(deptName)
values ('JavaEE学科部');
insert into t_dept(deptName)
values ('品质保障部');
insert into t_dept(deptName)
values ('企业合作部');

select * from t_dept;

# 部门表准备就绪，插入员工表数据内容
insert into javaee_2203.t_employee(name, deptId)
values ('苟磊', 1);
insert into javaee_2203.t_employee(name, deptId)
values ('老黑', 1);
insert into javaee_2203.t_employee(name, deptId)
values ('老万', 1);
insert into javaee_2203.t_employee(name, deptId)
values ('栋哥', 1);
insert into javaee_2203.t_employee(name, deptId)
values ('博哥', 1);
insert into javaee_2203.t_employee(name, deptId)
values ('杨仔', 1);
insert into javaee_2203.t_employee(name, deptId)
values ('瑞哥', 1);
insert into javaee_2203.t_employee(name, deptId)
values ('东哥', 1);
insert into javaee_2203.t_employee(name, deptId)
values ('阳哥', 1);
insert into javaee_2203.t_employee(name, deptId)
values ('小单', 1);
insert into javaee_2203.t_employee(name, deptId)
values ('大哥', 2);
insert into javaee_2203.t_employee(name, deptId)
values ('二贝', 2);
insert into javaee_2203.t_employee(name, deptId)
values ('萍姐', 2);
insert into javaee_2203.t_employee(name, deptId)
values ('瑞姐', 3);
insert into javaee_2203.t_employee(name, deptId)
values ('晓明老师', 3);

select * from t_employee;
select te.id, te.name, td.deptName
from t_employee te
left join t_dept td on td.id = te.deptId;

# 删除部门表数据
delete from t_dept where id = 1;
# [23000][1451] Cannot delete or update a parent row: a foreign key constraint fails (`javaee_2203`.`t_employee`,
# CONSTRAINT `fk_emp_dept` FOREIGN KEY (`deptId`) REFERENCES `t_dept` (`id`))
# 当前部门表中，删除对应数据字段和数据行，员工表正在使用，无法删除。

# 想要完成一个部门删除的全部过程
# 1. 删除员工表中所有使用部门表数据的对应数据行，从表数据
delete from t_employee where deptId = 1;
# 2. 再来删除对应的部门表数据，主表数据
delete from t_dept where id = 1;

# 外键约束有利有弊
# 优势:
#       1. 添加数据有条件约束，防止添加错误
#       2. 删除数据有条件约束，防止误删
# 劣势:
#       1. 整个数据库成为一坨，操作不便
#       2. 外键约束会降低【数据库】的性能
#       3. 羁绊过多，耦合度太高了

# 心中有外键思想，数据库无外键约束
# 用户进行注册，需要选择对应部门，完全可以利用前端页面，下拉菜单约束用户行为。
# 用户收货地址选择，可以通过菜单约束，保证用户操作不会超出合法范围

13.7 级联约束 cascade

drop table t_employee;
drop table t_dept;

# 设计 t_dept 部门表
create table t_dept
(
    id       int primary key auto_increment, -- 部门ID号，PRI AI
    deptName varchar(32) not null            -- 部门名 NN
);

# 设计 t_employee 员工表
# 同时需要加入【外键约束】
create table t_employee
(
    id     int primary key auto_increment, -- 员工ID号 PRI AI
    name   varchar(32) not null,           -- 员工名 NN
    deptId int         not null,           -- 部门ID号 NN

    constraint fk_emp_dept                 -- 声明外键，外键名称 fk_emp_dept
        foreign key (deptId)               -- 选择当前表中作为外键约束的数据 deptId
            references t_dept (id)         -- 选择连接作为外键约束的主表和对应字段名 主表 t_dept 字段 id
            on delete cascade              -- 级联操作 删除级联 主表数据删除，使用主表字段数据内容的从表数据行也删除
            on update cascade              -- 级联操作 修改级联 主表数据修改，使用主表字段数据内容的从表数据行也修改
);
# 插入部门表数据
insert into t_dept(deptName)
values ('JavaEE学科部');
insert into t_dept(deptName)
values ('品质保障部');
insert into t_dept(deptName)
values ('企业合作部');

select *
from t_dept;

# 部门表准备就绪，插入员工表数据内容
insert into javaee_2203.t_employee(name, deptId)
values ('苟磊', 1);
insert into javaee_2203.t_employee(name, deptId)
values ('老黑', 1);
insert into javaee_2203.t_employee(name, deptId)
values ('老万', 1);
insert into javaee_2203.t_employee(name, deptId)
values ('栋哥', 1);
insert into javaee_2203.t_employee(name, deptId)
values ('博哥', 1);
insert into javaee_2203.t_employee(name, deptId)
values ('杨仔', 1);
insert into javaee_2203.t_employee(name, deptId)
values ('瑞哥', 1);
insert into javaee_2203.t_employee(name, deptId)
values ('东哥', 1);
insert into javaee_2203.t_employee(name, deptId)
values ('阳哥', 1);
insert into javaee_2203.t_employee(name, deptId)
values ('小单', 1);
insert into javaee_2203.t_employee(name, deptId)
values ('大哥', 2);
insert into javaee_2203.t_employee(name, deptId)
values ('二贝', 2);
insert into javaee_2203.t_employee(name, deptId)
values ('萍姐', 2);
insert into javaee_2203.t_employee(name, deptId)
values ('瑞姐', 3);
insert into javaee_2203.t_employee(name, deptId)
values ('晓明老师', 3);

select *
from t_employee;

# 插入从表数据，外键约束字段和主表数据内容不一致
insert into javaee_2203.t_employee(name, deptId)
values ('王某平', 10);
# [23000][1452] Cannot add or update a child row: a foreign key constraint fails (`javaee_2203`.`t_employee`,
# CONSTRAINT `fk_emp_dept` FOREIGN KEY (`deptId`) REFERENCES `t_dept` (`id`) ON DELETE CASCADE ON UPDATE CASCADE)
# 外键约束依然存在，从表外键约束字段必须和主表数据内容对应，如果没有对应数据无法添加操作。

# 修改部门表 ID 号
# 因为在外键约束使用 使用 on update cascade 主表字段修改之后，从表对应数据内容一并修改
# 部门ID从 1 修改到 10
update t_dept
set id = 10
where id = 1;

select *
from t_dept;

# 员工原本对应部门ID为 1的数据同时转换为 10
select *
from t_employee;

# 删除部门表数据
# 因为在外键约束使用 使用 on delete cascade 主表字段删除之后，从表对应数据内容一并删除
# 在部门表中，删除了部门ID为10的数据
delete
from t_dept
where id = 10;

select *
from t_dept;

# 员工表中使用部门ID为10的数据全部删除
select *
from t_employee;

# 修改设置当前自增长累计数据
alter table t_employee auto_increment = 16;

14. 高阶查询

14.1 子查询

14.1.1 基本格式

# select filedName
# form tbName
# where (子查询结果);

14.1.2 子查询结果作为条件判断约束

# 查询工资高于 Oliver 的员工id，名字和工资
# 1. 必须查询出 Oliver 的工资是多少
select salary
from t_employees
where first_name = 'Oliver';

# 2. 在按照 Oliver 的工资标准作为第二次查询的约束限制，完成结果产出
select employee_id, first_name, salary
from t_employees
where salary > 7000;

# 子查询合并
select employee_id, first_name, salary
from t_employees
where salary > (select salary from t_employees where first_name = 'Oliver');

14.1.3 子查询结果作为枚举 in 操作限制

# 找出和 John 同部门的员工ID，名字，工资和部门编号
# 1. 找出 John 对应的部门 有三个部门都是 John 部门编号 100 50 80
select department_id
from t_employees
where first_name = 'John';

# 2. 利用 in 枚举操作找出 100 50 80 对应的员工数据
select employee_id, first_name, salary, department_id
from t_employees
where department_id in (100, 50, 80);

# 整合子查询
select employee_id, first_name, salary, department_id
from t_employees
where department_id in (select department_id from t_employees where first_name = 'John');

14.1.4 子查询结果作为一张表，从当前子查询结果临时表中完成二次查询

# 查询员工表中工资前五的员工信息
# 1. 工资降序，找出员工ID，名字和工资
select employee_id, first_name, salary
from t_employees
order by salary desc;

# 2. 第一步查询的结果作为一个临时表，从临时表找出员工ID，和名字
# 第一步查询结果临时表名称为 tempTable, 从 tempTable 找出  employee_id, first_name 数据
select employee_id, first_name
from (select employee_id, first_name, salary from t_employees order by salary desc)
         as tempTable
limit 5;

# 更好的方式
select employee_id, first_name
from t_employees
order by salary desc
limit 5;

14.2 合并查询【了解】

# 查询员工表员工ID，名字 和 工作表 工作ID编号和工作名称
# 合并查询要求 字段个数必须一致
select employee_id, first_name
from t_employees
union
select location_id, city
from t_locations;

# 不去重
# union all
select employee_id, first_name
from t_employees
union all
select department_id, department_name
from t_departments;

# 去重
# union
select employee_id, first_name
from t_employees
union
select manager_id, last_name
from t_employees;

14.3 表连接查询

14.3.1 基本格式

# select tbName1.fieldName1, tbName2.fieldName2
# from tbName1
# 连接方式 tbName2
# on condition;

14.3.2 笛卡尔乘积 【错误演示】

# 找出员工ID号，员工名字，部门名称
# 1. 员工ID号，员工名字在 t_employees表中
# 2. 部门名称，在t_departments表中
# 当前 SQL 没有加入任何的匹配约束，两个查询数据表中的所有数据行 乘积得到对应查询结果
# 会导致 t_employees 表中的每一个数据行都要匹配 t_departments 每一个数据行
# 【笛卡尔乘积】
select employee_id, first_name, department_name
from t_employees
         inner join t_departments;

select count(employee_id)
from t_employees;

select count(department_id)
from t_departments;

14.3.3 内连接查询 两张表

# 找出员工ID号，员工名字，部门名称
# 1. 员工ID号，员工名字在 t_employees表中
# 2. 部门名称，在t_departments表中
# 3. 限制条件是员工表数据行 department_id <==> 部门表中的 department_id 匹配得到 department_name

select t_employees.employee_id,      -- 员工表中的员工ID数据
       t_employees.first_name,       -- 员工表中的员工名字
       t_departments.department_name -- 部门表中的部门名
from t_employees -- 从员工表中获取数据
         inner join t_departments -- 内连接部门表，需要从部门表中获取数据
                    on t_employees.department_id = t_departments.department_id;
-- 限制员工表中部门ID和部门表中的部门ID一致

# 简化表名称，简拼操作
select te.employee_id,    -- 员工表中的员工ID数据
       te.first_name,     -- 员工表中的员工名字
       td.department_name -- 部门表中的部门名
from t_employees te -- 从员工表中获取数据 在当前SQL语句中 t_employees 表对应的名称为 te
         inner join t_departments td -- 内连接部门表，需要从部门表中获取数据，当前 SQL 语句中 t_departments 对应的名称为 td
                    on te.department_id = td.department_id;
-- 限制员工表中部门ID和部门表中的部门ID一致

14.3.4 内连接查询 三张表

# 查询 员工ID，员工名字，部门名称，和 部门所在城市
# 1. 员工ID，员工名字 员工表中
# 2. 部门名称 从部门表中
# 3. 部门所在城市 在位置表中
# 连接关系: 员工表中 department_id <===> 部门表中的 department_id
# 连接关系: 部门表中的 location_id <===> 位置表中的 location_id
select te.employee_id,
       te.first_name,
       td.department_name,
       tl.city
from t_employees te
         inner join t_departments td on te.department_id = td.department_id
         inner join t_locations tl on td.location_id = tl.location_id;

# 查询 员工ID，员工名字，部门名称，和 岗位名称
# 1. 员工ID，员工名字 员工表中
# 2. 部门名称 从部门表中
# 3. 岗位名称 岗位表中
# 连接关系: 员工表中 department_id <===> 部门表中的 department_id
# 连接关系: 员工表中 job_id <===> 岗位表中的 job_id
select te.employee_id,
       te.first_name,
       td.department_name,
       tj.job_title
from t_employees te
         inner join t_departments td on te.department_id = td.department_id
         inner join t_jobs tj on te.job_id = tj.job_id;

14.3.5 内连接查询 四张表

# 查询 员工ID，员工名字，部门名称，所在城市和国家
# 1. 员工ID，员工名字 员工表中
# 2. 部门名称  部门表中
# 3. 所在城市  位置表
# 4. 国家 国家表
# 连接关系: 员工表中 department_id <===> 部门表中的 department_id
# 连接关系: 部门表中的 location_id <===> 位置表 location_id
# 连接关系: 位置表 country_id <===> 国家表 country_id
select te.employee_id,
       te.first_name,
       td.department_name,
       tl.city,
       tc.country_name
from t_employees te
         inner join t_departments td on te.department_id = td.department_id
         inner join t_locations tl on td.location_id = tl.location_id
         inner join t_countries tc on tl.country_id = tc.country_id;

14.3.6 内连接查询 五张表

# 查询 员工ID，员工名字，岗位名称，部门名称，所在城市和国家
# 1. 员工ID，员工名字 员工表
# 2. 岗位名称 岗位表
# 3. 部门名称  部门表
# 4. 所在城市  位置表
# 5. 国家 国家表

# 连接关系: 员工表中 department_id <===> 部门表中的 department_id
# 连接关系: 部门表中的 location_id <===> 位置表 location_id
# 连接关系: 位置表 country_id <===> 国家表 country_id
# 连接关系: 员工表中 job_id <===> 岗位表 job_id

# 补充限制 限制 国家 United Kingdom 按照工资升序，选取最低的 5 个
select te.employee_id,
       te.first_name,
       te.salary,
       tj.job_title,
       td.department_name,
       tl.city,
       tc.country_name
from t_employees te
         inner join t_departments td on te.department_id = td.department_id
         inner join t_locations tl on td.location_id = tl.location_id
         inner join t_countries tc on tl.country_id = tc.country_id
         inner join t_jobs tj on te.job_id = tj.job_id
where country_name = 'United Kingdom'
order by salary
limit 5;

# 按照国家计算所有员工的平均工资, 降序排序
select avg(te.salary),
       tc.country_name
from t_employees te
         inner join t_departments td on te.department_id = td.department_id
         inner join t_locations tl on td.location_id = tl.location_id
         inner join t_countries tc on tl.country_id = tc.country_id
group by country_name
order by avg(te.salary) desc;

14.3.7 左外连接查询

# left outer join
# 左表所有字段全部展示，右表字段匹配展示，如果没有对应数据，显示 null
# 公司部门【左表】 核心 部门名称
# 员工人数【右表】 核心 字段人数
# 按照 左外连接查询
# IT 10
# HR 5
# AD 1
# SR 10
# T  null

# 找出所有的公司部门名称和对应的员工名称，如果此部门没有人数显示 null
select department_id, department_name
from t_departments;

# 1. 员工表 员工名称
# 2. 部门表 得到部门名称
# 左表是部门表，部门表 left outer join 左侧
select td.department_name, te.first_name
from t_departments td
         left outer join
     t_employees te on td.department_id = te.department_id;

14.3.8 右外连接查询

# right outer join
# 右表所有字段全部展示，左表字段匹配展示，如果没有对应数据，显示 null

# 找出所有的公司部门名称和对应的员工名称，如果此部门没有人数显示 null
# 右表是部门表， 部门表 right outer join 右侧
select td.department_name, te.first_name
from t_employees te
         right outer join
         t_departments td on td.department_id = te.department_id;