来源网站：SQL之母

建表语句

create table if not exists student
(
id integer not null primary key AUTOINCREMENT,
name varchar(256) not null,
age int null,
class_id bigint not null,
score double default 0 null,
exam_num int default 0 null
);

insert into student (name, age, class_id, score, exam_num)
values (‘鸡哥’, 25, 1, 2.5, 1);
insert into student (name, age, class_id, score, exam_num)
values (‘鱼皮’, 18, 1, 400, 4);
insert into student (name, age, class_id, score, exam_num)
values (‘热dog’, 40, 2, 600, 4);
insert into student (name, age, class_id, score, exam_num)
values (‘摸FISH’, null, 2, 360, 4);
insert into student (name, age, class_id, score, exam_num)
values (‘李阿巴’, 19, 3, 120, 2);
insert into student (name, age, class_id, score, exam_num)
values (‘老李’, 56, 3, 500, 4);
insert into student (name, age, class_id, score, exam_num)
values (‘李变量’, 24, 4, 390, 3);
insert into student (name, age, class_id, score, exam_num)
values (‘王加瓦’, 23, 4, 0, 4);
insert into student (name, age, class_id, score, exam_num)
values (‘赵派森’, 80, 4, 600, 4);
insert into student (name, age, class_id, score, exam_num)
values (‘孙加加’, 60, 5, 100.5, 1);

查询语句

  1. select name, score, score * 2 as double_score from student;
  2. select name, age from student where name != ‘热dog’
  3. select name, age, score from student where age is not null
  4. select name, score from student where name not like ‘%李%’ or score > 500; // 模糊查询，李%开头，%李结尾，_单字符。
  5. select distinct class_id, exam_num from student; // 去重
  6. select name, age, score from student order by score desc, age asc; // 排序
  7. select name, age from student order by age asc limit 1, 3; // 跳过第1行，取3行
  8. 条件分支
1
2
3
4
5
6
7
8
9
SELECT
  name,
  CASE WHEN (age > 60) THEN '老同学'
       WHEN (age > 20) THEN '年轻'
       ELSE '小同学' END AS age_level
FROM
  student
ORDER BY
  name asc;
  1. select name, date() as 当前日期 from student; // 获取当前日期,date(),time(),datetime()
  2. UPPER(name) // 大写, LOWER(name) // 小写, TRIM(name) // 去空格, LENGTH(name) // 字符串长度, SUBSTR(name, 1, 2) // 截取字符串, REPLACE(name, ‘李’, ‘王’) // 替换字符串, CONCAT(name, ‘王’) // 连接字符串, LOCATE(name, ‘李’) // 查找字符串, ROUND(score, 2) // 四舍五入, FLOOR(score) // 向下取整, CEIL(score) // 向上
  3. 聚合函数
1
2
3
4
5
6
7
SELECT
  SUM(score) AS total_score, // 求和
  AVG(score) AS avg_score, // 平均值
  MAX(score) AS max_score, // 最大值
  MIN(score) AS min_score  // 最小值
FROM
  student;
  1. 单字段分组
1
2
3
4
5
6
7
SELECT
  class_id,
  AVG(score) AS avg_score
FROM
  student
GROUP BY
  class_id;
  1. 多字段分组
1
2
3
4
5
6
7
8
SELECT
  class_id,
  exam_num,
  COUNT(*) AS total_num
FROM
  student
GROUP BY
  class_id, exam_num;
  1. 分组后过滤
1
2
3
4
5
6
7
8
9
SELECT
  class_id,
  SUM(score) AS total_score
FROM
  student
GROUP BY
  class_id
HAVING
  SUM(score) > 150;
  1. 关联查询 - cross join
    假设有一个学生表 student ，包含以下字段：id（学号）、name（姓名）、age（年龄）、class_id（班级编号）；还有一个班级表 class ，包含以下字段：id（班级编号）、name（班级名称）。
    请你编写一个 SQL 查询，将学生表和班级表的所有行组合在一起，并返回学生姓名（student_name）、学生年龄（student_age）、班级编号（class_id）以及班级名称（class_name）。
    select s.name student_name, s.age student_age, s.class_id class_id, c.name class_name from student s, class c;
  2. 关联查询 - inner join
    select s.name student_name, s.age student_age, s.class_id class_id, c.name class_name, c.level class_level from student s join class c on s.class_id = c.id;
  3. 关联查询 - outer join(left join, right join)
    select s.name student_name, s.age student_age, s.class_id class_id, c.name class_name, c.level class_level from student s left join class c on s.class_id = c.id;
  4. 子查询
    select name, score, class_id from student where class_id in (select distinct id from class);
    子查询中的一种特殊类型是 “exists” 子查询，用于检查主查询的结果集是否存在满足条件的记录，它返回布尔值（True 或 False），而不返回实际的数据。
    select name, age, class_id from student where not exists (select class_id from class where class.id = student.class_id);
  5. 组合查询：将多个 SELECT 查询结果合并
    UNION 和 UNION ALL（不去除重复的行）
1
2
3
select name, age, score, class_id from student
union all
select name, age, score, class_id from student_new;
  1. 开窗函数 - sum over
    开窗函数 - 允许我们在查询中进行对分组数据进行计算、同时保留原始行的详细信息。
    SUM(计算字段名) OVER (PARTITION BY 分组字段名)
1
2
3
4
5
6
7
8
9
10
SELECT 
    id,
    name,
    age,
    class_id,
    score,
    exam_num,
    AVG(score) OVER (PARTITION BY class_id) AS class_avg_score
FROM
    student;
  1. 开窗函数 - sum over order by
    SUM(计算字段名) OVER (PARTITION BY 分组字段名 ORDER BY 排序字段 排序规则)
1
2
3
4
5
6
7
8
9
SELECT 
    id,
    name,
    age,
    score,
    class_id,
    SUM(score) OVER (PARTITION BY class_id ORDER BY score ASC) AS class_sum_score
FROM
    student;
  1. 开窗函数 - rank
    对查询结果集中的行进行排名
1
2
3
4
RANK() OVER (
  PARTITION BY 列名1, 列名2, ... -- 可选，用于指定分组列
  ORDER BY 列名3 [ASC|DESC], 列名4 [ASC|DESC], ... -- 用于指定排序列及排序方式
) AS rank_column
  1. 开窗函数 - row_number
    为查询结果集中的每一行分配唯一连续排名
1
2
3
4
ROW_NUMBER() OVER (
  PARTITION BY column1, column2, ... -- 可选，用于指定分组列
  ORDER BY column3 [ASC|DESC], column4 [ASC|DESC], ... -- 用于指定排序列及排序方式
) AS row_number_column
  1. 开窗函数 - lag / lead (前/后)
    获取在当前行之前或之后的行的值
1
2
3
4
5
6
7
8
9
10
SELECT
    id,
    name,
    age,
    score,
    class_id,
    LAG(name) over (PARTITION BY class_id ORDER BY score DESC) as prev_name,
    LEAD(name) OVER (PARTITION BY class_id ORDER BY score DESC) AS next_name
FROM
    student;