MySQL 学习笔记——子查询、分页查询、连接查询sql99

最新推荐文章于 2021-11-11 19:18:23 发布
最新推荐文章于 2021-11-11 19:18:23 发布
阅读量195 收藏 2
点赞数
分类专栏： 笔记 文章标签： mysql sql
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/i_am_handsome_/article/details/109300719
版权

MySQL 数据库管理系统学习Day03

DQL语言的学习

多表连接查询 SQL99语法

sql99语法：通过join关键字实现连接
含义：1999年推出的sql语法

支持：

  1. 内连接:等值连接、非等值连接 、自连接
  2. 外连接：左外连接、右外连接
  3. 交叉连接

语法：
SELECT 字段，…
FROM 表1
【INNER|LEFT|RIGHER|CROSS】JOIN 表2
ON 连接条件
【INNER|LEFT|RIGHER|CROSS】JOIN 表3
ON 连接条件
【WHERE 筛选条件】
【GROUP BY分组字段】
【HAVING 分组后的筛选条件】
【ORDER BY排序的字段或表达式】

好处：语句上，连接条件和筛选条件实现了分离，简洁明了！

内连接

特点：

  1. 添加排序、分组、筛选
  2. inner可以省略
  3. 筛选条件放在where后面，连接条件放在on后面，提高分离性，便于阅读
  4. inner join连接和sql92语法中的等值连接效果是一样的，都是查询多表的交集
#一）等值连接
#案例：查询哪个部门的员工个数>3的部门名和员工个数，并按个数降序（添加排序）
#①查询每个部门的员工个数
SELECT COUNT(*),e.department_id
FROM employees e
INNER JOIN departments d
ON e.`department_id` = d.`department_id`
GROUP BY department_id;

#②在①结果上筛选员工个数>3的记录并按个数排序
SELECT COUNT(*),e.department_id,department_name
FROM employees e
INNER JOIN departments d
ON e.`department_id` = d.`department_id`
GROUP BY department_id
HAVING COUNT(*) > 3
ORDER BY COUNT(*) DESC;

#二）非等值连接
#查询每个工资级别的个数>20的个数，并且按工资级别降序
SELECT COUNT(*),salary,grade_level
FROM employees e
JOIN job_grades g
ON e.`salary` BETWEEN g.`lowest_sal` AND g.`highest_sal`
GROUP BY grade_level
HAVING COUNT(*) > 20
ORDER BY grade_level DESC;

#三）自连接
#案例：查询员工名和直接上级的名称
#sql99
	SELECT e.last_name,m.last_name
	FROM employees e
	JOIN employees m ON e.`manager_id`=m.`employee_id`;
	
#sql92	
	SELECT e.last_name,m.last_name
	FROM employees e,employees m 
	WHERE e.`manager_id`=m.`employee_id`;

外连接

应用场景：用于查询一个表中有,另一个表中没有的记录
特点：

  1. 外连接的查询结果为主表中的所有记录
    如果从表中有和它匹配的，则显示匹配的值
    如果总表中没有和它匹配的，则显示null
    外连接查询结果=内连接结果+主表中有而从表中没有的记录
  2. 左外连接，left join左边的是主表
    右外连接，right join右边的是主表
  3. 左外和右外交换两个表的顺序，可以实现同样的效果
  4. 全外连接=内连接的结果+表1中有但表2没有的+表2中有但表1没有的
#案例1：查询哪个部门没有员工
#左外
USE myemployees;
SELECT d.*,e.employee_id
FROM departments d
LEFT OUTER JOIN employees e
ON d.`department_id`=e.`department_id`
WHERE employee_id IS NULL;


#右外
SELECT d.*,e.employee_id
FROM employees e
RIGHT OUTER JOIN departments d
ON d.`department_id`=e.`department_id`
WHERE employee_id IS NULL;

#全外
#MySQL不支持全外 看看就行
SELECT b.*,bo.*
FROM beauty b
FULL OUTER JOIN boys bo
ON bo.id = b.`boyfriend_id`; 

#交叉连接
#就是一个笛卡尔乘积
SELECT b.*,bo.*
FROM beauty b
CROSS JOIN boys bo;

sql92和sql99的对比：
功能：sql99支持的较多
可读性：sql99实现连接条件和筛选条件的分离，可读性较高

子查询

含义：
一条查询语句中又嵌套了另一条完整的select语句，其中被嵌套的select语句，称为子查询或内查询

在外面的查询语句，称为主查询或外查询

分类：

按子查询出现的位置：
select后面：
仅仅支持标量子查询
from 后面：
支持表子查询
where或having后面：★
标量子查询（单行） ✔
列子查询 （多行） ✔
行子查询
exists后面（相关子查询）：
表子查询

按结果集的行列数不同：
标量子查询（结果集只有一行一列）
列子查询（结果集只有一列多行）
行子查询（结果集有一行多列）
表子查询（结果集一般为多行多列）

特点：

  1. 子查询都放在小括号内
  2. 子查询可以放在from后面、select后面、where后面、having后面，但一般放在条件的右侧
  3. 子查询优先于主查询执行，主查询使用了子查询的执行结果
  4. 子查询根据查询结果的行数不同分为以下两类：
    4.1 单行子查询
    结果集只有一行
    一般搭配单行操作符使用：> < = <> >= <=
    非法使用子查询的情况：
    a、子查询的结果为一组值
    b、子查询的结果为空
    4.2 多行子查询
    结果集有多行
    一般搭配多行操作符使用：any、all、in、not in
    in： 属于子查询结果中的任意一个就行
    any和all往往可以用其他查询代替
#一、WHERE后面的子查询
#1.标量子查询
#案例：返回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
);

#非法使用标量子查询  -->子查询的结果不是一行一列
SELECT MIN(salary)
FROM employees
GROUP BY department_id
HAVING MIN(salary) > (
	SELECT salary #不能多行
	FROM employees
	WHERE department_id = 50
);

#2.列子查询（多行子查询）
#案例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 last_name
FROM employees
WHERE department_id =ANY(
	SELECT DISTINCT department_id
	FROM departments
	WHERE location_id IN(1400,1700)
);


# not in ---> <> all
SELECT last_name
FROM employees
WHERE department_id <>ALL(
	SELECT DISTINCT department_id
	FROM departments
	WHERE location_id IN(1400,1700)
);

#案例2：返回其他工种中比job_id为'IT_PROG'工种任一工资低的员工的员工号、姓名、job_id、以及salary

#①查询job_id为'IT_PROG'部门任一工资
SELECT  DISTINCT salary
FROM employees
WHERE job_id = 'IT_PROG';

#②查询员工号、姓名、job_id、以及salary  salary< ①中的任意一个
SELECT last_name,employee_id,job_id,salary
FROM employees
WHERE salary < ANY(
	SELECT DISTINCT salary
	FROM employees
	WHERE job_id = 'IT_PROG'

)
AND job_id <> 'IT_PROG';

#或
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';


#案例3：返回其他部门中比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';

#或
SELECT last_name,employee_id,job_id,salary
FROM employees
WHERE salary < (
	SELECT MIN( salary)
	FROM employees
	WHERE job_id = 'IT_PROG'

)
AND job_id <> 'IT_PROG';


#3、行子查询（结果集一行多列或多行多列）

#案例：查询员工编号最小并且工资最高的员工信息
SELECT *
FROM employees
WHERE (employee_id,salary)=(#要求筛选条件都用一个筛选符号
	SELECT MIN(employee_id),MAX(salary)
	FROM employees

);


#①查询最小的员工编号
SELECT MIN(employee_id)
FROM employees;

#②查询最高工资
SELECT MAX(salary)
FROM employees;

#③查询员工信息
SELECT *
FROM employees
WHERE employee_id = (
	SELECT MIN(employee_id)
	FROM employees
)AND salary = (
	SELECT MAX(salary)
	FROM employees
);

#二、select后面
/*

仅仅支持标量子查询一行一列
*/

#案例：查询每个部门的员工个数

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

#案例2：查询员工号=102的部门名
SELECT (
	SELECT department_name
	FROM departments d
	INNER JOIN employees e
	ON  e.department_id = d.department_id
	WHERE e.employee_id = 102

)部门名;

#三、from后面
/*
将子查询结果充当一张表，要求必须起别名
*/
#案例：查询每个部门的平均工资的工资等级
#①查询每个部门的平均工资
SELECT AVG(salary),department_id
FROM employees
GROUP BY department_id;

SELECT * FROM job_grades;

#②连接①的结果集和job_grades表，筛选条件平均工资 between Lowes_sal, and highest_sal
SELECT ag_dep.*,g_dep.grade_level
FROM (
	SELECT AVG(salary) ag,department_id
	FROM employees
	GROUP BY department_id
)ag_dep
INNER JOIN job_grades g_dep
ON ag_dep.ag BETWEEN g_dep.`lowest_sal` AND g_dep.`highest_sal`;


#四、exists后面（相关子查询）1代表true 0 代表false
/*
语法：
exists（完整的查询语句）
结果：
1或0
*/
SELECT EXISTS(SELECT employee_id FROM employees WHERE  salary = 99999);

#案例1：查询有员工的部门名  

#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`
);

#案例2：查询没有女朋有的男神信息
#in
SELECT bo.*
FROM boys bo
WHERE bo.id NOT IN (
	SELECT boyfriend_id
	FROM beauty
);

#exists
SELECT bo.*
FROM boys bo
WHERE NOT EXISTS (
	SELECT boyfriend_id
	FROM beauty b
	WHERE bo.`id`= boyfriend_id
);

分页查询

应用场景：
实际的web项目中需要根据用户的需求提交对应的分页查询的sql语句
语法：
select 字段|表达式,…
from 表1
inner left right outer join 表2
on 连接条件
【where 条件】
【group by 分组字段】
【having 条件】
【order by 排序的字段】
limit 【起始的条目索引，】条目数;
执行顺序：
from - join - on - where - group by - having - select - order by - limit
特点：

  1. 起始条目索引从0开始
  2. limit子句放在查询语句的最后
  3. 公式：
    select *
    from 表
    limit （page-1）*sizePerPage , sizePerPage;

注：
每页显示条目数sizePerPage
要显示的页数 page

#案例1：查询前五条员工信息
SELECT * FROM employees LIMIT 0,5;
SELECT * FROM employees LIMIT 5;

#案例2：查询第11条--第25条
SELECT * FROM employees LIMIT  10,15;

#案例3：有奖金的员工信息，并且工资较高的前10名显示出来
SELECT 
  * 
FROM
  employees 
WHERE commission_pct IS NOT NULL 
ORDER BY salary DESC 
LIMIT 10 ;
i_am_handsome_
关注
  • 0
    点赞
  • 踩
  • 2
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
专栏目录
在JPA的@Query注解中使用limit条件(详解)
08-30
下面小编就为大家带来一篇在JPA的@Query注解中使用limit条件(详解)。小编觉得挺不错的，现在就分享给大家，也给大家做个参考。一起跟随小编过来看看吧
jpa limit查询_如何对JPA查询进行分页
weixin_36051940的博客
01-12 267
I have a submission table with columns like ID, Name, Code among other properties. My requirement is to search for records based on the mentioned properties and return a paginated set.This is the pseu...
python数据保存到mongodb
weixin_44015047的博客
09-14 1397
本文主要介绍MongoDB和python的连接，将python爬取的数据保存到MongoDB中。 以虎扑网为例，爬取网站的文章标题，连接，作者等信息，并保存到MongoDB中。 导入相关python库 import requests from bs4 import BeautifulSoup import time import random from pymongo import MongoClient import MySQLdb 定义方法来获取当前网页的所有文章信息 def get_informat
jpa limit查询_spring data jpa 分页查询
weixin_39802020的博客
12-21 489
法一(本地sql查询,注意表名啥的都用数据库中的名称，适用于特定数据库的查询)public interface UserRepository extends JpaRepository{@Query(value= "SELECT * FROM USERS WHERE LASTNAME = ?1",countQuery= "SELECT count(*) FROM USERS WHERE LASTN...
QuerySyntaxException unexpected token: limit JPA分页分页查询
ljwaheng的博客
04-20 1596
第一次写java，总结了一点经验我以为分页是直接写sql语句，然后执行sql语句，但是 抛出QuerySyntaxException unexpected token: limit错误，然后百度发现，JPQL里面没有limit的，使用Hibernate的setFirstResult,setMaxResults实现数据库分页 ，列如query.setFirstResult(0),query.setM...
四、MySQL分页查询 + 子查询复习 学习笔记 （复习连接查询相关内容 详解）
12-14
应用场景：当要显示的数据，一页显示不全，需要分页提交sql请求 语法： SELECT 查询列表 FROM 表名 【JOIN type JOIN 表2 ON 连接条件 WHERE 筛选条件 GROUP BY 分组字段 HAVING 分组后的筛选 ORDER BY 排序的字段】...
08mysql连接查询(sql99)
01-16
这是我自己学习mysql时的学习笔记，每一个知识点都是自己动手写的，涵盖了mysql中的几乎全部的基础知识点，查询，子查询，分组，排序，常用函数，多表连接，视图，变量，存储过程，函数，分支结构，循环结构等等
MySQL笔记之子查询使用介绍
09-10
子查询是将一个查询语句嵌套在另一个查询语句中,内层查询语句的查询结果，可以为外层查询语句提供查询条件
python 连接mysql批量sql查询并导出成excel.zip
最新发布
10-08
将配置的所有sql 循环在每一个配置号的mysql连接-库中执行，并分别为每一个连接生成一张标，一个查询sql的结果为1个sheet页 用法： 1. 配置多个Mysql连接、库、文件名 2. 配置SavePath存储路径 3. 配置sql， 格式为...
ORACLE、MYSQL、SQLSERVER数据分页查询
12-14
看过此博文后Oracle、MySql、SQLServer 数据分页查询，在根据公司的RegionRes表格做出了 　SQLserver的分页查询语句： SELECT DISTINCT TOP 500 rr.ID AS id ，rr.strName AS name ，rr.nType AS res_type ，...
JPA Specification
02-05
persistence-3_0-pfd-spec.pdf JPA Specification
MySQL学习笔记5（连接查询及子查询）
Li的博客
07-22 240
非等值查询、连接、子查询一、非等值查询二、连接1、内连接2、外链接3、全外连接和自连接三、子查询1、子查询2、单行子查询3、多行子查询 一、非等值查询 #案例1：查询名字中第三个字符为a，第五个字符为e的员工的工资以及对应的工资级别 SELECT salary,grade_level FROM employees e,job_grades g WHERE e.salary BETWEEN g.lowest_sal ANDg.highest_sal AND e.last_name LIKE ‘__a_e%’
JPA Hibernate HQL limit 分页写法 Pageable PageRequest
wuyujin1997的博客
11-11 1828
Pageable 和 PageRequest 传参解决 要用到的两个API： org.springframework.data.domain.Pageable; org.sprineframework.data.domain.PapeRequest; StudentRepository.java import com.wuyujin.model.entity.Student; import org.springframework.data.domain.Pageable; import org.sprin
jpa limit查询_JPA Specification复杂查询及排序
weixin_39540934的博客
12-21 1435
一、前言刚使用spring-data-jpa,遇到不少难题，网上查了很多资料，发现讲jpa的不多，发个我刚做过的接口的过程吧。二、需求看到图了吗？需要实现搜索以及各种字段的排序还要分页，还有可能有选择各种条件的下拉列表，是不是很变态？三、开始了一、dao需要先处理dao层，这里喜欢叫repository。做一个实体类的dao层接口，继承JpaSpecificationExecutor，再写一个查询...
Java - JPA通过Query接口setMaxResults实现limit限制查询条数
Zebe
01-29 9408
实现JPA的限制查询功能 要实现JPA的limit限制查询功能，可以通过 JPA Query 接口中的 setMaxResults 方法来实现指定最大结果返回多少条。如果不通过 setMaxResults 设置最大返回条数，JPA默认最大返回 Integer 的上限，即 Integer.MAX_VALUE，这个数字是21亿左右。 可以参考这个官方文档地址：https://docs.oracle....
jpa limit查询_jpa使用自定义查询条件
weixin_34950302的博客
02-17 714
意思就是jpa中使用类似like(模糊查询)和 limit (分页)查询结合，通过以下例子解析：可理解为like，实际效率是比模糊查询like效率高的建表：sqlcreate tableorder_info(order_idvarchar(30) not null,source_idvarchar(30),serial_novarchar(30),source_systemvarchar(20),...
jpa Specification使用
莹未来
08-08 8571
spring jpa Specification 使用本文只是做个笔记，备忘
SpringDataJpa之强大的手动分页查询(封装与技巧)
热门推荐
★【World Of Moshow 郑锴】★
06-12 1万+
背景 JPA对于简单类的查询还是不错的，但通常业务场景不会那么简单，必须用到sql语句来组成一些查询，这个时候我们最好就用手动分页了。 实体类 这是一个员工的实体，即将对他进行操刀 @Entity @NamedQuery(name=&amp;quot;Employee.findAll&amp;quot;, query=&amp;quot;SELECT e FROM Employee e&amp;quot;) public class Employee im...
IDEA读取.properties配置文件的路径
i_am_handsome_的博客
11-06 4006
不同情况下读取.properties配置文件的默认路径Junit中读取main方法中读取 Junit中读取 类的加载器(ClassLoader)获取的流默认路径是： 当前Module的src目录下 普通输入流获取的默认路径是： 当前Module下 public class ClassLoaderTest { @Test public void test2() throws IOException { Properties pros = new Properties();
mysql连接查询和子查询
08-30
MySQL中的连接查询和子查询都是用于多个表之间的数据查询，但它们有一些不同之处。 连接查询是通过使用JOIN关键字将多个表连接在一起，通过共享列的值来检索相关的数据。连接查询可以根据连接条件返回两个或多个表中的匹配行。连接查询可以进一步划分为内连接、左连接、右连接和全外连接，每种连接类型的结果集不同。 下面是一个连接查询的示例： ```sql SELECT * FROM table1 JOIN table2 ON table1.id = table2.id; ``` 子查询是一个查询嵌套在另一个查询中，用于在主查询中使用子查询的结果。子查询可以返回单个值、一列或多列数据。子查询可以嵌套到多个级别，可以用于WHERE子句、FROM子句、SELECT子句等。 下面是一个子查询的示例： ```sql SELECT * FROM table1 WHERE table1.id IN (SELECT id FROM table2); ``` 连接查询和子查询在功能上可以实现相同的结果，但在性能和可读性上有一些差异。通常情况下，连接查询更适合于关联较大的表，而子查询更适合于较小的数据集。 希望这个回答对你有所帮助！如果你有任何其他问题，请随时问我。

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

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

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

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

抵扣说明：

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

余额充值