MySQL学习笔记

最新推荐文章于 2024-07-11 20:24:07 发布

DarkCircl

最新推荐文章于 2024-07-11 20:24:07 发布

阅读量294

点赞数

文章标签： mysql 数据库学习

本文链接：https://blog.csdn.net/weixin_45839775/article/details/126176854

版权

文章目录

- MySQL

MySQL

1. 为什么要使用数据库

项目中数据内容使用文件存储存在的问题
	1. 安全问题，数据文件可以被其他人修改
	2. 规范问题，处理方式不规范，对应文档编码集不规范
	3. 操作问题，不存在回滚事务操作
	4. 索引操作不规范，无法对多个数据模块进行查询操作。
	5. 文件操作对于数据的整合能力，数据存储占用空间相较于数据库更大！！！
	
可以用数据库解决以上问题
	1. 安全限制更加全面
	2. 操作规范，数据基本的模型是键值对模型，并且有专业的 SQL 语句
	3. 操作规范，可以设置回滚，事务操作，保证数据安全
	4. 存在索引机制，可以快速查询，同时可以连接多张数据表完成复合数据查询
	5. 数据存储有一定优化机制，可以提升数据存储效能

2. 数据库概述

关系型数据库
	MySQL Oracle SQLServer
非关系型数据库
	Redis MongoDB MemoryCache

MySQL 性能OK，价格实惠(免费)

3. 数据库系统

DBMS Database Manager System
	数据库管理系统

MySQL Oracle SQLServer 都是数据库管理系统软件

4. 数据库基本操作

4.1 cmd 连接数据库

> mysql -hlocalhost -uroot -p
Enter password: ******
# mysql 明确当前连接/操作的数据库是哪一个。
# -hlocalhost -h host 主机名/域名/ip 地址 一般情况下是 ip 地址，
# 如果是本机服务器 localhost ，但是可以省略
# -u user 用户名
# -p password 密码

# 连接数据库需要内容 数据库是哪一个，数据库在哪里，登陆数据库的用户名和对应密码

# windows Linux 操作系统中 本机 MySQL 数据库连接常用形式
> mysql -uroot -p
Enter password: ******

4.2 数据库相关操作

-- 展示当前数据库服务器上所有的数据库内容，在 DBMS 上查询所有的 DB
> show databases;

-- 在数据库服务器上创建一个新的数据库，数据库名称 javaee_2211
> create database javaee_2211;
# 数据库提示 Query OK, 1 row affected (0.00 sec)
# 表示以上 SQL 语句执行 OK，目前数据库有一行受到了影响

-- 数据库服务器展示创建指定数据库的流程和对应 SQL 语句
> show create database javaee_2211;

# +-------------+----------------------------------------------------------------------+
# | Database    | Create Database                                                      |
# +-------------+----------------------------------------------------------------------+
# | javaee_2211 | CREATE DATABASE `javaee_2211` /*!40100 DEFAULT CHARACTER SET utf8 */ |
# +-------------+----------------------------------------------------------------------+

-- 删除指定数据库，【慎用】直接删除整个数据库中的所有内容，一般情况下，项目开发中普通程序员不具备操作权限。
> drop database javaee_2211;

4.3 数据表相关操作

-- 选择使用哪一个数据库
-- 想要执行对于数据表的操作内容，首先要选择数据表操作 SQL 针对的是哪一个数据库
> use javaee_2211;

-- 展示当前数据库中有多少个数据表
> show tables;

-- 创建数据表
> create table person1
(
    # id 字段名 Field int类型 ==> Java 中的 int/Integer 类型
    id int,
    # name 字段名 Field varchar(255) 可变长字符串 ==> Java 中的 String
    name varchar(255),
    # gender 字段名 Field tinyInt(1) 最小整型并且数值位数 1 位 ==> Java 中的 boolean 类型/byte
    gender tinyint(1)
);

-- 查询数据表创建过程
> show create table person1;

# CREATE TABLE `person1` (
#   `id` int(11) DEFAULT NULL,
#   `name` varchar(255) DEFAULT NULL,
#   `gender` tinyint(1) DEFAULT NULL
# ) ENGINE=InnoDB DEFAULT CHARSET=utf8

-- 查看指定数据表结构
> desc person1;
# +--------+--------------+------+-----+---------+-------+
# | Field  | Type         | Null | Key | Default | Extra |
# +--------+--------------+------+-----+---------+-------+
# | id     | int(11)      | YES  |     | NULL    |       |
# | name   | varchar(255) | YES  |     | NULL    |       |
# | gender | tinyint(1)   | YES  |     | NULL    |       |
# +--------+--------------+------+-----+---------+-------+

-- 删除数据表
> drop table person1;

-- 退出数据库
> exit;
> quit;

5. 数据表修改操作[非重点]

修改字段名，添加字段名，修改数据类型

# 添加字段到指定数据表
# tips: 默认在最后一个字段之后添加新的字段内容
> alter table person1 add info text;
> alter table person1 add score int;


# 删除指定表的指定字段
> alter table person1 drop info;

# change
# 同时修改字段名和数据类型名
> alter table person1 change gender sex char(1);

# modify
# 修改指定字段的数据类型
> alter table person1 modify score float(4,1);

6. insert 添加操作【重点】

>  CREATE TABLE `person1` (
  `id` int(11) DEFAULT NULL,
  `name` varchar(255) DEFAULT NULL,
  `gender` tinyint(1) DEFAULT NULL,
  `address` varchar(255) DEFAULT NULL,
  `score` float(4,1) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8
# +---------+--------------+------+-----+---------+-------+
# | Field   | Type         | Null | Key | Default | Extra |
# +---------+--------------+------+-----+---------+-------+
# | id      | int(11)      | YES  |     | NULL    |       |
# | name    | varchar(255) | YES  |     | NULL    |       |
# | gender  | tinyint(1)   | YES  |     | NULL    |       |
# | address | varchar(255) | YES  |     | NULL    |       |
# | score   | float(4,1)   | YES  |     | NULL    |       |
# +---------+--------------+------+-----+---------+-------+

# 1. 规规矩矩添加数据，有什么字段，加什么字段
# insert into 添加操作
# person1 表名
# (字段名1, 字段名2)
# values(对应字段名的数据内容，要求符合数据类型，严格遵从数据类型一致化要求)
> insert into person1(id, name, gender, address, score) values(1, '张三', 0, 'China', 100.0);

# 2. 精简模式
# 省略了 into 同时省略在字段声明，但是要求添加的数据顺序，个数必须和字段顺序一致
> insert person1 values(2, '张三', 0, 'China', -5.5);

# 3. 指哪打哪 【常用】
# 明确限制添加的字段名称和对应数据，数据个数顺序类型和声明字段一致
> insert into person1(id, name, gender) values(3, '李四', 0);

7. update 修改操作【慎用】

-- 修改数据表内容
-- 慎用，修改数据必须有合理合法的条件约束
> update person1 set name = '李四';

-- 依据条件修改指定字段数据内容
-- 指定 id 数据修改字段内容
> update person1 set name = '南希今天晚上将于安倍先生共进晚餐' where id = 16;

-- 指定范围修改
> update person1 set name = '王五' where id >= 11;

-- 修改多个字段
-- 多个字段数据使用 , 逗号隔开
> update person1 set name = '赵六', address = '成都' where id >= 11;

8. delete 删除操作【慎用】

-- 删除数据表内容
-- 整个数据表中的所有数据内容全部清空
> delete from person1;

-- 可以限制条件删除
-- 指定 id 删除数据
> delete from person1 where id = 16;
-- 指定 name 字段删除
> delete from person1 where name = '王五';
-- 删除 id >= 2
> delete from person1 where id >= 2;

9. 事务处理【重点】

关闭 SQL 语句自动提交功能，在校验合法数据之后，再行提交数据操作，也可以对之前的操作进行回滚

事务一般用于限制【更新操作 insert delete update】

# 关闭自动提交 SQL 语句功能，开启【事务操作】
# 从关闭 SQL 语句自动提交位置开始，之后的 SQL 语句执行都是临时执行，不涉及的真正数据修改。
> set autocommit = 0;

# 回滚 SQL 语句操作
> rollback;

# 提交从事务开启到 commit 之间的所有 SQL 语句 或者是 commit 到 commit
> commit;

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

10. Company表结构

11. select 查询操作 DQL【重点】

11.1 基本格式

select

目标字段

from

目标数据表

11.1.1 基本查询，字段+表名

# Ctrl + Alt + L 代码对齐
# 项目研发中基本上不允许使用的操作
select *  -- 目标字段 * ==> 当前数据表的所有字段内容
from t_employees; -- 目标表名 t_employees

# 以上 SQL 语句操作会导致将整个目标数据表的所有数据内容全部查询结果，会导致数据库效率降低。

# 指定字段名称查询目标数据内容
# 有且只在目标数据表中查询 employee_id, first_name, last_name
select employee_id, first_name, last_name 
from t_employees;

11.1.2 查询数据信息进行数据计算

# 查询目标数据表中的 employee_id, first_name, last_name, salary
select employee_id, first_name, last_name, salary
from t_employees;

# 计算得到年薪 salary * 16 一年 16 薪
select employee_id, first_name, last_name, salary * 16
from t_employees;

11.1.3 去重查询 distinct

# 没有去重查询的情况下，目标查询结果数据行为 107 行
select first_name
from t_employees;

# 使用 distinct 去重查询，去除目标结果中的所有重复 first_name, 剩余数据行为 91 行
select distinct first_name
from t_employees;

11.1.4 字段别名

# 从目标数据表中查询目标数据内容
select employee_id, first_name, last_name, salary
from t_employees;

# 可以使用 as 给予目标字段名称别名操作
select employee_id as 'ID', first_name as '名', last_name as '姓', salary as '薪水'
from t_employees;

11.2 排序查询 order by

11.2.1 单一条件排序

# 查询目标字段 employee_id, first_name, salary 并且按照 salary 工资升序排序
# asc 升序要求，可以省略
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;

# 查询目标字段 employee_id, first_name, salary 并且按照 salary 工资降序排序
# desc 降序排序要求
select employee_id, first_name, salary
from t_employees
order by salary desc;

11.2.2 多条件排序

# 要求 按照 salary 降序，如果出现相同的工资情况，employee_id 降序
select employee_id, first_name, salary
from t_employees
order by salary desc, employee_id desc;

11.3 条件查询 where

11.3.1 基本格式

select 字段名
from 表名
where condition;

11.3.2 等值判断 =

# 查询 id = 150 的员工 first_name, last_name, salary
select first_name, last_name, salary
from t_employees
where employee_id = 150;

# 查询 first_name = David 对应的员工信息，first_name, last_name, salary
select first_name, last_name, salary
from t_employees
where first_name = 'David'; -- 数据库字符串是英文单引号包含的内容

11.3.3 不等值判定(> < >= <= != <>)

# 找出工资大于 8000 的 first_name, last_name, salary
select first_name, last_name, salary
from t_employees
where salary > 8000;

# 找出工资大于等于 8000 的 first_name, last_name, salary
select first_name, last_name, salary
from t_employees
where salary >= 8000;

# 找出工资小于 3000 的 first_name, last_name, salary
select first_name, last_name, salary
from t_employees
where salary < 3000;


# 找出工资小于等于 3000 的 first_name, last_name, salary
select first_name, last_name, salary
from t_employees
where salary <= 3000;

# 找出不在 department_id = 30 的部门员工  first_name, last_name, salary
select first_name, last_name, salary
from t_employees
where department_id != 30;

select first_name, last_name, salary
from t_employees
where department_id <> 30;

11.3.4 逻辑判断 (and or not)

# 找出部门 department_id = 50 同时要求工资在 8000 以上的员工信息
select employee_id, first_name, last_name, salary, department_id
from t_employees
where department_id = 50 and salary >= 8000; -- and 连接多个条件，逻辑与操作

select employee_id, first_name, last_name, salary, department_id
from t_employees
where department_id = 50 && salary >= 8000; -- && 连接多个添加 同理 and，逻辑与操作

# 找出部门 department_id = 50 或者 salary 在 5000 块钱以下的员工信息
select employee_id, first_name, last_name, salary, department_id
from t_employees
where department_id = 50 or salary < 5000; -- or 或者

select employee_id, first_name, last_name, salary, department_id
from t_employees
where department_id = 50 || salary < 5000; -- || 逻辑或

# 找出员工信息，不在 50 部门的
select employee_id, first_name, last_name, salary, department_id
from t_employees
where not department_id = 50;

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

11.3.5 区间操作 between and

# 找出员工信息，要求工资在 6000 ~ 8000 之间 【要头要尾】
select employee_id, first_name, last_name, salary
from t_employees
where salary between 6000 and 8000;

# 找出员工信息，要求工资在 6000 ~ 8000 之间 【要头要尾】
select employee_id, first_name, last_name, salary
from t_employees
where salary >= 6000 and salary <= 8000;

11.3.6 null 判断

# 空值判断，判断当前字段对应的数据是否没有内容
# 查询所有没有提成的员工信息
select employee_id, first_name, last_name, commission_pct
from t_employees
where commission_pct is null;

# 查询所有有提成的员工信息
select employee_id, first_name, last_name, commission_pct
from t_employees
where commission_pct is not null;

11.3.7 枚举查询 in

# 查询员工信息，要求部门在 30 50 80
# 使用 || 多条件连接
select employee_id, first_name, last_name, department_id
from t_employees
where department_id = 30 || department_id = 50 || department_id = 80;

# in 枚举查询操作 (目标数据内容，不同的内容使用 逗号隔开)
select employee_id, first_name, last_name, department_id
from t_employees
where department_id in (30, 50, 80);

# 查询不在 30 50 80 部门id 的员工信息
select employee_id, first_name, last_name, department_id
from t_employees
where department_id not in (30, 50, 80);

11.3.8 模糊查询 like

# % 匹配任意个数字符，可以没有，可以多个
# _ 匹配一个字符，必须有！！！没有不匹配
# 查询 first_name 以 d 结尾的员工数据
select first_name, last_name
from t_employees
where first_name like '%d';

# 查询 first_name 以 d 结尾的员工数据 要求名字字符总数 5 个
select first_name, last_name
from t_employees
where first_name like '____d';

# 查询 first_name 倒数第二个字符为 i
select first_name, last_name
from t_employees
where first_name like '%i_';

# 查询 first_name 数据带有字符 d 不区分大小写
select first_name, last_name
from t_employees
where first_name like '%d%';

# 查询结果同上
select first_name, last_name
from t_employees
where first_name like '%D%';

11.3.9 分支查询

# case
# 	when condition1 then ret1
# 	when condition2 then ret2
# 	when condition3 then ret3
# end
-- 从case开始，到end结束。满足条件对应一个结果，类似于Java中的switch case

-- 查询姓名，工资以及对应工资划分的等级LEVEL
select first_name, last_name,
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 first_name, last_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;

11.4 时间查询

-- 语法
select 时间函数([参数列表]);
-- 查询时间情况下，得到的是一个单列单表(虚表)

时间函数	功能描述
`sysdate()`	当前系统时间 (年，月，日，时，分，秒)
`CURRENT_TIMESTAMP()`	当前系统时间
`curdate()`	当前日期
`curtime()`	当前时间
`week()`	指定日期是这一年的第几周
`hour()`	指定日期是今天第几个小时 24 小时制
`minute()`	指定日期是小时的第几分钟
`second()`	指定日期是分钟的第几秒

# 测试
select sysdate();
select current_timestamp();
select curdate();
select curtime();
select week(sysdate());
select hour(sysdate());
select minute(sysdate());
select second(sysdate());

11.5 字符串应用

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

# 测试
select concat('今天', '是个', '好', '天气~~~');
select insert('0123456789', 2, 5, 'ABCDF');
select lower('ABCDEFGabcdefg');
select upper('ABCDEFGabcdefg');
select substring('0123456789', 5, 3);

11.6 内置方法

方法	功能描述
`sum()`	指定字段一列总和
`avg()`	指定字段一列平均值
`max()`	指定字段一列中的最大值
`min()`	指定字段一列中的最小值
`count()`	指定字段有多少行

# 月支出工资总和
select sum(salary)
from t_employees;

# 人均工资
select avg(salary)
from t_employees;

# 工资最大值
select max(salary)
from t_employees;

# 工资最小值
select min(salary)
from t_employees;

# count 计数指定字段的数据行个数
select count(employee_id)
from t_employees; # 61 60 55 64

select count(1)
from t_employees; # 59 64 55 58

select count(*)
from t_employees; # 58 54 79 56

11.7 分组查询

-- 查询各部门人数是多少
-- 1. 需要按照department_id进行分组
-- 2. 计数需要使用count， 根据用户的employee_id进行计数操作
select department_id, count(employee_id) -- 目标的查询字段，查询目标中有一个 count 计数 根据员工 ID 计数
from t_employees -- 从员工表中查询
group by department_id;
-- 按照部门 ID 号进行分组操作

-- Error 当前操作目标字段和数据内容无法匹配，如果需要查询结果，必须有分组操作。
select department_id, count(employee_id) -- 目标的查询字段，查询目标中有一个 count 计数 根据员工 ID 计数
from t_employees;
-- 从员工表中查询

-- 查询各部门的平均工资
-- 1. 需要按照department_id进行分组
-- 2. 平均工资使用avg方法计算
select department_id, avg(salary)
from t_employees
group by department_id;

-- 查询各部门，各岗位的人数
-- 1. 需要按照department_id进行分组
-- 2. 需要按照岗位job_id进行分组
-- 3. 记录人数，count(employee_id)
select department_id, job_id, count(employee_id)
from t_employees
group by department_id, job_id;
-- 多条件分组情况

11.8 分组过滤查询

-- 查询指定100，50，30，80部门最高工资，限制领导 ID 100
-- 1. 需要按照department_id进行分组
-- 2. 最高工资
-- 3. 限制manager_id = 100
-- 4. 限制department_id号为100，50，30，80
select department_id, manager_id, max(salary)
from t_employees
group by department_id, manager_id -- 分组操作之后，所有的条件限制都是基于分组查询结果内容来完成的。
having department_id in (30, 50, 80, 100)
   and manager_id = 100;
-- having 是分组之后的条件约束,having 可以使用判断条件一定是分组查询之后的数据内容

# 按照部门分组，统计部门人数大于 20 的
select department_id, count(1)
from t_employees
group by department_id
having count(1) > 20;
-- 限制过滤分组查找的条件是在 from 之前的字段或者目标数据内容

# 按照 manager_id 上级领导分组，统计每一个领导所属的人员个数,要求人数大于 5
select manager_id, count(1)
from t_employees
group by manager_id
having count(1) > 5;

11.9 限定查询

# 限定查询到的数据行个数，当前限制 5 行
select employee_id, first_name, last_name
from t_employees
limit 5;

# 限定查询到的数据行个数，当前限制 10 行
select employee_id, first_name, last_name
from t_employees
limit 10;

# 限定查询数据的开始数据行下标，和对应的目标数据行个数 limit offset, count
# 完成了一个类似于【分页操作】0 1 2 每一页 10 个数据
# limit (pageCount - 1) * itemCount, itemCount;
# limit (1 - 1) * 10, 10 ==> limit 0, 10
# limit (2 - 1) * 10, 10 ==> limit 10, 10
# limit (3 - 1) * 10, 10 ==> limit 20, 10
select employee_id, first_name, last_name
from t_employees
limit 0, 10;

select employee_id, first_name, last_name
from t_employees
limit 10, 10;

select employee_id, first_name, last_name
from t_employees
limit 20, 10;

11.10 基本查询总结

select fieldName
from tbName
where condition_
group by 分组
having 分组过滤
order by 排序[asc/desc]
limit offset, count;

-- from 数据来源，从那张表中查询数据
-- where 查询数据的条件
-- group by 分组
-- having 分组之后条件约束
-- select 查询指定的字段
-- order by 排序要求
-- limit 限制结果行数

11.11 子查询

-- 1. 找出Jack的工资
select salary
from t_employees
where first_name = 'Jack';

-- 2. 得到Jack工资，作为条件查询对应的员工信息
select employee_id, first_name, salary
from t_employees
where salary > 8400;

-- 整合
-- 将一个 select 查询的结果作为当前查询所需的条件
select employee_id, first_name, salary
from t_employees
where salary > (select salary
                from t_employees
                where first_name = 'Jack');

-- 根据Jack,Hermann的部门编号
-- 1. 根据Jack, Hermann的部门编号
select department_id
from t_employees
where first_name = 'Jack' or first_name = 'Hermann';

-- 2. 根据Jack,Hermann的部门编号，使用in枚举查询，限制条件
select employee_id, first_name, last_name
from t_employees
where department_id = 80 and department_id = 70;

select employee_id, first_name, last_name
from t_employees
where department_id in (70, 80);

-- 整合
-- 如果是一行数据结果，可以使用 = 操作限制条件
-- 如果子查询(Sub query) 结果是多行数据，有且只能使用  in
select employee_id, first_name, last_name
from t_employees
where department_id = (select department_id
                       from t_employees
                       where first_name = 'Jack'
                          or first_name = 'Hermann'); -- ERROR

select employee_id, first_name, last_name
from t_employees
where department_id in (select department_id
                        from t_employees
                        where first_name = 'Jack'
                           or first_name = 'Hermann'
);


-- 查询员工表中工资前五名的员工信息
-- 1. 找到员工的id，first_name，工资降序
select employee_id, first_name, salary
from t_employees
order by salary desc
limit 5;

-- 子查询结果作为，下一次查询的临时表
select employee_id, first_name
from (select employee_id, first_name
      from t_employees
      order by salary desc)
         as temp
limit 5;

11.12 表连接查询【重点】

11.12.1 基本格式

select 字段名1, 字段名2
from tbName1, tbName2
condition;

11.12.2 笛卡尔乘积

# 查询指定员工的 id, first_name, last_name, department_name
# id, first_name, last_name ==> 员工表 t_employees
# department_name ==> 部门表 t_department
select employee_id, first_name, last_name, department_name
from t_employees,
     t_departments;
# 结果数据行是两张表数据行相乘结果 27 * 107 ==>  2889
# 因为没有条件约束的情况下，导致两张表的数据完整匹配导致。

11.12.3 加入条件约束

select employee_id, first_name, last_name, department_name
from t_employees,
     t_departments
-- 员工表中的部门id 要求 和 部门表的 部门 id 一致
where t_employees.department_id = t_departments.department_id;

# 优化一点，可以给予数据表简称
# 简称可以用于条件字段使用，查询字段使用，明确绑定当前操作的字段是哪一个数据表中的。
select te.employee_id,
       te.first_name,
       te.last_name,
       td.department_name
from t_employees te,
     t_departments td
-- 员工表中的部门id 要求 和 部门表的 部门 id 一致
where te.department_id = td.department_id;

11.12.4 内连接查询

# 内连接查询
# 两张表内连接
select employee_id, first_name, last_name, department_name -- 查询的目标字段
from t_employees te -- 查询目标字段的第一张表，起别名 te
         inner join t_departments td -- inner join 内连接查询 第二张部门表，别名 td
                    on te.department_id = td.department_id;
-- 条件约束 on 和 where 一样，on 是使用在 连接查询操作

# 内连接查询
# 三张表
# 找出员工的id，first_name, last_name, job_title, department_name
# 字段和表信息关系
# t_employees employee_id, first_name, last_name
# t_jobs job_title 和 员工表连接条件是 job_id
# t_departments department_name 和 员工表连接条件是 department_id
select employee_id, first_name, last_name, job_title, department_name
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;

# 内连接查询
# 四张表
# 查询员工的 id，first_name, last_name, department_name, state_province, country_name
# 字段和表信息关系
# t_employees employee_id, first_name, last_name
# t_departments department_name 和 员工表连接条件是 department_id
# t_locations state_province 和 部门表连接条件 是 location_id
# t_countries country_name 和 位置表连接条件 country_id
select employee_id, first_name, last_name, department_name, state_province, 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;

# 期望查询的数据是员工 id ，first_name, last_name, country_name
# 提供数据的是两个数据表，但是完成两个数据表的查询操作，需要中间表的衔接！！！
select employee_id, first_name, last_name, 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;

# 内连接查询
# 五张张表
# 查询员工的 id，first_name, last_name, job_title,  department_name, state_province, country_name
select employee_id, first_name, last_name, job_title, department_name, state_province, 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;

11.12.5 外连接查询

# 需求
# 查询所有的部门，展示部门所有员工信息，如果该部门没有员工，显示 null
# 查询 department_name, first_name, last_name
# 内连接查询无法满足要求，因为数据条件限制如果没有字段对应数据内容，不限制！！！
select department_name, first_name, last_name
from t_departments td
         inner join t_employees te on td.department_id = te.department_id;

# 部门名称和每一个部门的人数 [有人的部门才统计]
select department_name, count(1)
from t_departments td
         inner join t_employees te
                    on td.department_id = te.department_id
group by department_name;

# 外连接查询操作
select department_name, first_name, last_name
from t_departments td
         -- 左外连接查询，左表字段数据内容完整展示，右表字段数据匹配展示，如果没有对应数据内容，显示 null
         -- join 左侧是左表，右侧是右表
         left outer join t_employees te
                         on td.department_id = te.department_id;

select department_name, first_name, last_name
from t_employees te
         -- 右外连接查询，右表字段数据内容完整展示，左表字段数据匹配展示，如果没有对应数据内容，显示 null
         -- join 左侧是左表，右侧是右表
         right outer join t_departments td
                         on td.department_id = te.department_id;

# 统计每一个部门的员工人数，要求显示没有员工的部门
select department_name, count(first_name)
from t_departments td
         left outer join t_employees te
                         on td.department_id = te.department_id
group by department_name;

# 没有员工的部门信息
select department_name
from t_departments td
         -- 左外连接查询，左表字段数据内容完整展示，右表字段数据匹配展示，如果没有对应数据内容，显示 null
         -- join 左侧是左表，右侧是右表
         left outer join t_employees te
                         on td.department_id = te.department_id
where first_name is null;

12. 数据约束

12.1 默认 default

# default
# 约束的字段，如果没有在添加数据过程中，明确数据，采用默认值填充
create table person2
(
    id      int,
    name    varchar(32),
    country varchar(32) default '中华人民共和国'
);

# +---------+-------------+------+-----+-----------------------+-------+
# | Field   | Type        | Null | Key | Default               | Extra |
# +---------+-------------+------+-----+-----------------------+-------+
# | id      | int(11)     | YES  |     | NULL                  |       |
# | name    | varchar(32) | YES  |     | NULL                  |       |
# | country | varchar(32) | YES  |     | 中华人民共和国        |       |
# +---------+-------------+------+-----+-----------------------+-------+
# 给予字段赋值数据内容，数据采用用户提供的数据效果
insert into person2(id, name, country)
values (1, '张三', 'PRC');
select *
from person2;

# 没有给予带有默认信息字段数据赋值，采用默认值
insert into person2(id, name)
values (2, '李四');
select *
from person2;

insert into person2(name)
values ('王五');

insert into person2(name, country)
values ('David', null);
select *
from person2;

12.2 非空 not null

# not null ==> NN
# 数据库要求当前字段不可以为 null。不可以为空
create table person3
(
    id      int                      not null,
    name    varchar(32)              not null,
    country varchar(32) default '中国' not null
);
# +---------+-------------+------+-----+---------+-------+
# | Field   | Type        | Null | Key | Default | Extra |
# +---------+-------------+------+-----+---------+-------+
# | id      | int(11)     | NO   |     | NULL    |       |
# | name    | varchar(32) | NO   |     | NULL    |       |
# | country | varchar(32) | NO   |     | 中国    |       |
# +---------+-------------+------+-----+---------+-------+

insert into person3(id, name)
values (1, '李四');

-- Error [1364] Field 'id' doesn't have a default value
-- id 字段没有默认值，而且要求 not null，非空，不可以留白
insert into person3(name)
values ('王五');

# Error [1048] Column 'id' cannot be null
# id 这一列不可以为 null
# Error [1048] Column 'name' cannot be null
# name 这一列不可以为 null
insert into person3(id, name)
values (null, null);

# 查询表中数据
select *
from person3;

12.3 唯一 unique

# unique ==> UNI
# 要求对应字段数据行内容不可以重复，有且只有一个
create table person4
(
    id      int unique,
    name    varchar(32) not null,
    country varchar(32) default 'China'
);

# +---------+-------------+------+-----+---------+-------+
# | Field   | Type        | Null | Key | Default | Extra |
# +---------+-------------+------+-----+---------+-------+
# | id      | int(11)     | YES  | UNI | NULL    |       |
# | name    | varchar(32) | NO   |     | NULL    |       |
# | country | varchar(32) | YES  |     | China   |       |
# +---------+-------------+------+-----+---------+-------+
insert into person4(id, name)
values (1, '少爷');

-- Error [1062] Duplicate entry '1' for key 'id'
-- id 字段唯一修饰，不可以重复
insert into person4(id, name)
values (1, '王五');

-- id 字段唯一修饰，但是 null 不作为唯一判断标准
insert into person4(name)
values ('李四');
insert into person4(name)
values ('赵六');

update person4
set id = 1
where name = '王五';

# 查询表中数据
select *
from person4;

12.4 主键 primary key

# primary key ==> not null + unique 非空 + 唯一
# primary key ==> PRI
# 一张表有且只有一个主键，并且主键修饰字段不具备业务逻辑问题
# 例如: 主键一般用于修饰 id / 编号 性质数据
# 主键修饰一个性别??? 不合适 主键修饰年龄??? 不合适
create table person5
(
    id int primary key,
    name varchar(32) not null ,
    country varchar(32) default '中国' not null
);

# +---------+-------------+------+-----+---------+-------+
# | Field   | Type        | Null | Key | Default | Extra |
# +---------+-------------+------+-----+---------+-------+
# | id      | int(11)     | NO   | PRI | NULL    |       |
# | name    | varchar(32) | NO   |     | NULL    |       |
# | country | varchar(32) | NO   |     | 中国    |       |
# +---------+-------------+------+-----+---------+-------+

insert into person5(id, name)
values (1, '少爷');

# Error [1062] Duplicate entry '1' for key 'id'
# 字段主键修饰，不能重复，唯一
insert into person5(id, name)
values (1, '少爷');

# Error [1364] Field 'id' doesn't have a default value
# id 字段没有默认值，主键修饰对应字段不可以为 null
insert into person5(name)
values ('少爷');

# Error [1048] Column 'id' cannot be null
# id 这一列不能为空
insert into person5(id, name)
values (null, '少爷');

# 查询表中数据
select * from person5;

12.5 自增长

# auto_increment ==> AI
# 要求修饰的字段最起码是一个唯一，通常情况下是 主键
create table person6
(
    id      int primary key auto_increment,
    name    varchar(32)              not null,
    country varchar(32) default '中国' 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   |     | 中国    |                |
# +---------+-------------+------+-----+---------+----------------+

insert into person6(name)
values ('郭德纲');
insert into person6(name)
values ('于谦');

insert into person6(name)
values ('高峰');
insert into person6(name)
values ('栾云平');

# 如果添加数据有超出自增长数据目前的累加情况，会依据当前添加数据自增加修改情况继续累加

# delete 操作删除数据不会影响 自增长累加
delete
from person6
where id = 9;


# update 操作不会影响自增加数据累加
update person6
set id = 9
where name = '朱云峰';


# truncate 清空操作，首先会清空数据表所有数据内容，并且重置自增长数据
truncate person6;

12.6 外键

# 创建 department 表
create table department
(
    id              int primary key auto_increment,
    department_name varchar(32) not null
);

# 创建 person7 表
create table person7
(
    id            int primary key auto_increment,
    name          varchar(32) not null,
    department_id int         not null
);


# 添加 person 信息到 Person7 表中，要求 department_id <==> 部门表 id 存在联系

alter table person7
    add constraint fk_person_department -- 申明外键，同时键入外键的名称 fk_person_department
        foreign key (department_id) -- 当前 Person 7 表中哪一个字段作为外键字段
            references department (id); -- 与指定表指定字段完成外键约束，要求数据表是 department 表，字段要求 id

# 添加数据
insert into person7(name, department_id)
VALUES ('张三', 5);

-- Error [1452] Cannot add or update a child row: a foreign key
# 不能添加或者修改子表数据行内容
-- constraint fails (`javaee_2211`.`person7`, CONSTRAINT `fk_person_department`
-- FOREIGN KEY (`department_id`) REFERENCES `department` (`id`))
# 在 person7 和 department 表之间有外键约束限制，添加 person7 数据行内容会将
# department_id 和department.id 比对校验成功，可以执行操作，校验失败，无法执行
insert into person7(name, department_id)
VALUES ('李四', 15);

-- Error [1452] Cannot add or update a child row: a foreign key
# 不能添加或者修改子表数据行内容
-- constraint fails (`javaee_2211`.`person7`, CONSTRAINT `fk_person_department`
-- FOREIGN KEY (`department_id`) REFERENCES `department` (`id`))
# 在 person7 和 department 表之间有外键约束限制，添加 person7 数据行内容会将
# department_id 和department.id 比对校验成功，可以执行操作，校验失败，无法执行
update person7
set department_id = 10
where name = '王五';

-- Error [1451] Cannot delete or update a parent row: a foreign key constraint fails (`javaee_2211`.`person7`,
# 不能所以删除和修改主表数据，因为存在外键约束，子表数据行如果存在主表数据行信息使用，主表不能轻举妄动
-- CONSTRAINT `fk_person_department` FOREIGN KEY (`department_id`) REFERENCES `department` (`id`))
delete
from department
where id = 5;

-- Error [1451] Cannot delete or update a parent row: a foreign key constraint fails (`javaee_2211`.`person7`,
# 不能所以删除和修改主表数据，因为存在外键约束，子表数据行如果存在主表数据行信息使用，主表不能轻举妄动
-- CONSTRAINT `fk_person_department` FOREIGN KEY (`department_id`) REFERENCES `department` (`id`))
update department
set id = 10
where id = 1;

# 外键约束会导致主表和子表操作存在一定的约束和限制
# 同时影响操作效率和操作范围。
# 外键操作可以有优化条件【级联操作】

12.7 级联操作

alter table person7
    drop foreign key fk_person_department;

alter table person7
    add constraint fk_person_department -- 申明外键，同时键入外键的名称 fk_person_department
        foreign key (department_id) -- 当前 Person 7 表中哪一个字段作为外键字段
            references department (id) -- 与指定表指定字段完成外键约束，要求数据表是 department 表，字段要求 id
            on delete cascade -- 主表删除，子表随之删除
            on update cascade;
-- 主表修改，子表随之修改

# 子表操作依然会校验数据问题
# Error
insert into person7(name, department_id)
VALUES ('原画刘', 10);
# Error
update person7
set id = 10
where id = 1;

# Success 通过
# 主表原本 id 为 1 的数据 ==> id = 10
# 子表使用当前 id 数据的所有数据行全部 department_id 1 ==> 10
# 【级联修改】
update department
set id = 1
where id = 10;

# Success 成功
# 主表删除数据行内容，从表使用当前数据行内容的相关数据全部删除
# 【级联删除】
delete
from department
where id = 5;

# 查询表数据
select *
from person7;