資料庫的一些操作（Sql）

      對于最近做的項目（教務系統），我想收獲最大之一就是對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)

當然這個項目用的最多的還是連表查詢，最多的時候接近10個表的連表查詢   項目快結束了，現在還在改bug，不過還是學到了很多，給大家分享一下，就當總結下