sql学习(一)

具体学习视频请参考：https://www.bilibili.com/video/BV12b411K7Zu?

SQL语言是一种数据库查询和程序设计语言，用于存取数据以及查询、更新和管理关系数据库系统。博客将会根据课程并结合相关案例详细介绍sql的具体语法。

mysql服务的启动和停止

命令行：

• 停止 net stop MySQL名字
• 启动 net start MySQL名字

DQL(数据查询)语言学习

• 基础查询
• 条件查询
• 排序查询
• 常见函数
• 分组函数
• 分组查询
• 连接查询
• 分页查询
• union联合查询

DML(数据管理)语言学习

• 增删改语句

DDL(数据定义)语言学习

• 库和表的管理
• 常见数据类型介绍
• 常见约束

TCL(事物管理)语言学习

• 事务和事务处理

基础查询

语法：
select 查询列表 from 表名;
特点：
1.查询的列表可以是：表中的字段，常量值，函数，表达式
2.查询的结果是虚拟的表格

小补充：F12格式化

取别名的好处：

• 有利于理解
• 如果要查询的字段有相同的名字，可以区分开来

+号的作用

• 只有一个功能：运算符
• 两个操作数都为数值型，则做加法运算
• 有其中一方为字符型，则试图将其中一方转换成数值型
  • 如果转换成功，则做正常的加法运算
  • 如果转换失败，则将字符型转换成0
• 其中一方为null，结果为null

#指定选用的库
USE myemployees;
#查询表中的单个字段：
SELECT last_name FROM employees;
#查询表中的多个字段
SELECT last_name,email,job_id FROM employees;
#查询表中的所有字段
SELECT * FROM employees;
#查询常量值
SELECT 100;
SELECT 'join';
#查询表达式
SELECT 100*98;
#查询函数
SELECT VERSION();

#起别名
SELECT 100*89 AS 结果;
SELECT 100*89 结果；

#去重
#案例:查询员工表中所有部门的部门编号
SECOND DISTINCT department_id FROM employees;

#连接
#案例：查询员工的名和员工的姓连接成一个字段并显示成姓名
SELECT CONCAT(last_name,first_name) AS 姓名 FROM employees;

#显示表的结构
DESC departments;

#案例
SELECT 
 CONCAT(last_name,',',IFNULL(commission_pct,0)) AS output
FROM
 employees;

条件查询

语法：
SELECT 查询列表 FROM 表名 WHERE 筛选条件;
分类

• 按条件表达式筛选
• 条件运算符：< > = != <> <= >=
• 按逻辑表达式筛选
• 逻辑运算符：and or not && || !
  • 作用：用来连接条件表达式
• 模糊查询
• like； between and； in； is null；is not null
• like一般和通配符使用
  • 通配符
    • % 任意多个字符(包含0个字符)
    • ‘-’ 1个字符
    • \ 转义字符
• between and 特点
  • 包含临界值
  • 两个临界值不能调换
• in的作用：判断某字段是否属于in列表中的项
  • 特点：
    • 提高语句简洁度
    • in列表的值类型必须统一(兼容）
    • 不支持通配符
• <=> 安全等于，可以判断null值，也可以不是判断null值

#按条件表达式筛选
SELECT * FROM employees WHERE salary >12000;
SELECT last_name FROM employees WHERE department_id <>90;

#按逻辑表达式查询
SELECT last_name,salary,commission_pct FROM employees WHERE salary >=10000 AND salary<=2000;

SELECT
 last_name,
 department_id
FROM
 employees
WHERE
 department_id<90 OR department_id>110 OR salary>15000;
 
#模糊查询
SELECT 
 *
FROM 
 employees
WHERE
 last_name LIKE '%a%';

SELECT * FROM employees WHERE salary BETWEEN 10000 AND 20000;

SELECT last_name,job_id FROM employees WHERE job_id IN ('AD_VP','FI_MGR');

SELECT salary,commission_pct FROM employees WHERE commission_pct IS NULL;

SELECT salary,commission_pct FROM employees WHERE commission_pct IS NOT NULL;

#安全等于 <=>
SELECT salary,commission_pct FROM employees WHERE commission_pct <=> NULL;
SELECT salary,commission_pct FROM employees WHERE salary <=> 12000;

排序查询

语法：
SELECT 查询列表 FROM 表名 [WHERE 筛选条件] ORDER BY DESC/ASC;
特点：

• desc降序，asc升序
• 如果不写，默认升序
• 可以支持单个字段，多个字段也可以，表达式，函数，别名都可以
• 执行顺序：一般放在查询语句最后面，limit子句除外

SELECT salary,last_name
FROM employees
WHERE salary>10000
ORDER BY salary DESC;

SELECT *,salary*12*(1+IFNULL(commission_pct,0)) 年薪
FROM employees
ORDER BY salary*12*(1+IFNULL(commission_pct,0)) DESC;

SELECT LENGTH(last_name) 长度,salary 
FROM employees
ORDER BY 长度 DESC;

SELECT *
FROM employees
ORDER BY salary DESC,job_id;

常见函数

概念：将一组逻辑语句封装在函数体内，对外暴露方法名
好处:

• 隐藏了实现细节
• 提高了代码的重用性

调用：
SELECT 函数名(实参列表) 【from 表】
分类：

• 单行函数

  • concat() 拼接字符串
  • length() 获取字节个数
  • upper() 转换大写，lower() 转换小写
  • substr() ，substring()截取字符
  • instr() 返回子串在主串中的起始索引，如果找不到返回0
  • trim() 去前后空格
  • lpad() 用指定的字符在左边填充指定长度
  • rpad() 用指定的字符在右边填充指定长度
  • replace() 替换
  • isnull()等

• 数学函数

  • round() 四舍五入
  • ceil() 向上取整
  • floor() 向下取整
  • truncate() 截断
  • mod() 取余
  • rand() 获取随机数，返回0-1之间的小数

• 日期函数

  • now() 返回当前系统日期和时间
  • curdate() 返回当前日期不包括时间
  • curtime() 返回当前时间不包含日期
  • 获取之指定的部分
    • 年 year()
    • 月 month()
    • monthname() 用英文表示月份
    • 日 day()
    • 小时 hour()
    • 分钟 minute()
    • 秒 seconde()
    • datediff() 两个日期相差的天数

• str_to_date() 将日期格式的字符转换成指定格式

• date_formate() 将日期转换成字符

• 其他函数

  • version() 版本号码
  • datebase() 查看当前的数据库
  • user() 当前用户
  • password·(‘字符’) 返回该字符的加密形式
  • md5(‘字符’) 返回该字符md5的加密形式

• 流程控制函数

  • if 函数：实现类似if else的效果
  • case 函数
    • 使用一 相当于switch case的使用效果
    • case 要判断的字段或表达式 when 常量1 then 要显示的值1或语句1 … else 要显示的值n或语句n end
    • case 使用2：类似于多重if
    • case when 条件1 then 语句1或要显示的值1 …else 要显示的值n end

• 分组函数 统计函数 组函数 ：做统计使用

• 分类

  • max() ,min() 最大最小值
  • sum() 求和
  • avg() 平均值
  • count() 计算个数

• 参数支持的类型

  • sum和avg处理数值型
  • max和min和count可以处理数值型和字符型

• 是否忽略null值

  • null值不参与运算

• 可以和distinct搭配去重运算

• count函数的单独介绍

  • count(*)，count(1)统计行数

#将姓大写，名小写拼接在一起
SELECT CONCAT(UPPER(last_name),LOWER(first_name)) 姓名
FROM employees;

#将美好提取出来，下标从1开始
SELECT SUBSTR('这个世界真美好',6);
#将这个世界截取出来，第一个参数是位置参数，第二个参数是长度
SELECT SUBSTR('这个世界真美好',1,4);

#案例：将姓第一个字母大写，其他的小写，然后用_拼接
SELECT CONCAT(UPPER(SUBSTR(last_name,1,1)),'_',LOWER(SUBSTR(last_name,2)))
FROM employees;

#返回美好所在的索引位置
SELECT INSTR('这个世界真美好','美好') AS out_put;

#去前后空格
SELECT TRIM('    你好 呀  ') AS out_put;

#去掉前后的aaa
SELECT TRIM('a' FROM'aaaa你好 aa 呀aaaaa') AS out_put;

#左填充a使得整个字符个数为10 
SELECT LPAD('你好',10,'a');

#将世界替换成WORLD
SELECT REPLACE('你好呀世界','世界','WORLD');

#数学函数
#四舍五入
SELECT ROUND(1.66);
SELECT ROUND(1.355);
#向上取整，乡下取整
SELECT CEIL(1.59);
SELECT FLOOR(1.55);
#截断
SELECT TRUNCATE(1.55,1);
#取余
SELECT MOD(10,3);

#日期时间
SELECT NOW();
SELECT CURDATE();
SELECT CURTIME();
SELECT YEAR(NOW());
SELECT YEAR('1999-8-12') 年;
SELECT YEAR(hiredate) 年 FROM employees;
SELECT MONTH(NOW()) 月;
SELECT MONTHNAME(NOW()) 月;

#str_to_date
SELECT STR_TO_DATE('1999-12-8','%Y-%c-%d') 日期;
SELECT * FROM employees WHERE hiredate=STR_TO_DATE('4-3 1992','%c-%d %Y');

#date_format
SELECT DATE_FORMAT('1999/8/12','%Y-%c-%d');

#案例：查询有奖金的员工的工资和入职日期(xx月/xx日  xx年)
SELECT salary,DATE_FORMAT(hiredate,'%x/%d %Y') FROM employees WHERE commission_pct IS NOT NULL;

#其他函数
SELECT VERSION();
SELECT DATABASE();
SELECT USER();

#流程控制函数
#if
SELECT IF(10>5,'大','小');
SELECT last_name,IF(ISNULL(commission_pct),0,commission_pct) 
FROM employees;

#case 
#查询员工的工资，如部门号为30，则显示原工资的1.1倍，部门号为40，显示原工资的1.2倍，部门号为50，显示原工资的1.3倍，其他部门显示原工资
SELECT salary 原始工资,department_id,
CASE department_id
WHEN 30 THEN salary*1.1
WHEN 40 THEN salary*1.2
WHEN 50 THEN salary*1.3
ELSE salary
END AS 新工资
FROM employees;

#case使用2
#查询员工的工资情况
#如果工资大于20000，显示A级别
#如果工资大于15000 显示B
#大于10000，显示C
#否则 ，显示D
SELECT salary,
CASE
WHEN salary>20000 THEN 'A'
WHEN salary BETWEEN 15000 AND 20000 THEN 'B'
WHEN salary BETWEEN 10000 AND 15000 THEN 'C'
ELSE 'D'
END AS 工资等级
FROM employees;

#分组函数
SELECT AVG(salary) FROM employees;
SELECT SUM(salary) FROM employees;
SELECT MIN(salary) FROM employees;
SELECT MAX(salary) FROM employees;
SELECT COUNT(salary) FROM employees;

SELECT SUM(DISTINCT salary) FROM employees;
SELECT COUNT(DISTINCT salary) FROM employees;

#使用count统计行数
SELECT COUNT(*) FROM employees;
SELECT COUNT(1) FROM employees;

分组查询

分组查询的筛选条件分类：

• 分组前筛选 数据源为原始表 用where
• 分组后筛选 分组后的结果集 用having
• 能用分组前筛选的尽量用分组前

#查询每个部门的平均工资
SELECT salary,department_id
FROM employees
GROUP BY department_id;

#查询每个工种的最高工资
SELECT job_id,MAX(salary) 最高工资
FROM employees
GROUP BY job_id;

#查询每个位置上的部门个数
SELECT COUNT(*) 部门个数,location_id
FROM departments
GROUP BY location_id;

#查询邮箱中包含a字符的每个部门的平均工资
SELECT email,COUNT(*) 部门个数,AVG(salary)
FROM employees
WHERE email LIKE '%a%'
GROUP BY department_id;

#添加复杂的筛选条件
#查询哪个部门的员工个数>2
#查询每个部门的员工个数
#根据上面的结果查询个数大于2的部门
SELECT COUNT(*) 员工个数,department_id
FROM employees
GROUP BY department_id
HAVING COUNT(*)>2;

#查询领导编号大于102的每个领导手下的最低工资大于5000的领导编号和最低工资
SELECT MIN(salary) 最低工资,manager_id
FROM employees
WHERE manager_id>102
GROUP BY manager_id
HAVING MIN(salary)>5000;

#按表达式或函数分组
#按员工姓名的长度分组，查询每一组的员工个数，筛选员工个数>5的有哪些
SELECT LENGTH(last_name) 姓名长度,COUNT(*) 员工个数
FROM employees
GROUP BY 姓名长度
HAVING COUNT(*)>5;

#按多个字段进行分组
#查询每个部门每个工种的平均工资
SELECT AVG(salary) 平均工资
FROM employees
GROUP BY department_id,job_id;

#添加排序
#查询每个部门每个工种的平均工资，并按平均工资的高低进行排序
SELECT AVG(salary) 平均工资
FROM employees
GROUP BY department_id,job_id
ORDER BY 平均工资 DESC;

连接查询

又称为多表查询或多表连接
当查询的字段涉及到多个表的字段时，这个时候需要用到多表查询
笛卡尔乘积现象：
表1有m行，表2有n行，结果为m*n行
发生原因：没有有效的连接条件
如何避免：添加连接条件
分类：

• 按年代分类
  • sql92标准
  • sql99标准[推荐]
• 按功能进行分类
  • 内连接
    • 等值连接
    • 特点：
    • 多表等值连接的结果为多表的交集部分
    • n表连接至少需要n-1条连接条件
    • 多表的顺序没有要求
    • 一般需要为多表起别名
    • 可以搭配前面的所有子句使用
    • 非等值连接
    • 自连接
  • 外连接
    • 左外连接
    • 右外连接
    • 全外连接
  • 交叉连接

为表起别名的好处

• 提高简洁度
• 区分多个重名字段
• 注意，如果起了别名，那么就不可以用原来的表名去限定

#等值连接
#案例：查询员工名和对应的部门名
SELECT department_name,last_name
FROM departments,employees
WHERE departments.`department_id`=employees.`department_id`;

#为表起别名
SELECT department_name,last_name
FROM departments AS d,employees AS l
WHERE d.department_id=l.department_id;

#添加筛选条件  AND
#查询有奖金的员工名和部门名
SELECT last_name,department_name
FROM employees e,departments d
WHERE d.`department_id`=e.`department_id`
AND e.`commission_pct` IS NOT NULL;

#添加分组
#查询每个城市的部门个数和城市名
SELECT COUNT(*) 部门个数,city
FROM departments d,locations l
WHERE d.`location_id`=l.`location_id`
GROUP BY l.`city`;

#添加排序
#查询每个工种的工种名和员工个数，并按员工个数降序
SELECT job_title,COUNT(*) 员工个数
FROM jobs j,employees e
WHERE j.`job_id`=e.`job_id`
GROUP BY j.`job_id`
ORDER BY COUNT(*) DESC;

#三表连接
#查询员工名，部门名和所在的城市
SELECT last_name,city,department_name
FROM employees e,locations l,departments d
WHERE d.`location_id`=l.`location_id`
AND e.`department_id`=d.`department_id`;

#非等值连接
SELECT salary,grade_level
FROM employees e,job_grades j
WHERE salary BETWEEN j.`lowest_sal` AND j.`highest_sal`;

#自连接
#查询员工名和其上级的名字
SELECT e.employee_id,e.last_name,m.employee_id,m.last_name
FROM employees e,employees m
WHERE e.`manager_id`=m.`employee_id`;

分类：

• 内连接 inner
• 等值连接
• 特点：
  • 可以添加前面的子句
  • inner 可以省略
  • 筛选条件放在where后面，连接条件放在on后面，提高分离性，便于阅读
  • 查询多表的交集部分
• 非等值连接
• 外连接
• 应用场景
  • 一般用于查询一个表中有，另外一个表中没有的记录
• 外连接的查询结果为主表中的所有记录
  • 如果从表中有匹配的值，则显示匹配的值
  • 如果从表中没有与之匹配的，则显示null
  • 外连接的结果=内连接的结果+主表中有而从表中没有的记录
  • 左外连接 left 【outer】
  • left左边的为主表
  • 右外连接 right 【outer】
  • right的右边的为主表
  • 左外和右外交换两个表可以达到相同的效果
  • 全外连接 full 【outer】
  • 全外连接的结果相当于左外连接和右外连接的结果之和
• 交叉连接 cross
• 相当于笛卡尔乘积

#sql99语法
#语法
SELECT 查询列表
FROM 表1 别名【连接条件】
JOIN 表2 别名
ON 连接条件
WHERE 筛选条件

#内连接	 
#等值连接
#查询员工名和部门名
SELECT last_name,department_name
FROM employees e
INNER JOIN departments d
ON e.`department_id`=d.`department_id`;

#加上筛选
#查询员工名字中包含e的员工名和工种名
SELECT last_name,job_title
FROM employees e
INNER JOIN jobs j
ON e.`job_id`=j.`job_id`
WHERE last_name LIKE '%e%';

#添加分组和筛选
#查询部门个数大于3的城市名和部门个数
SELECT COUNT(*) 部门个数,city
FROM locations l
INNER JOIN departments d
ON d.`location_id`=l.`location_id`
GROUP BY city
HAVING COUNT(*)>3;

#添加排序
查询哪个部门的员工个数大于3的部门名和员工个数，并按个数降序
SELECT COUNT(*) 员工个数,department_name
FROM departments d
INNER JOIN employees e
ON d.`department_id`=e.`department_id`
GROUP BY e.department_id
HAVING COUNT(*)>3
ORDER BY COUNT(*) DESC;

#三表连接
#查询员工名，部门名和工种名，并按部门名降序
SELECT last_name,department_name,job_title
FROM employees e 
INNER JOIN departments d ON e.`department_id`=d.`department_id`
INNER JOIN jobs j ON j.`job_id`e.`job_id`
ORDER BY department_name DESC;		

#非等值连接
#查询员工的工资级别
SELECT salary,grade_level
FROM employees e
INNER JOIN job_grades j
ON e.`salary` BETWEEN j.`lowest_sal` AND j.`highest_sal`;

#自连接
#查询员工的名字和其上级的名字
SELECT e.last_name,m.last_name
FROM employees e
INNER JOIN employees m
ON e.`manager_id`=m.`employee_id`;

#外连接
#左右外连接
#查询哪个部门没有员工
SELECT department_name,employee_id
FROM departments d
LEFT OUTER JOIN employees e
ON d.`department_id`=e.`department_id`
WHERE e.`employee_id` IS NULL;

sql92 VS sql99
功能：sql99支持的语法更多
可读性：sql99实现连接条件和筛选条件的分离

#案例：查询哪个城市没有部门
SELECT city,department_id
FROM departments d
RIGHT OUTER JOIN locations l
ON d.`location_id`=l.`location_id`
WHERE d.`department_id` IS NULL;

#查询部门名为SAL或IT的员工信息
SELECT e.*,department_name
FROM employees e
LEFT OUTER JOIN departments d
ON e.`department_id`=d.`department_id`
WHERE d.department_name='ASL' OR d.`department_name`='IT';

子查询

定义：出现在其他语句中的select语句，称之为子查询或内查询
外部的查询语句称为主查询或外查询
分类：

• 按子查询出现的位置分
  • select后面
    • 仅仅支持标量子查询
  • from后面
    • 支持表子查询
  • where或having后面
    • 标量子查询
    • 列子查询
    • 行子查询(用的较少)
    • 特点：
    • 子查询放在小括号内
    • 子查询一般放在条件的右侧
    • 标量子查询，一般搭配着单行操作符使用(< > = <>)
    • 列子查询一般搭配着多行操作符使用(in any/some all)
    • 列子查询(多行查询)的操作符
    • in/not in 等于列表中的任一一个
    • any/some 和子查询返回的某一个值比较
    • all 和子查询返回的所有值比较
  • exists后面(相关子查询)
    • 支持表子查询
• 按结果集的行列数不同分类
  • 标量子查询(结果集只有一行一列)
  • 列子查询(结果集只有一列多行)
  • 行子查询(结果集可以有一行多列)
  • 表子查询(结果集一般有多行多列)

where或having后面

#1.标量子查询
#案例：谁的工资比Abel高
#步骤：查询名字为Abel的工资
SELECT salary
FROM employees
WHERE last_name='Abel';
#查询员工信息，salary>Abel的工资
SELECT *
FROM employees
WHERE salary>(
 SELECT salary
 FROM employees
 WHERE last_name='Abel'
);

#案例2：返回job_id与141号相同，salary比143号员工多的员工姓名，job_id和工资
SELECT job_id,salary,last_name
FROM employees
WHERE job_id=(
 SELECT job_id
 FROM employees
 WHERE employee_id=141
)
AND salary>(
 SELECT salary
 FROM employees
 WHERE employee_id=143
);

#案例3：返回工资最少的员工的last_name，job_id和salary
SELECT last_name,job_id,salary
FROM employees
WHERE salary=(
 SELECT MIN(salary) 
 FROM employees
);

#案例4：查询最低工资大于50号部门的最低工资的部门id和其最低工资
SELECT MIN(salary),department_id
FROM employees
GROUP BY department_id
HAVING MIN(salary)>(
 SELECT MIN(salary)
 FROM employees
 WHERE department_id=50 
);

#列子查询(多行子查询)
#案例1：返回location_id是1400或1700的部门的所有员工姓名
#步骤：查询location_id是1400或1700的部门编号
SELECT DISTINCT department_id
FROM departments
WHERE location_id IN(1400,1700);
#查询1中的部门编号的员工姓名
SELECT last_name
FROM employees
WHERE department_id IN(
 SELECT DISTINCT department_id
 FROM departments
 WHERE location_id IN(1400,1700)
);

#案例2：返回其他部门中比job_id为'IT_PROG'部门任一工资低的员工的员工号，姓名，job_id，以及salary·
#步骤：job_id为'IT_PROG'部门的员工的最低工资
#返回其他部门中工资比第一步骤中还低的员工信息
CT salary,last_name,job_id,employee_id
FROM employees
WHERE salary<ANY(
 SELECT salary
 FROM employees
 WHERE job_id='IT_PROG'
) AND job_id<>'IT_PROG';
另外一种：
SELECT salary,last_name,job_id,employee_id
FROM employees
WHERE salary<(
 SELECT max(salary)
 FROM employees
 WHERE job_id='IT_PROG'
) AND job_id<>'IT_PROG';

#案例3：返回其他部门中比job_id为'IT_PROG'部门所有工资低的员工的员工号，姓名，job_id，以及salary·
SELECT salary,last_name,job_id,employee_id
FROM employees
WHERE salary<(
 SELECT MIN(salary)
 FROM employees
 WHERE job_id='IT_PROG'
);
另一种表述：
SELECT salary,last_name,job_id,employee_id
FROM employees
WHERE salary<ALL(
 SELECT salary
 FROM employees
 WHERE job_id='IT_PROG'
);

#行子查询（结果集为一行多列或多行多列）
#案例1：查询员工编号最小但工资最高的员工信息
SELECT * 
FROM employees
WHERE employee_id=(
 SELECT MIN(employee_id)
 FROM employees
) AND salary=(
 SELECT MAX(salary)
 FROM employees
);

SELECT *
FROM employees
WHERE (employee_id,salary)=(
 SELECT MIN(employee_id),MAX(salary)
 FROM employees
);

select后面的子查询

注意：

• 仅仅支持标量子查询

#案例：查询每个部门的员工个数
SELECT d.*,(
 SELECT COUNT(*)
 FROM employees e
 WHERE e.`department_id`=d.`department_id`
) 个数
FROM departments d;

SELECT COUNT(*),d.department_id
FROM employees e,departments d
WHERE e.`department_id`=d.`department_id`
GROUP BY d.department_id;

#查询员工号为102的部门名
SELECT department_name,(
 SELECT employee_id
 FROM employees
 WHERE employee_id=102
) 员工号
FROM departments d;

#查询员工号为102 的部门名字
SELECT department_name
FROM departments d
INNER JOIN employees e
ON d.`department_id`=e.`department_id`
WHERE employee_id=102;

from后面子查询

注意：

• 要求查询结果充当一张表，要求必须起别名

#查询每个部门的平均工资的工资等级
SELECT avg_dep.*,g.`grade_level`
FROM (
 SELECT AVG(salary) average,department_id
 FROM employees e
 GROUP BY department_id
) avg_dep
INNER JOIN job_grades g
ON avg_dep.average BETWEEN g.`lowest_sal` AND g.`highest_sal`;

exists子查询(相关子查询)

语法：
exists(完整的查询语句)
结果：
1或0

我们会发现一般exists的子查询都是连接子查询

#小案例
SELECT EXISTS(SELECT salary FROM employees WHERE salary=30000);
#查询有员工名的部门名
SELECT department_name
FROM departments d
WHERE EXISTS(
 SELECT *
 FROM employees e
 WHERE d.`department_id`=e.`department_id`
);

子查询案例

#查询和Zlotkey相同部门的员工姓名和工资
SELECT last_name,salary
FROM employees
WHERE department_id=(
 SELECT department_id
 FROM employees
 WHERE last_name='Zlotkey'
);

#查询工资比公司平均工资高的员工的员工号，姓名和工资
SELECT last_name,salary,employee_id
FROM employees
WHERE salary>(
 SELECT AVG(salary)
 FROM employees
);
#查询各部门中工资比本部门平均工资高的员工的员工号和姓名
#1.找到各个部门的平均工资表
#2.用employees表对比1表，连接条件为department_id相等，条件是工资大于本部门的平均工资
SELECT last_name,employee_id,salary
FROM employees e
INNER JOIN (
 SELECT AVG(salary) ag,department_id
 FROM employees
 GROUP BY department_id
) avg_dep
ON avg_dep.`department_id`=e.`department_id`
WHERE e.`salary`>avg_dep.ag;

#查询和名字中包含字母u的员工在相同部门的员工的员工号和姓名
#1.查询员工姓名中包含u的员工所在的部门
SELECT DISTINCT department_id
FROM employees
WHERE last_name LIKE '%u%';

SELECT employee_id,last_name
FROM employees e
INNER JOIN (
 SELECT DISTINCT department_id
 FROM employees
 WHERE last_name LIKE '%u%'
) ag
ON ag.`department_id`=e.`department_id`;

SELECT employee_id,last_name
FROM employees e
WHERE e.`department_id` IN(
 SELECT DISTINCT department_id
 FROM employees
 WHERE last_name LIKE '%u%'
);

#查询在部门的location_id为1700的部门工作的员工的员工号
SELECT employee_id
FROM employees e
WHERE e.`department_id` IN(
 SELECT department_id
 FROM departments d
 WHERE d.`location_id`=1700
);

#查询管理者是King的员工的工资和姓名
#1.查询姓名为King的员工编号
SELECT employee_id
FROM employees
WHERE last_name='K_ing';
#2.查询其管理者是1的员工信息
SELECT salary,last_name,manager_id
FROM employees e
WHERE e.`manager_id` IN (
 SELECT employee_id
 FROM employees
 WHERE last_name='K_ing'
);

#查询工资最高的员工的姓名，要求first_name和last_name显示为一例，列为姓.名
#1.查询最高工资
SELECT MAX(salary)
FROM employees;
#查询工资等于1的员工信息
SELECT CONCAT(last_name,',',first_name) 姓名
FROM employees
WHERE salary=(
 SELECT MAX(salary)
 FROM employees
);