MySQL基础知识及操作

一、 数据库分类

1.1 什么是数据库

数据库 是一个有组织的集合，用于存储和管理数据的系统。它是一个软件系统，被设计用来存储、检索和管理数据，并提供数据的快速访问和处理。数据库可以被看作是一种特殊的文件系统，但与传统的文件系统不同的是：它能够更加高效的存储和管理大量结构化数据。

1.2 数据库分类

1.2.1 关系型数据库（RDBMS）

• 定义：基于关系模型，数据以表格形式存储，表之间通过外键关联。

• 特点：

  • 严格的表结构（Schema）

  • 支持 SQL 语言

  • 事务的 ACID 特性（原子性、一致性、隔离性、持久性）

  • 适合复杂查询和事务场景

• 代表数据库：MySQL、Oracle、PostgreSQL、SQL Server。

1.2.2 非关系型数据库（NoSQL）

定义：非结构化存储，支持灵活的数据模型。

分类：

• 文档型：MongoDB（JSON 格式存储）

• 键值型：Redis、Memcached

• 列存储：Cassandra、HBase

• 图数据库：Neo4j

特点：

高扩展性、高性能

弱化 Schema，支持动态数据结构

适合高并发、大数据量场景（如社交网络、实时分析）

1.2.3 时序数据库（Time-Series Database）

定义： 专门用于存储和查询 时间序列数据（按时间顺序生成的数据点集合）的数据库类型。

核心特点：

1. 时间为主键：数据以时间戳为默认主索引

2. 高吞吐写入：支持每秒百万级数据点写入

3. 高效压缩：时序数据专用压缩算法（如Delta编码）

4. 时间窗口聚合：内置滑动窗口、降采样函数

5. 冷热分层：自动分离热数据与历史数据

主流时序数据库对比

数据库	类型	核心优势	适用场景	开源协议
InfluxDB	原生时序数据库	- 专为时序优化<br>- 内置TSQL查询语言<br>- 生态完善（Telegraf+Grafana集成）	IoT监控、实时指标分析	商业版+开源版
TDengine	分布式时序数据库	- 超高压缩率（1/10存储空间）<br>- 自带流式计算引擎<br>- 兼容SQL语法	工业物联网、车联网	Apache 2.0
ClickHouse	列式分析数据库	- PB级数据分析能力<br>- 支持标准SQL<br>- 向量化查询引擎	日志分析、时序大数据聚合	Apache 2.0

 

 

二、SQL 语言

2.1 什么是 SQL？

SQL（Structured Query Language)，标准 SQL 由 ANSI 标准委员会管理，从而称为 ANSI SQL。各个 DBMS 都有自己的实现，如 PL/SQL、Transact-SQL 等。

2.2 SQL 分类

类型	全称	关键字	功能
DDL	数据定义语言	CREATE, ALTER, DROP	定义/修改表结构
DML	数据操作语言	INSERT, UPDATE, DELETE	增删改数据
DQL	数据查询语言	SELECT	查询数据
DCL	数据控制语言	GRANT, REVOKE	权限管理

2.3 SQL 语法特点

1、不区分大小写

SELECT * FROM users;  -- 等价于 select * from Users;

2、多条 SQL 语句必须以分号（;）分隔。

3、SQL 支持三种注释

## 注释1
-- 注释2
/* 注释3 */

 

三、MySQL 简介

3.1 什么是 MySQL？

MySQL 是一个开源的关系型数据库管理系统（RDBMS），由瑞典公司 MySQL AB 开发，现隶属于 Oracle 公司。

• 开源特性：遵循 GPL 协议，社区版免费使用，企业版提供额外功能和支持服务。

• 名称由来：创始人 Michael Widenius 的女儿名为 "My"，因此命名为 MySQL。

3.2 发展历程

• 1995 年：首个版本发布，迅速成为 Web 开发领域的首选数据库。

• 2008 年：被 Sun Microsystems 收购。

• 2010 年：Oracle 收购 Sun Microsystems，MySQL 成为 Oracle 旗下产品。

• 关键版本：

  • MySQL 5.5（2010）：默认存储引擎改为 InnoDB，支持半同步复制。

  • MySQL 5.7（2015）：性能优化、JSON 支持、GIS 增强。

  • MySQL 8.0（2018）：窗口函数、原子 DDL、默认字符集改为 utf8mb4。

3.3 核心特点

特性	说明
高性能	支持高并发读写，优化查询引擎，适用于 OLTP（在线事务处理）。
高可靠性	支持 ACID 事务、崩溃恢复、主从复制。
易扩展	支持分布式架构（如 MySQL Cluster、分库分表）。
跨平台	支持 Windows、Linux、macOS 等主流操作系统。
多存储引擎	提供 InnoDB（事务安全）、MyISAM（高速读）、Memory（内存表）等引擎。

3.4 适用场景

• Web 应用：如 WordPress、Joomla 等 CMS 系统的默认数据库。

• SaaS 服务：支持多租户数据隔离和快速查询。

• 电商平台：处理订单、用户、商品等结构化数据。

• 日志系统：通过 MyISAM 引擎高效存储日志（需权衡事务需求）。

3.5 数据库术语

• 数据库（database） - 保存有组织的数据的容器（通常是一个文件或一组文件）。

• 数据表（table） - 某种特定类型数据的结构化清单。

• 列（column） - 表中的一个字段。所有表都是由一个或多个列组成的。

• 行（row） - 表中的一个记录。

• 主键（primary key） - 一列（或一组列），其值能够唯一标识表中每一行。

四、MySQL安装

【mysql安装】3.win10 mysql5.7安装(zip免安装版本) - 满Sir - 博客园

net stop mysql;
net start mysql;
​
# 登陆MySQL
mysql -u root -p
​
show databases;
​
use test-03;
​
select * from employees;
​
exit;
​

 

五、MySQL基本操作

5.1 数据库（Database）操作

5.1.1 创建数据库

create database helloworld;

5.1.2 使用数据库

use helloworld;

5.1.3 显示当前使用的数据库

select database();

5.1.4 删除数据库

DROP DATABASE helloworld;

 

5.2 表（Table）操作

5.2.1 创建表（CREATE TABLE）

-- 部门表 departments
CREATE TABLE `departments` (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '部门ID',
  `name` varchar(50) NOT NULL COMMENT '部门名称',
  `location` varchar(100) DEFAULT NULL COMMENT '办公地点',
  `budget` decimal(12,2) DEFAULT NULL COMMENT '年度预算',
  `department_code` varchar(20) DEFAULT NULL COMMENT '部门编号',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='部门信息表';
​
-- 员工表 employees
CREATE TABLE `employees` (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '员工ID',
  `name` varchar(50) NOT NULL COMMENT '姓名',
  `gender` varchar(2) DEFAULT '男' COMMENT '性别',
  `salary` decimal(10,2) NOT NULL COMMENT '薪资',
  `hire_date` date NOT NULL COMMENT '入职日期',
  `department_id` int(11) DEFAULT NULL COMMENT '部门id',
  `employee_code` varchar(20) DEFAULT NULL COMMENT '员工编号',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8mb4 COMMENT='员工信息表';
​
-- 学生表 student
CREATE TABLE `student` (
  `id` int(11) NOT NULL,
  `name` varchar(32) DEFAULT NULL,
  `gender` varchar(2) DEFAULT NULL,
  `student_code` varchar(20) DEFAULT NULL COMMENT '学生学号'
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
​

 

5.2.2 表维护操作

-- 重命名表
RENAME TABLE student TO new_student;
​
-- 复制表结构（不含数据）
CREATE TABLE student_2025 LIKE student;
​
-- 复制表结构及数据
CREATE TABLE employees_2025 AS SELECT * FROM employees WHERE salary > 8000;
​
-- 删除表
DROP TABLE IF EXISTS employees_2025;

 

5.2.3 修改表结构（ALTER TABLE）

-- 添加新列（带位置控制）
ALTER TABLE employees ADD COLUMN phone VARCHAR(20) AFTER name;
​
-- 重命名列
ALTER TABLE employees 
CHANGE COLUMN phone phoneNum DATE NOT NULL;
​
-- 删除列
ALTER TABLE employees 
DROP COLUMN phoneNum;
​

 

5.2.4 索引（INDEX）

• 作用

• • 通过索引可以更加快速高效地查询数据。

  • 用户无法看到索引，它们只能被用来加速查询。

• 注意

• • 更新一个包含索引的表需要比更新一个没有索引的表花费更多的时间，这是由于索引本身也需要更新。因此，理想的做法是仅仅在常常被搜索的列（以及表）上面创建索引。

• 唯一索引

• • 唯一索引表明此索引的每一个索引值只对应唯一的数据记录。

创建索引

CREATE INDEX student_index ON student (id);

创建唯一索引

CREATE UNIQUE INDEX code_index ON student (student_code);

删除索引

ALTER TABLE student DROP INDEX code_index;

 

5.3.4 数据操作

5.3.1 插入数据

-- 插入部门数据
INSERT INTO departments (name, location, budget, department_code) VALUES
('研发部', '北京总部', 1000000.00, 'RD001'),
('市场部', '上海分部', 800000.00, 'MK001'),
('销售部', '广州分部', 900000.00, 'SL001'),
('财务部', '深圳分部', 700000.00, 'FA001'),
('人力资源部', '北京总部', 600000.00, 'HR001'),
('客服部', '成都分部', 500000.00, 'CS001'),
('设计部', '杭州分部', 750000.00, 'DE001'),
('法务部', '北京总部', 400000.00, 'LE001'),
('运营部', '武汉分部', 850000.00, 'OP001'),
('采购部', '南京分部', 650000.00, 'PU001');
​
-- 插入员工数据
INSERT INTO employees (name, gender, salary, hire_date, department_id, employee_code) VALUES
('张三', '男', 8000.00, '2023-01-01', 1, 'E0001'),
('李四', '女', 9000.00, '2023-02-01', 1, 'E0002'),
('王五', '男', 7500.00, '2023-03-01', 2, 'E0003'),
('赵六', '女', 8500.00, '2023-04-01', 2, 'E0004'),
('孙七', '男', 9500.00, '2023-05-01', 3, 'E0005'),
('周八', '女', 8200.00, '2023-06-01', 3, 'E0006'),
('吴九', '男', 7800.00, '2023-07-01', 4, 'E0007'),
('郑十', '女', 8800.00, '2023-08-01', 4, 'E0008'),
('王十一', '男', 9200.00, '2023-09-01', 5, 'E0009'),
('李十二', '女', 8300.00, '2023-10-01', 5, 'E0010'),
('张十三', '男', 7600.00, '2023-11-01', 6, 'E0011'),
('刘十四', '女', 8600.00, '2023-12-01', 6, 'E0012'),
('陈十五', '男', 9300.00, '2024-01-01', 7, 'E0013'),
('杨十六', '女', 8400.00, '2024-02-01', 7, 'E0014'),
('黄十七', '男', 7900.00, '2024-03-01', 8, 'E0015'),
('胡十八', '女', 8900.00, '2024-04-01', 8, 'E0016'),
('林十九', '男', 9400.00, '2024-05-01', 9, 'E0017'),
('何二十', '女', 8100.00, '2024-06-01', 9, 'E0018'),
('郭二十一', '男', 7700.00, '2024-07-01', 10, 'E0019'),
('马二十二', '女', 8700.00, '2024-08-01', 10, 'E0020');
​
-- 向 student 表插入 10 条数据
INSERT INTO student (id, name, gender, student_code) VALUES
(1, '小明', '男', 'S001'),
(2, '小红', '女', 'S002'),
(3, '小刚', '男', 'S003'),
(4, '小丽', '女', 'S004'),
(5, '小强', '男', 'S005'),
(6, '小美', '女', 'S006'),
(7, '小辉', '男', 'S007'),
(8, '小兰', '女', 'S008'),
(9, '小龙', '男', 'S009'),
(10, '小娜', '女', 'S010');

 

5.3.2更新数据

UPDATE employees 
SET salary = salary * 1.2 
WHERE gender = '男';

5.3.3 删除数据

DELETE FROM employees 
WHERE hire_date < '2020-01-01';

5.3.4 清空表

TRUNCATE TABLE employees;

DELETE FROM employees ;

 

5.3.5 查询数据

• SELECT 语句用于从数据库中查询数据。

• DISTINCT 用于返回唯一不同的值。它作用于所有列，也就是说所有列的值都相同才算相同。

• LIMIT 限制返回的行数。可以有两个参数，第一个参数为起始行，从 0 开始；第二个参数为返回的总行数。

• • ASC ：升序（默认）

  • DESC ：降序

查询所有列

SELECT *
FROM employees;

查询单列

SELECT name
FROM employees;

查询多列

SELECT name, gender, salary,hire_date
FROM employees;

查询不同的值

SELECT DISTINCT location FROM departments;

限制查询结果

-- 返回前 5 行
SELECT * FROM departments LIMIT 5;
SELECT * FROM departments LIMIT 0, 5;
-- 返回第 3 ~ 5 行
SELECT * FROM departments LIMIT 2, 3;

AS的使用

在 SQL 中，AS 关键字主要用于给表或列起别名，这样可以提高查询语句的可读性，使结果集的列名更具描述性，也方便在一些复杂查询中引用表。

列别名

当你查询表中的数据时，可以使用 AS 为列指定一个更具描述性的名称，这样查询结果集中显示的列名就会是你指定的别名。

-- 查询员工的姓名和薪资，并为列指定别名
SELECT 
    name AS 员工姓名,
    salary AS 员工薪资
FROM 
    employees;

表别名

在进行多表查询或者子查询时，使用 AS 为表指定别名可以简化 SQL 语句，提高代码的可读性，同时避免表名冲突。

-- 查询员工姓名和所在部门名称
SELECT 
    e.name AS 员工姓名,
    d.name AS 部门名称
FROM 
    employees AS e
JOIN 
    departments AS d
ON 
    e.department_id = d.id;

 

 

5.4 子查询

子查询是嵌套在较大查询中的 SQL 查询。子查询也称为内部查询或内部选择，而包含子查询的语句也称为外部查询或外部选择。

• 子查询可以嵌套在 SELECT，INSERT，UPDATE 或 DELETE 语句内或另一个子查询中。

• 子查询通常会在另一个 SELECT 语句的 WHERE 子句中添加。

• 可以使用比较运算符，如 >，<，或 =。比较运算符也可以是多行运算符，如 IN，ANY 或 ALL。

• 子查询必须被圆括号 () 括起来。

• 内部查询首先在其父查询之前执行，以便可以将内部查询的结果传递给外部查询。

-- 查询薪资高于平均薪资的员工信息
SELECT 
    e.id,
    e.name,
    e.gender,
    e.salary,
    e.hire_date,
    e.department_id,
    e.employee_code
FROM 
    employees e
WHERE 
    e.salary > (
        SELECT 
            AVG(salary)
        FROM 
            employees
    );

5.4.1 WHERE

• WHERE 子句用于过滤记录，即缩小访问数据的范围。

• WHERE 后跟一个返回 true 或 false 的条件。

• WHERE 可以与 SELECT，UPDATE 和 DELETE 一起使用。

• 可以在 WHERE 子句中使用的操作符

运算符	描述
=	等于
<>	不等于。注释：在 SQL 的一些版本中，该操作符可被写成 !=
>	大于
<	小于
>=	大于等于
<=	小于等于
BETWEEN	在某个范围内
LIKE	搜索某种模式
IN	指定针对某个列的多个可能值

SELECT 语句中的 WHERE 子句

SELECT * FROM employees 
WHERE salary >8000;

UPDATE 语句中的 WHERE 子句

UPDATE employees
SET salary = 10000.0
WHERE name = '孙七';

DELETE 语句中的 WHERE 子句

DELETE FROM employees
WHERE ID = 19;

5.4.2 IN 和 BETWEEN

• IN 操作符在 WHERE 子句中使用，作用是在指定的几个特定值中任选一个值。

• BETWEEN 操作符在 WHERE 子句中使用，作用是选取介于某个范围内的值。

IN 示例

SELECT id, name, gender, salary, hire_date, department_id, employee_code
FROM employees
WHERE department_id IN (2, 5);

BETWEEN 示例

SELECT id, name, gender, salary, hire_date, department_id, employee_code
FROM employees
WHERE salary BETWEEN 8500 AND 9000;

5.4.3 AND、OR、NOT

• AND、OR、NOT 是用于对过滤条件的逻辑处理指令。

• AND 优先级高于 OR，为了明确处理顺序，可以使用 ()。

• AND 操作符表示左右条件都要满足。

• OR 操作符表示左右条件满足任意一个即可。

• NOT 操作符用于否定一个条件。

AND 示例

SELECT id, name, gender, salary, hire_date, department_id, employee_code
FROM employees
WHERE department_id = 4 AND gender = '女';

OR 示例

SELECT id, name, gender, salary, hire_date, department_id, employee_code
FROM employees
WHERE department_id = 4 OR department_id = 5；

NOT 示例

SELECT id, name, gender, salary, hire_date, department_id, employee_code
FROM employees
WHERE salary NOT BETWEEN 8500 AND 9000;

5.4.4 LIKE

• LIKE 操作符在 WHERE 子句中使用，作用是确定字符串是否匹配模式。

• 只有字段是文本值时才使用 LIKE。

• LIKE 支持两个通配符匹配选项：% 和 _。

• % 表示任何字符出现任意次数。

• _ 表示任何字符出现一次。

% 示例

SELECT id, name, gender, salary, hire_date, department_id, employee_code
FROM employees
WHERE name LIKE '%十一%';

_ 示例

SELECT id, name, gender, salary, hire_date, department_id, employee_code
FROM employees
WHERE name LIKE '__十';

 

5.5 连接和组合

5.5.1连接（JOIN）

• 如果一个 JOIN 至少有一个公共字段并且它们之间存在关系，则该 JOIN 可以在两个或多个表上工作。

• 连接用于连接多个表，使用 JOIN 关键字，并且条件语句使用 ON 而不是 WHERE。

• JOIN 保持基表（结构和数据）不变。

• JOIN 有两种连接类型：内连接和外连接。

• 内连接又称等值连接，使用 INNER JOIN 关键字。在没有条件语句的情况下返回笛卡尔积。

• 外连接返回一个表中的所有行，并且仅返回来自次表中满足连接条件的那些行，即两个表中的列是相等的。外连接分为左外连接、右外连接。

• • 左外连接就是保留左表没有关联的行。

  • 右外连接就是保留右表没有关联的行。

 

1 内连接（INNER JOIN）

SELECT 
    e.id AS employee_id,
    e.name AS employee_name,
    e.gender,
    e.salary,
    e.hire_date,
    d.id AS department_id,
    d.name AS department_name,
    d.location,
    d.budget
FROM 
    employees e
INNER JOIN 
    departments d ON e.department_id = d.id;

 

--笛卡尔积
SELECT 
    e.id AS employee_id,
    e.name AS employee_name,
    e.gender,
    e.salary,
    e.hire_date,
    d.id AS department_id,
    d.name AS department_name,
    d.location,
    d.budget,
    d.department_code
FROM 
    employees e
INNER JOIN 
    departments d;

 

2 外连接

左连接（LEFT JOIN）

SELECT 
    e.id AS employee_id,
    e.name AS employee_name,
    e.gender,
    e.salary,
    e.hire_date,
    d.id AS department_id,
    d.name AS department_name,
    d.location,
    d.budget
FROM 
    employees e
LEFT JOIN 
    departments d ON e.department_id = d.id;

右连接（RIGHT JOIN）

SELECT 
    e.id AS employee_id,
    e.name AS employee_name,
    e.gender,
    e.salary,
    e.hire_date,
    d.id AS department_id,
    d.name AS department_name,
    d.location,
    d.budget
FROM 
    employees e
RIGHT JOIN 
    departments d ON e.department_id = d.id;

5.5.2 组合（UNION）

• UNION 运算符将两个或更多查询的结果组合起来，并生成一个结果集，其中包含来自 UNION 中参与查询的提取行。

• UNION 基本规则

• • 所有查询的列数和列顺序必须相同。

  • 每个查询中涉及表的列的数据类型必须相同或兼容。

  • 通常返回的列名取自第一个查询。

• 默认会去除相同行，如果需要保留相同行，使用 UNION ALL。

• 只能包含一个 ORDER BY 子句，并且必须位于语句的最后。

• 应用场景

• • 在一个查询中从不同的表返回结构数据。

  • 对一个表执行多个查询，按一个查询返回数据。

组合查询

-- 查询薪资大于 8500 的员工信息
SELECT 
    'Employee' AS record_type,
    e.id,
    e.name,
    e.salary,
    NULL AS budget
FROM 
    employees e
WHERE 
    e.salary > 8500
UNION
-- 查询年度预算大于 800000 的部门信息
SELECT 
    'Department' AS record_type,
    d.id,
    d.name,
    NULL AS salary,
    d.budget
FROM 
    departments d
WHERE 
    d.budget > 800000;

5.5.3 JOIN vs UNION

• JOIN vs UNION

• • JOIN 中连接表的列可能不同，但在 UNION 中，所有查询的列数和列顺序必须相同。

  • UNION 将查询之后的行放在一起（垂直放置），但 JOIN 将查询之后的列放在一起（水平放置），即它构成一个笛卡尔积。

5.6 函数

5.6.1 文本处理

函数	说明
LEFT()、RIGHT()	左边或者右边的字符
LOWER()、UPPER()	转换为小写或者大写
LTRIM()、RTIM()	去除左边或者右边的空格
LENGTH()	长度

5.6.2 日期和时间处理

• 日期格式：YYYY-MM-DD

• 时间格式：HH:MM:SS

函 数	说 明
AddDate()	增加一个日期（天、周等）
AddTime()	增加一个时间（时、分等）
CurDate()	返回当前日期
CurTime()	返回当前时间
Date()	返回日期时间的日期部分
DateDiff()	计算两个日期之差
Date_Add()	高度灵活的日期运算函数
Date_Format()	返回一个格式化的日期或时间串
Day()	返回一个日期的天数部分
DayOfWeek()	对于一个日期，返回对应的星期几
Hour()	返回一个时间的小时部分
Minute()	返回一个时间的分钟部分
Month()	返回一个日期的月份部分
Now()	返回当前日期和时间
Second()	返回一个时间的秒部分
Time()	返回一个日期时间的时间部分
Year()	返回一个日期的年份部分

mysql> SELECT NOW();

 

5.6.3 数值处理

函数	说明
SIN()	正弦
COS()	余弦
TAN()	正切
ABS()	绝对值
SQRT()	平方根
MOD()	余数
EXP()	指数
PI()	圆周率
RAND()	随机数

5.6.4 汇总

函 数	说 明
AVG()	返回某列的平均值
COUNT()	返回某列的行数
MAX()	返回某列的最大值
MIN()	返回某列的最小值
SUM()	返回某列值之和

AVG() 函数示例

-- 计算员工的平均薪资
SELECT 
    COUNT(id) AS employee_count
FROM 
    employees;

COUNT() 函数示例

--找出员工的最高薪资
SELECT 
    COUNT(id) AS high_salary_employee_count
FROM 
    employees
WHERE 
    salary > 8000;

MAX() 函数示例

--找出员工的最高薪资
SELECT 
    MAX(salary) AS max_salary
FROM 
    employees;

MIN() 函数示例

-- 找出员工的最早入职日期
SELECT 
    MIN(hire_date) AS earliest_hire_date
FROM 
    employees;

SUM() 函数示例

--计算所有员工的薪资总和
SELECT 
    SUM(salary) AS total_salary
FROM 
    employees;

 

 

5.7 排序和分组

5.7.1 ORDER BY

• ORDER BY 用于对结果集进行排序。

• • ASC ：升序（默认）

  • DESC ：降序

• 可以按多个列进行排序，并且为每个列指定不同的排序方式

指定多个列的排序方向

SELECT 
    id, name, gender, salary, hire_date, department_id, employee_code
FROM 
    employees
ORDER BY 
    salary ASC,employee_code DESC;

5.7.2 GROUP BY

• GROUP BY 子句将记录分组到汇总行中。

• GROUP BY 为每个组返回一个记录。

• GROUP BY 通常还涉及聚合：COUNT，MAX，SUM，AVG 等。

• GROUP BY 可以按一列或多列进行分组。

• GROUP BY 按分组字段进行排序后，ORDER BY 可以以汇总字段来进行排序。

分组后排序

-- 按部门预算降序排序，若预算相同则按部门名称升序排序
SELECT 
    id, name, location, budget, department_code
FROM 
    departments
ORDER BY 
    budget DESC, name ASC;

-- 统计每个部门的人数和薪资总和,并按薪资总和升序排序
SELECT 
    d.id AS department_id,
    d.name AS department_name,
    SUM(e.salary) AS total_salary,
    COUNT(e.id) AS employee_count
FROM 
    employees e
JOIN 
    departments d ON e.department_id = d.id
GROUP BY 
    d.id, d.name
ORDER BY 
    total_salary;

 

5.7.3 HAVING

• HAVING 用于对汇总的 GROUP BY 结果进行过滤。

• HAVING 要求存在一个 GROUP BY 子句。

• WHERE 和 HAVING 可以在相同的查询中。

• HAVING vs WHERE

• • WHERE 和 HAVING 都是用于过滤。

  • HAVING 适用于汇总的组记录；而 WHERE 适用于单个记录。

使用 WHERE 和 HAVING 过滤数据

-- 筛选员工数量大于 2 的部门
SELECT 
    department_id, COUNT(id) AS employee_count
FROM 
    employees
GROUP BY 
    department_id
HAVING 
    COUNT(id) > 2;

 

--筛选出 2023 年入职且部门平均薪资大于 8000 的部门
SELECT 
    department_id, AVG(salary) AS average_salary
FROM 
    employees
WHERE 
    YEAR(hire_date) = 2023
GROUP BY 
    department_id
HAVING 
    AVG(salary) > 8000;

 

5.8 SQL优化

1.对查询进行优化，应尽量避免全表扫描，首先应考虑在 where 及 order by 涉及的列上建立索引。

2.应尽量避免在 where 子句中使用!=或<>操作符，否则将引擎放弃使用索引而进行全表扫描。

3.应尽量避免在 where 子句中对字段进行 null 值判断，否则将导致引擎放弃使用索引而进行全表扫描，如：

select id from t where num is null 

可以在num上设置默认值0，确保表中num列没有null值，然后这样查询：

select id from t where num=0 

4.应尽量避免在 where 子句中使用 or 来连接条件，否则将导致引擎放弃使用索引而进行全表扫描，如：

select id from t where num=10 or num=20
​

可以这样查询：

select id from t where num=10 
union all 
select id from t where num=20 

5.下面的查询也将导致全表扫描：

select id from t where name like '%abc%' 

若要提高效率，可以考虑全文检索。

6.in 和 not in 也要慎用，否则会导致全表扫描，如：

select id from t where num in(1,2,3) 

对于连续的数值，能用 between 就不要用 in 了：

select id from t where num between 1 and 3 

7.如果在 where 子句中使用参数，也会导致全表扫描。因为SQL只有在运行时才会解析局部变量，但优化程序不能将访问计划的选择推迟到运行时；它必须在编译时进行选择。然而，如果在编译时建立访问计划，变量的值还是未知的，因而无法作为索引选择的输入项。如下面语句将进行全表扫描：

select id from t where num=@num 

可以改为强制查询使用索引：

select id from t with(index(索引名)) where num=@num 

8.应尽量避免在 where 子句中对字段进行表达式操作，这将导致引擎放弃使用索引而进行全表扫描。如：

select id from t where num/2=100 

应改为:

select id from t where num=100*2 

9.应尽量避免在where子句中对字段进行函数操作，这将导致引擎放弃使用索引而进行全表扫描。如：

select id from t where substring(name,1,3)='abc'--name以abc开头的id 
select id from t where datediff(day,createdate,'2005-11-30')=0--'2005-11-30'生成的id 

应改为:

select id from t where name like 'abc%' 
select id from t where createdate>='2005-11-30' and createdate<'2005-12-1' 
​

10.不要在 where 子句中的“=”左边进行函数、算术运算或其他表达式运算，否则系统将可能无法正确使用索引。

11.在使用索引字段作为条件时，如果该索引是复合索引，那么必须使用到该索引中的第一个字段作为条件时才能保证系统使用该索引，否则该索引将不会被使用，并且应尽可能的让字段顺序与索引顺序相一致。

12.不要写一些没有意义的查询，如需要生成一个空表结构：

select col1,col2 into #t from t where 1=0 

这类代码不会返回任何结果集，但是会消耗系统资源的，应改成这样：

create table #t(...) 

13.很多时候用 exists 代替 in 是一个好的选择：

select num from a where num in(select num from b) 

用下面的语句替换：

select num from a where exists(select 1 from b where num=a.num) 

14.并不是所有索引对查询都有效，SQL是根据表中数据来进行查询优化的，当索引列有大量数据重复时，SQL查询可能不会去利用索引，如一表中有字段sex，male、female几乎各一半，那么即使在sex上建了索引也对查询效率起不了作用。

15.索引并不是越多越好，索引固然可以提高相应的 select 的效率，但同时也降低了 insert 及 update 的效率，因为 insert 或 update 时有可能会重建索引，所以怎样建索引需要慎重考虑，视具体情况而定。一个表的索引数最好不要超过6个，若太多则应考虑一些不常使用到的列上建的索引是否有必要。

16.应尽可能的避免更新 clustered 索引数据列，因为 clustered 索引数据列的顺序就是表记录的物理存储顺序，一旦该列值改变将导致整个表记录的顺序的调整，会耗费相当大的资源。若应用系统需要频繁更新 clustered 索引数据列，那么需要考虑是否应将该索引建为 clustered 索引。

17.尽量使用数字型字段，若只含数值信息的字段尽量不要设计为字符型，这会降低查询和连接的性能，并会增加存储开销。这是因为引擎在处理查询和连接时会逐个比较字符串中每一个字符，而对于数字型而言只需要比较一次就够了。

18.尽可能的使用 varchar/nvarchar 代替 char/nchar ，因为首先变长字段存储空间小，可以节省存储空间，其次对于查询来说，在一个相对较小的字段内搜索效率显然要高些。

19.任何地方都不要使用 select * from t ，用具体的字段列表代替“*”，不要返回用不到的任何字段。

20.尽量使用表变量来代替临时表。如果表变量包含大量数据，请注意索引非常有限（只有主键索引）。

21.避免频繁创建和删除临时表，以减少系统表资源的消耗。

22.临时表并不是不可使用，适当地使用它们可以使某些例程更有效，例如，当需要重复引用大型表或常用表中的某个数据集时。但是，对于一次性事件，最好使用导出表。

23.在新建临时表时，如果一次性插入数据量很大，那么可以使用 select into 代替 create table，避免造成大量 log ，以提高速度；如果数据量不大，为了缓和系统表的资源，应先create table，然后insert。

24.如果使用到了临时表，在存储过程的最后务必将所有的临时表显式删除，先 truncate table ，然后 drop table ，这样可以避免系统表的较长时间锁定。

25.尽量避免使用游标，因为游标的效率较差，如果游标操作的数据超过1万行，那么就应该考虑改写。

26.使用基于游标的方法或临时表方法之前，应先寻找基于集的解决方案来解决问题，基于集的方法通常更有效。

27.与临时表一样，游标并不是不可使用。对小型数据集使用 FAST_FORWARD 游标通常要优于其他逐行处理方法，尤其是在必须引用几个表才能获得所需的数据时。在结果集中包括“合计”的例程通常要比使用游标执行的速度快。如果开发时间允许，基于游标的方法和基于集的方法都可以尝试一下，看哪一种方法的效果更好。

28.在所有的存储过程和触发器的开始处设置 SET NOCOUNT ON ，在结束时设置 SET NOCOUNT OFF 。无需在执行存储过程和触发器的每个语句后向客户端发送 DONE_IN_PROC 消息。

29.尽量避免向客户端返回大数据量，若数据量过大，应该考虑相应需求是否合理。

300.尽量避免大事务操作，提高系统并发能力。