【SQL】SQL语法总结（二）：聚合函数、常用函数、复杂查询、视图

五 聚合函数

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;  -- 更改使得某些行消失