MySQL数据库学习（4）

于 2022-12-09 20:53:21 发布
阅读量482 收藏
点赞数
文章标签： 数据库 mysql 学习
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/maelee/article/details/128106131
版权

Day4

一.排序

当我们使用普通查询语句的时候可以看到，每次查询出的结果都是相似的

SELECT * FROM employees;

在这里插入图片描述
当我们没有使用排序操作，默认查询返回的数据就是按照添加数据的顺序。

1.1排序规则&单列排序

  • 使用 ORDER BY 子句排序
    • ASC（ascend）: 升序(默认是升序)
    • DESC（descend）:降序
  • ORDER BY 子句在SELECT语句的结尾。
# 5.1.1排序规则：ORDER BY(升序，降序)默认升序排列
SELECT employee_id,last_name,salary
FROM employees
ORDER BY salary DESC;

在这里插入图片描述

# 使用别名进行排序
SELECT employee_id,salary,salary *12 annua_sal
FROM employees
ORDER BY annua_sal;

在这里插入图片描述

注意：
（1）列的别名只能在ORDER BY中使用，不能在WHERE中使用，这和SQL语句执行顺序有关，SQL语句执行时，并不是顺序执行，它先从FROM语句开始执行，在执行WHERE等查询操作，此时无法识别别名，筛选出数据；再执行SELECT操作，筛选出字段，最后再执行ORDER BY操作。
（2）WHERE需要声明在FROM后，ORDER BY前

1.2多列排序

# 5.2 二级排序
SELECT employee_id,salary,department_id
FROM employees
ORDER BY department_id DESC,salary ASC;

可以看出同id下，salary按升序排列
在这里插入图片描述

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

二.分页

2.1背景

背景1：查询返回的记录太多了，查看起来很不方便，怎么样能够实现分页查询呢？
背景2：表里有 4 条数据，我们只想要显示第 2、3 条数据怎么办呢？

2.2规则

分页原理：所谓分页显示，就是将数据库中的结果集，一段一段显示出来需要的条件。
  • 代码：
LIMIT 起始位置偏移量,显示的行数
LIMIT 0,显示行数 = LIMIT 显示行数

# 5.2 分页
# 每页显示二十条记录
SELECT employee_id,last_name
FROM employees
LIMIT 0,20;

在这里插入图片描述

# 每页显示二十条记录，第二页
SELECT employee_id,last_name
FROM employees
LIMIT 20,20;

在这里插入图片描述

分页显式公式：（当前页数-1）*每页条数，每页条数

SELECT * FROM table
LIMIT(PageNo - 1)*PageSize,PageSize;
5.2.2 WHERE,ORDER BY,LIMIT顺序
# 5.2.2 WHERE,ORDER BY,LIMIT
SELECT employee_id,last_name,salary
FROM employees
WHERE salary>6000
ORDER BY salary DESC
LIMIT 0,10;

在这里插入图片描述

注意：

（1）使用 LIMIT 的好处：约束返回结果的数量可以 减少数据表的网络传输量 ，也可以 提升查询效率 。如果我们知道返回结果只有1 条，就可以使用 LIMIT 1 ，告诉 SELECT 语句只需要返回一条记录即可。这样的好处就是 SELECT 不需要扫描完整的表，只需要检索到一条符合条件的记录即可返回。
（2）8.0新特性

# 表中有107条数据，只显示32,33条
# SELECT employee_id,last_name
# FROM employees
# LIMIT 31,2;
# 5.2.3 MySQL8.0新特性：LIMIT 显示条数 OFFSET 起始偏移量
SELECT employee_id
FROM employees
LIMIT 2 OFFSET 31;

（3）在不同的 DBMS 中使用的关键字可能不同。在 MySQL、PostgreSQL、MariaDB 和 SQLite 中使用 LIMIT 关键字，而且需要放到 SELECT 语句的最后面。

三.多表查询

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

1.案例说明

# 6.1 案例引入
# 这里DESC是description(描述)的缩写,可以查看对表的描述(descend降序)
DESC employees;
DESC departments;
DESC locations;

在这里插入图片描述
例：查询员工名‘Abel’在哪个城市工作，通过每个表每个表的查，可以查到，但是查询需要三次请求，过于麻烦。

# 查询员工名‘Abel’在哪个城市工作
SELECT *
FROM employees
WHERE last_name = 'Abel';

SELECT *
FROM departments
WHERE department_id = 80;

SELECT *
FROM locations
WHERE location_id = 2500;

在这里插入图片描述

解决这个问题有两个思路：
（1）能否把需要查询的这三张表合成为一张表？
会造成数据冗余，导致从磁盘中加载到内存中时间过长，不利于高并发场景。
（2）利用多表查询
使用多表查询

SELECT last_name,employees.department_id,departments.location_id，city,street_address
FROM employees,departments,locations
WHERE last_name = 'Abel'&&employees.department_id = departments.department_id&&departments.location_id = locations.location_id;

在这里插入图片描述

2.多表查询

SELECT employee_id,department_name
FROM employees,departments;

查询出了2889条记录，但是员工只有107条记录，发现一个员工对应了很多条部门，查询出现错误，经过分别对employee_id,department_name查询发现employee_id有107条记录，department_name有27条记录，等于说是每个员工都与每个部门匹配了一遍，出现了笛卡尔积的错误。
在这里插入图片描述

2.1 笛卡尔积（或交叉连接）的理解

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

SQL92中，笛卡尔积也称为 交叉连接 ，英文是 CROSS JOIN 。在 SQL99 中也是使用 CROSS JOIN表示交叉连接。它的作用就是可以把任意表进行连接，即使这两张表不相关。在MySQL中如下情况会出现笛卡尔积：

SELECT last_name,department_name 
FROM employees,departments;

SELECT last_name,department_name 
FROM employees CROSS JOIN departments;

SELECT last_name,department_name 
FROM employees INNER JOIN departments;

SELECT last_name,department_name 
FROM employees JOIN departments;
  • 笛卡尔积的错误会在下面条件下产生：
    • 省略多个表的连接条件（或关联条件）
    • 连接条件（或关联条件）无效
    • 所有表中的所有行互相连接
  • 为了避免笛卡尔积， 可以在 WHERE 加入有效的连接条件。
  • 加入连接条件后，查询语法：
SELECT table1.column, table2.column
FROM table1, table2
WHERE table1.column1 = table2.column2; #连接条件

#案例：查询员工的姓名及其部门名称
SELECT last_name, department_name
FROM employees, departments
WHERE employees.department_id = departments.department_id;
2.2 多表查询的正确方式
  • 需要有连接
  • 在表中有相同列时，在列名之前加上表名前缀。
# 6.2.2 多表查询的正确方式,需要有连接条件
SELECT employee_id,employees.department_id
FROM employees,departments
WHERE employees.department_id = departments.department_id;

总共106条记录，因为有一个人部门ID为NULL，没有查询出来
在这里插入图片描述

# 使用表的别名(列的别名不能在WHERE中出现)：若果给表起了别名，那么一旦使用表名，必须用别名
SELECT t1.employee_id,t2.department_name,t1.department_id
FROM employees t1,departments t2
WHERE t1.department_id = t2.department_id;

# 查询员工的employee_id last_name department_name city
SELECT employees.employee_id,employees.last_name,departments.department_name,locations.city
FROM employees,departments,locations
WHERE employees.department_id = departments.department_id && departments.location_id = locations.location_id;
注意：
（1）如果给表起了别名，那么一旦使用表名，必须用别名；
（2）要实现n表查询，则至少需要n-1个连接条件；
2.3 多表查询分类讲解
  • 分类1. 等值连接 & 非等值连接
    • 等值连接
      在这里插入图片描述
    • 非等值连接
# 6.3.1 针对连接条件：等值连接 & 非等值连接
# 上面的查询都是等值连接
# 非等值连接
SELECT employees.last_name,employees.salary,job_grades.grade_level
FROM employees,job_grades
WHERE employees.salary BETWEEN job_grades.lowest_sal AND job_grades.highest_sal;

在这里插入图片描述

  • 分类2.自连接 & 非自连接

前面的不同表之间的连接查询都是非自连接，自己和自己连接查询的为自连接

# 6.3.2 自连接 & 非自连接
# 前面的不同表之间的连接查询都是非自连接，自己和自己连接查询的为自连接
# 查询员工姓名及其ID与其管理者ID姓名
SELECT emp.employee_id,emp.last_name,mgr.employee_id,mgr.last_name
FROM employees emp,employees mgr
WHERE emp.manager_id = mgr.employee_id;

在这里插入图片描述

  • 分类3. 内连接 & 外连接
（1）内连接： 合并具有同一列的两个以上的表的行, 结果集中不包含一个表与另一个表不匹配的行。

在这里插入图片描述

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

在这里插入图片描述
如果是左外连接，则连接条件中左边的表也称为 主表 ，右边的表称为 从表 。
如果是右外连接，则连接条件中右边的表也称为 主表 ，左边的表称为 从表 。

（3）SQL92,SQL99语法规则
  • SQL92语法:SQLyog,Navicat不支持SQL92,但是ORCL支持
    • 内连接：见上
    • 外连接： 使用 ‘ + ’
#左外连接
SELECT employee_id,last_name,department_name
FROM employees,departments
WHERE employees.department_id = departments.department_id(+);	#employees.employee_id多一个，所有右表增加

#右外连接
SELECT last_name,department_name
FROM employees ,departments
WHERE employees.department_id(+) = departments.department_id;
  • SQL99：使用JOIN…ON子句创建连接的语法结构，既可以处理外连接也可以处理内连接
# SQL99语法标准：
# 内连接：不加INNER默认内连接
SELECT last_name,department_name
FROM employees e INNER JOIN departments d
ON e.department_id = d.department_id;

SELECT last_name,department_name,city
FROM employees e JOIN departments d
ON e.department_id = d.department_id
JOIN locations l
ON d.location_id = l.location_id;

# 外连接
# 左外连接
SELECT last_name,department_name
FROM employees e LEFT OUTER JOIN departments d
ON e.department_id = d.department_id;
# 右外连接
SELECT last_name,department_name
FROM employees e RIGHT OUTER JOIN departments d
ON e.department_id = d.department_id;

左外连接：107条记录
在这里插入图片描述

在这里插入图片描述

右外连接：122条记录
在这里插入图片描述

在这里插入图片描述

满外连接：

# 满外连接:MYSQL不支持FULL OUTER JOIN
SELECT last_name,department_name
FROM employees e FULL OUTER JOIN departments d
ON e.department_id = d.department_id;

mysq不支持，ORCL支持
在这里插入图片描述

SELECT last_name,department_name
FROM employees e LEFT OUTER JOIN departments d
ON e.department_id = d.department_id
UNION ALL
SELECT last_name,department_name
FROM employees e RIGHT OUTER JOIN departments d
ON e.department_id = d.department_id
WHERE e.department_id IS NULL;
UNION操作符

在这里插入图片描述

7种JOIN实现
# 6.4 7种join实现
SELECT employee_id,department_name
FROM employees e JOIN departments d
ON e.department_id  = d.department_id;

SELECT employee_id,department_name
FROM employees e LEFT OUTER JOIN departments d
ON e.department_id = d.department_id;

SELECT employee_id,department_name
FROM employees e RIGHT OUTER JOIN departments d
ON e.department_id = d.department_id;
# 过滤掉中间相同的
SELECT employee_id,department_name
FROM employees e LEFT JOIN departments d
ON e.department_id = d.department_id
WHERE d.department_id IS NULL;

SELECT employee_id,department_name
FROM employees e RIGHT JOIN departments d
ON e.department_id = d.department_id
WHERE e.department_id IS  NULL;
#满外连接另一种写法
SELECT employee_id,department_name
FROM employees e LEFT JOIN departments d
ON e.department_id = d.department_id
WHERE d.department_id IS NULL
UNION ALL
SELECT employee_id,department_name
FROM employees e RIGHT JOIN departments d
ON e.department_id = d.department_id;

SELECT employee_id,department_name
FROM employees e LEFT JOIN departments d
ON e.department_id = d.department_id
WHERE d.department_id IS NULL
UNION ALL
SELECT last_name,department_name
FROM employees e RIGHT OUTER JOIN departments d
ON e.department_id = d.department_id
WHERE e.department_id IS NULL;

3.SQL99语法新特性

3.1 自然连接

SQL99 在 SQL92 的基础上提供了一些特殊语法，比如 NATURAL JOIN 用来表示自然连接。我们可以把自然连接理解为 SQL92 中的等值连接。它会帮你自动查询两张连接表中 所有相同的字段 ，然后进行等值连接 。

SELECT employee_id,last_name,department_name
FROM employees e JOIN departments d
ON e.`department_id` = d.`department_id`
AND e.`manager_id` = d.`manager_id`;

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

在这里插入图片描述

这两个执行结果相同，自然连接不够灵活。

3.2 USING连接

当我们进行连接的时候，SQL99还支持使用 USING 指定数据表里的同名字段进行等值连接。但是只能配合JOIN一起使用。比如：

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

在这里插入图片描述
不适用于自连接

垃圾桶里也挺好
关注
MySQL数据排序asc、desc
Jason的博客
03-25 35万+
数据排序 asc、desc 1、单一字段排序order by 字段名称 作用： 通过哪个或哪些字段进行排序 含义： 排序采用 order by 子句，order by 后面跟上排序字段，排序字段可以放多个，多个采用逗号间隔，order by默认采用升序（asc），如果存在 where 子句，那么 order by 必须放到where 语句后面。 （1）、按照薪水由小到大排序（系统默认由小到...
2. mysql基本查询
借风拥你
01-02 2304
一、演示表介绍 1.employees员工表 字段名称 字段含义 employee_id 员工编号 first_name 名 last_name 姓 email 邮箱 phone_number 电话号码 job_id 工种编号 salary 工资 commission_pct 奖金率 manager_id 上级领导的编号 department_id 部门编号 hiredate 入职日期 2.departments部门表 字段名称 字段含义
数据库—查询语句中asc、desc表示什么意思？
知名互联网小白的博客
09-23 4万+
asc是升序，就是这个值按照从小到大进行排序；desc是降序，从大到小显示。 数据库中使用order by语句进行排序，其中升序用asc，降序用desc。 如: select * from t order by 列a asc 这是升序 select * from t order by 列a desc 这是降序 ...
MySQL中的外键
春水碧于天的博客
04-16 968
MySQL中的外键：外键：外键就是关系数据库的一个基本特征的实现，表明该字段的值是跟外部的某个表的字段的值“对应”的，或者也可以说来自于该外部的某个表的某个字段值，如果给一个设置外键的字段插入一个值，而该值并没有在该外键所指定的外部表中的对应字段中出现，则该值就会插入失败，也就是数据库内部的”约束机制”（使我们的数据更加纯净）下面举个栗子来体会一下外键的作用：比如说我们有一个班级表，当我们在学生表中
MySQL高级SQL语句（上）
gcc001224的博客
06-26 822
通配符主要用于替换字符串中的部分字符，通过部分字符的匹配将相关结果查询出来。 通常通配符都是跟 LIKE 一起使用的，并协同 WHERE 子句共同来完成查询任务。（增、删、改、查）对MySQL数据库的查询，除了基本的查询外，有时间需要对查询结果集进行处理。列如只取10条数据、对查询结果进行排序或分组等等。 查询学生信息按兴趣id降序排列 通过 SQL 查询出来的结果，还可以对其进行分组，使用 GROUP BY 语句来实现 ，GROUP BY 通常都是结合聚合函数一起使用的，常用的聚合函数包括：
注意使用 BTREE 复合索引各字段的 ASC/DESC 以优化 order by 查询效率
Defonds 的专栏
07-02 6194
tbl_direct_pos_201506 表有 190 万数据，DDL：CREATE TABLE `tbl_direct_pos_201506` ( `acq_ins_code` char(13) NOT NULL DEFAULT '' COMMENT '机构代码', `trace_num` char(6) NOT NULL DEFAULT '' COMMENT '跟踪号', `tr
mysql数据库学习手册.docx
07-22
《MySQL学习手册》主要针对Windows平台，深入浅出地介绍了如何管理和使用MySQL数据库系统。以下是对MySQL的安装、Workbench的使用以及基本的数据库操作的详细说明。 1. MySQL的下载与安装： 要安装MySQL，首先访问...
MySQL数据库应用形考实验1-4全答案.zip
12-21
在这个"MySQL数据库应用形考实验1-4全答案.zip"压缩包中，包含了四个关于MySQL基础操作的实验训练，涵盖了从数据库和表的创建，到数据的查询、增删改，以及数据库系统的维护等关键知识点。以下是对这些实验的详细...
国家开放大学 MySQL数据库应用 实验训练4：数据库系统维护
06-05
MySQL 数据库应用实验训练 4：数据库系统维护 本实验训练旨在帮助学生理解视图和索引的概念和作用，掌握视图和索引的基本操作，并且了解它们在数据库系统维护中的应用。 一、视图的概念和作用 视图是一种基于基表...
MYSQL数据库学习手册
04-12
【MySQL数据库学习手册】 在深入探讨MySQL之前，我们先来理解数据库的基础知识。数据库是存储和管理数据的核心工具，它按照数据结构组织信息，扮演着电子化文件柜的角色，允许用户进行添加、删除、修改和查找等操作...
mysql数据库学习与练习
最新发布
03-15
总的来说，“MySQL数据库学习与练习”涵盖了数据库设计、SQL语法、事务处理、性能优化、安全性和数据库管理等多个方面。通过实践这些知识，你可以成为一名熟练的MySQL数据库管理员，有效地处理和管理数据。无论你是...
数据库中desc和asc
Sunny_day_after_day的博客
01-14 5115
desc：将表中的数据按照某列进行降序排列       例 name age salary 独孤九剑 66 11000 李莫愁 45 12030 萧十一郎 35 10240 乔峰 40 15230      select * from table order by salary desc   排序后的表如下： ...
数据库: asc和desc的意思
svygh123的专栏
01-03 2万+
asc 上升, ascend [əˈsend] 的缩写, 数据库表示升序排列,如1,2,3,4 desc 下降, descend[dɪˈsend]的缩写, 数据库表示降序排列,如4,3,2,1
mysql数据库asc_mysql数据库
weixin_36091906的博客
01-30 2212
MySQL数据库的相关概念一、数据库的好处1、可以持久化数据到本地2、结构化查询二、数据库的常见概念1、DB：数据库，存储数据的容器2、DBMS：数据库管理系统，又称为数据库软件或数据库产品，用于创建或管理DB3、SQL：结构化查询语言，用于和数据库通信的语言，不是某个数据库软件特有的，而是几乎所有的主流数据库软件通用的语言三、数据库存储数据的特点1、数据存放到表中，然后表再放到库中2、一个库中可...
Mysql| order by 排序检索数据(ASC,DESC)
YvesHe的专栏
11-12 1万+
在myslq数据中,检索出来的数据往往是以底层数据添加到表中的顺序显示的,但是可能存在更新和删除操作,这样就会影响排序顺序,所有在没有指定排序顺序时从数据中查询的数据是没有排序意义的.这里就需要使用Oder by 子句来实现数据的排序.EG: SELECT * FROM products ORDER BY products.prod_price DESC,products.prod_name
MySQL索引的问题
Ascend2015的博客
04-18 192
关于B树、B+树 我们都知道数据库的索引是基于B树、B+树这样的数据结构，这里并不打算就树的数据结构展开说，我也是一直看一直忘。引用一篇文章B树与B+树，有兴趣的自行了解吧。 MySQL的存储引擎和索引 顺序查找在实际工程中的时间复杂度O(N)是不能容忍的，这是索引产生的原因。如果表中只有一个字段，在这个字段上建立主键，我们可以很轻松地脑补出这个字段关键字存储在B+树上的每个结点。但是如果是多字段...
MySQL问题一 升序和降序
热门推荐
wenteryan的博客
04-12 8万+
MySQL问题一 升序和降序 浅谈MySQL升序和降序,便于我们读取数据。MySQL 排序 我们知道从 MySQL 表中使用 SQL SELECT 语句来读取数据。 如果我们需要对读取的数据进行排序，我们就可以使用 MySQL 的 ORDER BY 子句来设定你想按哪个字段哪中方式来进行排序，再返回搜索结果。 语法讲解 SELECT * FROM table_name ORDER BY f
MySQL 排序
iteye_5722的博客
12-07 146
如果需要对读取的数据进行排序，就可以使用 MySQL 的 ORDER BY 子句来设定你想按哪个字段哪种方式来进行排序，再返回搜索结果。 以下是 SQL SELECT 语句使用 ORDER BY 子句将查询数据排序后再返回数据： SELECT field1, field2,...fieldN table_name1, table_name2... ORDER BY field1,[fiel...
011MySQL子查询 和 多表查询 删除
gh_xiaohe的博客
04-15 2276
常用的 SQL 标准有哪些 笛卡尔积（或交叉连接） 多表查询的好处 多表查询分类讲解 等值连接非等值连接 自连接 vs 非自连接 内连接 vs 外连接 SQL92 SQL99 SQL92：使用(+)创建连接 SQL99语法实现多表查询 7种SQL JOINS的实现 SQL99语法新特性 自然连接 USING连接
深入理解MySQL数据库学习教程
数据库设计是数据库学习的关键部分，包括了解如何设计表结构、字段属性、主键、外键等。掌握使用SQL语句进行数据的增删改查（CRUD）操作是必须的。CRUD分别代表创建(Create)、读取(Read)、更新(Update)和删除(Delete...
评论 1
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

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

抵扣说明：

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

余额充值