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

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