一、表连接
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