GREET 数据库

转载

mob64ca14048514 2024-09-12 11:49:42

文章标签 GREET 数据库 datediff 获取年月日获取时间左查询 文章分类 云原生云计算

对于最近做的项目（教务系统），我想收获最大之一就是对sql语句的学习吧！这里，我总结一下我在项目中用到的一些SQL语句：

1，获取时间：getdate() 方法convert(varchar(10),getdate(),120)是获取当前时间的一个格式化方法，其中varchar(10)定义的是你要的字段的长度，当然长度的不同返回的也会不同的，如果我们只要日期部分，设成10正好为日期长度，如果设成19则正好可以读到时间部分。最后那个参数是返回不同的格式

参数	结果
100	05 8 2006 9:27PM
101	05/08/2006
102	2006.05.08
103	08/05/2006
104	08.05.2006
105	08-05-2006
106	08 05 2006
107	05 08, 2006
108	21:30:51
109	05 8 2006 9:31:11
110	05-08-2006
111	2006/05/08
112	20060508
113	08 05 2006 21:31:59
114	21:33:06:503
120	2006-05-08 21:33:38

2，获取当前时间是星期几：

set datefirst 1 SELECT datepart(weekday, getdate())

其中 set datefirst 1设置开始算的时间 1代表星期一这里返回的是数字

3.获取年月日：

年：select year(getdate()) 返回当前年份比如2014

月：select month(getdate()) 返回当前月份比如 3

日：select day(getdate()) 返回当前日期比如 30 这三个函数参数都是传入的是时间我这里是当前时间为例用时可以灵活运用

4.函数 datediff(参数一，时间2，时间2) 这个函数是计算两个时间的差参数一以什么单位计算常用的一般是天（day）月（month）年（year）。。。当然还有其他参数，有兴趣可以百度百度如 select datediff (day,'20014-3-29','2014-3-30') 返回的是 1

下面看几个例子

【1】 SELECT (SELECT
CAST (
(datediff(DAY ,(SELECT sem.StartTime
FROM
Semester AS sem
WHERE
sem.StartTime < convert(varchar(10),getdate(),120) AND
sem.EndTime > convert(varchar(10),getdate(),120) ), getdate())) AS INT
)+7)/7 as week

说明：这个sql语句业务是：在学期表中找出以当前学期的开始时间和当前时间的差算出当前是第几周返回数字如 5 表示第五周

【2】SELECT
sem.SemesterName,
sch.SchoolYearName,
sem.SchoolYearID,
sem.SemesterID
FROM
Semester AS sem ,
SchoolYear AS sch
WHERE
sem.StartTime < convert(varchar(10),getdate(),120) AND
sem.EndTime > convert(varchar(10),getdate(),120) AND
sem.SchoolYearID = sch.SchoolYearID

说明：这个sql业务很简单就是查找学年学期表当前的学期学年

【3】 set datefirst 1
SELECT DISTINCT cla.ClassroomName,clad.StartWeeks,clad.EndWeeks,clad.[Section],clad.Location,clad.Week,tea.TeacherName
FROM Classroom AS cla ,ClassroomDetail AS clad ,Semester AS sem ,SchoolYear AS sch ,CourseChooseDetail AS chod ,CourseChoose AS cho ,Teacher AS tea ,Student AS stu,Operator AS opr
WHERE
(SELECT datepart(weekday, getdate())) = clad.Week AND
clad.StartWeeks < (SELECT CAST ((datediff(DAY,(SELECT sem.StartTime FROM Semester AS sem,SchoolYear AS sch WHERE sem.StartTime < CONVERT (VARCHAR(10), getdate(), 120) AND sem.EndTime >CONVERT (VARCHAR(10), getdate(), 120) AND sem.SchoolYearID = sch.SchoolYearID),getdate())) AS INT) + 7) / 7 AND
clad.EndWeeks > (SELECT CAST ((datediff(DAY,(SELECT sem.StartTime FROM Semester AS sem,SchoolYear AS sch WHERE sem.StartTime < CONVERT (VARCHAR(10), getdate(), 120) AND sem.EndTime >CONVERT (VARCHAR(10), getdate(), 120) AND sem.SchoolYearID = sch.SchoolYearID),getdate())) AS INT) + 7) / 7 AND
cla.SchoolYearID = sch.SchoolYearID AND
sem.SemesterID = cla.SemesterID AND
cla.ClassroomID = cho.ClassroomID AND
clad.TeacherID = tea.TeacherID AND
opr.OperatorID = stu.OperatorID AND
stu.StudentID = chod.StudentID AND
opr.OperatorID = :operatorID AND
chod.CourseChooseID = chod.CourseChooseID

说明：这个sql业务是：先找到当前是当前学期的第几周和是星期几然后再找到满足当前周次大于课堂详情表开始周，小于结束周然后连表查询几个表传入参数就只有OperatorID一个主要是找学生的今日课表

5.左连接和右连接：左右查询主要是连表查询时有一边的值为空时用，当有一边为空数据时，也会返回这一列

左：FROM 表一 LEFT JOIN 表二 ON 列1=列2 右类同

【4】SELECT stu.StudentID,res.ReStudyID,cla.ClassroomName,stu.GradeMajorAssignID,stu.StudentName, stu.PassportCode, stu.StudentCode,
    maj.MajorName,maj.MajorID,cou.CourseID,cou.CourseName,res.isPaid,res.isPass FROM Student stu,
    Major maj ,Course cou,Classroom cla2,(ReStudy res LEFT JOIN Classroom cla ON cla.ClassroomID = res.ClassroomID) WHERE res.StudentID = stu.StudentID AND res.MajorID = maj.MajorID AND res.PreClassroomID = cla2.ClassroomID AND stu.StudentName like :StudentName
    AND stu.PassportCode like :PassportCode AND stu.StudentCode like :StudentCode AND
    maj.MajorID =:MajorID

说明：这个sql主要是左查询重修表中的课堂ID 当课堂表的课堂ID为空时还是要返回当前这一列

6.当一个表有两个字段都是连同一个id表时，可以直接在from后面多加一次用as重新名一个名字即可

7.case... when语句在sql中的运用

SELECT
sch.SchoolYearName,
sch.StartYear

FROM
dbo.SchoolYear AS sch
WHERE (sch.StartYear =case (select MONTH(GETDATE()))
             when 1 then (select year(GETDATE())-1)
             when 2 then (select year(GETDATE())-1)
             when 3 then (select year(GETDATE())-1)
             when 4 then (select year(GETDATE())-1)
             when 5 then (select year(GETDATE())-1)
             when 6 then (select year(GETDATE())-1)
             when 7 then (select year(GETDATE())-1)
             when 8 then (select year(GETDATE())-1)
             when 9 then (select year(GETDATE()))
             when 10 then (select year(GETDATE()))
             when 11 then (select year(GETDATE()))
             when 12 then (select year(GETDATE()))
end)