sql-复杂查询语句

数据来源

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，这一行就成为关联子查询，具体来说，他的运作过程是

1. 括号里的先执行，先看括号里的where，先确定p1中的一个商品类型，计算这个商品类型的avg,将p1中属于这个商品类型的与之比较，筛选适合的条目。
2. 循环所有的商品类型，取并集。

看看如下需求：选取出各商品种类中高于该商品种类的平均销售单价的商品

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

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;

2. 请根据如下结果编写 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;

3. 请根据习题一中的条件编写一条 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 

• 字符串函数

1. concat
   concat(str1,str2,str3)
   作用：拼接字符串

2. length
   length(str1)
   作用：求str1字符串的长度

3. lower
   lower(str1)
   作用：小写转换
   注：只能对英文使用

4. replace
   replace(str1,str2,str3)
   作用：取代字符串的值
   str1：操作对象
   str2：要替换的字符串
   str3：替换成什么

5. substring
   substring(str1,1,2)
   作用：截图字符串
   str1：操作对象
   1：从那个位置开始截取
   2：截图的长度

6. 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
  作用：查询，分为前向一致，中间一致，后向一致

1. 前方一致

select *
	from samplelike
	where strcol like 'ddd%'

指的是：返回前三个元素为ddd的数据行

2. 中间一致

select *
	from samplelike
	where strcol like '%ddd%'

3. 后向一致

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

1. 运算或者函数中含有 NULL 时，结果全都会变为NULL ？（判断题）

answer：普通的算数函数都是null，有些函数比如：in，not in，coulesec等函数当输入数据是null的时候，不一定返回null。

2. 对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

3. 按照销售单价（ 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;