sqlnote

发表于:2007-05-25来源:作者:点击数: 标签:
SELECT * FROM usertable ORDER BY age desc,userid ASC 先按年龄从大到小排序,相同的年龄的再按uerid从小到大排序。 ========================================================================================= SELECT p1.pub_id,p2.pub_id,p1.pr_info

SELECT *
FROM usertable
ORDER BY age desc,userid ASC  先按年龄从大到小排序,相同的年龄的再按uerid从小到大排序。
=========================================================================================
SELECT p1.pub_id,p2.pub_id,p1.pr_info
FROM pub_info AS p1 INNER JOIN pub_info AS p2
ON DATALENGTH(p1.pr_info)=DATALENGTH(p2.pr_info)
=========================================================
连接类型,可分为三种:内连接、外连接和交叉连接.内连接又分为等值连接、自然连接和不等连接三种.外连接分为左外连接(LEFT OUTER JOIN或LEFT JOIN)、右外连接(RIGHT OUTER JOIN或RIGHT JOIN)和全外连接(FULL OUTER JOIN或FULL JOIN)三种.

下面使用等值连接列出authors和publishers表中位于同一城市的作者和出版社:
SELECT *
FROM authors AS a INNER JOIN publishers AS p
ON a.city=p.city
又如使用自然连接,在选择列表中删除authors 和publishers 表中重复列(city和state):
SELECT a.*,p.pub_id,p.pub_name,p.country
FROM authors AS a INNER JOIN publishers AS p
ON a.city=p.city

交叉连接不带WHERE 子句,它返回被连接的两个表所有数据行的笛卡尔积,返回到结果集合中的数
据行数等于第一个表中符合查询条件的数据行数乘以第二个表中符合查询条件的数据行数。
例,titles表中有6类图书,而publishers表中有8家出版社,则下列交叉连接检索到的记录数将等
于6*8=48行。
SELECT type,pub_name
FROM titles CROSS JOIN publishers
ORDER BY type


原文转自:http://www.ltesting.net