MySQL中select 查询完整语法与子查询使用

本文涉及的产品
RDS MySQL Serverless 基础系列,0.5-2RCU 50GB
RDS MySQL Serverless 高可用系列,价值2615元额度,1个月
云数据库 RDS PostgreSQL,高可用系列 2核4GB
简介: MySQL中select 查询完整语法与子查询使用

select查询完整语法格式如下:

selet[select 选项] 
字段列表[字段别名] 
from 数据源
[where条件字句]
[group by 字句]
[having 字句]
[order by 字句]
[limit 字句]

【1】select选项

即select对查出来的结果的处理方式

  • all :默认的,保留所有的结果;
  • distinct:去重,将查出来的结果重复的去掉(所有字段值都相同才叫重复)。

下面两条语句等价:

select * from p_user_2;
SELECT all * from p_user_2

distinct示例:

select DISTINCT NAME,age from p_user_2

cfb20e230913386ce9a2528063b279a1.png


【2】字段别名

多表操作时可能会有字段名字重复,此时可重命名。

示例如下:

select  NAME [as] '用户名',age [as] '年龄' from p_user_2;
-- as可缺省

【3】数据源

数据源即数据的来源,关系型数据库数据来源为数据表。本质上只要保证数据类似二维表,最终都可以作为数据源。

数据源分多种:单表数据源,多表数据源(多表查询)以及查询语句(from子句)。

单表数据源

select * from p_user

多表数据源

select * from p_user,c_user


可以自定义列,别名进行查询。如果默认查询且两表存在重复字段名,后置+1(此处用的Navicat for MySQL,如果在dos下,字段不会+1):


需要注意的是:这样查询效果是从一张表中取出一条记录,去另外一张表中匹配所有的记录,而且全部保留(包括记录数和字段数) 将这种结果称之为--笛卡尔积(交叉连接)


查询语句:

select * from (select NAME,age from p_user) as t;
-- from后面查询语句结果作为一个临时表;
-- 表一定要有别名

【4】where子句

where是唯一一个直接从磁盘获取数据的时候就开始判断的条件。从磁盘取出一条记录,开始where判断。判断如果成立,则保存到内存中;失败则直接放弃。

where子句,用来判断数据筛选数据,返回结果0或者1,0--false;1--true

判断条件:

比较运算符:<,>,>=,<=,!=,<>,=,like,between and,in/not in ;

逻辑运算符:and(&&),or(||),not(!)。

in 是一个区间,一个集合,准备的说是一个散列值的序列。

between是两个数直接的区间范围,左边的数必须小于或者等于右边的数字。

select * from p_user where age BETWEEN 10 and 20;
select * from p_user where age <20 and age >10;
select * from p_user where age in(10,11,12,15,19,18)

【5】group by 子句

group by:按照某个条件进行分组,记录相同的(按照数据表中保存的次序)只保留一条,然后根据条件字段进行排序默认升序。

即,对分组的结果合并之后的整个结果进行排序!

分组的意义是为了统计数据(按组统计:按分组字段进行统计,一个组只统计一条数据)。

MySQL 提供的统计函数

count():统计分组后的记录数,即每一组有多少记录;

max():统计每组中的最大值;

min():统计每组中的最小值;

avg():统计每组中的平均值;

sum:对每组进行求和。

语法格式如下:

select [columns] from table_name [where..] group by [columns] [having ...]

需要说明的是,在select指定的字段要么就要包含在group By语句的后面,作为分组的依据;要么就要被包含在聚合函数中。

详细参考group by实例分析


下面操作是在Navicat for MySQL中进行,该工具对语法进行了处理。

① 按照年龄进行分组

select * from p_user GROUP BY age;

f55ac757d63964e11d322e48e108fe14.png注意,group by 默认进行了排序,其age列效果同下 :

select DISTINCT age from p_user ORDER BY age asc;

08476d16d236b331424fd1a425b60d54.png

② 按照年龄分组并count

select *, COUNT(*) from p_user GROUP BY age;

8556faa82239c61f4db046d3def33f09.png

count( ):里面可以使用两种参数:*代表统计记录,字段名代表统计对应的字段(NULL不统计)。


count()是分组之后统计每组的记录数,单独执行count查询只会返回一行结果。


MYISAM存储引擎下 ,COUNT(*)的效率高。INNODB存储引擎下,COUNT(*)和COUNT(1)的效率差不多,比COUNT(字段)要高一些


③ 按照年龄分组在count基础上取最大、最小值

select *,count(age), max(id),min(id) from p_user GROUP BY age;

3fb016ca90d6fa02c654af0270d7f58c.png

④ 多字段分组

分组之后整合的结果也是先按照sex后按照age排序。

select sex,age,COUNT(age) from p_user GROUP BY sex,age;

5733a636bb643473ea76629ab2fea620.png


如果想统计每组中的name呢?可以使用GROUP_CONCAT()函数。

group_concat() : 可以对分组的结果中的某个字段进行字符串链接(保留该组所有的某个字段)。

select sex,age,COUNT(age),GROUP_CONCAT(name) from p_user GROUP BY sex,age

b16ba2a2558db1aa4f453139c16df97d.png

回溯统计with rollup 。

解释如下:任何一个分组后都会有一个小组,最后都需要根据当前分组的字段向上级分组进行汇报统计。

回溯统计的时候会将分组字段置空。

正常分组如下:

SELECT sex,COUNT(*) from p_user GROUP BY sex;

868725e7770f2d5ab9ca0c2bed93a7d5.png

回溯统计如下:

select sex,count(*)from p_user GROUP BY sex with rollup;

a4bd5c44ef45ba80a739170466b08169.png

多字段回溯统计

正常统计1-九条记录:

select sex,age,COUNT(age),GROUP_CONCAT(name) from p_user GROUP BY age,sex 

ec2bacf330580933c8f745152a0cb5b4.png

回溯统计1-16条:

select sex,age,COUNT(age),GROUP_CONCAT(name) 
from p_user GROUP BY age,sex WITH ROLLUP

2eea142613716a8b972a2975085570e9.png

按照年龄进行分组,之后又针对每个年龄进行sex分组。那么首先向sex的上级分组age进行汇报统计,然后age再向顶级分组进行汇报统计。age有六组,故进行六次(sex-age)汇报统计,最后(age - 顶级)进行一次总的汇报统计。


正常统计2-九条记录:

select sex,age,COUNT(age),GROUP_CONCAT(name) from p_user GROUP BY sex,age

150cac70f2289eb34026948289004e25.png

回溯统计2-12条:

select sex,age,COUNT(age),GROUP_CONCAT(name) 
from p_user GROUP BY sex,age WITH ROLLUP

560c382df99bb0a6f68693bbb4c3cca5.png

首先根据sex进行分组,之后再根据age进行分组。那么汇报统计首先是age-sex,因为sex只有两组,故回溯统计两次。最后sex-顶层,进行一次回溯统计。共统计三次,故12条

多字段回溯:考虑第一层分组会有此回溯;第二次分组要看第一次分组的组数,组数是多少,回溯就是多少,然后加上第一层回溯即可。


【6】having子句

having子句与where子句一样进行条件判断的。

where是针对磁盘数据进行判断,进入到内存之后会进行分组操作,而分组结果需要having进行过滤。

having能做where能做的几乎所有事情,反之不能。

① 分组统计的结果或者统计函数只有having能使用,where不可以。

select age,count(*) from p_user group by age having count(*)>1;
-- where 不可以,因为where是在group by前进行过滤,而count(*)是在group by之后统计。

② having能够使用字段别名,where不能。

where是从磁盘获取数据,名字只可能是字段名,别名是在字段进入内存后才会产生。

select age,count(*) as total from p_user group by age having total>1;
select name as 名字,age from p_user having 名字 like '%明%';
-- 如果换成where则错误。

【7】order by子句

order by : 排序,根据某个字段进行升序或者降序排序,依赖校对集。

语法:order by 字段名 [asc|desc]默认asc-升序,desc是降序。

排序可以进行多字段排序:先根据某个字段进行排序,然后排序好的内部,再按照某个数据进行再次排序。

select * from p_user ORDER BY sex,age;

【8】limit子句

limit子句是一种限制结果的子句:限制数量。

① 限制查询长度(记录数)

select * from p_user limit 2;

② 限制起始位置和偏移长度,limit m,n

常用来进行数据分页;记录数从 0 开始;

select * from p_user limit 2 ,10;
-- 查询从第二条到第十条的数据

数据分页

分页可以为用户节省时间,提高服务器响应效率,减少资源的浪费。

对于服务器来讲,每次根据用户选择的页码来获取不同的数据,limit offset,length。

length:每页显示的数据量,基本不变。offset:(页码-1)*length(因为记录数从0开始哦)。


【9】子查询

① 基础概念

出现在其他语句内部的select语句,称为子查询或内查询。内部嵌套其他select语句的查询,称为外查询或主查询。

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

按子查询出现的位置:

select后面:
  仅仅支持标量子查询
from后面:
  支持表子查询
where或having后面:
  标量子查询(单行) 
  列子查询  (多行) 
  行子查询
exists后面(相关子查询)
  表子查询

按结果集的行列数不同:

标量子查询(结果集只有一行一列)
列子查询(结果集只有一列多行)
行子查询(结果集有一行多列)
表子查询(结果集一般为多行多列)

标量子查询,一般搭配着单行操作符使用> < >= <= = <>

列子查询,一般搭配着多行操作符使用in、any/some、all

select first_name from employees 
where department_id in(
select department_id from departments
where location_id=1700
)

查询在部门的location_id为1700的部门工作的员工的员工号

SELECT employee_id
FROM employees
WHERE department_id =ANY(
  SELECT DISTINCT department_id
  FROM departments 
  WHERE location_id  = 1700
);

返回其它部门中比job_id为‘IT_PROG’部门所有工资都低的员工 的员工号、姓名、job_id 以及salary

SELECT last_name,employee_id,job_id,salary
FROM employees
WHERE salary<ALL(
  SELECT DISTINCT salary
  FROM employees
  WHERE job_id = 'IT_PROG'
) AND job_id<>'IT_PROG';

需要注意的是

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

② 单行子查询

只返回一行,使用单行比较操作符。

image.png

SELECT last_name FROM employees
WHERE salary >
(SELECT salary FROM employees WHERE last_name = 'Abel');

③ 子查询中的HAVING 子句

首先执行子查询,向主查询中的HAVING 子句返回结果。

查询最低工资大于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
);

④ from后面子句

将子查询结果充当一张表,要求必须起别名

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后面(相关子查询)

#in
SELECT department_name
FROM departments d
WHERE d.`department_id` IN(
  SELECT department_id
  FROM employees
)
#EXISTS
SELECT department_name
FROM departments d
WHERE EXISTS(
  SELECT *
  FROM employees e
  WHERE d.`department_id`=e.`department_id`
);

⑥ select子句

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

至于selec各个子句直接执行的顺序,点击查看select执行顺序

相关实践学习
每个IT人都想学的“Web应用上云经典架构”实战
本实验从Web应用上云这个最基本的、最普遍的需求出发,帮助IT从业者们通过“阿里云Web应用上云解决方案”,了解一个企业级Web应用上云的常见架构,了解如何构建一个高可用、可扩展的企业级应用架构。
MySQL数据库入门学习
本课程通过最流行的开源数据库MySQL带你了解数据库的世界。 &nbsp; 相关的阿里云产品:云数据库RDS MySQL 版 阿里云关系型数据库RDS(Relational Database Service)是一种稳定可靠、可弹性伸缩的在线数据库服务,提供容灾、备份、恢复、迁移等方面的全套解决方案,彻底解决数据库运维的烦恼。 了解产品详情:&nbsp;https://www.aliyun.com/product/rds/mysql&nbsp;
目录
相关文章
|
26天前
|
SQL 缓存 监控
MySQL缓存机制:查询缓存与缓冲池优化
MySQL缓存机制是提升数据库性能的关键。本文深入解析了MySQL的缓存体系,包括已弃用的查询缓存和核心的InnoDB缓冲池,帮助理解缓存优化原理。通过合理配置,可显著提升数据库性能,甚至达到10倍以上的效果。
|
28天前
|
SQL 存储 关系型数据库
MySQL体系结构详解:一条SQL查询的旅程
本文深入解析MySQL内部架构,从SQL查询的执行流程到性能优化技巧,涵盖连接建立、查询处理、执行阶段及存储引擎工作机制,帮助开发者理解MySQL运行原理并提升数据库性能。
|
8天前
|
SQL 关系型数据库 MySQL
MySQL的查询操作语法要点
储存过程(Stored Procedures) 和 函数(Functions) : 储存过程和函数允许用户编写 SQL 脚本执行复杂任务.
104 14
|
10天前
|
SQL 关系型数据库 MySQL
MySQL的查询操作语法要点
以上概述了MySQL 中常见且重要 的几种 SQL 查询及其相关概念 这些知识点对任何希望有效利用 MySQL 进行数据库管理工作者都至关重要
58 15
|
27天前
|
SQL 监控 关系型数据库
SQL优化技巧:让MySQL查询快人一步
本文深入解析了MySQL查询优化的核心技巧,涵盖索引设计、查询重写、分页优化、批量操作、数据类型优化及性能监控等方面,帮助开发者显著提升数据库性能,解决慢查询问题,适用于高并发与大数据场景。
|
28天前
|
SQL 关系型数据库 MySQL
MySQL入门指南:从安装到第一个查询
本文为MySQL数据库入门指南,内容涵盖从安装配置到基础操作与SQL语法的详细教程。文章首先介绍在Windows、macOS和Linux系统中安装MySQL的步骤,并指导进行初始配置和安全设置。随后讲解数据库和表的创建与管理,包括表结构设计、字段定义和约束设置。接着系统介绍SQL语句的基本操作,如插入、查询、更新和删除数据。此外,文章还涉及高级查询技巧,包括多表连接、聚合函数和子查询的应用。通过实战案例,帮助读者掌握复杂查询与数据修改。最后附有常见问题解答和实用技巧,如数据导入导出和常用函数使用。适合初学者快速入门MySQL数据库,助力数据库技能提升。
|
1月前
|
存储 关系型数据库 MySQL
使用命令行cmd查询MySQL表结构信息技巧分享。
掌握了这些命令和技巧,您就能快速并有效地从命令行中查询MySQL表的结构信息,进而支持数据库维护、架构审查和优化等工作。
198 9
|
28天前
|
SQL 监控 关系型数据库
MySQL高级查询技巧:子查询、联接与集合操作
本文深入解析了MySQL高级查询的核心技术,包括子查询、联接和集合操作,通过实际业务场景展示了其语法、性能差异和适用场景,并提供大量可复用的代码示例,助你从SQL新手进阶为数据操作高手。
|
2月前
|
Oracle 关系型数据库 MySQL
比较Oracle和MySQL的语法差异。
在使用Oracle和MySQL时,数据库设计、查询优化、以及日常管理的方式会因为这些差异而有不同的考虑和应用策略。因此,开发人员和数据库管理员必须了解各自数据库的特性和语法差异,以便更有效地利用数据库资源。适应这些语法和功能上的差异对于维护跨数据库平台应用至关重要。
173 0
|
2月前
|
人工智能 Java 关系型数据库
Java的时间处理与Mysql的时间查询
本文总结了Java中时间与日历的常用操作,包括时间的转换、格式化、日期加减及比较,并介绍了MySQL中按天、周、月、季度和年进行时间范围查询的方法,适用于日常开发中的时间处理需求。

推荐镜像

更多