1、假设现在存在一个和数据库student,用来存放学生信息,该数据库中存在两张表分别为studentinfo和studentclass,这两个表的结构如下:
studentinfo 学生信息表字段名 类型 默认值 注释
#StudNum char(10) NULL 学号
Name char(8) NULL 姓名
Age int 0 年龄
TEL char(11) NULL 电话
Address char(50) NULL 地址
ClassNum char(10) NULL 班级编号
注意:“#”表示这个字段是关键字/主键
--删除表
DROP TABLE studentinfo;
CREATE TABLE studentinfo(
StudNum CHAR(10) PRIMARY KEY,
Name CHAR(8),
Age INT DEFAULT 0,
TEL CHAR(11),
Address CHAR(50),
ClassNum CHAR(10)
);
COMMENT ON COLUMN studentinfo.studnum IS '学号';
COMMENT ON COLUMN studentinfo.name IS '姓名';
COMMENT ON COLUMN studentinfo.age IS '年龄';
COMMENT ON COLUMN studentinfo.tel IS '电话';
COMMENT ON COLUMN studentinfo.address IS '地址';
COMMENT ON COLUMN studentinfo.classnum IS '班级编号';
SELECT * FROM scott.studentinfo;
TRUNCATE TABLE scott.studentinfo;
classinfo 班级信息表
字段名 类型 默认值 注释
#ClassNum char(10) NULL 班级编号
ClassName char(36) NULL 班级名称
Assistant char(12) NULL 辅导员
StudentNums int 0 学生数,单位:人
注意:“#”表示这个字段是关键字/主键
CREATE TABLE classinfo(
ClassNum CHAR(10) PRIMARY KEY,
ClassName CHAR(36),
Assistant CHAR(12),
StudentNums INT DEFAULT 0
);
SELECT * FROM classinfo;
Studentinfo.unl中的数据如下:
2006010001|张三|20|13400010001|深圳市龙岗区|2006010901|
2006010002|李四|21|13400010002|深圳市龙岗区|2006020405|
2006010003|张三|19|13400010003|深圳市龙岗区|2006030102|
2006010004|王五|21|13400010004|深圳市龙岗区|2006010901|
Classinfo.unl中的数据如下:
2006010901|2006级信息技术学员计算机1班|陈峰|45|
2006020405|2006级外语学院英语5班|张永|30|
2006030102|2006级数电学院数学2班|李丽|45|
2006040501|2006级文体学院音乐1班|赵丽|18|
--请根据上述描述,完成下面题目:
--1、创建上述studentinfo、studentclass表:
--2、将上面两个文件中数据分别导入数据库的两个表中(Studentinfo.unl->studentinfo,Classinfo.unl->classinfo)
--3、请使用sql语句查询出studentinfo表中所有的班级编号,不允许出现重复记录
SELECT * FROM studentinfo;
SELECT * FROM classinfo;
TRUNCATE TABLE studentinfo; (DDL语言,默认所有内容都删除,删除速度比delete快)
UPDATE studentinfo SET classnum = 2006030102 WHERE classnum=2005030102;
SELECT DISTINCT classnum FROM studentinfo;
SELECT s.tel "电话号码",s.age "年龄",s.address "住址",s.studnum,c.classnum,
c.classname "班级名称",c.assistant "辅导员"
FROM studentinfo s,Classinfo c
WHERE s.classnum = c.classnum
AND s.studnum = '2006010003'
AND s.name ='张三';
UPDATE studentinfo SET tel='13700010001'
WHERE studnum = '2006010003';
SELECT * FROM studentinfo WHERE studnum = '2006010003' FOR UPDATE;
SELECT * FROM classinfo WHERE classnum = 2006030102 ;
UPDATE classinfo c SET c.studentnums = 46
WHERE c.classnum = (
SELECT s.classnum FROM studentinfo s
WHERE s.studnum = '2006010003'
)
6、请使用sql语句为班级信息表添加一个班长的字段,并将学习信息表中学生的姓名字段类型修改为:char(6)
班长字段的要求如下:字段名 类型 默认值 注释
MonitorName varchar2(8) XXX 班长姓名
ALTER TABLE studentinfo ADD Monitorname VARCHAR2(8) DEFAULT '张三';
ALTER TABLE studentinfo MODIFY NAME VARCHAR2(6);
SELECT * FROM studentinfo;
DELETE FROM studentinfo s
WHERE s.age = (
SELECT MAX(s.age) FROM studentinfo s WHERE s.name = '张三'
)
--1、表行锁。
--2、共享锁(S锁):
如果事务T对数据A加上共享锁后,则其他事务只能对A再加共享锁,不能加独占锁。获准共享锁的事务只能读取数据,不能修改数据。
排他锁(X锁):
如果事务T对数据A加上独占锁后,则其他事务不能在对A加任何类型的封锁。获准独占锁的事务既能读取数据,也能修改数据。
数据库死锁的原因:若干事务相互等待对方释放封锁,就陷入了无限期等待状态,系统进入死锁。
1、要求一个事务必须一次性封锁所需要的所有数据(要么全成功,要么全部成功)
2、规定封锁数据的顺序,所有事务必须按照这个顺序实行封锁
解除数据库死锁的方法:允许死锁发生,然后解除它,如果发现死锁,则将其中一个代价较小的事务撤销,回滚这个事务,并释放此事务持有的封锁,使其他事务继续进行。
1.共享锁【S锁】
又称读锁,若事务T对数据对象A加上S锁,则事务T可以读A但不能修改A,其他事务只能再对A加S锁,而不能加X锁,直到T释放A上的S锁。这保证了其他事务可以读A,但在T释放A上的S锁之前不能对A做任何修改。
2.排他锁【X锁】
又称写锁。若事务T对数据对象A加上X锁,事务T可以读A也可以修改A,其他事务不能再对A加任何锁,直到T释放A上的锁。 这保证了其他事务在T释放A上的锁之前不能再读取和修改A。
•表级锁
―保护表的数据
―在多个用户同时访问数据时确保数据的完整性
―可以设置为三种模式:共享、共享更新和 排他
语法:lock table<table_name>in<mode>;
lock table tab_name in share update mode;
select column1,column2
from goods
where goods
where gid=1001
for update of column1,column2
LOCK TABLE studentinfo IN SHARE UPDATE MODE;
SELECT tel,address FROM studentinfo FOR UPDATE OF tel,address;
LOCK TABLE scott.studentinfo IN EXCLUSIVE MODE;
SELECT tel,address FROM studentinfo FOR UPDATE OF tel,address;
SELECT * FROM studentinfo;
INSERT INTO studentinfo(studnum,NAME,age) VALUES('2006010005','王五',30);
--Oracle的锁表与解锁
SELECT /*+ rule */ s.username,
decode(l.type,'TM','TABLE LOCK',
'TX','ROW LOCK',
NULL) LOCK_LEVEL,
o.owner,o.object_name,o.object_type,
s.sid,s.serial#,s.terminal,s.machine,s.program,s.osuser
FROM v$session s,v$lock l,dba_objects o
WHERE l.sid = s.sid
AND l.id1 = o.object_id(+)
AND s.username is NOT NULL;
--kill session语句
alter system kill session'50,492';
--以下几个为相关表
SELECT * FROM v$lock;
SELECT * FROM v$sqlarea;
SELECT * FROM v$session;
SELECT * FROM v$process ;
SELECT * FROM v$locked_object;
SELECT * FROM all_objects;
SELECT * FROM v$session_wait;
--1.查出锁定object的session的信息以及被锁定的object名
SELECT l.session_id sid, s.serial#, l.locked_mode,l.oracle_username,
l.os_user_name,s.machine, s.terminal, o.object_name, s.logon_time
FROM v$locked_object l, all_objects o, v$session s
WHERE l.object_id = o.object_id
AND l.session_id = s.sid
ORDER BY sid, s.serial# ;
--比上面那段多出sql_text和action
SELECT l.session_id sid, s.serial#, l.locked_mode, l.oracle_username, s.user#,
l.os_user_name,s.machine, s.terminal,a.sql_text, a.action
FROM v$sqlarea a,v$session s, v$locked_object l
WHERE l.session_id = s.sid
AND s.prev_sql_addr = a.address
ORDER BY sid, s.serial#;
SELECT s.sid, s.serial#, s.username, s.schemaname, s.osuser, s.process, s.machine,
s.terminal, s.logon_time, l.type
FROM v$session s, v$lock l
WHERE s.sid = l.sid
AND s.username IS NOT NULL
ORDER BY sid;
任何DML语句其实产生了两个锁,一个是表锁,一个是行锁。
杀锁命令*/
alter system kill session 'sid,serial#'
SELECT /*+ rule */ s.username,
decode(l.type,'TM','TABLE LOCK',
'TX','ROW LOCK',
NULL) LOCK_LEVEL,
o.owner,o.object_name,o.object_type,
s.sid,s.serial#,s.terminal,s.machine,s.program,s.osuser
FROM v$session s,v$lock l,dba_objects o
WHERE l.sid = s.sid
AND l.id1 = o.object_id(+)
AND s.username is NOT NULL;