metaX

V1

2022/08/09阅读:24主题:默认主题

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

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,4AS order_year
     ,SUBSTRING(order_time,6,2AS order_month
     ,SUBSTRING(order_time,9,2AS 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 categoryFROM 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
      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 PRECEDINGAS 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 FOLLOWINGAS current_avg
FROM order_content;
  • 其他窗口函数

    • CUME_DIST(),返回分组内小于或等于当前rank值的行数/分组内总行数的数据

    • LAG() 返回当前字段前n行的数据

    • LEAD() 返回当前字段后n行的数据

    • FIRST_VALUE():返回当前第一个值

    • LAST_VALUE():返回当前最后一个值

    • NTH_VALUE: 返回有序行的第n小的值

    • NTILE():将分区中的有序数据分为n个等级,记录等级数

分类:

后端

标签:

后端

作者介绍

metaX
V1