MySQL 学习日记(1)
数据库存储数据的特点
1、将数据放到表中，表再放入库中
2、一个数据库可以有多个表，每个表都有一个唯一的名字，用来标识自己，表名具有唯一性
3、表具有一些特性，这些特性定义了数据在表中如何存储，类似java中类的设计
4、表由列组成，我们也称为字段。所有的表都是由一个或多个列组成的，每一列类似java中的属性
5、表中的数据按行存储，每一行类似java中的对象。
安装
链接：https://pan.baidu.com/s/1Z13tSuemXLrG5fnAq1PqVQ
提取码：ainp
配置文件介绍
在安装目录下my.ini下
# 端口号
port=3306
#安装目录
basedir=""
# 文件目录
datadir=""
修改后，需要重启服务。
开始和停止服务
方式1：
计算机管理 -> 服务 -> MySQL... -> 启动/停止
方式2：
命令行窗口管理员模式打开 -> net start/stop MySQL...
MySQL 服务端的登录和退出
方式1:
使用MySQL自带的命令行窗口。
方式2:
使用命令行，输入指令
mysql -u root -h localhost -P 3306 -u root -p
退出 exit 或者 Ctrl+C
MySQL 常见命令感性了解
命令以;结尾
显示有多少数据库
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| test |
+--------------------+
打开数据库test
mysql> use test;
Database changed
查看有多少表
mysql> show tables;
Empty set (0.00 sec)
显示指定库的表
mysql> show tables from mysql;
+---------------------------+
| Tables_in_mysql |
+---------------------------+
| columns_priv |
| db |
| event |
| func |
| general_log |
| help_category |
| help_keyword |
| help_relation |
| help_topic |
| host |
| ndb_binlog_index |
| plugin |
| proc |
| procs_priv |
| proxies_priv |
| servers |
| slow_log |
| tables_priv |
| time_zone |
| time_zone_leap_second |
| time_zone_name |
| time_zone_transition |
| time_zone_transition_type |
| user |
+---------------------------+
查询在那个库
mysql> select database();
+------------+
| database() |
+------------+
| test |
+------------+
创建一个表
mysql> create table stuinfo(
-> id int,
-> name varchar(20));
Query OK, 0 rows affected (0.01 sec)
mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| stuinfo |
+----------------+
1 row in set (0.00 sec)
查看数据
mysql> select * from stuinfo;
Empty set (0.00 sec)
插入、删除
mysql> insert into stuinfo (id,name) values(1,'john');
Query OK, 1 row affected (0.00 sec)
mysql> insert into stuinfo (id,name) values(2,'rose');
Query OK, 1 row affected (0.00 sec)
mysql> select * from stuinfo;
+------+------+
| id | name |
+------+------+
| 1 | john |
| 2 | rose |
+------+------+
2 rows in set (0.00 sec)
mysql> update stuinfo set name='lilei' where id=1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> select * from stuinfo;
+------+-------+
| id | name |
+------+-------+
| 1 | lilei |
| 2 | rose |
+------+-------+
2 rows in set (0.00 sec)
mysql> delete from stuinfo where id=1;
Query OK, 1 row affected (0.00 sec)
mysql> select * from stuinfo;
+------+------+
| id | name |
+------+------+
| 2 | rose |
+------+------+
1 row in set (0.00 sec)
查看 MySQL 服务端版本
方式1:
mysql> select version();
+-----------+
| version() |
+-----------+
| 5.5.54 |
+-----------+
方式2：
C:\WINDOWS\system32>mysql --version
mysql Ver 14.14 Distrib 5.5.54, for Win64 (AMD64)
语法规范
1、不区分大小写，但建议关键字大写，表名、列名小写
2、每条命令最好用分号结尾
3、每条命令根据需要，可以进行缩进和换行
4、注释
单行注释: # 注释文字
单行注释: -- 注释文字
多行注释: /* 注释文字 */
安装图形用户界面
导入myemployees库
链接：https://pan.baidu.com/s/1Bv9oNdfg16EnBPE7AysziA
提取码：qwh5
下载数据库文件，导入myemployees.sql
栏位下:
-
员工编号
-
名
-
性
-
邮箱
-
电话号码
-
工作编号
-
月薪
-
奖金率
-
上级领导的员工编号
-
部门编号
-
入职时间
-
部门编号
-
部门名称
-
部门领导的员工编号
-
位置编号
-
位置编号
-
所属的编号
-
邮编
-
城市
-
州/省
-
国家编号
-
工种编号
-
工种名称
-
最低工资
-
最高公司
基础查询
# 基础查询
/*
快捷键:
F9 执行
F12 格式化
语法:
select 查询列表 from 表名;
特点:
1、查询列表可以是: 表中的字段、常量值、表达式、函数
2、查询结果是一个虚拟的表格
*/
USE myemployees;
# 1、查询单个字段
SELECT last_name FROM employees;
# 2、查询表中多个字段
SELECT last_name,salary,email FROM employees;
# 3、查询表中所有字段
SELECT * FROM employees;
# 4、查询常量值
SELECT 100;
SELECT 'john';
# 5、查询表达式
SELECT 98*97;
# 6、查询函数
SELECT VERSION();
# 7、起别名
/*
便于理解
如果要查询的字段有变量名的情况，使用别名可以区别开来
*/
# 方式一: 使用 AS
SELECT 100%98 AS 结果;
SELECT last_name AS 姓,first_name AS 名 FROM employees;
# 方式二: 使用空格
SELECT last_name 姓,first_name 名 FROM employees;
# 案例: 查询salary, 显示的结果为 out put，建议加上双引号
SELECT salary AS "out put" FROM employees;
# 8、去重
# 案例: 查询员工表中设计到的所有部门编号
SELECT DISTINCT department_id FROM employees;
# 9、+ 号的作用
/*
java 中的+号:
①运算符，两个操作数都为数值型
②连接符
mysql中:
仅有一个功能: 运算符
select 100+90; 两个操作数都为数值型，做加法运算
select '123'+90; 其中一方为字符型，视图将字符型转换为数字型，如果转换成功
做加法运行，
select 'john'+90;转换失败，则将字符型数值转换为0
select null+10; 只要其中一方为null，结果为null
*/
# 案例: 查询员工名和姓连接成一个字段，并显示成姓名
SELECT
CONCAT(last_name, first_name) AS 姓名
FROM
employees ;
条件查询
# 条件查询
/*
语法:
select 查询列表
from 表名
where 筛选条件;
分类:
一、按条件表达式筛选
条件运算符: > < = != <> >= <=
二、按逻辑表达式筛选
逻辑运算符: && || ! and or not
三、模糊查询
like between...and in is null
*/
# 一、按条件表达式筛选
# 案例1：查询工资>12000的员工信息
SELECT * FROM employees WHERE salary>12000;
# 案例2：查询部门编号不等于90的员工名和部门编号
SELECT last_name,department_id FROM employees WHERE department_id<>90;
# 二、按逻辑表达式筛选
# 案例1：查询工资z在10000到20000之间的员工名、工资及奖金
SELECT last_name,salary,commission_pct FROM employees WHEN salary>=10000 AND salary<=20000;
# 案例2：查询部门编号不是在90到110之间，或者工资高于150000的员工信息
SELECT * FROM employees WHERE (department_id<90 OR department_id>110) OR salary>15000;
# 三、模糊查询
# 1. like
# 特点: 和通配符搭配使用,
# 通配符: % 任何任意多个字符，包含0个字符, _ 任意单个字符
# 案例一：查询员工名中包含字符a的员工信息
SELECT * FROM employees WHERE last_name LIKE '%a%';
# 案例二：查询员工名中第三个字符为n，第5个字符为l的员工名和工资
SELECT last_name,salary FROM employees WHERE last_name LIKE '__n_l%';
# 案例三：查询员工名中第二个字符为_的员工名
SELECT last_name FROM employees WHERE last_name LIKE '_\_%';
# 显示指定转义字符
SELECT last_name FROM employees WHERE last_name LIKE '_$_%' ESCAPE '$';
# 2. between...and
# 特点，可以提高语句简洁性，包含临界值，两个临界值不能调换顺序
# 案例一：查询员工编号在100到120之间的员工信息
SELECT * FROM employees WHERE employee_id>=100 AND employee_id<=120;
SELECT * FROM employees WHERE employee_id BETWEEN 100 AND 120;
# 3、in 判断某字段的值是否属于in列表中的某一项
# 使用in提高语句简洁度，in 列表的值类型必须统一或兼容
# 案例一: 查询员工的工种编号是 IT_PROT、AD_VP、AD_PRES 的中的一个的员工名和工种编号
SELECT last_name,job_id FROM employees WHERE job_id IN ('IT_PROT','AD_VP','AD_PRES');
# 4、is null
# = 不能判断 NULL，IS 只能搭配 NULL
# 案例1：查询没有奖金的员工名和奖金率
SELECT last_name,commission_pct FROM employees WHERE commission_pct IS NULL;
# 安全等于: <=>
SELECT last_name,commission_pct FROM employees WHERE commission_pct <=> NULL;
# IS NULL 仅可判断NULL, <=> 都可判断，但可读性差
排序查询
# 排序查询
/*
语法:
select 查询列表
from 表
where 筛选条件
order by 排序列表 [asc|desc]
1、asc 升序，desc 降序，默认升序
*/
# 案例一: 查询员工信息，要求工资从高到低排序
SELECT * FROM employees ORDER BY salary DESC;
SELECT * FROM employees ORDER BY salary ASC;
# 案例二：查询部门编号 >= 90 的员工信息，按入职时间排序[添加筛选条件]
SELECT *
FROM employees
WHERE department_id >- 90
ORDER BY
hiredate ASC;
# 案例三: 按年薪的高低显示员工的信息和年薪 [表达式排序]
SELECT *,salary*12*(1+IFNULL(commission_pct,0)) 年薪
FROM employees
ORDER BY
年薪 DESC;
# 案例四: 按姓名的长度显示员工的姓名和工资 [按函数排序]
SELECT LENGTH(last_name) 字节长度,last_name,salary
FROM employees
ORDER BY LENGTH(last_name) DESC;
# 案例五: 查询员工信息，先按工资升序排序，再按员工编号降序排序 [按多个字段排序]
SELECT *
FROM employees
ORDER BY salary ASC, employee_id DESC;
常用函数
字符函数
# 常见函数
/*
概念: 类似于java的方法，将一组逻辑语句封装在方法体中，对外暴露方法名
好处: 1、隐藏了具体实现细节 2、提高代码的重用性
调用: select 函数名(实参列表) from 表;
特点: 1、叫什么(函数名) 2、干什么(功能)
分类: 1、单行函数
concat、length、ifnull
2、分组函数
做统计，又称为统计函数，聚合函数、组函数
*/
# 字符函数
# 1、length
SELECT LENGTH('john');
SELECT LENGTH('你好');
# 2、查看字符集
SHOW VARIABLES LIKE '%char%';
# concat 拼接字符串
SELECT CONCAT(last_name,'_',first_name) 姓名 FROM employees;
# 3、upper、lower 大小写
SELECT UPPER('john');
SELECT LOWER('joHn');
# 姓大写，名小写
SELECT CONCAT(UPPER(last_name),'_',LOWER(first_name)) 姓名 FROM employees;
# 4、substr/substring 截取字符串
SELECT SUBSTR('字符串',2) output; # 从指定位置开始截取
SELECT SUBSTR('字符串',2,1) output; # 从指定位置开始截取指定个数的字符
# 案例: 姓名中首字母大写，其他字符小写，然后用_拼接，显示出来
SELECT CONCAT(UPPER(SUBSTR(last_name,1,1)),'_',LOWER(SUBSTR(last_name,2))) output
FROM employees;
# 5、instr 返回起始索引，如果找不到返回0，注：mysql中索引从1开始
SELECT INSTR('字符串','串') AS out_put;
# 6、trim 删除前后空格或指定字符
SELECT LENGTH(TRIM(' 字符串 ')) AS out_put;
SELECT TRIM('a' FROM 'aaaaaaa字符串aaaaaaa') AS out_put;
# 7、lpad 用指定字符左填充到指定长度
SELECT LPAD('字符串',10,'*') AS out_put;
# 8、rpad 用指定字符右填充到指定长度
SELECT RPAD('字符串',10,'*') AS out_put;
# 9、replace 替换
SELECT REPLACE('字符串','字符','数字') AS out_put;
数学函数
# 数学函数
# 1、round 四舍五入
SELECT ROUND(1.65);
SELECT ROUND(1.4736,2); # 小数点后保留
# 2、ceil 向上取整，返回>=该参数的最小整数
SELECT CEIL(1.52);
# 3、floor 向下取整，返回<=该参数的最大整数
SELECT FLOOR(-9.99);
# 4、truncate 截断
SELECT TRUNCATE(1.65,1); # 小数点后保留多少位
# 5、mod 取余
SELECT MOD(10,3);
SELECT 10%3;
日期函数
# 日期函数
# 1、now 返回当前系统日期+时间
SELECT NOW();
# 2、curdate 返回当前系统日期，不包含时间
SELECT CURDATE();
# 3、curtime 返回当前时间，不包含日期
SELECT CURTIME();
# 4、year month day可以获取指定部分: 年、月、日、小时、分钟
SELECT YEAR(NOW());
SELECT YEAR('1992-2-3');
SELECT YEAR(hiredate) 年 FROM employees;
SELECT MONTH(NOW());
SELECT MONTHNAME(NOW());
SELECT DAY(NOW());
# 5、str_to_date: 将日期格式的字符串转换为指定格式的日期
SELECT STR_TO_DATE('3-3-1998','%c-%d-%Y') out_put;
# 示例: 查询入职日期为 1992-4-3 的员工信息
SELECT * FROM employees WHERE hiredate='1992-4-3';
SELECT * FROM employees WHERE hiredate=STR_TO_DATE('4-3 1992','%c-%d %Y');
# 6、date_format: 将日期转换为字符
SELECT DATE_FORMAT(NOW(),'%Y年%c月%c日') AS out_put;
# 查询有奖金的员工名和入职日期
SELECT last_name,DATE_FORMAT(hiredate,'%m月/%d日 %y年')
FROM employees
WHERE commission_pct IS NOT NULL;
其他函数
# 其他函数
SELECT VERSION();
SELECT DATABASE();
SELECT USER();
流程控制函数
# 流程控制函数
# 1、if 函数
SELECT IF(10>5,'大','小');
SELECT last_name,commission_pct,IF(commission_pct IS NULL,'没奖金','有奖金')
FROM employees;
# 2、case 函数的使用一: switch case 的效果
/*
case 要判断的字段或表达式
when 常量1 then 要显示的值或语句;
....
else 要现实的值的默认情况
end
*/
# 案例: 查询员工的工资
# 部门号=30，显示的工资为1.1倍
# 部门号=40，显示的工资为1.2倍
# 部门号=40，显示的工资为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;
# 3、case 函数的使用二: 类似多重if
/*
如果工资>20000,显示A级别
如何工资>15000,显示B级别
如果工资>10000,显示C级别
否则显示D级别
*/
SELECT salary,
CASE
WHEN salary>20000 THEN 'A'
WHEN salary>15000 THEN 'B'
WHEN salary>10000 THEN 'C'
ELSE 'D'
END
FROM employees;
分组函数
# 分组函数
/*
功能，用于统计使用，又称为聚合函数或者统计函数或组合函数
分组:
sum 求和、avg 平均值、max 最大值、min 最小值、count 计算个数
*/
# 1、简单使用
SELECT SUM(salary) FROM employees;
SELECT AVG(salary) FROM employees;
SELECT MAX(salary) FROM employees;
SELECT MIN(salary) FROM employees;
SELECT COUNT(salary) FROM employees;
# 2、参数支持那些类型
# 一般用于数值型
SELECT SUM(last_name),AVG(last_name) FROM employees;
SELECT SUM(hiredate),AVG(hiredate) FROM employees;
# 支持字符型、日期型、数值型
SELECT MAX(last_name),MIN(last_name) FROM employees;
# count() 统计非空的个数，支持任何类型
# 3、是否忽略null值
# sum、avg、max、min、count 都忽略null值
SELECT SUM(commission_pct),AVG(commission_pct) FROM employees;
# 4、可以和distinct搭配实现去重运算
SELECT SUM(DISTINCT salary),SUM(salary) FROM employees;
# 5、count 函数的单独介绍
SELECT COUNT(salary) FROM employees;
# 用来统计行数
SELECT COUNT(*) FROM employees;
# 加了一列1，统计1的个数
SELECT COUNT(1) FROM employees;
# 效率 INNODB 存储引擎下,count(*) 效率低 MYSIAM count(*)效率高
# 6、和分组函数一同查询的字段有限制，要求是group by 后的字段
SELECT AVG(salary),employee_id FROM employees;
分组查询
# 连接查询
/*
在网盘下载文件中找到并导入girls.sql
含义: 又称多表查询，当查询的字段来自多个表时，就会用到连接查询
笛卡尔乘积现象：表1 有m行 表2 有n行 结果m*n行
发生原因：没有有效的连接条件
如何避免：添加有效的连接条件
分类:
按年代分类
sql92标准: 仅仅支持内连接
sql99标准[推荐]: 支持内连接+外连接+交叉连接
按功能分类
内连接
等值连接 非等值连接 自连接
外连接
左外连接 右外连接 全连接
交叉连接
*/
SELECT * FROM beauty;
SELECT * FROM boys;
SELECT `name`,boyName FROM beauty,boys
WHERE beauty.boyfriend_id = boys.id;
# 一、sql92标准
# 1、等值连接
# 案例1：查询女神名和对应的男神名
SELECT `name`,boyName
FROM boys,beauty
WHERE beauty.`boyfriend_id` = boys.`id`
# 案例2：查询员工名和对应的部门名
SELECT last_name,department_name
FROM employees, departments
WHERE employees.`department_id`=departments.`department_id`
# 2、为表起别名，查询员工名、工种号、工种名
/*
①提高语句的简洁度
②区分多个重名的字段
注意：如果为表起别名，则查询的字段就不能使用原来的表名限定
*/
SELECT last_name,e.job_id,job_title
FROM employees e,jobs
WHERE e.`job_id`=jobs.`job_id`
# 3、可以加筛选
# 案例：可以加有奖金的员工名，部门名
SELECT last_name,department_name
FROM employees e,departments d
WHERE e.department_id=d.department_id
AND e.commission_pct IS NOT NULL;
# 案例2：查询城市名中第二个字符为o的对应部门名和城市名
SELECT department_name,city
FROM departments d,locations l
WHERE d.`location_id`=l.`location_id`
AND city LIKE '_o%';
# 4、可以加分组
# 案例1：查询每个城市的部门个数
SELECT COUNT(*) 个数,city
FROM departments d,locations l
WHERE d.`location_id`=l.`location_id`
GROUP BY city;
# 案例2：查询有奖金的每个部门的部门名称和该部门的最低工资
SELECT department_name,d.`manager_id`,MIN(salary)
FROM departments d,employees e
WHERE d.`department_id`=e.`department_id`
AND commission_pct IS NOT NULL
GROUP BY department_name,d.`manager_id`;
# 5、可以加排序
# 案例: 查询每个工种的工种名和员工的个数，并且按照员工个数排序
SELECT job_title,COUNT(*)
FROM employees e,jobs j
WHERE e.`job_id`=j.`job_id`
GROUP BY job_title
ORDER BY COUNT(*) DESC;
# 6、可以实现三表连接
SELECT last_name,department_name,city
FROM employees e,departments d,locations l
WHERE e.`department_id`=d.`department_id`
AND d.`location_id`=l.`location_id`
AND city LIKE 's%'
ORDER BY department_name DESC;
# 7、非等值查询
SELECT salary,employee_id FROM employees;
SELECT * FROM job_grades;
CREATE TABLE job_grades
(grade_level VARCHAR(3),
lowest_sal INT,
highest_sal INT);
INSERT INTO job_grades
VALUES ('A', 1000, 2999);
INSERT INTO job_grades
VALUES ('B', 3000, 5999);
INSERT INTO job_grades
VALUES('C', 6000, 9999);
INSERT INTO job_grades
VALUES('D', 10000, 14999);
INSERT INTO job_grades
VALUES('E', 15000, 24999);
INSERT INTO job_grades
VALUES('F', 25000, 40000);
# 案例1：查询员工的工资和工资级别
SELECT salary,grade_level
FROM employees e,job_grades g
WHERE salary BETWEEN g.lowest_sal AND g.highest_sal
AND g.grade_level = 'A';
# 8、自连接
# 案例：查询员工名和上级的名称
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`;
# 二、sql99标准
/*
语法
select 查询列表
from 表1 别名 [连接类型]
join 表2 别名
on 连接条件
[where 筛选条件]
[group by 分组]
[having 筛选条件]
[order by 排序列表]
分类:
内连接: inner
外连接:
左外: left[outer]
右外: right[outer]
全外: full[outer]
交叉连接
*/
# ① 内连接
/*
语法:
select 查询列表
from 表1 别名
inner join 表2 别名
on 连接条件
分类:
等值
非等值
自连接
特点
1、可添加排序、分组、筛选
2、inner 可以省略
3、筛选条件放在where 后，连接条件放在on后
*/
# 1、等值连接
# 案例1 查询员工名，部门名
SELECT last_name,department_name FROM departments d
INNER JOIN employees e
ON e.`department_id` = d.`department_id`;
# 案例2 查询名字中包含e的员工名和工种名(添加筛选)
SELECT last_name,job_title FROM employees e
INNER JOIN jobs j ON e.`job_id`=j.`job_id`
WHERE e.`last_name` LIKE '%e%';
# 案例3 查询部门个数>3的城市名和部门个数(添加分组+筛选)
SELECT city,COUNT(*) 部门个数
FROM departments d
INNER JOIN locations l
ON d.`location_id`=l.`location_id`
GROUP BY city
HAVING COUNT(*)>3;
# 2、非等值连接
# 查询员工的工资级别
SELECT salary,grade_level
FROM employees e
JOIN job_grades g
ON e.`salary` BETWEEN g.`lowest_sal` AND g.`highest_sal`;
# 3、自连接
# 查询员工名字、上级的名字
SELECT e.last_name,m.last_name
FROM employees e
JOIN employees m
ON e.`manager_id`=m.`employee_id`;
# ② 外连接
/*
应用场景：用于查询一个表中有，另一个表中没有的基类
特点:
1、外连接的查询结果为主表中的所有记录
如果从表中有和它匹配的，则显示匹配的值
如果从表中没有和它匹配的，则显示null
外连接查询结果=内连接查询结果+主表中有而从表中没有的记录
2、左外连接, left join 左边是主表
右外连接，right join 右边是主表
3、左外和右外交换两个表的顺序，可以实现同样的效果
4、全外连接=内连接的结果+表1中有而表2中没有的+表1中没有而表2中有的
*/
# 案例1 查询哪个部门没有员工
# 左外
SELECT d.*,e.employee_id
FROM departments d
LEFT OUTER JOIN employees e
ON d.`department_id` = e.`department_id`
WHERE e.`employee_id` IS NULL;
# 右外
SELECT d.*,e.employee_id
FROM employees e
RIGHT OUTER JOIN departments d
ON d.`department_id` = e.`department_id`
WHERE e.`employee_id` IS NULL;
# 交叉连接 (笛卡尔积)
SELECT d.*,e.employee_id
FROM employees e
CROSS JOIN departments d;
子查询
出现在其他语句中的select 语句，称为子查询。
# 一、where 或 having 后面
/*
1、标量子查询(单行子查询)
2、列子查询(多行子查询)
3、行子查询(多列多行)
特点:
① 子查询放在小括号内
② 子查询一般放在条件的右侧
③ 标量子查询，一般搭配单行操作符
④ 子查询的优先级高于主查询，主查询用到了子查询的结果
*/
# 1、标量子查询
# 案例1：谁的工资比 Abel 高?
# ① 查询Abel的工资
SELECT salary
FROM employees
WHERE last_name='Abel';
# ② 查询员工的信息，满足salary>①结果
SELECT *
FROM employees
WHERE salary>(
SELECT salary
FROM employees
WHERE last_name='Abel'
);
# 2、列子查询
# 案例1 返回location_id是1400或1700的部门中所有员工的姓名
# ①查询location_id是1400或1700的部门编号
SELECT DISTINCT department_id
FROM departments
WHERE location_id IN(1400,1700);
# ②查询员工姓名，要求部门号是①列表中的某一个
SELECT last_name
FROM employees
WHERE department_id IN(
SELECT DISTINCT department_id
FROM departments
WHERE location_id IN(1400,1700)
);
# 3、行子查询(结果集一行多列或多行多列)
# 案例: 查询员工编号最小并且工资最高的员工信息
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;
# 三、from 后面
# 案例：查询每个部门的平均工资的工资等级
# ① 查询每个部门的平均工资
SELECT AVG(salary),department_id
FROM employees GROUP BY department_id;
SELECT * FROM job_grades;
# ② 连接①的结果集合job_grades表，筛选条件平均工资between lowest_sal and highest_sal
SELECT ag_dep.*,g.grade_level
FROM(
SELECT AVG(salary) ag,department_id
FROM employees
GROUP BY department_id
) ag_dep
INNER JOIN job_grades g
ON ag_dep.ag BETWEEN lowest_sal AND highest_sal;
# 四、exists后面(相关子查询)
/*
语法:
exists(完整的查询语句)
结果:
1或0
*/
# 查询有员工的部门名
SELECT department_name
FROM departments d
WHERE EXISTS(
SELECT *
FROM employees e
WHERE d.`department_id`=e.`department_id`
);
分页查询
应用场景：当要显示的数据，一页显示不全，需要分页提交sql请求。
# 分页查询
/*
语法
select 查询列表
from 表
join type join 表2
on 连接条件
where 筛选条件
group by 分组字段
having 分组后筛选
order by 排序的字段
limit by 排序的字段
limit [offset,] size
offset 要显示条目的起始索引(从0开始)
size 要显示的条目个数
*/
# 案例: 查询前5条员工信息
SELECT * FROM employees LIMIT 0,5;
SELECT * FROM employees LIMIT 5;
联合查询
# 联合查询
# 含义: union(联合、合并)：将多条查询语句的结果合并成一个结果
/*
语法
查询语句1
union [all]
查询语句2
union [all]
特点:
1、要求多条查询语句的查询列数是一致的
2、要求多条查询语句的查询的每一列的类型和顺序最好一致
3、union 关键字默认去重，如果使用union all 可以包含重复项
*/
# 查询部门编号>90 或邮箱包含a的员工信息
SELECT * FROM employees WHERE email LIKE '%a%'
UNION
SELECT * FROM employees WHERE department_id>90;
浙公网安备 33010602011771号