零基礎(chǔ)輕松學(xué)MySQL 5.7

前 言
第1章 數(shù)據(jù)庫概述 ·····································1
1.1 數(shù)據(jù)庫基本概念 ·······························1
1.1.1 數(shù)據(jù)存儲(chǔ)方式 ·······························1
1.1.2 什么是數(shù)據(jù)庫 ············································2
1.1.3 數(shù)據(jù)庫的存儲(chǔ)結(jié)構(gòu) ····································2
1.1.4 數(shù)據(jù)庫在開發(fā)中的作用 ···········································4
1.2 數(shù)據(jù)庫技術(shù) ··············································4
1.2.1 數(shù)據(jù)庫系統(tǒng) ·······························4
1.2.2 了解SQL語言 ·······································6
1.2.3 數(shù)據(jù)庫訪問技術(shù) ···············································6
1.3 認(rèn)識(shí)MySQL ···············································7
1.3.1 MySQL的優(yōu)勢(shì) ······································7
1.3.2 MySQL的版本 ······································8
1.3.3 如何獲取MySQL ···········································8
1.4 數(shù)據(jù)庫的學(xué)習(xí)方法 ···········································10
1.5 小結(jié) ·········································11
1.6 練習(xí)題 ·····································11
第2章 安裝和配置MySQL ·········································12
2.1 MySQL 5.7的新特性 ·······································12
2.2 在不同平臺(tái)上安裝MySQL ·····································13
2.2.1 安裝條件 ··············································13
2.2.2 在Windows系統(tǒng)上安裝MySQL ········································13
2.2.3 在Linux系統(tǒng)上安裝MySQL ································17
2.2.4 打開MySQL數(shù)據(jù)庫 ··············································20
2.3 驗(yàn)證MySQL是否安裝成功 ············································21
2.3.1 獲取服務(wù)器狀態(tài) ············································22
2.3.2 執(zhí)行簡單的SQL 命令 ············································22
2.4 啟動(dòng)服務(wù)和登錄MySQL 數(shù)據(jù)庫 ·······································22
2.4.1 啟動(dòng)MySQL 服務(wù) ··············································22
2.4.2 登錄MySQL 數(shù)據(jù)庫 ··············································23
2.4.3 配置Path 變量 ·····································25
2.5 配置MySQL 數(shù)據(jù)庫 ········································26
2.5.1 使用配置向?qū)?······································26
2.5.2 手工更改配置 ······································30
2.6 使用MySQL Workbench 圖形管理工具·····································30
2.7 使用免安裝的MySQL ·····································31
2.8 小結(jié) ·········································33
2.9 練習(xí)題 ·····································33
第3 章 操作MySQL 數(shù)據(jù)庫 ········································ 34
3.1 創(chuàng)建數(shù)據(jù)庫 ································34
3.1.1 基本語法 ··············································34
3.1.2 創(chuàng)建并查看數(shù)據(jù)庫 ·······································35
3.1.3 創(chuàng)建指定字符集的數(shù)據(jù)庫 ·····································36
3.2 修改數(shù)據(jù)庫 ··········································37
3.2.1 基本語法 ··············································37
3.2.2 修改數(shù)據(jù)庫的字符集 ·············································38
3.3 刪除數(shù)據(jù)庫 ·············································38
3.3.1 基本語法 ··············································38
3.3.2 刪除數(shù)據(jù)庫 ··········································39
3.4 選擇數(shù)據(jù)庫 ···········································40
3.5 使用圖形界面管理數(shù)據(jù)庫 ·····································40
3.6 綜合案例 ·······························43
3.6.1 案例目的 ··············································43
3.6.2 案例演示過程 ······································44
3.7 小結(jié) ·········································46
3.8 練習(xí)題 ·····································46
第4 章 MySQL 存儲(chǔ)引擎與數(shù)據(jù)類型 ········································· 47
4.1 存儲(chǔ)引擎 ·······························47
4.1.1 什么是存儲(chǔ)引擎 ·········································47
4.1.2 MySQL 5.7 支持的存儲(chǔ)引擎 ·································48
4.1.3 如何選擇存儲(chǔ)引擎 ··············································48
4.1.4 操作默認(rèn)存儲(chǔ)引擎 ·············································49
4.2 數(shù)據(jù)類型 ·······························49
4.2.1 整數(shù)類型 ··············································50
4.2.2 浮點(diǎn)數(shù)類型和定點(diǎn)數(shù)類型 ·····································51
4.2.3 日期與時(shí)間類型 ········································52
4.2.4 字符串和二進(jìn)制類型 ··········································55
4.3 綜合案例 ·······························59
4.3.1 案例目的 ··············································59
4.3.2 案例演示過程 ······································59
4.4 小結(jié) ·········································60
4.5 練習(xí)題 ·····································60
第5 章 操 作 表 ········································ 62
5.1 表的基本概念 ··································62
5.2 創(chuàng)建表 ·····································62
5.2.1 基本語法 ··············································62
5.2.2 在指定的數(shù)據(jù)庫中創(chuàng)建表 ·····································63
5.2.3 查看表結(jié)構(gòu) ··········································64
5.3 修改表 ·····································65
5.3.1 基本語法 ··············································66
5.3.2 添加字段 ··············································66
5.3.3 修改字段數(shù)據(jù)類型 ································67
5.3.4 刪除字段 ··············································68
5.3.5 修改字段名稱 ······································69
5.3.6 修改表名 ··············································69
5.4 刪除表 ·····································70
5.4.1 基本語法 ··············································70
5.4.2 刪除表 ·································70
5.5 使用圖形界面操作數(shù)據(jù)表 ·································71
5.6 綜合案例 ·······························75
5.6.1 案例目的 ··············································75
5.6.2 案例演示過程 ······································75
5.7 小結(jié) ········································79
5.8 練習(xí)題 ·····································79
第6 章 約 束 ··········································· 80
6.1 約束的作用 ······································80
6.2 主鍵約束 ·······························80
6.2.1 選取設(shè)置主鍵約束的字段 ·····································80
6.2.2 在創(chuàng)建表時(shí)設(shè)置主鍵約束 ·····································81
6.2.3 在創(chuàng)建表時(shí)設(shè)置復(fù)合主鍵 ·····································82
6.2.4 在修改表時(shí)添加主鍵約束 ·····································83
6.2.5 使用圖形界面操作主鍵約束 ·································83
6.3 外鍵約束 ·······························84
6.3.1 選取設(shè)置外鍵約束的字段 ·····································84
6.3.2 在創(chuàng)建表時(shí)設(shè)置外鍵約束 ·····································85
6.3.3 在修改表時(shí)添加外鍵約束 ·····································86
6.3.4 刪除外鍵約束 ······································87
6.3.5 使用圖形界面操作外鍵約束 ·································88
6.4 約束 ·······························89
6.4.1 選取設(shè)置約束的字段 ·····································89
6.4.2 在創(chuàng)建表時(shí)設(shè)置約束 ·····································89
6.4.3 在修改表時(shí)添加約束 ·····································90
6.4.4 刪除約束 ······································90
6.4.5 使用圖形界面操作約束 ·································91
6.5 檢查約束 ·······························91
6.5.1 選取設(shè)置檢查約束的字段 ·····································91
6.5.2 在創(chuàng)建表時(shí)設(shè)置檢查約束 ·····································92
6.5.3 在修改表時(shí)添加檢查約束 ·····································92
6.5.4 刪除檢查約束 ······································93
6.5.5 使用圖形界面操作檢查約束 ·································93
6.6 默認(rèn)值約束 ······································93
6.6.1 選取設(shè)置默認(rèn)值約束的字段 ·································93
6.6.2 在創(chuàng)建表時(shí)設(shè)置默認(rèn)值約束 ·································93
6.6.3 在修改表時(shí)添加默認(rèn)值約束 ·································94
6.6.4 刪除默認(rèn)值約束 ····································94
6.6.5 使用圖形界面操作默認(rèn)值約束 ········································95
6.7 非空約束 ·······························95
6.7.1 選取設(shè)置非空約束的字段 ·····································95
6.7.2 在創(chuàng)建表時(shí)設(shè)置非空約束 ·····································95
6.7.3 在修改表時(shí)添加非空約束 ·····································96
6.7.4 刪除非空約束 ······································97
6.7.5 使用圖形界面操作非空約束 ·································97
6.8 查看表中的約束 ···············································98
6.9 綜合案例 ·······························98
6.9.1 案例目的 ··············································98
6.9.2 案例演示過程 ······································99
6.10 小結(jié) ·····································100
6.11 練習(xí)題 ·······························100
第7 章 函 數(shù) ········································· 102
7.1 數(shù)值型函數(shù) ····································102
7.1.1 ABS 函數(shù) ············································102
7.1.2 MOD 函數(shù) ··········································103
7.1.3 SQRT 函數(shù) ·········································103
7.1.4 SIGN 函數(shù) ··········································103
7.1.5 CEIL 與CELING 函數(shù) ·········································104
7.1.6 三角函數(shù) ············································104
7.2 字符串函數(shù) ····································106
7.2.1 LENGTH 函數(shù) ·····································106
7.2.2 UPPER 與LOWER 函數(shù) ······································107
7.2.3 LEFT 與RIGHT 函數(shù) ···········································107
7.2.4 CONCAT 函數(shù) ·····································108
7.2.5 TRIM 函數(shù) ·········································108
7.2.6 INSERT 函數(shù) ······································109
7.2.7 SUBSTRING 函數(shù) ·······························109
7.2.8 REVERSE 函數(shù) ····································110
7.2.9 REPLACE 函數(shù) ····································110
7.3 日期與時(shí)間函數(shù) ·············································111
7.3.1 NOW 和SYSDATE 函數(shù) ·····································111
7.3.2 CURDATE 函數(shù)和CURRENT_DATE 函數(shù) ·································111
7.3.3 CURTIME 函數(shù)和CURRENT_TIME 函數(shù) ··································112
7.3.4 DAYOFWEEK 函數(shù)和WEEKDAY 函數(shù) ·······································112
7.3.5 DAYOFMONTH 函數(shù)和DAYOFYEAR 函數(shù) ······································113
7.3.6 MONTH 函數(shù)和MONTHNAME 函數(shù) ········································113
7.3.7 DATEDIFF 函數(shù)···································114
7.3.8 ADDDATE 函數(shù) ···································114
7.3.9 DATE_FORMAT 函數(shù) ··········································115
7.4 聚合函數(shù) ········································116
7.4.1 MAX 函數(shù) ··········································116
7.4.2 MIN 函數(shù) ············································117
7.4.3 COUNT 函數(shù) ······································118
7.4.4 SUM 函數(shù) ···········································118
7.4.5 AVG 函數(shù) ···········································119
7.5 綜合案例 ········································119
7.5.1 案例目的 ············································119
7.5.2 案例演示過程 ······································120
7.6 小結(jié) ·······································121
7.7 練習(xí)題 ·································121
第8 章 使用基本的查詢語句 ····································· 122
8.1 查詢語句基礎(chǔ) ································122
8.1.1 基本語法 ············································122
8.1.2 使用“*”查詢表中的全部內(nèi)容 ····································123
8.1.3 查詢表中指定的字段 ···········································124
8.1.4 使用DISTINCT 關(guān)鍵字去除結(jié)果中的重復(fù)行 ································126
8.1.5 在查詢時(shí)給表和字段設(shè)置別名 ······································127
8.1.6 使用LIMIT 關(guān)鍵字指定查詢結(jié)果的行數(shù) ····································128
8.1.7 使用ORDER BY 對(duì)查詢結(jié)果進(jìn)行排序 ······································130
8.2 帶WHERE 關(guān)鍵字的條件查詢 ········································132
8.2.1 常用的運(yùn)算符 ······································132
8.2.2 單一條件的查詢語句 ···········································138
8.2.3 多條件的查詢語句 ··············································138
8.2.4 使用LIKE 的模糊查詢 ········································139
8.2.5 日期字段作為條件的查詢語句 ······································141
8.3 綜合案例 ········································141
8.3.1 案例目的 ············································141
8.3.2 案例演示過程 ······································142
8.4 小結(jié) ·······································145
8.5 練習(xí)題 ·································145
第9 章 高級(jí)查詢 ······································ 146
9.1 多表查詢 ········································146
9.1.1 內(nèi)連接查詢 ········································147
9