sql——函数、谓词、CASE表达式

函数

算术函数

• ABS(m)：绝对值
  NULL的绝对值为NULL
• mod（m,n）or %(sqlsever中使用)：求余
  m= x mod(n)，x=mod(m,n)
  sqlsever中不支持该函数，用运算符%来计算余数
• round(对象数值，保留位数)：四舍五入

字符串函数

——字符串进行替换、截取、简化等操作

字符串函数

• str1||str2 or str1+str2(sqlsever中使用)：拼接两个字符串
  sqlsever中用str1+str2表示
  str+NULL得到NULL
• length(字符串) or len(str)(sqlsever中使用)：计算字符串长度
• lower(字符串)：转换为小写（只对英文字母生效）
• upper(字符串)：转换为大写（只对英文字母生效）
• replace(对象字符串,替换前的字符串,替换后的字符串)：把字符串的一部分替换为其他字符串
  对于表

select str1,str2,str3,
	replace(str1,str2,str3) as re_str
from SampleStr

逻辑：比对str1,str2，若str1中有str2，则将str2替换为str3，例： str1=abc太郎，srr2=abc，str3=ABC，str1中存在abc（str2），故把str1中的abc替换为ABC（str3），最终结果为“ABC太郎”

• substring(对象字符串,截取的起始位置,截取的字符数)

日期相关函数

例：substring(string,2,3)=tri

• current_date ：日期函数，返回SQL执行日期

select current_date
---sqlsever
SELECT CAST(CURRENT_TIMESTAMP AS DATE) AS CUR_DATE

• current_time：当前时间

SELECT CAST(CURRENT_TIMESTAMP AS TIME) AS CUR_TIME-----sqlsever

• current_timestamp：当前日期和时间（同样适用于sqlsever）
• extract(日期元素 from 日期)：截取日期元素

select current_timestamp,
	extract(year from current_timestamp) as year;
-------sqlsever
select current_timestamp,
	datepart(year,current_timestamp) as year;

转换函数

cast函数——数据类型的转换

cast(转换前的值 as 想要转换为的数据类型)
----------------例
select cast('001' as int) as int_num;

coalesce函数——将NULL值转换为其他值

coalesce(数据1,数据2,数据3,...)------返回可变参数（数据1,数据2,数据3...）中左侧开第一个不是NULL的值
----------例
select coalesce(NULL,NULL,1,'test') as A;

谓词

——谓词的返回值均为真值

like——字符串的部分一致查询

• 前方一致str1%：以str1为开头的所有字符串
• 中间一致%str1%：包含str1的所有字符串
• 最后方一致%str1：以str1为结尾的的所有字符串 or str1_：由str1+任意1个字符组成的字符串
  创建表：
  

select * from Sample_like
 where str1 like 'abc%';
select * from Sample_like
 where str1 like '%bdd%';
select * from Sample_like
 where str1 like '%dd';
 select * from Sample_like
 where str1 like 'abc__';

detween——范围查询

between xx and xxx----查询结果包含左右2个临界值
select 商品名称,销售单价
 from Products
where 销售单价 between 100 and 1000;

IS NULL and IS NOT NULL

IN(值1,值2,值3,…)——指定多个值进行查询

select 商品名称,销售单价
 from Products
where 销售单价 in (320,500,880);

同样可以使用not in进行条件排除
IN和NOT IN都无法选取NULL数据

• 可以使用子查询作为in的参数
  例：
  添加新表：
  
  和
  
  要求：查询在大阪售卖的商品名及销售价格
  完成上述操作需2步：1.从表ShopProduct中找到大阪中销售的商品id。2.从表products中找到商品id对应的商品名称及销售单价。

select 商品名称,销售单价
 from Products
 where 商品id in (select 商品id 
					from ShopProduct 
				  where 商店id='000C')

exist

——判断是否存在满足某种条件的记录
——一般只涉及一个参数且为关联子查询
——作为exist参数的子查询中经常会使用select *

select 商品名称,销售单价
 from Products as P
 where exists (select *   ---------select *为常见用法
					from ShopProduct as SP 
				  where SP.商店id='000C'
				  and SP.商品id=P.商品id)
---------------子查询中判断ShopProduct中是否存在商品id为000C且其商品id和Products表中的一致的记录

运行结果和上例一致

case表达式

简单case表达式

搜索表达式的用法包含了简单case表达式的全部功能

搜索case表达式

case when 求值表达式 then 表达式
	 when 求值表达式 then 表达式
	 ...
	 else 表达式
end

相当于if ...then；elif...then；... else...

--------------搜索case语句
select 商品名称,商品种类,
	case when 商品种类='衣服' then 'A：'+'商品种类'
		 when 商品种类='办公用品' then 'B：'+'商品种类'
		 when 商品种类='厨房用具' then 'A：'+'商品种类'
		 else NULL  -----else可省略不写，默认else null
	end as 商品种类ABC
from Products;

--------------简单case语句
elect 商品名称,商品种类,
	case 商品种类
		when '衣服' then 'A：'+'商品种类'
		 when '办公用品' then 'B：'+'商品种类'
		 when '厨房用具' then 'A：'+'商品种类'
		 else NULL  
	end as 商品种类ABC
from Products;