[大数据]SQL随记（待更新）

-- The SELECT Statement
USE sql_store;

SELECT *
FROM customers
-- WHERE customer_id = 1
ORDER BY first_name 


-- LIKE Exercise
SELECT customer_id,first_name,phone,address
FROM customers
-- WHERE address LIKE '%trail%' OR	address LIKE '%avenue%'
WHERE phone LIKE '%9'

-- The REGEXP Operator
SELECT *
FROM customers
-- WHERE first_name REGEXP 'ELKA|AMBUR'
-- WHERE last_name REGEXP 'EY$|ON$'
-- WHERE last_name REGEXP '^MY|SE'
WHERE last_name REGEXP 'B[RU]'


-- IS NULL运算符
SELECT *
FROM orders
WHERE shipped_date IS NULL


-- ORDER BY 子句
SELECT *,quantity * unit_price AS total_price
FROM order_items
WHERE order_id = 2
ORDER BY  total_price DESC

-- LIMIT子句 
SELECT *
FROM customers
ORDER BY points DESC
LIMIT 3

-- Inner Joins内连接
SELECT order_id,o.product_id,p.name,quantity,o.unit_price
FROM order_items o
JOIN products p
     ON o.product_id = p.product_id

-- Joining Across Databases跨数据库连接 
USE sql_inventory;

SELECT *
FROM sql_store.order_items oi
JOIN products p
     ON oi.product_id = p.product_id

-- Self Joins自连接``
USE sql_hr;

SELECT 
     e.employee_id,
     e.first_name,
     m.first_name AS managerv0
FROM employees e
JOIN employees m
    ON e.reports_to = m.employee_id

-- Joining Multiple Tables多表连接
USE sql_store;
SELECT 
    o.order_id,
    o.order_date,
    c.first_name,
    c.last_name,
    os.name AS status
FROM orders o
JOIN customers c
	ON o.customer_id = c.customer_id
JOIN order_statuses os
    ON o.status = os.order_status_id

-- Exercise
USE sql_invoicing;
SELECT 
     p.date,
     p.invoice_id,
     p.amount,
     c.name,
     pm.name
FROM payments p
JOIN clients c
   ON p.client_id = c.client_id
JOIN payment_methods pm
   ON p.payment_method = pm.payment_method_id

-- Compound Join Conditions复合连接条件 
SELECT *
FROM order_items oi
JOIN order_item_notes oin
   ON oi.order_id = oin.order_Id
   AND oi.product_id = oin.product_id

-- Implicit Join Syntas隐式连接语法 
-- 显式
SELECT *
FROM orders o
JOIN customers c
   ON o.customer_id = c.customer_id

-- 隐式
SELECT *
FROM orders o,customers c
WHERE o.customer_id = c.customer_id


-- Outer Joins外连接
SELECT 
    c.customer_id,
    c.first_name,
    o.order_id
FROM customers c
LEFT JOIN orders o
    ON c.customer_id = o.customer_id
ORDER BY c.customer_id

-- Exercise
SELECT 
    p.product_id,
    p.name,
    oi.quantity
FROM products p
LEFT JOIN order_items oi
   ON p.product_id = oi.product_id

-- Outer Joins Between Multiple Tables多表外连接 
SELECT 
    c.customer_id,
    c.first_name,
    o.order_id,
    sh.name AS shipper
FROM customers c
LEFT JOIN orders o
    ON c.customer_id = o.customer_id
LEFT JOIN shippers sh
    ON o.shipper_id = sh.shipper_id
ORDER BY c.customer_id

-- Exercise
SELECT 
    o.order_date,
    o.order_id,
    c.first_name,
    s.name AS shipper,
    os.name AS status
FROM orders o
LEFT JOIN customers c
   ON o.customer_id = c.customer_id
LEFT JOIN shippers s
   ON o.shipper_id = s.shipper_id
LEFT JOIN order_statuses os
   ON o.status = os.order_status_id
ORDER BY status
 
-- Self Outer Joins 自外连接
USE sql_hr;

SELECT
   e.employee_id,
   e.first_name,
   m.first_name AS manager
FROM employees e
LEFT JOIN employees m
   ON e.reports_to = m.employee_id
   
-- The USING Clause USING子句 
SELECT 
    o.order_id,
    c.first_name,
    sh.name AS shipper
FROM orders o
JOIN customers c
    USING (customer_id)
LEFT JOIN shippers sh
    USING (shipper_id)
    
SELECT *
FROM order_items oi
JOIN order_item_notes oin
    USING (order_id,product_id)


-- Excercise
USE sql_invoicing;

SELECT 
    p.date,
    c.name AS client,
    p.amount,
    pm.name
FROM payments p
JOIN clients c
   USING (client_id)
JOIN payment_methods pm
   ON p.payment_method = pm.payment_method_id

-- Natural Joins自然连接 
SELECT 
    o.order_id,
    c.first_name
FROM orders o
NATURAL JOIN customers c
-- 数据库引擎自己连接 

-- Cross Joins交叉连接 有点像笛卡尔积 排列组合
-- 显式
SELECT 
    c.first_name AS customer,
    p.name AS product
FROM customers c
CROSS JOIN products p
ORDER BY c.first_name
-- 隐式
SELECT 
    c.first_name AS customer,
    p.name AS product
FROM customers c,products p
ORDER BY c.first_name

-- Exercise
-- 隐式
SELECT 
	p.name,
    s.name AS shipper
FROM products p,shippers s
ORDER BY p.name
-- 显式
SELECT 
	p.name,
    s.name AS shipper
FROM products p
CROSS JOIN shippers s
ORDER BY p.name

-- Unions联合 
-- 同一张表
SELECT 
    order_id,
    order_date,
    'Active' AS status
FROM orders
WHERE order_date >= '2019-01-01'
UNION
SELECT 
    order_id,
    order_date,
    'Archived' AS status
FROM orders
WHERE order_date < '2019-01-01';
-- 不同的表
SELECT first_name
FROM customers
UNION 
SELECT name
FROM shippers

-- Exercise
SELECT 
    customer_id,
    first_name,
    points,
    'Bronze' AS type
FROM customers 
WHERE points < 2000
UNION
SELECT 
    customer_id,
    first_name,
    points,
    'Silver' AS type
FROM customers 
WHERE points BETWEEN 2000 and 3000
UNION
SELECT 
    customer_id,
    first_name,
    points,
    'Gold' AS type
FROM customers 
WHERE points > 3000
ORDER BY first_name

-- Column Attributes 列属性 
-- Inserting a Single Row 插入单行 
INSERT INTO customers (
	first_name,
    last_name,
    birth_date,
    address,
    city,
    state)
VALUES (
    'John',
    'Smith',
    '1990-01-01',
    'address',
    'city',
    'CA')

-- Inserting Multiple Rows 插入多行 
INSERT INTO shippers(name)
VALUES ('shipper1'),
	   ('shipper2'),
       ('shipper3')

-- Exersice
INSERT INTO products(name,quantity_in_stock,unit_price)
VALUES ('name1',1,1.1),
	   ('name2',2,2.2),
       ('name3',3,3.3)

-- Inserting Hierarchical Rows插入分层行 
INSERT INTO orders(customer_id,order_date,status)
VALUES (1,'2019-01-02',1);
INSERT INTO order_items
VALUES
	(LAST_INSERT_ID(),1,1,2.95),
    (LAST_INSERT_ID(),2,1,3.95) 

-- Creating a Copy of a Table 创建表复制 
CREATE TABLE order_archived AS
SELECT * FROM orders3

INSERT INTO order_archived 
SELECT *
FROM orders
WHERE order_date < '2019-01-01'


-- Exercise
CREATE TABLE invoices_archived AS
SELECT 
	i.invoice_id,
    i.number,
    i.client_id,
    c.name AS client,
    i.invoice_total,
    i.payment_total,
    i.invoice_date,
    i.due_date,
    i.payment_date
FROM invoices i
JOIN clients C
	USING (client_id)
WHERE payment_date IS NOT NULL

-- Updating a Single Row更新单行

UPDATE invoices
SET payment_total = 10,payment_date = '2019-03-01'
WHERE invoice_id = 1 

UPDATE invoices
SET payment_total = DEFAULT,payment_date = NULL
WHERE invoice_id = 1 

UPDATE invoices
SET 
	payment_total = invoice_total * 0.5,
    payment_date = due_date
WHERE invoice_id = 3


-- Updating Multiple Rows更新多行 
UPDATE invoices
SET 
	payment_total = invoice_total * 0.5,
    payment_date = due_date
WHERE invoice_id IN (3,4)


-- Exercise
UPDATE customers
SET 
	points = points + 50
WHERE birth_date < '1990-01-01'


-- Using Subqueries in Updates在Updates中用子查询 
UPDATE invoices
SET 
	payment_total = invoice_total * 0.5,
    payment_date = due_date
WHERE invoice_id = 
			(SELECT client_id
			FROM clients
			WHERE name = 'Myworks')

UPDATE invoices
SET 
	payment_total = invoice_total * 0.5,
    payment_date = due_date
WHERE invoice_id IN
			(SELECT client_id
			 FROM clients
			 WHERE State IN ('CA','NY'))

UPDATE invoices
SET 
	payment_total = invoice_total * 0.5,
    payment_date = due_date
WHERE payment_date IS NULL


-- Exercise
UPDATE orders
SET comments = 'Gold customer'
WHERE customer_id IN
			(SELECT customer_id
			FROM customers
			WHERE points > 3000)

-- Deleting Rows删除行 
DELETE FROM invoices
WHERE client_id = (
	SELECT client_id
    FROM clients
    WHERE name = 'Myworks'
)

-- Restoring the Databases恢复数据库

-- Aggregate Functions 聚合函数
-- MAX()、MIN()、AVG()、SUM()、COUNT()
SELECT 
	MAX(invoice_total) AS highter,
    MIN(invoice_total) AS lowest,
    AVG(invoice_total) AS average,
    SUM(invoice_total*1.1) AS total,
    COUNT(DISTINCT client_id) AS total_records
FROM invoices
WHERE invoice_date > '2019-07-01'

-- Exercise 
SELECT 
	'First half of 2019' AS date_range,
    SUM(invoice_total) AS total_sales,
    SUM(payment_total) AS total_payments,
    SUM(invoice_total - payment_total) AS what_we_expect
FROM invoices
WHERE invoice_date 
	BETWEEN '2019-01-01' AND '2019-06-30'
UNION
SELECT 
	'Second half of 2019' AS date_range,
    SUM(invoice_total) AS total_sales,
    SUM(payment_total) AS total_payments,
    SUM(invoice_total - payment_total) AS what_we_expect
FROM invoices
WHERE invoice_date BETWEEN '2019-07-01' AND '2019-12-31'
UNION
SELECT 
	'Total' AS date_range,
    SUM(invoice_total) AS total_sales,
    SUM(payment_total) AS total_payments,
    SUM(invoice_total - payment_total) AS what_we_expect
FROM invoices
WHERE invoice_date BETWEEN '2019-01-01' AND '2019-12-31'

-- GROUP BY 子句
SELECT 
	state,
    city,
	SUM(invoice_total) AS total_sales
FROM invoices i 
JOIN clients USING (client_id)
-- WHERE invoice_date >= '2019-07-01'
GROUP BY state,city
-- ORDER BY total_sales DESC

-- Exeercise
SELECT 
	date,
    pm.name AS payment_method,
    SUM(amount) AS total_payments
FROM payments p
JOIN payment_methods pm
	ON p.payment_method = pm.payment_method_id
GROUP BY date,payment_method
ORDER BY date

-- The HAVING Clause HAVING子句 
SELECT 
	client_id,
    SUM(invoice_total) AS total_sales,
    COUNT(*) AS number_of_invoices
FROM invoices
GROUP BY client_id
HAVING total_sales > 500 AND number_of_invoices > 5

-- Exercise
USE sql_store;

SELECT 
	c.customer_id,
	c.first_name,
	c.last_name,
	SUM(oi.quantity*oi.unit_price) AS total_sales
FROM customers c
JOIN orders o USING(customer_id)
JOIN order_items oi USING(order_id)
WHERE c.state = 'VA'
GROUP BY 
	c.customer_id,
	c.first_name,
	c.last_name
HAVING total_sales > 100

-- The ROLLUP Operator ROLLUP 运算符
SELECT
	state,
    city,
    SUM(invoice_total) AS total_sales
FROM invoices i 
JOIN clients c USING (client_id)
GROUP BY state,city WITH ROLLUP

-- Exercise
SELECT 
	pm.name AS payment_method,
    SUM(amount) AS total
FROM payments p 
JOIN payment_methods pm 
	ON p.payment_method = pm.payment_method_id
GROUP BY pm.name WITH ROLLUP

-- Writing Complex Queries编写复杂查询 
-- Subqueries 子查询 
SELECT *
FROM products
WHERE unit_price > (
	SELECT unit_price
    FROM products
    WHERE product_id = 3)

-- Exercise
USE sql_hr;

SELECT *
FROM employees
WHERE salary > (
	SELECT AVG(salary)
    FROM employees
)

-- The IN Operator IN运算符 
SELECT *
FROM products
WHERE product_id NOT IN(
	SELECT DISTINCT product_id
    FROM order_items
)

-- Exercise
USE invoicing;

SELECT *
FROM clients
WHERE client_id NOT IN(
	SELECT DISTINCT client_id
	FROM invoices
)

-- Subqueries vs Joins子查询vs连接
SELECT *
FROM clients
LEFT JOIN invoices USING (client_id)
WHERE invoice_id IS NULL

-- Exercise
SELECT 
	customer_id,
    first_name,
    last_name
FROM customers
WHERE customer_id IN (
	SELECT customer_id
    FROM orders
    WHERE order_id IN (
		SELECT order_id
        FROM order_items
        WHERE product_id = 3
    )
)

SELECT DISTINCT
	c.customer_id,
    c.first_name,
    c.last_name
FROM customers c
LEFT JOIN orders o USING (customer_id)
LEFT JOIN order_items oi USING (order_id)
WHERE oi.product_id = 3

-- The ALL Keyword ALL关键字
SELECT *
FROM invoices
WHERE invoice_total > (
	SELECT MAX(invoice_total)
    FROM invoices
    WHERE client_id = 3
)

SELECT *
FROM invoices
WHERE invoice_total > ALL(
	SELECT invoice_total
    FROM invoices
    WHERE client_id = 3
)

-- The ANY Keyword ANY关键字
SELECT *
FROM clients
WHERE client_id = ANY (
	SELECT client_id
	FROM invoices
	GROUP BY client_id
	HAVING COUNT(*) >= 2
)

SELECT *
FROM clients
WHERE client_id IN (
	SELECT client_id
	FROM invoices
	GROUP BY client_id
	HAVING COUNT(*) >= 2
)

-- Correlated Subqueries 相关子查询 
USE sql_hr;

SELECT *
FROM employees e
WHERE salary > (
	SELECT AVG(salary)
    FROM employees
    WHERE office_id = e.office_id
)

-- Exercise
USE invoicing;

SELECT *
FROM invoices i 
WHERE invoice_total > (
	SELECT AVG(invoice_total)
    FROM invoices
    WHERE client_id = i.client_id
)

-- The EXISTS Operator EXISTS运算符
-- 1.IN 
SELECT *
FROM clients
WHERE client_id IN (
	SELECT DISTINCT client_id
    FROM invoices
);
-- 2.连接 
SELECT 
	DISTINCT client_id,
	name,
    address,
    city,
    state,
    phone
FROM clients
JOIN invoices USING (client_id);
-- 3.EXISTS
SELECT *
FROM clients c
WHERE EXISTS (
	SELECT client_id
    FROM invoices
    WHERE client_id = c.client_id
)

-- Exercise
USE sql_store;

SELECT *
FROM products p
WHERE NOT EXISTS(
	SELECT product_id
    FROM order_items
    WHERE product_id = p.product_id
    ) 

-- Subqueries in the SELECT Clause SELECT子句中的子查询 
SELECT 
	invoice_id,
    invoice_total,
    (SELECT AVG(invoice_total)
		FROM invoices) AS invoice_average,
	invoice_total - (SELECT invoice_average) AS difference
FROM invoices

-- Exercise
SELECT 
	client_id,
    name,
    (SELECT SUM(invoice_total)
		FROM invoices
        WHERE client_id = c.client_id) AS total_sales,
	(SELECT AVG(invoice_total)
		FROM invoices) AS average,
	(SELECT total_sales - average) AS diffrence
FROM clients c

-- Subqueries in the FROM Clause FROM子句中的子查询 
SELECT *
FROM (
	SELECT 
		client_id,
		name,
		(SELECT SUM(invoice_total)
			FROM invoices
			WHERE client_id = c.client_id) AS total_sales,
		(SELECT AVG(invoice_total)
			FROM invoices) AS average,
		(SELECT total_sales - average) AS diffrence
	FROM clients c
) AS sales_summary
WHERE total_sales IS NOT NULL

-- Numeric Functions数值函数
SELECT ROUND(5.7345,2) -- 四舍五入
SELECT TRUNCATE(5.7345,3) -- 截断函数
SELECT CEILING(5.2) -- 返回大于或等于这个数字的最小整数 —— 向上取整
SELECT FLOOR(5.2) -- 返回小于或等于这个数字的最小整数 —— 向下取整
SELECT ABS(-5.2) -- 取绝对值
SELECT RAND() -- 介于0~1之间的随机数 

-- String Functions 字符串函数 
SELECT LENGTH('sky') -- 返回字符串中的字符数 
SELECT UPPER('sky') -- 大写的字符串
SELECT LOWER('Sky') -- 小写的字符串
SELECT LTRIM('     sky') -- 移除字符串左边的空格 
SELECT RTRIM('sky     ') -- 移除字符串右边的空格 
SELECT TRIM('     sky   ') -- 移除字符串所有的空格 
SELECT LEFT('Kindergarten',4) -- 取字符串左侧的前4个字符 
SELECT RIGHT('Kindergarten',6) -- 取字符串右侧的前6个字符 
SELECT SUBSTRING('Kindergarten',3,5) -- 取字符串任何位置的字符，第二个参数是 起始位置，第三个参数是截取长度 
SELECT LOCATE('n','Kindergarten') -- 定位起始位置 -- LOCATE('q','Kindergarten')没有返回0 -- LOCATE('garten','Kindergarten')返回7
SELECT REPLACE('Kindergarten','garten','garden') -- 替换字符，将garten替换成garden
SELECT CONCAT('first','last') -- 连接字符串 
USE sql_store;

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

-- Date Functions日期函数 
SELECT NOW(),CURDATE(),CURTIME()  -- 返回 当前日期和时间，当前日期，当前时间 
SELECT YEAR(NOW()),MONTH(NOW()),DAY(NOW()),HOUR(NOW()),MINUTE(NOW()),SECOND(NOW()) -- 返回 当前年份，当前月份，当前日期，当前小时，当前分钟，当前秒数 
SELECT DAYNAME(NOW()) -- 返回 星期 
SELECT MONTHNAME(NOW()) -- 返回 字符串月份  March
SELECT EXTRACT(YEAR FROM NOW()) -- 返回 年份  

-- Exercise
SELECT *
FROM orders
WHERE YEAR(order_date) = YEAR(NOW())-3

-- Formatting Dates and Times 格式化日期和时间 
SELECT DATE_FORMAT(NOW(),'%M %d %Y') -- 返回 March 08 2022 区分M、D、Y的大小写
SELECT TIME_FORMAT(NOW(),'%H:%i %p') -- 返回 21:54 PM

-- Calculating Dates and Times 计算日期和时间 
SELECT DATE_ADD(NOW(),INTERVAL 1 YEAR) -- 返回明年的同一时间 
SELECT DATE_ADD(NOW(),INTERVAL -1 YEAR) -- 返回去年的同一时间 
SELECT DATE_SUB(NOW(),INTERVAL 1 YEAR) -- 同上
SELECT DATEDIFF('2019-01-05 09:00','2019-01-01 17：00') -- 返回天数的间隔 4
SELECT DATEDIFF('2019-01-01 17：00','2019-01-05 09:00') -- 返回天数的间隔 -4
SELECT TIME_TO_SEC('09:00') - TIME_TO_SEC('09:02') -- 返回午夜到参数时刻的秒数 

-- IFNULL和COALESCE函数
USE sql_store;

SELECT
	order_id,
    IFNULL(shipper_id,'Not assigned') AS shipper_1,  -- 返回 第二个参数的值 
    COALESCE(shipper_id,comments,'Not assigned') AS shipper_2 -- 返回 一堆值中的第一个非空值  
FROM orders

-- Exercise
SELECT 
	CONCAT(first_name,' ',last_name) AS customer,
    IFNULL(phone,'Unknown')
FROM customers

-- IF函数
SELECT 
	order_id,
    order_date,
    IF(
		YEAR(order_date) = YEAR(NOW()),
        'Active',
        'Archived') AS category
FROM orders

-- Exercise
SELECT 
	product_id,
    name,
    count(*) AS orders,
    IF(count(*) > 1, 'Many times','Once') AS frequency
FROM products 
JOIN order_items USING (product_id)
GROUP BY product_id,name

-- CASE运算符
SELECT 
	order_id,
    order_date,
    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

-- Exercise
SELECT 
	CONCAT(first_name,' ',last_name) AS coustomer,
    points,
    CASE
		WHEN points > 3000 THEN 'Gold'
        WHEN points >= 2000 THEN 'Silver'
        ELSE 'Bronze'
    END AS category
FROM customers
ORDER BY points DESC

-- 创建视图 
USE invoicing;

CREATE VIEW sales_by_client AS
SELECT 
	c.client_id,
    c.name,
    SUM(invoice_total) AS total_sale
FROM clients c
JOIN invoices i USING (client_id)
GROUP BY client_id,name

-- Exercise
CREATE VIEW client_balance AS
SELECT 
	client_id,
    name,
    SUM(invoice_total - payment_total) AS balance
FROM clients c
JOIN invoices i USING (client_id)
GROUP BY client_id,name


-- 更改或删除视图
-- 方法一 
DROP VIEW sales_by_client
-- 方法二
CREATE OR REPLACE VIEW sales_by_client AS
SELECT 
	c.client_id,
    c.name,
    SUM(invoice_total) AS total_sale
FROM clients c
JOIN invoices i USING (client_id)
GROUP BY client_id,name

-- 可更新视图
-- distinct
-- Aggregate Functions (min,max,sum)
-- group by / having
-- union
-- 没有上述语句，可更新视图
create or replace view invoices_with_balance as
select
	invoice_id,
    number,
    client_id,
    invoice_total,
    payment_total,
    invoice_total - payment_total as balance,
    invoice_date,
    due_date,
    payment_date
from invoices
where (invoice_total - payment_total) > 0

-- 删除1条记录
delete from invoices_with_balance
where invoice_id = 1

-- 更新视图
update invoices_with_balance
set due_date = date_add(due_date, interval 2 day)
where invoice_id = 2

-- with check option语句
-- 这条子句会防止update或者delete语句将行从视图中删除
create or replace view invoices_with_balance as
select
	invoice_id,
    number,
    client_id,
    invoice_total,
    payment_total,
    invoice_total - payment_total as balance,
    invoice_date,
    due_date,
    payment_date
from invoices
where (invoice_total - payment_total) > 0
with check option