最簡(jiǎn)單的查詢語(yǔ)法格式:
SELECT <列名>
FROM <表名>
[WHERE <查詢條件表達(dá)式>]
[ORDER BY <排序的列名> [ASC 或 DESC]]
·查詢所有的數(shù)據(jù)行和列:
SELECT * FROM Student
·查詢部分行列－－條件查詢
SELECT SCode,SName,SAddress
FROM Student
WHERE SAddress = '陜西咸陽(yáng)'()
★<>為不等于符號(hào)
SELECT SCode,SName,SAddress
FROM Student
WHERE SAddress <> '陜西咸陽(yáng)'
★合并的新列名
SELECT FirstName+'.'+LastName AS '姓名'
FROM Employees
或
SELECT '姓名' = FirstName+'.'+LastName
FROM Employees
·查詢空行
SELECT SName FROM Student WHERE SEmail IS NULL
·查詢中使用常量列
★查詢輸出多了一列"學(xué)校名稱"，該列的所有數(shù)據(jù)都是"陜西咸陽(yáng)"
SELECT 姓名=SName,地址=SAddrees,'陜西咸陽(yáng)' AS 學(xué)校名稱
·查詢販貨限制的行數(shù)
★TOP為限制行數(shù)的關(guān)鍵字
SELECT TOP 5 SName,SAddreess
FROM Student WHERE SSex=0
★百分比限制關(guān)鍵字PERCENT
SELECT TOP 20 PERCENT SName,SAddrees
FROM Student WHERE SSex = 0
·查詢排序
★降低10%再加5，按照及格成績(jī)排列
SELECT StudentID AD 學(xué)員編號(hào)，(Score*0.9+5) AS 綜合成績(jī)
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 學(xué)員編號(hào),Score AS 成績(jī)
FROM Score
WHERE Score > 60
ORDER BY Score,CourseID(這兩的字段有先后順序)
·查詢中使用函數(shù)
★更新信息,從表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進(jìn)行模糊查詢
SELECT * FROM Student WHERE SName LIKE '王%'
★使用BETWEEN在某個(gè)范圍內(nèi)進(jìn)行查詢
SELECT * FROM SCore WHERE Score BETWEEN 60 AND 80
★查詢不在1992年8月1號(hào)到1993年8月1號(hào)之間訂購(gòu)的讀書列表
SELELCT * FROM Sales WHERE ord_date NOT BETWEEN '1992-8-1' AND '1993-8-1'
★使用IN在列舉值內(nèi)進(jìn)行查詢
SELECT SName AS 學(xué)員姓名 FROM Student WHERE SAddress IN ('北京','廣州','上海')
ORDER BY SAddress
·SQL Server 中的聚合函數(shù)
★SUM(只能返回一個(gè)數(shù)值)
SELECT SUM(ytd_sales) FROM tiles WHERE tyde = 'business'
★AVG
SELECT AVG(SCore) AS 平均成績(jī) FROM Score WHERE Score >=60
★MAX、MIN
SELECT AVG(Score) AS 平均成績(jī),MAX(Score) AS 最高分,MIN(Score) AS 最低分
FROM Score WHERE Score >=60
★COUNT(返回提供的表達(dá)式中非空值的計(jì)數(shù)，可以用數(shù)字和字符類型的列)
SELELCT COUNT(*) AS 及格人數(shù) FROM Score WHERE Score >=60
·分組查詢
★使用GROUP BY進(jìn)行分組查詢
SELECT CourseID,AVG(Score) AS 課程平均成績(jī)
FROM Score
GROUP BY CourseID
·多表連接查詢
★內(nèi)連接
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 學(xué)員姓名,CS.CourseName AS 課程名稱,C.Score AS 考試成績(jī)
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
·案例分析
★★查詢一張表中的奇數(shù)和偶數(shù)行
1、只能依靠標(biāo)識(shí)列的值來進(jìn)行判斷和選取
2、數(shù)據(jù)行可能存在增加，修改和刪除，因此標(biāo)識(shí)列的數(shù)據(jù)值并不完全可靠
3、SELECT
INTO創(chuàng)建一張新表，順便創(chuàng)建新的表示列，再在新的表示列上執(zhí)行奇偶判斷
4、奇數(shù)的判斷依據(jù)為：標(biāo)識(shí)列值%2不等與0；偶數(shù)的判斷依據(jù)為：標(biāo)識(shí)列值%2等于0
5、刪除臨時(shí)表TEMPTABLE
SELECT A,TDENTITY(INT 1,1) AS ID
INTO TEMPTABLE
FROM TBL
SELECT SUM(A) AS 奇數(shù)列匯總 FROM TEMPTABLE
WHERE ID%2 <> 0
SELECT SUM(A) AS 偶數(shù)列匯總 FROM TEMPTABLE
WHERE ID%2 = 0posted on 2007-12-01 11:02
凌晨風(fēng) 閱讀(893)
評(píng)論(0) 編輯 收藏 所屬分類:
Java學(xué)習(xí)筆記