部分常见ORACLE面试题以及SQL注意事项 重点标注颜色.doc

部分常见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)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;/永假式课堂笔记注意事项：1.1.DDL: create table.drop table 表名;(数据定义语言:Data Definition Language)2.DML: o.update .delete .(数据操纵语言): Data Manipulation Language3.DQL:select.from.(数据查询语言:Data Query Language)2.-使用commit确认提交事务:Transaction3.-员工分布在哪些部门?每个部门显示一次select distinct deptno, job from employee_ning;4.distinct(去重复的)5.-模糊查询: like, -%:任意多个字符-_:代表一个字符6.-空值的查询-错的,空值不能使用=比较select ename, bonus from employee_ningwhere bonus = null;-对的select ename, bonus from employee_ningwhere bonus is null;7.-排序-order by salary; 薪水由低到高:正序select ename, salary, bonus from employee_ningorder by salary;-order by salary desc; 薪水由高到低排列:倒序select ename, salary, bonus from employee_ningorder by salary desc;order by列名 从高到底 +desc()从低到高查看表结构:desc(describe)倒序排序:desc(descend)8.数字函数select ENAME,salary*1.2345678 from tarena111;-截取select ename,trunc(salary*1.2345678,2) from tarena111;-四舍五入select ename,round(salary*1.2345678,2) from tarena111;9.日期select to_char(sysdate, yyyy-mm-dd hh24:mi:ss) from tarena111; to_date字符-日期 字符 - 数字 - PK id name age cid id name11 scott 18 2 1 1106班12 tiger 19 1 2 1105班13 tom 17 3 1104班-只有两条有班级的学生select , from student_wh s join class_wh con s.cid = c.id;15.复制表先创建一个新表，再select要复制的表的所有数据，用as来复制create table salgrade_wh1asselect * from salgrade_wh;-利用子查询向表中新增多条记录insert into emp_bak_wh(select * from employee_whwhere deptno = 10);-可以一步实现:create table emp_bak_wh(empno, ename, job, salary, depton)asselect empno, ename, job, salary, deptnofrom employee_whwhere deptno = 10;16.左连接与外连接-把没有部门的员工也列出来,-希望得到下述结果:1001 liucs developer bj1002 zwj developer bj1003 zsf account tj1009 wxb 17. 集合(结果集)操作-A并B,-union:去掉重复记录, 排序(消耗性能)-union all: 不去重,不排序/*如果结果集A/B没有重复记录,且不需要排序,则首选union all.*/select empno, ename, salary, deptnofrom employee_whwhere deptno = 20union allselect empno, ename, salary, deptno from employee_whwhere salary 7000;-交集select empno, ename, salary, deptnofrom employee_wh where deptno = 20intersectselect empno, ename, salary, deptno from employee_whwhere salary 7000;-差集:A-Bselect empno, ename, salary, deptnofrom employee_whwhere deptno = 20minusselect empno, ename, salary, deptno from employee_whwhere salary 7000;-使用左外连接(left outer join) select e.empno, e.ename, d.dname, d.locationfrom employee_wh eleft outer join department_wh don e.deptno = d.deptno;-t1 left outer join t2-等价写法: t2 right outer join t1-t1做驱动表1)如果t1表中的记录在匹配表中找不到匹配记录,则匹配一行空行2)外连接的结果集 = 内连接的结果集+ 驱动表中匹配不上的记录和 空值的组合3)外连接的本质是把驱动表中匹配不上的记录找回来,即一个都不能少.-t2(Department)做驱动表, -t1(Employee)做匹配表t1 right outer join t2t2 left outer join t1-查出所有的部门记录-包括没有员工的部门select e.empno, e.ename, d.dname, d.location from employee_wh eright outer join department_wh don e.deptno = d.deptno;-重要的是确定驱动表-t1一个都不能少t1 left outer join t2t2 right outer join t1-t2一个都不能少t2 left outer join t1t1 right outer join t2小结1:1)外连接的结果集 = 内连接的结果集 + 驱动表中在匹配表中找不到匹配记录的数据和空值的组合.2)驱动表和匹配表不能互换3)写外连接时:先写出内连接,再确定哪张表做驱动表.4)选择left join或者right join不重要重要的是选择哪张表做驱动表.-小结2：1)select 列名from 表名where 列名 (子查询);是单行比较运算符,子查询只能返回一行.2)select 列名from 表名where 列名 in (子查询);in是多行比较运算符,子查询可以返回多行.-查询每个部门的人数,-哪个部门的人数比部门20的人数多select deptno, count(*) count_emp from employee_whgroup by deptnohaving count(*) (select count(*) from employee_wh where deptno = 20);子查询:关联 / 非关联非关联:先子查询,子查询的结果参与主查询关联:先主查询,主查询的某些列值作为参