-- The SELECT StatementUSE sql_store;SELECT*FROM customers
-- WHERE customer_id = 1ORDERBY first_name
-- LIKE ExerciseSELECT customer_id,first_name,phone,address
FROM customers
-- WHERE address LIKE '%trail%' OR address LIKE '%avenue%'WHERE phone LIKE'%9'-- The REGEXP OperatorSELECT*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 ISNULL-- ORDER BY 子句SELECT*,quantity * unit_price AS total_price
FROM order_items
WHERE order_id =2ORDERBY total_price DESC-- LIMIT子句 SELECT*FROM customers
ORDERBY points DESCLIMIT3-- 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 ASstatusFROM orders o
JOIN customers c
ON o.customer_id = c.customer_id
JOIN order_statuses os
ON o.status= os.order_status_id
-- ExerciseUSE 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
LEFTJOIN orders o
ON c.customer_id = o.customer_id
ORDERBY c.customer_id
-- ExerciseSELECT
p.product_id,
p.name,
oi.quantity
FROM products p
LEFTJOIN 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
LEFTJOIN orders o
ON c.customer_id = o.customer_id
LEFTJOIN shippers sh
ON o.shipper_id = sh.shipper_id
ORDERBY c.customer_id
-- ExerciseSELECT
o.order_date,
o.order_id,
c.first_name,
s.name AS shipper,
os.name ASstatusFROM orders o
LEFTJOIN customers c
ON o.customer_id = c.customer_id
LEFTJOIN shippers s
ON o.shipper_id = s.shipper_id
LEFTJOIN order_statuses os
ON o.status= os.order_status_id
ORDERBYstatus-- Self Outer Joins 自外连接USE sql_hr;SELECT
e.employee_id,
e.first_name,
m.first_name AS manager
FROM employees e
LEFTJOIN 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)LEFTJOIN shippers sh
USING(shipper_id)SELECT*FROM order_items oi
JOIN order_item_notes oin
USING(order_id,product_id)-- ExcerciseUSE 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
NATURALJOIN customers c
-- 数据库引擎自己连接 -- Cross Joins交叉连接 有点像笛卡尔积 排列组合-- 显式SELECT
c.first_name AS customer,
p.name AS product
FROM customers c
CROSSJOIN products p
ORDERBY c.first_name
-- 隐式SELECT
c.first_name AS customer,
p.name AS product
FROM customers c,products p
ORDERBY c.first_name
-- Exercise-- 隐式SELECT
p.name,
s.name AS shipper
FROM products p,shippers s
ORDERBY p.name
-- 显式SELECT
p.name,
s.name AS shipper
FROM products p
CROSSJOIN shippers s
ORDERBY p.name
-- Unions联合 -- 同一张表SELECT
order_id,
order_date,'Active'ASstatusFROM orders
WHERE order_date >='2019-01-01'UNIONSELECT
order_id,
order_date,'Archived'ASstatusFROM orders
WHERE order_date <'2019-01-01';-- 不同的表SELECT first_name
FROM customers
UNIONSELECT name
FROM shippers
-- ExerciseSELECT
customer_id,
first_name,
points,'Bronze'AStypeFROM customers
WHERE points <2000UNIONSELECT
customer_id,
first_name,
points,'Silver'AStypeFROM customers
WHERE points BETWEEN2000and3000UNIONSELECT
customer_id,
first_name,
points,'Gold'AStypeFROM customers
WHERE points >3000ORDERBY first_name
-- Column Attributes 列属性 -- Inserting a Single Row 插入单行 INSERTINTO customers (
first_name,
last_name,
birth_date,
address,
city,
state)VALUES('John','Smith','1990-01-01','address','city','CA')-- Inserting Multiple Rows 插入多行 INSERTINTO shippers(name)VALUES('shipper1'),('shipper2'),('shipper3')-- ExersiceINSERTINTO products(name,quantity_in_stock,unit_price)VALUES('name1',1,1.1),('name2',2,2.2),('name3',3,3.3)-- Inserting Hierarchical Rows插入分层行 INSERTINTO orders(customer_id,order_date,status)VALUES(1,'2019-01-02',1);INSERTINTO order_items
VALUES(LAST_INSERT_ID(),1,1,2.95),(LAST_INSERT_ID(),2,1,3.95)-- Creating a Copy of a Table 创建表复制 CREATETABLE order_archived ASSELECT*FROM orders3
INSERTINTO order_archived
SELECT*FROM orders
WHERE order_date <'2019-01-01'-- ExerciseCREATETABLE invoices_archived ASSELECT
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 ISNOTNULL-- Updating a Single Row更新单行UPDATE invoices
SET payment_total =10,payment_date ='2019-03-01'WHERE invoice_id =1UPDATE invoices
SET payment_total =DEFAULT,payment_date =NULLWHERE invoice_id =1UPDATE 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)-- ExerciseUPDATE customers
SET
points = points +50WHERE 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 ISNULL-- ExerciseUPDATE orders
SET comments ='Gold customer'WHERE customer_id IN(SELECT customer_id
FROM customers
WHERE points >3000)-- Deleting Rows删除行 DELETEFROM invoices
WHERE client_id =(SELECT client_id
FROM clients
WHERE name ='Myworks')-- Restoring the Databases恢复数据库
-- Aggregate Functions 聚合函数-- MAX()、MIN()、AVG()、SUM()、COUNT()SELECTMAX(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'UNIONSELECT'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'UNIONSELECT'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'GROUPBY state,city
-- ORDER BY total_sales DESC-- ExeerciseSELECTdate,
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
GROUPBYdate,payment_method
ORDERBYdate-- The HAVING Clause HAVING子句 SELECT
client_id,SUM(invoice_total)AS total_sales,COUNT(*)AS number_of_invoices
FROM invoices
GROUPBY client_id
HAVING total_sales >500AND number_of_invoices >5-- ExerciseUSE 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'GROUPBY
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)GROUPBY state,city WITH ROLLUP-- ExerciseSELECT
pm.name AS payment_method,SUM(amount)AS total
FROM payments p
JOIN payment_methods pm
ON p.payment_method = pm.payment_method_id
GROUPBY pm.name WITH ROLLUP-- Writing Complex Queries编写复杂查询 -- Subqueries 子查询 SELECT*FROM products
WHERE unit_price >(SELECT unit_price
FROM products
WHERE product_id =3)-- ExerciseUSE sql_hr;SELECT*FROM employees
WHERE salary >(SELECTAVG(salary)FROM employees
)-- The IN Operator IN运算符 SELECT*FROM products
WHERE product_id NOTIN(SELECTDISTINCT product_id
FROM order_items
)-- ExerciseUSE invoicing;SELECT*FROM clients
WHERE client_id NOTIN(SELECTDISTINCT client_id
FROM invoices
)-- Subqueries vs Joins子查询vs连接SELECT*FROM clients
LEFTJOIN invoices USING(client_id)WHERE invoice_id ISNULL-- ExerciseSELECT
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))SELECTDISTINCT
c.customer_id,
c.first_name,
c.last_name
FROM customers c
LEFTJOIN orders o USING(customer_id)LEFTJOIN order_items oi USING(order_id)WHERE oi.product_id =3-- The ALL Keyword ALL关键字SELECT*FROM invoices
WHERE invoice_total >(SELECTMAX(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
GROUPBY client_id
HAVINGCOUNT(*)>=2)SELECT*FROM clients
WHERE client_id IN(SELECT client_id
FROM invoices
GROUPBY client_id
HAVINGCOUNT(*)>=2)-- Correlated Subqueries 相关子查询 USE sql_hr;SELECT*FROM employees e
WHERE salary >(SELECTAVG(salary)FROM employees
WHERE office_id = e.office_id
)-- ExerciseUSE invoicing;SELECT*FROM invoices i
WHERE invoice_total >(SELECTAVG(invoice_total)FROM invoices
WHERE client_id = i.client_id
)-- The EXISTS Operator EXISTS运算符-- 1.IN SELECT*FROM clients
WHERE client_id IN(SELECTDISTINCT client_id
FROM invoices
);-- 2.连接 SELECTDISTINCT client_id,
name,
address,
city,
state,
phone
FROM clients
JOIN invoices USING(client_id);-- 3.EXISTSSELECT*FROM clients c
WHEREEXISTS(SELECT client_id
FROM invoices
WHERE client_id = c.client_id
)-- ExerciseUSE sql_store;SELECT*FROM products p
WHERENOTEXISTS(SELECT product_id
FROM order_items
WHERE product_id = p.product_id
)-- Subqueries in the SELECT Clause SELECT子句中的子查询 SELECT
invoice_id,
invoice_total,(SELECTAVG(invoice_total)FROM invoices)AS invoice_average,
invoice_total -(SELECT invoice_average)AS difference
FROM invoices
-- ExerciseSELECT
client_id,
name,(SELECTSUM(invoice_total)FROM invoices
WHERE client_id = c.client_id)AS total_sales,(SELECTAVG(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,(SELECTSUM(invoice_total)FROM invoices
WHERE client_id = c.client_id)AS total_sales,(SELECTAVG(invoice_total)FROM invoices)AS average,(SELECT total_sales - average)AS diffrence
FROM clients c
)AS sales_summary
WHERE total_sales ISNOTNULL-- Numeric Functions数值函数SELECTROUND(5.7345,2)-- 四舍五入SELECTTRUNCATE(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 ')-- 移除字符串所有的空格 SELECTLEFT('Kindergarten',4)-- 取字符串左侧的前4个字符 SELECTRIGHT('Kindergarten',6)-- 取字符串右侧的前6个字符 SELECT SUBSTRING('Kindergarten',3,5)-- 取字符串任何位置的字符,第二个参数是 起始位置,第三个参数是截取长度 SELECT LOCATE('n','Kindergarten')-- 定位起始位置 -- LOCATE('q','Kindergarten')没有返回0 -- LOCATE('garten','Kindergarten')返回7SELECTREPLACE('Kindergarten','garten','garden')-- 替换字符,将garten替换成gardenSELECT CONCAT('first','last')-- 连接字符串 USE sql_store;SELECT CONCAT(first_name,' ',last_name)AS full_name
FROM customers
-- Date Functions日期函数 SELECTNOW(),CURDATE(),CURTIME()-- 返回 当前日期和时间,当前日期,当前时间 SELECTYEAR(NOW()),MONTH(NOW()),DAY(NOW()),HOUR(NOW()),MINUTE(NOW()),SECOND(NOW())-- 返回 当前年份,当前月份,当前日期,当前小时,当前分钟,当前秒数 SELECT DAYNAME(NOW())-- 返回 星期 SELECT MONTHNAME(NOW())-- 返回 字符串月份 MarchSELECT EXTRACT(YEARFROMNOW())-- 返回 年份 -- ExerciseSELECT*FROM orders
WHEREYEAR(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(),INTERVAL1YEAR)-- 返回明年的同一时间 SELECT DATE_ADD(NOW(),INTERVAL-1YEAR)-- 返回去年的同一时间 SELECT DATE_SUB(NOW(),INTERVAL1YEAR)-- 同上SELECT DATEDIFF('2019-01-05 09:00','2019-01-01 17:00')-- 返回天数的间隔 4SELECT DATEDIFF('2019-01-01 17:00','2019-01-05 09:00')-- 返回天数的间隔 -4SELECT 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
-- ExerciseSELECT
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
-- ExerciseSELECT
product_id,
name,count(*)AS orders,IF(count(*)>1,'Many times','Once')AS frequency
FROM products
JOIN order_items USING(product_id)GROUPBY product_id,name
-- CASE运算符SELECT
order_id,
order_date,CASEWHENYEAR(order_date)=YEAR(NOW())THEN'Active'WHENYEAR(order_date)=YEAR(NOW())-1THEN'Last Year'WHENYEAR(order_date)<YEAR(NOW())-1THEN'Archived'ELSE'Future'ENDAS category
FROM orders
-- ExerciseSELECT
CONCAT(first_name,' ',last_name)AS coustomer,
points,CASEWHEN points >3000THEN'Gold'WHEN points >=2000THEN'Silver'ELSE'Bronze'ENDAS category
FROM customers
ORDERBY points DESC
-- 创建视图 USE invoicing;CREATEVIEW sales_by_client ASSELECT
c.client_id,
c.name,SUM(invoice_total)AS total_sale
FROM clients c
JOIN invoices i USING(client_id)GROUPBY client_id,name
-- ExerciseCREATEVIEW client_balance ASSELECT
client_id,
name,SUM(invoice_total - payment_total)AS balance
FROM clients c
JOIN invoices i USING(client_id)GROUPBY client_id,name
-- 更改或删除视图-- 方法一 DROPVIEW sales_by_client
-- 方法二CREATEORREPLACEVIEW sales_by_client ASSELECT
c.client_id,
c.name,SUM(invoice_total)AS total_sale
FROM clients c
JOIN invoices i USING(client_id)GROUPBY client_id,name
-- 可更新视图-- distinct-- Aggregate Functions (min,max,sum)-- group by / having-- union-- 没有上述语句,可更新视图createorreplaceview invoices_with_balance asselect
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条记录deletefrom invoices_with_balance
where invoice_id =1-- 更新视图update invoices_with_balance
set due_date = date_add(due_date,interval2day)where invoice_id =2-- with check option语句-- 这条子句会防止update或者delete语句将行从视图中删除createorreplaceview invoices_with_balance asselect
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)>0withcheckoption