面试积累数据库篇（二）

作者: 大炮对着虫子 | 来源:发表于2017-11-20 10:22 被阅读17次

面试积累数据库篇（二）
面试积累java篇（二）
面试积累数据库篇（一）
面试积累之数据库篇（六）
面试积累网络篇
面试积累 java篇（一）
Java 面试八股文之数据库篇（二）
好句积累篇﹤二﹥
大学生求职面试篇：面试准备之了解公司
面试篇-数据库

1、删除除了id号不同,其他都相同的学生冗余信息
学生表如下:
id号姓名分数
1 张三 69
2 李四 89
3 张三 69

创建表
create table tablename(id int auto_increment primary key,name varchar(20),code varchar(20));

insert into tablename values(null,"菜菜","100");
insert into tablename values(null,"嘿嘿","200");
insert into tablename values(null,"菜菜","100");

delete from tablename where id not in(select min(id) myid from 
tablename group by name,code );
在mysql中，不能先select出同一表中的某些值，再update这个表。

oracle就不会出现这样的情况，解决方法：
delete from tablename where id not in(select myid from (select min(id) 
myid from tablename group by name,code ) as t);

2、用一条SQL语句查询出每门课都大于80分的学生姓名

张三语文 81
张三数学 75
李四语文 76
李四数学 90
王五语文 81
王五数学 100
王五英语 90

创建表

create table student (id int auto_increment   primary key,
name varchar(20),
kemu varchar(20),
code varchar(20));

插入数据

insert into student values(null,"张三","语文","98");
insert into student values(null,"张三","数学","75");
insert into student values(null,"小李","语文","98");
insert into student values(null,"小李","数学","97");

查询：

select distinct name from student where name not in(
select distinct name from student where code <80);

3、.每个月份的发生额都比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 auto_increment primary key,
AccID varchar(20),
Occmonth varchar(20),
DebitOccur bigint
);

准备数据：


insert into TestDB values(null,"101","1995-1-1",110);
insert into TestDB values(null,"101","1995-2-1",120);
insert into TestDB values(null,"101","1995-3-1",130);
insert into TestDB values(null,"101","1995-4-1",140);
insert into TestDB values(null,"101","1995-5-1",150);
insert into TestDB values(null,"101","1995-6-1",160);
insert into TestDB values(null,"101","1995-7-1",170);
insert into TestDB values(null,"101","1995-8-1",180);
insert into TestDB values(null,"101","1995-9-1",190);
insert into TestDB values(null,"101","1995-10-1",200);
insert into TestDB values(null,"101","1995-11-1",210);
insert into TestDB values(null,"101","1995-12-1",220);

insert into TestDB values(null,"102","1995-1-1",120);
insert into TestDB values(null,"102","1995-2-1",130);
insert into TestDB values(null,"102","1995-3-1",140);
insert into TestDB values(null,"102","1995-4-1",150);
insert into TestDB values(null,"102","1995-5-1",160);
insert into TestDB values(null,"102","1995-6-1",170);
insert into TestDB values(null,"102","1995-7-1",180);
insert into TestDB values(null,"102","1995-8-1",190);
insert into TestDB values(null,"102","1995-9-1",220);
insert into TestDB values(null,"102","1995-10-1",210);
insert into TestDB values(null,"102","1995-11-1",220);
insert into TestDB values(null,"102","1995-12-1",230);


insert into TestDB values(null,"103","1995-1-1",120);
insert into TestDB values(null,"103","1995-2-1",130);
insert into TestDB values(null,"103","1995-3-1",140);
insert into TestDB values(null,"103","1995-4-1",150);
insert into TestDB values(null,"103","1995-5-1",160);
insert into TestDB values(null,"103","1995-6-1",170);
insert into TestDB values(null,"103","1995-7-1",100);
insert into TestDB values(null,"103","1995-8-1",190);
insert into TestDB values(null,"103","1995-9-1",220);
insert into TestDB values(null,"103","1995-10-1",210);
insert into TestDB values(null,"103","1995-11-1",220);
insert into TestDB values(null,"103","1995-12-1",230);

查询数据：


(select * from TestDB where AccID="101") as db101
select distinct AccID from TestDB where AccID not in(

select t1.AccID from TestDB as t1 inner join 
(select * from TestDB where AccID="101") as db101
on t1.Occmonth=db101.Occmonth and t1.DebitOccur<=db101.DebitOccur
);

sql语言包括：
数据定义DDL:(Create Alter Drop table) Create /Drop Index等
数据操作：select delete update insert
数据控制:grank,revoke

什么是存储过程，优点？

存储过程是一些预编译的SQL语句。
存储过程是一组记录集，由一些T-SQL语句组成的代码块，这些代码块实现了一些特定的功能（比如单表或多表的增删查改），然后给代码块取个名字，在需要用的时候调用它。

存储过程是一个预编译的代码块，执行效率比较高
一个存储过程代替了大量的SQL语句，可以降低了网络的通信量
一定程度上确保数据安全。

索引是什么，有什么作用和优缺点？

索引是用于提高数据表数据访问速度的数据库对象，

事务：

事务是并发控制的基本单位，事务操作中要么都执行，要么都不执行，它是不可分割的工作单位。比如银行转账工作，一个账号扣款另一个账号增款，这两个操作都要执行，要么都不执行，所以把他们看作一个事务来处理。
事务有4个特性（ACID）：
原子性：Atomic ,事务中的操作被看成一个最小的逻辑单位，这些操作要么全部都成功，要么都失败。
一致性(Consistemcy)：事务操作失败的话，应该会滚到最初状态
隔离性(Isolation)：多个事务之间是互不影响，多个用户对数据并发访问，不影响数据的正确性和完整性
持久性(Durability)：事务处理后，事务的处理结果存储在数据库，影响是永久的