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.MySQL2.3.PostgreSQL2.4.Oracle2.5.sql
create table dept_2 as select * from dept where 1 = 02.6.SQL Server2.6.1.sql
select * into dept_2 from dept where 1 = 02.7.DB22.7.1.create table dept_2 like dept3.删除违反参照完整性的记录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 14.2.如果要删除重复记录，首先要明确两行数据在什么条件下才会被认为是“重复的记录”4.2.1.sql
select min(id) from dupes group by name4.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_accidents5.2.识别哪些部门发生过3次以上事故5.2.1.sql
select deptno from dept_accidents group by deptnohaving count(*) ＞= 35.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.MySQL7.2.PostgreSQL7.3.SQL Server7.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.Oracle7.5.1.sql
select table_name from all_tables where owner = 'SMEAGOL'7.6.DB27.6.1.sql
select tabname from syscat.tables where tabschema = 'SMEAGOL'8.列举字段8.1.MySQL8.2.PostgreSQL8.3.SQL Server8.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.Oracle8.5.1.sql
select column_name, data_type, column_id from all_tab_columns where owner = 'SMEAGOL' and table_name = 'EMP'8.6.DB28.6.1.sql
select colname, typename, colno from syscat.columns where tabname = 'EMP' and tabschema = 'SMEAGOL'9.列举索引列9.1.MySQL9.1.1.show index from emp9.2.PostgreSQL9.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_name9.3.Oracle9.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 Server9.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.DB29.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.indname10.列举约束10.1.MySQL10.2.PostgreSQL10.3.SQL Server10.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_name10.5.Oracle10.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_name10.6.DB210.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.tabschema11.列举非索引外键11.1.如果通过外键实现父子关系，那么为子表里对应的列加上索引有助于减少锁11.2.子表和父表常用外键列做连接查询，因而加上索引有助于提升查询性能11.3.MySQL11.3.1.针对特定的表执行SHOW INDEX命令，并比较其输出结果与INFORMATION_SCHEMA.KEY_COLUMN_USAGE.COLUMN_NAME的异同11.3.2.如果KEY_COLUMN_USAGE里有对应的COLUMN_NAME，但是SHOW INDEX输出的结果里却没有，那么就说明该列没有索引11.4.PostgreSQL11.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 null11.5.Oracle11.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 null11.6.SQL Server11.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 null11.7.DB211.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 null12.用SQL生成SQL12.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 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') );