☆常用的Sql语句汇总_DDL/DML)_Sql常用语句

作者：小编更新时间：2023-08-11 13:47:34 浏览量：354人看过

1、获取所有表名、表信息

里面有表注释

mysql

2、获取当前表的主表（外键关联的表）

就是这个表中的外键关联的表

☆常用的Sql语句汇总_DDL/DML)_Sql常用语句

select * from INFORMATION_SCHEMA.KEY_COLUMN_USAGE where TABLE_SCHEMA = "dev_test_data" and REFERENCED_TABLE_NAME in ('t_c_my_dept') AND REFERENCED_TABLE_NAME != ""

获取其从表

-- 根据表名获取 其从属表的名字
select c.table_name from user_cons_columns cl
left join user_constraints c on cl.constraint_name = c.r_constraint_name
where c.constraint_type = 'R'
and cl.table_name = 't_c_dept' and c.owner = 'CJY'

3、获取表的约束

根据表名、数据库名

SQL

SELECTconstraint_name,column_name,table_catalog,table_schema,referenced_table_schema,referenced_table_name,referenced_column_name,table_name

☆常用的Sql语句汇总_DDL/DML)_Sql常用语句

oracle

☆常用的Sql语句汇总_DDL/DML)_Sql常用语句

4、获取表的索引

☆常用的Sql语句汇总_DDL/DML)_Sql常用语句

5、case when then else end

测试表：
CREATE TABLE test.cc (
  id int  PRIMARY key IDENTITY(1,1),
  name varchar(255)  NULL,
  age int  NULL,
  country varchar(255)  NULL
)

需求

需求：
用一句sql查询出一张表中年龄<10和年龄≥10的
【提示：用内联；group by后面括号里面可以写逻辑】
【提示：用case when】

☆常用的Sql语句汇总_DDL/DML)_Sql常用语句

下面是实现sql（两种方式）

-- 1 查询年龄小于10、大于10的人数
select 
case
when age > 0 and age < 10 then 'age小于10'
when age >= 10 and age < 20 then 'age大于10'
else '其他'
end as '条件',
count(*) as '人数'
from test.t_user
group by 
case
when age > 0 and age < 10 then 'age小于10'
when age >= 10 and age < 20 then 'age大于10'
else '其他'
end;


-- 2 查询中国人、美国人、其他国家人的年龄和（第一种写法）
select sum(age) as '年龄和',
case name
when 'cc' then '中国人'
when 'dd' then '中国人'
when 'ee' then '中国人'
when 'ff' then '美国人'
when 'gg' then '美国人'
when 'hh' then '美国人'
else '其他国家'
end as '国家'
from test.t_user
group by 
case name
when 'cc' then '中国人'
when 'dd' then '中国人'
when 'ee' then '中国人'
when 'ff' then '美国人'
when 'gg' then '美国人'
when 'hh' then '美国人'
else '其他国家'
end;

-- 3 查询中国人、美国人、其他国家人的年龄和（第二种写法）
select sum(age) as '年龄和',
case country
when '中国' then '中国人'
when '美国' then '美国人'
else '其他国家'
end as '国家'
from test.t_user
group by 
case country
when '中国' then '中国人'
when '美国' then '美国人'
else '其他国家'
end;

TD_DI_WORK_TABLE 表中有表名字段 TARGET_TABLE_NAME

☆常用的Sql语句汇总_DDL/DML)_Sql常用语句

TD_DI_WORK_TABLE 中有NODE_ID 与 TD_DI_NODE 关联

TD_DI_NODE 中有WORK_TYPE类型,表名该表的构建类型

☆常用的Sql语句汇总_DDL/DML)_Sql常用语句

oracle连接字符串：|| 、concat

方式一：

SELECT
wt.TARGET_TABLE_NAME || 
(CASE
dn.WORK_TYPE 
WHEN 1 THEN '(采集)'
WHEN 2 THEN '(归一)'
WHEN 7 THEN '(订阅)'
ELSE '(主题)' 
END
) AS tableName
FROM
TD_DI_WORK_TABLE AS wt
LEFT JOIN TD_DI_NODE AS dn ON
dn.ID = wt.NODE_ID

方式二：

SELECT
CONCAT(
wt.TARGET_TABLE_NAME, 
(CASE
dn.WORK_TYPE 
WHEN 1 THEN '(采集)'
WHEN 2 THEN '(归一)'
WHEN 7 THEN '(订阅)'
ELSE '(主题)' 
END
)
) AS tableName1
FROM
TD_DI_WORK_TABLE AS wt
LEFT JOIN TD_DI_NODE AS dn ON
dn.ID = wt.NODE_ID

结果：

☆常用的Sql语句汇总_DDL/DML)_Sql常用语句

SELECT
(
CASE
FILE_TYPE 
WHEN 1 THEN 'PDF'
WHEN 2 THEN 'picture'
WHEN 3 THEN 'DOC'
WHEN 4 THEN 'EXCEL'
WHEN 5 THEN 'PPT'
WHEN 6 THEN 'audio'
WHEN 7 THEN 'video'
WHEN 8 THEN 'text'
WHEN 9 THEN 'other'
ELSE '其他'
END
) AS AA
FROM
TD_DR_DATA_RESOURCE_STATIC AS drs

6、建表、建注释、建索引

☆常用的Sql语句汇总_DDL/DML)_Sql常用语句

CREATE TABLE SUBJECT2."t_name"(
"CLOB2" CLOB,
CONSTRAINT PK_UNION_e25f0721c3fe4a4aa47781606200475f PRIMARY KEY ( "id",
"idd"))

CREATE TABLE SUBJECT2."T_DDD"(
"id" CHAR (10) NOT NULL,
not cluster PRIMARY KEY("id"),
"idd" CHAR (2) NOT NULL,
"VARCHAR21" VARCHAR2 (10) DEFAULT ('-33') ,
"NUMBER1" NUMBER (10,7) DEFAULT ('-4⑤56565665') ,
"DECIMAL1" DECIMAL (10,7) DEFAULT ('54⑤56565665') NOT NULL,
"DATE1" DATE DEFAULT (SYSDATE) ,
"time1" TIME (6) DEFAULT (SYSDATE) ,
"Timestamp1" TIMESTAMP (6) DEFAULT (SYSDATE) NOT NULL,
"SMALLINT1" SMALLINT DEFAULT ('11111') NOT NULL,
"NUMERIC1" NUMERIC (11,6) DEFAULT ('11111') NOT NULL,
"INTEGER2" INTEGER DEFAULT ('1111111111') NOT NULL,
"BIGINT2" BIGINT DEFAULT ('1111111111') ,
"TEXT2" TEXT DEFAULT ('的捡垃圾拉法基拉法基垃圾啊独立开发经理就') NOT NULL,
"BLOB2" BLOB,
"CLOB2" CLOB
)

指明 CLUSTER（默认是聚集索引）,表明是主关键字上聚集索引；

聚集索引,是不能修改字段信息的

指明 NOT CLUSTER,表明是主关键字上非聚集索引；

一般用这个

-表注释
comment on table "SUBJECT2"."T_DDD" is '备注注111';
-字段注释
comment on column "SUBJECT2"."T_DDD"."VARCHAR21" is '注释';

select * from user_tables where table_name = '表名'

create index "SUBJECT2"."索引名字" on "SUBJECT2"."T_DDD2"("idd","VARCHAR21","DECIMAL1","time1","INTEGER2")

create index "SUBJECT2"."索引名字" on "SUBJECT2"."T_DDD"("idd")
- 简约
create index "索引名字" on "T_DDD"("idd")

SELECT * FROM TD_DI_NODE WHERE 1=0

public class Verify {

 *     char、varchar2 ①不能设置精度  ②最大长度 1-8188  ③设置了默认值：默认值长度 <= 长度   (一个中文占两个长度)
 *     number、numeric、decimal：①精度(>=0) <= ②长度(0-38)  ③设置了默认值：精度+默认值小数点前的长度 <= 长度
 *       默认值范围：-32768<=smallint<=32767    -2147483648<=integer<=2147483647
 *       默认值范围：-9223372036854775808<=bigint<=9223372036854775807
 *     time：①不能设置长度 ②时间格式：HH:mm:ss ③可以设置精度(>=0 且 <=6)  ④默认值还可以为：SYSDATE
 *     timestamp：①不能设置长度 ②时间格式：yyyy-MM-dd HH:mm:ss ③可以设置精度(>=0 且 <=6)  ④默认值还可以为：SYSDATE
        Assert.isTrue(length>=1  length<=8188,
        Assert.isTrue(defLen <= length,
        Assert.isTrue(precision <= length,
            Assert.isTrue(precision + split[0].length() <= length,
        Assert.isTrue(precision <= 6,
/** 判断数字num在min和max之间：min <= num <= max
    return defDec.compareTo(new BigDecimal(min)) > -1  defDec.compareTo(new BigDecimal(max)) < 1;
}
}

常量类

@Data
public class Constants {
public static final List DM_FIELD_ALL =
public static final List DM_FIELD_NUM_ALL =
public static final List DM_FIELD_NUM =
public static final List DM_FIELD_INT =
public static final List DM_FIELD_CHAR =
public static final String SYSDATE = "SYSDATE";
}

枚举类

/** 达梦字段类型,数据字典类型A11
 * @Description
 * @Author CC
 * @Date 2021/11/9
 * @Version 1.0
 */
public enum DmFieldEnums {
//getter/setter......
}

@Data
public class TableEntity implements Serializable {
private List columnEntities;//字段信息
}

@Data
public class ColumnEntity {
private String describe; //字段描述
}

public class CreateTableSql {

 *     char、varchar2 ①不能设置精度  ②最大长度 1-8188  ③设置了默认值：默认值长度 <= 长度   (一个中文占两个长度)
 *     number、numeric、decimal：①精度(>=0) <= ②长度(0-38)  ③设置了默认值：精度+默认值小数点前的长度 <= 长度
 *     smallint、int、integer、bigint：①不能设置长度、精度  ②默认值长度 <= 5、10、10、19
 *     time：①不能设置长度 ②时间格式：HH:mm:ss ③可以设置精度(>=0 且 <=6)  ④默认值还可以为：SYSDATE
 *     timestamp：①不能设置长度 ②时间格式：yyyy-MM-dd HH:mm:ss ③可以设置精度(>=0 且 <=6)  ④默认值还可以为：SYSDATE
    List keyColumnList = tableEntity.getColumnEntities().stream().filter(e -> e.getIsKey() != null 
    for (int i = 0; i < tableEntity.getColumnEntities().size(); i++) {
}

java实现sql

/**设置表注释sql
}

/**
public static String createTableIndexSql(String tableName, List indexFieldIdList) {
}

7、分页

方式一（推荐）

（效率高、可写where、order by）

-- page     :第几页
-- pageSize :每页条数
-- end      = page*pageSize
-- start    = end-pageSize+1
-- 例如：
-- page  pageSize   end-pageSize+1   page*pageSize
--  1         2        start=1           end=2
--  2         2        start=3           end=4
--  3         2        start=5           end=6
--  1         3        start=1           end=3
--  2         3        start=4           end=6
--  2         4        start=5           end=8

SELECT
*
FROM
(
SELECT
a.*,
ROWNUM rn
FROM
(
SELECT
*
FROM
SUBJECT2.EEE_EEE AS e
) a
WHERE
ROWNUM <= end
) AS rn
WHERE
rn >= start

写where、order

SELECT
*
FROM
(
SELECT
a.*,
ROWNUM rn
FROM
(
SELECT
*
FROM
SUBJECT2.EEE_EEE AS e 
WHERE e."AREA_ORIGIN_ID" IN (22,33,44,55,66,77)
ORDER BY e."AREA_ORIGIN_ID" DESC 
) a
WHERE
ROWNUM <= end
) AS rn
WHERE
rn >= start

方式二

不能写where、order

-- endNum   = page*pageSize
-- startNum = endNum-pageSize
-- 分页查询 page：第1页,pageSize：每页2条数据   endNum=2  startNum=0
-- 分页查询 page：第2页,pageSize：每页2条数据   endNum=4  startNum=2
-- 分页查询 page：第3页,pageSize：每页1条数据   endNum=3  startNum=2
-- 分页查询 page：第3页,pageSize：每页2条数据   endNum=6  startNum=4
-- 分页查询 page：第1页,pageSize：每页4条数据   endNum=4  startNum=0

SELECT
*
FROM
(
SELECT
ROWNUM AS rnum,
e.*
FROM
SUBJECT2."EEE_EEE" AS e
WHERE
ROWNUM <= endNum
) AS re
WHERE
re.rnum > startNum;

有字段：

SELECT
*
FROM
(
SELECT
ROWNUM AS rowno,
"AREA_ORIGIN_ID",
"Province",
"City",
"AREA_ORIGIN_NAME",
"MEMONIC",
"Remark",
"rrrr"
FROM
SUBJECT2."EEE_EEE" AS e
WHERE
ROWNUM <= endNum
) as rn
WHERE
rn.rowno > startNum

错误写法：

SELECT
*
FROM
(
SELECT
ROWNUM AS rnum,
e.*
FROM
SUBJECT2.EEE_EEE AS e
WHERE
ROWNUM <= 4
AND e."AREA_ORIGIN_ID" IN (22,33,44,55,66,77)
ORDER BY e."AREA_ORIGIN_ID" DESC 
) AS re
WHERE
re.rnum > 0;

-- page    pageSize         start           end 
--   -         -     (page-1)*pageSize    pageSize
--   1         2              0              2
--   2         2              2              2
--   3         3              6              3

SELECT
* 
FROM
trg
limit start,end

SELECT
*
FROM
trg
limit (page-1)*pageSize,pageSize

写where、order by

SELECT
* 
FROM
trg 
WHERE
id IN ( 10, 11, 12, 13, 14 ) 
ORDER BY id DESC
LIMIT start,end

8、连接字符串(concat)

① ||

不推荐,其他数据库不支持

SELECT 'q'||'w'||'e'||'r' AS str
结果：qwer

SELECT 'q'||'w'||null||'r' AS str
结果：qwr

② concat

9、Oracle的函数

TO_CHAR 把日期或数字转换为字符串

获取系统日期： SYSDATE

格式化日期：

日期：

12小时制：
SELECT TO_CHAR(SYSDATE() ,'yyyy-MM-dd HH:MI:SS') 
结果：2021-12-14 05:22:42

24小时制：
SELECT TO_CHAR(SYSDATE() ,'yyyy-MM-dd HH24:MI:SS') 
结果：2021-12-14 17:16:43

转换的格式：

表示 year 的：     y 表示年的最后一位 、
              day 当周第几天,全称, 比如星期五或者friday
表示hour的：             hh   2位数表示小时 12进制 、 
hh24 2位数表示小时 24小时
表示minute的：mi 2位数表示分钟
表示second的：ss 2位数表示秒 60进制
表示季度的：q 一位数 表示季度 （1-4）
另外还有ww 用来表示当年第几周 w用来表示当月第几周.

24小时制下的时间范围：00：00：00-23：59：59
12小时制下的时间范围：1：00：00-12：59：59

格式

数字格式:  9  代表一个数字 
     ,  显示千位指示符

截取两位小数（trunc）

select trunc(to_number('1.1271113'),2);

语法：NVL(A, B)

如果A为null,则返回B,否则返回A.

例如NVL（A,0）,如果A为null,返回0.

SELECT NVL(1,null)
结果：1

SELECT NVL(null,2)
结果：2

10、drop-删除表

①.0.1、oracle、dm级联删除

drop table "SYSDBA"."TABLE_NAME" cascade;

普通删除

drop table "SUBJECT_CS"."A_by" restrict;

11、UNION ALL 连表

问题：A一张表有字段Z,B表没有字段Z,是否可以连表?

SELECT
drs.ID,drs.RESOURCE_ID,drs."TYPE"
,drs.CREATOR_ID,drs.CREATOR_NAME ,drs.ORG_ID ,drs.CREATE_TIME
FROM TD_DR_DATA_RESOURCE_STATISTICS AS drs
WHERE RESOURCE_ID = 'fc937912543b49bca9f54ae18eb568e9'
UNION ALL
SELECT
urc.ID,urc.RESOURCE_ID,'3' AS "TYPE"
,urc.CREATOR_ID,urc.CREATOR_NAME ,urc.ORG_ID ,urc.CREATE_TIME
FROM TD_PORTAL_USER_RESOURCE_COLLECT AS urc
WHERE RESOURCE_ID = 'fc937912543b49bca9f54ae18eb568e9'

UNION ALL：合并重复的行

UNION：不合并重复的行

12、递归查询数据（树结构）-达梦、oracle

select * from table [start with condition1] connect by [prior] id=parentid

☆常用的Sql语句汇总_DDL/DML)_Sql常用语句

示例：

select t.*, level, CONNECT_BY_ROOT(id)
from tab_test t
start with t.id = 0
connect by prior t.id = t.fid;

☆常用的Sql语句汇总_DDL/DML)_Sql常用语句

--第一种,修改prior关键字位置
select t.*, level, CONNECT_BY_ROOT(id)
  from tab_test t
 start with t.id = 4
connect by t.id = prior t.fid;

--第二种,prior关键字不动 调换后面的id=fid逻辑关系的顺序
select t.*, level, CONNECT_BY_ROOT(id)
  from tab_test t
 start with t.id = 4
connect by prior t.fid = t.id;

☆常用的Sql语句汇总_DDL/DML)_Sql常用语句

SELECT
t.ID = #{id}
CONNECT BY PRIOR t.ID = t.P_ID;

13、开启、关闭数据库主外键

-- 1开启、0关闭
call "dmcompare"."FOREIGN_KEY_CHECKS"(1,'T_DATA_PROCESS','T_DATA_PROCESS');

达梦应该和oracle一样

14、清空表中的数据（TRUNCATE）

TRUNCATE TABLE "TD_SYS_USER_ORG";

15、生成32位字符串

函数：sys_guid()、newid()

select rawtohex(sys_guid());
-- 推荐使用newid()
select REPLACE(newid(),'-','');

16、边查询边插入

insert into TD_SYS_USER_ORG
(ID,USER_ID,ORG_ID,ORG_TYPE)
(
select 
rawtohex(sys_guid()),
ID,
ORG_ID,
2 
from TD_SYS_USER
);

select 查询出来的是列表

17、替换字符串

第一段：replce方法

用法1：REPLACE(sourceStr, searchStr, replacedStr)
sourceStr标识要被替换的字段名或字符串,searchStr表示要被替换掉的字符串,replacedStr表示要替换成的字符串.

用法2：REPLACE(sourceStr, searchStr)
sourceStr标识要被替换的字段名或字符串,searchStr表示要被剔除掉的字符串.

如：

select REPLACE(newid(),'-','');

18、查询数据库所有对象

ALL_OBJECTS 表数据库所有对象表：包括表、视图、物化视图、函数......等

-- 查询所有对象
SELECT * from ALL_OBJECTS
-- 筛选条件 - OWENR:哪个数据库.- OBJECT_TYPE 类型：
SELECT OBJECT_NAME,OBJECT_TYPE from ALL_OBJECTS
WHERE OWNER = 'MIDDLE' AND OBJECT_TYPE = 'MATERIALIZED VIEW'

查询（当前用户的）物化视图（USER_MVIEWS）：

SELECT MVIEW_NAME,REFRESH_METHOD FROM USER_MVIEWS

-- 整个数据库的物化视图
select * from DBA_MVIEWS where OWNER = 'MIDDLE'

-- 当前用户的物化视图
SELECT * FROM USER_MVIEWS WHERE MVIEW_NAME = 'V2'

-- 创建物化日志
create materialized view log on "Z_ZZX" with rowid, sequence (ID_CPM_JH, CODE) including new values;

-- 查询物化视图的日志
select * from MLOG$_Z_ZZX

-- 查询物化视图日志表
SELECT * from ALL_OBJECTS WHERE OWNER = 'MIDDLE' and OBJECT_NAME LIKE '%MLOG$%'

-- 查询物化视图
SELECT * from ALL_OBJECTS
WHERE OWNER = 'MIDDLE' AND OBJECT_TYPE = 'MATERIALIZED VIEW'

-- 查询物化视图需要的表
select REFERENCED_NAME from ALL_DEPENDENCIES WHERE OWNER = 'MIDDLE' AND TYPE = 'MATERIALIZED VIEW' AND NAME = 'WH_LOG' AND REFERENCED_NAME <> 'WH_LOG'

-- 连表查询
SELECT * FROM user_mviews um left join SYS.ALL_DEPENDENCIES ad on ad.NAME = um.MVIEW_NAME WHERE ad.OWNER = 'MIDDLE' AND ad.TYPE = 'MATERIALIZED VIEW' AND ad.NAME = 'WH_LOG' AND ad.REFERENCED_NAME <> 'WH_LOG'

-- 获取DDL语句（根据不同类型）
SELECT dbms_metadata.get_ddl('MATERIALIZED_VIEW','WH_LOG') FROM DUAL

-- 获取DDL-物化视图日志
SELECT dbms_metadata.get_ddl('TABLE','MLOG$_Z_ZZX') FROM DUAL
-- 获取DDL-物化视图
SELECT dbms_metadata.get_ddl('MATERIALIZED_VIEW','CS_COMMIT_COMPLETE') FROM DUAL
SELECT dbms_metadata.get_ddl('MATERIALIZED_VIEW_LOG','MLOG$_Z_ZZX') FROM DUAL

-- 获取DDL-视图
SELECT dbms_metadata.get_ddl('VIEW','V1') FROM DUAL

19、获取两个表中差异数据：minus（减法）

mysql没有.需要用别的方式替换

表结构必须一致,数据也必须一致才能减去

SELECT * from T_1
minus
SELECT * from T_2

以上就是土嘎嘎小编为大家整理的☆常用的Sql语句汇总_DDL/DML)相关主题介绍,如果您觉得小编更新的文章只要能对粉丝们有用,就是我们最大的鼓励和动力,不要忘记讲本站分享给您身边的朋友哦！！

网站首页

视频教程

下载频道

文章中心

问答中心

在线工具

☆常用的Sql语句汇总_DDL/DML)_Sql常用语句

1、获取所有表名、表信息

2、获取当前表的主表（外键关联的表）

3、获取表的约束

4、获取表的索引

5、case when then else end

6、建表、建注释、建索引

7、分页

8、连接字符串(concat)

9、Oracle的函数

10、drop-删除表

11、UNION ALL 连表

12、递归查询数据（树结构）-达梦、oracle

13、开启、关闭数据库主外键

14、清空表中的数据（TRUNCATE）

15、生成32位字符串

16、边查询边插入

17、替换字符串

18、查询数据库所有对象

19、获取两个表中差异数据：minus（减法）

相关推荐

编辑推荐

热门文章

网站首页

视频教程

下载频道

文章中心

问答中心

在线工具

☆常用的Sql语句汇总_DDL/DML)_Sql常用语句

1、获取所有表名、表信息

2、获取当前表的 主表（外键关联的表）

3、获取表的约束

4、获取表的索引

5、case when then else end

6、建表、建注释、建索引

7、分页

8、连接字符串(concat)

9、Oracle的函数

10、drop-删除表

11、UNION ALL 连表

12、递归查询数据（树结构）-达梦、oracle

13、开启、关闭数据库主外键

14、清空表中的数据（TRUNCATE）

15、生成32位字符串

16、边查询边插入

17、替换字符串

18、查询数据库所有对象

19、获取两个表中差异数据：minus（减法）

相关推荐

编辑推荐

热门文章

2、获取当前表的主表（外键关联的表）