SQL-第二章:在单一表格中检索数据
内容来自:
1、b站mosh老师的SQL课程(第二章) 【中字】SQL进阶教程 | 史上最易懂SQL教程!10小时零基础成长SQL大师!!_哔哩哔哩_bilibili www.bilibili.com/video/BV1UE41147KC/?p=17&spm_id_from=333.1007.top_right_bar_window_history.content.click&vd_source=171e84ea90c06aa5a434d7fa2502e75c
2、《SQL必知必会(第五版)》(第二章~第六章)
3、菜菜菊花酱数据分析课程
USE sql_store; -- 在use语句之后要使用select语句;用分号终止某条语句 SELECT * / 1, 2 -- 纵向筛选列,甚至可以是常数(*表示所有列),选取所有列会给MySQL带来巨大负担,又是也会检索出名字未知的列,一般不建议这么做 FROM customers/ sql_store.customers -- 选择表(有了sql_store.customers之后,上面可以不用交USE sql_store;) WHERE customer_id < 4 -- 横向筛选行 ORDER BY first_name -- 排序(明确排序结果参照的列) -- 以上4句可以写在同一行,也可以分开写,最好把每条子句放在新建行里 -- 顺序:select--from-- where--order by,否则会出现语法错误 -- 注释格式: -- 1、单行注释 # 2、单行注释 /* 3、多行注释 */
-- 基础语法 SELECT <select_list> FROM <table_list> [WHERE <where_condition>] [GROUP BY <group_by_list>] [HAVING <having_condition>] [ORDER BY <order_by_list> ASC|DESC] [LIMIT <limit_number>]
其中,SELECT 和 FROM 两个关键词是必选项,也就是说,只要是数据查询功能,都需要使用这两个关键词,其他的5个关键词,可以根据查询的实际情况有针对性的选择和使用
注意:
- SQL语句是由简单的英语单词构成的。这些单词称为关键字,每个SQL 语句都是由一个或多个关键字构成的。
关键字(keyword)
作为 SQL组成部分的保留字。关键字不能用作表或列的名字。《SQL必知必会》附录D列出了某些经常使用的保留字。
- 多条SQL语句必须以分号(;)分隔,单条语句则可以选择不加分号;
- SQL会完全无视大小写(绝大数情况下的大小写)、多余的空格(超过一个的空格)、缩进和换行;
- 用缩进、换行等只是为了代码看着更美观结构更清晰,换行符、大间隔和tab在SQL语句中都不起作用;
- SQL语句由子句组成,一个子句通常由一个关键字加上所提供的数据组成,比如上面的select子句和order by子句。
SELECT
- 列/字段选择语句,可选择列,列间数学表达式,特定值或文本,可用AS关键字设置列别名(AS可省略),注意
DISTINCT
关键字的使用 - 主要作用就是告诉数据库,在提取数据时需要选择的字段名称。这里的字段名称可以是数据表中已经有的名称,也可以是依据已有名称衍生出来的名称。
- 需要注意的是如果要查看表中所有数据,只需要在SELECT后面直接写一个星号(*)即可;
- 如果需要提取多个字段,在SELECT关键词后面逐一写出字段名称,并且字段名称之间需要用逗号(英文状态下)隔开
实例
-- 例1
USE sql_store;
-- 如果当前处于sql_store,可以把此语句删除
SELECT
DISTINCT last_name,
-- DISTINCT:去掉last_name中重复的名字,类似于python的set函数
first_name,
points,
(points + 70) % 100 AS discount_factor/'discount factor'
-- % 取余(取模)
FROM customers
注意:
- 在选择多个列时,一定要在列名之间加上逗号,但最后一个列名后不加;
- DISTINCT:是关键字,去掉last_name中重复的名字,只返回不同的值,类似于python的set函数;
- 上面的distinct关键字作用于所有的列,上面的代码会筛选出这4列组合起来的不同结果
/* 练习 单价涨价10%作为新单价 */ SELECT name, unit_price, unit_price * 1.1 'new price' -- 若没有'new price',则列名是‘unit_price * 1.1’ FROM products
如上面这个例子所示,取别名时,AS 可省,空格后跟别名就行,可看作是SQL会将将列变量及其数学运算之后的第一个空格识别为AS
需要修改表格信息可以直接click表格中的单元格进行修改,并点击右下角的apply
from
主要作用就是告诉数据库,在提取数据时需要选择的数据源(也就是表)名称。通常来说SELECT和FROM两个关键词都是一起使用的
# 选择指定的数据库
use test;
# 查看链家二手房所有数据
select * from sec_buildings;
# 查看链家二手房的小区名称、户型、面积、单价和总价;
select fname,ftype,size,price_unit,tot_amt from sec_buildings;
# 查看链家二手房的平均单价和平均总价
select avg(price_unit),avg(tot_amt) from sec_buildings;
从返回结果可以看出,如果是提取多个字段,返回的数据是按照SELECT后面的字段顺序排列的
where
- 行搜索条件\过滤条件,实际是一行一行/一条条记录依次验证是否符合条件,进行筛选。总的来说WHERE条件优先级就是数学→比较→逻辑运算,逻辑层次和执行优先级也是按照这三个的顺序来的
- 主要作用就是限定数据查询的条件,也就是实现数据子集的提取。通常情况下,查询条件可以包含算术运算符、比较运算符、逻辑运算符、区间运算符和通配符
实例1
USE sql_store; SELECT * FROM customers WHERE points > 3000 /WHERE state != 'va' -- 'VA'/'va'一样(忽略大小写)
比较运算符 > < = >= <= !=/<> !< !>(<>也是不等于,等同!=;!<:不小于,!>:不大于) ,注意等于是一个等号而不是两个等号
也可对日期或文本进行比较运算,注意SQL里日期的标准写法及其需要用引号包裹
WHERE birth_date > '1990-01-01'
实例2
今年(2019) 的订单
USE sql_store; select * from orders where order_date > '2019-01-01' -- 有更一般的方法,不用每年改代码
实例3
查询所有两室一厅的二手房信息
select
from sec_buildings
where ftype = "2室1厅"; -- 只有一个等号
实例4
查询2014年建的浦东、静安、闵行的二手房信息
select *
from sec_buildings
where built_date = "2014年建"
and region in ("浦东","静安","闵行");
实例5
查询闵行区房价在1000万以上的二手房名称、户型、面积、朝向和楼层
select fname,ftype,size,direction,floor
from sec_buildings
where region = "闵行"
and tot_amt > 1000;
实例6
查询浦东新区面积在60-70平米的二手房名称、户型、面积和总价
select fname,ftype,size,tot_amt
from sec_buildings
where region = "浦东"
and size between 60 and 70; -- 前后都包含
实例7
查询浦东新区所有阳台朝南或者朝南北的二手房信息
select *
from sec_buildings
where region = "浦东"
and (direction="朝南" or direction="朝南北");
实例8
查询浦东新区阳台不是朝南的二手房信息
select * from sec_buildings
where region = "浦东"
and not direction="朝南";
实例9
查询小区名称中包含“新天地”字样的二手房信息
select *
from sec_buildings
where fname like "%新天地%"
AND、OR、NOT:
用逻辑运算符AND、OR、NOT对(数学和)比较运算进行组合实现多重条件筛选
执行优先级:数学→比较→逻辑
逻辑运算符的优先级问题
- 逻辑运算符优先级:not > and > or
- 括号可以改变优先级
-- 实例 USE sql_store; SELECT * FROM customers WHERE birth_date > '1990-01-01' AND points > 1000 /WHERE birth_date > '1990-01-01' OR points > 1000 AND state = 'VA'
AND优先级高于OR,但最好加括号,更清晰
WHERE birth_date > '1990-01-01' OR (points > 1000 AND state = 'VA') -- 筛选出积分大于1000并且居住在VA的顾客,不管他生日在什么时候(在处理or操作符前,优先处理and操作符)
圆括号比AND或OR操作符更高的优先级
WHERE (birth_date > '1990-01-01' OR points > 1000) AND state = 'VA' -- DBMS优先过滤圆括号内的OR条件
在OR WHERE子句的第一个条件得到满足的情况下,就不再计算第二个条件了(在第一个条件满足时,不管第二个条件是否满足,相应的行都被检索出来)
NOT
否定后面所跟的任何条件,它从不单独使用,总是与其他操作符一起使用
WHERE NOT (birth_date > '1990-01-01' OR points > 1000)-- 注意not的位置
-- 去括号等效转化为
WHERE birth_date <= '1990-01-01' AND points <= 1000
exercise1:
订单6中总价大于30的商品
USE sql_store; SELECT * FROM order_items WHERE order_id = 6 AND quantity * unit_price > 30
注意优先级:数学→比较→逻辑
IN:
用IN运算符将某一属性与多个值(一系列值)进行比较,实质是多重相等比较运算条件的简化
优点:
- IN操作符的语法更清楚、更直观;
- 在与其他AND和OR操作符组合使用IN时,求值顺序更容易管理;
- IN操作符一般比一组OR操作符执行更快;
- IN的最大优点是可以包含其他SELECT语句,能够更动态地建立WHERE语句(后面章节会提到)。
案例:选出’va’、‘fl’、'ga’三个州的顾客
USE sql_store; SELECT * FROM customers WHERE state = 'va' OR state = 'fl' OR state = 'ga'
不能
state = 'va' OR 'fl' OR 'ga'
(字符串不能同bool值进行结合)因为数学和比较运算优先于逻辑运算(相当于是state = 'va'
,加括号state = ('va' OR 'fl' OR 'ga')
也不行,逻辑运算符只能链接bool值。用 IN 操作符简化该条件
WHERE state IN ('va', 'fl', 'ga')
可加NOT
WHERE state NOT IN ('va', 'fl', 'ga')
练习:库存量刚好为49、38或72的产品
USE sql_store; select * from products where quantity_in_stock in (49, 38, 72)
Between
用于表达范围型条件
注意:
- 用AND而非括号
- 闭区间,包含两端点
- 也可用于日期,毕竟日期本质也是数值,日期也有大小(早晚),可比较运算
- 同 IN 一样,BETWEEN 本质也是一种特定的 多重比较运算条件 的简化
案例:
选出积分在1k到3k的顾客
USE sql_store; select * from customers where points >= 1000 and points <= 3000
等效简化为:
WHERE points BETWEEN 1000 AND 3000
注意两端都是包含的 不能写作BETWEEN (1000, 3000),别和IN的写法搞混
练习
选出90后的顾客
SELECT * FROM customers WHERE birth_date BETWEEN '1990-01-01' AND '2000-01-01'
like
模糊查找,查找具有某种模式的字符串的记录/行
注意:
- 过时用法(但有时还是比较好用,之后发现好像用的还是比较多的);
- 注意和正则表达式一样都是用引号包裹表示字符串。
USE sql_store;
SELECT * FROM customers
WHERE last_name like 'brush%' / 'b____y'-- (6个字符长度)
-- 引号内描述想要的字符串模式,注意SQL(几乎)任何情况都是不区分大小写的
-- '%b%':适配b的前后可以有任意字符数
两种通配符:
通配符:用来匹配值的一部分的特殊字符,从而创建比较特定数据的搜索模式
搜素模式:由字面值、通配符或两者结合构成的搜索条件
通配符搜索只能用于文本字段(字符串),非文本数据类型字段不能用通配符搜索。
文本数据类型字段:以字符串形式存储字母、数字、符号等字符,比如2是数据型字段,‘2’是文本数据类型
%
任何个数(包括0个)的字符(用的更多)_
单个字符在搜索子句中使用通配符,必须使用LIKE操作符,它他会指示DBMS:LIke后跟的搜索模式利用通配符匹配而不是简单的相等匹配进行比较
练习:
分别选择满足如下条件的顾客:
- 地址包含 ‘TRAIL’ 或 ‘AVENUE’
- 电话号码以 9 结束
USE sql_store;
-- 地址包含 'TRAIL' 或 'AVENUE'
select *
from customers
where address like '%Trail%' or
address like '%avenue%';
-- in 是精确查找(数据必须是%Trail%,这里%就是%,不代表通配符),而这里是模糊查找
-- %告诉DBMS接受Fish之后的任意字符,不管它有多少字符
select *
from customers
where Not address like '%Trail%' -- 注意Not的位置
-- 地址中既包含'TRAIL' 又包含 'AVENUE',且'TRAIL'的顺序在'AVENUE'前面
select *
from customers
where address like '%Trail%avenue%'
-- 电话号码以 9 结束
select * from customers where phone like '%9';
注意:
like 'F%y'
:只匹配以F开头,y结尾的字符串,如果字符串中y后面还有空格('fhy '
)则无法被检索,解决办法:
- 给搜索模式再加一个%号:
'F%y%'
;- 用函数去掉空格(后面会讲)
- %不会匹配NULL
REGEXP
运算符:正则表达式(regular expression),在搜索字符串方面更为强大,可搜索更复杂的模板(模糊搜索)
实例
USE sql_store;
select * from customers
where last_name like '%field%'
等效于:
where last_name regexp 'field'
正则表达式可以组合来表达更复杂的字符串模式
where last_name regexp '^mac|field$|rose' where last_name regexp '[gi]e|e[fmq]' -- 查找含ge/ie或ef/em/eq的 where last_name regexp '[a-h]e|e[c-j]'-- [a-h]:[abcdefgh]
符号 意义 ^ 开头 $ 结尾 [abc] 含abc [a-c] 含a到c | or
练习
分别选择满足如下条件的顾客:
- first names 是 ELKA 或 AMBUR
- last names 以 EY 或 ON 结束
- last names 以 MY 开头 或包含 SE
- last names 包含 BR 或 BU
select *
from customers
where first_name regexp 'elka|ambur'
/where last_name regexp 'ey$|on$' -- 也可以'ey|on$'
/where last_name regexp '^my|se'
/where last_name regexp 'b[ru]'/'br|bu'
IS NULL
找出空值,找出有某些属性缺失的记录
案例
找出电话号码缺失的顾客
USE sql_store;
select * from customers
where phone is null/is not null
注意:IS NOT NULL中 NOT 不是前置于布林值,而是更符合英语语法地放在了be动词后
练习
找出还没发货的订单
USE sql_store;
select * from orders
where shipper_id is null
ORDER BY
排序语句
-
可多列
-
可以是列间的数学表达式(对数据进行额外处理)
-
可包括任何列,包括没选择的列(MySQL特性,其它DBMS可能报错),
-
可以是之前定义好的别名列(MySQL特性,甚至可以是用一个常数设置的列别名)
-
任何一个排序依据列后面都可选加 DESC(descending:降序,默认排序顺序为升序)
-
如果想在多个列上进行降序排序,必须对每一列指定DESC关键字。(
order by first_name desc, last_name desc
) -
在字典排序顺序中,A被视为于a相同;如果需要改变那这种排序顺序,需要请求数据库管理员进行修改。
最好别用
ORDER BY 1, 2
(表示以SELECT first_name, last_name from
选中列中的第first_name、last_name列为排序依据) 这种隐性依据,因为SELECT选择的列后面万一一变就容易出错,还是显性地写出列名作为排序依据比较好注意:workbench 中扳手图标可打开表格的设计模式,查看或修改表中各列(属性),可以看到谁是主键(column Name前面是否有黄色的标志,这个代表primary key,以为这这一列中的值能唯一识别表里的记录,也就是一一对映)。省略排序语句的话会默认按主键排序
补充:
没有主键,更新或删除表中特定行就极为困难;
表中的任何列都可以作为主键,只要它满足以下条件:
- 任意两行都不具有相同的主键值;
- 每一行都必须具有一个主键值(主键列不允许空值 NULL);
- 主键列中的值不允许修改或更新;
- 主键值不能重用(如果某行从表中删除,它的主键不能赋给以后的新行);
- 在使用多列作为主键时,上述条件必须应用到作为主键的所有列,所有列值的组合必须是唯一的(但其中单个列的值可以不唯一)
实例1
USE sql_store;
select name, unit_price * 1.1 + 10 as new_price
from products
order by new_price desc, product_id
-- 这两个分别是 别名列 和 未选择列,都用到了 MySQL 特性
实例2
SELECT * FROM customers order by state, first_name
筛选出的记录首先按照state来进行排序(每个字母依次进行排序),如果值一样,再根据first_name的每个字母进行排序
实例3
select first_name, last_name FROM customers order by 1, 2
order by 1, 2
:按select的清单(first_name, last_name)进行排序,先按first_name进行排序,在按last_name进行排序
- 优点
- 不用重新输入列名
- 缺点
- 不明确给出列名有可能造成错用列名排序;
- 在对 SELECT 清单进行更改时容易错误地对数据进行排序(忘记对 ORDER BY 子句做相应的改动);
- 如果进行排序的列不在SELECT 清单中,显然不能使用这个。
练习
订单2的商品按总价降序排列:
-- 法1. 可以以总价的数学表达式为排序依据
select * from order_items
where order_id = 2
order by quantity * unit_price desc
-- 列间数学表达式
-- 法2. 或先定义总价别名,在以别名为排序依据
select *, quantity * unit_price as 'total_price' -- 在结果里加了一列
from order_items
where order_id = 2
order by total_price desc
-- 列别名
limit
- 限制返回结果的记录数量,“前N个” 或 “跳过M个后的前N个”
- 需要注意的是这个关键词后面最多可以写两个整数型的值:
- LIMIT n:表示返回查询结果的前n行
- LIMIT m,n:表示从第m+1行开始,连续返回n行的数据
实例
USE sql_store;
select * from customers
limit 3 / 300 / 6, 3
-- 或者 limit 3 offset 6
- 6, 3 表示跳过前6个,取第7~9个,6是偏移量(逗号之前对应
offset
,逗号之后对应limit
);
如:网页分页 每3条记录显示一页 第3页应该显示的记录就是 limit 6, 3limit 3 offset 6
:第一个数字是检索的行数,第二个数字是从哪儿开始;
练习
找出积分排名前三的死忠粉
USE sql_store;
select *
from customers
order by points desc
limit 3
总结
子句顺序:select +
from +
where+
order by
limit:selct-纵选列,from-确定表,where-横选行(各种条件写法和组合要清楚熟悉,筛选符合条件的记录,单条记录的内容会随着纵选列而变化), order by
& limit
-最后再进行排序和限制
单表查询练习:彩票数据核对练习
彩票游戏规则:(来自菊花酱数据分析)
彩票有10个刮奖区,每个刮奖区有一个图符和一个奖符,彩票中奖金额 = 同一个刮奖区内的(图符倍数* 奖符金额),同时每张彩票售价5元所有彩票数据存储在lottery.csv文件中,共计600000条记录。根据此数据集完成以下练习:
- 求总中奖张数及金额
- 求各不同奖符的张数及金额(奖符为5元、10元等)
- 求中奖张数与总张数占比
- 检查每个本号中有100张彩票
- 检查每本彩票中最多只有一张中奖彩票金额超过50元
- 检查每本彩票中最多只有连续7张无奖票
把数据集导入到数据库
use test;
-- 创建表结构
create table lottery(
FNo varchar(10) not null,-- 一个本号有100张票号
TNo varchar(10) not null, -- ticket number(票号)
Mark varchar(20) not null,
reward varchar(20) not null,
bingovalue int not null
);
-- 导入数据集(需要事先将lotter文件复制到Uploads的文件夹内)
load data infile "C:/Programdata/MySQL/MySQL Server 8.0/Uploads/lottery.csv"
into table lottery
fields terminated by ','
lines terminated by '\n'
ignore 1 lines;
-- 查看前10行
select * from lottery
limit 10;
-- 创建一个自增字段,用来记录彩票张数
alter table lottery add rownumber int primary key auto_increment first;
FIRST
关键字的使用通常是为了指定新增列的位置。FIRST意味着新增的列
rownumber`应该被放置在表的最前面,即它将成为表中的第一列。
求总中奖张数及金额
select count(bingovalue) as 中奖总张数, sum(bingovalue) as 中奖总金额
from lottery
where bingovalue <> 0;
求各不同奖符的张数及金额
select bingovalue as 奖符, count( bingovalue) as 张数, sum( bingovalue) as 金额 -- select后面是计数聚合函数,就不需要用distinc
from lottery
group by bingovalue
having bingovalue <> 0;
求中奖张数与总张数占比,中奖金额与总金额的占比
set @allcount = (select count(bingovalue) from lottery); -- 计算总张数
set @allsum = (select count(*)*5 from lottery);-- 计算总金额(一张彩票5元)
select count(bingovalue)/@allcount as 中奖张数占比, sum(bingovalue)/@allsum as 中奖金额占比
from lottery
where bingovalue <> 0;
用户定义的变量以
@
符号开头,这使得它们与本地变量或表的列名区分开来.用户定义的变量主要用于临时存储信息,这些信息在查询间传递或用于复杂的计算中非常有用。它们在编写存储过程或编排一系列SQL命令时特别有用,可以减少需要重复执行的查询的数量,优化数据库的性能和查询效率。
检查每个本号下有100张彩票
select FNo, count(FNo) from lottery
group by FNo
having count(FNo) <> 100
检查每个本号下最多有一张中奖票金额超过50元
select FNo, count(FNo) from lottery
where bingovalue > 50
group by FNo
having count(FNo)>1
检查每本彩票中最多连续7张无奖
select *,rownumber1-rownumber-1 as gap
from(select *,
lead(rownumber,1) over(partition by Fno) as rownumber1 from(select * from lottery where bingovalue<>0) as a)
as b
where rownumber1-rownumber>=8;
思想:反其道行之。对于某个本号来说,上一个中奖的序号(rownumber)与下一个中奖的序号之间相隔超过8,则说明中间有7张没有奖的
额占比
from lottery
where bingovalue <> 0;
> 用户定义的变量以`@`符号开头,这使得它们与本地变量或表的列名区分开来.用户定义的变量主要用于临时存储信息,这些信息在查询间传递或用于复杂的计算中非常有用。它们在编写存储过程或编排一系列SQL命令时特别有用,可以减少需要重复执行的查询的数量,优化数据库的性能和查询效率。
## 检查每个本号下有100张彩票
```sql
select FNo, count(FNo) from lottery
group by FNo
having count(FNo) <> 100
检查每个本号下最多有一张中奖票金额超过50元
select FNo, count(FNo) from lottery
where bingovalue > 50
group by FNo
having count(FNo)>1
检查每本彩票中最多连续7张无奖
select *,rownumber1-rownumber-1 as gap
from(select *,
lead(rownumber,1) over(partition by Fno) as rownumber1 from(select * from lottery where bingovalue<>0) as a)
as b
where rownumber1-rownumber>=8;
思想:反其道行之。对于某个本号来说,上一个中奖的序号(rownumber)与下一个中奖的序号之间相隔超过8,则说明中间有7张没有奖的