☆常用的Sql语句汇总（DDL/DML）
常用的sql语句汇总1、获取所有表名、表信息
[*]里面有表注释
数据库种类sql备注mysql-- 获取所有表名、视图名
show tables
-- 获取 dev_test_data数据库 所有表、视图信息
select * from information_schema.tables where table_schema='dev_test_data'
-- 获取表名、视图名
select table_name from information_schema.tables where table_schema='dev_test_data'
-- 只获取表信息
select * from information_schema.tables where table_schema='dev_test_data' and table_type = 'BASE TABLE'达梦8
(底层是oracle)-- 获取表、视图名称
select table_name from user_tab_comments
-- 只获取表名称
select table_name from user_tab_comments where TABLE_TYPE = 'TABLE'
-- 获取表信息、视图
select * from user_tab_comments基本和oracle一样的oracle-- 获取表名
select table_name from user_tab_comments where TABLE_TYPE = 'TABLE'
-- 获取表信息
select * from user_tab_comments where TABLE_TYPE = 'TABLE'
-- 获取表、视图信息
select * from user_tab_comments2、获取当前表的 主表（外键关联的表）
数据库种类sql备注mysqlSELECT REFERENCED_TABLE_NAME FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE WHERE CONSTRAINT_SCHEMA='dev_test_data' AND TABLE_NAME = 't_c_my_dept' and REFERENCED_TABLE_NAME != 'null'获取字段：table_nameoracle、达梦8-- 根据表名获取 其主表 第一种 方法
select t1.table_name,
t2.table_name as "TABLE_NAME(R)",
t1.constraint_name,
t1.r_constraint_name as "CONSTRAINT_NAME(R)",
a1.column_name,
a2.column_name as "COLUMN_NAME(R)"
from user_constraints t1, user_constraints t2, user_cons_columns a1, user_cons_columns a2
where t1.owner = upper('CJY') and
t1.r_constraint_name = t2.constraint_name and
t1.constraint_name = a1.constraint_name and
t1.r_constraint_name = a2.constraint_name
and t1.table_name = 't_c_emp'
-- 根据表名获取 其主表 第二种 方法
select cl.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 c.table_name = 't_c_dept'
and c.owner = 'CJY'--(获取其主表)———— 外键关联的表
[*]就是这个表中的外键关联的表
2.1、获取从表
--mysql
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 != ""--oracle、达梦
获取其从表
-- 根据表名获取 其从属表的名字
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备注mysqlSELECT
constraint_name,
column_name,
table_catalog,
table_schema,
referenced_table_schema,
referenced_table_name,
referenced_column_name,
table_name
FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE
WHERE
table_schema = 'fd'
-- 筛选表名，一起查询快些
-- AND table_name = 't_autotb561'
AND (
referenced_table_name IS NOT NULL
OR
constraint_name = 'PRIMARY'
)23.2.3更新oracle/达梦8SELECT
aa.CONSTRAINT_NAME,
aa.COLUMN_NAME,
aa.CONSTRAINT_TYPE,
aa.SEARCH_CONDITION,
aa.R_CONSTRAINT_NAME,
bb.TABLE_NAME,
bb.COLUMN_NAME,
aa.TABLE_NAME
FROM
(
SELECT
A.CONSTRAINT_NAME,
A.TABLE_NAME,
A.COLUMN_NAME,
B.CONSTRAINT_TYPE,
B.SEARCH_CONDITION,
B.R_CONSTRAINT_NAME
FROM
USER_CONS_COLUMNS A,
USER_CONSTRAINTS B
WHERE
A.CONSTRAINT_NAME = B.CONSTRAINT_NAME
-- 模式名
AND A.owner = 'THEME_BY1'
-- 表名（查所有比一个个查询快）
-- AND A.TABLE_NAME = 'E_Z_CS_EMP'
) aa
LEFT JOIN USER_CONS_COLUMNS bb ON bb.CONSTRAINT_NAME = aa.R_CONSTRAINT_NAME23.2.3更新mysql
oracle
4、获取表的索引
[*]根据表名、数据库名
数据库种类SQL备注mysqlSELECT
index_name,
column_name,
COLLATION,
non_unique,
nullable,
index_type,
index_comment,
table_name
FROM information_schema.statistics
WHERE
table_schema = 'fd'
-- 筛选表名，一起查询快些
-- AND table_name = 't_c_my_dept';23.2.3更新oracle/达梦8SELECT
t.index_name,
t.column_name,
t.descend,
i.uniqueness,
i.compression,
i.INDEX_TYPE,
i.table_type,
t.TABLE_NAME
FROM
user_ind_columns t,
user_indexes i
WHERE
t.index_name = i.index_name
AND t.table_name = i.table_name
-- 筛选表，全部查询快些
-- AND t.TABLE_NAME = 'abcdTYB_T_AUTOTB557'23.2.3更新mysql
oracle
5、case when then else end
5.1、情况一
测试表：
CREATE TABLE test.cc (
id intPRIMARY key IDENTITY(1,1),
name varchar(255)NULL,
age intNULL,
country varchar(255)NULL
)需求
需求：
用一句sql查询出一张表中年龄<10和年龄≥10的
【提示：用内联；group by后面括号里面可以写逻辑】
【提示：用case when】5.2、情况二
TD_DI_WORK_TABLE 表中有表名字段 TARGET_TABLE_NAME
TD_DI_WORK_TABLE 中有NODE_ID 与 TD_DI_NODE 关联
[*]TD_DI_NODE 中有WORK_TYPE类型，表名该表的构建类型
要求：查询出表名+类型，类型需要根据1-7转换为中文：如：a_tree_pid(采集)
[*]oracle连接字符串：|| 、concat
方式一：
-- 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;方式二：
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结果：
5.3、例子
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_ID6、建表、建注释、建索引
6.1、达梦
1、常用数据类型
名称长度（精度）精度（标度）可做主键可索引默认值备注CHAR1-8188-truetrue默认值长度
页:
[1]