SQL面试题大全.pdf

部分常见部分常见 ORACLEORACLEORACLEORACLE 面试题以及面试题以及 SQLSQLSQLSQL 注意事项注意事项 一 表的创建 一 表的创建 一 表的创建 一 表的创建 一个通过单列外键联系起父表和子表的简单例子如下 CREATETABLE parent id INT NOT NULL PRIMARY KEY id CREATETABLE child id INT parent id INT INDEX par ind parent id FOREIGN KEY parent id REFERENCES parent id ON DELETE CASCADE 建表时注意不要用关键字当表名或字段名 如 insert use 等 CREATETABLE parent id INT NOT NULL PRIMARY KEY id TYPE INNODB InnoDBInnoDBInnoDBInnoDBTablesTablesTablesTables 概述概述 InnoDB 给 MySQL 提供了具有事务 commit 回滚 rollback 和崩溃修复能力 crashrecovery capabilities 的事务安全 transaction safe ACIDcompliant 型表 InnoDB提供了行锁 lockingonrowlevel 提供与Oracle类型一致的不加锁读取 non lockingreadinSELECTs 这些特性均提高了多用户并发操作的性能表现 在 InnoDB表中 不需要扩大锁定 lockescalation 因为InnoDB的列锁定 rowlevellocks 适宜非常小的空间 InnoDB是MySQL上第一个提供外键约束 FOREIGNKEYconstraints 的表引擎 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 42asselect from s empwhere 1 2 永假式 SQLSQLSQLSQL 查询练习题查询练习题查询练习题查询练习题 1 1 1 1 表 1 book 表 字段有id 主键 name 书名 表 2 bookEnrol 表 图书借出归还登记 字段有id bookId 外键 dependDate 变更时间 state 1 借出 2 归还 idname 1English 2Math 3JAVA idbookIddependDatestate 112009 01 021 212009 01 122 322009 01 141 412009 01 171 522009 02 142 622009 02 151 732009 02 181 832009 02 192 要求查询结果应为 被借出的书和被借出的日期 IdNamedependDate 1English2009 01 17 2Math2009 02 15 Select e bookId b name e dependDate from book b bookEnrol ewhere 第二个表是用来登记的 不管你是借还是还 都要添加一条记录 请写一个 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 2 2 第 1 题练习使用 group by having 子句 类似的笔试题还有 表一 各种产品年销售量统计表sale 年产品销量 2005a700 2005b550 2005c600 2006a340 2006b500 2007a220 2007b350 要求得到的结果应为 年产品销量 2005a700 2006b500 2007c350 即 每年销量最多的产品的相关信息 参考答案 Select from sale a wherenot exists select fromsalewhere年 a 年and销量 a 销量 or select from saleainnerjoin select 年 max 销量 as 销量 from salegroup by 年 b ona 年 b 年 and a 销量 b 销量 3 3 3 3 查询语句排名问题 查询语句排名问题 名次 姓名月积分 char 总积分 char 1WhatIsJava 199 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 表表 tbtbtbtb uidmark 17 16 23 22 25 34 33 48 41 43 想查出 uid 4 的名次 uidmc 43 selectuid sum mark astotalfromtab namegroupbyuidorderbytotal desc 4 4 4 4 表 A 字段如下 monthname income 月份人员 收入 1a1000 2a2000 3a3000 要求用一个 SQL 语句 注意是一个 的处所有人 不区分人员 每个月及上月和下月的总收入 要求列表输出为 月份 当月收入 上月收入 下月收入 2200010003000 Select Select Month FromTableWhere Month To Char Sysdate mm 月份 Select Sum Income FromTableWhere Month To Char Sysdate mm 当月收入 Select Sum Income FromTableWhere To Number Month To Number Extract Month From Sysdate 1 上月收入 Select Sum Income From Table WhereTo Number Month To Number Extract Month From Sysdate 1 下月收入 From Dual 5 5 5 5 删除重复记录删除重复记录 方法原理 1 Oracle 中 每一条记录都有一个 rowid rowid 在整个数据库中是唯一的 rowid 确定了每条记录是在 ORACLE 中的哪一个数据文件 块 行上 2 在重复的记录中 可能所有列的内容都相同 但 rowid 不会相同 所以只要确定出重复记录中 那些具有最大 rowid 的就可以了 其余全部删除 实现方法 SQL create table a 2 bm char 4 编码 3 mcvarchar2 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 aa 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 ORDERBYTop n column name WHERE ROWNUM N 例例1 1 1 1 查询 Employee 表的顶部10条记录 方法1 单表时可以用 select cEmployeeCode vFirstName vLastNamefrom employee where rownum 10 方法2 较复杂的查询 建议使用这种 select from select rownum as num cEmployeeCode vFirstName vLastNamefrom employee where num selectconcat 010 88888888 转 23 高乾竞电话fromdual 高乾竞电话 010 88888888 转 23 2 2 2 2 LTRIMLTRIMLTRIMLTRIM 和和 RTRIMRTRIMRTRIMRTRIM LTRIM删除左边出现的字符串 RTRIM删除右边出现的字符串 SQL selectltrim rtrim gaoqianjing fromdual LTRIM RTRIM gaoqianjing 3 SUBSTR string start count 3 SUBSTR string start count 3 SUBSTR string start count 3 SUBSTR string start count 取子字符串 从 start开始 取 count 个 SQL selectsubstr3 8 fromdual 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 返回字符串的长度 ORALCEORALCEORALCEORALCE常识常识 及及 SQLSQLSQLSQL 基本语法基本语法 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 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 wherecondition 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 whereename 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 年薪 fromemp 20 过滤重复行 使用关键字 distinct SQL select distinct fromemp 21 SQLPLUS 访问 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 wheredeptno 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 vCountryNameas 国家名称 DECODE vCountryName 中国 086 日本 116 as 国家编号 from countrys 国家名称 国家编号 中国 086 日本 116 韩国 结果 在 DECODE 中存在且成功匹配的值将会被显示 否则显示为 NULL S S S SQLQLQLQL语句书可以提高执行效率的语句书可以提高执行效率的方法方法 1 1 1 1 操作符号 操作符号 NOTNOTNOTNOT ININININ操作符操作符 此操作是强列推荐不使用的 因为它不能应用表的索引 推荐方案 用 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 2 2 注意 union 和 union all 的区别 union 比 union all 多做了一步 distinct 操作 能用 union all 的情况下尽 量不用 union 如 两个表 A 和 B 都有一个序号字段 ID 要求两个表中的 ID 字段最大的值 selectmax id asmax id from selectidfrom表A unionall selectidfrom表 B t 3 查询时尽量不要返回不需要的行 列 另外在多表连接查询时 尽量改成连接查询 少用子查询 4 尽量少用视图 它的效率低 对视图操作比直接对表操作慢 可以用存储过程来代替它 特别的是不 要用视图嵌套 嵌套视图增加了寻找原始资料的难度 我们看视图的本质 它是存放在服务器上的被优化好了的已经产生了查询规划的 SQL 对单个表检索数 据时 不要使用指向多个表的视图 直接从表检索或者仅仅包含这个表的视图上读 否则增加了不必要的开销 查询受到干扰 为了加快视图 的查询 MsSQL 增加了视图索引的功能 5 创建合理的索引 对于插入或者修改比较频繁的表 尽量慎用索引 因为如果表中存在索引 插入 和修改时也会引起全表扫描 索引一般使用于 where 后经常用作条件的字段上 6 在表中定义字段或者存储过程 函数中定义参数时 将参数的大小设置为合适即可 勿设置太大 这样开销很大 7 Between 在某些时候比 IN 速度更快 Between 能够更快地根据索引找到范围 用查询优化器可见到差 别 select from chineseresumewheretitlein 男 女 Select from chineseresumewhere between 男 and 女 是一样的 由于 in 会在比较多次 所以有时会慢些 8 在必要是对全局或者局部临时表创建索引 有时能够提高速度 但不是一定会这样 因为索引也耗 费大量的资源 他的创建同是实际表一样 9 WHERE 后面的条件顺序影响 WHERE 子句后面的条件顺序对大数据量表的查询会产生直接的影响 如 Select from zl yhjbqk wheredy dj 1KV 以下 and xh bz 1 Select from zl yhjbqk wherexh bz 1 and dy dj 1KV 以下 以上两个 SQL 中 dy dj 电压等级 及 xh bz 销户标志 两个字段都没进行索引 所以执行的时候都 是全表扫描 如果 dy dj 1KV 以下 条件在记录集内比率为 99 而 xh bz 1 的比率只为 0 5 在进 行第一条 SQL 的时候 99