img

songdeling

Java / 微服务

宋德凌

个人站

欢迎来到我的个人站~

Java面试题：数据库

| 阅读：次

Java面试题：数据库

1.delete与truncate区别

  • delete语句执行删除的过程是每次从表中删除一行,并且同时将该行的删除操作作为事务记录在日志中保存以便进行回滚操作,不清空auto_increment记录数
  • truncate则直接将表删除并重新建表,不会把单独的删除操作记录记入日志保存,删除行是不能恢复的,auto_increment将重置为0,效率比delete高

2.储存过程

  1. 概念

    存储过程是一组为了完成特定功能的SQL语句集,经编译后存储在数据库中,用户通过指定存储过程的名字并给定参数(如果该存储过程带有参数)来调用执行它

  2. 如何创建存储过程

    “pr_add”是个简单的MySQL存储过程,这个MySQL存储过程有两个int类型a,b的参数,返回和.

    drop procedure if exists pr_add;
create procedure pr_add (a int , b int) begin declare c int;
if a is null then set a = 0;
end if;
if b is null then set b = 0;
end if;
set c = a + b;
select c as sum;

3.索引

  1. 概念

    索引是对数据库中一或多个列值的排序,帮助数据库高效获取数据的数据结构.假如我们用类比的方法,数据库中的索引就相当于书籍中的目录一样,当我们想找到书中某个知识点,我们就可以直接去目录中找而不是在书中每页找.

  2. 分类

    普通索引

    唯一索引

    主键索引

    全文索引

  3. 优缺点

    1. 优点:

      加快检索速度

      唯一索引确保每行数据的唯一性

      在使用索引的过程可以优化隐藏器,提高系统性能

    2. 缺点:

      在对数据库修改时,要修改索引导致时间变长

      增删改维护速度下降

      占用物理和数据空间

  4. 如何创建索引

    /*1.添加PRIMARY KEY（主键索引）*/
ALTER TABLE `table_name` ADD PRIMARY KEY ( `column` );
/*添加UNIQUE(唯一索引)*/
ALTER TABLE `table_name` ADD UNIQUE ( `column` )
/*添加INDEX(普通索引)*/
ALTER TABLE `table_name` ADD INDEX index_name ( `column` )
/*添加FULLTEXT(全文索引)*/
ALTER TABLE `table_name` ADD FULLTEXT ( `column`)
/*添加多列索引(扩展)*/
ALTER TABLE `table_name` ADD INDEX index_name ( `column1`, `column2`,`column3` )

4.事务

  1. 应用场景:存在并发数据访问时才需要事务

  2. ACID四大特性

    1. 原子性(Atomicity):

      整个事务中的所有操作,要么全部完成,要么全部不完成,不可能停滞在中间的某个环节.任何一项操作的失败都会导致整个事务的失败

    2. 一致性(Correspondence):

      在事务开始之前和事务结束之后,数据库的完整性约束没有被破坏

    3. 隔离性(Isolation):

      并发执行的事务彼此无法看到对方的中间状态

    4. 持久性(Durability):

      在事务完成以后,该事务所对数据库所做的更改便持久的保存在数据库之中,并不会被回滚

  3. 事务中的问题与隔离级别

    • 问题:
      • 脏读:一个事务读取到了另一个事务未提交的数据
      • 不可重复读:一个事务中两次查询的数据不一致 –> 一个事务读到了另一个事务已提交的数据(update操作)
      • 虚读(幻读):一个事务中两次查询的数据不一致 –> 一个事务读到了另一个事务已经提交的数据(insert操作)
    • 隔离级别(安全从低到高,性能从高到低):
      • 读未提交:也叫脏读,是事务可以读取其他事务未提交的数据.(未解决任何问题)
      • 读已提交:在事务未提交之前所做的修改,其他事务是不可见的.(解决脏读问题)(Oracle,SqlServer默认事务隔离级别)
      • 可重复读:保证同一事务中的多次相同的查询的结果是一致的.(解决脏读,不可重复读问题)(MySQL默认事务隔离级别)
      • 可串行化:保证读取的范围内没有新的数据插入,比如事务第一次查询得到某个范围的数据,第二次查询也同样得到了相同范围的数据,中间没有新的数据插入到该范围中(解决脏读,不可重复读,虚读(幻读)的问题).

  4. 如何进行事务管理

    Connection提供了事务处理的方法,通过调用setAutoCommit(false)可以设置手动提交事务;当事务完成后用commit()显式提交事务;如果在事务处理过程中发生异常则通过rollback()进行事务回滚.除此之外,从JDBC3.0中还引入了Savepoint(保存点)的概念,允许通过代码设置保存点并让事务回滚到指定的保存点.

5.Oracle和MySQL的分页语句

  1. Oracle是通过rownum分页的

    /*查询1-10*/
select * from 
	(select rownum r , a from tableA where rownum <= 10)
where r > 0

  2. MySQL是通过limit关键字分页

    /*查询1-10*/
select * from tableA limit 0,10

6.Oracle经常用到的函数

Length() 	--长度
lower()  	--小写
upper()		--大写
to_date()	--转换日期
to_char()	--转化字符
Ltrim()		--去除左边空格
rtrim()		--去除右边空格
substr()	--取字串
add_month()	--增加或者减掉月份
to_number()	--转变为数字

7.Oracle高水位线

  1. 什么是高水位线（High Water Mark)?

    所有的oracle段（segments，在此，为了理解方便，建议把segment作为表的一个同义词）都有一个在段内容纳数据的上限，我们把这个上限称”为high water mark”或HM。这个HM是一个标记，用来说明已经有多少没有使用的数据块分配给这个segment。HWM通常增长的幅度为一次5个数据块，原则上HIRM只会增大，不会缩小，即使将表中的数据全部删除，HWM还是为原值，由于这个特点，使HRM很象一个水库的历史最高水位，这也就是HM的原始含义，当然不能说一个水库没水了，就说该水库的历史最高水位为0。但是如果我们在表上使用了truncate命令，则该表的HWM会被重新置为0。

  2. Oracle表段中的高水位线HWM

    在0racle数据的存储中，可以把存储空间想象为一个水库，数据想象为水库中的水。水库中的水的位置有一条线叫做水位线，在0racle中，这条线被称为高水位线（High-warter mark,HWM)。在数据库表刚建立的时候，由于没有任何数据，所以这个时候水位线是空的，也就是说HRM为最低值。当插入了数据以后，高水位线就会上涨，但是这里也有一个特性，就是如果你采用delete语句删除数据的话，数据虽然被删除了，但是高水位线却没有降低，还是你刚才删除数据以前那么高的水位。也就是说，这条高水位线在日常的增删操作中只会上涨，不会下跌。

8.数据库语句优化

1.对查询进行优化,应尽量避免全表扫描,首先应考虑在where及order by涉及的列上建立索引

2.应尽量避免在where子句中对字段进行null值判断,否则将导致引擎放弃使用索引而进行全表扫描

3.应尽量避免在where子句中使用!=或<>操作符,否则将导致引擎放弃使用索引而进行全表扫描

4.应尽量避免在where子句中使用or来连接条件,否则将导致引擎放弃使用索引而进行全表扫描

5.in和not in也要慎用,否则会导致全表扫描

6.索引并不是越多越好,索引固然可以提高相应的select的效率,但同时也降低了insert及update的效率,因为insert或update时有可能会重建索引,所以怎样建索引需要慎重考虑,是具体情况而定.一个表的索引数最好不要超过6个,若太多则应考虑一些不常使用到的列上建的索引是否有必要.

7.查询到的结果不要用*来查询所有字段,要明确指明结果字段

8.根据查询条件，建立索引，如果查询条件不止一个时，使用组合索引

9.在查询条件表达式的左侧尽量不要使用函数，否则素引失效

10.如果有like话，尽量避免%xxX%两侧都有%的条件，单侧%可以使用索引， 多侧不可以

11.建立索引时字段不能有null值

9.数据库优化

  1. MySQL数据库优化有哪些?

    1. EXPLAIN你的SELECT查询
    2. 当只要一行数据时使用LIMIT 1
    3. 使用ENUM而不是VARCHAR
    4. 固定长度的表会更快
    5. 分库分表

  2. Oracle数据库优化有哪些?

    1. 调整数据结构的设计
    2. 调整应用程序结构设计
    3. 调整数据库SQL语句
    4. 调整服务器内存分配
    5. 调整硬盘I/O
    6. 调整操作系统参数

理论知识结束

现有学生、课程、教师、成绩表，完成下面问题：

MySQL表和数据

-- Student(sid,Sname,Sage,Ssex) 学生表 
CREATE TABLE student (
  sid varchar(10) NOT NULL,
  sName varchar(20) DEFAULT NULL,
  sAge datetime DEFAULT '1980-10-12 23:12:36',
  sSex varchar(10) DEFAULT NULL,
  PRIMARY KEY (sid)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- Course(cid,Cname,tid) 课程表 
CREATE TABLE course (
  cid varchar(10) NOT NULL,
  cName varchar(10) DEFAULT NULL,
  tid int(20) DEFAULT NULL,
  PRIMARY KEY (cid)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--SC(sid,cid,score) 成绩表 
CREATE TABLE sc (
  sid varchar(10) DEFAULT NULL,
  cid varchar(10) DEFAULT NULL,
  score int(10) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- Teacher(tid,Tname) 教师表 
CREATE TABLE taacher (
  tid int(10) DEFAULT NULL,
  tName varchar(10) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- 数据：
insert  into taacher(tid,tName) values (1,'李老师'),(2,'何以琛'),(3,'叶平');

insert  into student(sid,sName,sAge,sSex) values 
('1001','张三丰','1980-10-12 23:12:36','男'),
('1002','张无极','1995-10-12 23:12:36','男'),
('1003','李奎','1992-10-12 23:12:36','女'),
('1004','李元宝','1980-10-12 23:12:36','女'),
('1005','李世明','1981-10-12 23:12:36','男'),
('1006','赵六','1986-10-12 23:12:36','男'),
('1007','田七','1981-10-12 23:12:36','女');

insert  into sc(sid,cid,score) values 
('1001','001',80),('1001','002',60),('1001','003',70),('1002','001',85),('1002','002',70),('1003','004',90),('1003','001',90),('1003','002',99),('1004','002',65),('1004','003',50),('1005','005',80),('1005','004',70),('1003','003',10),('1003','005',10);

insert  into course(cid,cName,tid) values 
('001','企业管理',3),('002','马克思',3),('003','UML',2),
('004','数据库',1),('005','英语',1);

Oracle表和数据

CREATE TABLE student (
  sid varchar2(10) NOT NULL,
  sName varchar2(20) DEFAULT NULL,
  sAge date ,
  sSex varchar2(10) DEFAULT NULL,
  PRIMARY KEY (sid)
) 

CREATE TABLE course (
  cid varchar2(10) NOT NULL,
  cName varchar2(10) DEFAULT NULL,
  tid number(20) DEFAULT NULL,
  PRIMARY KEY (cid)
) 

CREATE TABLE sc (
  sid varchar2(10) DEFAULT NULL,
  cid varchar2(10) DEFAULT NULL,
  score number(10) DEFAULT NULL
)


CREATE TABLE teacher (
  tid number(10) DEFAULT NULL,
  tName varchar2(10) DEFAULT NULL
)

insert  into course(cid,cName,tid) values ('001','企业管理',3);
insert  into course(cid,cName,tid) values ('002','马克思',3);
insert  into course(cid,cName,tid) values ('004','数据库',1);
insert  into course(cid,cName,tid) values ('005','英语',1);

insert  into sc(sid,cid,score) values ('1001','001',80);
insert  into sc(sid,cid,score) values ('1001','002',60);
insert  into sc(sid,cid,score) values ('1001','003',70);
insert  into sc(sid,cid,score) values ('1002','001',85);
insert  into sc(sid,cid,score) values ('1002','002',70);
insert  into sc(sid,cid,score) values ('1003','004',90);
insert  into sc(sid,cid,score) values ('1003','001',90);
insert  into sc(sid,cid,score) values ('1003','002',99);
insert  into sc(sid,cid,score) values ('1004','002',65);
insert  into sc(sid,cid,score) values ('1004','003',50);
insert  into sc(sid,cid,score) values ('1005','005',80);
insert  into sc(sid,cid,score) values ('1005','004',70);
insert  into sc(sid,cid,score) values ('1003','003',10);
insert  into sc(sid,cid,score) values ('1003','005',10);


insert  into student(sid,sName,sAge,sSex) values ('1001','张三丰',to_date('1980-10-12 23:12:36','YYYY-MM-DD HH24:MI:SS'),'男');
insert  into student(sid,sName,sAge,sSex) values ('1002','张无极',to_date('1995-10-12 23:12:36','YYYY-MM-DD HH24:MI:SS'),'男');
insert  into student(sid,sName,sAge,sSex) values ('1003','李奎',to_date('1992-10-12 23:12:36','YYYY-MM-DD HH24:MI:SS'),'女');
insert  into student(sid,sName,sAge,sSex) values ('1004','李元宝',to_date('1980-10-12 23:12:36','YYYY-MM-DD HH24:MI:SS'),'女');
insert  into student(sid,sName,sAge,sSex) values ('1005','李世明',to_date('1981-10-12 23:12:36','YYYY-MM-DD HH24:MI:SS'),'男');
insert  into student(sid,sName,sAge,sSex) values ('1006','赵六',to_date('1986-10-12 23:12:36','YYYY-MM-DD HH24:MI:SS'),'男');
insert  into student(sid,sName,sAge,sSex) values ('1007','田七',to_date('1981-10-12 23:12:36','YYYY-MM-DD HH24:MI:SS'),'女');

insert  into teacher(tid,tName) values (1,'李老师');
insert  into teacher(tid,tName) values (2,'何以琛');
insert  into teacher(tid,tName) values (3,'叶平');

问题

-- 1.查询“001”课程比“002”课程成绩高的所有学生的学号; 
SELECT a.sid from 
(SELECT sid,score from sc where cid = 001) a ,
(SELECT sid,score from sc where cid = 002) b 
where a.score>b.score and a.sid = b.sid
-- 2、查询平均成绩大于60分的同学的学号和平均成绩; 
select * from 
(SELECT sid,AVG(score) avg_score FROM sc GROUP BY sid ) a
where a.avg_score>60
-- 3、查询所有同学的学号、姓名、选课数、总成绩; 
select student.sid,student.sName,count(sc.cid),sum(sc.score) from
student left outer join	sc on student.sid = sc.sid
group by sc.sid,sname
-- 4、查询姓“李”的老师的个数; 
SELECT COUNT(tid) FROM teacher WHERE tName LIKE '李%'
-- 5、查询没学过“叶平”老师课的同学的学号、姓名; 
SELECT sid,sname FROM student WHERE sid NOT IN (
SELECT sid FROM teacher,course,sc 
WHERE teacher.tid = course.tid AND course.cid = sc.cid AND teacher.tName='叶平'
GROUP BY sid)
-- 6、查询学过“001”并且也学过编号“002”课程的同学的学号、姓名; 
-- 法一:
select a.* from(
select student.sid,student.sname from student,sc 
where student.sid=sc.sid and sc.cid=001 
) as a cross join(
select student.sid,student.sname from student,sc 
where student.sid=sc.sid and sc.cid=002 
) as b where a.sid = b.sid
-- 法二:

-- 法三:

-- 7、查询学过“叶平”老师所教的所有课的同学的学号、姓名; 
select student.sid ,student.sname from student ,sc 
where student.sid = sc.sid and sc.cid in
(select cid from course,teacher where teacher.tid = course.tid and teacher.tname = '叶平')
group by student.sid
-- 8、查询课程编号“002”的成绩比课程编号“001”课程低的所有同学的学号、姓名; 
select s.sid,s.sname from student as s,
(SELECT sid,score from sc where cid=001) as a,
(SELECT sid,score from sc where cid=002) as b
where a.sid = b.sid and a.sid = s.sid and a.score>b.score
-- 9、查询所有课程成绩小于60分的同学的学号、姓名; 
SELECT student.sid,student.sname from student where student.sid in 
(select sc.sid from sc where sc.score<60 group by sc.sid)
-- 10、查询没有学全所有课的同学的学号、姓名; 

-- 11、查询至少有一门课与学号为“1001”的同学所学相同的同学的学号和姓名; 

-- 13、把“SC”表中“叶平”老师教的课的成绩都更改为此课程的平均成绩; 

-- 14、查询和“1002”号的同学学习的课程完全相同的其他同学学号和姓名; 

-- 15、删除学习“叶平”老师课的SC表记录; 

-- 17、按平均成绩从高到低显示所有学生的“数据库”、“企业管理”、“英语”三门的课程成绩，
-- 按如下形式显示： 学生ID,,数据库,企业管理,英语,有效课程数,有效平均分 

-- 18、查询各科成绩最高和最低的分：以如下形式显示：课程ID，最高分，最低分 

-- 19、按各科平均成绩从低到高和及格率的百分数从高到低排序 

-- 20、查询如下课程平均成绩和及格率的百分数(用"1行"显示): 
-- 企业管理（001），马克思（002），OO&UML （003），数据库（004） 

-- 21、查询不同老师所教不同课程平均分从高到低显示 

-- 22、统计列印各科成绩,各分数段人数:课程ID,课程名称,[100-85],[85-70],[70-60],[ <60] 

-- 23、查询每门课程被选修的学生数 

-- 24、查询出只选修了一门课程的全部学生的学号和姓名 

-- 25、查询男生、女生人数 

-- 26、查询姓“张”的学生名单 

-- 27、查询同名同性学生名单，并统计同名人数 

-- 28、1981年出生的学生名单(注：Student表中Sage列的类型是datetime) 

-- 29、查询每门课程的平均成绩，结果按平均成绩升序排列，平均成绩相同时，按课程号降序排列 

-- 30、查询平均成绩大于85的所有学生的学号、姓名和平均成绩 

-- 31、查询课程名称为“数据库”，且分数低于60的学生姓名和分数 

-- 32、查询所有学生的选课情况; 

-- 33、查询任何一门课程成绩在70分以上的姓名、课程名称和分数; 

-- 34、查询不及格的课程，并按课程号从大到小排列 

-- 35、查询课程编号为003且课程成绩在80分以上的学生的学号和姓名; 

-- 36、求选了课程的学生人数 

-- 37、查询选修“叶平”老师所授课程的学生中，成绩最高的学生姓名及其成绩 

-- 38、查询各个课程及相应的选修人数 

-- 39、查询不同课程成绩相同的学生的学号、课程号、学生成绩 

-- 40、查询每门功课成绩最好的前两名 

-- 41、统计每门课程的学生选修人数（超过10人的课程才统计）。
-- 要求输出课程号和选修人数，查询结果按人数降序排列，若人数相同，按课程号升序排列 

-- 42、检索至少选修两门课程的学生学号 

-- 43、查询全部学生都选修的课程的课程号和课程名 

-- 44、查询没学过“叶平”老师讲授的任一门课程的学生姓名 

-- 45、查询两门以上不及格课程的同学的学号及其平均成绩 

-- 46、检索“004”课程分数小于60，按分数降序排列的同学学号 

-- 47、删除“1002”同学的“001”课程的成绩

持续更新中…

转载请注明原地址，宋德凌的博客：http://CoderOfSong.github.io 谢谢！

打赏一个呗

取消

感谢您的支持，我会继续努力的!

扫码支持
扫码支持
扫码打赏，你说多少就多少

打开支付宝扫一扫，即可进行扫码打赏哦