第一章、MySql概述
一、数据库概述
1、数据库定义
简单地说，就是软件存储数据的地方。将数据库看成一个楼房，表看成房间，数据看成房间里的物品。mysql的数据，都是放在表格中的。
2、关系型数据库
表格
| name | age | sex | grade | cpu |
| zhangs | 18 | 男 | 90 | null |
| zhaol | 20 | 女 | 90 | null |
| xiaomi | null | null | null | 8核 |
优点：好管理数据，sql语言
缺点：所有的列一致，导致数据容易冗余
3、非关系型数据库
eg:商品
炸鸡 编号 价格 口味 产地
手机 编号 价格 CPU 内存 品牌
{“id”:1,”price”:15,”taste”:”甘梅”,”proc”:”中国”}
{“id”:2,”price”:999,”CPU”:8,”pingpai”:”huawei”}
优点：减少数据冗余，不要求所有字段都保持一致
缺点：不容易管理，重复字段
4、常见的数据库管理系统（BDMS）
常见的数据库管理系统（DBMS）包括以下几种：
-
MySQL：一个开源的关系型数据库管理系统，广泛用于Web应用开发和小型到中型规模的应用。
-
PostgreSQL：也是一个开源的关系型数据库管理系统，它强调对SQL标准的支持和高级特性，适用于复杂的数据需求。
-
Oracle Database：由Oracle公司开发的商业关系型数据库管理系统，适用于大型企业级应用和复杂的数据处理。
-
Microsoft SQL Server：由Microsoft开发的商业关系型数据库管理系统，适用于Windows环境，广泛用于企业级应用。
-
SQLite：一个轻量级的嵌入式数据库管理系统，适用于移动应用和一些小型应用。
-
MongoDB：一个开源的文档型数据库，属于NoSQL数据库类型，适用于处理大量非结构化数据。
-
Redis：一个开源的键值存储数据库，也属于NoSQL数据库类型，适用于高速读写和缓存数据。
-
Microsoft Access：一个桌面级的关系型数据库管理系统，适用于小型应用和个人用户。
-
IBM Db2：一个商业关系型数据库管理系统，适用于大型企业级应用和数据仓库。
-
MariaDB：MariaDB是MySQL的一个分支，也是一个开源的关系型数据库管理系统，与MySQL兼容。
这些数据库管理系统具有不同的特点、优势和适用场景，选择合适的数据库取决于你的应用需求、规模和预算等因素。
二、SQL，DBC，DBMS，DB的区别和联系
1、SQL（Structured Query Language）：
- SQL 是一种用于管理和操作数据库的标准化查询语言。
- 它允许用户对数据库执行各种操作，如查询数据、插入数据、更新数据和删除数据等。
- SQL 是与数据库交互的主要语言，用于向 DBMS 发送数据库操作请求。
2、数据库连接（DBC，Database Connection）：
- DBC 是应用程序与数据库管理系统（DBMS）之间建立的通信通道。
- 它允许应用程序与数据库进行交互，发送 SQL 查询请求和接收查询结果。
- DBC 是应用程序通过 DBMS 访问数据库的桥梁。
3、数据库管理系统（DBMS，Database Management System）：
- DBMS 是一种软件，用于管理数据库的创建、访问、操作和维护。
- 它解释和执行应用程序发送的 SQL 查询，并处理数据库中的数据。
- DBMS 负责数据库的底层细节，包括数据的存储、检索、安全性和完整性等。
4、数据库（DB，Database）：
- 数据库是一个存储和组织数据的集合，用于持久化地存储大量结构化数据。
- 数据库是由 DBMS 管理和维护的，可以是关系型数据库、NoSQL 数据库等不同类型。
5、联系
- SQL 通过 DBC 与 DBMS 进行通信，应用程序通过 DBC 发送 SQL 查询给 DBMS，DBMS 解释并执行这些查询，然后将结果返回给应用程序，实现应用程序与数据库的交互。
- DBC 是应用程序与 DBMS 之间的通信桥梁，它允许应用程序访问数据库和执行数据库操作。
- DBMS 是负责管理数据库的软件，它解释和执行 SQL 查询，并处理数据库中的数据，使应用程序能够对数据进行增删改查等操作。
- DB 是数据库的物理容器，由 DBMS 管理和维护，包含了实际的数据和数据结构。
6、总结（MySQL是DBMS，而不是DB）
- DB
- DataBase：数据库，数据库实际上在硬盘上以文件的形式存在
- DBMS
- DataBase Management System：数据库管理系统，常见的有：MySQL,Oracle，DB2，Sybase，SqlServer。。。
- MySQL是DBMS
- SQL
- 结构化查询语言，是一门标准通用的语言，标准的sql适合于所有的数据库产品
- SQL属于高级语言，只要能看懂英语单词的，写出来的sql语句，可以读懂什么意思
- SQL语句在执行的时候，实际上内部也会先进行编译，然后在执行sql（sql语句的编译有DBMS完成）
综上所述，SQL 是用于管理和操作数据库的语言，DBC 是应用程序与 DBMS 之间的通信渠道，DBMS 是数据库管理系统，负责解释和执行 SQL 查询，而 DB 则是数据库的实际存储容器，包含了数据和数据结构。它们一起构成了数据库系统的核心
三、MySQL是C/S架构软件
MySQL是一个C/S架构的软件。
C/S架构是Client/Server架构的缩写，是一种计算机网络体系结构。在C/S架构中，客户端（Client）和服务器（Server）是相互独立的两个部分，它们通过网络进行通信。
在MySQL的C/S架构中，MySQL服务器运行在一个计算机上，负责管理数据库和执行SQL语句，而客户端应用程序（如Web应用、桌面应用等）运行在用户的终端设备上，负责与MySQL服务器进行通信，向服务器发送SQL查询请求并接收查询结果。通过C/S架构，用户可以通过客户端与MySQL服务器进行交互，实现数据库的增删改查操作等。
需要注意的是，MySQL也支持其他架构，如单机模式、主从复制和集群模式等，这些架构可根据具体的应用场景和需求来选择。但最常见的使用方式是基于C/S架构。
四、MySQL是关系型数据库管理系统（Relational Database Management System）
MySQL是一种开源的关系型数据库管理系统（RDBMS），它是目前最流行的数据库之一
mysql是一款管理数据库的软件，即：数据库管理软件。
mysql跟数据库的关系：简单的理解就是一个管理，包含的关系，在mysql中可以创建数据库，使用数据库，删除数据库等等。
MySQL是一种关系型数据库管理系统（RDBMS），它采用了关系型数据模型来组织和存储数据。在关系型数据库中，数据以表格的形式组织，每个表格由行（记录）和列（字段）组成。
MySQL具有以下关系型数据库的特点：
-
表格结构：MySQL使用表格来存储数据，每个表格包含多个行和列，每一行表示一个记录，每一列表示一个字段。
-
主键：每个表格都有一个主键，主键用于唯一标识表格中的每个记录。
-
外键：MySQL支持外键，它可以用来建立表格之间的关系，从而实现数据之间的引用完整性和一致性。
-
SQL查询：MySQL使用结构化查询语言（SQL）来执行各种数据库操作，如查询、插入、更新和删除等。
-
事务支持：MySQL支持事务处理，可以保证多个操作的一致性和隔离性，从而确保数据的完整性和可靠性。
-
数据完整性：MySQL支持在表格中定义约束条件，如唯一约束、非空约束等，用于保护数据的完整性。
-
多用户支持：MySQL允许多个用户同时访问数据库，通过访问控制机制来保护数据的安全性。
-
触发器和存储过程：MySQL支持触发器和存储过程，可以在数据库层面上执行自定义的操作和业务逻辑。
以上特点使得MySQL成为一种强大和流行的数据库管理系统，被广泛用于各种应用场景，从小型网站到大型企业级应用都有使用。
1、表（table）的理解
表：table，是关系型数据库（MySQL）的基本组成单元，所有的数据都以表格的形式组织，目的是可读性强
一个表包括行和列
行（row）：称为数据/记录（data）
列（column）：称为字段（field）
每个字段应该包括：字段名，数据类型，相关的约束等
2、 数据库实际上在硬盘上以文件的形式存在
数据库实际上在硬盘上以文件的形式存在，这些文件包含了数据表、索引、事务日志以及其他与数据库相关的信息。数据库系统将这些文件组织成一种结构，以便有效地存储和管理数据。
以下是一些关于数据库文件的常见概念：
-
数据文件：数据文件包含数据库表的实际数据。每个表通常都有一个相关联的数据文件，其中包含表中的行和列数据。这些数据文件可能以特定的格式和结构存储数据，以提高访问和查询效率。
-
索引文件：索引文件包含用于加速数据检索的索引结构。数据库管理系统（DBMS）使用索引文件来快速定位和检索数据，从而提高查询性能。索引可以基于一个或多个列来创建。
-
日志文件：事务日志文件用于记录数据库的变化，包括插入、更新和删除操作。这些日志文件对于数据恢复和维护数据一致性非常重要。数据库将事务操作记录到日志文件，然后将这些操作应用到数据文件中。
-
配置文件：配置文件包含了数据库系统的配置信息，例如数据库名称、表空间设置、权限设置等。这些配置文件通常是用于管理和维护数据库的元数据。
-
临时文件：临时文件用于存储临时数据，例如排序操作中的临时文件。这些文件通常在操作完成后被删除。
-
备份文件：备份文件包含数据库的备份副本，用于数据恢复和灾难恢复。数据库管理员定期创建备份文件以保护数据库的数据。
数据库系统负责管理这些文件，确保数据的持久性、一致性和可用性。不同的数据库管理系统可能会使用不同的文件组织和存储结构，但它们都会在底层硬盘上以文件的形式存储数据。这些文件通常被数据库系统抽象为表、视图、索引等高级数据库对象，以便用户可以方便地访问和操作数据，而无需直接处理文件。
五、MySQL的特点
1、优点
- 开源免费：MySQL是开源软件，可以免费使用，同时有付费版供企业使用。
- 跨平台：MySQL可以在各种操作系统上运行，包括Windows、Linux、macOS等。
- 高性能：MySQL在大部分应用场景下具有优秀的性能，能够处理高并发的读写操作。
- 可扩展性：MySQL支持水平和垂直扩展，可以通过复制、分片等方式提高性能和容量。
- 容易使用：MySQL提供了简单易用的命令行和图形界面工具来管理和操作数据库。
2、缺点
虽然MySQL是一个非常强大和流行的数据库，但它也有一些限制，比如在处理海量数据时可能会有性能问题。对于大规模的数据应用，可能需要考虑其他更高级的数据库系统，如NoSQL数据库或分布式数据库。但对于大多数应用来说，MySQL仍然是一个可靠、稳定和高效的选择。
3、数据存储
MySQL使用表来存储数据，每个表由多个列组成，每列对应着不同的数据类型，如整数、字符串、日期等。每行表示一个记录，表中的记录通过唯一的主键来标识。
4、查询语言
MySQL使用结构化查询语言（SQL）来操作和查询数据。SQL允许用户从数据库中检索、插入、更新和删除数据。
5、数据完整性
MySQL支持数据完整性的保护，可以通过定义主键、外键、唯一约束、默认值等来确保数据的一致性和准确性。
6、事务支持
MySQL支持事务处理，可以在多个操作之间提供一致性和隔离性，保证数据的完整性和可靠性
7、安全性
MySQL提供了用户认证和授权机制，可以对数据库和表进行访问控制，保护数据的安全性。
8、应用领域
MySQL广泛用于各种规模的应用，从小型网站到大型企业应用都有使用。它被用于Web开发、电子商务、社交媒体、数据分析等众多领域。
六、安装，卸载MySQL
1、server安装
2、client安装
3、MySQL默认端口为3306，服务名为MySQL80
4、卸载MySQL
七、MySQL常用命令（非sql语句）
此命令只适用于MySQL RDBMS，其他数据库不行
MySQL 的命令是指在 MySQL 提示符下直接输入的命令，而不是 SQL 查询语句。以下是一些常用的 MySQL 命令：
1、mysql -u username -p; 登录 MySQL 服务器
这会提示您输入 MySQL 用户名（
username）和密码，然后登录到 MySQL 服务器。
2、show variables like "version"; 显示当前 MySQL 服务器版本
这会显示 MySQL 服务器的版本信息。
3、select version()；查看MySQL的版本号
4、show databases；列出所有数据库
这会列出 MySQL 服务器上所有的数据库。
5、use database_name；切换到指定数据库
这会选择要使用的数据库，之后的操作将在该数据库上执行。
6、select database()；查看当前使用的是哪个数据库
7、show tables；显示当前数据库中的所有表
这会列出当前数据库中的所有表格。
show tables from 数据库名; //查询指定数据库中的所有表
8、desc table_name；显示表结构
这会显示表格的结构，包括每个列的名称、数据类型和约束
9、help；或者\h；显示帮助信息
这会显示 MySQL 提示符的帮助信息。
10、\c：结束一条正在编写的sql语句
11、show create table table_name；查看创建表的sql语句
12、exit；或者 quit；退出 MySQL 提示符
这会退出 MySQL 提示符，回到命令行界面。
13、create database 数据库名称：创建数据库
新建数据库：create database [if not exists] 数据库名 [default charset 字符集（utf8）] [collate 排序规则];
14、drop database 数据库名称：删除数据库
删除库：drop database [if exists] 数据库名；
第二章、SQL语句
一、SQL语句的分类
SQL（Structured Query Language）语句可以根据其功能和用途进行分类。以下是一些常见的 SQL 语句分类：
1、DQL：Data Query Language 数据查询语句
查询语句，凡是select语句都是DQL
- SELECT：用于从数据库中检索数据，从一个或多个表中选择特定的列或所有列。
- FROM：指定查询的数据来源表。
- WHERE：用于指定条件，筛选满足条件的数据行。
- GROUP BY：将结果按指定的列进行分组。
- HAVING：在 GROUP BY 之后对结果进行筛选。
- ORDER BY：对结果进行排序。
2、DML：Data Manage Language 数据操作语句
对表当中的数据进行增删改
- INSERT INTO：用于向数据库表中插入新数据。
- UPDATE：用于更新数据库表中的现有数据。
- DELETE FROM：用于从数据库表中删除数据。
3、DDL：Data Define Language 数据定义语句
对表结构进行增删改
- CREATE TABLE：用于创建新的数据库表。
- ALTER TABLE：用于修改现有数据库表的结构。
- DROP TABLE：用于删除数据库表。
4、DCL：Data Control Language 数据控制语句
授权和撤销权限等
- GRANT：用于授予用户或用户组对数据库对象的特定权限。
- REVOKE：用于撤销先前授予的权限。
5、TCL：Transaction Control Language 事务控制语句：
- COMMIT：用于确认事务，并将更改保存到数据库中。
- ROLLBACK：用于撤消事务中的更改，回滚到事务开始前的状态。
- SAVEPOINT：用于在事务中创建保存点，可以用于回滚到特定的保存点。
这只是 SQL 语句的一些常见分类，实际上，SQL 语言非常丰富，具有更多的功能和语句，用于满足不同的数据库操作需求。每个数据库管理系统（DBMS）可能还会有特定的扩展和功能，以适应特定的数据库类型和应用场景。
二、sql脚本
当一个文件的扩展名为：“.sql”，并且改文件中编写了大量的sql语句，我们称这样的文件为sql脚本。
注意：直接使用source 命令可以执行sql脚本
sql脚本中的数据量太大的时候，无法打开，使用source命令完成初始化
三、sql语句通用语法
1、sql语句可以单行或多行书写，以分号结尾。
2、sql语句可使用空格或缩进来增加可读性。
3、sql语句不区分大小写，关键字使用大写，字段列表中间加逗号，开始和结束时不用。
4、注释：--或#或ctrl+/
四、SQL语句不区分大小写
对于字段名，SQL不区分大小写
但是对于表中的数据，SQL按理来说应该是区分大小写，因为大写和小写，应该不能是同一个数据
在MySQL中数据也不区分大小写，语法不严谨，在Oracle中数据区分大小写，语法比较严谨
第三章、导入数据（练习数据）
链接：https://pan.baidu.com/s/1ZYiX6JAzyRU500cGBZ0DYQ?pwd=dljd
提取码：dljd
一、导入步骤
1、登录MySQL
dos窗口命令：mysql -uroot -p密码
2、查看数据库
show databases; （这个不是SQL语句，属于MySQL的命令）
3、创建数据库
create database bjpowernode; （这个不是SQL语句，属于MySQL的命令）
4、使用bjpowernode数据库
use bjpowernode；（这个不是SQL语句，属于MySQL的命令）
5、查询当前在哪个数据库
select database();
6、查看数据库中的所有表
show tables；（这个不是SQL语句，属于MySQL的命令）
7、初始化数据
source 要导入的sql脚本（直接拖进去）
mysql -u username -p your_database_name < your_script.sql --default-character-set=utf8
二、数据介绍
第四章、DQL：Data Query Language 数据查询语句
一、查询语法
select 字段列表（定位列，多个字段逗号分隔） from 表名 [where 条件（定位行）];
注意：
1、任何一条sql语句都以“；”号结尾。
2、sql语句不区分大小写。
3、通配符：*，表示所有字段，实际开发中效率较低，不建议使用
该语句中先执行from后面的内容，字段列表控制列的展示及顺序，字段列表可为表中有的，也可为新建的；只有where为筛选条件
二、as：起别名
as可省略，表名也可起别名，只有用as才会放到表头上
在MySQL中，你可以使用AS关键字为表名、列名或计算字段起别名。别名是一个临时的名称，可以用于简化查询结果或在查询中使用更有意义的名称。以下是MySQL中为表名、列名和计算字段起别名的语法：
1、列名别名
SELECT column_name AS alias_name
FROM table_name;示例：
SELECT first_name AS fname, last_name AS lname
FROM employees;2、表名别名（用于更复杂的查询）
SELECT column_name
FROM table_name AS alias_name;示例：
SELECT e.first_name, e.last_name, d.department_name
FROM employees AS e
JOIN departments AS d ON e.department_id = d.department_id;3、计算字段别名
SELECT expression AS alias_name
FROM table_name;示例：
SELECT product_name, unit_price, quantity, (unit_price * quantity) AS total_price
FROM order_details;请注意，在使用别名时，最好使用单引号将别名括起来，特别是当别名中包含空格或特殊字符时，以避免与关键字或其他语法冲突。实际上，在MySQL中，括号并不是必需的，但建议使用它们以提高可读性。
4、起别名的好处
第一：执行效率高
第二：可读性好
三、数据库对象及执行原理
第五章、单表查询（简单查询）
在MySQL中，单表查询是指对单个表执行查询操作的情况。这是最简单的查询类型，用于从一个表中检索数据。单表查询通常涉及SELECT语句，并且可以使用WHERE子句来过滤结果。
select * from 表名；查询表的所有数据；
select 字段1，字段2，....from 表名；查看表的某些字段；
一、查询一个字段
select ename from emp;
二、查询多个字段
select empno,ename from emp; 字段的顺序控制列的展示顺序
三、字段可以参与数学运算
查询员工的年薪
select ename,sal * 12 from emp;
1、字段起别名
select ename as '姓名',sal * 12 as '年薪' from emp;
注意：字符串中有中文等非ASCII字符使用单引号括起来，虽然Mysql支持双引号，但是不通用
四、查询全部字段（通配符：*）
select * from emp;
实际开发中不建议使用通配符*，效率较低
第六章、条件查询
一、概述
SQL（Structured Query Language）中的条件查询用于从数据库表中检索满足特定条件的数据行。条件查询是 SQL 中最常见和重要的查询类型之一，它允许你根据指定的条件过滤和选择数据，以满足查询的需求。以下是条件查询的详细说明：
SQL 中条件查询通常使用 SELECT 语句，结合 WHERE 子句来执行。WHERE 子句用于指定一个或多个条件，以限制查询结果的返回。基本的语法如下：
1、语法
SELECT column1, column2, ...
FROM table_name
WHERE condition;SELECT语句用于选择要检索的列（字段）。FROM子句用于指定要从中检索数据的表。WHERE子句用于指定条件，只有满足条件的行才会包含在查询结果中。
执行顺序：先执行from，让后执行where，最后select
二、逻辑运算符
不同列的元素用and，同列元素用or
逻辑运算符用于组合多个条件，常见的逻辑运算符包括：
1、AND：逻辑与，要求多个条件都为真。
AND运算符用于将多个条件组合在一起，要求同时满足所有条件才返回结果为真（True）。语法：
condition1 AND condition2，其中condition1和condition2是要组合的两个条件。
示例：查询员工表中工资大于 50000 且雇佣日期在 2020 年之后的员工：
SELECT * FROM employees WHERE salary > 50000 AND hire_date >= '2020-01-01';2、OR：逻辑或，只要满足一个条件即可。
OR运算符用于将多个条件组合在一起，只要满足其中一个条件就返回结果为真（True）。语法：
condition1 OR condition2，其中condition1和condition2是要组合的两个条件。
示例：查询产品表中价格低于 100 或库存大于 10 的产品：
SELECT * FROM products WHERE price < 100 OR stock_quantity > 10;案例：找出工作岗位是MANAGER和SALEMAN的员工
select e.ename, e.job
from emp as e
where e.job = 'manager' or e.job = 'salesman';
+--------+----------+
| ename | job |
+--------+----------+
| ALLEN | SALESMAN |
| WARD | SALESMAN |
| JONES | MANAGER |
| MARTIN | SALESMAN |
| BLAKE | MANAGER |
| CLARK | MANAGER |
| TURNER | SALESMAN |
+--------+----------+案例：找出薪资大于1000的并且部门编号是20或30部门的员工
当运算符的优先级不确定的时候加小括号
select e.ename, e.sal, e.deptno
from emp e
where e.sal > 1000 and e.deptno = 20 or e.deptno = 30; //错误的
select e.ename, e.sal, e.deptno
from emp e
where e.sal > 1000 and (e.deptno = 20 or e.deptno = 30); //正确的
+--------+---------+--------+
| ename | sal | deptno |
+--------+---------+--------+
| ALLEN | 1600.00 | 30 |
| WARD | 1250.00 | 30 |
| JONES | 2975.00 | 20 |
| MARTIN | 1250.00 | 30 |
| BLAKE | 2850.00 | 30 |
| SCOTT | 3000.00 | 20 |
| TURNER | 1500.00 | 30 |
| ADAMS | 1100.00 | 20 |
| FORD | 3000.00 | 20 |
+--------+---------+--------+3、NOT：逻辑非，用于否定一个条件。
NOT运算符用于否定一个条件，即返回条件的相反结果。语法：
NOT condition，其中condition是要否定的条件。
示例：查询客户表中不属于 "VIP" 分组的客户：
SELECT * FROM customers WHERE NOT customer_group = 'VIP';逻辑运算符允许你在 SQL 查询中构建复杂的条件表达式，以满足特定的查询需求。你可以组合多个条件，使用括号来明确运算次序，以创建更具灵活性和精确性的查询条件。通过逻辑运算符，你可以对数据库中的数据进行更精细的筛选和检索。
三、比较运算符
特殊情况：不能写为a<字段<b,应该写为字段>a and 字段<b
可比较的字段数据类型有 int， char， varchar， datetime 等
1、=：等于，精确查找，并非赋值
从emp表中查询工资等于5000的员工姓名
-- 查询工资等于5000的员工姓名
select e.ENAME as '员工姓名'
from emp e
where e.SAL = 5000;
+----------+
| 员工姓名 |
+----------+
| KING |
+----------从emp表中查询‘SMITH’ 员工的工资是多少
select e.sal as '工资'
from emp e
where e.ename = 'SMITH'; //字符串使用单引号括起来
+--------+
| 工资 |
+--------+
| 800.00 |
+--------+2、!= 或 <>：不等于
从emp表中找出工资不等于3000的员工
//写法一：
select e.ename as '员工姓名',e.sal as '工资'
from emp e
where e.sal != 3000;
//写法二：
select e.ename as '员工姓名',e.sal as '工资'
from emp e
where e.sal <> 3000;
3、<：小于
从emp表中找出工资小于3000的员工
select e.ename as '员工姓名',e.sal as '工资'
from emp e
where e.sal < 3000;
+----------+---------+
| 员工姓名 | 工资 |
+----------+---------+
| SMITH | 800.00 |
| ALLEN | 1600.00 |
| WARD | 1250.00 |
| JONES | 2975.00 |
| MARTIN | 1250.00 |
| BLAKE | 2850.00 |
| CLARK | 2450.00 |
| TURNER | 1500.00 |
| ADAMS | 1100.00 |
| JAMES | 950.00 |
| MILLER | 1300.00 |
+----------+---------+4、>：大于
从emp表中找出工资高于3000的员工
select e.ename as '员工姓名',e.sal as '工资'
from emp e
where e.sal > 3000;
+----------+---------+
| 员工姓名 | 工资 |
+----------+---------+
| KING | 5000.00 |
+----------+---------+5、<=：小于等于
从emp表中找出工资小于等于3000的员工
select e.ename as '员工姓名',e.sal as '工资'
from emp e
where e.sal <= 3000;
+----------+---------+
| 员工姓名 | 工资 |
+----------+---------+
| SMITH | 800.00 |
| ALLEN | 1600.00 |
| WARD | 1250.00 |
| JONES | 2975.00 |
| MARTIN | 1250.00 |
| BLAKE | 2850.00 |
| CLARK | 2450.00 |
| SCOTT | 3000.00 |
| TURNER | 1500.00 |
| ADAMS | 1100.00 |
| JAMES | 950.00 |
| FORD | 3000.00 |
| MILLER | 1300.00 |
+----------+---------+6、>=：大于等于
从emp表中找出工资高于等于3000的员工
select e.ename as '员工姓名',e.sal as '工资'
from emp e
where e.sal >= 3000;
+----------+---------+
| 员工姓名 | 工资 |
+----------+---------+
| SCOTT | 3000.00 |
| KING | 5000.00 |
| FORD | 3000.00 |四、between and运算符（左右闭区间）
SQL 中的 BETWEEN 和 AND 运算符一起使用，用于指定一个范围条件，用于过滤满足指定范围的数据。BETWEEN 用于指定范围的起始值和结束值，AND 用于分隔这两个值。这个条件通常用于数值和日期字段上。以下是 BETWEEN 和 AND 的详细解释：
BETWEEN 运算符的一般语法如下：
column_name BETWEEN value1 AND value2;column_name是要比较的列名。value1是范围的起始值。value2是范围的结束值。
起始值必须小于结束值，否则找不到数据
between and 在使用的时候必须 左小右大
1、实现原理及注意事项
BETWEEN运算符的工作方式是，它会包含起始值和结束值，检查指定列中的数据是否在这个范围内。如果数据位于起始值和结束值之间（包括起始值和结束值），则该条件为真，数据将包含在查询结果中。
- 需要注意的是，
BETWEEN运算符可以用于不同数据类型的列，包括数值、日期、字符串等。但要确保列的数据类型与范围值的数据类型匹配，以避免错误。
- 应用于数字int类型方面，等同于 >= and <=，左右都为闭区间
- 应用于字符串 或 日期方面，等同于 >= and <，左闭右开
- 另外，可以使用
NOT BETWEEN来查找不在指定范围内的数据，即排除指定范围的数据。- 总之，
BETWEEN和AND运算符组合在一起，提供了一种方便的方式来指定数据的范围条件，用于过滤和检索数据库中的数据。
案例一：找出工资在1100 到 3000之间的员工，包括1100和3000，数据类型为int，左右都是闭区间
select e.ename as '姓名', e.sal as '工资'
from emp e
where e.sal between 1100 and 3000;
//等价于
select e.ename as '姓名', e.sal as '工资'
from emp as e
where e.sal >= 1100 and e.sal <= 3000;
+--------+---------+
| 姓名 | 工资 |
+--------+---------+
| ALLEN | 1600.00 |
| WARD | 1250.00 |
| JONES | 2975.00 |
| MARTIN | 1250.00 |
| BLAKE | 2850.00 |
| CLARK | 2450.00 |
| SCOTT | 3000.00 |
| TURNER | 1500.00 |
| ADAMS | 1100.00 |
| FORD | 3000.00 |
| MILLER | 1300.00 |
+--------+---------+案例二：找出入职日期在 1980-01-01 到 1981-12-31 入职的员工，数据类型为date，左闭右开
select e.ename as '姓名', e.hiredate as '入职日期'
from emp e
where e.hiredate between '1980-01-01' and '1981-12-31';
+--------+------------+
| 姓名 | 入职日期 |
+--------+------------+
| SMITH | 1980-12-17 |
| ALLEN | 1981-02-20 |
| WARD | 1981-02-22 |
| JONES | 1981-04-02 |
| MARTIN | 1981-09-28 |
| BLAKE | 1981-05-01 |
| CLARK | 1981-06-09 |
| KING | 1981-11-17 |
| TURNER | 1981-09-08 |
| JAMES | 1981-12-03 |
| FORD | 1981-12-03 |
+--------+------------+案例三：姓名首字符为 a 到 c 的员工姓名，左闭右开
select e.ename
from emp as e
where e.ename between 'a' and 'c';
+-------+
| ename |
+-------+
| ALLEN |
| BLAKE |
| ADAMS |
+-------+五、null运算符
在 SQL 中，IS NULL 和 IS NOT NULL 是用于测试字段是否为 NULL 值的运算符。NULL 表示缺少值或未知值，通常用于表示数据不可用或未填充的情况。以下是关于这两个运算符的详细解释：
在数据库中NULL不是一个空值，而是一个空值对象，不能使用 = 来衡量
1、对SQL中null的理解
SQL中的null相当于java中的null空值对象，Python中的None空值对象，都是在堆内存中有一块固定的内存空间。null变量中的变量值包括内存地址和数据类型，但是内存地址的空间中什么没有，是空的。
is表示判断变量的值，内存地址，相当于java中的==
=表示判断变量的内容，相当于java中的equals()方法
- null is null 永远为True
- null is not null 永远为False
- null = null 或者 null != null 永远为False
- 字段名 = null 或者 字段名 != null 永远为False
2、IS NULL 运算符
IS NULL 用于测试一个字段是否包含 NULL 值。如果字段的值为 NULL，那么条件为真；如果字段的值不是 NULL，则条件为假。语法如下：
column_name IS NULL;示例：查询员工表中没有分配办公室的员工：
SELECT * FROM employees WHERE office IS NULL;这将返回办公室字段值为 NULL 的员工记录。
案例：查询emp表中津贴为null的员工
select e.ename, e.sal, e.comm
from emp e
where e.comm is null;
+--------+---------+------+
| ename | sal | comm |
+--------+---------+------+
| SMITH | 800.00 | NULL |
| JONES | 2975.00 | NULL |
| BLAKE | 2850.00 | NULL |
| CLARK | 2450.00 | NULL |
| SCOTT | 3000.00 | NULL |
| KING | 5000.00 | NULL |
| ADAMS | 1100.00 | NULL |
| JAMES | 950.00 | NULL |
| FORD | 3000.00 | NULL |
| MILLER | 1300.00 | NULL |
+--------+---------+------+3、IS NOT NULL 运算符
IS NOT NULL 用于测试一个字段是否不包含 NULL 值。如果字段的值不是 NULL，则条件为真；如果字段的值为 NULL，则条件为假。语法如下：
column_name IS NOT NULL;示例：查询产品表中有库存的产品：
SELECT * FROM products WHERE stock_quantity IS NOT NULL;这将返回库存数量字段值不为 NULL 的产品记录。
案例：查询emp表中津贴不为null的员工
select e.ename, e.sal, e.comm
from emp e
where e.comm is not null;
+--------+---------+---------+
| ename | sal | comm |
+--------+---------+---------+
| ALLEN | 1600.00 | 300.00 |
| WARD | 1250.00 | 500.00 |
| MARTIN | 1250.00 | 1400.00 |
| TURNER | 1500.00 | 0.00 |
+--------+---------+---------+案例：查询emp表中没有津贴的员工
select e.ename, e.sal, e.comm
from emp e
where e.comm is null or e.comm = 0;
+--------+---------+------+
| ename | sal | comm |
+--------+---------+------+
| SMITH | 800.00 | NULL |
| JONES | 2975.00 | NULL |
| BLAKE | 2850.00 | NULL |
| CLARK | 2450.00 | NULL |
| SCOTT | 3000.00 | NULL |
| KING | 5000.00 | NULL |
| TURNER | 1500.00 | 0.00 |
| ADAMS | 1100.00 | NULL |
| JAMES | 950.00 | NULL |
| FORD | 3000.00 | NULL |
| MILLER | 1300.00 | NULL |
+--------+---------+------+IS NULL 和 IS NOT NULL 运算符通常用于过滤包含 NULL 值的记录或排除包含 NULL 值的记录。在数据库查询中，处理 NULL 值非常重要，因为它们可能会影响数据的准确性和完整性。通过使用这两个运算符，你可以轻松地处理 NULL 值并编写具有条件约束的查询。
六、like 运算符
在 SQL 中，LIKE 运算符用于在字符串列中进行模糊匹配，允许你搜索包含特定字符模式的数据。LIKE 运算符通常与通配符一起使用，通配符是用于表示模式的特殊字符。最常用的通配符是百分号 % 和下划线 _。以下是 LIKE 运算符和通配符的详细解释：
1、百分号 % 通配符
%通配符用于匹配任意数量的字符（包括零个字符）。当
%放置在模式的开头、结尾或中间，它可以匹配任何字符序列。
示例：查询所有以 "A" 开头的产品：
SELECT * FROM products WHERE product_name LIKE 'A%';查询所有包含 "apple" 的产品名称：
SELECT * FROM products WHERE product_name LIKE '%apple%';查询员工姓名中含有o字符的
select e.ename
from emp e
where e.ename like '%o%';
+-------+
| ename |
+-------+
| JONES |
| SCOTT |
| FORD |
+-------+2、下划线 _ 通配符：
_通配符用于匹配单个字符。当
_放置在模式中，它可以匹配一个字符的位置。
示例：查询所有包含 "a" 和 "e" 之间有一个字符的产品名称：
SELECT * FROM products WHERE product_name LIKE 'a_e';查询员工姓名中第二个字母是 a 字符的员工姓名
select e.ename
from emp e
where e.ename like '_a%';
+--------+
| ename |
+--------+
| WARD |
| MARTIN |
| JAMES |
+--------+查询员工姓名中含有 _ 的员工姓名
使用转义字符：\ ，\_表示一个普通的下划线
select name
from t_user
where name like '%\_%';
+---------+
| name |
+---------+
| wang_wu |
+---------+查询员工姓名中最后一个字母是 t 的员工姓名
select e.ename
from emp e
where e.ename like '%t';
+-------+
| ename |
+-------+
| SCOTT | //scott是oracle数据库中的一个用户，密码是tiger
+-------+LIKE 运算符和通配符非常有用，可以用于在文本数据中查找模式或关键字，从而实现模糊搜索。它在 SQL 查询中提供了灵活的文本匹配功能。注意，不同的数据库管理系统可能对 LIKE 运算符的实现细节有所不同，但通常通配符的使用方式是相似的。
七、成员运算符
在 SQL 中，成员运算符用于检查一个值是否包含在一个集合中。最常见的成员运算符是 IN 和 NOT IN。这些运算符允许你在查询中测试一个值是否存在于指定的集合中。以下是 IN 和 NOT IN 运算符的详细解释：
1、IN 运算符
IN运算符用于测试一个值是否存在于指定的集合中。
- 字段名 in （值1，值2） 相当于 字段名=值1 or 字段名=值2
语法：
value IN (value1, value2, ...)，其中value是要测试的值，(value1, value2, ...)是包含可能值的集合。
示例：查询订单表中客户编号属于 {101, 102, 103} 的订单：
SELECT * FROM orders WHERE customer_id IN (101, 102, 103);in等同于or：找出工作岗位是MANAGER和SALEMAN的员工
select e.ename, e.job
from emp e
where e.job in ('manager', 'salesman'); //可读性好，效率更高一点
//等同于
select e.ename, e.job
from emp e
where e.job = 'manager' or e.job = 'salesman';
+--------+----------+
| ename | job |
+--------+----------+
| ALLEN | SALESMAN |
| WARD | SALESMAN |
| JONES | MANAGER |
| MARTIN | SALESMAN |
| BLAKE | MANAGER |
| CLARK | MANAGER |
| TURNER | SALESMAN |
+--------+----------+IN 运算符也可以与子查询一起使用，例如：
SELECT * FROM products WHERE category_id IN (SELECT category_id FROM categories WHERE category_name = 'Electronics');2、NOT IN 运算符
NOT IN运算符用于测试一个值是否不在指定的集合中。
- 字段名 not in （值1，值2） 相当于 字段名 != 值1 and 字段名 != 值2
语法：
value NOT IN (value1, value2, ...)，其中value是要测试的值，(value1, value2, ...)是不包含该值的集合。
示例：查询员工表中不属于部门 {HR, Finance} 的员工：
SELECT * FROM employees WHERE department NOT IN ('HR', 'Finance');查询薪资不是800或者5000的员工信息
select e.ename, e.sal, e.job
from emp e
where e.sal not in (800, 5000);
+--------+---------+----------+
| ename | sal | job |
+--------+---------+----------+
| ALLEN | 1600.00 | SALESMAN |
| WARD | 1250.00 | SALESMAN |
| JONES | 2975.00 | MANAGER |
| MARTIN | 1250.00 | SALESMAN |
| BLAKE | 2850.00 | MANAGER |
| CLARK | 2450.00 | MANAGER |
| SCOTT | 3000.00 | ANALYST |
| TURNER | 1500.00 | SALESMAN |
| ADAMS | 1100.00 | CLERK |
| JAMES | 950.00 | CLERK |
| FORD | 3000.00 | ANALYST |
| MILLER | 1300.00 | CLERK |
+--------+---------+----------+NOT IN 运算符也可以与子查询一起使用。
成员运算符允许你在查询中执行基于集合成员关系的条件测试。这对于在 SQL 查询中筛选特定值的情况非常有用，可以用于过滤或选择数据。需要注意的是，IN 和 NOT IN 运算符在不同的数据库管理系统中可能会有一些差异，因此要根据所使用的数据库系统查看具体的文档和示例。
第七章、去重查询（distinct关键字）
一、概述
在 SQL 中，DISTINCT 是一个关键字，用于从查询结果中删除重复的行，以返回唯一的值。它通常用于 SELECT 语句中，以确保查询结果中的每个值都是唯一的。
1、语法
查询部分字段时，可能会出现多行数据是一样的，重复的行，去重后相同的行只保留一行，若有多个字段时，确保多个列的组合是唯一的，即就是多个列的数据为一个组合，只要组合中的数据是唯一的就行，并不是以哪一列为准。
使用 DISTINCT 的一般语法如下：
SELECT DISTINCT column1, column2, ...
FROM table_name;DISTINCT关键字用于指示数据库系统只返回唯一的行。column1, column2, ...是要选择的列名，你可以指定一个或多个列，以确定哪些列的唯一性应该被考虑。table_name是要查询的表名。
2、注意事项
- 如果在查询中使用了聚合函数（如
COUNT、SUM等），则DISTINCT将应用于聚合函数的结果。 DISTINCT通常会增加查询的执行时间，因为它需要对结果集进行额外的去重操作，特别是在大型数据集上。
3、distinct只能出现在所有字段的最前面
distinct只能出现在所有字段的最前面
select distinct e.job from emp e; //distinct关键子去重重复记录
+-----------+
| job |
+-----------+
| CLERK |
| SALESMAN |
| MANAGER |
| ANALYST |
| PRESIDENT |
+-----------+
select ename, distinct job from emp;
//以上语法报错
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 'distinct job from emp' at line 1
//因为ename是14行，distinct job 是5行，不能在一张表中
4、多字段组合去重
select e.deptno, e.job from emp e order by e.deptno;
+--------+-----------+
| deptno | job |
+--------+-----------+
| 10 | MANAGER |
| 10 | PRESIDENT |
| 10 | CLERK |
| 20 | CLERK |
| 20 | MANAGER |
| 20 | ANALYST |
| 20 | CLERK |
| 20 | ANALYST |
| 30 | SALESMAN |
| 30 | SALESMAN |
| 30 | SALESMAN |
| 30 | MANAGER |
| 30 | SALESMAN |
| 30 | CLERK |
+--------+-----------+
//表示deptno和job两字段联合去重，组合起来
select distinct e.deptno, e.job from emp e order by e.deptno;
+--------+-----------+
| deptno | job |
+--------+-----------+
| 10 | CLERK |
| 10 | MANAGER |
| 10 | PRESIDENT |
| 20 | ANALYST |
| 20 | CLERK |
| 20 | MANAGER |
| 30 | CLERK |
| 30 | MANAGER |
| 30 | SALESMAN |
+--------+-----------+二、案例
1、查询员工表中不重复的部门名称：
SELECT DISTINCT department FROM employees;这将返回员工表中所有不重复的部门名称。
2、查询订单表中不重复的客户名称和客户地址：
SELECT DISTINCT customer_name, customer_address FROM orders;这将返回订单表中所有不重复的客户名称和客户地址组合。
3、查询产品表中不重复的产品分类
SELECT DISTINCT category FROM products;这将返回产品表中所有不重复的产品分类。
1、distinct使用在聚合函数中
select count(distinct e.job) from emp e;
+-----------------------+
| count(distinct e.job) |
+-----------------------+
| 5 |
+-----------------------+第八章、排序查询
一、概述
在SQL中，排序查询是一种用于按特定列的值对结果集进行排序的操作。排序查询通常使用ORDER BY子句来实现，该子句允许你指定一个或多个列，并指定升序（ASC）或降序（DESC）排序方式。
1、基本语法
SELECT * FROM 表名
ORDER BY 列1 [ASC|DESC], 列2 [ASC|DESC], ...;SELECT *: 选择所有列，你也可以选择特定的列。FROM 表名: 指定要从哪个表中获取数据。ORDER BY: 这是排序的关键字。列1, 列2, ...: 你要排序的列的列表，可以是一个或多个列。[ASC|DESC]: 可选的升序（ASC，默认）或降序（DESC）排序方式。如果不指定，默认为升序。
2、升序和降序排序
- 升序排序（ASC）按列的值从小到大排列。
- 降序排序（DESC）按列的值从大到小排列。
3、多列排序
越靠前的字段越能起到主导作用，只有当前面的字段无法完成排序的时候，才会启用后面的字段
- 你可以根据多个列进行排序，这样可以按主要排序列的值进行排序，如果有相同的值，则按次要排序列的值进行排序，以此类推。
4、执行顺序
select
* 3
from
tablename 1
where
条件 2
order by
需要排序的列 4
order by 是最后执行的
//因为先执行了select后面的语句，后面的order by 才能只用别名
select
e.ename, e.sal as salary
from
emp e
order by
salary desc;
+--------+---------+
| ename | salary |
+--------+---------+
| KING | 5000.00 |
| SCOTT | 3000.00 |
| FORD | 3000.00 |
| JONES | 2975.00 |
| BLAKE | 2850.00 |
| CLARK | 2450.00 |
| ALLEN | 1600.00 |
| TURNER | 1500.00 |
| MILLER | 1300.00 |
| WARD | 1250.00 |
| MARTIN | 1250.00 |
| ADAMS | 1100.00 |
| JAMES | 950.00 |
| SMITH | 800.00 |
+--------+---------+二、NULL 值的处理
默认情况下，对于包含 NULL 值的列，它们会在排序时排在结果集的最前面（对于升序排序）或最后面（对于降序排序）。你可以使用NULLS FIRST或NULLS LAST来明确指定 NULL 值的排序位置。
三、性能注意事项
对大型表执行排序可能会导致性能问题。在这种情况下，你可以考虑在要排序的列上创建索引以提高性能。
四、使用其他函数和表达式
你还可以在ORDER BY子句中使用其他函数和表达式，而不仅仅是列名，以实现更复杂的排序逻辑。
综上所述，排序查询是SQL中非常常见且有用的操作之一，它允许你按照特定的标准对查询结果进行排序，以获得有序的数据集。你可以根据需要组合多个列和排序方向来满足不同的排序需求。
五、可排序的字段类型
可排序的字段数据类型有 int， char， varchar， datetime 等
六、案例
假设有一个名为employees的表，其中包含employee_id、first_name和hire_date列，你可以执行以下查询来按照hire_date升序排序：
SELECT * FROM employees
ORDER BY hire_date ASC;若要按照hire_date降序排序并在同一查询中按first_name升序排序：
SELECT * FROM employees
ORDER BY hire_date DESC, first_name ASC;按照工资升序，找出员工名和薪资
默认是升序，asc表示升序，desc表示降序
select e.ename, e.sal
from emp e
order by e.sal asc;
+--------+---------+
| ename | sal |
+--------+---------+
| SMITH | 800.00 |
| JAMES | 950.00 |
| ADAMS | 1100.00 |
| WARD | 1250.00 |
| MARTIN | 1250.00 |
| MILLER | 1300.00 |
| TURNER | 1500.00 |
| ALLEN | 1600.00 |
| CLARK | 2450.00 |
| BLAKE | 2850.00 |
| JONES | 2975.00 |
| SCOTT | 3000.00 |
| FORD | 3000.00 |
| KING | 5000.00 |
+--------+---------+按照工资的降序排列，当工资相同的时候再按照名字的升序排列
select e.ename, e.sal
from emp e
order by e.sal desc, e.ename asc;
+--------+---------+
| ename | sal |
+--------+---------+
| KING | 5000.00 |
| FORD | 3000.00 |
| SCOTT | 3000.00 |
| JONES | 2975.00 |
| BLAKE | 2850.00 |
| CLARK | 2450.00 |
| ALLEN | 1600.00 |
| TURNER | 1500.00 |
| MILLER | 1300.00 |
| MARTIN | 1250.00 |
| WARD | 1250.00 |
| ADAMS | 1100.00 |
| JAMES | 950.00 |
| SMITH | 800.00 |
+--------+---------+特殊情况：使用order by 排序的时候可以不写字段名，直接写列号
select e.ename, e.sal
from emp e
order by 2, 1; //此处的2指的是 select 后面字段列表中的第二个字段，1指的是 第一个字段
+--------+---------+
| ename | sal |
+--------+---------+
| SMITH | 800.00 |
| JAMES | 950.00 |
| ADAMS | 1100.00 |
| MARTIN | 1250.00 |
| WARD | 1250.00 |
| MILLER | 1300.00 |
| TURNER | 1500.00 |
| ALLEN | 1600.00 |
| CLARK | 2450.00 |
| BLAKE | 2850.00 |
| JONES | 2975.00 |
| FORD | 3000.00 |
| SCOTT | 3000.00 |
| KING | 5000.00 |
+--------+---------+找出工作岗位是 salesman 的员工，并且要求按照薪资的降序排序
select
e.ename, e.sal, e.job
from
emp e
where
e.job = 'salesman'
order by
e.sal desc;
+--------+---------+----------+
| ename | sal | job |
+--------+---------+----------+
| ALLEN | 1600.00 | SALESMAN |
| TURNER | 1500.00 | SALESMAN |
| WARD | 1250.00 | SALESMAN |
| MARTIN | 1250.00 | SALESMAN |
+--------+---------+----------+第九章、聚合函数（分组函数，多行处理函数）
一、概述
在 SQL 中，聚合函数是用于对多行数据进行计算并返回单个结果的函数。这些函数通常用于对数据进行汇总、统计和分析。常见的 SQL 聚合函数包括 COUNT、SUM、AVG、MIN 和 MAX 等。
聚合函数，又称分组函数，多行处理函数
多行处理函数的特点：输入多行，最终输出的结果是1行
1、所有的聚合函数都是对‘某一组（列）’数据进行操作的
聚合函数是对于某一列中的多行数据进行计算，并返回单个结果
2、聚合函数自动忽略null
自动忽略null是指分组函数在传入参数的时候不会优先将null传入，若是null以及传入，或者null不得不传入，则最终结果为null
//本来应该为14行数据，但是有由于在comm列中有10个null，自动忽略了，所以只统计出4行数据
select
count(e.empno) as '总人数'
from
emp e;
+---------------+
| count(e.comm) |
+---------------+
| 4 |
+---------------+3、单行处理函数
定义：输入一行，输出一行
就是在字段列表进行数学表达式运算
4、数学表达式中只要有一个数据为null，则整个表达式结果为null
重点：所有数据库都是这样规定的，只要有NULL参与的运算，结果一定是NULL。
计算每个员工的年薪
select
e.ename as '姓名', (e.sal + e.comm) * 12 as '年薪'
from
emp e;
+--------+----------+
| 姓名 | 年薪 |
+--------+----------+
| SMITH | NULL |
| ALLEN | 22800.00 |
| WARD | 21000.00 |
| JONES | NULL |
| MARTIN | 31800.00 |
| BLAKE | NULL |
| CLARK | NULL |
| SCOTT | NULL |
| KING | NULL |
| TURNER | 18000.00 |
| ADAMS | NULL |
| JAMES | NULL |
| FORD | NULL |
| MILLER | NULL |
+--------+----------+再次验证聚合函数会自动忽略null
因为有结论只要是null参与数学运算则结果为null，以下执行结果显示，聚合函数的结果不是null，所以聚合函数的运算会自动忽略null
select
sum(e.comm)
from
emp e;
//等同于下列语句，因为聚合函数会自动忽略null
select
sum(e.comm)
from
emp e
where
e.comm is not null;
+-------------+
| sum(e.comm) |
+-------------+
| 2200.00 |
+-------------+5、ifnull()函数：单行处理函数
在 SQL 中，IFNULL 函数用于处理 NULL 值，它接受两个参数：第一个参数是要检查的表达式或列，第二个参数是在第一个参数为 NULL 时返回的替代值。IFNULL 函数的作用是如果第一个参数为 NULL，则返回第二个参数，否则返回第一个参数的值。在一些数据库系统中，IFNULL 函数也可以用 COALESCE 函数来代替，具有相同的功能。
以下是 IFNULL 函数的一般语法：
IFNULL(expression, replacement_value)expression是要检查的表达式、列或值。replacement_value是在expression为 NULL 时返回的替代值。
示例：
假设你有一个员工表（employees），其中包含员工的姓名和出生日期，但有些员工的出生日期未知（即为 NULL）。你想要查询员工的姓名和出生日期，并将出生日期未知的员工标记为 "未知"。你可以使用 IFNULL 函数来实现：
SELECT first_name, IFNULL(birth_date, '未知') AS formatted_birth_date
FROM employees;在上述查询中，我们使用 IFNULL 函数来检查 birth_date 列是否为 NULL。如果 birth_date 为 NULL，则返回 "未知" 作为替代值；否则，返回实际的出生日期值。这样，你可以在结果集中看到出生日期未知的员工的标记。
需要注意的是，不同的数据库管理系统可能使用不同的函数来实现相同的功能。在某些数据库中，你可能会看到 COALESCE 函数用于处理 NULL 值，它的语法和功能与 IFNULL 相似。因此，在特定数据库中查看文档以确认可用的函数和语法是很重要的。
改写上述案例：计算每个员工的年薪
select
e.ename as '姓名', (e.sal + ifnull(e.comm, 0)) * 12 as '年薪'
from
emp e;
+--------+----------+
| 姓名 | 年薪 |
+--------+----------+
| SMITH | 9600.00 |
| ALLEN | 22800.00 |
| WARD | 21000.00 |
| JONES | 35700.00 |
| MARTIN | 31800.00 |
| BLAKE | 34200.00 |
| CLARK | 29400.00 |
| SCOTT | 36000.00 |
| KING | 60000.00 |
| TURNER | 18000.00 |
| ADAMS | 13200.00 |
| JAMES | 11400.00 |
| FORD | 36000.00 |
| MILLER | 15600.00 |
+--------+----------+6、聚合函数只能使用在select和having子句中，不能在其他子句中使用
SQL中的聚合函数通常用于SELECT语句中，对结果集中的列进行汇总、统计或计算。聚合函数允许你执行各种数学和统计操作，例如计算总和、平均值、最小值、最大值等，以便从数据中提取有用的信息。
聚合函数通常不会直接用于FROM、WHERE、GROUP BY、HAVING等子句中，因为它们的主要任务是处理SELECT语句中的列数据。在这些子句中，你通常会使用普通的列名来过滤、分组和筛选数据。
但是，在某些情况下，你可以在HAVING子句中使用聚合函数，用于筛选分组后的数据。HAVING子句通常用于过滤分组后的结果，而不是单个行。
以下是一个使用聚合函数的示例，其中聚合函数用于HAVING子句中：
SELECT department, AVG(salary) AS avg_salary
FROM employees
GROUP BY department
HAVING AVG(salary) > 50000; 在上面的示例中，我们首先按部门分组，然后计算每个部门的平均工资。接着，我们使用HAVING子句筛选出平均工资大于 50,000 的部门。
总之，聚合函数通常用于SELECT语句中，但在HAVING子句中也可以使用它们来筛选分组后的数据。这允许你在聚合级别上执行条件过滤。其他子句（如FROM、WHERE、GROUP BY）通常使用普通列名来操作和筛选数据。
找出工资高于平均工资的员工
select e.ename, e.sal from emp e where e.sal > avg(e.sal);//ERROR 1111 (HY000): Invalid use of group function
思考以上的错误信息：无效的使用了分组函数
原因：SQL语句中有一个语法规则，分组函数不可直接使用在where子句中
因为 group by 是在where执行之后才会执行的，分组函数必须在分完组后才能执行
解决方案：
第一步：找出平均薪资
select avg(e.sal) from emp e; //平均薪资
+-------------+
| avg(e.sal) |
+-------------+
| 2073.214286 |
+-------------+
第二步：找出高于平均工资的员工
select e.ename, e.sal from emp e where e.sal > 2073.214286;
+-------+---------+
| ename | sal |
+-------+---------+
| JONES | 2975.00 |
| BLAKE | 2850.00 |
| CLARK | 2450.00 |
| SCOTT | 3000.00 |
| KING | 5000.00 |
| FORD | 3000.00 |
+-------+---------+
第三部：使用子查询，合并代码
select e.ename, e.sal from emp e where e.sal > (select avg(sal) from emp);7、分组函数也能组合起来用
select count(*), sum(sal), avg(sal), min(sal), max(sal) from emp;
+----------+----------+-------------+----------+----------+
| count(*) | sum(sal) | avg(sal) | min(sal) | max(sal) |
+----------+----------+-------------+----------+----------+
| 14 | 29025.00 | 2073.214286 | 800.00 | 5000.00 |
+----------+----------+-------------+----------+----------+二、COUNT 函数（用于计算某列或某个表中行的数量）
COUNT函数用于计算指定列或行的数量。语法：
COUNT(expression)，其中expression可以是列名、常量或通配符*。
示例：计算员工表中的员工数量：
SELECT COUNT(*) FROM employees;这将返回员工表中的总行数，即员工的数量。
找出总人数
select
count(*) as '总人数'
from
emp e;
//等同于
select
count(e.empno) as '总人数'
from
emp e;
+--------+
| 总人数 |
+--------+
| 14 |
+--------+1、 count(*) 和 count(具体某个字段) 的区别
count(*)：统计表中数据的总行数
不是统计某个字段中数据的个数，而是统计总记录条数（和某个字段无关）
select count(*) from emp;
+----------+
| count(*) |
+----------+
| 14 |
+----------+count(具体某个字段)：统计具体某个字段（列）中不为null的数据的总行数
select count(comm) from emp;
+-------------+
| count(comm) |
+-------------+
| 4 |
+-------------+三、SUM 函数（用于int类型数据，用于计算数值列的总和）
SUM函数用于计算指定列的总和。语法：
SUM(column)，其中column是要计算总和的列名。
示例：计算订单表中订单总金额：
SELECT SUM(order_amount) FROM orders;这将返回订单表中所有订单的总金额。
计算工资总和
select
sum(e.sal) as '工资总和'
from
emp e;
+----------+
| 工资总和 |
+----------+
| 29025.00 |
+----------+四、AVG 函数（用于int类型数据，用于计算数值列的平均值）
AVG函数用于计算指定列的平均值。语法：
AVG(column)，其中column是要计算平均值的列名。
示例：计算产品表中产品价格的平均值：
SELECT AVG(price) FROM products;这将返回产品价格的平均值。
找出平均工资
select
avg(e.sal) as '平均工资'
from
emp e;
+-------------+
| 平均工资 |
+-------------+
| 2073.214286 |
+-------------+五、MIN 函数（一般用于int类型数据，用于获取数值列中的最大值，或者获取字符串列中的按字母顺序最大的值）
MIN函数用于找出指定列中的最小值。语法：
MIN(column)，其中column是要查找最小值的列名。
示例：查找员工表中薪水最低的员工：
SELECT MIN(salary) FROM employees;这将返回员工表中薪水最低的员工的薪水值。
找出最低工资
select
min(e.sal) as '最低工资'
from
emp e;
+----------+
| 最低工资 |
+----------+
| 800.00 |
+----------+六、MAX 函数（一般用于int类型数据，用于获取数值列中的最小值，或者获取字符串列中的按字母顺序最小的值）
MAX函数用于找出指定列中的最大值。语法：
MAX(column)，其中column是要查找最大值的列名。
示例：查找产品表中价格最高的产品：
SELECT MAX(price) FROM products;这将返回产品表中价格最高的产品的价格。
找出最高工资
select
max(e.sal) as '最高工资'
from
emp e;
+----------+
| 最高工资 |
+----------+
| 5000.00 |
+----------+聚合函数通常与 GROUP BY 子句一起使用，以对数据进行分组和汇总。它们在 SQL 查询中非常有用，可以帮助你执行各种统计和分析任务。值得注意的是，聚合函数返回单个值，因此它们通常出现在 SELECT 语句的列中，而不是在 WHERE 子句中。
第十章、分组查询
一、概述
SQL 中的分组查询（Group By）用于将结果集按照一个或多个列的值进行分组，并对每个组应用聚合函数以汇总数据。分组查询通常与聚合函数（如 SUM、COUNT、AVG、MAX、MIN）一起使用，以对每个组执行计算。以下是分组查询的详细解释：
1、基本语法
SELECT
column1, column2, aggregate_function(column3) （字段列表）
FROM
table_name（表名）
[WHERE
条件]
GROUP BY
column1, column2(分组字段名)
[having
分组后过滤条件];column1, column2是要分组的列，你可以指定一个或多个列，根据这些列的值来创建分组。aggregate_function(column3)是要应用于每个分组的聚合函数，通常会对某个列的值进行计算。table_name是要查询的表名。
2、执行顺序
oracle中语法比较严格，一般在oracle中能执行的SQL语句在MySQL中一定能执行，但是在M有SQL中能执行的SQL语句在oracle中不一定能执行
select 4 （MySQL中执行顺序） 5 （oracle中执行顺序）
字段列表
from 1 1
表名
where 2 2
分组前过滤条件
group by 3 3
分组字段
having 5 4
分组后过滤条件
order by 6 6
对结果集进行排序
在 SQL 查询中，各个子句的执行顺序通常如下：
-
FROM 子句：
FROM子句指定了要查询的数据表（或视图）。这是查询的起点，从中获取数据。在执行查询之前，数据库会从指定的表中检索数据。 -
WHERE 子句：
WHERE子句用于筛选从数据表中检索的数据，只包括符合条件的行。条件由WHERE子句中的逻辑操作符定义，例如等于 (=)、不等于 (<>)、小于 (<)、大于 (>) 等。 -
GROUP BY 子句：
GROUP BY子句用于将结果集中的行分组到一个或多个列的值相同的组中。这是对数据进行分组的步骤，通常与聚合函数一起使用，以便对每个组执行计算。 -
HAVING 子句：
HAVING子句通常与GROUP BY子句一起使用，用于在分组后对组进行条件筛选。它允许你筛选分组后的结果集，只包括满足特定条件的分组。 -
SELECT 子句：
SELECT子句用于选择要包括在查询结果中的列，并可以进行列的重命名、计算、聚合等操作。在这一步中，数据库生成最终的结果集。 -
ORDER BY 子句：
ORDER BY子句用于对结果集中的行进行排序。你可以指定一个或多个列以确定排序顺序，并可以选择升序 (ASC) 或降序 (DESC) 排序。
总体来说，上述子句的执行顺序反映了 SQL 查询的逻辑顺序。然而，数据库管理系统的查询优化器可能会根据查询的复杂性和索引的使用情况进行优化，以提高查询性能。因此，在实际执行中，查询可能会以不同的方式执行，但结果应该与上述逻辑一致。理解这些子句的执行顺序对于编写高效的查询和调试查询错误非常重要。
3、执行原理
group by 会将原表数据分成若干组，在select结果集中只会显示为一行数据，相当于多行处理函数
4、若没有group by子句，则默认整张表自己为一个大组
没有group by子句的话，默认整张表自成一组
二、GROUP BY 子句
GROUP BY子句用于将结果集中的行分组到一个或多个列的值相同的组中，以便对每个组执行聚合操作。这允许你对数据进行汇总和分析，通常与聚合函数一起使用。- 按照某个字段或者某些字段进行分组
- 当一条SQL语句中没有group by的话，整张表的数据会自成一组
- 当一条SQL语句中没有group by的话，默认会有一个缺省的group by，将整张表自成一组
1、分组函数一般都会和group by联合使用，且分组函数一定会在group by子句之后执行
这也就是为什么它被称为分组函数的原因
并且任何一个分组函数（count，sum，avg，max，min）都是在group by语句执行结束之后才会执行的。
2、带有group by的语句select子句中的字段必须为分组字段名和聚合函数
select ename, job, max(sal) from emp group by job;
以上语句在MySQL当中，查询结果是有的，但是结果没有意义，在Oracle数据库中会报错。语法错误，Oracle的语法规则比MySQL的语法规则严谨
规则：
当一条语句中有group by的话，select后面只能跟分组函数和参与分组的字段。
3、多个字段联合分组
在 SQL 中，GROUP BY 后面可以跟多个字段，以便按多个列的值对结果集进行分组。这允许你创建更细粒度的分组，以便更精确地分析和汇总数据。
可以理解为将多个字段的数据内容进行组合后在分组
4、案例
找出每个工作岗位的最高薪资
select e.job as '工作岗位', max(e.sal) as '最高薪资' from emp e group by e.job;
+-----------+----------+
| 工作岗位 | 最高薪资 |
+-----------+----------+
| CLERK | 1300.00 |
| SALESMAN | 1600.00 |
| MANAGER | 2975.00 |
| ANALYST | 3000.00 |
| PRESIDENT | 5000.00 |
+-----------+----------+找出每个工作岗位的平均薪资
select e.job, avg(e.sal) from emp e group by e.job;
+-----------+-------------+
| job | avg(e.sal) |
+-----------+-------------+
| CLERK | 1037.500000 |
| SALESMAN | 1400.000000 |
| MANAGER | 2758.333333 |
| ANALYST | 3000.000000 |
| PRESIDENT | 5000.000000 |
+-----------+-------------+找出每个部门不同工作岗位的最高薪资
select e.deptno, e.job, max(e.sal) from emp e group by e.deptno, e.job order by e.deptno;
+--------+-----------+------------+
| deptno | job | max(e.sal) |
+--------+-----------+------------+
| 10 | CLERK | 1300.00 |
| 10 | MANAGER | 2450.00 |
| 10 | PRESIDENT | 5000.00 |
| 20 | ANALYST | 3000.00 |
| 20 | CLERK | 1100.00 |
| 20 | MANAGER | 2975.00 |
| 30 | CLERK | 950.00 |
| 30 | MANAGER | 2850.00 |
| 30 | SALESMAN | 1600.00 |
+--------+-----------+------------+三、HAVING 子句
1、having是对以组为单位的结果集进行过滤，不是对组内的数据进行过滤
HAVING子句通常与GROUP BY子句一起使用，用于在分组后对组进行条件筛选。它允许你筛选分组后的结果集（是以组为单位，表示组的结果集，而不是组内数据的结果集），只包括满足特定条件的分组。- having是对分组之后的以组为单位的结果集进行再次过滤，而不是对每个组内的数据进行过滤
2、having子句中只能有分组函数
- having子句只能有分组函数
- 可以理解为：分组之后，将原表分割成若干个组，组内是没有字段信息的，只有将组合并成原表才会有字段信息，只有分组函数才能将组合并为原表，使用字段信息，因此，having子句中只能有分组函数
- having中的字段只能出现在分组函数的参数中，字段不能独立出现，否则报错：
- ERROR 1054 (42S22): Unknown column 'e.sal' in 'having clause'
3、案例
找出每个部门的最高薪资，要求显示薪资大于2900的数据
第一步：找出每个部门的最高薪资
select e.deptno, max(e.sal) from emp e group by e.deptno;
+--------+------------+
| deptno | max(e.sal) |
+--------+------------+
| 20 | 3000.00 |
| 30 | 2850.00 |
| 10 | 5000.00 |
+--------+------------+
第二步：找出薪资大于2900
//注意：'maxSal'表示字符串，在此处表示为字段，变量名
//但是having子句中的maxSal不能加引号，因为此处是变量值，不能是字符串（变量名）
//这种方式效率较低
select e.deptno as '部门编号', max(e.sal) as 'maxSal' from emp e group by e.deptno having maxSal > 2900;
select e.deptno, max(e.sal) as '最高薪资' from emp e group by e.deptno having 最高薪资 > 2900;
+----------+---------+
| 部门编号 | maxSal |
+----------+---------+
| 20 | 3000.00 |
| 10 | 5000.00 |
+----------+---------+
//这种方式效率比较高
//建议尽量能用where过滤的就使用where 过滤
select e.deptno, max(e.sal) from emp e where e.sal > 2900 group by e.deptno;
找出每个部门的平均薪资，要求显示薪资大于2000的数据
第一步：找出每个部门的平均薪资
select e.deptno, avg(e.sal) from emp e group by e.deptno order by e.deptno asc;
+--------+-------------+
| deptno | avg(e.sal) |
+--------+-------------+
| 10 | 2916.666667 |
| 20 | 2175.000000 |
| 30 | 1566.666667 |
+--------+-------------+
第二步：要求显示平均薪资大于2000的数据
select
e.deptno, avg(e.sal)
from
emp e
group by
e.deptno
having
avg(e.sal) > 2000
order by
e.deptno asc;
+--------+-------------+
| deptno | avg(e.sal) |
+--------+-------------+
| 10 | 2916.666667 |
| 20 | 2175.000000 |
+--------+-------------+
第十一章、连接查询
一、概述
在实际开发中，大部分的情况下都不是从单表中查询数据，一般都是多张表联合查询取出最终的结果。在实际开发中，一般一个业务都会对应多张表，比如：学生和班级，起码得两张表
| stuno | stuname | classno | classname |
| 1 | zhangsan | 1 | 高新一中 |
| 2 | lisi | 1 | 高新一中 |
| ... | ... | ... | ... |
学生和班级信息存储在一张表中，结果就像上面一样，数据会存在大量的重复，导致数据的冗余。
在SQL中，连接查询是一种用于从多个表中检索相关数据的查询操作。连接查询将两个或多个表中的数据按照某种关联条件进行匹配，然后将匹配的数据组合在一起以生成结果集。SQL支持不同类型的连接操作，包括内连接、左连接、右连接和全外连接。
1、编写SQL原则
SQL中的表都是以字段为标识符（变量名），行为基本单位进行遍历
字段均为变量名，变量名中存储的值就是表中的数据，实际对比的是变量名中的值，SQL中的 = 相当于Python中的is，Java中的 ==
二、多表关系
多表关系（有笛卡尔积，先要消除，两张表有一个消除笛卡尔积的连接条件）
1、一对多（多对一）
- 案例：部门与员工的关系
- 关系：一个部门对应多个员工，多个员工对应一个部门
- 实现：在多的一方建立外键，指向一的一方的主键
2、多对多
- 案例：学生与课程的关系
- 关系：一个学生可选择多门课程，一门课程也可供多个学生选择
- 实现：建立第三张中间表，中间表至少包含两个外键，分别关联两方主键
3、一对一
- 关系：一对一关系，多用于单表拆分，将一张表的基础字段放在一张表中，将详情放在另一张表中，以便提升效率
- 实现：在任意一方加入外键，关联另一方主键，并设置外键为unique
三、连接查询的分类
1、按年代划分
- SQL92
- 一些老的DBA可能还在使用这种语法，DBA：Database Administrator，数据库管理员
- SQL99
- 比较新的语法
2、按连接方式划分
- 内连接
- 等值连接
- 非等值连接
- 自连接
- 外连接
- 左外连接（左连接）
- 右外连接（右连接）
- 自连接
- 全连接（这个不讲，很少有！）
四、笛卡尔积现象
1、定义
在表的链接查询方面有一种现象被称为：笛卡尔积现象（笛卡尔乘积现象）
笛卡尔积现象：当两张表进行连接查询的时候，没有任何条件进行限制，最终的查询结果条数是两张表记录条数的乘积
案例：找出每一个员工的部门名称，要求显示员工名和部门名
select e.ename, e.deptno from emp e;
+--------+--------+
| ename | deptno |
+--------+--------+
| SMITH | 20 |
| ALLEN | 30 |
| WARD | 30 |
| JONES | 20 |
| MARTIN | 30 |
| BLAKE | 30 |
| CLARK | 10 |
| SCOTT | 20 |
| KING | 10 |
| TURNER | 30 |
| ADAMS | 20 |
| JAMES | 30 |
| FORD | 20 |
| MILLER | 10 |
+--------+--------+
select d.deptno, d.dname from dept d;
+--------+------------+
| deptno | dname |
+--------+------------+
| 10 | ACCOUNTING |
| 20 | RESEARCH |
| 30 | SALES |
| 40 | OPERATIONS |
+--------+------------+
select e.ename, d.dname from emp e, dept d;
+--------+------------+
| ename | dname |
+--------+------------+
| SMITH | OPERATIONS |
| SMITH | SALES |
| SMITH | RESEARCH |
| SMITH | ACCOUNTING |
| ALLEN | OPERATIONS |
| ALLEN | SALES |
| ALLEN | RESEARCH |
| ALLEN | ACCOUNTING |
......
56 rows in set (0.00 sec)
2、避免笛卡尔积现象
当然是加条件过滤
思考：避免了笛卡尔积现象，会减少记录的匹配次数吗？
不会，此处还是56次，只不过显示的是有效记录，并不会提高效率
//SQL92语法，以后不用
select
e.ename, d.dname
from
emp e, dept d
where
e.deptno = d.deptno
order by
d.dname;
+--------+------------+
| ename | dname |
+--------+------------+
| CLARK | ACCOUNTING |
| KING | ACCOUNTING |
| MILLER | ACCOUNTING |
| SMITH | RESEARCH |
| JONES | RESEARCH |
| SCOTT | RESEARCH |
| ADAMS | RESEARCH |
| FORD | RESEARCH |
| ALLEN | SALES |
| WARD | SALES |
| MARTIN | SALES |
| BLAKE | SALES |
| TURNER | SALES |
| JAMES | SALES |
+--------+------------+
14 rows in set (0.01 sec)五、内连接之等值连接
1、语法
SQL92语法：
select
表1.字段列表，表2.字段列表
from
表1，表2
where
表1.字段=表2.字段（两张表的外键或相关字段相等，消除笛卡尔积）
SQL99语法：语法结构更清晰，标的链接条件和后来的where条件分离了
inner是可以省略的，戴着inner是可读性好一点
select
表1.字段列表,表2.字段列表
from
表1
[inner] join
表2
on
表1.字段列表=表2.字段列表 （连接条件）
[where 条件];
2、特点
条件是等量关系
案例：找出每一个员工的部门名称，要求显示员工名和部门名
SQL92语法:（太老了，不用了）
select
e.ename, d.dname
from
emp e, dept d
where
e.deptno = d.deptno;
SQL99语法：（常用的）
select
e.ename, d.dname
from
emp e
join
dept d
on
e.deptno = d.deptno
order by
d.dname;
+--------+------------+
| ename | dname |
+--------+------------+
| CLARK | ACCOUNTING |
| KING | ACCOUNTING |
| MILLER | ACCOUNTING |
| SMITH | RESEARCH |
| JONES | RESEARCH |
| SCOTT | RESEARCH |
| ADAMS | RESEARCH |
| FORD | RESEARCH |
| ALLEN | SALES |
| WARD | SALES |
| MARTIN | SALES |
| BLAKE | SALES |
| TURNER | SALES |
| JAMES | SALES |
+--------+------------+六、内连接之非等值连接
1、特点
连接条件中的关系是非等量关系
案例：找出每个员工的工资等级，要求显示员工名、工资、工资等级
//inner可以省略，表示内连接
select
e.ename, e.sal, s.grade
from
emp e
inner join
salgrade s
on
e.sal between s.losal and s.hisal
order by
s.grade;
+--------+---------+-------+
| ename | sal | grade |
+--------+---------+-------+
| SMITH | 800.00 | 1 |
| ADAMS | 1100.00 | 1 |
| JAMES | 950.00 | 1 |
| WARD | 1250.00 | 2 |
| MARTIN | 1250.00 | 2 |
| MILLER | 1300.00 | 2 |
| ALLEN | 1600.00 | 3 |
| TURNER | 1500.00 | 3 |
| JONES | 2975.00 | 4 |
| BLAKE | 2850.00 | 4 |
| CLARK | 2450.00 | 4 |
| SCOTT | 3000.00 | 4 |
| FORD | 3000.00 | 4 |
| KING | 5000.00 | 5 |
+--------+---------+-------+
14 rows in set (0.00 sec)七、内连接之自连接
1、特点
一张表看做两张表，自己连接自己
案例：找出每个员工的上级领导，要求显示员工名和对应的领导名
//将一张表看成两张表：员工表的领导编号等于领导表的员工编号
select
e1.empno as '员工编号', e1.ename as '员工名', e2.ename as '领导名'
from
emp e1
inner join
emp e2
on
e1.mgr = e2.empno;
+----------+--------+--------+
| 员工编号 | 员工名 | 领导名 |
+----------+--------+--------+
| 7369 | SMITH | FORD |
| 7499 | ALLEN | BLAKE |
| 7521 | WARD | BLAKE |
| 7566 | JONES | KING |
| 7654 | MARTIN | BLAKE |
| 7698 | BLAKE | KING |
| 7782 | CLARK | KING |
| 7788 | SCOTT | JONES |
| 7844 | TURNER | BLAKE |
| 7876 | ADAMS | SCOTT |
| 7900 | JAMES | BLAKE |
| 7902 | FORD | JONES |
| 7934 | MILLER | CLARK |
+----------+--------+--------+
13 rows in set (0.01 sec)八、外连接（使用多）
1、定义
什么是外连接，和内连接有什么区别？
- 内连接
- 假设A和B表进行连接，使用内连接的话，凡是A表和B表能够匹配上的记录查询出来，这就是内连接，AB两张表没有主副之分，两张表是平等的
- 外连接
- 假设A和B表进行连接，使用外连接的话，AB两张表中有一张表是主表，一张表是副表，主要查询主表中的数据，捎带这查询副表，当副表中的数据没有和主表中的数据匹配上，副表自动模拟出NULL与之匹配
外连接的分类？
- 左外连接（左连接）：表示左边的这张表是主表
- 右外连接（右连接）：表示右边的这张表是主表
左连接有右连接的写法，右连接也有对应的左连接的写法
外连接最重要的特点是：主表的数据无条件的全部查询出来
表名.*:表时该表的所有字段
2、左外连接
2.1、语法
左外连接：查询左表的所有数据，以及交集部分
select
表1.字段列表,表2.字段列表(列的展示及顺序）
from
表1 （若无where则以左边表的全部行的数据为准，右表不能匹配则以null代替，数据行数不定）
left [outer] join //outer可省略
表2
on
表1.字段列表=表2.字段列表（仅此一个连接条件）
[where 条件];
按先后顺序默认表1为左表，表2为右表
案例：找出每个员工的上级领导？（所有员工必须全部查询出来）
内连接之自连接：
select
a.ename as '员工', b.ename as '领导'
from
emp a
inner join
emp b
on
a.mgr = b.empno;
左外连接(左连接)：
select
a.ename as '员工', b.ename as '领导'
from
emp a
left outer join
emp b
on
a.mgr = b.empno;
右外连接（右连接）：
select
a.ename as '员工', b.ename as '领导'
from
emp b
right outer join
emp a
on
a.mgr = b.empno;
+--------+-------+
| 员工 | 领导 |
+--------+-------+
| SMITH | FORD |
| ALLEN | BLAKE |
| WARD | BLAKE |
| JONES | KING |
| MARTIN | BLAKE |
| BLAKE | KING |
| CLARK | KING |
| SCOTT | JONES |
| KING | NULL |
| TURNER | BLAKE |
| ADAMS | SCOTT |
| JAMES | BLAKE |
| FORD | JONES |
| MILLER | CLARK |
+--------+-------+
14 rows in set (0.00 sec)3、右外连接
3.1、语法
右外连接：查询右表的所有数据，以及交集部分
select
表1.字段列表,表2.字段列表(列的展示及顺序）
from
表1
right [outer] join //outer可省略
表2（若无where则以右边表的全部行的数据为准，左表不能匹配则以null代替，数据行数不定）
on
表1.字段列表=表2.字段列表（仅此一个连接条件）
[where 条件];
按先后顺序默认表1为左表，表2为右表
案例：找出哪个部门没有员工？
select
d.*
from
dept d
left outer join
emp e
on
d.deptno = e.deptno
where
e.ename is null;
+--------+------------+--------+
| DEPTNO | DNAME | LOC |
+--------+------------+--------+
| 40 | OPERATIONS | BOSTON |
+--------+------------+--------+
1 row in set (0.00 sec)案例：找出每个部门对应的员工数量
select
d.dname, count(e.ename)
from
dept d
left outer join
emp e
on
d.deptno = e.deptno
group by
d.dname;
+------------+----------+
| 部门名称 | 员工数量 |
+------------+----------+
| ACCOUNTING | 3 |
| RESEARCH | 5 |
| SALES | 6 |
| OPERATIONS | 0 |
+------------+----------+
4 rows in set (0.00 sec)
九、三表连接查询
1、语法
n张表联查至少有n-1个消除笛卡尔积的连接条件，两两去找
表示： 表1 和 表2 先进行表连接，连接之后的表再和 表3 进行连接
select
字段列表
from
表1
inner join
表2
on
连接条件1
inner join
表3
on
连接条件2
...
left outer join
表4
on
连接条件3
...
[where 条件];
2、案例
案例：找出每一个员工的部门名称及工资等级
select
e.ename, d.dname, s.grade
from
emp e
inner join
dept d
on
e.deptno = d.deptno //连接条件1
inner join
salgrade s
on
e.sal between s.losal and s.hisal; //连接条件2
+--------+------------+-------+
| ename | dname | grade |
+--------+------------+-------+
| SMITH | RESEARCH | 1 |
| ALLEN | SALES | 3 |
| WARD | SALES | 2 |
| JONES | RESEARCH | 4 |
| MARTIN | SALES | 2 |
| BLAKE | SALES | 4 |
| CLARK | ACCOUNTING | 4 |
| SCOTT | RESEARCH | 4 |
| KING | ACCOUNTING | 5 |
| TURNER | SALES | 3 |
| ADAMS | RESEARCH | 1 |
| JAMES | SALES | 1 |
| FORD | RESEARCH | 4 |
| MILLER | ACCOUNTING | 2 |
+--------+------------+-------+
14 rows in set (0.00 sec)案例：找出每一个员工的部门名称，工资等级，以及上级领导
select
e.ename as '员工名', d.dname as '部门名称', s.grade as '工资等级', e1.ename as '领导名'
from
emp e
inner join
dept d
on
e.deptno = d.deptno
inner join
salgrade s
on
e.sal between s.losal and s.hisal
left outer join
emp e1
on
e.mgr = e1.empno;
+--------+------------+----------+--------+
| 员工名 | 部门名称 | 工资等级 | 领导名 |
+--------+------------+----------+--------+
| SMITH | RESEARCH | 1 | FORD |
| ALLEN | SALES | 3 | BLAKE |
| WARD | SALES | 2 | BLAKE |
| JONES | RESEARCH | 4 | KING |
| MARTIN | SALES | 2 | BLAKE |
| BLAKE | SALES | 4 | KING |
| CLARK | ACCOUNTING | 4 | KING |
| SCOTT | RESEARCH | 4 | JONES |
| KING | ACCOUNTING | 5 | NULL |
| TURNER | SALES | 3 | BLAKE |
| ADAMS | RESEARCH | 1 | SCOTT |
| JAMES | SALES | 1 | BLAKE |
| FORD | RESEARCH | 4 | JONES |
| MILLER | ACCOUNTING | 2 | CLARK |
+--------+------------+----------+--------+
14 rows in set (0.01 sec)案例：查询高出部门平均薪资的员工
select
e.ename as '员工姓名', e.sal as '薪资', d.dname as '部门名称', a.aa as '部门平均薪资'
from
emp e
inner join
dept d
on
e.deptno = d.deptno
inner join
(select e.deptno, avg(e.sal) as 'aa' from emp e group by e.deptno) a
on
e.deptno = a.deptno
and
e.sal > a.aa;
+----------+---------+------------+--------------+
| 员工姓名 | 薪资 | 部门名称 | 部门平均薪资 |
+----------+---------+------------+--------------+
| ALLEN | 1600.00 | SALES | 1566.666667 |
| JONES | 2975.00 | RESEARCH | 2175.000000 |
| BLAKE | 2850.00 | SALES | 1566.666667 |
| SCOTT | 3000.00 | RESEARCH | 2175.000000 |
| KING | 5000.00 | ACCOUNTING | 2916.666667 |
| FORD | 3000.00 | RESEARCH | 2175.000000 |
+----------+---------+------------+--------------+
6 rows in set (0.00 sec)十、内连接和外连接结果集数据行数思考
内连接：没有主表和副表之分，结果集数据行数不能超过所有连接的表中数据的最大行数
外连接：有主表和副表之分，结果集数据行数不能小于主表中数据的行数
第十二章、子查询
一、概述
子查询 查询结果来自一张表，但查询条件来自不同表：
sql语句中嵌套的select语句，又称嵌套查询
1、定义
select语句中嵌套select语句，被嵌套的select语句是子查询
2、子查询出现的位置
select
...（select）...
from
...（select）...
where
...（select）...
3、对子查询的理解
SQL语句查询出来的实际上表中的数据，字段只是标识符，方便定位具体某个数据
二、where子句中的子查询
案例：找出高于平均薪资的员工信息
select * from emp e where e.sal > avg(e.sal); //错误的写法，where子句中不能写分组函数
第一步：找出平均薪资
select avg(e.sal) from emp e;
+-------------+
| avg(e.sal) |
+-------------+
| 2073.214286 |
+-------------+
1 row in set (0.00 sec)
第二步：where过滤
select * from emp e where e.sal > 2073.214286;
+-------+-------+-----------+------+------------+---------+------+--------+
| EMPNO | ENAME | JOB | MGR | HIREDATE | SAL | COMM | DEPTNO |
+-------+-------+-----------+------+------------+---------+------+--------+
| 7566 | JONES | MANAGER | 7839 | 1981-04-02 | 2975.00 | NULL | 20 |
| 7698 | BLAKE | MANAGER | 7839 | 1981-05-01 | 2850.00 | NULL | 30 |
| 7782 | CLARK | MANAGER | 7839 | 1981-06-09 | 2450.00 | NULL | 10 |
| 7788 | SCOTT | ANALYST | 7566 | 1987-04-19 | 3000.00 | NULL | 20 |
| 7839 | KING | PRESIDENT | NULL | 1981-11-17 | 5000.00 | NULL | 10 |
| 7902 | FORD | ANALYST | 7566 | 1981-12-03 | 3000.00 | NULL | 20 |
+-------+-------+-----------+------+------------+---------+------+--------+
6 rows in set (0.00 sec)
第一步和第二步合并：
select * from emp e where e.sal > (select avg(e.sal) from emp e);三、from子句中的子查询
案例：找出每个部门员工平均薪水的薪资等级
第一步：找出每个部门平均薪水（按照部门编号分组，求sal的平均值）
select e.deptno, avg(e.sal) as 'avgSal' from emp e group by e.deptno;
+--------+-------------+
| deptno | avgSal |
+--------+-------------+
| 20 | 2175.000000 |
| 30 | 1566.666667 |
| 10 | 2916.666667 |
+--------+-------------+
第二步：将以上的查询结果当做临时表t，让t表和salgrade s表连接，条件是：t.avgSal between s.losal and s.hisal
select
t.*, s.grade
from
(select e.deptno, avg(e.sal) as 'avgSal' from emp e group by e.deptno) as t
inner join
salgrade s
on
t.avgSal between s.losal and s.hisal
order by
t.deptno asc;
+--------+-------------+-------+
| deptno | avgSal | grade |
+--------+-------------+-------+
| 10 | 2916.666667 | 4 |
| 20 | 2175.000000 | 4 |
| 30 | 1566.666667 | 3 |
+--------+-------------+-------+
3 rows in set (0.00 sec)
三表联查方式
select
d.deptno, d.dname, a.avgSal, s.grade
from
dept d
inner join
(select e.deptno, avg(e.sal) as 'avgSal' from emp e group by e.deptno) as a
on
d.deptno = a.deptno
inner join
salgrade s
on
a.avgSal between s.losal and s.hisal
order by
d.deptno asc;
+--------+------------+-------------+-------+
| deptno | dname | avgSal | grade |
+--------+------------+-------------+-------+
| 10 | ACCOUNTING | 2916.666667 | 4 |
| 20 | RESEARCH | 2175.000000 | 4 |
| 30 | SALES | 1566.666667 | 3 |
+--------+------------+-------------+-------+
3 rows in set (0.00 sec)案例：找出每个部门员工平均的薪水等级
第一步：找出每个员工的薪水等级
select e.ename, e.sal, e.deptno, s.grade from emp e inner join salgrade s on e.sal between s.losal and s.hisal;
+--------+---------+--------+-------+
| ename | sal | deptno | grade |
+--------+---------+--------+-------+
| SMITH | 800.00 | 20 | 1 |
| ALLEN | 1600.00 | 30 | 3 |
| WARD | 1250.00 | 30 | 2 |
| JONES | 2975.00 | 20 | 4 |
| MARTIN | 1250.00 | 30 | 2 |
| BLAKE | 2850.00 | 30 | 4 |
| CLARK | 2450.00 | 10 | 4 |
| SCOTT | 3000.00 | 20 | 4 |
| KING | 5000.00 | 10 | 5 |
| TURNER | 1500.00 | 30 | 3 |
| ADAMS | 1100.00 | 20 | 1 |
| JAMES | 950.00 | 30 | 1 |
| FORD | 3000.00 | 20 | 4 |
| MILLER | 1300.00 | 10 | 2 |
+--------+---------+--------+-------+
14 rows in set (0.00 sec)
第二步：基于以上结果，继续按照deptno分组，求grade平均值
select
e.deptno, avg(s.grade)
from
emp e
inner join
salgrade s
on
e.sal between s.losal and s.hisal
group by
e.deptno
order by
e.deptno;
+--------+--------------+
| deptno | avg(s.grade) |
+--------+--------------+
| 10 | 3.6667 |
| 20 | 2.8000 |
| 30 | 2.5000 |
+--------+--------------+
3 rows in set (0.00 sec)
子查询解法：
select
t.deptno as '部门编号', d.dname as '部门名称', avg(t.grade) as '平均薪资等级'
from
(select e.deptno, e.ename, s.grade from emp e inner join salgrade s on e.sal between s.losal and s.hisal) as t
inner join
dept d
on
t.deptno = d.deptno
group by
t.deptno
order by
部门编号 asc;
+----------+------------+--------------+
| 部门编号 | 部门名称 | 平均薪资等级 |
+----------+------------+--------------+
| 10 | ACCOUNTING | 3.6667 |
| 20 | RESEARCH | 2.8000 |
| 30 | SALES | 2.5000 |
+----------+------------+--------------+
3 rows in set (0.00 sec)
四、select子句中的子查询
案例：找出每个员工所在的部门名称，要求显示员工名和部门名
解法一：
select
e.ename, d.dname
from
emp e
inner join
dept d
on
e.deptno = d.deptno;
解法二：
select
e.ename, (select d.dname from dept d where e.deptno = d.deptno) as 'dname'
from
emp e;
+--------+------------+
| ename | dname |
+--------+------------+
| SMITH | RESEARCH |
| ALLEN | SALES |
| WARD | SALES |
| JONES | RESEARCH |
| MARTIN | SALES |
| BLAKE | SALES |
| CLARK | ACCOUNTING |
| SCOTT | RESEARCH |
| KING | ACCOUNTING |
| TURNER | SALES |
| ADAMS | RESEARCH |
| JAMES | SALES |
| FORD | RESEARCH |
| MILLER | ACCOUNTING |
+--------+------------+
14 rows in set (0.00 sec)第十三章、union：联合查询
一、概述
联合查询：就是把多次查询的结果合并起来，形成一个新的查询结果集
在SQL中，UNION是一种用于组合多个查询结果集的操作符。UNION操作符用于合并两个或多个SELECT语句的结果，以生成一个包含所有结果的单个结果集。
1、语法
select 字段列表 from 表1 .....
union [all]
select 字段列表 from 表2....;加all：会将全部的数据直接合并在一起 union：对合并的数据去重
对于联合查询的多张表的列数必须保持一致，字段类型也需要保持一致。
2、注意事项
-
两个SELECT语句必须具有相同数量的列，并且这些列的数据类型必须兼容。
mysql> select e.ename, e.sal from emp e
-> union
-> select d.dname from dept d;
ERROR 1222 (21000): The used SELECT statements have a different number of columns-
UNION默认会去除重复的行，如果要包括重复行，可以使用UNION ALL。
-
以第一个select语句的结果集的字段名为准
select e.ename from emp e
union
select d.dname from dept d;
+------------+
| ename |
+------------+
| SMITH |
| ALLEN |
| WARD |
| JONES |
| MARTIN |
| BLAKE |
| CLARK |
| SCOTT |
| KING |
| TURNER |
| ADAMS |
| JAMES |
| FORD |
| MILLER |
| ACCOUNTING |
| RESEARCH |
| SALES |
| OPERATIONS |
+------------+
18 rows in set (0.00 sec)二、案例
案例：找出工作岗位是salesman和manager的员工
写法一：
select
e.ename, e.job
from
emp e
where
e.job = 'salesman' or e.job = 'manager';
写法二：
select
e.ename, e.job
from
emp e
where
e.job in ('salesman', 'manager');
写法三：
select
e.ename, e.job
from
emp e
where
e.job = 'salesman'
union
select
e.ename, e.job
from
emp e
where
e.job = 'manager';
+--------+----------+
| ename | job |
+--------+----------+
| ALLEN | SALESMAN |
| WARD | SALESMAN |
| MARTIN | SALESMAN |
| TURNER | SALESMAN |
| JONES | MANAGER |
| BLAKE | MANAGER |
| CLARK | MANAGER |
+--------+----------+
7 rows in set (0.00 sec)第十四章、limit：分页查询
一、概述
在SQL中，LIMIT是一个用于限制查询结果返回的行数的关键字。它通常与SELECT语句一起使用，以限制查询结果的大小，以便只返回满足条件的前几行。不同的数据库管理系统（DBMS）可能使用不同的语法，但LIMIT的基本用法是相似的。
1、MySQL特有limit
limit是MySQL数据库管理系统特有的，其他数据库中没有，不通用。
Oracle中有一个相同的机制，rownum
2、limit的作用
读取结果集中的部分数据
3、语法及执行顺序
limit是SQL语句中最后执行的一个环节
select 5
字段列表
from 1
表名
where 2
条件
group by 3
字段
having 4
分组后的过滤条件
order by 6
排序字段
limit 7
startIndex, length //表示从起始索引+1行开始显示
- startIndex：起始索引
- 起始索引=（查询页码-1）*每页显示行数 第一页索引为0
- 第一页查询中起始索引可省略，默认为0
- length：表示取几个（每页显示行数）
案例：取出工资前5名的员工（思路：降序取前5个）
解法一：
select
e.ename, e.sal
from
emp e
order by
e.sal desc
limit
5; //起始索引默认为0，可省略
解法二：
select
e.ename, e.sal
from
emp e
order by
e.sal desc
limit
0, 5;
+-------+---------+
| ename | sal |
+-------+---------+
| KING | 5000.00 |
| SCOTT | 3000.00 |
| FORD | 3000.00 |
| JONES | 2975.00 |
| BLAKE | 2850.00 |
+-------+---------+
5 rows in set (0.00 sec)案例：找出工资排名在第4到第9名的员工
select
e.ename, e.sal
from
emp e
order by
e.sal desc
limit
3, 6;
+--------+---------+
| ename | sal |
+--------+---------+
| JONES | 2975.00 |
| BLAKE | 2850.00 |
| CLARK | 2450.00 |
| ALLEN | 1600.00 |
| TURNER | 1500.00 |
| MILLER | 1300.00 |
+--------+---------+
6 rows in set (0.01 sec)二、通用的标准分页SQL
每页显示3条记录
- 第1页：0，3
- 第2页：3，3
- 第3页：6，3
- 第4页：9，3
- 第5页：12，3
每页显示pageSize条记录：
第pageNo页：（pageNo - 1）* pageSize，pageSize
pageSize：是每页显示多少条记录
pageNo：是显示第几页
java代码{
int pageNo = 2; //页码是2
int pageSize = 10; //每页显示10条
limit （pageNo - 1）* 10，10
}
268
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
