☆常用的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

1. 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、达梦
获取其从表

1. -- 根据表名获取 其从属表的名字
2. select c.table_name from user_cons_columns cl
3. left join user_constraints c on cl.constraint_name = c.r_constraint_name
4. where c.constraint_type = 'R'
5. 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、情况一

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

复制代码

需求

1. 需求：
2. 用一句sql查询出一张表中年龄<10和年龄≥10的
3. 【提示：用内联；group by后面括号里面可以写逻辑】
4. 【提示：用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. -- 1 查询年龄小于10、大于10的人数
2. select
3. case
4.         when age > 0 and age < 10 then 'age小于10'
5.         when age >= 10 and age < 20 then 'age大于10'
6.         else '其他'
7. end as '条件',
8. count(*) as '人数'
9. from test.t_user
10. group by
11. case
12.         when age > 0 and age < 10 then 'age小于10'
13.         when age >= 10 and age < 20 then 'age大于10'
14.         else '其他'
15. end;
18. -- 2 查询中国人、美国人、其他国家人的年龄和（第一种写法）
19. select sum(age) as '年龄和',
20. case name
21.         when 'cc' then '中国人'
22.         when 'dd' then '中国人'
23.         when 'ee' then '中国人'
24.         when 'ff' then '美国人'
25.         when 'gg' then '美国人'
26.         when 'hh' then '美国人'
27.         else '其他国家'
28. end as '国家'
29. from test.t_user
30. group by
31. case name
32.         when 'cc' then '中国人'
33.         when 'dd' then '中国人'
34.         when 'ee' then '中国人'
35.         when 'ff' then '美国人'
36.         when 'gg' then '美国人'
37.         when 'hh' then '美国人'
38.         else '其他国家'
39. end;
41. -- 3 查询中国人、美国人、其他国家人的年龄和（第二种写法）
42. select sum(age) as '年龄和',
43. case country
44.         when '中国' then '中国人'
45.         when '美国' then '美国人'
46.         else '其他国家'
47. end as '国家'
48. from test.t_user
49. group by
50. case country
51.         when '中国' then '中国人'
52.         when '美国' then '美国人'
53.         else '其他国家'
54. end;

复制代码

方式二：

1. SELECT
2.         wt.TARGET_TABLE_NAME ||
3.         (CASE
4.                 dn.WORK_TYPE
5.                 WHEN 1 THEN '(采集)'
6.                 WHEN 2 THEN '(归一)'
7.                 WHEN 7 THEN '(订阅)'
8.                 ELSE '(主题)'
9.                 END
10.         ) AS tableName
11. FROM
12.         TD_DI_WORK_TABLE AS wt
13. LEFT JOIN TD_DI_NODE AS dn ON
14.         dn.ID = wt.NODE_ID

复制代码

结果：

5.3、例子

1. SELECT
2.         CONCAT(
3.         wt.TARGET_TABLE_NAME,
4.         (CASE
5.                 dn.WORK_TYPE
6.                 WHEN 1 THEN '(采集)'
7.                 WHEN 2 THEN '(归一)'
8.                 WHEN 7 THEN '(订阅)'
9.                 ELSE '(主题)'
10.                 END
11.         )
12.         ) AS tableName1
13. FROM
14.         TD_DI_WORK_TABLE AS wt
15. LEFT JOIN TD_DI_NODE AS dn ON
16.         dn.ID = wt.NODE_ID

复制代码

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

6.1、达梦

1、常用数据类型

[table][tr]名称长度（精度）精度（标度）可做主键可索引默认值备注[/tr][tr][td]CHAR[/td][td]1-8188[/td][td]-[/td][td]true[/td][td]true[/td][td]默认值长度