PL/SQL常用指令收集
1.select 语句
SELECT * FROM dept d 显示dept(别名d)表中的所有字段
SELECT deptno, loc FROM dept 显示dept表中的deptno与loc字段内容
语句中同样可以用算术运算与函数 如 sal+30，sal+nvl(comm,0)
2、where 子句
SELECT ename，job FROM em
where job='CLERK' 显示JOB为CLERK的字段
where子句中同样可以用比较运算符>,<,<>,<=,>=
between ... and ...（在两值之间）
in(...,....,...,) (匹配列出的值)
like (匹配一个字符的模式)
is null (是空值)
用逻辑运算符连接多个条件 and，or，not(优先级比较运算>not>and>or)
3、order by子句
SELECT ename，job，deptno FROM emp
order by deptno 按deptno顺序排列(asc可隐藏)
........................... desc 按..........降序排列
后可跟多个字段，先后排列顺序
4、组函数与group by子句
SELECT deptno, AVG(sal) FROM emp
GROUP BY deptno 显示emp中按deptno分组的deptno与平均工资
组函数有 avg(..) 求平均数 sum(..)求和 count( ..)计数 max(..)求最大值
min(..)求最小值 stddev(..)标准差 variance(..)方差
5、having子句
SELECT deptno, max(sal) FROM emp
GROUP BY deptno
HAVING max(sal)>2900 显示emp表按deptno分组最大工资大于2900的最大工资
having 功能等同于where功能 当使用分组语句时使用
6、insert语句
INSERT INTO dept (deptno, dname, loc)
VALUES (50, 'DEVELOPMENT', 'DETROIT') 在dept表中插入值字段与值一一对应
插入整条数据时，前面可以不跟字段，安装表中顺序写入值如：
INSERT INTO dept
VALUES (70, 'FINANCE', NULL) null为空值
插入的值可以跟select，where语句
7、update语句
UPDATE emp
SET deptno = 20
WHERE empno = 7782 将emp表中empno等于7782中的denptno修改为20
8、delete语句
DELETE FROM dept
WHERE dname = 'DEVELOPMENT' 删除dept表中dname为DEVELOPMENT的数据
不用where子句为删除整张表
9、savepoint 与rollback
savepoint a1 存储a1存储点
rollback to a1 回到a1存储点状态
10、建表
CREATE TABLE dept 2 (deptno NUMBER(2),
dname VARCHAR2(14),
loc VARCHAR2(13)) 建立dept2表 下面分别为字段与属性
建表时可以用select 语句选择已有表中的字段（where 1<>1）或者数据
11、修改表
ALTER TABLE dept30
ADD (job VARCHAR2(9)) 修改dept30表增加job字段
ALTER TABLE dept30
MODIFY (ename VARCHAR2(15)) 修改deptno中ename的属性为varchar2属性
alter table test
rename column name to dname 重命名test表中name字段为dname字段
12、删除表
DROP TABLE dept30 删除dept30表
13、改变对象名称
RENAME dept TO department 将dept表重命名为department
14、截取表的所有记录
TRUNCATE TABLE department 删除department中的所有数据
15、添加注释
COMMENT ON TABLE emp
IS 'Employee Information'
select * from ALL_COL_COMMENTS 显示所注释
USER_COL_COMMENTS
ALL_TAB_COMMENTS
USER_TAB_COMMENTS
16、创建约束
CREATE TABLE emp
(empno NUMBER(4),
ename VARCHAR2(10),
deptno NUMBER(7,2) NOT NULL,
CONSTRAINT emp_empno_pk
PRIMARY KEY (EMPNO)); 后两行创建约束主键为empno
CONSTRAINT dept_dname_uk UNIQUE(dname));唯一性约束
CONSTRAINT dept_deptno_pk PRIMARY KEY(dept_no)主键约束
CONSTRAINT emp_deptno_fk FOREIGN KEY (deptno)
REFERENCES dept (deptno)); 外检约束
CONSTRAINT emp_deptno_ck
CHECK (DEPTNO BETWEEN 10 AND 99) check约束只能在10-99之间
17、加约束
ALTER TABLE emp
ADD CONSTRAINT emp_mgr_fk
FOREIGN KEY(mgr) REFERENCES emp(empno);
18、删除约束
ALTER TABLE emp
DROP CONSTRAINT emp_mgr_fk;
19、使约束失效
ALTER TABLE emp
ENABLE CONSTRAINT emp_empno_pk;
20、查看约束
SELECT * FROM user_constraints
WHERE table_name = 'EMP';
21、创建视图
CREATE VIEW empvu10
AS SELECT empno, ename, job
FROM emp
WHERE deptno = 10
22、修改视图
CREATE OR REPLACE VIEW empvu10
(employee_number, employee_name, job_title)
AS SELECT empno, ename, job
FROM emp
WHERE deptno = 10;
23、使用 WITH CHECK OPTION子句
使用WITH CHECK OPTION可使DML操作限制在视图所包含的范围内.
CREATE OR REPLACE VIEW empvu20
AS SELECT *
FROM emp
WHERE deptno = 20
WITH CHECK OPTION CONSTRAINT empvu20_ck;
24、删除视图
DROP VIEW view;
25、创建序列号
CREATE SEQUENCE dept_deptno
INCREMENT BY 1
START WITH 91
MAXVALUE 100
NOCACHE
NOCYCLE;
NEXTVAL 返回下一个可用序列号值
CURRVAL 包含当前的序列号值
26、创建索引
CREATE INDEX emp_ename_idx
ON emp(ename);
27、创建同义词
CREATE SYNONYM d_sum
FOR dept_sum_vu;
28、授予权限
GRANT create table, create sequence, create view
TO scott;
29、创建角色
CREATE ROLE manager
30、修改账号密码
ALTER USER scott
IDENTIFIED BY lion
31、授予对象权限
GRANT select
ON emp
TO sue, rich
WITH GRANT OPTION 可转授此权限
32、收回对象的权限
REVOKE select, insert
ON dept
FROM scott; 收回权限
