MySQL数据库基础查询

概述

一、数据库的好处
1、可以持久化数据到本地
2、结构化查询
二、数据库存储数据的特点
1、数据存放到表中，表再放到库中
2、一个库可以由多张表，每张表具有唯一的表名
3、表中有一个或多个“字段”
三、MySQL优点
1、开源，免费，成本低
2、性能高，移植性好
3、体积小，便于安装

（一）条件查询

模糊查询

like:

①一般和通配符搭配使用
通配符：
% 任意多个字符，包含0个
_ 任意单个字符

#案例1：查询员工名中包含字符a的员工信息
SELECT
	*
FROM
	employees
WHERE
	last_name LIKE '%a%';
#案例2：查询员工名中第3个字符为n，第五个字符为l的员工名和工资
SELECT
	last_name,
	salary
FROM
	employees
WHERE
	last_name LIKE '__n_l%';
#案例3：查询员工名中第二个字符为_的员工名（转义字符）	
SELECT
	last_name
FROM 
	employees
WHERE 	 
	last_name LIKE '_\_%';

between and

#案例1：查询员工编号在100到120之间的员工信息
SELECT
	*
FROM
	employees
WHERE
	employee_id BETWEEN 100 AND 120;

in

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

is null(is not null)

#=或者<>不能用于判断null值
#案例1：查询没有奖金的员工名和奖金率
SELECT
	last_name,
	commission_pct
FROM
	employees
WHERE
	commission_pct IS NULL;
#安全等于  <=>  is null可以换成<=> null
#案例1：查询没有奖金的员工名和奖金率
SELECT
	last_name,
	commission_pct
FROM
	employees
WHERE
	commission_pct <=> NULL;

（二）排序查询

引入：
select * from employees;
语法：
SELECT 查询列表
from 表
[where 筛选条件]
order by 排序列表 [asc|desc]
特点：
1、asc代表升序，desc代表降序，默认不写是升序
2、order by子句中可以支持单个字段、多个字段、表达式、函数、别名
3、order by子句一般是放在查询语句的最后面，limit子句除外

（三）常见函数

字符函数

length

concat

upper,lower

substr

instr返回子串的起始索引

trim去掉首尾的

replace替换

数学函数

round四舍五入

ceil向上取整

floor 向下取整

truncate 截断

mod 取余

日期函数

now:返回当前系统日期加时间

curdate 返回当前系统日期，不包含时间

curtime() 返回当前时间，不包含日期

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

date_format 将日期转换成字符

（四）流程控制函数

if函数

case函数的使用一

case函数的使用二

（五）分组函数

功能：用作统计使用，又称为聚合函数或统计函数或组函数
分类：
sum 求和、avg平均值、max、min、count计算个数
特点
1、sum，avg用于处理数值型
max，min，count可以处于任何类型
2、以上分组函数都忽略null值
3、可以和distinct搭配实现去重
4、count函数的单独介绍
5、和分组函数一同查询的字段要求是group by后的字段

（六）分组查询

语法：
select 分组函数（max，min这些），列（要求出现在group by后面）
from 表
【where 筛选条件】
group by 分组的列表
【order by】子句
注意： 查询列表必须特殊，要求是分组函数和group by后出现的字段
特点：
1、分组查询中的筛选条件分为两类（筛选的数据源不同
数据源 位置 关键字
分组前筛选： 原始表 group by子句前面 where
分组后筛选：分组后的结果集 group by子句后面 having
①分组函数做条件，肯定是放在having子句中
②能用分组前筛选的，优先考虑用分组前筛选
2、group by子句支持单个字段分组，多个字段分组，
（多个字段之间用逗号隔开无顺序要求），表达式或函数（用的较少）
3、可添加排序（放在group by后）

添加分组后的筛选条件

#案例1：查询哪个部门的员工个数大于2
#①查询每个部门的员工个数
SELECT COUNT(*),department_id
FROM employees
GROUP BY department_id;
#②根据1的结果进行筛选
SELECT COUNT(*),department_id
FROM employees
GROUP BY department_id
HAVING COUNT(*)>2;
#案例3：查询领导编号>102的每个领导手下的最低工资>5000的领导编号
#①查询领导编号>102的每个领导手下的最低工资
SELECT MIN(salary),manager_id
FROM employees
WHERE manager_id>102
GROUP BY manager_id;
#②在1的基础上，最低工资>5000
SELECT MIN(salary),manager_id
FROM employees
WHERE manager_id>102
GROUP BY manager_id
HAVING MIN(salary)>5000;

（七）连接查询

按功能分类：
内连接：等值连接，非等值连接，自连接
外连接：左外连接，右外连接，全外连接
交叉连接

内连接：inner

语法：
select 查询列表
from 表1 别名
inner join 表2 别名
on 连接条件；
分类：等值、非等值、自连接
特点：
①添加排序、分组、筛选
②inner可以省略
③筛选条件放在where后面，连接条件放在on后面，提高分离性，便于阅读
④inner join连接和sql92语法中的等值连接效果一样，都是查询多表的交集

等值连接

非等值连接

#查询员工的工资级别
SELECT salary,grade_level
FROM employees e
INNER JOIN job_grades g
ON e.`salary` BETWEEN g.`lowest_sal` AND g.`highest_sal`;

自连接

SELECT e.last_name,m.last_name
FROM employees e
JOIN employees m
ON e.`manager_id`=m.`employee_id`;

外连接

应用场景：用于查找一个表中有，另一个表中没有的记录
特点：
1、外连接的查询结果为主表中所有的记录
如果从表中有和它匹配的，则显示匹配的值
如果从表中没有和它匹配的，则显示null
外连接查询结果=内连接结果+主表中有而从表中没有的记录
2、左外连接：left jon左边的是主表
右外连接：right join右边的是主表
3、左外和右外交换两个表的顺序，可以实现同样的效果
4、全外连接=内连接结果+表1有但表2没有+表2有但表1没有

#案例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语句，称为子查询或内查询
外部的查询语句，称为主查询或外查询
分类：
按子查询出现的位置：
select后面：仅仅支持标量子查询
from后面：支持表子查询
☆ where或者having后面：标量子查询，列子查询，行子查询
exists后面（相关子查询）：表子查询
按结果集的行列数不同：
标量子查询（结果集只有一行一列）
列子查询（结果集只有一列多行）
行子查询（结果集有一行多列）
表子查询（结果集一般为多行多列）

where或having后面

#1、标量子查询
#2、列子查询（多行子查询）
#3、行子查询（多行多列）

特点：
①子查询放在小括号内
②子查询一般放在条件右侧
③标量子查询，一般搭配单行操作符使用： > < >= <= = <>
列子查询一般搭配多行操作符使用
IN/NOT IN、ANY/SOME、ALL
④子查询的执行优先于主查询执行

标量子查询

#案例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'
);

列子查询（多行子查询）

#案例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)
);#in可以换成 =ANY

行子查询（结果集是一行多列或多行多列）

#案例：查询员工编号最小并且工资最高的员工信息
SELECT *
FROM employees
WHERE employee_id=(
	SELECT MIN(employee_id)
	FROM employees
)
AND salary=(
	SELECT MAX(salary)
	FROM employees
);

select后面

from后面

exists后面（相关子查询）

（九）分页查询

应用场景：当要显示的数据一页显示不全，需要分页提交sql请求
语法：
select 查询列表
from 表
【join type join 表2
where 筛选条件
group by 分组字段
having 分组后的筛选
order by 排序的字段】
limit offset，size;

	offset:要显示条目的起始索引，起始索引从0开始
	size：要显示的条目数

特点：
①limit语句放在查询语句的最后
②公式：要显示的页数 page，每页的条目数size

	select 查询列表
	from 表
	limit (page-1)*size,size;