SQL刷题：SQL必知必会

MySQL函数参考教程

1.多字段排序用逗号隔开

对多个字段排序的时候，同样满足默认ASC

select cust_id , order_num from Orders order by cust_id , order_date desc

select cust_id , order_num from Orders order by cust_id asc , order_date desc

这俩是一样的，即先按cust_id升序(默认)，再按order_date 降序
多字段用逗号隔开，而非and

2.varchar类型加’单引号’，数字型可加可不加

• prod_price是double类型的，下面两种写法都一样，在SQL中，数字类型都可以不加单引号

    select prod_id, prod_name from Products where prod_price = '9.49'
    select prod_id, prod_name from Products where prod_price = 9.49
  

• 而另一个案例中，这俩字段都是varchar类型，所以必须加单引号

    select vend_name from Vendors where vend_country = 'USA' and vend_state = 'CA'
  

• 只要是varchar类型，无论是在哪个地方出现（例如in），都要加单引号

    select order_num, prod_id, quantity from OrderItems where quantity >= 100 and prod_id in ('BR01','BR02','BR03')
  

3.加不加`反引号`问题

4.对于字符串加’单引号’还是"双引号"都一样

对于字符串来说都一样

例如如下两种写法都是一样的

	select prod_name, prod_desc from Products where prod_desc not like "%toy%"
	select prod_name, prod_desc from Products where prod_desc not like '%toy%'

5.未出现的词、模糊匹配: not like及多种写法

题目链接
REGEXP 正则匹配参考链接

NOT LIKE及其同义写法

SELECT
  prod_name, prod_desc
FROM
  Products
WHERE
  prod_desc NOT LIKE '%toy%'
# prod_desc NOT REGEXP 'toy' --正则匹配，只要匹配到toy就算，加个NOT就是不取
# LOCATE('toy', prod_desc) = 0 
# INSTR(prod_desc, 'toy') = 0
# POSITION('toy' IN prod_desc) = 0

5.1 LOCATE函数（缺点：不走索引）

参考链接
MySQL中的LOCATE()函数用于查找字符串中子字符串的位置。它将返回字符串中子字符串首次出现的位置。如果字符串中不存在子字符串，则它将返回0。在字符串中搜索子字符串的位置时，它不会执行区分大小写的搜索。

语法：

1）locate（substr,str）

2）locate（substr,str,pos）

1.解释：返回目标字符串substr在字符串集str中第一次出现的位置

2.解释：返回目标字符串substr在字符串集str的截取范围为从第pos位到字符串末尾是否出现，以及在整个str中第一次出现的位置；其实是两个步骤，1.先判断在截取字符串中是否出现，若出现则返回在整个字符串第一次出现的位置，若未出现，则返回0

5.2 INSTR函数（缺点：不走索引）

https://www.codenong.com/cs107014294/

5.3 POSITION函数（缺点：不走索引）

https://www.codenong.com/cs107014294/

6.同时存在、同时出现：like及正则写法

6.1 like写法

SELECT
  prod_name,
  prod_desc
FROM
  Products
WHERE
  prod_desc  like '%toy%'
  and prod_desc  like  '%carrots%'

6.2 正则写法

SELECT
  prod_name,
  prod_desc
FROM
  Products
WHERE
  prod_desc REGEXP '.*toy.*'
  and prod_desc REGEXP '.*carrots.*'

或者是

select prod_name,prod_desc
from Products
where prod_desc regexp "(.*toy.*carrots.*)|(.*carrots.*toy.*)"

7.按顺序同时出现

题目链接
【问题】编写 SQL 语句，从 Products 表中检索产品名称（prod_name）和描述（prod_desc），仅返回在描述中以先后顺序同时出现 toy 和 carrots 的产品。提示：只需要用带有三个 % 符号的 LIKE 即可。

7.1 like写法按顺序用%

select prod_name, prod_desc from Products where prod_desc like '%toy%carrots%'

7.2 正则写法用.

select prod_name, prod_desc from Products where prod_desc regexp 'toy.carrots'

8.排序时数字序号的含义就是第几列

order by 2 就是按select的第二列升序排序

9.字符串截取、拼接函数：顾客登录名

题目链接

9.1 substring函数

这道题有三个关键点：

• 1.截取函数：substring()
  用法：SUBSTRING(str ,n ,m)：返回字符串str从第n个字符截取到第m个字符（区别于数组索引）；
• 2.拼接函数：concat()
  用法：select concat(A,B)
• 3.大写函数UPPER()

示例：

	select cust_id,cust_name,
	upper(concat(substring(cust_contact,1,2),substring(cust_city,1,3))) 
	as user_login
	from Customers

9.2 left函数

 select cust_id,cust_name,upper(concat(left(cust_contact,2),left(cust_city,3))) as user_login from Customers;

MySQL LEFT() 函数从指定字符串的左侧返回指定数量的字符组成的字符串。如果想从字符串的右侧提取字符，请使用 RIGHT() 函数。

9.3 mid、substr都是substring的同义词

MID(str, pos, len) 等同于 SUBSTRING(str, pos, len) 和 SUBSTR(str, pos, len) 函数。

10.注意取某个时间范围内的边界：某一个月内的订单

查2020年1月的订单
题目链接

10.1大于小于 和 between and

• 左闭右开

    select * from Orders where order_date >= '2020-01-01' and order_date < '2020-02-01' order by order_date;
  

• 而如果是用between and的话就是闭区间，这会导致右侧的范围不够灵活，所以尽可能用大于小于，比如下面这两种写法都是错的，

    --1. 2020-01-31 零时刻之后的数据取不到
    select * from Orders where order_date between '2020-01-01' and  '2020-01-31' order by order_date;
    --2. 会错误的取到 2020-02-01零时刻的数据
    select * from Orders where order_date between '2020-01-01' and  '2020-02-01' order by order_date;
  

10.2 like写法

select order_num, order_date
from Orders
where order_date like "2020-01%"
order by order_date;

10.3 year 和 month函数

select order_num, order_date
from Orders
where Year(order_date)='2020' and Month(order_date)='01'
order by order_date;

10.4 date_fomat函数

select order_num, order_date
from Orders
where date_format(order_date, '%Y%m') = '202001'
order by order_date;

11.取范围内最大值

有max(xxx) 和 order by xxx desc limit 1两种写法

12.子查询和联表都能筛选条件

题目链接

• 两张表我一般喜欢用子查询，看着比较清楚，MySQL5.7之后对子查询的优化已经做的相当好了

    select cust_id from Orders where order_num in (select order_num from OrderItems where item_price >= 10)
    select cust_id from Orders join OrderItems on OrderItems.order_num = Orders.order_num and OrderItems.item_price >= 10
  

• 三张及以上的表我喜欢用多个join

    select cust_email from Customers 
        join Orders on Orders.cust_id = Customers.cust_id
        join OrderItems on OrderItems.order_num = Orders.order_num and OrderItems.prod_id = 'BR01'
  

13.隐式内连接

显式内连接：

select cust_email from Customers 
    join Orders on Orders.cust_id = Customers.cust_id
    join OrderItems on OrderItems.order_num = Orders.order_num and OrderItems.prod_id = 'BR01'

隐式内连接：

select c.cust_email
 from OrderItems a, Orders b, Customers c
 where a.order_num = b.order_num
     and b.cust_id = c.cust_id
     and a.prod_id = 'BR01'