安裝MySQL
有三種安裝方式
- 安裝原生MySQL,或
- 安裝XAMPP,或
- 安裝MAMP
因為屆時要使用phpmyadmin做資料庫管理,需要有php以及Apache或Nginx或IIS的網頁伺服器環境,所以建議安裝XAMPP或MAMP較為簡便,因為他會順便把php與Apache也一起安裝進去。(甚至phpmyadmin也會順便一起安裝進去)
XAMPP
把Apache, MySQL的伺服器按下「Start」以啟動伺服器。
然後就可以連線到 http://127.0.0.1 以及 http://127.0.0.1/phpmyadmin 查看資料
預設不須帳號密碼就可以連線到 http://127.0.0.1/phpmyadmin 管理資料庫。
登入MySQL資料庫
指令:
mysql -u username
shell> mysql -u root
# -u:帳號 root
mysql>\q #離開(或quit)
shell>
登入另㇐台主機mysql資料庫
shell> mysql -h hostname -u username –p
# -h:hostname IP
# -u: 帳號
# -p:密碼
更改root密碼
mysql>set password for root@localhost=password('your password');
mysql>flush privileges; #權限立即寫入MySQL資料庫
重要資料夾
Bin 資料夾
MySQL可執行檔位於bin目錄內,主要的成員如下:
- mysqld可執行檔:可啟動MySQL伺服器,為㇐個資料庫管理工具,透過此工具可對資料庫進行組態配置
- mysql可執行檔:即MySQL監視器。這是客戶端命令列工具,可下達SQL命令來進行資料庫、表格與資料的操作
Data資料夾
每個資料庫對應到資料目錄(Data Directory)內的㇐個資料庫目錄。
資料庫中的表格(Table)對應到資料庫目錄內的㇐個檔案。
- 資料目錄還可能包含其他文件:
- 伺服器生成的狀態和日誌文件。
- Data資料夾
- data: This is where your actual database data resides.
Share資料夾
MySQL資料目錄包含伺服器管理的所有資料庫與其內的表格。通常
它們被組裝成樹狀結構,可使用UNIX或Windows檔案系統的層階
架構來實現這樣的樹狀結構。
- Share資料夾
- Share:containing MySQL’s error messages
MySQL可執行的指令
- mysqladmin 指令:是㇐個維護/管理公用程式,可執行許多與維護/管理有關的命令。
- mysqlshow 指令:用於獲取有關資料庫和表的信息。
- myisamchk 指令:用於檢查和修復損壞的MyISAM表。
- mysqldump 指令:用於備份資料庫。
- mysqlbinlog 指令:用於讀取二進制日誌的內容,這對於災難恢復至關重要。
使用資料庫
- create:建立資料庫
- show
- use
- describe
- source:把多個執行SQL指令寫在㇐個檔案,類似批次BAT檔,㇐次執行多個SQL指令
指令:
CREATE DATABASE [IF NOT EXISTS] databaseName;
mysql> create database employee; # 建立資料庫
mysql> show databases; # 印出有那些資料庫
mysql> use databasename; # 使用特定資料庫
mysql> show tables; # 印出有那些表格
mysql> describe tablename; # 印出有那些欄位
mysql> source filename; 執行檔案內的多個SQL指令
shell> mysql -u username -p < filename
正規化
目的為簡化資料庫資料,讓資料庫搜尋更有效率,去除重複資料
第一正規化
目的為:去除重複
第二正規化
- 考慮相依性
- 外鍵參考到主鍵
- 一個資料表可以有多個主鍵
- 外部資料表的某一欄位(外鍵)參考到主資料表的主鍵
- 主鍵有唯一性,不可為空值,不可重複
第三正規化
- 若A與B有相依性,B與C有相依性,則A與C為遞移相依性
- 去除遞移相依,則為第三正規化
- Third Normal Form 3F:remove all transitive dependencies移除遞移相依
- 實務上只做到第三正規化
做完正規化後,可以避免以下錯誤:
- Insertion Anomalies 輸入異常
- Deletion Anomalies 刪除異常
- Update Anomalies 更新異常
SQL指令
- SQL keywords are not case sensitive 關鍵字(保留字)不分大小寫
- 關鍵字(保留字)可以是別名,資料庫名稱,表格名稱,欄位名稱等等
- 註解符號:
#
號開始的文字都是註解--
:"–"雙減號開始的文字也是註解/*....*/
:用㇐對"/“及”*"括起來的文字是註解
範例:
mysql> create database employee; # 建立employee資料庫
mysql> show databases; # 展示所有資料庫
mysql> use employee; # 使用employee 資料庫
SQL語言
常用的欄位資料型態:
- INT : 整數型態 (-2147483648 ~ +2147483647)
- DECIMAL(M, D): 浮點數(含有小數點的數)
- M:整數與小數位數的總位數
- D :小數位數(介於 0~M) – 最多38位數
- VARCHAR(N):變動⾧度的字串
- N:最多255個字元
- CHAR(N):固定⾧度的字串
- DATE:可以存放格式為yyyy-mm-DD的日期資料
- BLOB(Binary Large OBject):可以存放二進位資料
CREATE TABLE命令
CREATE TABLE [IF NOT EXISTS] table_name(
column_1_definition,
column_2_definition,
...,
[FOREIGN KEY (column_1) references table2_name
(column_1)]
) ENGINE=storage_engine;
# 欄位與欄位之間用逗點隔開
DDL
(Data Definition Language 數據定義語言)用於操作對象及對象本身,這種對象包括資料庫,表對象,及視圖對象。
包含的操作語句:
create:創建資料庫和資料庫的一些對象
drop:刪除數據表、索引、觸發程序、條件約束以及數據表的權限等
alter:修改數據表定義及數據屬性
DML
(Data Manipulation Language 數據操控語言) 用於操作資料庫對象對象中包含的數據
包含的操作語句:
insert: 向資料庫插入一條數據
delete: 刪除表中的一條或多條記錄
update: 用於修改表中的數據
DQL
(Data Query Language 數據查詢語言 )用於查詢數據
包含的操作語句:
select: 用於查詢表中的數據
DCL
(Data Control Language 數據控制語句) 用於操作資料庫對象的權限
包含的操作語句:
greate: 分配權限給用戶
revoke: 廢除資料庫中某用戶的權限
索引
- 索引可由㇐或多個欄位所組成,使用者可以自行建立索引。
- MySQL建立主鍵(Primary Key)與唯㇐鍵(Unique Key)時,會自動建立索引。
- MySQL中建立外鍵(Foreign Key)時,需建立索引以方便搜尋。
- 主鍵與唯㇐鍵被捨棄時,索引㇐併刪除。
- 外鍵被捨棄時,並不會㇐併刪除索引。
- 刪除資料表時,所有的索引㇐併刪除。
- 索引的使用與維護由系統負責。
建立與刪除索引
CREATE [UNIQUE] INDEX index
ON TableName(column [ASC|DESC],...);
範例:
create index name on employee(name); # 建立索引
drop index name on employee; # 刪除索引
已更改的方式建立索引
修改表格以建立索引,跟上一個語法效果相同
ALTER TABLE table add INDEX IndexName;
範例:
alter table employee add index name (name);
建立索引的原則:
- 定義主鍵的數據列一定要建立索引。
- 定義有外鍵的數據列一定要建立索引。
- 對於經常查詢的數據列最好建立索引。
- 對於需要在指定範圍內的快速或頻繁查詢的數據列;
- 經常用在WHERE子句中的數據列。
- 經常出現在關鍵字order by、group by、distinct後面的欄位,建立索引。如果建立的是複合索引,索引的欄位順序要和這些關鍵字後面的欄位順序一致,否則索引不會被使用。
- 對於那些查詢中很少涉及的列,重複值比較多的列不要建立索引。
- 對於定義為text、image和bit的數據類型的列不要建立索引。
- 對於經常存取的列避免建立索引
- 限制表上的索引數目。對一個存在大量更新操作的表,所建索引的數目一般不要超過3個,最多不要超過5個。索引雖說提高了訪問速度,但太多索引會影響數據的更新操作。
- 對複合索引,按照欄位在查詢條件中出現的頻度建立索引。在複合索引中,記錄首先按照第一個欄位排序。對於在第一個欄位上取值相同的記錄,系統再按照第二個欄位的取值排序,以此類推。因此只有複合索引的第一個欄位出現在查詢條件中,該索引才可能被使用,因此將應用頻度高的欄位,放置在複合索引的前面,會使系統最大可能地使用此索引,發揮索引的作用。