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') );
轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/556864.html
標籤：其他
上一篇：資料庫系統概論—恢復與并發
下一篇：返回列表