SQLyou基础知识总结(带案例)
提示:文章写完后,目录可以自动生成,如何生成可参考右边的帮助文档
文章目录
- 一,SQL简介
- 1. **关系型数据库的背景**
- 2. **SQL的核心功能**
- 3. **SQL的特点**
- 4. **常见的数据库管理系统 (DBMS)**
- 5. **SQL标准**
- 6. **事务管理**
- 7. **SQL与大数据**
- 二,DDL基础操作
- DDL的基本操作
- 1. CREATE 语法
- **语法**:
- **例子**:
- 2. ALTER 语法
- **语法**:
- **例子**:
- 3. DROP 语法
- **语法**:
- **例子**:
- 4. TRUNCATE 语法
- **语法**:
- **例子**:
- DDL操作的案例
- 1. 创建一个新的表 `departments`
- 2. 修改 `departments` 表:添加列、修改列、删除列
- 3. 删除整个 `departments` 表
- 4. 清空 `departments` 表中的所有数据
- DDL 操作特点
- 三,DML基础操作
- DML的基本操作
- 1. SELECT 语法
- **语法**:
- **例子**:
- 2. INSERT 语法
- **语法**:
- **例子**:
- 3. UPDATE 语法
- **语法**:
- **例子**:
- 4. DELETE 语法
- **语法**:
- **例子**:
- DML操作的案例
- 1. 使用 `SELECT` 查询数据
- 2. 使用 `INSERT` 向表中插入数据
- 3. 使用 `UPDATE` 更新数据
- 4. 使用 `DELETE` 删除数据
- DML 操作的特点
- 四,DCL的基本操作
- DCL 的基本操作
- 1. GRANT 语法
- **语法**:
- **例子**:
- 2. REVOKE 语法
- **语法**:
- **例子**:
- 权限类型概述
- DCL 操作的案例
- 1. 授予用户 `admin` 对 `employees` 表的所有权限:
- 2. 撤销用户 `admin` 对 `employees` 表的 `DELETE` 权限:
- 3. 授予用户 `manager` 对 `departments` 表的 SELECT 权限,并允许他授予该权限给其他用户:
- 4. 撤销用户 `manager` 的 SELECT 权限的转授权:
- DCL 特点
- 总结
- 五,DQL的基本操作
- DQL(Data Query Language,数据查询语言)
- DQL的基本操作
- 1. SELECT 语法
- **基本语法**:
- **例子**:
- 2. WHERE 子句
- **语法**:
- **常用的运算符**:
- **例子**:
- 3. ORDER BY 子句
- **语法**:
- **例子**:
- 4. LIMIT 子句
- **语法**:
- **例子**:
- 5. GROUP BY 和 HAVING 子句
- **语法**:
- **例子**:
- 6. 聚合函数
- **例子**:
- DQL 操作的案例
- 1. 查询员工的名字、姓氏和工资,按工资降序排列,并返回前 5 名:
- 2. 查询在 "IT" 部门工作的员工信息,并按照入职日期排序:
- 3. 查询每个部门的平均工资,并筛选出平均工资超过 6000 的部门:
- 4. 查询姓氏以字母 "A" 开头的员工数量:
- DQL 的特点
- 总结
SQL,全称为Structured Query Language(结构化查询语言),是一种用于管理和操作关系型数据库的标准语言。它主要用于执行查询、更新和管理数据库中的数据。以下是关于SQL的基础知识概述:
一,SQL简介
1. 关系型数据库的背景
SQL 是专门为与关系型数据库交互而设计的语言。关系型数据库是以表格(表)的形式存储数据的,每个表包含若干行(记录)和列(字段)。表与表之间可以通过外键来建立关联,形成关系模型。
2. SQL的核心功能
SQL 语言的核心功能可以概括为以下几个方面:
-
数据查询:SQL 的最主要功能是从数据库中查询数据。最常用的 SQL 命令是
SELECT
,用于从数据库中提取特定的数据集。 -
数据操作:可以对数据库中的数据进行添加、更新、删除等操作。常用命令有
INSERT
(插入)、UPDATE
(更新)、DELETE
(删除)。 -
数据定义:SQL 也可以用于创建、修改和删除数据库中的表结构或其他对象。相关的命令包括
CREATE
(创建)、ALTER
(修改)、DROP
(删除)。 -
数据控制:SQL 允许对数据库中的数据进行权限管理和事务控制。命令如
GRANT
(赋予权限)、REVOKE
(撤销权限)和COMMIT
(提交事务)等,用于控制数据的安全性和完整性。
3. SQL的特点
-
声明性语言:SQL 是一种声明性语言,用户只需描述“要做什么”,而不需要详细说明“怎么做”。比如,用户用
SELECT
命令告诉数据库要获取什么数据,而数据库负责执行具体的检索操作。 -
跨平台支持:SQL 是一个标准化语言,虽然不同的数据库管理系统(如 MySQL、PostgreSQL、Oracle、SQL Server 等)有各自的方言和扩展,但核心部分是通用的。这使得学习 SQL 后可以很容易地在不同的数据库系统中迁移。
-
关系代数与关系演算:SQL 是基于关系代数和关系演算的,它支持从多个表中进行数据提取、数据联接、子查询等复杂操作,以满足不同场景下的数据处理需求。
4. 常见的数据库管理系统 (DBMS)
SQL 是与各种关系型数据库管理系统 (DBMS) 一起使用的,如:
- MySQL:开源、广泛使用的数据库系统,适合Web应用开发。
- PostgreSQL:功能强大的开源数据库,支持复杂查询和事务处理。
- Oracle:商用数据库,常用于企业级应用,具有强大的功能和扩展性。
- SQL Server:微软开发的关系型数据库,适用于Windows环境的企业级应用。
5. SQL标准
SQL 有多个标准版本,由国际标准化组织 (ISO) 发布。最早的 SQL 标准是 1986 年发布的 SQL-86,之后经历了多次更新,包括:
- SQL-92
- SQL:1999
- SQL:2003
- SQL:2008
- SQL:2011
虽然有标准,但不同数据库管理系统的实现和扩展略有不同。例如,MySQL 和 SQL Server 在一些数据类型、函数和查询优化上有自己的实现方式。
6. 事务管理
事务是 SQL 中一个重要的概念。一个事务代表一组SQL操作,它们要么全部成功,要么全部失败,以保证数据的一致性。事务具有以下四个特性,称为 ACID 特性:
- 原子性(Atomicity):事务中的操作要么全部完成,要么全部不完成。
- 一致性(Consistency):事务执行前后,数据库保持一致的状态。
- 隔离性(Isolation):并发事务之间互不干扰。
- 持久性(Durability):事务一旦提交,结果将永久保存。
7. SQL与大数据
随着大数据技术的发展,SQL 也逐渐进入大数据领域。现在,许多大数据框架(如 Hadoop、Spark)也支持使用 SQL 来查询海量数据,虽然底层机制不同于传统的关系型数据库,但 SQL 的通用性依旧广泛被采用。
二,DDL基础操作
DDL(Data Definition Language,数据定义语言)是SQL中的一个子集,用于定义和管理数据库中的结构和对象。它的主要功能包括创建、修改和删除数据库中的表、索引、视图等对象。
DDL的基本操作
DDL包含以下几个核心命令:
- CREATE:创建数据库对象(如表、索引等)。
- ALTER:修改数据库对象(如表的结构、索引等)。
- DROP:删除数据库对象(如表、索引等)。
- TRUNCATE:删除表中的所有数据,但保留表结构。
1. CREATE 语法
CREATE
语句用于创建数据库对象,最常见的就是创建表。
语法:
CREATE TABLE table_name (
column1 datatype [constraint],
column2 datatype [constraint],
...
);
table_name
: 表名。column1, column2
: 列名。datatype
: 数据类型(如INT
、VARCHAR
、DATE
等)。constraint
: 约束(如PRIMARY KEY
、NOT NULL
、UNIQUE
等)。
例子:
CREATE TABLE employees (
employee_id INT PRIMARY KEY, -- 员工ID,作为主键
first_name VARCHAR(50) NOT NULL, -- 员工的名字,不能为空
last_name VARCHAR(50) NOT NULL, -- 员工的姓氏,不能为空
hire_date DATE, -- 员工的入职日期
salary DECIMAL(10, 2) -- 员工的工资,包含10位数字,其中2位为小数
);
解释:
- 创建了一个名为
employees
的表,包含employee_id
、first_name
、last_name
、hire_date
、salary
5个列。 employee_id
是主键,不能重复且不能为空。first_name
和last_name
不能为空。
2. ALTER 语法
ALTER
语句用于修改现有的数据库对象,如添加列、删除列或修改列的数据类型。
语法:
ALTER TABLE table_name
ADD column_name datatype [constraint]; -- 添加列
ALTER TABLE table_name
MODIFY column_name datatype [constraint]; -- 修改列
ALTER TABLE table_name
DROP COLUMN column_name; -- 删除列
例子:
- 添加列:
ALTER TABLE employees
ADD email VARCHAR(100); -- 为表添加一个 email 列
- 修改列:
ALTER TABLE employees
MODIFY salary DECIMAL(12, 2); -- 修改 salary 列,扩展数字长度
- 删除列:
ALTER TABLE employees
DROP COLUMN hire_date; -- 删除 hire_date 列
3. DROP 语法
DROP
语句用于删除数据库中的表、索引或其他对象。执行此操作后,对象将被完全移除,且无法恢复。
语法:
DROP TABLE table_name;
例子:
DROP TABLE employees; -- 删除 employees 表
注意:删除表后,表中的所有数据也会丢失,无法恢复。
4. TRUNCATE 语法
TRUNCATE
语句用于清空表中的所有数据,但保留表结构。与 DELETE
不同,TRUNCATE
不会逐行删除,而是直接释放数据空间,速度较快。
语法:
TRUNCATE TABLE table_name;
例子:
TRUNCATE TABLE employees; -- 清空 employees 表中的所有数据
注意:TRUNCATE
操作不能触发 ON DELETE
触发器,并且不能用于表中有外键依赖的表。
DDL操作的案例
1. 创建一个新的表 departments
CREATE TABLE departments (
department_id INT PRIMARY KEY, -- 部门ID,主键
department_name VARCHAR(50) NOT NULL, -- 部门名称,不能为空
manager_id INT, -- 经理ID,可以为空
location VARCHAR(100) -- 部门地址
);
2. 修改 departments
表:添加列、修改列、删除列
- 添加新列
budget
:
ALTER TABLE departments
ADD budget DECIMAL(10, 2); -- 添加预算列
- 修改列
location
的长度:
ALTER TABLE departments
MODIFY location VARCHAR(150); -- 将 location 的长度从100修改为150
- 删除列
manager_id
:
ALTER TABLE departments
DROP COLUMN manager_id; -- 删除经理ID列
3. 删除整个 departments
表
DROP TABLE departments; -- 完全删除 departments 表及其数据
4. 清空 departments
表中的所有数据
TRUNCATE TABLE departments; -- 清空表但保留表结构
DDL 操作特点
- DDL 操作一般不会生成回滚日志,所以一旦执行
DROP
或TRUNCATE
,数据将无法恢复。 - DDL 操作在事务中执行时,会自动提交,也就是说在事务中执行 DDL 语句,事务会立即生效并自动提交。
三,DML基础操作
DML(Data Manipulation Language,数据操作语言)是SQL的一个子集,专门用于对数据库中的数据进行查询、插入、更新和删除等操作。与DDL操作数据库结构不同,DML操作的是表中的具体数据。DML语句不会影响数据库的结构。
DML的基本操作
DML 主要包括以下几个操作:
- SELECT:从表中查询数据。
- INSERT:向表中插入数据。
- UPDATE:更新表中的已有数据。
- DELETE:删除表中的数据。
1. SELECT 语法
SELECT
语句用于从表中查询数据,是SQL中最常用的DML操作。
语法:
SELECT column1, column2, ...
FROM table_name
[WHERE condition]
[ORDER BY column1, column2, ... [ASC|DESC]]
[LIMIT number];
column1, column2
: 要查询的列名。如果要查询所有列,使用*
。table_name
: 表名。WHERE condition
: 查询条件(可选)。ORDER BY
: 按指定列排序(可选)。LIMIT
: 限制返回的行数(可选)。
例子:
- 查询所有列:
SELECT * FROM employees; -- 查询 employees 表中的所有列和数据
- 查询指定列并设置条件:
SELECT first_name, last_name, salary
FROM employees
WHERE salary > 50000; -- 查询工资大于50000的员工的名字和工资
- 排序和限制查询结果:
SELECT first_name, last_name, salary
FROM employees
ORDER BY salary DESC -- 按照工资降序排列
LIMIT 10; -- 只返回前10条记录
2. INSERT 语法
INSERT
语句用于向表中插入新的数据行。
语法:
INSERT INTO table_name (column1, column2, ...)
VALUES (value1, value2, ...);
column1, column2
: 要插入值的列名。value1, value2
: 插入的具体值,顺序与列名一致。
例子:
- 插入一条完整的记录:
INSERT INTO employees (employee_id, first_name, last_name, hire_date, salary)
VALUES (101, 'John', 'Doe', '2023-09-01', 75000.00); -- 向 employees 表中插入一条新记录
- 插入部分列(其他列使用默认值):
INSERT INTO employees (employee_id, first_name, last_name)
VALUES (102, 'Jane', 'Smith'); -- 只插入 ID 和姓名,其他列将使用默认值
3. UPDATE 语法
UPDATE
语句用于修改表中已有的数据。通常会搭配 WHERE
子句一起使用,以限制需要更新的行。
语法:
UPDATE table_name
SET column1 = value1, column2 = value2, ...
[WHERE condition];
column1, column2
: 要更新的列名。value1, value2
: 更新后的值。WHERE condition
: 限定更新范围的条件。如果没有WHERE
子句,所有行都会被更新。
例子:
- 更新指定行:
UPDATE employees
SET salary = 80000 -- 将工资设置为80000
WHERE employee_id = 101; -- 仅更新 employee_id 为101的行
- 更新多列:
UPDATE employees
SET salary = salary * 1.1, -- 将工资增加10%
hire_date = '2023-01-01' -- 将入职日期设为指定日期
WHERE employee_id = 102; -- 仅更新 employee_id 为102的员工
4. DELETE 语法
DELETE
语句用于删除表中的数据行。为了避免误操作,通常会与 WHERE
子句一起使用。
语法:
DELETE FROM table_name
[WHERE condition];
table_name
: 表名。WHERE condition
: 限定需要删除的行。如果没有WHERE
子句,表中所有行都会被删除,但表结构不会被删除。
例子:
- 删除指定行:
DELETE FROM employees
WHERE employee_id = 102; -- 删除 employee_id 为102的员工
- 删除所有行:
DELETE FROM employees; -- 删除 employees 表中的所有数据,但保留表结构
注意:DELETE
只会删除表中的数据,不会影响表的结构,与 DROP
和 TRUNCATE
不同。
DML操作的案例
1. 使用 SELECT
查询数据
-- 查询工资高于60000的员工的ID和姓名
SELECT employee_id, first_name, last_name
FROM employees
WHERE salary > 60000;
解释:这条语句查询了工资大于60000的员工的ID、名字和姓氏。
2. 使用 INSERT
向表中插入数据
-- 向 employees 表中插入一条新的员工记录
INSERT INTO employees (employee_id, first_name, last_name, hire_date, salary)
VALUES (103, 'Alice', 'Johnson', '2023-08-15', 55000.00);
解释:插入一条新记录,ID为103,姓名为Alice Johnson,入职日期为2023年8月15日,工资为55000。
3. 使用 UPDATE
更新数据
-- 将员工ID为103的员工工资提高10%
UPDATE employees
SET salary = salary * 1.1
WHERE employee_id = 103;
解释:将 employee_id
为103的员工工资增加10%。
4. 使用 DELETE
删除数据
-- 删除员工ID为103的记录
DELETE FROM employees
WHERE employee_id = 103;
解释:删除 employee_id
为103的员工记录。
DML 操作的特点
- 可回滚:DML 操作属于事务的一部分,可以在事务内回滚。如果删除、插入或更新数据后事务没有提交,可以回滚到之前的状态。
- 影响数据,而非结构:DML 操作只会影响表中的数据,不会改变表的结构。对于结构修改,需要使用DDL操作。
- 事务支持:DML 操作可以在事务中执行。事务可以通过
COMMIT
提交,也可以通过ROLLBACK
回滚,保证数据的一致性。
通过DML的操作,用户可以方便地对数据库中的数据进行增删改查操作,是SQL日常操作中最频繁使用的部分。
四,DCL的基本操作
DCL (Data Control Language,数据控制语言) 是 SQL 中用于控制数据库对象的访问权限的一个子集。它的主要功能是授予或撤销用户对数据库对象的访问权限,从而确保数据库的安全性。DCL 包括两个主要命令:GRANT
和 REVOKE
。
DCL 的基本操作
DCL 的两个核心命令是:
- GRANT:授予用户或角色对数据库对象的权限。
- REVOKE:撤销用户或角色对数据库对象的权限。
1. GRANT 语法
GRANT
语句用于授予一个或多个用户对数据库对象(如表、视图、序列等)的访问权限。可以授予的权限包括 SELECT、INSERT、UPDATE、DELETE、EXECUTE 等。
语法:
GRANT privilege [, ...]
ON object
TO user [, ...]
[WITH GRANT OPTION];
privilege
: 权限类型(如SELECT
、INSERT
、UPDATE
、DELETE
等)。object
: 数据库对象(如表、视图、序列等)。user
: 被授予权限的用户或角色。WITH GRANT OPTION
: 允许用户将该权限授予其他用户(可选项)。
例子:
- 授予 SELECT 权限:
GRANT SELECT
ON employees
TO john; -- 授予用户 john 对 employees 表的 SELECT 权限
- 授予多个权限:
GRANT SELECT, INSERT, UPDATE
ON employees
TO alice; -- 授予用户 alice 对 employees 表的 SELECT、INSERT 和 UPDATE 权限
- 授予权限并允许转授:
GRANT SELECT
ON departments
TO bob
WITH GRANT OPTION; -- 授予用户 bob 对 departments 表的 SELECT 权限,并允许他将该权限授予其他用户
解释:
- 第一个例子中,用户
john
被授予了对employees
表的查询权限。 - 第二个例子中,用户
alice
可以查询、插入和更新employees
表中的数据。 - 第三个例子中,用户
bob
除了拥有查询departments
表的权限外,还可以将此权限授予其他用户。
2. REVOKE 语法
REVOKE
语句用于撤销一个或多个用户对数据库对象的权限。撤销操作会移除此前使用 GRANT
授予的权限。
语法:
REVOKE privilege [, ...]
ON object
FROM user [, ...];
privilege
: 需要撤销的权限类型。object
: 数据库对象。user
: 被撤销权限的用户或角色。
例子:
- 撤销 SELECT 权限:
REVOKE SELECT
ON employees
FROM john; -- 撤销用户 john 对 employees 表的 SELECT 权限
- 撤销多个权限:
REVOKE SELECT, INSERT
ON employees
FROM alice; -- 撤销用户 alice 对 employees 表的 SELECT 和 INSERT 权限
- 撤销转授权限:
REVOKE GRANT OPTION FOR SELECT
ON departments
FROM bob; -- 撤销用户 bob 对 departments 表的 SELECT 权限的转授权
解释:
- 在第一个例子中,用户
john
失去了对employees
表的查询权限。 - 在第二个例子中,用户
alice
被撤销了对employees
表的查询和插入权限,但如果她之前被授予了其他权限(如UPDATE
),这些权限将不会受到影响。 - 在第三个例子中,用户
bob
不再能够将SELECT
权限授予其他用户,但他仍然拥有SELECT
权限。
权限类型概述
- SELECT:允许用户查询表中的数据。
- INSERT:允许用户向表中插入新数据。
- UPDATE:允许用户更新表中的数据。
- DELETE:允许用户删除表中的数据。
- EXECUTE:允许用户执行存储过程或函数。
- REFERENCES:允许用户在表的外键约束中引用其他表的主键。
DCL 操作的案例
1. 授予用户 admin
对 employees
表的所有权限:
GRANT SELECT, INSERT, UPDATE, DELETE
ON employees
TO admin; -- 授予用户 admin 对 employees 表的所有权限
2. 撤销用户 admin
对 employees
表的 DELETE
权限:
REVOKE DELETE
ON employees
FROM admin; -- 撤销用户 admin 对 employees 表的 DELETE 权限
3. 授予用户 manager
对 departments
表的 SELECT 权限,并允许他授予该权限给其他用户:
GRANT SELECT
ON departments
TO manager
WITH GRANT OPTION; -- 授予 manager 用户 SELECT 权限,并允许他将权限授予他人
4. 撤销用户 manager
的 SELECT 权限的转授权:
REVOKE GRANT OPTION FOR SELECT
ON departments
FROM manager; -- 撤销 manager 用户的 SELECT 权限的转授权
DCL 特点
-
安全性控制:DCL 提供了一种灵活的机制来控制谁可以访问和操作数据库中的数据。这对于保护敏感数据免受未经授权的访问至关重要。
-
细粒度的权限管理:管理员可以授予或撤销特定的权限,以满足不同用户的需求。例如,可以授予一个用户查询权限而不给予插入或删除权限。
-
角色管理:权限不仅可以授予个别用户,还可以授予角色。这样,可以将一组常见权限授予一群用户,从而简化权限管理。
-
转授机制:
WITH GRANT OPTION
提供了一个强大的功能,允许用户将权限授予其他用户,这样可以实现权限的级联管理。
总结
DCL 是 SQL 中用于管理数据库访问权限的重要部分。通过 GRANT
和 REVOKE
,数据库管理员能够有效地控制谁能够访问和修改数据库对象,从而保护数据库的安全性。掌握 DCL 可以帮助你确保数据库的安全性和一致性,同时也能为不同用户提供适当的权限以满足业务需求。
五,DQL的基本操作
DQL(Data Query Language,数据查询语言)
DQL 是 SQL 语言的一个子集,用于从数据库中查询数据。DQL 的核心是 SELECT
语句,它用于从一个或多个表中检索数据,并根据指定的条件进行筛选、排序和分组。
DQL的基本操作
DQL 的核心命令是 SELECT
,它主要用于执行以下任务:
- 查询表中的数据
- 通过条件过滤数据
- 对数据进行排序、分组
- 使用聚合函数计算数据
1. SELECT 语法
SELECT
语句是 SQL 查询数据的基本语句,可以从表中提取数据,并且可以与其他子句配合使用进行高级查询。
基本语法:
SELECT column1, column2, ...
FROM table_name
WHERE condition
ORDER BY column_name [ASC|DESC];
column1, column2
: 要查询的列名,或使用*
来查询所有列。table_name
: 要查询的表名。WHERE condition
: 用于指定筛选条件。ORDER BY
: 对查询结果进行排序,ASC
为升序(默认),DESC
为降序。
例子:
SELECT first_name, last_name, salary
FROM employees
WHERE salary > 5000
ORDER BY salary DESC;
解释:
- 从
employees
表中查询first_name
、last_name
和salary
列。 - 条件是
salary
大于 5000。 - 按照
salary
以降序方式排序。
2. WHERE 子句
WHERE
子句用于过滤查询结果,只有满足条件的记录才会被返回。可以使用逻辑运算符、比较运算符和其他条件组合。
语法:
SELECT column1, column2, ...
FROM table_name
WHERE condition;
常用的运算符:
=
,!=
,<
,<=
,>
,>=
:比较运算符AND
,OR
:逻辑运算符BETWEEN ... AND ...
:查询在指定范围内的数据LIKE
:模糊匹配,通常与通配符%
(匹配任意字符)一起使用IN
:查询值在一个指定集合中的记录IS NULL
:查询空值
例子:
- 使用
WHERE
和BETWEEN
:
SELECT first_name, last_name, hire_date
FROM employees
WHERE hire_date BETWEEN '2020-01-01' AND '2023-01-01';
解释:
- 查询
employees
表中hire_date
在 2020 年到 2023 年之间的员工信息。
- 使用
LIKE
:
SELECT first_name, last_name
FROM employees
WHERE last_name LIKE 'S%';
解释:
- 查询
employees
表中last_name
以字母“S”开头的员工。
3. ORDER BY 子句
ORDER BY
子句用于对查询结果进行排序。可以按一个或多个列排序,默认是升序排列,也可以指定降序。
语法:
SELECT column1, column2, ...
FROM table_name
ORDER BY column_name [ASC|DESC];
ASC
:升序排列(默认)。DESC
:降序排列。
例子:
SELECT first_name, last_name, salary
FROM employees
ORDER BY salary DESC;
解释:
- 按照
salary
列以降序方式对结果排序。
4. LIMIT 子句
LIMIT
子句用于限制查询结果的返回行数。它对于大数据查询时非常有用,可以避免一次性返回过多结果。
语法:
SELECT column1, column2, ...
FROM table_name
LIMIT number_of_rows;
例子:
SELECT first_name, last_name, salary
FROM employees
ORDER BY salary DESC
LIMIT 10;
解释:
- 按照
salary
降序排列并返回前 10 名员工的first_name
、last_name
和salary
。
5. GROUP BY 和 HAVING 子句
GROUP BY
用于将数据分组,并且通常与聚合函数一起使用,如 COUNT()
、SUM()
、AVG()
、MAX()
、MIN()
等。HAVING
子句用于过滤分组后的结果。
语法:
SELECT column1, aggregate_function(column2)
FROM table_name
GROUP BY column1
HAVING condition;
aggregate_function
:聚合函数,如COUNT()
、SUM()
、AVG()
。GROUP BY
:按指定列进行分组。HAVING
:对分组后的结果进行筛选(类似WHERE
,但作用于分组结果)。
例子:
- 按部门分组,统计每个部门的员工数量:
SELECT department_id, COUNT(employee_id) AS employee_count
FROM employees
GROUP BY department_id;
解释:
- 按照
department_id
分组,并计算每个部门的员工人数。
- 按部门分组,筛选出员工数量超过 5 的部门:
SELECT department_id, COUNT(employee_id) AS employee_count
FROM employees
GROUP BY department_id
HAVING COUNT(employee_id) > 5;
解释:
- 按照
department_id
分组,返回员工数大于 5 的部门。
6. 聚合函数
聚合函数是 DQL 中非常常用的工具,通常与 GROUP BY
一起使用,用于计算数据的统计信息。
- COUNT():计算行数
- SUM():求和
- AVG():计算平均值
- MAX():返回最大值
- MIN():返回最小值
例子:
- 计算所有员工的平均工资:
SELECT AVG(salary) AS average_salary
FROM employees;
解释:
- 计算
employees
表中所有员工的平均工资。
- 统计每个部门的员工总工资:
SELECT department_id, SUM(salary) AS total_salary
FROM employees
GROUP BY department_id;
解释:
- 按照
department_id
分组,并计算每个部门的员工工资总和。
DQL 操作的案例
1. 查询员工的名字、姓氏和工资,按工资降序排列,并返回前 5 名:
SELECT first_name, last_name, salary
FROM employees
ORDER BY salary DESC
LIMIT 5;
2. 查询在 “IT” 部门工作的员工信息,并按照入职日期排序:
SELECT first_name, last_name, hire_date
FROM employees
WHERE department_id = (SELECT department_id FROM departments WHERE department_name = 'IT')
ORDER BY hire_date;
3. 查询每个部门的平均工资,并筛选出平均工资超过 6000 的部门:
SELECT department_id, AVG(salary) AS average_salary
FROM employees
GROUP BY department_id
HAVING AVG(salary) > 6000;
4. 查询姓氏以字母 “A” 开头的员工数量:
SELECT COUNT(*) AS count_A
FROM employees
WHERE last_name LIKE 'A%';
DQL 的特点
SELECT
语句是 DQL 的核心,用于从表中查询数据。- 可以通过
WHERE
、ORDER BY
、LIMIT
等子句进行查询条件的细化。 - 聚合函数与
GROUP BY
、HAVING
联合使用,支持复杂的统计和分析任务。 - DQL 操作通常不会修改数据,仅用于数据的读取和分析。
总结
DQL 是 SQL 中用于查询和提取数据的部分。通过 SELECT
语句,用户可以非常灵活地从数据库中获取所需的信息,并可以通过不同的子句和聚合函数对数据进行过滤、排序和分组。DQL 是数据分析、报表生成和应用程序开发中的核心部分。