oracle、mysql+数据库面试+真题

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; 

4、请用一条sql语句，查询出学号为2006010003的张三的电话号码、年龄、住址、班级名称、辅导员，并列出查询结果
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 ='张三';

5、请使用sql语句，修改学号为2006010003的同学的电话号码为13700010001，其所在班级人数为46人
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;

7、请使用sql，删除年龄最大的姓名为张三的学生记录
DELETE FROM studentinfo s
    WHERE s.age = (
        SELECT MAX(s.age) FROM studentinfo s WHERE s.name = '张三' 

    )

8、请使用文字解释共享锁和独占锁的区别，并分别列出这两种锁表格操作的语句。
--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# ;

--2.查出锁定表的session的sid, serial#,os_user_name, machine name, terminal和执行的语句
--比上面那段多出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#;

--3.查出锁定表的sid, serial#,os_user_name, machine_name, terminal，锁的type,mode
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语句产生的锁，还可以发现，
任何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;