当前位置：个人在线分享 > 【CS.DB】从零到精通：这可能是全网最全面最强大的SQL入门教程

【CS.DB】从零到精通：这可能是全网最全面最强大的SQL入门教程

作者 : admin 本文共8306个字，预计阅读时间需要21分钟 发布时间： 2024-06-9 共2人阅读

文章目录

    • 1. 什么是SQL？
      • 1.1 SQL的历史
        • 1.1.1 SQL的标准化过程
    • 2. SQL基础语法
      • 2.1 数据库操作
        • 2.1.1 创建数据库
        • 2.1.2 删除数据库
      • 2.2 表操作
        • 2.2.1 创建表
        • 2.2.2 删除表
        • 2.2.3 修改表
      • 2.3 数据操作
        • 2.3.1 插入数据
        • 2.3.2 更新数据
        • 2.3.3 删除数据
      • 2.4 查询数据
        • 2.4.1 基本查询
        • 2.4.2 条件查询
        • 2.4.3 排序查询
        • 2.4.4 聚合函数
    • 3. 高级SQL语法
      • 3.1 连接操作
        • 3.1.1 内连接
        • 3.1.2 左连接
        • 3.1.3 右连接
        • 3.1.4 全外连接
      • 3.2 子查询
        • 3.2.1 简单子查询
        • 3.2.2 相关子查询
      • 3.3 联合查询
      • 3.4 GROUP BY 详解
        • 3.4.1 GROUP BY 语法
        • 3.4.2 创建示例数据
        • 3.4.3 经典例子
          • 3.4.3.1 按产品分组计算总销售数量
          • 3.4.3.2 按日期分组计算总销售数量
          • 3.4.3.3 按产品和日期分组计算总销售数量
      • 3.5 HAVING 子句
        • 3.5.1 使用 HAVING 进行过滤
        • 3.5.2 举例: 销售数据分析
          • 3.6.1.1 计算每个客户的总订单金额
          • 3.6.1.2 计算每个月的总销售额
          • 3.6.1.3 查找总订单金额超过1000的客户
    • 4. 数据完整性与约束
      • 4.1 主键约束
      • 4.2 外键约束
      • 4.3 唯一约束
      • 4.4 检查约束
    • 5. SQL函数
      • 5.1 字符串函数
      • 5.2 数学函数
      • 5.3 日期函数
    • 6. 视图
      • 6.1 创建视图
      • 6.2 查询视图
      • 6.3 删除视图
    • 7. 存储过程与函数
      • 7.1 创建存储过程
      • 7.2 调用存储过程
      • 7.3 创建函数
      • 7.4 调用函数
    • 8. 事务
      • 8.1 开始事务
      • 8.2 提交事务
      • 8.3 回滚事务
    • 9. SQL性能优化
      • 9.1 索引
      • 9.2 查询优化
      • 9.3 数据库设计优化
    • 10. SQL安全性
      • 10.1 用户权限管理
      • 10.2 数据加密
    • 11. 常见SQL错误与调试
      • 11.1 语法错误
      • 11.2 数据类型错误
      • 11.3 约束违反错误
    • 12. SQL工具与资源
      • 12.1 SQL客户端工具
      • 12.2 在线学习资源
      • 12.3 书籍推荐


【CS.DB】从零到精通：这可能是全网最全面最强大的SQL入门教程插图

1. 什么是SQL？

SQL（结构化查询语言）是一种用于管理和操作关系数据库的标准语言。它用于查询、更新、插入和删除数据库中的数据。

1.1 SQL的历史

SQL由IBM在20世纪70年代开发，并于1986年被美国国家标准协会（ANSI）采纳为标准。

1.1.1 SQL的标准化过程
  • 1970年：E.F. Codd提出关系数据库模型。
  • 1974年：IBM开始开发SQL。
  • 1986年：ANSI采纳SQL为标准。

2. SQL基础语法

2.1 数据库操作

2.1.1 创建数据库
CREATE DATABASE mydatabase;
2.1.2 删除数据库
DROP DATABASE mydatabase;

2.2 表操作

2.2.1 创建表
CREATE TABLE employees (
    employee_id INT PRIMARY KEY,
    first_name VARCHAR(50),
    last_name VARCHAR(50),
    birth_date DATE,
    hire_date DATE
);
2.2.2 删除表
DROP TABLE employees;
2.2.3 修改表
ALTER TABLE employees ADD COLUMN email VARCHAR(100);
ALTER TABLE employees DROP COLUMN email;

2.3 数据操作

2.3.1 插入数据
INSERT INTO employees (employee_id, first_name, last_name, birth_date, hire_date)
VALUES (1, 'John', 'Doe', '1980-01-01', '2020-01-01');
2.3.2 更新数据
UPDATE employees SET email = 'john.doe@example.com' WHERE employee_id = 1;
2.3.3 删除数据
DELETE FROM employees WHERE employee_id = 1;

2.4 查询数据

2.4.1 基本查询
SELECT * FROM employees;
2.4.2 条件查询
SELECT * FROM employees WHERE last_name = 'Doe';
2.4.3 排序查询
SELECT COUNT(*) FROM employees;
SELECT AVG(salary) FROM employees;
SELECT MAX(salary) FROM employees;
SELECT MIN(salary) FROM employees;
2.4.4 聚合函数
SELECT COUNT(*) FROM employees;
SELECT AVG(salary) FROM employees;
SELECT MAX(salary) FROM employees;
SELECT MIN(salary) FROM employees;

3. 高级SQL语法

3.1 连接操作

3.1.1 内连接

内连接用于返回两个表中存在匹配关系的行。

假设我们有两个表employees和departments，如下所示：

表：employees

employee_idfirst_namelast_namedepartment_id
1JohnDoe1
2JaneSmith2
3MikeJohnson3
4ChrisLeeNULL

表：departments

department_iddepartment_name
1HR
2Finance
3IT
4Marketing

内连接查询：

SELECT employees.first_name, departments.department_name
FROM employees
INNER JOIN departments ON employees.department_id = departments.department_id;

结果：

first_namedepartment_name
JohnHR
JaneFinance
MikeIT

解释： 内连接返回两个表中匹配关系的行。因为Chris的department_id为NULL，没有匹配的部门，所以不包含在结果中。

3.1.2 左连接

左连接返回左表中的所有行，即使在右表中没有匹配的行。

左连接查询：

SELECT employees.first_name, departments.department_name
FROM employees
LEFT JOIN departments ON employees.department_id = departments.department_id;

结果：

first_namedepartment_name
JohnHR
JaneFinance
MikeIT
ChrisNULL

解释： 左连接返回employees表中的所有行，并从departments表中返回匹配的行。如果没有匹配，则返回NULL。因此，Chris仍然包含在结果中，但没有匹配的部门。

3.1.3 右连接

右连接返回右表中的所有行，即使在左表中没有匹配的行。

右连接查询：

SELECT employees.first_name, departments.department_name
FROM employees
RIGHT JOIN departments ON employees.department_id = departments.department_id;

结果：

first_namedepartment_name
JohnHR
JaneFinance
MikeIT
NULLMarketing

解释： 右连接返回departments表中的所有行，并从employees表中返回匹配的行。如果没有匹配，则返回NULL。在这个例子中，新增的Marketing部门没有对应的员工，所以显示为NULL。其余匹配的行显示对应的员工和部门。

通过这个例子，我们可以清楚地看到右连接的作用。即使employees表中没有匹配的记录，右连接也会返回departments表中的所有行，并用NULL填充没有匹配的行。

Note: employees表的每个department_id都有匹配的部门，那么结果将与内连接相同。

3.1.4 全外连接

全外连接（FULL OUTER JOIN）用于返回左表和右表中的所有行。如果在另一表中没有匹配的行，则返回NULL。

全外连接查询：

SELECT employees.first_name, departments.department_name
FROM employees
FULL OUTER JOIN departments ON employees.department_id = departments.department_id;

结果：

first_namedepartment_name
JohnHR
JaneFinance
MikeIT
ChrisNULL
NULLMarketing

解释： 全外连接返回两个表中的所有行。如果在另一表中没有匹配的行，则返回NULL。此例中，Chris没有匹配的部门，因此department_name为NULL；Marketing没有匹配的员工，因此first_name为NULL。

3.2 子查询

3.2.1 简单子查询
SELECT first_name, last_name
FROM employees
WHERE department_id = (SELECT department_id FROM departments WHERE department_name = 'Sales');
3.2.2 相关子查询
SELECT first_name, last_name
FROM employees e
WHERE EXISTS (SELECT 1 FROM departments d WHERE d.department_id = e.department_id AND d.department_name = 'Sales');

3.3 联合查询

SELECT first_name, last_name FROM employees
UNION
SELECT first_name, last_name FROM managers;

3.4 GROUP BY 详解

3.4.1 GROUP BY 语法

GROUP BY 语句用于根据一个或多个列对结果集进行分组。通常与聚合函数（如COUNT、SUM、AVG、MAX、MIN等）一起使用，以对每个组执行计算。

基本语法：

SELECT column_name, aggregate_function(column_name)
FROM table_name
WHERE condition
GROUP BY column_name;
3.4.2 创建示例数据

首先，我们创建一个名为sales的示例表，并插入一些数据：

CREATE TABLE sales (
    sale_id INT,
    product VARCHAR(50),
    quantity INT,
    sale_date DATE
);

INSERT INTO sales (sale_id, product, quantity, sale_date) VALUES
(1, 'Apple', 10, '2024-06-01'),
(2, 'Banana', 5, '2024-06-01'),
(3, 'Apple', 8, '2024-06-02'),
(4, 'Banana', 7, '2024-06-02'),
(5, 'Apple', 12, '2024-06-03'),
(6, 'Banana', 3, '2024-06-03');
3.4.3 经典例子
3.4.3.1 按产品分组计算总销售数量
SELECT product, SUM(quantity) AS total_quantity
FROM sales
GROUP BY product;

此查询按product列分组，并计算每种产品的总销售数量。结果如下：

producttotal_quantity
Apple30
Banana15
3.4.3.2 按日期分组计算总销售数量
SELECT sale_date, SUM(quantity) AS total_quantity
FROM sales
GROUP BY sale_date;

此查询按sale_date列分组，并计算每个日期的总销售数量。结果如下：

sale_datetotal_quantity
2024-06-0115
2024-06-0215
2024-06-0315
3.4.3.3 按产品和日期分组计算总销售数量
SELECT product, sale_date, SUM(quantity) AS total_quantity
FROM sales
GROUP BY product, sale_date;

此查询按product和sale_date列分组，并计算每种产品在每个日期的总销售数量。结果如下：

productsale_datetotal_quantity
Apple2024-06-0110
Apple2024-06-028
Apple2024-06-0312
Banana2024-06-015
Banana2024-06-027
Banana2024-06-033

3.5 HAVING 子句

HAVING 子句用于在分组后的结果集上进行过滤，与GROUP BY一起使用。它的作用类似于WHERE，但WHERE子句作用于分组前的行，而HAVING子句作用于分组后的结果。

SELECT product, SUM(quantity) AS total_quantity
FROM sales
GROUP BY product
HAVING SUM(quantity) > 15;
3.5.1 使用 HAVING 进行过滤

此查询按product列分组，并计算每种产品的总销售数量。然后，过滤出总销售数量大于15的产品。结果如下：

producttotal_quantity
Apple30
3.5.2 举例: 销售数据分析

假设我们有一个名为orders的表，包含以下字段：

ColumnDescription
order_id订单ID
customer_id客户ID
product_id产品ID
order_date订单日期
quantity订单数量
price订单价格（单价）

我们希望进行以下分析：

3.6.1.1 计算每个客户的总订单金额
SELECT customer_id, SUM(quantity * price) AS total_amount
FROM orders
GROUP BY customer_id;
3.6.1.2 计算每个月的总销售额
SELECT EXTRACT(YEAR_MONTH FROM order_date) AS month, SUM(quantity * price) AS total_sales
FROM orders
GROUP BY EXTRACT(YEAR_MONTH FROM order_date);
3.6.1.3 查找总订单金额超过1000的客户
SELECT customer_id, SUM(quantity * price) AS total_amount
FROM orders
GROUP BY customer_id
HAVING total_amount > 1000;

4. 数据完整性与约束

4.1 主键约束

CREATE TABLE employees (
    employee_id INT PRIMARY KEY,
    first_name VARCHAR(50),
    last_name VARCHAR(50)
);

4.2 外键约束

CREATE TABLE orders (
    order_id INT PRIMARY KEY,
    employee_id INT,
    FOREIGN KEY (employee_id) REFERENCES employees(employee_id)
);

4.3 唯一约束

CREATE TABLE users (
    user_id INT PRIMARY KEY,
    email VARCHAR(100) UNIQUE
);

4.4 检查约束

CREATE TABLE products (
    product_id INT PRIMARY KEY,
    price DECIMAL(10, 2),
    CHECK (price > 0)
);

5. SQL函数

5.1 字符串函数

SELECT UPPER(first_name) FROM employees;
SELECT LOWER(last_name) FROM employees;
SELECT LENGTH(first_name) FROM employees;

5.2 数学函数

SELECT ABS(-10);
SELECT ROUND(price, 2) FROM products;
SELECT MOD(salary, 2) FROM employees;

5.3 日期函数

SELECT CURRENT_DATE;
SELECT EXTRACT(YEAR FROM birth_date) FROM employees;
SELECT DATE_ADD(hire_date, INTERVAL 1 YEAR) FROM employees;

6. 视图

6.1 创建视图

CREATE VIEW employee_view AS
SELECT first_name, last_name, department_name
FROM employees
JOIN departments ON employees.department_id = departments.department_id;

6.2 查询视图

SELECT * FROM employee_view;

6.3 删除视图

DROP VIEW employee_view;

7. 存储过程与函数

7.1 创建存储过程

CREATE PROCEDURE add_employee(
    IN first_name VARCHAR(50),
    IN last_name VARCHAR(50),
    IN birth_date DATE,
    IN hire_date DATE
)
BEGIN
    INSERT INTO employees (first_name, last_name, birth_date, hire_date)
    VALUES (first_name, last_name, birth_date, hire_date);
END;

7.2 调用存储过程

CALL add_employee('Jane', 'Smith', '1990-02-01', '2021-02-01');

7.3 创建函数

CREATE FUNCTION calculate_age(birth_date DATE) RETURNS INT
BEGIN
    DECLARE age INT;
    SET age = YEAR(CURRENT_DATE) - YEAR(birth_date);
    RETURN age;
END;

7.4 调用函数

SELECT calculate_age('1980-01-01');

8. 事务

8.1 开始事务

START TRANSACTION;

8.2 提交事务

COMMIT;

8.3 回滚事务

ROLLBACK;

9. SQL性能优化

9.1 索引

CREATE INDEX idx_last_name ON employees(last_name);

9.2 查询优化

  • 使用EXPLAIN分析查询计划
  • 避免SELECT *
  • 使用适当的联合和子查询
  • 优化索引使用

9.3 数据库设计优化

  • 规范化数据库设计
  • 使用适当的数据类型
  • 避免冗余数据

10. SQL安全性

10.1 用户权限管理

CREATE USER 'username'@'localhost' IDENTIFIED BY 'password';
GRANT SELECT, INSERT ON mydatabase.* TO 'username'@'localhost';
REVOKE INSERT ON mydatabase.* FROM 'username'@'localhost';

10.2 数据加密

  • 使用数据库自带的加密功能
  • 在应用层进行数据加密

11. 常见SQL错误与调试

11.1 语法错误

  • 错误示例：SELEC * FROM employees;
  • 调试方法：检查SQL关键字拼写，使用SQL编辑器的语法高亮功能

11.2 数据类型错误

  • 错误示例：INSERT INTO employees (employee_id, first_name) VALUES ('one', 'John');
  • 调试方法：确保数据类型匹配，使用CAST或CONVERT函数进行类型转换

11.3 约束违反错误

  • 错误示例：INSERT INTO employees (employee_id) VALUES (1); -- 重复的主键
  • 调试方法：检查表的约束条件，确保数据符合约束要求

12. SQL工具与资源

12.1 SQL客户端工具

  • MySQL Workbench
  • SQL Server Management Studio (SSMS)
  • DBeaver
  • HeidiSQL

12.2 在线学习资源

  • LeetCode SQL练习
  • SQLZoo

12.3 书籍推荐

  • 《SQL基础教程》 by Ben Forta
  • 《SQL必知必会》 by Ben Forta
  • 《SQL权威指南》 by Jonathan Gennick

#sql

本站无任何商业行为
个人在线分享 » 【CS.DB】从零到精通：这可能是全网最全面最强大的SQL入门教程
admin

admin 钻石

分享到：

相关推荐

E-->