mysql基础之查询

2022/1/14 19:06:02

编程Tag： mysql 基础 select 连接查询 id ... where

本文主要是介绍mysql基础之查询，对大家解决编程问题具有一定的参考价值，需要的程序猿们随着小编来一起学习吧！

select基础

- 基本查询
- - 基本语法
  - 去重-别名-表结构
  - 着重号
- 条件查询
- 排序与分页
- 分组查询
- - 单列分组
  - 多列分组
  - GROUP BY中使用WITH ROLLUP
  - HAVING
  - WHERE和HAVING的对比
- 多表查询
- - 笛卡尔积
  - 等值连接 vs 非等值连接
  - 自连接 vs 非自连接
  - 内连接 vs 外连接
  - UNION的使用
  - SQL99语法新特性
- 子查询
- - 基本使用
  - 子查询的分类
  - 单行子查询
  - 多行子查询
  - 相关子查询
  - EXISTS 与 NOT EXISTS关键字
- SELECT的执行过程

基本查询

基本语法

select [distinct] * | {colunm1,colunm2,...}
	from table_name;
	
特点
1、查询列表可以是字段、常量、表达式、函数，也可以是多个
2、查询结果是一个虚拟表

基本的查询语句

SELECT 1; #没有任何子句
SELECT 9/2; #没有任何子句

SELECT 标识选择哪些列
	FROM 标识从哪个表中选择

去重-别名-表结构

distinct（去重）

SELECT DISTINCT job_id FROM employees;

起别名

空格 AS 双引号

# as:全称：alias(别名),可以省略
# 列的别名可以使用一对""引起来，不要使用''。
SELECT employee_id emp_id,last_name AS lname,department_id "部门id",salary * 12 AS "annual sal"
	FROM employees;

查看表结构：

DESCRIBE employees;
DESC employees;

一般情况下，除非需要使用表中所有的字段数据，最好不要使用通配符‘*’。使用通配符虽然可以节省输入查询语句的时间，但是获取不需要的列数据通常会降低查询和所使用的应用程序的效率。通配符的优势是:当不知道所需要的列的名称时，可以通过它获取它们。

在生产环境下，不推荐你直接使用 SELECT * 进行查询。

着重号

sql语法需要保证表中的字段、表名等没有和保留字、数据库系统或常用方法冲突。如果真的相同，请在 SQL语句中使用一对``（着重号）引起来。

mysql> SELECT * FROM ORDER;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that
corresponds to your MySQL server version for the right syntax to use near 'ORDER' at
line 1

条件查询

条件查询：根据条件过滤原始表的数据，查询到想要的数据

语法：
select
要查询的字段|表达式|常量值|函数
from 表名
where 条件 ;

-- 查询姓名为赵云的学生成绩
SELECT * FROM student WHERE `name` = '赵云';
-- 查询英语成绩大于 90 分的同学
SELECT * FROM student WHERE english>90;
-- 查询总分大于 250 分的所有同学
SELECT * FROM student 
	WHERE (chinese + english + math)>250;

注：

不能在 WHERE 子句中使用聚合函数。

排序与分页

使用 ORDER BY 子句排序

ASC（ascend）: 升序（默认）

DESC（descend）:降序

ORDER BY 子句在SELECT语句的结尾（除limit语句之外）。

单列排序：

-- 对姓韩的学生成绩[总分]排序输出(升序) where + order by
SELECT `name`,(chinese + english + math)AS total_score FROM student
	WHERE `name` LIKE '韩%'
	ORDER BY (chinese + english + math);

SELECT *,(chinese + english + math)AS total FROM student
	WHERE `name` LIKE '韩%'
	ORDER BY total;

多列排序：

-- 先按数学升序若相等按英语降序排
SELECT `name` ,math,english FROM student
	ORDER BY math,english DESC;

可以使用不在SELECT列表中的列排序。

在对多列进行排序的时候，首先排序的第一列必须有相同的列值，才会对第二列进行排序。如果第一列数据中所有值都是唯一的，将不再对第二列进行排序。

分页

当要查询的条目数太多，一页显示不全

格式：

LIMIT [位置偏移量,] 行数

第一个“位置偏移量”参数指示MySQL从哪一行开始显示，是一个可选参数，如果不指定“位置偏移量”，将会从表中的第一条记录开始（第一条记录的位置偏移量是0，第二条记录的位置偏移量是 1，以此类推）；第二个参数“行数”指示返回的记录条数。

MySQL 8.0中可以使用“LIMIT 3 OFFSET 4”，意思是获取从第5条记录开始后面的3条记录，和“LIMIT 4,3;”返回的结果相同。

公式：

假如要显示的页数为page，每一页条目数为size
select 查询列表
from 表
limit (page-1)*size,size;

注意：LIMIT 子句必须放在整个SELECT语句的最后！

分组查询

可以使用GROUP BY子句将表中的数据分成若干组。

单列分组

在SELECT列表中所有未包含在聚合函数中的列都应该包含在 GROUP BY子句中,包含在 GROUP BY 子句中的列不必需包含在SELECT 列表中

SELECT department_id, AVG(salary)
FROM employees
GROUP BY department_id ;

多列分组

实例
-- 显示每个部门的每种岗位的平均工资和最低工资
/*分析 1. 显示每个部门的平均工资和最低工资
	2. 显示每个部门的每种岗位的平均工资和最低工资
*/
SELECT FORMAT(AVG(sal),2),MIN(sal),deptno,job
	FROM emp
	GROUP BY deptno,job;

注：

若查询中同时有聚合函数和非聚合函数需要使用group by。

GROUP BY中使用WITH ROLLUP

使用 WITH ROLLUP 关键字之后，在所有查询出的分组记录之后增加一条记录，该记录计算查询出的所有记录的总和，即统计记录数量。

SELECT department_id,AVG(salary)
FROM employees
WHERE department_id > 80
GROUP BY department_id WITH ROLLUP;

注意：

当使用ROLLUP时，不能同时使用ORDER BY子句进行结果排序，即ROLLUP和ORDER BY是互相排斥的。

HAVING

过滤分组：HAVING子句

行已经被分组。

使用了聚合函数。

满足HAVING 子句中条件的分组将被显示。

HAVING 不能单独使用，必须要跟 GROUP BY 一起使用。

-- 显示最高工资高于10000的部门id
SELECT department_id, MAX(salary)
FROM employees
GROUP BY department_id
HAVING MAX(salary)>10000 ;

-- 显示平均工资低于 2000 的部门号和它的平均工资
SELECT AVG(sal),deptno FROM emp
	GROUP BY deptno
	HAVING AVG(sal)<2000;

WHERE和HAVING的对比

WHERE 可以直接使用表中的字段作为筛选条件，但不能使用分组中的计算函数作为筛选条件； HAVING 必须要与 GROUP BY 配合使用，可以把分组计算的函数和分组字段作为筛选条件。
如果需要通过连接从关联表中获取需要的数据，WHERE 是先筛选后连接，而 HAVING 是先连接后筛选。

特点：WHERE 比 HAVING 更高效。

多表查询

多表查询，也称为关联查询，指两个或更多个表一起完成查询操作。

前提条件：这些一起查询的表之间是有关系的（一对一、一对多），它们之间一定是有关联字段，这个关联字段可能建立了外键，也可能没有建立外键。比如：员工表和部门表，这两个表依靠“部门编号”进行关联。

笛卡尔积

笛卡尔乘积是一个数学运算。假设我有两个集合 X 和 Y，那么 X 和 Y 的笛卡尔积就是 X 和 Y 的所有可能组合，也就是第一个对象来自于 X，第二个对象来自于 Y 的所有可能。组合的个数即为两个集合中元素个数的乘积数。

笛卡尔积现象会在下面条件下产生：

省略多个表的连接条件（或关联条件）
连接条件（或关联条件）无效
所有表中的所有行互相连接

为了避免笛卡尔积，可以在 WHERE 加入有效的连接条件。

等值连接 vs 非等值连接

等值连接–where后的条件为等值的连接条件

SELECT employees.employee_id, employees.last_name,
	employees.department_id, departments.department_id,
	departments.location_id
FROM employees, departments
WHERE employees.department_id = departments.department_id;

扩展

1.多个连接条件用 AND 操作符

2.多个表中有相同列时，必须在列名之前加上表名前缀。

3.在不同表中具有相同列名的列可以用表名加以区分。

4.连接 n个表,至少需要n-1个连接条件

注：

如果我们使用了表的别名，在查询字段中、过滤条件中就只能使用别名进行代替，不能使用原有的表名，否则就会报错。

阿里开发规范

【强制】对于数据库中表记录的查询和变更，只要涉及多个表，都需要在列名前加表的别名（或表名）进行限定。

非等值连接–where后的条件为非等值的连接条件

SELECT e.last_name, e.salary, j.grade_level
FROM employees e, job_grades j
WHERE e.salary BETWEEN j.lowest_sal AND j.highest_sal;

-- 查询英语分数在 80－90 之间的同学。
SELECT * FROM student
	WHERE english>=80 AND english<=90;

自连接 vs 非自连接

自连接

当需要把同一张表当做两张表使用

当table1和table2本质上是同一张表，只是用取别名的方式虚拟成两张表以代表不同的意义。然后两个表再进行内连接，外连接等查询。

-- 自连接语法：
	select 查询列表
	from 表1 别名,表1 别名
	where 【非】等值的连接条件
	【and 筛选条件】;

SELECT worker.ename AS '员工名',boss.ename AS '上级名'
	FROM AS emp  worker,emp boss
	WHERE worker.mgr = boss.empno;

非自连接

查询的表本质上没有同一张表

内连接 vs 外连接

内连接–: 合并具有同一列的两个以上的表的行, 结果集中不包含一个表与另一个表不匹配的行。

外连接–: 两个表在连接过程中除了返回满足连接条件的行以外还返回左（或右）表中不满足条件的行，这种连接称为左（或右）外连接。没有匹配的行时, 结果表中相应的列为空(NULL)。

SQL92：使用(+)创建连接

内连接就是上面那些。外连接如下

#左外连接
SELECT last_name,department_name
FROM employees ,departments
WHERE employees.department_id = departments.department_id(+);
#右外连接
SELECT last_name,department_name
FROM employees ,departments
WHERE employees.department_id(+) = departments.department_id;

在 SQL92 中采用（+）代表从表所在的位置。即左或右外连接中，(+) 表示哪个是从表。

Oracle 对 SQL92 支持较好，而 MySQL 则不支持 SQL92 的外连接。而且在 SQL92 中，只有左外连接和右外连接，没有满（或全）外连接。

SQL99语法实现多表查询

基本语法

SELECT table1.column, table2.column,table3.column
	FROM table1
	JOIN table2 ON table1 和 table2 的连接条件
	JOIN table3 ON table2 和 table3 的连接条件

内连接(INNER JOIN)

#语法
select 查询列表
	from 表1 别名
	[INNER]join 表2 别名 on 连接条件
	WHERE 等其他子句;

外连接(OUTER JOIN)

-- 1左外连接：左侧表全部显示
-- 2右外连接：右侧表全部显示
语法：
select 查询列表
	from 表1 别名
	left|right|full [outer] join 表2 别名 on 连接条件
	where 筛选条件;
-- 比如：列出部门名称和这些部门的员工名称和工作，同时要求 显示出那些没有员工的部门。
-- 使用左外连接	
SELECT dname, ename, job 
	FROM dept LEFT JOIN emp
	ON dept.deptno = emp.deptno;

特点：
①查询的结果=主表中所有的行，如果从表和它匹配的将显示匹配行，如果从表没有匹配的则显示null
②left join 左边的就是主表，right join 右边的就是主表
full join 两边都是主表
③一般用于查询除了交集部分的剩余的不匹配的行

注：

LEFT JOIN 和 RIGHT JOIN 只存在于 SQL99 及以后的标准中，在 SQL92 中不存在，只能用 (+) 表示。

满外连接(FULL OUTER JOIN)

满外连接的结果 = 左右表匹配的数据 + 左表没有匹配到的数据 + 右表没有匹配到的数据。 SQL99是支持满外连接的。使用FULL JOIN 或 FULL OUTER JOIN来实现。需要注意的是，MySQL不支持FULL JOIN，但是可以用 LEFT JOIN UNION RIGHT join代替。

UNION的使用

联合(合并)查询–将多次查询结果合并成一个结果。

UNION 操作符返回两个查询的结果集的并集，去除重复记录。

UNION ALL操作符返回两个查询的结果集的并集。对于两个结果集的重复部分，不去重。

注意：执行UNION ALL语句时所需要的资源比UNION语句少。如果明确知道合并数据后的结果数据不存在重复数据，或者不需要去除重复的数据，则尽量使用UNION ALL语句，以提高数据查询的效率。

-- union all 就是将两个查询结果合并，不会去重
SELECT ename,sal,job FROM emp WHERE sal>2500 
UNION ALL
SELECT ename,sal,job FROM emp WHERE job='MANAGER' 

-- union  就是将两个查询结果合并，会去重
SELECT ename,sal,job FROM emp WHERE sal>2500 
UNION 
SELECT ename,sal,job FROM emp WHERE job='MANAGER'

特点

多条查询语句的查询的列数必须是一致的
条查询语句的查询的列的类型几乎相同
union代表去重，union all代表不去重

SQL99语法新特性

自然连接

SQL99 在 SQL92 的基础上提供了一些特殊语法，比如 NATURAL JOIN 用来表示自然连接。

它会帮你自动查询两张连接表中 所有相同的字段 ，然后进行等值连接。

SELECT employee_id,last_name,department_name
FROM employees e NATURAL JOIN departments d;

USING连接

SQL99还支持使用 USING 指定数据表里的同名字段进行等值连接。但是只能配合JOIN一起使用。

SELECT employee_id,last_name,department_name
FROM employees e JOIN departments d
USING (department_id);

与自然连接 NATURAL JOIN 不同的是，USING 指定了具体的相同的字段名称，你需要在 USING 的括号 () 中填入要指定的同名字段。同时使用 JOIN…USING 可以简化 JOIN ON 的等值连接。

【强制】超过三个表禁止 join。需要 join 的字段，数据类型保持绝对一致；多表关联查询时，保证被关联的字段需要有索引。

子查询

基本使用

子查询指一个查询语句嵌套在另一个查询语句内部的查询。

使用情况：

查询需要从结果集中获取数据，或者需要从同一个表中先计算得出一个数据结果，然后与这个数据结果（可能是某个标量，也可能是某个集合）进行比较。

实例：

# 如何显示与 SMITH 同一部门的所有员工?
/*
1. 先查询到 SMITH 的部门号得到
SELECT deptno FROM emp
	WHERE ename = 'SMITH';
2. 把上面的 select 语句当做一个临时表来使用
*/
-- 方法一：	
SELECT ename FROM emp,(-- 临时表
	SELECT deptno FROM emp
	WHERE ename = 'SMITH'
)table_deptno	
	WHERE emp.deptno = table_deptno.deptno;
-- 方法二：	
SELECT ename FROM emp
	WHERE deptno = (SELECT deptno FROM emp
				WHERE ename = 'SMITH'
			);
			
# 查询员工中谁的薪水比Abel高			
SELECT last_name,salary
FROM employees
WHERE salary > (
SELECT salary
FROM employees
WHERE last_name = 'Abel'
);

子查询的使用

子查询（内查询）在主查询之前一次执行完成。
子查询的结果被主查询（外查询）使用。

注意事项

子查询要包含在括号内
将子查询放在比较条件的右侧
单行操作符对应单行子查询，多行操作符对应多行子查询

子查询的分类

按内查询的结果返回一条还是多条记录，将子查询分为单行子查询、多行子查询。
按内查询是否被执行多次，将子查询划分为相关(或关联)子查询和不相关(或非关联)子查询。

单行子查询

内查询的结果只有一行

单行比较操作符：= > >= < <= <>

多行子查询

内查询的结果有多行

多行操作符：in any all

EXISTS 与 NOT EXISTS关键字

关联子查询通常也会和 EXISTS操作符一起来使用，用来检查在子查询中是否存在满足条件的行。

如果在子查询中不存在满足条件的行：
- 条件返回 FALSE
- 继续在子查询中查找
如果在子查询中存在满足条件的行：
- 不在子查询中继续查找
- 条件返回 TRUE
NOT EXISTS关键字表示如果不存在某种条件，则返回TRUE，否则返回FALSE。

SELECT department_id, department_name
FROM departments d
WHERE NOT EXISTS (SELECT *
                  FROM employees
                  WHERE department_id = d.department_id
                 );

**题目中可以使用子查询，也可以使用自连接: **

一般情况建议你使用自连接，因为在许多 DBMS 的处理过程中，对于自连接的处理速度要比子查询快得多。

SELECT的执行过程

#方式1：
SELECT ...,....,...
FROM ...,...,....
WHERE 多表的连接条件
AND 不包含组函数的过滤条件
GROUP BY ...,...
HAVING 包含组函数的过滤条件
ORDER BY ... ASC/DESC
LIMIT ...,...

#方式2：
SELECT ...,....,...
FROM ... JOIN ...
ON 多表的连接条件
JOIN ...
ON ...
WHERE 不包含组函数的过滤条件
AND/OR 不包含组函数的过滤条件
GROUP BY ...,...
HAVING 包含组函数的过滤条件
ORDER BY ... ASC/DESC
LIMIT ...,...
#其中：
#（1）from：从哪些表中筛选
#（2）on：关联多表查询时，去除笛卡尔积
#（3）where：从表中筛选的条件
#（4）group by：分组依据
#（5）having：在统计结果中再次筛选
#（6）order by：排序
#（7）limit：分页

SELECT 查询时的两个顺序：

关键字的顺序是不能颠倒的

SELECT ... FROM ... WHERE ... GROUP BY ... HAVING ... ORDER BY ... LIMIT...

SELECT 语句的执行顺序

FROM -> WHERE -> GROUP BY -> HAVING -> SELECT 的字段 -> DISTINCT -> ORDER BY -> LIMIT

这篇关于mysql基础之查询的文章就介绍到这儿，希望我们推荐的文章对大家有所帮助，也希望大家多多支持为之网！