面试时常问的10个数据库问题

2018-06-11  本文已影响0人  Asian_Road
微信图片_20180611205732.jpg
  1. inner join 和 outer join 的区别?

假设你要join两个没有重复列的表,这是最常见的情况:
inner join A 和 B 获得的是A和B的交集(intersect),即韦恩图(venn diagram) 相交的部分.
outer join A和B获得的是A和B的并集(union), 即韦恩图(venn diagram)的所有部分.

假定有两张表,每张表只有一列,列数据如下:
A B
1 3
2 4
3 5
4 6
注意(1,2)是A表独有的,(3,4) 两张共有, (5,6)是B独有的。

Inner join 使用等号进行inner join以获得两表的交集,即共有的行。

select * from a INNER JOIN b on a.a = b.b;
select a.*,b.*  from a,b where a.a = b.b;

a | b
--+--
3 | 3
4 | 4

Left outer join
full outer join 除了获得B表中符合条件的列外,还将获得A表所有的列。

select * from a LEFT OUTER JOIN b on a.a = b.b;
select a.*,b.*  from a,b where a.a = b.b(+);

a | b
--+-----
1 | null
2 | null
3 | 3
4 | 4

Full outer join
full outer join 得到A和B的交集,即A和B中所有的行.。如果A中的行在B中没有对应的部分,B的部分将是 null, 反之亦然。

select * from a FULL OUTER JOIN b on a.a = b.b;

a | b
-----+-----
1 | null
2 | null
3 | 3
4 | 4
null | 6
null | 5


2.union与union all的区别

在数据库中,union和union all关键字都是将两个结果集合并为一个,但这两者从使用和效率上来说都有所不同。

union在进行表链接后会筛选掉重复的记录,所以在表链接后会对所产生的结果集进行排序运算,删除重复的记录再返回结果。如

select * from test_union1 
    union 
select * from test_union2 

这个SQL在运行时先取出两个表的结果,再用排序空间进行排序删除重复的记录,最后返回结果集,如果表数据量大的话可能会导致用磁盘进行排序。
而union all只是简单的将两个结果合并后就返回。这样,如果返回的两个结果集中有重复的数据,那么返回的结果集就会包含重复的数据了。
从效率上说,union all要比union快很多,所以,如果可以确认合并的两个结果集中不包含重复的数据的话,那么就使用union all,如下:

select * from test_union1 
  union all 
select * from test_union2 

使用 union 组合查询的结果集有两个最基本的规则:
1。所有查询中的列数和列的顺序必须相同。
2。数据类型必须兼容


  1. write a query to find and remove duplicates in a table without creating another table

     DELETE FROM table 
     WHERE name IN (SELECT name FROM table GROUP BY name HAVING COUNT(*) > 1)
     AND NOT id IN (SELECT min(id) FROM table GROUP BY name)
    

  1. 什么是游标,解释不同类型的游标

游标实际上是一种从包括多条数据记录的结果集中每次提取一条记录的机制。
游标是系统为用户开设的一个数据缓冲区,存放SQL语句的执行结果 。
游标(cursor)必须在声明处理程序之前被声明,并且变量和条件必须在声明游标或处理程序之前被声明。
游标分为 显式游标 和 隐式游标
当查询返回结果超过一行时,就需要一个显式游标,此时用户不能使用select into语句。 显式游标在PL/SQL块的声明部分声明,在执行部分或异常处理部分打开,取数据,关闭。 这里要做一个声明,我们所说的游标通常是指显式游标,而显式游标需要被声明。
当我们在PL/SQL中进行非查询(或者返回单条记录的查询)语句,如update、delete、insert等时,ORACLE 系统会自动地为这些操作设置游标并创建其工作区,并且隐式游标的名字为SQL,由ORACLE 系统定义。 对于隐式游标的操作,如定义、打开、取值及关闭操作,都由ORACLE 系统自动地完成,无需用户进行处理。


  1. stored procedure和Function的差异

存储过程(Stored Procedure)是数据库系统中,一组为了完成特定功能的SQL 语句集,经编译后存储在数据库中,用户通过指定存储过程的名字并给出参数(如果该存储过程带有参数)来执行它。


image
  1. 数据库范式

数据库的设计范式是数据库设计所需要满足的规范,满足这些规范的数据库是简洁的、结构明晰的,同时,不会发生插入(insert)、删除(delete)和更新(update)操作异常。


  1. primary key与unique的区别

     UNIQUE 约束的字段中不能包含重复值,可以为一个或多个字段定义 UNIQUE 约束
     - 作为Primary Key的域/域组不能为null,而Unique Key可以。 
     - 在一个表中只能有一个Primary Key,而多个Unique Key可以同时存在。 
     简单的说,primary key = unique +  not null 
    

  1. 数据库中drop delete truncate 的区别

    1. drop (删除表):删除内容和定义,释放空间。简单来说就是把整个表去掉,要新增数据是不可能的,除非新增一个表。 drop语句将删除表的结构被依赖的约束(constrain),触发器(trigger)索引(index);依赖于该表的存储过程/函数将被保留,但其状态会变为:invalid。
    2. truncate (清空表中的数据):删除内容、释放空间但不删除定义(保留表的数据结构)。与drop不同的是,只是清空表数据而已。
      注意:truncate 不能删除行数据,要删就要把表清空。
    3. delete (删除表中的数据):delete 语句用于删除表中的行。
      delete语句执行删除的过程是每次从表中删除一行,并且同时将该行的删除操作作为事务记录在日志中保存,以便进行进行回滚操作。
      truncate与不带where的delete :只删除数据,而不删除表的结构(定义)
    4. truncate table 删除表中的所有行,但表结构及其列、约束、索引等保持不变。新行标识所用的计数值重置为该列的种子。如果想保留标识计数值,请改用delete。
      如果要删除表定义及其数据,请使用 drop table 语句。
    5. 对于由foreign key约束引用的表,不能使用truncate table ,而应使用不带where子句的delete语句。由于truncate table 记录在日志中,所以它不能激活触发器。
    6. 执行速度,一般来说: drop> truncate > delete。
    7. delete语句是数据库操作语言(dml),这个操作会放到 rollback segement 中,事务提交之后才生效;如果有相应的 trigger,执行的时候将被触发。
    8. truncate、drop 是数据库定义语言(ddl),操作立即生效,原数据不放到 rollback segment 中,不能回滚,操作不触发 trigger。

  1. 找出每个部门员工中第三高的薪水
    Select EmpID,empname,deptid,salary
     From (
    Select *
       ,RN  = Row_Number() over (Partition By deptid Order By Salary)
       ,Cnt = sum(1) over (Partition By deptid)
     From  employee1
       ) A
     Where RN = case when Cnt<3 then Cnt else 3 end

10.rank和row_number的区别 并解释

row_number() 是没有重复值的排序(即使两天记录相等也是不重复的),可以利用它来实现分页
dense_rank() 是连续排序,两个第二名仍然跟着第三名
rank()       是跳跃拍学,两个第二名下来就是第四名
上一篇 下一篇

猜你喜欢

热点阅读