SQL数据查询

--1 获取MyStudents表中年龄最大的前5个人，和前20%
select top 5 * from Mystudent order by MysAge Desc
select top 20 percent * from Mystudent order by MysAge Desc

--2 获取当前时间
select GETDATE() as 当前时间
select CONVERT(char(8),getdate(),108)

--3 去掉重复distinct
select distinct * from Mystudent

--4 查询英语成绩最大，最小，总分和平均值
select MAX(MysEnglish) as 英语成绩 from Mystudent
select MIN(MysEnglish) as 英语成绩 from Mystudent
select SUM(MysEnglish) as 英语总分 from Mystudent
select AVG(MysEnglish) as 英语平均分 from Mystudent

--5 查询班集总人数
select distinct COUNT(*) as 总人数 from Mystudent

--6 查询数学成绩为null的人数（在数据库中进行设置n各人的数学成绩为null看效果）
--drop table Text2
create table Text2
(
TId int identity(1,1) primary key,
TName nvarchar(10),
TGender nchar(2),
TAge int,
TMath float,
TEnglish float,
TBirthday datetime
)
insert into Text2
select '姜玉阳','男',22,90,89,'1991-10-04' union
select '李小琳','女',20,70,85,'1993-09-12' union
select '高小丽','女',19,80.5,90,'1994-01-17' union
select '包小白','男',20,60,99,'1993-12-08' union
select '关阳','男',20,100,80.5,'1991-12-05' union
select '刘霞','女',18,70,85,'1995-11-12' union
select '吴双','女',19,100,98,'1994-09-09' union
select '路风','男',20,60,99,'1993-07-19' union
select '杨幂','女',21,null,99,'1992-04-20' union
select '董卿','女',20,null,80,'1993-05-26' union
select '步惊云','男',20,null,70,'1993-09-23' union
select '谢涛','男',21,null,100,'1992-08-11'

select count(*) from Text2 where TMath is null
--select * from Text2

--7 男学生出生日期的最大值和最小值（设计出生日期字段）
select MAX(TBirthday) as 出生日期最大,MIN (TBirthday) as 出生日期最小 from Text2 where TGender='男'