1. 複製數據到另一個表

1.1.  sql

 insert into dept_east (deptno,dname,loc)
 select deptno,dname,loc
   from dept
  where loc in ( 'NEW YORK','BOSTON' )

2. 複製表定義

2.1. 複製表結構，而不複製數據

2.2. MySQL

2.3. PostgreSQL

2.4. Oracle

2.5. sql

create table dept_2
 as
 select *
   from dept
  where 1 = 0

2.6. SQL Server

2.6.1.  sql

select *
   into dept_2
   from dept
  where 1 = 0

2.7. DB2

2.7.1. create table dept_2 like dept

3. 刪除違反參照完整性的記錄

3.1. 從表裡刪除一些記錄，因為在另一個表裡不存在與這些記錄相匹配的數據

3.2. 刪除其實就是查詢，最重要的步驟是要寫出正確的WHERE子句條件，以找出要刪除哪些記錄

3.3. sql

delete from emp
 where not exists (
   select * from dept
    where dept.deptno = emp.deptno
)

4. 刪除重覆記錄

4.1. sql

create table dupes (id integer, name varchar(10))
insert into dupes values (1, 'NAPOLEON')
insert into dupes values (2, 'DYNAMITE')
insert into dupes values (3, 'DYNAMITE')
insert into dupes values (4, 'SHE SELLS')
insert into dupes values (5, 'SEA SHELLS')
insert into dupes values (6, 'SEA SHELLS')
insert into dupes values (7, 'SEA SHELLS')
select * from dupes order by 1

4.2. 如果要刪除重覆記錄，首先要明確兩行數據在什麼條件下才會被認為是“重覆的記錄”

4.2.1. sql

select min(id)
  from dupes
 group by name

4.2.2.   sql

delete from dupes
   where id not in ( select min(id)
                       from dupes
                      group by name )

5. 刪除被其他表參照的記錄

5.1. sql

create table dept_accidents
( deptno         integer,
  accident_name varchar(20) )
insert into dept_accidents values (10,'BROKEN FOOT')
insert into dept_accidents values (10,'FLESH WOUND')
insert into dept_accidents values (20,'FIRE')
insert into dept_accidents values (20,'FIRE')
insert into dept_accidents values (20,'FLOOD')
insert into dept_accidents values (30,'BRUISED GLUTE')
select * from dept_accidents

5.2. 識別哪些部門發生過3次以上事故

5.2.1. sql

select deptno
  from dept_accidents
 group by deptno
having count(*) ＞= 3

5.3. 刪除在上述部門工作的員工

5.3.1.  sql

delete from emp
  where deptno in ( select deptno
                      from dept_accidents
                     group by deptno
                    having count(*) ＞= 3 )

6. 元數據查詢

6.1. SMEAGOL 模式

7. 列舉模式中的表

7.1. MySQL

7.2. PostgreSQL

7.3. SQL Server

7.4. 信息模式

7.4.1. information schema，這是按照ISO SQL 標准定義的一組視圖

7.4.2.  sql

select table_name
   from information_schema.tables
  where table_schema = 'SMEAGOL'

7.5. Oracle

7.5.1. sql

select table_name
  from all_tables
 where owner = 'SMEAGOL'

7.6. DB2

7.6.1.  sql

select tabname
   from syscat.tables
  where tabschema = 'SMEAGOL'

8. 列舉欄位

8.1. MySQL

8.2. PostgreSQL

8.3. SQL Server

8.4. 信息模式

8.4.1.  sql

select column_name, data_type, ordinal_position
   from information_schema.columns
  where table_schema = 'SMEAGOL'
    and table_name   = 'EMP'

8.5. Oracle

8.5.1.   sql

select column_name, data_type, column_id
    from all_tab_columns
   where owner      = 'SMEAGOL'
     and table_name = 'EMP'

8.6. DB2

8.6.1.  sql

select colname, typename, colno
   from syscat.columns
  where tabname   = 'EMP'
    and tabschema = 'SMEAGOL'

9. 列舉索引列

9.1. MySQL

9.1.1. show index from emp

9.2. PostgreSQL

9.2.1.   sql

select a.tablename,a.indexname,b.column_name
    from pg_catalog.pg_indexes a,
         information_schema.columns b
   where a.schemaname = 'SMEAGOL'
     and a.tablename  = b.table_name

9.3. Oracle

9.3.1. sql

select table_name, index_name, column_name, column_position
  from sys.all_ind_columns
 where table_name  = 'EMP'
   and table_owner = 'SMEAGOL'

9.4. SQL Server

9.4.1.   sql

select a.name table_name,
         b.name index_name,
          d.name column_name,
          c.index_column_id
     from sys.tables a,
          sys.indexes b,
          sys.index_columns c,
          sys.columns d.
   where a.object_id = b.object_id
    and b.object_id = c.object_id
    and b.index_id  = c.index_id
    and c.object_id = d.object_id
   and c.column_id = d.column_id
    and a.name      = 'EMP'

9.5. DB2

9.5.1.   sql

select a.tabname, b.indname, b.colname, b.colseq
    from syscat.indexes a,
         syscat.indexcoluse b
   where a.tabname   = 'EMP'
     and a.tabschema = 'SMEAGOL'
     and a.indschema = b.indschema
     and a.indname   = b.indname

10. 列舉約束

10.1. MySQL

10.2. PostgreSQL

10.3. SQL Server

10.4. 信息模式

10.4.1.   sql

select a.table_name,
          a.constraint_name,
          b.column_name,
          a.constraint_type
     from information_schema.table_constraints a,
          information_schema.key_column_usage b
    where a.table_name      = 'EMP'
      and a.table_schem     = 'SMEAGOL'
      and a.table_name      = b.table_name
     and a.table_schema    = b.table_schema
     and a.constraint_name = b.constraint_name

10.5. Oracle

10.5.1.   sql

select a.table_name,
          a.constraint_name,
         b.column_name,
          a.constraint_type
     from all_constraints a,
          all_cons_columns b
    where a.table_name      = 'EMP'
      and a.owner           = 'SMEAGOL'
      and a.table_name      = b.table_name
     and a.owner           = b.owner
     and a.constraint_name = b.constraint_name

10.6. DB2

10.6.1.   sql

select a.tabname, a.constname, b.colname, a.type
    from syscat.tabconst a,
         syscat.columns b
   where a.tabname   = 'EMP'
     and a.tabschema = 'SMEAGOL'
     and a.tabname   = b.tabname
     and a.tabschema = b.tabschema

11. 列舉非索引外鍵

11.1. 如果通過外鍵實現父子關係，那麼為子表裡對應的列加上索引有助於減少鎖

11.2. 子表和父表常用外鍵列做連接查詢，因而加上索引有助於提升查詢性能

11.3. MySQL

11.3.1. 針對特定的表執行SHOW INDEX命令，並比較其輸出結果與INFORMATION_SCHEMA.KEY_COLUMN_USAGE.COLUMN_NAME的異同

11.3.2. 如果KEY_COLUMN_USAGE里有對應的COLUMN_NAME，但是SHOW INDEX輸出的結果里卻沒有，那麼就說明該列沒有索引

11.4. PostgreSQL

11.4.1.   sql

select fkeys.table_name,
          fkeys.constraint_name,
          fkeys.column_name,
          ind_cols.indexname
     from (
   select a.constraint_schema,
          a.table_name,
          a.constraint_name,
          a.column_name
    from information_schema.key_column_usage a,
         information_schema.referential_constraints b
   where a.constraint_name    = b.constraint_name
     and a.constraint_schema  = b.constraint_schema
     and a.constraint_schema  = 'SMEAGOL'
     and a.table_name         = 'EMP'
         ) fkeys
         left join
         (
  select a.schemaname, a.tablename, a.indexname, b.column_name
    from pg_catalog.pg_indexes a,
         information_schema.columns b
   where a.tablename  = b.table_name
     and a.schemaname = b.table_schema
         ) ind_cols
      on (    fkeys.constraint_schema = ind_cols.schemaname
          and fkeys.table_name        = ind_cols.tablename
          and fkeys.column_name       = ind_cols.column_name )
   where ind_cols.indexname is null

11.5. Oracle

11.5.1.   sql

select a.table_name,
          a.constraint_name,
          a.column_name,
          c.index_name
     from all_cons_columns a,
          all_constraints b,
          all_ind_columns c
    where a.table_name       = 'EMP'
      and a.owner            = 'SMEAGOL'
     and b.constraint_type  = 'R'
     and a.owner            = b.owner
     and a.table_name       = b.table_name
     and a.constraint_name  = b.constraint_name
     and a.owner            = c.table_owner  (+)
     and a.table_name       = c.table_name   (+)
   and a.column_name      = c.column_name  (+)
     and c.index_name       is null

11.6. SQL Server

11.6.1.   sql

select fkeys.table_name,
          fkeys.constraint_name,
          fkeys.column_name,
          ind_cols.index_name
     from (
   select a.object_id,
          d.column_id,
          a.name table_name,
          b.name constraint_name,
         d.name column_name
    from sys.tables a
         join
         sys.foreign_keys b
      on (   a.name      = 'EMP'
         and a.object_id = b.parent_object_id
         )
         join
         sys.foreign_key_columns c
         on ( b.object_id = c.constraint_object_id )
            join
            sys.columns d
         on (   c.constraint_column_id = d.column_id
         and a.object_id               = d.object_id
         )
         ) fkeys
         left join
         (
  select a.name index_name,
         b.object_id,
         b.column_id
    from sys.indexes a,
         sys.index_columns b
   where a.index_id = b.index_id
          ) ind_cols
      on (     fkeys.object_id = ind_cols.object_id
          and fkeys.column_id = ind_cols.column_id )
   where ind_cols.index_name is null

11.7. DB2

11.7.1.   sql

select fkeys.tabname,
          fkeys.constname,
          fkeys.colname,
          ind_cols.indname
     from (
   select a.tabschema, a.tabname, a.constname, b.colname
     from syscat.tabconst a,
          syscat.keycoluse b
    where a.tabname   = 'EMP'
     and a.tabschema = 'SMEAGOL'
     and a.type      = 'F'
     and a.tabname   = b.tabname     and a.tabschema = b.tabschema
         ) fkeys
         left join
         (
  select a.tabschema,
         a.tabname,
         a.indname,
         b.colname
    from syscat.indexes a,
         syscat.indexcoluse b
   where a.indschema = b.indschema
     and a.indname   = b.indname
         ) ind_cols
      on (     fkeys.tabschema = ind_cols.tabschema
           and fkeys.tabname   = ind_cols.tabname
          and fkeys.colname   = ind_cols.colname )
   where ind_cols.indname is null

12. 用SQL生成SQL

12.1. 使用字元串拼接SQL 語句，通過查詢某些表來獲取需要填入的數據（例如資料庫對象名稱）

12.2. Oracle示例

12.2.1. /* 生成SQL以計算各個表的行數 */

select 'select count(*) from '||table_name||';' cnts
  from user_tables;
CNTS
--------------------------------------
select count(*) from ANT;
select count(*) from BONUS;
select count(*) from DEMO1;
select count(*) from DEMO2;
select count(*) from DEPT;
select count(*) from DUMMY;

12.2.2.  /* 禁用所有表的外鍵約束 */

select 'alter table '||table_name||
        ' disable constraint '||constraint_name||';' cons
   from user_constraints
  where constraint_type = 'R';
CONS
--------------------------------------------------------
alter table ANT disable constraint ANT_FK;
alter table BONUS disable constraint BONUS_FK;
alter table DEMO1 disable constraint DEMO1_FK;
alter table DEMO2 disable constraint DEMO2_FK;
alter table DEPT disable constraint DEPT_FK;
alter table DUMMY disable constraint DUMMY_FK;

12.2.3.  /* 根據EMP表的某些列生成插入腳本 */

select 'insert into emp(empno,ename,hiredate) '||chr(10)||
       'values( '||empno||','||''''||ename
       ||''',to_date('||''''||hiredate||''') );' inserts
 from emp
where deptno = 10;
INSERTS
---------------------------------------------------------------
insert into emp(empno,ename,hiredate)
values( 7782,'CLARK',to_date('09-JUN-1981 00:00:00') );
insert into emp(empno,ename,hiredate)
values( 7839,'KING',to_date('17-NOV-1981 00:00:00') );
insert into emp(empno,ename,hiredate)
values( 7934,'MILLER',to_date('23-JAN-1982 00:00:00') );