Các phát biểu về SQL

Khái niệm về quan hệ

Để phát triển ứng dụng Webbằng bất kỳ loại cơ sở dữ liệu nào, giai đoạn phân tích thiết kế hệ thống cực kỳ quan trọng. Nếu kết quả phân tích không tối ưu thì ứng dụng đó không thể đạt được giá trị kỹ thuật cũng như giá trị thương mại. Thiết kế cơ sở dữ liệu không tối ưu, chúng có thể dẫn đến việc chương trình chạy chậm và không bền vững.

Một khi ứng dụng chạy chậm đi do cơ sở dữ liệu không tối ưu thì rất có thể bạn phải thiết kế và xây dựng lại từ đầu toàn bộ cấu trúc của chương trình và cơ sở dữ liệu.

Xuất phát từ lý do này, khi xây dựng một ứng dụng thông tin quản lý, chúng ta cần phải qua những bước phân tích thiết kế hệ thống kỹ lưỡng để có được mô hình quan hệ và ERDtrước khi đến các mô hình chức năng chi tiết.

Tuy nhiên, trong lý thuyết một số kiến thức cơ bản bắt buộc bạn phải thực hiện theo mô hình hệ thống ứng với những quan hệ toàn vẹn, nhưng trong thực tế, do tính đặc thù của ứng dụng, thường bạn phải thiết kế lại mô hình theo nhu cầu cân đối giữa độ phức tạp và tính tối ưu.

Trong ứng dụng bán hàng qua mạng Testđã trình bày trong bài, khi quan tâm đến một hợp đồng trên mạng, ngoài những thông tin liên lạc về khách hàng, bạn cần phải lưu trữ dữ liệu khác như chiết hàng mua, phương thức trả tiền, phương thức giao hàng,... Vấn đề được thảo luận ở đây, mỗi hợp đồng có nhì6u mặt hàng chi tiết.

Trong trường hợp này, chúng ta có 6 thực thể liên quan như sau, thực thể danh mục Customers (thông tin liên lạc của khách hàng), Orders(hợp đồng mua hàng), OrderDetails(chi tiết hàng mua), Items (danh mục sản phẩm).

Mô hình quan hệ

Giả sử rằng khi nhập số liệu vào cơ sở dữ liệu, ứng với hợp đồng có mã 101, của khách hàng có tên Nguyễn Văn A, ... có hai sản phẩm chi tiết: 11 (Nước ngọt) và 32 (xà phòng Lux).

Trong trường hợp này bạn đang có một mẩu tin hợp đồng trong bảng tblCustomers,một mẩu tin hợp đồng trong bảng tblOrders và hai mẩu tin trong bảng tblOrderDetails.

Nếu muốn biết thông tin hợp đồng của khách hàng A, rõ ràng bạn cần dùng phát biểu SELECT với mệnh đề kết hợp từ 3 bảng trên. Kết quả trả về 2 mẩu tin là sự kết hợp thông tin từ hai bảng tblCustomers, tblOrders và tblOrderDetails.

Khi thực thi phát biểu SQLdạng SELECTứng với cơ sở dữ liệu như trên bạn phải duyệt qua hai mẩu tin.

Tất nhiên, khi viết ứng dụng thì điều này chấp nhận được, và có thể coi là tối ưu. Giả sử rằng, ứng dụng này được phát triển trên WEB cần lưu tâm đến vấn đề tối ưu tốc độ truy vấn thì sao?

Người thiết kế cơ sở dữ liệu trong trường hợp này phải thay đổi lại cấu trúc để tăng tốc độ truy cập qua mạng khi xử lý trên cơ sở dữ liệu của người dùng.

Khái niệm về mệnh đề JOIN

Trong hầu hết phát biểu SELECT, phần lớn kết quả mà bạn mong muốn lấy về đều có liên quan đến một hoặc nhiều bảng khác nhau. Trong trường hợp như vậy, khi truy vấn dữ liệu bạn cần sử dụng mệnh đề JOINđể kết hợp dữ liệu trên hai hay nhiều bảng lại với nhau.

Khi sử dụng JOIN, bạn cần quan tâm đến trường (cột) nào trong bảng thứ nhất có quan hệ với trường (cột) nào trong bảng thứ hai. Nếu mô hình quan hệ của bạn không tối ưu hay không đúng, quản trình sử dụng JOIN sẽ cho kết quả trả về không như ý muốn.

Trở lại ứng dụng bán hàng qua mạng trong giáo trình này, khi xuất một hợp đồng bán hàng cho khách hàng, theo thiết kế trong cơ sở dữ liệu chúng ta có rất nhiều bảng liên quan đến nhau.

Chẳng hạn, nếu quan tâm bán hàng thì bán cho ai. Suy ra, liên quan đến thông tin khách hàng, bán sản phẩm gì cho họ thì liên quan đến mã sản phẩm, nếu khách hàng trả tiền thì liên quan đến phiếu thu, nếu khách hàng có công nợ thì liên quan đến nợ kỳ trước...

Trong phân này, chúng tôi tiếp tục thiết kế một số bảng dữ liệu cùng với kiểu dữ liệu tương ứng và quan hệ giữa các bảng được mô tả như sau:

[CustID] int auto_increment Primary key,
[CustName] [varchar] (50) NULL ,
[Address] [varchar] (100) NULL,
[Tel] [varchar] (20) NULL,
[FaxNo] [varchar] (20) NULL,
[Email] [varchar] (50) NULL,
[Contact] [varchar] (50) NULL
[Country] [varchar] (3) NULL,
[Province] [varchar] (3) NULL
tblCustomers (danh sách khách hàng)

[OrderID] [int] Not null auto_increment Primary Key,
[OrderDate] [date] NULL ,
[CustID] int ,
[Description] [varchar] (200) NULL ,
[ShipCost] [float] NULL ,
[TranID] [tinyint] NULL ,
[PaymentID] [tinyint] NULL ,
[Amount] [float] NULL ,
[TotalAmount] [float] NULL ,
tblOrders (Hợp đồng bán hàng)

[SubID] [int] auto_increment  NOT NULL ,
[OrderID] int ,
[ItemID] int,
[No] int,
[Qtty] [int] NULL ,
[Price] int NULL ,
[Discount] [Float] NULL ,
[Amount] [Float] NULL

tblOrderDetails (Hợp đồng bán hàng chi tiết)

[ItemID] int auto_increment Primary key,
[ItemName] [varchar] (200) NULL ,
[Unit] [nvarchar] (20) NULL ,
[Cost] [Float] NULL ,
[Active] [tinyint] NOT NULL ,
[Category] int
tblItems (Danh sách sản phẩm)

Bạn có thể tìm thấy các bảng dữ liệu còn lại trong dữ liệu Test trong đĩa đính kèm theo sách.

Mệnh đề INNER JOIN

Phát biểu SQLdạng SELECTcó sử dụng mệnh đề INNERJOINthường dùng để kết hợp hai hay nhiều bảng dữ liệu lại với nhau, cú pháp của SELECT có sử dụng mệnh đề INNERJOIN:

SELECT [SELECT LIST] FROM <FIRST_TABLENAME>
    INNER JOIN <SECOND_TABLENAME> ON <JOIN CONDITION>
    WHERE <CRITERIANS>
    ORDER BY <COLUMN LIST>
    [ASC / DESC]
    

Nếu bạn cần lấy ra một số cột trong các bảng có kết nối lại với nhau bằng mệnh đề INNER JOIN thì cú pháp này viết lại như sau:

SELECT [FIELD1,FIELD2, ...] FROM <FIRST_TABLENAME>
    INNER JOIN <SECOND_TABLENAME>
    ON <JOIN CONDITION>
WHERE <CRITERIANS> ORDER BY <COLUMN LIST>
[ASC / DESC]

Select CustName,OrderID, OrderDate,Amount, TotalAmount
from tblCustomers inner join tblOrders
on tblCustomers.CustID = tblOrders.CustID
where month (OrderDate) = 10
order by  CustName

SELECT first_tablename.*, second_tablename.*
[,next table name]
FROM <first_tablename>
INNER JOIN <second_tablename> ON <join conditions>
[INNER JOIN <next_tablename>
ON <join conditions>] WHERE <conditions> ORDER BY <column list>
[ASC / DESC]

Nếu bạn cần lấy ra tất cả các cột trong các bảng có kết nối lại với nhau bằng mệnh đề INNER JOIN, cú pháp trên có thể viết lại như sau:

SELECT first_tablename.*, second_tablename.*
    [,next table name]
    FROM <first_tablename>
    INNER JOIN <second_tablename> ON <join conditions>
    

Select CustID,CustName,OrderID, OrderDate,TotalAmount
from tblCustomers
inner join tblOrders
On TblCustomers.CustID=tblOrders.CustID
where month (OrderDate) = 10
order by  CustName DESC

Nếu trong những bảng cần kết nối có tên trường (cột) giống nhau thì khi thực thi phát biểu SQL dạng SELECT phải chỉ rõ cột thuộc bảng nào. Trong trường hợp cả hai cùng lấy dữ liệu ra thì bạn cần chuyển ánh xạ tên khác cho cột thông qua mệnh đề AS, ví dụ như:

SELECT first_tablename.CustID as CUSTID, second_tablename.CustID as CUSTID
FROM <first_tablename>
INNER JOIN <second_tablename> ON <join condition>
WHERE <criterians>
ORDER BY <column list>
[ASC / DESC]

Nếu trong những bảng cần kết nối đó có tên trường (cột) giống nhau và không được chỉ rõ như trường hợp trên khi khai báo trong cơ sở dữ liệu SQL Server, khi thực thi phát biểu SQL dạng SELECT bạn sẽ bị lỗi, chẳng hạn như:

SELECT first_tablename.*, second_tablename.* FROM <first_tablename>
INNER JOIN <second_tablename> ON <join condition>
WHERE <criterians> ORDER BY <column list>
[ASC / DESC]

Tuy nhiên, với phát biểu trên bạn có thể thực thi trong cơ sở dữ liệu MySQL. Ngoài ra, phát biểu SQL dạng SELECT sử dụng INNER JOIN bạn có thể ánh xạ (alias) tên của bảng thành tên ngắn gọn để dễ tham chiếu về sau.

Thực ra phát biểu ALIAS có ý nghĩa giống như AS với tên cột trong bảng thành tên cột khác trong phát biểu SELECT.

Select p.*,s.*
from tablename1
inner join tablename2
On tablename1.field1 = tablename2.field2

Select c.CustName,
s.OrderID,s.OrderDate, s.TotalAmount
from tblCustomer c inner join tblOrders s
On c.CustID=s.CustID
where month (s.OrderDate) = 10
order by  c.CustName DESC
 

Tất nhiên, bạn cũng có thể viết phát biểu trên ứng với từng cột muốn lấy ra bằng cách khai báo tên cột.

Mệnh đề LEFT JOIN

Trường hợp bạn mong muốn kết quả lấy ra trong hai bảng kết hợp nhau theo điều kiện: Những mẩu tin bảng bên trái tồn tại ứng với những mẩu tin ở bảng bên phải không tồn tại bạn hãy dùng mệnh đề LEFT JOIN trong phát biểu SQL dạng SELECT, cú pháp có dạng:

select <Column list>
    from lefttablename
    LEFT JOIN righttablename
    on lefttabkename.field1=righttablename.field2
    Where <conditions> Order by <column name>
    ASC/DESC
    

Chẳng hạn, bạn chọn ra tất cả các sản phẩm (với các cột) có hay không có doanh số bán trong tháng hiện tại. Một số sản phẩm không bán trong tháng sẽ có cột Amountcó cột Amountgiá trị NULL.

  
  select ItemID,ItemName,Amount from tblItems
    left join tblOrderDetails
    on tblItems.ItemID=tblOrderDetails.ItemID
    order by Amount
    

Phép toán RIGHT JOIN

Ngược lại với phát biểu SQLdạng SELECTsử dụng mệnh đề LEFTJOINlà phát biểu SQL dạng SELECTsử dụng mệnh đề RIGHTJOINsẽ xuất dữ liệu của bảng bên phải cho dù dữ liệu của bảng bên trái không tồn tại, cú pháp có dạng:

Select <Column list> From lefttablename
    RIGHT JOIN righttablename
    On lefttabkename.field1=righttablename.field2
    Where <conditions> Order by <column name> ASC/DESC
    

Trong ví dụ sau, bạn có thể chọn ra tất cả các sản phẩm có hay không có doanh số bán trong tháng hiện tại. Các sản phẩm không tồn tại doanh số bán sẽ không hiện ra.

Select ItemName,Qtty, Price,Amount
From tblItems
Right  join tblOrderDetails
On tblItems.ItemID=tblOrderDetails.ItemID Where OrderID in (12,14,23,15)
Order by ItemID

Phép toán hợp (union)

Union không giống như những mệnh đề JOINđã giới thiệu trên đây. Union là phép toán dùng để nối hai hay nhiều câu truy vấn dạng Select lại với nhau.

Đối với JOIN, bạn có thể kết nối dữ liệu được thực hiện theo chiều ngang. Đối với Unionbạn kết nối dữ liệu được thực hiện theo chiều dọc.

Để chọn ra những khách hàng thường xuyên trong tblCustomers, kết quả trả về là danh sách các khách hàng thường xuyên.

Select CustID,CustName from tblCustomers

Để chọn ra những khách hàng vãng lai trong tblTempCustomers, kết quả trả về là danh sách các khách hàng vãng lai.

Select CustID,CustName from tblTempCustomers
   

Nếu dùng phép toán UNIONđể kết nối hai bảng trên, kết quả trả về là danh sách cả hai loại khách hàng trong cùng một recordset.

Select CustID,CustName
From tblCustomers
UNION
Select CustID,CustName
From tblTempCustomers

SQL dạng thay đổi và định nghĩa cơ sở dữ liệu

CREATE Database <Database NAME> CREATE <OBJECT TYPE>
    <OBJECT NAME>
    

CREATE DATABASE <Database name>

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>]
    )]

[ 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]

USE master
GO
CREATE DATABASE Test
ON
( NAME = Test,
FILENAME = 'c:mssql7dataTestdat.mdf',
SIZE = 10,
MAXSIZE = 50, FILEGROWTH = 5 )

LOG ON
( NAME = 'Testlog',
FILENAME = 'c:mssql7dataTestlog.ldf', SIZE = 5MB,
MAXSIZE = 25MB,
FILEGROWTH = 5MB ) GO

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)
);

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)
);

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'
);

[CustID] [varchar] (10)
/* hay */
[CustID] int

Số 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 bạn muốn một cột có giá trị tăng tự động như AutoNumber/Identity, bạn 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, bạn khai báo số tự động trong SQL Server, bạn cần phải khai báo thêm các thông số như seed. Seed là giá trị khởi đầu khi SQL 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ả bạn 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 bạn 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

Trong giao diện đồ họa bạn chỉ cần check vào tuỳ chọn AutoIncreament như hình 2

Chọn auto_increment

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 bạn 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 mẩu tin được nhập vào.

Đối với một số kiểu dữ liệu không cho phép NULL bạn 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, bạn 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 như hình 2.

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ì Bạn 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, bạn 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, bạ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.

  ALTER TABLE tblorders
ADD Activate TINYINT DEFAULT "1"

Khi thay đổi thiết lập giá trị mặc định cho cột bạn nên quan tâm đến giá trị mặc định đó có phù hợp cho những mẩu tin đang tồn tại hay không.

Muốn thay đổi giá trị mặc định của cột cho những mẩu tin đang tồn tại, bạn sử dụng đến mệnh đề phụ như trong ví dụ sau:

ALTER TABLE tblorders CHANGE OrderDate OrderDate DATETIME DEFAULT "0000-00-00"

    

Thay đổi kiểu dữ liệu từ Datedang DateTime, bạn có thể khai báo như ví dụ sau:

ALTER TABLE tblorders CHANGE OrderDate OrderDate DATE
DEFAULT "0000-00-00 00:00:00"

    

Mặc khác, bạn cũng có thể tạo hay thay đổi bảng trong màn hình MySQL- Font . Chỉ cần chọn ngăn Database | R-Click | Create New Table, cửa sổ xuất hiện như hình 3.

Phát biểu SQL dạng DROP

Drop là phát biểu thực hiện phép xoá. DROPdù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]

Bạ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

Ngoài ra, bạn cũng có thể dùng MySQl-Front để xoá bảng hay các đối tượng Table trong cơ sở dữ liệu chỉ định. Nếu chọn nhiều bảng cùng một lúc bạn sử dụng phím Control hay Shift như sau: