商品描述
本書采用項目引導結合任務驅動的模式進行編寫。全書分為兩部分：示範篇和實訓篇。 示範篇以“學生成績管理系統”數據庫項目為主線，將該數據庫項目分解為多個任務，每個任務按照“知識目標”→“能力目標”→“任務情境”→“任務描述”→“任務分析”→“知識導讀”→“任務實施”→“任務總結”的形式進行編排，詳細講述了數據庫的設計、MySQL 數據庫的創建與管理、MySQL數據庫表數據的操作、MySQL 數據庫數據的程序式處理、MySQL 數據庫的運行與維護。 實訓篇以“社區書房管理系統”數據庫項目為主線，包括數據庫的設計、數據庫和表的管理、表數據的更新、表數據的查詢、索引的應用、視圖的應用、存儲過程和存儲函數的應用、觸發器的應用、數據庫的安全管理共 9 個實訓任務，重點培養學生提出問題、分析問題和解決問題的綜合能力。 本書以培養學生的數據庫設計、應用和管理能力為目標，內容新穎，通俗易懂，實用性強，可作為高等院校、高等職業院校數據庫相關課程的教材，也可作為廣大技術人員及自學者參考用書。
目錄大綱
第一篇 示範篇——學生成績 管理系統
工作任務一 數據庫的設計··············································································.2
1.1 初識數據庫系統················································································.2
1.1.1 數據庫系統的基本概念 ······························································.3
1.1.2 數據庫系統的基本特點 ······························································.6
1.1.3 數據庫系統的內部體系結構 ························································.6
1.2 “學生成績管理系統”數據庫設計概述與需求分析··································.10
1.2.1 數據庫設計概述·······································································.11
1.2.2 數據抽象過程··········································································.12
1.2.3 數據庫設計的需求分析 ·····························································.12
1.3 “學生成績管理系統”數據庫概念設計·················································.21
1.3.1 概念模型················································································.22
1.3.2 概念模型的表示方法 ································································.23
1.3.3 E-R 模型的設計·······································································.24
1.4 “學生成績管理系統”數據庫邏輯設計·················································.26
1.4.1 關系模型的基本術語 ································································.27
1.4.2 關系的定義和性質····································································.28
1.4.3 關鍵碼···················································································.29
1.4.4 E-R 模型到關系模型的轉換························································.29
1.4.5 關系模式的規範化····································································.30
1.5 “學生成績管理系統”數據庫物理設計·················································.35
1.5.1 MySQL 簡介···········································································.36
1.5.2 MySQL 系統數據類型·······························································.36
知識鞏固 1 ····························································································.41
工作任務二 MySQL 數據庫的創建與管理··························································44
2.1 “學生成績管理系統”數據庫創建和管理··············································.44
2.1.1 數據庫概述·············································································.45
2.1.2 使用 Navicat 圖形化工具創建數據庫·············································.46
2.1.3 SQL 簡介 ···············································································.48
2.1.4 使用 CREATE DATABASE 語句創建數據庫 ··································.48
VI | MySQL 數據庫應用項目化微課教程
2.1.5 使用 ALTER DATABASE 語句修改數據庫 ····································.51
2.1.6 使用 DROP DATABASE 語句刪除數據庫 ······································.51
2.2 “學生成績管理系統”數據表創建·······················································.52
2.2.1 數據表的概述··········································································.53
2.2.2 數據完整性·············································································.54
2.2.3 數據完整性約束·······································································.54
2.2.4 使用 Navicat 圖形化工具創建數據表·············································.55
2.2.5 使用 CREATE TABLE 語句創建數據表·········································.57
2.2.6 使用 CREATE TABLE…LIKE 語句復制數據表·······························.58
2.3 “學生成績管理系統”數據表管理·······················································.60
2.3.1 使用 SQL 語句顯示表信息 ·························································.61
2.3.2 使用 Navicat 圖形化工具修改數據表·············································.62
2.3.3 使用 ALTER TABLE 語句修改數據表···········································.63
2.3.4 使用 ALTER TABLE 語句修改表約束···········································.64
2.3.5 使用 RENAME TABLE 語句修改表名···········································.65
2.3.6 使用 DROP TABLE 語句刪除數據表 ············································.66
知識鞏固 2 ····························································································.67
工作任務三 MySQL 數據庫表數據的操作··························································70
3.1 數據更新························································································.70
3.1.1 使用 Navicat 圖形化工具更新數據················································.71
3.1.2 使用 INSERT 語句插入數據 ·······················································.72
3.1.3 使用 UPDATE 語句修改數據 ······················································.73
3.1.4 使用 DELETE 語句刪除數據·······················································.73
3.1.5 使用 TRUNCATE TABLE 語句清空數據 ·······································.74
3.2 單表查詢························································································.75
3.2.1 查詢簡介················································································.76
3.2.2 SELECT 查詢··········································································.77
3.2.3 查詢指定字段··········································································.77
3.2.4 查詢滿足條件的記錄 ································································.78
3.2.5 查詢結果的編輯·······································································.81
3.2.6 按指定列名排序·······································································.82
3.2.7 LIMIT 子句限制返回的行數 ·······················································.83
3.3 分組統計查詢··················································································.86
3.3.1 聚合（集合）函數····································································.87
3.3.2 分組統計················································································.88
3.3.3 分組篩選················································································.89
3.4 多表連接查詢··················································································.92
目錄 | VII
3.4.1 使用連接謂詞連接····································································.94
3.4.2 使用 JOIN 關鍵字連接 ······························································.95
3.5 嵌套查詢······················································································.100
3.5.1 嵌套查詢概述········································································.102
3.5.2 使用關系運算符的嵌套查詢 ·····················································.102
3.5.3 使用謂詞 IN 的嵌套查詢··························································.103
3.5.4 使用謂詞 EXISTS 的嵌套查詢···················································.104
3.5.5 帶子查詢的數據更新 ······························································.105
3.6 索引····························································································.110
3.6.1 索引概述··············································································.111
3.6.2 使用 Navicat 圖形化工具創建與刪除索引·····································.112
3.6.3 創建索引··············································································.113
3.6.4 使用 SHOW INDEX 語句查看索引·············································.115
3.6.5 使用 DROP INDEX 語句刪除索引··············································.115
3.7 視圖的創建與應用··········································································.116
3.7.1 視圖概述··············································································.117
3.7.2 使用 Navicat 圖形化工具創建視圖··············································.118
3.7.3 使用 CREATE VIEW 語句創建視圖············································.120
3.7.4 使用 SQL 語句查看視圖 ··························································.122
3.7.5 使用 ALTER VIEW 語句修改視圖··············································.123
3.7.6 使用 DROP VIEW 語句刪除視圖 ···············································.123
3.7.7 通過視圖管理數據··································································.123
知識鞏固 3 ··························································································.126
工作任務四 MySQL 數據庫數據的程序式處理 ·····················································.130
4.1 存儲過程和存儲函數的創建與應用·····················································.130
4.1.1 MySQL 編程基礎···································································.131
4.1.2 存儲過程··············································································.138
4.1.3 存儲函數··············································································.144
4.1.4 流程控制語句········································································.147
4.1.5 遊標····················································································.156
4.2 事務管理······················································································.161
4.2.1 事務的概念···········································································.162
4.2.2 事務的類型及操作··································································.163
4.3 觸發器的創建和應用·······································································.167
4.3.1 觸發器的概念········································································.168
4.3.2 創建與使用觸發器··································································.168
4.3.3 查看觸發器···········································································.170
VIII | MySQL 數據庫應用項目化微課教程
4.3.4 刪除觸發器···········································································.170
知識鞏固 4 ··························································································.171
工作任務五 MySQL 數據庫的運行與維護·······················································.174
5.1 MySQL 環境搭建 ···········································································.174
5.1.1 MySQL 安裝與配置································································.175
5.1.2 MySQL 圖形化管理工具介紹····················································.175
5.1.3 MySQL 服務器操作································································.176
5.2 數據庫的用戶和權限管理·································································.193
5.2.1 用戶管理··············································································.194
5.2.2 權限管理··············································································.198
5.3 數據庫的備份與還原·······································································.206
5.3.1 備份和還原概述·····································································.207
5.3.2 使用 Navicat 圖形化管理工具備份和還原數據庫 ···························.207
5.3.3 使用 mysqldump 命令備份數據庫···············································.210
5.3.4 使用 mysql 命令還原數據庫······················································.211
5.3.5 MySQL 日誌·········································································.212
5.4 表數據的導入與導出·······································································.214
5.4.1 使用 Navicat 圖形化管理工具將數據導出到 Excel 中 ·····················.215
5.4.2 使用 Navicat 圖形化管理工具導入 Excel 中的數據 ························.217
5.4.3 使用 mysql 命令導出為文本文件················································.220
5.4.4 使用 mysqlimport 命令導入文本文件···········································.221
知識鞏固 5 ··························································································.224
第二篇 實訓篇——社區書房管理系統
實訓任務一 數據庫的設計···········································································.228
一、實訓目的·················································································.228
二、實訓任務·················································································.228
實訓任務二 數據庫和表的管理·····································································.230
一、實訓目的·················································································.230
二、實訓任務·················································································.230
實訓任務三 表數據的更新···········································································.233
一、實訓目的·················································································.233
二、實訓任務·················································································.233
實訓任務四 表數據的查詢···········································································.235
一、實訓目的·················································································.235
目錄 | IX
二、實訓任務·················································································.235
實訓任務五 索引的應用··············································································.238
一、實訓目的·················································································.238
二、實訓任務·················································································.238
實訓任務六 視圖的應用··············································································.239
一、實訓目的·················································································.239
二、實訓準備·················································································.239
三、實訓任務·················································································.239
實訓任務七 存儲過程和存儲函數的應用 ························································.241
一、實訓目的·················································································.241
二、實訓任務·················································································.241
實訓任務八 觸發器的應用···········································································.242
一、實訓目的·················································································.242
二、實訓任務·················································································.242
實訓任務九 數據庫的安全管理·····································································.243
一、實訓目的·················································································.243
二、實訓任務·················································································.243
