SQL基础复习_范围运算符______……______和______用来限制查询数据的范围。(小写)

SQL 语法要点

• SQL 语句不区分大小写，但是数据库表名、列名和值是否区分，依赖于具体的 DBMS 以及配置。例如：SELECT 与 select、Select 是相同的。
• 多条 SQL 语句必须以分号（;）分隔。
• 处理 SQL 语句时，所有空格都被忽略。

DDL

DDL 的主要功能是定义数据库对象（如：数据库、数据表、视图、索引等）

数据定义

 数据库（DATABASE）

查询数据库

• 查询所有数据库

show databases;

 

• 查询当前数据库

select database();

 

 创建数据库

create database test;
create database if not exists test;	#如果test数据库不存在，则创建test数据；即使存在也不会报错

删除数据库

drop database test;
drop database if exists test;	#如果test数据库存在则删除，不存在系统也不会报错

选择数据库

use test;

 数据表（TABLE）

查询数据表

查询当前数据库所有表，前提是进入相关数据库

show tables;

查询表结构（查看当前这张表里面有哪些字段）

desc tb_user;	#desc 表名;

查询指定表的建表语句

show create table tb_user;	#show create table 表名;

创建数据表

普通创建

CREATE TABLE 表名(
 
​ 字段1 字段1类型 COMMENT '字段1注释' ,
 
​ 字段2 字段2类型 COMMENT '字段2注释' ,
 
​ 字段3 字段3类型 COMMENT '字段3注释' ,
 
​ ……
 
​ 字段n 字段n类型 COMMENT '字段n注释'
 
)COMMENT '表注释';
 
注意：最后一个字段后面没有逗号,表注释后有分号

create table user (
  id       int(10)     unsigned not NULL          comment 'Id',
  username varchar(64) not NULL default 'default' comment '用户名',
  password varchar(64) not NULL default 'default' comment '密码',
  email    varchar(64) not NULL default 'default' comment '邮箱'
) comment '用户表';

根据已有的表创建新表

• 两个表更改数据都不会影响另外一个表

CREATE TABLE vip_user AS
SELECT * FROM user;
#根据user的字段和数据创建一个一样的表，只是表名是vip_user，更改vip_user数据不会影响user，反之亦然。

数据类型

主要分为三类：数值类型、字符串类型、日期时间类型。

数值类型(无符号加上unsigned)

eg：age tinyint ，score double(4,1)小数一共4位，保留一位小数；

字符串类型

eg：username varchar(20)   ， gender char(1)

注意： char(10)不论输入多少一定占用10个空间，varchar(10)占用空间和输入相关。

日期时间类型

eg:`birthday date

修改数据

• 添加列字段

alter table 表名 add 字段名 类型（长度）[comment 注释] [约束];
alter table emp add nickname varchar(20) comment '昵称';

• 修改列数据类型

alter table 表名 modify 字段名 新数据类型（长度）;

alter table emp modify nickname varchar(30);

• 修改列字段名和字段类型

alter table 表名 change 旧字段名 新字段名 类型（长度） [comment 注释] [约束];

eg:将emp表中nickname字段修改为eusername，类型为varchar(30)

alter table emp change nickname username varchar(30) comment '昵称';

• 删除字段

alter table 表名 drop 字段名;

eg:将emp表的字段username删除

alter table emp drop username;

• 添加主键

alter table user
add primary key (id);

• 删除主键

alter table user
drop primary key;

• 修改表名

alter table 表名 rename to 新表名;

eg: 将emp表的表名修改为employee

alter table emp rename to employee;

 删除数据表

drop table [if exist] 表名;	 #指定表的表名和数据全部删除
truncate table 表名;			#删除指定表，并重新创建该表

注意：在删除表时，表中的全部数据也会被删除。

视图（VIEW）（待补充）

定义：

• 视图是基于 SQL 语句的结果集的可视化的表。
• 视图是虚拟的表，本身不包含数据，也就不能对其进行索引操作。对视图的操作和对普通表的操作一样。

作用：

• 简化复杂的 SQL 操作，比如复杂的联结；
• 只使用实际表的一部分数据；
• 通过只给用户访问视图的权限，保证数据的安全性；
• 更改数据格式和表示。

mysql视图

 创建视图

create view top_10_user_view as
select id, username
from user
where id < 10;

 删除视图

drop view top_10_user_view;

 索引（INDEX）(待补充)

索引是一种用于快速查询和检索数据的数据结构，其本质可以看成是一种排序好的数据结构。

索引的作用就相当于书的目录。打个比方: 我们在查字典的时候，如果没有目录，那我们就只能一页一页的去找我们需要查的那个字，速度很慢。如果有目录了，我们只需要先去目录里查找字的位置，然后直接翻到那一页就行了。

优点：

• 使用索引可以大大加快 数据的检索速度（大大减少检索的数据量）, 这也是创建索引的最主要的原因。
• 通过创建唯一性索引，可以保证数据库表中每一行数据的唯一性。

缺点：

• 创建索引和维护索引需要耗费许多时间。当对表中的数据进行增删改的时候，如果数据有索引，那么索引也需要动态的修改，会降低 SQL 执行效率。
• 索引需要使用物理文件存储，也会耗费一定空间。

但是，使用索引一定能提高查询性能吗?

大多数情况下，索引查询都是比全表扫描要快的。但是如果数据库的数据量不大，那么使用索引也不一定能够带来很大提升。

 创建索引

CREATE INDEX user_index
ON user (id);

 添加索引

ALTER table user ADD INDEX user_index(id)

 创建唯一索引

CREATE UNIQUE INDEX user_index
ON user (id);

 删除索引

ALTER TABLE user
DROP INDEX user_index;

 约束

约束

1. 概念：约束是作用于表字段上的规则，用于限制存储在表中的数据。

2. 目的：保证数据库中数据的正确、有效性和完整性。

如果存在违反约束的数据行为，行为会被约束终止。

约束可以在创建表时规定（通过 CREATE TABLE 语句），或者在表创建之后规定（通过 ALTER TABLE 语句）。

约束类型：

• NOT NULL - 指示某列不能存储 NULL 值。
• UNIQUE - 保证某列的每行必须有唯一的值。
• PRIMARY KEY - NOT NULL 和 UNIQUE 的结合。确保某列（或两个列多个列的结合）有唯一标识，有助于更容易更快速地找到表中的一个特定的记录。
• FOREIGN KEY - 保证一个表中的数据匹配另一个表中的值的参照完整性。
• CHECK - 保证列中的值符合指定的条件。
• DEFAULT - 规定没有给列赋值时的默认值。

创建表时使用约束条件：

id是需要非空且唯一，是主键；name需要非空；age需要检测是否大于0，小于等于120；状态为赋值就默认为1；性别检查为男或者女。

create table user(
    id int primary key auto_increment comment '主键',
    name varchar(10) not null comment '姓名',
    age int check ( age > 0 and age <= 120 ) comment '年龄',
    status char(1) default '1' comment '状态',
    gender char(1) check ( gender = '男' or gender = '女' ) comment '性别'
)comment '用户表';

外键约束

外键用来让两张表的数据之间建立连接，从而保证数据的一致性和完整性。

添加外键

alter table 表名 add constraint 外键名称 foreign key(外键字段名) references 主表（主表列名）;

eg:添加外键

alter table emp add constraint fk_emp_dept_id foreign key (dept_id) references dept(id);

删除外键

alter table 表名 drop foreign key 外键名称;

eg：删除外键

alter table emp drop foreign key fk_emp_dept_id;

在创建外键的时候，可以设置关键字指定在对外键删除和更新的时候进行相对应的操作。

 alter table 表名 add constraint 外键名称 foreign key (外键字段) references 主表名(主表字段名) on update 操作名称 on delete 操作名称;

DML和DQL：

增删改查，又称为 CRUD，数据库基本操作中的基本操作。

DML-添加数据INSERT

给全部字段添加数据

insert into 表名 values (值1,值2,……);

# 插入一行
insert into user
values (10, 'root', 'root', 'xxxx@163.com');
# 插入多行
insert into user
values (10, 'root', 'root', 'xxxx@163.com'), (12, 'user1', 'user1', 'xxxx@163.com'), (18, 'user2', 'user2', 'xxxx@163.com');

给指定字段添加数据

insert into 表名(字段名1,字段名2,……) values (值1,值2,……);

insert into user (username, password, email)
values ('admin', 'admin', 'xxxx@163.com');

插入查询出来的数据

insert into user (username)
select name from account;

DML-修改数据UPDATE

UPDATE 语句用于更新表中的记录。

update 表名 set 字段名1=值1,字段名2=值2,……[where 条件];

注意：修改语句的条件可以有，也可以没有有；如果没有条件，则会修改整张表的数据。 

eg:修改id为1的数据，将name修改为itheima

update employee set name = 'itheima' where id = 1;

eg:将所有员工入职日期修改为2023-08-14

update employee set entrydate = '2023-08-14';

DML-删除数据DELETE

delete from 表名 [where 条件];

• DELETE 语句用于删除表中的记录。
• truncate table 可以清空表，也就是删除所有行。

注意：

delete语句的条件可以有，也可以没有；如果没有条件，则会删除整张表的所有数据；

delete语句不能删除某一个字段的值（可以使用update）。

删除表中的指定数据

delete from user
where username = 'robot';

清空表中的数据

truncate table user;

DQL-查询数据SELECT

SELECT 语句用于从数据库中查询数据

基本查询

1.查询多个字段

select 字段1,字段2,字段3…… from 表名;
select * from 表名;

eg:查询指定字段name，workno，age返回

在返回的显示中只有name，workno，age三列

select name,workno,age from emp;

eg2:查询所有字段返回

select * from emp; 			#*可以表示所有的字段

2.设置别名

select 字段1 [as 别名1],字段2 [as 别名2]…… from 表名;

eg:查询所有员工的工作地址，，给已知的列起别名

select workadderss as '工作地址' from emp;

3.去除重复记录

select distinct 字段列表 from 表名;

• DISTINCT 用于返回不同的值。它作用于所有列。也就是说如果是查询两个列，需要两个列的数据的值都相同才算一个不同的数据，才会返回一个。

eg:查询公司员工的上班地址（不重复）

select distinct workadderss '工作地址' from emp;

排序查询:order by

select 字段列表 from 表名 order by 字段1 排序方式,字段2 排序方式2;

排序方式

• asc：升序（默认值）
• desc：降序
• order by 对多列排序的时候，先排序的列放前面，后排序的列放后面。并且，不同的列可以有不同的排序规则。

 注意：如果是多字段排序，当第一个字段相同时，才会根据第二个字段进行排序。如下图所示：

显示员工信息，按照department_id的降序排列，salary的升序排列。

select employee_id,salary,department_id
from employees
order by department_id desc,salary asc;

首先对department_id进行降序排列，在department_id数值相同的时候，在对salary进行升序排列。 

注意：order by应在where后面使用

聚合函数

将一列数据作为一个整体，进行纵向计算。

select 聚合函数(字表列段) from 表名;

常见的聚合函数：

分组查询:group by

分组查询

select 字段列表 from 表名 [where 条件] group by 分组字段名 [having 分组后过来条件];

group by 通常还涉及聚合count，max，sum，avg 等。

group by 可以按一列或多列进行分组。

group by 按分组字段进行排序后，order by 可以以汇总字段来进行排序。

where和having的区别

• 执行时机不同：where是分组之前进行过滤指定的行，不满足where条件不参与分组，where 在group by 前；而having是分组之后对结果进行过滤，一般都是和 group by 连用，having 在 group by 之后。
• 判断条件不同：where不能对聚合函数进行判断，后面不能加聚合函数；而having可以。

# 分组查询
# 根据性别分组，统计男性员工和女性员工的数量
select gender,count(*) from emp group by gender;
 
# 根据性别分组，统计男性员工和女性员工的平均年龄
select gender,avg(age) from emp group by gender;
 
# 根据workaddr工作地址进行分组，限制age年纪小于45(where)，然后进行计数(聚合函数)，然后进行判断计数大于3进行输出(having)
select workadderss ,count(*) from emp where age < 45 group by
workadderss having count(*) > 3;

注意：

• 执行顺序：where > 聚合函数 > having。(上述第三个例子，先进行where，在计数，最后having)
• 分组之后，查询的字段一般为聚合函数和分组字段，查询其他字段无任何意义。(select后的字段一般是group by的分组字段或者进行聚合函数，其他字段无意义)

条件查询:where

• WHERE 子句用于过滤记录，即缩小访问数据的范围。
• WHERE 后跟一个返回 true 或 false 的条件。
• WHERE 可以与 SELECT，UPDATE 和 DELETE 一起使用。
• 可以在 WHERE 子句中使用的操作符。

SELECT 语句中的 WHERE 子句

select * from Customers
where cust_name = 'Kids Place';

UPDATE 语句中的 WHERE 子句

update Customers
set cust_name = 'Jack Jones'
where cust_name = 'Kids Place';

DELETE 语句中的 WHERE 子句

delete from Customers
where cust_name = 'Kids Place';

运算符

in 和 between

IN 示例

• IN 操作符在 WHERE 子句中使用，作用是在指定的几个特定值中任选一个值。

# 查询年龄等于 18 或 20 或 40 的员工信息
select * from emp where age in (18,20,40);
 

BETWEEN 示例

• BETWEEN 操作符在 WHERE 子句中使用，作用是选取介于某个范围内的值。
• 含最小、最小值

select * from emp where age between 15 and 20;
# 查询年龄在 15岁到 20岁之间得员工信息

and 和 or 和 not

• AND、OR、NOT 是用于对过滤条件的逻辑处理指令。
• AND 优先级高于 OR，为了明确处理顺序，可以使用 ()。
• AND 操作符表示左右条件都要满足。
• OR 操作符表示左右条件满足任意一个即可。
• NOT 操作符用于否定一个条件。

AND 示例

# 查询性别为 女 且年龄小于 25岁 的员工信息
select * from emp where age < 25 and gender = '女';

OR 示例

# 查询年龄等于 18 或 20 或 40 的员工信息
select * from emp where age = 18 or age = 20 or age = 40;

NOT 示例

# 查询有身份证号的员工信息
select * from emp where idcard is not null;

like

• LIKE 操作符在 WHERE 子句中使用，作用是确定字符串是否匹配模式。
• 只有字段是文本值时才使用 LIKE。
• LIKE 支持两个通配符匹配选项：% 和 _。
• 不要滥用通配符，通配符位于开头处匹配会非常慢。
• % 表示任何字符出现任意次数。
• _ 表示任何字符出现一次。

% 示例

任意字符出现任意次数

select name from emp where name like "范%";
#查询姓范的人

_ 示例

select name from emp where name like "范_";
#查询只有两个字的范姓的人

分页查询

select 字段列表 from 表名 limit 起始索引,查询记录数;

# 分页查询
# 查询第1页员工数据，展示10条记录
select * from emp limit 0,10;
 
select * from emp limit 10;
 
# 从第11行开始输出后面的10条数据
select * from emp limit 10, 10;

注意：

• 起始索引的数据不输出，查询第2行的数据，需要从1开始。起始索引从0开始，起始索引 = (查询页码 - 1)*每页显示记录数。 
• 分页查询是数据库的方言，不同的数据库有不同的实现，MySQL中是limit。
• 如果查询的最开始的数据，起始索引可以省略，直接简写为limit 10。

DCL(待补充)

DCL英文全称是Data Control Language（数据控制语言），用来管理数据库用户、控制数据库的访问权限。

TCL

不能回退 SELECT 语句，回退 SELECT 语句也没意义；也不能回退 CREATE 和 DROP 语句。

MySQL 默认是隐式提交，每执行一条语句就把这条语句当成一个事务然后进行提交。当出现 START TRANSACTION 语句时，会关闭隐式提交；当 COMMIT 或 ROLLBACK 语句执行后，事务会自动关闭，重新恢复隐式提交。

通过 set autocommit=0 可以取消自动提交，直到 set autocommit=1 才会提交；autocommit 标记是针对每个连接而不是针对服务器的。

指令：

• START TRANSACTION - 指令用于标记事务的起始点。
• SAVEPOINT - 指令用于创建保留点。
• ROLLBACK TO - 指令用于回滚到指定的保留点；如果没有设置保留点，则回退到 START TRANSACTION 语句处。
• COMMIT - 提交事务。

-- 开始事务
START TRANSACTION;
 
-- 插入操作 A
INSERT INTO `user`
VALUES (1, 'root1', 'root1', 'xxxx@163.com');
 
-- 创建保留点 updateA
SAVEPOINT updateA;
 
-- 插入操作 B
INSERT INTO `user`
VALUES (2, 'root2', 'root2', 'xxxx@163.com');
 
-- 回滚到保留点 updateA
ROLLBACK TO updateA;
 
-- 提交事务，只有操作 A 生效
COMMIT;

函数

日期函数：

数值函数

常见的数值函数如下：

MySQL中内置了很多字符串函数，常用的几个如下：

流程函数

流程函数也是很常见的一类函数，可以在SQL语句中实现条件筛选，从而提高语句的效率。

窗口函数（待补充）：

窗口函数也叫OLAP函数(Online Analytical Processing,联机分析处理)或者分析函数，可以对数据进行实时分析处理。

窗口函数和group by有类似之处，其区别在于窗口会对每个分组之后的数据进行分别操作，而group by一般对分组之后的函数使用聚集函数汇总。

窗口函数和普通聚合函数的区别

①聚合函数是将多条记录聚合为一条；窗口函数是每条记录都会执行，有几条记录执行完还是几条。

②聚合函数也可以用于窗口函数

完整的窗口函数如下定义：

其中 window function 是窗口函数的名称，expression 是可选的分析对象 (字段名或者表达式)

OVER 子句包含分区(PARTITION BY)、排序(ORDER BY)以及窗口大小(frame clause)3 个选项。

• partition by子句：按照指定字段进行分区，窗口函数在不同的分区内分别执行，
• order by子句：按照指定字段进行排序，窗口函数将按照排序后的记录顺序进行编号。可以和partition by子句配合使用，也可以单独使用。
• frame子句：当前分区的一个子集，用来定义子集的规则，通常用来作为滑动窗口使用。

常见的窗口函数：

将上述函数按照功能划分，可以把MySQL支持的窗口函数分为如下几类：

序号函数：

• row_number(）：进行排序，输出为1，2，3
• rank():进行排序，相同的值会是相同序号，序号不连续，输出为1，2，2，4
• dense_rank()：进行排序，相同的值会是相同序号，序号连续，输出为1，2，2，3

分布函数：PERCENT_RANK()、CUME_DIST()

前后函数：LAG()、LEAD()

头尾函数：FIRST_VALUE()、LAST_VALUE()

其它函数：NTH_VALUE()、NTILE()

待补充

多表联查

多表关系

表结构之间也存在着各种联系，基本上分为三种：

• 一对多
• 一对一
• 多对多

一对多

案例：部门 与 员工的关系

关系：一个部门对应多个员工，一个员工对应一个部门

实现：在多的一方建立外键，指向一的方的主键
 

多对多

案例：学生与课程的关系

关系：一个学生可以选修多门课程，一门课程也可以供多个学生选择

实现：建立第三张中间表，中间表至少包含两个外键，分别关联两方主键

一对一

案例：用户 与 用户详情的关系

关系：一对一关系，多用于单表拆分，将一张表的基础字段放在一张表中，其他详情字段放在另一张表中，以提升操作效率

实现：在任意一方假如外键，关联另外一方的外键，并且设置为唯一的(unique)

多变查询概述

概述：指从多张表中查询数据

笛卡尔积：笛卡尔乘积是指在数学中，两个集合A集合 和 B集合的所有组合情况。在查询多表时，需要消除无效的笛卡尔积，即消除在查询的时候，两个表的所有组合中无效的交集。

内连接

内连接查询的是两张表的交集部分

• 隐式内连接

  select 字段列表 from 表1,表2 where 条件……;
  

• 显式内连接

  select 字段列表 from 表1 [inner] join 表2 on 连接条件……;
  

eg：查询每一个员工的姓名，及关联的部门的名称 

# 查询每一个员工的姓名，及关联的部门的名称（隐式内连接实现）
#首先实现隐式内连接，加上条件，实现消除笛卡儿集，A表的外键/外键等于B表的主键/外键：
select * from emp, dept where emp.dept_id = dept.id;
#然后加上需要查询的字符：
select emp.name, dept.name from emp, dept where emp.dept_id = dept.id;
 
 
# 查询每一个员工的姓名，及关联的部门的名称（显式内连接实现）
#首先实现显式内连接，加上条件，实现消除笛卡儿集，A表的外键/外键等于B表的主键/外键：
select * from emp inner join dept where emp.dept_id = dept.id;
#然后加上需要查询的字符：
select emp.name, dept.name from emp inner join dept on emp.dept_id = dept.id;  # 正常写法
 
select emp.name, dept.name from emp join dept on emp.dept_id = dept.id;  # 省略inner
 
#在查询中起别名。
select e.name, d.name from emp e join dept d on e.dept_id = d.id;    # 起别名

 注意：在起别名后，必须使用别名，否则会报错。

注意：在查询中，如果有一方主键/外键为null，此时则不属于两张表的交集，此时会查询不到。比如17号。

外连接

外连接查询语法：

左外连接

select 字段列表 from 表1 left [outer] join 表2 on 条件……;

相当于查询字段1（左表）的所有数据包含表1和表2交集部分的数据

右外连接

select 字段列表 from 表1 right [outer] join 表2 on 条件……;

相当于查询表2（右表）的所有数据包含表1和表2交集部分数据
 

eg:查询emp表的所有数据，和对应的部门信息（左外连接）

select emp.*, dept.name from emp left outer join dept on emp.dept_id = dept.id;
select emp.*, dept.name from emp left  join dept on emp.dept_id = dept.id;#可以省略outer

此时，可以查询到17号dept_id为空的数据，因为此数据不包括在A表和B表的交集，但是属于A表。

eg:查询dept表的所有数据，和对应的员工信息（右外连接）

select dept.*, emp.* from emp right outer join dept on emp.dept_id = dept.id;
select * from emp e right join dept d on e.dept_id = d.id;#起别名

自连接

自连接查询，可以是内连接，也可以是外连接查询。一定要起别名。根据是交集还是所有信息进行选择内连接和外连接查询。

select 字段列表 from 表A 别名A join 表名A 别名B on 条件……;

eg：查询 员工 及其 所属领导的名字

select a.name '员工', b.name '领导' from emp a join emp b on a.managerid = b.id;

联合查询（union,union all）

对于union查询，就是把多次查询的结果合并起来，形成一个新的查询结果集。

#将上面的查询个下面的查询组合起来
select 字段列表 from 表A …… 
union [all]
select 字段列表 from 表B ……;

eg：将工资低于 5000 的员工， 和 年龄大于50岁的员工全部查询出来

select * from emp where salary < 5000
union all
select * from emp where age > 50;

注意：

• union all是将查询结果直接拼接，如果有同时满足两个查询条件的，会重复输出，需要去重就用union。
• 对于联合查询的多张表的列数必须保持一致，字段类型也需要保持一致。否则会报错。

子查询

概念：SQL语句中嵌套select语句，称为嵌套语句，又称子查询。

select * from t1 where column1 = (select column1 from t2);

 子查询外部的语句可以使insert / update / delete / select 的任何一个。

• 根据子查询结果不同，分为：

  • 标量子查询（子查询结果为单个值）
  • 列子查询（子查询结果为一列）
  • 行子查询（子查询结果为一列）
  • 表子查询（子查询结果为多行多列）

• 根据子查询位置，分为： where之后、from之后、select之后。

标量子查询

子查询返回的结果是单个值（数字、字符串、日期等），最简单地形式，这种子查询称为标量子查询

常用的操作符：= <> > >= < <=

eg：查询销售部的所有员工信息
#子查询的结果为：4
select * from emp where dept_id = (select id from dept where name = '销售部');

列子查询

子查询返回的结果是一列（可以是多行），这种查询称为列子查询

eg;查询 销售部 和 市场部的所有员工信息

子查询的结果为：

 使用in的时候，需要dept_id为2或者4之一。

# 查询 销售部 和 市场部 的部门ID
select id from dept where name = '市场部' or name = '销售部'
# 根据部门ID，查询员工信息
select * from emp where dept_id in (select id from dept where name = '市场部' or name = '销售部');

eg：查询比 财务部 所有人工资都高的员工信息

第一个子查询的结果为：3

第二个子查询的结果为：

# 查询所有 财务部 人员工资
select id from dept where name = '财务部';
 
select salary from emp where dept_id = (select id from dept where name = '财务部');
# b 比 财务部 所有人工资都高的员工
select * from emp where 
salary > all(select salary from emp where 
dept_id = (select id from dept where name = '财务部'));

eg ：查询比研发部其中任意一人员工工资高的员工信息（all换成any）

# 查询研发部门所有人工资
select salary from emp where dept_id = (select id from dept where name = '研发部');
 
# 比研发部其中任意一人工资都高的员工信息
select * from emp where salary > any(select salary from emp where dept_id = (select id from dept where name = '研发部'));

行子查询

子查询返回的结果是一行（可以是多列），这种子查询称为行子查询。

常用的操作符：= 、 <> 、 in、 not in

eg：查询与 张无忌 的薪资及直属领导相同的员工信息

子查询的结果：

# 行子查询
# 查询 张无忌 的薪资及直属领导相同的员工信息
select salary, managerid from emp where name = '张无忌';
 
# 查询与 张无忌 的薪资及直属领导相同的员工信息
select * from emp where (salary, managerid) = (select salary, managerid from emp where name = '张无忌');

表子查询

子查询返回的结果是多行多列，这种子查询称为表子查询。

常用的操作符：in

eg：查询与 鹿杖客、宋远桥 的职位和薪资相同的员工信息

# 表子查询
# 查询 鹿杖客、宋远桥 的职位和薪资
select job, salary from emp where name = '鹿杖客' or name = '宋远桥';
 
# 查询与 鹿杖客、宋远桥 的职位和薪资相同的员工信息
select * from emp where (job, salary) in 
(select job, salary from emp where name = '鹿杖客' or name = '宋远桥');

eg：查询入职日期是 2006-01-01 之后的员工信息，及其部门信息

# 表子查询
# 入职日期是 2006-01-01 之后的员工信息
select * from emp where entrydate > '2006-01-01';
 
# 使用上面查询出来的表进行联查另外一个表
#查询这部分员工，对应的部门信息
select e.*, d.* from (select * from emp where entrydate > '2006-01-01') e 
left join dept d on e.dept_id = d.id;

多表联查例子：

create table dept
(
    id   int auto_increment comment 'ID' primary key,
    name varchar(50) not null comment '部门名称'
) comment '部门表';
 
insert into dept (id, name)
values (1, '研发部'),
       (2, '市场部'),
       (3, '财务部'),
       (4, '销售部'),
       (5, '总经办'),
       (6, '人事部');
 
 
drop table if exists emp;
 
create table emp
(
    id        int auto_increment comment 'ID' primary key,
    name      varchar(50) not null comment '姓名',
    age       int comment '年龄',
    job       varchar(20) comment '职位',
    salary    int comment '薪资',
    entrydate date comment '入职时间',
    managerid int comment '直属领导ID',
    dept_id   int comment '部门ID',
    -- 添加外键
    constraint fk_emp_dept_id foreign key (dept_id) references dept (id)
) comment '员工表';
 
insert into emp (id, name, age, job, salary, entrydate, managerid, dept_id)
values (1, '金庸', 66, '总裁', 20000, '2000-01-01', null, 5),
       (2, '张无忌', 20, '项目经理', 12500, '2005-12-05', 1, 1),
       (3, '杨逍', 33, '开发', 8400, '2000-11-03', 2, 1),
       (4, '韦一笑', 48, '开发', 11000, '2002-02-05', 2, 1),
       (5, '常遇春', 43, '开发', 10500, '2004-09-07', 3, 1),
       (6, '小昭', 19, '程序员鼓励师', 6600, '2004-10-12', 2, 1),
       (7, '灭绝', 60, '财务总监', 8500, '2002-09-12', 1, 3),
       (8, '周芷若', 19, '会计', 48000, '2006-06-02', 7, 3),
       (9, '丁敏君', 23, '出纳', 5250, '2009-05-13', 7, 3),
       (10, '赵敏', 20, '市场部总监', 12500, '2004-10-12', 1, 2),
       (11, '鹿杖客', 56, '职员', 3750, '2006-10-03', 10, 2),
       (12, '鹤笔翁', 19, '职员', 3750, '2007-05-09', 10, 2),
       (13, '方东白', 19, '职员', 5500, '2009-02-12', 10, 2),
       (14, '张三丰', 88, '销售总监', 14000, '2004-10-12', 1, 4),
       (15, '俞莲舟', 38, '销售', 4600, '2004-10-12', 14, 4),
       (16, '宋远桥', 40, '销售', 4600, '2004-10-12', 14, 4),
       (17, '陈友谅', 42, null, 2000, '2011-10-12', 1, null);
 
create table salgrade(
    grade int,
    losal int,
    hisal int
)comment '薪资等级表';
 
insert into salgrade
values (1, 0, 3000),
       (2, 3001, 5000),
       (3, 5001, 8000),
       (4, 8001, 10000),
       (5, 10001, 15000),
       (6, 15001, 20000),
       (7, 20001, 25000),
       (8, 25001, 30000);

#查询员工的姓名、年龄、职位、部门信息（隐式内连接）
select e.name,e.age,e.job,d.name as '部门name' from emp e ,dept d where e.dept_id=d.id;
#查询年龄小于30岁的员工的姓名、年龄、职位、部门信息（显示内连接）
select e.name,e.age,e.job,d.name from emp e inner join dept d on e.dept_id=d.id where e.age<30 ;
#查询拥有员工的部门ID、部门名称
select distinct d.id ,d.name from dept d inner join emp e where d.id=e.dept_id;#内连接
select id ,name from dept where id in (select distinct dept_id from emp);#子查询
#查询所有年龄大于40岁的员工，及其归属的部门名称；如果员工没有分配部门，也需要显示出来
select e.*,d.name from emp e left join dept d on e.dept_id=d.id where e.age>40 ;
#查询所有员工的工资等级
select e.id,e.salary,s.*  from emp e , salgrade s where e.salary between s.losal and s.hisal;
#查询研发部所有员工的信息工资等级
select a.id,a.name,a.salary ,s.* from (select * from emp where emp.dept_id=(select id from dept where dept.name='研发部')) a ,salgrade s where a.salary between s.losal and s.hisal;#自己写的
#表三个emp,dept,salgrade
#连接条件两个(e.salary between s.losal and s.hisal) ； d.id=e.dept_id
#查询条件d.name='研发部'
SELECT e.*, .NAME, s.*  FROM emp e, dept d, salgrade s  WHERE ( e.salary BETWEEN s.losal AND s.hisal ) AND d.id = e.dept_id AND d.NAME = '研发部';
#查询研发部员工的平均工资
#表：dept，emp
#连接条件：dept.id=emp.dept_id
#查询条件：dept.name='研发部'
select avg(e.salary) from emp e, dept d where d.id=e.dept_id and d.`name`='研发部';
#查询工资比灭绝高的员工信息
select e.* from emp e where e.salary>(select e.salary from emp e where e.`name`='灭绝');#子查询
#select * from emp a join emp b where a.salary
select e1.* from emp e1,emp e2 where e1.salary>e2.salary and e2.name='灭绝' ;#自连接
#查询比平均薪资高的员工
select e.* from emp e where e.salary>(select avg(e.salary) from emp e);
#查询低于本部门平均工资的员工信息
select e1.* ,(select avg(e.salary) from emp e where e.dept_id=e1.dept_id) from emp e1 where e1.salary<(select avg(e.salary) from emp e where e.dept_id=e1.dept_id);
#查询所有的部门信息，并统计部门的员工人数
select * ,(select count(*) from emp where emp.dept_id=d.id )as '人数' from dept d ;