mysql--mysql中的子查询和常用函数的整理

一、子查询的分类
①按照功能划分

	标量子查询：子查询返回的结果是一个数据（一行一列）
	
	列子查询：返回的结果是一列（一列多行）
	
	行子查询：返回的结果是一行（一行多列）
	
	表子查询：返回的结果是多行多列（多行多列）
	
	Exists子查询：返回的结果1或者0（类似布尔操作）

② 按照出现的位置划分

	select后面：
			仅仅支持标量子查询（通常与> < = 搭配使用）
	from后面：
			表子查询
	where或having后面：
			标量子查询（通常与> < = 搭配使用）
			列子查询（通常与in any some all 搭配使用）
			行子查询
	exists后面：
			标量子查询
			列子查询
			行子查询
			表子查询

二、查询功能总结

	select 查询列表    ⑦  扩展一：+/concat函数/ifnull函数/isnull函数    扩展二：仅仅支持标量子查询（通常与> < = 搭配使用）  
	from 表1 别名       ①  表子查询
	连接类型 join 表2   ② 
	on 连接条件         ③ 
	where 筛选          ④ 扩展一：标量子查询（通常与> < = !=等搭配使用）
			              列子查询（通常与in any some all 搭配使用）
			              行子查询
	group by 分组列表   ⑤
	having 筛选         ⑥ 标量子查询（通常与> < = 搭配使用）
			              列子查询（通常与in any some all 搭配使用）
			              行子查询
	order by排序列表    ⑧
	limit 起始条目索引，条目数;  ⑨

三、Exists子查询

基本语法：where exists(查询语句); //exists就是根据查询得到的结果进行判断：如果结果存在，那么返回1，否则返回0

Where 1：永远为真

四、子查询中的关键字的使用

	子查询中特定关键字的使用
	In
	主查询 where 条件 in (列子查询);
	
	Any
	任意一个
	
	= any(列子查询)：条件在查询结果中有任意一个匹配即可，等价于in
	
	<>any(列子查询)：条件在查询结果中不等于任意一个
	
	1  =any(1,2,3)  ===== true
	
	1  <>any(1,2,3)  ===== true
	
	Some
	与any完全一样：在国外，some与any的正面含义一致，但是否定就大不相同：not any与not some
	
	开发者为了让对应的使用者不要在语法上纠结：重新设计了some
	
	All
	= all(列子查询)：等于里面所有
	
	<>all(列子查询)：不等于其中所有
	
	All数据展示
	
	如果对应的匹配字段有NULL，那么不参与匹配

参考：子查询参考

五、mysql 中的常见函数:循环函数
①if 函数

注意：
if 函数 注意 注意 注意：只有一个等号：(java 中和动态sql中会有两个等号，这点要很注意）
实际上 if 判定的是

SELECT name,IF(STATUS=1,'上架','下架') FROM crm_project

SELECT IF(TRUE,1+1,1+2);
-> 2
 
SELECT IF(FALSE,1+1,1+2);
-> 3
 
SELECT IF(STRCMP("111","222"),"不相等","相等");
-> 不相等

select *,if(book_name='java','已卖完','有货') as product_status from book where price =50

②case when then else end 函数：

	SELECT name,
	CASE 
	when dept_id>5 THEN 'A'
	when dept_id>3 THEN 'B'
	when dept_id>1 THEN 'C'
	else 'D'
	END AS '部门等级'
	FROM crm_project

常用函数参考：mysql常用函数参考

③ 复杂语句中使用（函数/存储过程/触发器）
1）if else

if  条件表达式  then
 	语句1
else
	语句2
end  if;

2. while语句

while 条件 do
	要循环执行的代码;
end while;

举例：

BEGIN
DECLARE sTemp VARCHAR(4000);
DECLARE sTempChd VARCHAR(4000);

SET sTemp = '$';
SET sTempChd = cast(deptId AS CHAR);-- deptId 的类型装换为char型

WHILE sTempChd IS NOT NULL DO
SET sTemp = CONCAT(sTemp,',',sTempChd);
SELECT GROUP_CONCAT(dept_id) INTO sTempChd FROM sys_dept where FIND_IN_SET(parent_id,sTempChd)>0;
END WHILE;
RETURN sTemp;
END

六、函数补充
1）IFNULL()、NULLIF()、ISNULL()、IS NOT NULL、IS NULL

① IFNULL(expr1,expr2)用法
假如expr1不为NULL，则 IFNULL() 的返回值为expr1; 否则其返回值为 expr2。IFNULL()的返回值是数字或是字符串，具体情况取决于其所使用的语境。
mysql> SELECT IFNULL(1,0);
-> 1
mysql> SELECT IFNULL(NULL,10);
-> 10
mysql> SELECT IFNULL(1/0,10);
-> 10
mysql> SELECT IFNULL(1/0,‘yes’);
-> ‘yes’
IFNULL(expr1,expr2)的默认结果值为两个表达式中更加“通用”的一个，顺序为STRING、REAL或 INTEGER。

其中 1 和 0 可以嵌套select

②NULLIF(expr1,expr2)用法
如果expr1 = expr2 成立，那么返回值为NULL，否则返回值为expr1。这和CASE WHEN expr1 = expr2 THEN NULL ELSE expr1 END相同。
mysql> SELECT NULLIF(1,1);
-> NULL
mysql> SELECT NULLIF(1,2);
-> 1
如果参数不相等，则 MySQL 两次求得的值为 expr1。

③ ISNULL(expr) 的用法
如expr 为null，那么isnull() 的返回值为 1，否则返回值为 0。
mysql> select isnull(1+1);
-> 0
mysql> select isnull(1/0);
-> 1
使用= 的null 值对比通常是错误的。

isnull 的一个应用：配套order by 使用，在按字段排序的时候，先把空值排在最后，isnull(s.sidx)相当于被这个函数的返回值按照升序排列，如果s.sidx为空，返回1，如果不为空，返回1，再按照升序排序，则为空的必然在后面

    <select id="queryPage" resultType="io.sportii.common.entity.pension.ServerEntity">
        SELECT
        s.id,
        sc.`name` AS server_category_name,
        s.`name`,
        sb.`name` AS business_name,
        s.display,
        (select d.value from sys_dict d where d.type='isdisplay' and d.code=s.display) as display_name,
        s.create_date,
        s.sidx
        FROM
        pension_server AS s
        INNER JOIN pension_server_category AS sc ON s.server_category_id = sc.category_id
        INNER JOIN sys_business AS sb ON s.business_id = sb.id
        <where>
            <if test="serverCategoryName!=null and serverCategoryName!=''">
                sc.`name` = #{serverCategoryName}
            </if>
        </where>
        ORDER BY
        <choose>
            <when test="sidx != null and '' != sidx">
                ${sidx} ${order}
            </when>
            <otherwise>
                isnull(s.sidx),s.sidx,s.create_date desc
            </otherwise>
        </choose>
    </select>

④ is null /is not null

七、cast（） 类型装换函数

①语法：
CAST (expression AS data_type)
②参数说明：
expression：任何有效的SQServer表达式。
AS：用于分隔两个参数，在AS之前的是要处理的数据，在AS之后是要转换的数据类型
data_type：目标系统所提供的数据类型，包括bigint和sql_variant，不能使用用户定义的数据类型。

③使用CAST函数进行数据类型转换时，在下列情况下能够被接受：
（1）两个表达式的数据类型完全相同。
（2）两个表达式可隐性转换。
（3）必须显式转换数据类型。
如果试图进行不可能的转换（例如，将含有字母的 char 表达式转换为 int 类型），SQServer 将显示一条错误信息。
如果转换时没有指定数据类型的长度，则SQServer自动提供长度为30。

④ 举例
SELECT CAST(‘12’ AS int)

SET sTempChd = cast(deptId AS CHAR);

八、 CONCAT（） 拼串函数的三种形式
① concat 函数
功能：将多个字符串连接成一个字符串。
语法：concat(str1, str2,…)　　
返回结果为连接参数产生的字符串，如果有任何一个参数为null，则返回值为null。

语法：concat(str1, seperator,str2,seperator,…)
返回结果为连接参数产生的字符串并且有分隔符，如果有任何一个参数为null，则返回值为null。

② concat_ws()函数
功能：和concat()一样，将多个字符串连接成一个字符串，但是可以一次性指定分隔符（concat_ws就是concat with separator）
语法：concat_ws(separator, str1, str2, …)
说明：第一个参数指定分隔符。需要注意的是分隔符不能为null，如果为null，则返回结果为null。

③group_concat()函数
功能：将group by产生的同一个分组中的值连接起来，返回一个字符串结果。
语法：group_concat( [distinct] 要连接的字段 [order by 排序字段 asc/desc ] [separator ‘分隔符’] )
说明：通过使用distinct可以排除重复值；如果希望对结果中的值进行排序，可以使用order by子句；separator是一个字符串值，缺省为一个逗号。

select GROUP_CONCAT(name ORDER BY id DESC SEPARATOR ';') from customers

结果：
yangshiying;饺子;15;贝多芬;朱茵;张学友;黎明;周杰伦;陈道明;刘亦菲;迪丽热巴;成龙;汤唯;林志玲;王菲;汪峰

select GROUP_CONCAT(uid) from users

使用GROUP_CONCAT()函数，默认以‘，’将拼接的字符串隔开，得到类似以下形式的字符串：

“1,2,3,4,5,6,”

使用DISTINCT()函数可去重，得到一个不包含重复值的字符串。

select GROUP_CONCAT(DISTINCT(uid)) from users

九、FIND_IN_SET（）函数

①FIND_IN_SET(str,strlist)

str 要查询的字符串
strlist 字段名 参数以”,”分隔 如 (1,2,6,8,10,22)
查询字段(strlist)中包含(str)的结果，返回结果为null或记录

假如字符串str在由N个子链组成的字符串列表strlist 中，则返回值的范围在 1 到 N 之间。 一个字符串列表就是一个由一些被 ‘,’ 符号分开的子链组成的字符串。如果第一个参数是一个常数字符串，而第二个是type SET列，则FIND_IN_SET() 函数被优化，使用比特计算。 如果str不在strlist 或strlist 为空字符串，则返回值为 0 。如任意一个参数为NULL，则返回值为 NULL。这个函数在第一个参数包含一个逗号(‘,’)时将无法正常运行。

看不懂概念也没事，按下面类子：

例子1：

SELECT FIND_IN_SET('b', 'a,b,c,d');

结果：2
因为b 在strlist集合中放在2的位置 从1开始

select FIND_IN_SET('1', '1');

返回 就是1 这时候的strlist集合有点特殊 只有一个字符串 其实就是要求前一个字符串 一定要在后一个字符串集合中才返回大于0的数

select FIND_IN_SET('2', '1，2'); 

返回2

select FIND_IN_SET('6', '1'); 

返回0 strlist中不存在str，所以返回0。

②、find_in_set()和in的区别：

如果list是常量，则可以直接用IN， 否则要用find_in_set()函数。

③、find_in_set()和like的区别：

主要的区别就是like是广泛的模糊查询，而 find_in_set() 是精确匹配，并且字段值之间用‘,'分开。
参考：添加链接描述