一、子查询的分类
①按照功能划分
标量子查询:子查询返回的结果是一个数据(一行一列)
列子查询:返回的结果是一列(一列多行)
行子查询:返回的结果是一行(一行多列)
表子查询:返回的结果是多行多列(多行多列)
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
③ 复杂语句中使用(函数/存储过程/触发器)
1)if else
if 条件表达式 then
语句1
else
语句2
end if;
- 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() 是精确匹配,并且字段值之间用‘,'分开。