›
›

Giáo trình Lập trình Web - Chương 5: PHP và MySQL (Tiếp theo) - Trần Công Án

pdf 50 trang huongle 2330
Download
Bạn đang xem 20 trang mẫu của tài liệu "Giáo trình Lập trình Web - Chương 5: PHP và MySQL (Tiếp theo) - Trần Công Án", để tải tài liệu gốc về máy bạn click vào nút DOWNLOAD ở trên

Tài liệu đính kèm:

  • pdfgiao_trinh_lap_trinh_web_chuong_5_php_va_mysql_tiep_theo_tra.pdf

Nội dung text: Giáo trình Lập trình Web - Chương 5: PHP và MySQL (Tiếp theo) - Trần Công Án

  1. CT428. Lập Trình Web Chương 5. PHP & MySQL (MySQL) Giảng viên: Trần Công Án (tcan@cit.ctu.edu.vn) Bộ môn Mạng máy tính & Truyền thông Khoa Công Nghệ Thông Tin & Truyền Thông Đại học Cần Thơ 2013 – 2014
  2. [CT428] Chương 5.1 – MySQL Nội Dung Giới thiệu MySQL Sử dụng giao diện dòng lệnh Các lệnh DDL trên Cơ sở dữ liệu (Database) Các lệnh DDL trên table Các lệnh thao tác trên dữ liệu (DML) Một số công cụ quản lý MySQL miễn phí Các nội dung khác liên quan đến MySQL TS. Trần Công Án (Khoa CNTT&TT) [CT428] Chương 5.1 – MySQL 3
  3. [CT428] Chương 5.1 – MySQL Giới thiệu MySQL MySQL I Là một Hệ quản trị CSDL mã nguồn mở và phổ biến nhất hiện nay. I Có khả năng quản lý các CSDL lớn và hiệu năng cao. I Tại sao sử dụng MySQL? I Đa nền. I Miễn phí. I Gọn nhẹ, có thể cài đặt trên các máy tính “bình thường”. I Có thể dễ dàng sử dụng giao diện Shell để quản trị. I Hỗ trợ nhiều cầu nối để sử dụng trong các ngôn ngữ khác. TS. Trần Công Án (Khoa CNTT&TT) [CT428] Chương 5.1 – MySQL 4
  4. [CT428] Chương 5.1 – MySQL Sử dụng giao diện dòng lệnh Nối Kết Với MySQL I Nối kết đến MySQL: shell, công cụ với giao diện GUI, API. I Shell: từ dòng lệnh gõ vào mysql (Linux: /usr/local/mysql/bin; Windows: c:\Program Files\MySQL\MySQL Server 5.0\bin). I Thoát khỏi MySQL: exit hoặc QUIT TS. Trần Công Án (Khoa CNTT&TT) [CT428] Chương 5.1 – MySQL 5
  5. [CT428] Chương 5.1 – MySQL Sử dụng giao diện dòng lệnh Cú Pháp Câu Lệnh Từ Dòng Lệnh I Các lệnh MySQL từ cửa sổ dòng lệnh luôn kết thúc bằng dấu ; I Một lệnh có thể bao gồm nhiều dòng. I Một lệnh đang được nhập từ phím sẽ không được thực thi nếu nó kết thúc bằng \c I Các lệnh không phân biệt chữ hoa, chữ thường. mysql> SELECT version(); mysql> SELECT + + -> curdate(); | version() | + + + + | curdate() | | 5.6.14 | + + + + | 2014-02-20 | 1 row in set (0.00 sec) + + 1 row in set (0.00 sec) TS. Trần Công Án (Khoa CNTT&TT) [CT428] Chương 5.1 – MySQL 6
  6. [CT428] Chương 5.1 – MySQL Các lệnh DDL trên Cơ sở dữ liệu (Database) Các Lệnh Trên Cơ Sở Dữ Liệu I Một MySQL server có thể quản lý nhiều CSDL. I Các lệnh cơ bản trên CSDL bao gồm: I SHOW DATABASES: liệt kê các CSDL có trên server. I USE : chọn CSDL sẽ được sử dụng. I CREATE DATABASE : tạo CSDL. I DROP DATABASE : xóa CSDL. I ALTER DATABASE : thay đổi thông số của CSDL. I Chú ý: muốn thực hiện các lệnh tác động đến CSDL, người dùng cần được gán quyền create database. TS. Trần Công Án (Khoa CNTT&TT) [CT428] Chương 5.1 – MySQL 7
  7. [CT428] Chương 5.1 – MySQL Các lệnh DDL trên Cơ sở dữ liệu (Database) Người Dùng (Users) Và Quyền (Privileges) I Trong MySQL, người dùng root có quyền cao nhất. I Nối kết đến MySQL với người dùng cụ thể: mysql-u I Truy vấn tên người dùng đang nối kết: SELECT user(); I Gán quyền cho người dùng: GRANT ON TO ' '@' ' IDENTIFIEDBY ' ' I Nếu người dùng chỉ định trong lệnh GRANT chưa có trong server, người dùng sẽ tự động được tạo. I Người dùng cũng có thể được tạo tường minh bằng lệnh CREATE USER TS. Trần Công Án (Khoa CNTT&TT) [CT428] Chương 5.1 – MySQL 8
  8. [CT428] Chương 5.1 – MySQL Các lệnh DDL trên Cơ sở dữ liệu (Database) Người Dùng (Users) Và Quyền (Privileges) Tran-Cong-Ans-MacBook-Pro:~ tcan$ mysql mysql> SELECT user(); Hàm user() trả về người dùng hiện hành + + | user() | + + | tcan@localhost| + + 1 row in set (0.00 sec) mysql> CREATE DATABASE ltweb; ERROR 1044 (42000): Access denied for user 'tcan'@'localhost' to database 'ltweb' TS. Trần Công Án (Khoa CNTT&TT) [CT428] Chương 5.1 – MySQL 9
  9. [CT428] Chương 5.1 – MySQL Các lệnh DDL trên Cơ sở dữ liệu (Database) Người Dùng (Users) Và Quyền (Privileges) Tran-Cong-Ans-MacBook-Pro:~ tcan$ mysql-u root mysql> CREATE DATABASE ltweb; QueryOK, 1 row affected (0.11 sec) mysql> GRANT ALLON ltweb.*TO 'tcan'@'localhost'; QueryOK, 0 rows affected (1.82 sec) Tran-Cong-Ans-MacBook-Pro:~ tcan$ mysql mysql> SHOW DATABASES; + + | Database| + + | information_schema| | ltweb| | test| + + 3 rows in set (0.06 sec) TS. Trần Công Án (Khoa CNTT&TT) [CT428] Chương 5.1 – MySQL 10
  10. [CT428] Chương 5.1 – MySQL Các lệnh DDL trên table Các Lệnh DDL Cơ Bản Trên Table I Các lệnh DDL (Data Manipulate Language) cho phép tạo và thay đổi cấu trúc table. I CREATE TABLE : tạo table. I ALTER TABLE : thay đổi cấu trúc table. I DROP TABLE : xóa table. I DESCRIBE : truy vấn cấu trúc table. I Chú ý: cần phải chọn CSDL trước khi thực hiện các lệnh này. I Truy vấn danh sách các table trong CSDL: SHOW TABLES; TS. Trần Công Án (Khoa CNTT&TT) [CT428] Chương 5.1 – MySQL 11
  11. [CT428] Chương 5.1 – MySQL Các lệnh DDL trên table Kiểu dữ liệu Kiểu Dữ Liệu Trong MySQL I Chuỗi ký tự: CHAR(255), VARCHAR(65K), TINYTEXT(255), TEXT(65K), MEDIUMTEXT(16M), LONGTEXT(4G). I Nhị phân: BINARY(255), VARBINARY(65K), TINYBLOB(255), BLOB(65K), MEDIUMBLOB(16M), LONGBLOB(4G). I Kiểu số: TINYINT(1), SMALLINT(2), MEDIUMINT(3), INT/INTEGER(4), BIGINT(8), FLOAT(4), DOUBLE/REAL(8). I Ngày tháng: DATETIME(yyyy-mm-dd hh-mm-ss), DATE(yyyy-mm-dd), TIMESTAMP(yyyy-mm-dd, 1970–2037), TIME, YEAR(1901–2155). TS. Trần Công Án (Khoa CNTT&TT) [CT428] Chương 5.1 – MySQL 12
  12. [CT428] Chương 5.1 – MySQL Các lệnh DDL trên table Kiểu dữ liệu Giá Trị Mặc Định & Thuộc Tính Của Kiểu DL Giá trị mặc định Kiểu dữ liệu Thuộc nh NULL NOT NULL Số nguyên AUTO_INCREMENT, UNSIGNED, NULL 0 SERIAL DEFAULT VALUE Số thực UNSIGNED, ZERO FILL NULL 0 Chuỗi BINARY, CHARACTER SET NULL “” Nhị phân NULL “” Ngày tháng NULL All 0 TIMESTAMP curdate() curdate() TS. Trần Công Án (Khoa CNTT&TT) [CT428] Chương 5.1 – MySQL 13
  13. [CT428] Chương 5.1 – MySQL Các lệnh DDL trên table Tạo table Tạo Table – CREATE TABLE CREATE TABLE ( [options], [options], [, PRIMARY KEY(col_name[,col_name, ])] ) I Các thông số (options) của các trường: I NOT NULL: trường không được rỗng. I DEFAULT : đặt giá trị mặc nhiên cho trường. I AUTO_INCREMENT: giá trị tự tăng, tính từ m/tin được thêm sau cùng. I UNSIGNED: số không dấu (số dương). TS. Trần Công Án (Khoa CNTT&TT) [CT428] Chương 5.1 – MySQL 14
  14. [CT428] Chương 5.1 – MySQL Các lệnh DDL trên table Tạo table Tạo Table – CREATE TABLE mysql> USE ltweb; mysql> SHOW TABLES; Database changed + + | Tables_in_ltweb| mysql> CREATE TABLE student( + + -> id CHAR(10), | student| -> name VARCHAR(100), + + -> birthday DATE, 1 row in set (0.04 sec) -> province INT UNSIGNED, -> PRIMARY KEY(id)); QueryOK, 0 rows affected (0.52 sec) I Tạo table có cấu trúc giống một table khác: CREATE TABLE LIKE TS. Trần Công Án (Khoa CNTT&TT) [CT428] Chương 5.1 – MySQL 15
  15. [CT428] Chương 5.1 – MySQL Các lệnh DDL trên table Tạo table Xem Cấu Trúc Table mysql> DESCRIBE student; + + + + + + + | Field| Type| Null| Key| Default| Extra| + + + + + + + | id| char(10) |NO| PRI| | | | name| varchar(100) | YES| | NULL| | | birthday| date| YES| | NULL| | | provine| int(10) unsigned| YES| | NULL| | + + + + + + + 4 rows in set (0.85 sec) TS. Trần Công Án (Khoa CNTT&TT) [CT428] Chương 5.1 – MySQL 16
  16. [CT428] Chương 5.1 – MySQL Các lệnh DDL trên table Sửa cấu trúc table Sửa Cấu Trúc Table I Đổi tên table: ALTER TABLE RENAME I Đổi kiểu dữ liệu của trường: ALTER TABLE MODIFY I Đổi tên trường: ALTER TABLE CHANGE I Thêm trường: ALTER TABLE ADD I Xóa trường: ALTER TABLE DROP TS. Trần Công Án (Khoa CNTT&TT) [CT428] Chương 5.1 – MySQL 17
  17. [CT428] Chương 5.1 – MySQL Các lệnh DDL trên table Sửa cấu trúc table Sửa Cấu Trúc Table mysql> ALTER TABLE student ADD enroll TIMESTAMP DEFAULT now(); QueryOK, 0 rows affected (0.38 sec) Records:0 Duplicates:0 Warnings:0 mysql> describe student; + + + + + + + | Field| Type| Null| Key| Default| Extra| + + + + + + + | id| char(10) |NO| PRI| || | name| varchar(100) | YES| | NULL || | birthday| date| YES| | NULL || | province| int(10) unsigned| YES| | NULL || | enroll| timestamp|NO|| CURRENT_TIMESTAMP| | + + + + + + + 5 rows in set (0.00 sec) TS. Trần Công Án (Khoa CNTT&TT) [CT428] Chương 5.1 – MySQL 18
  18. [CT428] Chương 5.1 – MySQL Các lệnh thao tác trên dữ liệu (DML) Các Lệnh Thao Tác Dữ Liệu (DML) I Bao gồm các lệnh thao tác trên dữ liệu của các tables. I INSERT INTO : thêm mẩu tin vào table. I DELETE FROM : xóa mẩu tin khỏi table. I UPDATE : cập nhật các mẫu tin trong table. I SELECT : truy vấn dữ liệu của table. I Chú ý: cần phải chọn CSDL trước khi thực hiện các lệnh này. TS. Trần Công Án (Khoa CNTT&TT) [CT428] Chương 5.1 – MySQL 19
  19. [CT428] Chương 5.1 – MySQL Các lệnh thao tác trên dữ liệu (DML) Thêm dữ liệu vào table – INSERT INTO Thêm Dữ Liệu Vào Table – INSERT INTO I Thêm một mẩu tin vào table: INSERT INTO [ ] VALUES ( ) I Ví dụ: mysql> INSERT INTO student -> VALUES('0123456', 'Tom', '1996-01-15', 1, '2014-2-20'); QueryOK, 1 row affected (0.01 sec) mysql> INSERT INTO student(id, name, birthday, province) -> VALUES('0123457', 'Jerry', '2000-01-15', 1); QueryOK, 1 row affected (0.00 sec) TS. Trần Công Án (Khoa CNTT&TT) [CT428] Chương 5.1 – MySQL 20
  20. [CT428] Chương 5.1 – MySQL Các lệnh thao tác trên dữ liệu (DML) Thêm dữ liệu vào table – INSERT INTO Thêm Dữ Liệu Vào Table – INSERT INTO I Thêm dữ liệu từ một tập tin bên ngoài (text, csv) vào table: LOAD DATA LOCAL INFILE INTO TABLE [options] I Các options cơ bản: I FIELDS TERMINATEDBY ' ': chuỗi ký tự ngăn cách các trường. I FIELDS ENCLOSEDBY ' ': ký tự bao quanh giá trị của trường. I LINE TERMINATEDBY ' ': ký tự ngăn cách các mẩu tin. I IGNORE LINES : bỏ qua n hàng đầu tiên. TS. Trần Công Án (Khoa CNTT&TT) [CT428] Chương 5.1 – MySQL 21
  21. [CT428] Chương 5.1 – MySQL Các lệnh thao tác trên dữ liệu (DML) Thêm dữ liệu vào table – INSERT INTO Thêm Dữ Liệu Vào Table – INSERT INTO mysql> LOAD DATA LOCAL INFILE '/Users/tcan/student-data-more.txt' -> INTO TABLE student FIELDS TERMINATEDBY ','; QueryOK, 8 rows affected, 6 warnings (0.09 sec) Records:8 Deleted:0 Skipped:0 Warnings:6 TS. Trần Công Án (Khoa CNTT&TT) [CT428] Chương 5.1 – MySQL 22
  22. [CT428] Chương 5.1 – MySQL Các lệnh thao tác trên dữ liệu (DML) Truy vấn dữ liệu trong table – SELECT Cú Pháp Câu Lệnh Truy Vấn – SELECT I Cú pháp của lệnh truy vấn SELECT: SELECT | * FROM [WHERE ] [GROUPBY [HAVING ]] I Diễn giải các mệnh đề cơ bản: lựa chọn (SELECT) một số biểu thức/trường của các mẫu tin từ (FROM) một/các table thỏa (WHERE) điều kiện TS. Trần Công Án (Khoa CNTT&TT) [CT428] Chương 5.1 – MySQL 23
  23. [CT428] Chương 5.1 – MySQL Các lệnh thao tác trên dữ liệu (DML) Truy vấn dữ liệu trong table – SELECT Dữ Liệu Minh Họa I Table student đã tạo. I Table classics có cấu trúc và dữ liệu như sau: + + + + + + + | Field | Type | Null | Key | Default | Extra | + + + + + + + | author | varchar(128) | YES | MUL | NULL | | | title | varchar(128) | YES | | NULL | | | type | varchar(16) | YES | | NULL | | | year | char(4) | YES | | NULL | | | price | float | YES | | 0 | | + + + + + + + 5+ + + + + + rows in set (0.00 sec) | author | title | type | year | price | mysql>+ + + + + + describe student; + + + + + + +| Mark Twain | The Adventures of Tom Sawyer | Fiction | 1876 | 100 | | FieldJane Austen | Type | Pride and| Null Prejudice | Key | Default | Fiction | Extra | 1811 | | 99.9 | + + + + + + +| Charles Darwin | The Origin of Species | Non-Fiction | 1856 | 60.5 | | idCharles Dickens| char(10) | The Old | CuriosityNO | PRI Shop | | Fiction | | 1841 | | 54.9 | | nameWilliam Shakespeare| varchar(100) | Romeo and| YES Juliet | | NULL | Play | | 1594 | | 79.9 | |+ + + + + + birthday | date | YES | | NULL | | |5 provincerows in set| int(10) (0.02 sec) unsigned | YES | | NULL | | | enroll | timestamp | NO | | CURRENT_TIMESTAMP | | + + + + + + +mysql> TS. Trần Công5 rows Án (Khoain set CNTT&TT) (0.05 sec) [CT428] Chương 5.1 – MySQL 24 mysql>
  24. [CT428] Chương 5.1 – MySQL Các lệnh thao tác trên dữ liệu (DML) Truy vấn dữ liệu trong table – SELECT Lựa Chọn Tất Cả Các Mẫu Tin I Lựa chọn tất cả các mẩu tin của table student: SELECT* FROM student; + + + + + + | id | name | birthday | province | enroll | + + + + + + | 0123400 | Nobisuke | NULL | 4 | 2000-09-01 00:00:00 | | 0123401 | Tamako | 1971-12-23 | 4 | 2000-09-01 00:00:00 | | 0123456 | Tom | 1996-01-15 | 1 | 2014-02-20 00:00:00 | | 0123457 | Jerry | 2000-01-15 | 1 | 2014-02-21 15:19:36 | | 0123458 | Doremon | 2112-02-20 | 2 | 2014-02-21 23:46:50 | | 0123459 | Nobita | 1992-01-02 | 2 | 2014-02-21 23:46:50 | | 0123460 | Xuka | 1992-01-03 | 3 | 2014-02-21 23:46:50 | | 0123461 | Chaien | 1980-05-30 | 3 | 2014-02-21 23:46:50 | | 0123462 | Dekhi | 1993-07-18 | 3 | 2014-02-21 23:46:50 | | 0123463 | Xeko | 1992-01-16 | 3 | 2014-02-21 23:46:50 | + + + + + + 10 rows in set (0.00 sec) TS. Trần Côngmysql> Án (Khoa CNTT&TT) [CT428] Chương 5.1 – MySQL 25
  25. [CT428] Chương 5.1 – MySQL Các lệnh thao tác trên dữ liệu (DML) Truy vấn dữ liệu trong table – SELECT Lựa Chọn Một Số Trường/Biểu Thức SELECT id, name, birthday FROM student SELECT id, name, year(curdate())-year(birthday)AS age FROM student + + + + + + + + | id | name | birthday | | id | name | age | + + + + + + + + | 0123400 | Nobisuke | NULL | | 0123400 | Nobisuke | NULL | | 0123401 | Tamako | 1971-12-23 | | 0123401 | Tamako | 43 | | 0123456 | Tom | 1996-01-15 | | 0123456 | Tom | 18 | | 0123457 | Jerry | 2000-01-15 | | 0123457 | Jerry | 14 | | 0123458 | Doremon | 2112-02-20 | | 0123458 | Doremon | -98 | | 0123459 | Nobita | 1992-01-02 | | 0123459 | Nobita | 22 | | 0123460 | Xuka | 1992-01-03 | | 0123460 | Xuka | 22 | | 0123461 | Chaien | 1980-05-30 | | 0123461 | Chaien | 34 | | 0123462 | Dekhi | 1993-07-18 | | 0123462 | Dekhi | 21 | | 0123463 | Xeko | 1992-01-16 | | 0123463 | Xeko | 22 | + + + + + + + + 10 rows in set (0.24 sec) 10 rows in set (0.38 sec) TS. Trầnmysql> Công Án (Khoa CNTT&TT) [CT428] Chương 5.1 –mysql> MySQL 26
  26. [CT428] Chương 5.1 – MySQL Các lệnh thao tác trên dữ liệu (DML) Truy vấn dữ liệu trong table – SELECT Một Số Hàm Thông Dụng Của MySQL I Hàm xử lý chuỗi: CONCAT(str1, str2, ), CONCAT_WS(sep, str1, str2, ), LEFT/RIGHT(str, len), MID(str, pos, len), LENGTH(str), LPAD/RPAD(str, len, padstr), LOCATE(substr, str, pos), LOWER/UPPER(str), TRIM/LTRIM/RTRIM(str) I Hàm xử lý kiểu ngày tháng: CURDATE(), DATE(expr), DATE_ADD(date, INTERVAL expr unit), DAY/MONTH/YEAR(date), DATENAME(date), DAYOFWEEK(date) I Hàm xử lý giờ: CURTIME(), HOUR/MINUTE/SECOND(time), TIMEDIFF(expr1, expr2) I Hàm toán học: ABS(expr), FLOOR(expr), MOD(expr), ROUND(expr) TS. Trần Công Án (Khoa CNTT&TT) [CT428] Chương 5.1 – MySQL 27
  27. [CT428] Chương 5.1 – MySQL Các lệnh thao tác trên dữ liệu (DML) Truy vấn dữ liệu trong table – SELECT Truy Vấn Có Điều Kiện – WHERE + + + + | id | name | birthday | SELECT id, name, birthday + + + + FROM student | 0123400 | Nobisuke | NULL | WHERE province=4 | 0123401 | Tamako | 1971-12-23 | + + + + 2 rows in set (0.33 sec) SELECT id, name, birthday + + + + | id | name | birthday | FROM student mysql> + + + + WHERE MONTH(birthday)=MONTH( | 0123458 | Doremon | 2112-02-20 | CURDATE()) + + + + + + + +1 row in set (0.06 sec) SELECT id, name, birthday | id | name | birthday | FROM student + + + +mysql> select now(); + + WHERE province=3 | 0123460 | Xuka | 1992-01-03 | | 0123462now() | Dekhi | 1993-07-18 | | AND YEAR(birthday)>=1990 |+ + 0123463 | Xeko | 1992-01-16 | + + + +| 2014-02-21 22:24:20 | 3+ + rows in set (0.04 sec) 1 row in set (0.34 sec) mysql> TS. Trần Công Án (Khoa CNTT&TT) [CT428] Chươngmysql> 5.1 – MySQL select curdate(); 28 + + | curdate() | + + | 2014-02-21 | + + 1 row in set (0.12 sec) mysql>
  28. [CT428] Chương 5.1 – MySQL Các lệnh thao tác trên dữ liệu (DML) Truy vấn dữ liệu trong table – SELECT Truy Vấn Có ĐK – Miền Giá Trị, Danh Sách + + + + | id | name | birthday | SELECT id, name, birthday + + + + FROM student | 0123456 | Tom | 1996-01-15 | WHERE birthday BETWEEN | 0123459 | Nobita | 1992-01-02 | 1990/1/1 AND 2000/1/1 | 0123460 | Xuka | 1992-01-03 | ' ' ' ' | 0123462 | Dekhi | 1993-07-18 | | 0123463 | Xeko | 1992-01-16 | + + + + 5 rows in set (0.06 sec) SELECT id, name, birthday + + + + FROM student | id | name | birthday | mysql> WHERE LOWER(name)IN + + + + ('tom', 'jerry') | 0123456 | Tom | 1996-01-15 | | 0123457 | Jerry | 2000-01-15 | + + + + 2 rows in set (0.44 sec) mysql> TS. Trần Công Án (Khoa CNTT&TT) [CT428] Chương 5.1 – MySQL 29
  29. [CT428] Chương 5.1 – MySQL Các lệnh thao tác trên dữ liệu (DML) Truy vấn dữ liệu trong table – SELECT Truy Vấn Có ĐK – NULL, Pattern, FULLTEXT SELECT id, name, birthday + + + + FROM student | id | name | birthday | WHERE birthdayIS NULL + + + + | 0123400 | Nobisuke | NULL | + + + + 1 row in set (0.00 sec) SELECT id, name, province + + + + | id | name | province | FROM student mysql> WHERE name LIKE '%nobi%' + + + + | 0123400 | Nobisuke | 4 | | 0123459 | Nobita | 2 | + + + + ALTER TABLE classics 2 rows in set (0.03 sec) ADD FULLTEXT(author, title) + + + |mysql> author | title | + + + SELECT author, title | William Shakespeare | Romeo and Juliet | FROM classics | Charles Darwin | The Origin of Species | | Charles Dickens | The Old Curiosity Shop | WHERE MATCH(author, title) + + + AGAINST('charles romeo') 3 rows in set (3.39 sec) mysql> TS. Trần Công Án (Khoa CNTT&TT) [CT428] Chương 5.1 – MySQL 30
  30. [CT428] Chương 5.1 – MySQL Các lệnh thao tác trên dữ liệu (DML) Sắp xếp dữ liệu, tránh trùng lặp Sắp Xếp, Tránh Trùng Lặp Dữ Liệu I Sắp xếp dữ liệu: dùng mệnh đề ORDERBY | I Lọc dữ liệu bị trùng lắp: dùng từ khóa DISTINCT trong mệ/đề SELECT. + + + | author | title | + + + | Charles Darwin | The Origin of Species | SELECT author, title | Charles Dickens | The Old Curiosity Shop | FROM classics | Jane Austen | Pride and Prejudice | | Mark Twain | The Adventures of Tom Sawyer | ORDERBY author | William Shakespeare | Romeo and Juliet | + + + 5 rows in set (0.02 sec) + + SELECT DISTINCT type mysql>| type select distinct| type from classics; + + FROM classics + + | typeFiction | + +| Non-Fiction | | FictionPlay | |+ + Non-Fiction | |3 Playrows in set |(0.00 sec) + + mysql> TS. Trần Công Án (Khoa CNTT&TT) [CT428]3 rows Chương in set 5.1 (0.00 – MySQL sec) 31 mysql>
  31. [CT428] Chương 5.1 – MySQL Các lệnh thao tác trên dữ liệu (DML) Tổng hợp dữ liệu (Summarizing/Aggregating data) Tổng Hợp Dữ Liệu I Dùng các hàm tổng hợp để tạo ra các dữ liệu có tính tổng hợp từ các trường của table (e.g. sách có giá cao nhất, . . . ). I Cú pháp: aggregate_function([ALL|DISTINCT] ) I Các hàm tổng hợp tính toán trên một tập các giá trị và trả về một giá trị đơn. I Các hàm thông dụng: AVG, COUNT, MAX, MIN, SUM TS. Trần Công Án (Khoa CNTT&TT) [CT428] Chương 5.1 – MySQL 32
  32. [CT428] Chương 5.1 – MySQL Các lệnh thao tác trên dữ liệu (DML) Tổng hợp dữ liệu (Summarizing/Aggregating data) Tổng Hợp Dữ Liệu – Ví Dụ SELECT COUNT(*) NoOfStudent + + | NoOfStudent | FROM student + + | 10 | + + 1 row in set (0.00 sec) + + SELECT COUNT(*) |mysql> COUNT(*) | FROM student + + | 1 | WHERE birthdayIS NULL + + 1 row in set (0.03 sec) + + + + |mysql> MAX(price) | MIN(price) | AVG(price) | + + + + SELECT MAX(price), MIN(price), AVG(price) | 100.00 | 54.90 | 79.040001 | FROM classics + + + + 1 row in set (0.00 sec) + + + |mysql> COUNT(type) | COUNT(DISTINCT type) | + + + SELECT COUNT(type), COUNT(DISTINCT type) | 5 | 3 | + + + FROM classics; 1 row in set (0.00 sec) mysql> TS. Trần Công Án (Khoa CNTT&TT) [CT428] Chương 5.1 – MySQL 33
  33. [CT428] Chương 5.1 – MySQL Các lệnh thao tác trên dữ liệu (DML) Nhóm/Gộp dữ liệu (Data grouping) Nhóm Dữ Liệu – GROUP BY & HAVING I Có thể nhóm dữ liệu dựa vào giá trị 1 trường hay một biểu thức. I Cú pháp: GROUPBY | [HAVING ] I Mệnh đề HAVING dùng để lọc dữ liệu sau khi nhóm. I Thường kết hợp với các hàm tổng hợp dữ liệu để tổng hợp dữ liệu cho từng nhóm. I Chú ý: Nếu câu truy vấn có mệnh đề WHERE, việc nhóm gộp dữ liệu chỉ được thực hiện trên tập dữ liệu thỏa điều kiện mệnh đề WHERE. TS. Trần Công Án (Khoa CNTT&TT) [CT428] Chương 5.1 – MySQL 34
  34. [CT428] Chương 5.1 – MySQL Các lệnh thao tác trên dữ liệu (DML) Nhóm/Gộp dữ liệu (Data grouping) Nhóm Dữ Liệu – GROUP BY & HAVING SELECT type, COUNT(title), max(price) + + + + | type | COUNT(title) | max(price) | FROM classics + + + + GROUPBY type | Fiction | 3 | 100.00 | | Non-Fiction | 1 | 60.50 | | Play | 1 | 79.90 | + + + + SELECT type, COUNT(title) 3 rows in set (0.06 sec) + + + mysql> select * from classics; FROM classics | type | COUNT(title) | + + + + + + + + + GROUPBY type | author | title | type | year | price | HAVING count(title)>2 + + + + + +| Fiction | 3 | |+ + + Mark Twain | The Adventures of Tom Sawyer | Fiction | 1876 | 100.00 | |1 Janerow inAusten set (0.27 sec) | Pride and Prejudice | Fiction | 1811 | 99.90 | |+ + + Charles Darwin | The Origin of Species | Non-Fiction | 1856 | 60.50 | |mysql> Charles SELECT Dickens type, COUNT(title),| The Old Curiosity max(price) Shop | Fiction | 1841 | 54.90 | | type | COUNT(*) | SELECT type, COUNT(*) | William -> ShakespeareFROM classics | Romeo and Juliet | Play | 1594 | 79.90 | + + + + + + + + + -> GROUP BY type FROM classics | Fiction | 3 | 5 rows -> in set (0.06 sec) | Non-Fiction | 1 | WHERE year>1800 -> ; + + + mysql>+ + + + select * from student; GROUPBY type 2 rows in set (0.00 sec) + + + + + +| type | COUNT(title) | max(price) | |+ + + + id | name | birthday | province | enroll | + + + + + +mysql> | Fiction | 3 | 100.00 | | 0123400 | Nobisuke | NULL | 4 | 2000-09-01 00:00:00 | | Non-Fiction | 1 | 60.50 | | 0123401 | Tamako | 1971-12-23 | 4 | 2000-09-01 00:00:00 | TS. Trần Công Án (Khoa CNTT&TT) [CT428] Chương 5.1 – MySQL| Play | 1 | 79.9035 | | 0123456 | Tom | 1996-01-15 | 1 | 2014-02-20 00:00:00 | |+ + + + 0123457 | Jerry | 2000-01-15 | 1 | 2014-02-21 15:19:36 | |3 0123458rows in |set Doremon (0.06 |sec) 2112-02-20 | 2 | 2014-02-21 23:46:50 | | 0123459 | Nobita | 1992-01-02 | 2 | 2014-02-21 23:46:50 | |mysql> 0123460 select | Xuka * from | classics;1992-01-03 | 3 | 2014-02-21 23:46:50 | |+ + + + + + 0123461 | Chaien | 1980-05-30 | 3 | 2014-02-21 23:46:50 | | 0123462author | Dekhi | 1993-07-18| title | 3 | 2014-02-21 | 23:46:50type | | year | price | |+ + + + + + 0123463 | Xeko | 1992-01-16 | 3 | 2014-02-21 23:46:50 | + + + + + +| Mark Twain | The Adventures of Tom Sawyer | Fiction | 1876 | 100.00 | 10| Janerows Austenin set (0.10 sec) | Pride and Prejudice | Fiction | 1811 | 99.90 | | Charles Darwin | The Origin of Species | Non-Fiction | 1856 | 60.50 | mysql>| Charles select Dickens * from classics | The Oldwhere Curiosity year>1950; Shop | Fiction | 1841 | 54.90 | Empty| William set (0.07Shakespeare sec) | Romeo and Juliet | Play | 1594 | 79.90 | + + + + + + mysql> select * from classics where year>'1950'; 5 rows in set (0.06 sec) Empty set (0.05 sec) mysql> selectselect from from classics student; where year>1800; + + + + + ++ + + + + + | authorid | name | title | birthday | province || enrolltype | year | |price | + + + + + ++ + + + + + | Mark0123400 Twain | Nobisuke | The | AdventuresNULL of | Tom Sawyer 4 || 2000-09-01Fiction 00:00:00 | 1876 | |100.00 | | Jane0123401 Austen | Tamako | Pride | 1971-12-23 and Prejudice | 4 || 2000-09-01Fiction 00:00:00 | 1811 | | 99.90 | | Charles0123456 Darwin| Tom | The | Origin1996-01-15 of Species | 1 || 2014-02-20Non-Fiction 00:00:00 | 1856 | | 60.50 | | Charles0123457 Dickens| Jerry | The | Old2000-01-15 Curiosity | Shop 1 || 2014-02-21Fiction 15:19:36 | 1841 | | 54.90 | + + + + + +| 0123458 | Doremon | 2112-02-20 | 2 | 2014-02-21 23:46:50 | 4| rows0123459 in set| Nobita (0.01 sec) | 1992-01-02 | 2 | 2014-02-21 23:46:50 | | 0123460 | Xuka | 1992-01-03 | 3 | 2014-02-21 23:46:50 | mysql>| 0123461 SELECT | Chaien type, COUNT(*) | 1980-05-30 | 3 | 2014-02-21 23:46:50 | -> FROM classics | 0123462 | Dekhi | 1993-07-18 | 3 | 2014-02-21 23:46:50 | -> WHERE year>1800 | 0123463 | Xeko | 1992-01-16 | 3 | 2014-02-21 23:46:50 | -> GROUP BY type + + + + + + -> ; + + +10 rows in set (0.10 sec) | type | COUNT(*) | + + +mysql> select * from classics where year>1950; |Empty Fiction set (0.07 | sec) 3 | | Non-Fiction | 1 | + + +mysql> select * from classics where year>'1950'; 2Empty rows setin set(0.05 (0.00 sec) sec) mysql> select * from classics where year>1800; + + + + + + | author | title | type | year | price | + + + + + + | Mark Twain | The Adventures of Tom Sawyer | Fiction | 1876 | 100.00 | | Jane Austen | Pride and Prejudice | Fiction | 1811 | 99.90 | | Charles Darwin | The Origin of Species | Non-Fiction | 1856 | 60.50 | | Charles Dickens | The Old Curiosity Shop | Fiction | 1841 | 54.90 | + + + + + + 4 rows in set (0.01 sec) mysql> SELECT type, COUNT(*) -> FROM classics -> WHERE year>1800 -> GROUP BY type -> ; + + + | type | COUNT(*) | + + + | Fiction | 3 | | Non-Fiction | 1 | + + +
  35. [CT428] Chương 5.1 – MySQL Các lệnh thao tác trên dữ liệu (DML) Kết nối dữ liệu giữa các bảng (Join) Nối Kết Dữ Liệu – JOIN | WHERE I Join dùng để truy vấn dữ liệu từ nhiều table, dựa vào mối quan hệ luận lý giữa chúng (giá trị của các trường). I Các cách kết nối: I NATURAL JOIN : tự động kết nối table trong mệnh đề FROM và NATURAL JOIN dựa trên các trường cùng tên. I JOIN ON : kết nối table ở mệnh đề FROM và JOIN dựa trên các trường chỉ định trong mệnh đề ON. I FROM WHERE : kết nối các table trong mệnh đề FROM sử dụng điều kiện trong mệnh đề WHERE. I Các loại nối kết: INNER JOIN,(LEFT|RIGHT)OUTER JOIN TS. Trần Công Án (Khoa CNTT&TT) [CT428] Chương 5.1 – MySQL 36
  36. [CT428] Chương 5.1 – MySQL Các lệnh thao tác trên dữ liệu (DML) Kết nối dữ liệu giữa các bảng (Join) Dữ Liệu Minh Họa I Tạo thêm table province có cấu trúc và dữ liệu như sau: + + + + + + + | Field | Type | Null | Key | Default | Extra | + + + + + + + | province | int(10) unsigned | NO | PRI | 0 | | | name | varchar(100) | YES | | NULL | | + + + + + + + 2+ + + rows in set (0.07 sec) | province | name | mysql>+ + + | 1 | Tokyo | | 2 | Kobe | | 3 | Kagawa | | 4 | Hirosima | | 5 | Osaka | + + + 5 rows in set (0.00 sec) mysql> select * from student; + + + + + + TS. Trần Công Án (Khoa CNTT&TT) [CT428] Chương 5.1 – MySQL 37 | id | name | birthday | province | enroll | + + + + + + | 0123400 | Nobisuke | NULL | 4 | 2000-09-01 00:00:00 | | 0123401 | Tamako | 1971-12-23 | 4 | 2000-09-01 00:00:00 | | 0123456 | Tom | 1996-01-15 | 1 | 2014-02-20 00:00:00 | | 0123457 | Jerry | 2000-01-15 | 1 | 2014-02-21 15:19:36 | | 0123458 | Doremon | 2112-02-20 | 0 | 2014-02-21 23:46:50 | | 0123459 | Nobita | 1992-01-02 | 2 | 2014-02-21 23:46:50 | | 0123460 | Xuka | 1992-01-03 | 3 | 2014-02-21 23:46:50 | | 0123461 | Chaien | 1980-05-30 | 3 | 2014-02-21 23:46:50 | | 0123462 | Dekhi | 1993-07-18 | 3 | 2014-02-21 23:46:50 | | 0123463 | Xeko | 1992-01-16 | 3 | 2014-02-21 23:46:50 | + + + + + + 10 rows in set (0.00 sec) mysql>
  37. [CT428] Chương 5.1 – MySQL Các lệnh thao tác trên dữ liệu (DML) Kết nối dữ liệu giữa các bảng (Join) Kết Nối Dữ Liệu – INNER JOIN FTable JTable SELECT id, student.name, province.name FROM student JOIN province + + + + | id | name | name | ON student.province= + + + + province.province | 0123456 | Tom | Tokyo | | 0123457 | Jerry | Tokyo | | 0123459 | Nobita | Kobe | | 0123460 | Xuka | Kagawa | SELECT id, s.name, p.name | 0123461 | Chaien | Kagawa | FROM studentASs, provinceASp | 0123462 | Dekhi | Kagawa | WHERE s.province= p.province | 0123463 | Xeko | Kagawa | | 0123400 | Nobisuke | Hirosima | | 0123401 | Tamako | Hirosima | + + + + 9 rows in set (0.01 sec) mysql> TS. Trần Công Án (Khoa CNTT&TT) [CT428] Chương 5.1 – MySQL 38
  38. [CT428] Chương 5.1 – MySQL Các lệnh thao tác trên dữ liệu (DML) Kết nối dữ liệu giữa các bảng (Join) Kết Nối Dữ Liệu – LEFT OUTER JOIN + + + + SELECT id, s.nameAS SName, p.name | id | SName | PName | AS PName + + + + FROM studentASs | 0123456 | Tom | Tokyo | | 0123457 | Jerry | Tokyo | LEFT OUTER JOIN province asp | 0123459 | Nobita | Kobe | ON s.province= p.province | 0123460 | Xuka | Kagawa | | 0123461 | Chaien | Kagawa | | 0123462 | Dekhi | Kagawa | | 0123463 | Xeko | Kagawa | | 0123400 | Nobisuke | Hirosima | FTable JTable | 0123401 | Tamako | Hirosima | | 0123458 | Doremon | NULL | + + + + 10 rows in set (0.02 sec) mysql> TS. Trần Công Án (Khoa CNTT&TT) [CT428] Chương 5.1 – MySQL 39
  39. [CT428] Chương 5.1 – MySQL Các lệnh thao tác trên dữ liệu (DML) Kết nối dữ liệu giữa các bảng (Join) Kết Nối Dữ Liệu – RIGHT OUTER JOIN + + + + SELECT id, s.nameAS SName, p.name | id | SName | PName | AS PName + + + + FROM studentASs | 0123400 | Nobisuke | Hirosima | | 0123401 | Tamako | Hirosima | RIGHT OUTER JOIN province asp | 0123456 | Tom | Tokyo | ON s.province= p.province | 0123457 | Jerry | Tokyo | | 0123459 | Nobita | Kobe | | 0123460 | Xuka | Kagawa | | 0123461 | Chaien | Kagawa | | 0123462 | Dekhi | Kagawa | FTable JTable | 0123463 | Xeko | Kagawa | | NULL | NULL | Osaka | + + + + 10 rows in set (0.00 sec) mysql> TS. Trần Công Án (Khoa CNTT&TT) [CT428] Chương 5.1 – MySQL 40
  40. [CT428] Chương 5.1 – MySQL Các lệnh thao tác trên dữ liệu (DML) Kết nối dữ liệu giữa các bảng (Join) Kết Nối Dữ Liệu – Full OUTER JOIN (UNION) SELECT id, s.nameAS SName, p.name AS PName + + + + FROM studentASs | id | SName | PName | LEFT OUTER JOIN province asp + + + + ON s.province= p.province | 0123456 | Tom | Tokyo | UNION | 0123457 | Jerry | Tokyo | SELECT id, s.nameAS SName, p.name | 0123459 | Nobita | Kobe | | 0123460 | Xuka | Kagawa | AS PName | 0123461 | Chaien | Kagawa | FROM studentASs | 0123462 | Dekhi | Kagawa | RIGHT OUTER JOIN province asp | 0123463 | Xeko | Kagawa | ON s.province= p.province | 0123400 | Nobisuke | Hirosima | | 0123401 | Tamako | Hirosima | | 0123458 | Doremon | NULL | | NULL | NULL | Osaka | + + + + FTable JTable 11 rows in set (0.24 sec) mysql> TS. Trần Công Án (Khoa CNTT&TT) [CT428] Chương 5.1 – MySQL 41
  41. [CT428] Chương 5.1 – MySQL Các lệnh thao tác trên dữ liệu (DML) Các Câu Truy Vấn Lồng Nhau Các Câu Truy Vấn Lồng Nhau I Kết quả một câu truy vấn có thể được sử dụng trong mệnh đề điều kiện của một câu truy vấn khác. SELECT id, name, province + + + + FROM student | id | name | province | WHERE province NOTIN + + + + | 0123458 | Doremon | 0 | (SELECT province FROM province) + + + + 1 row in set (0.05 sec) + + + + mysql> SELECT id, s.name AS SName, s.province SELECT id, name, birthday | id | name | birthday | -> FROM student AS s FROM student + + + + -> LEFT OUTER JOIN province as p | 0123458 | Doremon | 2112-02-20 | WHERE birthday= -> ON s.province = p.province + + + + (SELECT MAX(birthday) FROM student) -> WHERE p.province IS NULL 1 row in set (0.00 sec) -> ; + + + + mysql> | id | SName | province | + + + + TS. Trần Công Án (Khoa CNTT&TT) [CT428] Chương 5.1 – MySQL| 0123458 | Doremon | 0 | 42 + + + + 1 row in set (0.09 sec) mysql>
  42. [CT428] Chương 5.1 – MySQL Các lệnh thao tác trên dữ liệu (DML) Giới hạn số mẩu tin trả về Giới Hạn Số Mẩu Tin Trả Về – LIMIT I Cú pháp: LIMIT[offset,] (Chọn tối đa number of rows mẩu tin, tính từ offset) I Thường được dùng trong phân trang kết quả truy vấn. + + + + SELECT id, name, birthday | id | name | birthday | + + + + FROM student | 0123400 | Nobisuke | NULL | LIMIT2 | 0123401 | Tamako | 1971-12-23 | + + + + 2 rows in set (0.02 sec) + + + + SELECT id, name, birthday mysql>| id select | name id, name,| birthday birthday | from student LIMIT 2,2; + + + + FROM student + + + + | id0123456 | nameTom | birthday1996-01-15 | LIMIT 2, 2 + + + +| 0123457 | Jerry | 2000-01-15 | |+ + + + 0123456 | Tom | 1996-01-15 | |2 0123457rows in |set Jerry (0.00 | 2000-01-15sec) | + + + + 2mysql> rows in set (0.00 sec) TS. Trần Công Án (Khoa CNTT&TT) [CT428] Chương 5.1 – MySQL 43 mysql>
  43. [CT428] Chương 5.1 – MySQL Các lệnh thao tác trên dữ liệu (DML) Cập nhật dữ liệu của table – UPDATE Cập Nhật Dữ Liệu Của Table – UPDATE I Cú pháp: UPDATE SET = [, = ]> [WHERE ] I Chú ý: Nếu không có điều kiện, tất cả các mẫu tin sẽ được c/nhật. UPDATE classics + + ++ + + | title | title | price | | price | SET price= price*0.9 + + ++ + + | The Adventures| The Adventures of Tom Sawyer of Tom | 90.00Sawyer | | 81.00 | | Pride and| PridePrejudice and Prejudice | 89.91 | | 80.92 | | The Origin| The of OriginSpecies of Species | 54.45 | | 54.45 | UPDATE classics | The Old |Curiosity The Old CuriosityShop Shop | 49.41 | | 44.47 | SET price= price*0.9 | Romeo and| RomeoJuliet and Juliet | 71.91 | | 71.91 | WHERE type= 'Fiction' + + ++ + + 5 rows in 5set rows (0.00 in setsec) (0.00 sec) mysql> mysql> TS. Trần Công Án (Khoa CNTT&TT) [CT428] Chương 5.1 – MySQL 44
  44. [CT428] Chương 5.1 – MySQL Các lệnh thao tác trên dữ liệu (DML) Xóa dữ liệu của table – DELETE Xóa Dữ Liệu Của Table – DELETE I Cú pháp: DELETE FROM [WHERE ] I Ví dụ: DELETE FROM classics WHERE year ý : Nếu không có điều kiện, tất cả các mẫu tin sẽ bị xóa. TS. Trần Công Án (Khoa CNTT&TT) [CT428] Chương 5.1 – MySQL 45
  45. [CT428] Chương 5.1 – MySQL Một số công cụ quản lý MySQL miễn phí Một Số Công Cụ Quản Lý MySQL MySQL Workbench, HeidiSQL, Toad, Eclipse (+plugin), myPHPQdmin, . . . TS. Trần Công Án (Khoa CNTT&TT) [CT428] Chương 5.1 – MySQL 46
  46. [CT428] Chương 5.1 – MySQL Một số công cụ quản lý MySQL miễn phí Một Số Công Cụ Quản Lý MySQL TS. Trần Công Án (Khoa CNTT&TT) [CT428] Chương 5.1 – MySQL 47
  47. [CT428] Chương 5.1 – MySQL Một số công cụ quản lý MySQL miễn phí Một Số Công Cụ Quản Lý MySQL TS. Trần Công Án (Khoa CNTT&TT) [CT428] Chương 5.1 – MySQL 48
  48. [CT428] Chương 5.1 – MySQL Các nội dung khác liên quan đến MySQL Một Số Nội Dung Khác Liên Quan Đến MySQL I Sử dụng ràng buộc (constraints): foreign key, cascade update/delete, . . . I Chỉ định bảng mã (character set), qui tắc sắp xếp (collation) cho dữ liệu của các table. I Tạo các thủ tục/hàm (stored procedure/function) và view. I Sử dụng bẫy sự kiện (trigger). I Quản lý quyền (prililege), bảo mật (security). I Sao lưu (backup) và phục hồi (recover) dữ liệu. TS. Trần Công Án (Khoa CNTT&TT) [CT428] Chương 5.1 – MySQL 49