Oracle常用操作指令与SQL数据操作和查询

#创建用户与赋予权限(默认情况为lock)
create user chenghao identified by chenghao account unlock;
#授权
grant connect to chenghao;
grant resource to chenghao;
grant dba to chenghao;
//回收权限
REVOKE 角色|权限 FROM 用户（角色）
//修改用户的密码
ALTER USER 用户名 IDENTIFIED BY 新密码
//修改用户处于锁定（非锁定）状态
ALTER USER 用户名 ACCOUNT LOCK|UNLOCK

#查询系统当前的日期和时间，sysdate返回DATE类型，用systimestamp可以返回系统当前的日期、时间和时区
select sysdate,systimestamp from dual;

#创建表和约束(’/‘是为了执行缓存区的语句，没有分号的语句只保存在缓存区，’/'将其执行)
CREATE TABLE INFOS
(
STUID VARCHAR2(7) NOT NULL,
STUNAME VARCHAR2(10) NOT NULL,
GENDER VARCHAR2(2) NOT NULL,
AGE NUMBER(2) NOT NULL,
SEAT NUMBER(2) NOT NULL,
ENROLLDATE DATE,
STUADDRESS VARCHAR2(50) DEFAULT ‘地址不详’,
CLASSNO VARCHAR2(4) NOT NULL
)
/
alter table infos add constraint pk_infos primary key(stuid)
/
alter table infos add constraint ck_infos_gender
check (gender = ‘男’ or gender = ‘女’)
/
alter table infos add constraint ck_infos_seat
check(seat >= 0 and seat <= 50)
/
alter table infos add constraint ck_infos_age
check(age >= 0 and age <= 100)
/
alter table infos add constraint ck_infos_classno
check((classno >=‘1001’ and classno <= ‘1999’)
or
(classno >=‘2001’ and classno <= ‘2999’))
/
alter table infos add constraint un_stuname unique(stuname)
/

#创建一个成绩表和约束,创建外键约束
create table scores
(
id number,
term varchar2(2),
stuid varchar2(7) not null,
examno varchar2(7) not null,
writtenscore number(4,1) not null,
labscore number(4,1) not null
)
/
alter table scores add constraint ck_scores_term
check(term = ‘s1’ or term = ‘s2’)
/
alter table scores add constraint fk_scores_infos_stuid
foreign key(stuid) references
infos(stuid)
/

#根据结果集创建表，不会保留表约束
create table infos1 as select * from infos;
#复制表结构
create table infos2 as select * from infos where 1=2;

#向Infos和scores表中插入数据(在Oracle中日期是国际化的，因此需要对日期进行格式化)
insert into infos values(
‘s100102’,‘林冲’,‘男’,22,2,
to_date(‘2009-8-9 06:30:10’,‘YYYY-MM-DD HH24:MI:SS’),
‘西安’,‘1001’
)
/
insert into infos values(
‘s100104’,‘袁潇儿’,‘女’,26,3,SYSDATE,default,‘1001’)
/
#提交增、删、改的操作
commit
/

#insert向表中插入一个结果集
insert into infos2 select * from infos;
commit;

#insert 向表中插入一个常量结果集
insert into infos
select ‘s100106’,‘卢俊义’,‘男’,23,5,
to_date(‘2008-8-9 08:00:10’,‘YYYY-MM-DD HH24:MI:SS’),‘青龙寺’,‘1001’
from dual
/
commit
/

#update操作
update infos set classno = ‘1002’,stuaddress = ‘山东莱芜’
where stuname = ‘袁潇儿’;
commit;

#delete操作
delete from infos where stuid = ‘s100103’;

#truncate命令(他是一个ddl操作，删除的数据不能恢复，但是delete命令是dml命令，删除后可以通过日志文件恢复)
#慎用
truncate table tablename;

#算数运算，只有±/四种，求余数的话要用函数MOD(x,y):返回x除以y的余数
select stuname,age,(age10-8) from infos where age < 26;

#字符串连接(将查询到的结果用字符串连接在一起)
select (stuname || 'from ’ ||stuaddress) as “hometown”
from infos
where age < 25;

#去重，利用distinct
select distinct gender from infos;

#安装oracle示例用户scott。首先cd到/opt/oracle/product/11.2.0/db_1/rdbms/admin
#然后执行sqlplus / as sysdba
#执行@utlsampl.sql
#然后就可以查询scott下的emp表的
select * from scott.emp;

#emp中的null值
select ename,job,sal,comm from scott.emp where sal < 2000;

#查询emp表中没有发放奖金的员工
select ename,job,sal,comm from scott.emp
where sal < 2000
and comm is null;

#in操作(引号中的内容区分大小写)
select ename,job,sal from scott.emp
where job in(‘SALESMAN’,‘PRESIDENT’,‘ANALYST’);

#between操作
select ename,job,sal from scott.emp where sal between 1000 and 2000;
#也可以使用传统方法
select ename,job,sal from scott.emp where sal >= 1000 and sal <= 2000;

#like模糊查询
#%：表示零个或者多个任意字符
#_：代表一个任意字符
#语法是：LIKE ‘字符串’[ESCAPE ‘字符’]。匹配的字符串中，ESCAPE 后面的“字符”作为转 义字符。
#显示员工名称以J开头以S结尾的员工的姓名、工资
select ename,job,sal from scott.emp where ename like ‘J%S’;

#集合运算
#intersect交集，union all并集,union并集，minus补集
#查询出dept表中那个部门下没有员工。只需要求出dept表中的部门号和emp表中的部门号的补集合即可。
select deptno from scott.dept
minus
select deptno from scott.emp

#用union插入多条数据
insert into scott.dept
select 50,‘公关部’,‘台湾’ from dual
union
select 60,‘研发部’,‘西安’ from dual
union
select 70,‘培训部’,‘西安’ from dual
/

#查询出工资大于2000的员工姓名部门工作工资
select e.ename,e.job,e.sal,d.dname
from scott.emp e,scott.dept d
where e.deptno = d.deptno
and e.sal > 2000;
#也可以使用sql标准内连接
select e.ename,e.job,e.sal,d.dname
from scott.emp e
inner join scott.dept d
on e.deptno = d.deptno
where e.sal > 2000;

#请查询出每个部门下的员工姓名、工资
select e.ename,e.job,e.sal,d.dname
from scott.emp e,scott.dept d
where e.deptno(+) = d.deptno
/
#也可以使用标准写法
SELECT e.ENAME,e.JOB,e.SAL,d.DNAME
FROM scott.EMP e
RIGHT OUTER JOIN scott.DEPT d
ON e.DEPTNO=d.DEPTNO

#推荐使用sql/92表的写法

#练习
#1.显示与blake在同一部门工作的雇员的项目和受雇日期，但是blake不包含在内
select ename,job,hiredate from scott.emp
where deptno in
(
select deptno from scott.emp
where ename = ‘BLAKE’
)
and ename <> ‘BLAKE’;

#2.显示位置在Dallas的部门内的雇员姓名、变化以及工作
select ename,job from scott.emp
where deptno in
(
select deptno from scott.dept
where loc = ‘DALLAS’
)

#3.显示被KING直接管理的雇员的姓名以及工资
select ename,sal from scott.emp
where mgr in
(select empno from scott.emp where ename = ‘KING’)

#4.创建一查询，显示能获得与SCOTT一样工资和奖金的其他雇员的姓名、受雇日期以及工资
select ename,hiredate,sal,comm
from scott.emp
where sal in (select sal from scott.emp where ename = ‘SCOTT’)