一、表連接
cross 返回連接類型左右兩側的表中的所有行的所有組合。
inner 返回連接類型左側表和右側表有相同值的所有行。
left 返回左側表中的所有行，以及與左側表相匹配的右側表中的值，如沒有匹配的返回NULL。
right 返回右側表中的所有行，以及與右側表相匹配的左側表中的值，如沒有匹配的返回NULL。
full 返回左右兩側表的所有行。
1.下例是內連接的例子。該實例返回PUBS數據庫的出版商及作者均在同一城市的出版商及作者信息。
USE pubs
方法一(內連接)：
SELECT p.pub_id,p.pub_name,p.state,p.city,(a.au_fname+' '+a.au_lname) AS authorName
FROM publishers p INNER JOIN authors a ON p.city = a.city
方法二(where子句)：
SELECT p.pub_id,p.pub_name,p.state,p.city,(a.au_fname+' '+a.au_lname) AS authorName
FROM publishers p, authors a WHERE p.city = a.city
結果：
p.pub_id p.pub_name p.state p.city authorName
1389 Algodata Infosystems CA Berkeley Cheryl Carson
1389 Algodata Infosystems CA Berkeley Abraham Bennet
2.下例是左聯接的事例
USE pubs
SELECT p.pub_id,p.pub_name,p.state,p.city,(a.au_fname+' '+a.au_lname)
AS authorName FROM publishers p LEFT JOIN authors a ON p.city = a.city
結果：
p.pub_id p.pub_name p.state p.city authorName
0736 New Moon Books MA Boston NULL
0877 Binnet & Hardley DC Washington NULL
1389 Algodata Infosystems CA Berkeley Cheryl Carson
1389 Algodata Infosystems CA Berkeley Abraham Bennet
1622 Five Lakes Publishing IL Chicago NULL
1756 Ramona Publishers TX Dallas NULL
9901 GGG&G NULL M黱 chen NULL
9952 Scootney Books NY New York NULL
9999 Lucerne Publishing NULL Paris NULL
3.下例是右聯接的事例
USE pubs
SELECT p.pub_id,p.pub_name,p.state,p.city,(a.au_fname+' '+a.au_lname)
AS authorName FROM publishers p RIGHT JOIN authors a ON p.city = a.city
4.一下查詢為外聯接
USE pubs
SELECT (a.au_fname+' '+a.au_lname) AS authorName ,p.pub_name
FROM authors a FULL OUTER JOIN publishers p ON p.city = a.city
ORDER BY p.pub_name ASC,a.au_lname,a.au_fname ASC
二、向表中插入數據
USE northwind
INSERT INTO Shippers (CompanyName,phone )
Values (N'Snowflake Shpping' , N'(523)333-4443')
三、修改表中的數據
USE PUBS
UPDATE titles SET price = price*2 WHERE pub_id IN
(SELECT pub_id FROM publishers WHERE pub_name = 'New Moon Books')
四、刪除表中的數據
DELETE salesWHERE title_id IN (SELECT title_id FROM titles WHERE type = 'business')
五、創建視圖
USE pubs
IF EXISTS (SELECT TABLE_NAME FROM INFORMATION_SCHEMA. VIEWS
WHERE TABLE_NAME ＝'CA_VIEW')DROP VIEW CA View GO
CREATE VIEW CA_VIEW AS
SLECTE au_lname,au_fname,city,state FROM authors
WHERE state = 'CA' WITH CHECK OPTION