牛客网-SQL基础知识

提示：文章写完后，目录可以自动生成，如何生成可参考右边的帮助文档

---

前言

提示：这里可以添加本文要记录的大概内容：

---

提示：以下是本篇文章正文内容，下面案例可供参考

一、检索数据？

1.SQL60 从 Customers 表中检索所有的 ID

select cust_id from Customers

关键词：select 检索单个列：select [列名] from [表名]
更严格的为剔除重复值
从表中获取所有id（剔除重复的），直接加入distinct即可。

 select distinct cust_id from Customers

SQL61 检索并列出已订购产品的清单

注意去重

select distinct prod_id from OrderItems

SQL62 检索所有列

select * from Customers

二、排序检索数据

SQL63 检索顾客名称并且排序

从上到下，Z-A，决定了是降序，所以是：desc，反之，A-Z，是升序（默认）。
按照cust_name排序，则排序语句是：order by 要排序的字段 升序（默认asc）或者降序（desc）。
格式：select 字段 from 表名 order by 要排序的字段 desc或asc 英文分号;
select cust_name from Customers order by cust_name desc;

2.SQL64 对顾客ID和日期排序

select 
    cust_id,order_num
from 
    Orders 
order by 
    cust_id, 
    order_date DESC;

先按哪个标签排，就把那个标签写在前面。两个标签之间，用逗号隔开。
升序asc
降序desc

SQL65 按照数量和价格排序

select
    quantity,item_price
from 
     OrderItems
order by 
    quantity desc,
    item_price DESC;

注意：多到少，高到低都是降序

SQL66 检查SQL语句

SELECT vend_name 
FROM Vendors 
ORDER BY vend_name DESC;

题目中代码有两处错误点：
（1）逗号。逗号作用是用来隔开列与列之间的，在select [列名] 后 不应该加逗号
（2）排序是order by是有by的

三、过滤数据

SQL67 返回固定价格的产品

select prod_id,prod_name
from Products
where prod_price=9.49;

考察点：WHERE条件筛选。

SQL68 返回更高价格的产品

select prod_id,prod_name
from Products
where prod_price>=9

select prod_id,prod_name
from Products
where prod_price=9 or prod_price>9

两种写法：
（1）返回价格为 9 美元或更高的产品，使用>=
（2）或者使用or
条件筛选 之 >=,<=,>,<,= 比较符

SQL69 返回产品并且按照价格排序

select prod_name,prod_price
from Products
where prod_price between 3 and 6
order by prod_price

用法：select [列名] from [表名] where 判断条件 order by
判断条件：between [下届] and [上届] (包含上下界）

SQL70 返回更多的产品

select distinct order_num 
from OrderItems
where quantity>=100

题目有歧义。一是直接查询大于100的订单然后去重
第二种：

SELECT
    order_num
FROM 
    OrderItems
GROUP BY 
    order_num
HAVING 
    SUM(quantity) >= 100;

重点：GROUP BY分组 + SUM 组内求和 + HAVING 过滤

过滤数据

SQL71 检索供应商名称

这个题是指按照国家和州进行筛选

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

SQL72 检索并列出已订购产品的清单

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

SQL73 返回所有价格在 3美元到 6美元之间的产品的名称和价格

select prod_name,prod_price
from Products
where prod_price between 3 and 6
order by prod_price asc;

select prod_name,prod_price
from Products
where prod_price>=3 and prod_price<=6
order by prod_price asc;

SQL74 纠错2

SELECT
    vend_name
FROM
    Vendors
WHERE
    vend_country = 'USA'
    AND vend_state = 'CA'
ORDER BY vend_name;

将order by 放到语句最后即可。
知识点，考察sql关键词的顺序。order by 的位置位于所有语句的倒数第二，倒数第一是 limit

用通配符进行过滤

SQL75 检索产品名称和描述（一）

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

分析 关键词：like 用法：[字符] like ‘%_[]字符’ %表示任何字符出现任意次数 _表示单个字符 []表示一个字符集

SQL76 检索产品名称和描述（二）

select prod_name,prod_desc
from Products
where locate ('toy',prod_desc)=0
order by prod_name;

select prod_name,prod_desc
from Products
where instr(prod_desc,'toy')=0
order by prod_name;

注：单引号双引号都可

select prod_name,prod_desc
from Products
where prod_desc not like '%toy%'
order by prod_name;

本题的思路与上题恰恰相反，因此解题的思路几乎一样，同样可以采用三种方式
1.locate函数：Locate(str,sub) > 0，表示sub字符串包含str字符串；Locate(str,sub) = 0，表示sub字符串不包含str字符串。
2.instr(filed,str)函数：返回str子字符串在filed字符串的第一次出现位置
3.not like

SQL77 检索产品名称和描述（三）

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

注意：

where prod_desc like '%toy%' and '%carrots%';不行（这种语法错误）
where prod_desc like '%toy%carrots%';不行（这种又先后顺序）

SQL78 检索产品名称和描述（四）

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

创建计算字段

SQL79 别名

select vend_id,
vend_name as vname,
vend_address as vaddress,
vend_city as vcity
from Vendors
order by vend_name asc;

SQL80 打折

 select prod_id,
 prod_price,
 0.9*prod_price as sale_price
 from Products 

SQL81 顾客登录名

关键词：substing,concat,upper
用法：
字符串的截取：substring(字符串，起始位置，截取字符数）
字符串的拼接：concat(字符串1，字符串2，字符串3,…)
字母大写：upper(字符串）

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

SQL82 返回 2020 年 1 月的所有订单的订单号和订单日期

字符串匹配（近似查找法）
(1)使用like

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

(2)切割字符串

select order_num, order_date 
from Orders
where left(order_date, 7) = '2020-01'
order by order_date

LEFT(expression, length)函数
解析： 从提供的字符串的左侧开始提取给定长度的字符，并返回。
RIGHT(expression, length)函数
解析： 从提供的字符串的右侧开始提取给定长度的字符，并返回。
（3）字符串比较

select order_num, order_date 
from Orders
where order_date >= '2020-01-01 00:00:00' and order_date <= '2020-01-31 23:59:59'
order by order_date;

（4）用正则来查找（效率不如like，能用like就用like）

select order_num, order_date 
from Orders
where order_date regexp '2020-01'
order by order_date

时间函数匹配
（5）year(字段)=XX，month(字段)=XX

select order_num, order_date 
from Orders
where year(order_date) = '2020' and month(order_date) = '1'
order by order_date

（6）利用date_format函数 （参考其中的匹配规则进行匹配）

select order_num, order_date 
from Orders
where date_format(order_date,'%Y-%m')='2020-01'    #年份Y要大写
order by order_date

汇总数据

SQL83 确定已售出产品的总数

（1）聚合函数sum()的使用
（2）列别名

select sum(quantity) as items_ordered 
from OrderItems

SQL84 确定已售出产品项 BR01 的总数

select sum(quantity) as items_ordered 
from OrderItems
where prod_id='BR01'

select sum(quantity) as items_ordered 
from OrderItems
group by prod_id
having prod_id='BR01'

SQL85 确定 Products 表中价格不超过 10 美元的最贵产品的价格

分析
关键词：max
用法：
最大值—max()
最小值—min()
平均值—avg()
总值 —sum()
总数 —count()

select max(prod_price) as max_price
from Products
where prod_price<=10

分组数据

SQL86 返回每个订单号各有多少行数

select order_num,count(order_num) as order_lines
from OrderItems
group by order_num 
order by order_lines asc 

注意：返回每个订单号（order_num）各有多少行数（order_lines）是计数，用count()
别名 as
分组 group by
排序 order by

SQL87 每个供应商成本最低的产品

select vend_id,min(prod_price) as cheapest_item
from Products
group by vend_id
order by cheapest_item asc

从最低成本到最高成本对结果进行升序排序。—order by cheapest_item asc

SQL88 返回订单数量总和不小于100的所有订单的订单号

分析
关键词：having
用法：
where—过滤过滤指定的行
having–过滤分组，与group by连用

select order_num
from OrderItems
group by order_num
having sum(quantity)>=100
order by order_num asc

SQL89 计算总和

select order_num,sum(item_price*quantity) as total_price
from OrderItems
group by order_num
having sum(item_price*quantity)>=1000
order by order_num asc

单价乘以数量求和

SQL90 纠错3

先执行GROUP BY order_num，对所有字段按照order_num 分组，
再执行HAVING items >= 3，对分组进行过滤，即查询出满足items大于等于3的记录，
再执行SELECT order_num, COUNT(order_num) AS items，
最后执行ORDER BY items, order_num;对记录进行排序，默认是升序。

SELECT
    order_num,
    COUNT(*) AS items
FROM
    OrderItems
GROUP BY
    order_num
HAVING
    COUNT(*) >= 3
ORDER BY 
    items,
    order_num;

分组时按照order_num分组。

总结

提示：这里对文章进行总结：
例如：以上就是今天要讲的内容，本文仅仅简单介绍了pandas的使用，而pandas提供了大量能使我们快速便捷地处理数据的函数和方法。