Transact-SQL简明语法
<div style="border-right: #cccccc 1px solid; padding-right: 5px; border-top: #cccccc 1px solid; padding-left: 4px; font-size: 13px; padding-bottom: 4px; border-left: #cccccc 1px solid; width: 98%; padding-top: 4px; border-bottom: #cccccc 1px solid; background-color: #eeeeee;">http://www.blogjava.net/Images/OutliningIndicators/None.gif最简单的查询语法格式:http://www.blogjava.net/Images/OutliningIndicators/None.gif SELECT <列名>
http://www.blogjava.net/Images/OutliningIndicators/None.gif FROM <表名>
http://www.blogjava.net/Images/OutliningIndicators/None.gif [WHERE <查询条件表达式>]
http://www.blogjava.net/Images/OutliningIndicators/None.gif [ORDER BY <排序的列名> ]]
http://www.blogjava.net/Images/OutliningIndicators/None.gif·查询所有的数据行和列:
http://www.blogjava.net/Images/OutliningIndicators/None.gif SELECT * FROM Student
http://www.blogjava.net/Images/OutliningIndicators/None.gif·查询部分行列－－条件查询
http://www.blogjava.net/Images/OutliningIndicators/None.gif SELECT SCode,SName,SAddress
http://www.blogjava.net/Images/OutliningIndicators/None.gif FROM Student
http://www.blogjava.net/Images/OutliningIndicators/None.gif WHERE SAddress = '陕西咸阳'()
http://www.blogjava.net/Images/OutliningIndicators/None.gif ★<>为不等于符号
http://www.blogjava.net/Images/OutliningIndicators/None.gif SELECT SCode,SName,SAddress
http://www.blogjava.net/Images/OutliningIndicators/None.gif FROM Student
http://www.blogjava.net/Images/OutliningIndicators/None.gif WHERE SAddress <> '陕西咸阳'
http://www.blogjava.net/Images/OutliningIndicators/None.gif ★合并的新列名
http://www.blogjava.net/Images/OutliningIndicators/None.gif SELECT FirstName+'.'+LastName AS '姓名'
http://www.blogjava.net/Images/OutliningIndicators/None.gif FROM Employees
http://www.blogjava.net/Images/OutliningIndicators/None.gif或
http://www.blogjava.net/Images/OutliningIndicators/None.gif SELECT '姓名' = FirstName+'.'+LastName
http://www.blogjava.net/Images/OutliningIndicators/None.gif FROM Employees
http://www.blogjava.net/Images/OutliningIndicators/None.gif·查询空行
http://www.blogjava.net/Images/OutliningIndicators/None.gif SELECT SName FROM Student WHERE SEmail IS NULL
http://www.blogjava.net/Images/OutliningIndicators/None.gif·查询中使用常量列
http://www.blogjava.net/Images/OutliningIndicators/None.gif ★查询输出多了一列"学校名称"，该列的所有数据都是"陕西咸阳"
http://www.blogjava.net/Images/OutliningIndicators/None.gif SELECT 姓名=SName,地址=SAddrees,'陕西咸阳' AS 学校名称
http://www.blogjava.net/Images/OutliningIndicators/None.gif·查询贩货限制的行数
http://www.blogjava.net/Images/OutliningIndicators/None.gif ★TOP为限制行数的关键字
http://www.blogjava.net/Images/OutliningIndicators/None.gif SELECT TOP 5 SName,SAddreess
http://www.blogjava.net/Images/OutliningIndicators/None.gif FROM Student WHERE SSex=0
http://www.blogjava.net/Images/OutliningIndicators/None.gif ★百分比限制关键字PERCENT
http://www.blogjava.net/Images/OutliningIndicators/None.gif SELECT TOP 20 PERCENT SName,SAddrees
http://www.blogjava.net/Images/OutliningIndicators/None.gif FROM Student WHERE SSex = 0
http://www.blogjava.net/Images/OutliningIndicators/None.gif
http://www.blogjava.net/Images/OutliningIndicators/None.gif·查询排序
http://www.blogjava.net/Images/OutliningIndicators/None.gif ★降低10%再加5，按照及格成绩排列
http://www.blogjava.net/Images/OutliningIndicators/None.gif SELECT StudentID AD 学员编号，(Score*0.9+5) AS 综合成绩
http://www.blogjava.net/Images/OutliningIndicators/None.gif FROM Score
http://www.blogjava.net/Images/OutliningIndicators/None.gif WHERE (Score*0.9+5)>60
http://www.blogjava.net/Images/OutliningIndicators/None.gif ORDER BY Score
http://www.blogjava.net/Images/OutliningIndicators/None.gif ★合并查到的所有姓名信息，然后按照姓名降序排列
http://www.blogjava.net/Images/OutliningIndicators/None.gif SELECT Au_Lname + '.' + Au_Fname AS EMP FROM Author UNION
http://www.blogjava.net/Images/OutliningIndicators/None.gif SELECT Fname + '.' + Lname AS EMP FROM Employee
http://www.blogjava.net/Images/OutliningIndicators/None.gif ORDER BY EMP DESC
http://www.blogjava.net/Images/OutliningIndicators/None.gif ★多字段排序
http://www.blogjava.net/Images/OutliningIndicators/None.gif SELECT StudentID AS 学员编号,Score AS 成绩
http://www.blogjava.net/Images/OutliningIndicators/None.gif FROM Score
http://www.blogjava.net/Images/OutliningIndicators/None.gif WHERE Score > 60
http://www.blogjava.net/Images/OutliningIndicators/None.gif ORDER BY Score,CourseID(这两的字段有先后顺序)
http://www.blogjava.net/Images/OutliningIndicators/None.gif·查询中使用函数
http://www.blogjava.net/Images/OutliningIndicators/None.gif ★更新信息,从表Card中把字段password中o改为0，i改为1
http://www.blogjava.net/Images/OutliningIndicators/None.gif UPDATE Card SET password = REPLACE(密码,'o','0')
http://www.blogjava.net/Images/OutliningIndicators/None.gif UPDATE Card SET password = REPLACE(密码,'i','1')
http://www.blogjava.net/Images/OutliningIndicators/None.gif 或
http://www.blogjava.net/Images/OutliningIndicators/None.gif UPDATE Card SET password = REPLACE(REPLACE(密码,'o','0'),'i','1')
http://www.blogjava.net/Images/OutliningIndicators/None.gif ★特殊排序
http://www.blogjava.net/Images/OutliningIndicators/None.gif 排序前:13-1,13-2,13-3,13-10,13-100,13-108,13-18,13-11,13-15,14-1,14-2
http://www.blogjava.net/Images/OutliningIndicators/None.gif 排序后:13-1,13-2,13-3,13-10,13-11,13-15,13-18,13-100,13-108,14-1,14-2
http://www.blogjava.net/Images/OutliningIndicators/None.gif SELCT ListNumber
http://www.blogjava.net/Images/OutliningIndicators/None.gif FROM SellRecord
http://www.blogjava.net/Images/OutliningIndicators/None.gif ORDER BY Convert(int,Left(ListNumber,Charindex('-',ListNumber)-1)),
http://www.blogjava.net/Images/OutliningIndicators/None.gif Convert(int,Stuff(ListNumber,1,Charindex('-',ListNumber),''))
http://www.blogjava.net/Images/OutliningIndicators/None.gif·模糊查询
http://www.blogjava.net/Images/OutliningIndicators/None.gif ★使用LIKE进行模糊查询
http://www.blogjava.net/Images/OutliningIndicators/None.gif SELECT * FROM Student WHERE SName LIKE '王%'
http://www.blogjava.net/Images/OutliningIndicators/None.gif ★使用BETWEEN在某个范围内进行查询
http://www.blogjava.net/Images/OutliningIndicators/None.gif SELECT * FROM SCore WHERE Score BETWEEN 60 AND 80
http://www.blogjava.net/Images/OutliningIndicators/None.gif ★查询不在1992年8月1号到1993年8月1号之间订购的读书列表
http://www.blogjava.net/Images/OutliningIndicators/None.gif SELELCT * FROM Sales WHERE ord_date NOT BETWEEN '1992-8-1' AND '1993-8-1'
http://www.blogjava.net/Images/OutliningIndicators/None.gif ★使用IN在列举值内进行查询
http://www.blogjava.net/Images/OutliningIndicators/None.gif SELECT SName AS 学员姓名 FROM Student WHERE SAddress IN ('北京','广州','上海')
http://www.blogjava.net/Images/OutliningIndicators/None.gif ORDER BY SAddress
http://www.blogjava.net/Images/OutliningIndicators/None.gif·SQL Server 中的聚合函数
http://www.blogjava.net/Images/OutliningIndicators/None.gif ★SUM(只能返回一个数值)
http://www.blogjava.net/Images/OutliningIndicators/None.gif SELECT SUM(ytd_sales) FROM tiles WHERE tyde = 'business'
http://www.blogjava.net/Images/OutliningIndicators/None.gif ★AVG
http://www.blogjava.net/Images/OutliningIndicators/None.gif SELECT AVG(SCore) AS 平均成绩 FROM Score WHERE Score >=60
http://www.blogjava.net/Images/OutliningIndicators/None.gif ★MAX、MIN
http://www.blogjava.net/Images/OutliningIndicators/None.gif SELECT AVG(Score) AS 平均成绩,MAX(Score) AS 最高分,MIN(Score) AS 最低分
http://www.blogjava.net/Images/OutliningIndicators/None.gif FROM Score WHERE Score >=60
http://www.blogjava.net/Images/OutliningIndicators/None.gif ★COUNT(返回提供的表达式中非空值的计数，可以用数字和字符类型的列)
http://www.blogjava.net/Images/OutliningIndicators/None.gif SELELCT COUNT(*) AS 及格人数 FROM Score WHERE Score >=60
http://www.blogjava.net/Images/OutliningIndicators/None.gif·分组查询
http://www.blogjava.net/Images/OutliningIndicators/None.gif ★使用GROUP BY进行分组查询
http://www.blogjava.net/Images/OutliningIndicators/None.gif SELECT CourseID,AVG(Score) AS 课程平均成绩
http://www.blogjava.net/Images/OutliningIndicators/None.gif FROM Score
http://www.blogjava.net/Images/OutliningIndicators/None.gif GROUP BY CourseID
http://www.blogjava.net/Images/OutliningIndicators/None.gif·多表连接查询
http://www.blogjava.net/Images/OutliningIndicators/None.gif ★内连接
http://www.blogjava.net/Images/OutliningIndicators/None.gif SELECT Student,SName,Score.CourseID,Score.Score
http://www.blogjava.net/Images/OutliningIndicators/None.gif FROM Student,Score
http://www.blogjava.net/Images/OutliningIndicators/None.gif WHERE Student.Score = Score.StudentID
http://www.blogjava.net/Images/OutliningIndicators/None.gif或
http://www.blogjava.net/Images/OutliningIndicators/None.gif SELECT S.SName,C.CourseID,C.Score
http://www.blogjava.net/Images/OutliningIndicators/None.gif FROM Student AS S INNER JOIN Score AS C
http://www.blogjava.net/Images/OutliningIndicators/None.gif ON (S.Score=C.StudentID)
http://www.blogjava.net/Images/OutliningIndicators/None.gif ★三表连接查询
http://www.blogjava.net/Images/OutliningIndicators/None.gif SELECT S.SName AS 学员姓名,CS.CourseName AS 课程名称,C.Score AS 考试成绩
http://www.blogjava.net/Images/OutliningIndicators/None.gif FROM Student AS S
http://www.blogjava.net/Images/OutliningIndicators/None.gif INNER JOIN Score AS C ON (S.Score=C.StudentID)
http://www.blogjava.net/Images/OutliningIndicators/None.gif INNER JOIN Course AS CS ON (CS.CourseID=C.CourseID)
http://www.blogjava.net/Images/OutliningIndicators/None.gif ★左外连接查询
http://www.blogjava.net/Images/OutliningIndicators/None.gif SELECT S.SName,C.CourseID,C.Score
http://www.blogjava.net/Images/OutliningIndicators/None.gif FROM Student AS S
http://www.blogjava.net/Images/OutliningIndicators/None.gif LEFT OUTER JOIN Score AS C ON S.Score = C.StudentID
http://www.blogjava.net/Images/OutliningIndicators/None.gif ★右外连接查询
http://www.blogjava.net/Images/OutliningIndicators/None.gif SELECT Titles.Title_id,Titles.Title,Publishers.Pub_name
http://www.blogjava.net/Images/OutliningIndicators/None.gif FROM titles
http://www.blogjava.net/Images/OutliningIndicators/None.gif RIGHT OUTER JOIN Publishers ON Titles.Pub_id = Publishers.Pub_id
http://www.blogjava.net/Images/OutliningIndicators/None.gif·案例分析
http://www.blogjava.net/Images/OutliningIndicators/None.gif ★★查询一张表中的奇数和偶数行
http://www.blogjava.net/Images/OutliningIndicators/None.gif 1、只能依靠标识列的值来进行判断和选取
http://www.blogjava.net/Images/OutliningIndicators/None.gif 2、数据行可能存在增加，修改和删除，因此标识列的数据值并不完全可靠
http://www.blogjava.net/Images/OutliningIndicators/None.gif 3、SELECThttp://www.blogjava.net/Images/dot.gifINTO创建一张新表，顺便创建新的表示列，再在新的表示列上执行奇偶判断
http://www.blogjava.net/Images/OutliningIndicators/None.gif 4、奇数的判断依据为：标识列值%2不等与0；偶数的判断依据为：标识列值%2等于0
http://www.blogjava.net/Images/OutliningIndicators/None.gif 5、删除临时表TEMPTABLE
http://www.blogjava.net/Images/OutliningIndicators/None.gif
http://www.blogjava.net/Images/OutliningIndicators/None.gif SELECT A,TDENTITY(INT 1,1) AS ID
http://www.blogjava.net/Images/OutliningIndicators/None.gif INTO TEMPTABLE
http://www.blogjava.net/Images/OutliningIndicators/None.gif FROM TBL
http://www.blogjava.net/Images/OutliningIndicators/None.gif
http://www.blogjava.net/Images/OutliningIndicators/None.gif SELECT SUM(A) AS 奇数列汇总 FROM TEMPTABLE
http://www.blogjava.net/Images/OutliningIndicators/None.gif WHERE ID%2 <> 0
http://www.blogjava.net/Images/OutliningIndicators/None.gif
http://www.blogjava.net/Images/OutliningIndicators/None.gif SELECT SUM(A) AS 偶数列汇总 FROM TEMPTABLE
http://www.blogjava.net/Images/OutliningIndicators/None.gif WHERE ID%2 = 0
页:
[1]