oracle学习笔记

创建表空间

create tablespace name
datafile ‘d:’
size 20M
autoextend on;

创建用户

create user chen
identified by 123456
default tablespace name
temporary tablespace temp;

查询所有用户

select * from dba_users;

解锁

alter user scott account unlock;

赋予权限

grant dba to chen;

收回权限

revoke dba from chen;

数据库管理系统（DBMS）

数据查询语言（DQL）:select
数据操作语言（DML）:insert,update,delete
数据控制语言（DCL）:grant,revoke
事物处理语言（TPL）:commit,rollback
数据定义语言（DDL）:create,drop,alter

创建表 ,给约束

create table tb_user(
id int primary key,
username varchar2(50) not null,
sex char(2) check(sex=‘男’ or sex=‘女’),
age int check(age>0 and age<100),
address varchar2(50) default’guangzhou’,
phone varchar2(11) unique,
id_class int references tb_class(id)
)

create table tb_class(
id int primary key,
code varchar2(20),
teachar varchar(20)
)

删表

drop table tb_user;

删表数据

delete from tb_user;

截断表（删表数据）

truncate table tb_user;

改表名

rename tb_user to tb_person;

说明

comment on table tb_person is ‘用户表’;

改表结构

alter table tb_person add(address varchar2(20));
alter table tb_person modify(address varchar2(50));
alter table tb_person drop(address);
alter table tb_person rename column address to place;

插入数据

insert into tb_person(id,username,sex,age,phone)
values(1,‘chen’,‘女’,20,139000000000);

修改数据

update tb_person
set age=21
where name=‘chen’;

删除数据

delete from tb_person
where id=1;

复合约束

create table tb_report(
year char(4),
month char(2),
unique(year,month)
);
alter table tb_report add primary key(year);

别名

select year as 年 ,month 月 from tb_report;

去重

select distinct year from tb_report;

– 比较符号：> 大于号、< 小于号、= 等于、<> 不等于、>= 大于等于、<= 小于等于

模糊查询

select * from tb_person
where username like ‘%c%’;

排序

– 排序：升序（默认） asc、降序 desc
select * from tb_person
order by id;

scott 用户查询奖金不为空员工信息 和年薪
select emp弄，enamel，job，sal,comm,(sal+nvl(comm,0))
from emp where comm is not nul;

多表查询

内连

select * from emp,dept
where emp.deptno=dept.deptno;

select * from emp e inner join dept d
on e.deptno=d.deptno;

左外连(左信息全)

select * from emp e，dept d
where e.deptno=d.deptno(+);

select * from emp e left outer join
dept d on e.deptno=d.deptno;

右外连

select * from emp e,dept d
where e.deptno(+)=d.deptno;

select * from emp e right outer join
dept d on e.deptno=d.deptno;

全外连

select * from emp e full outer join dept d
on e.deptno=d.deptno;

自连

select e1.empno,e1.ename,e1.mgr,e2.ename
from emp e1,emp e2
where e1.mgr=e2.empno;