這篇文章協助大家學習MySQL這套資料庫管理系統,並利用MySQL Workbench練習如何儲存、搜尋、排序及檢索資料。


資料庫系統簡介

一個資料庫管理系統(DataBase Manage-ment System, DBMS)中可以有很多個資料庫,也可以有很多組使用者帳戶,當使用者想要存取資料庫內容時,必須先通過帳戶驗證,下圖說明它們的關係。

database


MySQL簡介

MySQL是一套快速、功能強大的資料庫管理系統。所謂資料庫管理系統( Database Management System, 簡稱為 DBMS ),它是透過一組程式模組來組織、管理、儲存和讀取資料庫的資料,任何使用者在操作資料庫時,都需要透過資料庫管理系統來處理。
目前MySQL已經成為最流行的開源資料庫,因此被廣泛地應用在Internet上的中小型網站中。隨著MySQL的不斷成熟,它也逐漸用於更多大規模網站和應用,例如Google和Facebook等網站。


MySQL Workbench簡介

MySQL Workbench是一款專為資料庫架構師、開發人員和DBA打造的一個統一的視覺化工具。MySQL Workbench提供了資料建模工具、SQL開發工具和全面的管理工具(包括伺服器配置、使用者管理、備份等),可在Windows、Linux和Mac OS上使用。

workbench

新版的MySQL Workbench 6介面如上圖所示。最大的改進在於圖形化使用者介面(GUI)和工作流,同時為開發人員和DBA提供更加現代和精簡的設計、開發和管理資料庫的工具。


使用 Workbench

  • 使用root或其他使用者帳戶登入

點選+ 新增一個連線。

add

輸入連線名稱,帳號、密碼(或連線時輸入),可以按下 ”Test Connection” 確認資料庫是否連接成功。

mysql1

  • 查看所有的使用者

use mysql; #使用mysql資料庫
select *from user; -- 選擇user資料表

mysql2

說明:

執行單行指令快捷鍵:Ctrl + Enter。
執行全部或已框選指令快捷鍵:Ctrl + Shift + Enter。
localhost、127.0.0.1和::1 (IPv6)皆代表本機地址。
單行註解:- – 註解內容 or #註解內容
多行註解:/*註解內容*/

  • 新增使用者
grant privileges on database.table to 'user'@'localhost' identified by 'password'

說明:
privileges設定權限等級。
database.table設定可以存取的資料庫與資料表名稱。
‘user’@’localhost’ 設定使用者的帳號與主機。
‘password’ 設定使用者的密碼。

舉例來說,我們可以下這個指令來新增使用者,並檢視一下結果

grant all on *.* to 'student'@'localhost' identified by '123456' with grant option;
select *from user;

mysql3
說明:

all代表擁有所有管理權限。
*.*代表所有資料庫裡的所有資料表。
with grant option:可以賦予其他使用者權限。

  • 其他使用者管理功能
set SQL_SAFE_UPDATES = 0; #關閉Safe Update Mode
delete from user where user ='user'; #刪除使用者
update user set password= password ('password') where user='user'; #修改密碼
flush privileges; #在mysql資料庫內,要用flush更新記憶體上的資料

MySQL資料格式
主要分為三類:Numeric、Date and Time、String Type。

  • Numeric
type bytes range
tinyint 1 -128 ~ 127
unsigned 0 ~ 255
smallint 2 -32768 ~ 32767
unsigned 0 ~ 65535
integer 4 -2147483648 ~ 2147483647
unsigned 0 ~ 16777215
bigint 8 -9.2 * 10的18次方 ~ 9.2 * 10的18次方
unsigned 0 ~ 4294967295
float 4 -3.402823466E+38 to -1.175494351E-38
,0,and
1.175494351E-38 to 3.402823466E+38
double 8 -1.7976931348623157E+308 to -2.2250738585072014E-308
,0,and
2.2250738585072014E-308 to 1.7976931348623157E+308
  • Date and Time
type bytes range
DATE 3 ‘1000-01-01’ ~ ‘9999-12-31’
DATETIME 8 ‘1000-01-01 00:00:00’ ~ ‘9999-12-31 23:59:59’
TIMESTAMP 4 ‘1970-01-01 00:00:00’ ~ ‘sometime in 2037’
TIME 3 ‘-838:59:59’ ~ ‘838:59:59’
YEAR 1 1901 ~ 2155 , and 0000
DATE 3 ‘1000-01-01’ ~ ‘9999-12-31’
  • String Type
type bytes
CHAR( L ) L bytes    1 <= L <= 255 bytes
VARCHAR( L ) L bytes    1 <= L <= 255 bytes
BLOB 可變長度 , 最長可達64KB( 65536 characters )
TEXT 可變長度 , 最長可達64KB( 65536 characters )
LONGBLOB 可變長度 , 最長可達4GB( 4294967295 characters )
LONGTEXT 可變長度 , 最長可達4GB( 4294967295 characters )
CHAR( L ) : L表示字串長度
BLOB 與 TEXT 的差別在於 BLOB 內容有大小寫之別

建立/刪除資料庫、資料表

#建立/刪除test1資料庫
create database test1;
use test1;
show tables;
drop database test1;

#建立/刪除mytable資料表
create table mytable(school char(5),name char(10),id int);
show tables;
describe mytable;
drop table mytable;


常用語法舉例

#資料表查詢 (選擇資料表mytable的所有欄位資料)
select * from mytable ;

#新增資料
insert into mytable(school, name, id) values ('NCTU','Jerry','123');
insert into mytable values ('NCTU','Jerry','123');

#更新資料
update mytable set name = 'HaHa' where id = '123';
#刪除資料
delete from mytable where name =" HaHa ";

#由檔案匯入資料 (用tap隔開欄位)
load data local infile "c:\\data.txt" into table mytable

由txt文字檔匯入mysql

mysql4 mysql5

#資料表查詢+條件+排序 (DESC代表由大到小排序)
select * from mytable where id = '123' order by name DESC;

#改變資料欄位 (加入時間記錄)
alter table mytable add column recordtime TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP;