SQL面试题大全.pdf

部分常见部分常见 ORACLEORACLE 面试题以及面试题以及 SQLSQL 注意事项注意事项 一 表的创建 一 表的创建 一个通过单列外键联系起父表和子表的简单例子如下 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 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 5 from srcTbl 以 上 两 句 都 是 将 srcTbl 的 数 据 插 入 到 destTbl 但 两 句 又 有 区 别 的 第 一 句 select into from 要 求 目 标 表 destTbl 不 存 在 因 为 在 插 入 时 会 自 动 创 建 第二句 insert into select from 要求目标表 destTbl 存在 由于目标表已经存在 所以我们除了插入源表 srcTbl 的字段外 还可以插入常量 如例中的 5 如果只想要结构而不要数据 create table s emp 42 as select from s emp where 1 2 永假式 SQLSQL 查询练习题查询练习题 1 1 表1 book 表 字段有 id 主键 name 书名 表 2 bookEnrol 表 图书借出归还登记 字段有 id bookId 外键 dependDate 变更时间 state 1 借 出 2 归还 id name 1 English 2 Math 3 JAVA id bookId dependDate state 1 1 2009 01 02 1 2 1 2009 01 12 2 3 2 2009 01 14 1 4 1 2009 01 17 1 5 2 2009 02 14 2 6 2 2009 02 15 1 7 3 2009 02 18 1 8 3 2009 02 19 2 要求查询结果应为 被借出的书和被借出的日期 Id Name dependDate 1 English 2009 01 17 2 Math 2009 02 15 Select e bookId b name e dependDate from book b bookEnrol e where 第二个表是用来登记的 不管你是借还是还 都要添加一条记录 请写一个SQL 语句 获取到现在状态为已借出的所有图书的相关信息 参考语句 select book id book name max dependDate from book inner join bookEnrol on book id bookEnrol bookid AND booker state 1 group by book id 2 2 第 1 题练习使用 group by having 子句 类似的笔试题还有 表一 各种产品年销售量统计表 sale 年 产品 销量 2005 a 700 2005 b 550 2005 c 600 2006 a 340 2006 b 500 2007 a 220 2007 b 350 要求得到的结果应为 年 产品 销量 2005 a 700 2006 b 500 2007 b 350 即 每年销量最多的产品的相关信息 参考答案 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 3 查询语句排名问题 查询语句排名问题 名次 姓名 月积分 char 总积分 char 1 WhatIsJava 1 99 2 水王 76 981 3 新浪网 65 96 4 牛人 22 9 5 中国队 64 89 6 北林信息 66 66 7 加太阳 53 66 8 中成药 11 33 9 西洋参 25 26 10 大拿 33 23 如果用总积分做降序排序 因为总积分是字符型 所以排出来是这样子 9 8 7 6 5 要求按照总积分的数字大小排序 select from tablename order by cast 总积分 as int desc 表表tb tb uid mark 1 7 1 6 2 3 2 2 2 5 3 4 3 3 4 8 4 1 4 3 想查出uid 4的名次 uid mc 4 3 select uid sum mark as total from tab name group by uid order by total desc 4 4 表A字段如下 month name income 月份 人员 收入 1 a 1000 2 a 2000 3 a 3000 要 求 用 一 个 SQL 语 句 注 意 是 一 个 的 处 所 有 人 不 区 分 人 员 每 个 月 及 上 月 和 下 月 的 总 收 入 要求列表输出为 月份 当月收入 上月收入 下月收入 2 2000 1000 3000 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 5 删除重复记录删除重复记录 方法原理 1 Oracle中 每 一 条 记 录 都 有 一 个rowid rowid 在 整 个 数 据 库 中 是 唯 一 的 rowid确 定 了 每 条 记 录 是 在ORACLE中 的 哪 一 个 数 据 文 件 块 行 上 2 在 重复 的 记录 中 可 能 所有 列 的 内 容 都相 同 但 rowid 不 会相 同 所 以 只要 确 定出 重 复记 录 中 那些具有最大rowid的就可以了 其余全部删除 实现方法 SQL create table a 2 bm char 4 编码 3 mc varchar2 20 名称 4 5 SQL select rowid bm mc from a ROWID BM MC 000000D5 0000 0002 1111 1111 000000D5 0001 0002 1112 1111 000000D5 0002 0002 1113 1111 000000D5 0003 0002 1114 1111 000000D5 0004 0002 1111 1111 000000D5 0005 0002 1112 1111 000000D5 0006 0002 1113 1111 000000D5 0007 0002 1114 1111 查询到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 0002 1111 1111 000000D5 0001 0002 1112 1111 000000D5 0002 0002 1113 1111 000000D5 0003 0002 1114 1111 删除重复记录 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 0002 1111 1111 000000D5 0005 0002 1112 1111 000000D5 0006 0002 1113 1111 000000D5 0007 0002 1114 1111 其他其他组合函数组合函数 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 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 select concat 010 88888888 转23 高 乾 竞 电 话 from dual 高乾竞电话 010 88888888转23 2 2 LTRIMLTRIM和和RTRIMRTRIM LTRIM 删除左边出现的字符串 RTRIM 删除右边出现的字符串 SQL select ltrim rtrim gao qian jing from dual LTRIM RTRIM gao qian jing 3 3 SUBSTR string start count SUBSTR string start count 取子字符串 从start开始 取count个 SQL select substr3 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 返回字符串的长度 ORALCEORALCE 常识 及 常识 及 SQL 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 ORACLE 9i 中的 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 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 3000 from emp SQL select ename sal 12 sal 100 from 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 员工姓名 员工薪水 KING 60000 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 92 46 50 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 92 45 40 32 求模函数 MOD a b 返回 a被b 整除后的余数 33 Oracle 内部默认的日期格式 DD MON YY 24 9 月 06 34 DUAL 哑元系统表 是名义表 只能范围唯一值 35 Date类型的算术运算 以天为单位 例如 部门编号为10的员工分别工作了多少年 SQL select ename sysdate hiredate 365 as years from emp where deptno 10 ENAME YEARS CLARK 25 3108341 KING 24 8697382 MILLER 24 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 varchar2 or char number 当字符串是数字字符时 varchar2 or 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 3 用DECODE函数 进行匹配和显示 select vCountryName as 国家名称 DECODE vCountryName 中国 086 日本 116 as 国家编号 from countrys 国家名称 国家编号 中国 086 日本 116 韩国 结果 在DECODE中存在且成功匹配的值将会被显示 否则显示为 NULL S SQLQL 语句书可以提高执行效率的语句书可以提高执行效率的方法方法 1 1 操作符号 操作符号 NOT INNOT 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 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 1 and dy dj 1KV以下 以上两个 SQL 中 dy dj 电压等级 及 xh bz 销户标志 两个字段都没进行索引 所以执行的时候都是全表扫描 如果 dy dj 1KV 以下 条件在记录集内比率为 99 而 xh bz 1 的比率只为 0 5 在进行第一条 SQL 的时候 99 条记录 都进行 dy dj 及 xh bz 的 比较 而在进行第二条 SQL 的时