sql整理

最新推荐文章于 2025-01-02 18:15:00 发布

原创最新推荐文章于 2025-01-02 18:15:00 发布 · 272 阅读

0 ·

CC 4.0 BY-SA版权

oracle 同时被 2 个专栏收录

6 篇文章

订阅专栏

MySQL

2 篇文章

订阅专栏

本文深入探讨了SQL查询中的优化技巧，包括使用EXISTS替代IN、NOT EXISTS替代NOT IN，以及如何通过调整查询语法来提高数据库性能。通过具体实例说明了如何在不同场景下应用这些优化策略，以实现更高效的查询执行。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

*插入数据后并查出插入数据的主键id

<insert id="abatorgenerated_insert" parameterClass="com.garea.cloud.entities.em.CustOrg">
        insert into cust_org (org_id)
        values (#orgId:BIGINT#)
        <selectKey keyProperty="orgId" resultClass="java.lang.Long">
        <![CDATA[  
        SELECT LAST_INSERT_ID() AS id
        ]]>
        </selectKey>
    </insert>

*<![CDATA[]>

*case when else end

*DATE_FORMAT(,)

<![CDATA[select ca.id as id,ca.open_id as openId,
    case
    when cp.birthday is NULL then NULL
    else
    (year(now())-year(cp.birthday)-1) + ( DATE_FORMAT(cp.birthday, '%m%d') <= DATE_FORMAT(NOW(), '%m%d') ) end as age,
    (SELECT GROUP_CONCAT(beConcerned.person_name) FROM cust_person_relation cor left join cust_person beConcerned on beConcerned.id = cor.be_concerned_id WHERE cor.concerned_id = #accountId:BIGINT#) as beConcerned
    from cust_account ca
    left join cust_person cp on cp.id = ca.id
    left join cust_org co on co.org_code = cp.org_code]]>
    where ca.id = #accountId:BIGINT#

<select id="selectAllByParams" parameterClass="com.garea.cloud.dto.em.CustPersonSearchDto" resultClass="com.garea.cloud.dto.em.CustPersonAccountOrgDto">
  	 <![CDATA[select ca.id as id,
	from cust_person cp 
	left join cust_account ca on ca.id = cp.id
	left join cust_org co on co.org_code = cp.org_code ]]>
	<dynamic prepend="where">
		<isNotEmpty prepend="and" property="accountLevel">
			ca.account_level = #accountLevel:SMALLINT#
		</isNotEmpty>
		<isNotEmpty prepend="and" property="params">
			(cp.person_name like CONCAT('%',#params:VARCHAR#,'%')
			 or ca.nick_name like CONCAT('%',#params:VARCHAR#,'%')
			 or cp.mobile_phone like CONCAT('%',#params:VARCHAR#,'%'))
		</isNotEmpty>
	</dynamic>
	order by ca.create_time desc
  </select>

*LEFT (<character_expression>， <integer_expression>)

返回character_expression 左起 integer_expression 个字符。

*用EXISTS替代IN、用NOT EXISTS替代NOT IN：

在许多基于基础表的查询中,为了满足一个条件,往往需要对另一个表进行联接.在这种情况下, 使用EXISTS(或NOT EXISTS)通常将提高查询的效率. 在子查询中,NOT IN子句将执行一个内部的排序和合并. 无论在哪种情况下,NOT IN都是最低效的 (因为它对子查询中的表执行了一个全表遍历). 为了避免使用NOT IN ,我们可以把它改写成外连接(Outer Joins)或NOT EXISTS。
例子：

（高效）SELECT * FROM  EMP (基础表)  WHERE  EMPNO > 0  AND  EXISTS (SELECT ‘X'  FROM DEPT  WHERE  DEPT.DEPTNO = EMP.DEPTNO  AND  LOC = ‘MELB') 
(低效)SELECT  * FROM  EMP (基础表)  WHERE  EMPNO > 0  AND  DEPTNO IN(SELECT DEPTNO  FROM  DEPT  WHERE  LOC = ‘MELB')
SELECT * FROM (
		SELECT * FROM  cust_area  WHERE  area_parent IN (SELECT area_code FROM  cust_area WHERE area_name = #areaName:VARCHAR#)) a 
		WHERE EXISTS (SELECT 1 FROM cust_store t WHERE (LEFT(t.area_code,4) = LEFT(a.area_code, 4)) AND  (t.status = 2  OR  t.status = 5))

下面链接有这方面的介绍：
http://my.oschina.net/xianggao/blog/87216