数据库学习
一、
SELECT *
FROM customers
WHERE last_name LIKE 'b____y'
-- %any number of characters
-- _ single character
第二章
1.正则表达式
SELECT *
FROM customers
WHERE last_name REGEXP '[a-h]e'
^ 代表字符串开头
$ 代表字符串结尾
| 代表一个逻辑或 OR, 这样就可以给出多个搜索模式
[] 匹配任意在括号里列举的单字符
[a-h] 表示从a-h的任意字母
exercise
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]'
2.排序
ORDER BY
SELECT *, quantity * unit_price AS total_price
FROM order_items
WHERE order_id = 2
ORDER BY total_price DESC
3.限定查询返回的记录
SELECT *
FROM customers
LIMIT 6, 3
6被称为偏移量,跳过前6条记录然后获取三条记录
Exercise
获取积分最多的前三名顾客
SELECT *
FROM customers
ORDER BY points DESC
LIMIT 3
注:LIMIT 语句要放到最后,语句顺序很重要
第三章 表之间的连接
1.内连接
SELECT order_id, o.customer_id, first_name, last_name
FROM orders o
JOIN customers c
ON o .customer_id = c.customer_id
别名AS 可以省略
练习
SELECT order_id, o.product_id, quantity, o.unit_price
FROM order_items o
JOIN products p
ON o.product_id = p.product_id
☆多张表有一样的表名是,调用的时候要添加表名称前缀,让他生效。
SELECT 返回结果
2.跨数据库连接
USE sql_inventory;
SELECT *
FROM sql_store.order_items oi
JOIN sql_inventory.products p
ON oi.product_id = p.product_id
只需要给不在当前数据库的表加前缀
3.自连接
USE sql_hr;
SELECT
e.employee_id,
e.first_name,
m.first_name AS manager
FROM employees e
JOIN employees m
ON e.reports_to = m.employee_id
4.多表连接
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
表连接尽量不要超过3张,所有的表链接都会一定的影响查询速度
练习
USE sql_invoicing;
SELECT
p.date,
p.invoice_id,
p.amount,
c.name,
pm.name AS payment_method
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
5.复合连接条件
SELECT *
FROM order_items oi
JOIN order_item_notes oin
ON oi.order_id = oin.order_id
AND oi.product_id = oin.product_id
复合主键,包含超过了一列。
6.隐式连接语法
显式连接语法(正常下的操作)
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
★建议不要使用,如果忘记写WHERE o.customer_id = c.customer_id ,则会得到交叉连接的结果!(笛卡儿积)
即:订单表里的每条记录都和顾客表里的每条记录连接了。
7.外连接
JOIN 前是右,后是左。
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
LEFT JOIN 做连接:左边这个表里的所有记录都会返回,不管正确与否。
SELECT
c.customer_id,
c.first_name,
o.order_id
FROM orders o
RIGHT JOIN customers c
ON c.customer_id = o.customer_id
ORDER BY c.customer_id
练习
SELECT
p.product_id,
name,
quantity
FROM products p
LEFT JOIN order_items oi
ON p.product_id = oi.product_id
8.多表外连接(尽量使用左连接)
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
练习
SELECT
o.order_date,
o.order_id,
c.first_name,
sh.name AS shipper,
os.name AS status
FROM orders o
JOIN customers c
ON o.customer_id = c.customer_id
LEFT JOIN shippers sh
ON o.shipper_id = sh.shipper_id
LEFT JOIN order_statuses os
ON o.status = os.order_status_id
9.自外连接
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
10. USING子句
使用前提是一样的字段名称
注:USING 只能在不同表中的列名字完全一样的情况下使用。
SELECT
o.order_id,
c.first_name
FROM orders o
JOIN customers c
-- ON o.customer_id = c.customer_id
USING(customer_id) --与上一句作用相同
SELECT *
FROM order_items oi
JOIN order_items_notes oin
USING (order_id, product_id)
练习
USE sql_invoicing;
SELECT
p.date,
c.name AS client,
p.amount,
pm.name AS payment_methods
FROM payments p
JOIN clients c
USING (client_id)
JOIN payment_methods pm
ON pm.payment_method_id = payment_method
11.自然连接
数据库引擎自己选择 相同名称的列连接
SELECT
o.order_id,
c.first_name
FROM orders o
NATURAL JOIN customers c
注:不建议使用
12.交叉连接
顾客表里的每条记录都会和产品表里的每条记录结合。
显式交叉连接
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
-- CROSS JOIN products p
ORDER BY c.first_name
练习
SELECT -- 隐式
sh.name AS shipper,
p.name AS product
FROM shippers sh, products p
ORDER BY sh.name
SELECT --显式
sh.name AS shipper,
p.name AS product
FROM shippers sh
CROSS JOIN products p
ORDER BY sh.name
13.联合UNION
合并多段查询的记录,可以是同一个表也可以是不同表。
注:查询返回的结果一定要一样,否则会出错。
第一段查询决定返回结果的列名
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
customer_id,
first_name,
points,
'Bronze' AS type
FROM customers
WHERE points < 2000
UNION
SELECT
customer_id,
first_name,
points,
'Sliver' AS type
FROM customers
-- WHERE points < 3000 AND points >2000
WHERE points BETWEEN 2000 AND 3000 --别忘了用BEWTEEN
UNION
SELECT
customer_id,
first_name,
points,
'Gold' AS type
FROM customers
WHERE points > 3000
ORDER BY first_name
第四章 数据库的设计
1.列属性
INT 整型
*VARCHAR(50)*可变字符的缩写,这列最多可以有50个字符,不会因不够而浪费空间。一般用来存储字符串和文本值。
CHAR(50) 顾客名只有5个字符,MySQL会在插入45个空格符填满这列,会浪费空间。
PK—主键列的缩写
NN—非空值,决定该列是否可以写空值
AI— 自动递增的意思,通常被用来主键列
2.插入单行
字符串和日期值要带上引号,带引号双引号都行。
INSERT INTO customers
VALUES (DEFAULT,
'John',
'Smith',
'1990-01-01',
NULL,
'address',
'city',
'CA',
DEFAULT)
等同
INSERT INTO customers(
first_name,
last_name,
birth_date,
address,
city,
state)
VALUES (
'John',
'Smith',
'1990-01-01',
'address',
'city',
'CA')
3.插入多行
INSERT INTO shippers(name)
VALUES ('shipper1'),
('shipper2'),
('shipper3')
一对括号一行
练习
INSERT INTO products(name, quantity_in_stock, unit_price)
VALUES ('product1',1,2),
('product2',2,3),
('product3',3,4)
4.插入分层行
INSERT INTO orders(customer_id, order_date, status)
VALUES (1, '2019-01-02', 1);
INSERT INTO order_items
VALUES (last_insert_id(),2, 1, 3.95),
(last_insert_id(),1, 1, 2.95)
LAST_INSERT_ID (获取最近插入的id,即新生成的id)
5.创建表复制
从一张表复制数据到另一张表
CREATE TABLE orders_archived AS
SELECT * FROM orders -- 子查询语句
用这个技巧创建表时,MySQL会忽略主键等属性
子查询是部分属于另外一段SQL语句的选择语句
Teuncate Table 截断表(删除表中所有行)
使用选择语句作为插入语句中的子查询的一个例子:
INSERT INTO orders_archived
SELECT *
FROM orders
WHERE order_date < '2019-01-01'
练习
CREATE TABLE invoices_archived AS
SELECT
i.invoice_id,
i.number,
c.name AS client,
i.invoice_total,
i.payment_total,
i.invoice_date,
i.payment_date,
i.due_date
FROM invoices i
JOIN clients c
ON i.client_id = c.client_id
WHERE payment_date IS NOT NULL
DROP table 删除表
6.更新单行
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
客户在截止日期付了百分之50的支票
UPDATE invoices
SET payment_total = invoice_total * 0.5,
payment_date = due_date
WHERE invoice_id = 3
7.更新多行
UPDATE invoices
SET payment_total = invoice_total * 0.5,
payment_date = due_date
WHERE client_id = 3
练习:1990年前出生的人加50积分
USE sql_store;
UPDATE customers
SET points = points + 50
WHERE birth_date <'1990-01-01'
8.在UPDATE中用子查询
用子查询
UPDATE invoices
SET
payment_total = invoice_total * 0.5,
payment_date = due_date
WHERE client_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
注:以上两种使用前最好先确定一下查询的内容是否正确!
练习
积分大于3000的顾客,在注释中标注“Gold customer”
USE sql_store;
UPDATE orders
SET comments = 'Gold customer'
WHERE customer_id IN
(SELECT customer_id
FROM customers
WHERE points > 3000)
9.删除行
DELETE FROM invoices
WHERE client_id = (
SELECT client_id
FROM clients
WHERE name = 'Myworks')
10.恢复数据库
打开数据库脚本,重新执行
第五章
1.聚合函数(汇总数据)(只运行非空值)
为数据汇总编写查询
USE sql_invoicing;
SELECT
MAX(invoice_total) AS highest,
MIN(invoice_total) AS lowest,
AVG(invoice_total) AS average,
SUM(invoice_total) AS total,
COUNT(invoice_total) AS number_of_invoices,
COUNT(payment_date) AS count_of_payments,
COUNT(*) AS total_records
--加上* 表示所有记录(包括NULL)
FROM invoices
去掉重复值↓
COUNT(DISTINCT client_id) AS total_records
练习
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'
2. GROUP BY 子句
注意顺序
SELECT
client_id,
SUM(invoice_total) AS total_sales
FROM invoices
WHERE invoice_date >= '2019-07-01'
GROUP BY client_id
ORDER BY total_sales DESC
group by语句永远在from和where子句之后
SELECT
state,
city,
SUM(invoice_total) AS total_sales
FROM invoices
JOIN clients USING (client_id)
GROUP BY client_id
练习
SELECT
date,
pm.name AS payment_method,
SUM(p.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
3. HAVING子句
HAVING 在分组行后筛选数据
WHERE在分组行之后筛选数据
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
HAVING 中用到的列必须是SELECT中提到的列!
WHERE中不存在这个问题。
练习
USE sql_store;
SELECT
c.customer_id,
c.first_name,
c.last_name,
state,
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 state = 'VA'
GROUP BY
c.customer_id,
c.first_name,
c.last_name
HAVING total_sales > 100
4. ROLLUP运算符(仅MySQL中有)
只应用于聚合值得列
USE sql_invoicing;
SELECT
client_id,
SUM(invoice_total) AS total_sales
FROM invoices
GROUP BY client_id WITH ROLLUP
每个组及每个结果得汇总值 ↓
USE sql_invoicing;
SELECT
state,
city,
SUM(invoice_total) AS total_sales
FROM invoices
JOIN clients USING (client_id)
GROUP BY state,city WITH ROLLUP
练习
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
注:用rollup运算符得时候,不能在group by子句中使用列别名
第六章
1.恢复数据库
2.子查询
先执行内查询,再执行外查询。
SELECT *
FROM products
WHERE unit_price > (
SELECT unit_price
FROM products
WHERE product_id = 3
)
练习
找到高于平均雇员工资的雇员。
USE sql_hr;
SELECT *
FROM employees
WHERE salary > (
SELECT AVG(salary)
FROM employees
)
3. IN运算符
SELECT *
FROM products
WHERE product_id NOT IN(
SELECT DISTINCT product_id
FROM order_items
)
练习
SELECT *
FROM clients
WHERE client_id NOT IN (
SELECT DISTINCT client_id
FROM invoices
)
4.子查询VS连接
与3中的练习一样的功能,根据情况适当选择
SELECT *
FROM clients
LEFT JOIN invoices USING (client_id)
whERE invoice_id IS NULL
练习 用两种方法找出购买生菜的顾客
USE sql_store;
-- 自己写的嵌套
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 customer_id, first_name, last_name
FROM customers
LEFT JOIN orders USING (customer_id)
LEFT JOIN order_items oi USING (order_id)
WHERE oi.product_id = 3
-- MOSH解答_子查询
SELECT customer_id, first_name, last_name
FROM customers
WHERE customer_id IN (
SELECT o.customer_id
FROM order_items oi
JOIN orders o USING (order_id)
WHERE product_id = 3
)
5. ALL关键字
ALL关键字会将所有满足条件的记录返回出来
且可以有好几个需求值时,也可以返回。>ALL(120,150,20,…)
大于括号里的所有项
每当用了ALL关键字,都可以用MAX聚合函数改写 两者都好用
--MAX聚合函数实现
SELECT *
FROM invoices
WHERE invoice_total > (
SELECT MAX(invoice_total)
FROM invoices
WHERE client_id = 3
)
--ALL函数实现
SELECT *
FROM invoices
WHERE invoice_total > ALL(
SELECT invoice_total
FROM invoices
WHERE client_id = 3
)
6. ANY关键字
ANY和SOME关键字一样,得到高于子查询返回的任意值。
-- 使用IN运算符
SELECT *
FROM clients
WHERE client_id IN(
SELECT client_id
FROM invoices
GROUP BY client_id
HAVING COUNT(*) > 2
)
-- 不使用IN运算符使用‘= ANY()’
SELECT *
FROM clients
WHERE client_id = ANY (
SELECT client_id
FROM invoices
GROUP BY client_id
HAVING COUNT(*) > 2
)
-- ANY 和 SOME 一样
SELECT *
FROM clients
WHERE client_id = SOME (
SELECT client_id
FROM invoices
GROUP BY client_id
HAVING COUNT(*) > 2
)
=ANY 与 IN等效
7.相关子查询
使用相关子查询的时候在,这段查询会在主查询的每一行的层面执行
-- for each employee
-- calulate the avg salary for employee.office
-- return the employee if salary > avg
SELECT *
FROM employees e
WHERE salary > (
SELECT AVG(salary)
FROM employees
WHERE office_id = e.office_id
)
练习
USE sql_invoicing;
SELECT *
FROM invoices i
WHERE invoice_total > (
SELECT AVG(invoice_total)
FROM invoices
WHERE client_id = i.client_id
)
8. EXISTS运算符
与IN 运算符相比,在记录多时使用EXISTS运算符提高效率。
IN运算符的子查询返回多个值给外查询,而EXIESTS返回的是一个指令(TRUE || FALSE),如果在相关子查询中找到满足条件的记录,则返回给EXISTS一个true,记录下这个记录。
-- Select clients that have an invoice
-- 方法一:子查询
SELECT *
FROM clients
WHERE client_id IN (
SELECT DISTINCT client_id
FROM invoices
)
-- 方法二:连接
SELECT *
FROM clients
JOIN invoices USING (client_id)
-- 方法三:EXISTS
SELECT *
FROM clients c
WHERE EXISTS (
SELECT client_id
FROM invoices
WHERE client_id = c.client_id
)
练习
-- Find the products that have never been ordered
USE sql_store;
SELECT *
FROM products p
WHERE NOT EXISTS (
SELECT product_id
FROM order_items
WHERE product_id = p.product_id
)
-- IN 运算符
SELECT *
FROM products
WHERE product_id NOT IN(
SELECT DISTINCT product_id
FROM order_items
)
9. SELECT子句中的子查询
表达式中不能使用列的别名
第六行:利用SELECT来使用别名用于表达式
SELECT
invoice_id,
invoice_total,
(SELECT AVG(invoice_total)
FROM invoices) AS invoice_average,
invoice_total - (SELECT invoice_average) AS difference --←←←
FROM invoices
练习
SELECT
client_id,
c.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) - (SELECT average) AS difference
(SELECT total_sales - average) AS difference
FROM invoices
RIGHT JOIN clients c USING(client_id)
GROUP BY client_id
MOSH答案
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) - (SELECT average) AS difference
(SELECT total_sales - average) AS difference
FROM clients c
10. FROM子句中的子查询
每当我们在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) - (SELECT average) AS difference
(SELECT total_sales - average) AS difference
FROM clients c
) AS sales_summary
WHERE total_sales IS NOT NULL
作为视图存储在数据库中
可以用视图来存储上面FROM子句中的内容,会大大简便查询。
第七章 MySQL内置函数
1.数值函数
SELECT ROUND(5.73) -- 用来四舍五入 6
SELECT ROUND(5.73,1) -- 保留了一位小数四舍五入5.7
SELECT ROUND(5.7345,2) -- 保留了两位小数四舍五入5.73
SELECT TRUNCATE(5.7365,2) -- 截断函数,保留了两位小数,移除其他位数5.73
SELECT CEILING(5.7) -- 上限函数,返回大于或等于这个数的最小整数 6
SELECT FLOOR(5.7) -- 下限函数,返回小于于或等于这个数的最小整数 5
SELECT ABS(-5.2) -- 用来计算绝对值 5.2
SELECT RAND() -- 用来生成0-1区间的随机浮点数
2.字符串函数
SELECT LENGTH('sky') -- 3个字符
SELECT UPPER('sky') -- 转成大写字母
SELECT LOWER('Sky') -- 转成小写字母
-- 删除字符串里不必要的空格
SELECT LTRIM(' Sky') -- left trim(左修整)的简写,移除字符串左侧的空白字符或其他预定义字符
SELECT RTRIM('Sky ')
SELECT TRIM(' Sky ') -- 删除所有前导或者尾随空格
SELECT LEFT('Kindergarten',4) -- 返回字符串左侧的4个字符 Kind
SELECT RIGHT('Kindergarten',6) -- 返回字符串右侧的6个字符 garten
SELECT SUBSTR('Kindergarten', 3, 5) -- 字符截断函数,可以得到一个字符串中任何位置的字符 nderg
SELECT SUBSTR('Kindergarten', 3) -- 返回从起始位置起算到字符串最后的所有字符 ndergarten
SELECT LOCATE('n','Kindergarten') -- 会返回第一个字符或者一串字符匹配位置,不分大小写,找不到会返回0。 n在字符串中的位置 3
SELECT LOCATE('garten','Kindergarten') -- 7
SELECT REPLACE('Kindergarten','garten','garden') -- 3个参数,将garten转换成garden
SELECT CONCAT('first','last') -- 串联字符串
USE sql_store;
SELECT CONCAT(first_name,' ', last_name)
FROM customers
3.日期函数
SELECT NOW(), -- 获取当前日期 2022-02-17 18:26:44
CURDATE(), -- 获取当年年月日 2022-02-17
CURTIME(), -- 获取当前时间去掉年月日 18:26:44
YEAR(NOW()), -- 先获取当前日期,返回年 2022
MONTH(NOW()), -- 先获取当前日期,返回月 2
DAY(NOW()), -- 先获取当前日期,返回日 17
HOUR(NOW()), -- 先获取当前日期,返回时 18
MINUTE(NOW()), -- 先获取当前日期,返回分 33
SECOND(NOW()),-- 先获取当前日期,返回秒 11
-- ***以上只能返回整数,下面两种可以返回字符串***
DAYNAME(NOW()), -- 返回星期数 Thursday
MONTHNAME(NOW()), -- 返回月February
EXTRACT(YEAR FROM NOW()) -- 获取年 2022
练习
返回当前年份的订单
SELECT *
FROM orders
WHERE YEAR(order_date) = YEAR(NOW())
4.格式化日期和时间
SELECT DATE_FORMAT(NOW(),'%m %d %y')
-- 年 %Y四位数2022,%y两位数22
-- 月 %M月份名February,%m两位数02
-- 日 %d 17
SELECT TIME_FORMAT(NOW(),'%H:%i %p')
-- H代表时,i表示分钟,p表示am或pm
5.计算日期和时间
SELECT DATE_ADD(NOW(), INTERVAL 1 DAY), -- 增加一天
DATE_ADD(NOW(), INTERVAL 1 YEAR), -- 增加一年
DATE_ADD(NOW(), INTERVAL -1 YEAR), -- 减少一年
DATE_SUB(NOW(), INTERVAL 1 YEAR), -- 减少一年
DATEDIFF('2019-01-05', '2019-01-01'), -- 返回天数的间隔,不是小时或分钟 4
DATEDIFF('2019-01-01', '2019-01-05'), -- 返回天数的间隔,不是小时或分钟 -4
TIME_TO_SEC('9:00'),-- 返回从零点(午夜)计算的秒数 32400
TIME_TO_SEC('9:00') - TIME_TO_SEC('9:02') -- 时间间隔 -120
6. IFNULL和COALESCE函数
IFNULL函数:我们用其他内容替换空值
COALESCE函数:我们提供一堆值,返回这堆值中的第一个非空值
USE sql_store;
SELECT
order_id,
IFNULL(shipper_id,'Not assigned') AS shipper
FROM orders
USE sql_store;
SELECT
order_id,
COALESCE(shipper_id, comments, 'Not assigned') AS shipper
FROM orders
练习
SELECT
CONCAT(first_name, ' ', last_name) AS customer,
IFnull(phone, 'Unkown') AS phone
FROM customers