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

川长思鸟来 2022-06-16 06:14 286阅读 0赞

**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;