Bài giảng Lab 7: MySQL

LAB 7: MySQL

1. Mục tiêu

- Sử dụng thành thạo phpmyadmin để tạo và quản trị CSDL với MySQL

- Tạo view và store procedure

- Tạo một user và gán quyền sử dụng cho user

- Sao lưu và phục hồi

2. Tóm tắt lý thuyết

 Cài đặt phpmyadmin

Download phpmyadmin từ https://www.phpmyadmin.net/downloads/

Giải nén vào thư mục gốc web server

 Quản trị CSDL

Các bước tạo CSDL bằng phpmyadmin

Bước 1:

Mở phpmyadmin bằng cách vào localhost và chọn phpmyadmin… Sau khi

đăng nhập với tài khoản mặc định (username: root, password: root)

Bên trái là danh sách các cơ sở dữ liệu, giữa là các thiết lập chung và bên phải

là các thông tin về MySQL server và Apache server.

Bước 2: Tạo mới CSDL tên bookstore

- Click vào liên kết Database

- Nhập bookstore vào ô Database name và chọn Collation là utf8_general_ci

- Tạo table loại (loại sách): có 2 cột maloai và tenloai

- Điền các thông tin vào bảng như sau

- Sau khi lưu sẽ được bảng loai như sau:

Bước 3: Tạo ràng buộc

Sau khi tạo xong các bảng, chúng ta sẽ tạo ràng buộc khóa ngoại cho các

bảng. Ví dụ bảng sách có 1 khóa ngoại đến bảng loai thông qua maloai và 1

khóa ngoại đến bảng nhaxb thông qua manxb. Cách thực hiện như sau:

 Mở bảng Sách chế độ xem cấu trúc (structures)

 Click link Relation View

 Điền các thông tin  Save

Bước 4: Thêm dữ liệu

Lưu ý: Các bảng có ràng buộc khóa ngoại thì phải thêm dữ liệu trong bảng

có quan hệ 1 trước rồi đến bảng quan hệ nhiều sau

Chọn table cần thêm dữ liệu sau đó chọn link insert

- Sửa dữ liệu

Mở bảng cần sửa và click chọn vào Edit

Thay đổi dữ liệu và click Go

- Xóa 1 dòng trong bảng

Chọn bảng cần xóa  click vào Delete trên dòng dữ liệu muốn xóa

- Xóa bảng khỏi CSDL

Click vào tên CSDL  click Drop vào bảng cần xóa

- Xóa CSDL

Click link Database chọn CSDL cần xóa click Drop (dưới cùng)

 View

Tạo view

CREATE VIEW ten_view AS SELECT …

Sửa view

ALTER VIEW ten_view AS SELECT …

Xóa view

DELETE VIEW ten_view

 Procudure

Tạo procedure

CREATE PROCEDURE ten_pro([danh_sach_tham_so])

BEGIN

//khối lệnh

END

Ghi chú:

Tham số có 3 loại:

- IN: tham số đầu vào

- OUT: tham số đầu ra

- INOUT: tham số vừa là đầu vào vừa là đầu ra.

Ví dụ:

Thủ tục sau đếm số lượng các quyển sách có giá >= giá đầu vào

CREATE PROCEDURE demsach(IN g INT, OUT sl INT)

BEGIN

SELECT COUNT(*) INTO sl FROM sach WHERE gia>=g;

END

Sửa procedure

Nên xóa và tạo lại

Xóa procedure

DROP PROCEDURE ten_pro

 Tạo user

Vào Privileges  Add user  Điền các thông tin, chọn CSDL và các quyền

 Go

 Sao lưu

Chọn CSDL muốn sao lưu Export

- Nếu sao lưu nhanh (sử dụng các thiết lập mặc định) thì chọn Export

Method là Quick rồi click Go

- Nếu muốn thiết lập lại để cho biết sao lưu gì thì chọn Custom  Chọn

những gì cần sao lưu  Go

 Phục hồi

Chọn CSDL cần phục hồi  Import  Chọn file sql  Go

3. Ví dụ

3.1 Tạo ràng buộc khóa ngoại giữa bảng sach và bảng loai

- Mở bảng sách ở chế độ structure rồi click vào Relation view. Nếu dùng

phpmyadmin 3.3 thì như hình sau:

Nếu dùng phpmyadmin v4 thì như hình sau:

Chúng ta muốn tạo ràng buộc khóa ngoại giữa bảng sach và bảng loai dựa

vào maloai nhưng không thể tạo được vì maloai chưa phải là Index

- Tạo index cho maloai

Mở bảng sach chế độ structure  Click More  Chọn Add Index trên cột

Maloai.

- Sau khi tạo Index thì vào lại Relation view sẽ thấy như sau:

3.2 Tạo view liệt kê các sách chưa bán được quyển nào

CREATE VIEW sache AS

SELECT * FROM `sach` WHERE masach NOT IN (SELECT masach

FROM chitiethd)

3.3 Tạo procedure để lấy giá của một quyển sách

CREATE PROCEDURE giasach(IN id VARCHAR(15),OUT g INT)

BEGIN

SELECT gia INTO g FROM sach WHERE masach=id;

END

4. Vận dụng

4.1 Tạo CSDL bookstore và bảng loai bằng phpmyadmin như hướng dẫn của

phần 2.

4.2 Tạo các bảng còn lại để hoàn thành CSDL bookstore như trong hình sau:

4.3 Tạo ràng buộc cho các khóa ngoại còn lại

4.4 Sửa cột trangthai của bảng hoadon để có giá trị mặc định là 0.

4.5 Tạo view cho biết 10 sách có giá cao nhất

4.6 Tạo procedure để liệt masach, tensach của 1 loại khi biết mã loại

5. Nâng cao

5.1 Viết câu truy vấn tất cả các quyển sách được nhóm theo từng loại gồm các

thông tin sau: masach, tensach, maloai, tenloai.

5.2 Tạo procedure dùng để cập nhật giá 1 quyển sách

5.3 Viết câu truy vấn cho biết sách nào bán chạy nhất

5.4 Tạo view cho biết 10 quyển sách bán chạy nhất

5.5 Sao lưu CSDL bookstore

5.6 Xóa CSDL bookstore.

5.7 Phục hồi CSDL bookstore