Ôn tập chi tiết về database trong lập trình PHP

Bài viết được sự cho phép của tác giả Thầy Long Web

Ôn tập database trong lập trình php nhắc bạn nhớ các kiến thức liên quan đến database để dùng trong lập trình web php.

CÁC KHÁI NIỆM CƠ BẢN VỀ DATABASE

Dữ liệu là gì?

Dữ liệu là các thông tin cần lưu trữ để sử dụng lại

Cơ sở dữ liệu (database)

Cơ sở dữ liệu là các dữ liệu được lưu trữ có tổ chức

Hệ quản trị cơ sở dữ liệu

Là các chương trình giúp quản lý và tổ chức cơ sở dữ liệu. Ví dụ: MySQL, SQL Server …

MySQL là hệ quản trị cơ sở dữ liệu mã nguồn mở phổ biến nhất thế giới và được các nhà phát triển rất ưa chuộng trong quá trình phát triển ứng dụng. Vì MySQL là cơ sở dữ liệu tốc độ cao, ổn định và dễ sử dụng, hoạt động trên nhiều hệ điều hành.

Table

Mỗi table là 1 bảng dữ liệu, chứa một mảng thông tin nào đó. Mỗi table có tên, nhiều cột và nhiều hàng.

Mỗi dòng của table là một đơn vị dữ liệu cần lưu trữ, ví dụ như 1 tin, 1 câu hỏi bình chọn, 1 user.

Mỗi cột của table gọi là 1 field hay trường.  Dữ liệu trong một cột thuộc một kiểu xác định nào đó, như char, date, int…

Bạn tạo table khi cần quản lý danh sách các dữ liệu nào đó. Ví dụ: tạo table sanpham để chứa danh sách các sản phẩm, tạo table ykien để chứa danh sách các bình luận, table users chứa danh sách các người dùng.

Ví dụ : cần quản lý danh sách khách hàng, bạn tạo table KhachHang

id	HoTen	NgaySinh	ĐiểmTíchLũy	Active	Tỉnh
1	Mai Thanh Toán	1/4/2001	24	false	HCM
2	Mai Anh Tới	2/1/2000	7	true	HN
3	Mai Chủ Nhật	7/2/2001	93	false	null
4	Hứa Đi Anh	9/2/2001	63	true	HN
5	Đào Công Sự	6/4/2002	80	true	HCM
6	Phạm Kỷ Luật	2/3/2000	0	true	null

Khoá chính

Khóa chính là field đặc biệt trong table, các giá trị trong field này không bao giờ trùng nhau. Khoá chính có thể gồm nhiều field nhưng thường là một field.

Khóa chính (primary key) được tạo ra để quản lý các record trong table, giá trị trong khóa chính dùn để nhận dạng một record nào đó trong table. Ví dụ: trong table KhachHang ở trên, field id được dùng làm khóa chính vì các giá trị trong đó không trùng nhau. Người có mã là 3 chỉ có 1 người duy nhất là Mai Chủ Nhật.

Vì tính chất không được trùng giá trị trong field dùng làm khóa chính, cho nên khi tạo bảng, bạn nên chỉ định thuộc tính tự động tăng cho field này, để khỏi phải nhập và đảm bảo các giá trị trong đó không bao giờ trùng lặp.

Khoá ngoại

Khóa ngoại là cột trong table mà dữ liệu trong nó tham khảo đến khoá chính ở 1 table khác, các giá trị trong field khóa ngoại không được gõ tùy ý mà chỉ nhập những giá trị có trong khóa chính tương ứng.

Trong mỗi table có thể có nhiều field khoá ngoại, mỗi khóa ngoại tham chiếu đến 1 khóa chính ở bảng khác.

Các thuộc tính của field

Mỗi field trong table có một số đặc điểm hay thuộc tính mà bạn cần chỉ định lúc tạo table:

• Null (Yes/No) : cho/không cho giá trị null trong field.
• Default: đặc điểm này để quy định giá trị mặc định cho field, giá trị này sẽ được dùng khi chèn 1 dòng mới mà cột này không có giá trị.
• Comment: đặc điểm dùng để ghi chú cho field.
• Auto Increment  (Yes/No) : cho field tự động tăng giá trị khi chèn dòng mới.
• Type: kiểu của dữ liệu của field. Field này chứa số, chữ, ngày, hay giờ… bạn chỉ định cho đúng nhé. Các kiểu dữ liệu xem bên dưới nhé.
• Length: độ lớn của dữ liệu trong field.

Các kiểu dữ liệu của field trong MySql

Kiểu chuỗi

• CHAR : dùng khi field chỉ chứa chữ, độ dài cố định. Ví dụ: nếu mọi giá trị trong field là chữ và có 9 ký tự thì bạn dùng kiểu char và cho length của field là 9
• VARCHAR: dùng khi field chỉ chứa chữ, độ dài không cố định. Ví dụ: field hoten chứa chữ, độ dài không cố định, tối đa 30 ký tự thì bạn dùng kiểu varchar và cho length của field là 30.
• VARBINARY  và BINARY: Gần giống như Char và Varchar nhưng chứa những binary strings (char, varchar là none binary string). Dữ liệu binary không có character set, do đó việc sort và compare sẽ chỉ dựa trên giá trị các byte được lưu. Độ dài của chuỗi cũng tính bằng số byte (Char, varchar tính bằng số ký tự)
• TEXT và BLOB: chứa text có độ dài lớn. BLOB values chứa các binary string không có character set nên việc sorting và comparison chỉ dựa trên giá trị các byte. TEXT có character set, và việc sorting và comparison sẽ dựa theo collation của character set được dùng.

Tham khảo việc làm MySQL Hồ Chí Minh hấp dẫn

Kiểu Ngày tháng

• DateTime: Lưu dữ liệu kiểu ngày giờ. Có giá trị từ ‘1000-01-01 00:00:00’  đến ‘9999-12-31 23:59:59’.
• Date: Lưu dữ liệu kiểu ngày. Có giá trị từ 1000-01-01 đến 9999-12-31
• TimeStamp: là 1 số nguyên diễn tả 1 thời điểm, giá trị là số giây tính từ giây đầu tiên của năm 1970.
• Time: Lưu giờ. Hiển thị theo kiểu ‘HH:MM:SS’
• Year: Lưu năm

Kiểu số

Field kiểu số chỉ chứa số, dùng khi cần tính toán cộng trừ nhân chia. Khi dùng kiểu số cho field, bạn có thể dùng các kiểu sau: tinyint, smallint, int, bigint (số nguyên), float, double (số thực)

Type	Bytes	Minimum Value	Maximum Value
TINYINT	1	-128	127
SMALLINT	2	-32768	32767
MEDIUMINT	3	-8388608	8388607
INT	4	-2147483648	2147483647
BIGINT	8	-9223372036854775808	9223372036854775807

Mối quan hệ giữa các table

Hai table có quan hệ với nhau khi dữ liệu trong chúng là bổ sung cho nhau. Có ba loại quan hệ giữa hai table:

Quan hệ 1-1: khi 1 record bên table A có quan hệ với 1 record bên table B và ngược lại.
Quan hệ 1-nhiều: khi 1 record bên table A có quan hệ với nhiều record bên table B.
Quan hệ nhiều-nhiều: khi 1 record ở table A có quan hệ với nhiều record bên table B & ngược lại.

Trong thực tế, bạn sẽ gặp nhiều nhất là loại quan hệ 1-nhiều. Hai table A và B có quan hệ 1-nhiều với nhau khi table A có field khóa chính và table B có field khóa ngoại tương ứng với khóa chính ở bên A.

Storage Engine

MySQL có nhiều cách tổ chức lưu dữ liệu cho các table, như MyISAM, InnoDB, Memory,…  Bạn thường gặp nhất là hai loại MyISAM và InnoDB.

MyISAM là cách tổ chức table mặc định của MySQL. Mỗi table theo kiểu MyISAM sẽ được lưu trên 3 file : tableName.FRM (chứa thuộc tính của bản thân table), tableName.MYD (lưu dữ liệu của table), tableName.MYI (lưu các index của table) . Ví dụ: table users dùng MyISAM được lưu trên 3 file users.FRM,  users.MYD và users.MYI.

MyISAM hỗ trợ full-text searches còn InnoDB thì không

InnoDB là cách tổ chức table có hỗ trợ transaction (commit, rollback) để bảo vệ dữ liệu. InnoDB hỗ trợ relationship còn MyISAM thì không. Mỗi table theo kiểu InnoDB sẽ được lưu trên 2 file : tableName.FRM (chứa thuộc tính của bản thân table), tableName.IBD (lưu dữ liệu của table). Ví dụ: table tin dùng InoDB được lưu trên 2  file tin.FRM  và tin.IBD

NGÔN NGỮ SQL

Ngôn ngữ sql là các cây lệnh bạn dùng để tương tác vào dữ liệu trong database, như chèn thêm dữ liệu vào table, chỉnh sửa dữ liệu trong table, xóa dữ liệu trong các table, lấy ra dữ liệu từ các table để xử lý….Sau đây là một số câu lệnh quan trọng cần nhớ.

1. Chèn dữ liệu vào table

Muốn chèn dữ liệu vào table thì bạn dùng lệnh Insert into với cú pháp như sau:

INSERT INTO tbl_name (field1, field2,…) VALUES (val1, val2,…)

Ví dụ:

INSERT INTO khachhang  (hoten, NgaySinh, DiemTichLuy) VALUES (' Mai Thanh Toán', '1/4/2001', 24)

2. Chỉnh dữ liệu trong table

Muốn chỉnh dữ liệu trong table thì dùng lệnh update với cú pháp thế này:

UPDATE tbl_name SET  field1=value1, field2=value2,… WHERE condition

Ví dụ:

UPDATE khachhang SET hoten ='Đào Được Vàng',  NgaySinh ='10/3/2000' WHERE id=5

3. Xoá dữ liệu trong table

Muốn xóa dữ liệu trong table thì dùng lệnh Delete from theo cú pháp như sau:

DELETE FROM tbl_name WHERE condition

Ví dụ:

DELETE FROM  khachhang WHERE id=5

4. Lấy dữ liệu từ table

Lấy dữ liệu từ table là lệnh thường dùng nhất, bạn dùng lệnh Select theo cú pháp như sau:

SELECT  * | colname1, colname2
FROM  tbl_name1, table_name2
WHERE  DieuKienKetBang | DieuKienLoc
GROUP BY colname1, colname2
HAVING DieuKienLocTheoNhom
ORDER BY colname1 ASC|DESC, colname2 ASC|DESC
LIMIT offset, rowcount

Ví dụ 1:  Lấy các record từ bảng tin trong hôm nay

SELECT idTin, TieuDe FROM tin WHERE Ngay=curdate()

Ví dụ 2 : Lấy 10 tin mới

SELECT idTin, TieuDe FROM tin ORDER BY Ngay DESC LIMIT 0,10

Ví dụ 3 : Lấy 10 tin được xem nhiều nhất

SELECT idTin, SoLanXem, TieuDe FROM tin ORDER BY SoLanXem DESC LIMIT 0,10;

Ví dụ 4 : Lấy các dòng dữ liệu trong bảng tin mà tiêu đề có chứa a

SELECT idTin, Ngay, TieuDe FROM tin WHERE TieuDe LIKE 'C%'

Ví dụ 5:  select có kết bảng

SELECT idTin, Ngay, TieuDe, Ten
FROM tin, loaitin
WHERE tin.idLT= loaitin.idLT AND TieuDe like '%a%'
ORDER BY Ngay DESC, idTin DESC

Ví dụ 6:  select lồng nhau (lấy các tin trong ngày mới nhất)

SELECT Ngay, TieuDe FROM tin WHERE  Ngay >= All (SELECT Ngay FROM tin)

Ví dụ 7: Lấy dữ liệu ngẫu nhiên

select idTin, TieuDe FROM tin ORDER BY rand() LIMIT 0,3;

Ví dụ 8 : Thống kê từng user đã nhập bao nhiêu tin

SELECT  idUser, count(idTin) as SoTin FROM tin GROUP BY idUser

Ví dụ 9 : Thống kê  theo loại tổng số lần xem tin

SELECT loaitin.idLT, Ten, sum (solanxem) as TongSoLanXem
FROM tin, loaitin WHERE tin.idLT=loaitin.idLT GROUP BY loaitin.idLT, Ten

Ví dụ 10 : Hiện  năm, tháng, Số đơn đặt hàng, Tổng số lượng, Tổng tiền. Chỉ chọn những đơn hàng đã giao, sắp xếp theo Năm, Tháng giảm dần

SELECT year(Ngay) as Nam, month(Ngay) as Thang, count(idTin) as SoTin, sum (SoLanXem) as TongSoLanXem
FROM tin
GROUP BY Nam , Thang
ORDER BY Nam DESC, Thang DESC

5. Các phép toán trong sql

Các phép toán gồm :   AND,  && , OR  ,||, NOT, BETWEEN … AND…,  IN, NOT

Ví dụ 1: Dùng phép toán between

SELECT idTin,TieuDe, Ngay FROM tin
WHERE Ngay BETWEEN '2010-01-01' AND '2010-01-31' ORDER BY Ngay ASC

Ví dụ 2: Lấy các bài viết trong các loại 1,3,9 (Sử dụng phép toán in)

SELECT idTin, TieuDe, idLT FROM tin WHERE idLT IN ( 1, 9, 3 ) ORDER BY idTin DESC

6. Các hàm trong MYSQL

a. Các hàm trong phát biểu GROUB BY

Sum: Trả về tổng các giá trị của cột
AVG: Trả về giá trị bình quân của cột
MIN: Trả về giá trị nhỏ nhất của cột                    
MAX: Trả về giá trị lớn nhất của cột        
Count: Trả về số lượng record

b. Các hàm xử lý chuỗi trong mysql

– Char: Chuyển đổi kiểu mã ASCII từ số nguyên sang dạng chuỗi. Ví dụ:

SELECT char(35)  // Kết quả:  #

– Upper: chuyển đổi chuỗi sang kiểu chữ hoa

SELECT UPPER(tieude) FROM tin 

– Lower: chuyển đổi chuỗi sang kiểu chữ thường:

SELECT LOWER(tieude) FROM tin 

– Length: Trả về chiều dài của chuỗi:

SELECT Length(tieude) as SoKyTu, tieude FROM tin 

– Left: lấy n ký tự bên trái của chuỗi

SELECT left(tieude, 40) as tieude FROM tin ;

– Right: lấy n ký tự bên phải chuỗi

SELECT idTin,tieude FROM tin WHERE right(tieude, 1) ='c'

c. Các hàm về xử lý thời gian trong mysql

– curdate(): Trả về ngày, tháng và năm hiện hành

SELECT idTin,tieude FROM tin WHERE Ngay =curdate()

– dayofmonth: trả về ngày thứ mấy trong tháng:

Select dayofmonth(curdate()) as homnay

– month: Trả về giá trị tháng

SELECT idTin,tieude FROM tin WHERE month(Ngay)=3

– Year: Trả về giá trị năm

SELECT idTin,tieude FROM tin WHERE year(Ngay)=2010

–  STR_TO_DATE: Chuyển chuỗi thành dạng ngày giờ của MysQL

SELECT STR_TO_DATE('01/05/2013','%d/%m/%Y');    è 2013-05-01

– UNIX_TIMESTAMP : Hàm trả về số giây từ giây đầu tiên của năm 1970 đếm hiện tại

SELECT  UNIX_TIMESTAMP();

Bài viết gốc được đăng tải tại longnv.name.vn

Xem thêm:

• So sánh RDBMS và NoSQL. Nên sử dụng loại CSDL nào cho dự án của bạn?
• Sao lưu và phục hồi dữ liệu trong SQL server
• Cách Import dữ liệu từ File Script vào trong SQL Server

Xem thêm tuyển dụng các vị trí IT hấp dẫn trên TopDev