ToB企服应用市场:ToB评测及商务社交产业平台

标题: ☆常用的Sql语句汇总（DDL/DML） [打印本页]
作者: 石小疯    时间: 2023-5-6 22:06
标题: ☆常用的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 关联

要求：查询出表名+类型，类型需要根据1-7转换为中文：如：a_tree_pid(采集)
方式一：
  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]默认值长度



欢迎光临 ToB企服应用市场:ToB评测及商务社交产业平台 (https://dis.qidao123.com/) Powered by Discuz! X3.4