1. Prioridade de Operações no SQL
2. Funções de Agregação
As cinco funções de agregação comuns são: MAX, MIN, SUM, AVG e COUNT.
3. Subconsultas Escalares
Subconsultas que retornam um único valor podem ser usadas em expressões.
4. Funções de Janela
Permitem realizar cálculos em um conjunto de linhas relacionadas à linha atual.
5. Ordem de Execução das Consultas SQL
t.codigo_localizacao =#{codigoLocalizacao,jdbcType=VARCHAR}
6. Especificação de Tipos de Campo
É fundamental definir os tipos de dados corretos para cada campo.
7. Otimização de Consultas SQL
<sql id="ListaDeColunasBase">
id, nome, primeira_letra, ordem
</sql>
<select>
select
<include refid="ListaDeColunasBase" />
from pms_marca
</select>
Equivalente a:
<select>
select id, nome, primeira_letra, ordem
from pms_marca
</select>
8. Concatenação de Múltiplas Colunas
É possível concatenar colunas do mesmo tipo de dados para retornar um único resultado.
Método 1:
select nome_funcionario|| 'trabalha como '|| cargo as mensagem
from empregos
where departamento=10
Método 2:
SELECT concat(nome_ponto || 'endereco_ponto como A '|| observacao) as mensagem
FROM "pontos_interesse_wisdom"
9. Lógica Condicoinal em SQL
SELECT id, nome_ponto,
case when id<60 then 'ATUALIZAR'
when id >100 then 'outro'
else 'GRANDE'
end as status
FROM "pontos_interesse_wisdom"
10. Limitando o Número de Linhas Retornadas
SELECT id, nome_ponto,
case when id<60 then 'ATUALIZAR'
when id >100 then 'outro'
else 'GRANDE'
end as status
FROM "pontos_interesse_wisdom"
LIMIT 10
11. Retorno de Linhas Aleatórias
SELECT id, nome_ponto,
case when id<60 then 'ATUALIZAR'
when id >100 then 'outro'
else 'GRANDE'
end as status
FROM "pontos_interesse_wisdom"
ORDER BY random() LIMIT 10
12. Substituição de Valores Nulos
SELECT coalesce(id_ponto,'11') FROM "pontos_interesse_wisdom"
13. Funções de Tradução e Substituição
select dados
from visao
order by replace(dados,
replace(
translate(dados,'0123456789','##########'),'#',''),''))
14. Verificação de Comprimento de Dados
SELECT * from info_pessoa
WHERE length(cpf)!=18
15. Ordenação com Valores Nulos
SELECT
nome_funcionario,
salario,
comissao
FROM
( SELECT nome_funcionario, salario, comissao, CASE WHEN comissao IS null entao 0 ELSE 1 AS is_null FROM empregados ) AS x
ORDER BY
is_null,
comissao
16. Formatação de Data em DTO:
@JsonFormat(pattern = DateUtils.PADRAO_DATA_HORA)
17. Ordenação por Dois Campos
SELECT nome_funcionario ,salario,cargo,comissao
from
empregados order by case when cargo='VENDEDOR'entao comissao else salario end
18. Dados da Última Semana
//postreSQL
DataAlarme >= now() - interval '10 dia'
SELECT count(*) FROM politicas_conhecimento WHERE data_criacao >= date_trunc( 'mes', now( ) )
//达梦、MySQL
DATE_SUB(curdate(), INTERVAL '10' DAY) <= DataAlarme
19. Funções de Tratamento de Datas
//MySQL：
convert(Data,'tipo_desejado') //Data pode ser tempo de entrada
//ou
date_format('valor','%Y-%m-%d %H:%i:%s') //Data pode ser tempo de entrada
//达梦：
to_char(Data,'YYYY-MM-DD HH24:MI:SS') //Data pode ser tempo de entrada
//ou
to_date(Data,'YYYY-MM-DD HH24:MI:SS') //Data pode ser tempo de entrada
//ou
date_format(Data,'%Y-%m-%d %H:%i:%s') //Data pode ser tempo de entrada
//PostgreSQL:
to_date(Data,'YYYY-MM-DD HH24:MI:SS') //Data pode ser tempo de entrada
//ou
to_char(Data,'YYYY-MM-DD HH24:MI:SS')
20. Consulta de Dados Comuns em Duas Tabelas
SELECT
e.nome_funcionario,
e.id_funcionario
FROM
empregados AS e,
alunos AS s
WHERE
e.nome_funcionario = s.nome_funcionario
AND e.id_funcionario = s.id_funcionario
21. Consulta com Formatação de Tempo
select id,
nome,
data,
conteudo,
canal
from reclamacoes_vento
<where>
<if test="data!=null and data!=''">
and to_char(data,'YYYY-MM-dd') =#{data}
</if>
<if test="nome!=null and nome!=''">
and nome like ('%'||#{nome}||'%')
</if>
</where>
22. Escape de Caracteres em XML
to_char(tempo_fim_plano_isolamento,'YYYY-mm-dd') <=#{dias}
23. Verificação de Igualdade entre Tabelas
select * from empregados where id>30 except select * from empregados where id>50
24. Atribuição de Valores para Nulos
select coalesce(conta_sucesso, 1) from tabelaA
25. Inserção com Valores Padrão
INSERT into dept values (1,DEFAULT,'ddddd ')
26. Cópia de Estrutura de Tabela sem Dados
CREATE TABLE dept2 as SELECT * FROM dept where 1=0
27. Restrição de Edição a Colunas Específicas
CREATE VIEW empregados_novos as SELECT cod_depto ,nome_depto from dept
Ao definir permissões, permitir apenas edição nesta visualização
28. Limite de Tipo Integer no pgsql
select cast(10*1024*1024*1024 as bigint)
29. Conversão de String para Array
string_to_array(codigo_imovel, '#')
30. Operadores ANY e ALL
LEFT JOIN info_apartamento sai ON sai.codigo_imovel = any (string_to_array(spi.codigo_imovel, '#'))
31. SQL Retornando Listas com Subconsultas
1. Retornando objeto
<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. Retornando coleção de objetos
<select id="getDualPhoto" resultType="io.renren.project.building.storiedbuilding.dto.WisdomUnitDTO">
select wu.*
from prédio_comercial as wcb
inner join unidade as wu on wcb.nome_prédio=wu.nome_prédio_comercial 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 prédio_comercial as wcb
inner join unidade as wu on wcb.nome_prédio=wu.nome_prédio_comercial where baimo_id=#{baimoId}) as numero,
(SELECT sum(numero_funcionarios) FROM prédio_comercial as wcb
inner join unidade as wu on wcb.nome_prédio=wu.nome_prédio_comercial where baimo_id=#{baimoId}) as soma
from prédio_comercial where baimo_id=#{baimoId}
</select>
32. Obtenção de Ponto Central no pgsql
ST_Centroide
ST_Within(st_centroid(geometria_origem),geometria_destino)
33. Conversão de Múltiplos Campos para JSON
select alunos.*, array_to_json( array_agg (row_to_json(notas))) AS JsonArray
from alunos
left join notas on alunos.id = notas.id_aluno
group by alunos.id
34. Substring e Divisão de Strings
1. DIVIDIR_PARTES
A função DIVIDIR_PARTES() divide uma string usando um delimitador específico e retorna a N-ésima parte. Sintaxe:
DIVIDIR_PARTES(string, delimitador, posição)
string : String a ser dividida
delimitador: Caractere usado como separador
posição: Qual parte retornar (começa em 1), deve ser um número positivo. Se o valor exceder o número de partes, retorna string vazia.
Exemplo:
35. Agrupamento por Campo com Combinação de Linhas
MySQL: distinct para remoção de duplicatas
select demo_table.nome, group_concat(distinct area separator ',') as nome_combinado
from demo_table
group by nome
Pgsql: distinct para remoção de duplicatas
select nome ,
array_to_string(array (select unnest(array_agg(distinct area))), '，') as nome_combinado
from demo_table
group by nome
36. Atualização com Junção de Tabelas
UPDATE tabela_alvo
SET campo1 = fonte.campo1,
campo2 = fonte.campo2,
FROM
tabela_fonte fonte
WHERE
tabela_alvo.codigo = fonte.codigo
37. Mascaramento de Número de Telefone
SELECT CONCAT(LEFT(numero_telefone, 3), '****',
RIGHT(numero_telefone, 4)) AS numero_telefone_mascarado
FROM tabela_contatos;
38. Formatação de Data
SELECT * FROM "registro_fluxo" where data_criacao >= '2023-07-11'::timestamp
39. Formatação de String em XML
<if test="serviceId == '1426030897226063874'.toString">
srl.is_export,
</if>
40. Uso de Parâmetros em Condições WHERE
update estoque_produto set total = total - #{quantidade}
where codigo_produto = #{codigoProduto} and (total - #{quantidade}) >= 0
41. Ordenação por Data Convertida de String
SELECT * FROM "registros_qywx" ORDER BY to_timestamp(tempo_msg, 'YYYY-MM-DD HH24:MI:SS.FF') DESC
42. Dados com Múltiplos Rótulos
SELECT
concat(#{rotulo},"id") as "id"
FROM
pontos_interesse_dq
WHERE
nome_camada = #{nomeCamada}
AND rotulos LIKE CONCAT('%',#{rotulo},'%')
AND status = '1'
<if test="listaCodGrade!=null and listaCodGrade.size()>0">
AND codigo_grade in
<foreach collection="listaCodGrade" item="item" separator="," open="(" close=")">
cast(#{item} as varchar)
</foreach>
</if>
ORDER BY
ordem desc, status DESC,"id"
LIMIT 9
43. Atualização com Junção de Tabelas
PostgreSQL formato de SQL para atualização com junção:
UPDATE tabela_alvo
SET campo1 = ft.campo1,
campo2 = ft.campo2
FROM
tabela_fonte ft
WHERE
tabela_alvo.campo = ft.campo;
Exemplo de código:
UPDATE usuario
SET nome_usuario = ft.nome,
idade = ft.idade
FROM
informacoes_usuario
WHERE
usuario.id = ft.id;
44. Combinação de Dados de Duas Tabelas
Diferença 1: Obter a interseção dos resultados
- union: Realiza união de dois conjuntos de resultados, sem linhas duplicadas, equivalente a distinct, com ordenação padrão;
- union all: Realiza união de dois conujntos de resultados, incluindo linhas duplicadas, ou seja, todos os resultados são exibidos, independentemente de serem duplicados;
Diferença 2: Operações após obter os resultados
- union: Ordena os resultados obtidos
- union all: Não ordena os resultados obtidos
SELECT NULL AS ID
,
'@todos' AS id_usuario,
NULL AS openid,
NULL AS "posicao",
'Todos' AS NOME,
NULL AS TIPO,
NULL AS ATUALIZACAO,
NULL AS data_atualizacao,
NULL AS data_criacao,
NULL AS criador,
'' AS chat_id UNION ALL
SELECT
*
FROM
informacoes_usuario_qywx
WHERE
chat_id =#{ chatId}
45. Parâmetros como Valores Fixos
SELECT NULL AS ID
,
'@todos' AS id_usuario,
NULL AS openid,
NULL AS "posicao",
'Todos' AS NOME,
NULL AS TIPO,
NULL AS ATUALIZACAO,
NULL AS data_atualizacao,
NULL AS data_criacao,
NULL AS criador,
'${chatId}' AS chat_id UNION ALL
select * from informacoes_usuario_qywx where chat_id = #{chatId}
46. Alteração da Ordem de Retorno do SQL: Usando LinkedHashMap
<select id="selectGather" resultType="java.util.LinkedHashMap" ">
SELECT *
FROM usuario
ORDER BY horacriacao DESC LIMIT 0, 1
</select>
47. Conversão de String para Array
string_to_array( '1707231306494468097,1707231306519633921', ',' )
48. Operador de Inclusão
SELECT
*
FROM "info_envio_grupo_qywx"
WHERE
string_to_array("substring"( "replace"(tipo_mensagem, '"', ''), 2, "length"("replace"(tipo_mensagem, '"', ''))-2), ',')
@> string_to_array('texto,link', ',')
49. Problema com Tag em XML ao Passar Valores Inteiros
<if test="distance = null">
st_asgeojson (st_transform(geometria, 3857)) :: json AS "geometria",
</if>
<if test="distance != null ">
st_asgeojson ( ST_Buffer ( st_transform(geometria, 3857), ${distance})) :: json AS "geometria",
</if>
50. Adição do Módulo Postgis no Banco de Dados PostgreSQL
-- Habilitando extensão postgis
CREATE EXTENSION postgis;
-- Habilitando outras extensões (opcional)
CREATE EXTENSION postgis_topology;
CREATE EXTENSION fuzzystrmatch;
CREATE EXTENSION postgis_tiger_geocoder;
51. Obtenção da Data de Hoje
DATE('now') + time '22:00'
52: Conversão de Formato
SELECT
*
FROM
"info_envio_grupo_qywx"
WHERE
tipo_envio = '3'
AND data_criacao::DATE = current_date
53: Conversão de Dados para JSON e Extração de Chave
Código:
SELECT '[{"a":1},{"b":2},{"c":3}]'::JSON -> 1 -> 'b';
Resultado:
2
54: Problema de Ordenação com Valores Nulos
PostgreSQL fornece NULLS FIRST e NULLS LAST para controlar se valores nulos aparecem primeiro ou por último
55: Contagem de Ocorrências de uma Substring
SELECT
"sum" ( num1 )
FROM
( SELECT ( "length" ( campo ) - LENGTH ( REPLACE ( campo, 'substring', '' ) ) ) / 14 AS numl FROM "tabela" ) A
56: Ordenação com Valores Nulos
ORDER BY tempo_emprego desc nulls last
57: Recursividade em pgsql
WITH RECURSIVO P AS (
SELECT
t1.*
FROM
camada_sistema t1 WHERE id=1
UNION ALL
SELECT
t2.*
FROM
camada_sistema t2
INNER JOIN P ON t2.ID = P.pid
) SELECT *
FROM
P
58: Retorno JSON como Null para Arrays Vazios
@JsonInclude(JsonInclude.Include.NON_EMPTY)
59: Divisão de Campo por Vírgula em Múltiplos Dados
SELECT
"count"(*) as NumPoliticas ,
"unnest"(REGEXP_SPLIT_TO_ARRAY(categoria_pergunta, ',')) as TipoPolitica
FROM
"politicas_conhecimento_wisdom"
GROUP BY tipo
60: Erro com '->>' em Consulta JSON
@InterceptorIgnore(tenantLine = "true") TableVO getTable(nomeTabela);
Esta anotação, pelo seu significado literal, deve ignorar o interceptor do MyBatisPlus, resolvendo o problema
61: Separação de Caracteres em XML com Parâmetro Array
<if test="dataSourceInclude!=null and dataSourceInclude!=''">
and fonte_dados in
<foreach collection="dataSourceInclude.split(',')" item="fonte" open="(" separator="," close=")">
#{fonte,jdbcType=VARCHAR}
</foreach>
</if>
62: União entre Array do Banco e Parâmetro
and string_to_array(t2.id_depto,',') && string_to_array(#{listaIdDepto},',')
63: Soma de Valores Específicos em Campo
and string_to_array(t2.id_depto,',') && string_to_array(#{listaIdDepto},',')
64: Remoção de Duplicatas com Mesmos Dados
(SELECT t1.* FROM
adm.pessoas_excluidas_wisdom t1
INNER JOIN ( SELECT MAX( id ) AS id FROM adm.pessoas_excluidas_wisdom GROUP BY id_documento ) t2 ON t1.id = t2.id)
65: Obtenção do Valor da Chave Primária Autoincrementada
usegeneratedkeys="true" keyproperty="id"
66: Declaração if() para Gênero
select if(sexo=1,'Masculino','Feminino')
from usuario
67: ifnull('1','2')(usado no MySQL)
68: Otimização de SQL
NOT EXISTS é mais eficiente que NOT IN Quando a tabela principal é menor e a subconsulta maior, NOT IN é mais eficiente
69: Ponto Central em Polígono
ST_PontoSuperficie
70: Extração do Ano de Dados de Data
EXTRACT ( YEAR FROM tempo_registro )
71: Estrutura choose when
<choose>
<when test="tipo == 'dados_politicos'">
"dados_potenciais_politicos_wisdom"
</when>
<when test="tipo == 'dados_tecnicos'">
"dados_potenciais_tecnologia_wisdom"
</when>
72: Problema com Condição de Consulta em Junção
select MAX( id )
from adm.pessoas_excluidas_wisdom
WHERE nome_tabela = 'adm_yb_todas_wjnybxx_df' and codigo_comunidade = '410102010002' group by id_documento versus
select * from (SELECT t1.* FROM
adm.pessoas_excluidas_wisdom t1
INNER JOIN ( SELECT MAX( id ) AS id FROM adm.pessoas_excluidas_wisdom GROUP BY id_documento ) t2 ON t1.id = t2.id) t1
where nome_tabela ='adm_yb_todas_wjnybxx_df' and codigo_comunidade = '410102010002'
Contagem inconsistente
Na cláusula inner join, pode haver id_documento não incluídos nas condições posteriores WHERE, resultando em menos dados
73: Problema de Velocidade entre GROUP BY e WHERE
Em tabela única
SELECT count(*),nome_camada,'wisdom_ct_xqd_conexao_dlss' tname FROM "wisdom_ct_xqd_conexao_dlss" GROUP BY nome_camada
É mais rápido que consulta condicional e retorno unificado
select
'${nomeCamada}' as nomeCamada,
count(*) as count
from ${nomeTabela}
where
nome_camada = #{nomeCamada}
74: Passagem de Valores como Parâmetros em Subconsultas
SELECT DISTINCT
site.id siteId,
site.`codigo` siteCodigo,
site.`nome` siteNome,
site.lng_lat_alt lngLatAlt,
site.posicao,
hangar.`codigo` hangarCodigo,
hangar.NOME hangarNome,
hangar.marca hangarMarca,
dick.label hangarMarcaNome,
hangar.modelo hangarModelo,
task.tarefa_flag AS tarefaFlag,
task.id AS taskId,
task.tarefa_nome AS tarefaNome,
site.criacao_tempo,
uav.video_endereco_rede uavVideoEndereco,
hangar.video_endereco_rede hangarVideoEndereco,
video.video_endereco_rede siteVideoEndereco,
(
SELECT
t1.caminho_arquivo
FROM
arquivo_anexo_sistema t1
WHERE
t1.del_flag = 0 AND t1.busi_id IN ( SELECT t2.id FROM `multipla_dict_sistema` t2 WHERE t2.del_flag = 0 AND t2.`codigo` = hangar.marca )
ORDER BY
t1.criacao_tempo DESC
LIMIT 1
) marcaImagem,
(
SELECT
t1.caminho_arquivo
FROM
arquivo_anexo_sistema t1
WHERE
t1.del_flag = 0 AND t1.busi_id IN ( SELECT t2.id FROM `multipla_dict_sistema` t2 WHERE t2.del_flag = 0 AND t2.`codigo` = hangar.modelo )
ORDER BY
t1.criacao_tempo DESC
LIMIT 1
) modeloImagem
FROM
site_uav site