Download
1 / 58
580 likes | 717 Views
＊教科書投影片同教科書受著作權保護，僅供教師作為授課用途。學校教師得使用投影片，於授課期間，搭配用書進行教學；由於投影片極易重製、列印、出版，為避免第三人（含學生）因不知情而發生侵權行為，請支持與協助不將教學投影片以任何型式移轉予第三人使用。如有特殊需求，請向出版社申請授權協議。. Chapter 11 MySQL 基本操作環境. 接下來幾章介紹有關如何建立資料庫、在資料庫中查詢與異動資料，會有在 MySQL 的指令操作模式，也有用 phpMyAdmin 的 Web 介面操作方式。 本章將只針對 MySQL 的基本環境使用和資料型態等做介紹。. 開始. 離開.
E N D
＊教科書投影片同教科書受著作權保護，僅供教師作為授課用途。學校教師得使用投影片，於授課期間，搭配用書進行教學；由於投影片極易重製、列印、出版，為避免第三人（含學生）因不知情而發生侵權行為，請支持與協助不將教學投影片以任何型式移轉予第三人使用。如有特殊需求，請向出版社申請授權協議。＊教科書投影片同教科書受著作權保護，僅供教師作為授課用途。學校教師得使用投影片，於授課期間，搭配用書進行教學；由於投影片極易重製、列印、出版，為避免第三人（含學生）因不知情而發生侵權行為，請支持與協助不將教學投影片以任何型式移轉予第三人使用。如有特殊需求，請向出版社申請授權協議。 Chapter 11MySQL基本操作環境 接下來幾章介紹有關如何建立資料庫、在資料庫中查詢與異動資料，會有在 MySQL 的指令操作模式，也有用 phpMyAdmin 的 Web介面操作方式。 本章將只針對 MySQL 的基本環境使用和資料型態等做介紹。 開始 離開
目錄 11-1 MYSQL 操作環境介紹 11-2 建立表格 11-3 MYSQL 資料型態 11-4 資料處理相關的 SQL 指令介紹 離開 回章首 上一頁 下一頁
11-1 MySQL 操作環境介紹 接下來幾章介紹有關如何建立資料庫、在資料庫中查詢與異動資料，會有在 MySQL 的指令操作模式，也有用 phpMyAdmin 的 Web介面操作方式。 至於資料庫的基本概念，譬如：關聯式資料庫的觀念、何謂 SQL、正規化的步驟…等等，這些在第二章「關聯式資料庫與 SQL概論」已介紹過。 本章將只針對 MySQL 的基本環境使用和資料型態等做介紹。 下一節 上一頁 下一頁 回目錄
11-1 MySQL 操作環境介紹 從 Windows左下角的「開始」功能表進入，執行「AppServ」下的「MySQL Command Line Client」來進入 MySQL。 若在安裝時有設定 MySQL 管理者（root） 的密碼，那麼要輸入正確的管理者密碼才能成功進入 MySQL ： 輸入 MySQL管理者的密碼 下一節 上一頁 下一頁 回目錄
11-1 MySQL 操作環境介紹 當成功進入 MySQL 之後，它的命令列會變成以 “mysql>”為開頭。每下完一個 MySQL 指令後要加分號「;」再按 Enter 鍵才會執行該指令。 如果要重覆執行之前所下過的指令，或想把之前執行過的指令叫出來修改再執行，可以按上下鍵來瀏覽前後一個已下過的指令。若要離開 MySQL 的環境，只要下“exit; ”指令即可。 下一節 上一頁 下一頁 回目錄
11-1 MySQL 操作環境介紹 • 幾個在 MySQL 比較有機會用到的指令： • 顯示目前已有的資料庫- show databases • show databases 指令可以顯示目前伺服器上已有的資料庫名稱。 下一節 上一頁 下一頁 回目錄
11-1 MySQL 操作環境介紹 • 建立資料庫- create database [databasename] • create database 指令可以建立新的資料庫，後面加建立的新資料庫名稱即可。 重新show database, 可看到新建好的 “school”資料庫 下一節 上一頁 下一頁 回目錄
11-1 MySQL 操作環境介紹 • 顯示資料庫已有的表格- show tables • 在用 use 指令切換資料庫之後，可以用show tables 來查詢目前所在的資料庫中有哪些表格。 記得先用 use切換資料庫 下一節 上一頁 下一頁 回目錄
11-1 MySQL 操作環境介紹 • 建立表格- create table • 建立表格的create table 指令格式如下： • create table [table name] • (field1 type1, field2 type2, …..); 重新show tables, 可看到新建好的 “stdmaster”表格 下一節 上一頁 下一頁 回目錄
11-1 MySQL 操作環境介紹 • 顯示表格的欄位定義- describe [table name] • describe 指令後加上表格名稱，可以顯示該表格的欄位名稱和資料型態…等有關表格結構（Structure）的定義。 下一節 上一頁 下一頁 回目錄
11-1 MySQL 操作環境介紹 • 刪除表格- drop table [table name] • drop table 指令可以將指定的表格，連同表格內的所有資料記錄一併刪除。在執行時也不會有警告訊息，要小心使用。 重新show tables, 已看不到 “stdmaster”表格了 下一節 上一頁 下一頁 回目錄
11-1 MySQL 操作環境介紹 • 刪除資料庫- drop database [database name]; • drop database 指令會將目前存在的資料庫整個刪除，後面加欲刪除的資料庫名稱即可。 • 要特別注意的是，要是資料庫裡已經有些表格了，drop database 就會自動也將資料庫裡所有的表格，連同表格內的資料一併刪除。在執行時不會有警告訊息，使用上要小心。 • 切換（Switch）目前使用的資料庫- use [db name] • 所謂切換目前使用的資料庫，就是要將某個資料庫視為目前所有指令（如建立、刪除表格與顯示資料庫已有的表格）所針對的資料庫對象。 • 譬如：建立表格之前，要先切換作用中的資料庫，這樣稍後所建立的表格才會建立在想要放的資料庫裡。 下一節 上一頁 下一頁 回目錄
11-2 建立表格 建立表格的指令是Create Table，這一節將介紹以 Create Table 指令建立新表格的語法。 下一節 上一頁 下一頁 回目錄
11-2-1 建立表格 - Create Table • 完整的 Create Table 指令語法有許多參數可選用，這裡僅列出最常用的部份供讀者參考。 • Create Table <table name> • [ <field_definition,...>] • [select_statement]; • 其中 field_definition: • fieldname type • [NOT NULL | NULL] • [DEFAULT default_value] • [AUTO_INCREMENT [PRIMARY KEY]] • [ZEROFILL] 下一節 上一頁 下一頁 回目錄
11-2-1 建立表格 - Create Table 說明：field_definition 即欄位定義，它包含了：欄位名稱、欄位型態、和它的欄位設定子。MySQL有許多欄位設定子可使用，在下一小節再介紹。 在建立表格時搭配 select_statement 是代表在建立表格的同時，將別的表格的資料直接複製過來。 若使用 select_statement 可以省略 field_definition，建立出來的表格結構，即欄位定義，就會和資料來源的表格一樣。使用 select_statement 仍然可以自行定義新表格的結構，注意若欄位長度較來源資料短，資料會有被截斷問題。 範例： create table pricelist_new (prodno varchar(5), vendorno varchar(5), price smallint) select * from pricelist; 下一節 上一頁 下一頁 回目錄
11-2-1 建立表格 - Create Table 以下是複製資料來源的表格pricelist 目前的表格結構和內容： 這是複製的資料來源 下一節 上一頁 下一頁 回目錄
11-2-1 建立表格 - Create Table 經過執行指令建立新的表格 pricelist_new，並將表格pricelist的資料複製給它之後，新的表格 pricelist_new 結構因為文字的長度只有varchar(5)，而非 pricelist 表格中定義的 varchar(10)，所以文字的後半段被截掉了。 複製過來的資料內容被截掉 下一節 上一頁 下一頁 回目錄
11-2-1 建立表格 - Create Table 但若在Create Table 時，不指定欄位定義，就沒有上述問題。因為建立的新表格結構會和來源表格一模一樣，資料也一樣，當然資料也不會有被截掉的問題。使用方式視情況而定。 複製過來的資料和來源一模一樣 下一節 上一頁 下一頁 回目錄
11-2-2 欄位設定子 • 在定義表格的欄位時，除了一定要定義每個欄位的名稱和資料型態，My SQL還提供了幾個資料欄位設定子（Column Modifier）。 • 它可以用來設定資料欄位的屬性，包括了預設值（Default） 、自動增量（Auto Increment）、無號數（Unsigned）、主鍵值（Primary Key） 等。 • 空值/非空值（Null/Not Null） • Null 是允許該欄位的值是空值，而Not Null 是指該欄位不允許接受空值。沒有特別設定的話，預設狀態是 Null。 下一節 上一頁 下一頁 回目錄
11-2-2 欄位設定子 • 欄位預設值（DEFAULT） • 若某個欄位有用“DEFAULT”設預設值，那麼在新增（Insert） 記錄時，若沒有針對該欄位設定要給予的值，那麼該欄位就會自動以預設值做為此次新增記錄時的欄位值。 自動設為預設值 ‘F’ 下一節 上一頁 下一頁 回目錄
11-2-2 欄位設定子 • 自動增量設定（AUTO_INCREMENT） • “AUTO_INCREMENT”自動增量設定子只可用在整數型態欄位。欄位如果加了自動增量設定，則每增加一筆新紀錄，不需要為該欄位設定值，它的值就會自動增加1。 • 當你插入一筆資料時，其中的設定為“AUTO_INCREMENT”欄位值必須存入NULL值、0 或空白值。也可指定存入一個數值，但是假如該數字已經存在的話，則會產生錯誤。 • 假如這次所要插入的記錄，其欄位值將成為目前資料表格中該欄位的最大值，則再一次增加的紀錄，其該欄位值將會以此次加入的值加 1。 下一節 上一頁 下一頁 回目錄
11-2-2 欄位設定子 • 當新建一個新的資料表，並且指定其中一個欄位為“AUTO_INCREMENT”時，可以在插入第一筆資料時即指定一個值做為“AUTO_INCREMENT”的初始值，之後每次新增記錄就可以自初始值開始遞增。當一個欄位被設為“AUTO_INCREMENT”，且它是唯一個“自動增量欄位，MySQL 會要求將它設為 Primary Key。 • 例如： • Create Table custmast • (custno int not null auto_increment primary key, • birthdate date); 每次新增自動遞增1 下一節 上一頁 下一頁 回目錄
11-2-2 欄位設定子 每次新增自動遞增1 下一節 上一頁 下一頁 回目錄
11-2-2 欄位設定子 • 自動補齊零（ZEROFILL） • “ZEROFILL”適用於設在所有數值型態的欄位。如果將一個數值型態欄位設定為“ZEROFILL”，則會在數值之前補齊 “0”。例如，在插入記錄到資料表格時，將 78 的值存入到一個宣告為INT “ZEROFILL”的欄位時，此時存放的資料為 0000000078。 • “ZEROFILL”這個設定子對於需要格式化輸出的數值資料有幫助，譬如，會員編號從0000000001、0000000002、0000000003、….開始編號下去。前面不足位數的部份會用零補齊，這樣相同的位數會自動對齊好，在瀏覽資料時也較清楚。 • 例如： • Create Table custmast • (custno int zerofill, address varchar(30)); 下一節 上一頁 下一頁 回目錄
11-2-2 欄位設定子 • 無號數（UNSIGNED） • 無號數（UNSIGNED） 這個欄位設定僅適用於整數型態欄位。 一般的數字都有正負號，所以在二進位表示下，最高的位元就被拿來當作符號位元，所以才允許有負數。所謂無號數就是捨棄符號位元，改拿來表現數字，因此它只表示正數。因為多了一個位元可使用，它可表示的正數範圍是有號數的兩倍。這在某些數值只需要正數而不需要負數的情形下可以更加有效利用儲存空間。 • Create table sales • (prodno varchar(10), • pirce int unsigned, • amount int unsigned); 下一節 上一頁 下一頁 回目錄
11-3 MySQL 資料型態 • 資料庫的資料型別（Data Type）是指一個資料欄位內所存放資料之型態，它關係著資料所佔磁碟空間的大小與查詢資料庫時的執行效率。 • 而這節要介紹 MySQL 資料庫欄位可儲存的資料型態，這樣才能知道如何在建立資料庫表格時，為表格的每個欄位選擇適合它的資料型態。MySQL 資料庫的欄位資料型態，大致可分為五種： • 數字型態 • 日期與時間型態 • 字串與字元型態 • 列舉型態 • 集合型態 下一節 上一頁 下一頁 回目錄
11-3 MySQL 資料型態 • 將這幾個資料型態的儲存空間需求及其值的大小範圍整理如下： • 數字型態 • 各種數值型態所用之記憶體空間、數值範圍如下所示，M 表示數字的位數，D 表示小數點以下的位數： 下一節 上一頁 下一頁 回目錄
11-3 MySQL 資料型態 • 日期與時間型態 • 日期與時間資料型態，包含下列型態，其中 y 為年份，m為月份，d 為日期，h 表小時，m 表分鐘，s 為秒： 下一節 上一頁 下一頁 回目錄
11-3 MySQL 資料型態 • 字串與字元型態 • 字串與字元型態，包含下列資料型態，其中 N 表字串的長度： 下一節 上一頁 下一頁 回目錄
11-3 MySQL 資料型態 • 列舉型態 • 集合型態 下一節 上一頁 下一頁 回目錄
11-4 資料處理相關的 SQL 指令介紹 第二章已介紹過SQL 語法的發展歷史，以及 SQL 指令可以分成DDL（資料定義語言）、DML（資料處理語言）、DCL（資料控制語言）三大部份。 這一節主要介紹其中DML 中的資料查詢指令- Select 以及有關資料異動的指令 - Insert/Delete/Update。對於不曾接觸過SQL 指令的讀者， 這一節的內容對您來說是非常的重要，務必充分瞭解之後再往下閱讀。基本的SQL 指令其實相當簡單易學，接下來的介紹都會以例子說明，讀者可以很快理解。 上一節 上一頁 下一頁 回目錄
11-4-1 資料查詢 – Select Select 是所有SQL 指令中最常使用到的指令，它是向資料庫的一個或多個表格所下的資料查詢（Query）指令。透過Select 指令可以篩選出所需要的部份資料記錄（Records），還可以指定查詢資料結果顯示的次序。也可利用表格之間的關聯將不同表格的資料串連 （Join）在一起。 雖然在不同資料庫產品中，SQL 指令語法會有些差別，但在本書介紹的基礎指令上並不會有什麼差別。在 Access, SQL Server, Oracle, 甚至在下一章要介紹的 phpMyAdmin，都提供可以讓使用者直接下SQL 指令的地方，基本的語法並無差異，對各種資料庫都可適用。 接下來所用的範例SQL指令，讀者也可以在其他資料庫軟體上使用，只要資料一樣，顯示出來的結果也會一樣，只是不同軟體的畫面不相同而已。這一節將以MySQL 的操作畫面作說明。 上一節 上一頁 下一頁 回目錄
11-4-1 資料查詢 – Select • Select * from [table name] • 說明：Select * 代表要將表格內所有的欄位都顯示出來。“*”代表所有的欄位名稱。 • 範例：select * from stdscore; • 它會將“stdscore”表格的所有欄位全部顯示出來。 上一節 上一頁 下一頁 回目錄
11-4-1 資料查詢 – Select • Select field1[, field2, … ] from <table name>; • 說明：只將Select 後所列的欄位按照指令裡的次序顯示出來。 • 範例：select name, coursename, score, teacher from stdscore; • 它會將“stdscore”表格內的 name、coursename、score、teacher 欄位次序顯示出來。 上一節 上一頁 下一頁 回目錄
11-4-1 資料查詢 – Select • Select distinct field1[, field2, … ] from <table name>; • 說明：distinct（不同的）的作用是在將欄位所有相同值各取唯一的一筆出來。若 distinct 是擺在很多欄位之前，意思是將表格中Select distinct 後所列的幾個欄位所有不同值的排序組合都顯示出來。 • 範例：select distinct courseno, coursename, teacher from stdscore; • 它會將“stdscore”表格內所有資料 name, coursename, score, teacher 欄位值的不同組合都顯示出來。 上一節 上一頁 下一頁 回目錄
11-4-1 資料查詢 – Select • Select field1[, field2, … ] from <table name>; • where <query_condition> • 說明：query_condition 指定了要篩選滿足哪些邏輯條件成立的記錄（Records）。常見的包括等於（=）、不等於（<>）、大於（>）、大於或等於（>=）、小於（<）、小於或等於（<=）…等的比較條件。 • 也可以用“IN”來指定某個欄位的值是落在哪些範圍以內才要篩選出來。或用“BETWEEN…AND…”來指定某欄位的值是要落在所指定的最小與最大值範圍區間內。“LIKE”和萬用字元符號“%”搭配使用則是用來做文字型態資料的篩選。 • 最後，可以用“AND”和“OR”條件將多個邏輯條件一起寫在同一個查詢條件裡，達到多重篩選的目的。而“AND”的運算次序高於“OR”。 上一節 上一頁 下一頁 回目錄
11-4-1 資料查詢 – Select 若 Select 後不是用“*”符號，而是只列出所要的部份欄位出來。其實就和第2-5-2小節「關聯運算」中所介紹的投影運算（Project）的觀念很類似，作用是在表格中只篩選出所需要的欄位（Field）資料出來。 至於Select 加上了 where 條件，則是和第 2-5-2小節「關聯運算」中所介紹的選取運算 （Select） 的觀念很類似，作用是在表格中只篩選出符合所要的資料記錄 （Record） 出來。 上一節 上一頁 下一頁 回目錄
11-4-1 資料查詢 – Select • 範例：select stdno, name, score from stdscore where courseno=’MS1038’ ; • 它會將課程代號是 ’ MS1038’ 的學生學號、姓名和分數顯示出來。 上一節 上一頁 下一頁 回目錄
11-4-1 資料查詢 – Select 範例：select stdno, name, courseno, score from stdscore where coursename like ’St%’ and score>60; 它會將課程代號是 ’St’ 開頭，而且分數超過 60 分的學生學號、姓名、課程代號和分數顯示出來。 上一節 上一頁 下一頁 回目錄
11-4-1 資料查詢 – Select 範例：select stdno, name, courseno, score from stdscore where name in ('Tom','Linda') and score between 72 and 98; 它將會顯示學生姓名是“'Tom”或“Linda”，而且分數介於 72（包含）到 98（包含）之間的學生成績資料。 上一節 上一頁 下一頁 回目錄
11-4-1 資料查詢 – Select • Select field1[, field2, … ] from <table name>; • order by sort_field1 [desc] [,sort_field2 [desc], … ] • 說明：order by 是指將查詢出來的資料記錄依照order by 後的排序欄位由小至大排序。若先後列出多個排序欄位，表示先依照第一個排序欄位由小至大排序，若第一個排序欄位的值相同，則再視第二個排序欄位的值由小至大排序，依此類推。 • 若在排序欄位名稱後加上“desc”，則表示該欄位的排序是由大至小（Descending）。每個排序欄位的排序方式可以不相同，可視需要個別指定每個排序欄位的排序方式，若沒特別指定則會用預設方式：由小至大排序。 • order by 可以和 where 條件一起使用，代表要先用where 篩選出符合條件的資料記錄，再依order by 的次序顯示篩選後的結果。但要注意在SQL 指令中要先寫where 子句再用 order by 子句。 上一節 上一頁 下一頁 回目錄
11-4-1 資料查詢 – Select • 範例：select stdno, name, courseno, score from stdscore where teacher = 'Anderson' order by score desc, name; • 它會將授課講師是“James Lee”的學生成績資料顯示出來，顯示時要先依照分數由高至低排序，若分數相同再依照學生姓名由小至大排序。 上一節 上一頁 下一頁 回目錄
11-4-1 資料查詢 – Select • Select [group_field1 [ ,group_field2, … ] ], count（*） • from <table name> • group by group_field1 [ ,group_field2, … ] ] • [ having having_condition ]; • 說明：group by 的作用是將整個表格資料中，被列為彙總欄位的一個或多個欄位的所有值的排列組合情況加以分組 （Grouping）。count 是 SQL 可用的函數之一，count(*) 是該分組的資料筆數。 • 若Select 指令有用到group by子句，那麼寫在Select 後面的欄位應是這些分組欄位之中的部份欄位。 • Select 後也可用 count 或接下來將介紹的avg 或sum 等彙總函數的欄位。 上一節 上一頁 下一頁 回目錄
11-4-1 資料查詢 – Select • 至於having group_condition 則是限定只有分組出來的結果有滿足分組條件的，才顯示該分組的資料。 • having_condition 中也必須使用彙總函數的欄位，譬如：count(*)>1，或接下來介紹的其它彙總函數來表示， • 譬如：avg(score)>80。若省略 having 子句，則所有分組結果都會顯示出來。 • group by ... having …可以和order by 和 where 子句一起併用，但在Select 指令中的次序是先where，接下來是 group by ... having …，最後才是 order by。 上一節 上一頁 下一頁 回目錄
11-4-1 資料查詢 – Select 範例：select courseno, coursename, teacher, count(*) from stdscore where stdno>='9643001' group by courseno, coursename, teacher having count(*)<2 order by courseno; 它會將學生代號大於或等於“9643001”的學生成績中，課程代號、課程名稱、授課講師的所有不同排序組合顯示出來，也用 count(*) 來顯示分組筆數。但最後只將其中分組筆數小於2 筆的分組資料才顯示出來。 上一節 上一頁 下一頁 回目錄
11-4-1 資料查詢 – Select • Select sum(field)/avg(field)/min(field)/max(field) from <table name> • group by group_field1 [ ,group_field2, … ] • [ having having_condition ]; • 說明：group by 除了可搭配前面所提的count函數，也可使用sum、avg、min、max 這些彙總函數，用來取得各分組的加總、平均、最小值、最大值。 • having_condition 也必須是用彙總函數的欄位，譬如：count（*）>1、avg（score）>80或min（score）>80…等。若省略having 子句，則所有分組結果都會顯示出來。 上一節 上一頁 下一頁 回目錄
11-4-1 資料查詢 – Select 範例：select courseno, coursename, teacher, count(*) from stdscore where stdno<='9643020' group by courseno, coursename, teacher having count(*)>=1 order by courseno; 上一節 上一頁 下一頁 回目錄
11-4-1 資料查詢 – Select • Select table1.field1 [, table1.field2…], table2.field1 [,table2.field2…], • from <table1>, <table2> where <table1>.foreignkey = <table2>.keyfield; • 說明：這是兩個表格的合併，透過第一個表格的外來鍵和第二個表格的主鍵來做連結。 • 若表格名稱太長，可以為表格名稱設別名 （Alias），語法如下： • Select alias1.field1 [,alias1.field2…], alias2.field1 [,alias2.field2…], • from table1 alias1, table2 alias2 • where alias1.foreignkey = alias2.keyfield; 上一節 上一頁 下一頁 回目錄
11-4-1 資料查詢 – Select • 範例：select p.*, v.vendorname from pricelist p, vendormaster v where p.vendorno=v.vendorno; • 假設有產品價格主檔表格“pricelist”和供應商主檔資料表格“vendormaster”的資料內容如下： 上一節 上一頁 下一頁 回目錄
11-4-1 資料查詢 – Select • 將這兩個表格資料連結後，結果如下： • 另外，也可以用inner join 和上面的指令達成相同的表格連結效果。 用where條件連結兩個表格 上一節 上一頁 下一頁 回目錄
