选读SQL经典实例笔记03_DML和元数据

来源：博客园时间：2023-07-09 06:24:20

(资料图片)

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 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.元数据查询

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 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") );

关键词：