SQL学习

SQL学习网站：https://sqlmother.yupi.icu/#/levels

基础语法 - 查询

查询-全表查询

使用 select 全表查询语句，查看整个学生表的内容

select * from student;

查询结果：

学号	姓名	年龄
101	小明	20
102	小红	22
103	小刚	21
104	小丽	19

查询 - 选择查询

使用"选择查询"来获取所有学生的姓名（name）和年龄（age）信息

select name,age from student；

name	age
鸡哥	25
鱼皮	18
热dog	40
摸FISH
李阿巴	19
老李	56
李变量	24
王加瓦	23
赵派森	80
孙加加	60

查询 - 别名

从名为 student 的数据表中选择出所有学生的姓名（name）和年龄（age）信息，并为它们取别名为 学生姓名 和 学生年龄

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

学生姓名	学生年龄
鸡哥	25
鱼皮	18
热dog	40
摸FISH
李阿巴	19
老李	56
李变量	24
王加瓦	23
赵派森	80
孙加加	60

查询 - 常量和运算

从名为student的数据表中选择出所有学生的姓名（name）和分数（score），并且额外计算出分数的 2 倍（double_score）

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

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

条件查询 - where

从名为student 的数据表中选择出所有学生的姓名（name）和成绩（score），要求学生姓名为 '鱼皮'。

select name,score from student where name='鱼皮';

name	score
鱼皮	400

条件查询 - 运算符

名为 student 的数据表中选择出所有学生的姓名（name）和年龄（age），要求学生姓名不等于 '热dog' 。

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

select name,age from student where name !='热dog';

name	age
鸡哥	25
鱼皮	18
摸FISH
李阿巴	19

使用 "!=" 运算符筛选出 name 不是 '小张' 的员工

select name, age, salary from employees where name != '小张';

使用 ">" 运算符筛选出工资高于 5500 的员工：

select name, age, salary from employees where salary > 5500;

使用 "BETWEEN" 运算符筛选出年龄在 25 到 30 之间的员工：

select name, age, salary from employees where age between 25 and 30;

条件查询 - 空值

从名为 student 的数据表中选择出所有学生的姓名（name）、年龄（age）和成绩（score），要求学生年龄不为空值。

在SQL查询中，我们可以使用 "IS NULL" 和 "IS NOT NULL" 来判断字段是否为空值或非空值。

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

name	age	score
鸡哥	25	2.5
鱼皮	18	400
热dog	40	600

条件查询 - 模糊查询

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

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

有如下 2 种通配符：

• 百分号（%）：表示任意长度的任意字符序列。

• 下划线（_）：表示任意单个字符。

  从名为 student 的数据表中选择出所有学生的姓名（name）和成绩（score），要求姓名（name）不包含 "李" 这个字。

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

我们使用 LIKE 模糊查询来找出姓名（name）中包含关键字 "张" 的员工信息：

select name, age, position from employees where name like '%张%';

条件查询 - 逻辑运算

在逻辑运算中，常用的运算符有：

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

从名为 student 的数据表中选择出所有学生的姓名（name）、成绩（score），要求学生的姓名包含 "李"，或者成绩（score）大于 500。

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

使用逻辑运算来找出姓名中包含关键字 "李" 且 年龄小于 30 岁的员工信息：

select name, age, salary from employees where name like '%李%' and age < 30;

基础语法 - 去重

在 SQL 中，可以使用 DISTINCT 关键字来实现去重操作。

举个应用场景：假设你是班长，要统计班级中有哪些不同的学生，而不关心他们重复出现的次数，就可以使用去重。

从名为 student 的数据表中选择出所有不重复的班级 ID（class_id）和考试编号（exam_num）的组合。

select distinct class_id,exam_num from student;

基础语法 - 排序

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

-- SQL 查询语句 1
select name, age from students order by age asc;

-- SQL 查询语句 2
select name, score from students order by score desc;

按照年龄升序（从小到大）：

name	age
张三	18
王五	19
李四	20
赵六	20

按照分数降序（从大到小）：

name	score
王五	92
张三	90
赵六	88
李四	85

从名为 student 的数据表中选择出学生姓名（name）、年龄（age）和成绩（score），首先按照成绩从大到小排序，如果成绩相同，则按照年龄从小到大排序。

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

基础语法 - 截断和偏移

在 SQL 中，使用 LIMIT 关键字来实现数据的截断和偏移。

截断和偏移的一个典型的应用场景是分页，即网站内容很多时，用户可以根据页号每次只看部分数据

从名为 student 的数据表中选择学生姓名（name）和年龄（age），按照年龄从小到大排序，从第 2 条数据开始、截取 3 个学生的信息

name	age
鱼皮	18
李阿巴	19
王加瓦	23

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

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

查询语句 1 结果，只获取了 2 条数据：

task_name	due_date
完成报告	2023-08-05
预约医生	2023-08-08

查询语句 2 结果，从下标为 2（第 3 条）数据的位置开始获取 2 条数据：

task_name	due_date
购买礼物	2023-08-10
安排旅行	2023-08-15

基础语法 - 条件分支

使用 case when 可以在查询结果中根据特定的条件动态生成新的列或对现有的列进行转换

将学生按照年龄划分为三个年龄等级（age_level）：60 岁以上为 "老同学"，20 岁以上（不包括 60 岁以上）为 "年轻"，20 岁及以下、以及没有年龄信息为 "小同学"。

返回结果应包含学生的姓名（name）和年龄等级（age_level），并按姓名升序排序。

-- 请在此处输入 SQL
SELECT name,
  CASE
    WHEN (age > 60) THEN '老同学'
    WHEN (age > 20) THEN '年轻'
    ELSE '小同学'
  END AS age_level
FROM
  student
ORDER BY
  name asc;

name	age_level
孙加加	年轻
摸FISH	小同学
李变量	年轻
李阿巴	小同学
热dog	年轻

使用条件分支 case when ，根据 name 来判断学生是否会说 RAP，并起别名为 can_rap。

示例 SQL 如下：

SELECT
  name,
  CASE WHEN (name = '鸡哥') THEN '会' ELSE '不会' END AS can_rap
FROM
  student;

查询结果：

name	can_rap
小明	不会
鸡哥	会
李华	不会
王五	不会

函数 - 时间函数

常用的时间函数有：

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

编写一个 SQL 查询，展示所有学生的姓名（name）和当前日期（列名为 "当前日期"）。

select name,DATE() as "当前日期" from student;

name	当前日期
鸡哥	2024-11-14
鱼皮	2024-11-14
热dog	2024-11-14
摸FISH	2024-11-14
李阿巴	2024-11-14

使用时间函数获取当前日期、当前日期时间和当前时间：

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

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

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

函数 - 字符串处理

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

编写一个 SQL 查询，筛选出姓名为 '热dog' 的学生，展示其学号（id）、姓名（name）及其大写姓名（upper_name）。

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

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

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

查询结果：

name	upper_name
小明	小明
热dog	热DOG
Fish摸摸	FISH摸摸
鸡哥	鸡哥

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

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

查询结果：

name	name_length
小明	2
热dog	4
Fish摸摸	6
鸡哥	2

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

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

查询结果：

name	lower_name
小明	小明
热dog	热dog
Fish摸摸	fish摸摸
鸡哥	鸡哥

函数 - 聚合函数

常见的聚合函数包括：

• COUNT：计算指定列的行数或非空值的数量。

• SUM：计算指定列的数值之和。

• AVG：计算指定列的数值平均值。

• MAX：找出指定列的最大值。

• MIN：找出指定列的最小值。

  编写一个 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;
  

假设有一个订单表 orders，包含以下字段：order_id（订单号）、customer_id（客户编号）、amount（订单金额）。数据如下：

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

1）使用聚合函数 COUNT 计算订单表中的总订单数：

SELECT COUNT(*) AS order_num
FROM orders;

查询结果：

order_num

4

2）使用聚合函数 COUNT(DISTINCT 列名) 计算订单表中不同客户的数量：

SELECT COUNT(DISTINCT customer_id) AS customer_num
FROM orders;

查询结果：

customer_num

3

3）使用聚合函数 SUM 计算总订单金额：

SELECT SUM(amount) AS total_amount
FROM orders;

查询结果：

total_amount

500

分组聚合 - 单字段分组

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

编写一个 SQL 查询，统计学生表中的班级编号（class_id）和每个班级的平均成绩（avg_score）。

-- 请在此处输入 SQL
select class_id,AVG(score) as avg_score from student group by class_id

class_id	avg_score
1	201.25
2	480
3	310
4	330
5	100.5

分组聚合 - 多字段分组

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

编写一个 SQL 查询，统计学生表中的班级编号（class_id），考试次数（exam_num）和每个班级每次考试的总学生人数（total_num）

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

class_id	exam_num	total_num
1	1	1
1	4	1
2	4	2
3	2	1

查询使用多字段分组查询表中 每个客户 购买的 每种商品 的总金额，相当于按照客户编号和商品编号分组：

-- 查询每个用户购买的每种商品的总金额，按照客户编号和商品编号分组
SELECT customer_id, product_id, SUM(amount) AS total_amount
FROM orders
GROUP BY customer_id, product_id;

查询结果：

customer_id	product_id	total_amount
A001	1	250
A001	2	50
A002	1	200
A003	1	50

分组聚合 - having 子句

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

请你编写一个 SQL 查询，统计学生表中班级的总成绩超过 150 分的班级编号（class_id）和总成绩（total_score）。

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

假设有一个订单表 orders，包含以下字段：order_id（订单号）、customer_id（客户编号）、amount（订单金额）。数据如下：

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

1）使用 HAVING 子句查询订单数超过 1 的客户：

SELECT customer_id, COUNT(order_id) AS order_num
FROM orders
GROUP BY customer_id
HAVING COUNT(order_id) > 1;

查询结果：

customer_id	order_num
A001	2

2）使用 HAVING 子句查询订单总金额超过 100 的客户：

-- 查询订单总金额超过100的客户
SELECT customer_id, SUM(amount) AS total_amount
FROM orders
GROUP BY customer_id
HAVING SUM(amount) > 100;

查询结果：

customer_id	total_amount
A001	250
A002	200

查询进阶 - 子查询

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

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

select name,score,class_id from student where class_id in (select distinct id from class);

假设我们有以下两个数据表：orders 和 customers，分别包含订单信息和客户信息。

orders 表：

order_id	customer_id	order_date	total_amount
1	101	2023-01-01	200
2	102	2023-01-05	350
3	101	2023-01-10	120
4	103	2023-01-15	500

customers 表：

customer_id	name	city
101	Alice	New York
102	Bob	Los Angeles
103	Charlie	Chicago

现在，我们希望查询出订单总金额 > 200 的客户的姓名和他们的订单总金额，示例 SQL 如下：

-- 主查询
SELECT name, total_amount
FROM customers
WHERE customer_id IN (
    -- 子查询
    SELECT DISTINCT customer_id
    FROM orders
    WHERE total_amount > 200
);

在上述 SQL 中，先通过子查询从订单表中过滤查询出了符合条件的客户 id，然后再根据客户 id 到客户信息表中查询客户信息，这样可以少查询很多客户信息数据。

上述语句的查询结果：

name	total_amount
Bob	350
Charlie	500

关注小张的知识杂货铺，让我们一起学习一起进步