20141104数据库join,union

use new

go

--------常用方法-----查销售部里年龄大于35 的人全部信息-------

select *from haha  where age >35 and bumen =(select code from bumen where bumen =‘销售部‘)

--------偏方exists（认识她，会用她就好）哈哈哈-------------

select *from haha  where age >35 and exists (select * from bumen where bumen.code=haha .bumen and bumen .bumen =‘销售部‘)

--把haha 表中bumen 命名为‘部门名’并且显示bumen 里的信息(后面又添加了ceo信息)-select name ,sex ,age,(select bumen  from bumen where bumen.code=haha.bumen )as 部门名,(select ceo from bumen where bumen.code=haha.bumen ) as CEO from haha

--可以select 查询，再命名新列 ，作为添加列

select haha .name ,sex,age,bumen.bumen,ceo from haha,bumen

 where haha.bumen=bumen.code

--表格中有的信息列  可以直接添加

select haha.name,sex,age,bumen.bumen ,ceo  from haha

join bumen on haha.bumen =bumen .code

--join 添加列  （注意表达方式）

-------------以上是三种方法，注意表达格式！---------

---join 添加的表on  两个表的连接关系 ---

insert into haha values (16,‘发顺丰‘,‘男‘,41,5)

insert into bumen values (6,‘安保部‘,‘看家‘,null,null)

--插入两行信息为了，方便观察jion xxx  on xxx 作用！

select haha .name ,sex,age,bumen .bumen ,ceo,zhineng from haha

join  bumen on haha .bumen =bumen .code

--

select haha .name ,sex,age,bumen .bumen ,ceo,zhineng from haha

 full  join  bumen on haha .bumen =bumen .code

-- full把两份表要提取的信息全部显示,不管信息在两个表中是不是有关系!

select haha .name ,sex,age,bumen .bumen ,ceo,zhineng from haha

left  join  bumen on haha .bumen =bumen .code

-- left 会把左边的表haha要提取的信息全部显示,并且按原顺序排列,不管信息在两个表中是不是有关系!

select haha .name ,sex,age,bumen .bumen ,ceo,zhineng from haha

right join  bumen on haha .bumen =bumen .code

--right 会把右边的表bumen要提取的信息全部显示,并且按原顺序排列,不管信息在两个表中是不是有关系! 

-------------联合查询（纵向添加表信息）添加 ’行’----------

select *from haha where age >40

union                           --要求两个表‘列’数据类型对应一样！

select *from haha where age <30

 --(显示表列名，显示的第一个表列名！两个表的相同信息，连接后自动去重！)

select name,bumen from haha

union

select ceo,code from bumen

￥￥￥￥￥￥￥￥￥￥￥￥￥￥￥￥￥￥￥￥￥￥￥￥￥￥￥￥￥￥￥￥￥￥￥￥￥

--创建学生信息表：学号、姓名、班级、性别、语文教师编号、数学教师编号、英语教师编号

create table xuesheng

(

code int ,

name varchar(20),

banji varchar(20),

sex varchar(20),

yuhao int,

shuhao int,

yinghao int,

)

go

insert into xuesheng values(1,‘周一‘,‘2班‘,‘男‘,1001,1007,1009)

insert into xuesheng values(2,‘郑二‘,‘1班‘,‘女‘,1001,1007,1009)

insert into xuesheng values(3,‘张三‘,‘3班‘,‘男‘,1001,1007,1009)

insert into xuesheng values(4,‘李四‘,‘2班‘,‘男‘,1001,1007,1009)

insert into xuesheng values(5,‘王五‘,‘3班‘,‘女‘,1001,1007,1009)

insert into xuesheng values(6,‘赵六‘,‘1班‘,‘男‘,1001,1007,1009)

insert into xuesheng values(7,‘田七‘,‘1班‘,‘男‘,1001,1007,1009)

insert into xuesheng values(8,‘孙八‘,‘3班‘,‘女‘,1001,1007,1009)

insert into xuesheng values(9,‘钱九‘,‘2班‘,‘男‘,1001,1007,1009)

insert into xuesheng values(10,‘吴王‘,‘3班‘,‘男‘,1001,1007,1009)

insert into xuesheng values(11,‘勾践‘,‘2班‘,‘女‘,1001,1007,1009)

insert into xuesheng values(12,‘西施‘,‘1班‘,‘男‘,1001,1007,1009)

select *from xuesheng

--创建教师表：教师编号、姓名、课程、性别、出生日期

create table jiaoshi 

(

code int ,

name varchar(20),

kecheng varchar(20),

sex  varchar(20),

shengri varchar(20),

)

go

insert into jiaoshi values(1001,‘赵文‘,‘语文‘,‘男‘,‘1989-09-16‘)

insert into jiaoshi values(1007,‘李数‘,‘数学‘,‘女‘,‘1993-09-1‘)

insert into jiaoshi values(1009,‘张外‘,‘英语‘,‘男‘,‘1988-09-9‘)

select *from jiaoshi

--update jiaoshi set kecheng =‘英语‘where code =1009

--修改的使用方法update haha set bumen=1 where bumen =‘销售部‘

--drop table jiaoshi --删除表

--delete jiaoshi where code =1001--删除表中的列

--创建分数表：语文分数、数学分数、英语分数、学生学号

create table fenshu

(

code  int,

yufen decimal(18,2),

shufen decimal(18,2),

yingfen decimal(18,2),

)

go

insert into fenshu values (1,98,97,99)

insert into fenshu values (2,87,76,88)

insert into fenshu values (3,59,68,99)

insert into fenshu values (4,88,99,77)

insert into fenshu values (5,88,70,97)

insert into fenshu values (6,88,47,69)

insert into fenshu values (7,74,87,77)

insert into fenshu values (8,98,97,99)

insert into fenshu values (9,90,46,99)

insert into fenshu values (10,98,100,99)

insert into fenshu values (11,90,91,79)

insert into fenshu values (12,98,57,99)

select *from fenshu

--分别插入虚拟数据，之后进行查询操作：

--1.查询此次考试语文成绩最高的学生的信息

select top 1  * from fenshu order by yufen desc--找到语文最高分的信息

select  *from  xuesheng where code =

(select top 1 code from fenshu order by yufen desc)

--2.查询此次考试数学成绩最低的学生的任课教师的信息

select top 1 *from fenshu order by shufen 

select *from xuesheng where code =(select top 1 code from fenshu order by shufen  )

select *from jiaoshi where code =(select shuhao from xuesheng where code =(select top 1 code from fenshu order by shufen  ))

--3.查询汇总成为一个表：各门课分数、学生姓名、班级、任课教师的姓名

select xuesheng .name ,banji,fenshu.yufen,shufen,yingfen,(select name from jiaoshi where code =(select distinct yuhao from xuesheng) )as 语文老师,(select name from jiaoshi where code =(select distinct shuhao from xuesheng))as 数学老师,(select name from jiaoshi where code =(select distinct yinghao from xuesheng))as 英语老师 from xuesheng

join fenshu on xuesheng.code=fenshu.code

join jiaoshi on xuesheng.yuhao=jiaoshi.code

--在新建的虚拟表里，可以用（select ……）as xxx 任意插入信息列！例如,

select name ,sex ,age,(select bumen  from bumen where bumen.code=haha.bumen )as 部门名,(select ceo from bumen where bumen.code=haha.bumen ) as CEO from haha

--4.查询每个班里数学最高分

--select xuesheng.name,banji,fenshu.shufen from xuesheng  

join fenshu on xuesheng.code =fenshu.code 

 group by banji -–此时xuesheng.name、fenshu.shufen都无效，因为不是聚合函数或者group by 里面不包含。

select banji,zuigaoshu from

(select banji,name,(select MAX(shufen)from fenshu)as zuigaoshu  from xuesheng) group by banji

--新建的虚拟表语句 加（），就代表一个新的表！就可以进行操作。

select banji ,MAX(shufen) as 每班中数学最高分 from xuesheng

join fenshu on xuesheng .code=fenshu .code

group by banji -–此时显示按照班级分组的最高数学成绩

--分组放在最后！聚合函数只能对表的列作用，对虚拟的select语句要在(select……)中查询！

$$$$$$$$老师解法$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$

select class,MAX(math) from student

join score on student.code = score.stucode

group by class

select max(math),class from score  --按照第一列所在表 ，写要查询的表

join student on student.code = score.stucode

group by class

select max(math),class from

(select math,(select class from student where student.code = score.stucode) as class from score) as newtable

group by class   --在（）作用下，查询select 语句

--5.查询语文课程平均分最高的班级的老师的信息

select *from xuesheng

select *from jiaoshi

select *from fenshu

select *from jiaoshi where code =(

select distinct yuhao from xuesheng where banji=(select top 1 banji from fenshu

join xuesheng on fenshu.code=xuesheng.code

group by banji order by AVG(yufen) desc)

)

$$$$$$$$$老师解法$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$

select *from teacher where code=(

select top 1 chteacher from student where class=(

select top 1 class  from student

join score on score.stucode = student.code

group by class order by AVG(chiese) desc   )

)