五 聚合函数
1. max() min() avg() sum() count()等等:
-- max() min() avg() sum() count() and so on
select
max(invoice_total) as highest,
min(invoice_total) as lowest,
max(payment_date) as least,
sum(invoice_total*1.1) as sum,
count(payment_date) as total ,-- 不计算空值的数量
count(*),-- 计算空值的数量
count(distinct client_id)-- 去掉重复值
from invoices
select
max(invoice_total) as highest,
min(invoice_total) as lowest,
max(payment_date) as least,
sum(invoice_total*1.1) as sum,
count(payment_date) as total ,-- 不计算空值的数量
count(*),-- 计算空值的数量
count(distinct client_id)-- 去掉重复值
from invoices
where invoice_date >'2019-07-01';
2.group by子句:分组统计(select 中查询的列必须是分组列或者聚合函数的参数列)
select
client_id,
sum(invoice_total) as total
from invoices
where invoice_date >'2019-07-01'
group by client_id
order by total desc;
select
state,
city,
sum(invoice_total) as total
from invoices
join clients using(client_id)
where invoice_date >'2019-07-01'
group by state,city
order by total desc;
3.having子句:用于筛选分组后的数据
select
client_id,
sum(invoice_total) as total_sales,
count(*)
from invoices
group by client_id
having total_sales>800 and count(*)>5
4.rollup运算符
select state,city,client_id, sum(invoice_total) as total_sales
from invoices
join clients using (client_id)
group by state,city with rollup;-- 汇总结果值 (只运用于聚合值的列)
select name as payment_method,sum(amount)
from payments p
join payment_methods pm on p.payment_method = pm.payment_method_id
group by name with rollup;
六 复杂查询
1.子查询:嵌套在另一条SQL查询语句内部的查询。子查询通常用于提供数据给主查询使用,可以放在SELECT
、FROM
、WHERE
、HAVING
等子句中。
select *
from products
where unit_price>(
select unit_price
from products
where product_id = 3
);
2.子查询在where子句中通常与in运算符结合使用
use sql_store;
select *
from products
where product_id not in(
select distinct product_id
from order_items
);
3.子查询和连接查询对比
-- 子查询方式
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
join orders using(customer_id)
join order_items using(order_id)
where product_id = 3;
4.all关键字:使用all前缀,invoice_total大于后面查询中的一组词中的每一个
select *
from invoices
where invoice_total >(
select max(invoice_total)
from invoices
where client_id =3);
select *
from invoices
where invoice_total>all ( -- 使用all前缀,invoice_total大于后面查询中的一组词中的每一个
select invoice_total
from invoices
where client_id=3);
5.any关键字
select*
from clients
where client_id in(
select client_id
from invoices
group by client_id
having count(*)>=2
);
select*
from clients
where client_id =any(
select client_id
from invoices
group by client_id
having count(*)>=2
);
6.相关子查询
use sql_hr;
select *
from employees e
where salary >(
select avg(salary)
from employees
where office_id = e.office_id
);
7.exists运算符
use sql_invoicing;
select *
from clients
where client_id in (
select distinct client_id
from invoices
);
select *
from clients c
where exists(
select client_id
from invoices
where client_id = c.client_id
);
8.select子句中的子查询
select
invoice_id,
invoice_total,
(select avg(invoice_total)
from invoices) as avg, -- 可以把avg赋值到每一列(对比下面一个查询)
invoice_total -(select avg) as diff
from invoices;
9.from子句中的子查询
select *
from (
select
client_id,
name,
(select sum(invoice_total) from invoices i where client_id=c.client_id) as total,
(select avg(invoice_total) from invoices) as avg,
(select avg) -(select total) as diff
-- 需要采用select+别名的方式或者原语句进行表示
from clients c
)as sale_sql -- 必须起别名
where total is not null;
七 常用函数
1.数值函数
-- round(a,b); -- 四舍五入 a表示需要四舍五入的数字的值,b为保留的位数
select round(9544.224,2);
-- ceiling(a); -- 返回大于或等于该数字的最小整数(只入)
select ceiling(5.2);
-- floor(a); -- 返回小于或等于该数字的最小整数(只舍)
select floor(12.6);
-- rand(); -- 返回一个0-1之间的随机数
select rand();
2.字符串函数
select length('sky');-- 返回字符串的字数
select upper('sky');-- 将字符串内容全部大写
select lower('sky'); -- 将字符串内容全部小写
select ltrim(' sky ');-- 删除字符串左侧的空白字符或其他预定义字符
select rtrim(' sky ');-- 删除字符串右侧的空白字符或其他预定义字符
select trim(' sky ');-- 删除字符串两侧的空白字符或其他预定义字符
select left('abcdefg',4);-- 返回字符串左侧的几个字符
select right('abcdefg',4);-- 返回字符串右侧的几个字符
select substring('abcdefg',4,2);-- 返回字符串中任意的几个字符(从4开始,截取两个)
select substring('abcdefg',4);-- 返回字符串中任意的几个字符(从4开始,截至末尾)
select locate('d','abcdefg');-- 返回字符串第一个字符或一串字符匹配位置 (不区分大小写)
select locate('h','abcdefg');-- 返回字符串第一个字符或一串字符匹配位置 (如果不存在则返回0)
select locate('de','abcdefgde');-- 返回字符串第一次匹配到的位置
select replace('abcdefg','ab','se');-- 替换字符串
select concat('d','abcdefg');-- 串联两个字符串
select concat(first_name,' ' ,last_name) as full_name
from customers;
3.日期函数
select now(),curdate(),curtime();-- 返回当前日期+时间、日期、时间
select year(now()),month(now()),day(now()),hour(now()),minute(now()),second(now());-- 提取日期中的年、月、日
select dayname(now());-- 返回字符串型的星期数
select monthname(now());-- 返回字符串型的月份
select extract(day from now());-- 返回任何单位的时间(如year、month、day...)
4.格式化日期和时间
select date_format(now(),'%m %d %y');-- 返回后两位 07 20 23
select date_format(now(),'%M %D %Y');-- 返回时间的完整版 July 20th 2023
select time_format(NOW(),'%H:%I %P');-- 15:05 p
select time_format(NOW(),'%h %i %p');-- '05 43 PM'
5.计算日期和时间
select date_add(now(),interval 1 year);-- 在日期上加上部分数字(负数表示减去)
select date_add(now(),interval -1 year);
select date_add(now(),interval 1 day);
select date_sub(now(),interval 1 year);-- 在日期上减去部分数字(负数表示增加)
select date_sub(now(),interval -1 year);
select date_sub(now(),interval 1 day);
select datediff('2019-01-05 12:00','2019-01-01 15:00'); -- 计算两个日期之间的差值(前减后)(不会考虑时间)
select time_to_sec('09:00');-- 返回从0计算的秒数
6.ifnull 和coalesce函数
use sql_store;
select
order_id,
ifnull(shipper_id ,'not assigned') as shipper -- 如果该值为空,则将该值置为指定值
from orders;
select
order_id,
coalesce(shipper_id ,comments,'not assigned') as shipper -- 如果首值为空,则将该值置为第二列中对应值,若第二列值夜为空,则置为指定值
from orders;
select
concat(first_name,' ' ,last_name) as full_name,
ifnull(phone,'unknown') as phone
from customers;
7.if函数(也可以使用onion)
select
order_id,
order_date,
if(year(order_date)=year(now()),'active','archived') as categroy
from orders;
8.case运算符
select
order_id,
order_date,
case
when year(order_date)=year(now())-6 then 'A'
when year(order_date)=year(now())-7 then 'B'
when year(order_date)<year(now())-5 then 'C'
ELSE 'C'
end
from orders;
八 视图
1.视图的创建
use sql_invoicing;
create view sales as -- 1.创建一个视图
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,name;
2.视图的使用
select *-- 使用视图
from sales
where total_sales>800;
3.删除视图
drop view sales;-
4.更改视图
create or replace view sales as -- 3.更改视图
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,name;
5.可更新视图:视图的语句中不包括distinct aggregate functions(min,max,sum...),group by having union ziju 则可以在视图上更新数据
create or replace view invoices_with_balance as
select
invoice_id,
number,
client_id,
invoice_total,
payment_total,
invoice_total-payment_total as balance
from invoices
where (invoice_total-payment_total)>0
with check option;-- 防止更改语句将某些行删除
delete from invoices_with_balance-- 更改视图中的数据
where invoice_id=1;
update invoices_with_balance
set payment_total=invoice_total
where invoice_id =2; -- 更改使得某些行消失