資料類型

整型資料

整型資料	位元組數	無符号的取值範圍	有符号的取值範圍
TINYINT	1	0~225	-125~127
SMALLINT	2	0-65535	-32768~32768
MEDIUMINT	3	0~16777215	···
INT	4	0-4294967295	···
BIGINT	8	0~18446744073709551615

浮點型

浮點型資料	位元組數
FLOAT	4
DOUBLE	8
定點數型資料
DECIMAL（（）M , 2）	M+2

日期時間類型

資料類型	位元組數	取值範圍	日期格式	零值
YEAR	1	1901~2155	YYYY	0000
DATE	4	1000-01-01-9999-12-3	YYYY-MM-DD	0000-00-00
TIME	3	-838:59:59-838:59:59	HH:MM:SS	00:00:00
DATETIME	8	11	YYYY-MM-DD、HH:MM:SS	0000-00-00 00:00:00
TIMESTAMP（時間戳）	4	1970-01-01 00:00:01~2038-01-19 03:14:07	YYYY-MM-DD、HH:MM:SS	0000-00-00 00:00:00

字元串型

字元串類型(機關：位元組)	位元組狀态	優點
CHAR（（int）Max）	固定	查詢快
VARCHAR（（int）Max）	動态	省空間

二進制資料類型

二進制資料類型（機關：長度）	長度狀态
BINARY（（int）Max）	固定
VARBINARY（（int）Max）	動态

BINARY類型的長度是固定的，如果長度不足最大長度,後面用“0”對齊，直到指定長度。

大文本資料類型

大文本資料類型	存儲範圍（機關：位元組）
TINYTEXT	0~255位元組
TEXT	0~65535位元組
MEDIUMTEXT	0~16777215位元組
LONGTEXT	0~4294967295位元組

大資料二進制類型

特殊二進制類型	存儲範圍（機關：位元組）
TINYBLOB	0~255位元組
BLOB	0-65535位元組
MEDIUMBLOB	0~16777215位元組
LONGBLOB	0-4294967295位元組

枚舉型

ENUM枚舉型，單選項

多選項

DDL操作資料庫

DDL (Data Definition Language):資料定義語言，定義資料庫對象:庫、表、列等

建立資料庫

CREATE DATABASE 庫名;

CREATE DATABASE IF NOT EXISTS 庫名;

CREATE DATABASE 庫名 CHARACTER SET 編碼方式;

CREATE DATABASE 庫名 CHARACTER SET 編碼方式 COLLATE 排序規則;

CREATE DATABASE ：建立資料庫

IF NOT EXISTS ：檢查是否已存在的狀态

CHARACTER SET：設定編碼方式

COLLATE ：排序規則

檢視資料庫

檢視目前資料庫:

SHOW DATABASES;

檢視建立的庫:

SHOW CREATE DATABASE 庫名;

檢視目前所選的資料庫：

SELECT DATABASE();

修改資料庫

ALTER DATABASE 庫名;

ALTER DATABASE IF NOT EXISTS 庫名;

ALTER DATABASE 庫名 CHARACTER SET 編碼方式;

ALTER DATABASE 庫名 CHARACTER SET 編碼方式 COLLATE 排序規則;

ALTER DATABASE ：修改資料庫

IF NOT EXISTS ：檢查是否已存在的狀态

CHARACTER SET ：設定編碼方式

COLLATE ：排序規則

删除資料庫

DROP DATABASE 庫名;

選擇資料庫

USE 庫名;

DDL表列操作

DDL (Data Definition Language):資料定義語言，定義資料庫對象:庫、表、列等

建立表

CREATE TABLE 表名(
   字段名1 資料類型 [完整性限制條件] [COMMENT '注釋内容'],
   字段名2 資料類型 [完整性限制條件] [COMMENT '注釋内容'],
   ·······
   字段名n 資料類型 [完整性限制條件] [COMMENT '注釋内容'],
   [完整性限制條件](字段1,字段2,·····,字段n)
)[編碼集設定];

編碼集設定:

CHARACTER SET 字元集名 [校對規則]

校對規則:

COLLATE 校對名

檢視表

檢視指定表字段結構：

DESC 表名;

檢視目前資料庫所有表:

SHOW TABLES;

檢視表的詳細結構：

SHOW CREATE TABLE 表名;

修改表

修改表名

RENAME TABLE 表名 to 新表名;

ALTER TABLE 表名 RENAME 新表名;

增加字段

ALTER TABLE 表名 ADD 字段名資料類型;

字段修改與順序

更改字段循序:

FIRST | （AFTER 字段名2）

FIRST：指定字段為表的第一個

AFTER：指定字段插入字段2的後面

修改字段名

ALTER TABLE 表名 CHANGE 字段名新字段名新字段類型;

修改表字元集

ALTER TABLE 表名 CHARACTER SET 字元集類型;

删除字段

ALTER TABLE 表名 DROP 字段名;

删除表

DROP TABLE 表名;

索引

索引的目的在于提高查詢效率，與我們查字典所用的目錄是一個道理

索引建立

建表時建立索引：

CREATE TABLE 表名(
	字段名1 資料類型 [完整性限制條件]，
	字段名2 資料類型 [完整性限制條件]，
     ·····
	字段名n 資料類型
[UNIQUE | FULLTEXT | SPATIAL] INDEX | KEY
	[索引名] (字段名1 [(長度)]) [ASC | DESC])
)[存儲引擎];

UNIQUE：可選參數，表示唯一性限制

FULLTEXT：可選參數，表示全文限制

SPATIAL：可選參數，表示空間限制

INDEX 和 KEY：用來表示字段的索引，二者選一即可

索引名：可選參數，表示建立的索引的名稱

字段名1：指定索引對應字段的名稱

長度：可選參數，用于表示索引的長度

ASC 和 DESC：可選參數，其中，ASC表示升序排列，DESC表示降序排列

單列索引

在表中單個字段上建立索引，它可以是普通索引、唯一索引或者全文素引，隻要保證該索引隻對應表中一個字段即可。

INDEX 索引名 (字段名1[,字段名2…]名);

CREATE INDEX 索引名 ON 表名 (字段名1[,字段名2…]);

ALTER TABLE 表名 ADD INDEX 索引名 (字段名1[,字段名2…]);

唯一索引 (UNIQUE )

使字段的值必須是唯一的

UNIQUE INDEX 索引名 (字段名 (ASC | DESC));

CREATE UNIQUE INDEX 索引名 ON 表名 (字段名);

ALTER TABLE 表名 ADD UNIQUE INDEX 索引名 (字段名);

全文索引 (FULLTEXT)

隻能建立在CHAR、VARCHAR或TEXT類型的字段上，現在隻有MyISAM存儲引擎支援全文索引

FULLTEXT INDEX 索引名 (字段名);

CREATE FULLTEXT INDEX 索引名 ON 表名 (字段名);

ALTER TABLE 表名 ADD FULLTEXT INDEX 索引名 (字段名);

注意：後面需存儲引擎(ENGINE =MyISAM)

空間索引 (SPATIAL)

隻能建立在空間資料類型的字段上。MySQL中的空間資料類型有4種,分别是LGEOMETRY、POINT、 LINESTRING和POLYGON，空間索引隻能在存儲引擎為MyISAM的表中建立

SPATIAL INDEX 索引名 (字段名);

CREATE SPATIAL INDEX 索引名 ON 表名 (字段名);

ALTER TABLE 表名 ADD SPATIAL INDEX 索引名 (字段名);

注意: 後面需存儲引擎（ENGINE =MyISAM）

索引删除

ALTER TABLE 表名 DROP INDEX 索引名;

DROP INDEX 索引名 ON 表名;

DML資料操作

DML (Data Manipulation Language):資料操作語言，定義資料庫記錄(資料）增删改的操作

插入資料

一條資料添加

INSERT INTO 表名 [(字段名1 [，字段名2. . .])] VALUES(值1 [,值2. . .]);

多條資料添加

INSERT INTO 表名 [(字段名)] VALUES (字段值1),(字段值2)…;

注意：

插入字段與它的資料類型位置是一一對應的
資料類型對應的值，字段值是非數值，則兩側必須添加單引号
null設定為空
資料的大小應在字段的資料類型規定範圍内
如果要插入所有字段資料時可以省寫字段，但必須按表中字段順序寫值
盡可能避免字段資料值寫入的是 null

更改資料

更改單表資料

UPDATE 表名 SET 字段名1 = 字段值1 [,字段名2 = 值2…] [WHERE 條件表達式];

更改多表資料

UPDATE 表名1 , 表名2 SET {修改字段值，可跨表修改} [WHERE 條件表達式];

注意：

邏輯運算符有： and（并且）、or（或者）、not（取非）
如果更改字段無條件表達式，則指定全部該字段的值一緻

删除資料

DELETE FROM 表名 [WHERE 條件表達式];

TRUNCATE TABLE 表名;

多表删除

DELETE {表名1,表名2…} FROM {表名1,表名2…} [WHERE 條件表達式];

注意：

如果無條件表達式，則删除全部資料
DELETE删除可找回
TRUNCATE删除不可找回，類似格式化資料，執行快
不能删除某列的值（可修改資料值置NULL）
多表删除建議WHERE過濾他們字段的關系
多表中的每張表需要逗号分隔

DCL安全通路

DCL (Data Control Language)︰資料控制語言，用來定義通路權限和安全級别

建立使用者

CREATE USER 使用者名@指定ip IDENTIFIED BY 密碼;

CREATE USER 使用者名@’%’ IDENTIFIED BY 密碼;

指定ip / 任意ip 可登入

授權使用者

GRANT 權限1[,權限2…權限n] ON 庫名.* TO 使用者名@指定ip;

GRANT ALL ON

TO 使用者名@指定ip;

指定權限 / 所有權限使用者授權

(指定權限自行查詢)

查詢權限

SHOW GRANTS FOR 使用者名@指定IP;

SHOW GRANTS FOR 使用者名@‘%’;

查詢指定 / 查詢所有 IP的權限情況

撤銷權限

REVOKE 權限1[,權限2…權限n] ON 庫名.* FROM 使用者名@指定IP;

删除使用者

DROP USER 使用者名@指定IP

DQL資料查詢

DQL（Data Query Language）：資料查詢語言，用來查詢記錄（資料）查詢

資料庫執行DQL語句不會對資料進行改變，而是讓資料庫發送結果集給用戶端

查詢傳回的結果集是一張虛拟表

SELECT的文法結構：

可選參數:

[WHERE 條件表達式];

[GROUP BY 字段名 [HAVING 條件表達式2]] ;

[ORDER BY 字段名 [ASC | DESC]];

[LIMIT [OFFSET] (int)記錄數];

别名：代替字段名

DISTINCT：過濾表字段中重複的值（資料），如果指定是多個字段，而且指定的字段值相同，則過濾重複的值！！！

WHERE：指定查詢條件

GROUP BY：将查詢結果按指定字段進行分組

HAVING：對分組後的結果進行過濾

ORDER BY：将查詢結果按指定字段進行排序，排列方式有參數ASC（升序）、DESC（降序）控制，預設為ASC（升序）

LIMIT：限制查詢結果的數量，後面有可有兩個參數，

OFFSET：表示偏移量，如果偏移量為0則從第一條開始,。不指定參數1，其預設值為0。

記錄數：表示傳回查詢記錄的條數

代碼示例

# 學生表
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);
INSERT INTO stu VALUES('S_1010', 'zhengShi',5, 'female');

# 雇員表
CREATE TABLE emp2(
	empno int,
	ename VARCHAR(50),
	job VARCHAR(50),
	mgr INT,
	hiredate DATE,sal DECIMAL(7,2),
	comm decimal(7,2),
	deptno int
);
# 添加資料
INSERT INTO emp2 values(7369,'SMITH','CLERK',7902,'1980-12-17',800,NULL,20);
INSERT INTO emp2 values(7499,'ALLEN','SALESMAN',7698,'1981-02-20',1600,300,30);
INSERT INTO emp2 values(7521,'WARD','SALESMAN',7698,'1981-02-22',1250,500,30);
INSERT INTO emp2 values(7566,'JONES', 'MANAGER',7839,'1981-04-02',2975,NULL,20);
INSERT INTO emp2 values(7654,'MARTIN','SALESMAN',7698,'1981-09-28',1250,1400,30);
INSERT INTO emp2 values(7698,'BLAKE','MANAGER',7839,'1981-05-01',2850,NULL,30);
INSERT INTO emp2 values(7782,'CLARK','MANAGER',7839,'1981-06-09',2450,NULL,10);
INSERT INTO emp2 values(7788,'SCOTT','ANALYST',7566,'1987-04-19',3000,NULL,20);
INSERT INTO emp2 values(7839,'KING','PRESIDENT',NULL,'1981-11-17',5000,NULL,10);
INSERT INTO emp2 values(7844,'TURNER','SALESMAN',7698,'1981-09-08',1500,0,30);
INSERT INTO emp2 values(7876,'ADAMS','CLERK',7788,'1987-05-23',1100,NULL,20);

# 部門表
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');

普通查詢

條件查詢 (WHERE)

··· WHERE (條件表達式);

# 查詢 age大于等于25 
SELECT * FROM stu WHERE age>=25;
# 查詢 age大于等于25 指定顯示 sname
SELECT sname FROM stu WHERE age>=25;
# 查詢 指定gender值非male
SELECT * FROM stu WHERE gender!='male';
SELECT * FROM stu WHERE gender<>'male';
SELECT * FROM stu WHERE NOT gender='male';

指定查詢 (IN)

查指定字段

··· WHERE 字段名 [NOT] IN (值1，值2，···);

# 查詢 指定sid
SELECT * FROM stu WHERE sid IN ('S_1011' , 'S_1004');
# 查詢 指定sid 
SELECT * FROM stu WHERE sid NOT IN ('S_1011' , 'S_1004');

範圍查詢 (BETWEEN AND)

查指定字段的範圍值

··· WHERE 字段名 [NOT] BETWEEN (int)值1 AND (int)值2;

# 查詢 指定age 12 - 28的範圍
SELECT * FROM stu WHERE age BETWEEN 12 AND 28;
# 查詢 指定age 非12 - 28的範圍
SELECT * FROM stu WHERE age NOT BETWEEN 12 AND 28;

查詢字段名的值1和值2的範圍，前提2值必須大于1值

空值查詢 (NULL)

查字段NULL值

··· WHERE 字段名 IS [NOT] NULL;

# 查詢 age為空的
SELECT * FROM stu WHERE age IS NULL;
# 查詢 gender不為空的
SELECT * FROM stu WHERE gender IS NOT NULL;

過濾查詢 (DISTINCT )

過濾重複字段

SELECT DISTINCT * | {字段1,字段2...字段n} FROM 表名 [···];

# 查詢表中不重複的字段
SELECT DISTINCT * FROM stu;

模糊查詢 (LIKE)

尋找比對的字元串

··· WHERE 字段名 [NOT] LIKE '比對字元串%_';

# 查詢 sname 字首有字元 l 
SELECT * FROM stu WHERE sname LIKE 'l%';
# 查詢 sname 第5個字元為 S 
SELECT * FROM stu WHERE sname LIKE '_____S%';

注意：

% 任意0 - n個字元 _ 一個字元，通配符（未知數） \ 進行轉義 \% 為 %

多條件查詢 (AND)

（交集）連接配接兩個或者多個查詢條件

··· WHERE 條件表達式1 [AND 條件表達式2 [··· AND 條件表達式n]];

#查詢 age>16 && age<28
SELECT * FROM stu WHERE age>16 AND age<28;
#查詢 age>16 && age<28 且不能有 25
SELECT * FROM stu WHERE age>16 AND age<28 AND age != 25;

多條件查詢 (OR)

（并集）記錄滿足任意一個條件即被查出

··· WHERE 條件表達式1 [OR 條件表達式2 [··· OR 條件表達式n]];

# 查詢 age<16 || age>28
SELECT * FROM stu WHERE age<16 OR age>28;
# 查詢 age<16 || age>28 且不能有 5
SELECT * FROM stu WHERE  age != 5 AND age<16 OR age>28;

OR和AND一起用的情況：

AND的優先級高于OR，是以當兩者在一起使用時，應該先運算AND兩邊的條件表達式，再運算OR兩邊的條件表達式

進階查詢

聚合函數

記數函數 (COUNT)

統計不為null的記錄條數

SELECT COUNT((*) | (字段名)) 
FROM 表名 [···];

# 查詢 emp2表 總記錄數
SELECT COUNT(*) FROM emp2 ;
# 查詢 emp2表 comm記錄數
SELECT COUNT(comm) FROM emp2 ;

求和函數 (SUM）

求出表中某個字段所有值的總和

SELECT SUM(字段名1)[,SUM(字段名2)...,SUM(字段名n)] 
FROM 表名 [···];

# 查詢 emp2表 sal字段總和 
SELECT SUM(sal) FROM emp2;
# 查詢 emp2表 sal+comm字段 總和（IFNULL(comm , 0) 如果字段值為NULL 則至為0）
SELECT SUM(sal+ IFNULL(comm , 0) ) FROM emp2;

平均值函數 (AVG)

求出某個字段所有值的平均值

SELECT AVG(字段名1)[,AVG(字段名2)...,AVG(字段名n)] 
FROM 表名 [···];

# 查詢 emp2表 sal字段平均值
SELECT AVG(sal) FROM emp2;

最大值函數 (MAX)

用于求出某個字段的最大值，文法格式:

SELECT MAX(字段名1)[,MAX(字段名2)...,MAX(字段名n)] 
FROM 表名 [···];

# 查詢 emp2表 mgr字段最大值
SELECT MAX(mgr) FROM emp2;

最小值函數 (MIN)

用于求出某個字段的最小值，文法格式:

SELECT MIN(字段名1)[,MIN(字段名2)...,MIN(字段名n)] 
FROM 表名 [···];

# 查詢 emp2表 mgr字段最小值
SELECT MIN(mgr) FROM emp2;

順序查詢 (ORDER BY)

對查詢結果進行排序，文法格式:

SELECT * | {字段1,字段2...字段n} FROM 表名 
ORDER BY 字段名1 [ASC | DESC] [,字段名2 [ASC| DESC]...,字段名n [ASC | DESC]];

# 查詢 排序 emp2 表的 mgr（降序）
SELECT * FROM emp2 ORDER BY mgr DESC;
# 查詢 排序 emp2表 的 先排序mar ，相同值的情況排序sal （預設升序）
SELECT * FROM emp2 ORDER BY mgr , sal ;

ORDER BY：指定字段進行排序

SELECT：指定查詢的字段

ASC升序（預設）、DESC降序

注意：指定字段升序排列時，某條字段值為NULL,則這條記錄會在第一條顯示，因NULL值被認為是最小值

分組查詢 (GROUP BY)

對字段值進行分組查詢

SELECT 字段名1 | [···] FROM 表名
GROUP BY 字段名1,字段2,···[HAVING 條件表達式 | ···];

# 查詢 emp2表 以daptno字段分組 進行sal求和 
SELECT deptno , SUM(sal) FROM emp2 GROUP BY deptno;
# 查詢 emp2表 以daptno字段分組 每個dapthon分組的記錄數 并排序
SELECT deptno , COUNT(*) FROM emp2 GROUP BY deptno ORDER BY deptno;
# 查詢 emp2表 以daptno字段分組 deptno值為30 過濾
SELECT deptno , SUM(sal) FROM emp2 GROUP BY deptno HAVING deptno != 30;
# 查詢 emp2表 以daptno字段分組 mgr值小于7800 過濾
SELECT deptno , COUNT(*) FROM emp2 WHERE mgr>7800 GROUP BY deptno;

注意：

GROUP BY後面的字段是指定字段進行分組
聚合函數一起使用
查詢過濾使用 HAVING 并非 WHERE
HAVING不能單獨出現，隻能存在GROUP BY後面
非指定字段分組不能顯示字段

如：（指定字段分組是deptno字段，但不能顯示empno字段）

SELECT empno , SUM(sal) FROM emp2 GROUP BY deptno;
指定分組的字段可進行排序

WHERE和HAVING差別

WHERE語句：分組前進行過濾,不能使用聚合函數

HAVING語句：分組後進行過濾，可使用聚合函數

限制查詢 (LIMIT)

限制查詢結果的數量

SELECT 字段名1[,字段名2,...字段n] FROM 表名 
LIMIT [(int)偏移量，](int)顯示數;

# 查詢 emp2表 sal最小5個
SELECT * FROM emp2 ORDER BY sal LIMIT 0 , 5;
# 查詢 emp2表 sal最大5個
SELECT * FROM emp2 ORDER BY sal DESC LIMIT 0 , 5;

批量分頁查詢的情況

如果有大量資料比如：10頁，每頁有10條，共有100條！

第一頁0，第二頁10，第三頁20····第九頁90

pagelndex 頁碼數、pagesize 每頁顯示的條數

LIMIT (pageindex-1)*pagesize , pagesize;

别名

在查詢資料時，可以為表和字段取别名，這個别名可以代替其指定的表和字段。

表别名

可以為表取一個别名,用這個别名來代替表的名稱。别名格式:

AS:用于指定表名的别名,它可以省略不寫。

字段别名

為字段取一個别名,用這個别名來代替表的名稱。别名格式:

SELECT (* | 字段名1 [别名][,字段名2[别名]····,字段名n[别名]]) FROM 表名 [...];
# 或者：
SELECT (* | 字段名1 [AS 别名][,字段名2[AS 别名]····,字段名n[AS 别名]]) FROM 表名 [...];

多表關系

多對一

在多對一的表關系中，應将外鍵建在多的一方

多對多

為了實作資料表多對多的關系，需要定義第三方中間表來儲存兩個關系表的外鍵

一對一

一對一的對應關系中，需要厘清主從關系，通常在從表中建立外鍵

表建立及測試調用例子複制粘貼即可

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------
-- Table structure for category
-- ----------------------------
DROP TABLE IF EXISTS `category`;
CREATE TABLE `category`  (
  `cid` varchar(32) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
  `cname` varchar(100) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  PRIMARY KEY (`cid`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of category
-- ----------------------------
INSERT INTO `category` VALUES ('c001', '電器');
INSERT INTO `category` VALUES ('c002', '服飾');
INSERT INTO `category` VALUES ('c003', '化妝品');
INSERT INTO `category` VALUES ('c004', '書籍');

-- ----------------------------
-- Table structure for class
-- ----------------------------
DROP TABLE IF EXISTS `class`;
CREATE TABLE `class`  (
  `classid` int(0) NOT NULL AUTO_INCREMENT,
  `classname` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  PRIMARY KEY (`classid`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 5 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of class
-- ----------------------------
INSERT INTO `class` VALUES (1, '紅龍班');
INSERT INTO `class` VALUES (2, '衛冕班');
INSERT INTO `class` VALUES (3, '神州班');
INSERT INTO `class` VALUES (4, '航天班');

-- ----------------------------
-- Table structure for orderitem
-- ----------------------------
DROP TABLE IF EXISTS `orderitem`;
CREATE TABLE `orderitem`  (
  `oid` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `pid` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of orderitem
-- ----------------------------

-- ----------------------------
-- Table structure for orders
-- ----------------------------
DROP TABLE IF EXISTS `orders`;
CREATE TABLE `orders`  (
  `oid` varchar(32) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
  `totalprice` double NULL DEFAULT NULL,
  `uid` int(0) NULL DEFAULT NULL,
  PRIMARY KEY (`oid`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of orders
-- ----------------------------

-- ----------------------------
-- Table structure for products
-- ----------------------------
DROP TABLE IF EXISTS `products`;
CREATE TABLE `products`  (
  `pid` varchar(32) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
  `name` varchar(40) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `price` double NULL DEFAULT NULL,
  `category_id` varchar(32) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  PRIMARY KEY (`pid`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of products
-- ----------------------------
INSERT INTO `products` VALUES ('p001', '聯\r\n想', 5000, 'c001');
INSERT INTO `products` VALUES ('p002', '海\r\n爾', 3000, 'c001');
INSERT INTO `products` VALUES ('p003', '雷\r\n神', 5000, 'c001');
INSERT INTO `products` VALUES ('p004', 'JACK\r\nJONES', 800, 'c002');
INSERT INTO `products` VALUES ('p005', '真維\r\n斯', 200, 'c002');
INSERT INTO `products` VALUES ('p006', '花花公\r\n子', 440, 'c002');
INSERT INTO `products` VALUES ('p007', '勁\r\n霸', 2000, 'c002');
INSERT INTO `products` VALUES ('p008', '香奈\r\n兒', 800, 'c003');
INSERT INTO `products` VALUES ('p009', '相宜本\r\n草', 200, 'c003');
INSERT INTO `products` VALUES ('p010', '梅明\r\n子', 200, NULL);

-- ----------------------------
-- Table structure for scores
-- ----------------------------
DROP TABLE IF EXISTS `scores`;
CREATE TABLE `scores`  (
  `sid` int(0) NOT NULL AUTO_INCREMENT,
  `score` int(0) NULL DEFAULT NULL,
  `subjectid` int(0) NULL DEFAULT NULL,
  `studentid` int(0) NULL DEFAULT NULL,
  PRIMARY KEY (`sid`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 13 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of scores
-- ----------------------------
INSERT INTO `scores` VALUES (1, 43, 1, 1);
INSERT INTO `scores` VALUES (2, 100, 2, 1);
INSERT INTO `scores` VALUES (3, 54, 3, 1);
INSERT INTO `scores` VALUES (4, 34, 1, 2);
INSERT INTO `scores` VALUES (5, 52, 2, 2);
INSERT INTO `scores` VALUES (6, 32, 3, 2);
INSERT INTO `scores` VALUES (7, 41, 1, 3);
INSERT INTO `scores` VALUES (8, 86, 3, 3);
INSERT INTO `scores` VALUES (9, 98, 2, 3);
INSERT INTO `scores` VALUES (10, 78, 3, 4);
INSERT INTO `scores` VALUES (11, 76, 2, 4);
INSERT INTO `scores` VALUES (12, 54, 1, 4);

-- ----------------------------
-- Table structure for student
-- ----------------------------
DROP TABLE IF EXISTS `student`;
CREATE TABLE `student`  (
  `studentid` int(0) NOT NULL AUTO_INCREMENT,
  `studentname` varchar(10) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `password` varchar(10) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `sex` char(1) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `classid` int(0) NULL DEFAULT NULL,
  `test` int(0) NULL DEFAULT NULL,
  PRIMARY KEY (`studentid`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 10 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of student
-- ----------------------------
INSERT INTO `student` VALUES (1, '黑貓', '111111', '女', 1, 10);
INSERT INTO `student` VALUES (2, '大鲸', '2222', '男', 3, 20);
INSERT INTO `student` VALUES (3, '白兔', '3333', '女', 3, 30);
INSERT INTO `student` VALUES (4, '柏竹', '4444', '男', NULL, 40);
INSERT INTO `student` VALUES (5, '棕熊', '5555', '男', 5, 50);
INSERT INTO `student` VALUES (6, '智乃', '6666', '女', 3, 60);
INSERT INTO `student` VALUES (7, '蕾姆', '7777', '女', 2, 70);
INSERT INTO `student` VALUES (8, '艾米', '8888', '女', 1, 80);
INSERT INTO `student` VALUES (9, '紗霧', '9999', '女', 2, 90);

-- ----------------------------
-- Table structure for subject
-- ----------------------------
DROP TABLE IF EXISTS `subject`;
CREATE TABLE `subject`  (
  `subjectid` int(0) NOT NULL AUTO_INCREMENT,
  `subjectname` varchar(10) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  PRIMARY KEY (`subjectid`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 5 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of subject
-- ----------------------------
INSERT INTO `subject` VALUES (1, 'Java');
INSERT INTO `subject` VALUES (2, 'MySQL');
INSERT INTO `subject` VALUES (3, 'HTML');

-- ----------------------------
-- Table structure for users
-- ----------------------------
DROP TABLE IF EXISTS `users`;
CREATE TABLE `users`  (
  `userid` int(0) NULL DEFAULT NULL,
  `username` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `upass` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of users
-- ----------------------------

SET FOREIGN_KEY_CHECKS = 1;

合并結果集 (UNION)

連接配接查詢的過程中，通過添加過濾條件來限制查詢結果，使查詢結果更加精确（ALL去除重複的記錄）

SELECT {* | 查詢字段} FROM 表1 UNION [ALL]
SELECT {* | 查詢字段} FROM 表2;

# 合并兩表資料
SELECT studentid,studentname FROM student UNION 
SELECT subjectid,subjectname FROM subject;

注意：合并前提必須将兩表的列數和類型一緻

内連接配接 (INNER JOIN)

内連接配接使用比較運算符，對兩表中指定字段值進行比較，列出與連接配接條件比對的資料行，字段值符合比對的進行連接配接，組合成新的記錄（無視不比對的字段）

SELECT  {* | 查詢字段}  FROM  表1  INNER  JOIN  表2  ON  {表1.關系字段 = 表2.關系字段 | WHERE 條件表達式} ;
# 或者(簡寫)
SELECT {* | 查詢字段}  FROM  表1,表2  WHERE  表1.關系字段 = 表2.關系字段;

# 查詢 與班關聯的學生（已經配置設定班級的學生）
SELECT * FROM student JOIN class ON student.classid = class.classid;

# 查詢花子所有成績
SELECT subjectname,score 
FROM
	scores , subject , student 
WHERE
	scores.subjectid = subject.subjectid 
	AND scores.studentid = student.studentid 
	AND student.studentname = '花兒';

# 查詢 所有科目的平均值
SELECT subjectname , AVG( score ) 
FROM
	scores JOIN subject 
	ON scores.subjectid = subject.subjectid 
GROUP BY
	subjectname;

外連接配接 (OUTER JOIN)

外連接配接彌補了内連接配接查詢不比對的條件，查出不滿足條件的可能

外連接配接包括：左連接配接、右連接配接

左連接配接

指定左表的所有記錄，所有滿足連接配接條件的記錄。如果左表的某條記錄在右表中不存在，則在右表中顯示為空值

右連接配接

右連接配接與左連接配接正好相反

SELECT {* | 查詢字段} FROM 表1 { LEFT | RIGHT } JOIN 表2 ON 表1.字段 = 表2.字段;
# 左外連接配接
SELECT  字段  FROM  主表  LEFT  JOIN 次表 ON 主表.字段 = 次表.字段;
# 右外連接配接
SELECT  字段  FROM  主表  RIGHT  JOIN  次表  ON  主表.字段 = 次表.字段;

# 左外查詢
SELECT * FROM
	student LEFT JOIN class 
	ON student.classid = class.classid;
# 右外查詢
SELECT * FROM
	student RIGHT JOIN class 
	ON student.classid = class.classid;

注意：主表會顯示所有字段，空值或不比對條件的字段均可查詢！！！

連接配接查詢(CROSS JOIN)

交叉連接配接又稱笛卡爾積，傳回結果的是被連接配接的兩個表中所有字段行乘積

SELECT {* | 查詢字段} FROM 表1 CROSS JOIN 表2 [WHERE 條件表達式];
# 或者(簡寫)
SELECT {* | 查詢字段} FROM 表1,表2 [WHERE 條件表達式];

# 将學生與班級交叉查詢
SELECT * FROM student CROSS JOIN class;

自然連接配接(NATURAL JOIN)

特殊的等值連接配接，前提兩表字段屬性必須相同，且字段名也要相同，無須添加連接配接條件，得出的結果是消除重複的字段

SELECT {* | 查詢字段} FROM 表1 NATURAL JOIN 表2;

# 查詢學生所在的班級
SELECT * FROM student NATURAL JOIN class;

子查詢

子查詢是指一個查詢語句嵌套在另一個查詢語句内部的查詢

在執行查詢語句時，首先會執行子查詢中的語句，然後将傳回的結果作為外層查詢的過濾條件，子查詢必須傳回是一個字段資料

IN

内層查詢語句僅僅傳回一個資料字段，資料字段中的值将供外層查詢語句進行比較操作

SELECT {* | 查詢字段} FROM 表名1 WHERE 字段 [NOT] IN(SELECT 字段 FROM 表名2 WHERE 條件表達式);

# 查詢 三班有多少女生
SELECT studentname FROM student 
WHERE
	classid IN(SELECT classid FROM class WHERE classid = 3)
AND sex = '女';
# 查詢 與‘智乃’同一個班的學生
SELECT studentname FROM	student 
WHERE 
	classid IN( SELECT classid FROM student WHERE studentname = '智乃' );

注意： IN 後面子查詢傳回結果要和IN前的字段比對

EXISTS

參數可以是任意一個子查詢，這個子查詢的作用相當于測試，傳回布爾值 , 如果 TRUE 外層查詢才會執行

SELECT  {* | 查詢字段}  FROM  表名1  WHERE  [NOT]  EXISTS  (SELECT  字段  FROM  表名2  WHERE  條件表達式);

# 測試 兩表關系（比對顯示，不比對不顯示）
SELECT
	studentid,
	studentname 
FROM student WHERE
	EXISTS ( SELECT * FROM class WHERE class.classid = student.classid );

ANY

滿足任意一個條件，子查詢傳回的字段清單進行比較，将不比對的過濾

SELECT * FROM 表名 WHERE [NOT] 字段 {比較運算符} ANY(SELECT 字段 FROM 表名);

# 查詢 已經配置設定班級的學生
SELECT classid,studentname FROM student 
WHERE
	classid = ANY(SELECT classid FROM class);
#或 （結果一樣）
SELECT classid,studentname FROM student 
WHERE
	classid IN( SELECT classid FROM class);

ALL

子查詢傳回的字段清單結果需同時滿足所有内層查詢條件，否則查詢為空

比較運算符} ALL(SELECT 字段 FROM 表名)

# 查詢 test 高于蕾姆的同學
SELECT studentname,test FROM student 
WHERE 
	test > ALL(SELECT test FROM student WHERE studentname = '蕾姆');	
# 查詢 比對錯班級的同學（資料錯亂）
SELECT classid,studentname FROM student 
WHERE
	classid > ALL(SELECT classid FROM class);
#或 （結果一樣）
SELECT classid,studentname FROM student 
WHERE
	classid NOT IN( SELECT classid FROM class);

比較運算符

ANY 和 ALL 都用到比較運算符，但還可以使用其他運算符

查詢優化

避免全表掃描，應先考慮 where 及 order by 涉及的列上建立索引
避免 where語句中的字段進行 null值 判斷
避免 where語句中使用 != 或 < / > 操作符
避免 where語句使用 OR 連接配接條件
謹慎使用 IN / NOT IN 進行查詢，連續數字可使用 BETWEEN 範圍查詢

完整性

保證了資料的有效性和準确性，以防止資料表中插入錯誤的資料

限制條件	說明
PRIMARY KEY	主鍵限制，用于唯一辨別對應的記錄
FOREIGN KEY	外鍵限制
NOT NULL	非空限制
UNIQUE [KEY]	唯一性限制
DEFAULT	預設值限制，用于設定字段的預設值
AUTO_INCREMENT	自動增長
FOREIGN KEY	外鍵限制
CHECK	限制取值範圍
UNSIGNED	無符号限制

注意：多個限制需要空格分隔

實體完整性

單字段主鍵

每表隻有一個主鍵，唯一性，不能NULL，可建立聯合主鍵

字段名資料類型 PRIMARY KEY[(字段名1,字段名2,···字段名n)];

ALTER TABLE student ADD PRIMARY KEY[(字段名1,字段名2,···字段名n)]

唯一限制

資料不能重複，隻能有一次為空

字段名資料類型 UNIQUE;

字段自動增加

在資料表中，若想為表中插入的新記錄自動生成唯一的 ID,可以使用自增限制來實作

字段名資料類型 AUTO_INCREMENT;

域完整性

資料類型

非空限制

字段的值不能為NULL（空）

字段名資料類型 NOT NULL;

預設限制

新添資料時，如果未指派，則自動插入預設值

字段名資料類型 DEFAULT(預設值);

無符号限制

字段名資料類型 UNSIGNED;

限制範圍

字段名資料類型 CHECK (字段值>0 and 字段值<=100);

引用完整性

外鍵限制

外鍵是指引用另一個表中的一個字段或多個字段。建立、加強兩表資料之間的連結

MySQL學習記錄操作篇資料類型DDL操作資料庫DDL表列操作索引DML資料操作DCL安全通路DQL資料查詢完整性函數

建立表時定義外鍵：

# 從表
CREATE TABLE 表名(
   字段名1 資料類型 [完整性限制條件],
   字段名2 資料類型 [完整性限制條件],
   ·······
   FOREIGN KEY (外鍵字段) REFERENCES 主表( 主表的 主鍵 / 唯一 字段 )
);

建立表後定義外鍵：

ALTER TABLE 從表 ADD CONSTRAINT 自定義外鍵名 FOREIGN KEY(從表外鍵字段) REFERENCES 主表( 主表的主鍵 / 唯一字段 );

外鍵删除

根據邏輯的需求，需要解除兩個表之間的關聯關系時，就需要删除外鍵限制

ALTER TABLE 表名 DROP FOREIGN KEY 外鍵名;

注意：

主表是被外鍵引用的字段，且該字段有主鍵限制或唯一性限制
被引用的是主表，引用的是從表，兩表是主從關系
引入外鍵後，從表外鍵字段隻能插入主表被引用的字段值
如果想删除主表一條記錄，則必須確定從表外鍵字段是否引用到主表字段值，確定資料的完整性
建立外鍵的表必須是InnoDB型不能是臨時表。因為MySQL中隻有InnoDB型的表才支援外鍵
定義外鍵名時，不能加引号。如: constraint ‘FK_ ID’ 或constraint" FK_ID "都是錯誤的

函數

數學函數

函數名稱	作用
ABS（x）	傳回x的絕對值
SQRT（x）	傳回x的非負2次方根
MOD（x , y）	傳回x被y除後的餘數
CEILING（x）	傳回不小于x的最小整數
FLOOR（x）	傳回不大于x的最大整數
ROUND（x , y）	對x進行四舍五入操作，小數點後保留y位
TRUNCATE（x）	舍去x中小數點y位後面的的數
SIGN（x）	傳回x的符号，-1、0或者1

字元串函數

函數名稱	作用
LENGTH（str）	傳回字元串str的長度
CONCAT（s1 , s2 , …）	傳回一個或者多個字元串連接配接産生的新的字元串
TRIM（str）	删除字元串兩側的空格
REPLACE（str , s1 , s2）	使用字元串s2替換字元串str中所有的字元串s1
SUBSTRING（tr , n , len）	傳回字元串st的子串，起始位置為n,長度為len
REVERSE（str）	傳回字元串反轉後的結果
LOCATE（s1 , str）	傳回子串s1在字元串str中的起始位置

日期與時間的函數

函數名稱	作用
CURDATE()	擷取系統目前日期
CURTIME()	擷取系統目前時間
SYSDATE()	擷取目前系統日期和時間
DATE_ADD(now() , INTERVAL num {時間機關})	指定過去或未來時間點（num是對現在的時間進行相加）
DATEDIFF(d1 , d2)	計算兩時間間隔的天數（從0000.1.1開始 n 天後的日期）
FROM_DAYS(day)	計算時間得出日期（從0000.1.1開始 n 天後的日期）
YEAR(d)	擷取日期年份
MONTH(d)	擷取日期月份
DAY(d)	擷取日期日

條件判斷函數

函數名稱	作用
IF（expr , v1 , v2）	如果expr表達式為true傳回v1,否則傳回v2
IFNULL（v1 , v2）	如果v1不為NULL傳回v1,否則傳回v2
CASE expr WHEN v1 THEN r1 [WHEN v2 THEN r2…] [ELSE m] END	如果expr值等于v1、v2等,則傳回對應位置THEN後面的結果，否則傳回ELSE後的結果m

加密函數

函數名稱	作用
MD5（str）	對字元串 str 進行MD5加密
ENCODE（str , pwd stu）	使用pwd作為密碼加密字元串str
DECODE（str , pwd str）	使用pwd作為密碼解密字元串str

MySQL學習記錄 操作篇資料類型DDL操作資料庫DDL表列操作索引DML資料操作DCL安全通路DQL資料查詢完整性函數

資料類型

整型資料

浮點型

日期 時間類型

字元串型

二進制資料類型

大文本資料類型

大資料二進制類型

枚舉型

多選項

DDL操作資料庫

建立資料庫

檢視資料庫

修改資料庫

删除資料庫

選擇資料庫

DDL表列操作

建立表

檢視表

修改表

修改表名

增加字段

字段 修改 與 順序

修改字段名

修改表字元集

删除字段

删除表

索引

索引建立

單列索引

唯一索引 (UNIQUE )

全文索引 (FULLTEXT)

空間索引 (SPATIAL)

索引删除

DML資料操作

插入資料

更改資料

删除資料

DCL安全通路

建立使用者

授權使用者

查詢權限

撤銷權限

删除使用者

DQL資料查詢

普通查詢

條件查詢 (WHERE)

指定查詢 (IN)

範圍查詢 (BETWEEN AND)

空值查詢 (NULL)

過濾查詢 (DISTINCT )

模糊查詢 (LIKE)

多條件查詢 (AND)

多條件查詢 (OR)

進階查詢

聚合函數

記數函數 (COUNT)

求和函數 (SUM）

平均值函數 (AVG)

最大值函數 (MAX)

最小值函數 (MIN)

順序查詢 (ORDER BY)

分組查詢 (GROUP BY)

限制查詢 (LIMIT)

别名

表别名

字段别名

多表關系

合并結果集 (UNION)

内連接配接 (INNER JOIN)

外連接配接 (OUTER JOIN)

連接配接查詢(CROSS JOIN)

自然連接配接(NATURAL JOIN)

子查詢

IN

EXISTS

ANY

ALL

比較運算符

MySQL學習記錄操作篇資料類型DDL操作資料庫DDL表列操作索引DML資料操作DCL安全通路DQL資料查詢完整性函數

日期時間類型

字段修改與順序