--创建学生信息表：学号，姓名，班级，性别，语文教师，数学教师，英语教师--创建一个教师表：编号，姓名，课程，性别，出生日期--创建一个分数表：语文，数学，外语，学号--查询 语文成绩最高的学生信息--查询 数学成绩最低的任课教师信息--查询汇总表，有分数，学生姓名，班级，及任课教师姓名。--查询每个班里数学最高分--查询语文课程平均分最高的班级的任课教师信息create database shujuku02gouse shujuku02gocreate table jiaoshi--创建jiaoshi表--(    code int primary key,--code 列设置主键    name varchar(20),    kecheng varchar(20),    sex varchar(10),    riqi varchar(20),)gocreate table xuesheng--创建xuesheng表，与jiaoshi表有关联(    xuehao int primary key,--xuehao列设置主键    name varchar(20),    banji varchar(10),    sex varchar(10),    Cnjs int references [dbo].[jiaoshi]([code]),--设置外键，与jiaoshi关联    Majs int references [dbo].[jiaoshi]([code]),--    Enjs int references [dbo].[jiaoshi]([code]),--)gocreate table fenshu--创建fenshu表，与学生表有关联(    xuhao int references [dbo].[xuesheng]([xuehao]),--设置外键，与xuesheng关联    yuwen decimal(10,2),    shuxue decimal(10,2),    yingyu decimal(10,2),)goinsert into jiaoshi values (1,‘刘欣欣‘,‘语文‘,‘女‘,‘1980-5-10‘)insert into jiaoshi values (2,‘文祥‘,‘语文‘,‘女‘,‘1983-9-19‘)insert into jiaoshi values (3,‘毕华‘,‘数学‘,‘男‘,‘1977-1-10‘)insert into jiaoshi values (4,‘闻广华‘,‘数学‘,‘男‘,‘1979-5-10‘)insert into jiaoshi values (5,‘张峰‘,‘英语‘,‘男‘,‘1984-5-10‘)insert into jiaoshi values (6,‘李莉‘,‘英语‘,‘女‘,‘1986-7-7‘)select *from jiaoshi insert into xuesheng values (1,‘曹操‘,‘1班‘,‘男‘,1,3,5)insert into xuesheng values (2,‘曹丕‘,‘1班‘,‘男‘,1,3,5)insert into xuesheng values (3,‘司马懿‘,‘1班‘,‘男‘,1,3,5)insert into xuesheng values (4,‘大乔‘,‘1班‘,‘女‘,1,3,5)insert into xuesheng values (5,‘小乔‘,‘1班‘,‘女‘,1,3,5)insert into xuesheng values (6,‘张飞‘,‘2班‘,‘男‘,2,3,6)insert into xuesheng values (7,‘关羽‘,‘2班‘,‘男‘,2,3,6)insert into xuesheng values (8,‘刘备‘,‘2班‘,‘男‘,2,3,6)insert into xuesheng values (9,‘诸葛‘,‘2班‘,‘男‘,2,3,6)insert into xuesheng values (10,‘貂蝉‘,‘2班‘,‘女‘,2,3,6)insert into xuesheng values (11,‘袁绍‘,‘3班‘,‘男‘,1,4,5)insert into xuesheng values (12,‘袁术‘,‘3班‘,‘男‘,1,4,5)insert into xuesheng values (13,‘马超‘,‘3班‘,‘男‘,1,4,5)insert into xuesheng values (14,‘华容‘,‘3班‘,‘女‘,1,4,5)insert into xuesheng values (15,‘马术‘,‘3班‘,‘男‘,1,4,5)select *from xuesheng insert into fenshu values (1,80,80,58)insert into fenshu values (2,65,35,80)insert into fenshu values (3,78,42,80)insert into fenshu values (4,85,80,15)insert into fenshu values (5,80,87,80)insert into fenshu values (6,86,80,76)insert into fenshu values (7,28,82,80)insert into fenshu values (8,45,72,68)insert into fenshu values (9,99,99,10)insert into fenshu values (10,87,88,36)insert into fenshu values (11,80,25,82)insert into fenshu values (12,68,28,80)insert into fenshu values (13,59,78,79)insert into fenshu values (14,28,99,96)insert into fenshu values (15,99,18,77)select *from fenshu --------------------select *from jiaoshi select *from xuesheng select *from fenshu -----------查询 语文成绩最高的学生信息select *from xuesheng where xuehao in(--根据xuehao查询学生信息select xuhao from fenshu where yuwen=(--根据yuwen分数查询学生xuhaoselect MAX(yuwen) from fenshu ))-----------查询 数学成绩最低的任课教师信息select *from jiaoshi where code in (--根据教师code查询教师信息select Majs from xuesheng where xuehao in(--根据学生的xuehao查询任课教师select xuhao from fenshu where shuxue=(--查询shuxue分数的学生学号select MIN(shuxue) from fenshu --查询shuxue最低分)))----------查询汇总表，有分数，学生姓名，班级，及任课教师姓名。select xuesheng.name,banji,yuwen,shuxue,yingyu,(select name from jiaoshi where xuesheng.Cnjs=jiaoshi.code)语文教师,--根据教师code查询教师姓名(select name from jiaoshi where xuesheng.Majs=jiaoshi.code)数学教师,(select name from jiaoshi where xuesheng.Enjs=jiaoshi.code)英语教师 from xuesheng join fenshu on xuesheng.xuehao=fenshu.xuhao----------查询每个班里数学最高分--首先根据学号对应出两个表的关系，使得班级与分数连接起来select banji,shuxue from xuesheng join fenshu on xuesheng.xuehao=fenshu.xuhao--然后加入条件select banji,max(shuxue) as 数学最高分 from xuesheng join fenshu on xuesheng.xuehao=fenshu.xuhao group by banji--------另一种方法select max(shuxue),banji from (select shuxue,(select banji from xuesheng where xuesheng.xuehao=fenshu.xuhao)as banji from fenshu) as newtablegroup by banji----------查询语文课程的平均分最高的班级的任课教师的信息select *from jiaoshi where code=(--根据编号查询教师信息select top 1 Cnjs from xuesheng join fenshu on xuesheng.xuehao=fenshu.xuhao group by banji,Cnjs order by AVG(yuwen) desc--按班级分组语文平均成绩的最高分的教师编号)

-------------约束------------drop table yueshu create table yueshu(    xuehao int primary key,--xuehao列设置主键    name varchar(20),    age int,    sex varchar(10),    cid varchar(50) unique--创建表时加入唯一键约束)gocreate table yueshu01(    xuehao int primary key,--xuehao列设置主键    name varchar(20),    age int,    sex varchar(10),    cid varchar(50) )--已创建好表，再加入唯一键约束：（注意表的数据中不可有重复）create unique index index_123--给约束命名on yueshu01(cid)insert into yueshu values (1,‘张三‘,35,‘男‘,0010)insert into yueshu values (2,‘张四‘,36,‘男‘,0010)select *from yueshu