数据库试题
部分常见ORACLE面试题以及SQL注意事项
一、表的创建：
一个通过单列外键联系起父表和子表的简单例子如下：
CREATE TABLE parent(id INT NOT NULL,
PRIMARY KEY(id)
)
CREATE TABLE child(id INT,parent_id INT,
INDEX par_ind(parent_id),
FOREIGN KEY(parent_id)REFERENCES parent(id)
ON DELETE CASCADE
)
建表时注意不要用关键字当表名或字段名，如insert,use等。
CREATE TABLE parent(id INT NOT NULL,
PRIMARY KEY(id)
)TYPE=INNODB;
InnoDB Tables概述
InnoDB给MySQL提供了具有事务(commit)、回滚(rollback)和崩溃修复能力(crash recovery capabilities)的事务安全(transaction-safe(ACID compliant))型表。
InnoDB提供了行锁(locking on row level)，提供与Oracle类型一致的不加锁读取(non-locking read in SELECTs)。这些特性均提高了多用户并发操作的性能表现。在InnoDB表中不需要扩大锁定(lock escalation)，
因为InnoDB的列锁定(row level locks)适宜非常小的空间。
InnoDB是MySQL上第一个提供外键约束(FOREIGN KEY constraints)的表引擎。
InnoDB的设计目标是处理大容量数据库系统，它的CPU利用率是其它基于磁盘的关系数据库引擎所不能比的。
从一个表中查询出数据插入到另一个表中的方法：
select*into destTbl from srcTbl;
insert into destTbl(fld1,fld2)select fld1,5from srcTbl;
以上两句都是将srcTbl的数据插入到destTbl，但两句又有区别的。
第一句（select into from）要求目标表（destTbl）不存在，因为在插入时会自动创建。
第二句（insert into select from）要求目标表（destTbl）存在，由于目标表已经存在，所以我们除了插入源表（srcTbl）的字段外，还可以插入常量，如例中的：5。
如果只想要结构而不要数据。
create table s_emp_42as select*from s_emp where1=2;//永假式
SQL查询练习题
1.
表1：book表，字段有id(主键)，name(书名)；
表2：bookEnrol表（图书借出归还登记），字段有id，bookId（外键），dependDate（变更时间），state （1.借出2.归还）。
id name
1English
2Math
3JAVA
id bookId dependDate state
112009-01-021
212009-01-122
322009-01-141
412009-01-171
522009-02-142
622009-02-151
732009-02-181
832009-02-192
要求查询结果应为：(被借出的书和被借出的日期)
Id Name dependDate
1English2009-01-17
2Math2009-02-15
Select e.bookId,https://www.wendangku.net/doc/402443210.html,,e.dependDate from book b,bookEnrol e where
第二个表是用来登记的，不管你是借还是还，都要添加一条记录。
请写一个SQL语句，获取到现在状态为已借出的所有图书的相关信息。
参考语句：
select book.id,https://www.wendangku.net/doc/402443210.html,,max(dependDate)
from book inner join bookEnrol on book.id=bookEnrol.bookid AND booker.state=1
group by book.id;
2
第(1)题练习使用group by/having子句。类似的笔试题还有：
表一：各种产品年销售量统计表sale
年产品销量
2005a700
2005b550
2005c600
2006a340
2006b500
2007a220
2007b350
要求得到的结果应为：
年产品销量
2005a700
2006b500
2007b350
即：每年销量最多的产品的相关信息。
参考答案：
Select*from sale a where not exists(select*from sale where年=a.年and销量>a.销量);
--or:
select*from sale a inner join(select年,max(销量)as销量from sale group by年)b
on a.年=b.年and a.销量=b.销量
3.查询语句排名问题：
名次姓名月积分(char)总积分（char）
1WhatIsJava199
2水王76981
3新浪网6596
4牛人229
5中国队6489
6北林信息6666
7加太阳5366
8中成药1133
9西洋参2526
10大拿3323
如果用总积分做降序排序..因为总积分是字符型，所以排出来是这样子(9,8,7,6,5...)，要求按照总积分的数字大小排序。
select*from tablename order by cast(总积分as int)desc
表tb
uid mark
17
16
23
22
25
34
33
48
41
43
想查出uid=4的名次：
uid mc
43
select uid,sum(mark)as total from tab_name group by uid order by total desc;
4
表A字段如下
month name income
月份人员收入
1a1000
2a2000
3a3000
要求用一个SQL语句（注意是一个）的处所有人（不区分人员）每个月及上月和下月的总收入
要求列表输出为
月份当月收入上月收入下月收入
2200010003000
Select(Select Month From Table Where Month=To_Char(Sysdate,'mm'))月份,
(Select Sum(Income)From Table Where Month=To_Char(Sysdate,'mm'))当月收入,
(Select Sum(Income)From Table Where To_Number(Month)=To_Number(Extract(Month From Sysdate))-1)上月收入,
(Select Sum(Income)From Table Where To_Number(Month)=To_Number(Extract(Month From Sysdate))+1)下月收入
From Dual
5.删除重复记录
方法原理：
1、Oracle中，每一条记录都有一个rowid，rowid在整个数据库中是唯一的，
rowid确定了每条记录是在ORACLE中的哪一个数据文件、块、行上。
2、在重复的记录中，可能所有列的内容都相同，但rowid不会相同，所以只要确定出重复记录中
那些具有最大rowid的就可以了，其余全部删除。
实现方法：
SQL>create table a(
2bm char(4),--编码
3mc varchar2(20)--名称
4)
5/
SQL>select rowid,bm,mc from a;
ROWID BM MC
-----------------------------
000000D5.0000.000211111111
000000D5.0001.000211121111
000000D5.0002.000211131111
000000D5.0003.000211141111
000000D5.0004.000211111111
000000D5.0005.000211121111
000000D5.0006.000211131111
000000D5.0007.000211141111
查询到8记录.
查出重复记录
SQL>select rowid,bm,mc from a where a.rowid!=(select max(rowid)from a b where a.bm=b.bm and a.mc=b.mc);
ROWID BM MC
------------------------------------------
000000D5.0000.000211111111
000000D5.0001.000211121111
000000D5.0002.000211131111
000000D5.0003.000211141111
删除重复记录
SQL>delete from a a where a.rowid!=(select max(rowid)from a b where a.bm=b.bm and a.mc=b.mc);
删除4个记录.
SQL>select rowid,bm,mc from a;
ROWID BM MC
------------------------------------------
000000D5.0004.000211111111
000000D5.0005.000211121111
000000D5.0006.000211131111
000000D5.0007.000211141111
其他组合函数
Group by子句
Distinct关键字
伪列ROWNUM，用于为子查询返回的每个行分配序列值
注意：组函数可以处理一组数据，返回一个值。组函数会忽略空值。where后只能跟单行函数，不能有组函数。
使用TOP-N分析法
TOP-N分析法基于条件显示表中最上面N条记录或最下面N条记录
TOP-N查询包含以下内容：
1，一个用于排序数据的内联视图
2，使用ORDER BY子句或DESC参数的子查询
3，一个外层查询。由它决定最终记录中行的数目。这包括ROWNUM伪列和用于比较运算符的WHERE 子句
//语法：
SELECT ROWNUM,column_list
FROM(SELECT column_list FROM table_name ORDER BY Top-n-column_name)
WHERE ROWNUM<=N
例1：查询Employee表的顶部10条记录
//方法1：单表时可以用
select cEmployeeCode,vFirstName,vLastName from employee where rownum<=10
//方法2：较复杂的查询，建议使用这种
select*from(select rownum as num,cEmployeeCode,vFirstName,vLastName from employee)
where num<=10
例2:查询Employee表的第1到第10条记录，可以用于分页显示
//注意:因为这里子查询的rownum需要被外层查询所使用，因此要使用别名，否则将被认为是两个不同的rownum
select*from(select rownum as num,Employee.*from Employee)where num between10and20
select*from(select rownum as num,Employee.*from Employee)where num between1and10
SQL注入1=1永远成立,相当于查询所有记录
select*from person_zdk where1=1or name like'%a%'and age=13;
DECODE函数
是ORACLE PL/SQL是功能强大的函数之一，目前还只有ORACLE公司的SQL提供了此函数，其他数据库厂商的SQL实现还没有此功能。
decode(条件,值1,翻译值1,值2,翻译值2,...值n,翻译值n,缺省值)
该函数的含义如下:
IF条件=值1THEN
RETURN(翻译值1)
ELSIF条件=值2THEN
RETURN(翻译值2)
......
ELSIF条件=值n THEN
RETURN(翻译值n)
ELSE
RETURN(缺省值)
END IF
假设我们想给智星职员加工资，其标准是：工资在8000元以下的将加20％；工资在8000元以上的加15％，用DECODE函数，那么我们就可以把这些流控制语句省略，通过SQL语句就可以直接完成。如下：select decode(sign(salary-8000),1,salary*1.15,-1,salary*1.2,salary from employee.
SQL中的单记录函数
CONCAT
1.1.CONCAT
连接两个字符串;
SQL>select concat('010-','88888888')||'转23'高乾竞电话from dual;
高乾竞电话
----------------
010-********转23
LTRIM和RTRIM
2.2.LTRIM
LTRIM删除左边出现的字符串
RTRIM删除右边出现的字符串
SQL>select ltrim(rtrim('gao qian jing',''),'')from dual;
LTRIM(RTRIM('
-------------
gao qian jing
.SUBSTR(string,start,count)
3.3..SUBSTR(string,start,count)
取子字符串,从start开始,取count个
SQL>select substr('130********',3,8)from dual;
SUBSTR('
--------
08888888
4日期函数
如:LAST_DAY返回本月日期的最后一天
具体参见oracle笔记.
其他主要函数：.TRUNC按照指定的精度截取一个数；SQRT返回数字n的根；POWER（n1,n2）返回n1的n2次方根；MOD(n1,n2)返回一个n1除以n2的余数；FLOOR对给定的数字取整数；REPLACE('string','s1','s2')string希望被替换的字符或变量s1被替换的字符串s2要替换的字符串；LOWER返回字符串,并将所有的字符小写；UPPER返回字符串,并将所有的字符大写；LENGTH 返回字符串的长度。
ORALCE常识及SQL基本语法
1，ORACLE安装完成后的初始口令？
internal/oracle
sys/change_on_install
system/manager
scott/tiger scott是Oracle的核心开发人员之一，tiger是他家的一只猫的名字
sysman/oem_temp
例：conn scott/tiger@jspdev;
conn system/manager@jspdev as sysdba;
2，IBM的Codd（Edgar Frank Codd）博士提出《大型共享数据库数据的关系模型》
3,ORACLE9i中的i（internet）是因特网的意思
4,ORACLE的数据库的物理结构：数据文件、日志文件、控制文件
5,ORACLE的数据库的逻辑结构：表空间——表——段——区间——块
表空间类似于SQLSERVER中数据库的概念
6,SYSDATE返回当前系统日期（说明：当函数没有参数时可以省略括号）
7,在SQL PLUS中执行缓冲区中的SQL命令的方式：
SQL>run
SQL>r
SQL>/
8,在SQL PLUS中修改当前会话的日期显示格式
SQL>alter session set nls_date_format='YYYY-MM-DD'
9,使用临时变量，提高输入效率
SQL>insert into emp(empno,ename,sal)values(&employeeno,'&employeename',&employeesal);
10,从其他表中复制数据并写入表
SQL>insert into managers(id,name,salary,hiredate)
SQL>select empno,ename,sal,hiredate
SQL>from emp
SQL>where job='MANAGER';
11,修改表中的记录
SQL>update table set column=value[,column=value,……][where condition];
12,删除表中的记录
SQL>delete[from]table[where condition];
13,数据库事务，事务是数据库一组逻辑操作的集合
一个事务可能是：
多个DML语句
单个DDL语句
单个DCL语句
14,事务控制使用savepoint,rollback,commit关键字
SQL>savepoint aaa;
SQL>rollback to aaa;
SQL>commit;
15,查询表中的数据
select*from table_name;
select column_list from table_name;
16,Number and Date可以用于算术运算
因为Date类型其实存储为Number类型
17,用运算表达式产生新列
SQL>select ename,sal,sal+3000from emp;
SQL>select ename,sal,12*sal+100from emp;
18,算术表达式中NULL值错误的处理
因为任何数与NULL运算无意义，所以为避免错误，需要用其他值替换NULL值
例如：
SQL>select ename"姓名",12*sal+comm"年薪"from emp where ename='KING';
姓名薪水
--------------------
KING
因为comm（提成工资）列为NULL值，结果也出现了NULL值，所以需要用0来替换NULL 注意函数nvl的使用NVL(原值,新值)
SQL>select ename"姓名",12*sal+NVL(comm,0)"年薪"from emp where ename='KING';
员工姓名员工薪水
--------------------
KING60000
——————————————
19,使用友好的列名,有下面三种形式
SQL>select ename as姓名,sal月薪,sal*12"年薪"from emp
20,过滤重复行，使用关键字distinct
SQL>select distinct*from emp;
21,SQL PLUS访问ORACLE数据库的原理
SQL*Plus—>Buffer—>Server—>Query Result
22,where子句中字符型是区分大小写的，最好都转成大写
因为在ORACLE库中，字符会转换成大写来保存
23,比较运算符：等于"=",不等于有两种"<>"或者"!="
24,复杂的比较运算符：
between……and……
in(……value list……)
like（%代表匹配至多个任意字符，_代表单个任意字符）
null（与NULL进行比较时，需要使用is null或者is not null）
25,逻辑运算符，按优先级从高到低排列
Not,And,Or
26,Order by子句中（asc表示升序，desc表示降序）
27,ORACLE函数，分为
单行函数：每条记录返回一个结果值
多行函数：多条记录返回一个结果值
28,字符函数——转换函数
LOWER：转为小写
UPPER：转为大写
INITCAP：将每个单词的首字母大写，其他字母小写
29,字符函数——操纵函数（注意：ORACLE以UNICODE存储字符）
CONCAT:连接两个字符串，与并置运算符“||”类似
SUBSTR:substr(string,position,length)从string中的position开始取length个字符
LENGTH:返回字符串的长度
INSTR:instr(string,value)返回value在string的起始位置
LPAD:lpad(string,number,value)若string不够number位，从左起用vlaue字符串填充(不支持中文)
30,四舍五入函数round(数值,小数位)
SQL>SELECT ROUND(45.923,2),ROUND(45.923,0),ROUND(45.923,-1)FROM DUAL;
ROUND(45.923,2)ROUND(45.923,0)ROUND(45.923,-1)
----------------------------------------------
45.924650
31,数值截取函数trunct
SQL>SELECT TRUNC(45.923,2),TRUNC(45.923,0),TRUNC(45.923,-1)FROM DUAL;
TRUNC(45.923,2)TRUNC(45.923,0)TRUNC(45.923,-1)
----------------------------------------------
45.924540
32,求模函数MOD(a,b)返回a被b整除后的余数
33,Oracle内部默认的日期格式:DD-MON-YY（24-9月-06）
34,DUAL：哑元系统表，是名义表，只能范围唯一值
35,Date类型的算术运算，以天为单位
例如：部门编号为10的员工分别工作了多少年
SQL>select ename,(sysdate-hiredate)/365as years from emp where deptno=10;
ENAME YEARS
--------------------
CLARK25.3108341
KING24.8697382
MILLER24.6861766
36,日期函数
MONTHS_BETWEEN返回两个日期之间相差多少个月
ADD_MONTHS在日期上加上月份数
NEXT_DAY下一个日子select next_day(sysdate,'星期一')from dual;
LAST_DAY该月的最后一天
ROUND四舍五入日期round(sysdate,'year')或者round(sysdate,'month') TRUNC截取日期trunc(sysdate,'year')或者trunc(sysdate,'month')
37,数据类型转换——Oracle可隐式转换的情况有：
From To
varchar2or char——number（当字符串是数字字符时）
varchar2or char——date
number——varchar2
date——varchar2
38,数据类型转换——Oracle数据类型转换函数
to_char
to_number
to_date
39,日期格式模型字符
YYYY代表完整的年份
YEAR年份
MM两位数的月份
MONTH月份的完整名称
DY每星期中天的三个字符缩写
DAY表示星期日——星期六
另外还有D,DD,DDD等。。。
40,NVL(value,substitute)
value:是可能有null的列，substitute是缺省值
这个函数的作用就是当出现null值的时候，后缺省值替换null
41,Coalesce(exp_name1,exp_name2……exp_n)
42,Decode函数：Decode(exp,testvalue1,resultvalue1,testvalue2,resultvalue2)例如，根据国家名称显示相应的国家代码:
1>创建国家表
create table countrys
(
vCountryName varchar2(50)
);
2>写入几行，分别为中国、日本、韩国
insert into countrys values('&name');
3>用DECODE函数，进行匹配和显示
select vCountryName as"国家名称",
DECODE(vCountryName,'中国','086','日本','116')as"国家编号"from countrys;
国家名称国家编号
-----------------------------------------------------
中国086
日本116
韩国
结果，在DECODE中存在且成功匹配的值将会被显示，否则显示为NULL
S QL语句书可以提高执行效率的方法
1、操作符号：NOT IN操作符
此操作是强列推荐不使用的，因为它不能应用表的索引。推荐方案：用NOT EXISTS或（外连接+判断为空）方案代替"IS NULL","<>","!=","!>","!<","NOT","NOT EXISTS","NOT IN","NOT LIKE","LIKE '%500'"，因为他们不走索引全是表扫描。NOT IN会多次扫描表，使用EXISTS、NOT EXISTS、IN、LEFT OUTER JOIN来替代，特别是左连接,而Exists比IN更快，最慢的是NOT操作。
2、注意union和union all的区别。union比union all多做了一步distinct操作。能用union all的情况下尽量不用union。
如:两个表A和B都有一个序号字段ID,要求两个表中的ID字段最大的值：
select max(id)as max_id
from(
select id from表A
union all
select id from表B)t
3、查询时尽量不要返回不需要的行、列。另外在多表连接查询时，尽量改成连接查询，少用子查询。
4、尽量少用视图，它的效率低。对视图操作比直接对表操作慢,可以用存储过程来代替它。特别的是不要用视图嵌套,嵌套视图增加了寻找原始资料的难度。
我们看视图的本质：它是存放在服务器上的被优化好了的已经产生了查询规划的SQL。对单个表检索数据时，不要使用指向多个表的视图，
直接从表检索或者仅仅包含这个表的视图上读，否则增加了不必要的开销,查询受到干扰.为了加快视图的查询，MsSQL增加了视图索引的功能。
5、创建合理的索引，对于插入或者修改比较频繁的表，尽量慎用索引。因为如果表中存在索引，插入和修改时也会引起全表扫描。
索引一般使用于where后经常用作条件的字段上。
6、在表中定义字段或者存储过程、函数中定义参数时，将参数的大小设置为合适即可，勿设置太大。这样开销很大。
7、Between在某些时候比IN速度更快,Between能够更快地根据索引找到范围。用查询优化器可见到差别。
select*from chineseresume where title in('男','女')
Select*from chineseresume where between'男'and'女'是一样的。由于in会在比较多次，所以有时会慢些。
8、在必要是对全局或者局部临时表创建索引，有时能够提高速度，但不是一定会这样，因为索引也耗费大量的资源。他的创建同是实际表一样。
9、WHERE后面的条件顺序影响
WHERE子句后面的条件顺序对大数据量表的查询会产生直接的影响，如
Select*from zl_yhjbqk where dy_dj='1KV以下'and xh_bz=1
Select*from zl_yhjbqk where xh_bz=1and dy_dj='1KV以下'
以上两个SQL中dy_dj（电压等级）及xh_bz（销户标志）两个字段都没进行索引，所以执行的时候都是全表扫描，如果dy_dj='1KV以下'条件在记录集内比率为99%，而xh_bz=1的比率只为0.5%，在进行第一条SQL的时候99%条记录都进行dy_dj及xh_bz的比较，而在进行第二条SQL的时候0.5%条记录都进行dy_dj及xh_bz的比较，以此可以得出第二条SQL的CPU占用率明显比第一条低。所以尽量将范围小的条件放在前面。。
10、用OR的字句可以分解成多个查询，并且通过UNION连接多个查询。他们的速度只同是否使用索引有关,如果查询需要用到联合索引，用UNION all执行的效率更高.多个OR的字句没有用到索引，改写成UNION的形式再试图与索引匹配。一个关键的问题是否用到索引。
11、没有必要时不要用DISTINCT和ORDER BY，这些动作可以改在客户端执行。它们增加了额外的开销。这同UNION和UNION ALL一样的道理。
12、使用in时，在IN后面值的列表中，将出现最频繁的值放在最前面，出现得最少的放在最后面，这样可以减少判断的次数
13、当用SELECT INTO时，它会锁住系统表(sysobjects，sysindexes等等)，阻塞其他的连接的存取。创建临时表时用显示声明语句，在另一个连接中SELECT*from sysobjects可以看到SELECT INTO会锁住系统表，Create table也会锁系统表(不管是临时表还是系统表)。所以千万不要在事物内使用它！！！这样的话如果是经常要用的临时表请使用实表，或者临时表变量。
14、一般在GROUP BY和HAVING字句之前就能剔除多余的行，所以尽量不要用它们来做剔除行的工作。他们的执行顺序应该如下最优：select的Where字句选择所有合适的行，Group By用来分组个统计行，Having字句用来剔除多余的分组。这样Group By和Having的开销小，查询快.对于大的数据行进行分组和Having十分消耗资源。如果Group BY的目的不包括计算，只是分组，那么用Distinct更快
15、一次更新多条记录比分多次更新每次一条快,就是说批处理好
16、慎用临时表，临时表存储于tempdb库中，操作临时表时，会引起跨库操作。尽量用结果集和表变量来代替它。
17、尽量将数据的处理工作放在服务器上，减少网络的开销，如使用存储过程。存储过程是编译好、优化过，并且被组织到一个执行规划里、且存储在数据库中的SQL语句，是控制流语言的集合，速度当然快。
18、不要在一段SQL或者存储过程中多次使用相同的函数或相同的查询语句，这样比较浪费资源,建议将结果放在变量里再调用。这样更快。
19、按照一定的次序来访问你的表。如果你先锁住表A，再锁住表B，那么在所有的存储过程中都要按照这个顺序来锁定它们。如果你（不经意的）某个存储过程中先锁定表B，再锁定表A，这可能就会导致一个死锁。