MySQL

MySQL

1. 数据库需求分析

数据存储问题。之前我们使用文件进行数据存储。文件存储有哪些弊端:
	1. 数据存储没有规范，文件存储内容的规范仅限于少数人中，或者当前项目中。
	2. 数据读取，写入操作没有操作规范。无法统一的完成数据的增删改查操作
	3. 数据存储文件情况下，无法限制权限。
	4. 数据存储类型问题。

2. 数据库概述

常见数据库 
	Oracle MySQL SQL Server DB2 sqlite...
	关系型数据库
		Oracle MySQL SQL Server DB2 sqlite
	非关系型数据库
		Redis MongoDB MemoryCache (Key=Value)

3. 数据库系统结构

4. 数据库基本操作，连接和创建

4.1 数据库连接

# 数据库连接
cmd > mysql -hlocalhost -uroot -p123456
# mysql 明确告知计算机当前打开操作的是MySQL数据库

# -hlocalhost -h host 这里需要告知计数器当前数据库服务器所在位置主机名，IP地址，域名，localhost表示当前计算机，本机地址 127.0.0.1。可以写入远程服务器地址。

# -uroot -u user 当前打开数据库的用户名 root是最高权限用户(默认用户) DBA

# -p123456 -p password 对应当前用户的密码。这里不建议在命令行中显式密码
# 推荐本机连接数据库的形式
cmd > mysql -uroot -p
Enter password: 123456

4.2 数据库创建操作

-- 展示当前数据库管理服务器(Database Managerment)中所有数据库 
mysql > show databases;

-- 创建数据库，对应当前项目。数据库的名字不区分大小写，会使用 _ 下划线分割不同的单词
mysql > create database javaee2009;

-- 删库 ==> 跑路
mysql > drop database javaee_2010;

-- 展示当前数据库创建过程，主要是对应编码集
mysql > show create database javaee2009;

-- 选择使用操作的数据 Database changed 接下来所有的数据表操作过程，都是针对当前数据库 javaee2009
mysql > use javaee2009;

4.3 数据表创建和删除

# 创建数据表
mysql > create table student 
(
    # id 字段名，int 数据类型对应Java中的int类型，字段声明使用逗号隔开
    id int,
    # name 字段名，varchar(30) 可变长字符串，最大容量为30字符
    name varchar(30),
    # age 是字段名 年龄 int类型
    age int,
    # info 信息，text长文本
    info text
); 

# 展示当前数据表对应的结构
mysql > desc student;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(11)     | YES  |     | NULL    |       |
| name  | varchar(30) | YES  |     | NULL    |       |
| age   | int(11)     | YES  |     | NULL    |       |
| info  | text        | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+

# 展示当前所选数据库的所有数据表
mysql > show tables;

# 删除数据表
mysql > drop table student;

4.4 数据表创建过程展示和分析

# 展示当前数据表创建过程中经历事情
mysql > show create table student;

# ENGINE=InnoDB DEFAULT CHARSET=utf8
# 在查看数据表创建过程中， Engine是对应当前MySQL数据库的默认存储引擎，这里采用InnoDB。InnoDB 是MySQL中举足轻重的一个存储引擎，提供了很多SQL操作模式和约束方式。
# DEFAULT CHARSET=utf8 默认的编码集

# 创建数据表选择存储引擎和编码集
mysql > create table student2
(
    id int, 
    name varchar(30)
) engine=MyISAM default charset=gbk;

5. 数据表修改[了解]

# alter 修改数据表结构 增删改

# 1. 添加新字段到当前数据表中，默认是在当前数据表的最后一个字段
mysql > alter table student add country varchar(30);
# 2. float(5, 2) 数据位数最长为5位，保留2位小数
mysql > alter table student add score float(5, 2) after info;

# 3. 删除表结构中的指定字段
mysql > alter table student drop country;

# 4. 修改 修改指定字段的名字和对应数据类型
mysql > alter table student change info description varchar(50);

# 5. 修改 指定字段的数据类型
mysql > alter table student modify info text;

6. 数据插入操作 insert

# 规规矩矩的方式 
# insert into tableName(fiedName) values(value);
# 要求student小括号中是完整的字段声明，values中是对应当前字段数据类型的所有
# 有效数据
mysql > insert into student(id, name, age, info, score) values(1, '狗帅帅', 998, '路见不平一声吼，苟帅是个单身狗', -55.5);

# 偷懒模式
# 省略字段声明，但是在values中需要对应所有的字段数据进行赋值操作，一个都不能少
mysql > insert student values(2, '苟磊', 16, '代码不规范~~~锤石你~~~', 100);
# ERROR 1136 (21S01): Column count doesn't match value count at row 1 字段个数和参数个数不符！！！报错！！！！
mysql > insert student values(2, '苟磊', '代码不规范~~~锤石你~~~', 100);

# 常用模式，非常常用！！！
# 指定数据表字段赋值操作，需要声明字段名称和对应的数据，数据类型必须一致
mysql > insert into student(id, name, age) values(3, '华子是你吗？', 999);
mysql > insert into student(score) values(100);

# 测试插入数据情况
mysql > select * from mysql;

# 数据模拟
insert into student values(5, '成龙', 66, '哦', 100);
insert into student values(6, '彭于晏', 36, '帅啊', 950);
insert into student values(7, '吴京', 30, '酷啊', 60);
insert into student values(8, '陈奕迅', 45, '十年', 100.5);
insert into student values(9, '周杰伦', 38, '夜曲', 999.99);
insert into student values(10, '迪丽热巴', 28, '漂亮', 150);
insert into student values(11, '古力娜扎', 28, '真·漂亮', 110);
insert into student values(12, '马尔扎哈', 166, '你谁啊?', 1);
insert into student values(13, '孙俪', 16, '娘娘', 95);
insert into student values(14, '邓超', 18, '从心', 85);
insert into student values(15, '张学友', 56, '吻别', 66.66);
insert into student values(16, '吴彦祖', 46, '帅啊~~~', 99);

7. 数据修改操作 update【慎用】

# 修改演示
# 修改操作小心使用，极有可能会导致所有数据出现问题。这里修改过程中没有任何的
# 条件约束 【语法允许，但是结果有问题】
mysql > update student set name = 'gpw';
mysql > update student set info = '骚磊，苟磊，翘臀，46号技师，秃头，卤蛋';

# 修改id为3的info数据 where 之后就是条件约束
mysql > update student set info = '华哥 第一炉???' where id = 3;

# 可以同时修改多个字段数据，每一个修改字段使用 , 逗号隔开
mysql > update student set 
    name = '华哥', 
    age = 666, 
    info = '华哥想做公务员，下午不上班那种', 
    score = -15 
   		where id = 3;

8. 数据删除操作 delete【慎用】

# 删除数据 因为没有任何的条件约束，会删除整体数据表中的所有数据行！！！
#【语法允许，结果不允许】
mysql > delete from student;

# 约束条件删除
mysql > delete from student where id = 12;

9. 事务操作 回滚【非常重要】

# 用于处理数据库操作中的问题，在开启事务之后，所有的SQL语句不会真实的提交给
# MySQL，只是临时数据和临时效果
# 1. 开启事务 关闭自动提交
mysql > set autocommit = 0;

# 2. 回滚
mysql > rollback;

# 3. 提交
mysql > commit;

10. 数据约束【重点】

10.1 非空

-- not null 要求当前字段不能为空 
-- 在一些数据库可视化工具中 NN
mysql > create table person1
(
    id int not null,
    name varchar(30) not null,
    country varchar(30)
);

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

-- 插入数据
insert into person1(id, name, country) VALUES (1, '气死你的苟磊', 'PRC');
select * from person1;

-- 没有赋值id和name字段
-- ERROR[1364] Field 'id' doesn't have a default value
-- id字段没有默认值
insert into person1(country) VALUES ('PRC');

-- 赋值id，name字段为null，但是存在【非空约束】，不能赋值为null
-- ERROR [1048] Column 'id' cannot be null
-- id字段不能为null
insert into person1(id, name, country) VALUES (null, null, 'PRC');

10.2 默认

-- default 默认值，当用户没有指定当前字段数据，会采用默认值赋值
mysql > create table person2
(
    id int not null, 
    name varchar(30) not null, 
    country varchar(30) default '中国'
);
+---------+-------------+------+-----+---------+-------+
| Field   | Type        | Null | Key | Default | Extra |
+---------+-------------+------+-----+---------+-------+
| id      | int(11)     | NO   |     | NULL    |       |
| name    | varchar(30) | NO   |     | NULL    |       |
| country | varchar(30) | YES  |     | 中国    |       |
+---------+-------------+------+-----+---------+-------+

-- 插入数据，country字段没有赋值数据，会使用默认值赋值操作
insert into person2(id, name) VALUES (1, '苟磊');
select * from person2;

-- 默认值数据可以选择赋值为null
insert into person2(id, name, country) VALUES (2, '特朗普', null);

-- 默认值修饰字段，可以自行赋值数据
insert into person2(id, name, country) VALUES (3, '白等', '澳大利亚');

10.3 唯一

-- 唯一在数据库设计中非常重要，一般用于数据中作为唯一标示的字段修饰 unique 
-- 在数据库可视化软件中会出现 UNI 标记
mysql > create table person3
(
    id int unique, 
    name varchar(30) not null,
    country varchar(30) default 'PRC'
);
+---------+-------------+------+-----+---------+-------+
| Field   | Type        | Null | Key | Default | Extra |
+---------+-------------+------+-----+---------+-------+
| id      | int(11)     | YES  | UNI | NULL    |       |
| name    | varchar(30) | NO   |     | NULL    |       |
| country | varchar(30) | YES  |     | PRC     |       |
+---------+-------------+------+-----+---------+-------+

insert into person3(id, name) values (1, '帅哥');
select * from person3;

-- ERROR [1062] Duplicate entry '1' for key 'id'
-- id 重复，唯一约束要求字段不能重复
insert into person3(id, name) values (1, '华哥');

-- null不作为数据库唯一约束判断，null在数据库中就是无数据！！！
insert into person3(name) values ('健哥');
insert into person3(name) values ('华哥哥');

10.4 主键

-- 非常重要！！！主键 primary key ==> not null + unique
-- 一般情况下数据表中会使用不带有业务逻辑或者数据展示模块来进行主键约束
-- 分析问题: 性别可以做主键??? 年龄可以做主键吗??? 姓名可以做主键吗???
-- 以上数据都不适合使用主键约束
-- 身份证号，ID号，手机号... 这些都可以作为主键约束。
-- 可视化数据库管理工具中，主键缩写存在 PRI
mysql > create table person4
(
    id int primary key, -- 主键约束
    name varchar(30) not null,
    country varchar(30) default 'PRC'
);

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

insert into person4(id, name) values (1, '王石');
select * from person4;

-- ERROR [1364] Field 'id' doesn't have a default value
-- id字段没有默认值，不能为空
insert into person4(name) values ('许家印');

-- ERROR [1062] Duplicate entry '1' for key 'PRIMARY'
-- id为主键，已经存在id 为 1 的数据行，不可以重复
insert into person4(id, name) values (1, '万达');

10.5 自增长

-- 自增长是用于字段在添加数据过程中，自行累加，要求当前字段必须是一个【键 key】
-- 可以是主键，也可以是唯一约束
-- auto_increment 在可视化工具中会出现 AI
mysql > create table person5
(
    id int primary key auto_increment,
	name varchar(30) not null,
    country varchar(30) default 'PRC'
);
+---------+-------------+------+-----+---------+----------------+
| Field   | Type        | Null | Key | Default | Extra          |
+---------+-------------+------+-----+---------+----------------+
| id      | int(11)     | NO   | PRI | NULL    | auto_increment |
| name    | varchar(30) | NO   |     | NULL    |                |
| country | varchar(30) | YES  |     | PRC     |                |
+---------+-------------+------+-----+---------+----------------+

insert into person5(name) values ('郭德纲');
insert into person5(name) values ('于谦');
insert into person5(name) values ('岳云鹏');
insert into person5(name) values ('孙越');

-- 如果在插入数据过程中，对于id进行赋值操作，可能会影响到自增长结果
insert into person5(id, name) values (8, '郭麒麟');
insert into person5(name) values ('阎鹤祥');

-- 在添加数据过程中，涉及到自增长数据赋值操作，如果在赋值过程低于自增长数据
-- 则不会影响原本的自增规律
insert into person5(id, name) values (5, '高峰');
insert into person5(name) values ('栾云平');

insert into person5(name) values ('孟鹤堂');
insert into person5(name) values ('周九良');

delete from person5 where id >= 11;

-- delete删除存在自增长数据表中的数据行，不会影响原本的自增长规律
insert into person5(name) values ('张云雷');
insert into person5(name) values ('杨九郎');

-- 截断数据 清空整个数据表，会将自增长数据回归起点！
truncate person5;

10.6 外键约束

10.6.1 数据库设计分析

-- 员工表
mysql > create table employee
(
    id int primary key auto_increment,
    name varchar(30) not null,
    -- deptName 部门名称
    deptName varchar(30) not null 
);
-- 在当前表结构中，存在于当前数据描述非强制性关系数据。并且部门数据存在大量的冗余
-- 新建一个部门表，员工表绑定对应的部门表ID号使用

mysql > drop table employee;

-- 部门表
mysql > create table dept
(
	id int primary key auto_increment,
    deptName varchar(30) not null
);

-- 员工表设计，这里在员工表字段中使用deptId连接部门表
mysql > create table employee
(
    id int primary key auto_increment,
    name varchar(30) not null,
    -- deptName 部门名称
    deptId int not null
);
-- 插入数据
-- 插入部门表数据
insert into dept(deptName) values ('郑州JavaEE教学部');
insert into dept(deptName) values ('郑州HTML5教学部');
select * from dept;

-- 插入员工数据
insert into employee(name, deptId) VALUES ('老黑', 1);
insert into employee(name, deptId) VALUES ('苟磊', 1);
insert into employee(name, deptId) VALUES ('杨仔', 1);
insert into employee(name, deptId) VALUES ('帅栋', 1);
insert into employee(name, deptId) VALUES ('老万', 1);
insert into employee(name, deptId) VALUES ('博哥', 1);
insert into employee(name, deptId) VALUES ('老高', 2);
insert into employee(name, deptId) VALUES ('阿翔', 2);
insert into employee(name, deptId) VALUES ('安琪', 2);
insert into employee(name, deptId) VALUES ('阿娇', 2);
insert into employee(name, deptId) VALUES ('鹏哥', 2);
insert into employee(name, deptId) VALUES ('辉哥', 2);
select * from employee;

-- 插入数据使用部门ID不存在，但是数据库没有任何的验证和操作。
-- 当前数据不合理！！！
insert into employee(name, deptId) values ('佳佳', 3);
-- 部门表是主表，员工表是从表

10.6.2 外键约束添加

truncate employee;
-- 修改员工表，添加声明外键字段
alter table employee
    -- add constraint 添加声明 employee_dept_id_fk 当前外键名称
    add constraint employee_dept_id_fk
        -- foreign key (deptId) 外键字段声明(deptId)
        -- references dept (id) 引用 dept表中(id字段)
        foreign key (deptId) references dept (id);
        
insert into employee(name, deptId) VALUES ('老黑', 1);
insert into employee(name, deptId) VALUES ('苟磊', 1);
insert into employee(name, deptId) VALUES ('杨仔', 1);
insert into employee(name, deptId) VALUES ('帅栋', 1);
insert into employee(name, deptId) VALUES ('老万', 1);
insert into employee(name, deptId) VALUES ('博哥', 1);
insert into employee(name, deptId) VALUES ('老高', 2);
insert into employee(name, deptId) VALUES ('阿翔', 2);
insert into employee(name, deptId) VALUES ('安琪', 2);
insert into employee(name, deptId) VALUES ('阿娇', 2);
insert into employee(name, deptId) VALUES ('鹏哥', 2);
insert into employee(name, deptId) VALUES ('辉哥', 2);

-- ERROR [1452] Cannot add or update a child row: a foreign key constraint fails
-- (`javaee2009`.`employee`, CONSTRAINT `employee_dept_id_fk` FOREIGN KEY (`deptId`) REFERENCES `dept` (`id`))
-- Cannot add or update a child row 不能添加或者修改从表数据行，因为存在外键约束
-- 当前dept表中没有对应的部门id为3的数据行
insert into employee(name, deptId) values ('佳佳', 3);
-- ERROR [1452] Cannot add or update a child row
update javaee2009.employee set deptId = 5 where id = 1;

-- ERROR [1451] Cannot delete or update a parent row
-- 主表中被从表使用的数据行不能被删除或者修改
delete from dept where id = 1;
-- ERROR [1451] Cannot delete or update a parent row
update dept set id = 10 where id = 1;

-- 外键约束的优劣势
-- 1. 优点 在从表添加数据过程中，可以让MySQL验证所用主表数据是否合理合法
-- 2. 缺点 主表和从表联系过于紧密，会导致修改，删除操作的不便性

10.6.2 外键级联操作

-- 主表修改和删除操作非常麻烦，是因为从表需要使用主表数据。
-- 修改表结构，删除原本employee表中的外键结构
alter table employee drop foreign key employee_dept_id_fk;

-- 添加新的外键约束
alter table employee
    -- 添加声明新的外键 外键名 employee_dept_id_fk
    add constraint employee_dept_id_fk
        -- 从表中外键字段为deptId, 引用dept表中的id号
        foreign key (deptId) references dept (id)
            -- on update cascade 修改级联操作
            -- on delete cascade 删除级联操作
            on update cascade on delete cascade;

-- 修改主表数据行内容，修改数据设计到外键约束主表字段
-- 主表修改，从表随之修改
update dept set id = 10 where id = 1;
select * from dept;
select * from employee;

-- 主表删除，当前使用主表字段的数据随着删除
delete from dept where id = 10;

11. DQL语句查询 select【重中之重】

11.1 查询语句基本格式

mysql > select fieldName 
from tbName;

11.1.1 单一字段单一表查询

-- 查询员工表中所有first_name
mysql > select first_name
from t_employees;

11.1.2 多字段单一表查询

-- 查询员工表中所有first_name, last_name 和 employee_id
mysql > select employee_id, first_name, last_name
from t_employees;

11.1.3 给予查询数据别名操作

-- 查询员工表中所有first_name 名 , last_name 姓 和 employee_id 员工编号
mysql > select employee_id as '员工编号', first_name as '名', last_name as '姓'
from t_employees;

11.1.4 禁用操作

-- 查询当前数据表的所有数据，对数据库效率影响很大！！！
mysql > select * 
from t_employees;

11.1.5 查询字段数据计算

-- 查询所有员工的first_name和年薪
mysql > select first_name as '名', salary * 12 as '年薪'
from t_employees;

11.1.6 去重查询

-- 查询所有的员工first_name，要求数据去重
mysql > select distinct first_name
from t_employees;

11.2 order by 排序查询

11.2.1 单一条件升序排序

-- 按照工资升序排序员工信息，需要查询的字段first_name, salary;
mysql > select first_name, salary
from t_employees
order by salary asc;

-- 查询字段并没有强制要求存在salary字段，在order by之前查询目标中，需要存在对应
-- 的指定字段数据
mysql > select first_name
from t_employees -- from t_employees 表内是存在salary字段
order by salary asc;

11.2.2 单一条件降序排序

-- 按照降序排序员工信息，需要查询的字段first_name, salary;
mysql > select first_name, salary
from t_employees
order by salary desc;

11.2.3 多条件排序

-- 查询员工表，按照部门编号升序，出现相同编号内容按照工资降序
mysql > select employee_id, first_name, department_id, salary
from t_employees
-- 多条件排序数据，不同的条件使用逗号分隔
order by department_id asc, salary desc; 

11.3 条件查询 【重中之重】

11.3.1 基本格式

mysql > select fieldName -- 查什么
from tbName -- 从哪里查
where condition; -- 啥条件

11.3.2 等值判断 =

-- 查询员工id号为150的员工信息
mysql > select employee_id, first_name, last_name
from t_employees
where employee_id = 150;

-- 查询员工first_name 为 Steven 员工信息
mysql > select employee_id, first_name, last_name
from t_employees
where first_name = 'Steven';

11.3.3 关系运算符判断 > < >= <= != <>

-- 查询员工信息，要求id号大于150
mysql > select employee_id, first_name, last_name
from t_employees
where employee_id > 150;

-- 查询员工信息，要求id号小于120
mysql > select employee_id, first_name, last_name
from t_employees
where employee_id < 120;

-- 查询员工信息，要求id号大于等160
mysql > select employee_id, first_name, last_name
from t_employees
where employee_id >= 160;

-- 查询员工信息，要求id号小于等160
mysql > select employee_id, first_name, last_name
from t_employees
where employee_id <= 160;

-- 查询员工信息，要求id号不等于100
mysql > select employee_id, first_name, last_name
from t_employees
where employee_id != 100;

mysql > select employee_id, first_name, last_name
from t_employees
where employee_id <> 100;

11.3.4 逻辑判断 and or not

-- 查询员工信息，要求员工的工资在5000以上 10000以下
mysql > select employee_id, first_name, last_name, salary
from t_employees
where salary >= 5000 and salary < 10000;

-- 查询员工数据，要求员工id小于150，或者工资大于10000
mysql > select employee_id, first_name, last_name, salary
from t_employees
where employee_id < 150 or salary > 10000;

-- 查询员工数据，要求当前员工不是编号为80部门
mysql > select employee_id, first_name, last_name, department_id
from t_employees
where not department_id = 80;

11.3.5 区间 between and

-- 查询员工信息，要求员工的工资在5000以上 10000以下
mysql > select employee_id, first_name, last_name, salary
from t_employees
-- salary >= 5800 and salary <= 10000;
where salary between 5800 and 10000; 

11.3.6 枚举 in

-- 查询部门编号为50, 60, 80的员工
select employee_id, first_name, last_name, department_id
from t_employees
-- department_id = 50 || department_id = 60 || department_id = 80
where department_id in(50, 60, 80); 

-- in 枚举操作常用于子查询

11.3.7 模糊查询 like

-- % _
-- % 表示匹配任意个数字符
-- _ 表示匹配单个字符

-- 找出first_name首字母为P的人名
select first_name
from t_employees
where first_name like 'P%';

-- _ 这里无法使用，因为 'P_' 要求字母P开头，后面有且只有一个字符
select first_name
from t_employees
where first_name like 'P_';

-- 找出人名第二个字母为a的first_name
select first_name
from t_employees
where first_name like '_a%';

-- 要求first_name第二个字母为a，前后各一个字符
select first_name
from t_employees
where first_name like '_a_';

11.3.8 null值判断

-- null 在数据库中表示当前字段没有数据！！！

-- 找出所有有提成的员工名字
select first_name, commission_pct
from t_employees
where commission_pct is not null;

-- 找出没有提成的员工名字
select first_name, commission_pct
from t_employees
where commission_pct is null;

11.3.9 分支结构

-- 数据库查询分支结构
case
    when condition1 then ret1
    when condition2 then ret2
    when condition3 then ret3
end as '别名'
select first_name, salary,
       case
           when salary > 15000 then 'A'
           when salary > 10000 then 'B'
           when salary > 5000 then 'C'
           when salary <= 5000 then 'D'
        end as 'Level'
from t_employees;

11.4 时间函数

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

-- 时间函数
select sysdate();
select current_timestamp();
select curdate();
select curtime();
select week(sysdate());
select hour(curtime());
select minute(curtime());
select second(curtime());

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('ABCDEFG', 1, 3, '华哥真·迟到小王子');
select lower('ABCDEFG');
select upper('abcdefg');
select substring('华哥·第一炉官方代理', 1, 6);

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;

select count(1)
from t_employees;

select count(*)
from t_employees;

11.7 分组查询 group by

-- 格式:
select fieldName 
from tbName
group by fieldName;
-- 要求分组字段必须在目前的查询条件以内

-- 查询各部门人数是多少
-- 1. 按照部门分组
-- 2. 使用count计数来统计人数
select department_id, count(1)
from t_employees
group by department_id;

-- 查询各部门的平均工资
-- 1. 按照部门分组
-- 2. avg工资
select department_id, avg(salary)
from t_employees
group by department_id;

-- 查询各部门，各岗位的人数
-- 1. 按照各部门分组
-- 2. 再按照岗位分组
-- 3. 统计个数
select department_id, job_id, count(1)
from t_employees
group by department_id, job_id;

11.8 分组条件过滤

-- 查询指定100，50，30，80部门id最高工资
-- 1. 使用where条件限制部门范围
-- 2. 按照部门id号进行分组操作
-- 3. 找出对应的最大值
select department_id, max(salary)
from t_employees
where department_id in (100, 50, 30, 80) -- 先条件
group by department_id; -- 后分组

-- 1. 先按照部门ID分组
-- 2. 在分组之后条件中选择部门id号范围 关键having
-- 3. max 取值 salary
select department_id, max(salary)
from t_employees
group by department_id -- 在分组之后加条件约束
having department_id in (100, 50, 30, 80); -- 分组之后的条件约束使用having

-- 如果是分组之后使用条件约束，有且只能操作分组结果【临时表】数据，需要明确临时表中
-- 的字段内容和数据内容。 

11.9 限定查询 limit

select employee_id, first_name, last_name
from t_employees
limit 10;

select employee_id, first_name, last_name
from t_employees
limit 20;

-- 第一页
select employee_id, first_name, last_name
from t_employees
limit 0, 20;

-- 第二页
select employee_id, first_name, last_name
from t_employees
limit 20, 20;

-- 第三页
select employee_id, first_name, last_name
from t_employees
limit 40, 20;

-- 分页内容
-- 1. 当前页码 pageNumber
-- 2. 每一页数据量 count
-- 第一页 (1 - 1) * count, count
-- 第二页 (2 - 1) * count, count
-- 数据库limit限制内容 (pageNumber - 1) * count, count

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 子查询

11.11.1 基本格式

select fieldName
from tbName
where (子查询内容);

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

-- 查询工资高于Jack的员工id和姓名
-- 1. 找出Jack的工资
-- 2. 得到Jack工资，作为条件查询对应的员工信息
select salary
from t_employees
where first_name = 'Jack';

select employee_id, first_name
from t_employees
where salary > 8400;

select employee_id, first_name, salary
from t_employees
where salary > (select salary
                from t_employees
                where first_name = 'Jack');

11.11.3 in 枚举条件 子查询

-- 查询和Jack同部门的员工信息
-- 1. 找出Jack的部门ID
-- 2. in 在同部门的小伙伴
select department_id
from t_employees
where first_name = 'Jack';

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

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

-- 找出部门人数大于5的，员工工资高于7500的员工信息
-- 1. 找出部门ID
select department_id
from t_employees
group by department_id
having count(department_id) > 5;

select employee_id, first_name, department_id, salary
from t_employees
where salary = 7500
  and department_id in (select department_id
                        from t_employees
                        group by department_id
                        having count(department_id) > 5);

11.11.4 子查询结果看作是一张表，作为查询目标

-- 查询员工表中工资前五名的员工信息
-- 1. 排序
select *
from t_employees
order by salary desc;

select employee_id, first_name
from (select *
      from t_employees
      order by salary desc) as temp
limit 5; -- 0

-- Better Answer
select employee_id, first_name
from t_employees
order by salary desc limit 5; -- 5

11.12 表连接查询【重点】

11.12.1 基本格式

select fieldName
from tbName
join tbName
on condition -- 条件

11.12.2 笛卡尔乘积问题、

select employee_id, first_name, department_name
from t_employees, t_departments;

-- 笛卡尔乘积是在多表数据查询过程中，没有任何条件约束的情况下，导致的数据出现多表数
-- 据行数相乘，数据量相当恐怖。
-- 多表连接查询过程中，一定要约束好条件

11.12.3 内连接查询

-- 查询所有部门部门名，和对应的员工信息id和first_name
-- 1. 员工表中的id 和 first_name信息
-- 2. 部门表中的部门名称
-- 3. 联系 员工表中部门ID和部门表中的部门ID

-- 以下写法适用于两张表，但是如果是多表情况下 使用繁琐
select department_name, employee_id, first_name
from t_departments,
     t_employees
where t_departments.department_id = t_employees.department_id;

-- 标准的内连接格式
select department_name, employee_id, first_name
from t_departments
         -- inner join 内连接查询
         inner join t_employees
                    -- 连接查询之后的条件约束
                    on t_departments.department_id = t_employees.department_id;

-- 给予数据表简称，方便操作
select department_name, employee_id, first_name
from t_departments td
         inner join t_employees te on td.department_id = te.department_id

-- 查询所有员工对应的ID号，名字，部门名称，和国家对应名字
-- 1. 员工表 员工ID 名字
-- 2. 部门表 部门名称
-- 3. 国籍表 国家名
-- 4. 中间完成数据串联的一个位置表
select employee_id, first_name, department_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号，名字，工作职称，部门名称，和国家对应名字
select employee_id, first_name, job_title, department_name, country_name, salary
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.4 左外联查询

-- 左外连接 left join
-- 左表字段完整展示，右表字段匹配左表数据展示，如果没有匹配项显示null

-- 展示员工ID号，员工名字，部门名称
-- 期望展示完整的员工ID和名字，内连接查询无法解决当前问题
select employee_id, first_name, department_name
from t_employees te
         left join t_departments td on te.department_id = td.department_id;

11.12.5 右外联查询

-- 右外连接 right join
-- 右表字段完整展示，左表字段匹配右表数据展示，如果没有匹配项显示null
select employee_id, first_name, department_name
from t_employees te
         right join t_departments td on te.department_id = td.department_id;