第九章 上机练习 12345

--SELECT * FROM [MySchool].[dbo].[Student]
--Select StudentNo, LoginPwd, StudentName, Sex, GradeId, Phone, Address, BornDate, Email
--from Student
--where address='学生宿舍'


--查询所有一期信息
--SELECT * FROM [MySchool].[dbo].[Student]
--select StudentNo, LoginPwd, StudentName, Sex, GradeId, Phone, Address, BornDate, Email
--from Student
--Where GradeId=1

--二期全部 姓名 电话
--SELECT * FROM [MySchool].[dbo].[Student]
--Select StudentName,Phone
--from Student
--where GradeId=2

--查询女同学的信息
--SELECT * FROM [MySchool].[dbo].[Student]
--Select StudentNo, LoginPwd, StudentName, Sex, GradeId, Phone, Address, BornDate, Email
--from Student
--where Sex='女'and GradeId=1

--查询课时超过60的
--SELECT * FROM [MySchool].[dbo].[Student]
--select SubjectId, SubjectName, ClassHour, GradeId
--from Subject
--where ClassHour>60

--查询一期 科目名称
--SELECT * FROM [MySchool].[dbo].[Student]
--select SubjectName
--from Subject
--where  GradeId=1

--查询二期 姓名地址
--SELECT * FROM [MySchool].[dbo].[Student]
--select  StudentName, Address
--from Student 
--where GradeId=2

--查询 无电子邮件 姓名和年级
--SELECT * FROM [MySchool].[dbo].[Student]
--select  StudentName,GradeId
--from Student
--where Email is null

--1990年后出生的学生姓名
--SELECT * FROM [MySchool].[dbo].[Student]
--select  StudentName
--from Student 
--where GradeId=2 and BornDate>'1990'

--查询2013-9-13
--SELECT * FROM [MySchool].[dbo].[Student]
--select StudentResult
--from Result
--where ExamDate='2013-9-1'

--排序
--SELECT * FROM [MySchool].[dbo].[Student]
--select Id, StudentNo, SubjectId, StudentResult, ExamDate
--from Result
--order by id DESC 

--查询位置
--SELECT * FROM [MySchool].[dbo].[Student]
--select StudentNo, LoginPwd,CHARINDEX('7',LoginPwd,1) as 位置, StudentName, Sex, GradeId, Phone, Address, BornDate, Email
--from student
 
--查询长度
--SELECT * FROM [MySchool].[dbo].[Student]
--select StudentNo, LoginPwd,LEN(LoginPwd) as 长度, StudentName, Sex, GradeId, Phone, Address, BornDate, Email
--from student

--改成大写
--SELECT * FROM [MySchool].[dbo].[Student]
--select StudentNo, LoginPwd, StudentName, Sex, GradeId, Phone, Address, BornDate, Email,upper(email) as 大写
--from student

--在右变返回指定的字符
--SELECT * FROM [MySchool].[dbo].[Student]
--select StudentNo, LoginPwd, StudentName, Sex, GradeId, Phone, Address,RIGHT('天津市南开区',3)as 返回, BornDate, Email
--from student

--替换
--SELECT * FROM [MySchool].[dbo].[Student]
--select StudentNo, LoginPwd, StudentName,REPLACE('欧阳燕飞','飞','跑')as 替换, Sex, GradeId, Phone, Address, BornDate, Email
--from student

--删除替换
--SELECT * FROM [MySchool].[dbo].[Student]
--select StudentNo, LoginPwd,STUFF(LoginPwd,2,3,'屎') as 删除替换,StudentName, Sex, GradeId, Phone, Address, BornDate, Email
--from student

--获取当前日期
--SELECT * FROM [MySchool].[dbo].[Student]
--select StudentNo, LoginPwd,StudentName, Sex, GradeId,GETDATE()as 当前, Phone, Address, BornDate, Email
--from student

--添加日期
--SELECT * FROM [MySchool].[dbo].[Student]
--select StudentNo, LoginPwd,StudentName, Sex, GradeId,DATEADD(MM,4,'01/01/2009')as 添加, Phone, Address, BornDate, Email
--from student

--指定日期部分的间隔
--SELECT * FROM [MySchool].[dbo].[Student]
--select StudentNo, LoginPwd,datediff(DD,'1999-9-9',GETDATE())as 间隔,StudentName, Sex, GradeId, Phone, Address, BornDate, Email
--from student

--指定字符串形式
--SELECT * FROM [MySchool].[dbo].[Student]
--select StudentNo, LoginPwd,datename(WEEK,GETDATE())as 制定形式,StudentName, Sex, GradeId, Phone, Address, BornDate, Email
--from student

--制定日期部分的整数形式
--SELECT * FROM [MySchool].[dbo].[Student]
--select StudentNo, LoginPwd,datepart(mm,BornDate)as 制定部分,StudentName, Sex, GradeId, Phone, Address, BornDate, Email
--from student

--SELECT * FROM [MySchool].[dbo].[Student]
--  随机数      绝对值     向上取整     向下取整      幂值    四舍五入为制定精度  返回值  平方根
--select RAND(),ABS(-66),CEILING(43.5),FLOOR(43.5),POWER(5,2),ROUND(46.9,1),sign(-12),SQRT(12)

--SELECT * FROM [MySchool].[dbo].[Student]
--   转变数据类型                返回用户名     返回字节数               返回计算机名  返回用户名  给用户ID返回用户名       
--select CONVERT(varchar(5),12345),current_user,datalength('科比布莱恩特'),HOST_NAME(),SYSTEM_USER,user_name(1)

--按出生日期查询学生信息
--SELECT * FROM [MySchool].[dbo].[Student]
--select StudentNo, LoginPwd, StudentName, Sex, GradeId, Phone, Address, BornDate, Email
--from Student
--where GradeId=1
--order by  BornDate

--排序日期 成绩
--SELECT * FROM [MySchool].[dbo].[Student]
--select Id, StudentNo, SubjectId, StudentResult, ExamDate
--from result
--order by examdate,StudentResult desc

--前五名的成绩
--SELECT * FROM [MySchool].[dbo].[Student]
--select top 5 Id, StudentNo, SubjectId, StudentResult, ExamDate
--from Result
--where  ExamDate='2013-3-22'

--Y2课时最多的科目名称
--SELECT * FROM [MySchool].[dbo].[Student]
--select  SubjectName, ClassHour
--from Subject
--order by ClassHour desc

--查询年龄最小的学生姓名 所在年级
--SELECT * FROM [MySchool].[dbo].[Student]
--select top 1 StudentName,GradeId, BornDate
--from Student
--order by BornDate

--最低分出现在哪个科目
--SELECT * FROM [MySchool].[dbo].[Student]
--select   SubjectId, StudentResult
--from Result 
--order by StudentResult

--查学生考试 按时间排序
--SELECT * FROM [MySchool].[dbo].[Student]
--select Id, StudentNo, SubjectId, StudentResult, ExamDate
--from Result
--where StudentNo='S1101003'
--order by ExamDate 

--查询学生最高分 时间 科目
--SELECT * FROM [MySchool].[dbo].[Student]
--select Id, StudentNo, SubjectId, StudentResult, ExamDate
--from Result
--where  StudentNo='S1101003'
--order by  StudentResult desc

--超过20岁的学生信息
--SELECT * FROM [MySchool].[dbo].[Student]
--select StudentNo, LoginPwd, StudentName, Sex, GradeId, Phone, Address, BornDate, Email
--from Student
--where GradeId=2 and BornDate>'1996'

--一月份过生日的学员信息
--SELECT * FROM [MySchool].[dbo].[Student]
--select StudentNo, LoginPwd, StudentName, Sex, GradeId, Phone, Address, BornDate, Email
--from Student
--where DATEPART(MM,BornDate)=1

--查询域名
--SELECT * FROM [MySchool].[dbo].[Student]
--select StudentNo,Email,right(email,(len(email))-(CHARINDEX('@',Email,1)))
--from Student
--where StudentNo='Y21003007'

--生成E-mail地址
--SELECT * FROM [MySchool].[dbo].[Student]
--select 'S1'+CONVERT(varchar(4),DATEPART(YYYY,getdate()))+CONVERT(varchar(2),DATEPART(mm,getdate()))+CONVERT(varchar(2),DATEPART(dd,getdate()))+RIGHT (RAND(),4)+'@jbit.com'
--from Student