sql語(yǔ)句的多表連接查詢(xún).doc

1、多表連接查詢(xún)SQL 多表連接查詢(xún)分為：內(nèi)連接, 外連接 , 交叉連接內(nèi)連接的使用 ? 根據(jù)表中共同的列進(jìn)行匹配，特別是存在主外鍵關(guān)系。一般用的比較多外連接 分為 左外連接，右外連接，完整連接交叉連接返回的記錄是兩張表相乘的記錄數(shù)內(nèi)連接主要一主表的信息為主如果沒(méi)有數(shù)據(jù)就不顯示例如 我們來(lái)看實(shí)例：先建兩張表 :create table student(? id int primary key identity(1,1),? name varchar(50)create table grade(? id int primary key identity(1,1), ? score money)alt

2、er table grade add sid int select * from gradealter table grade add constraint? fk_sid foreign key(sid)references student(id)insert into student values(哈哈 )insert into student? values(喜喜 )insert into student values(呵呵 )insert into student values(郁悶 )insert into student? values(小強(qiáng) )insert into studen

3、t values(小馬 )insert into grade values(90,1)insert into grade values(80,2)insert into grade values(70,null)select * from studentselect * from grade內(nèi)連接查詢(xún)方式有兩種:s.*,g.* from student s ,grade g where =;s.*, g.* from student s inner join grade g on =;我們看到我們主表沒(méi)有主表沒(méi)有匹配信息的時(shí)候其他的記錄沒(méi)有顯示下面我們來(lái)看外連接: 主要以從表信息為主從表沒(méi)有主

4、表記錄則顯示為 nullselect s.*,g.* from student s left join grade g on = select s.*,g.* from student s right join grade g on =還可以是 ?select s.*,g.* from student s left outer join grade g on = select s.*,g.* from student s right outer join grade g on =看他是左還是右下面來(lái)看看完整連接：將兩表的左和右 基礎(chǔ)數(shù)據(jù)全部查詢(xún)出來(lái)當(dāng)一個(gè)表沒(méi)有另一個(gè)表的記錄的時(shí)候則為 nul反之右表也是一樣select s.* ,g.* from student s full join grade g on =select s.* ,g.* from student s full outer join grade g on =交叉連接查詢(xún)：先返回左表所有