几句SQL

CREATE INDEX indexName ON tableName (fields1)

 为某列建索引

 

 

 

ANALYZE TABLE tableName COMPUTE STATISTICS;

 表分析

 

 

SELECT * FROM table1 t WHERE EXISTS (
SELECT 'X' FROM table2 p WHERE t.r_xm = p.xm AND  t.r_sfzh = p.sfzh
)

 两表比对并排重,比distinct要快

 

 

ALTER TABLE table1  NOCACHE

 清除表缓存

 

 

CREATE TABLE test_2 AS SELECT * FROM test_1

 创建一个和test_1表结构一样的test_2表

 

 

 

MERGE INTO TABLE1 A
USING (SELECT 'pk11111' AS TN FROM DUAL) B
ON (A.PKCOLUMN = B.TN)
WHEN MATCHED THEN
  UPDATE SET COLUMN1 = 'xxx'
WHEN NOT MATCHED THEN
  INSERT (COLUMN1) VALUES ('xxx')


merge into 语法










用上边查出来的结果集,减去下边的结果集


SELECT DISTINCT GXS
  FROM DTGK_USERS
 WHERE GXSMC IN ('管辖所150000000', '管辖所151010100', '管辖所151010101',
        '管辖所151010102', '管辖所151010103', '管辖所151010104')
MINUS
SELECT DISTINCT GXS
  FROM DTGK_USERS
 WHERE YHID IN ('test1', 'test2', 'test3', 'test4', 'test5', 'test6')