Download MySQL Basic Practice and more Slides Database Management Systems (DBMS) in PDF only on Docsity!
THỰC HA(NH QUA,N TRỊ DƯ 0 LIE3̣U (CT 467 )
Nội dung thực hành
Nội dung thực hành của học phần này bao gồm 6 buổi :
- Giới thiệu MySQL
- MySQL nâng cao (transaction, trigger,…)
- Giới thiệu về đồ án và cơ sở lý thuyết để thực hiện đồ án (JDBC)
- Tìm hiểu và phân tích yêu cầu của đồ án, thiết kế CSDL, tạo CSDL
- Thiết kế giao diện, kết nối và xử lý dữ liệu
- Báo cáo kết quả đồ án
BUỔI 1 - GIỚI THIỆU MYSQL
Nội dung:
- Giới thiệu Hệ quản trị cơ sở dữ liệu MySQL
- Căn bản về MySQL
- Tạo các ràng buộc toàn vẹn trong MySQL Phần này sẽ giới thiệu ngắn gọn về hệ Quản trị CSDL MySQL. Chi tiết về hệ quản trị CSDL này có thể được tham khảo trong giáo trình Lập trình Web (CT428) hoặc bài giảng Lập trình Web (link: https://goo.gl/cRBapd).
1.1. Giới thiệu MySQL
MySQL là một trong những Hệ quản trị cơ sở dữ liệu (CSDL) quan hệ, mã nguồn mở thông dụng nhất hiện nay. Đây được đánh giá là một hệ quản trị có hiệu năng tốt, đáng tin cậy, tính dãn nở (scalability) cao, dễ sử dụng và đa nền (Unix, Linux, BSD, Windows,…). MySQL được sử dụng cho các ứng dụng có kiến trúc client/server và trong các hệ thống nhúng. Ngoài ra, đây cũng chính là hệ quản trị trong LAMP (Linux , Apache, MySQL, PHP/Perl/Python), một trong những nền tảng phát triển web thông dụng nhất hiện nay. Hiện nay, MySQL bao gồm 5 phiên bản (chi tiết về các phiên bản này có thể được tham khảo tại www.mysql.com/products):
- MySQL Enterprise Edition
- MySQL Standard Edition
- MySQL Classic Edition
- MySQL Cluster CGE
- MySQL Embedded Một số đặc điểm và tính năng chính của MySQL: Unicode support, ACID compliance, stored procedures , triggers, cursors, views, transactions and savepoints, query
caching, sub-SELECTs (i.e. nested SELECTs), replication support, full-text indexing and searching, clustering, support for hotbackup, commit grouping,…
1.2. Cài đặt MySQL
1.2.1. Trên hệ điều hành Linux (Ubuntu và Debian) Sử dụng công cụ quản lý package apt-get như sau: sudo apt-get install mysql-server 1.2.2. Trên hệ điều hành Windows Bộ cài đặt MySQL phiên bản cộng đồng (cimmunity, miễn phí) và các công cụ cho Windows có thể được download tại địa chỉ sau: https://dev.mysql.com/downloads/ Để cài đặt MySQL, thực thi tập tin MySQL installer (file .msi) và thực hiện theo các bước được hướng dẫn bởi bộ cài đặt.
1.3. Nối kết và làm việc với MySQL
Để nối kết và thao tác trên MySQL, có thể dùng công cụ dòng lệnh (shell), các công cụ giao diện đồ họa, hoặc các API (để nối kết và truy xuất từ các chương trình). 1.3.1. Giao diện dòng lệnh Từ giao diện dòng lệnh (Terminal trong Linux, hoặc Command Prompt trong Windows), gõ lệnh: mysql [-u [-p ]] Figure 1. Nối kết MySQL từ giao diện dòng lệnh Để ngắt nối kết với MySQL, ta gõ lệnh exit hoặc quit. Lưu ý, đối số - u cho phép chỉ định tài khoản người dùng và đối số - p cho phép khai báo mật khẩu dùng để nối kết đến MySQL. Ví dụ, để nối kết đến MySQL bằng người dùng root thì dùng lệnh: mysql – u root 1.3.2. Giao diện đồ họa Hiện có nhiều công cụ giao diện đồ họa giúp người dùng truy xuất MySQL thuận tiện hơn. Một số công cụ thông dụng và miễn phí như MySQL Workbench, HeidiSQL, Toad,
Giao diện của tiện ích này sau khi nối kết vào server: Công cụ này bao gồm các chức năng cho phép tạo CSDL, các đối tượng trong CSDL như table, procedure, view,… bằng các lệnh DDL hay qua giao diện trực quan. Ngoài ra còn cho phép tạo và thực thi các câu truy vấn.
Danh sách các CSDL
trên server
Trợ giúp các lệnh
Cửa sổ tạo
lệnh truy vấn
Tên nối kết
Địa chỉ/Cổng của server
Tên tài khoản
truy cập server
Schema mặc định
1.4. Căn bản về MySQL
1.4.1. Cú pháp dòng lệnh Một số quy tắc chính đối với cú pháp dòng lệnh của MySQL:
- Các lệnh MySQL từ dòng lệnh luôn kết thúc bằng dấu chấm phẩy ;
- Một lệnh có thể bao gồm nhiều dòng
- Một lệnh đang nhập có thể được hủy bằng cách thêm “\c” ở cuối dòng
- Các lệnh không phân biệt chữ hoa, chữ thường 1.4.2. Các lệnh trên cơ sở dữ liệu (DDL, Data Definition Language) Một server có thể quản trị nhiều CSDL. Một số lệnh cơ bản liên quan đến việc quản lý các CSDL:
- SHOW DATABASES : liệt kê các CSDL trên server
- USE <db_name> : chọn CSDL sẽ được sử dụng (làm việc)
- CREATE DATABASE <db_name> : tạo CSDL
- DROP DATABASE <db_name> : xóa CSDL
- ALTER DATABASE <db_name> : thay đổi thông số của CSDL Để thực hiện các lệnh này, người dùng cần có quyền CREATE DATABASE. Chi tiết về các lệnh này và một số lệnh DDL khác có thể được tham khảo tại website của MySQL: https://dev.mysql.com/doc/refman/5.7/en/sql-syntax-data-definition.html 1.4.3. Người dùng và quyền (user and permission)
- Trong MySQL, người dung root (được tạo ra lúc cài đặt) có quyền cao nhất.
- Muốn tạo người dùng trên server, ta dùng lệnh: CREATE USER
- Gán quyền cho người dùng: GRANT ON <database.object> TO ''@'' IDENTIFIED BY '' Nếu người dùng được gán quyền trong câu lệnh GRANT chưa có trong hệ thống thì người dùng này sẽ được tự động tạo ra. Hình sau minh họa cho việc một người dùng không thể tạo CSDL trên server do chưa được gán quyền.
Chi tiết về các kiểu DL: https://dev.mysql.com/doc/refman/5.7/en/data-types.html 1.4.5. Các lệnh DDL trên table Một số lệnh DDL cơ bản cho phép tạo và thay đổi cấu trúc table: 1.4.5.1. Tạo table – CREATE TABLE v Cú pháp cơ bản: CREATE TABLE <tab_name> ( <col_name1> [options], <col_name2> [options], ... [, PRIMARY KEY(col_name[,col_name,...])] ) Cú pháp đầy đủ: https://dev.mysql.com/doc/refman/5.7/en/create-table.html v Một số thông số (options) áp dụng cho các trường: o NOT NULL : trường không được rỗng. o DEFAULT : chỉ định giá trị mặc nhiên o AUTO_INCREMENT : giá trị tự động tăng dần. o UNSIGNED : số không dấu (số dương) o PRIMARY KEY : khóa chính. 1.4.5.2. Thay đổi cấu trúc table – ALTER TABLE Một số lệnh cơ bản để thay đổi cấu trúc table:
- ALTER TABLE <tb_name> RENAME <new_tb_name> : đổi tên table
- ALTER TABLE <tb_name> MODIFY <col_name> : đổi kiểu dữ liệu của các trường
- ALTER TABLE <tb_name> CHANGE <col_name> <new_name> : đổi tên trường
- ALTER TABLE <tb_name> ADD <col_name> : thêm trường
- ALTER TABLE <tb_name> DROP <col_name> : xóa trường
1.4.5.3. Một số lưu ý:
- Cần phải chọn CSDL (lệnh: USE <db_name> ) trước khi thực hiện các lệnh này.
- Lệnh để lấy danh sách các table trong CSDL: SHOW TABLES
- Để truy vấn cấu trúc table, dùng lệnh: DESCRIBE <tb_name>
- Để xóa table ra khỏi CSDL, dùng lệnh: DROP TABLE <tb_name> 1.4.6. Các lệnh thao tác trên dữ liệu DML Các lệnh DML cho phép thao tác trên dữ liệu như thêm dữ liệu vào table, xóa dữ liệu ra khỏi table, cập nhật giá trị các mẫu tin trong table,…:
- INSERT INTO…: thêm mẩu tin vào table INSERT [INTO] tbl_name [( col_name ,...)] {VALUES | VALUE} ({ expr | DEFAULT},...),(...),...
- DELETE FROM… : xóa mẩu tin khỏi table DELETE FROM tbl_name [WHERE where_condition ] [LIMIT row_count ]
SELECT [ALL | DISTINCT | DISTINCTROW ] select_expr [, select_expr ...] [FROM table_references [WHERE where_condition ] [GROUP BY { col_name | expr | position } [ASC | DESC], ... [WITH ROLLUP]] [HAVING where_condition ] [ORDER BY { col_name | expr | position } [ASC | DESC], ...] [LIMIT {[ offset ,] row_count | row_count OFFSET offset }] [INTO OUTFILE ' file_name ' [CHARACTER SET charset_name ] export_options | INTO DUMPFILE ' file_name ' | INTO var_name [, var_name ]] Các thông số cơ bản của lệnh này cũng tương tự như ANSI SQL. Ý nghĩa một số thông số đặc trưng của MySQL như sau:
- DISTINCTROW: từ khóa khác của DISTINCT
- WITH ROLLUP: yêu cầu sinh ra kết quả thống kê được nhóm theo nhiều cấp.
- INTO OUTFILE: yêu cầu lưu kết quả truy vấn vào file hoặc biến Một số hàm thông dụng trong MySQL:
- Hàm xử lý chuỗi: CONCAT(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), CONCAT_WS(sep, str1, str2, ...)
- Hàm xử lý dữ liệu ngày tháng: CURDATE(), DATE(expr), DATE_ADD(date, INTERVAL expr unit), DAY/MONTH/YEAR(date), DATENAME(date), DAYOFWEEK(date)
- Hàm xử lý dữ liệu giờ: CURTIME(), HOUR/MINUTE/SECOND(time), TIMEDIFF(expr1, expr2)
- Hàm toán học: ABS(expr), FLOOR(expr), MOD(expr), ROUND(expr) Ví dụ: chọn ra các sinh viên có tháng sinh là tháng hiện tại
1.4.8. Nối kết (join) các bảng 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). Có 3 lệnh để nối kết các table:
- NATURAL JOIN : tự động nối kết các table trong mệnh đề FROM dựa trên các trường cùng tên
- JOIN ON : nối kết các table ở mệnh đề FROM và JOIN dựa trên các trường chỉ định trong mệnh đề ON
- FROM WHERE <join_condition> : kết nối các table trong mệnh đề FROM sử dụng điều kiện trong mệnh đề WHERE Các nối kết được phân làm 4 loại: INNER JOIN, LEFT/RIGHT/FULL OUTER JOIN. Để minh họa cho các câu lệnh trong phần này, các table sau sẽ được sử dụng: Table Student : Table Province : 1.4.8.1. Inner join Đây là loại nối kết mặc định trong MySQL. Inner join trả về các mẩu tin có giá trị của trường được chỉ định nối kết bằng nhau như được mô tả trong hình sau:
1.4.8.3. Right outer join Ngược với left outer join, right outer join trả về các mẩu tin giao nhau giữa các table và các mẩu tin của table trong mệnh đề JOIN. Đối với các mẩu tin của table trong mệnh đề JOIN mà không giao với table trong mệnh đề FROM , các trường trong mệnh đề SELECT sẽ mang giá trị NULL. Hoạt động của loại nối kết này được minh họa bằng câu truy vấn và kết quả tương ứng như sau: 1.4.8.4. Full outer join (union) Kết quả của full outer join là sự kết hợp của left outer join và right outer join.
1.4.9. Các câu truy vấn lồng nhau Câu truy vấn lồng nhau có nghĩa là một câu truy vấn được sử dụng trong mệnh đề điều kiện của một câu truy vấn khác. Sau đây là 2 ví dụ về câu truy vấn lồng nhau:
1.5. Ràng buộc (Constraint) trong MySQL
Ràng buộc là các qui tắc (rule) do người dùng thiết đặt để cho phép hay ngăn cấm các giá trị có thể được lưu trữ trong các trường của một mẩu tin. Mục đích của việc thiết đặt các ràng buộc là để đảm bảo tính toàn vẹn của dữ liệu. Ràng buộc trong MySQL có thể được phân làm 2 mức:
- Ràng buộc mức cột: dùng để thiết đặt cho các trường.
- Ràng buộc mức table: áo dụng cho toàn bộ table. Các kiểu ràng buộc được hỗ trợ trong MySQL:
- NOT NULL : không cho phép chưa giá trị NULL.
- UNIQUE : không cho phép một trường của các mẩu tin có giá trị trùng nhau.
- PRIMARY KEY : ràng buộc khóa chính, không cho phép trùng nhau.
- FOREIGN KEY : ràng buộc khóa ngoại, bắt buộc giá trị khóa ngoại phải có giá trị khóa chính tương ứng.
- CHECK : dùng để chỉ định các giá trị cho phép được lưu trữ trong một trường.
- DEFAULT : dùng để chỉ định giá trị mặc nhiên cho một trường khi giá trị của trường không được cung cấp. Các ràng buộc được chỉ định trong câu lệnh CREATE TABLE hoặc ALTER TABLE. Cú pháp tạo table với các thông số liên quan đến ràng buộc bao gồm: CREATE TABLE [IF NOT EXISTS] tbl_name ( create_definition ,...) create_definition : col_name column_definition | [CONSTRAINT [ symbol ]] PRIMARY KEY ( index_col_name ,...) | [CONSTRAINT [ symbol ]] UNIQUE [ index_name ] [ index_type ] ( index_col_name ,...) | [CONSTRAINT [ symbol ]] FOREIGN KEY
ü isbn_no : số xuất bản, không được rỗng và không được trùng nhau ü pub_id : mã NXB, là khóa ngoại tham chiếu đến pub_id trong table Publisher ü date_of_pub : ngày xuất bản, giá trị phải đúng định dạng của ngày tháng năm ü no_page : số trang, giá trị phải > 0
1.6. Thực hành
Bài 1. Làm quen với MySQL bằng giao diện dòng lệnh. v Mục tiêu:
- Tìm hiểu giao diện dòng lệnh trên MySQL
- Thực hiện một số lệnh cơ bản trên giao diện dòng lệnh v Yêu cầu:
- Dùng giao diện dòng lệnh (trên Windows là Command Prompt, trên Linux và MacOS là Console hoặc Terminal), nối kết đến MySQL, không chỉ định tên người dùng ( mysql ¿). Lưu ý : Nếu gõ lệnh mysql bị báo lỗi có nghĩa là đường dẫn đến MySQL chưa được thiết đặt. Cần thiết đặt đường dẫn trước khi gọi hoặc đối với Windows thì sử dụng MySQL Command Line Client: Start\All Programs\MySQL Server…\MySQL Command Line)
- Liệt kê các CSDL trên server ( show databases; ¿).
- Tạo 1 CSDL tên là dbms trên server và cho nhận xét ( create database dbms; ¿).
- Thoát ra khỏi MySQL ( quit; ¿).
- Nối kết lại vào MySQL với người dùng root ( mysql – u root ¿)
- Thực hiện lại các yêu cầu 2) và 3) và giải thích sự khác nhau giữa 2 kết quả. Bài 2. Làm quen với MySQL bằng giao diện đồ họa, chương trình MySQL Workbench. v Mục tiêu:
- Làm quen với chương trình MySQL Workbench
- Thực hiện một số thao tác cơ bản trong MySQL Workbench v Yêu cầu:
- Tạo nối kết đến MySQL trên máy tính cục bộ bằng tài khoản root
- Thực hiện các yêu cầu tương tự Bài 1 nhưng bằng giao diện đồ họa Bài 3. Tạo table, chèn dữ liệu vào table v Mục tiêu:
- Ôn tập các lệnh DML và DDL. v Yêu cầu:
- Tạo một CSDL tên là QLDIEM và tạo các tables như sau:
- Dùng các câu lệnh SQL để thêm dữ liệu cho các table trên như sau: Học phần (hocPhan): maHP tenHP soTinChi soTietLT soTietTH CT101 Lập trình căn bản 4 30 60 CT176 Lập trình Hướng đối tượng 3 30 30 CT237 Nguyên lý Hệ điều hành 3 30 30 TN001 Vi tích phân A1 3 45 0 TN101 Xác suất thống kê 3 45 0 SP 102 Đại số tuyến tính 4 60 0 TN172 Toán rời rạc 4 60 0 Khoa: maKhoa tenKhoa ktqtkd Kinh tế và quản trị kinh doanh cntt&tt Công nghệ thông tin và Truyền thông khtn Khoa học tự nhiên khxh Khoa học xã hội sp Sư phạm Sinh viên (sinhVien): mssv hoten ngaySinh noiSinh diaChi maKhoa B1234567 Nguyễn Thanh Hải M 2001 - 12 - 02 Bạc Liêu Phòng 01, KTX Khu B, ĐHCT cntt&tt B1234568 Trần Thanh Mai M 2001 - 01 - 20 Cần Thơ 232, Nguyễn Văn Khéo, Q. Ninh Kiều, TPCT cntt&tt B1234569 Trần Thu Thủy F 2001 - 07 - 01 Cần Thơ 02, Đại lộ Hòa Bình, Q. Ninh Kiều, TPCT cntt&tt B1334569 Nguyễn Thị Trúc Mã F 2002 - 05 - 25 Sóc Trăng 343, Đường 30/4, Q/ Ninh Kiều, TPCT sp B1345678 Trần Hồng Trúc F 2002 - 03 - 02 Cần Thơ 123, Trần Hưng Đạo, Q. Ninh Kiều, TPCT cntt&tt B1345679 Bùi Hoàng Yến F 2001 - 11 - 05 Vĩnh Long Phòng 201, KTX Khu A, TPCT cntt&tt
Bài 6. (Bài tập làm thêm) Cho lược đồ CSDL bao gồm các quan hệ đại lý ( daily ), hàng hóa ( hangHoa ), mua ( mua ) và bán ( ban ) như sau, hãy tạo các sơ đồ và thực hiện các câu lệnh truy vấn để trích xuất các thông tin theo yêu cầu:
- Thực hiện một số câu truy vấn sau: a. Liệt kê các giao dịch bán hàng gồm mã hàng, tên hàng, mã đại lý, ngày bán, số lượng và thành tiền. b. Thống kê giá trị bán theo từng tháng c. Thống kê giá trị bán theo đại lý, theo từng tháng d. Thống kê chênh lệch giữa giá trị mua và bán theo từng tháng.
- Thiết đặt ràng buộc sau: a. Giá trị soLuongBan, giaTriBan, soLuongMua, giaTriMua luôn > 0. b. Giá trị ngayBan và ngayMua >= ngày hiện tại.
