提示:文章写完后,目录可以自动生成,如何生成可参考右边的帮助文档
前言
提示:这里可以添加本文要记录的大概内容:
提示:以下是本篇文章正文内容,下面案例可供参考
一、检索数据?
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提供了大量能使我们快速便捷地处理数据的函数和方法。