mysql實例與連接,MySQL連接

本文目錄一覽：

• 1、MySQL — 關聯
• 2、jsp mysql連接實例
• 3、MySQL 中的各種 JOIN
• 4、怎麼鏈接一個服務器上的mysql數據庫
• 5、MySQL – 數據查詢語言DQL數據連接語法及實例

MySQL — 關聯

來自MySQL的學習筆記，寫的不對的地方大家多多指教哦

什麼是外鍵？

假設有 2 個表，分別是表 A 和表 B，它們通過一個公共字段“id”發生關聯關係，我們把這個關聯關係叫做 R。如果“id”在表 A 中是主鍵，那麼，表 A 就是這個關係 R 中的主表。相應的，表 B 就是這個關係中的從表，表 B 中的“id”，就是表 B 用來引用表 A 中數據的，叫外鍵。所以，外鍵就是從表中用來引用主表中數據的那個公共字段。

語法結構：

在創建表時添加外鍵約束：

在修改表時定義外鍵約束：

例子1：創建表時添加外鍵約束

首先創建主表：importhead

創建從表：test_mysql.importdetails

查詢外鍵約束的相關信息：

查詢結果為：

例子2：修改表時定義外鍵約束

修改表時定義從表test_mysql.importdetails的外鍵約束

刪除外鍵約束使用DROP，語法結構為：

例子：刪除從表test_mysql.importdetails的外鍵約束

在 MySQL 中，有 2 種類型的連接，分別是內連接（INNER JOIN）和外連接（OUTER JOIN）。

在 MySQL 裡面，關鍵字 JOIN、INNER JOIN、CROSS JOIN 的含義是一樣的，都表示內連接。我們可以通過 JOIN 把兩個表關聯起來，來查詢兩個表中的數據。

例子：有一張銷售表，如下圖：

有一張會員信息表，如下圖：

通過內連接，查詢會員的銷售記錄：

運行語句，結果如下：

根據上面的結果，其實可以得知：內連接查詢到結果集為兩個表的交集部分。

跟內連接只返回符合連接條件的記錄不同的是，外連接還可以返回表中的所有記錄，它包括兩類，分別是左連接和右連接。

例子1：左外連接

如果需要查詢所有銷售記錄，則可以使用左外連接

運行語句，結果為：

從上面的結果可以得知，LEFT JOIN左邊的表會返回全部記錄，而右邊的表只返回符合連接條件的記錄

例子2：右外連接：

運行語句，結果為：

從上面的結果可以得知，RIGHT JOIN右邊的表會返回全部記錄，而左邊的表只返回符合連接條件的記錄

jsp mysql連接實例

面是一個純jsp連接數據庫的例子

(1)把mysql的驅動放到tomcat的lib中 驅動是這個

解壓後在lib中有mysql-connector-java-5.1.6.jar.把這個文件放到tomcat的lib中5.X的在tomcat/common/lib 6.0在tomcat/lib

(2)建一個很簡單的表person就兩個字段username和password,數據庫名和數據庫密碼換成你的就是了

create database ibatis;–創建數據庫

use ibatis;–使用數據庫,以下表在該數據庫中

create table person(username varchar(20),password varchar(20));–創建person表

(3)創建index.jsp和regist.jsp

1:

index.jsp 提交表單頁面

%@ page pageEncoding=”GBK”%

html

head

/head

body

form action=”regist.jsp” method=”post”

username :input type = “text” name=”name”/

password :input type = “password” name=”password”/

input type = “submit” value=”提交”/

/form

/body

/html

2:regist.jsp //用戶註冊同時顯示所有用戶

%@ page contentType=”text/html; charset=GBK” %

%@ page import=”java.sql.*”%

body

center

%

request.setCharacterEncoding(“GBK”);

String uname=request.getParameter(“name”); //從表單獲得

String pwd=request.getParameter(“password”); //從表單獲得

String driver=”com.mysql.jdbc.Driver”; //我用的是mysql官方驅動你自己換一下就是了 在這裡有

String url=”jdbc:mysql://localhost:3306/ibatis?user=rootpassword=yanghao”; //這是數據庫連接地址Ibatis是數據庫名稱,user是用戶.password就是你的用戶名,根據實際情況你修改

String sql=”INSERT INTO person (username,password) VALUES(‘”+uname+”‘,'”+pwd+”‘)”; //把index.jsp提交的兩個數據插進數據庫的數據庫語句

Connection conn=null; //數據庫連接

Statement stmt=null;

ResultSet rs = null; //查詢結果

%

%

Class.forName(driver); //加載驅動

conn=DriverManager.getConnection(url); //獲得連接

stmt=conn.createStatement();

stmt.execute(sql);//存入數據庫

rs=stmt.executeQuery(“select * from person”); //查詢所有person語句

%

%

if(rs!=null){ //判斷以下

while(rs.next()){

String username=rs.getString(1);

String password=rs.getString(2);

%

table

tr

td%=username %/td

td%=password %/td

/tr

/table

%

//關閉數據庫連接,和開始的順序是反的

rs.close();//關閉結果集

stmt.close();//關閉Statement

conn.close();//關閉數據庫連接

//ok完成了插入和查詢操作

}

}

%

/center

/body

MySQL 中的各種 JOIN

本文主要介紹 SQL 標準中定義的各種連接的意義和區別，例如，交叉連接（ CROSS JOIN ）、內連接（ INNER JOIN ）、外連接（ OUTER JOIN ）、自然連接（ NATURAL JOIN ）等，並結合例子講解這些連接在 MySQL 中的語法和表現。

從網上的資料看， JOIN 更多翻譯為連接，本文中凡是出現中文“連接”的地方都是指 JOIN 。

本文中用到的所有例子涉及兩張表—— customers 用戶表和 orders 訂單表，其中訂單表中的 cust_id 字段表示用戶的唯一 ID，也就是用戶表的主鍵 cust_id 。兩張表的數據如下：

註：兩張表都經過了簡化，實際業務中這兩張表肯定還包括其他字段。

英文維基百科 JOIN 詞條 對連接的定義如下：

翻譯過來就是，“連接可以根據一張（自連接）或多張表中的共同值將這些表的列數據合併為一個新的結果集，標準 SQL 定義了五種連接：內連接、左外連接、右外連接、全外連接和交叉連接。”

也就是說，連接是 SQL 標準中定義的一種組合多張表的方式，當然一張表自身也可以和自身組合，稱為自連接。連接後得到的結果集的每一列其實都來自用於連接的多張表，不同的連接類型只是區分了這些列具體從哪張表裡來，列里填充的是什麼數據。

其實英文維基百科的 JOIN 詞條已經把各種連接的類型解釋地非常清楚了，非常值得去看一下。

我們來看一下 SQL 標準中定義的各種連接類型，理解各種連接最好的方法就是把需要連接的表想象成集合，並畫出可以反映集合的交與並的情況的圖——韋恩圖，例如下圖就畫出了 SQL 中定義的幾種主要連接。

請先仔細查看一下圖中的內容，你可以從中歸納出幾種連接類型呢？

雖然圖中畫了 7 種集合的交並情況，但是總結起來，主要是兩種連接類型在起作用——內連接（ INNER JOIN ）和外連接（ OUTER JOIN ），其中外連接又分為了左外連接（ LEFT OUTER JOIN ）、右外連接（ RIGHT OUTER JOIN ）和全外連接（ FULL OUTER JOIN ）。

下面先簡單介紹一下 SQL 標準中各種連接的定義，然後在「MySQL 中的連接」一節再用例子來演示 MySQL 中支持的各種連接。

連接既然是用來合併多張表的，那麼要定義一個連接就必須指定需要連接的表，並指定可選的連接條件。例如，一個典型的 SQL 連接語句如下：

我們用表 A 和表 B 指代需要連接的兩張表，經過 內連接 後得到的結果集 僅 包含所有滿足 連接條件 的數據；而經過 外連接 後得到的數據集 不僅 包含滿足 連接條件 的數據，還包含其他數據，具體的差別是：

在上面「SQL 標準定義的主要連接」一圖中並沒有列出交叉連接，交叉連接會對連接的兩張表做笛卡爾積，也就是連接後的數據集中的行是由第一張表中的每一行與第二張表中的每一行配對而成的，而不管它們 邏輯上 是否可以搭配在一起。假設交叉連接的兩張表分別有 m 和 n 行數據，那麼交叉連接後的數據集就包含 m 乘以 n 行數據。

連接根據連接的條件不同，又可以區分為等值連接和非等值連接，「SQL 標準定義的主要連接」圖中畫出的連接的連接條件都是比較兩個字段是否相等，它們都是等值連接。

自然連接是等值連接的一種特殊形式，自然連接會自動選取需要連接的兩張表中字段名相同的 所有 列做相等比較，而不需要再指定連接條件了。

註：以下內容全部基於 MySQL 5.7 版本，所有例子只保證在 MySQL 5.7 上是可以正確執行的。

MySQL 中支持的連接類型和關鍵字如下：

上面的表示方法摘自 MySQL 5.7 版本 官方文檔 ，其中 | 表示兩者皆可出現， [] 表示的是可選的， {} 表示的是必選的，例如 NATURAL LEFT JOIN 和 NATURAL JOIN 都是合法的。

可以看到，除了全外連接（ FULL OUTER JOIN ）以外， MySQL 基本支持了 SQL 標準中定義的各種連接。在 MySQL 中全外連接可以通過 UNION 合併的方式做到，當然前提是你知道自己為什麼需要這麼做，具體參見： Full Out Join in MySQL 。

MySQL 語法中還支持一個並不在 SQL 標準中的 STRAIGHT_JOIN ，它在 表現上 和內連接或者交叉連接並無區別，只是一種給 MySQL 優化器的一個提示， STRAIGHT_JOIN 提示 MySQL 按照語句中表的順序加載表，只有在你明確清楚 MySQL 服務器對你的 JOIN 語句做了負優化的時候才可能用到它。

還有一點需要說明的是，根據 官方文檔 ，在 MySQL 中， JOIN 、 CROSS JOIN 和 INNER JOIN 實現的功能是一致的，它們在語法上是等價的。從語義上來說， CROSS JOIN 特指無條件的連接（沒有指定 ON 條件的 JOIN 或者沒有指定 WHERE 連接條件的多表 SELECT ）， INNER JOIN 特指有條件的連接（指定了 ON 條件的 JOIN 或者指定了 WHERE 連接條件的多表 SELECT ）。當然，如果你非要寫 … CROSS JOIN … ON … 這樣的語法，也是可以執行的，雖然寫着交叉連接，實際上執行的是內連接。

下面我們就用例子來看一看 MySQL 中支持的幾種連接的例子。

註：下面的例子都沒有指定 ORDER BY 子句，返回結果的順序可能會因為數據插入順序的不同而略有不同。

MySQL 的交叉連接或內連接有兩種寫法，一種是使用 JOIN 並用 ON 或者 USING 子句指定連接條件的寫法，一種是普通的 SELECT 多表，並且用 WHERE 子句指定連接的鍵的寫法。

下面的例子是一個交叉連接：

上面的寫法等價於：

當然，第二種寫法中如果將 CROSS JOIN 替換成 JOIN 或者 INNER JOIN 也是可以正確執行的。上面兩條語句的執行結果如下：

可以看到共返回了 30 行結果，是兩張表的笛卡爾積。

一個內連接的例子如下：

上面的寫法等價於：

在連接條件比較的字段相同的情況下，還可以改用 USING 關鍵字，上面的寫法等價於：

上面三條語句的返回結果如下：

可以看到只返回了符合連接條件 customers.cust_id = orders.cust_id 的 6 行結果，結果的含義是所有有訂單的用戶和他們的訂單。

左外連接和右外連接的例子如下，其中的 OUTER 關鍵字可以省略：

其中右外連接的返回與內連接的返回是一致的（思考一下為什麼），左外連接的返回結果如下：

可以看到一共返回了 8 行數據，其中最後兩行數據對應的 order_id 的值為 NULL ，結果的含義是所有用戶的訂單，不管這些用戶是否已經有訂單存在了。

根據前面介紹的自然連接的定義，自然連接會自動用參與連接的兩張表中 字段名相同 的列做等值比較，由於例子中的 customers 和 orders 表只有一列名稱相同，我們可以用自然連接的語法寫一個與上面的內連接的例子表現行為一樣的語句如下：

可以看到，使用自然連接就不能再用 ON 子句指定連接條件了，因為這完全是多餘的。

當然，自然連接同樣支持左外連接和右外連接。

下面用一個 customers 表自連接的例子再來說明一下自然連接，語句如下：

因為是自連接，因此必須使用 AS 指定別名，否則 MySQL 無法區分“兩個” customers 表，運行的結果如下：

可以看到結果集和 customers 表完全一致，大家可以思考一下為什麼結果是這樣的。

文章之前也提到了，MySQL 還支持一種 SQL 標準中沒有定義的“方言”， STRAIGHT_JOIN ， STRAIGHT_JOIN 支持帶 ON 子句的內連接和不帶 ON 子句的交叉連接，我們來看一個 STRAIGHT_JOIN 版本的內連接的例子：

返回結果與前面內連接的例子是一致的，如下：

STRAIGHT_JOIN 的表現和 JOIN 是完全一致的，它只是一種給 MySQL 優化器的提示，使得 MySQL 始終按照語句中表的順序讀取表（上面的例子中，MySQL 在執行時一定會先讀取 customers 表，再讀取 orders 表），而不會做改變讀取表的順序的優化。關於 MySQL 優化器的話題這裡不做展開，需要說明的是除非你非常清楚你在做什麼，否則不推薦直接使用 STRAIGHT_JOIN 。

你能理解上面的語句是在檢索什麼數據嗎？

本文主要介紹了 SQL 標準里定義的各種連接的概念，以及 MySQL 中的實現，並通過各種例子來介紹了這些連接的區別。這些連接不一定都能在實際開發中用到，但是做到心中有知識也還是很有必要的。

那麼，現在再回憶一下，什麼是內連接、外連接、自連接、等值連接和自然連接？他們的區別是什麼？

最後，給大家留一個思考題，為什麼 MySQL 中沒有左外連接或者右外連接版本的 STRAIGHT_JOIN ？

怎麼鏈接一個服務器上的mysql數據庫

有多種方法啊。介紹其中一種，使用mysql數據庫的odbc驅動。

步驟：

1.安裝mysql數據庫的odbc驅動，mysql-connector-odbc-3.51.23-win32.msi(其中*是版本號)，下載並安裝。

2.在mysql中創建數據庫實例。

3.打開控制面板

—

管理工具

—

數據源odbc，在用戶dsn中添加一個mysql

odbc

3.51數據源。

4.在登錄login選項卡中輸入數據源名稱data

source

name，此處輸入mysqldns（也可以自己隨便命名，只要在後面導入數據的時候選擇正確的數據源名字就行）；然後輸入服務器server，用戶user，密碼password，輸入正確後選擇要導入的數據庫，database選擇你需要導入的數據庫。在連接選項connect

options中根據需要設置mysql使用的端口port和字符集character

set。

註：字符集一定要和mysql服務器相對應，如果mysql使用了gbk字符集，則一定要設置字符集為gbk，否則導入到sql

server可能會出現問號亂碼。

5.打開sql

server企業管理器，選擇該數據庫，單擊右鍵選擇所有任務

—

導出數據。

6.‘選擇數據源’為默認，‘選擇目的’為剛剛安裝的mysql數據源，用戶/系統dsn為mysqldns。

方法2：

有多種方法啊。介紹其中一種，使用mysql數據庫的odbc驅動。步驟：

1.安裝mysql數據庫的odbc驅動，mysql-connector-odbc-3.51.23-win32.msi(其中*是版本號)，下載並安裝。

2.在mysql中創建數據庫實例。

3.打開控制面板

—

管理工具

—

數據源odbc，在用戶dsn中添加一個mysql

odbc

3.51數據源。

4.在登錄login選項卡中輸入數據源名稱data

source

name，此處輸入mysqldns（也可以自己隨便命名，只要在後面導入數據的時候選擇正確的數據源名字就行）；然後輸入服務器server，用戶user，密碼password，輸入正確後選擇要導入的數據庫，database選擇你需要導入的數據庫。在連接選項connect

options中根據需要設置mysql使用的端口port和字符集character

set。

註：字符集一定要和mysql服務器相對應，如果mysql使用了gbk字符集，則一定要設置字符集為gbk，否則導入到sql

server可能會出現問號亂碼。

5.打開sql

server企業管理器，選擇該數據庫，單擊右鍵選擇所有任務

—

導出數據。

6.‘選擇數據源’為默認，‘選擇目的’為剛剛安裝的mysql數據源，用戶/系統dsn為mysqldns。

MySQL – 數據查詢語言DQL數據連接語法及實例

合併思想. N個表 — 1個表

連接的定義

將2個或2個以上的表通過一定的條件拼接起來形成一個虛表的過程。連接是from子句的一部分.

從2個表的連接開始.

左表: 放置在你左手邊的表. left_tab

右表: 放置在你右手邊的表. right_tab

連接語法

三種基本類型: 交叉連接,內連接與外連接

交叉連接

返回的就是一個笛卡爾積.

交叉連接語法

內連接

在交叉連接的基礎上,僅返回滿足連接條件的行.

連接條件

1.使用on關鍵字來指定.

2.on的用法與where類似.

3.on可以使用連接的所有表中的列值進行判斷

內連接的語法

外連接

在內連接的基礎上,返回一些額外的行.

左外連接(左連接)

在內連接的基礎上,左表除了返回滿足連接條件的行之外,剩下的所有不滿足連接條件的行也要一併返回,右表中與之沒有匹配的行的列值設置為null

右外連接(右連接)

在內連接的基礎上,右表除了返回滿足連接條件的行之外,剩下的所有不滿足連接條件的行也要一併返回,左表中與之沒有匹配的行的列值設置為null

外連接語法

語法:

表的別名

注意: 一旦給表設置了別名,則以後只能通過別名引用表中的列.別名會隱藏表名.

join嵌套

可以在一個join中嵌套另外一個join

一般來說,當連接的表的個數N=3時需要使用嵌套join.

當連接的表的個數為N時,則至少需要N-1個連接條件.

語法:

join嵌套

與內連接中的join嵌套用法類似.