Oracle数据常用语句（二）

五、创建自增序列

create table student(

sid varchar2(16),   --学号

stuname varchar2(24),  --姓名

age number(3),  --年龄

sex varchar2(8), --性别

tel varchar2(16), --电话

bdate date,       --生日

cardid varchar2(18),

constraint pk_sid primary key(sid)

);

 

drop table student;

drop table grade;

 

--1、序列 sequence(一个不会重复并且能自动增长的整型数容器)

CREATE SEQUENCE sequence_name

[START WITH num] --起始值

[INCREMENT BY increment] --步长值

[MAXVALUE num|NOMAXVALUE] --最大值

[MINVALUE num|NOMINVALUE] --最小值

[CYCLE|NOCYCLE] --是否循环

[CACHE num|NOCACHE] --缓冲区

 

--2、创建一个用于作学生主键的sequence，起始1,最大值99,步长1，会循环，没有cache

create sequence seq_sid

start with 1

increment by 1

minvalue 1

maxvalue 99

cycle

nocache

 

--3、从sequence中取出序列值

select seq_sid.nextval from dual;

insert into student(sid) values(to_char(seq_sid.nextval));

select * from student;

 

--4、删除sequence

drop sequence seq_sid;

 

--5、将'DYB17030101'类型的学号插入到学生表

select 'DYB170301'||lpad(to_char(seq_sid.nextval),3,'0') from dual;

insert into student(sid) values('DYB170301'||lpad(to_char(seq_sid.nextval),3,'0'));

select * from student;

 

六、插入数据到表

create table student(

sid varchar2(16),   --学号

stuname varchar2(24),  --姓名

age number(3),  --年龄

sex varchar2(8), --性别

tel varchar2(16), --电话

bdate date,       --生日

cardid varchar2(18),

constraint pk_sid primary key(sid)

);

 

drop table student;

drop table grade;

 

--一、插入单行数据

--1、插入空值

insert into student(sid,stuname) values(seq_sid.nextval,null);

insert into student(sid,stuname) values(seq_sid.nextval); -- 错误，没有足够值

--2、插入字符

insert into student(sid,stuname) values(seq_sid.nextval,'zhangsan');

--3、插入日期

insert into student(bdate,stuname,sid) values('28-7月-2017','lisi',seq_sid.nextval);

select * from student;

insert into student(sid,bdate) values(seq_sid.nextval,to_date('2017-7-28','yyyy-MM-dd'));

insert into student(sid,bdate) values(seq_sid.nextval,sysdate);

insert into student(sid,bdate) values(seq_sid.nextval,current_date);

--4、插入全部值

insert into student values(seq_sid.nextval,'zhangsan',22,'男','110','1-12月-1998',null);

insert into student values(seq_sid.nextval,'lisi',22,'女','120','12-6月-1999',null);

insert into student values(seq_sid.nextval,'wangwu',22,'女','119','8-12月-2001',null);

insert into student values(seq_sid.nextval,'zhaoliu',22,'男','110','12-3月-2000',null);

--5、向同一表插入多行

drop table student2;

create table student2 as select sid,stuname,bdate,cardid

from student where 1=2;

insert into student2 select sid,stuname,bdate,cardid from student;

select * from student2;

--6、将数据同时插入到多个表

drop table banji;

create table banji(

bno varchar2(16) constraint pk_banji primary key,

sid varchar2(16)

);

 

drop table sleepmember;

create table sleepmember(

sno varchar2(16) constraint pk_sleepmember primary key,

sid varchar2(16),

stuname varchar2(16)

);

 

insert all

into banji(bno,sid) values(sid,sid)

into sleepmember(sno,sid,stuname) values(sid,sid,stuname)

select sid,stuname from student where to_number(sid) > 14;

 

select * from student;

select * from banji;

select * from sleepmember;

七、表中删除记录

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) unique,

constraint pk_sid primary key(sid)

);

 

drop table class;

create table class( --科目表

cno varchar2(16), --科目编号

cname varchar2(16), --科目名称

tname varchar2(16), --教学老师

constraint pk_class_cno primary key(cno)

);

 

drop table grade;

create table grade(

gsid varchar2(16),

gcno varchar2(16),

grade number,

constraint pk_grade_gsid_gcno primary key(gsid,gcno),

constraint fk_grade_gsid foreign key(gsid) references student(sid) on delete cascade,

constraint fk_grade_gcno foreign key(gcno) references class(cno) on delete cascade

);

 

--1、删除符合条件数据

delete from student where sid='14';

select * from student;

--2、删除符合条件的数据

delete from student where sid in ('12','14');

delete from student where sid = '12' or sid = '14';

select * from student;

--3、使用嵌套查询作条件

select * from grade;

insert into student values('1','zhangsan',22,'男','110','1-12月-1998','450110199912121234');

insert into student values('2','lisi',22,'女','120','12-6月-1999','450110199912121236');

insert into student values('3','wangwu',22,'女','119','8-12月-2001','450110199912122334');

insert into student values('4','zhaoliu',22,'男','110','12-3月-2000','450123199912121234');

 

insert into class values('1','yuwen','张老师');

insert into class values('2','shuxue','李老师');

insert into class values('3','yingyu','王老师');

 

select * from student;

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

insert into grade values('1','2',77,'450110199912121234');

insert into grade values('1','3',88,'450110199912121234');

insert into grade values('2','1',76,null);

insert into grade values('2','2',56,null);

insert into grade values('2','3',54,null);

insert into grade values('3','1',99,null);

insert into grade values('3','2',88,null);

insert into grade values('3','3',98,null);

insert into grade values('4','1',69,null);

insert into grade values('4','2',80,null);

insert into grade values('4','3',56,null);

 

delete from grade where gsid in (select distinct(gsid) from grade where grade < 60);

delete from student where sid in (select distinct(gsid) from grade where grade < 60); --失败,

select * from student;

select * from grade;

delete from student where sid in

(select distinct(gsid) from grade where grade < 60 and gcno =

(select gcno from class where tname = '李老师'));

 

--4、删除有主外键关系的数据时，级联删除--on delete cascade

drop table grade;

create table grade(

gsid varchar2(16),

gcno varchar2(16),

grade number,

cardid varchar2(18),

constraint pk_grade_gsid_gcno primary key(gsid,gcno),

constraint fk_grade_gsid foreign key(gsid) references student(sid) on delete cascade, --定义级联删除

constraint fk_grade_gcno foreign key(gcno) references class(cno) on delete cascade

);

delete from student where sid in (select distinct(gsid) from grade where grade < 60); --删除主表数据的同时将外表数据删除

select * from student;

select * from grade;

 

--5、删除有主外键关系的数据时，将外表相应数据设置为null(要设置为空的外键不能有非空约束、主键约束)

create table grade(

gsid varchar2(16),

gcno varchar2(16),

grade number,

cardid varchar2(18),

constraint pk_grade_gsid_gcno primary key(gsid,gcno),

constraint fk_grade_gcno foreign key(gcno) references class(cno) on delete cascade,

constraint fk_grade_cardid foreign key(cardid)

references student(cardid) on delete set null -- 设置删除主表数据时，外表数据为空

);

select * from student;

select * from grade;

 

--6、级联删除主表(有主外键引用关系的主表删除)

drop table class;

drop table class cascade constraint; --级联删除主表,外表不受影响

select * from class;

select * from grade;

八、更新表记录

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) unique,

constraint pk_sid primary key(sid)

);

 

drop table class;

create table class( --科目表

cno varchar2(16), --科目编号

cname varchar2(16), --科目名称

tname varchar2(16), --教学老师

constraint pk_class_cno primary key(cno)

);

 

drop table grade;

create table grade(

gsid varchar2(16),

gcno varchar2(16),

grade number,

constraint pk_grade_gsid_gcno primary key(gsid,gcno),

constraint fk_grade_gsid foreign key(gsid) references student(sid) on delete cascade,

constraint fk_grade_gcno foreign key(gcno) references class(cno) on delete cascade

);

 

insert into student values('1','zhangsan',22,'男','110','1-12月-1998','450110199912121234');

insert into student values('2','lisi',22,'女','120','12-6月-1999','450110199912121236');

insert into student values('3','wangwu',22,'女','119','8-12月-2001','450110199912122334');

insert into student values('4','zhaoliu',22,'男','110','12-3月-2000','450123199912121234');

 

insert into class values('1','yuwen','张老师');

insert into class values('2','shuxue','李老师');

insert into class values('3','yingyu','王老师');

 

select * from student;

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

insert into grade values('1','2',77);

insert into grade values('1','3',88);

insert into grade values('2','1',76);

insert into grade values('2','2',56);

insert into grade values('2','3',54);

insert into grade values('3','1',99);

insert into grade values('3','2',88);

insert into grade values('3','3',98);

insert into grade values('4','1',69);

insert into grade values('4','2',80);

insert into grade values('4','3',56);

 

--、1、修改格式

update 表名 set 列名=新值 --修改所有列

update 表名 set 列名=新值 where 条件 --修改指定条件的列

 

--2、修改指定数据的值

update grade set grade = grade + 10 where gsid = 2 and gcno = 2;

select * from grade where gsid = 2;

 

--3、修改指定数据的值

update grade set grade = 60 where grade < 60 and gsid in

(select sid from student where stuname = 'zhaoliu');

select * from grade;

 

--4、修改指定数据的值

update grade set grade = 60 where

grade < 60 and

gsid in (select sid from student where stuname like 'zhaoliu') and

gcno = (select cno from class where cname = 'yingyu');

select * from grade;

 

--5、修改表中所有数据

update grade set grade = 0;

select * from grade;

 

 

九、Oracle 提交和回滚功能

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) unique,

constraint pk_sid primary key(sid)

);

 

drop table class;

create table class( --科目表

cno varchar2(16), --科目编号

cname varchar2(16), --科目名称

tname varchar2(16), --教学老师

constraint pk_class_cno primary key(cno)

);

 

drop table grade;

create table grade(

gsid varchar2(16),

gcno varchar2(16),

grade number,

constraint pk_grade_gsid_gcno primary key(gsid,gcno),

constraint fk_grade_gsid foreign key(gsid) references student(sid) on delete cascade,

constraint fk_grade_gcno foreign key(gcno) references class(cno) on delete cascade

);

 

insert into student values('1','zhangsan',22,'男','110','1-12月-1998','450110199912121234');

insert into student values('2','lisi',22,'女','120','12-6月-1999','450110199912121236');

insert into student values('3','wangwu',22,'女','119','8-12月-2001','450110199912122334');

insert into student values('4','zhaoliu',22,'男','110','12-3月-2000','450123199912121234');

 

insert into class values('1','yuwen','张老师');

insert into class values('2','shuxue','李老师');

insert into class values('3','yingyu','王老师');

 

select * from student;

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

insert into grade values('1','2',77);

insert into grade values('1','3',88);

insert into grade values('2','1',76);

insert into grade values('2','2',56);

insert into grade values('2','3',54);

insert into grade values('3','1',99);

insert into grade values('3','2',88);

insert into grade values('3','3',98);

insert into grade values('4','1',69);

insert into grade values('4','2',80);

insert into grade values('4','3',56);

 

--1、查询是否自动提交

show autocommit;

 

--2、设置自动提交

set autocommit on;

 

--3、设置不自动提交 (要手动事务处理)

set autocommit off;

 

--4、删除数据，然后回滚

delete from grade;

select * from grade;

rollback; --会将前面没有提交的操作回到操作之前的状态

select * from grade;

 

--5、删除数据,然后提交

delete from grade;

commit; --提交事务,将操作真正提交给数据库进操作,操作之后的数据不能再回滚

 

 

--6、设置和回到保存点

delete from grade where gsid = 1;

savepoint grade_1; --设置保存点

delete from grade where gsig > 2;

savepoint grade_2;

 

rollback to grade_2; --回到指定保存点

select * from grade;

rollback to grade_1;

select * from grade;

 

十、记录加锁和解锁

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) unique,

constraint pk_sid primary key(sid)

);

 

drop table class;

create table class( --科目表

cno varchar2(16), --科目编号

cname varchar2(16), --科目名称

tname varchar2(16), --教学老师

constraint pk_class_cno primary key(cno)

);

 

drop table grade;

create table grade(

gsid varchar2(16),

gcno varchar2(16),

grade number,

constraint pk_grade_gsid_gcno primary key(gsid,gcno),

constraint fk_grade_gsid foreign key(gsid) references student(sid) on delete cascade,

constraint fk_grade_gcno foreign key(gcno) references class(cno) on delete cascade

);

 

insert into student values('1','zhangsan',22,'男','110','1-12月-1998','450110199912121234');

insert into student values('2','lisi',22,'女','120','12-6月-1999','450110199912121236');

insert into student values('3','wangwu',22,'女','119','8-12月-2001','450110199912122334');

insert into student values('4','zhaoliu',22,'男','110','12-3月-2000','450123199912121234');

 

insert into class values('1','yuwen','张老师');

insert into class values('2','shuxue','李老师');

insert into class values('3','yingyu','王老师');

 

select * from student;

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

insert into grade values('1','2',77);

insert into grade values('1','3',88);

insert into grade values('2','1',76);

insert into grade values('2','2',56);

insert into grade values('2','3',54);

insert into grade values('3','1',99);

insert into grade values('3','2',88);

insert into grade values('3','3',98);

insert into grade values('4','1',69);

insert into grade values('4','2',80);

insert into grade values('4','3',56);

 

select * from grade;

 

--1、加行级锁

--1)、select * from grade for update;

--2)、select * from grade for update;

 

--2、表锁定格式

--lock table 表名称 | 视图名称，表名称 | 视图名称,...in 锁定模式 mode [nowait];

--说明：nowait选项：可选项，当视图锁定一张数据表时，如果发现已经被其他事务锁定，不会等待；

--说明：锁定模式3. share：共享锁，其他事务只允许执行查询操作，不能执行修改操作；

--说明：锁定模式4. share row exclusive：共享排它锁，允许任何用户进行查询操作，但不允许其他用户使用共享锁；

--说明：锁定模式5. exclusive：排它锁，事务将以独占方式锁定表，其他用户允许查询，但是不能修改也不能设置任何的锁；

lock table grade in share mode; --为grade加了共享锁,其它session只能查询，不能insert,update,delete

 

--3、解除锁

--1) commit; --事务提交以后，会释放对象的行，表级锁,

--2) 关闭连接

 

--4、死锁

--两个session对一对资源产生了循环等待,死锁就会产生

 

--5、死锁解决

--SELECT session_id,oracle_username,process from v$locked_object ; --查询出session id

--SELECT sid,serial#,username,lockwait,status FROM v$session where sid IN (6,14) ; --查询sid,进程号

--alter system kill session 'sid,serial#' --sid对应session会话id，serial;