SQL面试题与附解收集（一）

数据库三范式是什么?

答：

1NF:字段不可分;
2NF:有主键，非主键字段依赖主键;
3NF:非主键字段不能相互依赖; 

解释:
1NF:原子性 字段不可再分,否则就不是关系数据库;
2NF:唯一性 一个表只说明一个事物;
3NF:每列都与主键有直接关系，不存在传递依赖; 

 

说出一些数据库优化方面的经验?

索引内部原理：想象成Dictionary，插入、删除、更新的速度慢了，

加上索引也多占用了空间，查询的速度快了。加上索引以后速度提升非常明显。

（1）在经常检索的字段上（select * from Person where Name=@Name）

使用索引提高查询速度。（2）select中只列出必要的字段，而不是*。

（3）避免隐式类型转换造成的全表扫描，在索引上使用函数也会造成全表扫描

（因为索引只是为字段建立的，一旦使用表达式或者函数，那么索引就是失效了，

当然也可以使用“函数索引”、“表达式索引”解决这个问题），

使用索引不一定能提高查询速度。（4）避免在索引列上使用计算（where Name+'A'=@MyName）

 

什么是数据库事务？

事务三个特性：一致性、原子性、隔离性。

几个SQL语句，要么全部执行成功，要么全部执行失败。

要么全部导入成功，要么全部导入失败。事务的三个操作：Begin、Commit、RollBack。

事务(Transaction)是访问并可能更新数据库中各种数据项的一个程序执行单元(unit)

 

请你简单的说明数据库建立索引的优缺点

答：使用索引可以加快数据的查询速度，不过由于数据插入过程中会建索引，所以会降低数据的插入、更新速度，索引还会占磁盘空间。

 

存储过程相比于sql语句有什么优点，大数据分析时，除使用存储过程代替sql外还有哪些有效方法？

答：存储过程比较安全（数据库层）、高效（网络传输数据量小，预编译）。

优化SQL语句，多用临时表，少用或者不用游标；根据业务合理分库分表，建立索引；使用NOSQL。

union与union all

Union因为要进行重复值扫描，所以效率低。如果合并没有刻意要删除重复行，那么就使用Union All;

union和union all的区别是,union会自动压缩多个结果集合中的重复结果，而union all则将所有的结果全部显示出来，不管是不是重复。

select count(*)和select count(1)的区别
一般情况下，Select Count (*)和Select Count(1)两着返回结果是一样的

假如表沒有主键(Primary key), 那么count(1)比count(*)快，

如果有主键的話，那主键作为count的条件时候count(主键)最快

如果你的表只有一个字段的话那count(*)就是最快的

count(*) 跟 count(1) 的结果一样，都包括对NULL的统计，而count(column) 是不包括NULL的统计

电商平台高并发下数据库性能瓶颈问题，

总结如下：

1.绝大部份的BS系统最大的性能瓶颈我觉得应该在DATABASE          

       为什么？因为其它影响因素（网络，存储，WEB服务器......），是可以通过投入比较快速的解决           

       网络？通过增加带宽解决；存储？通过存储设备或区域存储，即可解决大容量，可靠性问题；

       WEB服务器？可以通过四层或七层负 载均衡解决。

 2.而DATABASE最大受限在INSERT，也就是业务上所说的“提交”或“保存”        

       为什么？对于数据的查询，可以通过缓存集群解决。提交或保存，带来的DB是write lock，是排它性；并随着数据量大或并发高，锁的粒度，锁的时长更大，问题更严重。  

3.解决之道？           

         队列也。

1、select 1 与 select *的区别
selelct 常量 from ... 对应所有行，返回的永远只有一个值，即常量。所以正常只会用来判断是否有还是没有（比如exists子句）。而select * from ... 是返回所有行的所有列。
性能上的差异，关键看你的from和where子句。比如说如果你的where条件中可以通过索引，那显然 select 1 from ... 的性能比 select * from ... 好。
2、select sum(1)的使用
select count(*)返回所有满足条件的记录数，此时同select sum(1)
但是sum()可以传任意数字，负数、浮点数都可以，返回的值是传入值n*满足条件记录数m

 

写出一条Sql语句：取出表A中第31到第40记录（SQLServer,以自动增长的ID作为主键,注意：ID可能不是连续的）。（至少写两种）
答：解1:  select top 10 * from A where id not in (select top 30 id from A) 

解2:  select top 10 * from A where id > (select max(id) from (select top 30 id from A )as A) 

实际上考察的就是分页。

declare @pagecount integer --每页多少条记录
declare @pageindex integer --第几页
set @pagecount = 10   --初始化每页10条记录
set @pageindex = 3    --初始化当前的页数

--方式1：通过top关键字定位
select top (@pagecount) name from A where id not in
(select top (@pagecount*@pageindex) id from A)

--方式2：通过row_number函数定位
select name from (select ROW_NUMBER() over (order by id) as rownum,name from A) as D
where rownum between @pageindex*@pagecount+1 and (@pageindex+1)*@pagecount

 

编写SQL从A表中查出Name字段重复三条以上的记录，并编写SQL删除这些重复记录

答：查询Name字段记录重复三条以上的记录

select name from A group by name having count(name)>3

删除重复记录

if exists(select * from sysobjects where name = 'tempA')
  drop table tempA
select ROW_NUMBER() over (order by name ) as rowid, name into tempA from A

select * from tempA

--删除重复记录，只保留rowid最小的那一行
delete from tempA where name in
(select name from tempA group by name having count(name)>3)
and rowid not in (select min(rowid) from tempA group by name having count(name)>3)

select * from tempA

 

学生表S（S_ID，S_Name）,课程表B（B_ID，B_Name），成绩表C（S_ID，B_ID,C_Score），现需要将成绩表中选了课程名称为a的学生成绩低于60的同学的成绩乘以1.1。

答：

with a as(
select S_ID,C_Score from C,B where C.B_ID = B.B_ID and B.B_Name = 'a')
update a set C_Score = C_Score*1.1 where C_Score<60

 

查询每门课程成绩都大于80分学生的学号

数据库 表 student
name      score  course
A             85   语文
A             75   数学
A             82   英语
B             75   语文
B             89   数学
B             79   英语
C             90  语文
C            100  数学
C            100  英语

请找出每门课程都超过80分的那个人名字的SQL语句

--SQL1:
select name from test.stu
group by name
having count(score) =sum(case  when score>80 then 1 else 0 end )
--SQL2:
select name from stu
group by name
having name not in (
select name from stu
where score <80)
--SQL3:
select name from test.stu
group by name
having min(score)>=80

数据库表
Student(S#,Sname,Sage,Ssex) 学生表 
Course(C#,Cname,T#) 课程表 
SC(S#,C#,score)	成绩表 
Teacher(T#,Tname) 教师表
1)查询“001”课程比“002”课程成绩高的所有学生的学号； 
2) 查询平均成绩大于60分的同学的学号和平均成绩
3) 查询所有同学的学号、姓名、选课数、总成绩；

--1
select SC1.S#
  from SC SC1 JOIN SC SC2 ON SC1.S#=SC2.S#
    WHERE SC1.C#='001' AND SC2.C#='002' AND SC1.score>SC2.score
 
--2
select S#,AVG(score) 平均成绩
  from SC
    group by S#
      having AVG(score)>60
 
--3
select Student.S#,Sname,COUNT(*) 选课数,SUM(score) 总成绩
   from Student JOIN SC on Student.S#=SC.S#
      group by Student.S#,Sname

表中有A B C三列,用SQL语句实现：当A列大于B列时选择A列否则选择B列，当B列大于C列时选择B列否则选择C列。
select (case when a>b then a else b end),(case when b>c then b else c end) from tab4

--http://www.educity.cn/wenda/391546.html