Bài giảng Lập trình mã nguồn mở (Phần 2)
CHƢƠNG 3: SỬ DỤNG PHP VỚI MYSQL
3.1. Cấu trúc và cú pháp của MySQL
MySQL là hệ thống cơ sở dữ liệu quan hệ. Ý nghĩa cơ bản của MySQL là nó có
thể lƣu trữ thông tin ở những vùng khác nhau và liên kết chúng lại với nhau. Có thể
chứa bất cứ thứ gì trong một cơ sở dữ liệu. Ví dụ nhƣ những thông tin liên quan đến
một ngƣời: chẳng hạn nhƣ first name, last name, address, phone….
MySQL cho phép tạo những thông tin riêng lẻ trên bảng hoặc những khu vực
chứa thông tin thích hợp. Trong MySQL mỗi bảng bao gồm những trƣờng dữ liệu.
MySQL là hệ quản lý dữ liệu quan hệ, nó cho phép chúng ta tạo những bảng
thông tin riêng, hoặc những vùng thông tin thích hợp. Trong hệ thống cơ sở dữ liệu
không quan hệ, tất cả những thông tin đƣợc lƣu trữ trong một bảng lớn tạo nên những
khó khăn trong việc sắp xếp và chỉ có thể chép dữ liệu. Trong SQL, mỗi bảng bao gồm
những phần riêng biệt, biễu diễn mỗi thông tin.
Ví dụ: Cho rằng bảng bao gồm tên khách hàng, địa chỉ và số ID, bảng khác bao
gồm số ID, nơi ở, ….Vùng chung là số ID, thông tin đƣợc lƣu trữ trong hai bảng riêng
biệt sẽ liên kết với nhau nơi mà số ID là nhƣ nhau.
3.1.1. Các kiểu dữ liệu MySQL
Trƣớc khi thiết kế cơ sở dữ liệu trên MySQL, cần phải tham khảo một số kiểu
dữ liệu thƣờng dùng, chúng bao gồm các nhóm nhƣ: numeric, date and time và string.
Trong khi thiết kế cơ sở dữ liệu, cần phải xem xét kiểu dữ liệu cho một cột
trong Table sao cho phù hợp với dữ liệu của thế giới thực.
Điều này có nghĩa là khi chọn dữ liệu cho cột trong Table, phải xem xét đến
loại dữ liệu cần lƣu trữ thuộc nhóm kiểu dữ liệu nào, chiều dài cũng nhƣ các ràng buộc
khác, nhằm khai báo cho phù hợp.
1) Loại dữ liệu numeric
Kiểu dữ liệu numeric bao gồm kiểu số nguyên và kiểu số chấm động, trong
trƣờng hợp dữ liệu kiểu dấu chấm động cần phải chỉ rõ bao nhiều số sau đấu phần lẻ.
Kiểu dữ liệu số nguyên
Loại
tinyint
Range
-127 ->128 hay
Bytes
Diễn giải
1
Số nguyên rất nhỏ
0 … 255
smallint
-32768 ->32767 hay
0 ... 65535
2
3
Số nguyên nhỏ
Số nguyên vừa
mediumint
-8388608 -> 838860 hay
0 ... 16777215
158
int
-231 -> 231 - 1 hay
0 … 232-1
-263 -> 263-1 hay
4
8
Số nguyên
bigint
Số nguyên lớn
0 … 264-1
Kiểu dữ liệu số chấm động
Loại
Range
Bytes
Diễn giải
float
Phụ thuộc số thập phân
Số thập dạng Single hay
Double.
Float(M,D)
Double(M,D)
Float(M[,D])
±1.175494351E-38
4
8
Số thập phân dạng
Single.
±3.40282346638
±1.7976931348623157308
±2.2250738585072014E-308
Số thập phân dạng
Double.
Số chấm động lƣu dƣới
dạng char.
2) Loại dữ liệu Date and Time
Kiểu dữ liệu Date and Time cho PHP nhập liệu dƣới dạng chuỗi hay dạng số.
Loại
Range
1000-01-01
-838:59:59
838:59:59
1000-01-01
00:00:00
Diễn giải
Date
Date trình by dƣới dạng yyyy-mm-dd.
Time trình by dƣới dạng hh:mm:ss.
Time
DateTime
Date v Time trình by dƣới dạng yyyy-mm-dd
hh:mm:ss.
9999-12-31
23:59:59
TimeStamp[(M)] 1970-01-01
00:00:00
TimeStamp trình by dƣới dạng yyyy-mm-dd
hh:mm:ss.
Year[(2|4)]
1970-2069
1901-2155
Year trình by dƣới dạng 2 số hay 4 số.
Đối với kiểu dữ liệu TimeStamp, có thể định dạng nhiều cách nhƣ trình bày
trong bảng sau:
Loại
Hiển thị
YYYYMMDDHHMMSS
YYYYMMDDHHMMSS
YYMMDDHHMMSS
YYMMDDHHMM
TimeStamp
TimeStamp(14)
TimeStamp(12)
TimeStamp(10)
TimeStamp(8)
YYYYMMDD
159
TimeStamp(6)
TimeStamp(4)
YYMMDD
YYMM
YY
TimeStamp(2)
3) Loại dữ liệu String
Kiểu dữ liệu String chia làm ba loại, loại thứ nhất char (chiều dài cố định) và
varchar (chiều dài biến thiên). Char cho phép nhập liệu dƣới dạng chuỗi với chiếu dài
lớn nhất bằng chiều dài đã định nghĩa, nhƣng khi truy cập dữ liệu trên Field có khai
báo dạng này, cần phải xử lý khoảng trắng. Điều này có nghĩa là nếu khai báo chiều
dài là 10, nhƣng chỉ nhập chuỗi 4 ký tự, MySQL lƣu trữ trong bộ nhớ chiều dài 10.
Ngƣợc lại với kiểu dữ liệu Char là Varchar, chiều dài lớn hất ngƣời dùng có thể
nhập vào bằng chiều dài đã định nghĩa cho Field này, bộ nhớ chỉ lƣu trữ chiều dài
đúng với chiều dài của chuỗi đã nhập.
Nhƣ vậy, có nghĩa là nếu khai báo kiểu varchar 10 ký tự, nhƣng chỉ nhập 5 ký
tự, MySQL chỉ lƣu trữ chiều dài 5 ký tự, ngoài ra, khi truy cập đến Field có kiểu dữ
liệu này, không cần phải giải quyết khoảng trắng.
Loại thứ hai là Text hay Blob, Text cho phép lƣu chuỗi rất lớn, Blob cho phép lƣu đối
tƣợng nhị phân. Loại thứ 3 là Enum và Set. Có thể tham khảo cả ba loại trên trong bảng 3.5.
Loại
Range
Diễn giải
char
1-255 characters
1-255 characters
Chiều dài của chuỗi lớn nhất 255 ký tự.
Chiều dài của chuỗi lớn nhất 255 ký tự
(characters).
varchar
tinyblob
28-1 characters
Khai báo cho Field chứa kiểu đối tƣợng nhị
phân cở 255
tinytext
blob
28-1 characters
216-1 characters
Khai báo cho Field chứa kiểu chuỗi cở 255.
Khai báo cho Field chứa kiểu blob cở
65,535 ..
text
216-1 characters
224-1 characters
224-1 characters
232-1 characters
232-1 characters
Khai báo cho Field chứa kiểu chuỗi dạng
văn bản cở 65,535
Mediumblob
Mediumtext
Longblob
Longtext
Khai báo cho Field chứa kiểu blob vừa
khoảng 16,777,215
Khai báo cho Field chứa kiểu chuỗi dạng
văn bản vừa khoảng 16,777,215
Khai báo cho Field chứa kiểu blob lớn
khoảng 4,294,967,295
Khai báo cho Field chứa kiểu chuỗi dạng
văn bản lớn khoảng 4,294,967,295
160
3.1.2. NULL/NOT NULL
Đây là trạng thái của một cột trong bảng cho phép chấp nhận giá trị NULL hay
không. Nếu chỉ ra ràng buộc giá trị NOT NULL thì bắt buộc phải có giá trị trong cột
này mỗi khi bản ghi đƣợc nhập vào.
Đối với một số kiểu dữ liệu không cho phép NULL nên thiết lập giá trị mặc
định cho cột đó, ví dụ nhƣ kiểu dữ liệu bit không cho phép NULL.
Trong phát biểu SQL tạo bảng, chỉ cần khai báo NULL hay NOT NULL sau kiểu
dữ liệu của cột. Trong giao diện đồ họa chỉ cần đánh dấu chọn vào tuỳ chọn Not NULL.
3.1.3. INDEXES
MySQl sử dụng INDEXES để giải quyết việc tìm kiếm thông tin.
Nếu lƣợng thông tin đƣợc lƣu trữ trong bảng lớn, bằng cách sử dụng hệ thống
chọn lọc bên trong MySQl sẽ giúp tìm nhanh và chính xác, nó làm đƣợc điều này nhờ
sử dụng INDEXES.
MySQL yêu cầu INDEX trong mỗi bảng. Thông thƣờng, sử dụng khóa chính,
hoặc tạo ra sự duy nhất để giữ dữ liệu riêng lẻ. Trƣờng này phải “not null” và “unique”
3.1.4. UNIQUE
Sử dụng UNIQUE để thể hiện tính duy nhất, không thể chèn thêm dữ liệu, khi
thêm vào chƣơng trình sẽ báo lỗi.
3.1.5. Tăng tự động (auto Increment)
auto_increment là khái niệm cực kỳ quan trọng trong MySQL (tƣơng đƣơng với
Identity trong SQL Server, Autonumber trong MS Access). Khi muốn một cột có giá
trị tăng tự động nhƣ AutoNumber/Identity, nên định nghĩa cột đó nhƣ auto_increment.
Khi sử dụng auto_increment làm số tăng tự động thì kiểu dữ liệu là số nguyên
hoặc số nguyên lớn.
Trong trƣờng hợp, khai báo số tự động trong MySQL Server, cần phải khai báo
thêm các thông số nhƣ seed. Seed là giá trị khởi đầu khi MySQL Server tự động tăng
giá trị, Increament là bƣớc tăng, nó cho biết mỗi lần tăng cần bao nhiêu giá trị.
Ví dụ khi tạo auto_increment cho cột ItemID [Int] auto_increment, nghĩa là bắt
đầu số 1 và mỗi lần tăng 1 số. Kết quả sẽ có là 1,2,3,4, ...n.
Trong phát biểu SQL của MySQL, để tạo bảng có gá trị tăng tự động chỉ cần
khai báo tên cột, kiểu dữ liệu Int (Integer) và auto_increment nhƣ sau:
IDNO Int auto_increment NOT NULL
3.1.6. Các kiểu bảng của MySQL và kỹ thuật lƣu trữ
MySQL hỗ trợ nhiều kiểu bảng dữ liệu hoặc các máy lƣu trữ khác nhau để giúp
chúng ta tối ƣu hóa CSDL của mình. Các kiểu bảng dữ liệu trong MySQL gồm:
161
Đặc điểm quan trọng nhất để phân biệt các kiểu bảng dữ liệu ở trên là tính có an
toàn giao tác hoặc không. Chỉ các bảng dữ liệu kiểu InnoDB và BDB là có tính an toàn
giao tác (transaction) và chỉ những bảng dữ liệu kiểu MyISAM hỗ trợ chỉ mục toàn
văn bản (full text index) và các đặc tính tìm kiếm. MyISAM cũng là kiểu bảng dữ liệu
mặc định khi tạo bảng dữ liệu mới mà không khai báo kiểu bảng dữ liệu cụ thể. Dƣới
đây là những đặc điểm chính của từng kiểu bảng dữ liệu:
ISAM: ISAM bị loại khỏi các phiên bản từ 5.x trở đi. Nó đƣợc thay thế bởi
MyISAM. Một bảng dữ liệu kiểu ISAM có dung lƣợng tối đa 4GB và không thể di chuyển.
MyISAM: Kiểu MyISAM là mặc định khi tạo ra một bảng dữ liệu mới. Các
thao tác trên bảng dữ liệu kiểu này diễn ra rất nhanh, tuy nhiên nó lại không hỗ trợ đặc
tính an toàn giao tác. Dung lƣợng của một bảng dữ liệu kiểu MyISAM phụ thuộc và hệ
điều hành. Bảng dữ liệu kiểu MyISAM có thể chuyển từ hệ thống này sang hệ thống
khác. Với bảng dữ liệu kiểu MyISAM có thể có tới 64 khóa và chiều dài tối đa của
khóa là 1024byte.
InnoDB: Khác với bảng dữ liệu kiểu MyISAM, bảng dữ liệu kiểu InnoDB có
đặc tính an toàn giao tác và hỗ trợ khóa dòng (row level locking). Các khóa ngoại
đƣợc hỗ trợ trong kiểu InnoDB. Tập tin dữ liệu của bảng dữ liệu kiểu InnoDB có thể
lƣu trữ ở nhiều file khác nhau. Vì thế dung lƣợng của bảng InnoDB phụ thuộc vào
dung lƣợng của ổ đĩa. Giống nhƣ bảng dữ liệu kiểu MyISAM, tập tin dữ liệu của
InnoDB có thể chuyển từ hệ thống này sang hệ thống khác. Điểm bất lợi của InnoDB
so với MyISAM là nó cần nhiều không gian lƣu trữ.
BD: BDB tƣơng tự nhƣ InnoDB ở tính an toàn. Nó hỗ trợ khóa trang (page level
locking). Tuy nhiên tập tin dữ liệu và DB không thể chuyển đổi giữa các hệ thống.
MERGE: Bảng dữ liệu kiểu Merge dduwwocj thêm vào để giải quyết vấn đề
hạn chế của MyISAM. Nó biến nhiều bảng MyISAM thành một bảng dữ liệu vì thế
những hạn chế về dung lƣợng của MyISAM không còn là trở ngại kĩ thuật.
HEAP: Bảng dữ liệu kiểu Heap đƣợc lƣu trữ trong bộ nhớ. Do đó, nó là kiểu
bảng đƣợc thao tác nhanh nhất. Do bởi cơ chế lƣu trữ, dữ liệu sẽ bị mất đi khi máy tính
không còn nguồn điện và đối khi nó còn có thể gây ra tình trạng tràn bộ nhớ đối với
máy chủ cơ sở dữ liệu. Các bảng Heap không hỗ trợ những trƣờng có
kiểu AUTO_INCREMENT, BLOB và TEXT.
162
3.1.7. Lệnh và cú pháp trong MySQL
MySQL là một hệ thống quản lý cơ sở dữ liệu quan hệ (RDBMS) hay còn đƣợc
gọi là Relational Database Management System. RDBMS là một trong những mô hình cơ
sở dữ liệu quan hệ thông dụng hiện nay, hầu hết sản phẩm cơ sở dữ liệu quan hệ hiện nay
đều dựa trên chuẩn của SQL và ANSI-SQL, chẳng hạn nhƣ SQL Server, Oracle,
PostgreSQL và MySQL. Điều này có nghĩa là tất cả những cơ sở dữ liệu quan hệ đều phải
có những tiêu chuẩn theo cú pháp SQL =và MySQL cũng không phải là ngoại lệ.
Ngôn ngữ SQL chia làm 4 loại sau:
. DDL (Data Definition Language): Ngôn ngữ định nghĩa dữ liệu, dùng để tạo
cơ sở dữ liệu, định nghĩa các đối tƣợng cơ sở dữ liệu nhƣ Table, Query, Views hay các
đối tƣợng khác.
. DML (Data Manipulation Language): Ngôn ngữ thao tác dữ liệu, dùng để
thao tác dữ liệu, chẳng hạn nhƣ các phát biểu: Select, Inert, Delete, Update, ...
. DCL: (Data Control Language): Ngôn ngữ sử dụng truy cập đối tƣợng cơ sở
dữ liệu, dùng để thay đổi cấu trúc, tạo ngƣời dùng, gán quyền chẳng hạn nhƣ: Alter,
Grant, Revoke, ...
. TCL: (Transaction Control Language): Ngôn sử dụng để khai báo chuyển tác
chẳng hạn nhƣ: Begin Tran, Rollback, Commit, ...
1) Phát biểu SQL dạng CREATE
Phát biểu SQL dạng CREATE dùng để tạo cơ sở dữ liệu và những đối tƣợng
của cơ sở dữ liệu trong MySQL, SQL Server, Oracle, ..., chúng cú pháp nhƣ sau:
CREATE Database <Database NAME>
CREATE <OBJECT TYPE>
<OBJECT NAME>
. OBJECT TYPE: Loại đối tƣợng cơ sở dữ liệu nhƣ Procedure, Table, View,...
. OBJECT NAME: Tên đối tƣợng trong cơ sở dữ liệu SQL nhƣ sp_IC,
tblEmployer, ...
2) Tạo cơ sở dữ liệu - Create database
Để tạo cơ sở dữ liệu trên MySQL hay SQL Server sử dụng cú pháp sau:
CREATE DATABASE <Database name>
Cú pháp đầy đủ của phát biểu tạo cơ sở dữ liệu nhƣ sau
CREATE DATABASE <database_name>
[ ON [PRIMARY] (
[Name= <'Logical file name'>,] FileName=<'File Name'>
[, SIZE=<Size in Megabyte or KiloByte> ]
[, MAXSIZE=<Size in Megabyte or KiloByte> ][, FILEGROWTH =
<No of Kylobyte|Percentage>]
163
)]
[ LOG ON
(
[Name= <'Logical file name'>,] FileName=<'File Name'>
[, SIZE=<Size in Megabyte or KiloByte> ]
[, MAXSIZE=<Size in Megabyte or KiloByte> ][, FILEGROWTH =
<No of Kylobyte|Percentage>]
)]
[COLLATE <Collation Name>]
[For Load | For Attach]
ON: Dùng để định nghĩa nơi chứa cơ sở dữ liệu và không gian chứa tập tin log.
. NAME: Dùng định nghĩa tên của cơ sở dữ liệu. Tên này dùng tham chiếu khi
gọi đến cơ sở dữ liệu, tên đƣợc dùng cho quá trình backup, export, Import, Shrink cơ
sở dữ liệu đó.
. FILENAME: Tên tập tin cơ sở dữ liệu lƣu trong đĩa cứng, thông thƣờng khi
cài SQL Server lên ổ đĩa nào thì giá trị mặc định cho phép lƣu tập tin đến thƣ mục đó.
Tuy nhiên, nếu muốn cũng có thể thay đổi vị trí các file này.
Khi tạo cơ sở dữ liệu, đã định nghĩa vị trí đặt tập tin ở thƣ mục nào thì không
thể di chuyển một cách thủ công (nhƣ dùng Explorer của Windows), vì làm điều đó
thật nguy hiểm nhất là khi dữ liệu trong cơ sở dữ liệu đang có giá trị kinh tế.
. SIZE: Dung lƣợng của cơ sở dữ liệu khi khởi tạo chúng. Thông thƣờng giá
trị mặc định là 1 MB.
. Dung lƣợng phải là số nguyên, có thể tăng thêm bằng cách sử dụng thủ tục
Shrink trong SQL Server.
. MAXSIZE: Dung lƣợng lớn nhất, khi dung lƣợng cơ sở dữ liệu tăng lên đến
mức MaxSize thì dừng lại.
Nếu khi dung lƣợng bằng MaxSize, các chuyển tác có thể bị huỷ bỏ hay trả về
lỗi không thể thực hiện đƣợc, và có thể làm cho cơ sở dữ liệu bị treo.
Để tránh điều này xảy ra, thì ngƣời quản trị cơ sở dữ liệu phải thƣờng xuyên
theo giỏi quá trình tăng dung lƣợng cơ sở dữ liệu theo thời gian, để có biện pháp tránh
mọi rủi ro có thể xảy ra.
. FILEGROWTH: Dung lƣợng khởi tạo cùng dung lƣợng tối đa cho phép tăng
trong quá trình thêm dữ liệu vào cơ sở dữ liệu. Nhằm tự động hóa, chúng ta phải thiết
lập quá trình tăng tự động theo chỉ số KB cho trƣớc hay tỷ lệ phần trăm theo dung
lƣợng đang có.
. LOG ON: Log on cho phép quản lý những chuyển tác xảy ra trong quá trình
sử dụng cơ sở dữ liệu của SQL Server.
164
Xây dựng cơ sở dữ liệu Test
Nhƣ đã trình bày ở trên, sau đây ví dụ tạo cơ sở dữ liệu Test có cú pháp nhƣ sau.
USE master
GO
CREATE DATABASE Test
ON
( NAME = Test,
FILENAME = 'c:\mssql7\data\Testdat.mdf',
SIZE = 10,
MAXSIZE = 50,
FILEGROWTH = 5 )
LOG ON
( NAME = 'Testlog',
FILENAME = 'c:\mssql7\data\Testlog.ldf',
SIZE = 5MB,
MAXSIZE = 25MB,
FILEGROWTH = 5MB )
GO
Để đơn giản hoá các đối tƣợng Table trong cơ sở dữ liệu Test, chúng ta chỉ
trình bày một vài phát biểu SQL dạng Create Table
Ví dụ: Tạo một số bảng trong Test
/* Tạo bảng danh sách khách hàng thường xuyên */
CREATE TABLE tblcustomers (
CustID int(3) unsigned NOT NULL auto_increment,
Username varchar(20) NOT NULL DEFAULT '' ,
Password varchar(10) NOT NULL DEFAULT '' ,
CustName varchar(50) ,
Address varchar(100) ,
Tel varchar(20) ,
FaxNo varchar(10) ,
Email varchar(50) ,
Contact varchar(50) ,
CountryCode char(3) ,
ProvinceCode char(3) ,
PRIMARY KEY (CustID),
INDEX CustID (CustID)
);
165
/* Tạo bảng hợp đồng mua hàng qua mạng */
CREATE TABLE tblorders (
OrderID int(3) NOT NULL auto_increment,
OrderDate date ,
CustID int(11) ,
Description varchar(100) DEFAULT '0' ,
TranID tinyint(3) DEFAULT '0' ,
PaymentID tinyint(3) DEFAULT '0' ,
Amount float DEFAULT '0' ,
ShipCost float DEFAULT '0' ,
TotalAmount float DEFAULT '0' ,
PRIMARY KEY (OrderID),
INDEX OrderID (OrderID)
);
/* Tạo bảng hợp đồng chi tiết mua hàng qua mạng */
CREATE TABLE tblorderdetails (
ItemID int(3) unsigned DEFAULT '0' ,
OrderID int(3) unsigned DEFAULT '0' ,
No tinyint(3) unsigned DEFAULT '0' ,
Qtty int(3) unsigned DEFAULT '0' ,
Price int(3) unsigned DEFAULT '0' ,
Discount int(3) unsigned DEFAULT '0' ,
Amount bigint(3) unsigned DEFAULT '0'
);
3) Một số quy định khi thiết kế Table
Tên cột - Column Name
Đặt tên cột cũng giống nhƣ đặt tên bảng, có rất nhiều quy tắc đặt tên (nhƣ đã
trình bày ở trên phần table), nhƣng khuyến khích nên theo một số quy tắc cơ bản sau:
. Tên cột bắt đầu chữ hoa, còn lại bằng chữ thƣờng.
. Tên ngắn gọn và đầy đủ ý nghĩa.
. Không nên đặt tên cột có khoảng trắng, sau này sẽ gặp những phiền toái khi
tham chiếu đến cột đó.
. Không đặt tên cột trùng với những từ khoá, từ dành riêng, và những ký tự
đặc biệt nhƣ những phép toán hay toán tử khác.
. Nên đặt tên cột cùng tên những cột có quan hệ với những bảng khác trong
cùng cơ sở dữ liệu, giúp dễ hiểu và tránh bị nhầm lẫn.
Kiểu dữ liệu - Data type
166
Nhƣ đã trình bày các lại dữ liệu trong phần trên, khi xây dựng cơ sở dữ liệu, tất
cả những trƣờng trong bảng cần phải có kiểu dữ liệu cụ thể. Vấn đề quan trọng là chọn
kiểu dữ liệu nào cho phù hợp với dữ liệu mà ngƣời dùng sẽ nhập vào.
Để thiết kế dữ liệu phù hợp với thực tế, ngoài tính ứng dụng hợp với ngữ cảnh
cũng cần quan tâm đến kiểu dữ liệu tƣơng thích và chiều dài của từng cột.
[CustID] [varchar] (10)
/* hay */
[CustID] int
Giá trị mặc định - Default
Thông thƣờng khi tạo ra một cột trong bảng đôi khi chúng ta cần áp dụng giá trị
mặc định, không chỉ cho trƣờng hợp số liệu không nhập từ bên ngoài mà còn cho các
cột tự động có giá trị tự sinh. Với những lý do nhƣ vậy, chúng ta cần có một số giá trị
mặc định cho những cột cần thiết.
. Nếu cột đó là số chúng ta có giá trị mặc định là 0
. Nếu cột đó là ngày tháng chúng ta có giá trị mặc định là ngày nào đó (nhƣ
0000-00-00 là CurDate())
. Nếu cột đó có giá trị là 0 hoặc 1, có thể khai báo giá trị mặc định là 0 hoặc 1
. Nếu cột đó là chuỗi chúng ta có giá trị mặc định nhƣ là 'A'
4) Thay cấu trúc đối tƣợng bằng ALTER
Khi chúng ta cần thiết phải sửa đổi một phần cấu trúc của các đối tƣợng nhƣ
table (view, hay SP trong SQL Server) vì mục đích nào đó, thì sử dụng phát biểu
ALTER để thay đổi cấu trúc của đối tƣợng hiện có:
ALTER <Object type>
<Object Name>
Khi một bảng tồn tại trong cơ sở dữ liệu, do nhu cầu cần thiết phải thay đổi cấu
trúc bảng, sử dụng phát biểu ALTER TABLE cùng các tham số của chúng nhƣ cú
pháp sau:
ALTER TABLE table alteration [,alteration]
Chẳng hạn, có thể sử dụng phát biểu ALTER TABLE để thêm một cột tên
Activate với kiểu dữ liệu TinyInt có giá trị mặc định là 1.
Ví dụ: Thêm một cột tên Activate vào bảng tblOrders
ALTER TABLE tblorders
ADD Activate TINYINT DEFAULT "1"
Khi thay đổi thiết lập giá trị mặc định cho cột nên quan tâm đến giá trị mặc định
đó có phù hợp cho những bản ghi đang tồn tại hay không.
Muốn thay đổi giá trị mặc định của cột cho những bản ghi đang tồn tại, sử dụng
đến mệnh đề phụ nhƣ trong ví dụ sau:
167
Ví dụ: Thiết lập giá trị mặc định trong bảng tblOrders
ALTER TABLE tblorders
CHANGE OrderDate
OrderDate DATETIME
DEFAULT "0000-00-00"
Thay đổi kiểu dữ liệu từ Date dang DateTime, có thể khai báo nhƣ ví dụ sau:
Ví dụ: Thay đổi kiểu dữ liệu
ALTER TABLE tblorders
CHANGE OrderDate
OrderDate DATE
DEFAULT "0000-00-00 00:00:00"
5) Phát biểu SQL dạng DRO
Drop là phát biểu thực hiện phép xoá. DROP dùng để xoá đối tƣợng của cơ sở
dữ liệu nhƣ bảng, cơ sở dữ liệu, ...Cú pháp của phát biểu DROP:
DROP <Object type> <Object name> [, .... n]
Có thể xoá cơ sở dữ liệu, bằng cách khai báo nhƣ sau:
Drop Database Test
/* Phát biểu DROP TABLE chỉ rõ bảng nào cần xoá,
nếu xoá nhiều bảng thì bạn cần dùng dấu phẩy (,) */
DROP TABLE tblCustomers, tblSuppliers
6) Phát biểu SQL dạng SELECT
a) Khái niệm cơ bản về Select
Phát biểu Select dùng để truy vấn dữ liệu từ một hay nhiều bảng khác nhau, kết
quả trả về là một tập bản ghi thoả các điều kiện cho trƣớc nếu có, cú pháp của phát
biểu SQL dạng SELECT:
SELECT <danh sách các cột>
[FROM <danh sách bảng>]
[WHERE <các điều kiện ràng buộc>]
[GROUP BY <tên cột / biểu thức trong SELECT> ]
[HAVING <điều kiện bắt buộc của GROUP BY>]
[ORDER BY <danh sách cột>]
[LIMIT FromNumber | ToNumber]
Danh sách các cột: Khai báo các tên cột, biểu thức kết hợp giữa các cột của
Table cần truy vấn. Trong trƣờng hợp có hai cột cùng tên của hai Table trong phát
biểu, cần phải chỉ định tên Table đi trƣớc. Chẳng hạn, nhƣ ví dụ sau.
Ví dụ: Phát biểu SELECT
Select ItemID,ItemName From tblItems Where Cost>100;
168
Select tblOrders.OrderID,OrderDate,ItemID,Qtty
From tblOrders,tblOrderDetails
Where tblOrders.OrderID = _ tblOrderDetail.OrderID;
b) Phát biểu SELECT với mệnh đề FROM
Phát biểu SQL dạng SELECT là một trong những phát biểu yêu cầu MySQL
truy lục dữ liệu trên cơ sở dữ liệu chỉ định. SELECT dùng để đọc thông tin từ cơ sở dữ
liệu theo những trƣờng quy định, hay những biểu thức cho trƣờng đó.
Mệnh đề FROM chỉ ra tên một bảng hay những bảng có quan hệ cần truy vấn.
Sau khi thực thi phát biểu SQL, kết quả trả về số bản ghi và tổng số bản ghi
đƣợc lấy ra từ bảng.
Dấu * cho phép lọc bản ghi với tất cả các trƣờng trong bảng, nếu muốn chỉ rõ
những trƣờng nào cần lọc cần nêu tên cụ thể những trƣờng đó.
Chúng ta sử dụng một phần cơ sở dữ liệu có sẵn của MySQL, đồng thời bổ
sung thêm cơ sở dữ liệu dành cho ứng dụng bán hàng qua mạng có tên là Test, và bao
gồm nhiều bảng. Bằng phát biểu SELECT chúng ta có thể biết số bảng hay đối tƣợng
khác đang có trong cơ sở dữ liệu Test
Ví dụ: Thực thi phát biểu SQL SELECT hệ thống
show tables from Test
/* Hiển thị tất cả tên bảng của cơ sở dữ liệu hiện hành */
Ghi chú: Có thể sử dụng phát biểu SQL trên để hiển thị những đối tƣợng trong
cơ sở dữ liệu, bằng cách thay thế các tham số và điều kiện.
Cú pháp:
Select * From tablename
/* Lọc tất cả số liệu của tất cả các cột (field) của tablename*/
Select field1,field2
From tablename
/* Lọc tất cả số liệu của 2 field: field1, field2 của tablename*/
Select * From tablename Limit 0,10
/* Lọc top 10 bản ghi đầu tiên của tất cả các field của tablename*/
Select field1, field2 From tablename Limit 0,10
/* Lọc top 10 bản ghi đầu tiên của 2 fields field1, field2 của
tablename*/
Ví dụ: Phát biểu phát biểu SQL dạng Select
Select * From tblCountries
/* Liệt kê tất cả các quốc gia trong bảng tblCountries hoặc bạn có thể liệt kê
tên như phát biểu sau */
Select CountryName From tblCountries
169
c) Phát biểu SQL dạng SELECT với mệnh đề Where
Khi dùng mệnh đề WHERE để tạo nên tiêu chuẩn cần lọc bản ghi theo tiêu
chuẩn đƣợc định nghĩa, thông thƣờng WHERE dùng cột (trƣờng) để so sánh với giá
trị, cột khác, hay biểu thức chứa cột (trƣờng) bất kỳ có trong bảng. Phát biểu SQL
dạng Select với mệnh đề Where cú pháp có dạng nhƣ sau:
Select *
from tablename
where conditions
Select field1, field2, field3
from tablename
where conditions
Với conditions trong cả hai phát biểu trên đƣợc định nghĩa điều kiện truy vấn
nhƣ khai báo sau:
Select * From tablename where field1>10
select * from tblCountries where CountryCode in('VNA','CHN')
Các phép toán so sánh trong conditions bao gồm:
> : lớn hơn
< : nhỏ hơn
where Amount > 100000;
where Amount < 100000;
>= : lớn hơn hoặc bằng where Amount >= 100000;
>= : nhỏ hơn hoặc bằng where Amount <= 100000;
= : bằng
where CustID=‟12';
where CustID!='12';
where CustID<>'12';
!= : Khác
<> : Khác
Các phép toán logic có thể sử dụng trong conditions
and : Phép toán "and"
SELECT *
FROM tblOrders
Where Amount!>100000 And CustID='12';
Or : Phép toán "or"
SELECT *
FROM tblOrderDetails
Where Amount!>100000 Or CustID=„12‟;
Not : Phép toán phủ định (not)
SELECT *
FROM tblOrders
where OrderDate is not null;
Not in : Phép toán phủ định (not in)
170
SELECT *
FROM tblOrders
where OrderID not in („12‟,‟15‟);
Between: Kết quả thuộc trong miền giá trị
SELECT * FROM tblOrders
Where Amount between 10 And 500;
Like : Phép toán so sánh gần giống, sử dụng dấu % để thể hiện thay thế bằng ký
tự đại diện
SELECT * FROM tblCustomers
where CustName like '%A';
Not Like : Phép toán phủ định so sánh gần giống, sử dụng dấu % để thể hiện
thay thế bằng ký tự đại diện
SELECT * FROM tblCustomers
where CustName not like '%A';
IN : Phép toán so sánh trong một tập hợp
SELECT * FROM tblOrders
Where OrderID in ('100','200','300');
Ví dụ: SQL dạng SELECT và Where
/* > : lớn hơn */
Select * From tblOrders
Where Amount > 100000;
/* < : nhỏ hơn */
Select * From tblOrders
Where Amount < 100000;
/* >= : lớn hơn hoặc bằng */
Select * From tblOrders
Where Amount >= 100000;
/* >= : nhỏ hơn hoặc bằng */
Select * From tblOrders
Where Amount <= 100000;
/* = : bằng */
Select * From tblOrders
Where CustID=„12‟;
/* != :Khác */
Select * From tblOrders
Where CustID !=„12‟;
/* <> : Khác */
171
Select * From tblOrders
Where CustID <>„12‟;
/* !> : Không lớn hơn */
Select * From tblOrders
Where Amount !> 100000;
/* !< : Không nhỏ hơn */
Select * From tblOrders
Where Amount !< 100000;
Các phép toán logic
/* and : Phép toán và */
Select * From tblOrders
Where Amount !>100000 And CustID=„12‟;
/* Or : Phép toán hoặc */
Select * From tblOrders
Where Amount !>100000 Or CustID=„12‟;
/* Not : Phép toán phủ định */
Select * From tblOrders
Where OrderDate is NOT NULL;
/* Between: giá trị nằm trong miền */
Select * From tblOrders
Where Amount Between 10 and 500;
/* Like : Phép toán so sánh gần giống, sử dụng % để thay thế bất kỳ ký tự */
Select * From tblOrders
Where Descriion like '%A' Or CustID ='152';
/* Not Like : Phép toán phủ định so sánh gần giống, sử dụng dấu % để thể hiện
thay thế bất kỳ ký tự */
Select * From tblOrders
Where Descriion not like '%A' Or CustID ='152';
/* IN : Phép toán so sánh trong một tập hợp */
Select * From tblOrders
Where OrderID in ('134','244','433');
/* Not IN : Phép toán phủ định so sánh trong một tập hợp */
Select * From tblOrders
Where OrderID not in ('134','244','433');
d) Mệnh đề Order by
172
Thông thƣờng, trong khi truy vấn bản ghi từ bảng dữ liệu, kết quả hiển thị cần
sắp xếp theo chiều tăng hay giảm dựa trên ký tự ALPHABET. Nhƣng cũng có thể sắp
xếp theo một tiêu chuẩn bất kỳ, chẳng hạn nhƣ biểu thức.
Khi sắp xếp dữ liệu trình bày trong kết quả, cần phải chọn trƣờng hay biểu thức
theo trật tự tăng dần hoặc giảm dần.
Cú pháp cho mệnh đề ORDER BY cùng với trạng thái tăng hay giảm, ứng với
ASC sắp xếp tăng dần, DESC giảm dần.
Cú pháp có dạng nhƣ sau:
Order by columnname DESC
Order by columnname1 + columnname2 DESC
Order by columnname ASC
Order by columnname1 ASC, columnname2 DESC
Ví d: SELECT với mệnh đề Order by DESC
/*-- Giảm dần theo thời gian */
Select OrderID , OrderDate, CustID, Amount
From tblOrders
Where Amount >1000 Order by OrderDate DESC
Ví dụ: SQL dạng SELECT với mệnh đề Order by và ASC
/*-- Tăng dần theo thời gian */
Select OrderID , OrderDate, CustID, Amount
From tblOrders
Where Amount >1000 Order by OrderDate ASC
Nếu muốn sắp xếp theo nhiều cột (trƣờng), chỉ cần sử dụng dấu phẩy (,) để
phân cách các cột.
Ví dụ: SELECT với mệnh đề Order by với 2 cột dữ liệu
Select OrderID , OrderDate, CustID, Amount
From tblOrders
Where Amount >1000 Order by OrderID,CustID DESC
Nếu muốn sắp xếp theo nhiều trƣờng kết hợp, chỉ cần dùng thứ tự từng cột cách
nhau bằng dấu +.
Ví dụ: SELECT với mệnh đề Order by hợp 2 cột
/*-- Giảm dần theo số OrderID và CustID */
Select OrderID , OrderDate, CustID, Amount
From tblOrders
Where Amount >1000 Order by OrderID + CustID DESC
173
Nếu trong phát biểu SQL dạng SELECT có nhiều bảng kết hợp lại với nhau, có
thể dùng thêm tên bảng ứng với cột của bảng đó. Phần này sẽ đƣợc diễn giải cụ thể
hơn trong phần kế tiếp (JOIN -Phép hợp).
e) SQL dạng SELECT với mệnh đề GROUP BY
Khi truy vấn bản ghi trên một hay nhiều bảng dữ liệu, thông thƣờng có những
nghiệp vụ thuộc trƣờng nào đó có cùng giá trị, ví dụ khi hiển thị hợp đồng phát sinh
trong tháng, kết quả sẽ có nhiều hợp đồng của khách hàng lặp đi lặp lại.
Ví dụ: SQL dạng SELECT với mệnh đề Order by
Select CustID, Amount
from tblOrders
Trong báo cáo chúng ta lại cần phải biết mỗi khách hàng có bao nhiêu lần trả
tiền, tổng số tiền của mỗi khách hàng đã trả là bao nhiêu?
Để làm điều này, chúng ta sử dụng mệnh đề GROUP BY trong phát biểu SQL
dạng SELECT cùng với một số hàm trong MySQL, nhƣng nhóm bản ghi bằng mệnh
đề Group By.
Ví dụ: SQL dạng SELECT với mệnh đề Group By
Select CustID, count (CustID), Sum(Amount)
From tblOrders
Group by CustID
Order by CustID
f) Phát biểu SQL dạng Select với AS
Khi cần thiết phải thay đổi tên trƣờng trong câu truy vấn, chỉ cần dùng phát
biểu AS. AS cho phép ánh xạ tên cũ, hay giá trị chƣa có tên thành tên mới (header).
Ví dụ, khi sử dụng GROUP BY, những cột tạo ra từ các phép toán count, sum,
max, min, ... cho ra kết quả không có header, nghĩa là không có tên cột để tham chiếu
trong khi gọi đến chúng. Chúng ta phải cần phát biểu AS cho những trƣờng hợp này.
Ví dụ: SQL dạng SELECT với AS và các hàm
Select CustID, Count (CustID) as No, Sum(Amount) as TIENHD,
Max(Amount) as HDLONNHAT, Min(Amount) as HDNHONHAT,
Avg(Amount) as TRUNGBINH
From tblOrders
Group by CustID
Order by CustID
g) Phát biểu SQL dạng Select với Limit N , M
Phát biểu SQL dạng SELECT cho phép truy lục chỉ một số bản ghi tính từ vị trí
thứ n đến vị trí thứ m trong Table (theo một tiêu chuẩn hay sắp xếp nào đó). Để làm
174
điều này, trong phát biểu SQL dạng SELECT dùng chỉ định từ khoá LIMIT với số
lƣợng bản ghi cần lấy từ vị trí thứ n đến m.
Chẳng hạn, trong trƣờng hợp khai báo Select * from tblOrders limit 0,10. Kết
quả sẽ trả về 10 bản ghi đầu tiên trong bảng tblOrders.
Cũng có thể sử dụng kết hợp LIMIT với các mệnh đề nhƣ WHERE, ORDER
BY nhằm tạo ra kết quả nhƣ ý muốn.
Do yêu cầu khác nhau thông qua phát biểu SQL dạng SELECT có sử dụng
LIMIT, nghĩa là kết quả trả về số lƣợng 10 bản ghi đầu tiên với tất cả các cột trong
bảng tblOrders
Ví dụ: Phát biểu SQL dạng SELECT với Limit N,M
Select * From tblOrders Limit 0,10
Nếu muốn lọc ra 10 hợp đồng có số tiền nhiều nhất, chỉ cần sử dụng sắp xếp
theo cột TotalAmount hay Amount trong bảng tblOrders.
Ví dụ: Phát biểu SQL dạng SELECT với Limit N,M
Select OrderID,OrderDate,CustID,Amount
From tblOrders
Order by Amount Desc
Limit 0,10
Nếu muốn lọc ra 10 sản phẩm có số lƣợng bán nhiều nhất, chỉ cần sử dụng sắp
xếp theo cột số lƣợng Qtty.
Ví dụ: Phát biểu SQL dạng Select với Limit N,M
Select ItemID,Qtty,Price,Amount
from tblOrderDetails
Where Amount>10
order by Qtty
Limit 0,10
h) Phát biểu SQL dạng SELECT với DISTINCT
Nếu có một hay nhiều bảng kết nối với nhau, sẽ xảy ra trùng lặp nhiều bản ghi.
Nhƣng trong trƣờng hợp này chỉ cần lấy ra một bản ghi trong tập bản ghi trùng lặp, sử
dụng phát biểu SQL dạng SELECT với chỉ định DISTINCT.
Ví dụ: Phát biểu SQL dạng SELECT
Select ItemID,Qtty,Price,Amount
from tblOrderDetails
order by Qtty
Ví dụ: Phát biểu SQL dạng SELECT với DISTINCT
Select Distinct ItemID,Qtty,Price,Amount
From tblOrderDetails
175
Order by Qtty
7) Nhập dữ liệu bằng phát biểu SQL dạng Insert
Khi thêm bản ghi vào bảng trong cơ sở dữ liệu MySQL, có nhiều cách để thực
hiện công việc này. Tuy nhiên, để sử dụng các phát biểu SQL mang tính chuyên
nghiệp trong MySQL, cần sử dụng phát biểu INSERT.
Có thể sử dụng phát biểu Insert ngay trên ứng dụng kết nối với MySQL. Khi
thêm dữ liệu, cần chú ý kiểu dữ liệu giống hoặc tƣơng ứng kiểu dữ liệu đã khai báo
của cột đó, nếu không phù hợp thì lỗi sẽ phát sinh.
Ngoài ra cần quan tâm đến quyền của User đang truy cập cơ sở dữ liệu. User
phải đƣợc cấp quyền Insert dữ liệu vào từng bảng cụ thể (quyền này do nhà quản trị cơ
sở dữ liệu phân quyền cho User đó).
Trong phát biểu INSERT INTO chúng ta thực hiện trên bảng tblOrderDetails và
bảng tblOrderDetailsHist, hai bảng này có cấu trúc nhƣ sau:
/* Bảng tblOrderDetails*/
CREATE TABLE tblorderdetails (
ItemID int(3) unsigned DEFAULT '0' ,
OrderID int(3) unsigned DEFAULT '0' ,
No tinyint(3) unsigned DEFAULT '0' ,
Qtty int(3) unsigned DEFAULT '0' ,
Price int(3) unsigned DEFAULT '0' ,
Discount int(3) unsigned DEFAULT '0' ,
Amount bigint(3) unsigned DEFAULT '0'
);
/* Bảng tblOrderDetailsHist, dùng để chứa các thông tin
hợp đồng chi tiết khi hợp đồng của khách hàng này kết thúc,
chương trình tự động xoá trong tblOrderDetails và lư trữ lại
trong bảng tblOrderDetailsHist.*/
CREATE TABLE tblorderdetailshist (
ItemID int(3) unsigned DEFAULT '0' ,
OrderID int(3) unsigned DEFAULT '0' ,
No tinyint(3) unsigned DEFAULT '0' ,
Qtty int(3) unsigned DEFAULT '0' ,
Price int(3) unsigned DEFAULT '0' ,
Discount int(3) unsigned DEFAULT '0' ,
Amount bigint(3) unsigned DEFAULT '0'
);
176
Khi Insert dữ liệu vào bảng, có 3 trƣờng hợp xảy ra: insert dữ liệu vào bảng từ
các giá trị cụ thể, insert vào bảng lấy giá trị từ một hay nhiều bảng khác, và cuối cùng
là kết hợp cả hai trƣờng hợp trên.
Insert vào bảng lấy giá trị cụ thể:
INSERT INTO <Tablename>[<columnname list>]
Values (data_value)
Ví dụ: INSERT dữ liệu vào bảng từ giá trị cụ thể
/* Thêm bản ghi với một số cột */
INSERT INTO
TBLCUSTOMERS
(CustName,Username,Password, Address,Tel,FaxNo,Email,Contact,
CountryCode,ProvinceCode)
Values ('Khach San CENTURY', „century‟,
‟1111‟,‟5 Le Loi‟,‟8676767‟,‟8767676‟, „[email protected]‟,‟Hoang
Anh‟, „VNA‟,‟HCM‟)
/* Thêm bản ghi với một số cột */
INSERT INTO
TBLORDERS (OrderID,OrderDate, CustID,Description,Amount)
Values ('11',curdate(),‟1', 'Dat hang qua mang', 20000)
Insert vào bảng lấy giá trị từ bảng khác:
INSERT INTO <Tablename1>[<columnname list>]
Select [columnname list]
From <Tablename2>
Where <Conditions>
Ví dụ: INSERT vào bảng từ giá trị của bảng khác
/* Thêm bản ghi với các cột cụ thể */
/* Chuyển tất cả những hợp đồng chi tiết từ bảng
tblOrderDetails vào bảng tblOrderDetailsHist */
INSERT INTO
TBLORDERDETAILSHIST( ItemID, OrderID, No, Qtty, Price, Discount,
Amount)
SELECT ItemID, OrderID, No, Qtty, Price, Discount, Amount
From tblOrderDetails
ORDER BY OrderID ASC
/* Có thể viết lại thêm bản ghi với tất cả các cột như sau
177
Tải về để xem bản đầy đủ
99 trang
22700
Free
Bạn đang xem 20 trang mẫu của tài liệu "Bài giảng Lập trình mã nguồn mở (Phần 2)", để tải tài liệu gốc về máy hãy click vào nút Download ở trên
File đính kèm:
- bai_giang_lap_trinh_ma_nguon_mo_phan_2.pdf
