SQL操作学习笔记

前言：才发现下学期的课表上是《数据库系统原理》，而不是数据库应用，遂趁假期抽出一周来把基础的应用操作给学习了。关于为什么要学原理而不是直接应用，知乎上有篇回答的很好：

计算机系为什么要学数据库原理和设计？ - 南慕伦的回答 - 知乎 https://www.zhihu.com/question/273489729/answer/382181550

一、查询

• 全表查询：select * from 数据表name

• 选择查询：select name,age from student 从数据表student中选择name age列查看

• 查询改名：select name as 学生姓名,age as 学生年龄 from student 从student表中查询name列取别名为学生姓名

• 常量和运算：select name,score,score*2 as double_score from student 从student的数据表中选择出所有学生的姓名（name）和分数（score），并且额外计算出分数的 2 倍（double_score）。汉字字符加单引号。

name	score	double_score
鸡哥	2.5	5
鱼皮	400	800
热dog	600	1200
摸FISH	360	720
李阿巴	120	240
老李	500	1000

• 条件查询：

  select name,score from student where name='鱼皮'从student 的数据表中选择出所有学生的姓名（name）和成绩（score），学生姓名为 ‘鱼皮’。

  select name,age from student where name!='热dog'从student的数据表中选择出所有学生的姓名（name）和年龄（age），学生姓名不等于 ‘热dog’ 。

  在MySQL中，单等号和双等号都用于条件判断语句中，但是它们的语义不同。

  • 单等号（=）用于赋值，将右侧的值赋给左侧的变量。例如：SET name = 'John'。
  • 双等号（==）或等于号（=）用于条件判断，测试两个表达式是否相等。例如： SELECT * FROM student WHERE id = 100 表示筛选出id值为100的学生信息。

  需要注意的是，在MySQL中，双等号（==）和等于号（=）有相同的语义，即用于测试两个表达式是否相等。但是在其他编程语言中，这两个符号可能具有不同的含义，应根据具体情况使用。

  select name,age,score from student where age is not null从 student 的数据表中选择出所有学生的姓名（name）、年龄（age）和成绩（score），学生年龄不为空值。（查询空值 is null)

  select name,score from studen where name not like '%李%';从 student 的数据表中选择出所有学生的姓名（name）、年龄（age）和成绩（score），学生姓名不包含李。(查询包含 like)

  • %张：以”张”结尾的字符串。 例如，”李张”、”王张”、”赵张” 等都会被匹配到，因为它们都以”张”结尾。

  • %张%：包含”张”的字符串。 例如，”张三”、”李张四”、”王张五”等都会被匹配到，因为它们中都包含了”张”这个字符。

  • 张%：以”张”开头的字符串。 例如，”张三”、”张四光”、”张五风”等都会被匹配到，因为它们都以”张”开头。

  select name,score from student where name like '%李%' or score>500学生的姓名包含 “李”，或者成绩（score）大于 500。

二、去重

• distinct去重：

  select distinct class_id,exam_num from student从student的数据表中选择出所有不重复的班级 ID（class_id）和考试编号（exam_num）的组合。（剔除掉有相同数值的组合）

三、排序

• order by排序：

  select name,age,score from student order by score desc,age asc按照成绩从大到小排序，如果成绩相同，则按照年龄从小到大排序。

order by也可用于自定义排序，使用when…then…语法。如ORDER BY CASE category WHEN '电子产品' THEN 1 WHEN '家居用品' THEN 2 WHEN '食品' THEN 3 ELSE 4，自定义的排序规则，按照’电子产品’、’家居用品’、’食品’的顺序排序结果，并对其他类别赋予排序值4。

四、截断和偏移

假设你有一张待办事项清单，上面有很多任务。当你每次只想查看其中的几个任务时，会怎么办呢？

1）你可以使用手指挡住不需要看的部分（即截断）

2）根据任务的编号，直接翻到需要查看的位置（即偏移）

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

• limit截断偏移：

#按照年龄从小到大排序，从第 2 条数据开始、截取 3 个学生的信息。
select name,age from student 
order by age asc 
limit 1,3

五、条件分支

case when 判断条件：一种条件表达式，用于在 SQL 查询中根据不同的条件返回不同的结果。case…end是其实语句和结束语句，when …then…相当于if 。

• 将学生按照年龄划分为三个年龄等级（age_level）：60 岁以上为 “老同学”，20 岁以上（不包括 60 岁以上）为 “年轻”，20 岁及以下、以及没有年龄信息为 “小同学”。返回结果应包含学生的姓名（name）和年龄等级（age_level），并按姓名升序排序：

select name, 										#根据返回结果要求，这里不能加上age
    case 
        when (age>60) then "老同学"
        when (age>20 and age <=60) then "年轻"
        else "小同学" 
    end as age_level								#end as 作为新列的别名
from student order by name asc

六、函数

• 时间函数:SQL Date 函数 (w3school.com.cn),部分函数如下：

  • select date() as 列表名 获取 日期 并对日期新取列名

  • select datetime() as 列表名获取 日期时间 并对日期新取列名

  • select time() as 列表名获取 时间 并对时间取列名

eg：select name,date() as 当前日期 from student 展示所有学生的姓名（name）和当前日期（列名为 “当前日期”）

1. CURRENT_DATE：返回当前的日期。
2. CURRENT_TIME：返回当前的时间。
3. CURRENT_TIMESTAMP：返回当前的日期和时间。
4. DATE：从日期时间值中提取日期部分。
5. TIME：从日期时间值中提取时间部分。
6. YEAR：从日期或日期时间值中提取年份。
7. MONTH：从日期或日期时间值中提取月份。
8. DAY：从日期或日期时间值中提取天数。
9. HOUR：从时间或日期时间值中提取小时。
10. MINUTE：从时间或日期时间值中提取分钟。
11. SECOND：从时间或日期时间值中提取秒数。
12. DATEDIFF：计算两个日期之间的差值（以天为单位）。
13. DATE_ADD：将指定的时间间隔添加到日期或日期时间值。
14. DATE_SUB：从日期或日期时间值中减去指定的时间间隔。

• 字符串处理：字符串处理是一类用于处理文本数据的函数。它们允许我们对字符串进行各种操作。

  #筛选出姓名为 '热dog' 的学生，展示其学号（id）、姓名（name）及其大写姓名（upper_name）
  select id,name,upper(name) as upper_name
  from student
  where name='热dog'
  

  1. CONCAT：连接两个或多个字符串。
  2. LENGTH：返回字符串的长度。
  3. SUBSTRING：从字符串中提取子字符串。
  4. UPPER：将字符串转换为大写。
  5. LOWER：将字符串转换为小写。
  6. TRIM：去除字符串两端的空格或指定的字符。
  7. LEFT：从左侧截取字符串指定数量的字符。
  8. RIGHT：从右侧截取字符串指定数量的字符。
  9. REPLACE：替换字符串中的指定部分。
  10. CHARINDEX：查找子字符串在字符串中第一次出现的位置。
  11. SUBSTRING_INDEX：根据指定的分隔符，返回字符串中的一部分。

七、分组聚合

聚合函数：聚合函数是一类用于对数据集进行 汇总计算 的特殊函数。它们可以对一组数据执行诸如计数、求和、平均值、最大值和最小值等操作。

eg1：

order_id	customer_id	amount
1	A001	100
2	A002	200
3	A001	150
4	A003	50

SELECT COUNT(*) AS order_num FROM orders;使用count函数计算orders表总数（即行数）并展示order_num的数值

SELECT COUNT(DISTINCT customer_id) AS customer_num FROM orders;使用count（distinct 列名）函数计算orders表中不同客户的数量并展示total_amount的数值

SELECT SUM(amount) AS total_amount FROM orders;使用sum函数计算orders表订单总金额

eg2：

#假设有一个学生表 student，包含以下字段：id（学号）、name（姓名）、class_id（班级编号）、score（成绩）。请你编写一个 SQL 查询，汇总学生表中所有学生的总成绩（total_score）、平均成绩（avg_score）、最高成绩（max_score）和最低成绩（min_score）。

select 
    sum(score) as total_score, 
    avg(score) as avg_score,
    max(score) as max_score,
    min(score) as min_score
from 
    student;

• 分组聚合：按照group by中的关键字进行分组，可以用来合并重复关键字项。

  单字段分组：

#假设有一个学生表 student，包含以下字段：id（学号）、name（姓名）、class_id（班级编号）、score（成绩）。请你编写一个 SQL 查询，统计学生表中的班级编号（class_id）和每个班级的平均成绩（avg_score）。

select
    class_id,
    avg(score) as avg_score
from
    student
group by
    class_id;

​ 多字段分组：

#假设有一个学生表 student，包含以下字段：id（学号）、name（姓名）、class_id（班级编号）、exam_num（考试次数）、score（成绩）。请你编写一个 SQL 查询，统计学生表中每个班级每次考试的总学生人数（total_num）。

select
    class_id,
    exam_num,
    count(*) as total_num
from
    student
group by
    class_id,
    exam_num;

COUNT( * )：表示计算指定表中所有记录的数量，无论具体列的值是什么。它不考虑NULL值，只关注行的存在与否。因此，COUNT( * ) 返回的是表中的总行数。

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

#假设有一个学生表 student，包含以下字段：id（学号）、name（姓名）、class_id（班级编号）、score（成绩）。请你编写一个 SQL 查询，统计学生表中班级的总成绩超过 150 分的班级编号（class_id）和总成绩（total_score）。

select 
    class_id,
    sum(score) as total_score
from student
group by class_id
having sum(score)>150;

八、查询进阶

1、关联查询

在 SQL 中，关联查询是一种用于联合多个数据表中的数据的查询方式。

• cross join：是一种简单的关联查询，不需要任何条件来匹配行，它直接将左表的 每一行 与右表的 每一行 进行组合，返回的结果是两个表的笛卡尔积（两个集合的所有可能有序对的集合）。

#将员工表和部门表的所有行组合在一起，获取员工姓名、工资、部门名称和部门经理，示例 SQL 代码如下：
SELECT e.emp_name, e.salary, e.department, d.manager
FROM employees e
CROSS JOIN departments d;

• inner join：返回两个表中满足关联条件的交集部分，即在两个表中都存在的匹配行。

多表关联查询的 SQL 中，我们最好在选择字段时指定字段所属表的名称（比如 e.emp_name），还可以通过给表起别名（比如 employees e）来简化 SQL 语句。

eg1:

#将学生表和班级表的所有行组合在一起，并返回学生姓名（student_name）、学生年龄（student_age）、班级编号（class_id）以及班级名称（class_name）。
SELECT s.name AS student_name, s.age AS student_age, s.class_id, c.name AS class_name
FROM student s
JOIN class c ON s.class_id = c.id;

eg2:

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

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

select 
    s.name as student_name,
    s.age as student_age,
    s.class_id as class_id,
    c.name as class_name,
    c.level as class_level
from student s
join class c on  s.class_id = c.id;

• outer 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 AS student_name,
    s.age AS student_age, 
    c.id AS class_id,
    c.name AS class_name,
    c.level AS class_level
FROM
    student s
LEFT JOIN class c ON s.class_id = c.id;

2、子查询

子查询就像是在一个盒子中的盒子，外层查询是大盒子，内层查询是小盒子。执行查询时，我们首先打开小盒子获取结果，然后将小盒子的结果放到大盒子中继续处理。

#假设有一个学生表 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(
    select 1
    from class
    where student.class_id = class.id
);

3、组合查询

组合查询是一种将多个 SELECT 查询结果合并在一起的查询操作。其中union操作用于将两个或多个查询的结果集合并， 并去除重复的行 。即如果两个查询的结果有相同的行，则只保留一行。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

4、开窗函数

开窗函数是一种强大的查询工具，它允许我们在查询中进行对分组数据进行计算、 同时保留原始行的详细信息 。

开窗函数可以与聚合函数（如 SUM、AVG、COUNT 等）结合使用，但与普通聚合函数不同，开窗函数不会导致结果集的行数减少。

• sum over：按照一定规则聚合函数

  #假设有一个学生表 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) as class_avg_score
  from
      student;
  

• sum over 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：根据指定列的结果或表达式对行进行排序，可产生相同排名

  #假设有一个学生表 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：根据指定列的结果或表达式对行进行排序，不可产生相同排名

  #假设有一个学生表 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;
  

1. RANK()函数：RANK()函数计算并返回每行的排名，如果有相同的值，则会跳过相同的排名并产生连续的排名，下一个排名将会增加与前面相同值的数量相等的步长。例如，如果有两个学生得分相同，他们的排名将是相同的，下一个学生的排名将增加2。如果两个学生得分不同，他们将获得不同的排名。如果两个学生得分相同，则他们将获得相同的排名，而下一个学生将获得跳过相同分数数量的排名。
2. ROW_NUMBER()函数：ROW_NUMBER()函数计算并返回每行的唯一排名，它不考虑相同值的情况。即使有多行具有相同的值，它们也会分配不同的排名。这意味着对于具有相同分数的学生，他们将获得不同的排名。

因此，主要区别在于RANK()函数处理相同值时可能会跳过排名，而ROW_NUMBER()函数始终产生唯一的排名。具体使用哪个函数取决于您的需求和对排名逻辑的理解。

• lag/lead：根据一定规则获取当前行之前/之后数据

  #假设有一个学生表 student，包含以下字段：id（学号）、name（姓名）、age（年龄）、score（分数）、class_id（班级编号）。
  
  #请你编写一个 SQL 查询，返回每个学生的详细信息（字段顺序和原始表的字段顺序一致），并且按照分数降序的方式获取每个班级内的学生的前一名学生姓名（prev_name）、后一名学生姓名（next_name）。
  
  select 
      id,name,age,score,class_id,
      lag(name,1,null) over(
          partition by class_id order by score desc
      )as  prev_name,
      lead(name,1,null) over(
          partition by class_id order by score desc
      )as next_name
  from 
      student
  

九、其他注意事项

• SQL下标也从0开始
• SQL不区分大小写
• SQL查询一个表只用一个select from
• 一段SQL语句只有最结尾使用分号
• 查询不同的内容之间使用逗号隔开