第九章 数据查询基础

          查询    

/****** Script for SelectTopNRows command from SSMS  ******/SELECT TOP 1000 [StudentNo]      ,[LoginPwd]      ,[StudentName]      ,[Sex]      ,[GradeId]      ,[Phone]      ,[Address]      ,[BornDate]      ,[Email]  FROM [MySchool].[dbo].[Student]    --查询所有的数据行和列  SELECT * FROM dbo.Student    查询部分行或列  SELECT StudentNo ,StudentName ,GradeId FROM dbo.Student WHERE Sex='女' AND GradeId=1  --查询中使用列的别名 SELECT StudentNo AS 学号,StudentName AS 姓名, GradeId AS 年级 FROM dbo.Student  WHERE Sex='男' AND GradeId=2   --查询空值(采用 "IS NULL"或"IS NOT NULL"来判断是否为空)  SELECT 姓名=StudentName,性别=Sex,年级=GradeId,Email FROM dbo.Student WHERE  Email IS NULL --------------  SELECT 姓名=StudentName,性别=Sex,年级=GradeId,Email FROM dbo.Student WHERE  Email IS NOT NULL       --查询中使用常量列(将常量的默认值添加到查询输出中)   SELECT '北大青鸟' AS 学校,StudentNo AS 学号,StudentName AS 姓名,Sex AS 性别,GradeId AS 年级,Phone AS 联系方式, Email FROM dbo.Student  -- 查询返回限制的行数 (查询部分前十列,使用TOP关键字约束) SELECT TOP 10 StudentNo,StudentName,Sex,GradeId FROM dbo.Student WHERE GradeId=1   --查询返回限制的行数 (查询百分比提取记录,使用PERCENT关键字来限制) SELECT TOP 20 PERCENT StudentNo AS 学号,StudentName AS 姓名,Sex AS 性别,GradeId AS 年级 FROM dbo.Student WHERE Sex='男'  --(1990年后出生的学生姓名) SELECT * FROM dbo.Student WHERE BornDate>1990       -------(字符串函数)  --查询所在位置 SELECT StudentNo AS 学号,StudentName AS 姓名, GradeId AS 年级,Phone AS 联系方式 ,CHARINDEX('6',Phone,1)as 位置,Address AS 地址   FROM dbo.Student  WHERE Sex='男' AND GradeId=2   --查询长度SELECT StudentNo AS 学号,StudentName AS 姓名, GradeId AS 年级,Phone AS 联系方式 ,Address AS 地址,LEN(Address)AS 长度  FROM dbo.Student  WHERE Sex='男' AND GradeId=2      --转换成大写  SELECT StudentNo AS 学号,StudentName AS 姓名, GradeId AS 年级,Phone AS 联系方式 ,Address AS 地址,Email,UPPER(Email)AS 大写Email  FROM dbo.Student  WHERE Sex='男' AND GradeId=2      --清除字符左边的空格                           --添加空格  SELECT StudentNo AS 学号,' '+StudentName+' ' AS 姓名, GradeId AS 年级,Phone AS 联系方式 ,Address AS 地址,Email  FROM dbo.Student  WHERE Sex='男' AND GradeId=2  --左边的空格  SELECT StudentNo AS 学号,' '+StudentName+' ' AS 姓名,LTRIM(StudentName), GradeId AS 年级,Phone AS 联系方式 ,Address AS 地址,Email  FROM dbo.Student  WHERE Sex='男' AND GradeId=2  --右边的空格  SELECT StudentNo AS 学号,' '+StudentName+' ' AS 姓名,RTRIM(StudentName), GradeId AS 年级,Phone AS 联系方式 ,Address AS 地址,Email  FROM dbo.Student  WHERE Sex='男' AND GradeId=2      --从字符串右边返回指定数目的字符  SELECT StudentNo AS 学号,StudentName AS 姓名, GradeId AS 年级,Phone AS 联系方式 ,RIGHT(Phone,4)AS 右边四位数, Address AS 地址,Email  FROM dbo.Student  WHERE Sex='男' AND GradeId=2   --从字符串左边返回指定数目的字符  SELECT StudentNo AS 学号,StudentName AS 姓名, GradeId AS 年级,Phone AS 联系方式 ,left(Phone,4)AS 左边四位数, Address AS 地址,Email  FROM dbo.Student  WHERE Sex='男' AND GradeId=2      --替换一个字符串中的字符   SELECT StudentNo AS 学号,StudentName AS 姓名, GradeId AS 年级,Phone AS 联系方式 , Address AS 地址,REPLACE(Address,'学生','优秀')AS 替换后地址, Email  FROM dbo.Student  WHERE Sex='男' AND GradeId=2      --删除指定长度的字符,并在该位置插入新的字符串  SELECT StudentName AS 姓名,Phone AS 联系方式,STUFF(Phone,3,5,'***')AS 插入后,Address AS 地址 FROM dbo.Student               -------(日期函数)  --yy,yyyy(年)  mm,m(月)  wk,ww(周)  dw,w(日期) mi,n(分) ss,s(秒)   --取得当前系统日期  SELECT StudentNo,StudentName,Address,BornDate,GETDATE()AS 当前日期,Email FROM dbo.Student       --将指定的数值添加到指定日期(月份)  SELECT StudentNo,StudentName,Address,BornDate,DATEADD(MM,4,BornDate )AS 添加后日期,Email FROM dbo.Student       --指定日期部分的间隔(月份)  SELECT StudentNo,StudentName,Address,BornDate,DATEDIFF(MM,'1995-9-9',BornDate)AS 日期间隔,Email FROM dbo.Student          --指定日期部分字符串形式(weekday)   SELECT StudentNo,StudentName,Address,BornDate,DATENAME(DW,BornDate)AS 字符串形式,Email FROM dbo.Student          --指定日期部分的整数形式   --(整数--年)   SELECT StudentNo,StudentName,Address,BornDate,DATEPART(year,BornDate)AS 整数型式,Email FROM dbo.Student    --(整数--月)   SELECT StudentNo,StudentName,Address,BornDate,DATEPART(month,BornDate)AS 整数型式,Email FROM dbo.Student    --(整数--日)   SELECT StudentNo,StudentName,Address,BornDate,DATEPART(DAY,BornDate)AS 整数型式,Email FROM dbo.Student               --------数学函数    --返回从0到1之间的随机float值  SELECT RAND()     --取数值表达式的绝对值  SELECT ABS(-78),ABS(-63)           --取整数        幂值           指定精度                    平方根       SELECT CEILING(55.6), POWER(5,2), ROUND(45.321,1),ROUND(23.212,2),SQRT(9)    --正数返回+1,负数返回-1,0则返回0  SELECT SIGN(-45),SIGN(23)               ------系统函数        --转变数据类型               返回当前用户姓名      字节数            计算机名字   所登录用户名  用户ID返回用户名  SELECT CONVERT(VARCHAR(5),12345), CURRENT_USER ,  DATALENGTH('滴滴答答'),HOST_NAME(), SYSTEM_USER,  USER_NAME(1)                 -- (E-maill中@的位置)  SELECT Email,CHARINDEX('@',Email,1 ) FROM dbo.Student   --(E-maill的长度)  SELECT Email,LEN(Email) FROM dbo.Student   

 

 

 

   排序

/****** Script for SelectTopNRows command from SSMS  ******/SELECT TOP 1000 [Id]      ,[StudentNo]      ,[SubjectId]      ,[StudentResult]      ,[ExamDate]  FROM [MySchool].[dbo].[Result]  --查看第一学期所有学生信息  SELECT  * FROM [MySchool].[dbo].[Result] WHERE SubjectId=1      --查看第二学期所有学生的信息  SELECT * FROM [MySchool].[dbo].[Result] WHERE SubjectId=2      --查看第三学期所有学生的信息  SELECT * FROM dbo.Result WHERE SubjectId=3   --查看StudentResult超过60的信息 SELECT * FROM dbo.Result WHERE StudentResult>60  --查询排序(使用ORDER BY进行排序   ASC:"升序--[默认]"  DESC:"降序") --ExamDate(升序输出) SELECT * FROM dbo.Result  ORDER BY  ExamDate    --StudentResult(降序输出) SELECT * FROM dbo.Result ORDER BY StudentResult DESC    --按日期,成绩,编号为1的考试科目信息  SELECT ExamDate,StudentResult,SubjectId  FROM dbo.Result WHERE SubjectId=1  ORDER BY ExamDate , StudentResult DESC      --前五名学生成绩  SELECT TOP 5 StudentResult FROM dbo.Result  WHERE ExamDate='2013-3-22'    --查询学号"S1101003"考试信息,先后顺序显示  SELECT StudentNo,StudentResult,ExamDate FROM dbo.Result WHERE StudentNo='s1101003'  ORDER BY ExamDate