MySQL 基本操作

李亮寬

MySQL Server

所謂的SQL（Structured Query Language）中文翻譯成結構化查詢語言，是目前關聯式資料庫管理系統（DBMS）上，使用率較高的查詢語言。SQL 的基本操作語法是由一些簡單的英文語句組成，相當容易學習。

市面上流通的 SQL 品牌眾多，如 Microsoft SQL Server、Oracle、SyBase、Informix、DB2...以及在 Linux 系統上常使用的 MySQL、Postgress SQL、mSQL...等。

談及資料庫，我們大致上用下圖來說明一個完整的資料庫系統架構

上圖包含了三個部分： 外部層：使用者可以看到與操作的部分，例如 資料表或VB、Foxpro...等應用程式。

概念層：為外部層與內部層溝通的橋樑。

內部層：實際儲存資料所在，其關係到資料存儲的技術、與檔案系統的關聯、存取效能...等。講到這裡，對剛入門的使用者而言，似乎有點複雜難懂，我們在這裡並不會詳細的解說資料庫系統，而是會為各位來介紹在整個資料庫統中重要的成員，也就是前面提及的資料庫管理系統（DBMS）。

所謂的資料庫管理系統（DBMS）是一種套裝軟體，負責處理使用者（或應用程式）存取資料的要求。當收到存取資料庫要求後，DBMS會先進行分析，然後檢查外部層綱要、外部層與概念層的對應關係、外部層綱要、概念層與內部層的對應關係、資料儲存結構，最後執行所要球的動作。

而一般高階的查詢語言會包含以下部分： 資料庫定義語言（Data Definition Language，DDL）：定義資料庫的綱要內容。

資料庫操作語言（Data Manipulation Language，DML）：用來對資料庫進行新增、查詢、刪除、修改等。

資料庫控制語言（Data Contorl Language，DCL）：用來控制資料庫資料的安全與存取權限。聽起來好像要學資料庫"粉"困難，其實上述也只是概念性的東西，我們還有一大堆東西沒有提及，像是資料庫正規化、資料模式、資料結構、資料探勘......等，對這些基本功有興趣的話，可以參考市面上的相關書籍，如"儒林出版的資料庫系統之理論與實務"...。好！把話題拉回來主題，本單元要介紹的是在 Linux 系統上常被使用的資料庫管理系統 MySQL 。這統資料庫管理系統事實上也有 Win32 的版本，有興趣的朋友可以連結到 MySQL 官方網站下載。（MySQL 的官方版發音為 My S‧Q‧L 不是 My Sequel ）

安裝

原則上，MySQL 在 Server 端安裝完成後，必須透過 MySQL Client 來進行管理或存取，但您也可以透過其它應用程式所提供的指令函數來進行管理或存取，如 PHP 所提供之函數等。因此，您在安裝時記得除了 MySQL Server 要安裝外，其 Client 端程式最好也順道的安裝進來。rpm  -ivh  MySQL-version.i386.rpm
rpm  -ivh  MySQL-client-version.i386.rpm

在安裝主要程式完畢時，會出現上圖的內容，特別注意的是在中間部分提醒使用者要去設定 MySQL 管理者帳戶密碼。

/usr/bin/mysqladmin -u root password 'new-password'
/usr/bin/mysqladmin -u root -h Linux.indicator-online.net password 'new-password'

其實，在上圖安裝主要套件完成時，會執行 mysql_install_db 的指令，幫 MySQL Server 建立相關的資料庫與資料表，建立完成後才提醒使用者記得變更管理者密碼（注意：此 root 密碼與 Linux 系統的管理帳戶 root 不同）。
[img=12,12]mhtml:file://Q:\01_教務處\01E_資訊組\_伺服器安裝\Discuz\安裝技術文件\MySQL基本操作.mht!x-usc:[/img] 啟動 MySQL

安裝沒有問題後，接下來就準備啟動 MySQL Server 了。

/etc/init.d/mysql  start

[img=12,12]mhtml:file://Q:\01_教務處\01E_資訊組\_伺服器安裝\Discuz\安裝技術文件\MySQL基本操作.mht![/img] 以工作端程式來連接 MySQL Server

MySQL服務啟動後，Server端程式已經大功告成，接著就是工作端的連接與管理。 你可以在安裝MySQL的本機端以mysqladmin或是mysql指令來管理與連接，小弟我習慣以mysql指令進入工作端操作模式來運作，連接指令如下：

mysql -h <hostname> -u <username> -p<password>

實際舉例：

mysql -h localhost -u root -p123456

其中 -h localhost 若是在本機端則不需要填寫，當然如果是以 root 身分且沒有設定密碼， 則可使用  mysql 指令即可進入，在次提醒各位 MySQL 內部的使用者帳戶，與 Linux 系統的使用者是分開的， 預設的 MySQL 最高權限的管理員是 root，其密碼是沒有設定的。
MySQL 建置安裝完成時，內部已經存在著兩個 Database（mysql 與 test）， 各位以 MySQL 工作端連結到 MySQL Server 後，可以使用下列指令來查看 MySQL 有多少的 Database：



使用 show databases 指令時，因為 show 是 MySQL 的內部指令，所以需加上分號或是 \g 傳送到 MySQL 內。 接著使用外部指令 use 開啟 mysql 資料庫，指令大小寫相同，但所要開啟的資料庫則大小寫有差別。

資料庫切換後，接著一樣以 show tables; 指令來查看預設的 mysql 資料庫內有那些資料表（稍後再詳談）：



如果要查詢資料表的欄位，可以使用 show columns from db from mysql; 指令來查看（db 是資料表名稱 mysql 是資料庫名稱）。這裏我們就不提及資料表內欄位屬性的問題了



最後要離開工作端程式可執行 \q 指令或 quit 或 exit。
OK！講解到這裡各位是不是覺得操作 MySQL 並不困難呢？！
再複習一下操作指令（請注意字串尾部的分號問題）：

[img=15,15]mhtml:file://Q:\01_教務處\01E_資訊組\_伺服器安裝\Discuz\安裝技術文件\MySQL基本操作.mht!x-usc:[/img] 啟用工作端連結 mysql
[img=15,15]mhtml:file://Q:\01_教務處\01E_資訊組\_伺服器安裝\Discuz\安裝技術文件\MySQL基本操作.mht![/img] 查詢顯示資料庫 show databases;
[img=15,15]mhtml:file://Q:\01_教務處\01E_資訊組\_伺服器安裝\Discuz\安裝技術文件\MySQL基本操作.mht![/img] 開始指定資料庫 use <database name>
[img=15,15]mhtml:file://Q:\01_教務處\01E_資訊組\_伺服器安裝\Discuz\安裝技術文件\MySQL基本操作.mht![/img] 查詢顯示工作表 show tables;
[img=15,15]mhtml:file://Q:\01_教務處\01E_資訊組\_伺服器安裝\Discuz\安裝技術文件\MySQL基本操作.mht![/img] 查詢工作表欄位 show colums from <table name> from <database>;
[img=15,15]mhtml:file://Q:\01_教務處\01E_資訊組\_伺服器安裝\Discuz\安裝技術文件\MySQL基本操作.mht![/img] 離開工作端程式 \q 或 quit 或 exit

[img=12,12]mhtml:file://Q:\01_教務處\01E_資訊組\_伺服器安裝\Discuz\安裝技術文件\MySQL基本操作.mht![/img] 使用者的建立

怎麼建立使用者呢？
MySQL 分為本機端操作的使用者與遠端操作的使用者， 所以在建立使用者時，要特別的指明要建立的是那一種類型的使用者，各位可以使用下列指令來建立一個名為 sqluser 本機端的使用者：

GRANT ALL PRIVILEGES ON *.* TO sqluser@localhost IDENTIFIED BY 'something' WITH GRANT OPTION;

在指令中 sqluser 後面＠符號後加上指定的主機名稱或網域名稱即變成遠端的使用者，如：

GRANT ALL PRIVILEGES ON *.* TO sqluser@tgic.idv.tw IDENTIFIED BY 'something' WITH GRANT OPTION;

不過您也不確定使用者從那一部機器連接， 所以您可以使用 "%" 符號來指定 MySQL 接受 sqluser 使用者從不同的主機連接進來。

GRANT ALL PRIVILEGES ON *.* TO sqluser@"%" IDENTIFIED BY 'something' WITH GRANT OPTION;

當然我們希望各位在做練習時，可以為 root 建立一個遠端的使用者。

GRANT ALL PRIVILEGES ON *.* TO root@"%" IDENTIFIED BY 'something' WITH GRANT OPTION;

不管您是建立什麼樣的名稱，請在建立後更改密碼：

SET PASSWORD FOR sqluser@"%"=PASSWORD('123456');
SET PASSWORD FOR root@"%"=PASSWORD('123456');



簡單的說，任何的伺服器登必須經過身分的認證後，才能使用其所提供的資源，MySQL 工作端的登入大致可分成本機端的使用者登入與遠端的使用者登入，例如 root 這個 MySQL 的帳號，預設值可是只能在本機端登入唷！

它無法從 本機以外 的工作端程式透過Internet連結登入，您必須在本機端再建立一個遠端登入的root使用者帳戶，本機以外的工作端程式才能夠順利登入。

在這裡我們要來看看登工作端連接到MySQL伺服器時，進行了那些動作？
不管您是在本機端以 mysql 指令連接或是從其他的程式連接，基本上MySQL會進行兩個階段的檢查工作：

[img=15,15]mhtml:file://Q:\01_教務處\01E_資訊組\_伺服器安裝\Discuz\安裝技術文件\MySQL基本操作.mht!x-usc:[/img] 確認是否允許連線：這當然就是要檢查您登入時所使用的帳戶以及密碼，還有您是在本機端登入還是從遠端登入？

[img=15,15]mhtml:file://Q:\01_教務處\01E_資訊組\_伺服器安裝\Discuz\安裝技術文件\MySQL基本操作.mht![/img] 檢查登入後下達指令的權限：例如您下達create database sb;指令時，MySQL會根據mysql資料庫內的資料，來判定是否指令可以被執行。

[img=12,12]mhtml:file://Q:\01_教務處\01E_資訊組\_伺服器安裝\Discuz\安裝技術文件\MySQL基本操作.mht![/img] mysql 資料庫內的五大表單

一切都 OK 後，我們來研究一下當建立使用者後，這些資料存放到那裡去了？
前面提及 MySQL 內建的兩個資料庫，一個是 mysql 一個是 test，test 是空的資料庫提供給各位測試練習，而 mysql 資料庫內則是存放著 MySQL 各項使用者與資料庫權限資料。mysql 資料庫內有六個表單，其中五個是我們在管理使用者與資料庫時會用到的，user、db、host、Tables_priv、Colums_priv。

[img=15,15]mhtml:file://Q:\01_教務處\01E_資訊組\_伺服器安裝\Discuz\安裝技術文件\MySQL基本操作.mht![/img] user：存放資料苦使用者的使用權限。
[img=15,15]mhtml:file://Q:\01_教務處\01E_資訊組\_伺服器安裝\Discuz\安裝技術文件\MySQL基本操作.mht![/img] db：存放資料庫的使用權限紀錄。
[img=15,15]mhtml:file://Q:\01_教務處\01E_資訊組\_伺服器安裝\Discuz\安裝技術文件\MySQL基本操作.mht![/img] host：存放連接的來源主機對資料庫的使用權限。
[img=15,15]mhtml:file://Q:\01_教務處\01E_資訊組\_伺服器安裝\Discuz\安裝技術文件\MySQL基本操作.mht![/img] Tables_priv：存放各表單的使用權限。
[img=15,15]mhtml:file://Q:\01_教務處\01E_資訊組\_伺服器安裝\Discuz\安裝技術文件\MySQL基本操作.mht![/img] Colums_priv：存放資料表內的欄位使用權限。

前面在建立使用者時，我們令所建立的使用者具有 ALL 所有的權限（包含所有的資料庫），各位往後在做管理時，切記不該如此來建立使用者（除非建立的是管理者帳號）。一般在管理權限時，是針對資料庫及表單來進行權限的授與，例如我們建立了一個叫做 News，內有 Books、Bookstores 等表單，您必須為使用者指令對該資料庫或表單的權限：

GRANT SELECT,INSERT,UPDATE,DELETE,CREATE,DROP ON News.* TO NewUser＠"%" IDENTIFIED BY '123456';

上述指令指定了六種權限（SELECT,INSERT,UPDATE,DELETE,CREATE,DROP）給一個 NewUser＠"%"，而 News.* 是指在 News 資料庫下所有的資料表。如此便可建立 NewUser 對 News 資料庫內所有的表單有查詢、新增、更新、刪除、建立表單、刪除表單等權限。 [img=12,12]mhtml:file://Q:\01_教務處\01E_資訊組\_伺服器安裝\Discuz\安裝技術文件\MySQL基本操作.mht![/img] 建立資料庫與資料表 前面提及了一些 MySQL 的管理操作，接下來我們要來談及資料庫建立及表單建立。

同樣的，資料庫的建立可以使用 mysqladmin 或是直接進入 Client 程式 mysql 來建立：

# mysqladmin create sb                    ＜＝建立以 sb 為名的資料庫

# mysql
mysql> create database sb;

sb 資料庫建立後，會在/var/lib/mysql目錄下建立一個sb目錄，接下來的工作就是在該資料庫內建立資料表：

mysql> use sb
mysql> create table books (b_no char(5) not null, b_name char(20), price int, primary key(b_no));

如此books的表單即便建立了（內有 b_no、b_name、price 三欄位），各位可以使用前面提到的 show 指令查看：

mysql> show columns from books from sb;


建立表單是不是很簡單呢？搞清楚要建立的欄位，然後以正確的指令操作。

知道了如何建立表單，也要瞭解如何刪除以建立的表單，各位可以使用 drop 的指令來刪除表單或資料庫：

mysql> drop table books;           ＜＝刪除表單
mysql> drop database sb;           ＜＝刪除資料庫

當然您可以使用像是 alter 來變更表單結構（詳細操作指令可參考相關 SQL 書籍）。

有了表單後，各位必須將資料輸入表單內，就是使用insert into的指令：

mysql> insert into books values ('00001','水滸傳',350);
mysql> insert into books (b_no,b_name,price) values ('00002','三國演義',400);

有了資料後，可以使用 select 資料庫控制語言指令來查詢表單內的資料：

mysql> select  *  from books;

MySQL 與 PHP
至於對資料庫的存取，PHP提供了許多存取的函數可以讓網頁編輯者使用，同樣的透過PHP網頁與MySQL連接也必須要先經過身分與權限的認證。以下是PHP提供的函數：

mysql_connect($host, $user, $password);     建立與MySQL伺服器的連接
令 $link=mysql_connect($host, $user, $password)
mysql_create_db("db_name", $link);                                      建立資料庫
mysql_drop_db("db_name", $link);                                        刪除資料庫
mysql_select_db("db_name", $link);                                       開啟資料庫（like use in mysql client)
mysql_query($query_string, $link);                                        查詢資料
mysql_db_query($database, $query_string, [$link])               到指定的資料庫查詢

實例

<?php
$hostname = "localhost";
$user = "root";
$pass = "";
$DB = "postoffice";


if($link = mysql_connect($host, $user, $pass))
{
echo("連接MySQL成功<BR>\n");
}
else
{
echo("連接MySQL失敗<BR>\n");
}

if(mysql_select_db($DB, $link))
{
echo("開啟DB成功<BR>\n");
}
else
{
echo("開啟DB失敗<BR>\n");
}

if(mysql_query("select * from mails", $link))
{
$query = mysql_query("select * from mails", $link);
$num=mysql_num_rows($query);
for($i=0;$i<$num;$i++);
{
$re=mysql_fetch_array($query);
echo $re[no];
echo ("<BR>\n");
echo $re[internal_mail];
echo ("<BR>\n");
echo $re[internal_price];
}
}
else
{
echo("查詢??＜BR>\n");
}
?>