（）select count(*) ,tid
from ( select employee921.id,employee921.name,employee921.salary,employee921.deptid tid from employee921 where salary >
(select avg(salary) from employee921 where deptid = tid)
) as t
group by tid ;
另外一种方式：关联查询 select a.ename,a.salary,a.deptid from emp a,
(select deptd,avg(salary) avgsal from emp group by deptid ) b where a.deptid=b.deptid and a.salary>b.avgsal; 1、存储过程与触发器必须讲，经常被面试到?
create procedure insert_Student (_name varchar(50),_age int ,out _id int) begin insert into student value(null,_name,_age); select max(stuId) into _id from student; end;
call insert_Student('wfz',23,@id); select @id;
mysql> create trigger update_Student BEFORE update on student FOR EACH ROW -> select * from student; 触发器不允许返回结果
create trigger update_Student BEFORE update on student FOR EACH ROW insert into student value(null,'zxx',28);
mysql的触发器目前不能对当前表进行操作
create trigger update_Student BEFORE update on student FOR EACH ROW delete from articles where id=8;
这个例子不是很好，最好是用删除一个用户时，顺带删除该用户的所有帖子 这里要注意使用OLD.id
触发器用处还是很多的，比如校内网、开心网、Facebook，你发一个日志，自动通知好友，其实就是在增加日志时做一个后触发，再向通知表中写入条目。因为触发器效率高。而UCH没有用触发器，效率和数据处理能力都很低。 存储过程的实验步骤：
mysql> delimiter |
mysql> create procedure insertArticle_Procedure (pTitle varchar(50),pBid int,out pId int)
-> begin
-> insert into article1 value(null,pTitle,pBid); -> select max(id) into pId from article1; -> end; -> |
Query OK, 0 rows affected (0.05 sec)
mysql> call insertArticle_Procedure('传智播客',1,@pid); -> |
Query OK, 0 rows affected (0.00 sec)
mysql> delimiter ; mysql> select @pid; +------+ | @pid | +------+
| 3 | +------+
1 row in set (0.00 sec)
mysql> select * from article1; +----+--------------+------+ | id | title | bid | +----+--------------+------+ | 1 | test | 1 | | 2 | chuanzhiboke | 1 | | 3 | 传智播客 | 1 | +----+--------------+------+ 3 rows in set (0.00 sec)
触发器的实验步骤：
create table board1(id int primary key auto_increment,name varchar(50),ar ticleCount int);
create table article1(id int primary key auto_increment,title varchar(50) ,bid int references board1(id));
delimiter |
create trigger insertArticle_Trigger after insert on article1 for each ro w begin
-> update board1 set articleCount=articleCount+1 where id= NEW.bid; -> end; -> |
delimiter ;
insert into board1 value (null,'test',0);
insert into article1 value(null,'test',1);
还有，每插入一个帖子，都希望将版面表中的最后发帖时间，帖子总数字段进行同步更新，用触发器做效率就很高。下次课设计这样一个案例，写触发器时，对于最后发帖时间可能需要用declare方式声明一个变量，或者是用NEW.posttime来生成。
1、数据库三范式是什么?
第一范式（1NF）：字段具有原子性,不可再分。所有关系型数据库系统都满足第一范式） 数据库表中的字段都是单一属性的，不可再分。例如，姓名字段，其中的姓和名必须作为一个整体，无法区分哪部分是姓，哪部分是名，如果要区分出姓和名，必须设计成两个独立的字段。
第二范式（2NF）：
第二范式（2NF）是在第一范式（1NF）的基础上建立起来的，即满足第二范式（2NF）必须先满足第一范式（1NF）。 要求数据库表中的每个实例或行必须可以被惟一地区分。通常需要为表加上一个列，以存储各个实例的惟一标识。这个惟一属性列被称为主关键字或主键。
第二范式（2NF）要求实体的属性完全依赖于主关键字。所谓完全依赖是指不能存在仅依赖主关键字一部分的属性，如果存在，那么这个属性和主关键字的这一部分应该分离出来形成一个新的实体，新实体与原实体之间是一对多的关系。为实现区分通常需要为表加上一个列，以存储各个实例的惟一标识。简而言之，第二范式就是非主属性非部分依赖于主关键字。
第三范式的要求如下：
满足第三范式（3NF）必须先满足第二范式（2NF）。简而言之，第三范式（3NF）要求一个数据库表中不包含已在其它表中已包含的非主关键字信息。 所以第三范式具有如下特征： 1，每一列只有一个值
2，每一行都能区分。
3，每一个表都不包含其他表已经包含的非主关键字信息。
例如，帖子表中只能出现发帖人的id，而不能出现发帖人的id，还同时出现发帖人姓名，否则，只要出现同一发帖人id的所有记录，它们中的姓名部分都必须严格保持一致，这就是数据冗余。
1、说出一些数据库优化方面的经验?
用PreparedStatement 一般来说比Statement性能高：一个sql 发给服务器去执行，涉及步骤：语法检查、语义分析， 编译，缓存 “inert into user values(1,1,1)”-?二进制 “inert into user values(2,2,2)”-?二进制 “inert into user values(?,?,?)”-?二进制
有外键约束会影响插入和删除性能，如果程序能够保证数据的完整性，那在设计数据库时就去掉外键。（比喻：就好比免检产品，就是为了提高效率，充分相信产品的制造商） （对于hibernate来说，就应该有一个变化：empleyee->Deptment对象，现在设计时就成了employee?deptid）
看mysql帮助文档子查询章节的最后部分，例如，根据扫描的原理，下面的子查询语句要比第二条关联查询的效率高：
1. select e.name,e.salary where e.managerid=(select id from employee where name='zxx');
2. select e.name,e.salary,m.name,m.salary from employees e,employees m where e.managerid = m.id and m.name='zxx';
表中允许适当冗余，譬如，主题帖的回复数量和最后回复时间等
将姓名和密码单独从用户表中独立出来。这可以是非常好的一对一的案例哟！
sql语句全部大写，特别是列名和表名都大写。特别是sql命令的缓存功能，更加需要统一大小写，sql语句?发给oracle服务器?语法检查和编译成为内部指令?缓存和执行指令。根据缓存的特点，不要拼凑条件，而是用?和PreparedStatment
还有索引对查询性能的改进也是值得关注的。
备注：下面是关于性能的讨论举例
4航班 3个城市 m*n
select * from flight,city where flight.startcityid=city.cityid and city.name='beijing'; m + n
select * from flight where startcityid = (select cityid from city where cityname='beijing');
select flight.id,'beijing',flight.flightTime from flight where startcityid = (select cityid from city where cityname='beijing')
2、union和union all有什么不同?
假设我们有一个表Student，包括以下字段与数据： drop table student; create table student (
id int primary key,
name nvarchar2(50) not null, score number not null );
insert into student values(1,'Aaron',78); insert into student values(2,'Bill',76); insert into student values(3,'Cindy',89); insert into student values(4,'Damon',90); insert into student values(5,'Ella',73); insert into student values(6,'Frado',61); insert into student values(7,'Gill',99); insert into student values(8,'Hellen',56); insert into student values(9,'Ivan',93); insert into student values(10,'Jay',90); commit;
Union和Union All的区别。 select *
from student where id < 4 union select *
from student
where id > 2 and id < 6 结果将是
1 Aaron 78 2 Bill 76 3 Cindy 89
4 Damon 90 5 Ella 73
如果换成Union All连接两个结果集，则返回结果是： 1 Aaron 78 2 Bill 76 3 Cindy 89 3 Cindy 89
4 Damon 90 5 Ella 73
可以看到，Union和Union All的区别之一在于对重复结果的处理。
UNION在进行表链接后会筛选掉重复的记录，所以在表链接后会对所产生的结果集进行排序运算，删除重复的记录再返回结果。实际大部分应用中是不会产生重复的记录，最常见的是过程表与历史表UNION。如： select * from gc_dfys union
select * from ls_jg_dfys
SSH框架面试题(自己+别人的试题)
