(資料圖片僅供參考)

1.復(fù)制數(shù)據(jù)到另一個表

1.1.sql

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

2.復(fù)制表定義

2.1.復(fù)制表結(jié)構(gòu)，而不復(fù)制數(shù)據(jù)

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.從表里刪除一些記錄，因為在另一個表里不存在與這些記錄相匹配的數(shù)據(jù)

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

3.3.sql

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

4.刪除重復(fù)記錄

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.如果要刪除重復(fù)記錄，首先要明確兩行數(shù)據(jù)在什么條件下才會被認(rèn)為是“重復(fù)的記錄”

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.識別哪些部門發(fā)生過3次以上事故

5.2.1.sql

select deptno  from dept_accidents group by deptnohaving 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.元數(shù)據(jù)查詢

6.1.SMEAGOL 模式

7.列舉模式中的表

7.1.MySQL

7.2.PostgreSQL

7.3.SQL Server

7.4.信息模式

7.4.1.information schema，這是按照ISO SQL 標(biāo)準(zhǔn)定義的一組視圖

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.如果通過外鍵實現(xiàn)父子關(guān)系，那么為子表里對應(yīng)的列加上索引有助于減少鎖

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

11.3.MySQL

11.3.1.針對特定的表執(zhí)行SHOW INDEX命令，并比較其輸出結(jié)果與INFORMATION_SCHEMA.KEY_COLUMN_USAGE.COLUMN_NAME的異同

11.3.2.如果KEY_COLUMN_USAGE里有對應(yīng)的COLUMN_NAME，但是SHOW INDEX輸出的結(jié)果里卻沒有，那么就說明該列沒有索引

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 語句，通過查詢某些表來獲取需要填入的數(shù)據(jù)（例如數(shù)據(jù)庫對象名稱）

12.2.Oracle示例

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

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. /* 根據(jù)EMP表的某些列生成插入腳本 */

select "insert into emp(empno,ename,hiredate) "||chr(10)||       "values( "||empno||","||""""||ename       ||""",to_date("||""""||hiredate||""") );" inserts from empwhere 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") );

標(biāo)簽：