SQL语句整理三

小强签名设计

已于 2023-12-18 16:18:46 修改

阅读量1.5k

点赞数

于 2020-05-07 10:43:17 首次发布

本文链接：https://blog.csdn.net/m0_37739193/article/details/105967532

版权

数据库专栏收录该内容

15 篇文章 3 订阅

订阅专栏

查看版本号：

mysql数据库: select version()

oracle数据库: select * from v$instance

sqlserver数据库: SELECT @@VERSION

SqlServer

创建临时表：

select * into #haha from heheda where id>9;

复制表数据到另一个表：

SQL Server中，如果目标表存在：
insert into 目标表 select * from 原表;

SQL Server中，如果目标表不存在：
select * into 目标表 from 原表;

Oracle中，如果目标表存在：
insert into 目标表 select * from 原表;
commit;

Oracle中，如果目标表不存在：
create table 目标表 as select * from 原表;

update set from的用法：

update t1
set 
    bo_user_id=d.bo_user_id
from
    vlc_cdo_vehicle_relation_20201124_2 t1
inner join
    #tmp5 d
on
    t1.fin_code=d.fin_code
where
    t1.bo_user_id=0 and t1.dealer_id=327 and d.bo_user_id is not null;

exists用法：

select 
    id,dmp_id 
from 
    vlc_bundles a 
where 
    exists (select dmp_id,count(*) from vlc_bundles b where a.dmp_id=b.dmp_id group by dmp_id having count(1)>1);

delete exists select用法：

delete from vlc_bundles a 
where 
    exists (select id,dmp_id,count(*) from vlc_bundles b where a.dmp_id=b.dmp_id group by dmp_id,id having count(1)>1);

查字符串长度函数：

select len('dsd');

json_value用法：

当表中某个字段存的值是json格式时内容太多只想查看其中一个字段的值：
select json_value(profile,'$.close_reason'),json_value(profile,'$.dmp_push.tag_list_raw[0][0]')
,json_value(profile,'$.dmp_push.tag_name_values') from vlc_bundles;

参考：https://docs.microsoft.com/en-us/sql/t-sql/functions/json-value-transact-sql?view=sql-server-ver15

convert函数：

对于简单类型转换，CONVERT()函数和CAST()函数的功能相同，只是语法不同。CAST()函数一般更容易使用，其功能也更简单。CONVERT()函数的优点是可以格式化日期和数值，它需要两个参数：第1个是目标数据类型，第2个是源数据。以下是两个例子：

SELECT CONVERT(int, '123');
SELECT CONVERT(date, '2020-08-02');

-- 使用convert转换时间格式获取小时，并转成int类型
SELECT top 10 created_at,CONVERT(INT, CONVERT(varchar(2),created_at, 108)) FROM dbo.heheda;
结果：
2018-05-29 02:01:35.463	2
2019-11-02 10:12:35.463	10

执行存储过程语句：

exec [lf_jybtjcpg_yuan].[dbo].[PRO_491] @tjrq=201801;

使用循环执行存储过程：
declare @tjrq int   
set @tjrq=201311
while @tjrq<=201910
begin
	if (right(@tjrq,2)>=13 and right(@tjrq,2)<=99) or right(@tjrq,2)=00
	begin
		select '不是日期，不执行';
	end
	else
	begin
		exec [lf_jybtjcpg_yuan].[dbo].[PRO_421_1b] @tjrq;
	end
	set @tjrq=@tjrq +1 
end

DATEDIFF() 函数返回两个日期之间的时间：

SELECT DATEDIFF(day,'2008-12-29','2008-12-30') AS DiffDate
结果：1

语法：

DATEDIFF(datepart,startdate,enddate)

startdate 和 enddate 参数是合法的日期表达式。
datepart 参数可以是下列的值：

datepart	缩写
年	yy, yyyy
季度	qq, q
月	mm, m
年中的日	dy, y
日	dd, d
周	wk, ww
星期	dw, w
小时	hh
分钟	mi, n
秒	ss, s
毫秒	ms
微妙	mcs
纳秒	ns

中文查不出来的问题：

数据库中有中文，但是查询条件有中文怎么也查不出来，原来客户的数据库是英文版的，所以数据库中的字段值是Unicode编码。然后在查询时加入N即可，N是将其内容xxx作为 Unicode字符常量（双字节）。而没有N的 ‘yyy’，是将yyy 作为字符常量（单字节）。
如select * from table where city=N'北京';

报错：Cannot resolve the collation conflict

在同一个数据库中用两张表进行关联查询报错：
Cannot resolve the collation conflict between "SQL_Latin1_General_CP1_CI_AS" and "Chinese_PRC_CI_AS" in the equal to operation.

解决：
select * from vlc_bundles a left join bundles_dashboard b 
on a.dmp_id collate SQL_Latin1_General_CP1_CI_AS=b.dmp_id;

insert或者update的字段数据类型为datetime想插入空值的时候

不要写为''，否则插入之后该字段的值为’1900-01-01 00:00:00.000’，应该插入NULL，如：

update vlc_dealers set deleted_at=NULL where id=193;

快速查看表结构：

SELECT  CASE WHEN col.colorder = 1 THEN obj.name
                  ELSE ''
             END AS 表名,
        col.colorder AS 序号 ,
        col.name AS 列名 ,
        ISNULL(ep.[value], '') AS 列说明 ,
        t.name AS 数据类型 ,
        col.length AS 长度 ,
        ISNULL(COLUMNPROPERTY(col.id, col.name, 'Scale'), 0) AS 小数位数 ,
        CASE WHEN COLUMNPROPERTY(col.id, col.name, 'IsIdentity') = 1 THEN '√'
             ELSE ''
        END AS 标识即是否自增 ,
        CASE WHEN EXISTS ( SELECT   1
                           FROM     dbo.sysindexes si
                                    INNER JOIN dbo.sysindexkeys sik ON si.id = sik.id
                                                              AND si.indid = sik.indid
                                    INNER JOIN dbo.syscolumns sc ON sc.id = sik.id
                                                              AND sc.colid = sik.colid
                                    INNER JOIN dbo.sysobjects so ON so.name = si.name
                                                              AND so.xtype = 'PK'
                           WHERE    sc.id = col.id
                                    AND sc.colid = col.colid ) THEN '√'
             ELSE ''
        END AS 主键 ,
        CASE WHEN col.isnullable = 1 THEN '√'
             ELSE ''
        END AS 允许空 ,
        ISNULL(comm.text, '') AS 默认值
FROM    dbo.syscolumns col
        LEFT  JOIN dbo.systypes t ON col.xtype = t.xusertype
        inner JOIN dbo.sysobjects obj ON col.id = obj.id
                                         AND obj.xtype = 'U'
                                         AND obj.status >= 0
        LEFT  JOIN dbo.syscomments comm ON col.cdefault = comm.id
        LEFT  JOIN sys.extended_properties ep ON col.id = ep.major_id
                                                      AND col.colid = ep.minor_id
                                                      AND ep.name = 'MS_Description'
        LEFT  JOIN sys.extended_properties epTwo ON obj.id = epTwo.major_id
                                                         AND epTwo.minor_id = 0
                                                         AND epTwo.name = 'MS_Description'
WHERE   obj.name = 'TableName'--表名
ORDER BY col.colorder ;

查询所有存储过程：

select Pr_Name as [存储过程], [参数]=stuff((select '，'+[Parameter]
from (
select Pr.Name as Pr_Name,parameter.name +' ' +Type.Name + ' ('+convert(varchar(32),parameter.max_length)+')' as Parameter
from sys.procedures Pr left join
sys.parameters parameter on Pr.object_id = parameter.object_id
inner join sys.types Type on parameter.system_type_id = Type.system_type_id
where type = 'P'
) t where Pr_Name=tb.Pr_Name for xml path('')), 1, 1, '')
from (
select Pr.Name as Pr_Name,parameter.name +' ' +Type.Name + ' ('+convert(varchar(32),parameter.max_length)+')' as Parameter
from sys.procedures Pr left join
sys.parameters parameter on Pr.object_id = parameter.object_id
inner join sys.types Type on parameter.system_type_id = Type.system_type_id
where type = 'P'
)tb
where Pr_Name not like 'sp_%' --and Pr_Name not like 'dt%'
group by Pr_Name
order by Pr_Name;

查询表索引：

SELECT   索引名称=a.name 
,表名=c.name 
,索引字段名=d.name 
,索引字段位置=d.colid 
FROM   sysindexes   a 
JOIN   sysindexkeys   b   ON   a.id=b.id   AND   a.indid=b.indid 
JOIN   sysobjects   c   ON   b.id=c.id 
JOIN   syscolumns   d   ON   b.id=d.id   AND   b.colid=d.colid 
WHERE   a.indid   NOT IN(0,255) 
-- and   c.xtype='U'   and   c.status>0 --查所有用户表 
AND   c.name='bundles_dashboard' --查指定表 
ORDER BY   c.name,a.name,d.name

重建索引：

alter index pk_my_users on my_users rebuild;

可参考：https://blog.csdn.net/mituan1234567/article/details/7950573或者http://lusanxiong.iteye.com/blog/1544950

将表数据改造成insert语句：

方法一：

select 
	'INSERT INTO t_jczc_cbqktj(id,tjrq,sjlx,xzqbm,xzqmc,sybxfhj,sybxfjx,sybxfzdts,sybxffjx,sybxfqt) VALUES('+cast(id as varchar)+','''+tjrq+''','''+sjlx+''','''+xzqbm+''','''+xzqmc+''','''+sybxfhj+''','''+sybxfjx+''','''+sybxfzdts+''','''+sybxffjx+''','''+sybxfqt+''');' 
from 
	[dbo].[zb_xssybxfry_xzq];

-- 运行结果：
INSERT INTO t_jczc_cbqktj(id,tjrq,sjlx,xzqbm,xzqmc,sybxfhj,sybxfjx,sybxfzdts,sybxffjx,sybxfqt) VALUES(1081,'201907','1a','110101','东城区','0','0','0','0','0');
INSERT INTO t_jczc_cbqktj(id,tjrq,sjlx,xzqbm,xzqmc,sybxfhj,sybxfjx,sybxfzdts,sybxffjx,sybxfqt) VALUES(1082,'201907','1a','110102','西城区','0','0','0','0','0');

在这里插入图片描述
备注：
(1).经测试，该用法在SQLserver上可以，在gaussDb上不行
(2).这张表id字段为bigint类型，其他字段都为varchar类型，用+号拼接的时候会要求所有字段的数据类型一致，不一致的话它会自动强转
(3).两个单引号表示一个引号，如果用一个引号的话会导致成多列而不是一列
在这里插入图片描述
方法二：

select 
	concat('INSERT INTO t_jczc_cbqktj(id,tjrq,sjlx,xzqbm,xzqmc,sybxfhj,sybxfjx,sybxfzdts,sybxffjx,sybxfqt) VALUES(',id,',''',tjrq,''',''',sjlx,''',''',xzqbm,''',''',xzqmc,''',''',sybxfhj,''',''',sybxfjx,''',''',sybxfzdts,''',''',sybxffjx,''',''',sybxfqt,''');') 
from 
	[dbo].[zb_xssybxfry_xzq];

备注：
(1).经测试，该用法在SQLserver上可以，在gaussDb上也适用

需要注意的点：

1.在做多表查询，每一个派生出来的表都必须有一个自己的别名，例如：

错误写法：select count(1) from (select hehe from haha);
正确写法：select count(1) from (select hehe from haha) a;

2.使用having的时候别名无效，例如：

错误写法：select count(1) a,haha from hehe group by haha having a>1;
正确写法：select count(1) a,haha from hehe group by haha having count(1)>1;

Oracle

创建表之前如果表存在则删除：

declare
      num number;
begin
    select count(1) into num from dba_tables where table_name = upper('AAA_HEHEDA') ;
    if num > 0 then
        execute immediate 'drop table xiao_qiang.aaa_heheda' ;
    end if;
end;

java代码实现：

            int count = GaussUttils.countoracle(conn_gauss);
            if (count != 0) {
                GaussUttils.executeSqls(conn_gauss);
            }

    public static int countoracle(Connection conn){
        String sql = "select count(1) count from dba_tables where table_name = ('AAA_HEHEDA')";
        ResultSet set = null;
        int result = 0;
        try {
            Statement stmt = null;
            stmt = conn.createStatement();
            set = stmt.executeQuery(sql);
            if(set.next()){
                result = set.getInt("count");
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return result;
    }

    public static void executeSqls(Connection conn) {
        Statement stmt = null;
        try {
            stmt = conn.createStatement();
            stmt.execute("drop table xiao_qiang.aaa_heheda");
            stmt.close();
        } catch (SQLException e) {
            if (stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException e1) {
                    e1.printStackTrace();
                }
            }
            e.printStackTrace();
        }
    }

注意：在用Java执行oracle语句的时候双引号里面一定不要有分号，如stmt.execute("drop table xiao_qiang.aaa_heheda");不要写成stmt.execute("drop table xiao_qiang.aaa_heheda;");否则会报错“ORA-00911: 无效字符问题”，而在gauss的代码中加了分号也可以正常执行而不会报这个错误。

update和select结合使用：

(1)update tableName set (a,b,c)=(select a,b,c from ida where ida.id=tableName.id);
(2)update tableName t1 set a=(select t2.a from ida t2 where t1.id=t2.id),b=(select t2.b from ida t2 where t1.id=t2.id),c=(select t2.c from ida t2 where t1.id=t2.id);
(3)每条数据执行为：UPDATE tableName SET (A,B,C)=(select A,B,C from tableName where id=''xxxxxx) WHERE id='xxxxxxx';

with：

with 
hehe as
    (select * from xiao_ogg.t_heheda partition(jsrq_2019_12)),
haha as
    (select * from xiao_ogg.t_hahada partition(jsrq_2019_12) where TZBZ='1')
select 
    a.he,
    b.ha,
    case
        when b.qie is null then '否'
        else '是'
        end "小强签名设计"
from 
    xiao_ogg.t_qieda a 
left join 
    hehe b
on 
    a.gid = b.gid;

注意：with和insert into在Oracle和Hive中的不同写法

Oracle：
INSERT INTO TABLE_B
	WITH TEMP_A AS (
		SELECT TIME,IOT_ID,NAME FROM IOT_XX_A
	),TEMP_B AS (
		SELECT TIME,IOT_ID,NAME,COUNT(DISTINCT IOT_ID) AS TIMES FROM TEMP_A
			GROUP BY TIME,IOT_ID,NAME
	)
SELECT TIME,IOT_ID,NAME,TIMES FROM TEMP_B;

按照上面的SQL语句，在ORACLE会执行，而在hive呢?他就会报错，具体的错是：Error: Error while compiling statement: FAILED: ParseException line 2:0 cannot recognize input near 'WITH' 'TABLE_B' 'AS' in statement
错误的原因就是INSERT INTO 位置放得不合适。把INSERT INTO语句换一下位置就可以执行了。

Hive：
WITH TEMP_A AS (
		SELECT TIME,IOT_ID,NAME FROM IOT_XX_A
	),TEMP_B AS (
		SELECT TIME,IOT_ID,NAME,COUNT(DISTINCT IOT_ID) AS TIMES FROM TEMP_A
			GROUP BY TIME,IOT_ID,NAME
	)
INSERT INTO TABLE_B	
	SELECT TIME,IOT_ID,NAME,TIMES FROM TEMP_B;

add_months()函数：

ADD_MONTHS函数在输入日期上加上指定的几个月返回一个新的日期。如果给出一负数，返回值日期之前几个月日期。
ADD_MONTHS(DATE,NUMBER)中的NUMBER应当是整数，给出小数时，正数被截为小于该数的最大整数，负数被截为大于该数的最小整数。

例：add_months(to_date('29-Feb-96','d-mon-yyyy'),-12.99) 返回 28－Feb-95
注：上例中29调整为28，是因为96年二月份最后一天是29号，而95年二月份最后一天是28号。

add_months(to_date('15-Nov-1961','d-mon-yyyy'),1) 返回 15-Dec-1961
add_months(to_date('30-Nov-1961','d-mon-yyyy'),1) 返回 31-Dec-1961
注：从30调整为31，为了保持都是对应最后一天。

add_months(to_date('31-Jan-1999','d-mon-yyyy'),1) 返回 28-Feb-1999
注：函数将31日调为28日，以使结果对应新一月的最后一天，因1999年2月只有28天

例：从emp表查询列出来公司就职时间超过24年的员工名单
select ename, hiredate
from emp
where hiredate <= add_months(sysdate, -288);
注：负数代表系统时间（sysdate）之前的24年的时间-288 = -24*12

左连接数据变少：

前言：有一次我用两张表左关联后生成的结果比主表的数据量要少，一开始百思不得其解，后来终于想明白了。
原因：where加在了左关联之后，和放在左关联之前的结果数据量是不一样的。

select
    *
from    
    hehe a
left join
    haha b
on
    a.dwid=b.dwid
where
    a.bbyf='2020-07-01 00:00:00'
and
    b.bbyf='2020-07-01 00:00:00';
注：当关联后的b表的bbyf字段不满足where条件的时候，上面的结果数量会少于下面的
select
    *
from 
    (select * from hehe where bbyf='2020-07-01 00:00:00') a
left join
    (select * from haha where bbyf='2020-07-01 00:00:00') b
on
    a.dwid=b.dwid;

Doris

json：

Doris 支持 json 解析函数，提供了3个 json 解析函数，分别是 get_json_int（string，string）、get_json_string（string，string） 和 get_json_double（string，string）。第一个参数为 json 字符串，第二个参数为 json 内的路径。

示例：

SELECT get_json_string('{"id":1,"nickName":"小强"}','$.nickName'); -- json_extract函数不适用在Doris里
# 返回结果：小强

SELECT json_object("name","xiaoyu","old",12,"height","165.1");
# 返回结果：{"old": 12, "name": "xiaoyu", "height": "165.1"}

达梦

查询表的字段名和注释：

select B.COLUMN_NAME "column_name", case when B.COMMENTS is null then '' else B.COMMENTS end as "comment"
from DBA_TAB_COMMENTS A left join DBA_COL_COMMENTS B on A.TABLE_NAME=B.TABLE_NAME
where B.OWNER='模式名称' and A.TABLE_NAME='表名称'
group by B.COLUMN_NAME, B.COMMENTS.

小强签名设计

关注

0
点赞
踩
6

收藏

觉得还不错? 一键收藏
打赏
1
评论
SQL语句整理三

文章目录SqlServerDATEDIFF() 函数返回两个日期之间的时间：将表数据改造成insert语句：Mysql分组之前进行排序：查询媒体名称为"小强日报"的数据量：内连接：去重：beauty_article_view视图：创建表：日期和字符相互转换：开窗函数：Hive创建数据库：使用数据库：重命名表名：建表时判断该表是否存在：SqlServerDATEDIFF() 函数返回两个日期之间...
复制链接

扫一扫