--联合结果集union(集合运算符--
select StudentNo as nonono,SUM(StudentResult) from Result where StudentNo=1 group by StudentNo
union
select StudentNo as no,aa='22' from Result where StudentNo=2
---union可以合并多个结果集
--它有两个前提和一个注意:
--1.合并的结果集的列数必须完全一致
--2.合并的多个结果集的对应列的类型需要一致(可以相互转换)
--3.结果集的列名只与第一个结果集有关
--
select top 3 * from Student
union --做了distinct操作
select top 3 * from Student
select top 3 * from Student
union all --不做distinct操作，它的合并效率更高，因为没有必须去判断结果记录是否重复
select top 3 * from Student
--要求在一个表格中查询出学生的英语最高成绩、最低成绩、平均成绩
select MAX(StudentResult) from Result
select MIN(StudentResult) from Result
select AVG(StudentResult) from Result
--
select MAX(StudentResult), MIN(StudentResult), AVG(StudentResult) from Result
--
select (select MAX(StudentResult) from Result),(select MIN(StudentResult) from Result),(select AVG(StudentResult) from Result)
select MAX(StudentResult) from Result
union
select MIN(StudentResult) from Result
union
select AVG(StudentResult) from Result
--查询每一个学员的成绩，同时在最下面显示平均分
union语句中不能添加order by排序，如果加只能加在最后，最后一句的order by只能去选择第一个结果集中的列
select cast(StudentNo as CHAR(2)) as id,StudentResult from Result
union
select '平均分',AVG(StudentResult) from Result order by id Desc
select ' '+cast(StudentNo as CHAR(2)) as id,StudentResult from Result
union
select ' 平均分',AVG(StudentResult) from Result
--数据检索--带条件查询
语法:
--select 字段列表/* from 表列表where 条件(not and or)
--1.查询所有学员信息
select * from Student
--2.查询所有女学员信息
select * from Student where Sex='女'
--3.多条件查询
select * from Student where Sex='女' and ClassId='6'
--4.指定查询的列
select StudentName,Sex,Phone,Address from Student where Sex='女' and ClassId='6'
select Student.StudentName,Student.Sex,Student.Phone,Student.Address from Student where Sex='女' and ClassId='6'
--5.指定列标题as可以指定列标题，但是它可以省略，这种操作纯粹是得到结果集之后，在视图中的另外一种显示方式，与查询无关
select StudentName as 姓名,Sex 性别,电话=Phone,Address from Student where Sex='女' and ClassId='6'
--6.添加常量列
select StudentName as 姓名,Sex 性别,电话=Phone,Address,'广州' as 城市 from Student where Sex='女' and ClassId='6'
--select有两种功能
1.查询
2.输出:以结果集的形式进行输出的
select 1
--
-使用top提取记录,top可以提取指定数量的记录，也可以使用百分比
--它不是四舍五入，而是Ceiling
--select CEILING(0.999)
select top 2 StudentName as 姓名,Sex 性别,电话=Phone,Address,'广州' as 城市 from Student where Sex='女' and ClassId='6'
select top 80 percent StudentName as 姓名,Sex 性别,电话=Phone,Address,'广州' as 城市 from Student where Sex='女' and ClassId='6'
--使用distinct来过滤重复记录.它所说的重复记录不是指表的原始记录,而是通过查询得到的结果集,只有查询的结果集的每一个字段值都一样,才认为是重复记录
select distinct LoginPwd,Sex from Student
--- 创建关系时的级联操作
alter table student
with nocheck --不检查现有数据
add constraint FK_Grade_Student_GradeId foreign key(gradeid) references grade(gradeid)
on delete set null
--[ ON DELETE { NO ACTION | CASCADE | SET NULL | SET DEFAULT } ]
--on--在做何种操作的时候做相应的处理
--NO ACTION--不做任何操作：该报错就报错，可以删除就删除
--CASCADE：级联：删除主表，对应的从表数据也会删除，更新也一样
--SET NULL：如果删除主表的记录，那么对应的从表记录的字段值会被设置为null,前提是从表的这个字段的值可以是null
--SET DEFAULT :删除主表记录，从表的记录的对应字段值设置为默认值，前提是你之前为这个字段设置了默认值
--聚合函数--
--max():求指定数据范围中的最大值:可以对任意类型进行聚合，如果是非数值么就按值的拼音进行排序
--min():求指定数据范围中的最小值:可以对任意类型进行聚合，如果是非数值么就按值的拼音进行排序
--avg:求指定数据范围中的平均值,它只能对数值进行聚合,不能对日期进行聚合
--sum:求指定数据范围中的和,它只能对数值进行聚合,不能对日期进行聚合
--count:求满足条件的记录数,与字段没有任何关系
select COUNT(*) from Student where ClassId=6
--查询年龄最大的学员 年龄值越大就越小
select min(BornDate) from Student
select max(BornDate) from Student
select SUM(StudentName) from Student
select min(StudentName) from Student
select max(StudentName) from Student
select max(BornDate) from Student
select avg(BornDate) from Student
--查询科目ID是的学员的总分
select SUM(StudentResult) from Result where SubjectId=1
--平均分
--在sql server中，null是指不知道是什么值。聚合函数会过滤掉null值
select avg(StudentResult*1.0) from Result where SubjectId=1 and StudentResult is not null
select * from Student order by StudentName
--类型转换函数
--+在使用时首先是运算符，系统会做隐藏的类型转换，如果可以转换那就OK，否则报错
select 1+'1'
--除非两边都是字符串类型，那么+号就是字符串连接符
select '1'+'1'
--cast(源数据as 目标类型)
print '我的总成绩是：'+cast(200 as varchar(30))
--Convert(目标类型，源数据,格式)
print '我的总成绩是：'+convert(char(3),200)
--为日期值添加格式
select CONVERT(char(30),GETDATE(),102)
模糊查询、
---带条件的查询---
--语法:select 字段列表/* from 表列表where 条件
select * from Student where ClassId=1 or ClassId=2 or ClassId=3
--使用in代表一个具体的值范围,in要求指定的范围的数据类型一致
select * from Student where ClassId in(1,2,3)
select * from Student where ClassId not in(1,2,3)
--所谓类型一致是指：系统会为你做强制类型转换，如果可以转换OK，否则报错
--在使用in的时候，指定的范围值需要与字段的值做相应的匹配：强制转换
select * from Student where ClassId in('1','2','3')
--查询考试成绩在`90之间的学员信息
select * from Result where StudentResult>=80 and StudentResult<=90
--如果是数值的范围判断可以使用between...and
select * from Result where StudentResult between 80 and 90
--带条件的查询-模糊查询:是对字符串而言
--必须明确：=就是严格的完全匹配
--%:代表任意个任意字符
--_:代表任意的单个字符
--[]:代表一个具体的值范围中的一个字符
--[^]:代表不在指定的范围之内,放在[]里面才有这个意义
--通配符有意义必须在模糊查询中：模糊查询的关键字是：like
select * from Student where StudentName like '张%' and Sex='女'
select * from Student where StudentName like '张__' and Sex='女'
--查询学号在~18之间的学员信息
select * from Student where StudentNo not like '[1-2]'
--如果放在范围值的中间没有意义了，只能放在开头
select * from Student where StudentNo like '[345^672]'
---isnull函数的使用：可以使用一个自定义的字符串替换null值
select StudentNo,StudentName,ISNULL(email,'没有电子邮箱') from Student where ClassId=6
--模糊查询练习：
--1.查询六期班所有姓王的学员
select classid from grade where classname='六期班'
select * from Student where StudentName like '王%' and ClassId=(select classid from grade where classname='六期班')
--2.查询所有科目中包含c 字符的科目信息--不区分大小写
select * from Subject where SubjectName like '%[Cc]%'
--3.查询office最近一次考试时间
select subjectid from Subject where SubjectName='office'
select max(ExamDate) from Result where SubjectId=(select subjectid from Subject where SubjectName='office')
--select 字段列表from 表列表where 条件order by 排序字段列表
--排序对查询得到的结果集做记录重排，并不是修改原始的查询结果集
--desc:降序排序
--asc:升序排序、默认就是升序排序
--top是order by 之后再取值
select top 1 ExamDate 考试日期,StudentNo from Result where SubjectId=(select subjectid from Subject where SubjectName='office') order by 考试日期 desc,StudentNo asc
--数据删除--
--语法:
--delete [from] 表名where 条件
delete from Student where StudentNo=4 or StudentNo=5 or StudentNo=6
--使用delete进行删除的特点：
--1.它是一条一条进行删除的，每一次的删除都会写入到日志文件，效率不高
--2.标识列值不会重新从标识种子计算
--使用truncate进行删除
--语法：
--truncate table 表名 --没有条件，
--1.它不是一条篥进行删除的，它是一次性整体删除，与删除的记录数无关
--2.它的日志文件的写入是按最小化折方式进行写入--一次
--3.标识列会重新从标识种子计算
truncate table student
---数据分组-统计信息-----
--select 字段列表from 表列表 where 对数据源进行数据筛选group by 分组字段列表Order by 排序字段列表
--1.得到所有学员总人数
select COUNT(*) from Student
--2.得到男女生的人数
select COUNT(*) from Student where Sex='男'
select COUNT(*) from Student where Sex='女'
--使用分组统计
select COUNT(*),sex from Student group by sex
select COUNT(*),sex from Student
select COUNT(*) from Student
select distinct sex from Student
--查询每个班级的人数
--分组统计有一个规则:与聚合函数一起出现在查询中的列，要么被聚合，要么被分组
--1.聚合不应出现在WHERE 子句中,语法规则
--2.where 的执行在分组之前，先对源数据做筛选之后再对筛选得到的结果集做分组
--3.having是对分组统计得到的结果集做筛选的。
--select 字段列表from 表列表 where 源数据筛选条件 group by 分组统计字段列表having 对分组统计结果集做筛选order by 得到最终结果集之后的数据重排
select classid ,COUNT(*) from Student group by ClassId having ClassId=6
select classid 班级,COUNT(*) 人数,StudentNo from Student group by ClassId having StudentNo>10
-- 5 显示 1获取数据源 2 筛选原 3 对数据源进行分组 4 对分组统计的结果集做筛选 6对最终结果集做数据重排
select classid 班级,COUNT(*) 人数 from Student where Email is not null group by ClassId having COUNT(*) between 2 and 3 order by 人数 desc
select StudentNo no,StudentName name from Student where StudentName like '%'
select COUNT(*) 人数 from Student where Email is not null having COUNT(*)>=3 order by 人数 desc
select classid, sex,COUNT(*) from Student group by ClassId,Sex order by ClassId,sex
--分组统计练习:
--1.查询每个班级的总学时数，并按照升序排列
select classid, SUM(ClassHour) from Subject where ClassId is not null group by ClassId order by SUM(ClassHour)
--2.查询每个参加考试的学员的平均分
select studentNo, AVG(StudentResult) from Result where StudentResult is not null group by StudentNo --having StudentResult is not null
--3.查询每门课程的平均分，并按照降序排列
select SubjectId, AVG(StudentResult) as score from Result where StudentResult is not null group by SubjectId order by score desc
--4.查询每个班级男女生的人数
select ClassId,Sex, COUNT(*) from Student group by ClassId,Sex
--数据更新-- 做修改和删除对于程序员而言一定需要看有没有条件
--语法：
--update 表名set 字段=新值,字段=新值where 条件(主键一般就可以做为条件)
update Student set GradeId=1
--修改学号为的学员班级是班
update Student set GradeId=3 where StudentNo=9
update Student set Gender=0,GradeId=3 where StudentNo=4
--多条件修改
update Student set Address='广州传智' where GradeId=1 and Gender=0