MySQL自學筆記(1)

安裝MySQL

有三種安裝方式

  1. 安裝原生MySQL,或
  2. 安裝XAMPP,或
  3. 安裝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 [email protected]=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 指令:用於讀取二進制日誌的內容,這對於災難恢復至關重要。

使用資料庫

  1. create:建立資料庫
  2. show
  3. use
  4. describe
  5. 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);

建立索引的原則:

  1. 定義主鍵的數據列一定要建立索引。
  2. 定義有外鍵的數據列一定要建立索引。
  3. 對於經常查詢的數據列最好建立索引。
  4. 對於需要在指定範圍內的快速或頻繁查詢的數據列;
  5. 經常用在WHERE子句中的數據列。
  6. 經常出現在關鍵字order by、group by、distinct後面的欄位,建立索引。如果建立的是複合索引,索引的欄位順序要和這些關鍵字後面的欄位順序一致,否則索引不會被使用。
  7. 對於那些查詢中很少涉及的列,重複值比較多的列不要建立索引。
  8. 對於定義為text、image和bit的數據類型的列不要建立索引。
  9. 對於經常存取的列避免建立索引
  10. 限制表上的索引數目。對一個存在大量更新操作的表,所建索引的數目一般不要超過3個,最多不要超過5個。索引雖說提高了訪問速度,但太多索引會影響數據的更新操作。
  11. 對複合索引,按照欄位在查詢條件中出現的頻度建立索引。在複合索引中,記錄首先按照第一個欄位排序。對於在第一個欄位上取值相同的記錄,系統再按照第二個欄位的取值排序,以此類推。因此只有複合索引的第一個欄位出現在查詢條件中,該索引才可能被使用,因此將應用頻度高的欄位,放置在複合索引的前面,會使系統最大可能地使用此索引,發揮索引的作用。