數(shù)據(jù)庫應(yīng)用（SQL Server）：12 表的連接和視圖

1、表的連接和視圖兩個(gè)表間連接的舉例將連接表的數(shù)據(jù)存入另一個(gè)表（連接關(guān)系的保存）超過兩個(gè)表的連接數(shù)組ARRAY視圖的創(chuàng)建Book,Author,Auth_BookBook：select bookid, title, authors from book order by bookid;author：select auth_id, auth_name from author;Book和Author的連接select auth_id, auth_name, bookid, title from book inner join author on book.authors like % | author.

2、auth_name | %;將連接表的數(shù)據(jù)存入另一個(gè)表連接關(guān)系的保存 insert into auth_book (auth_id, book_id) select auth_id, bookid from book inner join author on book.authors like % | author.auth_name | %;連接關(guān)系保存后，book表和author表的關(guān)系就保存于auth_book表。連接關(guān)系保存到auth_book表之后，book表的authors列還需要么 Book, Author, Auth_Book三個(gè)表的連接select book.bookid,

3、book.title, author.auth_name, book.publisher from book inner join auth_book on (book.bookid=auth_book.book_id) inner join author on (auth_book.auth_id=author.auth_id);練習(xí)完成上次課程的三個(gè)表book, author, auth_book并輸入數(shù)據(jù)通過連接關(guān)系將book, author的關(guān)系保存到auth_book表利用三個(gè)表建立連接，并進(jìn)行查詢ARRAY數(shù)組的定義舉例：一個(gè)整數(shù)的數(shù)組create table int_array1

4、 (a1 int); 表int_array1， 列a1是整數(shù)的數(shù)組insert into int_array1 values (1,2,3);insert into int_array1 values (100,200);insert into int_array1 values (13,15,17,19);字符數(shù)組create table city ( cities text ); insert into city values (Beijing,Shanghai,Guangzhou); insert into city values (New York,Atlanta);數(shù)組的檢索數(shù)組元素

5、的檢索select * from city where cities2=Shanghai;第二個(gè)元素為Shanghai的行ANY select * from city where Shanghai= any (cities);含有Shanghai這個(gè)元素的行ARRAY練習(xí)建立Country表，其中包含兩列CountryName Text, Cities Text輸入數(shù)據(jù)，例如:China,“Beijing”,”Shanghai”,”Guangzhou”USA,“New York”,” Washington DC”,”Chicago”檢索數(shù)據(jù)“Guangzhou”=ANY (Cities)“Ch

6、icago”=ANY (Cities)“New York”=Cities1SELECT查詢創(chuàng)建ARRAYselect array (select bookid from book);select array (select auth_name from author);將ARRAY應(yīng)用到更復(fù)雜的查詢中 select book.bookid, book.title, ARRAY(select auth_id from auth_book where auth_book.book_id=book.bookid) as author_id, book.publisher from book;三個(gè)表的

7、連接沒有用Array返回的數(shù)據(jù)，每本書的每個(gè)作者都形成一行用Array返回的數(shù)據(jù)select book.bookid, book.title, ARRAY (select auth_name from author where author.auth_id = ANY (ARRAY (select auth_id from auth_book where auth_book.book_id=book.bookid) AS Authorsfrom book;練習(xí)用SELECT 建立ARRAYSELECT BOOKID FROM BOOK;SELECT ARRAY (SELECT BOOKID F

8、ROM BOOK);SELECT ARRAY (SELECT TITLE FROM BOOK);SELECT ARRAY (SELECT LEFT(TITLE,4) FROM BOOK);比較三個(gè)表的連接返回的數(shù)據(jù)和用ARRAY查詢返回的數(shù)據(jù)視圖(VIEW)的創(chuàng)建視圖是保存的查詢，優(yōu)點(diǎn)在于SQL語法事先檢查和優(yōu)化省去輸入長串SQL的麻煩數(shù)據(jù)結(jié)構(gòu)更加清晰CREATE VIEWCreate ViewCREATE VIEW v_BOOK_AUTHOR ASselect book.bookid, book.title, author.auth_name, book.publisher from book inner join auth_book on (book.bookid=auth_book.book_id) inner join author on (auth_book.auth_id=author.auth_id);視圖的使用視圖創(chuàng)建完成后，查詢方式和Table一致Select * from v_BOOK_AUTHOR;Select * from v_BOOK_AUTHOR W