Transcript CREATE database

Structured Query Language
(SQL)
357337 – Web Programming and Web Database
1
Structured Query Language (SQL)
้
• SQL ทีใ่ ชในระบบฐานข
้อมูลแบบ Relational Database เกิดมาจาก
่ Relational Algebra
พืน
้ ฐานของ Relational Model อย่างเชน
• องค์ประกอบของภาษา SQL
– Data-Definition Language (DDL) : เป็ นคาสงั่ เพือ
่ กาหนด
relation schemas
– Interactive data-manipulation language (DML) : เป็ นการ
รวมคาสงั่ ใน query language ทีม
่ าจากพืน
้ ฐานของ relational algebra
และ relational calculus
– Embedded DML : อยูใ่ นรูป library เพือ
่ ติดต่อกับภาษาโปรแกรมอืน
่ ๆ
่ C, Pascal, PHP
เชน
– View Definition : คาสงั่ เกีย
่ วกับการสร ้าง view
ิ ธิการเข ้าถึงข ้อมูล
– Authorization : คาสงั่ เกีย
่ วกับสท
– Integrity : คาสงั่ เกีย
่ วกับการกาหนดความถูกต ้องของข ้อมูล
ิ้ สุดของ
– Transaction Control : คาสงั่ เกีย
่ วกับการเริม
่ ต ้นและสน
่ lock
transaction เชน
2
MySQL
• MySQL เป็ นโปรแกรมระบบฐานข้อมูลแบบ Relational
Database System
• เป็ น open source และสามารถ download มาใช้ได้
Free
• สามารถทางานได้กบั ระบบปฎิบตั ก
ิ ารสาคัญๆ ได้หมด เช่น
Windows, Linux, Mac OS
• Website: http://www.mysql.com
3
phpMyAdmin
• เมื่อติดตังโปรแกรม
้
AppServ สามารถใช้ งาน phpMyAdmin เพื่อจัดการกับ
MySQL โดยเข้ าไปที่ 127.0.0.1/phpmyadmin
4
การใช้ MySQL ผ่านทาง terminal
• ใน Windows ให้กด control panel -> run -> พิมพ์ cmd
• เมือ
่ ได้ terminal ให้พม
ิ พ์ mysql –u root –p
– หมายถึงเรียกใช้งานโปรแกรม mysql โดยจะเข้าสูร่ ะบบฐานข้อมูล
แบบ user (-u) ทีช
่ อ
ื่ root และ –p หมายถึง เป็ น account ทีม
่ ี
password ป้ องกันไว้อยู่
• เมือ
่ โปรแกรมถาม password ให้ใส่ password ของ root ทีต
่ ง้ ั
เอาไว้
5
การใช้ MySQL ผ่านทาง terminal
• ถ ้าพิมพ์รหัสถูกต ้องจะเข ้าสู่ prompt ของโปรแกรม
MySQL
6
คาสั่ง MySQL
• เราสามารถตรวจดูระบบฐานข ้อมูลทัง้ หมด ที่ root สามารถ
มองเห็นด ้วยคาสงั่
show databases;
7
คาสั่ง MySQL
• ถ ้าติดตัง้ AppServ จะมีฐานข ้อมูลเริม
่ ต ้นอยู่ 4 ฐานข ้อมูล
้
– Information_schema และ mysql เป็ นฐานข ้อมูลทีใ่ ชในการตั
ง้
ค่าระบบของ MySQL
– Phpmyadmin เป็ นฐานข ้อมูลทีเ่ ก็บค่าระบบของ PhpMyAdmin
– Test เป็ นฐานข ้อมูลทดลอง
• เราสามารถเลือกการทางานว่าจะทางานกับฐานข ้อมูลตัวไหนด ้วย
คาสงั่
ื่ ฐานข้อมูล>;
use <ชอ
่
เชน
use phpmyadmin;
8
คาสั่ง MySQL
้ น
• เราสามารถทราบว่าระบบฐานข ้อมูลทีเ่ ราใชอยู
่ ัน
้ ประกอบไปด ้วย
ตาราง อะไรบ ้าง โดยการใชค้ าสงั่
show tables;
9
การดูโครงสร้างของตารางทีถ
่ ูกสร้างมาแล้ว
• สามารถดูโครงสร ้างของตารางได ้โดยใชค้ าสงั่
ื่ ตาราง> หรือ DESC <ชอ
ื่ ตาราง>;
DESCRIBE <ชอ
10
การสร้างฐานข้อมูล
• เมือ
่ เราต ้องการออกแบบฐานข ้อมูลใหม่ เราควรจะสร ้างฐานข ้อมูลก่อน ด ้วย
คาสงั่
ื่ ฐานข้อมูล>;
CREATE database <ชอ
่
เชน
CREATE database CITLibrary;
11
การสร้างฐานข้อมูล
• เมือ
่ เราต ้องการลบฐานข ้อมูล สามารถทาได ้ด ้วยคาสงั่
ื่ ฐานข้อมูล>;
DROP database <ชอ
่
เชน
DROP database CITLibrary;
12
การสร้างตาราง (Create)
Attribute
Constraint

Create Table:
CREATE TABLE r (A1 D1 <NOT NULL, UNIQUE> , … ,An Dn
<integrity constraints 1>
Tuple or Attribute Constraints
...
<integrity constraints k>)

Integrity Constraints: ข ้อกาหนดเกีย
่ วกับข ้อมูล
่
(attribute, รายการ หรือ ตาราง) เชน


PRIMARY KEY ( Aj1, Aj2, …, Ajm )
CHECK ( P )
13
Domain Types ใน SQL

char(n)

varchar(n) เก็บตัวอักษร n ตัว จองพืน้ ทีแ่ บบ dynamic

text








smallint
int
double
float(n)
date
time
datetime
bool
เก็บตัวอักษร n ตัว จองพืน
้ ทีแ
่ บบคงที่
เก็บข ้อมูลทีอ
่ ยูใ่ นรูปตัวอักษร
(2byte) เก็บค่าตัวเลข -32768 ถึง 32767
(4byte) เก็บค่าตัวเลข -2147483648 ถึง 2147483647
(8byte) เก็บค่าได ้ทีต
่ วั เลข 15 ตาแหน่งหลังจุดทศนิยม
(4byte) เก็บค่าได ้ทีต
่ วั เลข 7 ตาแหน่งหลังจุดทศนิยม
YYYY-MM-DD
HH:MM:SS
YYYY-MM-DD HH:MM:SS
ค่า 0 คือ false, ค่าทีไ่ ม่ใช ่ 0 คือ true
14
ตัวอย่างการสร้างตารางพื้นฐาน
• สร ้างตาราง Employee
CREATE TABLE employee (
ssn INT,
fname VARCHAR(20),
lname VARCHAR(20),
address VARCHAR(50),
salary INT,
deptno SMALLINT
)

สร ้างตาราง Department
CREATE TABLE department (
deptno SMALLINT,
dname VARCHAR(30),
mgrssn INT,
startdate DATE
)
15
ตัวอย่างการสร้างตารางพร้อมกาหนดข้อกาหนด
สร ้างตาราง Employee
CREATE TABLE employee (
ssn INT NOT NULL AUTO_INCREMENT,
fname VARCHAR(20) NOT NULL,
lname VARCHAR(20) NOT NULL,
address VARCHAR(50),
salary INT,
deptno SMALLINT NOT NULL,
PRIMARY KEY (ssn),
)

16
ตัวอย่างการสร้างตารางพร้อมกาหนดข้อกาหนด
สร ้างตาราง Department
CREATE TABLE department (
deptno SMALLINT,
dname VARCHAR(30) NOT NULL,
mgrssn INT NOT NULL AUTO_INCREMENT,
startdate DATE DEFAULT Sysdate,
PRIMARY KEY (deptno),
CONSTRAINT dname_uk
UNIQUE (dname),
CONSTRAINT dept_mgr_fk
FOREIGN KEY (mgrssn)
REFERENCES employee(ssn))

17
Domain Constraints
• การกาหนดลักษณะชนิดของข ้อมูล
• ตรวจสอบเมือ
่ มีการเพิม
่ ข ้อมูล
• ตัวอย่างการสร ้าง domain
Constraint Name
– CREATE DOMAIN gender CHAR CONSTRIANT
gender_test
–
CHECK (gender IN (‘F’, ‘M’))
Constraint Condition
18
การลบและเปลีย่ นตาราง (Drop)
• การลบตาราง -- DROP TABLE r
– ลบตาราง Employee ออกจากระบบฐานข ้อมูล
DROP TABLE employee;
• การเปลีย
่ นตาราง (Alter) -- ALTER TABLE r ADD (a d)
– เพิม
่ attribute COMMISSION ในตาราง Employee
ALTER TABLE EMPLOYEE ADD (commission int)
– เปลีย
่ น attribute fname ในตาราง Employee ให ้มีขนาดใหญ่
ขึน
้
ALTER TABLE EMPLOYEE MODIFY (fname varchar(40))
19
การเพิม
่ ข้อมูล (Insert)
• INSERT INTO table-name VALUES (a1, a2, … ,an)
• INSERT INTO table-name(a1-name, a2-name, …, anname) VALUES (a1, a2, …, an)
• ตัวอย่าง
INSERT INTO employee VALUES (1, ‘John’,
‘Terry’,’Chelsea, London’, 100000, 2)
INSERT INTO employee(fname, lname, ssn, deptno)
VALUES (‘John’, ‘Terry’, 1, 2)
20
การลบข้อมูล (Delete)
• DELETE FROM r [WHERE <conditions>]
– ลบรายการของพนักงานทัง้ หมด
DELETE FROM employee
– ลบรายการของพนักงาน ทีม
่ รี หัส 001
DELETE FROM employee WHERE SSN = 001
21
การเปลีย่ นข้อมูล (Update)
• UPDATE r SET a = value
• UPDATE r SET a = value WHERE <conditions>
ื่ และนามสกุลพนักงาน ssn = 1 เป็ น Frank Lampard
– เปลีย
่ นชอ
UPDATE employee SET fname=‘Frank’,lname=‘Lampard’
WHERE ssn = 1
– เพิม
่ เงินเดือนให ้กับพนักงานทุกคน 10 %
UPDATE employee SET salary = salary*1.1
– ลดเงินเดือน 5% ของพนักงานทุกคนใน deptNo = 15
UPDATE employee SET salary = salary*0.95
WHERE dNo = 15
22
SQL
• ในการค ้นหาข ้อมูลด ้วย Relational Algebra สว่ นใหญ่จะอยูใ่ น
รูป
 A1, A2,..,An ( P ( R1 x R2 x...x Rm))
• ใน SQL นัน
้ จะมีคาสงั่ ในรูป
SELECT A1, A2, … , An
FROM
R1, R2, … , Rm
WHERE P
23
Relational Algebra -> SQL
จาก Relational Schema
Person(id, name, age)
ื่ ของคนทีม
Relational Algebra เพือ
่ หารหัส และ ชอ
่ อ
ี ายุมากกว่า 20
id ,name ( age 20 ( person))
SQL :
SELECT id, name FROM person WHERE age > 20;
24
Relational Algebra -> SQL
• ในกรณีท ี่ Relational Algebra ไม่มก
ี ารใช ้ project (  ) หมายถึง
ให ้แสดงผลลัพธ์ออกมาทุกฟิ ลด์ ซงึ่ ตรงกับเครือ
่ งหมาย “*” ใน
SQL
– Relational Algebra
 age20 ( person)
– SQL
SELECT * FROM person WHERE age > 20
25
Relational Algebra -> SQL
• ในกรณีท ี่ Relational Algebra ไม่มก
ี ารใช ้ select (  ) หมายถึงไม่ม ี
เงือ
่ นไขในการกรองผลลัพธ์ ดังนัน
้ ใน SQL สามารถละ WHERE
ออกไปได ้
– Relational Algebra
id ,name ( person)
– SQL
SELECT id,name FROM person
26
Relational Algebra -> SQL
• ถ ้าอยากจะแสดงข ้อมูลทุกแถว และทุกหลักในตาราง สามารถ
เขียนด ้วย SQL คือ
ื่ ตาราง>
SELECT * FROM <ชอ
่
เชน
SELECT * FROM person
27
SQL : SELECT
• คาสงั่ SELECT ใน SQL ตามด ้วยฟิ ลด์ทจ
ี่ ะค ้นหา วิธ ี SELECT มีอยู่ 2 แบบ
– SELECT ALL (เป็ น default ของคาสงั่ SELECT ถ ้าไม่ระบุ)
– SELECT DISTINCT
28
SQL : SELECT
• คาสงั่ SELECT สามารถทาการ + , - , * , / กับฟิ ลด์ทเี่ ลือก
แสดง ออกมาได ้
29
SQL : FROM
่ ากกว่า 1 และคัน
• ใชส้ าหรับเลือกตารางทีจ
่ ะค ้นหาข ้อมูล ถ ้าใสม
่ ด ้วย
เครือ
่ งหมาย “,” หมายถึงจะทาผลคูณ cartesian ของตารางนัน
้ ๆ
30
SQL : WHERE
• ใน Relational Algebra เงือ
่ นไขของ  จะเปลีย
่ นจาก
ั ลักษณ์ทางคณิตศาสตร์ เป็ น สญ
ั ลักษณ์ทพ
สญ
ี่ ม
ิ พ์ได ้ทาง
keyboard ปกติ
• Logical Operations (RA  SQL)
– 

AND
–   OR
–   NOT
• Arithmetic Operations (SQL)
– >, <, <=, >=, =, <>
31
SQL : WHERE
• หารหัสลูกค้ าที่ก้ ูและฝากเงินกับธนาคาร
32