今天是SQL系列的第三讲,我们会讲解条件查询,文本处理,百分比,行数限制,格式化以及子查询。
条件查询
IF条件查询
#if的语法IF(expr1,expr2,expr3)#示例SELECt IF(sva=1,"男","女") AS s FROM table_name WHERe sva != \\\'\\\';
CASE WHEN条件查询
case when 可以实现if函数的功能,同时也可以联合各类聚合函数使用。
# case when也可以实和if一样的功能SELECt CASE WHEN sva=1 THEN \\\'男\\\' ELSE \\\'女\\\' END AS s FROM table_nameWHERe sva != \\\'\\\';#case when可以联合聚合函数等使用SELECt count(DISTINCT CASE WHEN sva=1 THEN \\\'id\\\' ELSE \\\'null\\\' END) AS sFROM TABLE_NAMEWHERe sva != \\\'\\\';
文本处理
SUBSTr()字符串截取
substr语法详解:
substr(strings|express,m,[n])
strings|express :被截取的字符串或字符串表达式
m 从第m个字符开始截取
n 截取后字符串长度为n
示例: select substr(\\\'abcdefg\\\',3,4) from dual; # 结果是cdef select substr(\\\'abcdefg\\\',-3,4) from dual; # 结果efg select substr(\\\'abcde\\\',2),substr(\\\'abcde\\\',-2),substr(\\\'abcde\\\',2,3),substr(\\\'abcdewww\\\',-7,3) from dual; # 结果是bcde、de、bcd、bcd
字符串拼接
1.使用特殊操作符拼接
#ACESS和SQL Serve使用+SELECt vend_name + \\\' (\\\' + vend_country + \\\')\\\'FROM VendorsORDER BY vend_name;#DB2,Oracle, PostgreSQL,SQLite ,Open Office base使用||SELECt vend_name || \\\' (\\\' || vend_country || \\\')\\\' FROM VendorsORDER BY vend_name;
2.CONCAt()函数拼接
SELECt Concat(vend_name, \\\' (\\\', vend_country, \\\')\\\') FROM VendorsORDER BY vend_name;
SPLIT()字符串分割
语法结构
split(str, regex) – Splits
str:需要分割的字符
regex:以什么符号进行分割
1.基本用法
split(\\\'a,b,c,d\\\',\\\',\\\')# 得到的结果:["a","b","c","d"]
2.截取字符串中的某个值
当然,我们也可以指定取结果数组中的某一项
split(\\\'a,b,c,d\\\',\\\',\\\')[0]# 得到的结果:a
3.特殊字符的处理
特殊分割符号
regex 为字符串匹配的参数,所以遇到特殊字符的时候需要做特殊的处理
# 例3: "." 点split(\\\'192.168.0.1\\\',\\\'.\\\')# 得到的结果:[]# 正确的写法:split(\\\'192.168.0.1\\\',\\\'.\\\')# 得到的结果:["192","168","0","1"]
LENGTH()返回字符串长度
SELECt length(vend_name) vend_lenFROM VendorsORDER BY vend_name;
LOWER()/UPPER()将字符串转换为小写或大写
SELECt vend_name, LOWER(vend_name) AS vend_name_lowcase,UPPER(vend_name) AS vend_name_upercase FROM VendorsORDER BY vend_name;
REPLACe()字符串替换
#将adress字段中的区替换为”呕“select *,replace(address,\\\'区\\\',\\\'呕\\\') AS repfrom test_tb
LEFt()/RIGHt()返回字符串左边或右边的字符
select left(CONTRACT_NAME,2)fromgb_t_contract where 1=1;#从字符表达式最左边一个字符开始返回指定数目的字符.#若 b 的值大于 a 的长度,则返回字符表达式的全部字符a.如果 b 为负值或 0,则返回空字符串.select left(\\\'2323232\\\',9) ;# 返回值为空
LTRIm()/RTRIm()/TRIm()去掉字符串左边/右边或全部空格
select ltrim(\\\' sample \\\') from table;# 返回结果:\\\'sample \\\'select rtrim(\\\' sample \\\') from table;# 返回结果:\\\' sample\\\'select trim(\\\' sample \\\') from table;# 返回结果:\\\'sample\\\'
SOUNDEX() 返回字符串SOUNDEX值
#近似匹配SELECt cust_name, cust_contact FROM Customers WHERe SOUNDEX(cust_contact) = SOUNDEX(\\\'Michael Green\\\');
CAST数据类型转换
# 将str类型的dt字段转换为int类型的select cast(dt as int) dtfrom table
取百分比
percentile()
语法格式:
percentile_approx(DOUBLE col, p ,[B])) 近似中位数函数
percentile(DOUBLE col, p ) 中位函数
前者多了一个参数B,后者无参数,其余语法一致。
求近似的第pth个百分位数,p必须介于0和1之间,返回类型为double,但是col字段支持浮点类型。参数B控制内存消耗的近似精度,B越大,结果的准确度越高。默认为10,000。当col字段中的distinct值的个数小于B时,结果为准确的百分位数。
select percentile(mmr,0.3) as 30_percentile,percentile_approx(mmr,0.5) 50_percentilefrom match_table
限制行数
LIMIT的用法
select account_id,account_namefrom tablelimt 100
格式化显示
FORMAT()数据格式化
FORMAT() 函数用于对字段的显示进行格式化。
SQL FORMAT() 语法:
SELECt FORMAT(column_name,format) FROM table_name;
FORMAT(X,D):强制保留D位小数,整数部分超过三位的时候以逗号分割,并且返回的结果是string类型的。
SELECt FORMAT(100.3465,2),FORMAT(100,2),FORMAT(,100.6,2); # 结果分别:100.35,100.00,100.60
子查询
1.子查询条件过滤
SELECt cust_idFROM OrdersWHERe order_num IN (SELECt order_numFROM OrderItemsWHERe prod_id = \\\'RGAN01\\\');
2.子查询作为计算字段
SELECt cust_name, cust_state, (SELECt COUNT(*) FROM Orders WHERe Orders.cust_id = Customers.cust_id) AS orders FROM CustomersORDER BY cust_name;