MySQL复杂查询

最新推荐文章于 2024-03-27 16:37:32 发布

上烟雨心上尘

最新推荐文章于 2024-03-27 16:37:32 发布

阅读量421

点赞数 3

分类专栏： MySQL 文章标签：数据库 mysql

本文链接：https://blog.csdn.net/weixin_64632836/article/details/126905262

版权

MySQL 专栏收录该内容

5 篇文章 0 订阅

订阅专栏

MySQL复杂查询

MySQL的视图

视图是一个虚拟的表，不同于直接操作数据表，视图是依据SELECT语句来创建的（会在下面具体介绍），所以操作视图时会根据创建视图的SELECT语句生成一张虚拟表，然后在这张虚拟表上做SQL操作。

视图是一个虚拟的表，不同于直接操作数据表，视图是依据SELECT语句来创建的（会在下面具体介绍），所以操作视图时会根据创建视图的SELECT语句生成一张虚拟表，然后在这张虚拟表上做SQL操作。

视图创建

CREATE[OR REPLACE] VIEW <视图名称>(<列名1>,<列名2>,...) AS <SELECT语句>

例

CREATE 
	 VIEW view1_emp as SELECT
	pname,
	price 
FROM
	product;

同时也可以多表建立视图

CREATE VIEW view_shop_product(product_type, sale_price, shop_name)
AS
SELECT product_type, sale_price, shop_name
  FROM product,
       shop_product
 WHERE product.product_id = shop_product.product_id;

select前面有AS

视图不仅可以基于真实表，我们也可以在视图的基础上继续创建视图。

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-BYI96SH3-1663396803703)(./img/ch03/ch03.02view2.png)]

虽然在视图上继续创建视图的语法没有错误，但是我们还是应该尽量避免这种操作。这是因为对多数 DBMS 来说，多重视图会降低 SQL 的性能。

查看视图

查看有哪些视图和表

SHOW FULL TABLES

查看视图内容

SELECT * FROM <视图>

通过视图可以实现了过滤隐私信息

原表变化视图随之变化

修改视图结构

其中视图名在数据库中需要是唯一的，不能与其他视图和表重名。

ALTER VIEW 视图名 AS SELECT 语句

更新视图

某些视图可以更新，在UPDATE ,DELETE,INSERT等语句中使用它们，来更新基表的内容

对于一个视图来说，如果包含以下结构的任意一种都是不可以被更新的：

聚合函数 SUM()、MIN()、MAX()、COUNT() 等。
DISTINCT 关键字。
GROUP BY 子句。
HAVING 子句。
UNION 或 UNION ALL 运算符。
FROM 子句中包含多个表。
…

视图归根结底还是从表派生出来的，因此，如果原表可以更新，那么视图中的数据也可以更新。反之亦然，如果视图发生了改变，而原表没有进行相应更新的话，就无法保证数据的一致性了。

更新视图

UPDATE productsum
   SET sale_price = '5000'
 WHERE product_type = '办公用品';

（其实就是在原表中修改）

删除视图

删除视图的基本语法如下：

DROP VIEW <视图名1> [ , <视图名2> …]

注意：需要有相应的权限才能成功删除。

删除视图

我们删除刚才创建的productSum视图

DROP VIEW productSum;

如果我们继续操作这个视图的话就会提示当前操作的内容不存在。

重命名视图

RENAME TABLE 视图名 TO 新视图名

子查询：select嵌套

函数

聚合函数

GROUP_CONCAT()函数将组中的字符串连接成为具有各种选项的单个字符串。

GROUP_CONCAT([DISTINCT] expression
    [ORDER BY asc/desc]
    [SEPARATOR sep]) FROM 表名;

数学函数

函数名	描述	实例
ABS(x)	返回 x 的绝对值	返回 -1 的绝对值：`SELECT ABS(-1) -- 返回1`
ACOS(x)	求 x 的反余弦值（单位为弧度），x 为一个数值	`SELECT ACOS(0.25);`
ASIN(x)	求反正弦值（单位为弧度），x 为一个数值	`SELECT ASIN(0.25);`
ATAN(x)	求反正切值（单位为弧度），x 为一个数值	`SELECT ATAN(2.5);`
ATAN2(n, m)	求反正切值（单位为弧度）	`SELECT ATAN2(-0.8, 2);`
AVG(expression)	返回一个表达式的平均值，expression 是一个字段	返回 Products 表中Price 字段的平均值：`SELECT AVG(Price) AS AveragePrice FROM Products;`
CEIL(x)	返回大于或等于 x 的最小整数	`SELECT CEIL(1.5) -- 返回2`
CEILING(x)	返回大于或等于 x 的最小整数	`SELECT CEILING(1.5); -- 返回2`
COS(x)	求余弦值(参数是弧度)	`SELECT COS(2);`
COT(x)	求余切值(参数是弧度)	`SELECT COT(6);`
COUNT(expression)	返回查询的记录总数，expression 参数是一个字段或者 * 号	返回 Products 表中 products 字段总共有多少条记录：`SELECT COUNT(ProductID) AS NumberOfProducts FROM Products;`
DEGREES(x)	将弧度转换为角度	`SELECT DEGREES(3.1415926535898) -- 180`
n DIV m	整除，n 为被除数，m 为除数	计算 10 除于 5：`SELECT 10 DIV 5; -- 2`
EXP(x)	返回 e 的 x 次方	计算 e 的三次方：`SELECT EXP(3) -- 20.085536923188`
FLOOR(x)	返回小于或等于 x 的最大整数	小于或等于 1.5 的整数：`SELECT FLOOR(1.5) -- 返回1`
GREATEST(expr1, expr2, expr3, …)	返回列表中的最大值	返回以下数字列表中的最大值：`SELECT GREATEST(3, 12, 34, 8, 25); -- 34`返回以下字符串列表中的最大值：`SELECT GREATEST("Google", "Runoob", "Apple"); -- Runoob`
LEAST(expr1, expr2, expr3, …)	返回列表中的最小值	返回以下数字列表中的最小值：`SELECT LEAST(3, 12, 34, 8, 25); -- 3`返回以下字符串列表中的最小值：`SELECT LEAST("Google", "Runoob", "Apple"); -- Apple`
LN	返回数字的自然对数，以 e 为底。	返回 2 的自然对数：`SELECT LN(2); -- 0.6931471805599453`
LOG(x) 或 LOG(base, x)	返回自然对数(以 e 为底的对数)，如果带有 base 参数，则 base 为指定带底数。	`SELECT LOG(20.085536923188) -- 3 SELECT LOG(2, 4); -- 2`
LOG10(x)	返回以 10 为底的对数	`SELECT LOG10(100) -- 2`
LOG2(x)	返回以 2 为底的对数	返回以 2 为底 6 的对数：`SELECT LOG2(6); -- 2.584962500721156`
MAX(expression)	返回字段 expression 中的最大值	返回数据表 Products 中字段 Price 的最大值：`SELECT MAX(Price) AS LargestPrice FROM Products;`
MIN(expression)	返回字段 expression 中的最小值	返回数据表 Products 中字段 Price 的最小值：`SELECT MIN(Price) AS MinPrice FROM Products;`
MOD(x,y)	返回 x 除以 y 以后的余数	5 除于 2 的余数：`SELECT MOD(5,2) -- 1`
PI()	返回圆周率(3.141593）	`SELECT PI() --3.141593`
POW(x,y)	返回 x 的 y 次方	2 的 3 次方：`SELECT POW(2,3) -- 8`
POWER(x,y)	返回 x 的 y 次方	2 的 3 次方：`SELECT POWER(2,3) -- 8`
RADIANS(x)	将角度转换为弧度	180 度转换为弧度：`SELECT RADIANS(180) -- 3.1415926535898`
RAND()	返回 0 到 1 的随机数	`SELECT RAND() --0.93099315644334`
ROUND(x)	返回离 x 最近的整数	`SELECT ROUND(1.23456) --1`
SIGN(x)	返回 x 的符号，x 是负数、0、正数分别返回 -1、0 和 1	`SELECT SIGN(-10) -- (-1)`
SIN(x)	求正弦值(参数是弧度)	`SELECT SIN(RADIANS(30)) -- 0.5`
SQRT(x)	返回x的平方根	25 的平方根：`SELECT SQRT(25) -- 5`
SUM(expression)	返回指定字段的总和	计算 OrderDetails 表中字段 Quantity 的总和：`SELECT SUM(Quantity) AS TotalItemsOrdered FROM OrderDetails;`
TAN(x)	求正切值(参数是弧度)	`SELECT TAN(1.75); -- -5.52037992250933`
TRUNCATE(x,y)	返回数值 x 保留到小数点后 y 位的值（与 ROUND 最大的区别是不会进行四舍五入）	`SELECT TRUNCATE(1.23456,3) -- 1.234`

MySQL 日期函数

函数名	描述	实例
ADDDATE(d,n)	计算起始日期 d 加上 n 天的日期	`SELECT ADDDATE("2017-06-15", INTERVAL 10 DAY); ->2017-06-25`
ADDTIME(t,n)	n 是一个时间表达式，时间 t 加上时间表达式 n	加 5 秒：`SELECT ADDTIME('2011-11-11 11:11:11', 5); ->2011-11-11 11:11:16 (秒)`添加 2 小时, 10 分钟, 5 秒:`SELECT ADDTIME("2020-06-15 09:34:21", "2:10:5"); -> 2020-06-15 11:44:26`
CURDATE()	返回当前日期	`SELECT CURDATE(); -> 2018-09-19`
CURRENT_DATE()	返回当前日期	`SELECT CURRENT_DATE(); -> 2018-09-19`
CURRENT_TIME	返回当前时间	`SELECT CURRENT_TIME(); -> 19:59:02`
CURRENT_TIMESTAMP()	返回当前日期和时间	`SELECT CURRENT_TIMESTAMP() -> 2018-09-19 20:57:43`
CURTIME()	返回当前时间	`SELECT CURTIME(); -> 19:59:02`
DATE()	从日期或日期时间表达式中提取日期值	`SELECT DATE("2017-06-15"); -> 2017-06-15`

字符串函数

CONCAT – 拼接

语法：CONCAT(str1, str2, str3)

MySQL中使用 CONCAT 函数进行拼接。

LENGTH – 字符串长度

语法：LENGTH( 字符串 )

LOWER – 小写转换

LOWER 函数只能针对英文字母使用，它会将参数中的字符串全都转换为小写。该函数不适用于英文字母以外的场合，不影响原本就是小写的字符。

类似的， UPPER 函数用于大写转换。

REPLACE – 字符串的替换

语法：REPLACE( 对象字符串，替换前的字符串，替换后的字符串 )

SUBSTRING – 字符串的截取

语法：SUBSTRING （对象字符串 FROM 截取的起始位置 FOR 截取的字符数）

使用 SUBSTRING 函数可以截取出字符串中的一部分字符串。截取的起始位置从字符串最左侧开始计算，索引值起始为1。

CASE表达式

语法：

CASE WHEN <求值表达式> THEN <表达式>
     WHEN <求值表达式> THEN <表达式>
     WHEN <求值表达式> THEN <表达式>
     .
     .
     .
ELSE <表达式>
END

上述语句执行时，依次判断 when 表达式是否为真值，是则执行 THEN 后的语句，如果所有的 when 表达式均为假，则执行 ELSE 后的语句。
无论多么庞大的 CASE 表达式，最后也只会返回一个值。

应用

应用场景1：根据不同分支得到不同列值

SELECT  product_name,
        CASE WHEN product_type = '衣服' THEN CONCAT('A ： ',product_type)
             WHEN product_type = '办公用品'  THEN CONCAT('B ： ',product_type)
             WHEN product_type = '厨房用具'  THEN CONCAT('C ： ',product_type)
             ELSE NULL
        END AS abc_product_type
  FROM  product;
+--------------+------------------+
| product_name | abc_product_type |
+--------------+------------------+
| T恤          | A ： 衣服        |
| 打孔器       | B ： 办公用品    |
| 运动T恤      | A ： 衣服        |
| 菜刀         | C ： 厨房用具    |
| 高压锅       | C ： 厨房用具    |
| 叉子         | C ： 厨房用具    |
| 擦菜板       | C ： 厨房用具    |
| 圆珠笔       | B ： 办公用品    |
+--------------+------------------+
8 rows in set (0.00 sec)

ELSE 子句也可以省略不写，这时会被默认为 ELSE NULL。但为了防止有人漏读，还是希望大家能够显式地写出 ELSE 子句。
此外， CASE 表达式最后的“END”是不能省略的，请大家特别注意不要遗漏。忘记书写 END 会发生语法错误，这也是初学时最容易犯的错误。

应用场景2：实现列方向上的聚合

通常我们使用如下代码实现行的方向上不同种类的聚合（这里是 sum）

SELECT product_type,
       SUM(sale_price) AS sum_price
  FROM product
 GROUP BY product_type;  
+--------------+-----------+
| product_type | sum_price |
+--------------+-----------+
| 衣服         |      5000 |
| 办公用品      |       600 |
| 厨房用具      |     11180 |
+--------------+-----------+
3 rows in set (0.00 sec)

假如要在列的方向上展示不同种类额聚合值，该如何写呢？

sum_price_clothes | sum_price_kitchen | sum_price_office
------------------+-------------------+-----------------
             5000 |             11180 |              600

聚合函数 + CASE WHEN 表达式即可实现该效果

-- 对按照商品种类计算出的销售单价合计值进行行列转换
SELECT SUM(CASE WHEN product_type = '衣服' THEN sale_price ELSE 0 END) AS sum_price_clothes,
       SUM(CASE WHEN product_type = '厨房用具' THEN sale_price ELSE 0 END) AS sum_price_kitchen,
       SUM(CASE WHEN product_type = '办公用品' THEN sale_price ELSE 0 END) AS sum_price_office
  FROM product;
+-------------------+-------------------+------------------+
| sum_price_clothes | sum_price_kitchen | sum_price_office |
+-------------------+-------------------+------------------+
|              5000 |             11180 |              600 |
+-------------------+-------------------+------------------+
1 row in set (0.00 sec)

应用场景3：实现行转列

-- CASE WHEN 实现数字列 score 行转列
SELECT name,
       SUM(CASE WHEN subject = '语文' THEN score ELSE null END) as chinese,
       SUM(CASE WHEN subject = '数学' THEN score ELSE null END) as math,
       SUM(CASE WHEN subject = '外语' THEN score ELSE null END) as english
  FROM score
 GROUP BY name;
+------+---------+------+---------+
| name | chinese | math | english |
+------+---------+------+---------+
| 张三 |      93 |   88 |      91 |
| 李四 |      87 |   90 |      77 |
+------+---------+------+---------+
2 rows in set (0.00 sec)


 subject = '外语' THEN score ELSE null END) as english
  FROM score
 GROUP BY name;
+------+---------+------+---------+
| name | chinese | math | english |
+------+---------+------+---------+
| 张三 |      93 |   88 |      91 |
| 李四 |      87 |   90 |      77 |
+------+---------+------+---------+
2 rows in set (0.00 sec)

上烟雨心上尘

关注

3
点赞
踩
1

收藏

觉得还不错? 一键收藏
0
评论
MySQL复杂查询

视图是一个虚拟的表，不同于直接操作数据表，视图是依据SELECT语句来创建的（会在下面具体介绍），所以操作视图时会根据创建视图的SELECT语句生成一张虚拟表，然后在这张虚拟表上做SQL操作。视图是一个虚拟的表，不同于直接操作数据表，视图是依据SELECT语句来创建的（会在下面具体介绍），所以操作视图时会根据创建视图的SELECT语句生成一张虚拟表，然后在这张虚拟表上做SQL操作。
复制链接

扫一扫