oracle常用指令
- desc:查看表结构
- list: 查看缓冲区指令(简写为l)
- 将屏幕中的内容保存到文件中
- spool 文件
- spool off
- 帮助命令:help 命令
- 清除屏幕内容和屏幕缓冲区内容
clear screen
shift + delete --同时按下
- set linesize 500
3.2 环境变量的设置与显示
show 命令----显示
show autocommit
set 命令----设置
- autocommit: 是否自动提交DML语句
- 设置列和列之间的间隔符:set colsep |
- feedback : 显示反馈信息的最低行数,默认值是6
- heading: 是否显示列标题 set heading on/off
- time: 命令提示符前面显示时间
- timing: 设置执行sql语句的时间
- 环境变量的备份
store set 文件
- arraysize: 设置从数据库中提取的行数。默认值是15。 set arraysize 20
oracle数据类型
数值类型
NUMBER(precision,scale) --precision:精度 scale:小数位数
NUMBER(5,2) --整数位数为2,小数位数为3,25.112
字符类型
CHAR --固定长度的字符串
CHAR(5) --5个字节的字符串,当长度小于5的时候自动补空格,总是包含5个字节的信息,超过则报错,缺省值为1(1个汉字占两个字节)
VARCHAR2 --可变长度的字符串
VARCHAR2(10) --表示占10个字节的字符串,当长度小于10字节时,不会自动不空格,占实际大小,大于则报错,缺省值为1
NCHAR --根据字符集而定的固定长度字符串,最大长度为2000bytes
NVARCHAR2 --根据字符集而定的可变长度字符串,最大长度4000bytes
--字符集设置成ZHS16GBK:汉字占2个字节
--字符集设置成AL32UTF8:汉字占3个字节
日期类型
DATE --存储固定日期和时间,一般占用7个字节的存储空间
TIMESTAMP --支持小数秒
ROWID
ROWID --为该表行的唯一标识,是一个伪劣,科研用在select中
--Oracle使用ROWID来建立内部索引,你可以引用ROWID的值,但ROWID并不存放在数据库中
3.3 查询
SELECT 列名1,列名2,... FROM 表名;
- 查询所有信息
SELECT * FROM emp; --尽量不使用这种方式
- 算术运算符: + - * /
-- 查询雇员的编号,工资和年薪(不包括补助)
SELECT empno,sal,12*sal FROM emp;
数字,日期都可以使用算术运算符
- 基于文本的过滤条件
--查询以"DB_"开头,且倒数第3个字符为i的课程的详细情况
select *
from Course
where Cname like 'DB\_%i__' escape '\';
- 空值:无效的,未定s义的。空值不是0。空值不可以进行
nvl(可能产生空值的列,指定值) --将空值变为指定值
nvl(comm,0)
- 列的别名
SELECT empno,sal "FROM" FROM emp; --as可以省略
SELECT empno,sal+1 as " " FROM emp;
- 连接符:列与列之间的合并显示/列与字符串的合并
SELECT empno|ename FROM emp;
SELECT 'id is:'||empno||',name is'||ename FROM emp;
- 去掉重复行:distinct
- 只能放在第一列的前面
- 不能联合列去重
- distinct 列:列可以进行算术运算
- 会保留空值
SELECT distinct job FROM emp;
- 排序
SELECT 列名1,列名2,... FROM 表名
ORDER BY 排序列名1 ASC/DESC,排序列名2 ASC/DESC;
函数
大小写控制函数
- LOWER(列名|表达式),UPPER,initcap(每个单词的首字母大写)
select lower("Hello,world") from dual;
select * from emp where ename = upper("King")
字符控制函数
length('Hello,world') --求长度
substr('sadasd',1,5) --sadas
select instr('helloworld','l') from dual; --返回结果:3 默认第一次出现“l”的位置
CONCAT('CSDN','_YEEXUN') --CSDN_yeeXun
trim(' Hello world ') --去除左右两边的空格
数值
round(列名|表达式,n) --将列或表达式所表示的数值四舍五入到小数点后的第n位
round(45.934,2) --45.93
trunc(列名|表达式,n) --将列或表达式所表示的数值截取到小数点后的第n位
日期
--sysdate 系统日期
select sysdate from dual; --2014/4/30 14:11:33 星期三
select next_day(sysdate,'星期日') from dual; --得到日期为:2014/5/4 14:12:53
--在指定日期上增加月数
add_months(hiredate,24); --增加一年
--计算两个日期之间有几个月
months_between(sysdata,hiredate);
--返回指定日期所在月份的最后一天
last_day(sysdate);
通用
decode --if else
decode(Dtype,
0,'可销售',
1,'废品')
嵌套查询
--查询其他系中比信息系中某一个学生年龄小的学生姓名和年龄
select sname,sage
from Student
where Sage < ANY(select Sage from Student where Sdept='IS')
AND Sdept <> 'IS';
--查询所有选修了1号课程的学生姓名
select Sname
from Student
where exists (select * from SC
where Sno=Student.Sno And Cno='1')
--查询没有选修1号课程的学生姓名
select Sname
from Student
where not exists (select * from SC
where Sno=Student.Sno And Cno='1')
--将计算机系全体同学的成绩置零
update SC
set grade = 0
where exists(select *
from Student
where Student.Sno=SC.Sno and Student.Sdept='CS')
集合查询
并
--查询计算机科学系的学生及年龄不大于19岁的学生
select * from Student where Sdept = 'CS'
UNION
select * from Student where Sage<= 19;
交
--查询计算机系的学生及年龄不大于19岁的学生集合的交集
select * from Student where Sdept = 'CS'
INTERSECT
select * from Student where Sage<= 19;
差
--查询计算机系的学生及年龄不大于19岁的学生集合的差集
select * from Student where Sdept = 'CS'
MINUS
select * from Student where Sage<= 19;
层次查询
select [level] 列名
from 表名
[where]
[start with] --从哪开始
[connect by 条件] --条件:父与子的关系
select empno,ename,mgr from emp
start with empno=7839
connect by prior empno = mgr
column empno format A30;
column ename format A50;
select lpad(' ',5*level-1)||empno EMPNO,lpad(' ',5*level-1)||ename ENAME from emp start with empno=7839--lpad:左填充
connect by prior empno = mgr
select * from table; -- 查询库中存在的表
插入
插入单值
标准
insert into 表名(列名1,列名2,...) values(值1,值2,...)
省略写法
insert into (select empno,ename,job from emp) values(9000,'张三1','农民')
insert into emp(empno,ename) select 9001,'张三2' from dual
- dual是临时表
利用子查询插入数据(批量插入)
insert into 表名(列名1,列名2,...) select 语句
- 向emp表中插入员工信息: empno=2345,ename=‘Tom’,其他信息与员工empno=7369的员工信息一致
create table emp2 as select * from emp where 1=2 --复制表结构
insert into emp select 2345,'Tom',job,hiredate,sal,comm,ename from emp where empno=7369
- 如果想要进行大量数据的插入(装载操作),可以利用 /+APPEND/关键字实现:不写日志,执行速率大大提高
insert /*+APPEND*/ into emp(empno,ename) select 22,'tom' from dual
向多个表插入数据
无条件多表插入
insert ALL
into emp1 values(empno,ename)
into emp2 values(empno,ename)
...
select empno,ename from emp where sal>800
有条件多表插入
insert all|First --ALL全部都插入(有一条数据既符合第一条,也符合第二条,全部都插入)
when 条件1 then into 表1(列1,列2,...)
when 条件2 then into 表2(列1,列2,...)
...
else into 表n(列1,列2,...)
子查询
多表插入的应用
修改数据
update emp set sal=50 where empno=100
update emp set ename='simth',hiredate=to_date('2001-01-01','yyyy-mm-dd') where empno = 100
update emp set(ename,sal) = (select'allen',10000 from dual) where empno=100
update emp set sal = 300+(select avg(sal) from emp where deptno=20) where deptno=30
删除数据
全部删除
delete from 表名
commit
局部删除
delete from Student where Sno ='2010200';
Truncate table表名
rownum表示行号,但它是伪列,一般用来做分页的操作
--显示前5行
select ROWNUM,emp.* from emp where ROWNUM<=5;
--显示6-10行
select * from (select ROWNUM m,empno,ename,job from emp where ROWNUM<=10) where ROWNUM>5;
建表
create table 表名(
列名 数据类型[Default 默认值],
列名 数据类型[Default 默认值]
)
create table Student(
Sno CHAR(9) PRIMARY KEY,
Sname CHAR(20) UNIQUE NOT NULL,
Ssex CHAR(2),
Sage NUMERIC(2,0),
Sdept CHAR(20),
FOREIGN KEY (Sdept) references Dept(Sdept)
);
create table person(
ename VARCHAR(10),
eid VARCHAR(18),
age NUMBER(3),
sex VARCHAR2(4) default '男',
constraint pk_eid primary key(eid),
);
设置格式
- 修改查询后输出的格式
column命令:修改列的样式 --可以简写为col
- HEADING
- JUSTIFY:对齐方式
- CLEAR:清除列的样式
- FORMAT:指定列的样式
column ename HEADING 员工名字 JUSTIFY center FORMAT A3
- A: 表示宽度(显示几个字符)
column sal HEADING 工资 JUSTIFY left FORMAT L9999.99
- L :代表本地货币
显示格式
column sal
--显示所有列的样式
column
--清除列的样式
column sal clear
--清除所有列的样式
clear column
压缩重复列值
break on 列名 [skip 值]
- skip: 压缩完之后组和组之间的距离
break on deptno skip 1
分组统计显示
compute 组函数
- 组函数:sum,count,avg,max,min
break on deptno
compute avg label '平均工资' of sal on deptno
用于设置显示输出宽度
set linesize 110; --设置一行的长度
设置标题和页脚
ttitle --标题
btitle -- 页脚
ttitle center 'EMPLOYEES'
btitle right 'MADE BY www'
repheader off -- 不显示标题
ttitle off
btitle off
删除表
drop table 表名 --以上是把表放在回收站中,并不是真正意义上的删除
--从回收站中恢复表
flashback table 表名 to before drop
--查询回收站中的表
select object_name,original_name,operation,type from recyclebin
drop table 表名 purge --彻底删除,不经过回收站
--从回收站中将某个表删除
purge table 表名;
--清空回收站
purge recyclebin;
--不可以将回收站中的表全部恢复,只能先恢复最近的一个
修改表
增加新的列
alter table 表名 add(列名 数据类型 DEFAULT 默认值)
alter table person add(age number(2),sex varchar(4))
desc person; --显示表信息
修改列的类型和长度
alter table 表名 modify(列名 数据类型 DEFAULT 默认值)
修改列名
alter table 表名 rename column 现列名 to 新名
alter table person rename column ename to name;
删除列
alter table 表名 drop column 列名
--删除多列:只能一列一列删除
表的重命名
rename 旧名 to 新名
rename operson to nperson;
修改列的状态,不删除,但是让它变成无效状态
alter table person set unused column
删除无用的列
alter table 表名 drop unused columns;
为表添加注释
comment on table 表名 is 注释内容
删除表的约束
--删除学生姓名必须取唯一值的约束
alter table student drop unique(sname);
视图
create view emp_view as
select * from emp
where empno = 5;
update emp_view set empno=6 where empno = 5; --emp_view没有with check option选项,故可以成功执行,若建立视图的时候有with check option选项,则报错,因为经过update之后新的记录无法出现在视图中了
序列
create sequence test_seq
startwith 10
increment by 2
maxvalue 100
minvalue 9
cycle
cache 10 --缓存中序列值的个数为10
- NEXTVAL:伪劣,序列返回的下一个值
- CURVAL:伪劣,返回序列的当前值
create table stu{
std number(3),
sname VARCHAR2(5)
};
create sequence test_seq
startwith 10
increament by 1;
insert into stu values(test_seq.NEXTVAL,'TOM');
select test_seq.CURVAL from dual;
- CURVAL在被引用之前,必须先使用NEXTVAL来产生一个序列值
修改序列
alter sequence 序列名
[increment by n]
[maxvalue n]
[minvalue n]
[cycle nocycle]
[cache n nocache]
删除序列
drop sequence 序列名
- ROWID:伪列,系统自动生成,ROWID表示的是每个数据库的记录的物理地址。这个地址是唯一的,好处是可以快速定位到记录上。
数据对象编号 | 相关文件编号 | 块编号 | 行编号 |
---|---|---|---|
000000 | fff | BBBBBB | RRR |
索引(INDEX):
- 单列索引
- 复合索引
创建索引
自动创建
- 在建表的时候,使用了PRIMARYKEY或unique约束时,数据库会自动创建一个索引
手工创建
create index 索引名
on 表名(列,...)
- 说明:关于索引名的命名规范:idx_表名_列名
create index idx_emp_ename
on emp(ename);
create index idx_emp_deptnojob
on emp(deptno,job);
drop index 索引名
用户管理
Oracle数据库初始用户
SYS
SYSTEM
SCOTT:用于测试网络连接的用户
PUBLIC:实质上是一个用户组,数据库中任何一个用户都属于该组。要为某个权限,只需要把权限授予PUBLIC就可以了。
用户属性
创建账号
create user 用户名 identified
[by 密码 externally|globally as 'external_name']
[default tablespace tablespace_name]
[temporary tablespace temp_tablespace_name]
[quota n K|MUNLIMITED on tablespace_name]
[profile profile_name]
[password expire]
[account lock|unlock]
--创建一个用户user3,口令为user3,默认表空间为users,在该表空间的配额为10mb,初始状态为锁定
create user user3 identified by user3
default tablespace users quota 10M
on users acount lock;
--将账号为user3的密码改为newuser3,同时将该用户解锁
alter user user3 identified by newuser3 account unlock;
--修改用户user3的默认表空间为orcltbs1,该表空间的配额为20MB,在USER表空间的配额为10MB
alter user user3 default tablespace orcltbs1 quota 20MB on user orcltbs1 quota 10MB on USERS;
--删除用户
drop user 用户名[cascade];
权限管理
系统权限
- 在给用户授予系统权限时,需要注意四个问题
- 只有DBA才有alter database系统权限
- 应用程序开发者一般给他的系统权限是create table,create view,create index
- 普通用户一般只给create session系统权限
- 只有授权时带有with admin option子句时,用户才可以将获得的权限再次授予其他人,也就是我们说的系统权限的传递性
语法格式
grant 权限 to 用户列表|public[with admin option];
--为public组授权create any view权限
grant create any view to public;
grant create session,create table,create view to user1;
grant create session,create table,create view to user1,user2;
--回收
revoke 权限 from 用户列表|角色列表|public;
revoke create table from user1;
对象权限
grant 对象权限 on 对象 to 用户列表|角色列表[with grant option];
--将emp表中的select,insert,update权限授予user1用户
grant select,update,insert on scott.emp to user1;
--对象权限的回收
revoke 对象权限列表 all on 对象 from 用户列表|角色列表
--回收user1用户在emp表上的select,udate权限
revoke select,update on scott.emp from user1;
查询权限信息
select * from DBA_TAB_PRIVS;
ALL_TAB_PRIVS;
USER_TAB_PRIVS;
DBA_COL_PRIVS;
ALL_COL_PRIVS;
USER_COL_PRIVS;
DBA_SYS_PRIVS;
USER_SYS_PRIVS;
角色管理
角色:一系列相关权限的集合
角色的分类
- 系统预定义角色
用户自定义角色
--创建角色
create role 角色名称 [not identified] [identified by 密码]
create role high_tiger_role;
create role middle_tiger_role identified by middlerole;
--角色权限的授予回收
grant create table,create view to low_riger_role
--注意:给角色授权时需要注意一个事情,一个角色可以被授予另一个角色,但不能授予其本身,不能产生循环授权
--修改角色:让角色生效或失效
alter role 角色名称[not identified] [identified by 密码]
--eg:为high_tiger_role角色添加口令,取消middle_tiger_role的角色口令
alter role high_tiger_role identified by highrole
alter role middle_tiger_role not identified
--注意:修改角色必须具有alter any role系统权限以及with admin option权限,如果是角色的创建者,则自动具有对该角色的修改权限
--角色的生效和失效
--所有角色失效
set role none
--设置一个角色生效
set role high_tiger_role identified by highrole;
利用角色进行权限管理
给用户或角色授予角色
grant 角色列表 to 用户列表|角色列表
--将connect,high_tiger_role角色授予用户user1
grant connect,high_tiger_role to user1;
grant resource,connect to middle_tiger_role;
用户角色的激活或屏蔽
alter user 用户名 default role [角色名称] [all [except 角色列表]]
--屏蔽所有角色:
alter user user1 default role none;
-
数据库的组成文件
- 日志文件,数据文件,控制文件
-
数据库的监听配置文件
- listener.ora
-
DML
- insert,delete,update,select
-
DDL
- create,alter,drop
-
DCL
- revoke,grant
-
常用完整性约束
- 主码约束:PRIMARY KEY
- 唯一性约束:UNIQUE
- 非空值约束:NOT NULL
- 参照完整性约束:FOREIGN KEY
-
事务的特征:
- 原子性
- 一致性
- 持久性
- 隔离性
-
Oracle事务:
- 执行rollback或commit都可以结束事务
- 事务开始后,执行DCL语句可以结束事务
- 通过savepoint,可控制rollback回滚范围
-
视图和表的异同
- 视图是在基本表上建立的表,它的结构和内容都来自表,依据基本表存在而存在。一个视图对应一个基本表,也可以对应多个基本表。视图是基本表的抽象和在逻辑意义上建立的新关系。
- 视图是已经编译号的sql语句,而表不是
- 视图是内容,表是窗口
- 表是内模式,视图是外模式
- 视图的建立和删除只影响视图本身,不影响对应表的基本表
- 视图没有实际的物理记录,而表有
--查询工资高于本部门平均工资的员工信息及其部门的平均工资
select e.*,avgsal from employees e join(
select department_id,avg(salary) avgsal from employees group by department_id) d on d.department_id = e.department_id and e.salary > d.avgsal;
--查询30号部门中工资排序前3名的员工信息
select deptno, ename, sal
from emp e1
where(
select count(1)
from emp e2
where e2.deptno=e1.deptno and e2.sal>=e1.sal
) <=3
--查询所有员工中工资排序在5-10名的员工信息
select * from (select s.*,rownum rn from(
select * from emp e by sal desc) s
where rn between 5 and 10);
--查询在2月份入职的所有员工信息
select * from employees where extract(month from hire_date)=2;
--查询每个部门工资最低的员工姓名,部门名称和工资
select ename,dname,esal
from emp,dept
where emp.dno = dept.dno and(emp.dno,esal) in (select dno,min(esal) from emp group by dno);