- select * from Classinfo
- select * from StuInfo
- select * from CourseInfo
- select * from ScoreInfo
-- 分组 group by, 分组后在结果列中只能出现分组依据列和聚合列
-- 统计男女人数
- select stuSexy,COUNT(*)
- from StuInfo
group by stuSexy-- 分组也是一个聚合过程, 把所有性别相同的元组放到了同一行
-- 算出每门课的平均分
- select cId,avg(score)
- from ScoreInfo
group by cId -- 根据科目进行分组, 算出平均分
- select cId,avg(score)
- from ScoreInfo
group by cId,StuId -- 按多个属性进行分组
-- 求每个班的男女人数, 同时输出班级名
- select ci.className,si.stuSexy,COUNT(*)
- from StuInfo as si
- inner join Classinfo as ci on si.classId=ci.classId
group by ci.className,si.stuSexy -- 除了这两个分组关键属性, 其他属性都被合并了, 所以不能再使用
- select ci.classId,si.stuSexy,COUNT(*)
- from StuInfo as si
- inner join Classinfo as ci on si.classId=ci.classId
group by ci.classId,si.stuSexy -- 只能使用 ci.classId 和 si.stuSexy 了
-- 统计学生编号 > 2 的各班级的各性别的学生人数
- select ci.className,si.stuSexy,COUNT(*)
- from StuInfo as si
- inner join Classinfo as ci on si.classId=ci.classId
- where si.stuId>2
- group by si.stuSexy,ci.className
-- 统计学生编号 > 2 的编号为 1 的班级的各性别的学生人数
- select ci.className,si.stuSexy,COUNT(*)
- from StuInfo as si
- inner join Classinfo as ci on si.classId=ci.classId
- where si.stuId>2 and ci.classId=1
- group by si.stuSexy,ci.className
--having 查询
-- 统计学生编号 > 2 的编号各班级的各性别的学生人数 > 1 的信息
- select ci.className,si.stuSexy,COUNT(*)
- from StuInfo as si
- inner join Classinfo as ci on si.classId=ci.classId
- where si.stuId>2
group by si.stuSexy,ci.className having count(*)>1 -- 对 count 增加条件
- select * from Classinfo
- select * from StuInfo
- select * from CourseInfo
- select * from ScoreInfo
-- 联合查询: 将多个查询的结果集合合并成一个集合 union
-- 要求: 1, 结果集列数要一致 2, 对应列的类型要一致
--union,union all,except,intersect 并, 直接相加, 差, 交
- select stuId from StuInfo
- union
- select stuid from ScoreInfo
- select stuId from StuInfo
- union all
- select stuid from ScoreInfo
- select stuId from StuInfo
- except
- select stuid from ScoreInfo
- select stuId from StuInfo
- intersect
- select stuid from ScoreInfo
-- 快速备份
-- 向一个不存在的表中快速插入数据: 包括插入属性行
select * into test1 from Classinfo
-- 新建一个空表, 但是包含属性列
- select * into test2 from Classinfo
- where 1=2
-- 向一个存在的表中插入数据
insert into test2(className)-- 因为 classId 是自增的所以不能从外界添加, 所以只能添加 className
select className from Classinfo
-- 格式转化函数 CAST,CONVERT
- select CAST(89.000000 as decimal(4,1))
- select CONVERT(decimal(4,1),89.000000)
- select CAST(1 as CHAR(1))+'1'
- select char(65)
- select ASCII('A')
- select left('ABCDEF',5)
- select right('ABCDEF',5)
- select substring('ABCDEF',1,5)
- select lower('ABCDEF')
- select upper('ABCDEFa')
select ltrim('ab c')-- 去掉左侧空格
- select RTRIM('abc')
- select STR(1)
- select GETDATE()
- select dateAdd(YY,1,GETDATE())
- select dateAdd(MM,1,GETDATE())
- select dateAdd(DD,1,GETDATE())
select DATEPART("Dayofyear",GETDATE())-- 求出一年中第几天
select DATEPART("year",GETDATE())-- 返回 int 型
- select DATEPART("month",GETDATE())
- select DATEPART("day",GETDATE())
select dateDiff(DD,dateAdd(YY,1,GETDATE()),GETDATE())-- 日期差
- select STR(DATEPART(YY,stuBirthday))+'-'+ltrim(STR(DATEPART(MM,stuBirthday)))+'-'+ltrim(STR(DATEPART(DD,stuBirthday)))
- from StuInfo
- select * from Classinfo
- select * from StuInfo
- select * from CourseInfo
- select * from ScoreInfo
-- 查询科目名称, 平均分
- select ci.cName,AVG(sco.score)
- from ScoreInfo as sco
- inner join CourseInfo as ci on sco.scoreId=ci.cId
- group by ci.cName
-- 查询班级名称, 一个班平均分
- -- sco-stu-class
- select ci.className,AVG(sco.score)
- from ScoreInfo as sco
- inner join StuInfo as si on sco.StuId=si.stuId
- inner join Classinfo as ci on si.classId=ci.classId
- group by ci.className
-- 查询查询班级名称, 科目名称, 平均分
- -- course-sco-stu-class
- select ci.className,cour.cName,AVG(sco.score)
- from ScoreInfo as sco
- inner join StuInfo as si on si.stuId=sco.StuId
- inner join Classinfo as ci on si.classId=ci.classId
- inner join CourseInfo as cour on sco.cId=cour.cId
- group by ci.className,cour.cName
- 1
来源: http://www.bubuko.com/infodetail-3280475.html