第1章 資料庫概述
1.1基本概念
1.1.1資料庫技術發展階段
人工管理階段——>檔案系統階段——>資料庫系統階段
1.1.2涉及的概念
資料庫(DB)、資料庫管理系統(DBMS)、資料庫系統(DBS)
1.1.3經曆的階段
層次資料庫和網狀資料庫(IDS)——>關系資料庫(Oracle、DB2、SQL Server、MySQL、Sybase等)——>後關系資料庫(面向對象資料庫技術(ORDBMS)、結構化資料庫技術(NOSQL))
1.1.4提供的功能：
1、資料定義語言(DDL)——>定義各種對象，完整性限制和保密限制等限制等
2、資料操作語言(DML)——>檢索(查詢)和更新(插入、删除和更新)
3、資料控制語言(DCL)——>資料完整性控制、資料安全性控制和資料庫的恢複等
1.2 MySQL資料庫管理系統
1.2.1MySQL概述
免費、開源、小型、關系型
1.2.2常用資料庫管理系統
Oracle、DB2、SQL Server、PostgreSQL等
第2章 MySQL安裝和配置
1、4個比較重要的檔案夾：bin、include、lib、share
2、my.ini配置檔案：如果修改了配置檔案，則必須重新啟動MySQL服務，修改的内容才能生效
3、啟動和關閉MySQL服務
net start // 檢視已啟動的服務項
net stop MySQL //停止MySQL服務
net start MySQL //啟動MySQL服務
第3章 MySQL資料庫基本操作
3.1資料庫和資料庫對象
1、系統資料庫：information_schema、performance_schema、mysql、test
2、使用者資料庫
3.2建立資料庫
CREATE DATABASE database_name
3.3檢視和選擇資料庫
//檢視資料庫
SHOW DATABASES;
//選擇資料庫
USE database_name;
3.4删除資料庫
DROP DATABASE database_name;
第4章 MySQL資料庫中存儲引擎和資料類型
4.1認識存儲引擎
// 檢視所支援的存儲引擎
SHOW ENGINES;
SHOW ENGINES \g
SHOW ENGINES \G
SHOW VARIABLES LIKE 'have%';
// 查詢預設存儲引擎
SHOW VARIABLES LIKE 'storage_engine%';
4.2資料類型
4.2.1整數類型
TINYINT—> (1)、SMALLINT—> (2)、MEDIUMINT—> (3)、INT和INTEGER—> (4)、BIGINT—> (8)
4.2.2浮點數類型、定點數類型和位類型
FLOAT—> (4)、DOUBLE—> (8)
DEC(M,D)和DECIMAL(M,D) —> (M+2)
BIT(M) —>(1-8)
4.2.3日期和時間類型
DATE—> (4)、DATETIME—> (8)、TIMESTAMP—> (4)、TIME—> (3)、YEAR—> (1)
4.2.4字元串類型
CHAR(M) —> (M)、VARCHAR(M) —> (M)
TINYTEXT—> (0-255)、TEXT—> (0-65535)、MEDIUMTEXT、LONGTEXT
BINARY(M) —> (M)、VAR BINARY(M) —> (M)
TINYBLOB—> (0-255)、BLOB—> (0-2^16)、MEDIUMBLOB(0-2^24)、LONGBLOB(0-2^32)
第5章 表的操作
5.1表的基本概念
列(Column)、索引(Index)、觸發器(Trigger)
5.2建立表
CREATE TABLE table_name(
屬性名 資料類型,
屬性名 資料類型,
..…..
屬性名 資料類型
) ;
5.3檢視表結構
//檢視表定義
DESCRIBE table_name;
DESC table_name;
//檢視表詳細定義
SHOW CREATE TABLE table_name \G
5.4删除表
DROP TABLE table_name;
5.5修改表
5.5.1修改表名
ALTER TABLE old_table_name
RENAME [TO] new_table_name;
5.5.2增加字段
//在表的最後一個位置增加字段
ALTER TABLE table_name
ADD 屬性名 資料類型;
//在表的第一個位置增加字段
ALTER TABLE table_name
ADD 屬性名 資料類型 FIRST;
//在表的指定字段之後增加字段
ALTER TABLE table_name
ADD 屬性名 資料類型
AFTER 屬性名;
5.5.3删除字段
ALTER TABLE table_name
DROP 屬性名;
5.5.4修改字段
//修改字段的資料類型
ALTER TABLE table_name
MODIFY 屬性名 資料類型;
//修改字段的名字
ALTER TABLE table_name
CHANGE 舊屬性名 新屬性名 舊資料類型;
//同時修改字段的名字和屬性
ALTER TABLE table_name
CHANGE 舊屬性名 新屬性名 新資料類型;
//修改字段的順序
ALTER TABLE table_name
MODIFY 屬性名1 資料類型 FIRST|AFTER 屬性名2;
5.7操作表的限制
5.7.1MySQL支援的完整性限制
NOT NULL(NK)、DEFAULT、UNIQUE KEY(UK)、PRIMARY KEY(PK)、AUTO_INCREAMENT、FOREIGE KEY(FK)
//多字段限制
CONSTRAINT 限制名 UNIQUE|PRIMARY KEY|…(屬性名，屬性名……)
//外鍵限制
CONSTRAINT 限制名 FOREIGE KEY (屬性名1)
REFERENCES 表名 (屬性名2)
第6章 索引的操作
6.1 為什麼使用索引
根據索引的存儲類型：B型樹索引(BTREE)和哈希索引(HASH)
MySQL支援6種索引：普通索引、唯一索引、全文索引、單列索引、多列索引和空間索引。
6.2建立和檢視索引
6.2.1建立和檢視普通索引
//建立表時建立普通索引
CREATE TABLE table_name(
屬性名 資料類型,
屬性名 資料類型,
……
屬性名 資料類型 ,
INDEX|KEY 【索引名】(屬性名1 【(長度)】【ASC|DESC】)
) ;
//在已經存在的表上建立普通索引
CREATE INDEX 索引名
ON 表名(屬性名【(長度)】【ASC|DESC】)
//通過ALTER TABLE建立普通索引
ALTER TABLE table_name
ADD INDEX|KEY 索引名(屬性名【(長度)】【ASC|DESC】)
6.2.2建立和檢視唯一索引
UNIQUE INDEX|KEY 索引名(屬性名【(長度)】【ASC|DESC】)
CREATE UNIQUE INDEX 索引名
ON 表名(屬性名【(長度)】【ASC|DESC】)
ALTER TABLE table_name
ADD UNIQUE INDEX|KEY 索引名(屬性名【(長度)】【ASC|DESC】)
6.2.3建立和檢視全文索引
隻能在存儲引擎為MyISAM的資料庫表上建立全文索引
CREATE TABLE table_name(
屬性名 資料類型,
屬性名 資料類型,
……
屬性名 資料類型 ,
FUNNTEXT INDEX|KEY 索引名(屬性名【(長度)】【ASC|DESC】)
) ENGINE=MyISAM;
CREATE FUNNTEXT INDEX 索引名
ON 表名(屬性名【(長度)】【ASC|DESC】)
ALTER TABLE table_name
ADD FUNNTEXT INDEX|KEY 索引名(屬性名【(長度)】【ASC|DESC】)
6.2.4建立和檢視多列索引
6.3 删除索引
DROP INDEX index_name
ON table_name;
第7章 視圖的操作
7.1為什麼使用視圖
1、對視圖内容的更新(添加、删除和修改)直接影響基本表。
2、當視圖來自多個基本表時，不允許添加和删除資料。
7.2建立視圖
CREATE VIEW view_name
AS 查詢語句;
7.3檢視視圖
//檢視視圖名
USE view;
SHOW TABLES;
//檢視視圖詳細資訊
SHOW TABLE STATUS 【FROM db_name】【LIKE 'pattern'】
例：SHOW TABLE STATUS FROM view \G
//檢視視圖定義資訊
SHOW CREATE VIEW view_name \G
//檢視視圖設計資訊
DESCRIBE|DESC view_name;
7.4 删除視圖
DROP VIEW view_name 【,view_name】…
7.5修改視圖
CREATE OR REPLACE VIEW view_name
AS 查詢語句;
ALTER VIEW view_name
AS 查詢語句;
7.6利用視圖操作基本表
第8章 觸發器的操作
8.1為什麼使用觸發器
觸發器(TRIGGER)
8.2建立觸發器
8.2.1建立有一條執行語句的觸發器
CREATE TRIGGER trigger_name
BEFORE|AFTER trigger_EVENT
ON table_name FOR EACH ROW trigger_STMT
8.2.2建立包含多條執行語句的觸發器
DELIMITER $$
CREATE TRIGGER trigger_name
BEFORE|AFTER trigger_EVENT
ON table_name FOR EACH ROW
BEGIN
trigger_STMT
END
$$
DELIMITER ;
8.3檢視觸發器
SHOW TRIGGERS \G
//通過檢視系統表檢視觸發器
USE information_schema;
SELECT * FROM triggers \G
8.4删除觸發器
DROP TRIGGER trigger_name;
第9章 資料的操作
9.1插入資料記錄
//插入完整資料記錄
INSERT INTO table_name(field1,field2,…)
VALUES(value1,value2,…)
//插入資料記錄一部分
//插入多條資料記錄
//插入查詢結果
9.2更新資料記錄
//更新特定資料記錄
UPDATE table_name
SET field1=value1,
Field2=value2,
……
WHERE CONDITION;
9.3删除資料記錄
DELECT FROM table_name
WHERE CONDITION;
第10章 單表查詢資料記錄
10.1簡單資料記錄查詢
SELECT field1, field2, ……, fieldn
FROM table_name
//避免重複查詢記錄
SELECTDISYINCTfield1, field2, ……, fieldn
FROM table_name
10.2條件資料記錄查詢
SELECT field1, field2, ……, fieldn
FROM table_name
WHERE CONDITION
關鍵字：(NOT) BETWEEN AND、(NOT) IS NULL、(NOT) IN、(NOT) LIKE'_'、'%
10.3排序資料記錄查詢
SELECT field1, field2, ……, fieldn
FROM table_name
WHERE CONDITION
ORDER BY fieldm1 [SAC|DESC] [,fieldm2 [SAC|DESC], ]
10.4限制資料記錄查詢數量
SELECT field1, field2, ……, fieldn
FROM table_name
WHERE CONDITION
LIMIT OFFSET_START, ROW_COUNT
OFFSET_START: 資料記錄的起始偏移量(可不指定，預設為0)
ROW_COUNT: 顯示的行數
10.5統計函數和分組資料記錄查詢
COUNT()、AVG()、SUM()、MAX()、MIN()
如果所操作的表中沒有任何資料記錄，則COUNT()函數會傳回資料0，而其它函數則會傳回NULL
//簡單分組查詢(顯示每組中的一條資料記錄)
SELECT function()
FROM table_name
WHERE CONDITION
GROUP BY field;
//實作統計功能分組查詢：GROUP_CONTACT() 顯示每個分組中的指定字段值
SELECT GROUP_CONTACT(field)
FROM table_name
WHERE CONDITION
GROUP BY field;
//實作多個字段分組查詢
SELECT GROUP_CONTACT(field), function(field)
FROM table_name
WHERE CONDITION
GROUP BY field1, field2……, fieldn ;
//實作HAVING子句限定分組查詢
SELECT function(field)
FROM table_name
WHERE CONDITION
GROUP BY field1, field2……, fieldn ;
HAVING CONDITION;
第11章 多表資料記錄查詢
11.1關系資料操作
并、笛卡爾積、内連接配接(自然連接配接、等值連接配接、不等連接配接)、外連接配接(左外連接配接、右外連接配接、全外連接配接)
11.2内連接配接查詢
SELECT field1, field2, ……, fieldn
FROM join_tablename1 INNER JOIN join_tablename2【INNER JOIN join_tablename】
ON join_condition
11.3外連接配接查詢
SELECT field1, field2, ……, fieldn
FROM join_tablename1 LEFT|RIGHT|FULL [OUTER] JOIN join_tablename2
ON join_condition
11.4合并查詢資料記錄
UNION、UNION ALL
11.5子查詢
//帶有關鍵字ANY的子查詢：
=ANY
>ANY (>=ANY)
比子查詢中傳回資料記錄中最小的還要大于(大于等于)資料記錄
//帶有關鍵字ANY的子查詢：
=ALL
>ALL (>=ALL) 比子查詢中傳回資料記錄中最大的還要大于(大于等于)資料記錄
第12章 使用MySQL運算符
12.1為什麼要使用運算符
12.2使用算術運算符
+、-、*、/(DIV)、%(MOD)
12.3使用比較運算符
>、)、!=(<>)、>=、<=、BETWEEN AND、IS NULL、IN、LIKE、REGEXP (正規表達式比對)
12.4使用邏輯運算符
AND(&&)、OR(||)、NOT(！)、XOR
12.5使用位運算符
&、|、-、^、<>
第13章 使用MySQL常用函數
13.1使用字元串函數
13.1.1合并字元串函數：CONCAT(S1,S2,…,SN)和CONCAT_WS(sep, S1,S2,…,SN)
13.1.2比較字元串大小函數：STRCMP(str1,str2)
13.1.3擷取字元串長度函數：LENGTH()和字元數函數：CHAR_LENGTH()
13.1.4字母大小寫轉換：UPPER()和LOWER()
UCASE()和LCASE()
13.1.5查找字元串：
FIND_IN_SET(str,str1)和FIELD(str,str1, str2,str3)
LOCATE(str,str1)、POSITION(str IN str1)、INSTR(str,str1)
ELT(n,str1,str2)
MAKE_SET(num,str1,str2…,strn)
LEFT(str,num)和RIGHT(str,num)、SUBSTRING(str,num,len)和MID(str,num,len)
LTRIM(str)、RIGHT(str)、TRIM(str)
INSERT(str,pos,len,newstr)、REPLACE(str,substr,newstr)
13.2使用數值函數
ABS(x)、CEIL(x) 、FLOOR(x)、MOD(x)、RAND()、ROUND(x,y)、TRUNCATE(x,y)、
13.3使用日期和時間函數
CURDATE()、CURTIME()、NOW()、UNIX_TIMESTAMP(date)、FROM_UNIXTIME()、
WEEK(date)、YEAR(date)、HOUR(time)、MINUTE(time)、MONTHNAME(date)
13.4使用系統資訊函數
VERSION()、DATABASE()、USER()、LAST_INSERT_ID()
第14章 存儲過程和函數的操作
14.1為什麼使用存儲過程和函數
簡單、高性能
14.2建立存儲過程和函數
//建立存儲過程文法形式
CREATE PROCEDURE procedure_name([procedure_parameter[,…]])
[charaxteristic…] routine_body
//建立函數文法形式
CREATE FUNCTION function_name([function_parameter[,…]])
[charaxteristic…] routine_body
14.3關于存儲過程和函數的表達式
14.3.1操作變量
//聲明變量
DECLARE var_name [,…] type [DEFAULT value]
//指派變量
SET var_name=expr[,…]
//通過"SELECT……INTO"實作
SELECT field_name[,…] INTO var_name[,…]
FROM table_name
WHERE condition
例：
DECLARE employee_sal INT DEFAULT 1000;
SET employee_sal=3500;
或者：
SELECT sal INTO employee_sal
FROM t_employee
WHERE empno=7565;
14.3.2操作條件
定義條件、定義處理程式
14.3.3使用光标
//聲明光标
DECLARE cursor_name CURSOR FOR select_statement;
//打開光标
OPEN cursor_name;
//使用光标
FETCH cursor_name INTO var_name [,var_name]…
//關閉光标
CLOSE cursor_name;
14.3.4使用流程控制
條件控制語句：IF、CASE
循環控制語句：LOOP、WHILE、REPEAT(退出循環體：LEAVE)
14.4檢視存儲過程和函數
SHOW PROCEDURE STATUS [LIKE 'pattern' ] \G
SHOW FUNCTION STATUS [LIKE 'pattern' ] \G
//通過檢視系統表information_schema_routines實作檢視存儲過程和函數的資訊
USE information_schema;
SELECT * FROM routines \G
//SHOW CREATE語句
SHOW CREATE PROCEDURE proce_name \G
SHOW CREATE FUNCTION func_name \G
14.5修改存儲過程和函數
ALTER PROCEDURE procedure_name
[characteristic…]
ALTER FUNCTION function_name
[characteristic…]
14.6删除存儲過程和函數
DROP PROCEDURE proce_name;
DROP FUNCTION func_name;
第15章 MySQL安全性機制
15.1MySQL軟體所提供的權限
15.1.1系統表mysql.user
該表有39個字段，可分為4類：使用者字段、權限字段、安全字段和資源控制手段
15.1.2系統表mysql.db和mysql.host
可分為2類：使用者字段和權限字段
15.1.3其它權限表
mysql.tables_priv、mysql.columns_priv、procs_priv
15.2MySQL所提供的使用者機制
15.2.1登陸和退出
mysql -h hostname|hostIP -p port -u usename -p DatabaseName -e "SQL語句";
EXIT|QUIT
15.2.2建立普通使用者賬戶
1、執行"CREATE USER"語句來建立使用者賬戶
CREATE USER username[IDENTIFIED BY [PASSWORD] 'password']
[ , username[IDENTIFIED BY [PASSWORD] 'password']]
……
[ , username[IDENTIFIED BY [PASSWORD] 'password']]
2、執行INSERT語句建立使用者
INSERT INTO user(Host, User, Password) Values('hostname', 'username', PSAAWORD('password'));
FLUSH PRIVILEGES;//使建立的使用者賬号生效
3、執行GRANT語句建立使用者
GRANTpriv_typeONdatabasename.tablename
TOusename[IDENTIFIED BY [PASSWORD] 'password']
[ , username[IDENTIFIED BY [PASSWORD] 'password']]
……
[ , username[IDENTIFIED BY [PASSWORD] 'password']]
15.2.3利用擁有超級權限使用者root修改使用者賬号密碼
1、通過mysqladmin指令修改root使用者密碼
mysqladmin -u usename -p password "new_password"
2、通過SET指令修改root使用者密碼
SET PASSWORD=PASSWORD("new_password")
3、更新系統表mysql.user資料記錄修改root使用者密碼
UPDATE user SET password=PASSWORD("new_passwprd")
WHERE user="root" AND host="localhost";
15.2.4利用root使用者修改普通使用者賬号密碼
GRANT SELECT,CREATE.DROP ON *.*
TO 'qian'@'localhost' IDENTIFIED BY 'new_passwprd'
WITH GRANT OPTION;
15.2.5删除普通使用者賬号
DROP USER user1 [,user2]…
//通過系統表mysql.user删除
DELETE FROM user
WHERE user="cjgong" AND host="localhost";
15.3權限管理
GRANTpriv_type ON database.table
TO user [IDENTIFIED BY [PASSWORD] 'passwprd']
[, user [IDENTIFIED BY [PASSWORD] 'passwprd']]
……
[WITH GRANT OPTION…..]
//檢視使用者所擁有權限
SHOW GRANTS FOR user
//收回使用者所擁有權限
REVOKEpriv_type ON database.table
TO user [IDENTIFIED BY [PASSWORD] 'passwprd']
[, user [IDENTIFIED BY [PASSWORD] 'passwprd']]
……
第16章 MySQL日志管理
16.1MySQL所支援的日志
二進制日志、錯誤日志、查詢日志
第17章 MySQL資料庫維護和性能提高
17.1MySQL資料庫維護
17.1.1通過複制資料檔案實作資料備份
隻适合存儲引擎為MyISAM的表
17.1.2通過指令mysqldump實作資料備份
//備份一個資料庫
mysqldump -u username -p dbname
table1 table2…tablen
>backupname.sql
//備份多個資料庫
mysqldump -u username -p --databases
dbname1 dbname2…dbnamen
>backupname.sql
//備份所有資料庫
mysqldump -u username -p --all -databases
>backupname.sql
17.1.3通過複制資料檔案實作資料還原
17.1.4通過指令MySQL實作資料還原
mysqldump -u username -p [dbname] < backupname.sql
17.1.5實作資料庫中表導出成文本檔案
//通過SELECT…INTO OUTFILE
SELECT [file_name] FROM table_name
[WHERE contion]
INTO OUTFILE 'FILE_NAME'
[OPTION]
//通過mysqldump指令
mysqldump -u root -pPassword -T file_dorectory dbname table_name [option]
//通過mysql指令
mysql -u root -pPassword -e "SELECT [file_name] FROM table_name" dbname > file_name
17.1.6實作文本檔案導入到資料庫表
//執行"LOAD DATA INFILE"指令
LOAD DATA [LOCAL] INFILE 'file_name' INTO TABLE table_name [OPTION];
//執行mysqlimport指令
mysqlimport -u root -pPassword [--LOCAL] dbname file_name [OPTION];