Sql语句特殊函数

1. group-concat函数的功能：

        将group by 产生的同一个分组中的值连接起来，返回一个字符串结果，

group-concat函数首先根据group by指定的列进行分组，将同一组的列显示出来，并且用分隔符分隔。由函数参数（字段名）决定要返回的列：

group_concat([distinct] 字段名 [order by 排序字段 asc/desc] [separator '分隔符'])

说明：

（1）使用distinct可以排除重复值；

（2）如果需要对结果中的值进行排序，可以使用order by字句；

（3）separator是一个字符串值，默认为逗号；

2.mysql replace用法

1.replace into
 

replace into table (id,name) values('1','aa'),('2','bb')

此语句的作用是向表table中插入两条记录。如果主键id为1或2不存在
就相当于

insert into table (id,name) values('1','aa'),('2','bb')

如果存在相同的值则不会插入数据

2.replace(object,search,replace)
把object中出现search的全部替换为replace

select replace('www.jb51.net','w','Ww')--->WwWwWw.jb51.net

例：把表table中的name字段中的aa替换为bb

update table set name=replace(name,'aa','bb')

mysql-replace实例说明：

UPDATE tb1 SET f1=REPLACE(f1, 'abc', 'def');
REPLACE(str,from_str,to_str)

        在字符串 str 中所有出现的字符串 from_str 均被 to_str替换，然后返回这个字符串。
REPLACE这个函数用来批量替换数据中的非法关键字是很有用的！

例1：

UPDATE BBSTopic SET tcontents = replace(replace(tcontents,'die','') ,'找死','') where tcontents like '%die%' or tcontents like '%找死%'

例2：

UPDATE typetable SET type_description=REPLACE(type_description,'360','//www.jb51.net');

3.mysql-right

MySQL数据库中系统函数right的功能说明，如下所示：

系统函数right的功能:

right函数的功能:

用于截取指定字符串右边指定的字节数

right函数语法:

right(str,len)

-------参数说明------

str:待截取字符串

len:截取的字节数

------返回值说明----

返回从右边截取后的字符串

注意事项:

1.len大于字符串的总长度时，则返回全部字符串

2.len小于等于零时，返回空字符串

3.当str是中文时，则返回截取的字节数，如：一个中文占3个字节。

4.mysql-substring

1、从左开始截取字符串

left（str, length） 
说明：left（被截取字段，截取长度） 
例：

select left(content,200) as abstract from my_content

2、从右开始截取字符串 
right（str, length） 
说明：right（被截取字段，截取长度） 
例：

select right(content,200) as abstract from my_content_t 

3、截取字符串 
substring（str, pos） 
substring（str, pos, length） 
说明：substring（被截取字段，从第几位开始截取） 
substring（被截取字段，从第几位开始截取，截取长度） 

例：

select substring(content,5) as abstract from my_content_t 
select substring(content,5,200) as abstract from my_content_t 

（注：如果位数是负数 如-5 则是从后倒数位数，到字符串结束或截取的长度）

4、按关键字截取字符串 
substring_index（str,delim,count） 
说明：substring_index（被截取字段，分隔符，关键字出现的次数） 

例：

select substring_index("www.w3cschool.cn",".",2) as abstract from wiki_user 

结果：www.w3cschool
（注：如果关键字出现的次数是负数 如-2 则是从后倒数，到字符串结束）  

5.SQL-exists的用法

1.什么时候用exists，什么时候用in?

主表为employees,从表为dept_emp，在主表和从表都对关联的列emp_no建立索引的前提下，

当从表比主表大时，exists查询的效率较高；

原因如下：

in是先执行子查询，得到一个结果集，将结果集代入外层谓词条件执行主查询，子查询只需要执行一次

exists是先从主查询中取得一条数据，再带入到子查询中，执行一次子查询，判断子查询是否能返回结果，主查询有多少条数据，子查询就要执行多少次

6.concat()函数

1、功能：将多个字符串连接成一个字符串。

2、语法：concat(str1, str2,…)

说明：返回结果为连接参数产生的字符串，如果有任何一个参数为null，则返回值为null。

3、举例：select concat (id, name, score) as 别名 from 表名；

6.1.concat_ws()函数

1、功能：和concat()一样，但是可以指定分隔符（concat_ws就是concat with separator）

2、语法：concat_ws(separator, str1, str2, …)

说明：第一个参数指定分隔符。需要注意的是分隔符不能为null，如果为null，则返回结果为null。

3、举例：select concat (‘#’,id, name, score) as 别名 from 表名；

6.2concat_ws()和group_concat()联合使用

题目：查询以name分组的所有组的id和score

select name,group_concat(concat_ws(‘-’,id,score)order by id) as 别名 from 表名 group by name；

7.case when的使用方法

Case具有两种格式。简单Case函数和Case搜索函数。

第一种 格式 : 简单Case函数 :

格式说明    

　　　　case 列名

　　　　when   条件值1   then  选项1

　　　　when   条件值2    then  选项2.......

　　　　else     默认值      end

第二种  格式 :Case搜索函数

格式说明    

　　　　case  

　　　　when  列名= 条件值1   then  选项1

　　　　when  列名=条件值2    then  选项2.......

　　　　else    默认值 end