SQL学习笔记——基础简单入门

文章来源：SQL之母 - 免费SQL自学网站 by 程序员鱼皮程序员鱼皮的免费 SQL 自学网站，入门数据库、入门 SQL 语言，编程学习http://sqlmother.yupi.icu/

注：文章仅供参考，不喜勿喷

       

目录

       

数据表

查询

条件查询

where

 运算符

空值 

 模糊查询

 逻辑运算

 基础语法

去重 （distinct）

 排序  （order by）

截断和偏移 （limit）

 条件分支 （case when）

函数

时间函数

 字符串处理

 聚合函数

分组聚合

单据段分组

多字段分组

having子句

查询进阶

关联查询-cross join

 关联查询-inner join

 关联查询-ourer join

子查询

子查询-exists

 组合查询

 开窗函数

 sum over

sum over order by

rank  

 row_number     

log/lead        

数据表

                student

ID	name	age	class	score
1	张三	21	1	80
2	李四	20	2	60
3	王老五	20	1	0
4	赵六	25	3	59
5	李翠花	19		30
6	Tom	20	2	90
7	杰哥	21	3	100
8	小明	18	4	86
9	李华	18	1	20

查询

全表查询

select *from student;

选择查询

select id,name,age from student; --查询学号，姓名，年龄

别名——是为数据表的列或查询结果中的字段取一个临时的、可读性更高的名字，使得查询结果更加直观和易读。

select id as 学号 , name as 学生姓名 from student;
 --将id取别名为学号，name取别名为学生姓名

常量和运算—— 是 SQL 中用于对数据进行计算和处理的重要概念。在 SQL 查询中，常量指的是固定的数值或文本（比如 "1"），而运算则是对这些常量进行数学运算或字符串操作。

select score + 10 as 和 , score * 2 as 积 from student; 
--分别将分数加10 和乘2并取别名   

条件查询

where

语法如下：

SELECT 列1, 列2, ...
FROM 表名
WHERE 条件;

列1, 列2, ...是你要选择的列，可以是具体的列名，也可以是*表示选择所有列。

表名是你要从中查询数据的表名。

条件是指定的查询条件，可以使用比较运算符（如=、<、>等）、逻辑运算符（如AND、OR等）、IN 操作符、LIKE 操作符等来设置条件。

select id,name,age from student where age >18 and score >=60;
--查找出年龄大于18 并且 分数大于等于60 的学生，并依次打印 id name age

 运算符

        ——是 SQL 中用于在条件查询中进行条件判断的特殊符号，比如 =、 !=、<、> 等。通过使用不同的运算符，我们可以在查询语句中设定多样化的条件，从而根据数据的不同属性进行灵活的筛选和过滤。

"BETWEEN" 运算符

select id,name,age from student where score between  60 and 80;
--查找分数在（60,80）的学生，并依次打印 id name age

空值 

        ——在数据库中，有时候数据表的某些字段可能没有值，即为空值（NULL）。空值表示该字段的值是未知的、不存在的或者没有被填写的。在SQL查询中，我们可以使用 "IS NULL" 和 "IS NOT NULL" 来判断字段是否为空值或非空值。

select id,name,age from student where class is null;
--查找班级没用填写的学生，并依次打印 id name age

 模糊查询

        ——是一种特殊的条件查询，它允许我们根据模式匹配来查找符合特定条件的数据，可以使用 LIKE 关键字实现模糊查询。

在 LIKE 模糊查询中，我们使用通配符来代表零个或多个字符，从而能够快速地找到匹配的数据。

有如下 2 种通配符：

• 百分号（%）：表示任意长度的任意字符序列。
• 下划线（_）：表示任意单个字符。

select id,name,age from student where name like '李%';
--查找姓李的学生，并依次打印 id name age

 逻辑运算

        ——是一种在条件查询中使用的运算符，它允许我们结合多个条件来过滤出符合特定条件的数据。在逻辑运算中，常用的运算符有：

• AND：表示逻辑与，要求同时满足多个条件，才返回 true。
• OR：表示逻辑或，要求满足其中任意一个条件，就返回 true。
• NOT：表示逻辑非，用于否定一个条件（本来是 true，用了 not 后转为 false）

select id,name,age from student where name like '李%' and score >90 ;
--查找分数大于90的李姓的学生，并依次打印 id name age

 基础语法

去重 （distinct）

        ——在数据表中，可能存在重复的数据记录，但如果我们想要过滤掉重复的记录，只保留不同的记录，就要使用 SQL 的去重功能。

在 SQL 中，我们可以使用 DISTINCT 关键字来实现去重操作。语法如下：

distinct 字段1, 字段2, 字段3, ...

select distinct class from student; --查找不同的班级   分别为 1 2 3

 排序  （order by）

        ——在 SQL 中，我们可以使用 ORDER BY 关键字来实现排序操作。ORDER BY 后面跟上需要排序的字段，可以选择升序（ASC）或降序（DESC）排列。

select id,name,age from student order by score desc;
--按照学生分数降序排序，并依次打印 id name age

当第一个重复时可以按照第二个 ，以此类推。语法如下：

order by 字段1 [升序/降序], 字段2 [升序/降序], ...

select id,name,age from student order by score desc,age asc;
--按照学生分数降序排序,分数相同按照年龄升序排序，并依次打印 id name age

截断和偏移 （limit）

        ——在 SQL 中，我们使用 LIMIT 关键字来实现数据的截断和偏移。截断和偏移的一个典型的应用场景是分页，即网站内容很多时，用户可以根据页号每次只看部分数据。

-- LIMIT 后只跟一个整数，表示要截断的数据条数（一次获取几条）
select id,name,age from student limit 2;
--一次获取2条

-- LIMIT 后跟 2 个整数，依次表示从第几条数据开始、一次获取几条
select id,name,age from student limit 2 , 2;
--从第2条数据开始，一次获取2条

 条件分支 （case when）

         ——条件分支 case when 是 SQL 中用于根据条件进行分支处理的语法。它类似于其他编程语言中的 if else 条件判断语句，允许我们根据不同的条件选择不同的结果返回。使用 case when 可以在查询结果中根据特定的条件动态生成新的列或对现有的列进行转换。

case when 支持同时指定多个分支，示例语法如下：

CASE WHEN (条件1) THEN 结果1
	   WHEN (条件2) THEN 结果2
	   ...
	   ELSE 其他结果 END

select id,name,age,case when (name = '李翠花') then 'woman' else 'man' end as sex
--添加性别列，当name等于李翠花时性别为女，其他都为男生，像极了大学学计算机的你

函数

时间函数

        ——在 SQL 中，时间函数是用于处理日期和时间的特殊函数。它们允许我们在查询中操作和处理日期、时间、日期时间数据，使得在数据库中进行时间相关的操作变得更加方便和灵活。

常用的时间函数有：

• DATE：获取当前日期
• DATETIME：获取当前日期时间
• TIME：获取当前时间

-- 获取当前日期
SELECT DATE() AS current_date;

-- 获取当前日期时间
SELECT DATETIME() AS current_datetime;

-- 获取当前时间
SELECT TIME() AS current_time;

 还有很多时间函数，比如计算两个日期的相差天数、获取当前日期对应的毫秒数等，实际运用时自行查阅即可。

 字符串处理

        ——在 SQL 中，字符串处理是一类用于处理文本数据的函数。它们允许我们对字符串进行各种操作，如转换大小写、计算字符串长度以及搜索和替换子字符串等。字符串处理函数可以帮助我们在数据库中对字符串进行加工和转换，从而满足不同的需求。

1）使用字符串处理函数 UPPER 将姓名转换为大写：

-- 将姓名转换为大写
SELECT name, UPPER(name) AS upper_name FROM student;

2）使用字符串处理函数 LENGTH 计算姓名长度：

-- 计算姓名长度
SELECT name, LENGTH(name) AS name_length FROM student;

3）使用字符串处理函数 LOWER 将姓名转换为小写：

-- 将姓名转换为小写并进行条件筛选
SELECT name, LOWER(name) AS lower_name FROM student;

 聚合函数

        ——在 SQL 中，聚合函数是一类用于对数据集进行 汇总计算 的特殊函数。它们可以对一组数据执行诸如计数、求和、平均值、最大值和最小值等操作。聚合函数通常在 SELECT 语句中配合 GROUP BY 子句使用，用于对分组后的数据进行汇总分析。

常见的聚合函数包括：

• COUNT：计算指定列的行数或非空值的数量。
• SUM：计算指定列的数值之和。
• AVG：计算指定列的数值平均值。
• MAX：找出指定列的最大值。
• MIN：找出指定列的最小值。

SELECT
  SUM(score) AS total_score, --学生分数和
  AVG(score) AS avg_score,   --分数平均值
  MAX(score) AS max_score,   --分数最大值
  MIN(score) AS min_score    --分数最小值
FROM
  student;

分组聚合

单据段分组

        ——在 SQL 中，分组聚合是一种对数据进行分类并对每个分类进行聚合计算的操作。它允许我们按照指定的列或字段对数据进行分组，然后对每个分组应用聚合函数，如 COUNT、SUM、AVG 等，以获得分组后的汇总结果。

在 SQL 中，通常使用 GROUP BY 关键字对数据进行分组。

SELECT  class, AVG(score) AS avg_score FROM student GROUP BY class
--分组聚合，查找每个班级号以及计算班级的平均值

多字段分组

        ——多字段分组和单字段分组的实现方式几乎一致，使用 GROUP BY 语法即可。

SELECT  class, AVG(score) AS avg_score FROM student GROUP BY class ,age;
--分组聚合，通过班级+年纪分组  并计算每个分组的平均分

having子句

        ——用于在分组聚合后对分组进行过滤。它允许我们对分组后的结果进行条件筛选，只保留满足特定条件的分组。HAVING 子句与条件查询 WHERE 子句的区别在于，WHERE 子句用于在 分组之前 进行过滤，而 HAVING 子句用于在 分组之后 进行过滤。

SELECT  class, AVG(score) AS avg_score FROM student GROUP BY class HAVING AVG(score) > 80;
--分组聚合，通过班级+年纪分组  筛选出班级平均分大于80的班级

查询进阶

关联查询-cross join

        ——在 SQL 中，关联查询是一种用于联合多个数据表中的数据的查询方式。其中，CROSS JOIN 是一种简单的关联查询，不需要任何条件来匹配行，它直接将左表的 每一行 与右表的 每一行 进行组合，返回的结果是两个表的笛卡尔积。

 例题：

假设有一个学生表 student ，包含以下字段：id（学号）、name（姓名）、age（年龄）、class_id（班级编号）；还有一个班级表 class ，包含以下字段：id（班级编号）、name（班级名称）。

请你编写一个 SQL 查询，将学生表和班级表的所有行组合在一起，并返回学生姓名（student_name）、学生年龄（student_age）、班级编号（class_id）以及班级名称（class_name）。

SELECT 
    student.name  student_name,
    student.age   student_age,
    class.id      class_id,
    class.name    class_name
FROM student
CROSS JOIN class

 关联查询-inner join

        ——INNER JOIN 是一种常见的关联查询方式，它根据两个表之间的关联条件，将满足条件的行组合在一起。

注意，INNER JOIN 只返回两个表中满足关联条件的交集部分，即在两个表中都存在的匹配行。

例题：

假设有一个学生表 student，包含以下字段：id（学号）、name（姓名）、age（年龄）、class_id（班级编号）。还有一个班级表 class，包含以下字段：id（班级编号）、name（班级名称）、level（班级级别）。

请你编写一个 SQL 查询，根据学生表和班级表之间的班级编号进行匹配，返回学生姓名（student_name）、学生年龄（student_age）、班级编号（class_id）、班级名称（class_name）、班级级别（class_level）。

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
  INNER JOIN class c ON s.class_id = c.id;

 关联查询-ourer join

        ——是一种关联查询方式，它根据指定的关联条件，将两个表中满足条件的行组合在一起，并 包含没有匹配的行 。在 OUTER JOIN 中，包括 LEFT OUTER JOIN 和 RIGHT OUTER JOIN 两种类型，它们分别表示查询左表和右表的所有行（即使没有被匹配），再加上满足条件的交集部分。

 例题：

假设有一个学生表 student，包含以下字段：id（学号）、name（姓名）、age（年龄）、class_id（班级编号）。还有一个班级表 class，包含以下字段：id（班级编号）、name（班级名称）、level（班级级别）。

请你编写一个 SQL 查询，根据学生表和班级表之间的班级编号进行匹配，返回学生姓名（student_name）、学生年龄（student_age）、班级编号（class_id）、班级名称（class_name）、班级级别（class_level），要求必须返回所有学生的信息（即使对应的班级编号不存在）。

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;

        有些数据库并不支持 RIGHT JOIN 语法，实现 RIGHT JOIN ,只需要把主表（from 后面的表）和关联表（LEFT JOIN 后面的表）顺序进行调换即可！

 

子查询

        ——是指在一个查询语句内部 嵌套 另一个完整的查询语句，内层查询被称为子查询。子查询可以用于获取更复杂的查询结果或者用于过滤数据。

当执行包含子查询的查询语句时，数据库引擎会首先执行子查询，然后将其结果作为条件或数据源来执行外层查询。

 例题：

假设有一个学生表 student，包含以下字段：id（学号）、name（姓名）、age（年龄）、score（分数）、class_id（班级编号）。还有一个班级表 class，包含以下字段：id（班级编号）、name（班级名称）。

请你编写一个 SQL 查询，使用子查询的方式来获取存在对应班级的学生的所有数据，返回学生姓名（name）、分数（score）、班级编号（class_id）字段。 

SELECT
  name,
  score,
  class_id
FROM
  student
WHERE
  class_id IN (
    SELECT DISTINCT   --去重
      id
    FROM
      class
  );

子查询-exists

        ——子查询是一种强大的查询工具，它可以嵌套在主查询中，帮助我们进行更复杂的条件过滤和数据检索。其中，子查询中的一种特殊类型是 "exists" 子查询，用于检查主查询的结果集是否存在满足条件的记录，它返回布尔值（True 或 False），而不返回实际的数据。

例题：

假设有一个学生表 student，包含以下字段：id（学号）、name（姓名）、age（年龄）、score（分数）、class_id（班级编号）。还有一个班级表 class，包含以下字段：id（班级编号）、name（班级名称）。

请你编写一个 SQL 查询，使用 exists 子查询的方式来获取 不存在对应班级的 学生的所有数据，返回学生姓名（name）、年龄（age）、班级编号（class_id）字段。

SELECT 
    name,
    age,
    class_id
FROM 
    student
WHERE
    NOT EXISTS (      --不存在的班级，需要用NOT反转
        SELECT
            class_id
        FROM
            class
        WHERE
            student.class_id = class.id
);

 组合查询

        ——组合查询是一种将多个 SELECT 查询结果合并在一起的查询操作。包括两种常见的组合查询操作：UNION 和 UNION ALL。

1. UNION 操作：它用于将两个或多个查询的结果集合并， 并去除重复的行 。即如果两个查询的结果有相同的行，则只保留一行。

2. UNION ALL 操作：它也用于将两个或多个查询的结果集合并， 但不去除重复的行 。即如果两个查询的结果有相同的行，则全部保留。

 例题：

假设有一个学生表 student，包含以下字段：id（学号）、name（姓名）、age（年龄）、score（分数）、class_id（班级编号）。还有一个新学生表 student_new，包含的字段和学生表完全一致。

请编写一条 SQL 语句，获取所有学生表和新学生表的学生姓名（name）、年龄（age）、分数（score）、班级编号（class_id）字段，要求保留重复的学生记录。

SELECT
  name,
  age,
  score,
  class_id
FROM
  student
UNION ALL
SELECT
  name,
  age,
  score,
  class_id
FROM
  student_new;

 开窗函数

开窗函数是一种强大的查询工具，它允许我们在查询中进行对分组数据进行计算、 同时保留原始行的详细信息 。开窗函数可以与聚合函数（如 SUM、AVG、COUNT 等）结合使用，但与普通聚合函数不同，开窗函数不会导致结果集的行数减少。

 sum over

        该函数用法为：

SUM(计算字段名) OVER (PARTITION BY 分组字段名)

 例题：

假设有一个学生表 student，包含以下字段：id（学号）、name（姓名）、age（年龄）、score（分数）、class_id（班级编号）。

请你编写一个 SQL 查询，返回每个学生的详细信息（字段顺序和原始表的字段顺序一致），并计算每个班级的学生平均分（class_avg_score）。

SELECT
  id,
  name,
  age,
  score,
  class_id,
  AVG(score) OVER (
    PARTITION BY
      class_id     --按照class_id分组
  ) AS class_avg_score
FROM
  student;

sum over order by

        可以实现同组内数据的 累加求和 。示例用法如下：

SUM(计算字段名) OVER (PARTITION BY 分组字段名 ORDER BY 排序字段 排序规则)

 例题：

假设有一个学生表 student，包含以下字段：id（学号）、name（姓名）、age（年龄）、score（分数）、class_id（班级编号）。

请你编写一个 SQL 查询，返回每个学生的详细信息（字段顺序和原始表的字段顺序一致），并且按照分数升序的方式累加计算每个班级的学生总分（class_sum_score）。

SELECT
    id,
    name,
    age,
    score,
    class_id,
    SUM (score) OVER (
    PARTITION BY 
        class_id
    ORDER BY
        score ASC     
)AS class_sum_score
FROM 
    student;

rank  

        Rank 开窗函数是 SQL 中一种用于对查询结果集中的行进行 排名 的开窗函数。它可以根据指定的列或表达式对结果集中的行进行排序，并为每一行分配一个排名。在排名过程中，相同的值将被赋予相同的排名，而不同的值将被赋予不同的排名。

当存在并列（相同排序值）时，Rank 会跳过后续排名，并保留相同的排名。

Rank 开窗函数的常见用法是在查询结果中查找前几名（Top N）或排名最高的行。

Rank 开窗函数的语法如下：

RANK() OVER (
  PARTITION BY 列名1, 列名2, ... -- 可选，用于指定分组列
  ORDER BY 列名3 [ASC|DESC], 列名4 [ASC|DESC], ... -- 用于指定排序列及排序方式
) AS 别名

其中，PARTITION BY 子句可选，用于指定分组列，将结果集按照指定列进行分组；ORDER BY 子句用于指定排序列及排序方式，决定了计算 Rank 时的排序规则。AS  用于指定生成的 Rank 排名列的别名。

 例题：

假设有一个学生表 student，包含以下字段：id（学号）、name（姓名）、age（年龄）、score（分数）、class_id（班级编号）。

请你编写一个 SQL 查询，返回每个学生的详细信息（字段顺序和原始表的字段顺序一致），并且按照分数降序的方式计算每个班级内的学生的分数排名（ranking）。

SELECT
  id,
  name,
  age,
  score,
  class_id,
  RANK() OVER (
    PARTITION BY
      class_id
    ORDER BY
      score DESC
  ) AS ranking
FROM
  student;

 row_number     

        Row_Number 开窗函数是 SQL 中的一种用于为查询结果集中的每一行 分配唯一连续排名 的开窗函数。

它与之前讲到的 Rank 函数，Row_Number 函数为每一行都分配一个唯一的整数值，不管是否存在并列（相同排序值）的情况。每一行都有一个唯一的行号，从 1 开始连续递增。

Row_Number 开窗函数的语法如下（几乎和 Rank 函数一模一样）：

ROW_NUMBER() OVER (
  PARTITION BY column1, column2, ... -- 可选，用于指定分组列
  ORDER BY column3 [ASC|DESC], column4 [ASC|DESC], ... -- 用于指定排序列及排序方式
) AS 别名

其中，PARTITION BY子句可选，用于指定分组列，将结果集按照指定列进行分组。ORDER BY 子句用于指定排序列及排序方式，决定了计算 Row_Number 时的排序规则。AS  用于指定生成的行号列的别名。

 例题：

假设有一个学生表 student，包含以下字段：id（学号）、name（姓名）、age（年龄）、score（分数）、class_id（班级编号）。

请你编写一个 SQL 查询，返回每个学生的详细信息（字段顺序和原始表的字段顺序一致），并且按照分数降序的方式给每个班级内的学生分配一个编号（row_number）。

SELECT
  id,
  name,
  age,
  score,
  class_id,
  ROW_NUMBER() OVER (
    PARTITION BY
      class_id
    ORDER BY
      score DESC
  ) AS row_number
FROM
  student;

log/lead        

        开窗函数 Lag 和 Lead 的作用是获取在当前行之前或之后的行的值，这两个函数通常在需要比较相邻行数据或进行时间序列分析时非常有用。

1）Lag 函数

Lag 函数用于获取 当前行之前 的某一列的值。它可以帮助我们查看上一行的数据。

Lag 函数的语法如下：

LAG(column_name, offset, default_value) OVER (PARTITION BY partition_column ORDER BY sort_column)

参数解释：

• column_name：要获取值的列名。
• offset：表示要向上偏移的行数。例如，offset为1表示获取上一行的值，offset为2表示获取上两行的值，以此类推。
• default_value：可选参数，用于指定当没有前一行时的默认值。
• PARTITION BY和ORDER BY子句可选，用于分组和排序数据。

2）Lead 函数

Lead 函数用于获取 当前行之后 的某一列的值。它可以帮助我们查看下一行的数据。

Lead 函数的语法如下：

LEAD(column_name, offset, default_value) OVER (PARTITION BY partition_column ORDER BY sort_column)

参数解释：

• column_name：要获取值的列名。
• offset：表示要向下偏移的行数。例如，offset为1表示获取下一行的值，offset为2表示获取下两行的值，以此类推。
• default_value：可选参数，用于指定当没有后一行时的默认值。
• PARTITION BY和ORDER BY子句可选，用于分组和排序数据。

例题：

假设有一个学生表 student，包含以下字段：id（学号）、name（姓名）、age（年龄）、score（分数）、class_id（班级编号）。

请你编写一个 SQL 查询，返回每个学生的详细信息（字段顺序和原始表的字段顺序一致），并且按照分数降序的方式获取每个班级内的学生的前一名学生姓名（prev_name）、后一名学生姓名（next_name）。

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;