第七章：常用函数、条件语句
7.1 数值函数 numeric functions
select round(3.14159, 2), # 3.14
ceiling(4.4), # 5
floor(4.4), # 4
abs(-2), # 2
rand(); # 生成0-1随机数
7.2 字符串函数 string functions
select length('ysy'),
upper('ysy'),
lower('YSy'),
# 去空格
ltrim(' y sy '), # 去掉字符前面的空格
rtrim(' y sy '), # 去掉字符后面的空格
trim(' y sy '), # 去掉前+后空格（中间的还在）
# 截取 切片(py) / substr(r)
left('Kindergarten', 4), # 前4个
right('Kindergarten', 6), # 后6个
substring('Kindergarten', 3, 2), # 从第3个（起始位置）往后的2个字母
# 寻找 in(py) / grep (r)
locate('瓜','想吃瓜的杨杨吃不到瓜'), # 返回“瓜”第一次出现的位置3
locate('寡','想吃瓜的杨杨吃不到瓜'), # 找不到的话返回 0
locate('吃瓜','想吃瓜的杨杨吃不到瓜'),
# 替换 replace(py) / gsub(r)
replace('Kindergarten', 'garten','garden'),
# 连接 +(py) / paste (r)
concat('aa','+','bb') # 输出aa+bb
;
use sql_store;
select concat(first_name, ' ', last_name) as full_name,
length(first_name) + length(last_name) as length_of_name,
concat('customer','_',left(last_name,1))
from customers;
7.3 日期函数
select
# 获取当前时间
now(), # 获取当前time
curdate(), # 获取当前日期
curtime(), # 获取当前时刻
# 提取时间的年月日
year(now()), # 同理还有 month day second
dayname(now()), # 获取星期几
monthname(now()), # 获取月份名称
# extract提取
extract(year from now())-1;
select year(order_date),
monthname(order_date),
day(order_date),
dayname(order_date)
from orders;
select *
from orders
where year(order_date) = year(now())-1;
7.4 格式化日期和时间 类似格式化输出这样
select date_format(now(),'%d-%m-%y'),
date_format(now(),'%y年%m月%d日%p');
7.5 计算日期和时间间隔
select now(),
# 前后几天/月/日
date_add(now(), interval - 4 day),
date_add(now(), interval 3 month),
datediff('2020-12-12',now()), # 还有38天过生日嘿嘿
datediff('2020-12-12','1997-12-12'); # 已经活了8401天
7.6 if null & coalesec
use sql_store;
select
order_id,
# ifnull 其实就是 if x=null:then a; else:x
ifnull(shipper_id, comments) as shipper_id_1,
# coalesce(a,b,c)如果a列是空就用b列内容，如果ab都空就填c
coalesce(shipper_id, comments, 'not assigned') as shipper_id_2
from orders;
select concat(first_name, ' ', last_name) as customer,
coalesce(phone,'unknown')as phone
from customers;
7.7 if
select
order_id,
order_date,
# if(expression, true, false)
if(year(order_date) < 2019 ,'archievd','active') as order_status
from orders;
select
p1.product_id,
name,
(select count(product_id)
from order_items
where product_id = p1.product_id) as order_num,
if((select order_num) = 0, 'never', 'at least once') as freq
from products p1
order by order_num desc;
7.8 case 运算符
select
order_id,
order_date,
case
when year(order_date) = year(now()) - 1 then '去年'
when year(order_date) = year(now()) - 2 then '前年'
when year(order_date) <= year(now()) - 3 then '老早之前'
else '将来'
end as category
from orders;
select
customer_id,
concat(first_name, ' ', last_name) as customer_name,
case
when points >= 3000 then 'gold'
when points >= 2000 then 'silver'
else 'bronze'
end as category
from customers
order by points desc;
第八章：视图
/*
创建视图，很像数据透视表 ~ 注意视图不存储数据~
更改或者删除视图
可更新视图
with option check
视图的优点：
方便简洁、减少改动带来的影响
*/
8.1 创建视图
use sql_invoicing;
## 1.create
create view sales_by_client as
select
c.client_id,
c.name,
sum(invoice_total) as total_sales
from clients c
join invoices i using(client_id)
group by client_id;
## 2.使用
select *
from sales_by_client
join clients using(client_id);
create view balance_client as
select
client_id,
name,
sum(invoice_total - payment_total) as balance_total
from clients c
join invoices i using(client_id)
group by client_id;
select * from balance_client;
## 8.2 更改或删除视图
```{sql}
drop view balance_client;
create or replace view balance_client as
select
client_id,
name,
sum(invoice_total - payment_total) as balance_total
from clients c
join invoices i using(client_id)
group by client_id;
### 保存方法——单独一个sql文件，文件名和视图名一样
8.3 可更新视图
/*
如果视图中没有：
- distinct / 聚合函数 / group by / having / union
那么叫做可更新视图（可以在insert update delete语句中使用)
*/
create or replace view invoices_with_balance as
select
invoice_id,
number as phone_number,
client_id,
invoice_total,
payment_total,
invoice_total - payment_total as balance_total,
invoice_date,
due_date,
payment_date
from invoices
where invoice_total > payment_total;
# 更新视图1
delete from invoices_with_balance
where invoice_id = 1;
# 更新视图2
update invoices_with_balance
set due_date = date_add(due_date, interval 2 day)
where invoice_id = 2;
8.4 with option check
update invoices_with_balance
set payment_total = invoice_total
where invoice_id = 3; # 会发现因为balance=0，直接drop掉了
create or replace view invoices_with_balance as
select
invoice_id,
number as phone_number,
client_id,
invoice_total,
payment_total,
invoice_total - payment_total as balance_total,
invoice_date,
due_date,
payment_date
from invoices
where invoice_total > payment_total
with check option; # 加上这一行之后，防止行被删除