常见SQL问题

一、数据库常见概念

1.触发器的作用？

 

触发器是一中特殊的存储过程，主要是通过事件来触发而被执行的。它可以强化约束，来维护数据的完整性和一致性，可以跟踪数据库内的操作从而不允许未经许可的更新和变化。可以联级运算。如，某表上的触发器上包含对另一个表的数据操作，而该操作又会导致该表触发器被触发。

 

2.什么是存储过程？用什么来调用？

 

存储过程是一个预编译的SQL 语句，优点是允许模块化的设计，就是说只需创建一次，以后在该程序中就可以调用多次。如果某次操作需要执行多次SQL ，使用存储过程比单纯SQL 语句执行要快。可以用一个命令对象来调用存储过程。

3.索引的作用？和它的优点缺点是什么？

索引就一种特殊的查询表，数据库的搜索引擎可以利用它加速对数据的检索。它很类似与现实生活中书的目录，不需要查询整本书内容就可以找到想要的数据。索引可以是唯一的，创建索引允许指定单个列或者是多个列。缺点是它减慢了数据录入的速度，同时也增加了数据库的尺寸大小。

4.什么是内存泄漏？

一般我们所说的内存泄漏指的是堆内存的泄漏。堆内存是程序从堆中为其分配的，大小任意的，使用完后要显示释放内存。当应用程序用关键字new 等创建对象时，就从堆中为它分配一块内存，使用完后程序调用free 或者delete 释放该内存，否则就说该内存就不能被使用，我们就说该内存被泄漏了。

5.维护数据库的完整性和一致性，你喜欢用触发器还是自写业务逻辑？为什么？

我是这样做的，尽可能使用约束，如check, 主键，外键，非空字段等来约束，这样做效率最高，也最方便。其次是使用触发器，这种方法可以保证，无论什么业务系统访问数据库都可以保证数据的完整新和一致性。最后考虑的是自写业务逻辑，但这样做麻烦，编程复杂，效率低下。

6.什么是事务？什么是锁？

事务就是被绑定在一起作为一个逻辑工作单元的SQL 语句分组，如果任何一个语句操作失败那么整个操作就被失败，以后操作就会回滚到操作前状态，或者是上有个节点。为了确保要么执行，要么不执行，就可以使用事务。要将有组语句作为事务考虑，就需要通过ACID 测试，即原子性，一致性，隔离性和持久性。

锁：在所以的 DBMS中，锁是实现事务的关键，锁可以保证事务的完整性和并发性。与现实生活中锁一样，它可以使某些数据的拥有者，在某段时间内不能使用某些数据或数据结构。当然锁还分级别的。

7.什么叫视图？游标是什么？

视图是一种虚拟的表，具有和物理表相同的功能。可以对视图进行增，改，查，操作，试图通常是有一个表或者多个表的行或列的子集。对视图的修改不影响基本表。它使得我们获取数据更容易，相比多表查询。

  游标：是对查询出来的结果集作为一个单元来有效的处理。游标可以定在该单元中的特定行，从结果集的当前行检索一行或多行。可以对结果集当前行做修改。一般不使用游标，但是需要逐条处理数据的时候，游标显得十分重要。

8.什么是主键?什么是外键?

主键是表格里的(一个或多个)字段，只用来定义表格里的行;主键里的值总是唯一的。外键是一个用来建立两个表格之间关系的约束。这种关系一般都涉及一个表格里的主键字段与另外一个表格(尽管可能是同一个表格)里的一系列相连的字段。那么这些相连的字段就是外键。

9.数据库事务的四大特征？

一个设计良好的数据库可以帮我们保证事务具有四大特性(ACID)：

原子性：原子性是指事务是一个不可分割的工作单位，事务中的操作要么都发生，要么都不发生。

一致性：如果事务执行之前数据库是一个完整的状态，那么事务结束后，无论事务是否执行成功，数据库仍然是一个完整的状态。

　　数据库的完整状态：当一个数据库中的所有的数据都符合数据库中所定义的所有约束，此时可以称数据库是一个完整的状态。

隔离型：多个用户并发访问数据库时，一个用户的事务不能被其他用户的事务所干扰，多个并发事务之间数据要相互隔离。

持久性：指一个事务一旦被提交，他对数据库的影响是永久性的。

如果两个线程一个修改，一个查询：

　脏读：一个事务读取到另一个事务未提交的数据。

　幻读：在当前事务中，读取到了另一个事务提交的插入的数据。

　不可重复读：在当前事务，读取到了另一事务提交的更新或删除的数据。

10.数据库的三大范式？

 第一范式（1NF）：对于添加的一个规范要求，所有的域都应该是原子性的，即数据库表的每一列都是不可分割的原子数据项，而不能是集合，数组，记录等非原子数据项。即实体中的某个属性有多个值时，必须拆分为不同的属性。

 第二范式（2NF）：在第一范式（1NF）的基础上建立起来的，即满足第二范式（2NF）必须先满足第一范式（1NF）。第二范式（2NF）要求数据库表中的每个实例或记录必须可以被唯一地区分。要求实体的属性完全依赖于主关键字。所谓完全依赖是指不能存在仅依赖主关键字一部分的属性

 第三范式（3NF）：在2NF基础上，任何非主属性不依赖于其它非主属性（在2NF基础上消除传递依赖）

 

 

二、简单SQL编写：

1.用一条SQL 语句查询出每门课都大于80 分的学生姓名？

// 第一种方式：
select name from table where name not in ( select name from table where score < 80 );

// 第二种方式：
select name from table group by name having min(score) >= 80;

 

2.删除除了自动编号id不同, 其他都相同的学生冗余信息？  

字段分别是：id，stunum，name，course，score

delete table where id not in ( select min(id) from table group by stunum,name,course,score );

3.一个叫 team 的表，里面只有一个字段name, 一共有4 条纪录，分别是a,b,c,d, 对应四个球对，现在四个球对进行比赛，用一条sql 语句显示所有可能的比赛组合?

// 所有组合：ab,ac,ad,  bc,bd, cd   所有左侧字母都小于右侧字母
select a.name,b.name from team a,team b where a.name < b.name order by a.name,b.name ;

4.从TestDB 数据表中查询出所有月份的发生额都比101 科目相应月份的发生额高的科目？

AccID ：科目代码，Occmonth ：发生额月份，DebitOccur ：发生额

select a.AccID from TestDB a,( select Occmonth,max(DebitOccur) from TestDB where AccID = '101' group by Occmonth )b 
　where a.Occmonth = b.Occmonth and a.DebitOccur > b.DebitOccur;

5.怎么把这样一个表儿
year   month amount
1991   1     1.1
1991   2     1.2
1991   3     1.3
1991   4     1.4
1992   1     2.1
1992   2     2.2
1992   3     2.3
1992   4     2.4
查成这样一个结果
year m1   m2   m3   m4
1991 1.1 1.2 1.3 1.4
1992 2.1 2.2 2.3 2.4 

select year, 
(select amount from table m where month=1 and m.year=aaa.year) as m1,
(select amount from table m where month=2 and m.year=aaa.year) as m2,
(select amount from table m where month=3 and m.year=aaa.year) as m3,
(select amount from table m where month=4 and m.year=aaa.year) as m4
from table group by year

6.复制表( 只复制结构, 源表名：a新表名：b) 

select * into b from a where 1<>1;   // where1=1，拷贝表结构和数据内容
Oracle: create table b 
　　　　　　as
　　　　　　select * from a where 1=2;

7.拷贝表( 拷贝数据, 源表名：a目标表名：b) 

 insert into b(a, b, c) select d,e,f from a; 

8.显示文章、提交人和最后回复时间

select a.title,a.username,b.adddate from table a,(select max(adddate) adddate from table where table.title=a.title) b;

9.日程安排提前五分钟提醒

select * from 日程安排 where datediff('minute',f 开始时间,getdate())>5；

10.两张关联表，删除主表中已经在副表中没有的信息

delete from info where not exists (select * from infobz where info.infid=infobz.infid )；

11.有两个表A 和B ，均有key 和value 两个字段，如果B 的key 在A 中也有，就把B 的value 换为A 中对应的value？

update b set b.value=(select a.value from a where a.key=b.key) where b.id in(select b.id from b,a where b.key=a.key);

 

12.已知有如下4张表：

学生表：STUDENT(S#,SNAME,SAGE,SSEX)

课程表：COURSE(C#,CNAME,T#)

成绩表：SC(S#,C#,SCORE)

教师表：TEACHER(T#,TNAME)

 

（1）查询课程编号为“001”的课程比“002”的课程成绩高的所有学生的学号？

select x.sno,x.score,y.score from sc x,sc y
where  x.cno=1001 
   and y.cno=1002
   and x.sno=y.sno
   and x.score > y.score；

（2）查询平均成绩大于60分的学生的学号和平均成绩？

select sno,avg(score) from sc
group by sno
having avg(score)>60；

（3）查询所有学生的学号、姓名、选课数、总成绩？

select sc.sno,sname,count(cno),sum(score)
from student join sc on student.sno=sc.sno
group by sc.sno,sname；

（4）查询姓“悟”的老师的个数？

select count(Tname) from teacher
where Tname like '悟%'；

（5）查询没学过“悟空”老师课的学生的学号、姓名？

select sno,sname from student 
where sno not in(select sno from SC where cno in(select cno from course
where tno in(select tno from teacher
where tname='悟空')))；