Transcript MySQL

ความรู้ เบือ้ งต้ นเกีย่ วกับ
MySQL
MySQL
อ. นัฐพงศ์ ส่ งเนียม
http://www.siam2dev.com
ม. ราชภัฎพระนคร
ฐานข้ อมูลคืออะไร?
ฐานข้ อมูล(Database) คือ กลุ่มของข้ อมูลแบบต่ างๆที่รวบรวมไว้
และนามาจัดเรียงให้ เป็ นระบบเพื่อใช้ ตามวัตถุประสงค์ ท่ตี ้ องการ
เช่ น สมุดรายชื่อผู้ใช้ โทรศัพท์ ทะเบียนรายชื่อนักศึกษา เป็ นต้ น
คุณสมบัตฐิ านข้ อมูล
 การนาข้ อมูลทีส่ ั มพันธ์ กนั ทั้งหมดมาไว้ รวมกัน
 ข้ อมูลทีม่ ีลกั ษณะเป็ นมาตรฐาน
 มีชุดเดียว
 ใช้ ได้ ท้งั หน่ วยงาน
 มีระบบตรวจสอบป้องกัน
 เป็ นอิสระจากโปรแกรม
 มีภาษาสอบถาม (QUERY LANGUAGE)
 ต้ องมีระบบจัดการฐานข้ อมูล (DATABASE
MANAGEMENT SYSTEM)
ข้ อดีของฐานข้ อมูล
ลดความซา้ ซ้ อนของข้ อมูล
ใช้ ข้อมูลร่ วมกันได้
มีระบบจัดการความปลอดภัยของข้ อมูล
หลีกเลี่ยงความขัดแย้ งของข้ อมูล
ระบบจัดการฐานข้ อมูล(DBMS)
นิยมเรียกว่ า ดีบีเอ็มเอส(DBMS)
คือชุ ดของโปรแกรมคอมพิวเตอร์ ซึ่งทาหน้ าทีส่ ร้ าง ดูแลรักษา และใช้ งาน
ส่ วนต่ าง ๆของฐานข้ อมูล
User/Programmers
DATABASE
SYSTEM
Application Programs/Queries
DBMS
SOFTWARE
SOFTWARE TO PROCESS
QUERIES/PROGRAMS
SOFTWARE TO ACCESS
DTORED DATA
Store Database
Definition
(META-DATA)
Stored DataBase
ระบบจัดการฐานข้ อมูล(DBMS)
ต้ องมีการใช้ งานทรัพยากรของคอมพิวเตอร์ อย่ างมีประสิ ทธิภาพ
ทาหน้ าทีต่ ิดต่ อกับตัวจัดการระบบไฟล์
การควบคุมการคงสภาพ
การควบคุมระบบความปลอดภัย
การสร้ างระบบสารองและการฟื้ นสภาพ
การควบคุมภาวะพร้ อมกัน
แบบจาลองข้ อมูล(Data Modelling)
มี 3 แบบ
ฐานข้ อมูลแบบลาดับขั้น (Hierarchical Database Model)
ฐานข้ อมูลแบบข่ ายงาน (Network Database Model)
ฐานข้ อมูลแบบตาราง (Relational Database Model)
แบบลาดับขั้น (Hierarchyical Database)
เป็ นการแสดงความสั มพันธ์ ของเอนติตใี้ นแบบลาดับขั้น ซึ่งเริ่มจาก
เอนติตหี้ ลักแล้วย่ อยลงไปเป็ นเอนติตีต้ ่ างๆตามลาดับ เป็ น
ความสั มพันธ์ แบบพ่อกับลูก
คณะ
1
N
N
N
อาจารย์
1
นิสิต
N
N
วิชา
ห้อง lab
แบบข่ ายงาน (Network Database)
เป็ นการแสดงความสัมพันธ์ ของเอนติตีใ้ นแบบที่เอนติตีม้ ี
ความสัมพันธ์ กับเอนติตีอ้ ื่นๆได้ หลายเอนติตี้
โครงการ
คณะ
1
N
N
อาจารย์
1
N
N
นิสิต
N
ห้อง lab
ฐานข้ อมูลเชิงสัมพันธ์ (Relational Database)
การจัดเก็บข้ อมูลจะอยู่ในรูปของตาราง มีคอลัมน์ และแถว ในแต่
ละตารางจะเก็บข้ อมูลเกี่ยวกับเรื่องใดเรื่องหนึ่ง
โดยข้ อมูลใน
ตารางหนึ่งอาจมีความสัมพันธ์ กับตารางอื่นๆได้
เช่ น ตารางที่เก็บข้ อมูลการขาย กับตารางที่เก็บข้ อมูลลูกค้ า และ
ตารางที่เก็บข้ อมูลสินค้ า เมื่อต้ องการพิมพ์ ใบสั่งซือ้ จะเห็นว่ ามี
ข้ อมูลจากทัง้ สามตาราง คือ มีรายการขาย ชื่อ ที่อยู่ลูกค้ า ชื่อ
ราคาสินค้ า ปริมาณการสั่งซือ้ ทัง้ นีต้ ้ องมีการกาหนดความสัมพันธ์
ระหว่ างตารางต่ างๆไว้ ก่อน
ฐานข้ อมูลเชิงสัมพันธ์
เก็บข้ อมูลในรู ปตาราง กลุ่มของข้ อมูลทีจ่ ัดมีความสั มพันธ์ กนั ในรู ปแนวนอน
และแนวตั้ง ซึ่งเรียกข้ อมูลทีเ่ รียงกันในรู ปแนวนอนว่ า เรคอร์ ด(record) และ
จะเรียกข้ อมูลทีว่ างในแนวตั้งว่ า เขตข้ อมูล(Field)
ระบบจัดการฐานข้ อมูลเชิงสัมพันธ์
หรือที่เรียกว่ า RDBMS(Relational DataBase System) เป็ น
เครื่องมือในการจัดเก็บข้ อมูล เพื่อให้ ค้นหาข้ อมูลแบบต่ างๆ
ได้ ตามต้ องการ
นอกจากนีย้ งั ช่ วยในการดูแลรักษาข้ อมูล
เช่ นการแก้ ไข เพิ่ม ลบข้ อมูล ตลอดจนการออกรายงานได้
ง่ ายขึน้
ความสามารถของระบบจัดการฐานข้ อมูลมี 3
ประการคือ
การกาหนดนิยามข้ อมูล(Data Definition)
การจัดการกับข้ อมูล(Data Manipulation)
การควบคุมข้ อมูล(Data Control)
การกาหนดนิยามข้ อมูล
ได้ แก่ การกาหนดโครงสร้ างของข้ อมูลที่เก็บในฐานข้ อมูล การ
กาหนดชนิดของข้ อมูลและลักษณะการจัดเก็บข้ อมูล เช่ น
สามารถจะกาหนดกฎในการตรวจสอบข้ อมูล
สามารถกาหนดความสัมพันธ์ (Relation)ระหว่ างกลุ่มของข้ อมูลหรื อที่
เรี ยกว่ าตาราง(Table) เพื่อให้ สามารถตรวจสอบว่ าข้ อมูลมีความถูกต้ อง
ตรงกันอยู่เสมอหรื อไม่
CREATE, ALTER, DROP
การจัดการกับข้ อมูล
ได้ แก่ การทางานกับข้ อมูล เช่ นการเรียกค้ นข้ อมูล(Select) การ
เพิ่ม (Insert), ปรับปรุ ง(Update), และลบข้ อมูล(Delete) ฟิ ลด์ ใด
ฟิ ลด์ หนึ่ง หรือพร้ อมกันหลายฟิ ลด์ หลายเรคอร์ ดได้
การควบคุมข้ อมูล
ได้ แก่ การรักษาความปลอดภัยของข้ อมูล เพื่อให้ แน่ ใจว่ าผู้ท่ จี ะ
อ่ านหรือแก้ ไขข้ อมูลได้ นัน้
จะต้ องเป็ นผู้ท่ ไี ด้ รับอนุญาตเท่ านัน้
และสามารถป้องกันไม่ ให้ มีการแก้ ไขข้ อมูลเดียวกันพร้ อมๆกันได้
GRANT, REVOKE
ศัพท์ เบือ้ งต้ นของฐานข้ อมูล
Table
Field
Record
เอนติต(ี ้ Entity)และแอตทริบวิ ต์ (Attribute)
เอนติตี ้ หมายถึงบุคคล สิ่งของ เหตุการณ์ หรืออะไรก็ได้ ท่ เี ราสนใจ
ต้ องการจะเก็บข้ อมูล
แอตทริบวิ ต์ คือคุณลักษณะหรือรายละเอียดของแต่ ละเอนติตี ้
โดยแต่ ละเอนติตจี ้ ะมีได้ หลายแอตทริบวิ ต
ความสัมพันธ์ (Relationship)
เป็ นสิ่งที่เชื่อมโยงระหว่ างข้ อมูลในตารางต่ างๆเข้ าด้ วยกัน แบ่ งได้
เป็ น 3 ลักษณะ ดังนี ้
แบบหนึ่งต่ อกลุ่ม(One-to-Many)
แบบหนึ่งต่ อหนึ่ง(One-to-One)
แบบกลุ่มต่ อกลุ่ม(Many –to-Many)
ความสั มพันธ์ (Relation)
หมายถึงสิ่ งทีเ่ กีย่ วข้ องกันของสิ่ งของตั้งแต่ สองสิ่ ง
คนกับบ้ าน
ความสั มพันธทาง Relational Database จะหมายถึง ตาราง 2
ตารางมีความสั มพันธ์ กนั ซึ่งแบ่ งเป็ น 3 แบบ ดังนี้
One-To-One เช่ น ความสั มพันธ์ ระหว่ างทะเบียนรถยนต์ กบั รถยนต์
รถยนต์
มี
ทะเบียน
ความสั มพันธ์ (Relation)
One-To-Many เช่ นอาจารย์ สอนนิสิต
อาจารย์
สอน
นิสิต
Many-To-Many เช่ นนิสิต 1 คนเลือกเรียนหลายวิชาแต่ ละวิชามีผู้
นิสิตหลายคนลงทะเบียน, คาสั่ งซื้อกับสิ นค้ า
นิสิต
เลือกเรี ยน
วิชา
MySQL
เป็ น SQL Database Server ที่นิยมใช้ งานกันบนระบบปฏิบัตกิ าร
ลีนุกซ์
MySQL นีไ้ ด้ ถูกพัฒนาโดย T.c.X. DataKonsultAB. โดยพัฒนาจน
ได้ มาตราฐาน ISO/ANSI SQL 1992
ทางาน แบบไคลเอนต์ / เซิร์ฟเวอร์ (client / server)
จุดมุ่งหมายหลักของ MySQL คือความเร็ว ความคงทน และง่ าย
ต่ อการใช้
การติดต่ อกับ MySQL
ผ่ านโปรแกรมไคลเอนต์ ท่ ชี ่ ือ mysql ซึ่งจะทาหน้ าที่ในการติดต่ อ
กับเซิร์ฟเวอร์ โดยจะต้ องระบุการเชื่อมโยงผ่ านพารามิเตอร์ ไป
ยังเซิร์ฟเวอร์ ท่ ตี ้ องการติดต่ อ โดยผ่ านจากชื่อผู้ใช้ และรหัสผ่ าน
รูปแบบการใช้ งานคาสั่ง เป็ นดังนี ้
$ mysql [- h host_name] [- u user_name] [- pyour_pass]
หรืออาจจะใช้ รูปแบบอื่นที่สามารถใช้ แทนของ '- h', '- u' และ
'- p' options คือ
'- - host = host_name', '- - user = user_name' และ '- - password =
your_pass'
การติดต่ อกับ MySQL
MySQL ใช้ ค่า default สาหรับการติดต่ อกับพารามิเตอร์ ท่ ีหายไป
จาก command line default host_name คือlocalhost และ default ชื่อ
ผู้ใช้ เป็ นชื่อที่ใช้ ในการ login บน Unix (จะไม่ มี password เช่ น ถ้ า
ไม่ มี '- p' option)
ดังนัน้ สาหรับตัวอย่ าง user ของ 'test' จะใช้ คาสั่งดังนี ้
$ mysql -h localhost -u test
$ mysql -h localhost
$ mysql -u test
การเพิ่มชื่อผู้ใช้ เข้ าไปในระบบฐานข้ อมูล MySQL
เริ่มแรกเมื่อติดตัง้ MySQL เสร็จจะมีช่ ือผู้ใช้ root อยู่ในระบบ
แล้ ว โดยผู้ใช้ จาเป็ นต้ องตัง้ รหัสผ่ านก่ อน ทัง้ นีเ้ พื่อความ
ปลอดภัยของระบบ ซึ่งจะสามารถใช้ คาสั่งในการตัง้ รหัสผ่ าน
หรือเปลี่ยนรหัสผ่ านดังนี ้
$ mysqladmin -u root password student
จากคาสั่งนีจ้ ะตัง้ รหัสผ่ านของผู้ใช้ root เป็ น student
ปัญหาทีเ่ กิด
สาหรับการเพิ่มชื่อผู้ใช้ เข้ าไปในระบบ (Add user ) นัน้ ใน
ระบบของ MySQL นัน้ จะเก็บข้ อมูลที่เกี่ยวกับชื่อผู้ใช้ สิทธิ
การใช้ งานต่ างๆ ของ MySQL ไว้ ในฐานข้ อมูลชื่อ mysql โดย
จะเก็บข้ อมูลของผู้ใช้ ไว้ ในตารางข้ อมูลชื่อ user ซึ่ง
ตารางข้ อมูลนีจ้ ะมีฟิลด์ ข้อมูลดังนี ้
* Table name *
‘ user ’
* Scope fields *
‘ host ’
‘ user ’
‘ password ’
* Privilege fields ‘
‘ select_priv ’
‘ insert_priv ’
‘ update_priv ’
‘ delete_priv ’
‘ index_priv ’
‘ alter_priv ’
‘ create_priv ’
‘ drop_priv ’
‘ grant_priv ’
‘reload_priv ’
‘ shutdown_priv ’
‘ process_priv ’
‘ file_priv ’
คาสั่ งเพิม่ ผู้ใช้
$ mysql –u root -p
Password :
mysql> INSERT INTO user
VALUES('localhost','test',PASSWORD('student'),
'Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y');
mysql> INSERT INTO user
VALUES('%','test',PASSWORD('password'),
'Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y');
mysql> FLUSH PRIVILEGES;
การติดต่ อกับ mysql
$ mysql –u root –ppassword php_book
Welcome to MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 1 to server version: 3.22.16agamma
Type ‘help’ for help
การสร้ าง ฐานข้ อมูลและการเรียกใช้
ใช้ คาสั่ง
msql > create database dataName;
ตัวอย่ าง
msql > create database php_book;
การเปิ ดใช้ งานฐานข้ อมูล
mysql > use ฐานข้ อมูล;
ตัวอย่ าง
mysql > use php_book;
The SQL Create Table Statement
คาสั่ง create table จะเป็ นคาสั่งที่ใช้ สาหรับตารางในระบบ
ฐานข้ อมูล ซึ่งจะต้ องใส่ ช่ ือตาราง ชื่อคอลัมน์ (field) ชนิดของ
คอลัมน์ โดยสามารถใส่ ได้ หลายคอลัมน์
รูปแบบคาสั่ง
Create table { table_name } (
{ fieldl_name } { field1_data_type }
, { field2_name } { field2_data_type }
);
ตัวอย่ าง
mysql > create table email_messages (
-> key_email_messages INTERGER
-> ,date_created
VARCHAR(19)
->
->
->
->
->
->
->
,date_updated
,date_email
,addr_from
,addr_reply_to
,subject
,message
);
VARCHAR(19)
VARCHAR(19)
VARCHAR(100)
VARCHAR(100)
VARCHAR(100)
MEDIUMTEXT
การสร้ างตารางด้ วย script
สร้ างscript ของ SQL เก็บลงแฟ้มชื่อ fname.sql
ที่ linux prompt พิมคาสั่ งดังนี้
cat fname.sql | mysql database name -p
เมื่อ -p หมายถึงให้ ใส่ password
ตัวอย่ างscript ชื่อ crea.sql
CREATE TABLE
publishers
(
key_publishers INT(5) DEFAULT '0' NOT NULL
AUTO_INCREMENT
,publisher_name VARCHAR(50) NOT NULL
,PRIMARY KEY (key_publishers)
)
CREATE TABLE
authors
(
key_authors INT(5) DEFAULT '0' NOT NULL
AUTO_INCREMENT
,author_name VARCHAR(50) NOT NULL
,author_phone VARCHAR(20) NOT NULL
,PRIMARY KEY (key_authors)
)
CREATE TABLE
books
(
key_books int(5) DEFAULT '0' NOT NULL AUTO_INCREMENT
,key_authors int(5) DEFAULT '0' NOT NULL
,key_publishers int(5) DEFAULT '0' NOT NULL
,book_title varchar(50) NOT NULL
,PRIMARY KEY (key_books)
);
คาสั่ งทีใ่ ช้ คอื
cat crea.sql | mysql bookstore -p
คาสั่งแสดงโครงสร้ างตาราง
mysql > describe table_name;
ตัวอย่ าง
mysql > describe email_message;
การแสดง database & Table
การแสดง database ใช้ คาสั่ ง
show database ;
การแสดง table ใช้ คาสั่ ง
show tables ;
The SQL Alter Table Statement
คาสั่ง alter table ใช้ สาหรับเพิ่มเติม หรือเปลี่ยนแปลงคอลัมน์ ใน
ตาราง รูปแบบ มีดงั นี ้
alter table { table_name } add column { field_name } {
field_data_type };
alter table { table_name } change column {existing_field_name}
{ new_field_name } { new_field_data_type };
alter table { table_name } drop column { existing_field_name}
Using Add Column Option
แสดงการใช้ คาสั่ง alter table เพิ่มฟิ ลดชื่อ temp_field ลงในตาราง
email_message ใช้ คาสั่งดังนี ้
mysql > use php_book;
Database changed
mysql > alter table email_messages add column
-> temp_field
-> integer;
mysql > describe email_messages;
Using Change Column Option
mysql > use php_book;
Database changed
mysql > alter table email_messages change column temp_field
-> temp_field
-> varchar(25);
เรียกดูโครงสร้ าง
mysql > describe email_messages;
Using Drop Column Option
การลบคอลัมน์ ออกจากตารางสามารถทาได้ โดยใช้ คาสั่ง alter
table ดังนี ้
mysql > use php_book;
Database changed
mysql > alter table email_messages drop column
-> temp_field;
The SQL Drop Table Statement
การลบตารางออกจากฐานข้ อมูลจะง่ ายกว่ าการลบคอลัมน์ โดย
กาหนดชื่อตารางที่จะลบ โดยใช้ คาสั่งดังนี ้
mysql > use php_book;
Database changed
mysql > drop table email_messages;
The SQL Insert Statement
คาสั่ง insert ใช้ สาหรับเพิ่มเรคอร์ ดลงในตาราง มีรูปแบบคาสั่ง
ดังนี ้
insert into { table_name }
( { column_list } )
values
( { value_list } )
สตริงก์ ท่ ใี ช้ ในคาสั่ง insert จะต้ องปิ ดล้ อมด้ วยเครื่องหมาย single
quote และจานวนของค่ าใน value_list จะต้ องเท่ ากับจานวน
คอลัมน์ ใน column_list
ตัวอย่ าง
ให้ พมิ พ์ คาสั่งเพิ่มข้ อมูลดังต่ อไปนี ้
mysql > create table test (
-> a integer ,b VARCHAR(10)
-> );
mysql > insert into test
-> (a, b)
-> values
-> ( 134, ‘aaa’ );
The SQL Update Statement
คาสั่ง update เป็ นคาสั่งใช้ สาหรับเปลี่ยนแปลงสารสนเทศใน
ตาราง มีรูปแบบคาสั่งดังนี ้
update { table_name }
set { column_name } = { expression }…
where { where_clause }
คาสั่ง update ที่ใช้ กันมากคือเพิ่มฟิ ลด์ ในเรคอร์ ด
ตัวอย่ าง
mysql> create table test (a integer);
mysql> insert into test (a) values (134);
mysql> insert into test (a) values (100);
mysql> select * from test;
mysql> update test set a = a+1;
Query OK, 2 rows affected (0.00 sec)
Rows matched: 2 Changed: 2 Warnings: 0
mysql> select * from test;
ตัวอย่ าง
update employees set salary = salary * 1.15;
เราสามารถใช้ คาสั่ง update กับอนุประโยค where ได้ ดังนี ้
update employees set salaary = salary * 1.15
where last_name = ‘medinets’ ;
นอกจากนีย้ ังสามารถใช้ ร่วมกับฟั งก์ ชันอื่นๆได้ อีกเช่ น
update employees set last_name = upper(last_name);
The SQL Select Statement
คาสั่ง select จะใช้ สาหรับการแสดงคอลัมน์ (field) หรือกลุ่มของ
คอลัมน์ ท่ เี ราต้ องการดูข้อมูล รูปแบบการใช้ งานคือ
select {field_list}from {table_list}
from {table_list}
where {where_clause} <------ filters
group by {column_list} <----- aggregates
order by {column_list} <------ sorts
having {having_cluase} <------ filters after aggregation
The Field List
คาสั่ง select ที่ง่ายที่สุดคือ
SELECT * FROM Table_name ;
คาสั่งจะแสดงทุกฟิ ลด์ ของเรคอร์ ดที่อยู่ในตาราง ถ้ าต้ องการ
แสดงข้ อมูลเฉพาะจะต้ องระบุฟิลด์ ด้วยดังตัวอย่ าง
SELECT str_name_first , int_age FROM Select01;
The Where Clause
อนุประโยค where ใช้ แสดงข้ อมูลแบบมีเงื่อนไข ดังเช่ น
SELECTstr_name_first, int_age
FROM select01 WHERE UPPER(str_name_first) LIKE ‘F%’ ;
SELECT str_name_first, int_age
FROM select01
WHERE UPPER(str_name_first) LIKE ‘F%’
OR UPPER(str_name_first) LIKE ‘G%’ ;
The Order By Clause
อนุประโยค Order by .ใช้ สาหรับแสดงผลลัพธ์ โดยการจัดเรียง
ข้ อมูล ดังตัวอย่ างเป็ นการจัดเรียงชื่อจากน้ อยไปมาก
SELECT str_name_first, int_age FROM select01
ORDER BY str_name_first;
หากต้ องการเรียกจากมากไปน้ อยจะต้ องระบุด้วยคาว่ า DESC ดังนี ้
SELECT str_name_first, int_age FROM select01
ORDER BY Str_name_first DESC;
The Group By Clause
ตัวอย่ างการใช้ group by
SELECT str_name_first
,count(*) FROM select01
GROUP BY str_name_first;
เราสามารถหาค่ าสูงสุดของอายุ
SELECT str_name_first
,MAX(int_age) AS OLDEST_PERSON
FROM
select01 GROUP BY str_name_first ;
The Having Clause
อนุประโยค HAVING จะใช้ ร่วมกับอนุประโยค group by เสมอ
เพื่อต้ องการให้ แสดงข้ อมูลที่ได้ ผ่านการจัดกลุ่มโดย GROUP BY
เพียงบางส่ วนตามเงื่อนไข เช่ น
SELECT str_name_first , MAX(int_age) AS OLDEST_PERSON
FROM select01 GROUP BY str_name_first
HAVING OLDEST_PERSON > 65;
SELECT str_name_first,MAX(int_age) AS OLDEST_PERSON
FROM select01 WHERE int_age > 65 GROUP BY str_name_first ;
The SQL Delete Statement
คาสั่ง DELETE ใช้ ลบข้ อมูลในแต่ ละแถวของตาราง มีรูปแบบคือ
DELETE FROM {table_name} WHERE {where_clause} ;
คาสั่ง delete เป็ นคาสั่งที่สาคัญ ต้ องใช้ อย่ างระวัง เรคอร์ ดที่ลงลบ
ออกแล้ วจะเรียกคืนไม่ ได้ ดังตัวอย่ าง
DELETE FROM select01
WHERE UPPER(str_name_first) = ‘Charles’;
หรือ SELECT COUNT(*) FROM select01
WHERE UPPER(str_name_first) = ‘Charles’;