复杂的查询语句
数据来源
1 视图
定义:视图与表的功能是差不多的,区别在于,视图是虚拟的,并不是真实存在的,他相当于一个窗口,通过视图,我们可以观察到真实的表,从而对其进行操作。
1.1 创建视图
创建视图的语法:
CREATE VIEW <视图名称>(<列名1>,<列名2>,...) AS <SELECT语句>
比如:创建一个名为productsum的视图(条件,统计不同类别商品的个数)
create view productsum
(product_type,cnt_count)
as
select product_type,count(*)
from product
group by product_type;
select *
from productsum2;
1.2 修改视图
修改视图的基本语法:
ALTER VIEW <视图名> AS <SELECT语句>
例如:把productSum视图改为regist_date在2009-9-11之后的数据
ALTER VIEW productSum
AS
SELECT product_type, sale_price
FROM Product
WHERE regist_date > '2009-09-11';
1.3 更新视图
更新视图的基本语法为:
update <视图名>
set <更新内容>
where <条件>;
比如:将商品类型为办公用品的商品售价全部更新为5000.
update productsum
set sale_price = 5000
where product_type = '办公用品';
注1:这里可能有点bug(Error Code: 1288. The target table productsum of the UPDATE is not updatable),是因为数据库的update模式,执行SET SQL_SAFE_UPDATES = 0即可消除错误
注2:update视图数据会改变原表的数据。因为视图的数据是从表中观察的,若是更新了观察状态,则表的状态也会更新
输出select * from product;
1.4 删除视图
删除视图的基本语句为DROP VIEW <视图名1> [ , <视图名2> …]
例如:删除productsum视图->drop view productsum;
2 子查询
子查询的定义:就是嵌套的select
例如:
SELECT stu_name
FROM (
SELECT stu_name, COUNT(*) AS stu_cnt
FROM students_info
GROUP BY stu_age) AS studentSum;
就是一个子查询
让我们看看具体的需求:查询出销售单价高于平均销售单价的商品
selcet product_name,sale_price
from product
where sale_price > (select avg(sale_price) from product);
2.1 关联子查询
这个逻辑有点奇怪,看一个例子:
SELECT product_type, product_name, sale_price
FROM product AS p1
WHERE sale_price > (SELECT AVG(sale_price)
FROM product AS p2
WHERE p1.product_type = p2.product_type
GROUP BY product_type);
这里语句的意思是:选出各个商品类别中售价大于商品类别平均售价的商品。
其中最重要的一行就是在WHERE p1.product_type = p2.product_type
,这一行就成为关联子查询,具体来说,他的运作过程是
- 括号里的先执行,先看括号里的where,先确定p1中的一个商品类型,计算这个商品类型的avg,将p1中属于这个商品类型的与之比较,筛选适合的条目。
- 循环所有的商品类型,取并集。
看看如下需求:选取出各商品种类中高于该商品种类的平均销售单价的商品
select product_name,product_type,sale_price
from product as p1
where sale_price > (select avg(sale_price) from product as p2
where p1.product_type = p2.product_type
group by product_type);
练习题1
- 创建出满足下述三个条件的视图(视图名称为 ViewPractice5_1)。使用 product(商品)表作为参照表,假设表中包含初始状态的 8 行数据。
- 条件 1:销售单价大于等于 1000 日元。
- 条件 2:登记日期是 2009 年 9 月 20 日。
- 条件 3:包含商品名称、销售单价和登记日期三列。
create view Viewpractice5_1 (product_name,sale_price,regist_date)
As
select product_name,sale_price,regist_date
from product
where sale_price >= 1000
and regist_date = '2009-09-20';
select * from viewpractice5_1;
- 请根据如下结果编写 SELECT 语句,其中 sale_price_all 列为全部商品的平均销售单价。
product_id | product_name | product_type | sale_price | sale_price_all
------------+-------------+--------------+------------+---------------------
0001 | T恤衫 | 衣服 | 1000 | 2097.5000000000000000
0002 | 打孔器 | 办公用品 | 500 | 2097.5000000000000000
0003 | 运动T恤 | 衣服 | 4000 | 2097.5000000000000000
0004 | 菜刀 | 厨房用具 | 3000 | 2097.5000000000000000
0005 | 高压锅 | 厨房用具 | 6800 | 2097.5000000000000000
0006 | 叉子 | 厨房用具 | 500 | 2097.5000000000000000
0007 | 擦菜板 | 厨房用具 | 880 | 2097.5000000000000000
0008 | 圆珠笔 | 办公用品 | 100 | 2097.5000000000000000
select product_id,product_type,sale_price,
(select avg(sale_price)
from product) as sale_price_all
from product;
- 请根据习题一中的条件编写一条 SQL 语句,创建一幅包含如下数据的视图(名称为AvgPriceByType)
product_id | product_name | product_type | sale_price | avg_sale_price
------------+-------------+--------------+------------+---------------------
0001 | T恤衫 | 衣服 | 1000 |2500.0000000000000000
0002 | 打孔器 | 办公用品 | 500 | 300.0000000000000000
0003 | 运动T恤 | 衣服 | 4000 |2500.0000000000000000
0004 | 菜刀 | 厨房用具 | 3000 |2795.0000000000000000
0005 | 高压锅 | 厨房用具 | 6800 |2795.0000000000000000
0006 | 叉子 | 厨房用具 | 500 |2795.0000000000000000
0007 | 擦菜板 | 厨房用具 | 880 |2795.0000000000000000
0008 | 圆珠笔 | 办公用品 | 100 | 300.0000000000000000
create view avgpricetype (product_id,
product_name,
product_type,
sale_price,
avg_sale_price) as
select product_id,product_type,product_name,sale_price,
(
select avg(sale_price)
from product as p2
group by product_type
having p1.product_type = p2.product_type
) as avg_sale_price
from product as p1;
/*
另外也可以用where
create view avgpricetype (product_id,
product_name,
product_type,
sale_price,
avg_sale_price) as
select product_id,product_type,product_name,sale_price,
(
select avg(sale_price)
from product as p2
where p1.product_type = p2.product_type
group by product_type
) as avg_sale_price
from product as p1;
*/
select * from avgpricetype;
3 各种不同的sql函数
3.1
使用的数据:
-- DDL :创建表
USE shop;
DROP TABLE IF EXISTS samplemath;
CREATE TABLE samplemath
(m float(10,3),
n INT,
p INT);
-- DML :插入数据
START TRANSACTION; -- 开始事务
INSERT INTO samplemath(m, n, p) VALUES (500, 0, NULL);
INSERT INTO samplemath(m, n, p) VALUES (-180, 0, NULL);
INSERT INTO samplemath(m, n, p) VALUES (NULL, NULL, NULL);
INSERT INTO samplemath(m, n, p) VALUES (NULL, 7, 3);
INSERT INTO samplemath(m, n, p) VALUES (NULL, 5, 2);
INSERT INTO samplemath(m, n, p) VALUES (NULL, 4, NULL);
INSERT INTO samplemath(m, n, p) VALUES (8, NULL, 3);
INSERT INTO samplemath(m, n, p) VALUES (2.27, 1, NULL);
INSERT INTO samplemath(m, n, p) VALUES (5.555,2, NULL);
INSERT INTO samplemath(m, n, p) VALUES (NULL, 1, NULL);
INSERT INTO samplemath(m, n, p) VALUES (8.76, NULL, NULL);
COMMIT; -- 提交事务
-- 查询表内容
SELECT * FROM samplemath;
+----------+------+------+
| m | n | p |
+----------+------+------+
| 500.000 | 0 | NULL |
| -180.000 | 0 | NULL |
| NULL | NULL | NULL |
| NULL | 7 | 3 |
| NULL | 5 | 2 |
| NULL | 4 | NULL |
| 8.000 | NULL | 3 |
| 2.270 | 1 | NULL |
| 5.555 | 2 | NULL |
| NULL | 1 | NULL |
| 8.760 | NULL | NULL |
+----------+------+------+
11 rows in set (0.00 sec)
- 算数函数(+ - * /)
例如 - 绝对值(abs)
- mod(取余数)
语法为:mod(a,b)-a是被除数,b是除数
- round(四舍五入)
语法为ROUND( 对象数值,保留小数的位数 )
用法:
select m,
n,p,
abs(n) as abs_n,
mod(m,p) as mod_mp,
round(m,1)
from samplemath
- 字符串函数
-
concat
concat(str1,str2,str3)
作用:拼接字符串 -
length
length(str1)
作用:求str1字符串的长度 -
lower
lower(str1)
作用:小写转换
注:只能对英文使用 -
replace
replace(str1,str2,str3)
作用:取代字符串的值
str1:操作对象
str2:要替换的字符串
str3:替换成什么 -
substring
substring(str1,1,2)
作用:截图字符串
str1:操作对象
1:从那个位置开始截取
2:截图的长度 -
substring_index
substring_index(str1,分隔符,n)
作用:从字符串str1中以分隔符分割后,取第n个(包括第n个)以后的字符串
例如:
SELECT SUBSTRING_INDEX('www.mysql.com', '.', 2);
输出为:mysql.com
3.2 日期函数
- current_date
截图现在的时间
select current_date;
Result:
current_date
‘2021-07-12’
- current_time
当前时间
select current_time;
Result:
current_time
‘15:03:58’
- current_timestamp
select current_timestamp;
Result:
‘2021-07-12 15:04:58’
- extract
作用:截图时间元素
SELECT CURRENT_TIMESTAMP as now,
EXTRACT(YEAR FROM CURRENT_TIMESTAMP) AS year,
EXTRACT(MONTH FROM CURRENT_TIMESTAMP) AS month,
EXTRACT(DAY FROM CURRENT_TIMESTAMP) AS day;
Result:
+---------------------+------+-------+------+
| now | year | month | day |
+---------------------+------+-------+------+
| 2021-07-12 15:06:45 | 2021 | 7 | 12 |
+---------------------+------+-------+------+
3.3 转换函数
- cast
作用:转换数据类型
语法:
CASE WHEN <求值表达式> THEN <表达式>
WHEN <求值表达式> THEN <表达式>
WHEN <求值表达式> THEN <表达式>
.
.
.
ELSE <表达式>
END
若要实现A :衣服 B :办公用品 C :厨房用具 ,即商品类别如果为衣服,则变为A:衣服
selcet product_name
cast when (product_type='衣服') then concat('A:',product_type)
cast when (product_type='办公用品') then concat('B:',product_type)
cast when (product_type='厨房用具') then concat('B:',product_type)
else null
end
from product
结果:
+--------------+------------------+
| product_name | abc_product_type |
+--------------+------------------+
| T恤 | A : 衣服 |
| 打孔器 | B : 办公用品 |
| 运动T恤 | A : 衣服 |
| 菜刀 | C : 厨房用具 |
| 高压锅 | C : 厨房用具 |
| 叉子 | C : 厨房用具 |
| 擦菜板 | C : 厨房用具 |
| 圆珠笔 | B : 办公用品 |
+--------------+------------------+
3.4 like、between、in、的用法
使用数据:
CREATE TABLE samplelike
( strcol VARCHAR(6) NOT NULL,
PRIMARY KEY (strcol)
samplelike);
-- DML :插入数据
INSERT INTO samplelike (strcol) VALUES ('abcddd');
INSERT INTO samplelike (strcol) VALUES ('dddabc');
INSERT INTO samplelike (strcol) VALUES ('abdddc');
INSERT INTO samplelike (strcol) VALUES ('abcdd');
INSERT INTO samplelike (strcol) VALUES ('ddabc');
INSERT INTO samplelike (strcol) VALUES ('abddc');
- like
作用:查询,分为前向一致,中间一致,后向一致
- 前方一致
select *
from samplelike
where strcol like 'ddd%'
指的是:返回前三个元素为ddd的数据行
- 中间一致
select *
from samplelike
where strcol like '%ddd%'
- 后向一致
select *
from samplelike
where strcol like '%ddd'
注:表示任何匹配一个字符 如:
like 'abc_’
- between
作用筛选一个范围之类的数值,闭区间
SELECT product_name, sale_price
FROM product
WHERE sale_price BETWEEN 100 AND 1000;
- in
where <列名> in (data1,data2,data3,...)
作用:过滤数据
例如:选出sale_price为320,500,1000的商品
select *
from product
where sale_price in (320,500,1000)
或者:
select *
from product
where sale_price=320
and sale_price=500
and sale_price=1000;
结果都一样:
+--------------+----------------+
| product_name | purchase_price |
+--------------+----------------+
| T恤 | 500 |
| 打孔器 | 320 |
| 高压锅 | 5000 |
+--------------+----------------+
注:但是一般都是使用’in’的用法,因为使用and或者or后面只能跟常数,而in的用法后面还可以跟子查询(即可以为变量)
3.5 练习题2
- 运算或者函数中含有 NULL 时,结果全都会变为NULL ?(判断题)
answer:普通的算数函数都是null,有些函数比如:in,not in,coulesec等函数当输入数据是null的时候,不一定返回null。
- 对product(商品)表执行如下 2 条 SELECT 语句,能够得到什么样的结果呢?
SELECT product_name, purchase_price
FROM product
WHERE purchase_price NOT IN (500, 2800, 5000);
结果:purchase_price不属于500,2800,5000的数据条目
SELECT product_name, purchase_price
FROM product
WHERE purchase_price NOT IN (500, 2800, 5000, NULL);
结果:空
因为任何值都不能用not in判断属不属于null
-
按照销售单价( sale_price)对练习 3.6 中的 product(商品)表中的商品进行如下分类。
- 低档商品:销售单价在1000日元以下(T恤衫、办公用品、叉子、擦菜板、 圆珠笔)
- 中档商品:销售单价在1001日元以上3000日元以下(菜刀)
- 高档商品:销售单价在3001日元以上(运动T恤、高压锅)
请编写出统计上述商品种类中所包含的商品数量的 SELECT 语句,结果如下所示。
low_price | mid_price | high_price
----------+-----------+------------
x1 | x2 | x3
select
count(case when sale_price <= 1000 then product_id else null end) as low_price,
count(case when sale_price between 1000 and 3000 then product_id else null end) as mid_price,
count(case when sale_price>=3001 then product_id else null end) as high_price,
from product;