MySQL学习记录5、6-编写复杂查询&数值函数

最新推荐文章于 2023-01-16 14:11:19 发布

天天的那个天啊

最新推荐文章于 2023-01-16 14:11:19 发布

阅读量186

点赞数

分类专栏： MySQL学习

本文链接：https://blog.csdn.net/a_A_A_a___/article/details/116725396

版权

MySQL学习专栏收录该内容

5 篇文章 0 订阅

订阅专栏

学习内容来自B站SQL进阶教程 | 史上最易懂SQL教程！10小时零基础成长SQL大师！！

5.编写复杂查询

1.子查询

-- 选择工资在平均工资以上的员工
SELECT *
FROM employees
WHERE salary > (
	SELECT AVG(salary)
    FROM employees
);

2.IN 运算符

-- 找到所有没有开发票的客户,客户开的发票在invoices表中
SELECT *
FROM clients
WHERE client_id NOT IN (
	SELECT DISTINCT client_id
    FROM invoices
);

3.子查询VS连接

-- 找到订购了product_id = 3的产品的顾客的部分信息
-- 子查询方式
SELECT 
	c.customer_id,
    c.first_name,
    c.last_name
FROM customers c
WHERE c.customer_id IN
(
	SELECT DISTINCT customer_id
    FROM orders
    WHERE order_id IN (
		SELECT DISTINCT order_id
        FROM order_items
		WHERE product_id = 3
    )
);
-- 使用连接的方式
SELECT 
	DISTINCT c.customer_id,
    c.first_name,
    c.last_name
FROM customers c
JOIN orders o USING(customer_id)
JOIN order_items oi USING(order_id)
WHERE oi.product_id = 3;

4.ALL 关键字

-- 找出所有大于客户id为3的所有的invoice_total的发票信息
-- 等同于找出大于客户id为3的最大invoice_total的所有发票
SELECT *
FROM invoices
WHERE invoice_total > ALL(
	SELECT invoice_total
    FROM invoices
    WHERE client_id = 3
);

5.ANY 关键字

-- 找到所有开的发票数大于大于2的客户
-- =ANY 与 IN 意义相同
SELECT *
FROM clients
WHERE client_id = ANY(
	SELECT client_id
    FROM invoices
    GROUP BY client_id
    HAVING COUNT(*) >= 2
);

6.相关子查询

-- 内部查询和外部查询产生了关系，即为相关子查询,之前学的都是非相关子查询
-- 本题获取所有客户高于该客户的发票平均值的所有发票
SELECT *
FROM invoices i
WHERE invoice_total > (
	SELECT AVG(invoice_total)
    FROM invoices
    WHERE client_id = i.client_id
);

7.EXISTS 运算符

-- 不同于IN，EXISTS并不会返回一个结果集，而是在找到一个满足条件的则返回一个
-- 找到从来没有被订购过的产品信息
SELECT *
FROM products p
WHERE NOT EXISTS (
	SELECT *
    FROM order_items oi
    WHERE oi.product_id = p.product_id
)

8.SELECT 语句中的子查询

-- 不能直接在SELECT 后面跟AVG（invoice_total） AS ....因为这样后面不能使用引用
-- 最后一个不能直接用invoice_average，因为这列并不在invoices里面，而是刚刚产生的，子查询可以调用刚刚建立的列。
（1）
SELECT 
	invoice_id,
    invoice_total,
    (
		SELECT AVG(invoice_total)
        FROM invoices
    ) AS invoice_average,
    invoice_total - (SELECT invoice_average) AS differens
FROM invoices;

-- （2）较复杂，在SELECT中使用了相关子查询，连接使用了左连接，保证输出所有客户
（2）SELECT 
	c.client_id,
    c.name,
    (
		SELECT SUM(invoice_total) FROM invoices
        WHERE i.client_id = client_id
    ) AS total_sales,
    (
		SELECT AVG(invoice_total)
        FROM invoices
    ) AS average,
    (SELECT total_sales) - (SELECT average) AS difference
FROM clients c
LEFT JOIN invoices i USING(client_id)
GROUP BY (c.client_id)

9.FROM中的子查询

-- 将8中（2）作为FROM后面的虚拟表
-- 更好的方法是将其作为视图使用
SELECT *
FROM (
	SELECT 
		c.client_id,
		c.name,
		(
			SELECT SUM(invoice_total) FROM invoices
			WHERE i.client_id = client_id
		) AS total_sales,
		(
			SELECT AVG(invoice_total)
			FROM invoices
		) AS average,
		(SELECT total_sales) - (SELECT average) AS difference
	FROM clients c
	LEFT JOIN invoices i USING(client_id)
	GROUP BY (c.client_id)
) AS sale_summary
WHERE total_sales IS NOT NULL;

6.数值函数

1.数值函数

（1）-- 指定四舍五入精度，下面为5.7，不指定则为整数
SELECT ROUND(5.73，1)
（2）-- 截断，不四舍五入
SELECT TRUNCATE(3.3366,2);
（3）-- 返回大于等于该数字的最小整数
SELECT CEILING(3.3366);
（4）-- 返回小于等于该数字的最大整数
SELECT FLOOR(3.3366);
（5）-- 计算绝对值
SELECT ABS(-3.3366)
（6）-- 生成0-1区间的随机浮点数
SELECT RAND()

2.字符串函数

（1）-- 得到字符串中的字符数
SELECT LENGTH('123')
（2）-- 字符串转换为大写
SELECT UPPER('abc')
（3）-- 字符串转换为小写
SELECT LOWER('ABC')
（4）-- LTRIM 删除字符串左边的空格
SELECT LTRIM('   1 23  ')
（5）-- RTRIM 删除字符串右边的空格
SELECT RTRIM('   1 23  ')
（6）-- TRIM 删除字符串两边的空格
SELECT TRIM('   1 23  ')
（7）-- LEFT获得字符串左边的3个字符
SELECT LEFT('12345',3)
（8）-- RIGHT获得字符串右边的3个字符
SELECT RIGHT('12345',3)
（9）-- SUBSTRING 从第3个位置截取4个字符
-- 最后一个参数如果不写，则从起始位置截取到末尾
SELECT SUBSTRING('12345678',3,4)
（10）-- LOCATE 返回一个字符或一串字符匹配的位置（不区分大小写，未找到返回0）
-- 返回3
SELECT LOCATE('34','12345678')
（11）-- REPLACE 将字符串中的‘12’替换为‘34’
SELECT REPLACE('12345678','12','34')
（12）-- CONCAT 合并字符串
①SELECT CONCAT('12345678','12','23','3')
②SELECT CONCAT(first_name,' ',last_name) AS full_name
FROM customers;

3.日期函数

（1）-- 当前的时间（日期+时间）
SELECT NOW()
（2）-- 分别返回当前的日期和时间
SELECT 
CURDATE(),
CURTIME()
（3）-- 返回当前的年份
SELECT 
YEAR(CURTIME()),
YEAR(NOW()),
YEAR(CURDATE())
（4）-- 返回当前的月份
SELECT 
MONTH(CURTIME()),
MONTH(NOW()),
MONTH(CURDATE())
（5）-- 返回当前是多少日
SELECT 
DAY(CURTIME()),
DAY(NOW()),
DAY(CURDATE())
（6）
-- 返回当前的小时 HOUR
-- 返回当前的分钟 MINUTE
-- 返回当前的秒 SECOND
-- 返回当前的字符串格式的星期数 DAYNAME
-- 返回当前的字符串格式的月份 MONTHNAME
-- 用法和前面相同
（7）-- EXTRACT 获取相应的日期格式（标准SQL提供的一部分）
-- 如获取当前年
SELECT EXTRACT(YEAR FROM NOW())

（8）-- 获取今年的订单
SELECT *
FROM orders
WHERE YEAR(order_date) = YEAR(NOW());

4.格式化日期和时间

（1）-- DATE_FORMAT格式化日期
-- %y表示两位数年份，%Y表示四位数年份
-- %m表示两位数月份，%M表示月份名称
-- %d表示两位数日，%D表示第几日如12th
-- 下面返回当前年月日，如2021年05月12日
SELECT DATE_FORMAT(NOW(),'%Y年%m月%d日')
（2）-- TIME_FORMAT格式化时间
-- %H代表小时，%i代表分钟数，%s代表秒数
--%p代表pm/am
-- 下面返回 23时35分35秒
SELECT TIME_FORMAT(NOW(),'%H时%i分%s秒')

5.计算日期和时间

（1）-- 给当前天数加一天(加一年将DAY换成YEAR即可)
  -- 传负值相当于减到一个过去的时间 或使用DATE_SUB
SELECT DATE_ADD(NOW(),INTERVAL 1 DAY)
（2）-- DATEDIFF 计算日期差
--如下面返回-1，第一个减去第二个
SELECT DATEDIFF('2021-01-15','2021-01-16')
（3）-- TIME_TO_SEC返回从0点计数的秒数
SELECT TIME_TO_SEC('09:00')

6.IFNULL 和COALESCE 函数

（1）-- 将orders表中的shipper_id为空的值替换为后面的字符串
SELECT 
	order_id,
    IFNULL(shipper_id,'Not assigned')
FROM orders
（2）-- 如果shipper_id为空，则返回comments对应的值，如果都为空，返回后面的值
SELECT 
	order_id,
    COALESCE(shipper_id,comments,'Not assigned')
FROM orders

7.IF函数

-- 如果第一个条件满足，返回第二个参数，否则返回第三个参数
SELECT 
	product_id,
    name,
    COUNT(*) AS orders,
    IF(COUNT(*) > 1,'Many times','Once') AS frequency
FROM order_items
JOIN products USING(product_id)
GROUP BY product_id

在这里插入图片描述

8.CASE运算符

-- IF语句只能返回一个判断式，而CASE可以返回多个
-- 格式为 CASE WHEN THEN ... 最后可以加个ELSE，最后END
SELECT 
	CONCAT(first_name,' ',last_name) AS customer,
    points,
    CASE 
		WHEN points < 2000 THEN 'Bronze'
        WHEN points BETWEEN 2000 AND 3000 THEN 'Silver'
        ELSE 'Gold'
	END AS category
FROM customers
ORDER BY points DESC;