SQL JOIN µÄÓ÷¨
¶ÔÓÚSQLÎÒҲѧϰÁË£¬µ«ÊÇÕæÕýÓõ½µÄʱºò²ÅÖªµÀºÜ¶à¶«Î÷»¹²»Ì«Àí½â£¬ÔÚÍøÉÏ¿´ÁËһϣ¬ÕâƪÎÄÕÂÖн²µÄºÜºÃ£¬ËùÒÔÄÃÀ´ºÍ´ó¼Ò·ÖÏíһϣº
SQL JOIN µÄÓ÷¨
¹ØÓÚsqlÓï¾äÖеÄÁ¬½Ó£¨join£©¹Ø¼ü×Ö£¬ÊǽÏΪ³£ÓöøÓÖ²»Ì«ÈÝÒ×Àí½âµÄ¹Ø¼ü×Ö£¬ÏÂÃæÕâ¸öÀý×Ó¸ø³öÁËÒ»¸ö¼òµ¥µÄ½âÊÍ --½¨±ítable1,table2£º
create table table1(id int,name varchar(10))
create table table2(id int,score int)
insert into table1 select 1,''lee''
insert into table1 select 2,''zhang''
insert into table1 select 4,''wang''
insert into table2 select 1,90
insert into table2 select 2,100
insert into table2 select 3,70
Èç±í
-------------------------------------------------
table1 | table2 |
-------------------------------------------------
id name |id score |
1 lee |1 90 |
2 zhang |2 100 |
4 wang |3 70 |
-------------------------------------------------
ÒÔϾùÔÚ²éѯ·ÖÎöÆ÷ÖÐÖ´ÐÐ
Ò»¡¢ÍâÁ¬½Ó
1.¸ÅÄ°üÀ¨×óÏòÍâÁª½Ó¡¢ÓÒÏòÍâÁª½Ó»òÍêÕûÍⲿÁª½Ó
2.×óÁ¬½Ó£ºleft join »ò left outer join
(1)×óÏòÍâÁª½ÓµÄ½á¹û¼¯°üÀ¨ LEFT OUTER ×Ó¾äÖÐÖ¸¶¨µÄ×ó±íµÄËùÓÐÐУ¬¶ø²»½ö½öÊÇÁª½ÓÁÐËùÆ¥ÅäµÄÐС£Èç¹û×ó±íµÄijÐÐÔÚÓÒ±íÖÐûÓÐÆ¥ÅäÐУ¬ÔòÔÚÏà¹ØÁªµÄ½á¹û¼¯ÐÐÖÐÓÒ±íµÄËùÓÐÑ¡ÔñÁбíÁоùΪ¿ÕÖµ(null)¡£
(2)sqlÓï¾ä
select * from table1 left join table2 on table1.id=table2.id
-------------½á¹û-------------
id name id score
------------------------------
1 lee 1 90
2 zhang 2 100
4 wang NULL NULL
------------------------------
×¢ÊÍ£º°üº¬table1µÄËùÓÐ×Ӿ䣬¸ù¾ÝÖ¸¶¨Ìõ¼þ·µ»Øtable2ÏàÓ¦µÄ×ֶΣ¬²»·ûºÏµÄÒÔnullÏÔʾ
3.ÓÒÁ¬½Ó£ºright join »ò right outer join
(1)ÓÒÏòÍâÁª½ÓÊÇ×óÏòÍâÁª½ÓµÄ·´ÏòÁª½Ó¡£½«·µ»ØÓÒ±íµÄËùÓÐÐС£Èç¹ûÓÒ±íµÄijÐÐÔÚ×ó±íÖÐûÓÐÆ¥ÅäÐУ¬Ôò½«Îª×ó±í·µ»Ø¿ÕÖµ¡£
(2)sqlÓï¾ä
select * from table1 right join table2 on table1.id=table2.id
-------------½á¹û-------------
id name id score
------------------------------
1 lee 1 90
2 zhang 2 100
NULL NULL 3 70
------------------------------
×¢ÊÍ£º°üº¬table2µÄËùÓÐ×Ӿ䣬¸ù¾ÝÖ¸¶¨Ìõ¼þ·µ»Øtable1ÏàÓ¦µÄ×ֶΣ¬²»·ûºÏµÄÒÔnullÏÔʾ
4.ÍêÕûÍⲿÁª½Ó:full join »ò full outer join
Ïà¹ØÎĵµ£º
index.jsp
<%@ page language="java" import="java.sql.*" import="java.lang.*" import="java.util.*" pageEncoding="GB2312"%>
<%
String path = request.getContextPath();
String basePath = request.getScheme()+"://"+request.getServerName()+":"+request.getServerPort()+path+"/";
%>
<%!
& ......
Sql´úÂë
--²ÉÓÃSQLÓï¾äʵÏÖsql2005ºÍExcel Êý¾ÝÖ®¼äµÄÊý¾Ýµ¼Èëµ¼³ö£¬ÔÚÍøÉÏÕÒÀ´Ò»--Ï£¬ÊµÏÖ·½·¨ÊÇÕâÑùµÄ£º
--Excel---->SQL2005 µ¼È룺
select * into useinfo from O ......
ÊìϤSQL SERVER 2000µÄÊý¾Ý¿â¹ÜÀíÔ±¶¼ÖªµÀ£¬ÆäDTS¿ÉÒÔ½øÐÐÊý¾ÝµÄµ¼Èëµ¼³ö£¬Æäʵ£¬ÎÒÃÇÒ²¿ÉÒÔʹÓÃTransact-SQLÓï¾ä½øÐе¼Èëµ¼³ö²Ù×÷¡£ÔÚTransact-SQLÓï¾äÖУ¬ÎÒÃÇÖ÷ҪʹÓÃOpenDataSourceº¯Êý¡¢OPENROWSET º¯Êý£¬¹ØÓÚº¯ÊýµÄÏêϸ˵Ã÷£¬Çë²Î¿¼SQLÁª»ú°ïÖú¡£ÀûÓÃÏÂÊö·½·¨£¬¿ÉÒÔÊ®·ÖÈÝÒ×µØʵÏÖSQL SERVER¡¢ACCESS¡¢EXCELÊý¾Ýת»»£ ......
1 £¬¶ÔÓÚÈÕÆÚ×Ö¶Î×Ö¶Î
access±íʾΪ£º#1981-28-12#
SQLSERVER2000±íʾΪ£º''1981-02-12''
2,SQLÓï¾äÇø±ð£¬select ,update ÔÚ¶Ôµ¥±í²Ù×÷ʱ¶¼²î²»¶à£¬
µ«¶à±í²Ù×÷ʱupdateÓï¾äµÄÇø±ðACCESSÓëSQLSERVERÖеÄUpdateÓï¾ä¶Ô±È:
SQLSERVERÖиüжà±íµÄUpdateÓï¾ä:
Update Tab1
SET a.Name = b.Name
from Tab1 a,Tab2 b
Whe ......
ÓÐЩÈË¿ÉÄܶÔÖ÷¼üºÍ¾Û¼¯Ë÷ÒýÓÐËù»ìÏý£¬ÆäʵÕâÁ½¸öÊDz»Í¬µÄ¸ÅÄÏÂÃæÊÇÒ»¸ö¼òµ¥µÄÃèÊö¡£²»Ïë¿´ÈÆ¿ÚÎÄ×ÖÕߣ¬Ö±½Ó¿´Á½ÕßµÄ¶Ô±È±í¡£ÓÈÆäÊÇ×îºóÒ»ÏîµÄ±È½Ï¡£
Ö÷¼ü£¨PRIMARY KEY £©
À´×ÔMSDNµÄÃèÊö£º
±íͨ³£¾ßÓаüº¬Î¨Ò»±êʶ±íÖÐÿһÐеÄÖµµÄÒ»ÁлòÒ»×éÁС£ÕâÑùµÄÒ»Áлò¶àÁгÆΪ±íµÄÖ÷¼ü (PK)£¬ÓÃÓÚÇ¿ÖƱíµÄʵÌåÍêÕûÐÔ¡£ÔÚ´´½ ......