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

• SELECt基础检索的知识点考查
• 过滤数据的知识点考查
• 字段的知识点考查
• 数据处理函数的知识点考查
• 聚合函数知识点考查
• 分组的知识点考查
• 子查询知识点考查
• 多表连接的知识点考查:
• 组合查询知识点考查
• CASE WHEN知识点考查
• 窗口函数的知识点考查

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指出连接的条件。

• 多种多表连接的区别

  • 常用的是左连接和内连接

组合查询知识点考查

• 组合查询的使用场景

  • 当存在多个查询结果符合条件，需要将多个查询结果进行纵向拼接时，就会用到组合查询。如：全外连接

• 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个等级,记录等级数