数据库部分
1、⽤两种⽅式根据部门号从⾼到低，⼯资从低到⾼列出每个员⼯的信息。employee:
eid,ename,salary,deptid;
select * from employee order by deptid desc,salary
2、列出各个部门中⼯资⾼于本部门的平均⼯资的员⼯数和部门号，并按部门号排序创建表：
mysql> create table employee921(id int primary key auto_increment,name varchar(50),salary bigint,deptid int);
插⼊实验数据：
mysql> insert into employee921 values(null,'zs',1000,1),(null,'ls',1100,1),(null,'ww',1100,1),(null,'zl',900,1) ,(null,'zl',1000,2), (null,'zl',900,2) ,(null,'zl',1000,2) , (null,'zl',1100,2);
编写sql语句：
（）select avg(salary) from employee921 group by deptid;
（）mysql> select employee921.id,employee921.name,employee921.salary,employee921.deptid tid from employee921 where salary > (select avg(salary) from employee921 where deptid = tid);
效率低的⼀个语句，仅供学习参考使⽤（在group by之后不能使⽤where，只能使⽤having，在group by之前可以使⽤where，即表⽰对过滤后的结果分组）：
mysql> select employee921.id,employee921.name,employee921.salary,employee921.dep
tid tid from employee921 where salary > (select avg(salary) from employee921 group by deptid having deptid = tid);（）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.avgsal3、存储过程与触发器必须讲，经常被⾯试到?
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),articleCount 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 row 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来⽣成。4数据库三范式是什么?
第⼀范式（1NF）：字段具有原⼦性,不可再分。所有关系型数据库系统都满⾜第⼀范式）
数据库表中的字段都是单⼀属性的，不可再分。例如，姓名字段，其中的姓和名必须作为⼀个整体，⽆法区分哪部分是姓，哪部分是名，如果要区分出姓和名，必须设计成两个独⽴的字段。
第⼆范式（2NF）：
第⼆范式（2NF）是在第⼀范式（1NF）的基础上建⽴起来的，即满⾜第⼆范式（2NF）必须先满⾜第⼀范式（1NF）。
要求数据库表中的每个实例或⾏必须可以被惟⼀地区分。通常需要为表加上⼀个列，以存储各个实例的惟⼀标识。这个惟⼀属性列被称为主关键字或主键。
第⼆范式（2NF）要求实体的属性完全依赖于主关键字。所谓完全依赖是指不能存在仅依赖主关键字⼀部分的属性，如果存在，那么这个属性和主关键字的这⼀部分应该分离出来形成⼀个新的实体，新实体与原实体之间是⼀对多的关系。为实现区分通常需要为表加上⼀个列，以存储各个实例的惟⼀标识。简⽽⾔之，第⼆范式就是⾮主属性⾮部分依赖于主关键字。
第三范式的要求如下：
满⾜第三范式（3NF）必须先满⾜第⼆范式（2NF）。简⽽⾔之，第三范式（3NF）要求⼀个数据库表中不包含已在其它表中已包含的⾮主关键字信息。
所以第三范式具有如下特征： 1，每⼀列只有⼀个值 2，每⼀⾏都能区分。 3，每⼀个表都不包含其他表已经包含的⾮主关键字信息。
例如，帖⼦表中只能出现发帖⼈的id，⽽不能出现发帖⼈的id，还同时出现发帖⼈姓名，否则，只要出现同⼀发帖⼈id的所有记录，它们中的姓名部分都必须严格保持⼀致，这就是数据冗余。5说出⼀些数据库优化⽅⾯的经验?
⽤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')6、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 studentvalues(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 < 4union
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 这个SQL在运⾏时先取出两个表的结果，再⽤排序空间进⾏排序删除重复的记录，最后返回结果集，如果表数据量⼤的话可能会导致⽤磁盘进⾏排序。 ⽽UNION ALL只是简单的将两个结果合并后就返回。这样，如果返回的两个结果集中有重复的数据，那么返回的结果集就会包含重复的数据了。 从效率上说，UNION ALL 要⽐UNION快很多，所以，如果可以确认合并的两个结果集中不包含重复的数据的话，那么就使⽤UNION ALL，7.分页语句
取出sql表中第31到40的记录（以⾃动增长ID为主键）sql server⽅案1：
select top 10 * from t where id not in (select top 30 id from t order by id ) orde by idsql server⽅案2：
select top 10 * from t where id in (select top 40 id from t order by id) order by id desc
mysql⽅案：select * from t order by id limit 30,10
oracle⽅案：select * from (select rownum r,* from t where r<=40) where r>30
--------------------待整理进去的内容-------------------------------------pageSize=20;pageNo = 5;
1.分页技术1（直接利⽤sql语句进⾏分页，效率最⾼和最推荐的）
mysql:sql = \"select * from articles limit \" + (pageNo-1)*pageSize + \oracle: sql = \"select * from \" +
\"(select rownum r,* from \" +
\"(select * from articles order by postime desc)\" + \"where rownum<= \" + pageNo*pageSize +\") tmp \" + \"where r>\" + (pageNo-1)*pageSize;
注释：第7⾏保证rownum的顺序是确定的，因为oracle的索引会造成rownum返回不同的值
简洋提⽰：没有order by时，rownum按顺序输出，⼀旦有了order by，rownum不按顺序输出了，这说明rownum是排序前的编号。如果对order by从句中的字段建⽴了索引，那么，rownum也是按顺序输出的，因为这时候⽣成原始的查询结果集时会参照索引表的顺序来构建。
sqlserver:sql = \"select top 10 * from id not id(select top \" + (pageNo-1)*pageSize + \"id from articles)\"
DataSource ds = new InitialContext().lookup(jndiurl);Connection cn = ds.getConnection();
//\"select * from user where id=?\" --->binary directivePreparedStatement pstmt = cn.prepareSatement(sql);ResultSet rs = pstmt.executeQuery()while(rs.next())
{
out.println(rs.getString(1));}
2.不可滚动的游标pageSize=20;pageNo = 5;cn = nullstmt = null;rs = null;try{
sqlserver:sql = \"select * from articles\";
DataSource ds = new InitialContext().lookup(jndiurl);Connection cn = ds.getConnection();
//\"select * from user where id=?\" --->binary directivePreparedStatement pstmt = cn.prepareSatement(sql);ResultSet rs = pstmt.executeQuery()for(int j=0;j<(pageNo-1)*pageSize;j++){
rs.next();} int i=0;
while(rs.next() && i<10){ i++;
out.println(rs.getString(1));}}cacth(){}finnaly{
if(rs!=null)try{rs.close();}catch(Exception e){} if(stm......... if(cn............}
3.可滚动的游标pageSize=20;pageNo = 5;cn = nullstmt = null;rs = null;try{
sqlserver:sql = \"select * from articles\";
DataSource ds = new InitialContext().lookup(jndiurl);Connection cn = ds.getConnection();
//\"select * from user where id=?\" --->binary directive
PreparedStatement pstmt = cn.prepareSatement(sql,ResultSet.TYPE_SCROLL_INSENSITIVE,...);//根据上⾯这⾏代码的异常SQLFeatureNotSupportedException，就可判断驱动是否⽀持可滚动游标
ResultSet rs = pstmt.executeQuery()rs.absolute((pageNo-1)*pageSize)int i=0;
while(rs.next() && i<10){ i++;
out.println(rs.getString(1));}}cacth(){}finnaly{
if(rs!=null)try{rs.close();}catch(Exception e){} if(stm......... if(cn............}
8.⽤⼀条SQL语句查询出每门课都⼤于80分的学⽣姓名
name kecheng fenshu 张三 语⽂ 81 张三 数学 75 李四 语⽂ 76 李四 数学 90 王五 100 王五 英语 90准备数据的sql代码：
create table score(id int primary key auto_increment,name varchar(20),subject varchar(20),score int);insert into score values
语⽂ 81 王五数学 (null,'张三','语⽂',81),(null,'张三','数学',75),(null,'李四','语⽂',76),(null,'李四','数学',90),(null,'王五','语⽂',81),(null,'王五','数学',100),(null,'王五 ','英语',90);
提⽰：当百思不得其解时，请理想思维，把⼩变成⼤做，把⼤变成⼩做，
答案： A: select distinct name from score where name not in (select distinct name from score where score<=80)
B:select distince name t1 from score where 80< all (select score from score where name=t1);9.所有部门之间的⽐赛组合
⼀个叫department的表，⾥⾯只有⼀个字段name,⼀共有4条纪录，分别是a,b,c,d,对应四个球对，现在四个球对进⾏⽐赛，⽤⼀条sql语句显⽰所有可能的⽐赛组合.
答：select a.name, b.name from team a, team b where a.name < b.name10.每个⽉份的发⽣额都⽐101科⽬多的科⽬
请⽤SQL语句实现：从TestDB数据表中查询出所有⽉份的发⽣额都⽐101科⽬相应⽉份的发⽣额⾼的科⽬。请注意：TestDB中有很多科⽬，都有1－12⽉份的发⽣额。 AccID：科⽬代码，Occmonth：发⽣额⽉份，DebitOccur：发⽣额。 数据库名：JcyAudit，数据集：Select* from TestDB准备数据的sql代码：drop table if exists TestDB;
create table TestDB(id int primary key auto_increment,AccID varchar(20), Occmonth date, DebitOccur bigint);insert into TestDB values(null,'101','1988-1-1',100),(null,'101','1988-2-1',110),(null,'101','1988-3-1',120),(null,'101','1988-4-1',100),(null,'101','1988-5-1',100),(null,'101','1988-6-1',100),(null,'101','1988-7-1',100),(null,'101','1988-8-1',100);
--复制上⾯的数据，故意把第⼀个⽉份的发⽣额数字改⼩⼀点insert into TestDB values(null,'102','1988-1-1',90),(null,'102','1988-2-1',110),(null,'102','1988-3-1',120),(null,'102','1988-4-1',100),(null,'102','1988-5-1',100),
(null,'102','1988-6-1',100),(null,'102','1988-7-1',100),(null,'102','1988-8-1',100);
--复制最上⾯的数据，故意把所有发⽣额数字改⼤⼀点insert into TestDB values(null,'103','1988-1-1',150),(null,'103','1988-2-1',160),(null,'103','1988-3-1',180),(null,'103','1988-4-1',120),(null,'103','1988-5-1',120),(null,'103','1988-6-1',120),(null,'103','1988-7-1',120),(null,'103','1988-8-1',120);
--复制最上⾯的数据，故意把所有发⽣额数字改⼤⼀点insert into TestDB values(null,'104','1988-1-1',130),(null,'104','1988-2-1',130),(null,'104','1988-3-1',140),(null,'104','1988-4-1',150),(null,'104','1988-5-1',160),(null,'104','1988-6-1',170),(null,'104','1988-7-1',180),(null,'104','1988-8-1',140);
--复制最上⾯的数据，故意把第⼆个⽉份的发⽣额数字改⼩⼀点insert into TestDB values(null,'105','1988-1-1',100),(null,'105','1988-2-1',80),(null,'105','1988-3-1',120),(null,'105','1988-4-1',100),(null,'105','1988-5-1',100),(null,'105','1988-6-1',100),(null,'105','1988-7-1',100),(null,'105','1988-8-1',100);
答案： select distinct AccID from TestDBwhere AccID not in
(select TestDB.AccIDfrom TestDB,
(select * from TestDB where AccID='101') as db101
where TestDB.Occmonth=db101.Occmonth and TestDB.DebitOccur<=db101.DebitOccur );
11.统计每年每⽉的信息
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 提⽰：这个与⼯资条⾮常类似，与学⽣的科⽬成绩也很相似。
准备sql语句：
drop table if exists sales;
create table sales(id int auto_increment primary key,year varchar(10), month varchar(10), amount float(2,1));insert into sales values(null,'1991','1',1.1),(null,'1991','2',1.2),(null,'1991','3',1.3),(null,'1991','4',1.4),(null,'1992','1',2.1),(null,'1992','2',2.2),(null,'1992','3',2.3),(null,'1992','4',2.4);答案⼀、 select sales.year ,
(select t.amount from sales t where t.month='1' and t.year= sales.year) '1',(select t.amount from sales t where t.month='1' and t.year= sales.year) '2',(select t.amount from sales t where t.month='1' and t.year= sales.year) '3',(select t.amount from sales t where t.month='1' and t.year= sales.year) as '4'from sales group by year;
12.显⽰⽂章标题，发帖⼈、最后回复时间表：id,title,postuser,postdate,parentid准备sql语句：
drop table if exists articles;
create table articles(id int auto_increment primary key,title varchar(50), postuser varchar(10), postdate datetime,parentid int referencesarticles(id));
insert into articles values
(null,'第⼀条','张三','1998-10-10 12:32:32',null),(null,'第⼆条','张三','1998-10-10 12:34:32',null),(null,'第⼀条回复1','李四','1998-10-10 12:35:32',1),(null,'第⼆条回复1','李四','1998-10-10 12:36:32',2),(null,'第⼀条回复2','王五','1998-10-10 12:37:32',1),(null,'第⼀条回复3','李四','1998-10-10 12:38:32',1),(null,'第⼆条回复2','李四','1998-10-10 12:39:32',2),(null,'第⼀条回复4','王五','1998-10-10 12:39:40',1);
答案：
select a.title,a.postuser,
(select max(postdate) from articles where parentid=a.id) replyfrom articles a where a.parentid is null;
注释：⼦查询可以⽤在选择列中，也可⽤于where的⽐较条件中，还可以⽤于from从句中。13.删除除了id号不同,其他都相同的学⽣冗余信息
2.学⽣表 如下: id号 学号 姓名 课程编号课程名称 分数 1 2005001 张三 0001 数学 69 2 2005002 李四 0001 数学 893 2005001 张三 0001 数学 69 A: delete from tablename where id号 not in(select min(id号) from tablename group by 学号,姓名,课程编号,课程名称,分数)实验：
create table student2(id int auto_increment primary key,code varchar(20),name varchar(20));insert into student2 values(null,'2005001','张三'),(null,'2005002','李四'),(null,'2005001','张三');
//如下语句，mysql报告错误，可能删除依赖后⾯统计语句，⽽删除⼜导致统计语句结果不⼀致。
delete from student2 where id not in(select min(id) from student2 group by name);//但是，如下语句没有问题：
select * from student2 where id not in(select min(id) from student2 group by name);
//于是，我想先把分组的结果做成虚表，然后从虚表中选出结果，最后再将结果作为删除的条件数据。delete from student2 where id not in(select mid from (select min(id) midfrom student2 group by name) as t);或者：
delete from student2 where id not in(select min(id) from (select * from student2) as t group by t.name);14.航空⽹的⼏个航班查询题：表结构如下：
flight{flightID,StartCityID ,endCityID,StartTime}city{cityID, CityName)实验环境：
create table city(cityID int auto_increment primary key,cityName varchar(20));create table flight (flightID int auto_increment primary key, StartCityID int references city(cityID), endCityID int references city(cityID), StartTime timestamp);
//航班本来应该没有⽇期部分才好，但是下⾯的题⽬当中涉及到了⽇期insert into city values(null,'北京'),(null,'上海'),(null,'⼴州');insert into flight values
(null,1,2,'9:37:23'),(null,1,3,'9:37:23'),(null,1,2,'10:37:23'),(null,2,3,'10:37:23');
1、查询起飞城市是北京的所有航班，按到达城市的名字排序
参与运算的列是我起码能够显⽰出来的那些列，但最终我不⼀定把它们显⽰出来。各个表组合出来的中间结果字段中必须包含所有运算的字段。
select * from flight f,city c
where f.endcityid = c.cityid and startcityid =
(select c1.cityid from city c1 where c1.cityname = \"北京\") order by c.cityname asc;
mysql> select flight.flightid,'北京' startcity, e.cityname from flight,city e where flight.endcityid=e.cityid and flight.startcityid=(select cityid from city where cityname='北京');
mysql> select flight.flightid,s.cityname,e.cityname from flight,city s,city e where flight.startcityid=s.cityid and s.cityname='北京' and flight.endCityId=e.cityID order by e.cityName desc;
2、查询北京到上海的所有航班纪录（起飞城市，到达城市，起飞时间，航班号）select c1.CityName,c2.CityName,f.StartTime,f.flightIDfrom city c1,city c2,flight fwhere f.StartCityID=c1.cityIDand f.endCityID=c2.cityIDand c1.cityName='北京'and c2.cityName='上海'
3、查询具体某⼀天（2005-5-8）的北京到上海的的航班次数select count(*) from
(select c1.CityName,c2.CityName,f.StartTime,f.flightIDfrom city c1,city c2,flight fwhere f.StartCityID=c1.cityIDand f.endCityID=c2.cityIDand c1.cityName='北京'and c2.cityName='上海'
and 查帮助获得的某个⽇期处理函数(startTime) like '2005-5-8%'
mysql中提取⽇期部分进⾏⽐较的⽰例代码如下：
select * from flight where date_format(starttime,'%Y-%m-%d')='1998-01-02'15.查出⽐经理薪⽔还⾼的员⼯信息：Drop table if not exists employees;
create table employees(id int primary key auto_increment,name varchar(50),salary int,managerid int references employees(id));
insert into employees values (null,' lhm',10000,null), (null,' zxx',15000,1),(null,'flx',9000,1),(null,'tg',10000,2),(null,'wzg',10000,3);
Wzg⼤于flx,lhm⼤于zxx
解题思路：
根据sql语句的查询特点，是逐⾏进⾏运算，不可能两⾏同时参与运算。
涉及了员⼯薪⽔和经理薪⽔，所有，⼀⾏记录要同时包含两个薪⽔，所有想到要把这个表⾃关联组合⼀下。
⾸先要组合出⼀个包含有各个员⼯及该员⼯的经理信息的长记录，譬如，左半部分是员⼯，右半部分是经理。⽽迪卡尔积会组合出很多垃圾信息，先去除这些垃圾信息。
select e.* from employees e,employees m where e.managerid=m.id and e.salary>m.salary;
16、求出⼩于45岁的各个⽼师所带的⼤于12岁的学⽣⼈数
数据库中有3个表 teacher 表，student表，tea_stu关系表。
teacher 表 teaID name age student 表 stuID name age teacher_student表 teaID stuID 要求⽤⼀条sql查询出这样的结果 1.显⽰的字段要有⽼师name, age 每个⽼师所带的学⽣⼈数 2 只列出⽼师age为40以下，学⽣age为12以上的记录预备知识：
1.sql语句是对每⼀条记录依次处理，条件为真则执⾏动作（select,insert,delete,update）
2.只要是迪卡尔积，就会产⽣“垃圾”信息，所以，只要迪卡尔积了，我们⾸先就要想到清除“垃圾”信息实验准备：
drop table if exists tea_stu; drop table if exists teacher; drop table if exists student;
create table teacher(teaID int primary key,name varchar(50),age int); create table student(stuID int primary key,name varchar(50),age int);
create table tea_stu(teaID int references teacher(teaID),stuID int references student(stuID));insert into teacher values(1,'zxx',45), (2,'lhm',25) , (3,'wzg',26) , (4,'tg',27);insert into student values(1,'wy',11), (2,'dh',25) , (3,'ysq',26) , (4,'mxc',27);insert into tea_stu values(1,1), (1,2), (1,3);insert into tea_stu values(2,2), (2,3), (2,4); insert into tea_stu values(3,3), (3,4), (3,1);insert into tea_stu values(4,4), (4,1), (4,2) , (4,3);
结果：2à3,3à2,4à3
解题思路：（真实⾯试答题时，也要写出每个分析步骤，如果纸张不够，就找别⼈要）1要会统计分组信息，统计信息放在中间表中：select teaid,count(*) from tea_stu group by teaid;
2接着其实应该是筛除掉⼩于12岁的学⽣，然后再进⾏统计，中间表必须与student关联才能得到12岁以下学⽣和把该学⽣记录从中间表中剔除，代码是：
select tea_stu.teaid,count(*) total from student,tea_stu
where student.stuid=tea_stu.stuid and student.age>12 group by tea_stu.teaid
3.接着把上⾯的结果做成虚表与teacher进⾏关联，并筛除⼤于45的⽼师select teacher.teaid,teacher.name,total from teacher ,(select tea_stu.teaid,count(*) total from student,tea_stu where student.stuid=tea_stu.stuid and student.age>12 group by tea_stu.teaid) as tea_stu2 where teacher.teaid=tea_stu2.teaid and teacher.age<45;17.求出发帖最多的⼈：
select authorid,count(*) total from articlesgroup by authoridhaving total=
(select max(total2) from (select count(*) total2 from articles group by authorid) as t);
select t.authorid,max(t.total) from
（select authorid,count(*) total from articles ）as t这条语句不⾏，因为max只有⼀列，不能与其他列混淆。
select authorid,count(*) total from articles
group by authorid having total=max(total)也不⾏。
18、⼀个⽤户表中有⼀个积分字段，假如数据库中有100多万个⽤户，若要在每年第⼀天凌晨将积分清零，你将考虑什么，你将想什么办法解决?
alter table drop column score;alter table add colunm score int;
可能会很快，但是需要试验，试验不能拿真实的环境来操⼑，并且要注意，
这样的操作时⽆法回滚的，在我的印象中，只有inert update delete等DML语句才能回滚，对于create table,drop table ,alter table等DDL语句是不能回滚。
解决⽅案⼀，update user set score=0;
解决⽅案⼆，假设上⾯的代码要执⾏好长时间，超出我们的容忍范围，那我就alter table user drop column score;alter table user add columnscore int。
下⾯代码实现每年的那个凌晨时刻进⾏清零。Runnable runnable = new Runnable(){ public void run(){ clearDb();
schedule(this,new Date(new Date().getYear()+1,0,0)); } };
schedule(runnable,
new Date(new Date().getYear()+1,0,1));
19、⼀个⽤户具有多个⾓⾊，请查询出该表中具有该⽤户的所有⾓⾊的其他⽤户。select count(*) as num,tb.idfrom tb,
(select role from tb where id=xxx) as t1where
tb.role = t1.role and tb.id != t1.idgroup by tb.idhaving
num = select count(role) from tb where id=xxx;20. xxx公司的sql⾯试
Table EMPLOYEES Structure:
EMPLOYEE_ID NUMBER Primary Key,FIRST_NAME VARCHAR2(25),LAST_NAME VARCHAR2(25),Salary number(8,2),HiredDate DATE,Departmentid number(2)TableDepartments Structure:
Departmentid number(2) Primary Key,DepartmentName VARCHAR2(25).
(2）基于上述EMPLOYEES表写出查询：写出雇⽤⽇期在今年的，或者⼯资在[1000,2000]之间的，或者员⼯姓名（last_name）以’Obama’打头的所有员⼯，列出这些员⼯的全部个⼈信息。（4分）select * from employees
where Year(hiredDate) = Year(date()) or (salary between 1000 and 200)
or left(last_name,3)='abc';
(3) 基于上述EMPLOYEES表写出查询：查出部门平均⼯资⼤于1800元的部门的所有员⼯，列出这些员⼯的全部个⼈信息。（4分）mysql> select id,name,salary,deptid did from employee1 where (select avg(salary) from employee1 where deptid = did) > 1800;
(4) 基于上述EMPLOYEES表写出查询：查出个⼈⼯资⾼于其所在部门平均⼯资的员⼯，列出这些员⼯的全部个⼈信息及该员⼯⼯资⾼出部门平均⼯资百分⽐。（5分）
select employee1.*,(employee1.salary-t.avgSalary)*100/employee1.salaryfrom employee1,
(select deptid,avg(salary) avgSalary from employee1 group by deptid) as twhere employee1.deptid = t.deptid and employee1.salary>t.avgSalary;21、注册Jdbc驱动程序的三种⽅式22、⽤JDBC如何调⽤存储过程代码如下：
package com.huawei.interview.lym;
import java.sql.CallableStatement;import java.sql.Connection;import java.sql.DriverManager;import java.sql.SQLException;import java.sql.Types;
public class JdbcTest { /**
* @param args */
public static void main(String[] args) { // TODO Auto-generated method stub Connection cn = null;
CallableStatement cstmt = null; try {
//这⾥最好不要这么⼲，因为驱动名写死在程序中了 Class.forName(\"com.mysql.jdbc.Driver\");
//实际项⽬中，这⾥应⽤DataSource数据，如果⽤框架，
//这个数据源不需要我们编码创建，我们只需Datasource ds = context.lookup() //cn = ds.getConnection();
cn = DriverManager.getConnection(\"jdbc:mysql:///test\
cstmt = cn.prepareCall(\"{call insert_Student(?,?,?)}\"); cstmt.registerOutParameter(3,Types.INTEGER); cstmt.setString(1, \"wangwu\"); cstmt.setInt(2, 25); cstmt.execute();
//get第⼏个，不同的数据库不⼀样，建议不写 System.out.println(cstmt.getString(3)); } catch (Exception e) {
// TODO Auto-generated catch block e.printStackTrace(); } finally {
/*try{cstmt.close();}catch(Exception e){} try{cn.close();}catch(Exception e){}*/ try {
if(cstmt != null) cstmt.close(); if(cn != null) cn.close();
} catch (SQLException e) {
// TODO Auto-generated catch block e.printStackTrace(); } } }
23、JDBC中的PreparedStatement相⽐Statement的好处
答：⼀个sql命令发给服务器去执⾏的步骤为：语法检查，语义分析，编译成内部指令，缓存指令，执⾏指令等过程。select * from student where id =3----缓存--àxxxxx⼆进制命令select * from student where id =3----直接取-àxxxxx⼆进制命令select * from student where id =4--- -à会怎么⼲？
如果当初是select * from student where id =?--- -à⼜会怎么⼲？ 上⾯说的是性能提⾼可以防⽌sql注⼊。
24. 写⼀个⽤jdbc连接并访问oracle数据的程序代码25、Class.forName的作⽤?为什么要⽤?
答：按参数中指定的字符串形式的类名去搜索并加载相应的类，如果该类字节码已经被加载过，则返回代表该字节码的Class实例对象，否则，按类加载器的委托机制去搜索和加载该类，如果所有的类加载器都⽆法加载到该类，则抛出ClassNotFoundException。加载完这个
Class字节码后，接着就可以使⽤Class字节码的newInstance⽅法去创建该类的实例对象了。
有时候，我们程序中所有使⽤的具体类名在设计时（即开发时）⽆法确定，只有程序运⾏时才能确定，这时候就需要使⽤Class.forName去动态加载该类，这个类名通常是在配置⽂件中配置的，例如，spring的ioc中每次依赖注⼊的具体类就是这样配置的，jdbc的驱动类名通常也是通过配置⽂件来配置的，以便在产品交付使⽤后不⽤修改源程序就可以更换驱动类名。26、⼤数据量下的分页解决⽅法。
答：最好的办法是利⽤sql语句进⾏分页，这样每次查询出的结果集中就只包含某页的数据内容。再sql语句⽆法实现分页的情况下，可以考虑对⼤的结果集通过游标定位⽅式来获取某页的数据。
sql语句分页，不同的数据库下的分页⽅案各不⼀样，下⾯是主流的三种数据库的分页sql：sql server: String sql =
\"select top \" + pageSize + \" * from students where id not in\" +
\"(select top \" + pageSize * (pageNumber-1) + \" id from students order by id)\" +
\"order by id\"; mysql:
String sql =
\"select * from students order by id limit \" + pageSize*(pageNumber-1) + \ oracle:
String sql =
27、说出数据连接池的⼯作机制是什么?
J2EE服务器启动时会建⽴⼀定数量的池连接，并⼀直维持不少于此数⽬的池连接。客户端程序需要连接时，池驱动程序会返回⼀个未使⽤的池连接并将其表记为忙。如果当前没有空闲连接，池驱动程序就新建⼀定数量的连接，新建连接的数量有配置参数决定。当使⽤的池连接调⽤完成后，池驱动程序将此连接表记为空闲，其他调⽤就可以使⽤这个连接。
实现⽅式，返回的Connection是原始Connection的代理，代理Connection的close⽅法不是真正关连接，⽽是把它代理的Connection对象还回到连接池中。
28、为什么要⽤ ORM? 和 JDBC 有何不⼀样?
orm是⼀种思想，就是把object转变成数据库中的记录，或者把数据库中的记录转变成objecdt，我们可以⽤jdbc来实现这种思想，其实，如果我们的项⽬是严格按照oop⽅式编写的话，我们的jdbc程序不管是有意还是⽆意，就已经在实现orm的⼯作了。
现在有许多orm⼯具，它们底层调⽤jdbc来实现了orm⼯作，我们直接使⽤这些⼯具，就省去了直接使⽤jdbc的繁琐细节，提⾼了开发效率，现在⽤的较多的orm⼯具是hibernate。也听说⼀些其他orm⼯具，如toplink,ojb等。
因篇幅问题不能全部显示，请点此查看更多更全内容