Oracle数据常用语句（一）

一、导入数据和导出数据

 

--drop table bonus;

drop table emp;

--drop table dept;

--drop table salgrade;

 

--1、将D:\表备份.dmp文件的数据导入到scott用户，并且导入数据行，并且指定日志

--imp scott/root file=D:\表备份.dmp tables=(dept,emp)

--rows=y indexes=y log=D:\imp_scott_dept_emp_20170727.log;

 

--2、将D:\表备份.dmp中的所有数据导入到scott用户

--imp scott/root file=D:\表备份.dmp full=y;

--  

--scott -> newuser;

 

--3、导出scott用户的指定表的内容到D:\scott_alltable.dmp，并指定日志文件

exp scott/root tables=(donus,dept,emp,salgrade)

file=D:\scott_alltable.dmp

rows=y log=exp_scott_alltable.log;

 

--4、通过scott用户删除newuser用户的表格

drop table newuser.emp;

drop table newuser.dept;

drop table newuser.salgrade;

imp scott/root file=d:\scott_alltable.dmp

fromuser=scott touser=newuser tables=(dept,emp,salgrade)

rows=y log=imp_to_newuser_alltable_2017727.log

 

--5.将emp表中工资在800到2000之间职信息导出

select * from emp;

drop table emp;

delete from emp;

imp scott/root file=D:\表备份 tables=emp rows=y;

exp scott/root file=D:\emp_800_2000.dmp tables=emp query=scott.emp:\"where sal\>800\";

 

 

--6、使用exp 将emp,dept 所有行导出到exp_scott_emp_dept.dmp

exp scott/root file=exp_scott_emp_dept.dmp tables=(dept,emp);

drop table emp;

drop table dept;

drop table bonus;

imp scott/root file=exp_scott_emp_dept.dmp tables=(dept,emp) rows=y ignore=y;

 

二、 创建表格

--1、创建表格,student

create table student(

sid varchar2(16),   --学号

stuname varchar2(24),  --姓名

age number(3),  --年龄

sex varchar2(8), --性别

tel varchar2(16), --电话

bdate date,       --生日

cardid varchar2(18) --身份证

);

 

drop table student;

--2、创建表格,student，增加check约束

create table student(

sid varchar2(16),   --学号

stuname varchar2(24),  --姓名

age number(3) constraint chk_age check(age > 18),

--sex varchar2(4) constraint chk_sex check(sex = '男' or sex = '女'),

sex varchar2(4) constraint chk_sex check(sex in ('男','女')),

tel varchar2(16), --电话

bdate date constraint chk_bdata check(bdate < to_date('2009-07-26', 'yyyy-MM-dd')),

cardid varchar2(18) constraint chk_cardid check(length(cardid) = 18 or length(cardid) = 16)

);

 

insert into student(cardid) values('123456789123456789'); --成功

insert into student(cardid) values('1234567891234567'); --成功

insert into student(age) values(19);  --执行成功

insert into student(age) values(15);  --执行失败，违反chk_age约束

insert into student(sex) values('男'); --成功

insert into student(sex) values('中'); --失败

insert into student(bdate) values(to_date('2009-07-22', 'yyyy-MM-dd')); --成功

insert into student(bdate) values(to_date('2009-07-29', 'yyyy-MM-dd')); --失败

select * from student;

 

drop table student;

--3、创建表格, not null约束

create table student(

sid varchar2(16) constraint notnull_sid not null,   --学号

stuname varchar2(24) not null,  --姓名

age number(3),  --年龄

sex varchar2(8), --性别

tel varchar2(16), --电话

bdate date,       --生日

cardid varchar2(18) --身份证

);

 

insert into student(sid,stuname) values('1','zhangsan'); --成功

insert into student(sid) values('zhangsan'); --失败

 

drop table student;

--3、创建表格, unique唯一性约束

create table student(

sid varchar2(16) constraint unique_sid unique,   --学号

stuname varchar2(24),  --姓名

age number(3),  --年龄

sex varchar2(8), --性别

tel varchar2(16), --电话

bdate date,       --生日

cardid varchar2(18) unique --身份证

);

 

insert into student(age) values(11); --重复插入成功

insert into student(sid) values('20170501'); --不能重复插入

 

drop table student;

--4、创建表格, 主键约束

create table student(

sid varchar2(16) constraint pk_sid primary key,   --学号

--sid varchar2(16) primary key,

stuname varchar2(24),  --姓名

age number(3),  --年龄

sex varchar2(8), --性别

tel varchar2(16), --电话

bdate date,       --生日

cardid varchar2(18),

--cardid varchar2(18) primary key--错误，一个表只能有一个主键

);

 

insert into student(sid) values('20170501'); --不能重复插入

 

--5外键约束

drop table student;

create table student(

sid varchar2(16) constraint pk_sid primary key,   --学号

stuname varchar2(24),  --姓名

age number(3),  --年龄

sex varchar2(8), --性别

tel varchar2(16), --电话

bdate date,       --生日

cardid varchar2(18)

);

 

drop table class;

create table class(

cno varchar2(16),

cname varchar2(16),

tname varchar2(16),

constraint pk_class_cno primary key(cno)

);

 

create table grade(

gsid varchar2(16),

gcno varchar2(16),

grade number,

constraint fk_grade_gsid foreign key(gsid) references student(sid),

constraint fk_grade_gcno foreign key(gcno) references class(cno),

constraint pk_grede_gsid_gcno primary key(gsid,gcno) --联合主键

);

 

insert into student(sid) values('1');

insert into class(cno) values('1');

insert into grade values('1','1',66);

select * from grade;

select * from student;

delete from grade where gsid = '1';

delete from student where sid = '1';

--有主外键表的数据插入和删除

--1、插入时,先插入主表再插入外表，外表所插入的外键必须在主表的主键存在

--2、删除时,先删除外表再删除主表，主表中的主键数据没有被外表的外键数据使用

 

--6、oracle中的特殊建表语句

--1)、使用另一个表的结构和数据来创建新表

select * from emp;

drop table emp;

create table emp_bak as select empno,ename,sal,comm from emp where 1=1;

select * from emp_bak;

--2)、使用另一个表的结构来创建新表

drop table emp;

drop table emp_bak;

create table emp_bak as select * from emp where 1=2;

select * from emp_bak;

 

--7、使用create创建用户

create user "NEWUSER" identified by "root" default tablespace "USERS" account unlock;

--8、使用drop删除用户

drop user "NEWUSER" cascade;

三、 表格修改字段

 

drop table student;

create table student(

sid varchar2(16),   --学号

stuname varchar2(24),  --姓名

age number(3),  --年龄

sex varchar2(8), --性别

tel varchar2(16), --电话

bdate date,       --生日

cardid varchar2(18)

);

 

select * from student;

drop table student;

--1、添加列

insert into student(sid,stuname,classid) values('1','zhangsan','1');

alter table student add classid varchar2(16);

--2、删除列

alter table student drop column classid;

--3、修改列的类型--(列中数据为null)

delete from student;

alter table student modify classid number;

--4、添加检查约束

alter table student add constraint chk_classid check(classid in ('1','2','3','4','5'));

--5、删除检查约束

alter table student drop constraint chk_classid;

--6、添加列的非空约束--(列中没有null数据)

alter table student modify classid constraint not_null_classid not null;

insert into student(classid) values(null); --错误

--7、删除列的非空约束

alter table student drop constraint not_null_classid;

insert into student(classid) values(null);  --成功

--8、添加唯一约束

alter table student modify classid constraint unique_classid unique;

insert into student(classid) values('1');  --不能重复插入

--9、删除唯一约束

alter table student drop constraint unique_classid;

insert into student(classid) values('1');  --可重复插入

--10、添加主键约束(列的数据必须非空唯一)

delete from student;

alter table student add constraint pk_student_sid primary key(sid);

--11、删除主键约束

alter table student drop constraint pk_student_sid;

--12、添加外键约束

create table card(

cardid varchar2(16),

carduser varchar2(16),

cardaddr varchar2(128),

constraint pk_card primary key(cardid)

);

alter table student add constraint fk_student_cardid foreign key(cardid) references card(cardid);

--13、删除外键约束

alter table student drop constraint fk_student_cardid;

--14、删除表

drop table student;

四、对表赋予权限和收回权限

--1、获得用户的系统权限

select * from user_sys_privs;

select * from dba_sys_privs where grantee='SCOTT';

--2、获得用户的角色

select * from user_role_privs;

select * from dba_role_privs where grantee='SCOTT';

--3、获得当前用户的所有对象权限

select * from user_tab_privs;

select * from dba_tab_privs where grantee='SYSTEM';

--4、赋CONNECT RESOURCE角色给NEWUSER(默认只能对自己创建的表有select,delete,update,insert权限)

grant connect,resource to "NEWUSER";

select * from dba_role_privs where grantee='NEWUSER';

 

create table usertemp(

id number

);

 

drop table usertemp;

 

--5、将NEWUSER的CONNECT,RESOURCE权限收回

revoke resource from "NEWUSER";

revoke connect from "NEWUSER";

 

select * from scott.EMP;

insert into scott.STUDENT(sid,stuname) values('1','zhagnsan');

 

--6、将scott.student表的增删改查权限赋给NEWUSER

grant select,insert,update,delete on scott.student to "NEWUSER";

--切换用户连接

select * from scott.student; --成功，有权限

insert into scott.STUDENT(sid,stuname) values('2','zhagnsan'); --成功，有权限

 

--7、回收NEWUSER对表scott.student表的增删改查权限

revoke select,insert,update,delete on scott.STUDENT from "NEWUSER";

select * from scott.student; --错误，因为没有权限

insert into scott.STUDENT(sid,stuname) values('2','zhagnsan'); --错误，因为没有权限

 

--8、将scott.student表的所有权限赋给NEWUSER

grant all privileges on scott.STUDENT to "NEWUSER";

 

--9、收回NEWUSER对scott.student表的所有权限

revoke all privileges on scott.STUDENT from "NEWUSER";

 

--10、将scott.student表的所有权限赋给所有人

grant all privileges on scott.STUDENT to PUBLIC;

 

--11、将所有人对scott.student表的所有权限收回

revoke all privileges on scott.STUDENT from PUBLIC;

 

--12、将scott用户所有表的增删改查权限赋给NEWUSER(批量赋权限)

select * from user_tables;

select 'grant insert,delete,update,select on scott.'

||table_name||' to NEWUSER' from user_tables;

 

--13、将scott用户所有表的增删改查权限从NEWUSER回收(批量回收权限)

select 'revoke insert,delete,update,select on scott.'

||table_name||' from NEWUSER' from user_tables;