SQL 增删改查中“查”的常考知识点

渐变-重构

已于 2022-08-09 18:28:51 修改

阅读量320

点赞数

文章标签：后端

于 2022-08-09 09:52:56 首次发布

本文链接：https://blog.csdn.net/hscing/article/details/126241525

版权

SQL语法简单，这里总结下SQL的"增删查改"常用操作中的"查"。面试笔试中经常考查“多表连接”，“窗口函数”，“子查询”，“分组聚合”等知识点

SELECT基础检索的知识点考查

检索单列与多列数据
正确使用DISTINCT
数据查询并排序ORDER BY

限制数据返回行数LIMIT

DROP TABLE IF EXISTS employee_info;
CREATE TABLE employee_info (
employee_id VARCHAR(8),
employee_name VARCHAR(8),
age INT,
gender VARCHAR(8),
salary INT
)
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8;
INSERT INTO
employee_info (employee_id, employee_name, age,gender,salary)
VALUE('e001','Bob',28,'male', 8000)
,('e002','Alice',25,'female',6000)
,('e003','David',26,'male',9000)
,('e004','Kitty',30,'female',11000)
,('e005','Allen',24,'male',5500);

# 检索单列数据
SELECT employee_name From employee_info;

# 检索多列数据
SELECT employee_id,employee_name,age FROM employee_info;

# *符号是通配符，匹配任意或所有数据。
SELECT * FROM employee_info;

# 插入一条数据
INSERT INTO
employee_info (employee_id,employee_name,age,gender,salary)
VALUE('e006','Ben',28,'male',8000)

SELECT gender FROM employee_info;

# DISTINCT 去重
SELECT DISTINCT gender FROM employee_info;

# ORDER BY 排序
SELECT * FROM employee_info ORDER BY age DESC;

# 默认升序排列
SELECT * FROM employee_info  ORDER BY  age;


# 插入一条数据
INSERT INTO
employee_info (employee_id,employee_name,age,gender,salary)
VALUE('e007','George',26,'male',10000)

## 多列排序,先按照age列进行降序排列，当age列的各个数据相同时,再按照salary列进行排列。
SELECT * FROM employee_info ORDER BY age DESC,salary;

# 使用LIMIT限制返回行数
SELECT * FROM employee_info LIMIT 3;

# 从第3行开始的后3行,这里2代表第3行
SELECT * FROM employee_info LIMIT 2,3;

过滤数据的知识点考查

使用WHERE过滤数据
多条件过滤的优先级
使用BETWEEN过滤的边界问题
关于NULL的过滤

表达式匹配过滤

DROP TABLE IF EXISTS purchase_info;
CREATE TABLE purchase_info(
    commodity_id VARCHAR(8),
    category VARCHAR(16),
    colour VARCHAR(16),
    purchase_quantity  INT,
    purchase_date DATE
)
ENGINE = InnoDB
DEFAULT CHARSET = utf8;
INSERT INTO
purchase_info
(commodity_id, category, colour, purchase_quantity, purchase_date)
VALUE ('c001','clothing','black',150,'2020-04-25')
,('c002','clothing','white',50,'2020-04-05')
,('c003','shoes','white',500,'2020-03-23')
,('c004','shoes','red',200,'2020-04-07')
,('c005','clothing','blue',120,'2020-04-15');

SELECT * FROM purchase_info WHERE category = 'clothing';

SELECT * FROM purchase_info WHERE category = 'clothing' and colour = 'blue';


SELECT * FROM purchase_info WHERE category = 'clothing' AND (colour = 'black' OR colour ='white');

# BETWEEN过滤 包括边界的
SELECT *
FROM purchase_info
WHERE purchase_date >= '2020-04-01'
AND purchase_date <= '2020-04-30';

# 简化
SELECT * FROM purchase_info WHERE purchase_date BETWEEN '2020-04-01' AND '2020-04-30';

#NULL过滤,若某个字段的某个值为空白,则在SQL中以NULL代替
INSERT INTO
purchase_info
(commodity_id, category, colour, purchase_quantity, purchase_date)
VALUE ('C007','category',NULL,NULL,'2020-04-15');

SELECT * FROM purchase_info WHERE colour IS NULL;

# 排序时 NULL 最小
select * from purchase_info ORDER BY purchase_quantity DESC;
# 使用IN
SELECT * FROM purchase_info WHERE colour IN ('black','white','blue');

SELECT *
FROM purchase_info
WHERE colour <> 'red'
AND colour IS NOT NULL;


# 使用LIKE 与通配符过滤
DROP TABLE IF EXISTS contact_info;
CREATE TABLE contact_info(
employee_id  VARCHAR(8),
employee_name VARCHAR(8),
email VARCHAR(32)
)
ENGINE = InnoDB
DEFAULT CHARSET = utf8;
INSERT INTO
contact_info(employee_id, employee_name, email)
    VALUE('e001','王乐','12345@qq.com')
        ,('e002','张三','88888@163.com')
        ,('e003','王梦瑶','wangmengyao@163.com')
        ,('e004','李四','lisi001@qq,com');

# %
SELECT *
FROM contact_info
WHERE email  LIKE '%@163.com';

# _
SELECT *
FROM   contact_info
WHERE employee_name like '王__';

# REGEXP
SELECT *
FROM contact_info
WHERE employee_name REGEXP '^[李王]';

SELECT *
FROM contact_info
WHERE email REGEXP '8{3,}';

SELECT * FROM contact_info WHERE email REGEXP '[a-zA-Z].*@';

字段的知识点考查

拼接字段生成所需新字段

# 对字段增加别名
SELECT address_id,scenic_spot,Concat(city,exact_address) AS full_address  FROM address_info;

# 指定别名时 AS可以省略，从而进一步将代码简化为:
SELECT address_id,scenic_spot,Concat(city,exact_address) full_address FROM address_info;

算数计算生成所需新字段

SELECT *,item_pricing*purchase_quantity AS total_price 
FROM shopping_list;

数据处理函数的知识点考查

文本处理函数

#SUBSTRING()函数提取年、月、日
SELECT *
 ,SUBSTRING(order_time,1,4) AS order_year
 ,SUBSTRING(order_time,6,2) AS order_month
 ,SUBSTRING(order_time,9,2) AS order_day
FROM  order_detail;

日期/时间处理函数

DATE_ADD()、NOW()、DAYOFWEEK() 、EXTRACT()
数值处理函数

SELECT ABS(-2);

聚合函数知识点考查

常用聚合函数的使用场景
- 聚合函数作用的对象是某个字段的多行数据,返回的是单个值。

SELECT AVG(goods_price) FROM goods_price;

聚合函数对NULL的处理
- 聚合函数在计算时会忽略NULL值。
DISTINCT和聚合函数的搭配，可以对列值进行去重处理后统计计数量
```
SELECT COUNT(DISTINCT category) FROM goods_category;
```

分组的知识点考查

GROUP创建分组
- 当存在GROUP BY时,使用SELECT查询出来的字段必须出现在GROUP BY之后，后者以聚合函数的形式出现。
使用HAVING过滤分组
- where用于对行进行过滤，对分组后的组进行过滤可以使用HAVING，并且需要将其写在GROUP BY部分之后

SELECT category
  ,AVG(purchase_quantity)
FROM purchase_info
GROUP BY category
HAVING COUNT(*)>3;

使用分组排序

SELECT colour,AVG(purchase_quantity)
FROM purchase_info
GROUP BY colour
ORDER BY AVG(purchase_quantity) DESC
LIMIT 3;

SELECT语句的执行顺序

SELECT category,AVG(purchase_quantity)
FROM purchase_info
WHERE colour IS NOT NULL
GROUP BY category
HAVING COUNT(*) >=2
ORDER BY AVG(purchase_quantity) DESC
LIMIT 1;

执行顺序为: FROM——>WHERE——>GROUP BY ——> COUNT()——>HAVING——>SELECT——>ORDER BY ——>LIMIT

子查询知识点考查

选择合适时机使用子查询
- 子查询语句可以多次使用SELECT，并且各个SELECT会有层次关系
- 子查询的运行过程是先运行子查询部分，也就是下面例子的 SELECT AVG(purchase_quantity) FROM purchase_info; 再使运行得到的结果参与子查询外部SELECT的运行。
分辨多种子查询
- 标量子查询（返回一行一列的数据）
- 关联子查询(子查询结果为多个数据)

# 标量子查询
SELECT *
FROM purchase_info
WHERE purchase_quantity>(SELECT AVG(purchase_quantity) FROM purchase_info);

# 关联子查询
SELECT *
FROM  purchase_info AS  a
WHERE purchase_quantity > (SELECT AVG(purchase_quantity)
                           FROM purchase_info AS b
                           WHERE a.category = b.category
                           GROUP BY category);

灵活使用EXISTS
- EXISTS来检查子查询是否至少会返回一行数据。带有EXISTS的子查询不返回任何记录的数据，只返回逻辑值True or False,可理解为“存在”.

SELECT *
FROM user_info  u
WHERE NOT EXISTS (
    SELECT * FROM order_info o
             WHERE o.user_id = u.user_id);

多表连接的知识点考查:

多表连接的使用场景
- 业务场景需要多张表连接起来，使用JOIN连接多张表，并且在连接时需要使用ON指出连接的条件。
多种多表连接的区别
- 1090617-20190520233002027-1421406253
- 常用的是左连接和内连接

组合查询知识点考查

组合查询的使用场景
- 当存在多个查询结果符合条件，需要将多个查询结果进行纵向拼接时，就会用到组合查询。如：全外连接
UNION和UNION ALL的区别
- 在组合查询中，包括UNION和UNION ALL 两种方法。两者的区别在于;UNION会将组合后的结果进行去重处理；而UNION ALL会将组合的结果直接拼接，并不进行去重处理。

# UNION将表纵向连接并去重
SELECT *
FROM table_a
UNION
SELECT *
FROM table_b;

# UNION将表纵向连接不去重
SELECT *
FROM table_a
UNION ALL
SELECT *
FROM table_b;

组合查询结果的排序

使用ORDER BY 对最终结果排序

SELECT *
FROM table_a
UNION
SELECT *
FROM table_b
ORDER BY a_age;

CASE WHEN知识点考查

了解CASE WHEN的几种形式

CASE <表达式>
	WHEN <值1>THEN <结果1>
	WHEN<值2>THEN <结果2>
	...
	ELSE<结果n>
END

DROP TABLE IF EXISTS personnel_info;
CREATE TABLE personnel_info(
    personnel_id VARCHAR(8),
    personnel_gender VARCHAR(8)
)
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8;
INSERT INTO
personnel_info(personnel_id, personnel_gender)
VALUE ('p001','male')
,('p002','female')
,('p003','female')
,('p004','male')
,('p005','female');

# CASE 表达式
SELECT personnel_id
        ,CASE personnel_gender
            WHEN 'female' THEN '0'
            WHEN 'male' THEN '1'
            ELSE '未知'
        END AS personnel_gender
FROM personnel_info;

CASE WHEN <条件1>THEN<结果1>
WEHN<条件2>THEN<结果2>
...
ELSE<结果n>
END

SELECT personnel_id
        ,CASE WHEN personnel_gender = 'female' THEN '0'
              when personnel_gender = 'male' THEN '1'
              ELSE '未知'
        END AS personnel_gender
FROM personnel_info;

掌握CASE WHEN的使用场景
- 当需要根据一些字段的值生成新的列或者逻辑判断较为复杂时，可能会使用 CASE WHEN

窗口函数的知识点考查

什么是窗口函数？
- 窗口函数也称为OLAP函数，OLAP的全称是Online Analysis Processing,可以对数据进行实时分析处理。
- 窗口函数的基本语法如下:
  
  <窗口函数>OVER ([PARTITION BY <用于分组的列>] ORDER BY <用于排序的列>)
RANK()函数将排序字段值相同的序号视为一样的，将后面排序字段值不相同的序号跳过相同的排名号往后排
DENSE_RANK()函数的功能与RANK()类似，DENSE_RANK()函数生成的序号是连续的，而RANK()函数生成的序号有可能不连续;
ROW_NUMBER()函数将查询出来的每一行记录生成一个序号，并依次排序且不会重复。

## RANK()、DENSE_RANK()、ROW_NUMBER()
SELECT  *
        ,RANK() OVER (PARTITION BY user_id ORDER BY order_price) AS ranking
        ,DENSE_RANK() OVER (PARTITION BY user_id ORDER BY order_price) AS DENSE_RANKING
        ,ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY order_price) AS rou_num
FROM order_content;

ROWS 2 PRECEDING当前行和前面两行作为一个窗口，AVG()函数作用在这三行上面

SELECT *
        ,AVG(order_price) OVER (ORDER BY order_date ROWS 2 PRECEDING) AS current_avg
FROM order_content;

ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING 当前行和一前一后行划为窗口，AVG()函数作用在这三行上面

SELECT *
        ,AVG(order_price) OVER (ORDER BY order_date   ROWS BETWEEN  1 PRECEDING AND 1 FOLLOWING) AS current_avg
FROM order_content;

其他窗口函数
- CUME_DIST(),返回分组内小于或等于当前rank值的行数/分组内总行数的数据
- LAG() 返回当前字段前n行的数据
- LEAD() 返回当前字段后n行的数据
- FIRST_VALUE():返回当前第一个值
- LAST_VALUE():返回当前最后一个值
- NTH_VALUE: 返回有序行的第n小的值
- NTILE():将分区中的有序数据分为n个等级,记录等级数