MySQL基础篇（运算符、排序分页、多表查询、函数）

MySQL基础篇

• ​​数据库概述​​

• ​​数据库与数据库管理系统​​
• ​​数据库与数据库管理系统的关系​​
• ​​Mysql介绍​​
• ​​RDBMS 与 非RDBMS​​

• ​​关系型数据库(RDBMS)​​
• ​​非关系型数据库(非RDBMS）​​

• ​​关系型数据库设计规则​​

• ​​ER模型​​
• ​​ORM​​
• ​​表的关联关系​​

• ​​基本的SELECT语句​​

• ​​SQL分类​​
• ​​SQL的规范​​

• ​​注释​​
• ​​命名规范​​
• ​​导入sql​​
• ​​导出sql​​

• ​​基本语句​​

• ​​查看所有​​
• ​​选择特定的列​​
• ​​去除重复行​​
• ​​空值参与运算​​

• ​​显示表结构​​
• ​​过滤数据​​

• ​​运算符​​

• ​​算术运算符​​
• ​​比较运算符​​
• ​​逻辑运算符​​
• ​​位运算符​​
• ​​运算符优先级​​
• ​​练习​​

• ​​排序和分页​​

• ​​排序规则​​
• ​​多列排序​​
• ​​分页​​
• ​​练习​​

• ​​多表查询​​

• ​​SQL连接标准 SQL92\SQL99​​
• ​​分类​​

• ​​等值连接 vs 非等值连接​​
• ​​自连接 vs 非自连接​​
• ​​内连接 vs 外连接​​
• ​​满外连接(FULL OUTER JOIN)​​

• ​​UNION的使用​​

• ​​UNION​​
• ​​UNION ALL​​

• ​​七种SQL JOINS​​
• ​​练习​​

• ​​多表查询联系​​
• ​​SQL JOINS练习​​

• ​​多表查询容易出现的问题​​

• ​​使用外连接连接出现重复记录​​

• ​​别名加不加引号问题​​

• ​​单行函数​​

• ​​不同DBMS函数的差异​​
• ​​MySQL的内置函数及分类​​
• ​​数值函数、字符串函数、日期和时间函数​​
• ​​流程控制函数​​

• ​​示例​​

• ​​加密与解密、信息、其他函数​​
• ​​练习​​

• ​​聚合函数​​

• ​​聚合函数类型​​
• ​​COUNT函数​​
• ​​能不能使用count(列名)替换count(*)?​​
• ​​count(列名)和count(*)效率​​
• ​​聚合函数示例​​
• ​​GROUP BY的使用​​

• ​​GROUP BY注意事项​​

• ​​HAVING的使用​​

• ​​WHERE和HAVING的对比​​

• ​​SELECT的执行原理​​
• ​​练习​​
• ​​易错点​​

数据库概述

数据库与数据库管理系统

DB（Database）：数据库

DBMS（Database Management System）：数据库管理系统

SQL：结构化查询语言

数据库与数据库管理系统的关系

一句话总结：DBMS就是管理数据库的软件，如Mysql、Oracle、SQLServer、Redis…

Mysql介绍

MySQL从5.7版本直接跳跃发布了8.0版本,mysql8对源代码进行了重构，最突出的一点是多MySQL Optimizer优化 器进行了改进，性能进一步得到提升

Oracle vs MySQL怎么选？

• Oracle性能、安全性高，但是收费
• Mysql体积小，成本低，代码开源

RDBMS 与 非RDBMS

RDBMS（Relational Database Management System）关系型数据库管理系统

关系型数据库(RDBMS)

关系型数据库就是把复杂的数据结构变成二元结构，也就是二维表格形式

• 关系型数据库以**行（row）和列（column）**的形式存储数据，便于理解。这一系列的行和列组合成表，一组表组合成库
• 表与表之间的数据记录有关系。通过关系模型来表示，关系型数据库就是建立在关系模型基础上的数据库
• SQL就是RDB的查询语言

非关系型数据库(非RDBMS）

可以看作关系型数据库的阉割版，它基于key-value存储值，不需要经过SQL层的解析，性能非常高

Redis、mongo、ES

关系型数据库设计规则

ER模型

平时说的ER（entity-relationship）模型，就是实体之间的联系模型

• 模型中有三个概念是：实体集、属性、联系集
• 一个实体集对应一个表
• 一个实体对应一行，也成为一条记录
• 一个属性对应一列，也称为一个字段

ORM

表 ----- 类

表中数据 ---- 对象

表中列 ---- 属性

表的关联关系

表与表之间的**数据记录**有关系(relationship)。现实世界中的各种实体以及实体之间的各种联系均用 关系模型来表示

分别是以下四种：

• 一对一

• 设计学生表、可以分为常用信息和不常用信息分别建表
• 每个常用信息记录会 一 一 对应不常用信息记录

• 一对多

• 客户表和订单表、分类表和商品表、部门表和员工表
• 一个客户数据可以对应订单表中的多条数据，一个分类可以对应多个商品、一个部门可以有多个员工

• 多对多

• 多对对关系必须用三个表来表示，第三个表被称为连接表，它将多对多关系划分成两个一对多关系
• 一门课程可以被多个学生选择，一学生可以选择多个课程

• 自我引用

基本的SELECT语句

SQL分类

SQL是一种规范，Mysql和Oracle都有对应的实现，但它们都属于SQL

下图形象表达了sql，和DBMS的关系

SQL在功能上可分为：

• DDL（Data Definition Languages、数据定义语言）

• creatre、drop、alter关键字都属于DDL

• DML（Data Manipulation Language、数据操作语言）

• insert、delete、update、select关键字都属于DML

• DCL（Data Control Language、数据控制语言）

• grant、revoke、commit、rollback关键字都属于DCL

因为SQL中查询用的最为频繁，所以也可以把查询语句相关的定义为DQL（Data Query Language、数据查询语言）

还有commit、rollback取出来称为TCL（Transaction Control Language，事务控制语言）

SQL的规范

这个点其实可以在数据库图形化软件中学习，你写好语句然后点击优化，软件给你优化后的就是标准的格式

特殊点注意：

• 反引号：它是为了区分MYSQL的保留字与普通字符而引入的符号。
• 当我们要那他们做表名或字段名的时候，我们要加反引号用于区分避免编译器把这部分认为是保留字而产生错误
• MySQL 在 Linux 环境下是大小写敏感的

• 数据库名、表名、表的别名、变量名严格区分大小写

• Mysql在windows不区分大小写

注释

单行注释：\#注释文字(MySQL特有的方式)
单行注释：-- 注释文字(--后面必须包含一个空格。)
多行注释：/* 注释文字 */

命名规范

导入sql

通过source命令进行导入

win：
mysql> source d:\mysqldb.sql
linux：
mysql> source /home/mysql/w3h5.sql;  或者  mysql -uroot -p123456 w3h5 < /home/mysql/w3h5.sql;

如果已经登陆数据库了，直接输入后面的部分：数据库名 < 数据库文件;
dbname < /home/mysql/data.sql

导出sql

mysqldump -uroot -p dbname > data.sql;
最好是加上路径/数据库文件名
mysqldump -uroot -p dbname > /home/mysql/w3h5.sql;

基本语句

下面通过测试如下表来学习语句使用

查看所有

SELECT
  * 
FROM
  pms_brand;

选择特定的列

SELECT
  id,name,sort,logo 
FROM
  pms_brand;

给列起别名

通过AS关键字给列起别名，别名便于计算，见名知意

AS可省略

SELECT
  id id值,
  NAME 名字,
  sort 别名,
  logo 品牌 
FROM
  pms_brand;

去除重复行

使用关键字DISTINCT去除重复行

SELECT DISTINCT
  sort 
FROM
  pms_brand;

空值参与运算

所有空值参与运算的，结果都为null

在mysql中，空值不等于空字符串。一个空字符串的长度是0。空值的长度是空，它是占用空间的！

显示表结构

使用DESC或全程DESCRIBE

DESC pms_brand;

• Fileld：字段名称
• Type：字段类型
• Null：改列是否能存NULL值
• Key：表示该列是否已编制索引。PRI表示该列是表主键的一部分；UNI表示该列是UNIQUE索引的一部分；MUL表示允许出现多次
• Default：表示该列默认值，如果有，值是多少。
• Extra：表示可以获取的与给定列有关的附加信息，例如AUTO_INCREMENT等

过滤数据

通过WHERE关键字来过滤数据

SELECT
  id,
  NAME,
  sort,
  logo 
FROM
  pms_brand
WHERE
  id = 1

运算符

算术运算符

在除法运算和模运算中，如果除数为0，将是非法除数，返回结果为NULL。

比较运算符

通过这些比较运算符，可以判断表中的哪些记录是符合条件的。

• 结果为真则返回 1
• 为假则返回 0
• 不确定则返回 NULL

常用实例

1、安全等于与 = 的区别在于当两个操作码均为 NULL 时，其所得值为 1 而不为 NULL，而当一个操作码为 NULL 时，其所得值为 0而不为 NULL。
mysql> select 2<=>3;
+-------+
| 2<=>3 |
+-------+
|     0 |
+-------+


mysql> select null=null;
+-----------+
| null=null |
+-----------+
|      NULL |
+-----------+

        
mysql> select null<=>null;
+-------------+
| null<=>null |
+-------------+
|           1 |
+-------------+


2、BETWEEN关键字的使用

mysql> select 5 between 1 and 10;
+--------------------+
| 5 between 1 and 10 |
+--------------------+
|                  1 |
+--------------------+

3、IN关键字的使用
mysql> select 5 in (1,2,3,4,5);
+------------------+
| 5 in (1,2,3,4,5) |
+------------------+
|                1 |
+------------------+

4、LIKE关键字的使用
mysql> select '12345' like '12%';
+--------------------+
| '12345' like '12%' |
+--------------------+
|                  1 |
+--------------------+

5、正则匹配的使用
mysql> select 'beijing' REGEXP 'xi';
+-----------------------+
| 'beijing' REGEXP 'xi' |
+-----------------------+
|                     0 |
+-----------------------+

逻辑运算符

逻辑运算符用来判断表达式的真假。如果表达式是真，结果返回 1。如果表达式是假，结果返回 0。

位运算符

位运算符是在二进制数上进行计算的运算符。位运算会先将操作数变成二进制数，进行位运算。然后再将计算结果从二进制数变回十进制数。

• 异或是相同为0，不同为1
• 右移也就是把二进制各个位向右边推一位，最后就相当于除以2了
• 左移同理把二进制各个数位向左边推一位，相当于乘2

1、异或运算
mysql> select 3^5;
+-----+
| 3^5 |
+-----+
|   6 |
+-----+

2、按位右移
mysql> select 3>>1;
+------+
| 3>>1 |
+------+
|    1 |
+------+

3、位左
mysql> select 3<<1;
+------+
| 3<<1 |
+------+
|    6 |
+------+

运算符优先级

练习

# 1.选择工资不在5000到12000的员工的姓名和工资
SELECT
  last_name,
  salary 
FROM
  employees 
WHERE
  salary NOT BETWEEN 5000 
  AND 12000
  
# 2.选择在20或50号部门工作的员工姓名和部门号
SELECT
  last_name,
  department_id 
FROM
  employees 
WHERE
  department_id BETWEEN 20 
  AND 50


# 3.选择公司中没有管理者的员工姓名及job_id
SELECT
  last_name,
  job_id 
FROM
  employees 
WHERE
  manager_id IS NULL

# 4.选择公司中有奖金的员工姓名，工资和奖金级别
SELECT
  last_name,
  salary,
  commission_pct 
FROM
  employees 
WHERE
  commission_pct IS NOT NULL

# 5.选择员工姓名的第三个字母是a的员工姓名
SELECT
  last_name
FROM
  employees 
WHERE
  last_name LIKE '__a%';

# 6.选择姓名中有字母a和k的员工姓名
SELECT
  last_name
FROM
  employees 
WHERE
  last_name LIKE '%a%k%' OR last_name LIKE '%k%a%';

# 7.显示出表 employees 表中 first_name 以 'e'结尾的员工信息
SELECT
  first_name
FROM
  employees 
WHERE
  first_name LIKE '%e';

# 8.显示出表 employees 部门编号在 80-100 之间的姓名、工种
SELECT
  last_name,
  job_id 
FROM
  employees 
WHERE
  department_id BETWEEN 80 
  AND 100;


# 9.显示出表 employees 的 manager_id 是 100,101,110 的员工姓名、工资、管理者id
SELECT
  last_name,
  salary,
  manager_id
FROM
  employees 
WHERE
  manager_id IN(100,101,110);

排序和分页

排序规则

排序使用ORDER BY子句排序

• ASC：升序
• DESC：降序

ORDER BY子句在SELECT语句的结尾

语法如下：

SELECT field1, field2,...fieldN FROM table_name1, table_name2...
ORDER BY field1 [ASC [DESC][默认 ASC]], [field2...] [ASC [DESC][默认 ASC]]

SELECT
  last_name,
  job_id,
  department_id,
  hire_date
FROM
  employees
ORDER BY
  hire_date

多列排序

SELECT
  last_name,
  job_id,
  department_id,
  hire_date 
FROM
  employees 
ORDER BY
  hire_date,
  salary

两列排序策略一致的情况下,如下图：

分页

分页原理：将数据库中结果集一段一段显示出来

使用LIMIT关键字实现分页

LIMIT [位置偏移量,] 行数

偏移量指的是参数指示Mysql从哪一行开始显示，是一个可选参数

如果不指定，默认从第一行开始显示（第一条记录偏移量是0）

第二个行数，表示返回的记录数

实例：

从第5条记录开始后面的3条记录
以下为两种写法
SELECT
  last_name,
  job_id,
  department_id,
  hire_date 
FROM
  employees 
  LIMIT 4,3

SELECT
  last_name,
  job_id,
  department_id,
  hire_date 
FROM
  employees 
  LIMIT 3 OFFSET 4

*分页显式公式：（当前页数-1）每页条数，每页条数

SELECT * FROM table LIMIT(PageNo - 1)*PageSize,PageSize;

约束的好处就是可以减少数据表的网络传输量，也可以提升查询效率

如果我们指定返回结果就是一条，就可以使用LIMIT1，告诉SELECT语句只需要返回一条记录即可，这样的好处就是SELECT不需要扫描完整的表，只需要检索到一条符合条件记录即可返回

练习

#1. 查询员工的姓名和部门号和年薪，按年薪降序,按姓名升序显示
SELECT
  last_name,
  department_id,
  salary * 12 annual_sal 
FROM
  employees 
ORDER BY
  annual_sal DESC,
  first_name ASC

#2. 选择工资不在 8000 到 17000 的员工的姓名和工资，按工资降序，显示第21到40位置的数据
SELECT
  last_name,
  salary 
FROM
  employees 
WHERE
  salary NOT BETWEEN 8000 AND 17000 
ORDER BY
  salary DESC 
  LIMIT 20,20

#3. 查询邮箱中包含 e 的员工信息，并先按邮箱的字节数降序，再按部门号升序
SELECT
  last_name,
  email,
  department_id
FROM
  employees 
WHERE
  email LIKE '%e%'
ORDER BY
  LENGTH(email),
  department_id ASC

多表查询

多个表一起查询，表与表之间是有关系的（一对一、一对多），它们之间一定有关联字段（唯一），这个关联字段可能建立了外键，也可能没有建立外键。

笛卡尔积的错误会在下面条件下产生：

• 省略多个表的连接条件
• 连接条件（或者是关联条件）无效
• 所有表中的所有行互相连接

为了避免它，可以在WHERE加入有效的连接条件

SQL连接标准 SQL92\SQL99

• SQL92

• 形式简单, 语句较长, 可读性较差.

• SQL99

• 语法更为复杂, 可读性强

SQL92中采用(+)代表从表所在位置

SELECT * FROM player, team WHERE player.team_id = team.team_id(+);

在SQL99中:

SELECT * FROM player LEFT JOIN team ON player.team_id = team.team_id;

SQL92查询中, 把所有需要连接的表放在FROM后, 在WHERE中写明连接的条件.

SQL99更灵活, 不需要一次性把所有需要连接的表放在from后, 而是采用JOIN方式, 每次连接一张表, 可以多次使用join进行连接.

建议多表连接使用SQL99标准, 可读性强.

分类

等值连接 vs 非等值连接

等值连接

• 每一列记录都有与之对应的值，如下图示

非等值连接

• 记录部分对应，不在是全部对应，如下图示

自连接 vs 非自连接

自连接

• 自己连接自己，本质是同一张表，通过取别名的形式对表形成不同的意义

内连接 vs 外连接

• INNER JOIN（内连接,或等值连接）：合并具有同一列的两个以上的表的行，结果集中不包含一个表与另一个表不匹配的行
• **LEFT JOIN（左连接）：两个表在连接过程中除了返回满足连接条件的行以外还返回左（或右）表中不满足条件的 行 ，这种连接称为左（或右） 外连接。**没有匹配的行时, 结果表中相应的列为空(NULL)。
• RIGHT JOIN（右连接）： 两个表在连接过程中除了返回满足连接条件的行以外还**返回右表中不满足条件的行 ，这种连接称为右外连接。**没有匹配的行时, 结果表中相应的列为空(NULL)。

SELECT
  e.employee_id,
  e.last_name,
  e.department_id,
  d.department_id,
  d.location_id 
FROM
  employees e
  JOIN departments d ON ( e.department_id = d.department_id );

left join 是left outer join的简写，它的全称是左外连接，是外连接中的一种。

SELECT
  e.last_name,
  e.department_id,
  d.department_name 
FROM
  employees e
  LEFT OUTER JOIN departments d ON ( e.department_id = d.department_id );

SELECT
  e.last_name,
  e.department_id,
  d.department_name 
FROM
  employees e
  RIGHT OUTER JOIN departments d ON ( e.department_id = d.department_id );

需要注意的是，LEFT JOIN 和 RIGHT JOIN 只存在于 SQL99 及以后的标准中，在 SQL92 中不存在， 只能用 (+) 表示。

满外连接(FULL OUTER JOIN)

• 满外连接的结果 = 左右表匹配的数据 + 左表没有匹配到的数据 + 右表没有匹配到的数据。
• SQL99是支持满外连接的。使用FULL JOIN 或 FULL OUTER JOIN来实现。
• 需要注意的是，MySQL不支持FULL JOIN，但是可以用 LEFT JOIN UNION RIGHT join代替。

UNION的使用

合并查询结果 利用UNION关键字，可以给出多条SELECT语句，并将它们的结果组合成单个结果集。合并 时，两个表对应的列数和数据类型必须相同，并且相互对应。各个SELECT语句之间使用UNION或UNION ALL关键字分隔。

UNION

SELECT column,... FROM table1 UNION [ALL] SELECT column,... FROM table2

UNION 操作符返回两个查询的结果集的并集，去除重复记录。

UNION ALL

和上面比起来，UNION没有进行去重操作

七种SQL JOINS

-- 七种SQL JOIN
-- 1、内连接
-- A∩B
SELECT
  e.employee_id,
  e.last_name,
  d.department_name 
FROM
  employees e
  JOIN departments d ON e.department_id = d.department_id;
  
  
-- 2、左连接
SELECT
  e.employee_id,
  e.last_name,
  d.department_name 
FROM
  employees e
  LEFT JOIN departments d ON e.department_id = d.department_id;


-- 3、右连接
SELECT
  e.employee_id,
  e.last_name,
  d.department_name 
FROM
  employees e
  RIGHT JOIN departments d ON e.department_id = d.department_id;


-- 4、左中位置的实现
-- 表达的意思就是员工没有部门的
SELECT
  e.employee_id,
  e.last_name,
  d.department_name 
FROM
  employees e
  LEFT JOIN departments d ON e.department_id = d.department_id 
WHERE
  d.department_id IS NULL;
-- 为什么在左连接的基础上加上WHERE d.department_id IS NULL就实现了？
-- 因为左连接是合并指定列的行，返回包含满足条件的和左表所有的行
-- 那么这个图意思就是在左连接查询结果的基础上，舍去同时满足的部分
-- 那同时满足的条件是什么呢？e.department_id = d.department_id
-- 所以我们在加上WHERE进行约束取e.department_id IS NULL或d.department_id IS NULL
-- 最好用d.department_id IS NULL这种方式


-- 5、右中位置的实现
-- 同上
SELECT
  e.employee_id,
  e.last_name,
  d.department_name 
FROM
  employees e
  RIGHT JOIN departments d ON e.department_id = d.department_id
WHERE
  e.department_id IS NULL;

-- 6、满外连接
-- 通过UNION ALL进行连接(左连接和右中位置的实现)
-- UNION ALL效率更高，它不必去重
-- UNION ALL连接的表，显示的字段、类型必须一致
SELECT
  e.employee_id,
  e.last_name,
  d.department_name 
FROM
  employees e
  LEFT JOIN departments d ON e.department_id = d.department_id UNION ALL
SELECT
  e.employee_id,
  e.last_name,
  d.department_name 
FROM
  employees e
  RIGHT JOIN departments d ON e.department_id = d.department_id 
WHERE
  e.department_id IS NULL;
  
-- 7、右下位置实现
-- 通过UNION ALL进行连接(左中位置实现和右中位置的实现)
SELECT
  e.employee_id,
  e.last_name,
  d.department_name 
FROM
  employees e
  LEFT JOIN departments d ON e.department_id = d.department_id 
WHERE
  d.department_id IS NULL UNION ALL
SELECT
  e.employee_id,
  e.last_name,
  d.department_name 
FROM
  employees e
  RIGHT JOIN departments d ON e.department_id = d.department_id 
WHERE
  e.department_id IS NULL;

练习

多表查询联系

# 1.显示所有员工的姓名，部门号和部门名称。
SELECT
  e.last_name,
  e.department_id,
  d.department_name 
FROM
  employees e
  LEFT JOIN departments d ON e.department_id = d.department_id


# 2.查询90号部门员工的job_id和90号部门的location_id
SELECT
  e.job_id,
  d.location_id 
FROM
  employees e
  LEFT JOIN departments d ON e.department_id = d.department_id 
WHERE
  e.department_id = 90
  

# 3.选择所有有奖金的员工的 last_name , department_name , location_id , city
SELECT
  e.last_name,
  d.department_name,
  l.location_id,
  l.city 
FROM
  employees e
  LEFT JOIN departments d ON e.department_id = d.department_id
  LEFT JOIN locations l ON d.location_id = l.location_id
WHERE
  commission_pct IS NOT NULL;


# 4.选择city在Toronto工作的员工的 last_name , job_id , department_id , department_name
SELECT
  e.last_name,
  e.job_id,
  d.department_name,
  d.department_id 
FROM
  employees e
  LEFT JOIN departments d ON e.department_id = d.department_id
  LEFT JOIN locations l ON d.location_id = l.location_id 
WHERE
  l.city = 'Toronto';


# 5.查询员工所在的部门名称、部门地址、姓名、工作、工资，其中员工所在部门的部门名称为’Executive’
SELECT
  d.department_name,
  l.street_address,
  e.last_name,
  e.job_id,
  e.salary
FROM
  employees e
  LEFT JOIN departments d ON e.department_id = d.department_id
  LEFT JOIN locations l ON d.location_id = l.location_id 
WHERE
  d.department_name = 'Executive';


# 6.选择指定员工的姓名，员工号，以及他的管理者的姓名和员工号，结果类似于下面的格式
employees Emp   manager   Mgr
kochhar   101   king      100

SELECT
  emp.last_name employees,
  emp.employee_id "Emp#",
  mgr.last_name manager,
  mgr.employee_id "Mgr#" 
FROM
  employees emp
  LEFT OUTER JOIN employees mgr ON emp.manager_id = mgr.employee_id;

# 7.查询哪些部门没有员工
SELECT
  d.department_id 
FROM
  departments d
  LEFT JOIN employees e ON e.department_id = d.department_id
WHERE
  e.department_id IS NULL;


# 8. 查询哪个城市没有部门
SELECT
  l.city 
FROM
  locations l
  LEFT JOIN departments d ON l.location_id = d.location_id
WHERE
  d.location_id IS NULL;


# 9. 查询部门名为 Sales 或 IT 的员工信息
SELECT
  e.last_name,
  e.salary,
  e.email,
  e.phone_number 
FROM
  employees e
  JOIN departments d ON e.department_id = d.department_id 
WHERE
  department_name = 'Sales' 
  OR department_name = 'IT'

SQL JOINS练习

CREATE TABLE `t_dept` (
`id` INT(11) NOT NULL AUTO_INCREMENT,
`deptName` VARCHAR(30) DEFAULT NULL,
`address` VARCHAR(40) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=INNODB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

CREATE TABLE `t_emp` (
`id` INT(11) NOT NULL AUTO_INCREMENT,
`name` VARCHAR(20) DEFAULT NULL,
`age` INT(3) DEFAULT NULL,
`deptId` INT(11) DEFAULT NULL,
empno int not null,
PRIMARY KEY (`id`),
KEY `idx_dept_id` (`deptId`)
#CONSTRAINT `fk_dept_id` FOREIGN KEY (`deptId`) REFERENCES `t_dept` (`id`)
) ENGINE=INNODB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;


-- 1. 所有有门派的人员信息
-- （ A、B两表共有）
INSERT INTO t_dept(deptName,address) VALUES('华山','华山');
INSERT INTO t_dept(deptName,address) VALUES('丐帮','洛阳');
INSERT INTO t_dept(deptName,address) VALUES('峨眉','峨眉山');
INSERT INTO t_dept(deptName,address) VALUES('武当','武当山');
INSERT INTO t_dept(deptName,address) VALUES('明教','光明顶');
INSERT INTO t_dept(deptName,address) VALUES('少林','少林寺');
INSERT INTO t_emp(NAME,age,deptId,empno) VALUES('风清扬',90,1,100001);
INSERT INTO t_emp(NAME,age,deptId,empno) VALUES('岳不群',50,1,100002);
INSERT INTO t_emp(NAME,age,deptId,empno) VALUES('令狐冲',24,1,100003);
INSERT INTO t_emp(NAME,age,deptId,empno) VALUES('洪七公',70,2,100004);
INSERT INTO t_emp(NAME,age,deptId,empno) VALUES('乔峰',35,2,100005);
INSERT INTO t_emp(NAME,age,deptId,empno) VALUES('灭绝师太',70,3,100006);
INSERT INTO t_emp(NAME,age,deptId,empno) VALUES('周芷若',20,3,100007);
INSERT INTO t_emp(NAME,age,deptId,empno) VALUES('张三丰',100,4,100008);
INSERT INTO t_emp(NAME,age,deptId,empno) VALUES('张无忌',25,5,100009);
INSERT INTO t_emp(NAME,age,deptId,empno) VALUES('韦小宝',18,null,100010);


#1.所有有门派的人员信息
-- （ A、B两表共有）
SELECT
  * 
FROM
  t_dept d
  JOIN t_emp e ON d.id = e.deptId



#2.列出所有用户，并显示其机构信息
-- （A的全集）
SELECT
  e.`name`,
  e.`age`,
  d.`deptName`
FROM
  t_emp e
  JOIN t_dept d ON d.id = e.deptId


#3.列出所有门派
-- （B的全集）
SELECT
 *
FROM
  t_dept


#4.所有不入门派的人员
-- （A的独有）
SELECT
  e.`name`,
  e.`age`
FROM
  t_emp e
  LEFT JOIN t_dept d ON e.deptId = d.id 
WHERE
  d.id IS NULL
  


#5.所有没人入的门派
-- （B的独有）
SELECT
  d.deptName,
  d.address
FROM
  t_dept d
  LEFT JOIN t_emp e ON d.id = e.deptId
WHERE
  e.deptId IS NULL


#6.列出所有人员和机构的对照关系
-- (AB全有)
SELECT
  e.NAME,
  e.age,
  d.deptName,
  d.address 
FROM
  t_dept d
  RIGHT JOIN t_emp e ON d.id = e.deptId UNION ALL
SELECT
  e.NAME,
  e.age,
  d.deptName,
  d.address 
FROM
  t_emp e
  LEFT JOIN t_dept d ON e.deptId = d.id 
WHERE
  d.id IS NULL

#MySQL Full Join的实现 因为MySQL不支持FULL JOIN,下面是替代方法
#left join + union(可去除重复数据)+ right join


#7.列出所有没入派的人员和没人入的门派
-- （A的独有+B的独有）
SELECT
  e.NAME,
  e.age,
  d.deptName,
  d.address 
FROM
  t_emp e
  LEFT JOIN t_dept d ON e.deptId = d.id 
WHERE
  d.id IS NULL UNION ALL
SELECT
  e.NAME,
  e.age,
  d.deptName,
  d.address 
FROM
  t_dept d
  LEFT JOIN t_emp e ON d.id = e.deptId
WHERE
  e.deptId IS NULL;

多表查询容易出现的问题

使用外连接连接出现重复记录

在使用连接查询的时候，例如以A表为主表，左连接B表，我们期望的是A表有多少条记录，查询结果就有多少条记录，但是可能会出现这样的结果，就是查询出来的记录总条数，多余A表的记录总条数，并且是查询的结果显示出来时有部分列是重复的，简单的说，就是产生了​​笛卡尔积​​。

解决的办法就是采用唯一键去关联做链接查询

别名加不加引号问题

1.mysql中在设置别名时 如果没有特殊字符空格 可以不要加 引号

我这里的别名起的中间有空格，这属于特殊字符

SELECT
  CONCAT(
    last_name,
    ' earns ',
    TRUNCATE ( salary, 0 ),
    ' monthly but he wants ',
  TRUNCATE ( salary * 3, 0 )) 'Dream Salary'
FROM
  employees

2.mysql中设置了引号别名 如果没有特殊字符引用时 可以直接引用 不要加引号

3.如果别名中含有特殊字符 那么需要加引号 引用时 用飘号包裹别名

单行函数

不同DBMS函数的差异

DBMS 之间的差异性很大，远大于同一个语言不同版本之间的差异。

比如：大部分DBMS使用（||）或者（+）来做拼接符，而在MySql中的字符拼接函数为concat()。

采用SQL函数的代码可移植性是很差的

MySQL的内置函数及分类

MySQL提供的内置函数从实现的功能角度可以分为:

• 数值函数
• 字符串函数
• 日期和时间函数
• 流程控制 函数
• 加密与解密函数
• 获取MySQL信息函数
• 聚合函数

分为两大类：

• 单行函数

• 操作数据对象
• 接受参数返回一个结果
• 只对一行进行变换
• 每行返回一个结果
• 可以嵌套
• 参数可以是一列或一个值

• 聚合函数（或分组函数）

数值函数、字符串函数、日期和时间函数

​​https://www.runoob.com/mysql/mysql-functions.html​​

流程控制函数

流程处理函数可以根据不同的条件，执行不同的处理流程，可以在SQL语句中实现不同的条件选择。 MySQL中的流程处理函数主要包括IF()、IFNULL()和CASE()函数。

示例

Mysql会把函数当成字段，所以我们一般给它起个别名

-- 1、IF(value,value1,value2)
-- 如果value的值为TRUE，返回value1， 否则返回value2
SELECT IF(1>0,'data1','data2')

-- 2、IFNULL(value1, value2)
-- 如果value1不为NULL，返回value1，否 则返回value2
SELECT IF(1>0,'data1','data2')

-- 3、CASE WHEN 条件1 THEN 结果1 WHEN 条件2 THEN 结果2 .... [ELSE resultn] END
-- 相当于Java的if...else if...else...
SELECT
  last_name,
  salary,
CASE
  WHEN salary >= 15000 THEN '高薪'   
  WHEN salary >= 10000 THEN '底薪' 
  ELSE '半残' 
END 'details'
FROM
  employees
  
-- 4、CASE expr WHEN 常量值1 THEN 值1 WHEN 常量值1 THEN 值1 .... [ELSE 值n] END
-- 相当于Java的switch...case...
SELECT
  last_name,
  salary,
CASE 
  salary >= 15000
  WHEN salary >= 15000 THEN '高薪' 
  WHEN salary >= 10000 THEN '底薪' 
  ELSE '半残' 
END 'details'
FROM
  employees

加密与解密、信息、其他函数

MySQL中内置了一些可以查询MySQL信息的函数，这些函数主要用于帮助数据库开发或运维人员更好地 对数据库进行维护工作。

MySQL中有些函数无法对其进行具体的分类，但是这些函数在MySQL的开发和运维过程中也是不容忽视 的。

**

练习

# 1.显示系统时间(注：日期+时间)
SELECT NOW()

# 2.查询员工号，姓名，工资，以及工资提高百分之20%后的结果（new salary）
SELECT
  employee_id,
  last_name,
  salary * 1.2 'new salary'
FROM
 employees

# 3.将员工的姓名按首字母排序，并写出姓名的长度（length）
SELECT
  employee_id,
  LENGTH( last_name ) last_name 
FROM
  employees 
ORDER BY
  first_name DESC;

# 4.查询员工id,last_name,salary，并作为一个列输出，别名为OUT_PUT
SELECT
  CONCAT( employee_id, last_name, salary ) OUT_PUT 
FROM
  employees

# 5.查询公司各员工工作的年数、工作的天数，并按工作年数的降序排序
SELECT
  DATEDIFF(SYSDATE(),hire_date) / 365 worked_years,
  DATEDIFF(SYSDATE(),hire_date) worked_days
FROM
  employees
ORDER BY 
worked_years DESC

# 6.查询员工姓名，hire_date , department_id，满足以下条件：雇用时间在1997年之后，department_id
-- 为80 或 90 或110, commission_pct不为空
SELECT
  last_name,
  hire_date,
  department_id
FROM
  employees
WHERE
  DATE_FORMAT(hire_date,'%Y') >= '1997'
AND
  department_id IN (80,90,110)
AND
  commission_pct IS NOT NULL

# 7.查询公司中入职超过10000天的员工姓名、入职时间
SELECT
  last_name,
  hire_date
FROM
  employees
WHERE
  DATEDIFF(NOW(),hire_date) > 10000


# 8.做一个查询，产生下面的结果
-- -- <last_name> earns `<salary>` monthly but wants <salary*3>
-- -- Dream Salary
-- -- King earns 24000 monthly but wants 72000
SELECT
  CONCAT(
    last_name,
    ' earns ',
    TRUNCATE ( salary, 0 ),
    ' monthly but he wants ',
  TRUNCATE ( salary * 3, 0 )) 'Dream Salary'
FROM
  employees
  
  
-- 9.使用CASE-WHEN，按照下面的条件：
-- -- job       grade
-- -- AD_PRES   A
-- -- ST_MAN    B
-- -- IT_PROG   C
-- -- SA_REP    D
-- -- ST_CLERK  E

-- -- 产生下面的结果
-- -- Last_name   Job_id    Grade
-- -- king        AD_PRES   A

SELECT
  last_name Last_name,
  job_id Job_id,
CASE
    job_id 
    WHEN 'AD_PRES' THEN
    'A' 
    WHEN 'ST_MAN' THEN
    'B' 
    WHEN 'IT_PROG' THEN
    'C' 
    WHEN 'SA_REP' THEN
    'D' 
    WHEN 'ST_CLERK' THEN
    'E' ELSE 'F' 
  END Grade 
FROM
  employees

聚合函数

聚合函数作用于一组数据，并对一组数据返回一个值。

聚合函数类型

• AVG()
• SUM()
• MAX()
• MIN()
• COUNT()

COUNT函数

作用

• 计算指定字段在查询结构中出现的个数

• COUNT(数字)这种情况是属于用数据库中不存在的字段表示，每一条记录它都会当成这个数字来计算个数。用其他数字也是一样

• 如果计算表中有多少条记录，如何实现？

• COUNT（*）
• COUNT（1）
• COUNT（具体字段）：不一定对，如果该字段包含NULL值，那么NULL值是不会被包含进记录计算的

能不能使用count(列名)替换count(*)?

不要使用 count(列名)来替代 count() ， count() 是 SQL92 定义的标准统计行数的语法，跟数 据库无关，跟 NULL 和非 NULL 无关。

说明：count(*)会统计值为 NULL 的行，而 count(列名)不会统计此列为 NULL 值的行。

count(列名)和count(*)效率

如果使用的是MyISAM 存储引擎，则三者效率相同，都是o(1)

如果使用的是InnoDB存储引擎，则三者效率:cOUNT(*) =COUNT(1)>COUNT(字段)

聚合函数示例

-- 1、可以对数值型数据使用AVG 和 SUM 函数。
SELECT 
  AVG(salary),
  MAX(salary),
  MIN(salary),
  SUM(salary)
FROM
  employees
  
6461.682243 24000.00  2100.00 691400.00 

-- 2、COUNT(*)返回表中记录总数，适用于任意数据类型。  
SELECT
  COUNT(*)
FROM
  employees;
107

GROUP BY的使用

很形象的图，下次忘了GROUP BY怎么用的话就想想这个图！！！

按照部门分组：

单个部门进行分组：
SELECT
  department_id,
  AVG(salary),
  SUM(salary)
FROM
  employees
  
多个部门进行分组：
SELECT
  department_id,
  job_id,
  AVG( salary ) 
FROM
  employees 
GROUP BY
  department_id,
  job_id

GROUP BY注意事项

1、SELECT中出现的非组函数的字段必须声明在GPOUP BY中。

以下是错误的示范：

last_name属于普通的字段，所以它必须声明在GROUP BY中

SELECT
  department_id,
  job_id,
  last_name,
  AVG( salary ) 
FROM
  employees 
GROUP BY
  department_id,
  job_id

反之，GROUP BY中声明的字段可以不出现在SELECT中。

SELECT
  department_id,
  AVG( salary ) 
FROM
  employees 
GROUP BY
  department_id,
  job_id

2、GROUP BY 声明在FROM后面、WHERE后面，ORDER BY前面、LIMIT前

3、使用 WITH ROLLUP 关键字之后，在所有查询出的分组记录之后增加一条记录，该记录计算查询出的所 有记录的总和，即统计记录数量。

SELECT
  department_id,
  AVG( salary ) 
FROM
  employees 
GROUP BY
  department_id,
  job_id WITH ROLLUP

当使用ROLLUP时，不能同时使用ORDER BY子句进行结果排序，即ROLLUP和ORDER BY是互相排斥 的。

HAVING的使用

HAVING的作用就是过滤分组

但使用有以下条件：

• 行已经被分组
• 使用了聚合函数
• 满足HAVING 子句中条件的分组将被显示
• HAVING 不能单独使用，必须要跟 GROUP BY 一起使用

SELECT
  department_id,
  AVG( salary ) 
FROM
  employees 
GROUP BY
  department_id,
  job_id
HAVING
  MAX(salary) >10000

如果过滤条件中使用了聚合函数，则必须使用HAVING来替换WHERE。否则，报错。

HAVING 必须声明在GROUP BY 的后面。

WHERE和HAVING的对比

• 区别1：

• WHERE 后面跟筛选条件的时候不能跟聚合函数
• HAVING和GROUP BY配合使用，可以用聚合函数当筛选条件
• 这决定了，在需要对数据进行分组统计的时候，HAVING 可以完成 WHERE 不能完成的任务。这是因为， 在查询语法结构中，WHERE 在 GROUP BY 之前，所以无法对分组结果进行筛选。HAVING 在 GROUP BY 之 后，可以使用分组字段和分组中的计算函数，对分组的结果集进行筛选，这个功能是 WHERE 无法完成 的。另外，WHERE排除的记录不再包括在分组中。

• 区别2：

• 如果需要通过连接从关联表中获取需要的数据，WHERE 是先筛选后连接，而 HAVING 是先连接 后筛选。
• 这一点，就决定了在关联查询中，WHERE 比 HAVING 更高效。因为 WHERE 可以先筛选，用一 个筛选后的较小数据集和关联表进行连接，这样占用的资源比较少，执行效率也比较高。HAVING 则需要 先把结果集准备好，也就是用未被筛选的数据集进行关联，然后对这个大的数据集进行筛选，这样占用 的资源就比较多，执行效率也较低。

开发中的选择：

WHERE和HAVING并不冲突，我们可以同时使用它们，更好的发挥它们的优点

包含分组统计函数条件用HAVING，普通条件用WHERE。

当数据量特别大的时候，运行效率会有很 大的差别。

SELECT的执行原理

SELECT ...,....,...
FROM ... JOIN ...
ON 多表的连接条件
JOIN ...
ON ...
WHERE 不包含组函数的过滤条件
AND/OR 不包含组函数的过滤条件
GROUP BY ...,...
HAVING 包含组函数的过滤条件
ORDER BY ... ASC/DESC
LIMIT ...,...
#其中：
#（1）from：从哪些表中筛选
#（2）on：关联多表查询时，去除笛卡尔积
#（3）where：从表中筛选的条件
#（4）group by：分组依据
#（5）having：在统计结果中再次筛选
#（6）order by：排序
#（7）limit：分页

SELECT 查询时的两个顺序：

• 关键字的顺序是不能颠倒的

• SELECT … FROM … WHERE … GROUP BY … HAVING … ORDER BY … LIMIT…

• SELECT 语句的执行顺序（在 MySQL 和 Oracle 中，SELECT 执行顺序基本相同）

• FROM -> WHERE -> GROUP BY -> HAVING -> SELECT 的字段 -> DISTINCT -> ORDER BY -> LIMIT

练习

#1.where子句可否使用组函数进行过滤?
不可以，where后面一般跟非组函数条件进行过滤
HAVING后面一般跟组函数进行过滤


#2.查询公司员工工资的最大值，最小值，平均值，总和
SELECT
  MAX(salary),
  MIN(salary),
  AVG(salary),
  SUM(salary)
FROM
  employees


#3.查询各job_id的员工工资的最大值，最小值，平均值，总和
SELECT
  job_id,
  MAX(salary),
  MIN(salary),
  AVG(salary),
  SUM(salary)
FROM
  employees
GROUP BY
  job_id


#4.选择具有各个job_id的员工人数
-- COUNT()这里的参数可填*,1,employee_id
-- 但是要注意如果填字段名，count不计算空值
SELECT
  job_id,
  COUNT(*)
FROM
  employees
GROUP BY
  job_id


# 5.查询员工最高工资和最低工资的差距（DIFFERENCE）
SELECT
  MAX( salary ) - MIN( salary ) 'DIFFERENCE' 
FROM
  employees



# 6.查询各个管理者手下员工的最低工资，其中最低工资不能低于6000，没有管理者的员工不计算在内
SELECT
  manager_id,
  MIN( salary ) 
FROM
  employees 
WHERE
--  salary > 6000 题中说到最低工资，所以应该用聚合函数MIN来求得最低工资然后进行判断
  manager_id IS NOT NULL 
GROUP BY
  manager_id
HAVING
  MIN(salary) >= 6000


# 7.查询所有部门的名字，location_id，员工数量和平均工资，并按平均工资降序
SELECT
  d.department_name,
  d.location_id,
  COUNT(employee_id),
  AVG( salary ) '平均工资'
FROM
  departments d
  LEFT JOIN employees e ON d.department_id = e.department_id 
GROUP BY
  department_name,location_id 
ORDER BY
  '平均工资' DESC

# 8.查询每个工种、每个部门的部门名、工种名和最低工资
-- 这里的每个其实也可看成所有！
-- 最后结果出现很多空值是因为部门有很多是没有人的，那么就会出现job_id和salary为空的情况
SELECT
  d.department_name,
  e.job_id,
  MIN( salary ) 
FROM
  departments d
  LEFT JOIN employees e ON d.department_id = e.department_id 
GROUP BY
  department_name,
  job_id

易错点

查询所有部门的名字，location_id，员工数量和平均工资，并按平均工资降序

这里部门表左连接员工表，而部门表有很多空部门，这样员工表和部门表连接时就会出现很多空值。如果我们在用COUNT（*）计算员工数量时，那么它会把NULL的也给算进去，所以这里呢COUNT应该填字段名，让它进行一个排NULL操作

所以

SELECT
  d.department_name,
  d.location_id,
  COUNT(employee_id),
  AVG( salary ) '平均工资'
FROM
  departments d
  LEFT JOIN employees e ON d.department_id = e.department_id 
GROUP BY
  department_name,location_id 
ORDER BY
  '平均工资' DESC

查询每个工种、每个部门的部门名、工种名和最低工资

这里的每个其实也可看成所有！
最后结果出现很多空值是因为部门有很多是没有人的，那么就会出现job_id和salary为空的情况

查询各个管理者手下员工的最低工资，其中最低工资不能低于6000，没有管理者的员工不计算在内

这里不能在WHERE后加salary > 6000

题中说到最低工资，所以应该用聚合函数MIN来求得最低工资然后进行判断