MySQL資料庫

1　資料庫概念（瞭解）

1.1　什麼是資料庫

資料庫就是用來儲存和管理資料的倉庫！
資料庫儲存資料的優點：

• 可儲存大量資料；
• 方便檢索；
• 保持資料的一致性、完整性；
• 安全，可共用；
• 通過組合分析，可產生新資料。

1.2　資料庫的發展歷程

• 沒有資料庫，使用磁碟檔案儲存資料；
• 庫；
• 網狀結構模型資料庫；
• 關係結構模型資料庫：使用二維表格來儲存資料；
• 關係-物件模型資料庫；

MySQL就是關係型資料庫！

專注於IT技術知識梳理，書寫前沿技術文章！更多技術在這裡慢慢探索吧，請關注傑哥！

1.3　常見資料庫

• Oracle：甲骨文（最高！）；
• DB2：IBM；
• SQL Server：微軟；
• Sybase：賽爾斯；
• MySQL：甲骨文；

1.4　理解資料庫

• RDBMS = 管理員（manager）+倉庫（database）
• database = N個table
• table：
• 表結構：定義表的列名和列型別！
• 表記錄：一行一行的記錄！

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

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

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

  當把表格建立好了之後，就可以向表格中新增資料了。向表格新增資料是以行為單位的！下面是s_student表的記錄：

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

1.5　應用程式與資料庫

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

2　安裝MySQL資料庫

2.1　安裝MySQL

參考：MySQL安裝圖解

2.2　MySQL目錄結構

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

• bin目錄中都是可執行檔案；
• my.ini檔案是MySQL的組態檔；

3　基本命令

3.1　啟動和關閉mysql伺服器

• 啟動：net start mysql；
• 關閉：net stop mysql；

在啟動mysql服務後，開啟windows工作管理員，會有一個名為mysqld.exe的程序執行，所以mysqld.exe才是MySQL伺服器程式。

3.2　使用者端登入退出mysql

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

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

  在登入成功後，開啟windows工作管理員，會有一個名為mysql.exe的程序執行，所以mysql.exe是使用者端程式。

SQL語句

專注於IT技術知識梳理，書寫前沿技術文章！更多技術在這裡慢慢探索吧，請關注傑哥！

1.SQL概述

1.1　什麼是SQL

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

• SQL-92：1992年釋出的SQL語言標準；
• SQL:1999：1999年釋出的SQL語言標籤；
• SQL:2003：2003年釋出的SQL語言標籤；

  這些標準就與JDK的版本一樣，在新的版本中總要有一些語法的變化。不同時期的資料庫對不同標準做了實現。
  雖然SQL可以用在所有關係型資料庫中，但很多資料庫還都有標準之後的一些語法，我們可以稱之為「方言」。例如MySQL中的LIMIT語句就是MySQL獨有的方言，其它資料庫都不支援！當然，Oracle或SQL Server都有自己的方言。

1.2　語法要求

• SQL語句可以單行或多行書寫，以分號結尾；
• 可以用空格和縮排來來增強語句的可讀性；
• 關鍵字不區別大小寫，建議使用大寫；

2　分類

• DDL（Data Definition Language）：資料定義語言，用來定義資料庫物件：庫、表、列等；
• DML（Data Manipulation Language）：資料操作語言，用來定義資料庫記錄（資料）；
• DCL（Data Control Language）：資料控制語言，用來定義存取許可權和安全級別；
• DQL（Data Query Language）：資料查詢語言，用來查詢記錄（資料）。

3　DDL

3.1　基本操作

• 檢視所有資料庫名稱：SHOW DATABASES；
• 切換資料庫：USE mydb1，切換到mydb1資料庫；

3.2　運算元據庫

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

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

• 修改資料庫編碼：ALTER DATABASE mydb1 CHARACTER SET utf8
  修改資料庫mydb1的編碼為utf8。注意，在MySQL中所有的UTF-8編碼都不能使用中間的「-」，即UTF-8要書寫為UTF8。

3.3　資料型別

MySQL與Java一樣，也有資料型別。MySQL中資料型別主要應用在列上。

常用型別：

• int：整型
• double：浮點型，例如double(5,2)表示最多5位，其中必須有2位小數，即最大值為999.99；
• decimal：泛型型，在表單錢方面使用該型別，因為不會出現精度缺失問題；
• char：固定長度255字串型別；char(20)
• Varchar：可變長度字串型別；varchar(20)
• text：字串型別；
• blob：位元組型別；
• date：日期型別，格式為：yyyy-MM-dd；
• time：時間型別，格式為：hh:mm:ss
• timestamp：時間戳型別；

3.4　操作表

• 建立表：
  CREATE TABLE 表名(
  列名 列型別,
  列名 列型別,
  …
  );
  例如：

CREATE TABLE stu( sid CHAR(6), sname VARCHAR(20), age INT,
gender VARCHAR(10) );

再例如：

CREATE TABLE emp( eid CHAR(6), ename VARCHAR(50), age INT,
gender VARCHAR(6), birthday DATE, hiredate DATE,
salary DECIMAL(7,2), resume VARCHAR(1000) );

• 檢視當前資料庫中所有表名稱：SHOW TABLES；
• 檢視指定表的建立語句：SHOW CREATE TABLE emp，檢視emp表的建立語句；
• 檢視表結構：DESC emp，檢視emp表結構；
• 刪除表：DROP TABLE emp，刪除emp表；
• 修改表：
  1.修改之新增列：給stu表新增classname列：
  ALTER TABLE stu ADD (classname varchar(100));
  2.修改之修改列型別：修改stu表的gender列型別為CHAR(2)：
  ALTER TABLE stu MODIFY gender CHAR(2);
  3.修改之修改列名：修改stu表的gender列名為sex：
  ALTER TABLE stu change gender sex CHAR(2);
  4.修改之刪除列：刪除stu表的classname列：
  ALTER TABLE stu DROP classname;
  5.修改之修改表名稱：修改stu表名稱為student：
  ALTER TABLE stu RENAME TO student;

4　DML

4.1　插入資料

語法：
INSERT INTO 表名(列名1,列名2, …) VALUES(值1, 值2)
I

NSERT INTO stu(sid, sname,age,gender) VALUES(‘s_1001’, ‘zhangSan’, 23,
‘male’); INSERT INTO stu(sid, sname) VALUES(‘s_1001’, ‘zhangSan’);

語法：
INSERT INTO 表名 VALUES(值1,值2,…)
因為沒有指定要插入的列，表示按建立表時列的順序插入所有列的值：
INSERT INTO stu VALUES(‘s_1002’, ‘liSi’, 32, ‘female’);

注意：所有字串資料必須使用單參照！

4.2　修改資料

語法：
UPDATE 表名 SET 列名1=值1, … 列名n=值n [WHERE 條件]
where條件中使用的 操作符

，<，<＝，>＝，＝，!＝，<>（比較運算）
AND， NOT， OR
BETWEEN … AND … 和NOT BETWEEN … AND
IS [NOT] NULL （空值判斷）

UPDATE stu SET sname=’zhangSanSan’, age=’32’, gender=’female’ WHERE sid=’s_1001’;
UPDATE stu SET sname=’liSi’, age=’20’ WHERE age>50 AND gender=’male’;
UPDATE stu SET sname=’wangWu’, age=’30’ WHERE age>60 OR gender=’female’;
UPDATE stu SET gender=’female’ WHERE gender IS NULL
UPDATE stu SET age=age+1 WHERE sname=’zhaoLiu’;

4.3　刪除資料
語法：
DELETE FROM 表名 [WHERE 條件]

DELETE FROM stu WHERE sid=’s_1001’003B DELETE FROM stu WHERE
sname=’chenQi’ OR age > 30; DELETE FROM stu;

語法：
TRUNCATE TABLE 表名
TRUNCATE TABLE stu;

雖然TRUNCATE和DELETE都可以刪除表的所有記錄，但有原理不同。DELETE的效率沒有TRUNCATE高！
TRUNCATE其實屬性DDL語句，因為它是先DROP TABLE，再CREATE TABLE。而且TRUNCATE刪除的記錄是無法回滾的，但DELETE刪除的記錄是可以回滾的（回滾是事務的知識！）。

5　DCL

5.1　建立使用者

語法：
CREATE USER 使用者名稱@地址 IDENTIFIED BY ‘密碼’;

CREATE USER user1@localhost IDENTIFIED BY ‘123’;
CREATE USER user2@’%’ IDENTIFIED BY ‘123’;

5.2　給使用者授權

語法：
GRANT 許可權1, … , 許可權n ON 資料庫.* TO 使用者名稱

GRANT CREATE,ALTER,DROP,INSERT,UPDATE,DELETE,SELECT ON mydb1.* TO
user1@localhost;
GRANT ALL ON mydb1.* TO user2@localhost;

5.3　復原授權

語法：
　　REVOKE許可權1, … , 許可權n ON 資料庫.* FORM 使用者名稱
REVOKE CREATE,ALTER,DROP ON mydb1.* FROM user1@localhost;

5.4　檢視使用者許可權

語法：
SHOW GRANTS FOR 使用者名稱
SHOW GRANTS FOR user1@localhost;

5.5　刪除使用者

語法：
DROP USER 使用者名稱
DROP USER user1@localhost;

5.6　修改使用者密碼

語法：
USE mysql;
UPDATE USER SET PASSWORD=PASSWORD(‘密碼’) WHERE User=’使用者名稱’ and Host=’IP’;
FLUSH PRIVILEGES;
UPDATE USER SET PASSWORD=PASSWORD(‘1234’) WHERE User=‘user2’ and Host=’localhost’;
FLUSH PRIVILEGES;

資料查詢語法（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 /*結果限定*/

建立名：

• 學生表：stu
  

CREATE TABLE stu (
	sid	CHAR(6),
	sname		VARCHAR(50),
	age		INT,
	gender	VARCHAR(50)
);

INSERT INTO stu VALUES('S_1001', 'liuYi', 35, 'male');
INSERT INTO stu VALUES('S_1002', 'chenEr', 15, 'female');
INSERT INTO stu VALUES('S_1003', 'zhangSan', 95, 'male');
INSERT INTO stu VALUES('S_1004', 'liSi', 65, 'female');
INSERT INTO stu VALUES('S_1005', 'wangWu', 55, 'male');
INSERT INTO stu VALUES('S_1006', 'zhaoLiu', 75, 'female');
INSERT INTO stu VALUES('S_1007', 'sunQi', 25, 'male');
INSERT INTO stu VALUES('S_1008', 'zhouBa', 45, 'female');
INSERT INTO stu VALUES('S_1009', 'wuJiu', 85, 'male');
INSERT INTO stu VALUES('S_1010', 'zhengShi', 5, 'female');
INSERT INTO stu VALUES('S_1011', 'xxx', NULL, NULL);

• 僱員表：emp
  

CREATE TABLE emp(
	empno		INT,
	ename		VARCHAR(50),
	job		VARCHAR(50),
	mgr		INT,
	hiredate	DATE,
	sal		DECIMAL(7,2),
	comm		decimal(7,2),
	deptno		INT
) ;

INSERT INTO emp values(7369,'SMITH','CLERK',7902,'1980-12-17',800,NULL,20);
INSERT INTO emp values(7499,'ALLEN','SALESMAN',7698,'1981-02-20',1600,300,30);
INSERT INTO emp values(7521,'WARD','SALESMAN',7698,'1981-02-22',1250,500,30);
INSERT INTO emp values(7566,'JONES','MANAGER',7839,'1981-04-02',2975,NULL,20);
INSERT INTO emp values(7654,'MARTIN','SALESMAN',7698,'1981-09-28',1250,1400,30);
INSERT INTO emp values(7698,'BLAKE','MANAGER',7839,'1981-05-01',2850,NULL,30);
INSERT INTO emp values(7782,'CLARK','MANAGER',7839,'1981-06-09',2450,NULL,10);
INSERT INTO emp values(7788,'SCOTT','ANALYST',7566,'1987-04-19',3000,NULL,20);
INSERT INTO emp values(7839,'KING','PRESIDENT',NULL,'1981-11-17',5000,NULL,10);
INSERT INTO emp values(7844,'TURNER','SALESMAN',7698,'1981-09-08',1500,0,30);
INSERT INTO emp values(7876,'ADAMS','CLERK',7788,'1987-05-23',1100,NULL,20);
INSERT INTO emp values(7900,'JAMES','CLERK',7698,'1981-12-03',950,NULL,30);
INSERT INTO emp values(7902,'FORD','ANALYST',7566,'1981-12-03',3000,NULL,20);
INSERT INTO emp values(7934,'MILLER','CLERK',7782,'1982-01-23',1300,NULL,10);

• 部分表：dept
  

CREATE TABLE dept(
	deptno		INT,
	dname		varchar(14),
	loc		varchar(13)
);

INSERT INTO dept values(10, 'ACCOUNTING', 'NEW YORK');
INSERT INTO dept values(20, 'RESEARCH', 'DALLAS');
INSERT INTO dept values(30, 'SALES', 'CHICAGO');
INSERT INTO dept values(40, 'OPERATIONS', 'BOSTON');

1　基礎查詢

1.1　查詢所有列

SELECT * FROM stu;

1.2　查詢指定列

SELECT sid, sname, age FROM stu;

2　條件查詢

2.1　條件查詢介紹

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

• =、!=、<>、<、<=、>、>=；
• BETWEEN…AND；
• IN(set)；
• IS NULL；
• AND；
• OR；
• 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字母的學生時就需要使用模糊查詢了。模糊查詢需要使用關鍵字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」開頭的學生記錄

SELECT *
FROM stu
WHERE sname LIKE ‘z%’;
其中「%」匹配0~n個任何字母。

3.4　查詢姓名中第2個字母為「i」的學生記錄

SELECT *
FROM stu
WHERE sname LIKE ‘_i%’;

3.5　查詢姓名中包含「a」字母的學生記錄

SELECT *
FROM stu
WHERE sname LIKE ‘%a%’;

4　欄位控制查詢

4.1　去除重複記錄

去除重複記錄（兩行或兩行以上記錄中系列的上的資料都相同），例如emp表中sal欄位就存在相同的記錄。當只查詢emp表的sal欄位時，那麼會出現重複記錄，那麼想去除重複記錄，需要使用DISTINCT：
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　給列名新增別名

在上面查詢中出現列名為sal+IFNULL(comm,0)，這很不美觀，現在我們給這一列給出一個別名，為total：
SELECT *, sal+IFNULL(comm,0) AS total FROM emp;
給列起別名時，是可以省略AS關鍵字的：
SELECT *,sal+IFNULL(comm,0) total FROM emp;

5　排序

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

SELECT *
FROM stu
ORDER BY sage ASC;
或者
SELECT *
FROM stu
ORDER BY sage;

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

SELECT *
FROM stu
ORDER BY age DESC;

5.3　查詢所有僱員，按月薪降序排序，如果月薪相同時，按編號升序排序

SELECT * FROM emp
ORDER BY sal DESC,empno ASC;
表之間的關係
Select 列
From 表
Where 條件
Group by 分組
Having 分組條件
Order by 排序

6　聚合函數

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

• COUNT()：統計指定列不為NULL的記錄行數；
• MAX()：計算指定列的最大值，如果指定列是字串型別，那麼使用字串排序運算；
• MIN()：計算指定列的最小值，如果指定列是字串型別，那麼使用字串排序運算；
• SUM()：計算指定列的數值和，如果指定列型別不是數值型別，那麼計算結果為0；
• AVG()：計算指定列的平均值，如果指定列型別不是數值型別，那麼計算結果為0；

6.1　COUNT

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

• 查詢emp表中記錄數：
  SELECT COUNT(*) AS cnt FROM emp;

• 查詢emp表中有佣金的人數：
  SELECT COUNT(comm) cnt FROM emp;
  注意，因為count()函數中給出的是comm列，那麼只統計comm列非NULL的行數。

• 查詢emp表中月薪大於2500的人數：
  SELECT COUNT(*) FROM emp
  WHERE sal > 2500;

• 統計月薪與佣金之和大於2500元的人數：
  SELECT COUNT(*) AS cnt FROM emp WHERE sal+IFNULL(comm,0) > 2500;

• 查詢有佣金的人數，以及有領導的人數：
  SELECT COUNT(comm), COUNT(mgr) FROM emp;

6.2　SUM和AVG

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

• 查詢所有僱員月薪和：
  SELECT SUM(sal) FROM emp;

• 查詢所有僱員月薪和，以及所有僱員佣金和：
  SELECT SUM(sal), SUM(comm) FROM emp;

• 查詢所有僱員月薪+佣金和：
  SELECT SUM(sal+IFNULL(comm,0)) FROM emp;

• 統計所有員工平均工資：
  SELECT SUM(sal), COUNT(sal) FROM emp;
  或者
  SELECT AVG(sal) FROM emp;

6.3　MAX和MIN

• 查詢最高工資和最低工資：
  SELECT MAX(sal), MIN(sal) FROM emp;

7　分組查詢

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

7.1　分組查詢

• 查詢每個部門的部門編號和每個部門的工資和：
  SELECT deptno, SUM(sal)
  FROM emp
  GROUP BY deptno;

• 查詢每個部門的部門編號以及每個部門的人數：
  SELECT deptno,COUNT(*)
  FROM emp
  GROUP BY deptno;

• 查詢每個部門的部門編號以及每個部門工資大於1500的人數：
  SELECT deptno,COUNT(*)
  FROM emp
  WHERE sal>1500
  GROUP BY deptno;

7.2　HAVING子句

• 查詢工資總和大於9000的部門編號以及工資和：
  SELECT deptno, SUM(sal)
  FROM emp
  GROUP BY deptno
  HAVING SUM(sal) > 9000;
  　　
  注意，WHERE是對分組前記錄的條件，如果某行記錄沒有滿足WHERE子句的條件，那麼這行記錄不會參加分組；而HAVING是對分組後資料的約束。

8　LIMIT

LIMIT用來限定查詢結果的起始行，以及總行數。
Limit (當前頁-1)*每頁顯示條數，每頁顯示條數。

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頁記錄應該怎麼查呢？

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

完整性約束

完整性約束是為了表的資料的正確性！如果資料不正確，那麼一開始就不能新增到表中。

1　主鍵

當某一列新增了主鍵約束後，那麼這一列的資料就不能重複出現。這樣每行記錄中其主鍵列的值就是這一行的唯一標識。例如學生的學號可以用來做唯一標識，而學生的姓名是不能做唯一標識的，因為學生有可能同名。
主鍵列的值不能為NULL，也不能重複！
　　指定主鍵約束使用PRIMARY KEY關鍵字

• 建立表：定義列時指定主鍵：
  CREATE TABLE stu(
  sid CHAR(6) PRIMARY KEY,
  sname VARCHAR(20),
  age INT,
  gender VARCHAR(10)
  );
• 建立表：定義列之後獨立指定主鍵：

CREATE TABLE stu(
sid CHAR(6),
sname VARCHAR(20),
age INT,
gender VARCHAR(10),
PRIMARY KEY(sid)
);

• 修改表時指定主鍵：
  ALTER TABLE stu
  ADD PRIMARY KEY(sid);

• 刪除主鍵（只是刪除主鍵約束，而不會刪除主鍵列）：
  ALTER TABLE stu DROP PRIMARY KEY;

2　主鍵自增長

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

• 建立表時設定主鍵自增長（主鍵必須是整型才可以自增長）：
  CREATE TABLE stu(
  sid INT PRIMARY KEY AUTO_INCREMENT,
  sname VARCHAR(20),
  age INT,
  gender VARCHAR(10)
  );

• 修改表時設定主鍵自增長：
  ALTER TABLE stu CHANGE sid sid INT AUTO_INCREMENT;

• 修改表時刪除主鍵自增長：
  ALTER TABLE stu CHANGE sid sid INT;

3　非空

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

• 指定非空約束：
  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　唯一

還可以為欄位指定唯一約束！當為欄位指定唯一約束後，那麼欄位的值必須是唯一的。這一點與主鍵相似！例如給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）三者之間的關係。

  例如在t_section表中sid為1的記錄說明有一個分類叫java，版主是t_user表中uid為1的使用者，即zs！
  例如在t_topic表中tid為2的記錄是名字為「Java是咖啡」的貼文，它是java版塊的貼文，它的作者是ww。
外來鍵就是用來約束這一列的值必須是另一張表的主鍵值！！！

• 建立t_user表，指定uid為主鍵列：
  CREATE TABLE t_user(
  uid INT PRIMARY KEY AUTO_INCREMENT,
  uname VARCHAR(20) UNIQUE NOT NULL
  );

• 建立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)
  );

• 修改t_section表，指定u_id為相對t_user表的uid列的外來鍵：
  ALTER TABLE t_section
  ADD CONSTRAINT fk_t_user
  FOREIGN KEY(u_id)
  REFERENCES t_user(uid);

• 修改t_section表，刪除u_id的外來鍵約束：
  ALTER TABLE t_section
  DROP FOREIGN KEY fk_t_user;

6　表與表之間的關係

• 一對一：例如t_person表和t_card表，即人和身份證。這種情況需要找出主從關係，即誰是主表，誰是從表。人可以沒有身份證，但身份證必須要有人才行，所以人是主表，而身份證是從表。設計從表可以有兩種方案：
• 在t_card表中新增外來鍵列（相對t_user表），並且給外來鍵新增唯一約束；
• 給t_card表的主鍵新增外來鍵約束（相對t_user表），即t_card表的主鍵也是外來鍵。
• 一對多（多對一）：最為常見的就是一對多！一對多和多對一，這是從哪個角度去看得出來的。t_user和t_section的關係，從t_user來看就是一對多，而從t_section的角度來看就是多對一！這種情況都是在多方建立外來鍵！
• 多對多：例如t_stu和t_teacher表，即一個學生可以有多個老師，而一個老師也可以有多個學生。這種情況通常需要建立中間表來處理多對多關係。例如再建立一張表t_stu_tea表，給出兩個外來鍵，一個相對t_stu表的外來鍵，另一個相對t_teacher表的外來鍵。

編碼

專注於IT技術知識梳理，書寫前沿技術文章！更多技術在這裡慢慢探索吧！

1　檢視MySQL編碼

SHOW VARIABLES LIKE ‘char%’;

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

• character_set_client：你傳送的資料必須與client指定的編碼一致！！！伺服器會使用該編碼來解讀使用者端傳送過來的資料；
• character_set_connection：通過該編碼與client一致！該編碼不會導致亂碼！當執行的是查詢語句時，使用者端傳送過來的資料會先轉換成connection指定的編碼。但只要使用者端傳送過來的資料與client指定的編碼一致，那麼轉換就不會出現問題；
• character_set_database：資料庫預設編碼，在建立資料庫時，如果沒有指定編碼，那麼預設使用database編碼；
• character_set_server：MySQL伺服器預設編碼；
• 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。

• 修改character_set_client變數：set character_set_client=gbk;
• 修改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上的設定覆蓋了，也就不會出現亂碼了。

MySQL資料庫備份與還原

備份和恢復資料

1　生成SQL指令碼

在控制檯使用mysqldump命令可以用來生成指定資料庫的指令碼文字，但要注意，指令碼文字中只包含資料庫的內容，而不會存在建立資料庫的語句！所以在恢復資料時，還需要自已手動建立一個資料庫之後再去恢復資料。
　　mysqldump –u使用者名稱 –p密碼 資料庫名>生成的指令碼檔案路徑

現在可以在C槽下找到mydb1.sql檔案了！
注意，mysqldump命令是在Windows控制檯下執行，無需登入mysql！！！

2　執行SQL指令碼

執行SQL指令碼需要登入mysql，然後進入指定資料庫，才可以執行SQL指令碼！！！
執行SQL指令碼不只是用來恢復資料庫，也可以在平時編寫SQL指令碼，然後使用執行SQL 指令碼來運算元據庫！大家都知道，在黑畫面下編寫SQL語句時，就算髮現了錯誤，可能也不能修改了。所以我建議大家使用指令碼檔案來編寫SQL程式碼，然後執行之！
SOURCE C:\mydb1.sql

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

還可以通過下面的方式來執行指令碼檔案：
mysql -uroot -p123 mydb1<c:\mydb1.sql
mysql –u使用者名稱 –p密碼 資料庫<要執行指令碼檔案路徑

這種方式無需登入mysql！

多表查詢

多表查詢有如下幾種：

• 合併結果集；
• 連線查詢
• 內連線
• 外連線
• 左外連線
• 右外連線
• 自然連線

1　合併結果集

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

• 2.合併結果集有兩種方式：
  UNION：去除重複記錄，例如：SELECT * FROM t1 UNION SELECT * FROM t2；
  UNION ALL：不去除重複記錄，例如：SELECT * FROM t1 UNION ALL SELECT * FROM t2。

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

2　連線查詢

連線查詢就是求出多個表的乘積，例如t1連線t2，那麼查詢出的結果就是t1*t2。

連線查詢會產生笛卡爾積，假設集合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表的同時，把每個員工的所在部門資訊顯示出來，那麼就需要使用主外來鍵來去除無用資訊了。

使用主外來鍵關係做為條件來去除無用資訊
SELECT * FROM emp,dept WHERE emp.deptno=dept.deptno;

上面查詢結果會把兩張表的所有列都查詢出來，也許你不需要那麼多列，這時就可以指定要查詢的列了。
SELECT emp.ename,emp.sal,emp.comm,dept.dname
FROM emp,dept
WHERE emp.deptno=dept.deptno;

還可以為表指定別名，然後在參照列時使用別名即可。
SELECT e.ename,e.sal,e.comm,d.dname
FROM emp AS e,dept AS d
WHERE e.deptno=d.deptno;

2.1　內連線

上面的連線語句就是內連線，但它不是SQL標準中的查詢方式，可以理解為方言！SQL標準的內連線為：
SELECT *
FROM emp e
INNER JOIN dept d
ON e.deptno=d.deptno;

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

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

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

外連線的特點：查詢出的結果存在不滿足條件的可能。
左連線：
SELECT * FROM emp e
LEFT OUTER JOIN dept d
ON e.deptno=d.deptno;

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

2.3　右連線

右連線就是先把右表中所有記錄都查詢出來，然後左表滿足條件的顯示，不滿足顯示NULL。例如在dept表中的40部門並不存在員工，但在右連線中，如果dept表為右表，那麼還是會查出40部門，但相應的員工資訊為NULL。
SELECT * FROM emp e
RIGHT OUTER JOIN dept d
ON e.deptno=d.deptno;

連線查詢心得：
連線不限與兩張表，連線查詢也可以是三張、四張，甚至N張表的連線查詢。通常連線查詢不可能需要整個笛卡爾積，而只是需要其中一部分，那麼這時就需要使用條件來去除不需要的記錄。這個條件大多數情況下都是使用主外來鍵關係去除。
兩張表的連線查詢一定有一個主外來鍵關係，三張表的連線查詢就一定有兩個主外來鍵關係，所以在大家不是很熟悉連線查詢時，首先要學會去除無用笛卡爾積，那麼就是用主外來鍵關係作為條件來處理。如果兩張表的查詢，那麼至少有一個主外來鍵條件，三張表連線至少有兩個主外來鍵條件。

3　自然連線

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

• 兩張連線的表中名稱和型別完成一致的列作為條件，例如emp和dept表都存在deptno列，並且型別一致，所以會被自然連線找到！
  當然自然連線還有其他的查詢條件的方式，但其他方式都可能存在問題！
  SELECT * FROM emp NATURAL JOIN dept;
  SELECT * FROM emp NATURAL LEFT JOIN dept;
  SELECT * FROM emp NATURAL RIGHT JOIN dept;

4　子查詢

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

• 子查詢出現的位置：
    where後，作為條件的一部分；
     from後，作為被查詢的一條表；
• 當子查詢出現在where後作為條件時，還可以使用如下關鍵字：
    any
     all
• 子查詢結果集的形式：
    單行單列（用於條件）
    單行多列（用於條件）
    多行單列（用於條件）
    多行多列（用於表）

練習：

1.工資高於甘寧的員工。
分析：
查詢條件：工資>甘寧工資，其中甘寧工資需要一條子查詢。

第一步：查詢甘寧的工資
SELECT sal FROM emp WHERE ename=‘甘寧’

第二步：查詢高於甘寧工資的員工
SELECT * FROM emp WHERE sal > (${第一步})

結果：
SELECT * FROM emp WHERE sal > (SELECT sal FROM emp WHERE ename=‘甘寧’)

子查詢作為條件
子查詢形式為單行單列

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

第一步：查詢30部門所有人工資
SELECT sal FROM emp WHERE deptno=30;

第二步：查詢高於30部門所有人工資的員工資訊
SELECT * FROM emp WHERE sal > ALL (${第一步})

結果：
SELECT * FROM emp WHERE sal > ALL (SELECT sal FROM emp WHERE deptno=30)

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

3.查詢工作和工資與殷天正完全相同的員工資訊
分析：
查詢條件：工作和工資與殷天正完全相同，這是子查詢

第一步：查詢出殷天正的工作和工資
SELECT job,sal FROM emp WHERE ename=‘殷天正’

第二步：查詢出與殷天正工作和工資相同的人
SELECT * FROM emp WHERE (job,sal) IN (${第一步})

結果：
SELECT * FROM emp WHERE (job,sal) IN (SELECT job,sal FROM emp WHERE ename=‘殷天正’)

子查詢作為條件
子查詢形式為單行多列

4.查詢員工編號為1006的員工名稱、員工工資、部門名稱、部門地址
分析：
查詢列：員工名稱、員工工資、部門名稱、部門地址
查詢表：emp和dept，分析得出，不需要外連線（外連線的特性：某一行（或某些行）記錄上會出現一半有值，一半為NULL值）
條件：員工編號為1006

第一步：去除多表，只查一張表，這裡去除部門表，只查員工表
SELECT ename, sal FROM emp e WHERE empno=1006

第二步：讓第一步與dept做內連線查詢，新增主外來鍵條件去除無用笛卡爾積
SELECT e.ename, e.sal, d.dname, d.loc
FROM emp e, dept d
WHERE e.deptno=d.deptno AND empno=1006

第二步中的dept表表示所有行所有列的一張完整的表，這裡可以把dept替換成所有行，但只有dname和loc列的表，這需要子查詢。
第三步：查詢dept表中dname和loc兩列，因為deptno會被作為條件，用來去除無用笛卡爾積，所以需要查詢它。
SELECT dname,loc,deptno FROM dept;

第四步：替換第二步中的dept
SELECT e.ename, e.sal, d.dname, d.loc
FROM emp e, (SELECT dname,loc,deptno FROM dept) d
WHERE e.deptno=d.deptno AND e.empno=1006

子查詢作為表
子查詢形式為多行多列