《数据库应用（SQL Server）：12 表的连接和视图》由会员分享，可在线阅读，更多相关《数据库应用（SQL Server）：12 表的连接和视图（20页珍藏版）》请在金锄头文库上搜索。
1、表的连接和视图两个表间连接的举例将连接表的数据存入另一个表（连接关系的保存）超过两个表的连接数组ARRAY视图的创建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 | %;将连接表的数据存入另一个表连接关系的保存 insert into auth_book (auth_id, book_id) select auth_id, bookid from book inner join author on book.authors like % | author.auth_name | %;连接关系保存后，book表和author表的关系就保存于auth_book表。连接关系保存到auth_book表之后，book表的authors列还需要么 Book, Author, Auth_Book三个表的连接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);练习1.完成上次课程的三个表book, author, auth_book并输入数据2.通过连接关系将book, author的关系保存到auth_book表3.利用三个表建立连接，并进行查询ARRAY数组的定义举例：一个整数的数组create table int_
4、array1 (a1 int); 表int_array1， 列a1是整数的数组insert into int_array1 values (1,2,3);insert into int_array1 values (100,200);insert into int_array1 values (13,15,17,19);字符数组create table city ( cities text ); insert into city values (Beijing,Shanghai,Guangzhou); insert into city values (New York,Atlanta);数组的
5、检索数组元素的检索select * from city where cities2=Shanghai;第二个元素为Shanghai的行ANY select * from city where Shanghai= any (cities);含有Shanghai这个元素的行ARRAY练习1.建立Country表，其中包含两列CountryName Text, Cities Text2.输入数据，例如:China,“Beijing”,”Shanghai”,”Guangzhou”USA,“New York”,” Washington DC”,”Chicago”3.检索数据“Guangzhou”=ANY
6、 (Cities)“Chicago”=ANY (Cities)“New York”=Cities1SELECT查询创建ARRAYselect array (select bookid from book);select array (select auth_name from author);将ARRAY应用到更复杂的查询中 select book.bookid, book.title, ARRAY(select auth_id from auth_book where auth_book.book_id=book.bookid) as author_id, book.publisher fr
7、om book;三个表的连接没有用Array返回的数据，每本书的每个作者都形成一行用Array返回的数据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;练习1.用SELECT 建立ARRAYSELECT BOOKID FROM BOOK;SELECT ARRAY (S
8、ELECT BOOKID FROM BOOK);SELECT ARRAY (SELECT TITLE FROM BOOK);SELECT ARRAY (SELECT LEFT(TITLE,4) FROM BOOK);2.比较三个表的连接返回的数据和用ARRAY查询返回的数据视图(VIEW)的创建视图是保存的查询，优点在于1.SQL语法事先检查和优化2.省去输入长串SQL的麻烦3.数据结构更加清晰CREATE VIEWCreate ViewCREATE VIEW v_BOOK_AUTHOR ASselect book.bookid, book.title, author.auth_name, b
9、ook.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);视图的使用视图创建完成后，查询方式和Table一致Select * from v_BOOK_AUTHOR;Select * from v_BOOK_AUTHOR WHERE BOOKID=1;dt和dvdt 列出tabledv列出view练习创建v_Book_Author视图创建利用ARRAY的视图v_Book_Authors利用创建的视图进行查询练习找出F. Albert Cotton为作者的书找出书名含有Inorganic 的书
