1、三个关联表的查询
use 新建
create table teacher
(
tcode int primary key,
lesson char(10),
age int,
birth datetime
)
go
create table student
(
xcode int primary key,
name varchar(50),
sex char(10),
banji char(10),
yujiao int,
shujiao int,
yingjiao int
)
go
create table score
(
fcode int ,
yufen decimal(18,2),
shufen decimal(18,2),
yingfen decimal(18,2)
)
go
insert into student values(1,'张三','男','一班',1001,1101,1111)
insert into student values(2,'李四','女','一班',1001,1101,1111)
insert into student values(3,'王五','男','一班',1001,1101,1111)
insert into student values(4,'李晨','女','一班',1001,1101,1111)
insert into student values(5,'范爷','男','一班',1001,1101,1111)
insert into student values(6,'邓超','女','二班',1002,1102,1112)
insert into student values(7,'孙俪','男','二班',1002,1102,1112)
insert into student values(8,'李冰冰','女','二班',1002,1102,1112)
insert into student values(9,'nimab','男','二班',1002,1102,1112)
insert into student values(10,'康熙','女','二班',1002,1102,1112)
insert into student values(11,'王凯','男','三班',1003,1103,1113)
insert into student values(12,'姚晨','女','三班',1003,1103,1113)
insert into student values(13,'登s','女','三班',1003,1103,1113)
insert into student values(14,'雍正','男','三班',1003,1103,1113)
insert into student values(15,'baby','女','三班',1003,1103,1113)
insert into teacher values(1001,'一班语文',34,1987-3-23)
insert into teacher values(1002,'二班语文',23,1997-3-23)
insert into teacher values(1003,'三班语文',56,1984-3-23)
insert into teacher values(1101,'一班数学',35,1988-3-23)
insert into teacher values(1102,'二班数学',34,1987-3-23)
insert into teacher values(1103,'三班数学',38,1986-3-23)
insert into teacher values(1111,'一班英语',26,1990-3-23)
insert into teacher values(1112,'二班英语',29,1999-3-23)
insert into teacher values(1113,'三班英语',40,1979-3-23)
insert into score values (1,87,98,59)
insert into score values(2,98.1,67.4,67.9)
insert into score values(3,87.3,67.8,79.9)
insert into score values(4,23,45,78)
insert into score values(5,46,76,57)
insert into score values(6,98,70,60)
insert into score values(7,36,54,45)
insert into score values(8,23,38,82)
insert into score values(9,24,56,70)
insert into score values(10,69,80,78)
insert into score values(11,56,68,92)
insert into score values(12,34,46,68)
insert into score values(13,16,47,83)
insert into score values(14,37,59,71)
insert into score values(15,20,35,74)
--查询此次语文成绩最高的学生的信息
select*from student where xcode=(select top 1fcode from score order by yufen desc)
--查询此次语文成绩最低的
select*from student where xcode=(select top 1fcode from score order by yufen)
--查询此次语文成绩最低的学生所任课教师的信息
select*from teacher where tcode=(select yujiao from student where xcode=(select top 1 fcode from score order by yufen))
--查询此次数学成绩最低的学生所任课教师的信息
select*from teacher where tcode=(select shujiao from student where xcode=(select top 1 fcode from score order by shufen))
--查询此次语文成绩最高的学生所任课教师的信息
select*from teacher where tcode=(select yujiao from student where xcode=(select top 1 fcode from score order by yufen desc))
--查询此次数学成绩最高的学生所任课教师的信息
select*from teacher where tcode=(select shujiao from student where xcode=(select top 1 fcode from score order by shufen desc))
--查询学生信息，将所有语文任课教师编号改为该科目的任课教师名字显示
select student.name,banji,sex,age,teacher.name as 语文老师 from student join teacher on student.yujiao=teacher.tcode
--查询学生信息，将所有任课教师编号改为该科目的任课教师名字显示
select student.name,banji,sex,(select name from teacher where tcode=yujiao),(select name from teacher where tcode=student.shujiao),(select name from teacher where tcode=student.yingjiao)from student
--查询各个学生的学号,姓名,语文分数,数学分数,英语分数,以及三门课里面每一门课的任课教师姓名
select student.name,banji,sex,score.yufen,shufen,yingfen,(select name from teacher where tcode=yujiao),
(select name from teacher where tcode=student.shujiao),(select name from teacher where tcode=student.yingjiao)
from student join score on student.xcode=score.fcode
--查询每个班级里的语文最高分
select banji, MAX(yufen)from student join score on student.xcode=score.fcode group by banji
--查看每个班的语文平均分
select banji,AVG(yufen)from student join score on student.xcode=score.fcode group by banji
--查询语文课程平均分最高的班级的语文教师的信息
select*from teacher where tcode=(select distinct yujiao from student where banji=(select top 1banji from student join score on student.xcode=score.fcode group by banji order by AVG(yufen)desc))
--查询数学课程平均分最高的班级的数学教师的信息
select*from teacher where tcode=(select distinct shujiao from student where banji=(select top 1banji from student join score on student.xcode=score.fcode group by banji order by AVG(shufen)desc))
2、局部变量
--查询数学分数最高的同学的信息
--利用变量查询
select*from student where xcode=(select top 1 fcode from score order by shufen desc)
declare @fcode1 int
select top 1@fcode1=fcode from score order by shufen desc
select*from student where xcode=@fcode1
--查询此次语文成绩最低的学生所任课教师的信息
--变量查询
select*from teacher where tcode=(select distinct yujiao from student where xcode=(select top 1fcode from score order by yufen))
declare @fcode2 int
select top 1@fcode2=fcode from score order by yufen
declare @yujiao1 int
select distinct @yujiao1=yujiao from student where xcode=@fcode2
select*from teacher where tcode=@yujiao1
--查询英语课程平均分最高的班级的英语教师的信息
--变量查询
select*from teacher where tcode=(select distinct yingjiao from student where banji=(select top 1banji from student join score on student.xcode=score.fcode group by banji order by AVG(yingfen)desc))
declare @banji varchar(50)
select top 1@banji=banji from student join score on student.xcode=score.fcode group by banji order by AVG(yingfen)desc
declare @yingjiao int
select distinct @yingjiao=yingjiao from student where banji=@banji
select*from teacher where tcode=@yingjiao
3、常用全局变量
--常用全局变量 @@****
--@@connections 连接次数
print @@connections -- 打印出我自从启动sql之后进行了多少次的数据库连接
--@@error 错误
insert into score values (33,44,55,66)
print @@error --执行上一个sql语句是有没有错误,没有错误返回0
--@@language 语言
print @@language --当前使用的语言
--@@rowcount row行 count数量
print @@rowcount --返回上一次执行sql语句时在表中影响的行数
--@@version 版本
print @@version --所使用的SQL的版本信息
use Student
go
--更改部门职责，查看影响行数
update bumen set bzhi='负责本公司的产品检验'
select '总共影响了'+ STR(@@ROWCOUNT) +'行' --STR转换出来有左边的空格
select '总共影响了'+ CONVERT(varchar(10),@@ROWCOUNT) +'行' --convert转换
select '总共影响了'+ CAST(@@ROWCOUNT as varchar(10)) +'行' --cast转换
4、运算符优先级
--运算符 算数运算符
--+-*/%
--关系运算符
--> < >= <= != !> !<
--逻辑运算符
--and or any between …and exists in like not
--运算符的优先级
--第一级 */%
--第二级 正负号（+）（-）
--第三级 + -
--第四级 > < >= <= != !> !<
--第五级 not
--第六级 and or between and
--第七级 all any in like some exists
--第八级 =等号
5、if 语句
--if 表达式
--begin
-- 语句
--end
--else
--begin
--语句
--end
--查看邓凯所教学生有多少及格的
select COUNT(*)from score where fcode in(select xcode from student where yujiao=(select tcode from teacher where name='邓凯')and yufen >60
)
--查看数学分数最高的学生的性别,
--若是男,[这是一个男生]
--否则,[这是一个女生]
declare @tname varchar(50)
declare @tage int
select top 1 @tname=name,@tage=age from teacher order by age desc
if @tage>=45
print @tname+'再干几年就退休了'
else if @tage<=30
print @tname+'你还年轻'
else
print @tname+'正当年'