目录
复杂查询
-- 1.获取比 id=3的产品 贵的产品
select *
from products
where unit_price > (
select unit_price
from products
where product_id = 3
)
-- 查找收入在平均线以上的雇员
select *
from employees
where salary > (
select avg(salary)
from employees
)
order by salary
-- 查询未被订购过的产品
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
)
用连接重写子查询
-- 查询没有发票的客户
select *
-- clients 为主表，提取客户信息
from clients
left join invoices using(client_id)
-- 采用左连接找到每个客户对应的发票id，如果为空，说明该客户无发票
where invoice_id is null
-- 找到订购产品id为3 的客户，选择客户id，name
select
distinct c.customer_id,c.first_name,c.last_name
from customers c
join orders o using(customer_id)
join order_items oi using(order_id)
where oi.product_id = 3
-- 找到订购产品id为3 的客户，选择客户id，name
select
customer_id,first_name,last_name
from customers
where customer_id in (
select distinct customer_id
from order_items oi
join orders o using (order_id)
where product_id =3
)
关键字all 可以使用max进行改写
-- 返回大于客户3所有发票额（即大于最大的发票额即可）的发票
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
)
any
-- 选择至少有两张发票的客户
select *
from clients
where client_id = any ( -- 也可将 = any 替换为 in
select client_id
from invoices
group by client_id
having count(*) >=2
)
相关子查询
背后逻辑
for each employee
calculate the avg salary for employee.office
return the employee if salary > avg
首先来到 employees 表，对 每个员工执行 子查询 ，计算同一部门员工的平均工资，
如果这名员工的工资高于平均值，这名员工就会返回在最终结果；
接着第二条记录也会计算所有同一部门员工的平均工资。将这种查询称为相关子查询，因为子查询和外查询存在相关性。
之前写的子查询都是非关联子查询，其子查询只运行一次，返回结果后赋值到外查询的子句中；
使用相关子查询的时候，这段查询是在主查询每一行的层面执行。
-- 选择工资超过部分平均的员工
select *
from employees e -- 注意
where salary > (
select avg(salary)
from employees
-- 内查询和外查询都用到员工表
where office_id = e.office_id
)
-- 查询每个客户中大于平均发票额的发票
select *
from invoices i
where invoice_total > (
select avg(invoice_total)
from invoices
where i.client_id = client_id
)
exists
-- 查询有发票的客户
select *
from clients
where client_id in (
-- 当有发票的客户很多的时候，会形成一个很大的列表，妨碍性能，故可用exists 替代
select distinct client_id
from invoices
);
select *
from clients c
-- 使用 exists 运算符 查看发票表里是否存在符合筛选条件的行，若符合 返回给exists 运算符 true ，然后该运算符就会添加当前记录
where exists ( -- 该子查询不返回结果集，返回匹配结果 true or false
select client_id
from invoices
where c.client_id = client_id
)
-- 查询没有被订购的产品
select p.product_id,p.`name`,p.quantity_in_stock,p.unit_price
from products p
left join order_items oi using (product_id)
where oi.order_id is null;
select *
from products p
where not exists(
select product_id
from order_items
where p.product_id = product_id
);
select *
from products
where product_id not in (
select product_id
from order_items
);
select 子句中使用子查询
select invoice_id,invoice_total,
-- 子查询
-- avg() 对应一行数据
(select avg(invoice_total) from invoices) as invoice_average,
-- 表达式中不能使用列别名
-- 一种是复制整段子查询 或者 将发票平均转换成一段 子查询
invoice_total - (select invoice_average) as difference
from invoices
select c.client_id,c.`name`,
-- sum(i.invoice_total) as total_sales,
(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 difference
-- sum(invoice_total) - (select average) as difference
from clients c
-- left join invoices i using (client_id)
-- group by c.client_id
from子句中的子查询 – 对应后续 视图
select *
from (
select c.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 - average) as difference
from clients c
) as sales_summary -- 此处需要设置别名 必须
where total_sales is null
mysql 内置函数
数值函数
-- round -- 四舍五入 第二个参数设置精确度
-- truncate 截断数字
-- ceiling 上限函数 返回大于或等于这个数字的最小整数
-- floor 地板函数 返回小于或等于这个数字的最大整数
-- abs 计算绝对值
-- rand 生成 0-1区间的随机浮点数
select round(5.754) ; -- 6
select round(5.754,1); -- 5.8 保留一位小数
select truncate(5.768,2); -- 5.76
select ceiling(5.7) ; -- 6
select floor(5.7) ;-- 5
select rand()
字符串函数
-- length 获取字符数
-- upper 转成大写
-- lower 转成小写
--
-- ltrim (left trim) 移除字符串左侧的空白字符或其他预定义字符
-- rtrim (right trim) 移除字符串右侧的空白字符或其他预定义字符
-- left 返回字符串左侧的几个字符
-- right 返回字符串右侧的几个字符
-- substr(string,start,length) (sub string)字符截取函数 可获取字符串任意位置的字符 length 可省
-- locate 返回第一个字符或者一串字符的匹配位置 不区分大小写 未搜索到返回 0
-- replace 替换一个字符或一串字符
-- concat 串联字符串
日期和时间函数
-- now 调用当前的日期和时间 年-月-日 时:分:秒
-- curdate (current date) -- 仅当前日期
-- curtime (current time) -- 仅当前时间
-- year 获取年份 与now,curdate 搭配使用 year(now())
-- month 获取月份
-- day 天
-- hour 时 minute 分 second 秒
select now(),curdate(),year(now());
-- 返回字符串格式
-- dayname, monthname,
SELECT dayname (now());
-- extract() sql 通用函数
select extract(year from now());
select *
from orders
-- where order_date >= '2019-01-01'
where year(order_date) >= year(now()) -- 获取当前年份订单
格式化日期和时间
-- date_fromat、time_fromat
select date_format(now(),'%M %D %Y'); -- February 27th 2021
select time_format(now(),'%H:%i %p'); -- 21:04 PM
计算日期和时间
-- date_add,date_sub
select
date_add(now(),interval 1 day), -- 明天
date_add(now(),interval 1 year),-- 明年
date_add(now(),interval -1 year); -- 去年
-- 日期间隔 datediff(a,b) 只返回天的间隔 a-b
-- 时间间隔 time_to_sec() 秒数 time_to_sec(a) - time_to_sec(b)
select time_to_sec('01:00') -- 3600 从零时开始计算有多少秒
other function
ifnull , coalesce
select
order_id,
-- ifnull 函数中 可以用其它内容替换空值
-- 将 shipper_id 中为NULL的值替换为 not assigned
ifnull(shipper_id,'Not assigned') as shipper,
-- 而 coalesce 函数中提供一堆值，此函数会返回这堆值中第一个非空值
coalesce(shipper_id,comments,'Not assigned')
from orders
select
concat(first_name,'',last_name) as customer,
ifnull(phone,'Unknown') as phone
from customers
if – 单一表达式
-- if(expression, first, second)
-- 类似三目运算符 expression ? first : second
select order_id,
order_date,
if(year(order_date) = 2019, -- if(year(order_date) = year(now()),,)
'active','archived') as '类别'
from orders
select p.product_id,p.`name`,
count(*) as number_order,
if (count(*) > 1,'many times','once') as frequency
from products p
join order_items oi using (product_id)
group by p.product_id
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 'feature' -- 上述表达式均不满足
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
order by points desc
视图
基本表是本身独立存在的表，在SQL中一个关系就对应一个表。视图是从一个或几个基本表导出的表。视图本身不存在独立存储在数据库中，是一个虚表。即数据库中只存放视图的定义而不存放视图对应的数据，这些数据仍然存放在导出视图的基本表中。视图适合于多表连接浏览时使用! 不适合增、删、改。视图在概念上基本与表等同。视图在概念上与基本表等同，用户可以在基本表那样使用视图，可以在视图上再定义视图。
创建
create view sales_by_client as -- 创建视图
select
c.client_id,c.`name` as client,
sum(i.invoice_total) as total_sales
from clients c
left join invoices i using (client_id)
group by client_id,name
select *
from sales_by_client
order by total_sales desc
create view clients_balance as
select
c.client_id,
c.`name`,
sum(invoice_total - payment_total) as balance
from clients c
left join invoices i using (client_id)
group by client_id
order by balance desc
更新 或 删除 drop
drop view sales_by_client,clients_balance
create or replace view clients_balance as
select ...
可更新视图 – 用于有权限的表
视图中未包含一些东西，则为 可更新视图
-- distinct
-- 聚合函数 aggregate function ( max,sum,.....)
-- group by / having
-- union
create or replace view invoice_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 -- 当使用update 或delete, 结果可能导致行消失，可以采用此方式解决 会得到错误提示
传送门
SQL进阶教程 | 史上最易懂SQL教程！10小时零基础成长SQL大师！！
可以通过数据库视图可以增删改吗？——浅谈with check option的作用
视图与基本表之间的联系与区别
扫一扫
1422
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
