SQL学习 DAY4

第六章  编写复杂查询

6.1 where中的子查询

# 子查询：挑选price大于product为3的价格数据
use sql_store;
select *
from products
where unit_price > (
     select unit_price from products where product_id=3
     )##此时子查询返回一个值；返回列表值的用in

 6.2 in 运算符写子查询

use sql_store;
select *
from products
where product_id not in (
 select distinct product_id #不考虑重复数
 from order_items)#子查询返回了多个值

 6.3 用连接代替子查询

##按上一个方法写
use sql_store;
select customer_id,first_name,last_name
from customers 
where customer_id in (
     select customer_id from order_items oi join orders o 
     using(order_id) where product_id=3
     )
#用连接写
select distinct customer_id,first_name,last_name
from customers c
join orders o  using (customer_id)
join order_items oi using (order_id)
where oi.product_id=3
##连接写更通俗易懂

 6.4 ALL 的用法

#####子查询___ ALL 返回多个值/列/表
select *
from invoice 
where invoice_total >all (select invoice_total from invoice where client_id=3)
# where *** 跟用max的结果一致
where invoice_total >(
    select max(invoice_total)
     from invoices 
      where client_id=3)#子查询返回了单一值

 6.5 any的用法

#any 的用法
#选择至少有两张发票的顾客
select *
from clients
where client_id=any(
     select client_id from invoices group by client_id having count(*) >= 2)

 6.6 相关子查询

#相关子查询：子查询和外查询存在相关性

 6.7 exist运算符

select * 
from clients
where client_id in(
      select distinct client_id  from invoices)

#exists更能提高效率  
#并没有先给外查询返回一个结果，它会返回一个指令 说明这个子查询是否有符合这个搜索条件的行
select *
from clients c
where exists (select client_id from invoices  where client_id=c.client_id)

 6.8 select后的子查询

#select 中的子查询
use sql_invoicing;
select invoice_id,invoice_total,
	  (select avg(invoice_total) from invoices) as invoice_average,
      invoice_total-(select invoice_average) as different
#重新命名的invoice_average需要加上select
from invoices

###例子
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- average) as difference
from clients c

 6.9 from句子中的子查询

#跟select的子查询基本一致 ，但使用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- average) as difference
	from clients c
) as sales_summary #选用from语句进行子查询 需要命名子查询，不管是否需要用到它

第七章 数值函数

#可用于对应数值、日期时间和字符串值, 书写： select round(**)

#round(5.42,1) 四舍五入保留小数位数5.4，

truncate(5.4313,2) 用来截断数据5.43，

ceiling（5.43）返回大于或等于这个数的最小整数6

floor(5.43）返回小于或等于这个数的最大整数；

abs(-5.4)取绝对值5.4，

rand()生成0-1区间的随机浮点数

7.1 字符串函数

select length("sky")#得到字符串长度； upper(**)/lower 转化为大/小写字母，

ltrim("  sky") 移除字符串左侧空白字符或其他预定义字符sky,

rtrim("sky  ")----移除右侧sky；trim("  sky ")----删除所有前后空格，

left("kind",2)返回左侧字符个数ki；right("kind",2)返回右侧的nd，

substring("kind",2,3)可得到字符串中任何位置的字符 第2个位置开始 取3个，为ind，；无"3"则返回第2位置开始到字符串结束的字符，

locate("n","kind"):返回第一个字符或一串字符匹配的位置且不区分大小写 3,locate("nd","kind") 为3

replace("kint","nt","nd")可替换一个字符或一串字符 kind

concat(“first”,"last")串联两个字符串 firstlast

use sql_store;
select concat(first_name," ",last_name) as full_name
from customers

7. 2日期函数  

 （1）基本知识

select now() 调用当前日期和时间；curdate()返回当前日期没有时间；curtime()返回当前时间没有日期，

year(**)函数 可获取**中的日期年份,为数值格式 year(now()) 2023,同理 month(**) ,day(now(**)) , hour(**)、minute(*)、second(*)

dayname(now()) 获取星期数 monday ，为字符串格式；同理monthname(**),

extract(day from now())返回现在几号 19；同理(year from now()) 返回现在几年 2023

（2）日期时间的格式化

1）日期格式函数：

date_format(now(),"%y") %y表示两位数年份 23 ;大写Y返回四位数 2023

date_format(now(),"%m %y")%m表示月份 8,；大写M表示返回月份名称 Augest

date_format(now(),"%m %d %y") %d返回几号 Augest 19 2023

2）时间格式函数：

time_format(now(),"%h:%i %p)h小时 i分钟 p代表am/pm

（3）计算日期函数

1）date_add() 给日期时间值添加日期成分 eg:

 select date_add(now(), interval 1 day)给当前日期加上一天"2023-08-20 21:58:35";加上一年则将day改成 year；若想得到过去时间则用负值 -1 、或者用date_sub(now,interval 1 day)

2）计算两个日期间隔

select datediff("2019-01-23","2019-01-01"） 返回天数间隔而不是小时/分钟，即使时间里有min/hour也是返回天数  4

3）计算两个时间间隔

select time_to_sec(09:00)返回零点计数的秒数 32400

time_to_sec(09:00)-time_to_sec(09:02)返回 -120

7.3 ifnull和coalesce函数

将一些空值加入标签

ifnull和coalesce的区别：

ifnull可以用其他内容替换空值；

coalesce会返回一堆值 且返回的是这堆值中的第一个非空值

#将一些空值加上“未分配”标签
##ifnull的使用
use sql_store;
select order_id,
       ifnull(shipper_id,"not assigned") as shipper
	   #空值返回not assigned
from orders
##coalesce 的使用
select order_id,
       coalesce(shipper_id,comments,"not assigned") as shipper
	   #空值返回not assigned
from orders

7.4 if 函数

use sql_store;
select product_id,name,
      count(*) as orders,
	  if(count(*)>=2,'many times','once') as frequency
from products  
join order_items using(product_id)#连接两个表
#使用了聚合函数count 故需要用product_id,name进行分组
group by product_id,name

 7.5 case函数

  ##case：返回多个测试标签值 ，if只能返回一个

select 
     order_id,
     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 "future"
     end as category#最后用end结束case语句
from orders