MySQL高级查询

最新推荐文章于 2024-06-29 16:43:44 发布
最新推荐文章于 2024-06-29 16:43:44 发布
阅读量909 收藏 30
点赞数 13
分类专栏： mysql 学习笔记 文章标签： mysql adb android
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/qq_37844454/article/details/139963695
版权

MySQL

前言

文本源自微博客 (www.microblog.store),且已获授权.

一. mysql基础知识

1. mysql常用系统命令

  • 启动命令 net start mysql
  • 停止命令 net stop mysql
  • 登录命令 mysql -h ip -P 端口 -u 用户名 -p

​ 本机可以省略 ip mysql -u 用户名 -p

  • 查看数据库版本 mysql --version / select version();(已登录)
  • 显示所有数据库 show databases;
    • 进⼊指定的库 use 库名;
    • 显示当前库中所有表 show tables;
    • 查看其他库中所有的表 show tables from 库名
    • 查看表的创建语句 show create table 表名;
    • 查看表结构 desc 表名;
    • 查看当前所在库 select database();
  • 查看当前mysql支持的存储引擎 show engines;
  • 查看系统变量及其值 show variables;
  • 查看某个系统变量 show VARIABLES like '变量名';

2. mysql语法规范

  1. 不区分大小写,但建议关键字大写,表明、列明小写
  2. 每条语句最好用英文分号结尾
  3. 每条命令根据需要，可以进行缩进或换行
  4. 注释
    • 单行注释： #注释文字
    • 单行注释： – 注释文字，注意这里需要加空格
    • 多行注释 /*注释文字*/

3. SQL的语言分类

  • DQL（Data Query Language）：数据查询语⾔ select 相关语句
  • DML（Data Manipulate Language）：数据操作语⾔ insert 、update、delete 语句
  • DDL（Data Define Language）：数据定义语⾔ create、drop、alter 语句
  • TCL（Transaction Control Language）：事务控制语⾔ set autocommit=0、start
    transaction、savepoint、commit、rollback

二. MySQL中数据类型介绍

1. MySQL的数据类型

主要包括以下五大类

  • 整形数据： bit、bool、tinyint、smallint、mediumint、int、bigint
  • **浮点数类型：**float、double、decimal、decimal
  • **字符串类型：**char、varchar、tinyblob、blob、mediumblob、longblob、tinytext、text、mediumtext、longtext
  • **日期类型：**Date、DateTime、TimeStamp、Time、Year
  • **其他数据类型：**xxxx

2. 数据类型选择的一些建议

  • **选小不选大：**⼀般情况下选择可以正确存储数据的最小数据类型，越小的数据类型通
    常更快，占用磁盘，内存和CPU缓存更小。
  • **简单就好：**简单的数据类型的操作通常需要更少的CPU周期，例如：整型比字符操作
    代价要小得多，因为字符集和校对规则(排序规则)使字符比整型比较更加复杂。
  • **尽量避免NULL：**尽量制定列为NOT NULL，除非真的需要NULL类型的值，有NULL的
    列值会使得索引、索引统计和值比较更加复杂。
  • 浮点类型的建议统⼀选择decimal
  • 记录时间的建议使用int或者bigint类型，将时间转换为时间戳格式，如将时间转换为
    秒、毫秒，进行存储，方便走索引

九. 分组查询（group by、having）

1. 前期准备

  1. 语法

    SELECT column, group_function,... FROM table
[WHERE condition]
GROUP BY group_by_expression
[HAVING group_condition];

  2. 说明

    group_function：聚合函数。
    group_by_expression：分组表达式，多个之间用逗号隔开。
    group_condition：分组之后对数据进行过滤。

    分组中，select后面只能有两种类型的列：

  3. 分组中，select后面只能有两种类型的列

  4. 出现在group by后的列

  5. 或者使用聚合函数的列

  6. 聚合函数

    函数名称作用
    max查询指定列的最大值
    min查询指定列的最小值
    count
    sum
    avg

    分组时，可以使用使用上面的聚合函数。

2. 查询

  1. 单字段分组

    查询每个用户下单数量，输出：用户id、下单数量

    SELECT
	user_id,
	COUNT( user_id ) 
FROM
	t_order 
GROUP BY
	user_id

  2. 查询每个用户每年下单数量，输出字段：用户id、年份、下单数量，如下

    SELECT
	user_id,
	the_year,
	COUNT( user_id ) 
FROM
	t_order 
GROUP BY
	user_id,
	the_year;

  3. 分组前筛选数据

    分组前对数据进行筛选，使用where关键字

    需要查询2018年每个用户下单数量，输出：用户id、下单数量

    
SELECT
	user_id,
	COUNT( user_id ) 
FROM
	t_order 
	WHERE the_year = 2018
GROUP BY
	user_id;

  4. 分组后筛选数据

    分组后对数据筛选，使用having关键字

    查询2018年订单数量大于1的用户，输出：用户id，下单数量

    SELECT
	user_id,
	COUNT( user_id ) AS total
FROM
	t_order 
	WHERE the_year = 2018
GROUP BY
	user_id
	HAVING total>1;

  5. where和having的区别

    where是在分组（聚合）前对记录进行筛选，而having是在分组结束后的结果里筛选，最后返回整个sql的查询结果。
    可以把having理解为两级查询，即含having的查询操作先获得不含having子句时的sql查询结果表，然后在这个结果表上使用having条件筛选出符合的记录，最后返回这些记录，因此，having后是可以跟聚合函数的，并且这个聚集函数不必与select后面的聚集函数相同。

十二. 子查询

子查询:

​ 出现在select语句中的select语句，称为子查询或内查询。

​ 外部的select查询语句，称为主查询或外查询。

1. 子查询分类

  • 按结果集的行列数不同分为4种
    • 标量子查询（结果集只有⼀行⼀列）
    • 列子查询（结果集只有⼀列多行）
    • 行子查询（结果集有⼀行多列）
    • 表子查询（结果集⼀般为多行多列）
  • 按子查询出现在主查询中的不同位置分
    • select后面：仅仅支持标量子查询。
    • from后面：支持表子查询。
    • where或having后面：支持标量子查询（单列单行）、列子查询（单列多行）、行子
      查询（多列多行）
    • exists后面（即相关子查询）：表子查询（多行、多列)

​ (数据准备:javacode2018_employees.sql)

2. select后面的子查询

子查询位于select后⾯的，仅仅支持标量子查询。

  • 实例1 查询每个部门员工个数

    -- 查询每个部门员工个数
SELECT
	a.*,
	( SELECT count(*) FROM employees b WHERE b.department_id = a.department_id ) AS 员工个数 
FROM
	departments a;

  • 查询员工号=102的部门名称

    -- 菜鸟版本
/*SELECT
	* 
FROM
	departments dep 
WHERE
	dep.department_id = ( SELECT emp.department_id FROM employees emp WHERE emp.employee_id = 102 );
	*/ 
-- 高级版
	SELECT
( SELECT a.department_name FROM departments a, employees b WHERE a.department_id = b.department_id AND b.employee_id = 102 ) AS 部门名;

3. from后面的子查询

将子查询的结果集充当⼀张表，要求必须起别名，否者这个表找不到。
然后将真实的表和子查询结果表进行连接查询。

  • 查询每个部门的平均工资

    SELECT
	emp.department_id,
	avg( emp.salary ) 
FROM
	employees AS emp 
GROUP BY
	emp.department_id;

  • 薪资等级表

    select * from job_grades;

  • 联合查询: 将上面2个结果连接查询,筛选条件: 平均工资 between lowest_sal and highest_sal

    SELECT
	emp.department_id,
	emp.department_name,
	sa AS '平均工资',
	job.grade_level 
FROM
	( 
     SELECT
        emp.department_id,
        dep.department_name,
        avg( emp.salary ) AS sa
    FROM
        employees AS emp ,
        departments AS dep 
        where  dep.department_id = emp.department_id
    GROUP BY
        emp.department_id
    ) emp,
	job_grades job 
WHERE
	emp.sa BETWEEN job.lowest_sal 
	AND job.highest_sal;

4. where和having后面的子查询

  1. where和having使用情况

    • 标量子查询(单行单列子查询)

    • 列子查询(单行多列子查询)

    • 行子查询(一行多列子查询)

  2. 特点

  • 子查询放在小括号内。

  • 子查询⼀般放在条件的右侧。

  • 标子查询，⼀般搭配着单行单列操作符使用 >、<、>=、<=、=、<>、!=

  • 列子查询，⼀般搭配着多行操作符使用

  • in(not in)：列表中的“任意⼀个”

    • any或者some：和子查询返回的“某⼀个值”比较，比如a>some(10,20,30)，a大于
      子查询中任意⼀个即可，a大于子查询中最小值即可，等同于a>min(10,20,30)。
    • all：和子查询返回的“所有值”比较，比如a>all(10,20,30)，大于子查询中所有
      值，换句话说，a大于子查询中最大值即可满足查询条件，等同于a>max(10,20,30);

  • 子的执行优先于主查询执行，因为主查询的条件用到了子查询的结果。

  1. mysql中的in、any、some、all

    in，any，some，all分别是子查询关键词之⼀。

  • in：in常用于where表达式中，其作用是查询某个范围内的数据
  • any和some⼀样： 可以与=、>、>=、<、<=、<>结合起来使用，分别表示等于、大于、
    大于等于、小于、小于等于、不等于其中的任何⼀个数据。
  • all：可以与=、>、>=、<、<=、<>结合是来使用，分别表示等于、大于、大于等于、小
    于、小于等于、不等于其中的其中的所有数据。
1. 标量子查询
1. ⼀般标量子查询

查询谁的工资比Abel的⾼？

  1. 查询abel的工资【改查询是标量子查询】

     SELECT
 	emp.salary 
 FROM
 	employees AS emp 
 WHERE
 	emp.last_name = 'Abel'

  2. 查询员工信息，满足salary>①的结果

     SELECT
 	* 
 FROM
 	employees emp 
 WHERE
 	emp.salary > ( SELECT emp.salary FROM employees AS emp WHERE emp.last_name = 'Abel' );
2. 多个标量子查询

返回jobid与141号员工相同，salary比143号员工多的员工、姓名、jobid和工资

  1. 查询141号员工的job_id

    -- 1. 查询141号员工的job_id
	SELECT
	emp.job_id 
FROM
	employees emp 
WHERE
	emp.employee_id = 141;

  2. 查询143号员工的salary

    SELECT
	emp.salary 
FROM
	employees emp 
WHERE
	emp.employee_id = 143;

  3. 查询员工的姓名、job_id、工资，要求job_id=① and salary>②

    SELECT
	* 
FROM
	employees emp 
WHERE
	emp.job_id = ( SELECT emp.job_id FROM employees emp WHERE emp.employee_id = 141 ) 
	AND emp.salary > ( SELECT emp.salary FROM employees emp WHERE emp.employee_id = 143 );
3. 子查询+分组函数

查询最低工资大于50号部门最低工资的部门id和其最低工资【having】

  1. 查询50号部门的最低工资

    SELECT
	min( salary ) 
FROM
	employees 
WHERE
	department_id = 50;

  2. 查询每个部门的最低工资

    SELECT
	min( salary ) 
FROM
	employees 
GROUP BY
	department_id;

  3. 在②的基础上筛选，满足min(salary)>①

    	
SELECT
	emp.department_id,
	min( emp.salary ) minSalary 
FROM
	employees emp 
GROUP BY
	emp.department_id 
HAVING
	MIN( emp.salary )>(
	SELECT
		min( salary ) 
	FROM
		employees 
	WHERE
		department_id = 50 
	);
2. 列子查询(子查询结果集⼀列多行)

列子查询需要搭配多行操作符使用：in(not in)、any/some、all。
为了提升效率，最好去重⼀下distinct关键字。

实例1

返回location_id是1400或1700的部门中的所有员工姓名:

  1. 方式一:

    a. 查询location_id是1400或1700的部门编号

    select DISTINCT department_id from departments WHERE location_id in(1400,1700);

    b. 查询员工姓名，要求部门是①列表中的某⼀个

    SELECT
	last_name 
FROM
	employees 
WHERE
	department_id IN ( SELECT DISTINCT department_id FROM departments WHERE location_id IN ( 1400, 1700 ) );

  2. 方式二:使用any实现

    SELECT
	last_name 
FROM
	employees 
WHERE
	department_id = ANY ( SELECT DISTINCT department_id FROM departments WHERE location_id IN ( 1400, 1700 ) );

  3. 拓展，下面与not in等价

    SELECT
	last_name 
FROM
	employees 
WHERE
	department_id <>ALL ( SELECT DISTINCT department_id FROM departments WHERE location_id IN ( 1400, 1700 ) );
实例2

返回其他工种中比jobid为’ITPROG’工种任意工资低的员工的员工号、姓名、job_id、salary

  1. 查询job_id为’IT_PROG’部门任-工资

    SELECT DISTINCT salary FROM employees WHERE job_id = 'IT_PROG'

  2. 查询员⼯号、姓名、job_id、salary，slary<①的任意⼀个

    SELECT
	* 
FROM
	employees 
WHERE
	salary < ANY ( SELECT DISTINCT salary FROM employees WHERE job_id = 'IT_PROG' ) 
	AND job_id != 'IT_PROG';

  3. 或者

    SELECT
	* 
FROM
	employees 
WHERE
	salary < ( SELECT MAX(salary ) FROM employees WHERE job_id = 'IT_PROG' ) 
	AND job_id != 'IT_PROG';
实例3

返回其他工种中比jobid为’ITPROG’部门所有⼯资低的员⼯的员⼯号、姓名、
job_id、salary

  1. 使用ALL实现:

    SELECT
	emp.employee_id,
	emp.first_name,
	emp.job_id,
	emp.salary 
FROM
	employees emp 
WHERE
	emp.salary < ALL ( SELECT DISTINCT salary FROM employees WHERE job_id = 'IT_PROG' ) 
	AND emp.job_id != 'IT_PROG';

  2. 使用min:

    	SELECT
	emp.employee_id,
	emp.first_name,
	emp.job_id,
	emp.salary 
FROM
	employees emp 
WHERE
	emp.salary < ( SELECT MIN(salary) FROM employees WHERE job_id = 'IT_PROG' ) 
	AND emp.job_id != 'IT_PROG';
3. 行子查询(子查询结果集一行多列)

查询员工编号最小并且工资最高的员工信息，3种方式:

a. 查询最⼩的员⼯编号

SELECT
	min( employee_id ) 
FROM
	employees;

b. 查询最⾼⼯资

SELECT
	MAX( salary ) 
FROM
	employees;
方式1 分开使用查询条件
SELECT
	* 
FROM
	employees 
WHERE
	employee_id = ( SELECT min( employee_id ) FROM employees ) 
	AND salary = ( SELECT MAX( salary ) FROM employees );
方式2 使用联合查询条件
SELECT
	* 
FROM
	employees 
WHERE
	( employee_id, salary ) = (
	SELECT
		min( employee_id ),
		MAX( salary ) 
	FROM
		employees 
	)
方式3 使用联合查询条件

SELECT
	* 
FROM
	employees 
WHERE
	( employee_id, salary ) IN (
	SELECT
		min( employee_id ),
		MAX( salary ) 
	FROM
		employees 
	);
4. exists后面（也叫做相关子查询)
  1. 语法：exists(完整的查询语句)。
  2. exists查询结果：1或0，exists查询的结果用来判断子查询的结果集中是否有值。
  3. ⼀般来说，能用exists的子查询，绝对都能用in代替，所以exists用的少。
  4. 和前面的查询不同，这先执行主查询，然后主查询查询的结果，再根据子查询进行过
    滤，子查询中涉及到主查询中用到的字段，所以叫相关子查询。
实例1 简单实例
SELECT EXISTS
	( SELECT employee_id FROM employees WHERE salary = 300000 ) AS 'exists返回1或者0';

+----------------------+
| exists返回1或者0 |
+----------------------+
| 0 |
+----------------------+
1 row in set (0.00 sec)
实例2 查询所有员⼯的部门名称

  1. 查询所有员⼯的部门名称

    SELECT
	a.department_name 
FROM
	departments a 
WHERE
	EXISTS (
	SELECT
		1 
	FROM
		employees b 
	WHERE
	a.department_id = b.department_id);

  2. 使用in实现

    SELECT
	a.department_name 
FROM
	departments a 
WHERE
	a.department_id IN ( SELECT department_id FROM employees );
实例3 查询所有员⼯的部门名称

  1. exists实现

    SELECT
	a.department_name 
FROM
	departments a 
WHERE
	NOT EXISTS ( SELECT 1 FROM employees b WHERE a.department_id = b.department_id AND b.department_id IS NOT NULL );

  2. in 实现

    SELECT
	a.department_name 
FROM
	departments a 
WHERE
	a.department_id NOT IN ( SELECT 1 FROM employees b WHERE a.department_id = b.department_id AND b.department_id IS NOT NULL );
写完bug就找女朋友
关注
  • 13
    点赞
  • 踩
  • 30
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
专栏目录
MySql高级查询
04-27
SqlYog高级查询 In子查询 ..................................
mysql第四章高级查询PPT
07-02
EXISTS子查询是用来判断子查询进行运算时是否有返回行；该关键字后面是一个任意的子查询，如果该子查询有返回行，则EXISTS子查询的结果为true，反之，则为false。
MySQL 高级查询
热门推荐
看花容易绣花难
02-12 2万+
MySQL 高级查询
MySQL高级查询操作
二哈喇子！
07-06 4647
MySQL数据库的高级查询操作
mysql高级查询
weixin_46875794的博客
11-13 1465
一、查询表中部分字段 1、查询编号喝姓名 select id,name from users; 2、查询编号和姓名（标题是中文） select id as 编号 ,name as 姓名 from users; 3、查询某某（姓名）的编号和姓名 select id,name,from users where name='1'; 4、查询某某（编号）的编号和姓名 select id,name from users where id='101'; 二、模糊查询 1、查询李某某的姓名 select name
Mysql高级查询语句
weixin_45185267的博客
06-14 1万+
LIKE用于在where子句中进行模糊查询，SQL LIKE 子句中使用百分号来表示任意0个或多个字符，下划线表示任意一个字符。 as 用法 在sql语句中as用于给字段或者表重命名 排序 ORDER BY 子句来设定你想按哪个字段哪种方式来进行排序，再返回搜索结果。使用 ORDER BY 子句将查询数据排序后再返回数据： 默认情况ASC表示升序，DESC表示降序 复合排序：对多个字段排序，即当第一排序项相同时按照第二排序项排序 限制 LIMIT 子句用于限制由 SELECT 语句返回的数据数量 或者 U
mysql高级查询~分页查询
伟庭的博客
01-01 6083
mysql高级查询~分页查询
31-视频32MySQL高级查询
09-13
31--[视频]32MySQL高级查询
MySql高级Sql查询
12-31
MySql高级Sql查询语句，整套开发视频源码以及开发文档。
MySQL-索引：聚集索引、覆盖索引、组合索引、前缀索引、唯一索引
睡不醒的每天
06-25 566
这意味着表中的行是按id的升序物理存储的，并且每个叶子节点包含了完整的行数据（包括username, email, created_at）。当你执行如下查询时，同样以上述的idx_username_email为例，它是一个基于username和email两个列创建的组合索引。在users表中，id列已经是一个主键，自动带有唯一约束。不仅包括查询列，还包括结果集中所需的其他列（如在SELECT列表中的列）。，无需回表获取其他列数据，这就是一个覆盖索引的例子。在数据库中，聚集索引决定了表数据行的物理存储顺序。
dataX同步SQLserver到MySQL数据
goodlook0123的专栏
06-28 724
DataX本身作为数据同步框架，将不同数据源的同步抽象为从源头数据源读取数据的Reader插件，以及向目标端写入数据的Writer插件，理论上DataX框架可以支持任意数据源类型的数据同步工作。同时DataX插件体系作为一套生态系统, 每接入一套新数据源该新加入的数据源即可实现和现有的数据源互通。
mysql的information_schema浅析
liulanba的博客
06-24 669
information_schema 是 MySQL 中的一个虚拟数据库，它包含了关于 MySQL 服务器的所有元数据。
MySQL中ALTER LOGFILE GROUP 语句详解
u011565038的博客
06-25 669
在 MySQL 的 InnoDB 存储引擎中，ALTER LOGFILE GROUP语句用于修改重做日志组（redo log group）的配置。重做日志是 InnoDB 用来保证事务持久性的一个关键组件，它们用于在系统崩溃后恢复数据。
MySQL 5.x和8.0区别
qq984676583的博客
06-25 364
在8.0版本中，索引可以被“隐藏”和“显示”。：8.0大幅改进了对JSON的支持，添加了其基于路径查询参数从JSON字段中抽取数据的JSON_EXTRACT()函数，以及用于数据分别组合到JSON数据和对象中的JSON_ARRAYAGG()和JSON_OBJECTAGG()聚合函数。：InnoDB现在支持表DDL的原子性，也就是InnoDB表上的DDL也可以实现事务完整性，要么失败回滚，要么成功提交，不至于出现DDL时部分成功的问题，此外还支持crash-safe特性，元数据存储在单个事务数据字典中。
MySQL——联表查询JoinON详解
chenyang_88的博客
06-25 284
【代码】MySQL——联表查询JoinON详解。
MySQL1（初始数据库 概念 DDL建库建表 数据库的三大范式 表约束）
qq_52897007的博客
06-24 1136
为了建立冗余较小、结构合理的数据库，设计数据库时必须遵循一定的规则。在关系型数据库中这种规则就叫做范式。三大范式只是一般设计数据库的基本理念，可以建立冗余较小、结构合理的数据库。如果有特殊情况，当然要特殊对待，数据库设计最重要的是看需求跟性能，需求>性能>表结 构。所以不能一味的去追求范式建立数据库。
MySQL期末答辩—仓库管理系统
qq_67222065的博客
06-28 246
【代码】MySQL期末答辩—仓库管理系统。
酒店客房管理系统（Java+MySQL）
最新发布
weixin_44107140的博客
06-29 295
例如，绿色表示可用，红色表示已入住，黄色表示预订，灰色表示维护中。通过颜色标记，管理员可以快速了解客房状态，提高管理效率。管理员可以通过界面记录客人的入住和退房信息，包括入住时间、退房时间、客人姓名、联系方式等。系统自动更新客房状态，确保客房信息实时准确。管理员可以通过图形界面添加、修改和删除客房信息，包括客房类型、价格、容量、描述等。系统提供客房统计功能。管理员可以查看客房的使用情况统计，包括入住率、退房率、预订率等。通过用户名和密码验证身份，确保只有授权的用户可以访问和管理酒店客房信息，登录界面友好。
mysql 高级查询
07-29
你好！关于 MySQL 的高级查询，有很多不同的方面和技巧。你可以提供更具体的问题或者告诉我你对高级查询的具体需求，这样我能够给你更准确的帮助。一般来说，MySQL 的高级查询包括以下几个方面： 1. 多表联接：使用 JOIN 语句将多个表关联起来，可以根据共同的字段将数据连接在一起。 2. 子查询：在查询中嵌套另一个查询，用于获取更复杂的数据结果。 3. 聚合函数：例如 SUM、COUNT、AVG 等函数，用于计算数据的总和、数量、平均值等。 4. 分组和排序：使用 GROUP BY 对查询结果进行分组，并使用 ORDER BY 对结果进行排序。 5. 条件查询：使用 WHERE 子句根据条件过滤数据，可以使用比较运算符、逻辑运算符等。 6. 使用子句：例如 HAVING 子句用于对分组后的结果进行过滤，LIMIT 子句用于限制查询结果的数量等。 这些只是高级查询的一部分，如果你有具体的问题，请告诉我，我将尽力为你解答。

“相关推荐”对你有帮助么？

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值