常见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='悟空')))；
