当前位置： 首页 > news >正文

第二章 SQL编程系列-SQL编程基础

news 2025/7/19 1:02:48

1 引言

        SQL，即结构化查询语言（Structured Query Language），是一种强大的编程语言，专门用于管理和操作数据库。作为数据库查询和操作的标准化语言，对于任何希望在数据领域深入的开发者来说，都是一项必须掌握的技能。它不仅能够高效地进行数据查询、数据操纵、数据定义和数据控制，而且其语法的灵活性和强大功能使其成为开发者的得力助手。 

1.1 数据库与表的结构

  • 数据库：存储数据的容器，由多个表组成
  • 表结构：由列（字段）和行（记录）构成
    • 列（Column）：定义数据类型（如INT、VARCHAR）和约束（如PRIMARY KEY）
    • 行（Row）：存储具体数据记录

1.2 SQL语言分类

类别英文全称核心功能示例语句
数据定义DDL (Data Definition)创建/修改数据库对象CREATE TABLE, ALTER DATABASE
数据操作DML (Data Manipulation)增删改数据INSERT, UPDATE, DELETE
数据查询DQL (Data Query)检索数据SELECT
数据控制DCL (Data Control)权限管理GRANT, REVOKE

2 SQL基础语法详解

2.1 数据查询（SELECT）

        数据查询（SELECT）是SQL（结构化查询语言）的核心功能之一，用于从数据库中检索和操作数据。以下是关于数据查询的详细阐述，涵盖基础查询、条件查询、聚合查询、连接查询、子查询以及排序和分组等方面：

2.1.1 基础查询

        基础查询是最简单的SELECT语句形式，用于从表中检索数据。可以选择所有列或特定列。

语法：

#检索特定的列
SELECT column1, column2, ... FROM table_name;#或检索所有列：
SELECT * FROM table_name;

示例：

# 检索员工表中的姓名和部门
SELECT name, department FROM employees;

2.1.2 条件查询

        使用WHERE子句过滤数据，只返回满足特定条件的行。

语法：

SELECT column1, column2, ... FROM table_name WHERE condition;

常用条件运算符：

  • =（等于）、<> 或 !=（不等于）
  • >、<、>=、<=（比较运算符）
  • BETWEEN ... AND ...（范围）
  • LIKE（模式匹配）
  • IN（指定多个可能值）
  • IS NULL 或 IS NOT NULL（检查空值）

示例：

-- 检索工资大于5000的员工
SELECT * FROM employees WHERE salary > 5000;-- 检索部门为"IT"或"HR"的员工
SELECT * FROM employees WHERE department IN ('IT', 'HR');

2.1.3 聚合查询

        使用聚合函数对数据进行计算，如计数、求和、平均值等。通常与GROUP BY子句一起使用。

语法：

SELECT aggregate_function(column_name) FROM table_name [WHERE condition] [GROUP BY column_name];

常用聚合函数：

  • COUNT()：计数
  • SUM()：求和
  • AVG()：平均值
  • MIN()：最小值
  • MAX()：最大值

示例：

-- 计算员工总数
SELECT COUNT(*) FROM employees;-- 计算每个部门的平均工资
SELECT department, AVG(salary) FROM employees GROUP BY department;

2.1.4 连接查询

        使用JOIN语句连接多个表，基于表之间的关联关系检索数据。

语法：

SELECT a.column1, b.column2, ...
FROM table1 a
[JOIN_TYPE] JOIN table2 b ON a.common_field = b.common_field;

常用连接类型：

  • INNER JOIN：返回两表中匹配的行。
  • LEFT JOIN（或LEFT OUTER JOIN）：返回左表的所有行，即使右表中没有匹配。
  • RIGHT JOIN（或RIGHT OUTER JOIN）：返回右表的所有行，即使左表中没有匹配。
  • FULL JOIN（或FULL OUTER JOIN）：返回两表的所有行，无论是否匹配。

示例：

-- 检索员工及其部门名称
SELECT e.name, d.department_name
FROM employees e
INNER JOIN departments d ON e.department_id = d.id;

2.1.5 子查询

        在查询中嵌套其他查询，子查询可以出现在SELECT、FROM、WHERE等子句中。

语法：

SELECT column1, column2, ...
FROM table_name
WHERE column_name [operator] (SELECT column_name FROM table_name WHERE condition);

示例：

-- 检索工资高于平均工资的员工
SELECT * FROM employees WHERE salary > (SELECT AVG(salary) FROM employees);-- 检索每个部门工资最高的员工
SELECT e.*
FROM employees e
WHERE e.salary = (SELECT MAX(salary)FROM employeesWHERE department_id = e.department_id
);

2.1.6 排序和分组

        使用ORDER BY和GROUP BY对结果进行排序和分组。

ORDER BY：对结果集进行排序。

  • ASC（升序，默认）或 DESC（降序）。
  • 可以按多列排序。

示例：

SELECT * FROM employees ORDER BY salary DESC, name ASC;

GROUP BY：对结果集进行分组，通常与聚合函数一起使用。

  • 可以按多列分组。

示例：

SELECT department, COUNT(*) as employee_count
FROM employees
GROUP BY department;

HAVING：对分组后的结果进行过滤（类似于WHERE，但用于分组后）。

示例：

SELECT department, AVG(salary) as avg_salary
FROM employees
GROUP BY department
HAVING AVG(salary) > 6000;

2.2 数据操作（INSERT, UPDATE, DELETE）

        数据操作（Data Manipulation）是SQL的核心功能之一，用于对数据库中的数据进行插入、更新和删除操作。此外，事务控制是确保数据一致性和完整性的重要机制。以下从插入数据、更新数据、删除数据和事务控制四个方面进行详细阐述：

2.2.1 插入数据（INSERT）

        插入数据用于向表中添加新记录。

语法：

INSERT INTO table_name (column1, column2, ...)
VALUES (value1, value2, ...);# 如果插入所有列的值，可以省略列名：INSERT INTO table_name VALUES (value1, value2, ...);

示例：

-- 向employees表中插入一条新记录
INSERT INTO employees (name, department, salary, hire_date)
VALUES ('John Doe', 'IT', 75000, '2023-01-15');-- 插入所有列的值（省略列名）
INSERT INTO departments VALUES (1, 'IT', 'Information Technology');

注意事项：

  • 确保插入的值与列的数据类型兼容。
  • 如果表有自增主键，通常不需要显式插入该列的值。

2.2.2 更新数据（UPDATE）

        更新数据用于修改表中现有记录的数据。

语法：

UPDATE table_name
SET column1 = value1, column2 = value2, ...
[WHERE condition];

示例：

-- 将ID为1的员工的工资更新为80000
UPDATE employees
SET salary = 80000
WHERE id = 1;-- 更新多个列
UPDATE employees
SET department = 'HR', salary = 70000
WHERE name = 'Jane Smith';

注意事项：

  • 如果没有WHERE子句，将更新表中的所有记录（通常应避免）。
  • 确保WHERE条件准确，以避免意外更新多条记录。

2.2.3 删除数据（DELETE）

        删除数据用于从表中删除记录。

语法：

DELETE FROM table_name [WHERE condition];

示例：

-- 删除ID为2的员工记录
DELETE FROM employees WHERE id = 2;-- 删除所有部门为'Marketing'的员工记录
DELETE FROM employees WHERE department = 'Marketing';

注意事项：

如果没有WHERE子句，将删除表中的所有记录（通常应避免）。
删除操作是不可逆的，除非有备份或事务回滚机制。

2.2.4 事务控制

        事务控制用于确保数据操作的原子性、一致性、隔离性和持久性（ACID特性）。通过使用BEGIN TRANSACTION、COMMIT和ROLLBACK，可以管理一组操作作为一个逻辑单元。

语法：

BEGIN TRANSACTION; -- 开始事务-- 执行一系列SQL语句
INSERT INTO table1 VALUES (...);
UPDATE table2 SET column1 = ... WHERE condition;
DELETE FROM table3 WHERE condition;COMMIT; -- 提交事务，所有更改永久生效
-- 或
ROLLBACK; -- 回滚事务，撤销所有更改

示例：

BEGIN TRANSACTION;-- 从账户A转账100到账户B
UPDATE accounts SET balance = balance - 100 WHERE account_id = 'A';
UPDATE accounts SET balance = balance + 100 WHERE account_id = 'B';-- 检查操作是否成功（例如，检查余额是否足够）
-- 如果一切正常，提交事务
COMMIT;-- 如果出现问题（例如，余额不足），回滚事务
-- ROLLBACK;

注意事项：

  • 事务应尽可能短，以减少锁定资源的时间。
  • 在事务中，确保所有操作要么全部成功，要么全部失败，以保持数据一致性。

2.3 数据定义（CREATE, ALTER, DROP，TRUNCATE）

        数据定义（Data Definition）是SQL中用于定义和管理数据库对象（如表、视图、索引等）结构的关键部分。以下从创建表、修改表、删除表和删除表记录（TRUNCATE）四个方面进行详细阐述：

2.3.1 创建表（CREATE）

        创建表用于建立新表，并定义表的结构，包括列名、数据类型、约束等。

语法：

CREATE TABLE table_name (column1 datatype [constraints],column2 datatype [constraints],...[table_constraints]
);

常用数据类型：

  • INT 或 INTEGER：整数
  • VARCHAR(n)：可变长度字符串，最大长度为n
  • CHAR(n)：固定长度字符串，长度为n
  • DATE：日期
  • DECIMAL(p, s)：精确数值，p为总位数，s为小数位数
  • BOOLEAN：布尔值（TRUE/FALSE）

常用约束：

  • PRIMARY KEY：主键，唯一标识表中的每一行
  • FOREIGN KEY：外键，引用另一表的主键
  • NOT NULL：列不能包含NULL值
  • UNIQUE：列中的值必须唯一
  • CHECK：确保列中的值满足特定条件
  • DEFAULT：为列指定默认值

示例：

CREATE TABLE employees (id INT PRIMARY KEY AUTO_INCREMENT,name VARCHAR(100) NOT NULL,department VARCHAR(50),salary DECIMAL(10, 2),hire_date DATE DEFAULT CURRENT_DATE,is_active BOOLEAN DEFAULT TRUE
);

2.3.2 修改表（ALTER）

        修改表用于更改现有表的结构，如添加或删除列、修改列的数据类型或约束等。

语法：

# 添加列：
ALTER TABLE table_name ADD column_name datatype [constraints];# 删除列：
ALTER TABLE table_name DROP COLUMN column_name;# 修改列的数据类型或约束：
ALTER TABLE table_name MODIFY COLUMN column_name new_datatype [new_constraints];# 添加或删除约束：
ALTER TABLE table_name ADD CONSTRAINT constraint_name constraint_type (column_name);
ALTER TABLE table_name DROP CONSTRAINT constraint_name;

示例：

-- 向employees表添加一个email列
ALTER TABLE employees ADD email VARCHAR(100);-- 删除employees表的department列
ALTER TABLE employees DROP COLUMN department;-- 修改employees表的salary列的数据类型为DECIMAL(12, 2)
ALTER TABLE employees MODIFY COLUMN salary DECIMAL(12, 2);

2.3.3 删除表（DROP）

        删除表用于移除整个表及其数据，包括表的结构、索引、触发器等。

语法：

DROP TABLE table_name;

示例：

-- 删除employees表
DROP TABLE employees;

注意事项：

  • 删除表是一个不可逆的操作，所有数据将被永久删除。
  • 在删除表之前，应确保已备份重要数据。

2.3.4 删除表记录（TRUNCATE）

        删除表记录用于快速删除表中的所有记录，但不删除表本身及其结构。与DELETE不同，TRUNCATE通常更快且不记录单独的行删除操作。

语法：

TRUNCATE TABLE table_name;

示例：

-- 删除employees表中的所有记录
TRUNCATE TABLE employees;

注意事项：

  • TRUNCATE操作通常比DELETE更快，因为它不记录单独的行删除操作。
  • TRUNCATE不能用于有外键约束引用的表（除非禁用外键检查）。
  • TRUNCATE是DDL（数据定义语言）操作，而DELETE是DML（数据操纵语言）操作。

2.4 数据控制（GRANT, REVOKE）：

        数据控制是数据库管理系统（DBMS）中用于管理用户对数据库对象（如表、视图、存储过程等）访问权限的重要机制。通过使用GRANT和REVOKE语句，数据库管理员可以精确控制哪些用户可以执行哪些操作，从而确保数据的安全性和完整性。

2.4.1 GRANT语句

        GRANT语句用于向用户或角色授予对数据库对象的访问权限。

语法：

GRANT privilege_type [(column_list)] ON object_name TO user_or_role [WITH GRANT OPTION];

常用权限类型：

  • SELECT：允许查询数据。
  • INSERT：允许插入数据。
  • UPDATE：允许更新数据。
  • DELETE：允许删除数据。
  • ALL PRIVILEGES：授予所有权限。
  • EXECUTE：允许执行存储过程或函数。
  • CREATE、ALTER、DROP：允许创建、修改或删除数据库对象。

用户或角色：

  • 可以是具体的用户名，也可以是角色名（一组权限的集合）。

WITH GRANT OPTION：

  • 如果指定，被授权的用户可以将权限再授予其他用户。

示例：

-- 授予用户john对employees表的SELECT和INSERT权限
GRANT SELECT, INSERT ON employees TO john;-- 授予用户jane对products表的UPDATE权限，并允许她将权限授予其他用户
GRANT UPDATE ON products TO jane WITH GRANT OPTION;-- 授予角色manager对departments表的ALL PRIVILEGES权限
GRANT ALL PRIVILEGES ON departments TO manager;

2.4.2 REVOKE语句

        REVOKE语句用于撤销之前授予用户或角色的权限。

语法：

REVOKE privilege_type [(column_list)] ON object_name FROM user_or_role [CASCADE | RESTRICT];

CASCADE | RESTRICT：

  • CASCADE：如果撤销的权限被其他用户通过WITH GRANT OPTION继承，则同时撤销这些用户的权限。
  • RESTRICT：如果撤销的权限被其他用户继承，则拒绝撤销操作（默认行为）。

示例：

-- 撤销用户john对employees表的INSERT权限
REVOKE INSERT ON employees FROM john;-- 撤销用户jane对products表的UPDATE权限，并级联撤销她授予其他用户的权限
REVOKE UPDATE ON products FROM jane CASCADE;-- 撤销角色manager对departments表的ALL PRIVILEGES权限
REVOKE ALL PRIVILEGES ON departments FROM manager;

2.4.3 权限管理的实际应用

细粒度控制：

  • 通过GRANT和REVOKE，可以实现对数据库对象的细粒度访问控制。例如，只允许某些用户查询数据，而禁止他们修改或删除数据。

角色管理：

  • 可以创建角色，将一组权限授予角色，然后将角色分配给用户。这样可以简化权限管理，特别是在用户数量较多时。

审计和合规：

  • 权限管理是数据库审计和合规的重要组成部分。通过记录权限的授予和撤销操作，可以追踪谁对数据库对象进行了哪些操作。

安全性：

  • 合理的权限管理可以防止未经授权的访问和数据泄露。例如，只允许数据库管理员执行DROP TABLE等危险操作。

2.4.4 示例场景

        假设有一个公司数据库，包含员工表（employees）、部门表（departments）和工资表（salaries）。

授予权限：

-- 授予HR经理对employees表的全部权限
GRANT ALL PRIVILEGES ON employees TO hr_manager;-- 授予财务经理对salaries表的SELECT权限
GRANT SELECT ON salaries TO finance_manager;-- 授予普通员工对departments表的SELECT权限
GRANT SELECT ON departments TO employee;

撤销权限：

-- 如果某员工离职，撤销其对departments表的SELECT权限
REVOKE SELECT ON departments FROM former_employee;-- 如果财务经理不再需要查看工资表，撤销其权限
REVOKE SELECT ON salaries FROM finance_manager;

http://www.lqws.cn/news/451441.html

相关文章：

  • 人力资源战略重构，AI驱动高质量发展论坛顺利召开
  • OpenGL和OpenGL ES区别
  • Unity渲染管线 Global Volume 及 Post-processing
  • MACOS系统运行模拟器畅玩SWITCH游戏
  • Windsurf SWE-1模型评析：软件工程的AI革命
  • 用 DeepSeek 打造智能高考志愿填报推荐系统
  • 腾讯混元3D制作简单模型教程-2
  • c++set和pair的使用
  • Vue + AbortController 请求取消弹窗 hook 封装
  • 【Redis】解码Redis字符串：命令执行与内存优化背后的编码逻辑
  • Excel批量计算时间差
  • 邮件合并----批量从excel表中导出数据到word中
  • Linux -- 线程、锁
  • 在spring boot中使用Logback
  • 【Wi-Fi天气时钟】ESP-01S固件烧录与测试
  • （亚马逊2025峰会）7个AIGC应用案例
  • 150页PPT麦肯锡波士顿解决问题方法
  • 前端应用更新提示的优雅实现：如何让用户及时刷新页面？
  • 【内存】Linux 内核优化实战 - vm.panic_on_oom
  • 20250620在Ubuntu20.04.6下编译KickPi的K7的Android14系统
  • 无人机低空经济十大前沿创新应用探索-具体做无人机什么呢？优雅草卓伊凡
  • Spring---Spring MVC 执行流程
  • STM32:AS5600
  • Redis八股文
  • 快速搜索与管理PDF文档的专业工具
  • 网络核心 - CNI、Service 与 Ingress/Gateway API 解析
  • 大模型学习入门——Day3:注意力机制
  • LINUX620 NFS
  • 【2025 年】软件体系结构考试试卷-期末考试
  • 永磁电机技术革新，未来动力新方向