金沙棋牌app手机下载

当前位置:金沙棋牌 > 金沙棋牌app手机下载 > mysql经典面试题,SqlServer四个排名函数

mysql经典面试题,SqlServer四个排名函数

来源:http://www.logblo.com 作者:金沙棋牌 时间:2019-11-09 19:48

select * from (select ROW_NUMBER() over(order by m.CreDate)as number,m.Account,m.Name,m.Sex, m.Tel
from Members m inner join Users u on m.CoachKey=u.[Key] or m.SalesKey=u.[Key] and u.DataState=1 where u.[Key]='6FCDD895-AEDB-464F-93E8-A79B013A23B5')as number
where number>=10*(1-1)+1 and number<=1*10

本文为原创,如需转载,请注明作者和出处,谢谢!
上一篇:SQL Server2005杂谈(2):公用表表达式(CTE)的递归调用

 

mysql 经典面试题

(2011-10-26 17:13:38)

金沙棋牌app手机下载 1转载

标签:

杂谈

分类: 日志

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(5

0),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,'z

l',1000,2) , (null,'zl',1100,2);

 

编写sql语句:

 

()select avg(salary) from employee921 group by deptid;

()mysql> select employee921.id,employee921.name,employee921.salary,employee921.dep

tid 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.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)

金沙棋牌app手机下载, 

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
  这个SQL在运行时先取出两个表的结果,再用排序空间进行排序删除重复的记录,最后返回结果集,如果表数据量大的话可能会导致用磁盘进行排序。
 而UNION ALL只是简单的将两个结果合并后就返回。这样,如果返回的两个结果集中有重复的数据,那么返回的结果集就会包含重复的数据了。
 从效率上说,UNION ALL 要比UNION快很多,所以,如果可以确认合并的两个结果集中不包含重复的数据的话,那么就使用UNION ALL,

3.分页语句
取出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 id

sql 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 + "," + 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 directive

PreparedStatement pstmt = cn.prepareSatement(sql);

ResultSet rs = pstmt.executeQuery()

while(rs.next())

{

out.println(rs.getString(1));

}

 

2.不可滚动的游标

pageSize=20;

pageNo = 5;

cn = null

stmt = 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 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 = null

stmt = 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............

}

3.用一条SQL语句 查询出每门课都大于80分的学生姓名 
name   kecheng   fenshu
张三     语文       81
张三     数学       75
李四     语文       76
李四     数学       90
王五     语文       81
王五     数学       100
王五     英语       90

准备数据的sql代码:

create table score(id int primary key auto_increment,name varchar(20),subject varchar(20),score int);

insert into score values

(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);

 

4.所有部门之间的比赛组合
一个叫department的表,里面只有一个字段name,一共有4条纪录,分别是a,b,c,d,对应四个球对,现在四个球对进行比赛,用一条sql语句显示所有可能的比赛组合.

答:select a.name, b.name
from team a, team b
where a.name < b.name

 

4.每个月份的发生额都比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 TestDB

where 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

);

 

4.统计每年每月的信息
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;

 

5.显示文章标题,发帖人、最后回复时间
表: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 references articles(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) reply

from articles a where a.parentid is null;

 

注释:子查询可以用在选择列中,也可用于where的比较条件中,还可以用于from从句中。

3.删除除了id号不同,其他都相同的学生冗余信息
2.学生表 如下:
id号   学号   姓名 课程编号 课程名称 分数
1        2005001  张三  0001      数学    69
2        2005002  李四  0001      数学    89
3        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) mid

from student2 group by name) as t);

或者:

delete from student2 where id not in(select min(id) from (select * from s

tudent2) as t group by t.name);

4.航空网的几个航班查询题:
表结构如下:

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 wh

ere flight.endcityid=e.cityid and flight.startcityid=(select cityid from city wh

ere cityname='北京');

 

mysql> select flight.flightid,s.cityname,e.cityname from flight,city s,city e wh

ere flight.startcityid=s.cityid and s.cityname='北京' and flight.endCityId=e.cit

yID order by e.cityName desc;

 

 

2、查询北京到上海的所有航班纪录(起飞城市,到达城市,起飞时间,航班号)

select c1.CityName,c2.CityName,f.StartTime,f.flightID

from city c1,city c2,flight f

where f.StartCityID=c1.cityID

and f.endCityID=c2.cityID

and c1.cityName='北京'

and c2.cityName='上海'

3、查询具体某一天(2005-5-8)的北京到上海的的航班次数

select count(*) from

(select c1.CityName,c2.CityName,f.StartTime,f.flightID

from city c1,city c2,flight f

where f.StartCityID=c1.cityID

and f.endCityID=c2.cityID

and 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'

5.查出比经理薪水还高的员工信息:
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.sala

ry>m.salary;

6、求出小于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.tea

id,count(*) total from student,tea_stu where student.stuid=tea_stu.stuid and stu

dent.age>12 group by tea_stu.teaid) as tea_stu2 where teacher.teaid=tea_stu2.tea

id and teacher.age<45;

 

 

7.求出发帖最多的人:
select authorid,count(*) total from articles

group by authorid

having 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)也不行。

 

10、一个用户表中有一个积分字段,假如数据库中有100多万个用户,若要在每年第一天凌晨将积分清零,你将考虑什么,你将想什么办法解决?
解决方案一,update user set score=0;

解决方案二,假设上面的代码要执行好长时间,超出我们的容忍范围,那我就alter table user drop column score;alter table user add column score 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,0));

 

10、一个用户具有多个角色,请查询出该表中具有该用户的所有角色的其他用户。
select count(*) as num,tb.id

from

 tb,

 (select role from tb where id=xxx) as t1

where

 tb.role = t1.role and tb.id != t1.id

group by tb.id

having

num = select count(role) from tb where id=xxx;

  1. 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)

Table Departments 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.salary

from employee1,

(select deptid,avg(salary) avgSalary from employee1 group by deptid) as t

where employee1.deptid = t.deptid and employee1.salary>t.avgSalary;

 

有一个SQL题在面试中出现的概率极高,最近有学生出去面试仍然会遇到这样的题目,在这里跟大家分享一下。

题目:数据库中有一张如下所示的表,表名为sales。

季度 销售量
1991 1 11
1991 2 12
1991 3 13
1991 4 14
1992 1 21
1992 2 22
1992 3 23
1992 4 24

要求:写一个SQL语句查询出如下所示的结果。

一季度 二季度 三季度 四季度
1991 11 12 13 14
1992 21 22 23 24

我给出的答案是这样的:

select 年, 
sum(case when 季度=1 then 销售量 else 0 end) as 一季度, 
sum(case when 季度=2 then 销售量 else 0 end) as 二季度, 
sum(case when 季度=3 then 销售量 else 0 end) as 三季度, 
sum(case when 季度=4 then 销售量 else 0 end) as 四季度 
from sales group by 年;

使用row_number()分页:
select * from (select row_number() over(order by 时间字段) 编号列,* from 表) as 别名
where 编号列>= pageSize*(pageCode-1) + 1 and 编号列<=pageCode*pageSize
说明:pageSize代表每页显示的数据行数,pageCode代表当前页的页码(从1开始)

下一篇:SQL Server2005杂谈(4):按列连接字符串的三种方法

图1

注意:多表分页必须把你要查的字段列出来,不能用*代替

三、dense_rank

图3

金沙棋牌app手机下载 2

金沙棋牌app手机下载 3

1. 编号小的桶放的记录不能小于编号大的桶。也就是说,第1捅中的记录数只能大于等于第2桶及以后的各桶中的记录。

 

金沙棋牌app手机下载 4

    在图6所示的记录中后三条记录的field1字段值是相同的。如果使用rank函数来生成序号,这3条记录的序号是相同的,而第4条记录会根据当前的记录 数生成序号,后面的记录依此类推,也就是说,在这个例子中,第4条记录的序号是4,而不是2。rank函数的使用方法与row_number函数完全相 同,SQL语句如下:

举例:多表关联分页

金沙棋牌app手机下载 5

金沙棋牌app手机下载 6

    这个查询结果除了没有序号列row_number,其他的与图4所示的查询结果完全一样。

金沙棋牌app手机下载 7

4. ntile   
    下面分别介绍一下这四个排名函数的功能及用法。在介绍之前假设有一个t_table表,表结构与表中的数据如图1所示:

 

其中field1字段的类型是int,field2字段的类型是varchar

 

图2

    // mod表示取余,div表示取整 
    if(记录总数 mod 桶数 == 0)
    {
        recordCount = 记录总数 div 桶数;
        将每桶的记录数都设为recordCount
    } 
    else
    {
        recordCount1 = 记录总数 div 桶数 + 1;
        int n = 1;  //  n表示桶中记录数为recordCount1的最大桶数
        m = recordCount1 * n;
        while(((记录总数 - m)  mod  (桶数 -  n))  != 0 )
        {
            n++;
            m = recordCount1 * n;
        } 
        recordCount2 = (记录总数 - m) div  (桶数 - n);
        将前n个桶的记录数设为recordCount1
        将n + 1个至后面所有桶的记录数设为recordCount2
    }

2. rank

select rank() over(order by field1),* from t_table order by field1

金沙棋牌app手机下载 8

    其中row_number列是由row_number函数生成的序号列。在使用row_number函数是要使用over子句选择对某一列进行排序,然后才能生成序号。

图9

图8

    上面的SQL语句使用了CTE,关于CTE的介绍将读者参阅《SQL Server2005杂谈(1):使用公用表表达式(CTE)简化嵌套SQL》。
    另外要注意的是,如果将row_number函数用于分页处理,over子句中的order by 与排序记录的order by 应相同,否则生成的序号可能不是有续的。
    当然,不使用row_number函数也可以实现查询指定范围的记录,就是比较麻烦。一般的方法是使用颠倒Top来实现,例如,查询t_table表中第2条和第3条记录,可以先查出前3条记录,然后将查询出来的这三条记录按倒序排序,再取前2条记录,最后再将查出来的这2条记录再按倒序排序,就是最终结果。SQL语句如下:

一、row_number

 

    上面的SQL语句的查询结果如图9所示。

图3

    由于t_table表的记录总数是6,而上面的SQL语句中的ntile函数指定了桶数为4。

一、row_number

图9

金沙棋牌app手机下载 9

图2

2. 所有桶中的记录要么都相同,要么从某一个记录较少的桶开始后面所有捅的记录数都与该桶的记录数相同。也就是说,如果有个桶,前三桶的记录数都是10,而第4捅的记录数是6,那么第5桶和第6桶的记录数也必须是6。

排名函数是SQL Server2005新加的功能。在SQL Server2005中有如下四个排名函数:

select row_number() over(order by field2 desc) as row_number,* from t_table order by field1 desc

    上面的SQL语句查询出来的结果如图5所示。

    就拿本例来说,记录总数为6,桶数为4,则会算出recordCount1的值为2,在结束while循环后,会算出recordCount2的值是1,因此,前2个桶的记录是2,后2个桶的记录是1。

    上面的SQL语句使用了CTE,关于CTE的介绍将读者参阅《SQL Server2005杂谈(1):使用公用表表达式(CTE)简化嵌套SQL》。
    另外要注意的是,如果将row_number函数用于分页处理,over子句中的order by 与排序记录的order by 应相同,否则生成的序号可能不是有续的。
    当然,不使用row_number函数也可以实现查询指定范围的记录,就是比较麻烦。一般的方法是使用颠倒Top来实现,例如,查询t_table表中第2条和第3条记录,可以先查出前3条记录,然后将查询出来的这三条记录按倒序排序,再取前2条记录,最后再将查出来的这2条记录再按倒序排序,就是最终结果。SQL语句如下:

 

    排名函数是SQL Server2005新加的功能。在SQL Server2005中有如下四个排名函数:

 

图7

 

select ntile(4) over(order by field1) as bucket,* from t_table

1. row_number

    也许有的读者会问这么一个问题,SQL Server2005怎么来决定某一桶应该放多少记录呢?可能t_table表中的记录数有些少,那么我们假设t_table表中有59条记录,而桶数是5,那么每一桶应放多少记录呢?

 

金沙棋牌app手机下载 10

 

    上面的SQL语句查询出来的结果如图5所示。

    这个查询结果除了没有序号列row_number,其他的与图4所示的查询结果完全一样。

1. row_number

图6

select dense_rank() over(order by field1),* from t_table order by field1

select row_number() over(order by field2 desc) as row_number,* from t_table order by field1 desc

    读者可以比较图7和图8所示的查询结果有什么不同

    上面的SQL语句的查询结果如图4所示。

金沙棋牌app手机下载 11

    上面的SQL语句的查询结果如图3所示。

with t_rowtable
as
(
    select row_number() over(order by field1) as row_number,* from t_table
)
select * from t_rowtable where row_number>1 and row_number < 4 order by field1

 

    其中row_number列是由row_number函数生成的序号列。在使用row_number函数是要使用over子句选择对某一列进行排序,然后才能生成序号。

 

图4

金沙棋牌app手机下载 12

    根据上面的算法,如果记录总数为59,桶数为5,则前4个桶的记录数都是12,最后一个桶的记录数是11。

二、rank

    row_number函数的用途是非常广泛,这个函数的功能是为查询出来的每一行记录生成一个序号。row_number函数的用法如下面的SQL语句所示:

    rank函数考虑到了over子句中排序字段值相同的情况,为了更容易说明问题,在t_table表中再加一条记录,如图6所示。

    上面的SQL语句的查询结果如图8所示。

 

    上面的SQL语句的查询结果如图2所示。

    上面的SQL语句的查询结果如图7所示。

    在图6所示的记录中后三条记录的field1字段值是相同的。如果使用rank函数来生成序号,这3条记录的序号是相同的,而第4条记录会根据当前的记录 数生成序号,后面的记录依此类推,也就是说,在这个例子中,第4条记录的序号是4,而不是2。rank函数的使用方法与row_number函数完全相 同,SQL语句如下:

3. dense_rank

select rank() over(order by field1),* from t_table order by field1

    如果记录总数为53,桶数为5,则前3个桶的记录数为11,后2个桶的记录数为10。

    根据上面的两个约定,可以得出如下的算法:

2. rank

图1

    就拿本例来说,记录总数为6,桶数为4,则会算出recordCount1的值为2,在结束while循环后,会算出recordCount2的值是1,因此,前2个桶的记录是2,后2个桶的记录是1。

金沙棋牌app手机下载 13

四、ntile
    ntile函数可以对序号进行分组处理。这就相当于将查询出来的记录集放到指定长度的数组中,每一个数组元素存放一定数量的记录。ntile函数为每条记 录生成的序号就是这条记录所有的数组元素的索引(从1开始)。也可以将每一个分配记录的数组元素称为“桶”。ntile函数有一个参数,用来指定桶数。下 面的SQL语句使用ntile函数对t_table表进行了装桶处理:

    rank函数考虑到了over子句中排序字段值相同的情况,为了更容易说明问题,在t_table表中再加一条记录,如图6所示。

select ntile(4) over(order by field1) as bucket,* from t_table

图6

 

select * from (select top 2 * from( select top 3 * from t_table order by field1) a order by field1 desc) b order by field1

 

 

金沙棋牌app手机下载 14

    上面的SQL语句的查询结果如图9所示。

金沙棋牌app手机下载 15

3. dense_rank

三、dense_rank

    上面的SQL语句的查询结果如图7所示。

    也许有的读者会问这么一个问题,SQL Server2005怎么来决定某一桶应该放多少记录呢?可能t_table表中的记录数有些少,那么我们假设t_table表中有59条记录,而桶数是5,那么每一桶应放多少记录呢?

    实际上通过两个约定就可以产生一个算法来决定哪一个桶应放多少记录,这两个约定如下:

金沙棋牌app手机下载 16

select row_number() over(order by field1) as row_number,* from t_table

 

    上面的SQL语句的查询结果如图3所示。

    根据上面的算法,如果记录总数为59,桶数为5,则前4个桶的记录数都是12,最后一个桶的记录数是11。

    上面的SQL语句的查询结果如图4所示。

 

二、rank

转载地址:

图8

 

 

4. ntile   
    下面分别介绍一下这四个排名函数的功能及用法。在介绍之前假设有一个t_table表,表结构与表中的数据如图1所示:

图5

    实际上,row_number函数生成序号的基本原理是先使用over子句中的排序语句对记录进行排序,然后按着这个顺序生成序号。over子句中的order by子句与SQL语句中的order by子句没有任何关系,这两处的order by 可以完全不同,如下面的SQL语句所示:

    // mod表示取余,div表示取整 
    if(记录总数 mod 桶数 == 0)
    {
        recordCount = 记录总数 div 桶数;
        将每桶的记录数都设为recordCount
    } 
    else
    {
        recordCount1 = 记录总数 div 桶数 + 1;
        int n = 1;  //  n表示桶中记录数为recordCount1的最大桶数
        m = recordCount1 * n;
        while(((记录总数 - m)  mod  (桶数 -  n))  != 0 )
        {
            n++;
            m = recordCount1 * n;
        } 
        recordCount2 = (记录总数 - m) div  (桶数 - n);
        将前n个桶的记录数设为recordCount1
        将n + 1个至后面所有桶的记录数设为recordCount2
    }

金沙棋牌app手机下载 17

 

 

四、ntile
    ntile函数可以对序号进行分组处理。这就相当于将查询出来的记录集放到指定长度的数组中,每一个数组元素存放一定数量的记录。ntile函数为每条记 录生成的序号就是这条记录所有的数组元素的索引(从1开始)。也可以将每一个分配记录的数组元素称为“桶”。ntile函数有一个参数,用来指定桶数。下 面的SQL语句使用ntile函数对t_table表进行了装桶处理:

with t_rowtable
as
(
    select row_number() over(order by field1) as row_number,* from t_table
)
select * from t_rowtable where row_number>1 and row_number < 4 order by field1

1. 编号小的桶放的记录不能小于编号大的桶。也就是说,第1捅中的记录数只能大于等于第2桶及以后的各桶中的记录。

select row_number() over(order by field1) as row_number,* from t_table

    我们可以使用row_number函数来实现查询表中指定范围的记录,一般将其应用到Web应用程序的分页功能上。下面的SQL语句可以查询t_table表中第2条和第3条记录:

    实际上通过两个约定就可以产生一个算法来决定哪一个桶应放多少记录,这两个约定如下:

    dense_rank函数的功能与rank函数类似,只是在生成序号时是连续的,而rank函数生成的序号有可能不连续。如上面的例子中如果使用dense_rank函数,第4条记录的序号应该是2,而不是4。如下面的SQL语句所示:

 

金沙棋牌app手机下载 18

金沙棋牌app手机下载 19

    实际上,row_number函数生成序号的基本原理是先使用over子句中的排序语句对记录进行排序,然后按着这个顺序生成序号。over子句中的order by子句与SQL语句中的order by子句没有任何关系,这两处的order by 可以完全不同,如下面的SQL语句所示:

2. 所有桶中的记录要么都相同,要么从某一个记录较少的桶开始后面所有捅的记录数都与该桶的记录数相同。也就是说,如果有个桶,前三桶的记录数都是10,而第4捅的记录数是6,那么第5桶和第6桶的记录数也必须是6。

    如果记录总数为53,桶数为5,则前3个桶的记录数为11,后2个桶的记录数为10。

select dense_rank() over(order by field1),* from t_table order by field1

    由于t_table表的记录总数是6,而上面的SQL语句中的ntile函数指定了桶数为4。

 

图5

其中field1字段的类型是int,field2字段的类型是varchar

 

    根据上面的两个约定,可以得出如下的算法:

图4

    读者可以比较图7和图8所示的查询结果有什么不同

图7

    dense_rank函数的功能与rank函数类似,只是在生成序号时是连续的,而rank函数生成的序号有可能不连续。如上面的例子中如果使用dense_rank函数,第4条记录的序号应该是2,而不是4。如下面的SQL语句所示:

select * from (select top 2 * from( select top 3 * from t_table order by field1) a order by field1 desc) b order by field1

 

    我们可以使用row_number函数来实现查询表中指定范围的记录,一般将其应用到Web应用程序的分页功能上。下面的SQL语句可以查询t_table表中第2条和第3条记录:

    row_number函数的用途是非常广泛,这个函数的功能是为查询出来的每一行记录生成一个序号。row_number函数的用法如下面的SQL语句所示:

 

金沙棋牌app手机下载 20

    上面的SQL语句的查询结果如图2所示。

 

    上面的SQL语句的查询结果如图8所示。

本文由金沙棋牌发布于金沙棋牌app手机下载,转载请注明出处:mysql经典面试题,SqlServer四个排名函数

关键词: