sql语法

一、查询

1.全表查询

select * from student
//或者特定字段查询
select name,age from student

建表语句

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

insert into	`studenyt` (`name`,`age`,`class_id`,`score`,`exam_num`) 
values ('asd','12','1','256',`4`);

2.别名查询

别名语法 {原始字段名} as {别名} 来为查询结果的列名取一个便于理解的名称。通过使用别名，我们可以更直观地知道查询结果中每一列的含义，方便阅读和使用。

select name as 学生姓名,age as 学生年龄 from student

sql也可以把常量作为列名

比如 select 200,’啦啦啦’ as hobby

200	hobby
200	啦啦啦

3.常量和运算

常量指固定的数值或文本，运算则是对这些常量进行数学或字符串操作

select name,score,score*2 as double_score from student

将分数的2倍额外作为一列，展现出来

2.条件查询

只想取表中的部分数据

用where子句过滤

select name,score from student where name=='羽泪云'

也可以!=，>，between

1.空值

应用场景，比如筛选出没有参加考试的学生

用is null筛选出空值的；is not null筛选出不为空的

select name,age,score from student where score is null

2.模糊查询

比如查询姓氏，以张开头('张%')，以X结尾('%X')，不包含某个字

包含那就like ， 不包含那就 not like

select name,score from student where name not like '%李%'

3.逻辑运算

与、或、非

select name,score from student where name like '%李%' or score>500

二、去重

distinct，单字段去重，或多个字段组合(唯一)去重

select distinct class_id,exam_num from student

筛选出不重复班级ID和考试编号的组合

三、排序

​ order by

升序 asc ，降序desc

select name,age,score from student order by score desc,age asc

当第一个字段的值相同时，再按照第二个字段的值进行排序，以此类推。

按成绩降序排列，如果成绩相同，按年龄升序排列

四、截断和偏移

截断：挡住不需要看的部分（分页查询）

偏移：翻到要查看的位置

limit n，一次获取n条数据

limit n,m 从第n条开始，总共获取m条数据；第一条的下标是0

select name,age from student order by age asc limit 1,3

五、条件分支

单分支

CASE WHEN (name = '鸡哥') THEN '会' ELSE '不会' END AS can_rap

多分支

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

select name,
case when (age>60) then '老同学' 
    when (age>20) then '年轻'
    else '小同学' end as age_level
from student
order by name asc

六、时间函数

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

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

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

select name,date() as 当前日期 from student

七、字符串处理

转换大小写、计算字符串长度以及搜索和替换子字符串等。字符串处理函数可以帮助我们在数据库中对字符串进行加工和转换

比如姓名，有英文的转大写之类的，length()计算长度

select id,name,upper(name) as upper_name from student
where name='热dog'

八、聚合函数

常见的聚合函数包括：

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 中，通常使用 GROUP BY 关键字对数据进行分组。

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

统计每个班级的平均分

1.多字段分组

id（学号）、name（姓名）、class_id（班级编号）、exam_num（考试次数）、score（成绩），统计学生表中每个班级，每次考试的总学生人数

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

2.having 子句

HAVING 子句用于在分组聚合后对分组进行过滤。它允许我们对分组后的结果进行条件筛选，只保留满足特定条件的分组。

区别：WHERE 子句用于在 分组之前 进行过滤，而 HAVING 子句用于在 分组之后 进行过滤。

示例：

统计学生表中班级的总成绩超过 150 分的班级编号（class_id）和总成绩（total_score）。

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

进阶：

1.关联查询

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

单张表的基础上，获取更多额外数据，比如获取学生表中学生所属的班级信息等。

cross join

CROSS JOIN 是一种简单的关联查询，不需要任何条件来匹配行，它直接将左表的 每一行 与右表的 每一行 进行组合，返回的结果是两个表的笛卡尔积。

新建班级语句

create table if not exists `class`
(
    `id`       integer          not null primary key AUTOINCREMENT,
    `name`     varchar(256)     not null,
    `level`    varchar(256)     not null,
    `student_num` integer default 0 not null,
    `head_teacher_id` bigint not null
);
insert into `class` (`id`, `name`, `level`, `student_num`, `head_teacher_id`)
values (1, '唱班', '优', 10, 1);
...

示例：

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

select s.name as student_name,s.age as student_age, 
        s.class_id as class_id,
        c.name as class_name
from student s
cross join class c

inner join

它根据两个表之间的关联条件，将满足条件的行组合在一起。

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

即只有两个表之间存在对应关系的数据才会被放到查询结果中。

示例：

根据学生表和班级表之间的班级编号进行匹配，返回学生姓名（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
inner join class c on s.class_id==c.id

outer join

它根据指定的关联条件，将两个表中满足条件的行组合在一起，并 包含没有匹配的行 。

在 OUTER JOIN 中，包括 LEFT OUTER JOIN 和 RIGHT OUTER JOIN 两种类型，它们分别表示查询左表和右表的所有行（即使没有被匹配），再加上满足条件的交集部分。

示例：

请你编写一个 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
outer join class c on s.class_id==c.id

语句错误：RIGHT and FULL OUTER JOINs are not currently supported

错了，用left join

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
left join class c on s.class_id==c.id

这样的结果就是，即使在班级表中没有记录的班级，也会出现，但是对应的班级表信息为空

2.子查询

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

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

示例：

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

select name,score,class_id 
from student
where class_id in(
    select distinct id
    from class
    where id is not null
)

1.exsits

子查询中的一种特殊类型是 “exists” 子查询，用于检查主查询的结果集是否存在满足条件的记录，它返回布尔值（True 或 False），而不返回实际的数据。

示例：

请你编写一个 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。

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

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

示例：

获取所有学生表和新学生表的学生姓名（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.开窗函数

1.sum over

允许我们在查询中进行对分组数据进行计算、 同时保留原始行的详细信息

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

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

示例：

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

select 
id,
name,
age,
score,
class_id,
avg(score) over (partition by class_id) as class_avg_score
from student

partition by子句按照class_id划分

2.sum over order by

sum over order by，可以实现同组内数据的 累加求和 。

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

应用场景：老师对学生点名，对已点到的学生们的分数进行总和

示例：

返回每个学生的详细信息（字段顺序和原始表的字段顺序一致），并且按照分数升序的方式累加计算每个班级的学生总分（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

3.rank

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

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

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

使用方式：

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

示例：

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

select id,name,age,score,class_id,
        rank() over
        (partition by class_id order by score desc)
        as ranking
 from student

4.row_number

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

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

语法格式：

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

示例：

返回每个学生的详细信息（字段顺序和原始表的字段顺序一致），并且按照分数降序的方式给每个班级内的学生分配一个编号（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

即使呢score相同，也会有连续的row_number，即分个1,2,3排名出来 等下，暂时先放着

5.lag/lead

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

语法格式：

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子句可选，用于分组和排序数据。

lead同

应用场景：比如查看学生某个时期，之前的成绩，之后的成绩

示例：

返回每个学生的详细信息（字段顺序和原始表的字段顺序一致），并且按照分数降序的方式获取每个班级内的学生的前一名学生姓名（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

挑战

1.冒险者与金币

假设有一家冒险者公会，他们有一张名为 rewards 的表格，用于记录每个冒险者在各个任务中获得的金币奖励情况。

表格字段如下：

• adventurer_id：冒险者ID，唯一标识每个冒险者。
• adventurer_name：冒险者姓名。
• task_id：任务ID，唯一标识每个任务。
• task_name：任务名称。
• reward_coins：冒险者在该任务中获得的金币奖励数量。

请你编写一条 SQL 查询语句，依次输出每个冒险者的 id（adventurer_id）、冒险者姓名（adventurer_name）、获得的总金币奖励（total_reward_coins），并按照总金币奖励从高到低排序，其中只列出总金币奖励排名前 3 的冒险者。

select distinct adventurer_id,adventurer_name,
    sum(reward_coins) over
    (partition by adventurer_id) as total_reward_coins
 from rewards
order by total_reward_coins desc  

怎么输出前三呢

好吧 limit

select distinct adventurer_id,adventurer_name,
    sum(reward_coins) over
    (partition by adventurer_id) as total_reward_coins
 from rewards
order by total_reward_coins desc limit 3

分组聚合

select distinct adventurer_id,adventurer_name,
            sum(reward_coins) as total_reward_coins
from rewards
group by adventurer_id,adventurer_name
order by total_reward_coins desc limit 3