SQL笔记

原创已于 2025-09-16 16:20:54 修改 · 1.3k 阅读

6 ·

CC 4.0 BY-SA版权

文章标签：

#sql #linq #p2p

于 2022-06-22 14:18:05 首次发布

sql 专栏收录该内容

1 篇文章

订阅专栏

本文详细讲解了SQL中的聚合函数(max, min, sum, avg, count)、标量子查询、窗口函数的用法，以及SQL查询顺序、类型转换、数据聚合、日期处理、联表操作和优化策略。涉及案例涵盖了字段优先级、拼接、条件逻辑、数据筛选、结果排序、null值处理和常见数据库技术如PostgreSQL和达梦的语法差异。

11 1.优先级问题：

2.聚合函数

常见的五个聚合函数：（max、min、sum、avg、count）

3.标量子查询

4.窗口函数

5.sql查询的顺序问题

t.bashichu_code =#{bashichuCode,jdbcType=VARCHAR}

6.会指定字段类型

7.sql 优化问题

<sql id="Base_Column_List">
    id, name, first_letter, sort
</sql>
    
<select>
    select
    <include refid="Base_Column_List" />
    from pms_brand
</select>
    
等同于：

<select>
    select  id, name, first_letter, sort
    from pms_brand
</select>

8.查询多个列拼接起来返回可以拼接相同类型的数据，不同类型无法拼接

方式一：
select ename|| 'work as a '|| job as msg
     from emp 
        where depton=10 
方式二：
SELECT concat(point_name || 'point_address as A '|| note) as msg 
FROM "wisdom_xy_interest_point"

9.sql使用条件逻辑

SELECT id, point_name,
		case when id<60 then 'UPDATE'
					when id >100 then 'else'
					else 'BIG'
					end as status
					FROM "wisdom_xy_interest_point"

10.限制返回的行数

SELECT id, point_name,
		case when id<60 then 'UPDATE'
					when id >100 then 'else'
					else 'BIG'
					end as status
					FROM "wisdom_xy_interest_point"	
					LIMIT 10

11.随机返回10行数据

SELECT id, point_name,
		case when id<60 then 'UPDATE'
					when id >100 then 'else'
					else 'BIG'
					end as status
					FROM "wisdom_xy_interest_point"	
			ORDER BY random()		LIMIT 10

12.将字段的值为null替换成别的数据输出，不为null的则为原值(替换的值类型与字段一致)

SELECT coalesce(point_id,'11') FROM "wisdom_xy_interest_point"

13.translate函数（翻译），replace（替换）

select data
    from v
    order   by replace(data,
                replace(
                    translate(data,'0123456789','##########'),'#',''),''))

14.判断数据长度

SELECT * from sys_person_info

	WHERE length(id_card)!=18

15.排序时处理null值

SELECT
	ename,
	sal,
	comm 
FROM
	( SELECT ename, sal, comm, CASE WHEN comm IS nullehrn 0 ELSE 1 AS is_null FROM emp ) AS x 
ORDER BY
	is_null,
	comm

16.dto返回日期格式：

@JsonFormat(pattern = DateUtils.DATE_TIME_PATTERN)

17.根据两个字段的值进行排序：

SELECT ename ,sal,job,comm
from 
emp order by case when job='SALESMAN'then comm else sal end

18.最近一周的数据

//postreSQL
AlarmDate >= now() - interval '10 day'
SELECT count(*) FROM wisdom_knowledge_policy WHERE create_date >= date_trunc( 'month', now( ) )

//达梦、MySQL
DATE_SUB(curdate(), INTERVAL '10' DAY) <= AlarmDate

19.日期处理函数

//MySQL：
convert(Date,'转换为的数据类型') //Date可为输入时间
//或
date_format('转换值','%Y-%m-%d %H:%i:%s') //Date可为输入时间

//达梦：
to_char(Date,'YYYY-MM-DD HH24:MI:SS') //Date可为输入时间
//或
to_date(Date,'YYYY-MM-DD HH24:MI:SS') //Date可为输入时间
//或
date_format(Date,'%Y-%m-%d %H:%i:%s') //Date可为输入时间

//PostgreSQL:
to_date(Date,'YYYY-MM-DD HH24:MI:SS') //Date可为输入时间
//或
to_char(Date,'YYYY-MM-DD HH24:MI:SS')

20.查询两张表相同的数据

SELECT
	e.ename,
	e.eid 
FROM
	emp AS e,
	student AS s 
WHERE
	e.ename = s.ename 
	AND e.eid = s.eid

21.时间格式的数据查询

select  id,
            name,
            date,
            content,
            channel
        from wisdom_wind_complaint
        <where>
            <if test="date!=null and date!=''">
                and to_char(date,'YYYY-MM-dd') =#{date}
            </if>
            <if test="name!=null and name!=''">
                and name like ('%'||#{name}||'%')
            </if>
        </where>

22.xml里面的<需要转义

to_char(isolation_plan_end_time,'YYYY-mm-dd') &lt;=#{days}

23.判断两个表包含的数据是否相同

select * from emp where no>30 except select * from emp where no>50

24.将null值赋值

select coalesce(success_cnt, 1) from tableA

25.插入数据时插入默认值

INSERT into dept values (1,DEFAULT,'ddddd ')

26.复制表结构不复制数据

CREATE TABLE dept2 as SELECT * FROM dept where 1=0

27.只允许向特定的列添加数据

CREATE VIEW emp_new as SELECT deptno ,dname from dept 

设置权限的时候只允许对这个视图具有编辑权限

28.pgsql integer类型超出边界

select cast(10*1024*1024*1024  as bigint)

29.将字符串转换成数组

string_to_array(spi.house_code, '#')

30.ang和all

LEFT JOIN sys_apartment_info sai ON sai.house_code = any (string_to_array(spi.house_code, '#'))

31.sql返回list集合，子查询

1.返回对象
<resultMap id="BaseResultMap" type="io.renren.modules.project.unit.entity.WisdomUnitEntity">
        <id column="id" jdbcType="BIGINT" property="id" />
        <result column="unit_name" property="unitName"  jdbcType="VARCHAR" />
        <result column="bashichu_code" property="bashichuCode"  jdbcType="VARCHAR"  />
        <result column="community_code" property="communityCode"  jdbcType="VARCHAR" />
        <result column="grid_code" property="gridCode"  jdbcType="VARCHAR"  />
        <result column="address" property="address" jdbcType="VARCHAR"  />
        <result column="property_company" property="propertyCompany"  jdbcType="VARCHAR"  />
        <result column="property_phone" property="propertyPhone"   jdbcType="VARCHAR" />
        <result column="manage_name" property="manageName" jdbcType="VARCHAR"  />
 </resultMap>
2.返回对象集合
<select id="getDualPhoto" resultType="io.renren.project.building.storiedbuilding.dto.WisdomUnitDTO">
        select wu.*
            from wisdom_commercial_building as wcb
            inner join wisdom_unit as wu on wcb.building_name=wu.business_building_name where baimo_id=#{baimoId}
    </select>
    <resultMap id="BaseResultMap" type="io.renren.project.building.storiedbuilding.dto.WisdomCommercialBuildingDTO" autoMapping="true">
        <collection property="wisdomUnitDTOS" column="baimo_id"
                    ofType="io.renren.project.building.storiedbuilding.dto.WisdomUnitDTO"
                    select="getDualPhoto"/>
    </resultMap>
    <select id="listCommerciaBuilding"
            resultMap="BaseResultMap">
        select  *,
        (SELECT count(*) FROM wisdom_commercial_building as wcb
               inner join wisdom_unit as wu on wcb.building_name=wu.business_building_name  where baimo_id=#{baimoId}) as number,
        (SELECT sum(number_of_employees) FROM wisdom_commercial_building as wcb
               inner join wisdom_unit as wu on wcb.building_name=wu.business_building_name  where baimo_id=#{baimoId}) as sum
        from wisdom_commercial_building  where baimo_id=#{baimoId}
    </select>

32.获取pgsql中心点坐标

ST_Centroid

ST_Within(st_centroid(wztgt.geom),wztgo.geom)

33.将多个字段转换成json数据

select students.*, array_to_json( array_agg (row_to_json(scores))) AS JsonArray
from students
left join scores on students.id = scores.student_id
group by students.id

34.字符串截取

1. SPLIT_PART
SPLIT_PART() 函数通过指定分隔符分割字符串，并返回第N个子串。语法：

SPLIT_PART(string, delimiter, position)

string : 待分割的字符串
delimiter：指定分割字符串
position：返回第几个字串，从1开始，该参数必须是正数。如果参数值大于分割后字符串的数量，函数返回空串。
示例：

35.指定字段分组实现多行合并成一条数据

mysql：distinct去重

select demo_table.name, group_concat(distinct major separator ',') as combine_name
from demo_table
group by name

pgsql：distinct去重

select name ,
       array_to_string(array (select unnest(array_agg(distinct major))), '，') as combination_name
from demo_table
group by name

36.连表更新数据

UPDATE 要更新的表
SET 字段1 = cqt.字段1,
 字段2 = cqt.字段2,

FROM
    数据来源表 cqt
WHERE
    要更新的表.bsm = cqt.bsm

37.手机号加密

SELECT CONCAT(LEFT(phone_number, 3), '****', 
RIGHT(phone_number, 4)) AS masked_phone_number 
FROM table_name;

38.时间格式

SELECT * FROM "wisdom_flow_record" where create_date >=  '2023-07-11'::timestamp

39.xml格式化字符串

 <if test="serviceId == '1426030897226063874'.toString">
                srl.is_export,
            </if>

40.sql中where可以使用传参作为判断条件

update seata_storage set total = total - #{count}
            where commodity_code = #{commodityCode} and (total - #{count}) >= 0

41.字符串转日期格式排序

SELECT * FROM "wisdom_qywx_record" ORDER BY to_timestamp(msgtime, 'YYYY-MM-DD HH24:MI:SS.FF')  DESC

42.一条数据属于多个标签，如何返回数据问题

SELECT
            concat(#{label},"id") as "id"
        FROM
            wisdom_dq_interest_point
        WHERE
            layer_name = #{layerName}
            AND labels LIKE CONCAT('%',#{label},'%')
            AND status = '1'
            <if test="gridCodeList!=null and gridCodeList.size()>0">
                AND grid_code in
                <foreach collection="gridCodeList" item="item" separator="," open="(" close=")">
                    cast(#{item} as varchar)
                </foreach>
            </if>
        ORDER BY
        sort desc, status DESC,"id"
        LIMIT 9
//返回数据时带上标签信息 例如：标签id+自身id

43.连表更新

PostgreSQL两表关联更新SQL格式如下：

UPDATE 要更新的表

SET 字段1 = ft.字段1,

字段2 = ft.字段2

FROM

数据来源表ft

WHERE

要更新的表.字段 = ft.字段;

PostgreSQL两表关联更新SQL代码举例：

UPDATE user

SET username = ft.name,

age = ft.age

FROM

userinfo

WHERE

user.id = ft.id;

44.两表数据合并

一、区别1：取结果的交集

1、union: 对两个结果集进行并集操作, 不包括重复行,相当于distinct, 同时进行默认规则的排序;

2、union all: 对两个结果集进行并集操作, 包括重复行, 即所有的结果全部显示, 不管是不是重复;

二、区别2：获取结果后的操作

1、union: 会对获取的结果进行排序操作

2、union all: 不会对获取的结果进行排序操作

SELECT NULL AS ID
	,
	'@all' AS userid,
	NULL AS openid,
	NULL AS "position",
	'全部' AS NAME,
	NULL AS TYPE,
	NULL AS UPDATE,
	NULL AS update_date,
	NULL AS create_date,
	NULL AS creator,
	'' AS chat_id UNION ALL
SELECT
	* 
FROM
	qywx_user_info 
WHERE
	chat_id =#{ chatId}

45.参数作为固定值

SELECT NULL AS ID
                ,
               '@all' AS userid,
               NULL AS openid,
               NULL AS "position",
               '全部' AS NAME,
               NULL AS TYPE,
               NULL AS UPDATE,
            NULL AS update_date,
            NULL AS create_date,
            NULL AS creator,
           '${chatId}' AS chat_id UNION ALL
        select * from qywx_user_info where chat_id = #{chatId}

46.sql返回map顺序变更：使用linkedHashMap接收

<select id="selectGather" resultType="java.util.LinkedHashMap" ">
    SELECT *
    FROM  user
    ORDER BY createtime DESC LIMIT 0, 1
</select>

47.字符串转数组

string_to_array( '1707231306494468097,1707231306519633921', ',' )

48.包含运算符

SELECT 
*
	
 FROM "qywx_group_send_info" 

 WHERE	
 string_to_array("substring"( "replace"(message_type, '"', ''), 2, "length"("replace"(message_type, '"', ''))-2), ',') 
 @>  string_to_array('text,link', ',')

49.xml<if>标签传入整数值后都会修改成false值；只有字符串才能使用!=''

<if test="distance = null">
                            st_asgeojson (st_transform(geom, 3857)) :: json AS "geometry",
                        </if>
                        <if test="distance != null ">
                            st_asgeojson ( ST_Buffer ( st_transform(geom, 3857), ${distance})) :: json AS "geometry",
                        </if>

50.pg数据库添加postgis模块

-- 启用postgis插件
CREATE EXTENSION postgis;
-- 启用其他拓展（可选）
CREATE EXTENSION postgis_topology;
CREATE EXTENSION fuzzystrmatch;
CREATE EXTENSION postgis_tiger_geocoder;

51.获取今天日期

 DATE('now') + time '22:00'

52：格式转换

SELECT
            *
        FROM
            "qywx_group_send_info"
        WHERE
            send_type = '3'
          AND create_date::DATE  = current_date

53：数据转json，取出其中一个key的数据

代码：
SELECT '[{"a":1},{"b":2},{"c":3}]'::JSON -> 1 -> 'b';
 
结果：
2

54：null值排序问题

PostgreSQL提供了NULLS FIRST和NULLS LAST来控制null值排在前面还是后面

55:字符串某一个字符串出现的次数

SELECT
	"sum" ( num1 ) 
FROM
	( SELECT ( "length" ( 字段 ) - LENGTH ( REPLACE ( 字段, '出现的字符串', '' ) ) ) / 14 AS numl FROM "表明" ) A

56：null值排序

ORDER BY employment_time  desc nulls last

57：pgsql递归

WITH RECURSIVE P AS (
	SELECT
		t1.* 
	FROM
		sys_layer t1  WHERE id=1
	
 UNION ALL
	SELECT
		t2.* 
	FROM
		sys_layer t2
		INNER JOIN P ON t2.ID = P.pid 
	) SELECT *
FROM
P

58：返回json时为空数则设置为null

@JsonInclude(JsonInclude.Include.NON_EMPTY)

59：一个字段按照，分隔拆分成多个数据

SELECT
            "count"(*) as PolicyNum ,
            "unnest"(REGEXP_SPLIT_TO_ARRAY(categorize_question, ',')) as PolicyType
        FROM
            "wisdom_knowledge_policy"
        GROUP BY type

60：:: json ->> 查询报 ‘->>’错误

@InterceptorIgnore(tenantLine = "true")
TableVO getTable(tableName);

该注解的用处看字面意思应该是忽略了MyBatisPlus的拦截器，问题得以解决

61：xml字符分隔，参数转数组

<if test="dataSourceInclude!=null and dataSourceInclude!=''">
            and data_source in
            <foreach collection="dataSourceInclude.split(',')" item="dataSource" open="(" separator="," close=")">
                #{dataSource,jdbcType=VARCHAR}
            </foreach>
        </if>

62：数据库数组与参数求并集

and string_to_array(t2.dept_id,',') &amp;&amp; string_to_array(#{deptIdList},',')

63：求某个字段是某个值的和

and string_to_array(t2.dept_id,',') &amp;&amp; string_to_array(#{deptIdList},',')

64：有相同数据的时候去重

(SELECT t1.* FROM
        adm.wisdom_yfw_people_deleted t1
        INNER JOIN ( SELECT MAX( id ) AS id FROM adm.wisdom_yfw_people_deleted GROUP BY id_card ) t2 ON t1.id = t2.id)

65：获取数据库自增主键值

usegeneratedkeys=”true” keyproperty=”id”

66：if（）语句判断性别

 select if(sex=1,'男性','女性')
from user

67：ifnull('1','2')(mysql中使用)

68：sql优化

not exits 比 not in 的效率更高

当主表较小子查询较大的时候 not in 效率更高

69:面内中心点

ST_PointOnSurface

70：日期格式数据提取年份

EXTRACT ( YEAR FROM register_time )

71：choose when

 <choose>
                <when test="type == '政治数据'">
                    "wisdom_potential_politics"
                </when>
                <when test="type == '科技数据'">
                    "wisdom_potential_technology"
                </when>

72：查询条件在连接语句中的问题

select  MAX( id ) 
from adm.wisdom_yfw_people_deleted
 WHERE table_name = 'adm_yb_all_wjnybxx_df' and community_code = '410102010002' group by id_card 与 
select  * from (SELECT t1.* FROM
        adm.wisdom_yfw_people_deleted t1
        INNER JOIN ( SELECT MAX( id ) AS id FROM adm.wisdom_yfw_people_deleted GROUP BY id_card ) t2 ON t1.id = t2.id) t1
 where table_name ='adm_yb_all_wjnybxx_df' and community_code = '410102010002' 
 数量不一致

在inner join 子句中可能存在查询出的id_card 不在后面的where 条件内导致数据少

73：group by 和 where 条件速度问题

在单表中

SELECT count(*),layer_name,'wisdom_ct_xqd_duijie_dlss' tname FROM "wisdom_ct_xqd_duijie_dlss" GROUP BY layer_name

比条件查询后统一返回快

  select
            '${layerName}' as layerName,
            count(*) as count
        from ${tableName}
        where
            layer_name = #{layerName}

74：将查询出来的每条数据的值作为参数传递到子查询中，AND t2.`code` = hangar.brand

SELECT DISTINCT
	site.id siteId,
	site.`code` siteCode,
	site.`name` siteName,
	site.lng_lat_alt lngLatAlt,
	site.position,
	hangar.`code` hangarCode,
	hangar.NAME hangarName,
	hangar.brand hangarBrand,
	dick.label hangarBrandName,
	 hangar.model hangarModel,
	  task.task_flag AS taskFlag,
	task.id AS taskId,
	task.task_name AS taskName,
	 site.create_time,
	 uav.video_netaddress uavVideoNetaddress,
	 hangar.video_netaddress hangarVideoNetaddress,
	 video.video_netaddress siteVideoNetaddress,
	 (
	SELECT
		 t1.file_path 
	FROM
		sys_annex_file t1 
	WHERE
		t1.del_flag = 0  AND t1.busi_id IN ( SELECT t2.id FROM `sys_mul_dict` t2 WHERE t2.del_flag = 0 AND t2.`code` = hangar.brand ) 
	ORDER BY
		 t1.create_time DESC 
		LIMIT 1 
	) brandImage,
	 (
	SELECT
		 t1.file_path 
	FROM
		sys_annex_file t1 
	WHERE
		t1.del_flag = 0  AND t1.busi_id IN ( SELECT t2.id FROM `sys_mul_dict` t2 WHERE t2.del_flag = 0 AND t2.`code` = hangar.model ) 
	ORDER BY
		 t1.create_time DESC 
		LIMIT 1 
	) modelImage  
FROM
	bo_uav_site site