Java之MySQL

1、資料庫

 1　資料庫概念（瞭解）

1.1　什麼是資料庫

資料庫就是用來存儲和管理數據的倉庫！

資料庫存儲數據的優先：可存儲大量數據；

l  方便檢索；

l  保持數據的一致性、完整性；

l  安全，可共用；

l  通過組合分析，可產生新數據。

1.2　資料庫的發展歷程

l  沒有資料庫，使用磁碟文件存儲數據；

l  層次結構模型資料庫；

l  網狀結構模型資料庫；

l  關係結構[c1] 模型資料庫：使用二維表格來存儲數據；

l  關係-對象模型資料庫；

　　MySQL就是關係型資料庫！

1.3　常見資料庫

l  Oracle（神喻）：甲骨文（最高！）；

l  DB2：IBM；

l  SQL Server：微軟；

l  Sybase：賽爾斯；

l  MySQL：甲骨文；

1.4　理解資料庫

l  RDBMS = 管理員（manager）+倉庫（database）

l  database = N個table

l  table：

• 表結構：定義表的列名和列類型！
• 表記錄：一行一行的記錄！

我們現在所說的資料庫泛指“關係型資料庫管理系統（RDBMS - Relational database management system[c2] ）”，即“資料庫伺服器”。

當我們安裝了資料庫伺服器後，就可以在資料庫伺服器中創建資料庫，每個資料庫中還可以包含多張表。

資料庫表就是一個多行多列的表格。在創建表時，需要指定表的列數，以及列名稱，列類型等信息。而不用指定表格的行數，行數是沒有上限的。下麵是tab_student表的結構：

當把表格創建好了之後，就可以向表格中添加數據了。向表格添加數據是以行為單位的！下麵是s_student表的記錄：

　　大家要學會區分什麼是表結構，什麼是表記錄。

1.5　應用程式與資料庫

　　應用程式使用資料庫完成對數據的存儲！

2　安裝MySQL資料庫

2.1　安裝MySQL

　　參考：MySQL安裝圖解.doc

2.2　MySQL目錄結構

MySQL的數據存儲目錄為data，data目錄通常在C:\Documents and Settings\All Users\Application Data\MySQL\MySQL Server 5.1\data位置。在data下的每個目錄都代表一個資料庫。

MySQL的安裝目錄下：

l  bin目錄中都是可執行文件；

l  my.ini文件是MySQL的配置文件；

3　基本命令

3.1　啟動和關閉mysql伺服器

l  啟動：net start mysql；

l  關閉：net stop mysql；

在啟動mysql服務後，打開windows任務管理器，會有一個名為mysqld.exe的進程運行，所以mysqld.exe才是MySQL伺服器程式。

3.2　客戶端登錄退出mysql

在啟動MySQL伺服器後，我們需要使用管理員用戶登錄MySQL伺服器，然後來對伺服器進行操作。登錄MySQL需要使用MySQL的客戶端程式：mysql.exe

l  登錄：mysql -u root -p 123 -h localhost；

• -u：後面的root是用戶名，這裡使用的是超級管理員root；
• -p：後面的123是密碼，這是在安裝MySQL時就已經指定的密碼；
• -h：後面給出的localhost是伺服器主機名，它是可以省略的，例如：mysql -u root -p 123；

l  退出：quit或exit；

在登錄成功後，打開windows任務管理器，會有一個名為mysql.exe的進程運行，所以mysql.exe是客戶端程式。

2、SQL語句

1        SQL概述

1.1　什麼是SQL

SQL（Structured Query Language）是“結構化查詢語言”，它是對關係型資料庫的操作語言。它可以應用到所有關係型資料庫中，例如：MySQL、Oracle、SQL Server等。SQ標準（ANSI/ISO）有：

l  SQL-92：1992年發佈的SQL語言標準；

l  SQL:1999：1999年發佈的SQL語言標簽；

l  SQL:2003：2003年發佈的SQL語言標簽；

這些標準就與JDK的版本一樣，在新的版本中總要有一些語法的變化。不同時期的資料庫對不同標準做了實現。

雖然SQL可以用在所有關係型資料庫中，但很多資料庫還都有標準之後的一些語法，我們可以稱之為“方言”。例如MySQL中的LIMIT語句就是MySQL獨有的方言，其它資料庫都不支持！當然，Oracle或SQL Server都有自己的方言。

1.2　語法要求

l  SQL語句可以單行或多行書寫，以分號結尾；

l  可以用空格和縮進來來增強語句的可讀性；

l  關鍵字不區別大小寫，建議使用大寫；

2　分類

l  DDL（Data Definition Language）：數據定義語言，用來定義資料庫對象：庫、表、列等；

l  DML（Data Manipulation Language）：數據操作語言，用來定義資料庫記錄（數據）；

l  DCL（Data Control Language）：數據控制語言，用來定義訪問許可權和安全級別；

l  DQL（Data Query Language）：數據查詢語言，用來查詢記錄（數據）。

3　DDL（數據定義語言）

3.1　基本操作

l  查看所有資料庫名稱：SHOW DATABASES；　

l  切換資料庫：USE mydb1，切換到mydb1資料庫；

3.2　操作資料庫

l  創建資料庫：CREATE DATABASE [IF NOT EXISTS] mydb1；

創建資料庫，例如：CREATE DATABASE mydb1，創建一個名為mydb1的資料庫。如果這個數據已經存在，那麼會報錯。例如CREATE DATABASE IF NOT EXISTS mydb1，在名為mydb1的資料庫不存在時創建該庫，這樣可以避免報錯。

l  刪除資料庫：DROP DATABASE [IF EXISTS] mydb1；

刪除資料庫，例如：DROP DATABASE mydb1，刪除名為mydb1的資料庫。如果這個資料庫不存在，那麼會報錯。DROP DATABASE IF EXISTS mydb1，就算mydb1不存在，也不會的報錯。

l  修改資料庫編碼：ALTER DATABASE mydb1 CHARACTER SET utf8

修改資料庫mydb1的編碼為utf8。註意，在MySQL中所有的UTF-8編碼都不能使用中間的“-”，即UTF-8要書寫為UTF8。

3.3　數據類型

MySQL與Java一樣，也有數據類型。MySQL中數據類型主要應用在列上。

常用類型：

l  int：整型

l  double：浮點型，例如double(5,2)表示最多5位，其中必須有2位小數，即最大值為999.99；

l  decimal：泛型型，在表單錢方面使用該類型，因為不會出現精度缺失問題；

l  char：固定長度字元串類型；

l  varchar：可變長度字元串類型；

l  text：字元串類型；

l  blob：位元組類型；

l  date：日期類型，格式為：yyyy-MM-dd；

l  time：時間類型，格式為：hh:mm:ss

l  timestamp：時間戳類型；

3.4　操作表

l  創建表：

CREATE TABLE 表名(

  列名 列類型,

  列名 列類型,

  ......

);

例如：

再例如：

l  查看當前資料庫中所有表名稱：SHOW TABLES；　

l  查看指定表的創建語句：SHOW CREATE TABLE emp，查看emp表的創建語句；

l  查看表結構：DESC emp，查看emp表結構；

l  刪除表：DROP TABLE emp，刪除emp表；

l  修改表：

1. 添加列：給stu表添加classname列：

ALTER TABLE stu ADD (classname varchar(100));

1. 修改列類型：修改stu表的gender列類型為CHAR(2)：

ALTER TABLE stu MODIFY gender CHAR(2);

1. 修改列名：修改stu表的gender列名為sex：

ALTER TABLE stu change gender sex CHAR(2);

1. 刪除列：刪除stu表的classname列：

ALTER TABLE stu DROP classname;

1. 修改表名稱：修改stu表名稱為student：

ALTER TABLE stu RENAME TO student;

4　DML（數據操作語言）

4.1　插入數據

語法：

INSERT INTO 表名(列名1,列名2, …) VALUES(值1, 值2)

語法：

INSERT INTO 表名 VALUES(值1,值2,…)

因為沒有指定要插入的列，表示按創建表時列的順序插入所有列的值：

　　註意：所有字元串數據必須使用單引用！

4.2　修改數據（update）

語法：

UPDATE 表名 SET 列名1=值1, … 列名n=值n [WHERE 條件]

4.3　刪除數據

語法：

DELETE FROM 表名 [WHERE 條件]

語法：

TRUNCATE TABLE 表名

雖然TRUNCATE和DELETE都可以刪除表的所有記錄，但有原理不同。DELETE的效率沒有TRUNCATE高！

TRUNCATE其實屬性DDL語句，因為它是先DROP TABLE，再CREATE TABLE。而且TRUNCATE刪除的記錄是無法回滾的，但DELETE刪除的記錄是可以回滾的（回滾是事務的知識！）。

5　DCL

5.1　創建用戶

語法：

CREATE USER 用戶名@地址 IDENTIFIED BY '密碼';

5.2　給用戶授權

　　語法：

GRANT 許可權1, … , 許可權n ON 資料庫.* TO 用戶名

5.3　撤銷授權

　　語法：

　　REVOKE許可權1, … , 許可權n ON 資料庫.* FORM 用戶名

5.4　查看用戶許可權

語法：

SHOW GRANTS FOR 用戶名

5.5　刪除用戶

語法：

DROP USER 用戶名

5.6　修改用戶密碼

語法：

USE mysql;

UPDATE USER SET PASSWORD=PASSWORD(‘密碼’) WHERE User=’用戶名’ and Host=’IP’;

FLUSH PRIVILEGES;

3、數據查詢語法（DQL）

　　DQL就是數據查詢語言，資料庫執行DQL語句不會對數據進行改變，而是讓資料庫發送結果集給客戶端。

語法：

SELECT selection_list /*要查詢的列名稱*/

  FROM table_list /*要查詢的表名稱*/

  WHERE condition /*行條件*/

  GROUP BY grouping_columns /*對結果分組*/

  HAVING condition /*分組後的行條件*/

  ORDER BY sorting_columns /*對結果分組*/

  LIMIT offset_start, row_count /*結果限定*/

創建名：

l  學生表：stu

l  雇員表：emp

l  部分表：dept

1　基礎查詢

1.1　查詢所有列

SELECT * FROM stu;

1.2　查詢指定列

SELECT sid, sname, age FROM stu; 【select sid,sname,sid+sname from stu;】

可以一次查詢多列

2　條件查詢

2.1　條件查詢介紹

條件查詢就是在查詢時給出WHERE子句，在WHERE子句中可以使用如下運算符及關鍵字：

l  =、!=、<>、<、<=、>、>=；

l  BETWEEN…AND； （左邊兩端都包含）

l  IN(set)；

l  IS NULL； update stu set sage=20 where sage=null; sage=null永遠是false，所以要用is null。

l  IS NOT NULL

l  AND；

l  OR；

l  NOT；

2.2　查詢性別為女，並且年齡50的記錄

　　SELECT * FROM stu

WHERE gender='female' AND ge<50;

2.3　查詢學號為S_1001，或者姓名為liSi的記錄

SELECT * FROM stu

WHERE sid ='S_1001' OR sname='liSi';

2.4　查詢學號為S_1001，S_1002，S_1003的記錄

SELECT * FROM stu

WHERE sid IN ('S_1001','S_1002','S_1003');

2.5　查詢學號不是S_1001，S_1002，S_1003的記錄

SELECT * FROM tab_student

WHERE s_number NOT IN ('S_1001','S_1002','S_1003');

2.6　查詢年齡為null的記錄

SELECT * FROM stu

WHERE age IS NULL;

2.7　查詢年齡在20到40之間的學生記錄

SELECT *

FROM stu

WHERE age>=20 AND age<=40;

或者

SELECT *

FROM stu

WHERE age BETWEEN 20 AND 40;

2.8　查詢性別非男的學生記錄

SELECT *

FROM stu

WHERE gender!='male';

或者

SELECT *

FROM stu

WHERE gender<>'male';

或者

SELECT *

FROM stu

WHERE NOT gender='male';

2.9　查詢姓名不為null的學生記錄

SELECT *

FROM stu

WHERE NOT sname IS NULL;

或者

SELECT *

FROM stu

WHERE sname IS NOT NULL;

3　模糊查詢（單詞中包含a字母）

當想查詢姓名中包含a字母的學生時就需要使用模糊查詢了。模糊查詢需要使用關鍵字LIKE。

3.1　查詢姓名由5個字母構成的學生記錄

SELECT *

FROM stu

WHERE sname LIKE '_____';

模糊查詢必須使用LIKE關鍵字。其中 “_”匹配任意一個字母，5個“_”表示5個任意字母。

3.2　查詢姓名由5個字母構成，並且第5個字母為“i”的學生記錄

SELECT *

FROM stu

WHERE sname LIKE '____i';

3.3　查詢姓名以“z”開頭的學生記錄  （%表示其餘的位數，“%”匹配0~n個任何字母）

SELECT *

FROM stu

WHERE sname LIKE 'z%';

其中“%”匹配0~n個任何字母。

3.4　查詢姓名中第2個字母為“i”的學生記錄 （ '_i%'）

SELECT *

FROM stu

WHERE sname LIKE '_i%';

3.5　查詢姓名中包含“a”字母的學生記錄（where sname like '%y%' and sage>15;可以結合使用）

SELECT *

FROM stu

WHERE sname LIKE '%a%';

4　欄位控制查詢(去除重覆記錄，欄位加減，添加別名等)

4.1　去除重覆記錄( distinct )

去除重覆記錄（兩行或兩行以上記錄中系列的上的數據都相同），例如emp表中sal欄位就存在相同的記錄。當只查詢emp表的sal欄位時，那麼會出現重覆記錄，那麼想去除重覆記錄，需要使用DISTINCT：(man man man female female)-à man female

SELECT DISTINCT sal FROM emp;

4.2　查看雇員的月薪與佣金之和

　　因為sal和comm兩列的類型都是數值類型，所以可以做加運算。如果sal或comm中有一個欄位不是數值類型，那麼會出錯。

SELECT *,sal+comm FROM emp;

comm列有很多記錄的值為NULL，因為任何東西與NULL相加結果還是NULL，所以結算結果可能會出現NULL。下麵使用了把NULL轉換成數值0的函數IFNULL：

SELECT *, sal+IFNULL (comm, 0) FROM emp;

4.3　給列名添加別名（AS …）會生成新的total列

在上面查詢中出現列名為sal+IFNULL(comm,0)，這很不美觀，現在我們給這一列給出一個別名，為total：

SELECT *, sal+IFNULL(comm,0) AS total FROM emp;

給列起別名時，是可以省略AS關鍵字的：

SELECT *,sal+IFNULL(comm,0) total FROM emp;

5　排序( ORDER BY _____ ASC ascend;   DESC descend)

5.1　查詢所有學生記錄，按年齡升序排序 ORDER BY … ASC

SELECT *

FROM stu

ORDER BY sage ASC;

或者

SELECT *

FROM stu

ORDER BY sage;

5.2　查詢所有學生記錄，按年齡降序排序

SELECT *

FROM stu

ORDER BY age DESC;

5.3　查詢所有雇員，按月薪降序排序，如果月薪相同時，按編號升序排序(order by …asc,…desc)

SELECT * FROM emp

ORDER BY sal DESC,empno ASC;

6　聚合函數

聚合函數是用來做縱向運算的函數：

l  COUNT( …)：統計指定列不為NULL的記錄行數；

l  MAX()：計算指定列的最大值，如果指定列是字元串類型，那麼使用字元串排序運算；

l  MIN()：計算指定列的最小值，如果指定列是字元串類型，那麼使用字元串排序運算；

l  SUM()：計算指定列的數值和，如果指定列類型不是數值類型，那麼計算結果為0；

l  AVG()：計算指定列的平均值，如果指定列類型不是數值類型，那麼計算結果為0；

l  CONCAT(): mysql特有的，連接字元串

l  ROUND（AVG（），2）保留兩位小數

6.1　COUNT

當需要縱向統計時可以使用COUNT()。

l  查詢emp表中記錄數：

SELECT COUNT(*) AS cnt FROM emp;

l  查詢emp表中有佣金的人數：

SELECT COUNT(comm) cnt FROM emp;

註意，因為count()函數中給出的是comm列，那麼只統計comm列非NULL的行數。

l  查詢emp表中月薪大於2500的人數：

SELECT COUNT(*) FROM emp

WHERE sal > 2500;

l  統計月薪與佣金之和大於2500元的人數：(先判斷有沒有為null的，有的話用IFNULL)

SELECT COUNT(*) AS cnt FROM emp WHERE sal+IFNULL(comm,0) > 2500;

l  查詢有佣金的人數，以及有領導的人數：

SELECT COUNT(comm), COUNT(mgr) FROM emp;

6.2　SUM和AVG

當需要縱向求和時使用sum()函數。

l  查詢所有雇員月薪和：

SELECT SUM(sal) FROM emp;

l  查詢所有雇員月薪和，以及所有雇員佣金和：

SELECT SUM(sal), SUM(comm) FROM emp;

l  查詢所有雇員月薪+佣金和：

SELECT SUM(sal+IFNULL(comm,0)) FROM emp;

l  統計所有員工平均工資：

SELECT SUM(sal), COUNT(sal) FROM emp;

或者

SELECT AVG(sal) FROM emp;

6.3　MAX和MIN

l  查詢最高工資和最低工資：

SELECT MAX(sal), MIN(sal) FROM emp;

6.4　CONCAT（’ ‘, ename, ‘,’ job ）【mysql特有，連接字元串】

6.5 ROUND（AVG（sal），2）保留兩位小數

7　分組查詢(一般查詢主信息（當前組最高值，人數，等等），不能查詢個人信息)

當需要分組查詢時需要使用GROUP BY子句，例如查詢每個部門的工資和，這說明要使用部分來分組。

7.1　分組查詢（一般後面跟count（*））

l  查詢每個部門的部門編號和每個部門的工資和：

SELECT deptno, SUM(sal)

FROM emp

GROUP BY deptno;

l  查詢每個部門的部門編號以及每個部門的人數：

SELECT deptno,COUNT(*)

FROM emp

GROUP BY deptno;

l  查詢每個部門的部門編號以及每個部門工資大於1500的人數：

SELECT deptno,COUNT(*)

FROM emp

WHERE sal>1500

GROUP BY deptno;

7.2　HAVING子句（分組後的約束）

l  查詢工資總和大於9000的部門編號以及工資和：

SELECT deptno, SUM(sal)

FROM emp

GROUP BY deptno

HAVING SUM(sal) > 9000;

註意，WHERE是對分組前記錄的條件，如果某行記錄沒有滿足WHERE子句的條件，那麼這行記錄不會參加分組；而HAVING是對分組後數據的約束。

8　LIMIT（限定查詢結果的起始行，方言）

LIMIT用來限定查詢結果的起始行，以及總行數。

8.1　查詢5行記錄，起始行從0開始

SELECT * FROM emp LIMIT 0, 5;

註意，起始行從0開始，即第一行開始！

8.2　查詢10行記錄，起始行從3開始

SELECT * FROM emp LIMIT 3, 10;

8.3　分頁查詢

如果一頁記錄為10條，希望查看第3頁記錄應該怎麼查呢？

l  第一頁記錄起始行為0，一共查詢10行；

l  第二頁記錄起始行為10，一共查詢10行；

l  第三頁記錄起始行為20，一共查詢10行；

4、完整性約束

　　完整性約束是為了表的數據的正確性！如果數據不正確，那麼一開始就不能添加到表中。

1　主鍵（primary key）

當某一列添加了主鍵約束後，那麼這一列的數據就不能重覆出現。這樣每行記錄中其主鍵列的值就是這一行的唯一標識。例如學生的學號可以用來做唯一標識，而學生的姓名是不能做唯一標識的，因為學習有可能同名。

主鍵列的值不能為NULL，也不能重覆！

　　指定主鍵約束使用PRIMARY KEY關鍵字

l  創建表：定義列時指定主鍵：

CREATE TABLE stu(

                  sid        CHAR(6) PRIMARY KEY,

                  sname      VARCHAR(20),

                  age            INT,

                  gender     VARCHAR(10)

);

l  創建表：定義列之後獨立指定主鍵：

CREATE TABLE stu(

                  sid        CHAR(6),

                  sname      VARCHAR(20),

                  age            INT,

                  gender     VARCHAR(10),

                   PRIMARY KEY(sid)

);

l  修改表時指定主鍵：

ALTER TABLE stu

ADD PRIMARY KEY(sid);

l  刪除主鍵（只是刪除主鍵約束，而不會刪除主鍵列）：

ALTER TABLE stu DROP PRIMARY KEY;

2　主鍵自增長（非空，唯一，可被引用：整型才可以自增長：AUTO_INCREMENT）

MySQL提供了主鍵自動增長的功能！這樣用戶就不用再為是否有主鍵是否重覆而煩惱了。當主鍵設置為自動增長後，在沒有給出主鍵值時，主鍵的值會自動生成，而且是最大主鍵值+1，也就不會出現重覆主鍵的可能了。

l  創建表時設置主鍵自增長（主鍵必須是整型才可以自增長）：

CREATE TABLE stu(

                   sid INT PRIMARY KEY AUTO_INCREMENT,

                  sname      VARCHAR(20),

                  age            INT,

                  gender     VARCHAR(10)

);

l  修改表時設置主鍵自增長：（關鍵字：change）

ALTER TABLE stu CHANGE sid sid INT AUTO_INCREMENT;

l  修改表時刪除主鍵自增長：

ALTER TABLE stu CHANGE sid sid INT;

3　非空（NOT NULL）

指定非空約束的列不能沒有值，也就是說在插入記錄時，對添加了非空約束的列一定要給值；在修改記錄時，不能把非空列的值設置為NULL。

l  指定非空約束：

CREATE TABLE stu(

                   sid INT PRIMARY KEY AUTO_INCREMENT,

                   sname VARCHAR(10) NOT NULL,

                  age            INT,

                  gender     VARCHAR(10)

);

當為sname欄位指定為非空後，在向stu表中插入記錄時，必須給sname欄位指定值，否則會報錯：

INSERT INTO stu(sid) VALUES(1); 【報錯】

　　插入的記錄中sname沒有指定值，所以會報錯！

4　唯一（UNIQUE）

還可以為欄位指定唯一約束！當為欄位指定唯一約束後，那麼欄位的值必須是唯一的。這一點與主鍵相似！例如給stu表的sname欄位指定唯一約束：

CREATE TABLE tab_ab(

         sid INT PRIMARY KEY AUTO_INCREMENT,

         sname VARCHAR(10) UNIQUE

);

　　INSERT INTO sname(sid, sname) VALUES(1001, 'zs');

INSERT INTO sname(sid, sname) VALUES(1002, 'zs');

　　當兩次插入相同的名字時，MySQL會報錯！

5　外鍵(外鍵就是用來約束這一列的值必須是另一張表的主鍵值)

主外鍵是構成表與表關聯的唯一途徑！

外鍵是另一張表的主鍵！例如員工表與部門表之間就存在關聯關係，其中員工表中的部門編號欄位就是外鍵，是相對部門表的外鍵。

我們再來看BBS系統中：用戶表（t_user）、分類表（t_section）、帖子表（t_topic）三者之間的關係。Topic id    user id  section id

例如在t_section表中sid為1的記錄說明有一個分類叫java，版主是t_user表中uid為1的用戶，即zs！

例如在t_topic表中tid為2的記錄是名字為“Java是咖啡”的帖子，它是java版塊的帖子，它的作者是ww。

外鍵就是用來約束這一列的值必須是另一張表的主鍵值！！！

l  創建t_user表，指定uid為主鍵列：

CREATE TABLE t_user(

         uid   INT PRIMARY KEY AUTO_INCREMENT,

         uname     VARCHAR(20) UNIQUE NOT NULL

);

l  創建t_section表，指定sid為主鍵列，u_id為相對t_user表的uid列的外鍵：

CREATE TABLE t_section(

                   sid    INT PRIMARY KEY AUTO_INCREMENT,

                   sname      VARCHAR(30),

                   u_id INT,

                   CONSTRAINT fk_t_user FOREIGN KEY(u_id) REFERENCES t_user(uid)

);

l  修改t_section表，指定u_id為相對t_user表的uid列的外鍵：

ALTER TABLE t_section

ADD CONSTRAINT fk_t_user (自定義外鍵別名)

FOREIGN KEY(u_id)

REFERENCES t_user(uid);

l  修改t_section表，刪除u_id的外鍵約束：

ALTER TABLE t_section

DROP FOREIGN KEY fk_t_user;

6　表與表之間的關係（看視頻）

l  一對一：例如t_person表和t_card表，即人和身份證。這種情況需要找出主從關係，即誰是主表，誰是從表。人可以沒有身份證，但身份證必須要有人才行，所以人是主表，而身份證是從表。設計從表可以有兩種方案：

• 在t_card表中添加外鍵列（相對t_person表），並且給外鍵添加唯一約束；
• 給t_card表的主鍵添加外鍵約束（相對t_personr表），即t_card表的主鍵也是外鍵。

l  一對多（多對一）：最為常見的就是一對多！一對多和多對一，這是從哪個角度去看得出來的。t_user和t_section的關係，從t_user來看就是一對多，而從t_section的角度來看就是多對一！這種情況都是在多方創建外鍵！

l  多對多(中間表)：例如t_stu和t_teacher表，即一個學生可以有多個老師，而一個老師也可以有多個學生。這種情況通常需要創建中間表來處理多對多關係。例如再創建一張表t_stu_tea表，給出兩個外鍵，一個相對t_stu表的外鍵，另一個相對t_teacher表的外鍵。

5、編碼

1　查看MySQL編碼（SHOW VARIABLES LIKE 'char%'）

　　SHOW VARIABLES LIKE 'char%';

因為當初安裝時指定了字元集為UTF8，所以所有的編碼都是UTF8。

l  character_set_client：你發送的數據必須與client指定的編碼一致！！！伺服器會使用該編碼來解讀客戶端發送過來的數據；

l  character_set_connection：通過該編碼與client一致！該編碼不會導致亂碼！當執行的是查詢語句時，客戶端發送過來的數據會先轉換成connection指定的編碼。但只要客戶端發送過來的數據與client指定的編碼一致，那麼轉換就不會出現問題；

l  character_set_database：資料庫預設編碼，在創建資料庫時，如果沒有指定編碼，那麼預設使用database編碼；

l  character_set_server：MySQL伺服器預設編碼；

l  character_set_results：響應的編碼，即查詢結果返回給客戶端的編碼。這說明客戶端必須使用result指定的編碼來解碼；

2　控制台編碼

修改character_set_client、character_set_results、character_set_connection為GBK，就不會出現亂碼了。但其實只需要修改character_set_client和character_set_results。

控制台的編碼只能是GBK，而不能修改為UTF8，這就出現一個問題。客戶端發送的數據是GBK，而character_set_client為UTF8，這就說明客戶端數據到了伺服器端後一定會出現亂碼。既然不能修改控制台的編碼，那麼只能修改character_set_client為GBK了。

伺服器發送給客戶端的數據編碼為character_set_result，它如果是UTF8，那麼控制台使用GBK解碼也一定會出現亂碼。因為無法修改控制台編碼，所以只能把character_set_result修改為GBK。

l  修改character_set_client變數：set character_set_client=gbk;

l  修改character_set_results變數：set character_set_results=gbk;

設置編碼只對當前連接有效，這說明每次登錄MySQL提示符後都要去修改這兩個編碼，但可以通過修改配置文件來處理這一問題：配置文件路徑：D:\Program Files\MySQL\MySQL Server 5.1\ my.ini

3　MySQL工具

使用MySQL工具是不會出現亂碼的，因為它們會每次連接時都修改character_set_client、character_set_results、character_set_connection的編碼。這樣對my.ini上的配置覆蓋了，也就不會出現亂碼了。

6、MySQL資料庫備份與還原

1、備份和恢複數據

1　生成SQL腳本(不加分號;)

在控制台使用mysqldump命令可以用來生成指定資料庫的腳本文本，但要註意，腳本文本中只包含資料庫的內容，而不會存在創建資料庫的語句！所以在恢複數據時，還需要自已手動創建一個資料庫之後再去恢複數據。

現在可以在C盤下找到mydb1.sql文件了！

註意，mysqldump命令是在Windows控制臺下執行，無需登錄mysql！！！

2　執行SQL腳本

執行SQL腳本需要登錄mysql，然後進入指定資料庫，才可以執行SQL腳本！！！

執行SQL腳本不只是用來恢複數據庫，也可以在平時編寫SQL腳本，然後使用執行SQL 腳本來操作資料庫！大家都知道，在黑屏下編寫SQL語句時，就算發現了錯誤，可能也不能修改了。所以我建議大家使用腳本文件來編寫SQL代碼，然後執行之！

　　註意，在執行腳本時需要先行核查當前資料庫中的表是否與腳本文件中的語句有衝突！例如在腳本文件中存在create table a的語句，而當前資料庫中已經存在了a表，那麼就會出錯！

還可以通過下麵的方式來執行腳本文件：

mysql -uroot -p123 mydb1<c:\mydb1.sql

這種方式無需登錄mysql！

7、多表查詢（重要）

多表查詢有如下幾種：

l  合併結果集；

l  連接查詢

• 內連接
• 外連接

²  左外連接

²  右外連接

²  全外連接（MySQL不支持）

• 自然連接

l  子查詢

1　合併結果集（UNION , UNION ALL列數、列類型必須相同。）

1. 作用：合併結果集就是把兩個select語句的查詢結果合併到一起！
2. 合併結果集有兩種方式：

l  UNION：去除重覆記錄，例如：SELECT * FROM t1 UNION SELECT * FROM t2；

l  UNION ALL：不去除重覆記錄，例如：SELECT * FROM t1 UNION ALL SELECT * FROM t2。

（兩個4d）

1. 要求：被合併的兩個結果：列數、列類型必須相同。

2　連接查詢

連接查詢就是求出多個表的乘積，例如t1連接t2，那麼查詢出的結果就是t1*t2。（4*3條記錄）

連接查詢會產生笛卡爾積，假設集合A={a,b}，集合B={0,1,2}，則兩個集合的笛卡爾積為{(a,0),(a,1),(a,2),(b,0),(b,1),(b,2)}。可以擴展到多個集合的情況。

那麼多表查詢產生這樣的結果並不是我們想要的，那麼怎麼去除重覆的，不想要的記錄呢，當然是通過條件過濾。通常要查詢的多個表之間都存在關聯關係，那麼就通過關聯關係去除笛卡爾積。

你能想像到emp和dept表連接查詢的結果麽？emp一共14行記錄，dept表一共4行記錄，那麼連接後查詢出的結果是56行記錄。

也就你只是想在查詢emp表的同時，把每個員工的所在部門信息顯示出來，那麼就需要使用主外鍵來去除無用信息了。

　　使用主外鍵關係做為條件來去除無用信息

上面查詢結果會把兩張表的所有列都查詢出來，也許你不需要那麼多列，這時就可以指定要查詢的列了。張三沒了

還可以為表指定別名，然後在引用列時使用別名即可。

2.1　內連接(…inner join… on ..=..)

上面的連接語句就是內連接，但它不是SQL標準中的查詢方式，可以理解為方言！SQL標準的內連接為：

內連接的特點：查詢結果必須滿足條件。例如我們向emp表中插入一條記錄：

　　其中deptno為50，而在dept表中只有10、20、30、40部門，那麼上面的查詢結果中就不會出現“張三”這條記錄，因為它不能滿足e.deptno=d.deptno這個條件。

2.2　外連接（左連接、右連接）

外連接的特點：查詢出的結果存在不滿足條件的可能。

左連接：

左連接是先查詢出左表（即以左表為主），然後查詢右表，右表中滿足條件的顯示出來，不滿足條件的顯示NULL。

這麼說你可能不太明白，我們還是用上面的例子來說明。其中emp表中“張三”這條記錄中，部門編號為50，而dept表中不存在部門編號為50的記錄，所以“張三”這條記錄，不能滿足e.deptno=d.deptno這條件。但在左連接中，因為emp表是左表，所以左表中的記錄都會查詢出來，即“張三”這條記錄也會查出，但相應的右表部分顯示NULL。

2.3　右連接  …right outer join ..on…=…

右連接就是先把右表中所有記錄都查詢出來，然後左表滿足條件的顯示，不滿足顯示NULL。例如在dept表中的40部門並不存在員工，但在右連接中，如果dept表為右表，那麼還是會查出40部門，但相應的員工信息為NULL。

連接查詢心得：

連接不限與兩張表，連接查詢也可以是三張、四張，甚至N張表的連接查詢。通常連接查詢不可能需要整個笛卡爾積，而只是需要其中一部分，那麼這時就需要使用條件來去除不需要的記錄。這個條件大多數情況下都是使用主外鍵關係去除。

兩張表的連接查詢一定有一個主外鍵關係，三張表的連接查詢就一定有兩個主外鍵關係，所以在大家不是很熟悉連接查詢時，首先要學會去除無用笛卡爾積，那麼就是用主外鍵關係作為條件來處理。如果兩張表的查詢，那麼至少有一個主外鍵條件，三張表連接至少有兩個主外鍵條件。

3　自然連接(natural join ,natural left join ,natural right join)

大家也都知道，連接查詢會產生無用笛卡爾積，我們通常使用主外鍵關係等式來去除它。而自然連接無需你去給出主外鍵等式，它會自動找到這一等式：

l  兩張連接的表中名稱和類型完成一致的列作為條件，例如emp和dept表都存在deptno列，並且類型一致，所以會被自然連接找到！

當然自然連接還有其他的查找條件的方式，但其他方式都可能存在問題！

4　子查詢

子查詢就是嵌套查詢，即SELECT中包含SELECT，如果一條語句中存在兩個，或兩個以上SELECT，那麼就是子查詢語句了。

l  子查詢出現的位置：

• where後，作為條件的一部分；
• from後，作為被查詢的一條表；

l  當子查詢出現在where後作為條件時，還可以使用如下關鍵字：

• any
• all

l  子查詢結果集的形式：（下麵的例子分別對應這4種情況）

• 單行單列（用於條件）
• 單行多列（用於條件）
• 多行單列（用於條件）
• 多行多列（用於表）

條件

 * (***)單行單列：SELECT * FROM 表1 別名1 WHERE 列1 [=、>、<、>=、<=、!=] (SELECT 列 FROM 表2 別名2 WHERE 條件)

* (**)多行單列：SELECT * FROM 表1 別名1 WHERE 列1 [IN, ALL, ANY] (SELECT 列 FROM 表2 別名2 WHERE 條件)

* (*)單行多列：SELECT * FROM 表1 別名1 WHERE (列1,列2) IN (SELECT 列1, 列2 FROM 表2 別名2 WHERE 條件)

* (***)多行多列：SELECT * FROM 表1 別名1 , (SELECT ....) 別名2 WHERE 條件

練習：

1. 工資高於甘寧的員工。

分析：

查詢條件：工資>甘寧工資，其中甘寧工資需要一條子查詢。

第一步：查詢甘寧的工資

第二步：查詢高於甘寧工資的員工

結果：

總結：

l  子查詢作為條件

l  子查詢形式為單行單列

1. 工資高於30部門所有人的員工信息(ALL)

分析：

查詢條件：工資高於30部門所有人工資，其中30部門所有人工資是子查詢。高於所有需要使用all關鍵字。

第一步：查詢30部門所有人工資

第二步：查詢高於30部門所有人工資的員工信息

結果：

總結：

l  子查詢作為條件

l  子查詢形式為多行單列（當子查詢結果集形式為多行單列時可以使用ALL或ANY關鍵字）

1. 查詢工作和工資與殷天正完全相同的員工信息

分析：

查詢條件：工作和工資與殷天正完全相同，這是子查詢

第一步：查詢出殷天正的工作和工資

第二步：查詢出與殷天正工作和工資相同的人 in(….)匹配

結果：

您的分享是我們最大的動力!

-Advertisement-