深入SQL：从基础操作到高级查询的全景解析

spiker_

于 2024-08-14 14:20:56 发布

阅读量1k

点赞数 31

文章标签： sql oracle 数据库

本文链接：https://blog.csdn.net/spiker_/article/details/141185138

版权

深入SQL：从基础操作到高级查询的全景解析

在数据管理中，SQL（Structured Query Language）是操作和查询关系型数据库的标准语言。本文将从基础的SQL语句开始，逐步深入，介绍SQL的进阶用法。

1. 基本SQL语句

1.1 创建数据库和表

创建数据库

CREATE DATABASE mydatabase;

这条语句用于创建一个名为 mydatabase 的数据库。

使用数据库

USE mydatabase;

选择使用 mydatabase 数据库，接下来的操作都将在此数据库内执行。

创建表

CREATE TABLE students (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(100) NOT NULL,
    age INT,
    grade CHAR(2)
);

创建一个名为 students 的表，包含 id、name、age 和 grade 字段。

1.2 插入数据

插入单条记录

INSERT INTO students (name, age, grade) VALUES ('Alice', 14, 'A');`

插入一条新记录到 students 表。

插入多条记录

INSERT INTO students (name, age, grade) VALUES 
    ('Bob', 15, 'B'),
    ('Charlie', 16, 'C');

插入多条记录到 students 表。

1.3 查询数据

查询所有数据

SELECT * FROM students;

查询 students 表中的所有记录。

查询特定列

SELECT name, grade FROM students;

仅查询 name 和 grade 列的数据。

使用条件查询

SELECT * FROM students WHERE age > 14;

查询 age 大于 14 的学生记录。

排序查询结果

SELECT * FROM students ORDER BY age DESC;

按 age 降序排列查询结果。

模糊查询

SELECT * FROM students WHERE name LIKE '%Ali%';

这条语句用于查找 name 中包含“Ali”的所有学生记录。

% 表示任意数量的字符（包括零个字符）。
_ 表示单个字符。

前缀匹配

SELECT * FROM students WHERE name LIKE 'A%';

这条语句会返回所有名字以“A”开头的学生记录。

后缀匹配

SELECT * FROM students WHERE name LIKE '%e';

这条语句会返回所有名字以“e”结尾的学生记录。

精确长度匹配

SELECT * FROM students WHERE name LIKE 'A_i_e';

这条语句会返回名字以“A”开头，第四个字符为“e”，并且中间有两个任意字符的学生记录，例如“Alice”。

1.4 更新数据

更新单条记录

UPDATE students SET grade = 'A+' WHERE name = 'Charlie';

更新 name 为 Charlie 的学生的 grade。

更新多条记录

UPDATE students SET grade = 'B' WHERE age < 15;

更新 age 小于 15 岁的所有学生的 grade。

1.5 删除数据

删除单条记录

DELETE FROM students WHERE name = 'Alice';

删除 name 为 Alice 的学生记录。

删除所有记录

DELETE FROM students;

删除 students 表中的所有记录。

2. 中级SQL语句

2.1 使用聚合函数

计数

SELECT COUNT(*) FROM students;

计算 students 表中的记录总数。

求平均值

SELECT AVG(age) FROM students;

计算 age 的平均值。

求总和

SELECT SUM(age) FROM students;

计算 age 的总和。

最大值和最小值

SELECT MAX(age), MIN(age) FROM students;

分别查询 age 的最大值和最小值。

2.2 分组和聚合

按年级分组并统计数量

SELECT grade, COUNT(*) FROM students GROUP BY grade;

按 grade 分组，并统计每个年级的学生数量。

使用HAVING过滤分组结果

SELECT grade, COUNT(*) FROM students GROUP BY grade HAVING COUNT(*) > 1;

只显示学生数量大于1的年级。

2.3 子查询

在WHERE子句中使用子查询

SELECT * FROM students WHERE age > (SELECT AVG(age) FROM students);

查询 age 大于所有学生平均年龄的学生。

在FROM子句中使用子查询

SELECT name, grade FROM (SELECT * FROM students WHERE age > 14) AS older_students;

查询 age 大于 14 的学生，并只显示他们的 name 和 grade。

2.4 连接表

内连接

SELECT students.name, courses.course_name 
FROM students
JOIN courses ON students.id = courses.student_id;

通过 id 和 student_id 的关系连接 students 表和 courses 表，并显示学生的 name 和所选课程的 course_name。

左连接

SELECT students.name, courses.course_name 
FROM students
LEFT JOIN courses ON students.id = courses.student_id;

使用左连接，显示所有学生，即使他们没有选课。

3. 高级SQL语句

3.1 嵌套子查询

SELECT name 
FROM students 
WHERE id IN (
    SELECT student_id 
    FROM courses 
    WHERE course_name = 'Math' AND student_id IN (
        SELECT id 
        FROM students 
        WHERE age > 14
    )
);

查询选了 Math 课并且 age 大于 14 岁的学生姓名。

3.2 窗口函数

计算累计和

SELECT name, age, 
       SUM(age) OVER (ORDER BY age) AS cumulative_age
FROM students;

按 age 排序并计算 age 的累计和。

计算行号

SELECT name, age, 
       ROW_NUMBER() OVER (ORDER BY age) AS row_num
FROM students;

按 age 排序，并为每条记录分配一个行号。

3.3 CTE（公共表表达式）

定义和使用CTE

WITH older_students AS (
    SELECT * FROM students WHERE age > 14
)
SELECT name, grade FROM older_students;

定义一个CTE older_students，并查询其中的数据。

3.4 复杂的JOIN和子查询组合

SELECT s.name, c.course_name, 
   (SELECT COUNT(*) FROM courses WHERE student_id = s.id) AS course_count
FROM students s
LEFT JOIN courses c ON s.id = c.student_id
WHERE s.age > (SELECT AVG(age) FROM students);