几句SQL

最新推荐文章于 2025-01-20 23:36:12 发布

iteye_9609

最新推荐文章于 2025-01-20 23:36:12 发布

阅读量93

点赞数

分类专栏： SQL 文章标签： SQL

SQL 专栏收录该内容

1 篇文章

订阅专栏

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')