MYSQL学习笔记（自用）第六章

第六章

第一节. 数值函数| Numeric Functions

SELECT ROUND(5.73)
SELECT ROUND(5.73, 1)
SELECT TRUNCATE(5.7345, 1)
SELECT CEILING(5.2)
SELECT FLOOR(5.2)
SELECT ABS(-5.2)
SELECT RAND()

第二节. 字符串函数| String Functions

SELECT LENGTH('sky')
SELECT UPPER('sky')
SELECT LOWER('sky')
SELECT LTRIM('     sky')
SELECT RTRIM('sky     ')
SELECT TRIM('sky     ')
SELECT LEFT('kindergarten', 6)
SELECT SUBSTRING('kindergarten', 3, 5)
SELECT LOCATE('n', 'kindergarten')
SELECT REPLACE( 'kindergarten', 'garten', 'garden')
SELECT CONCAT('first', 'last')

USE sql_store;
SELECT CONCAT(first_name,' ', last_name) AS full_name
FROM customers

第三节. MYSQL中的日期函数| DATA Functions in MYSQL

SELECT NOW(), CURDATE(), CURTIME()
SELECT YEAR(NOW())
SELECT DAY(NOW())
SELECT DAYNAME(NOW())
SELECT EXTRACT(YEAR FROM NOW())

第三节. 作业练习

SELECT*
FROM orders
WHERE YEAR(order_date) = YEAR(NOW())

第四节. 格式化日期和时间| Formatting Dates and Times

'2019-03-11'
SELECT DATE_FORMAT(NOW(), '%M %d %Y')

第五节. 计算日期和时间| Calculating Dates and Times

SELECT DATE_SUB(NOW(), INTERVAL 1 YEAR)
SELECT DATEDIFF('2019-01-05 9:00','2019-01-01 17:00')
SELECT TIME_TO_SEC('9:00') - TIME_TO_SEC ('9:02')

第六节. IFNULL和COALESCE函数| The IFNULL and COALESCE Functions

USE sql_store;

SELECT 
		order_id,
        IFNULL(shipper_id, 'Not assigned') AS shipper
FROM orders

SELECT 
		order_id,
        COALESCE(shipper_id, 'Not assigned') AS shipper
FROM orders

SELECT 
		order_id,
        IFNULL(shipper_id, '...'),
        COALESCE(shipper_id,  comments, 'Not assigned') AS shipper
FROM orders

第六节. 作业练习

SELECT
		CONCAT(first_name,' ', last_name) AS customer,
        COALESCE(phone, 'Unknown') AS phone
FROM customers

第七节.IF函数| The IF Functions

IF(expression, first, second)

SELECT
		order_id,
        order_date,
        IF(YEAR(order_date) = YEAR(NOW()), 'ACTICE', 'ARCHIVE')
FROM orders

第七节.作业练习

SELECT	
		product_id,
        name,
        COUNT(*) AS orders
FROM products
JOIN order_items USING (product_id)
GROUP BY product_id, name

第八节. CASE运算符| The CASE Operator

SELECT
		order_id,
IF(
	YEAR(order_date) = YEAR(NOW()),
	'ACTIVE,
	'ARCHIVED') AS category
FROM orders

SELECT
		order_id,
        CASE
			WHEN YEAR(order_date) = YEAR(NOW()) THEN 'ACTIVE'
            WHEN YEAR(order_date) = YEAR(NOW()) - 1 THEN 'LAST YEAR'
            WHEN YEAR(order_date) < YEAR(NOW()) - 1 THEN 'ARCHIVED'
            ELSE 'FUTURE'
		END AS category
FROM orders

第八节.作业练习

SELECT
		CONCAT(first_name, '', last_name) AS customer,
        points,
        CASE
			WHEN points > 3000 THEN 'Gold'
            WHEN points >= 2000 THEN 'Silver'
            ELSE 'Bronze'
		END AS category
FROM customers