mysql出色面试题

from (select row,number() over(order by 时间字段) 编号列,工资从低到高列出每个员工的信息,1、用两种方式根据部门号从高到低,    排名函数是SQL,Server2005中有如下四个排名函数,排名函数是SQL Server2005新加的功能,下面分别介绍一下这四个排名函数的功能及用法

图片 3

比喻:多表关联分页

mysql 突出面试题

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

图片 1转载

标签:

杂谈

分类: 日志

1、用两种艺术基于单位号从高到低,薪给从低到高列出各种职工的新闻。
employee:

     eid,ename,salary,deptid;

 select * from employee order by deptid desc,salary

 

 

二、列出各类部门安徽中华南理文大学程公司资超越本机关的平均薪资的职员和工人数和机构号,并按单位号排序
创建表:

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

   功效低的3个说话,仅供就学参考运用(在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;

一、存款和储蓄进程与触发器必须讲,日常被面试到?
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

 

触发器用处依旧广大的,比方校内网、心情舒畅网、推文(Tweet),你发一个日志,自动通告好友,其实正是在加多日志时做2个后触发,再向布告表中写入条约。因为触发器作用高。而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(‘传智播客’,一,@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),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);

还有,每插入2个帖子,都愿意将版面表中的末梢发帖时间,帖子总量字段进行联合更新,用触发器做功用就异常高。下次课设计那样三个案例,写触发器时,对于最终发帖时间或者要求用declare格局证明1(Wissu)个变量,或许是用NEW.posttime来变化。

 

一、数据库三范式是怎么?
首先范式(1NF):字段具备原子性,不可再分。全体关系型数据库系统都满意第二范式)

数据库表中的字段都以单1属性的,不可再分。比如,姓名字段,个中的姓和名必须作为三个完全,不能区分哪部分是姓,哪部分是名,假使要分化出姓和名,必须统一希图成多个独立的字段。

 

  第1范式(贰NF):

第叁范式(贰NF)是在第三范式(1NF)的基础上成立起来的,即满足第三范式(贰NF)必须先满意第3范式(一NF)。

务求数据库表中的各个实例或行必须能够被惟壹地分别。平常需求为表加上三个列,以存款和储蓄各类实例的天下无双标志。那么些惟壹属性列被称作主关键字或主键。

 

第1范式(二NF)供给实体的天性完全依据于主关键字。所谓完全依据是指不能够存在仅依据主关键字一部分的质量,假若存在,那么那本性格和主关键字的那1部
分应该分离出来产生三个新的实业,新实体与原实体之间是一对多的关系。为得以实现区分经常需求为表加上一个列,以存款和储蓄各类实例的并世无两标志。简单来讲,第叁范式
便是非主属性非部分注重于主关键字。

 

 第一范式的必要如下:

满意第一范式(3NF)必须先餍足第一范式(2NF)。简单来说,第二范式(三NF)须要多个数据库表中不分包已在其余表中已包括的非主关键字新闻。

于是第一范式具有如下特征:
         一,每一列只有三个值
         二,每一行都能分别。
         三,每多少个表都不包罗其余表已经蕴涵的非主关键字音讯。

举个例子,帖子表中只好出现发帖人的id,而不可能出现发帖人的id,还同时出现发帖人姓名,不然,只要出现雷同发帖人id的享有记录,它们中的姓名部分都无法不从严保持1致,这正是多少冗余。

 

1、说出一些数据库优化方面包车型地铁经历?
用PreparedStatement 一般的话比Statement质量高:3个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的区分之1在于对再度结果的管理。

 

  UNION在拓展表链接后会筛选掉重复的记录,所以在表链接后会对所发生的结果集实行排序运算,删除重复的记录再重回结果。实际大多数使用中是不会生出重复的笔录,最广泛的是过程表与正史表UNION。如:
select * from gc_dfys
union
select * from ls_jg_dfys
  这些SQL在运营时先抽出五个表的结果,再用排序空间实行排序删除重复的记录,最终回到结果集,要是表数据量大的话或然会促成用磁盘实行排序。
 而UNION
ALL只是大致的将八个结果合并后就回去。那样,假设回到的四个结果聚焦有重复的数量,那么再次来到的结果集就能够含有重复的数目了。
 从功效上说,UNION ALL
要比UNION快多数,所以,倘若可以确认合并的多个结实聚焦不分包重复的数目以来,那么就利用UNION
ALL,

3.分页语句
抽取sql表中第1一到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(直接使用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;

批注:第八行保证rownum的各种是明确的,因为oracle的目录会产生rownum重返不一样的值

简洋提示:未有order by时,rownum按梯次输出,1旦有了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));

}

 

二.不得滚动的游标

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…………

}

 

三.可滚动的游标

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,…);

//总部方那行代码的13分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语句 查询出每门课都大于七十八分的学童姓名 
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);

 

四.全体单位时期的较量组合
3个叫department的表,里面只有1个字段name,1共有四条纪录,分别是a,b,c,d,对应八个球对,未来七个球对拓展较量,用一条sql语句突显全数望的竞技组合.

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

 

4.每一个月份的发生额都比10一学科多的课程
请用SQL语句完毕:从TestDB数据表中查询出富有月份的发生额都比拾一科目相应月份的发生额高的教程。请留心:TestDB中有广高校科,都有1-7月份的产生额。
AccID:科目代码,Occmonth:发生额月份,DebitOccur:发生额。
数据库名:Jcy奥迪(Audi)t,数据集: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

);

 

四.计算每年每月的音信
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,’第贰条回复一’,’李四’,’一9玖陆-十-10 1二:35:32′,1),

(null,’第2条回复壹’,’李4′,’1997-拾-10 1二:3陆:3二’,2),

(null,’第2条回复2′,’王伍’,’一九97-10-十 1二:3柒:3贰’,一),

(null,’第二条回复三’,’李4′,’1九九陆-十-十 1二:38:3二’,1),

(null,’第一条回复二’,’李四’,’一99陆-10-十 1二:3玖:3二’,2),

(null,’第三条回复4′,’王五’,’1九玖7-10-10 1二:3九:40′,一);

 

答案:

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从句中。

叁.刨除除了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);

四.航空网的多少个航班查询题:
表结构如下:

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

 

 

壹、查询起飞城市是首都的享有航班,按达到城市的名字排序

 

 

涉足运算的列是本身至少可以显示出来的那多少个列,但最后本人不自然把它们展现出来。各种表组成出来的高级中学级结果字段中必须包罗全体运算的字段。

 

  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;

 

 

贰、查询法国巴黎到香江的享有航班纪录(起飞城市,达到城市,起飞时间,航班号)

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=’上海’

叁、查询具体某一天(200伍-5-捌)的京师到北京的的航车的班次数

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 ‘200伍-伍-捌%’

 

mysql中领取日期部分开始展览比较的示范代码如下:

select * from flight where
date_format(starttime,’%Y-%m-%d’)=’1998-01-02′

伍.查出比COO薪给还高的职工新闻:
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语句的询问特点,是逐行进行演算,不容许两行同时插手运算。

论及了职工薪资和经纪薪金,全数,一行记录要同时涵盖多个薪给,全数想到要把那些表自关联组合一下。

    
首先要组成出八个分包有种种职员和工人及该职员和工人的经营新闻的长记录,譬如,左半部分是职员和工人,右半部分是老董。而迪Carl积会组合出无数破烂信息,先去除这一个污源音讯。

 

select e.* from employees e,employees m where e.managerid=m.id and
e.sala

ry>m.salary;

六、求出小于肆陆岁的各样老师所带的胜出十一周岁的学习者人数
数据库中有二个表 teacher 表,student表,tea_stu关系表。
teacher 表 teaID name age
student 表 stuID name age
teacher_student表 teaID stuID
务求用一条sql查询出那样的结果
一.浮现的字段要有教师name, age 每种老师所带的学生人数
二 只列出老师age为40之下,学生age为1二之上的记录

策画知识:

     
1.sql语句是对每一条记下依次拍卖,条件为真则执行动作(select,insert,delete,update)

      
二.万1是迪Carl积,就能时有发生“垃圾”音讯,所以,只要迪Carl积了,我们先是将在想到清除“垃圾”消息

尝试绸缪:

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

 

解题思路:(真实面试答题时,也要写出每一个分析步骤,假使纸张不够,就找他人要)

一要会总括分组新闻,统计音信放在中间表中:

select teaid,count(*) from tea_stu group by teaid;

 

2跟着其实应当是筛除掉小于16周岁的上学的小孩子,然后再张开总括,中间表必须与student关联才干收获1三岁以下学生和把该学生记录从中间表中去除,代码是:

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

 

三.随着把下边的结果做成虚表与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;

 

 

柒.求出发帖最多的人:
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)也不行。

 

十、一个用户表中有一个积分字段,假设数据库中有十0多万个用户,若要在每年第一天凌晨将积分清零,你将思念什么,你将想如何方法解决?
竭泽而渔方案壹,update user set score=0;

化解方案2,借使上边的代码要实践好长期,高出大家的忍受范围,那本身就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));

 

十、多个用户全数多个剧中人物,请查询出该表中具有该用户的装有剧中人物的其余用户。
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).

 

 (二)基于上述EMPLOYEES表写出查询:写出雇用日期在今年的,或然薪酬在[1000,2000]里面包车型大巴,或许职员和工人姓名(last_name)以’前美总统’打头的有所职工,列出这么些职员和工人的全方位个人音信。(5分)

select * from employees

where Year(hiredDate) = Year(date())

or (salary between 1000 and 200)

or left(last_name,3)=’abc’;

 

(三)
基于上述EMPLOYEES表写出查询:查出部门平均薪给大于1800元的单位的全体职工,列出这个职员和工人的所有个人消息。(陆分)

mysql> select id,name,salary,deptid did from employee1 where (select
avg(salary)

 from employee1 where deptid = did) > 1800;

 

(四)
基于上述EMPLOYEES表写出查询:查出个人工资超过其所在机构平均报酬的职员和工人,列出这一个职员和工人的百分之百个人消息及该职员和工人薪水逾越部门平均薪酬百分比。(6分)

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;

 

有3个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 年;

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

二. 兼有桶中的记录要么都同样,要么从某一个记下较少的桶开首前边全体捅的记录数都与该桶的记录数同样。也正是说,假如有个桶,前三桶的记录数都以10,而第5捅的记录数是六,那么第伍桶和第伍桶的记录数也务必是六。

只顾:多表分页必须把你要查的字段列出来,无法用*代替

    读者能够比较图柒和图8所示的询问结果有什么样分歧

    上边的SQL语句的询问结果如图柒所示。

使用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诗歌(四):按列连接字符串的三种办法

 

 

    rank函数牵记到了over子句中排序字段值同样的情景,为了更易于表明难题,在t_table表中再加一条记下,如图陆所示。

    读者可以相比较图7和图八所示的询问结果有何两样

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

图片 2

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

图片 3

    row_number函数的用途是十三分普及,这些函数的功用是为查询出来的每1行记录生成2个序号。row_number函数的用法如下边包车型大巴SQL语句所示:

二、rank

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

 

图片 4

图6

 

 

    就拿本例来讲,记录总量为6,桶数为四,则会算出recordCount壹的值为2,在终结while循环后,会算出recordCount贰的值是1,因而,前二个桶的笔录是二,后二个桶的记录是一。

 

二. 有着桶中的记录要么都同样,要么从某2个记下较少的桶起始后边全数捅的记录数都与该桶的记录数同样。也正是说,倘若有个桶,前三桶的记录数都以十,而第4捅的记录数是6,那么第4桶和第6桶的记录数也务必是六。

 

    上边的SQL语句的询问结果如图7所示。

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

3. dense_rank

    就拿本例来讲,记录总量为6,桶数为四,则会算出recordCount一的值为贰,在终结while循环后,会算出recordCount二的值是一,因而,前三个桶的笔录是2,后2个桶的笔录是①。

    实际上,row_number函数生成序号的基本原理是先使用over子句中的排序语句对记录实行排序,然后按着那么些顺序生成序号。over子句中的order by子句与SQL语句中的order by子句未有其余关联,那两处的order by
能够完全不一致,如上边的SQL语句所示:

 

1. row_number

 

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

3. dense_rank

2. rank

图片 5

图3

    总部方的算法,假若记录总的数量为5玖,桶数为5,则前陆个桶的记录数皆以1二,最后一个桶的记录数是1一。

一. 号码小的桶放的笔录不能够小于编号大的桶。相当于说,第贰捅中的记录数只好大于等于第3桶及未来的各桶中的记录。

图8

    遵照上面包车型客车五个约定,能够汲取如下的算法:

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

图片 6

图片 7

图1

图7