Ô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.
BLOBvalues 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.TEXTcó character set, và việc sorting và comparison sẽ dựa theo collation của character set được dùng.
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();
