1、子查询与表连接（重要）

1、子查询(嵌套sql)

SELECT语句是SQL的查询。迄今为止我们所看到的所有SELECT语句都是简单查询，即从单个数据库表中检索数据的单条语句。
SQL还允许创建子查询（subquery），即嵌套在其他查询中的查询。
也就是说，select语句中嵌套select。

2、关系表

SQL最强大的功能之一就是能在数据检索查询的执行中联结（join）表。
在能够有效地使用联结前，必须了解关系表以及关系数据库设计的⼀些基础知识。
相同数据出现多次决不是一件好事，此因素是关系数据库设计的基础。
关系表的设计就是要保证把信息分解成多个表，一类数据一个表。
各表通过某些常用的值（即关系设计中的关系（relational））互相关联。

也就是说，为了降低某些数据的重复性以及这些大量重复的数据变动时引起的麻烦，我们可以把这些东西分离出去，然后用一条线把这些有关系的表联系起来，这些线就是一些标识的值。

3、表联结

inner join on
如果数据存储在多个表中，怎样用单条SELECT语句句检索出数据？
答案是使用联结。简单地说，联结是一种机制，用来在一条SELECT语句中关联表，因此称之为联结。
使用特殊的语法，可以联结多个表返回一组输出，联结在运行时关联表中正确的行。

在联结两个表时，你实际上做的是将第一个表中的每一行与第二个表中的每一行配对。
WHERE子句作为过滤条件，它只包含那些匹配给定条件（这里是联结条件）的行。

如果没有where条件，第⼀个表中的每个行将与第二个表中的每个行配对，而不管它们逻辑上是否可以配在一起。
由没有联结条件的表关系返回的结果为笛卡儿积。检索出的行的数目将是第一个表中的行数乘以第二个表中的行数。

不要忘了WHERE子句
应该保证所有联结都有WHERE子句，否则MySQL将返回比想要的数据多得多的数据。
同理，应该保证WHERE子句的正确性。不正确的过滤条件将导致MySQL返回不正确的数据

其实，对于这种联结可以使用稍微不同的语法来明确指定联结的类型。
两个表之间的关系是FROM子句的组成部分，以INNER JOIN指定。
在使用这种语法时，联结条件用特定的ON子句而不是WHERE子句给出。
传递给ON的实际条件与传递给WHERE的相同。
SQL规范首选INNER JOIN语法。

联结多个表**
SQL对一条SELECT语句中可以联结的表的数目没有限制。
创建联结的基本规则也相同。首先列出所有表，然后定义表之间的关系。

MySQL在运行时关联指定的每个表以处理联结。 这种处理可能是⾮常耗费资源的，因此应该仔细，不要联结不必要的表。联结的表越多，性能下降越厉害。

使用表别名 AS
别名除了用于列名和计算字段外，SQL还允许给表名起别名。
这样做有两个主要理由：

• 缩短SQL语句；
• 允许在单条SELECT语句中多次使用相同的表

应该注意，表别名只在查询执行中使用。与列别名不一样，表别名不返回到客户机

4、自联结

自联结就是自己联结自己，用来查询一些需要从本身获取的数据，但是过滤条件也是从本身而来。
用自联结而不用子查询，自联结通常作为外部语句用来替代从相同表中检索数据时使用的子查询语句。
虽然最终的结果是相同的，但有时候处理联结远比处理子查询快得多。

5、外部链接

许多联结将⼀个表中的行与另一个表中的行相关联。但有时候会需要包含没有关联行的那些行。
这时可以使用外部链接，即left join 和 right join

保证使用正确的联结条件，否则将返回不正确的数据。
应该总是提供联结条件，否则会得出笛卡儿积。
在一个联结中可以包含多个表，甚至对于每个联结可以采用不同的联结类型。虽然这样做是合法的，一般也很有用，但应该在一起测试它们前，分别测试每个联结。这将使故障排除更为简单。

6、组合查询 UNION

MySQL也允许执行多个查询（多条SELECT语句），并将结果作为单个查询结果集返回。
这些组合查询通常称为并（union）或复合查询（compound query）。
select xxx
union
select xxx

UNION规则
UNION必须由两条或两条以上的SELECT语句组成，语句之间用关键字UNION分隔（因此，如果组合4条SELECT语句句，将要使用3个UNION关键字）。
UNION中的每个查询必须包含相同的列、表达式或聚集函数（不过各个列不需要以相同的次序列出）
列数据类型必须兼容：类型不必完全相同，但必须是DBMS可以隐含地转换的类型（例如，不同的数值类型或不同的日期类型）。
UNION从查询结果集中自动去除了重复的行（换句话说，它的行为与单条SELECT语句中使用多个WHERE子句条件一样）。
这是UNION的默认行为，但是如果需要，可以改变它。如果想返回所有匹配行，可使用UNIONALL而不是UNION

SELECT语句的输出用ORDER BY子句句排序。在用UNION组合查询时，只能使用⼀条ORDERBY子句，它必须出现在最后一条SELECT语句之后。
对于结果集，不存在用一种方式排序一部分，而又用另一种方式排序另一部分的情况，因此不允许使用多条ORDER BY子句。

7、case when

CASE WHEN 条件 THEN 条件满足时的动作 ELSE 条件不满足时的动作 END

见50道练习题第 17 题。

8、if

IF 条件1 THEN 满足条件1动作 [ELSEIF 条件2 THEN 满足条件2动作] … [ELSE 不满足条件动作]END IF

9、@变量 := 值

变量赋值操作
例如 ： @i := 0

10、concat

concat(a,b) = ab

11、行转列

用case when

2、关于sql_mode

sql_mode是MySQL数据库中的一个环境变量 定义了mysql应该支持的sql语法，数据校验等
可以通过 select @@sql_mode; 查看当前数据库使用的sql_mode
查看当前数据库的sql_mode
select @@sql_mode;
修改sql_mode
1. 在当前数据库中进行修改(服务器重启后失败)
set @@sql_mode= ‘xxx’
2. 修改配置文件 my.cnf
sql_mode = ‘xxxx’
修改完成后要重启mysql服务
brew services stop mysql@5.7

sql_mode值的含义

关于ONLY_FULL_GROUP_BY是否开启的建议:
1. 建议开启,符合SQL标准
2. 在mysql中有any_value(field)函数,允许返回非分组字段.(和关闭only_full_group_by模式相同)

3、MySQL 事务（重要）

事务（Transaction）是由一系列对系统中数据进行访问与更新的操作所组成的一个程序执行逻辑单元。

• 事务的语法
• 事务的特性
• 事务并发问题
• 事务隔离级别
• 不同隔离级别的锁的情况(了解)
• 隐式提交(了解)

1、事务的语法

1. start transaction;/ begin;
   2. commit; 使得当前的修改确认
   3. rollback; 使得当前的修改被放弃

   2、事务的ACID特性

   1. 原子性（Atomicity）
   事务的原子性是指事务必须是一个原子的操作序列单元。事务中包含的各项操作在一次执行过程中，只允许出现两种状态之一。

• 全部执行成功
• 全部执行失败

事务开始后所有操作，要么全部做完，要么全部不做，不可能停滞在中间环节。事务执行过程中出错，会回滚到事务开始前的状态，所有的操作就像没有发生一样。也就是说事务是一个不可分割的整体，就像化学中学过的原子，是物质构成的基本单位。
2. ⼀致性（Consistency）
事务的⼀致性是指事务的执行不能破坏数据库数据的完整性和一致性，⼀个事务在执行之前和执行之后，数据库都必须处以一致性状态。
比如：如果从A账户转账到B账户，不可能因为A账户扣了钱，而B账户没有加钱。
3. 隔离性（Isolation）
事务的隔离性是指在并发环境中，并发的事务是互相隔离的。也就是说，不同的事务并发操作相同的数据时，每个事务都有各自完整的数据空间。
一个事务内部的操作及使用的数据对其它并发事务是隔离的，并发执行的各个事务是不能互相干扰的。
隔离性分4个级别，下面会介绍。
4. 持久性（Duration）
事务的持久性是指事务一旦提交后，数据库中的数据必须被永久的保存下来。即使服务器系统崩溃或服务器宕机等故障。只要数据库重新启动，那么一定能够将其恢复到事务成功结束后的状态。

3、事务的并发问题

• 脏读：读取到了没有提交的数据, 事务A读取了事务B更新的数据，然后B回滚操作，那么A读取到的数据是脏数据。
• 不可重复读：同一条命令返回不同的结果集（更新）。事务 A 多次读取同一数据，事务 B 在事务A多次读取的过程中，对数据作了更新并提交，导致事务A多次读取同一数据时，结果不一致。
• 幻读：重复查询的过程中，数据就发生了量的变化（insert， delete）。

  4、事务隔离级别

  

  4种事务隔离级别从上往下，级别越高，并发性越差，安全性就越来越高。一般数据默认级别是读已提交或可重复读。

查看当前会话中事务的隔离级别

mysql> select @@tx_isolation;
+-----------------+
| @@tx_isolation |
+-----------------+
| REPEATABLE-READ |
+-----------------+
1 row in set, 1 warning (0.93 sec)

设置当前会话中的事务隔离级别
mysql> set session transaction isolation level read uncommitted;
Query OK, 0 rows affected (0.00sec)

1. 读未提交（READ_UNCOMMITTED）

读未提交，该隔离级别允许脏读取，其隔离级别是最低的。换句话说，如果一个事务正在处理某一数据，并对其进行了更新，但同时尚未完成事务，因此还没有提交事务；而以此同时，允许另一个事务也能够访问该数据。
脏读示例：
在事务A和事务B同时执行时可能会出现如下场景：
余额应该为1500元才对。请看T5时间点，事务A此时查询的余额为0，这个数据就是脏数据，他是事务B造成的，很明显是事务没有进行隔离造成的。

2. 读已提交（READ_COMMITTED）

读已提交是不同的事务执行的时候只能获取到已经提交的数据。 这样就不会出现上面的脏读的情况了。
但是在同一个事务中执行同⼀个读取,结果不一致.
不可重复读示例
解决了脏读问题，但是还是解决不了不可重复读问题。
事务A其实除了查询两次以外，其它什么事情都没做，结果钱就从1000变成0了，这就是不可重复读的问题。

3. 可重复读（REPEATABLE_READ）

可重复读就是保证在事务处理过程中，多次读取同一个数据时，该数据的值和事务开始时刻是一致的。
因此该事务级别限制了不可重复读和脏读，但是有可能出现幻读的数据。
幻读
幻读就是指同样的事务操作，在前后两个时间段内执行对同一个数据项的读取，可能出现不一致的结果。
诡异的更新事件

4. 顺序读（SERIALIZABLE）

顺序读是最严格的事务隔离级别。它要求所有的事务排队顺序执行，即事务只能一个接一个地处理，不能并发。

5、不同的隔离级别的锁的情况(了解)

1. 读未提交（RU）: 有行级的锁，没有间隙锁。它与RC的区别是能够查询到未提交的数据。
   2. 读已提交（RC）：有行级的锁，没有间隙锁，读不到没有提交的数据。
   3. 可重复读（RR）：有行级的锁，也有间隙锁，每次读取的数据都是一样的，并且没有幻读的情况。
   4. 序列化（S）：有行级锁，也有间隙锁，读表的时候，就已经上锁了

   6、隐式提交(了解)

   DQL:查询语句
   DML:写操作(添加,删除,修改)
   DDL:定义语句(建库,建表,修改表,索引操作,存储过程,视图)
   DCL:控制语言(给用户授权,或删除授权)
   DDL（Data Define Language）：都是隐式提交。
   隐式提交：执行这种语句相当于执行commit; DDL
   https://dev.mysql.com/doc/refman/5.7/en/implicit-commit.html

   4、MySQL中的特性

   1、MySQL存储过程

   存储过程简单来说，就是为以后的使用而保存的一条或多条MySQL语句的集合。
   存储过程是一组为了完成特定功能的SQL语句集，经过编译之后存储在数据库中，在需要时直接调用。
   存储过程就像脚本语言中函数定义⼀样。
   具体的有需要再去了解。

   2、MySQL的触发器

   触发器是MySQL响应写操作(增、删、改)而自动执行的一条或一组定义在BEGIN和END之间的MySQL语句
   或可理解为：提前定义好一个或一组操作,在指定的SQL操作前或后来触发指定的SQL自动执行
   触发器就像是JavaScript中的事件一样

   3、MySQL中的视图

   什么是视图？
   视图是虚拟的表。与包含数据的表不一样，视图只包含使用时动态检索数据的查询。
   视图仅仅是用来查看存储在别处的数据的一种设施或方法。
   视图本身不包含数据，因此它们返回的数据是从其他表中检索出来的。
   在添加或更改这些表中的数据时，视图将返回改变过的数据。
   因为视图不包含数据，所以每次使用视图时，都必须处理查询执行时所需的任一个检索。
   如果你用多个联结和过滤创建了复杂的视图或者嵌套了视图，可能会发现性能下降得很厉害。

视图的作用
1. 重用SQL语句。
2. 简化复杂的SQL操作。在编写查询后，可以方便地重用它而不必知道它的基本查询细节。
3. 使用表的组成部分而不是整个表。
4. 保护数据。可以给用户授予表的特定部分的访问权限而不是整个表的访问权限。
5. 更改数据格式和表示。视图可返回与底层表的表示和格式不同的数据。
6. 注意:视图不能索引，也不能有关联的触发器或默认值。

5、索引和SQL优化

1、索引的概述和分类

1.1、什么是索引？

1.索引类似图书的目录，可以提高数据检索的效率
2.索引其实就是一种排好序的数据结构。

1.2、索引分类

• 主键索引
• 唯一索引
• 普通索引
• 全文索引
• 组合索引
  • 组合索引的“最左”原则：必须从左边的字段开始索引，否则无效

2、索引原理

哈希索引和B+Tree索引
只有memory（内存）存储引擎支持哈希索引，哈希索引用索引列的值计算该值的hashCode，然后hashCode相应的位置存执该值所在行数据的物理位置，因为使用散列算法，因此访问速度非常快，但是一个值只能对应一个hashCode，而且是散列的分布方式，因此哈希索引不支持范围查找和排序的功能
正常情况下，如果不指定索引的类型，那么一般是指B+Tree索引。
存储引擎以不同的方式使用B+Tree索引。性能也各有不同，但是InnoDB按照原数据格式进行存储。

数据结构：

• 链表
• 二叉树
• 平衡二叉树
• 红黑树
  • 特殊的平衡二叉树
• B树
  • 平衡多路查找树，多用于数据库和文件系统
  • 查找效率不均衡
  • 范围查找需要中序遍历
  • 每⼀个叶子结点上都带有数据
• B+树
  • 优化的B树
  • 只有叶子节点存储数据
  • 非叶子节点起索引作用
  • 所有叶子节点使用链表相连，防止范围查找的中序遍历
  • 磁盘读写代价更低
  • 随机IO的次数更少
  • 查询速度更稳定

聚簇索引：索引即数据，数据即索引
非聚簇索引：找到索引仅仅是找到当前索引值和key，如果需要索引外的内容，则需要回表。

MyISAM和InnoDB的区别
• 数据存储方式：
• InnoDB由两种文件组成，表结构，数据和索引
• MyISAM由三种文件组成，表结构、数据、索引
• 索引的方式：
• 索引的底层都是基于B+Tree的数据结构建立
• InnoDB中主键索引为聚簇索引，辅助索引是⾮非聚簇索引
• MyISAM中数据和索引存在不同的文件中，因此都是非聚簇索引
• 事务的⽀支持：
• InnoDB支持事务
• MyISAM不支持事务
总结

• 数据库的索引就是为了提高数据检索速度
• 而数据库的索引就是基于B+ Tree的数据结构实现的
• 在InnoDB中主键是聚簇索引而辅助索引是非聚簇索引
• 在MyISAM中主键索引和辅助索引都是非聚簇索引

  3、慢查询和SQL优化

  1、慢查询

  MySQL的慢查询，全名是慢查询日志
  是MySQL提供的一种日志记录，用来记录在MySQL中响应时间超过阀值的语句。
  默认情况下，MySQL数据库并不启动慢查询日志，需要手动来设置这个参数。
  如果不是调优需要的话，一般不建议启动该参数，开启慢查询日志会或多或少带来一定的性能影响。
  慢查询日志可用于查找需要很长时间才能执行的查询，因此是优化的候选者。

2、SQL优化

一条查询语句在经过MySQL查询优化器的各种基于成本和规则的优化会后生成一个所谓的执行计划
这个执行计划展示了接下来具体执行查询的方式，比如多表连接的顺序是什么，对于每个表采用什么访问方法来具体执行查询等等。
MySQL为我们提供了EXPLAIN语句来帮助我们查看某个语句的具体执行计划。
参数解释：

• id 在一个大的查询语句中每个SELECT关键字都对应一个唯一-的id
• select_ type SELECT关键字对应的那个查询的类型
• table表名
• partitions 匹配的分区信息
• type针对 单表的访问方法
• possible_ keys 可能用到的索引
• key 实际上使用的索引
• key_ len 实际使用到的索引长度
• ref 当使用索引列等值查询时，与索引列进行等值匹配的对象信息
• rows 预估的需要读取的记录条数
• filtered 某个表经过搜索条件过滤后剩余记录条数的百分比
• Extra 一些额外的信息

SQL语句的优化
1.避免嵌套语句(子查询)
2.避免多表查询(复杂查询简单化)

索引可以提高查询效率，但是会非常占用存储空间。

适当建立索引
1.创建并使用自增数字来建立主键索引
2.经常作为where条件的字段建立索引
3.添加索引的字段尽可能的保持唯一性
4.可考虑使用联合索引并进行索引覆盖

联合索引的索引覆盖(多个字段组合成了一个联合索引,在查询时,所要的字段和查询条件中的索引是一致)
注意索引绝不是加的越多越好(1.索引会占空间.2.索引会影响写入性能)

合理使用索引
MySQL索引通常是被用于提高WHERE条件的数据行匹配时的搜索速度,
在索引的使用过程中，存在一些使用细节和注意事项。

• 多个单列索引并不是最佳选择
• 复合索引的最左前缀原则
  • 查询条件中使用了复合索引的第一个字段，索引才会被使用。因此，在复合索引中索引列的顺序至关重要。如果不是按照索引的最左列开始查找，则无法使用索引。
• 尽可能达成索引覆盖
  • 如果一个索引包含所有需要的查询的字段的值，直接根据索引的查询结果返回数据，而无需读表，能够极大的提高性能。因此，可以定义一个让索引包含的额外的列，即使这个列对于索引而言是无用的。

因为不合理的使用可能会导致建立了索引之后,不一定就使用上了索引

当查询条件左右两侧类型不匹配的时候会发生隐式转换，隐式转换可能导致索引失效而进行全表扫描。

6、总结

联结查询和事务很重要，需要好好学习。
联结查询，思想不难，问题是有很多的细节需要注意，很多的查询要求要怎么实现？还有其他的实现方式吗？哪种方式更加好？平时在写SQL语句时，要多注意这些问题，可以多看看那50道经典的练习题，自己先想先实现，然后把其中的问题记录下来，对比别人写的之后，思考那种更优，为什么这样写？我为什么想不到？总之，一句话，就是多思考，然后把一些固定的套路整理整理。
牛刀小试：

-- 创建数据表:
-- 雇员表(employee):雇员编号(empid,主键)，姓名(name)，性别(sex)，职称(title)，出生日期(birthday)，所属部门(depid)
create table employee(
    empid varchar(10) not null primary key,
    name varchar(5) not null,
    sex enum("男", "女") not null default "男",
    title varchar(16) not null default "普通员工",
    birthday date,
    depid tinyint unsigned not null
)engine=innodb default charset=utf8;


-- 部门(department):部门编号(depid,主键)，部门名称(depname)
create table department(
    depid tinyint unsigned not null primary key auto_increment,
    depname varchar(10) not null
)engine=innodb default charset=utf8;


-- 工资表(salary):雇员编号(empid)，基本工资(basesalary)，职务工资(titlesalary)，扣除(deduction)
create table salary(
    empid varchar(10) not null primary key,
    basesalary int unsigned not null default 0,
    titlesalary int unsigned default 0,
    deduction int unsigned default 0
)engine=innodb default charset=utf8;


-- 插入数据
-- 雇员表
INSERT INTO `employee` VALUES("rs00000001", "阿凡达", "女","人事经理","1991-6-6",1);
INSERT INTO `employee` VALUES("rs00000002", "马拉车", "男","普通员工","1990-8-8",1);
INSERT INTO `employee` VALUES("gc00000001", "张三", "女","项目经理","1979-12-8",2);
INSERT INTO `employee` VALUES("gc00000002", "王五", "男","工程师","1994-2-12",2);
INSERT INTO `employee` VALUES("gc00000003", "李四", "男","普通员工","1992-8-12",2);
INSERT INTO `employee` VALUES("xs00000001", "张飞", "男","销售总监","1989-3-18",3);
INSERT INTO `employee` VALUES("xs00000002", "赵日天", "男","销售经理","1993-7-7",3);
INSERT INTO `employee` VALUES("xs00000003", "马冬梅", "女","普通员工","1996-4-8",3);
INSERT INTO `employee` VALUES("cw00000001", "马花藤", "男","财务总监","1975-6-6",4);
INSERT INTO `employee` VALUES("cw00000002", "张婷莎", "女","会计","1990-1-6",4);
INSERT INTO `employee` VALUES("cw00000003", "郭亭黑", "男","普通员工","1994-5-6",4);

-- 部门(department):部门编号(depid,主键)，部门名称(depname)
INSERT INTO `department` VALUES(1, "人事部");
INSERT INTO `department` VALUES(2, "工程部");
INSERT INTO `department` VALUES(3, "销售部");
INSERT INTO `department` VALUES(4, "财务部");

-- 工资表(salary):雇员编号(empid)，基本工资(basesalary)，职务工资(titlesalary)，扣除(deduction)
INSERT INTO `salary` VALUES("rs00000001", 3000,1000,300);
INSERT INTO `salary` VALUES("rs00000002", 1500,300,150);
INSERT INTO `salary` VALUES("gc00000001", 4000,1200,400);
INSERT INTO `salary` VALUES("gc00000002", 2000,700,200);
INSERT INTO `salary` VALUES("gc00000003", 1800,400,180);
INSERT INTO `salary` VALUES("xs00000001", 5000,1800,500);
INSERT INTO `salary` VALUES("xs00000002", 3000,1500,300);
INSERT INTO `salary` VALUES("xs00000003", 1800,500,180);
INSERT INTO `salary` VALUES("cw00000001", 6000,1500,600);
INSERT INTO `salary` VALUES("cw00000002", 2400,600,240);
INSERT INTO `salary` VALUES("cw00000003", 1500,200,150);


-- 练习:
-- 1. 修改表结构，在部门表中添加部门简介字段 "李四"
alter table department add info varchar(50);

-- 2. 将李四的职称改为“工程师”,并将她的基本工资改成 2000，职务工资为 700
update employee, salary
set employee.title = "工程师", salary.basesalary = 2000, salary.titlesalary = 700
where employee.name = "李四" and employee.empid = salary.empid;

select e.name,e.title,s.basesalary,s.titlesalary
from employee as e 
inner join salary as s 
on e.empid = s.empid
where e.name = "李四";

-- 这种是错误的，会报错ERROR 1288 (HY000): The target table new of the UPDATE is not updatable
-- update
-- (select e.name,e.title,s.basesalary,s.titlesalary
--     from employee as e 
--     inner join salary as s 
--     on e.empid = s.empid) as new 
-- set new.title = "工程师",new.basesalary = 2000,new.titlesalary = 700
-- where new.name = "李四";

-- 3. 删除人事部门的部门记录
-- 删除操作：delete 字段 from 表名 where 字段=某个值;
delete e.*, d.*, s.* from employee as e, department as d, salary as s where d.depname = "人事部" and e.depid = d.depid and s.empid = e.empid;
select e.*, d.depname,s.basesalary,s.titlesalary,s.deduction from employee as e, department as d, salary as s where d.depname = "人事部" and e.depid = d.depid and s.empid = e.empid;

-- 4. 查询出每个雇员的雇员编号，实发工资，应发工资
-- 应发工资=基本工资+奖金+津贴或者补贴+加班加点工资+特殊情况下支付的工资－劳动者因个人原因缺勤或旷工造成的工资或者奖金减少的部分。
-- 实发工资不等同于应发工资。实发工资=应发工资－五险一金个人缴纳部分－应缴个人所得税。
-- 在这里，我把扣除的当做实发工资和应发工资的差别值, 先把表中扣除金额全部修改为 0.1 * 基本工资
-- update salary as s set s.deduction = s.basesalary*0.1;

select e.name,e.empid as "雇员编号", 
sum(s.basesalary + s.titlesalary - s.deduction) as "实发工资", 
sum(s.basesalary + s.titlesalary) as "应发工资"
from employee as e inner join salary as s 
on e.empid = s.empid
group by e.name,e.empid
order by e.empid;


-- 5. 查询姓张且年龄小于 40 的员工记录
-- 查询年龄
select TIMESTAMPDIFF(YEAR, employee.birthday, CURDATE()) as age from employee;
-- 综合查询
select e.* from employee as e 
where e.name like "张%" and TIMESTAMPDIFF(YEAR, e.birthday, CURDATE()) < 40; 
-- 增加一个 age
select e.* , TIMESTAMPDIFF(YEAR, e.birthday, CURDATE()) as age from employee as e 
where e.name like "张%" and TIMESTAMPDIFF(YEAR, e.birthday, CURDATE()) < 40;

select e.*, Age.age from employee as e 
inner join (select employee.empid as empid,TIMESTAMPDIFF(YEAR, employee.birthday, CURDATE()) as age from employee) as Age
on e.empid = Age.empid
where e.name like "张%" and Age.age < 40; 

-- 这种的是不对的，显示ERROR 1054 (42S22): Unknown column 'age' in 'where clause'
-- select e.* , TIMESTAMPDIFF(YEAR, e.birthday, CURDATE()) as age from employee as e 
-- where e.name like "张%" and age < 40;

select e.* , TIMESTAMPDIFF(YEAR, e.birthday, CURDATE()) as age from employee as e 
where e.name like "张%" having age < 40;

select e.* , TIMESTAMPDIFF(YEAR, e.birthday, CURDATE()) as age 
from employee as e 
where e.name like "张%"
group by empid having age < 40;


-- 6. 查询雇员的雇员编号，姓名，职称，部门名称，实发工资
select e.empid as "雇员编号", e.name as "姓名", e.title as "职称", d.depname as "部门名称" , 
sum(s.basesalary + s.titlesalary - s.deduction) as "实发工资"
from employee as e
inner join department as d 
inner join salary as s
on e.depid = d.depid and e.empid = s.empid
group by e.empid;

-- 这个显示：ERROR 1140 (42000):
-- In aggregated query without GROUP BY, expression #1 of SELECT list contains nonaggregated column 'exercise2.e.empid'; this is incompatible with sql_mode=only_full_group_by
select e.empid as "雇员编号", e.name as "姓名", e.title as "职称", d.depname as "部门名称" , 
sum(s.basesalary + s.titlesalary - s.deduction) as "实发工资"
from employee as e
inner join department as d on e.depid = d.depid
inner join salary as s on e.empid = s.empid
;

-- 这个是正确的
select e.empid as "雇员编号", e.name as "姓名", e.title as "职称", d.depname as "部门名称",
from employee as e
inner join department as d on e.depid = d.depid;

-- 7. 查询销售部门的雇员姓名，工资
select e.name as "姓名", d.depname as "部门名称", 
s.basesalary as "工资"
from employee as e
inner join department as d on e.depid = d.depid
inner join salary as s on e.empid = s.empid
where d.depname = "销售部";

-- 8. 统计各职称的人数
select title as "职称", count(title) as "人数"
from employee group by title order by 人数 desc;

-- 9. 统计各部门的部门名称，实发工资总和，平均工资
select d.depname as "部门",
sum(s.basesalary + s.titlesalary - s.deduction) as "实发工资总和",
round(avg(s.basesalary + s.titlesalary - s.deduction),2) as "平均工资"
from employee as e
inner join department as d on e.depid = d.depid
inner join salary as s on e.empid = s.empid
group by 部门;

-- 10. 查询比销售部门所有员工基本工资都高的雇员姓名
select max(s.basesalary) as "最高工资"
from employee as e
inner join department as d on e.depid = d.depid
inner join salary as s on e.empid = s.empid
where d.depname = "销售部";

select e.name, s.basesalary
from employee as e
inner join department as d on e.depid = d.depid
inner join salary as s on e.empid = s.empid
where s.basesalary > (select max(s.basesalary)
                        from employee as e
                        inner join department as d on e.depid = d.depid
                        inner join salary as s on e.empid = s.empid
                        where d.depname = "销售部"
                    );
-- 怎样优化上面的语句呢？我还没想到，尝试了挺多，都失败了。肯定有更优的写法，毕竟我这里的子查询是在是丑陋
-- select e.name, s.basesalary
-- from employee as e
-- inner join department as d on e.depid = d.depid
-- inner join salary as s on e.empid = s.empid
-- ;