#创建用户与赋予权限(默认情况为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’)