第九章 数据查询基础

          查询    

/****** 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