Oracle SQL-何明
sql
Data Manipulation Language:DML
select
insert
insert into worker (empno,ename,sal) values(66,'张三‘,4600);
insert into worker(empno,ename,sal) select empno,ename,sal from emp where job like 'sal%";
在使用子查询向某表插入数据时不能使用values关键字
insert子句中的列数和数据类型必须与子查询中的列数和数据类型一致
insert的替代变量
insert into emp_DML(empno,ename,job,mgr,hiredate,sal,comm,deptno) values(&id,'&name','&job',7689,to_date('&hiredate','yyyy mm dd'),666,77,66);
update
update emp set sal=sal*1.5 where ename='smith';
基于另外的表修改记录:update emp set sal=(select losal from salgrade where grade=1) where sal<(select losal from salgrade where grade=1);
多列子查询修改记录:update emp set (job,sal)=(select job,sal from emp where empno=7369) where jon='保安';
delete
delete from emp where (job='manage' and sal>2500) or (job='salesman' and sal>1300);
基于另外的表删除:delete from emp where deptno=(select deptno from dept where dname=upper('美容’);
Data Definition Language:DDL
create
table
create table product (p_code number(6), p_name varchar2(30));
varchar2(size)变长字符型数据,默认值和最小值都是1,最大值2000
char(size):定长字符型数据,最大值2000
date:日期型数据
number(P,S)数字型数据,1<p<38,-84<s<124
LOB:LARGE OBJECT
clob
blob
bfile
raw
create table worker as select empno,ename,job from emp;
user
CREATE USER 用户名 IDENTIFIED BY口令;
alter
table
alter table worker add (hiredate date);
alter table worker modify (hireddate default sysdate);
alter table worker drop column hiredate;
alter table worker set unsed(income);
alter table worker drop unused columns;
user
ALTER USER CAT DEFAULT TABLESPACE USERS QUOTA 20M ON USERS;
rename
rename 对象原来名字 对象现在的名字
truncate
truncate table worker;清空worker数据,保留表结构
drop
drop table worker;删除表
Transaction Control
commit
set autocommit on
rollback
Data Control Language:DCL
grant
revoke
运算符
连接运算符 ||
distinct运算符
作用于多列时,每一种组合显示一行
oracle通过排序完成此功能,效率较低
比较运算符
>
>=
<
<=
=
<>
!=
between...and... not between...and...
包含上下限
可用于数字,字符(需用单引号),日期型(需用单引号)
in、not in
like
%:0或者多个字符
_:一个且只能是一个字符
转义符\
集合操作符
分类
union
union all
minus
intersect:返回两查询结果相同的
人造列
select d.location_id,department_name "部门", to_char(null) "仓库地址" from departments d union select l.location_id,to_char(null) "部门", l.state_province from locations l
子句
where子句
字符串区分大小写
不能用分组函数
order by子句
排序
ASC 升序(默认)
DESC 降序
若使用order by子句,则其一定是最后一个子句
可使用别名、列名、列号、表达式进行排序
多列排序顺序为从左至右为先后
group by子句
在使用分组函数后,则在任何不在分组函数中的列或表达式必须在group by子句中
进行分组后,结果更丰富,但是系统效率冲击较大
having子句
处理顺序
首先对记录进行分组
把所得到的分组应用于分组函数
最后显示满足having子句所指定的条件的结果
sqlplus 命令
desc emp
show all查看所有环境变量,使用set 进行设置
set linesize 80:每行显示宽度80个字符
set pagesize 50:报告的显示长度为25行
set heading off
set echo on
L和n text
n(设置当前行)A【ppend】附加
del
c【hange】
/:run命令
col[umn][{列名|别名}[可选项】】
col deptno for 999999:dept字段占6位数字
col loc
spool命令
保存命令后所有显示
spool d:\sql\output
spool off
脚本文件
生成:save d:\sql\sample
将脚本文件装入缓冲区:get d:\sql\sample.sql
编辑:ed d:\sql\sample
运行:start或者@d:\sql\sample.sql
函数
单行函数
null处理
null值
is null
is not null
升序,null最后
逻辑表达式
and优先级F-NULL-T
or的优先级T-NULL-F
not
函数
NVL
对数字型:NVL(COMM,0)
select avg(nvl(comm,0)) from emp;
select sum(comm),count(*),count(comm),avg(comm),avg(nvl(comm,0)) from emp ;
对字符型:NVL(to_char(comm),'no commission')
对日期型:NVL(hiredate,‘31-DEC-99’)
NVL2
nullif
decode
select ename,job, decode(job, 'SALESMAN',sal*1.15 'CLERK',SAL*1.2, SAL*1.4) "NEW SALARY" FROM EMP;
coalesce
分组函数,只能嵌套两层
count
count({*[distinct|all] 表达式})
返回非空行数目
可以通过distinct返回去重复数目
avg
sum
max
min
stddev
variance
多表连接
连接类型
equi join相等连接(内连接)
笛卡尔连接,通过where子句输出
self join自连接
自表连接:select w.empno,w.ename,w.job,w.mgr from emp w,emp m where w.mgr=m.empno
non—equijoin不等连接
select...where e.sal between s.losal and s.hisal...;
outer join外连接
运算符:(+)
+表示补充,即哪个表有加号,这个表就是匹配表。若加号写在右表,左表就是全部显示,故是左连接
外连接类型
left outer join左外连接,左表为基础表,右表为匹配表
right outer join右外连接
full outer join全外连接
select empno,ename,sal,emp.deptno,dept.deptno,loc from emp,dept where dept.deptno=emp.deptno(+);
select empno,ename,sal,emp.deptno,dept.deptno,loc from dept left outer join emp on( dept.deptno=emp.deptno);
子句
using子句
select e.empno,e.ename,e.sal,deptno,d.loc from emp e join dept d using (deptno) ;
on子句
select e.empno,e.ename,e.sal,e.deptno,d.loc from emp e join dept d on (e.deptno=d.deptno);
子查询
单行子查询
多行子查询
操作符
in
select empno,ename,job,sal from emp where sal in (select max(sal) from emp group by job);
any
select empno,ename,job,sal from emp where sal>any (select avg(sal) from emp group by job);
<any小于最大的
>any大于最小的
select a.ename,a.sal,b.ename,b.sal from emp a left outer join ( select empno,ename,job,sal from emp where sal<any (select avg(sal) from emp group by job) ) b on a.empno=b.empno;
=any相当于in
all
select empno,ename,job,sal from emp where sal>all (select avg(sal) from emp group by job);
>all 大于最大的
<all小与最小的
=all,子查询返回列表中所有值不符合逻辑
多列子查询
分类
成对比较多列子查询
select empno,ename,job,sal from emp where (sal,job) in (select max(sal),job from emp group by job);
非成对比较多列子查询
select empno,ename,job,sal from emp where sal in (select max(sal) from emp group by job) and job in (select distinct job from emp);
数据字典
分类
USER_*:有关用户所拥有的对象信息
ALL_*:有关用户可以访问的对象的信息
DBA_*:有关整个数据库中对象的信息
常用语句
select * from cat[user_catalog]:表名和类型
select name,created,log_mod from v$database
select instance_name,host_name,version,achiver from v$instance;
select username,created from dba_users; 创建用户及创建时间
替代变量
数字:select empno,ename,sal from emp where sal>&salary;
字符和日期:select empno,ename,sal from emp where sal>'&salary';
define v_col=job
accept
hide
undefine
select ename,job,&&v_col from emp order by &v_col;&&绑定变量后需要undefine v_col,否则变量一直在内存中不会改变
索引和约束
索引
create index emp_ename_idx on emp(ename);
create index emp_sal_idx on scott.emp(sal-2000);基于函数索引的权限问题
select * from user_indexes;
检查是否使用索引,运行utlxplan脚本,创建plan_table,在语句前加explan plan for,再查看plan_table
drop index scott.emp_ename_idx;
约束constraints
约束类型
非空not null
唯一unique:U
主键primarykey:P
外键foreign key:R
在进行插入操作时,只有操作在子表或从表一端才会产生违反引用完整性的问题,主表不会
在进行删除操作时,只有操作主表或父表一端。。。
alter table empcon add constraint empcon_deptno_fk forergn key(deptno) references deptcon(deptno);
条件check:C
create table person( id varchar2(1o), name varchar2(20), gender char(2), age number, constraint person_gender_ck check(gender='F'), constraint person_age_ck check(age between 18 and 30));
select * from user_constraints;
select * from user_cons_columns;
视图
CREATE [OR REPLACE] [FORCE|NOFORCE] VIEW 视图名 [别名[,别名]...] AS 子查询语句 [WITH CHECK OPTION[CONSTRAINT 约束名]] [WITH READ ONLY]
别名的个数要与列的个数一致
drop view 视图名
视图的DML操作限制
对视图的DML操作都可以转化为对视图所引用表的DML操作
序列号和同义词
序列号
CREATE SEQUENCE [START WITH n] [INCREMENT BY n] ...
nextval
currval
select * from user_sequences
alter sequence ...
drop sequence
同义词
CREATE SYNONYM S FOR SUPPIER;
drop synonym s;
用户管理
角色(role)
select * from role_sys_privs;
create role animal;
grant select any table,create table to animal;
grant animal to dog,cat,pig;
select * from dba_role_privs;
权限
系统权限
SELECT * FROM SESSION_PRIVS;
revoke select any table,create table from dog;
对象权限
分类
EXECUTE
PROCEDURE
ALTER
SEQUENCE
TABLE
SELECT
SEQUENCE
VIEW
TABLE
INDEX
TABLE
REFERENCES
TABLE
INSERT
VIEW
TABLE
UPDATE
VIEW
TABLE
DELETE
VIEW
TABLE
GRANT 对象的权限|ALL[列名【,列名。。。】 on 对象名 TO [用户名|角色名|PUBLIC] [WITH GRANT OPTION]
grant update(phone,fax) on supplier to cat;
select * from user_tab_privs_made;
sql
Data Manipulation Language:DML
select
insert
insert into worker (empno,ename,sal) values(66,'张三‘,4600);
insert into worker(empno,ename,sal) select empno,ename,sal from emp where job like 'sal%";
在使用子查询向某表插入数据时不能使用values关键字
insert子句中的列数和数据类型必须与子查询中的列数和数据类型一致
insert的替代变量
insert into emp_DML(empno,ename,job,mgr,hiredate,sal,comm,deptno) values(&id,'&name','&job',7689,to_date('&hiredate','yyyy mm dd'),666,77,66);
update
update emp set sal=sal*1.5 where ename='smith';
基于另外的表修改记录:update emp set sal=(select losal from salgrade where grade=1) where sal<(select losal from salgrade where grade=1);
多列子查询修改记录:update emp set (job,sal)=(select job,sal from emp where empno=7369) where jon='保安';
delete
delete from emp where (job='manage' and sal>2500) or (job='salesman' and sal>1300);
基于另外的表删除:delete from emp where deptno=(select deptno from dept where dname=upper('美容’);
Data Definition Language:DDL
create
table
create table product (p_code number(6), p_name varchar2(30));
varchar2(size)变长字符型数据,默认值和最小值都是1,最大值2000
char(size):定长字符型数据,最大值2000
date:日期型数据
number(P,S)数字型数据,1<p<38,-84<s<124
LOB:LARGE OBJECT
clob
blob
bfile
raw
create table worker as select empno,ename,job from emp;
user
CREATE USER 用户名 IDENTIFIED BY口令;
alter
table
alter table worker add (hiredate date);
alter table worker modify (hireddate default sysdate);
alter table worker drop column hiredate;
alter table worker set unsed(income);
alter table worker drop unused columns;
user
ALTER USER CAT DEFAULT TABLESPACE USERS QUOTA 20M ON USERS;
rename
rename 对象原来名字 对象现在的名字
truncate
truncate table worker;清空worker数据,保留表结构
drop
drop table worker;删除表
Transaction Control
commit
set autocommit on
rollback
Data Control Language:DCL
grant
revoke
运算符
连接运算符 ||
distinct运算符
作用于多列时,每一种组合显示一行
oracle通过排序完成此功能,效率较低
比较运算符
>
>=
<
<=
=
<>
!=
between...and... not between...and...
包含上下限
可用于数字,字符(需用单引号),日期型(需用单引号)
in、not in
like
%:0或者多个字符
_:一个且只能是一个字符
转义符\
集合操作符
分类
union
union all
minus
intersect:返回两查询结果相同的
人造列
select d.location_id,department_name "部门", to_char(null) "仓库地址" from departments d union select l.location_id,to_char(null) "部门", l.state_province from locations l
子句
where子句
字符串区分大小写
不能用分组函数
order by子句
排序
ASC 升序(默认)
DESC 降序
若使用order by子句,则其一定是最后一个子句
可使用别名、列名、列号、表达式进行排序
多列排序顺序为从左至右为先后
group by子句
在使用分组函数后,则在任何不在分组函数中的列或表达式必须在group by子句中
进行分组后,结果更丰富,但是系统效率冲击较大
having子句
处理顺序
首先对记录进行分组
把所得到的分组应用于分组函数
最后显示满足having子句所指定的条件的结果
sqlplus 命令
desc emp
show all查看所有环境变量,使用set 进行设置
set linesize 80:每行显示宽度80个字符
set pagesize 50:报告的显示长度为25行
set heading off
set echo on
L和n text
n(设置当前行)A【ppend】附加
del
c【hange】
/:run命令
col[umn][{列名|别名}[可选项】】
col deptno for 999999:dept字段占6位数字
col loc
spool命令
保存命令后所有显示
spool d:\sql\output
spool off
脚本文件
生成:save d:\sql\sample
将脚本文件装入缓冲区:get d:\sql\sample.sql
编辑:ed d:\sql\sample
运行:start或者@d:\sql\sample.sql
函数
单行函数
null处理
null值
is null
is not null
升序,null最后
逻辑表达式
and优先级F-NULL-T
or的优先级T-NULL-F
not
函数
NVL
对数字型:NVL(COMM,0)
select avg(nvl(comm,0)) from emp;
select sum(comm),count(*),count(comm),avg(comm),avg(nvl(comm,0)) from emp ;
对字符型:NVL(to_char(comm),'no commission')
对日期型:NVL(hiredate,‘31-DEC-99’)
NVL2
nullif
decode
select ename,job, decode(job, 'SALESMAN',sal*1.15 'CLERK',SAL*1.2, SAL*1.4) "NEW SALARY" FROM EMP;
coalesce
分组函数,只能嵌套两层
count
count({*[distinct|all] 表达式})
返回非空行数目
可以通过distinct返回去重复数目
avg
sum
max
min
stddev
variance
多表连接
连接类型
equi join相等连接(内连接)
笛卡尔连接,通过where子句输出
self join自连接
自表连接:select w.empno,w.ename,w.job,w.mgr from emp w,emp m where w.mgr=m.empno
non—equijoin不等连接
select...where e.sal between s.losal and s.hisal...;
outer join外连接
运算符:(+)
+表示补充,即哪个表有加号,这个表就是匹配表。若加号写在右表,左表就是全部显示,故是左连接
外连接类型
left outer join左外连接,左表为基础表,右表为匹配表
right outer join右外连接
full outer join全外连接
select empno,ename,sal,emp.deptno,dept.deptno,loc from emp,dept where dept.deptno=emp.deptno(+);
select empno,ename,sal,emp.deptno,dept.deptno,loc from dept left outer join emp on( dept.deptno=emp.deptno);
子句
using子句
select e.empno,e.ename,e.sal,deptno,d.loc from emp e join dept d using (deptno) ;
on子句
select e.empno,e.ename,e.sal,e.deptno,d.loc from emp e join dept d on (e.deptno=d.deptno);
子查询
单行子查询
多行子查询
操作符
in
select empno,ename,job,sal from emp where sal in (select max(sal) from emp group by job);
any
select empno,ename,job,sal from emp where sal>any (select avg(sal) from emp group by job);
<any小于最大的
>any大于最小的
select a.ename,a.sal,b.ename,b.sal from emp a left outer join ( select empno,ename,job,sal from emp where sal<any (select avg(sal) from emp group by job) ) b on a.empno=b.empno;
=any相当于in
all
select empno,ename,job,sal from emp where sal>all (select avg(sal) from emp group by job);
>all 大于最大的
<all小与最小的
=all,子查询返回列表中所有值不符合逻辑
多列子查询
分类
成对比较多列子查询
select empno,ename,job,sal from emp where (sal,job) in (select max(sal),job from emp group by job);
非成对比较多列子查询
select empno,ename,job,sal from emp where sal in (select max(sal) from emp group by job) and job in (select distinct job from emp);
数据字典
分类
USER_*:有关用户所拥有的对象信息
ALL_*:有关用户可以访问的对象的信息
DBA_*:有关整个数据库中对象的信息
常用语句
select * from cat[user_catalog]:表名和类型
select name,created,log_mod from v$database
select instance_name,host_name,version,achiver from v$instance;
select username,created from dba_users; 创建用户及创建时间
替代变量
数字:select empno,ename,sal from emp where sal>&salary;
字符和日期:select empno,ename,sal from emp where sal>'&salary';
define v_col=job
accept
hide
undefine
select ename,job,&&v_col from emp order by &v_col;&&绑定变量后需要undefine v_col,否则变量一直在内存中不会改变
索引和约束
索引
create index emp_ename_idx on emp(ename);
create index emp_sal_idx on scott.emp(sal-2000);基于函数索引的权限问题
select * from user_indexes;
检查是否使用索引,运行utlxplan脚本,创建plan_table,在语句前加explan plan for,再查看plan_table
drop index scott.emp_ename_idx;
约束constraints
约束类型
非空not null
唯一unique:U
主键primarykey:P
外键foreign key:R
在进行插入操作时,只有操作在子表或从表一端才会产生违反引用完整性的问题,主表不会
在进行删除操作时,只有操作主表或父表一端。。。
alter table empcon add constraint empcon_deptno_fk forergn key(deptno) references deptcon(deptno);
条件check:C
create table person( id varchar2(1o), name varchar2(20), gender char(2), age number, constraint person_gender_ck check(gender='F'), constraint person_age_ck check(age between 18 and 30));
select * from user_constraints;
select * from user_cons_columns;
视图
CREATE [OR REPLACE] [FORCE|NOFORCE] VIEW 视图名 [别名[,别名]...] AS 子查询语句 [WITH CHECK OPTION[CONSTRAINT 约束名]] [WITH READ ONLY]
别名的个数要与列的个数一致
drop view 视图名
视图的DML操作限制
对视图的DML操作都可以转化为对视图所引用表的DML操作
序列号和同义词
序列号
CREATE SEQUENCE [START WITH n] [INCREMENT BY n] ...
nextval
currval
select * from user_sequences
alter sequence ...
drop sequence
同义词
CREATE SYNONYM S FOR SUPPIER;
drop synonym s;
用户管理
角色(role)
select * from role_sys_privs;
create role animal;
grant select any table,create table to animal;
grant animal to dog,cat,pig;
select * from dba_role_privs;
权限
系统权限
SELECT * FROM SESSION_PRIVS;
revoke select any table,create table from dog;
对象权限
分类
EXECUTE
PROCEDURE
ALTER
SEQUENCE
TABLE
SELECT
SEQUENCE
VIEW
TABLE
INDEX
TABLE
REFERENCES
TABLE
INSERT
VIEW
TABLE
UPDATE
VIEW
TABLE
DELETE
VIEW
TABLE
GRANT 对象的权限|ALL[列名【,列名。。。】 on 对象名 TO [用户名|角色名|PUBLIC] [WITH GRANT OPTION]
grant update(phone,fax) on supplier to cat;
select * from user_tab_privs_made;