最簡單的查詢語法格式:
SELECT <列名>
FROM <表名>
[WHERE <查詢條件表達式>]
[ORDER BY <排序的列名> [ASC 或 DESC]]
·查詢所有的數據行和列:
SELECT * FROM Student
·查詢部分行列－－條件查詢
SELECT SCode,SName,SAddress
FROM Student
WHERE SAddress = '陜西咸陽'()
★<>為不等于符號
SELECT SCode,SName,SAddress
FROM Student
WHERE SAddress <> '陜西咸陽'
★合并的新列名
SELECT FirstName+'.'+LastName AS '姓名'
FROM Employees
或
SELECT '姓名' = FirstName+'.'+LastName
FROM Employees
·查詢空行
SELECT SName FROM Student WHERE SEmail IS NULL
·查詢中使用常量列
★查詢輸出多了一列"學校名稱"，該列的所有數據都是"陜西咸陽"
SELECT 姓名=SName,地址=SAddrees,'陜西咸陽' AS 學校名稱
·查詢販貨限制的行數
★TOP為限制行數的關鍵字
SELECT TOP 5 SName,SAddreess
FROM Student WHERE SSex=0
★百分比限制關鍵字PERCENT
SELECT TOP 20 PERCENT SName,SAddrees
FROM Student WHERE SSex = 0
·查詢排序
★降低10%再加5，按照及格成績排列
SELECT StudentID AD 學員編號，(Score*0.9+5) AS 綜合成績
FROM Score
WHERE (Score*0.9+5)>60
ORDER BY Score
★合并查到的所有姓名信息，然后按照姓名降序排列
SELECT Au_Lname + '.' + Au_Fname AS EMP FROM Author UNION
SELECT Fname + '.' + Lname AS EMP FROM Employee
ORDER BY EMP DESC
★多字段排序
SELECT StudentID AS 學員編號,Score AS 成績
FROM Score
WHERE Score > 60
ORDER BY Score,CourseID(這兩的字段有先后順序)
·查詢中使用函數
★更新信息,從表Card中把字段password中o改為0，i改為1
UPDATE Card SET password = REPLACE(密碼,'o','0')
UPDATE Card SET password = REPLACE(密碼,'i','1')
或
UPDATE Card SET password = REPLACE(REPLACE(密碼,'o','0'),'i','1')
★特殊排序
排序前:13-1,13-2,13-3,13-10,13-100,13-108,13-18,13-11,13-15,14-1,14-2
排序后:13-1,13-2,13-3,13-10,13-11,13-15,13-18,13-100,13-108,14-1,14-2
SELCT ListNumber
FROM SellRecord
ORDER BY Convert(int,Left(ListNumber,Charindex('-',ListNumber)-1)),
Convert(int,Stuff(ListNumber,1,Charindex('-',ListNumber),''))
·模糊查詢
★使用LIKE進行模糊查詢
SELECT * FROM Student WHERE SName LIKE '王%'
★使用BETWEEN在某個范圍內進行查詢
SELECT * FROM SCore WHERE Score BETWEEN 60 AND 80
★查詢不在1992年8月1號到1993年8月1號之間訂購的讀書列表
SELELCT * FROM Sales WHERE ord_date NOT BETWEEN '1992-8-1' AND '1993-8-1'
★使用IN在列舉值內進行查詢
SELECT SName AS 學員姓名 FROM Student WHERE SAddress IN ('北京','廣州','上海')
ORDER BY SAddress
·SQL Server 中的聚合函數
★SUM(只能返回一個數值)
SELECT SUM(ytd_sales) FROM tiles WHERE tyde = 'business'
★AVG
SELECT AVG(SCore) AS 平均成績 FROM Score WHERE Score >=60
★MAX、MIN
SELECT AVG(Score) AS 平均成績,MAX(Score) AS 最高分,MIN(Score) AS 最低分
FROM Score WHERE Score >=60
★COUNT(返回提供的表達式中非空值的計數，可以用數字和字符類型的列)
SELELCT COUNT(*) AS 及格人數 FROM Score WHERE Score >=60
·分組查詢
★使用GROUP BY進行分組查詢
SELECT CourseID,AVG(Score) AS 課程平均成績
FROM Score
GROUP BY CourseID
·多表連接查詢
★內連接
SELECT Student,SName,Score.CourseID,Score.Score
FROM Student,Score
WHERE Student.Score = Score.StudentID
或
SELECT S.SName,C.CourseID,C.Score
FROM Student AS S INNER JOIN Score AS C
ON (S.Score=C.StudentID)
★三表連接查詢
SELECT S.SName AS 學員姓名,CS.CourseName AS 課程名稱,C.Score AS 考試成績
FROM Student AS S
INNER JOIN Score AS C ON (S.Score=C.StudentID)
INNER JOIN Course AS CS ON (CS.CourseID=C.CourseID)
★左外連接查詢
SELECT S.SName,C.CourseID,C.Score
FROM Student AS S
LEFT OUTER JOIN Score AS C ON S.Score = C.StudentID
★右外連接查詢
SELECT Titles.Title_id,Titles.Title,Publishers.Pub_name
FROM titles
RIGHT OUTER JOIN Publishers ON Titles.Pub_id = Publishers.Pub_id
·案例分析
★★查詢一張表中的奇數和偶數行
1、只能依靠標識列的值來進行判斷和選取
2、數據行可能存在增加，修改和刪除，因此標識列的數據值并不完全可靠
3、SELECT
INTO創建一張新表，順便創建新的表示列，再在新的表示列上執行奇偶判斷
4、奇數的判斷依據為：標識列值%2不等與0；偶數的判斷依據為：標識列值%2等于0
5、刪除臨時表TEMPTABLE
SELECT A,TDENTITY(INT 1,1) AS ID
INTO TEMPTABLE
FROM TBL
SELECT SUM(A) AS 奇數列匯總 FROM TEMPTABLE
WHERE ID%2 <> 0
SELECT SUM(A) AS 偶數列匯總 FROM TEMPTABLE
WHERE ID%2 = 0posted on 2007-12-01 11:02
凌晨風 閱讀(893)
評論(0) 編輯 收藏 所屬分類:
Java學習筆記