数据库建表 多表查询

create table tsb(

zbh int primary key,

flh char(10) not null,

sm varchar(20) not null,

zz varchar(10) not null,

cbdw varchar(20) not null,

dj char(10))

 

create table dzb(jszh int primary key,

dw char(10),

xm char(10),

xb enum('男','女'),

zc char(10),

dz varchar(50))

 

 

create table jyb(

jszh int,

zbh int,

foreign key(jszh) references dzb(jszh),

 

foreign key(zbh) references tsb(zbh),

primary key(jszh,zbh),

jsrq date);

#daoru

 

 

 

/*

聚合函数

  count(*)---统计函数--数一数

  max(字段名)---最大值

  min(字段名)---最小值

  sum(字段名)---求和

  avg(字段名)---平均值

*/

 

 

#查询学生表中最大年龄和最小年龄

select max(nl),min(nl)

from xsb

 

#查询学生表中189班男生的人数

select count(*)

from xsb

where bj='189'and xb='男'

 

# 查询成绩表中最高分和最低分

select max(cj),min(cj)

from cjb

 

#查询001号学生的总分和平均分

select sum(cj)总分,avg(cj)平均分

from cjb

where xh='001'

 

#查询003号考试的科数

select count(*)

from cjb

where xh='003'

 

#查询学生表中学生来自几个省份

select count(*),jg

from xsb

group by jg

 

select distinct jg 籍贯

from xsb

 

select count(distinct jg)

from xsb

 

 

#查询189班男女生人数

select count(*),xb

from xsb

where bj='189'

group by xb

 

#查询每个学生的最高分，总分和平均分

select max(cj)最高分,sum(cj)总分,avg(cj)平均分,xh 学号

from cjb

group by xh

#查询每门课程的总分和平均分

select sum(cj)总分,avg(cj)平均分,kch 课程号

from cjb

group by kch

#查询各班男女生人数

select count(*),bj,xb

FROM

xsb ,

cjb

where bj='189'

group by bj,xb

#查询总成绩大于400分的学号

select xh

from cjb

group by xh

having sum(cj)>400

 

 

 

#按年龄从小到大显示全部学生信息

select *

from xsb

where nl is not null

order by nl asc

 

 

#按学号升序，成绩降序显示全部的成绩信息

select *

from cjb

order by xh, cj desc

#显示成绩表中成绩最好的前三条记录

select *

from cjb

order by cj desc

limit 5

#显示成绩表中成绩最好的第六到第十条记录

 

 

 

 

#多表查询---等值连接

 

/*

三张表

  select 查询内容/结果

  from A,B,C

  where A.主键=B.外键 and C.主键=B.外键

  and 查询条件

两张表

  select 查询内容

  from A,B

  where A.主键=B.外键

  and 查询条件

*/

 

#查询张三的C语言的成绩

select cj

from xsb,kcb,cjb

where xsb.xh=cjb.xh and kcb.kch=cjb.kch

and xm='张三'and kcm='C语言'

#查询参加缺陷管理课程考试的人数

select count(*)

from kcb,cjb

where kcb.kch=cjb.kch

and kcm='缺陷管理'

#查询总成绩大于400分的学员姓名

select xm,sum(cj)

from xsb,cjb

where xsb.xh=cjb.xh

group by xsb.xh

having sum(cj)>400

#查询189班男生C语言的总成绩

select sum(cj)

from xsb,kcb,cjb

where xsb.xh=cjb.xh and kcb.kch=cjb.kch

and bj='189' and xb='男' and kcm='C语言'

#按姓名显示每个人的最高分、总分以及平均分

select xm,max(cj),sum(cj),avg(cj)

from xsb,cjb

where xsb.xh=cjb.xh

group by xsb.xh

#      having sum(cj)>380

 

#显示(个人)总成绩前三名的学员姓名

select xm,sum(cj),cjb.xh

from xsb,cjb

where xsb.xh=cjb.xh

group by xsb.xh

order by sum(cj) desc

limit 3