SQL基础及优化---来自程序lee的自我概述

SQL基础及优化—来自程序lee的自我概述

1 基础

1.1 建表语句

--建表
CREATE TABLT temp(
	id    char        not null,
    name  VARCHAR2    not null,
    constraint PK_TEMP primary key(id)
)
--添加注释
COMMENT ON TABLE temp is '临时表';
--创建序列
create sequence idSequence
increment by 1
start with 1
nocycle;
--创建触发器,创建的id为CC开头的20位,如:CC000000000000000001
create trigger autoId_trigger
before insert on temp for each row when(new.id is null)
begin
select lpad(lpad(idSequence.Nextval,18,'0'),20,'CC') into:new.id from dual;
end autoId_trigger;
--当然,主键可以写在外面,通过 ALTER TABLE temp ADD CONSTRAINT PK_TEMP primary key(id);

1.2 删表语句

DROP TABLE temp;
--删除表及其约束
DROP TABLE temp cascade constraints;
--删除表主键
ALTER TABLE temp DROP primary key;

1.3 重命名表字段

ALTER TABLE temp RENAME COLUMN name TO temp_name;

1.4 改变表字段类型

ALTER TABLE temp MODIFY id VARCHAR2(20);

1.5 增加表字段

ALTER TABLE temp ADD sex CHAR(1);

1.6 删除表字段

ALTER TABLE temp DROP COLUMN sex;

1.7 删除数据

DELETE FROM temp where name='xxx';

1.8 修改数据

UPDATE temp SET name='xxxx' where id='CC00000000000000000001';

1.9 插入数据

INSERT INTO temp(name,sex) VALUES('xxxxx','2');

2 优化

2.1 注意表名顺序

--当from字句中的表名为多个,要考虑表名顺序,因为ORACLE执行顺序是从右到左,所以数据少的表排在后面,查询较优
table A --1000条数据
table B --1条数据
select count(*) from A,B; 速度快
select count(*) from B,A; 速度慢

2.2 WHERE字句中的连接顺序

--ORACLE采用自下而上解析WHERE字句,所以表之间的连接要写在其他HWERE条件之前
select * from temp T WHERE T.name = ‘MANAGER' AND 25 < (select count(*) from EMP E where T.sex=E.sex);  速度慢
select * from temp T WHERE 25 < (select count(*) from EMP E where T.sex=E.sex) AND T.name = ‘MANAGER';  速度快

2.3 避免使用 *

2.4 使用DECODE函数

SELECT COUNT(AGENTCODE) FROM LAAGENT WHERE BRANCHTYPE = ‘1’;
SELECT COUNT(AGENTCODE) FROM LAAGENT WHERE BRANCHTYPE = ‘2’;
SELECT COUNT(AGENTCODE) FROM LAAGENT WHERE BRANCHTYPE = ‘3’;
--替换为
SELECT COUNT(DECODE(BRANCHTYPE, 1, ’Y’, NULL)) COUNT1,
       COUNT(DECODE(BRANCHTYPE, 2, ’Y’, NULL)) COUNT2,
       COUNT(DECODE(BRANCHTYPE, 3, ’Y’, NULL)) COUNT3
  FROM LAAGENT;

2.5 减少对表的查询

--子查询尽量少用表查询,会大大减少查询效率
SELECT *
  FROM TABLE
 WHERE NAME = (SELECT NAME FROM TEMP WHERE VERSION = 001)
   AND PROPERTY = (SELECT PROPERTY FROM TEMP WHERE VERSION = 001);
SELECT *
  FROM TABLE
 WHERE (NAME, PROPERTY) =
       (SELECT NAME, PROPERTY FROM TEMP WHERE VERSION = 001);

2.6 使用IN 比OR执行更快

2.7 使用 EXISTS 和 NOT EXISTS 代替 IN 和 NOT IN

2.8 满足业务需求的情况下,使用 UNION ALL 代替 UNION

因为UNION再进行表链接后会筛掉重复的记录

2.9 避免使用 DISTINCT

2.10 在WHERE字句中,不要将索引参入运算

索引列是函数的一部分,那么会全表扫描

SELECT * FROM DEPT WHERE SAL * 12 > 25000; 速度慢  
SELECT * FROM DEPT WHERE SAL > 25000 / 12; 速度快

2.11 TRIM

trim函数会是索引失效,那么可以用VARCHAR2来作为字段类型

2.12 定期重构索引 ALTER INDEX REBUILD

索引需要空间来存储,每当记录再表中增减,或者索引发生修改,都会引起索引的修改,所以每条记录的INSERT,UPDATE,DELETE都会引起索引写入磁盘