文档介绍：JAVA必背面试题和项目面试通关要点
一数据库
、修改(SQL查询包含筛选查询、聚合查询和链接查询和优化问题,手写SQL语句,例如四个球队比赛,用SQL显示所有比赛组合;举例2:选择重复项,然后去掉重复项;) 数据库里的密码如何加密(md5);
(1)数据库的密码加密:单向加密,insert into users (username,password) values (‘mike’,MD5(‘guessme’));
(2)双向加密:insert into users (username ,password) values (‘mike’,ENCODE(‘guessme’,’abanaafn’));
(3)SQL筛选查询:select * from user where user_id in (select user_id from user group by user_id having count(user_id)>1) order by user_id desc;
(4)SQL聚合查询:select user_id from user group by user_id;
(5)SQL衔接查询: , from student s inner join mark m on =;左外连接:select , from student s left join mark m on =;右外连接:select , from student s right join mark m on =;全外连接:select , from student s full join mark m on =;
(6)球队比赛组合:select count(*) from department as a ,department as b <>;
(7)SQL选择重复语句:select id ,name from user where id in (select id from user group by id having count(1)>2);去除重复语句:select id from user group by id having count(*)>1;
(8)数据库优化:prepareStatement比Statement性能高,:语法检查,语义定义,,如果程序能保证数据的完整性,,特别是列名和表名.
2. 如何实现数据库分页功能? SQL语句复杂的关联表查询,累计求和。
在不使用框架情况下,如何连接数据库?
数据库连接完之后,需要关闭的两个接口是什么?
关闭数据库过程中最容易抛出的异常是什么?
(1)分页:”select * from user limit “+(pageNo-1)*pageSize+”,”+pageSize;
(2)求和:select * from user1 union select * from user2;
(3)手动连接数据库:=null;PreparedStatement pst=null;Result rs=null;try{(driverClassName);cn=(url,username,password);.prepareStatement(“sql”);(1,studentName);Result rs=();while(()){();}catch(Exception e){();}finally{if(rs!=null){();}if(pst!=null){()}!=null){();}}}
(4)Result prepareStatement 结果集和sql传输
(5)Exception
3. Oracle索引的优缺点、视图和存储过程、连接符是什么?默认端口是什么?MySQL什么是左链接、右链接和内链接?数据库中查询左链接、右链接和内链接结果是什么?
数据库的存储原理,讲一讲?
(1)端口: