MySQL学习笔记-查询

基础查询
1.1 条件查询
1.2 模糊查询
1.3 排序查询
分组查询
2.1 分组函数
2.2 分组查询
连接查询
3.1 多表连接(等值连接)
3.2 非等值查询
3.3 join连接
3.3.1 内连接
3.3.2 外连接
3.3.3 全外连接和自连接
3.4 子查询
3.4.1 单行子查询
3.4.2 多行子查询
分页查询

1. 基础查询

查询/显示查询语句中的内容。
语法：

select 查询语句;

查询语句可以是：

常量
表达式
函数
字段(如果要查询字段，则必须添加from 表名)

(1) 显示常量

select 常量;
#eg:
select 100;

(2) 显式表达式

select 表达式;
#eg:
select 12*34;

(3) 显示函数(方法)

#无参：
select 函数名();
#eg:
select version();
#有参：
select 函数名(参数);
#eg:
select length('john');

(4) 显示表中的字段

select 字段名 from 表名;
#eg:
select last_name from employees;

(5) 显示表中的多个字段

select 字段1,字段2,字段3,…… from 表名;
#eg:
select last_name,phone_number,first_name from employees;

(6) 显示表中所有字段

select * from 表名;
#eg:
select * from employees;

(7) 起别名

#方式一：
select 字段1 as 别名1,字段2 as 别名2,…… from 表名;
#方式二：
select 字段1 别名1,字段2 别名2 from 表名;
#eg:
select last_name as 姓 from employees;

(8) 去重

select distinct 字段名 from 表名;
#eg:
select distinct department_id from employees;

1.1 条件查询

根据指定的条件过滤出数据。
语法：

select 字段名 from 表名
where 条件;

分类：

按条件表达式查询
条件运算符：

> 、< 、>=、 <=、 =、 !=、 <>、<=>安全等于，用于判断是否为null
#eg:
#案例1：查询月薪>5000的员工信息
SELECT * FROM employees
 WHERE salary>5000;

#案例2：查询月薪=12000的员工信息
SELECT * FROM employees
 WHERE salary=12000;

#案例  查询月薪不等于12000的员工信息
SELECT * FROM employees
 WHERE salary<>12000;

#案例3：查询没有奖金的员工名
SELECT last_name,commission_pct FROM employees
 WHERE commission_pct<=>NULL;

按逻辑表达式查询
逻辑运算符：

and、&&  两个条件都为true，结果为true，反之为false
or、||   两个条件只要有一个为true，结果为true，反之为false
not、!   如果连接的条件本身为false，结果为true，反之为false
#eg:
#案例1：查询月薪在5000到12000的员工工资和姓名
SELECT salary,last_name FROM employees
 WHERE salary>= 5000 AND salary<=12000;

#案例2：查询月薪不在5000到12000的员工工资和姓名
SELECT salary,last_name FROM employees
 WHERE NOT(salary>= 5000 AND salary<=12000);

#案例3：查询部门编号=90 或 月薪>10000并且月薪<15000的员工信息
SELECT * FROM employees
 WHERE department_id = 90 OR (salary>10000 AND salary<15000;

模糊查询

1.2 模糊查询

(1) like

一般搭配着通配符使用，用作字符型的判断
通配符：

% 任意多个字符  
_ 任意单个字符

#案例1：查询姓名中包含字符 e的员工信息
SELECT * FROM employees
 WHERE last_name LIKE '%e%';

#案例2：查询姓名中第二个字符为e，第四个字符为a的员工信息
SELECT * FROM employees
 WHERE last_name LIKE '_e_a%';

#案例3：查询姓名中第三个字符为_的员工信息
SELECT * FROM employees
 WHERE last_name LIKE '__\_%';

#自定义转义符
#语法：ESCAPE 自定义转义符
SELECT * FROM employees
 WHERE last_name LIKE '__$_%' ESCAPE '$';
#ESCAPE '$';  这句话是 让$ 拥有转义字符的形式和功能

(2) between and

在……之间
特点：

等价于：字段>=值1 and 字段<=值2
取值包含两个临界值 [值1, 值2]
两个临界值顺序不能颠倒

#案例：显示出表employees部门编号在80-100之间 的姓名、职位
SELECT last_name,job_id,department_id
 FROM employees
 WHERE department_id BETWEEN 80 AND 100;
#等价于
SELECT last_name,job_id,department_id
 FROM employees
 WHERE department_id<=100 AND department_id>=80;

(3) in

判断某个字段的值是否在某个列表内
语法：

in(值1, 值2, ……)

等价于：

值1 or 值2 or ……

#案例1：显示出表employees的manager_id 是 100,101,110 的员工姓名、职位
SELECT last_name,job_id,manager_id
 FROM employees
 WHERE manager_id IN(100,101,110);

#等价于
SELECT last_name,job_id,manager_id
 FROM employees
 WHERE manager_id=100 OR manager_id=101 OR manager_id = 110;

#案例2：查询 job_id 为AD_VP或ST_MAN或SA_MAN
SELECT last_name,job_id,manager_id
 FROM employees
 WHERE job_id IN('AD_VP','S_\_MAN');

(4) is null/is not null

判断某个字段的值是否为空

#案例1：查询没有奖金的员工
SELECT * FROM employees
 WHERE commission_pct IS NULL;

#案例2：查询有奖金的员工
SELECT * FROM employees
 WHERE commission_pct  IS NOT  NULL;

SELECT * FROM employees
 WHERE NOT(commission_pct  IS   NULL);

1.3 排序查询

语法：

select 字段|表达式|常量
 from 表名
 (where 条件)
 order by 排序字段;

#升序 asc 默认
#降序 desc

排序查询可以按照表达式、函数、别名、多个字段进行排序。
在不使用分页的情况下，排序语句一般放在查询语句的最后。

(1) 按单个字段进行排序

SELECT *
 FROM employees
 ORDER BY salary ;

SELECT *
 FROM employees
 ORDER BY salary DESC;

(2) 按多个字段进行排序

SELECT *
 FROM employees
 ORDER BY salary DESC,employee_id ASC;

(3) 按表达式排序

#案例：按年薪降序
SELECT *,salary*12*(1+IFNULL(commission_pct,0)) 年薪
 FROM employees
 ORDER BY salary*12*(1+IFNULL(commission_pct,0)) DESC;

(4) 按别名排序

#案例：按年薪降序
SELECT *,salary*12*(1+IFNULL(commission_pct,0)) 年薪
 FROM employees
 ORDER BY 年薪 DESC;

(5) 按函数排序

#案例：按姓名中的字节长度大小降序
SELECT last_name,LENGTH(last_name)
 FROM employees
 ORDER BY LENGTH(last_name) DESC;

2. 分组查询

2.1 分组函数

分组函数，又称为统计函数或聚合函数。
分组函数与其他函数的区别：
分组函数传入一组值，最后只返回一个值，而其他函数不同。
例如：
length('john')传入了一个值，返回了一个值，不是分组函数。
max(salary)传入了一列值，返回了一个最大值，是分组函数。

常用分组函数及其特点：

最大值：max()
支持任何类型。
忽略null值。
最小值：min()
支持任何类型。
忽略null值。
求和：sum()
只支持数值类型。
忽略null值。
平均数：avg()
只支持数值类型。
忽略null值。
非空个数：count()
支持任何类型。
忽略null值。

#一、查询分组函数
SELECT MAX(salary) 最大值 FROM employees;
SELECT MIN(salary) 最小值 FROM employees;
SELECT SUM(salary) 和 FROM employees;
SELECT AVG(salary) 平均值 FROM employees;
SELECT COUNT(salary) 个数 FROM employees;
SELECT MAX(salary) 最大值,SUM(salary) 和 FROM employees;


#二、关于sum 和avg
/*
1.只支持数值型
2.sum和avg都忽略null值
*/
SELECT AVG(last_name) FROM employees;
SELECT SUM(last_name) FROM employees;

SELECT * FROM employees;
SELECT SUM(commission_pct)/107,AVG(commission_pct) FROM employees;


#三、关于max和min
/*
1.支持任何类型
2.max和min都忽略null值
*/
SELECT MAX(last_name),MIN(last_name) FROM employees;  //判断类型
SELECT MAX(commission_pct),MIN(commission_pct) FROM employees;  //判断是否忽略null

#四、关于count
SELECT COUNT(commission_pct) FROM employees;  //计算非空的值，忽略null
//查询员工表占了几个部门
SELECT COUNT(DISTINCT department_id) FROM employees;

#查询结果集中的行数
count(1)的性能没有count（*）高,
SQL服务器对count(*)进行了优化，所以效率高。

2.2 分组查询

分组查询就是搭配分组函数使用的查询。
语法：

select 分组函数 别名
 from 表名
 (where 分组前筛选)
 group by 分组的字段
 (having 分组后筛选)
 (order by 排序的字段)
#不可颠倒顺序

特点：

按多个字段分组，字段间用英文逗号分隔开，没有顺序要求。
可以和分组函数一起查询的字段，只能是分组后的字段，不能是任意字段。
分组筛选

阶段	针对的表	位置	关键字
分组前筛选	原始表	group by的前面	where
分组后筛选	结果集	group by的后面	having

having 和 group by 支持别名

#一、普通的分组查询
#01 案例：查询各部门的最高工资和部门号
SELECT MAX(salary),department_id FROM employees GROUP BY department_id;

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

#三、分组查询+筛选having
#案例1：查询有奖金的，每个部门的最高奖金率
SELECT MAX(commission_pct) 最高奖金率,department_id
 FROM employees
 WHERE commission_pct IS NOT NULL
 GROUP BY department_id;

#案例2：查询员工姓名中包含字符a，每个部门的最低工资高于3000的部门编号
SELECT department_id,MIN(salary) 最低工资
 FROM employees
 WHERE last_name LIKE '%a%'  
 GROUP BY department_id
 HAVING MIN(salary)>3000;

#案例3：查询电话以“110”开头的，工种号包含字符'T'的,每个工种的平均工资>5000的工种号和平均工资
SELECT job_id,AVG(salary)
 FROM employees
 WHERE phone_number LIKE '110%' AND job_id LIKE '%T%'
 GROUP BY job_id
 HAVING AVG(salary)>5000;

#案例4：查询每个地区的部门个数，求个数大于2的部门个数和地区号
SELECT COUNT(*) 个数,location_id
 FROM departments
 GROUP BY location_id
 HAVING COUNT(*)>2;

#四、having子句支持别名
#案例：查询每个地区的部门个数，求个数大于2的部门个数和地区号
SELECT COUNT(*) 个数,location_id 
 FROM departments
 GROUP BY location_id
 HAVING 个数>2;

#五、排序 order by 支持别名
#案例：查询员工姓名中包含字符a，每个部门的最低工资高于3000的部门编号
,按照最低工资降序排序
SELECT department_id,MIN(salary) 最低工资
 FROM employees
 WHERE last_name LIKE '%a%'  
 GROUP BY department_id
 HAVING MIN(salary)>3000
 ORDER BY 最低工资 DESC;

3. 连接查询

3.1 多表连接(等值连接)

从多个表中查询多个字段。
语法：

select 字段1, 字段2
 from 表1, 表2
 (where 连接条件)
*
笛卡尔乘积：
    产生原因：没有加连接条件，导致结果为 表1 的行数*表2 的行数
    解决方法：添加上有效的连接条件
*

分类：

传统模式的多表连接
SQL99推出的标准，使用join关键字实现链接
自连接

(1) 传统模式的多表连接

也叫等值连接。
特点：

表的顺序没有要求
n表连接，至少需要n-1个连接条件
一般需要为表起别名，这样可以提高语句简洁度，并且防止字段有歧义，提高可读性
可以添加分组、排序、筛选混合使用

#案例：查询员工名、部门名
SELECT `last_name`,`department_name`
 FROM `employees`,`departments`
 WHERE `employees`.`department_id`=`departments`.`department_id`;
#为表起别名
/*
一般需要为表起别名，好处：
a. 提高语句简洁度
b. 防止字段有歧义，比如说 两张表中都有name字段，使用b.name 或者a.name 这样表示就没有歧义 
c. 提高效率
如果已经为表起别名，则使用字段时，只能用别名限定而不能用表名限定
*/
SELECT e.`last_name`,d.`department_name`
 FROM `employees`  e,`departments` d
 WHERE e.`department_id`=d.`department_id`;

#添加筛选条件
#案例：查询 工资>5000的工种名和员工名、工资
SELECT job_title,last_name,salary
 FROM employees e,jobs j
 WHERE e.`job_id`=j.`job_id`
 AND salary>5000;

#添加分组和筛选
#案例：查询每个部门的员工个数和部门名
SELECT COUNT(*) 个数,department_name
 FROM employees e,departments d
 WHERE e.`department_id`=d.`department_id`
 GROUP BY e.`department_id`
 HAVING 个数>5;

#排序
#案例：查询每个部门的员工个数和部门名
SELECT COUNT(*) 个数,department_name
 FROM employees e,departments d
 WHERE e.`department_id`=d.`department_id`
 GROUP BY e.`department_id`
 HAVING 个数>5
 ORDER BY 个数 DESC;

#三表连接
#案例：查询员工名、部门名和所在城市
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`;

3.2 非等值查询

#案例1：查询员工的工资以及对应的工资级别
SELECT salary,grade_level
 FROM employees e,job_grades g
 WHERE e.`salary` BETWEEN g.`lowest_sal` AND g.`highest_sal`;

#案例2：查询名字中第三个字符为a，第五个字符为e的员工的工资以及对应的工资级别
SELECT salary,grade_level
 FROM employees e,job_grades g
 WHERE e.`salary` BETWEEN g.`lowest_sal` AND g.`highest_sal`
 AND e.`last_name` LIKE '__a_e%';

3.3 join连接

join关键字属于sql99语法的标准，1999推出了sql的一种新标准，使用到的关键字，叫做join。
分类：

内连接 [inner] join on
外连接
- 左外连接 left [outer] join on
- 右外连接 right [outer] join on
- 全外连接 full [outer] join on(MySQL中不支持全外连接，SQL server和Oracle支持)

3.3.1 内连接

语法：

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

特点：

效果和等值连接是一样的！
表的顺序可以调换
可以为表起别名
可以添加筛选、分组、排序
将筛选条件和连接条件实现了分类，提高代码的可读性
n表连接，至少需要n-1 个连接条件

#案例1 ：查询员工名、部门名
SELECT last_name,department_name
 FROM departments d
 JOIN employees e ON e.`department_id`=d.`department_id`;

#案例2：查询有奖金的员工名、部门名
SELECT last_name,department_name
 FROM departments d
 JOIN employees e ON e.`department_id`=d.`department_id`
 WHERE commission_pct IS NOT NULL;

#案例3：查询城市名、员工名和部门名
SELECT city,last_name,department_name
 FROM employees e
 INNER JOIN departments d ON e.`department_id`=d.`department_id`
 INNER JOIN locations l ON l.`location_id`=d.`location_id`;

3.3.2 外连接

语法：

select 字段1, 字段2, ……
 from 表1
 left|right [outer] join 表2
 on 连接条件;

特点：

查询结果：内连接的结果+主表中有但从表没有的记录（从表的字段用null填充）
左连接，左边的就是主表；右连接，右边的就是主表
一般来讲要查询的字段来自于哪个表，那哪个表就是主表
一般用于查询主表中有但从表中没有的记录

#案例1：查询没有男朋友的女神名称
USE girls;

SELECT `name`,boyName
 FROM beauty b
 LEFT OUTER JOIN boys bo
 ON b.`boyfriend_id`=bo.`id`
 WHERE bo.id IS NULL;

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

#案例3：查询哪个工种没有员工
SELECT j.job_id,e.`employee_id`
 FROM employees e
 RIGHT OUTER JOIN jobs j ON e.`job_id`=j.`job_id`
 WHERE  e.`job_id` IS NULL;

3.3.3 全外连接和自连接

语法：

select 字段1, 字段2, ……
 from 表1
 full outer join 表2
 on 连接条件

#全外连接
SELECT j.job_id,e.`employee_id`
 FROM employees e
 FULL OUTER JOIN jobs j ON e.`job_id`=j.`job_id`

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

3.4 子查询

嵌套在另一个查询中的查询语句称为子查询，外部的查询称为主查询。
语法：

select 字段1, 字段2, ……
 from 表1
 where 字段 in(
     select 字段1 from 表2
)

注意事项：

子查询放在小括号内
放在条件右侧
子查询优先于主查询执行

分类：

单行子查询：子查询的结果只有一个值，使用单行操作符(><>=<==<>)
多行子查询：子查询的结果是一个数据集，使用多行操作符(any、all、in、not in)

3.4.1 单行子查询

#案例1：谁的工资比Abel高
	#①查询Abel的工资
	SELECT salary FROM employees 
	WHERE last_name = 'Abel'
	
	#②查询员工的信息满足工资>①的结果
	SELECT * FROM employees
	WHERE salary>(
		SELECT salary FROM employees 
		WHERE last_name = 'Abel'
	);

#案例2：题目：返回job_id与141号员工相同，salary比143号员工多的员工 的姓名，job_id 和工资
	#①查询141的job_id
	SELECT job_id FROM employees 
	WHERE employee_id=141
	
	#②查询143的salary
	SELECT salary FROM employees
	WHERE employee_id=143        
	
	#③查询  姓名，job_id 和工资，满足job_id=①并且salary>②
	SELECT last_name,job_id,salary
	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 MIN(salary) FROM employees;

	#②查询员工的last_name,job_id和salary满足 salary=①
	SELECT last_name,job_id,salary
	FROM employees
	WHERE salary=(
		SELECT MIN(salary) FROM employees
	)
	
#案例4：查询最低工资大于50号部门最低工资的部门id和其最低工资
	#①查询50号部门的最低工资
	SELECT MIN(salary)
	FROM employees
	WHERE department_id=50

	#②查询每个部门的最低工资
	SELECT department_id,MIN(salary)
	FROM employees
	GROUP BY department_id

	#③筛选最低工资>①
	SELECT department_id,MIN(salary)
	FROM employees
	GROUP BY department_id
	HAVING MIN(salary)>(
		SELECT MIN(salary)
		FROM employees
		WHERE department_id=50
	);

3.4.2 多行子查询

#案例1：返回location_id是1400或1700的部门中的所有员工姓名
	#①查询location_id是1400或1700的部门编号
	SELECT department_id FROM departments
	WHERE location_id IN(1400,1700)

	#②查询department_id满足①结果的员工姓名
	SELECT last_name FROM employees
	WHERE department_id IN(
		SELECT department_id FROM departments
		WHERE location_id IN(1400,1700)
	)
  
#案例2：返回其它部门中比job_id为‘IT_PROG’部门任意工资低的员工的员工号、姓名、job_id 以及salary

	#①查询job_id为‘IT_PROG’部门工资
	SELECT salary
	FROM employees
	WHERE job_id = 'IT_PROG'

	#②返回其它部门中，工资<any ①的结果
	SELECT  last_name,employee_id,job_id,salary
	FROM employees 
	WHERE salary<(
		SELECT MAX(salary)
		FROM employees
		WHERE job_id = 'IT_PROG'
	)AND job_id<>'IT_PROG';

#题目：返回其它部门中比job_id为‘IT_PROG’部门所有工资都低的员工的员工号、姓名、job_id 以及salary
	SELECT  last_name,employee_id,job_id,salary
	FROM employees 
	WHERE salary<ALL(
		SELECT salary
		FROM employees
		WHERE job_id = 'IT_PROG'
	)AND job_id<>'IT_PROG';

4. 分页查询

语法：

select 查询语句
 from 表
 [where 条件]
 [group by 字段]
 [having 条件]
 [order by 排序字段]
 limit 条目起始索引 [条目数]

特点：

写法

写法一：

LIMIT 条目数
#意思：从第一条开始，显示指定条目数的数据

写法二：

LIMIT 起始条目索引，条目数
#意思：从指定的起始索引的条目开始，显示指定条目数的数据

起始索引从0开始

#案例1：查询员工表的中前五行
SELECT * FROM employees LIMIT 10;

#案例2：查询 员工表 的第11条到第20条
SELECT * FROM employees LIMIT 10,10;

#案例3：查询员工表的第15条到30条
SELECT * FROM employees LIMIT 14,16;