tiểu luận môn học cơ sở dữ liệu đề tài trình bày nội dung thực hành hàng tuần

Bạn đang xem bản rút gọn của tài liệu. Xem và tải ngay bản đầy đủ của tài liệu tại đây (6.78 MB, 39 trang )

<span class="text_page_counter">Trang 1</span><div class="page_container" data-page="1">

<b>TRƯỜNG ĐẠI HỌC BÁCH KHOA HÀ NỘI VIỆN TOÁN ỨNG DỤNG VÀ TIN HỌC </b>

</div><span class="text_page_counter">Trang 2</span><div class="page_container" data-page="2">

<b>Mục L c </b>ụ

<b>Tuần 1: Thiết lập môi trường làm việc MySQL ... 4 </b>

Bài 1: Thiết lập được hệ quản trị cơ sở dữ liệu trên máy tính cá nhân ... 4

Bài 2: Tạo được một cơ sở dữ liệu mẫu để làm việc ... 4

Bài 1: SELECT, ORDER BY, WHERE, SELECT DISTINCT ... 5

Bài 3: BETWEEN, LIKE, LIMIT... 6

Bài 4: IS NULL, Table & Column Aliases ... 7

Bài 5: JOINS, INNER JOIN, LEFT JOIN, RIGHT JOIN, SELF JOIN,CROSS JOIN ... 9

Bài 6: GROUP BY, HAVING, ROLLUP ... 11

Bài 7: Subquery, Derived Tables, EXISTS ... 12

Bài 8: UNION, MINUS, INTERSECT... 15

Bài 1: Xây dựng lược đồ (Diagram) ... 17

Bài 2: Truy vấn các bảng hệ thống trong cơ sở dữ liệu ... 18

TUẦN 4: THỰC HÀNH XÂY DỰNG ĐƯỢC CẤU TRÚC CỦA MỘT CƠ SỞ DỮ <b>LIỆU ... 19 </b>

Bài 1: Thiết lập bảng dữ liệu (PK, UK, DataType, Null, constraint) ... 19

Bài 2: Thiết lập quan hệ dữ liệu (Relationship) ... 19

Bài 3: Thiết lập khung nhìn (Views)... 20

Bài 4: Thiết lập thủ tục và hàm (procedure & function) ... 20

TUẦN 5: THỰC HÀNH CÁC CÂU LỆNH THAO TÁC DỮ LIỆU (PHẦN MYSQL <b>DATA MANIPULATION) ... 22 </b>

Bài 1: Thực hiện các câu lệnh cập nhật (insert,update, delete dữ liệu) ... 22

Bài 2: Cập nhật dữ liệu từ 1 bảng excel qua sinh tự động SQL Script ... 22

Bài 3: Cập nhật dữ liệu từ một bảng khác... 24

Bài 4: Cập nhật dữ liệu qua một procedure ... 24

<b>TUẦN 6: THỰC HÀNH CÁC CÂU LỆNH SỬ DỤNG HÀM TRONG TRUY VẤN ... 26 </b>

Bài 1: Aggregate Functions, Math Functions, Comparison Functions ... 26

Bài 2: Control Flow Functions and Expressions ... 27

Bài 3: Window Functions ... 28

Bài 4: Date Functions, String Functions ... 28

Bài 5: Đánh chỉ mục dữ liệu một bảng ... 29

<b>TUẦN 7: THỰC HÀNH THIẾT KẾ CƠ SỞ DỮ LIỆU LƯU TRỮ ĐƠN HÀNG ... 31 </b>

</div><span class="text_page_counter">Trang 3</span><div class="page_container" data-page="3">

Bài 1 + 2 : Xây dựng lược đồ R-E và E-R ... 31

Bài 3: Thiết kế trên hệ quản trị cơ sở dữ liệu ... 31

Bài 4: In diagram cơ sở dữ liệu ... 31

Bài 5: Viết câu lệnh thêm dữ liệu và bảng ... 32

Bài 6: Viết câu lệnh truy vấn Thay đổi cấu trúc CSDL ... 32

Bài 7: Thay đổi cấu trúc CSDL... 33

Bài 1: Start, Stop, and Restart MySQL Server ... 37

Bài 2: Users, Roles, and Privileges ... 37

Bài 3: Show commands ... 39

Bài 4: Backup and Restore ... 40

Bài 5: Database maintenance ... 40

</div><span class="text_page_counter">Trang 4</span><div class="page_container" data-page="4">

<b>Tuần 1: Thiết l</b>ập môi trườ<b>ng làm việc MySQL </b>

<b>Bài 1: Thi t lế ập được hệ quản trị cơ sở ữ liệu trên máy tính cá nhân d</b>

Em sử dụng hệ quản trị cơ sở dữ liệu MySQL.

<b>Bài 2: T</b>ạo đượ<b>c một cơ sở dữ liệu mẫu để làm việc </b>

Cơ sở dữ liệu mẫu được dùng ở đây được download ở: Cơ sở dữ liệu mẫu này có tên <b>classicmodels</b>. Bao gồm các bảng sau:

- Customers: Lưu trữ dữ liệu của khách hàng. - Products: Lưu trữ kích cỡ các xe mẫu.

- ProductLines: Lưu trữ các danh mục dòng sản phẩm - Orders: Lưu trữ các đơn bán hàng do khách hàng đặt - OrderDetails: Lưu trữ các chi tiết của mỗi đơn hàng - Payments: Lưu trữ các khoản thanh tốn

- Employees: Lưu trữ tất cả thơng tin của nhân viên - Offices: Lưu trữ dữ liệu bán hàng

<b>Input: </b>Câu lệnh chọn bảng Customers.

<b>Output: Bảng </b>Customers từ cơ sở dữ liệu mẫu <b>Classicmodels. Mã nguồn: </b>

USE classicmodels;

SELECT * FROM customers;

</div><span class="text_page_counter">Trang 5</span><div class="page_container" data-page="5">

<b>Tuần 2: Th c hành truy v n SQL </b>ựấ

<b>Bài 1: SELECT, ORDER BY, WHERE, SELECT DISTINCT </b>

- SELECT: Cho phép bạn chọn dữ liệu từ một hoặc nhiều bản. - ORDER BY: Sắp xếp lại các kết quả tìm được.

- WHERE: Thêm điều kiện tìm kiếm cho các kết quả được trả về. - SELECT DISTINCT: Loại bỏ các kết quả trùng lặp.

<b>Input: Các câu lệnh sử dụng SELECT, ORDER BY, WHERE, SELECT DISTINCT. Output: Từ cơ sở dữ liệu Classicmodels, chọn ra các kết quả không trùng lặp từ các </b>

trường state, city, country trong bảng Customers với điều kiện state không để trống và sắp xếp theo state.

<b>Mã nguồn: </b>

USE classicmdodels;

SELECTDISTINCT state, city, country, FROM customers;

WHEREstateIS NOT NULLORDERBYstate

<b>Bài 2: AND, OR, IN </b>

</div><span class="text_page_counter">Trang 6</span><div class="page_container" data-page="6">

6 AND: cho phép tồn tại nhiều điều kiện trong câu lệnh.

OR: dùng để kết hợp nhiều điều kiện trong một câu lệnh.

IN: Khai báo một tập hợp các giá trị, nếu dữ liệu cần tìm trùng khớp với giá trị khai báo thì hợp lệ.

<b>Input: câu lệnh có sử dụng AND, OR, IN </b>

<b>Output: </b>Từ cơ sở dữ liệu Classicmodels, chọn ra các kết quả từ trường officeCode, city, phone, country, state có trong bảng Offices, với điều kiện Country là USA hoặc Japan và officeCode < 6.

<b>Mã nguồn: </b>

USE classicmdodels;

SELECT officeCode, city, phone, country, state FROM offices,

WHERE country IN ('USA' OR 'JAPAN') AND officeCode < 6

<b>Bài 3: BETWEEN, LIKE, LIMIT </b>

BETWEEN: Kiểm tra xem một giá trị có nằm trong phạm vi khơng. LIKE: truy xuất dữ liệu dựa theo quy tắc tìm kiếm.

LIMIT: hạn chế số lượng kết quả tìm được.

<b>Input: </b>các câu lệnh chứa BETWEEN, LIKE, LIMIT.

</div><span class="text_page_counter">Trang 7</span><div class="page_container" data-page="7">

<b>Output: </b>Từ cơ sở dữ liệu classicmodels, chọn ra các kết quả từ trường

customerNumber, customerName, creditLimit, postalCode trong bảng customers với điều kiện customerName và creditLimit trong khoảng từ 100000 đến 400000 Mã nguồn:

USE classicmdodels;

SELECT customerNumber, customerName, creditLimit,postalCode FROM customers,

WHERE customerName LIKE '%a%' AND (creditLimit BETWEEN 100000.00 AND 400000.00)

ORDERBY customerNumber LIMIT200

Giao diện kết quả:

<b>Bài 4: IS NULL, Table & Column Aliases </b>

IS NULL: Kiểm tra xem giá trị có phải NULL khơng ALIAS: Dùng để định danh tên mới cho cột hoặc bảng.

<b>Input: câu lệnh sử dụng IS NULL và ALIAS </b>

<b>Output: Từ cơ sở dữ liệu classicmodels, chọn ra các kết quả từ trường </b>

contactFirstName, contactLastName, state từ bảng customers với điều kiện state khác NULL.

Mã nguồn:

</div><span class="text_page_counter">Trang 9</span><div class="page_container" data-page="9">

<b>Bài 5: JOINS, INNER JOIN, LEFT JOIN, RIGHT JOIN, SELF JOIN,CROSS JOIN </b>

INNER JOIN: Ghép hàng trong một bảng với mọi hàng trong bảng khác.LEFT JOIN: Chọn dữ liệu từ bảng trái ghép với mọi hàng trong bảng phải. RIGHT JOIN: Chọn dữ liệu từ bảng phải ghép với mọi hàng trong bảng trái. SELF JOIN: Dùng để nối một bảng với chính nó.

<b>Input: </b>Các câu lệnh chứa mệnh đề JOINMã nguồn:

USE classicmodels;

SELECT customerNumber, customerName, phone FROM customers

CROSSJOIN orderdetails

USE classicmodels;

SELECT orderNumber, orderDate, requiredDate, shippedDate FROM orders

INNERJOIN orderdetails USING (orderNumber)

INNERJOIN products USING (productCode)

ORDERBY orderNumber

USE classicmodels;

SELECT customerNumber, customerName, phone FROM customers

LEFT JOIN orders USING (customerNumber);

</div><span class="text_page_counter">Trang 10</span><div class="page_container" data-page="10">

ORDERBY salesRepEmployeeNumber Giao diện kết quả:

</div><span class="text_page_counter">Trang 11</span><div class="page_container" data-page="11">

<b>Bài 6: GROUP BY, HAVING, ROLLUP </b>

<b>- Group By: Hợp các câu lệnh để sắp xếp dữ liệu giống nhau thành một nhóm. - Having: Chỉ định điều kiện lọc mà kết quả nhóm xuất hiện trong kết quả. </b>

Mã nguồn: USE classicmodels;

SELECT city, COUNT(customerName) AS total_customers FROM customers

GROUPBY city

ORDERBY city

</div><span class="text_page_counter">Trang 12</span><div class="page_container" data-page="12">

USE classicmodels;

SELECT city, COUNT(customerName) AS total_customers FROM customers

GROUPBY city WITHROLLUPHAVINGcount(customerName) >=3

Giao diện kết quả:

<b>Bài 7: Subquery, Derived Tables, EXISTS </b>

<b>- Subquery: truy vấn lồng vào bên trong mệnh đề Where. </b>

</div><span class="text_page_counter">Trang 13</span><div class="page_container" data-page="13">

<b>- Derived Tables: Bảng được trả về từ câu lệnh Select. - Exists: Kiểm tra sự tồn tại. </b>

Mã nguồn: USE classicmodels;

SELECT customerNumber, customerName, creditLimit, (CASE

WHEN creditLimit < 10000THEN 'Bronze'

WHEN creditLimit BETWEEN 10000 AND100000THEN 'Silver' WHEN creditLimit > 100000THEN 'Gold'

SELECT customerNumber, checkNumber, amount FROM payments

WHERE amount =(SELECTMAX (amount) FROM payments) Giao diện kết quả:

</div><span class="text_page_counter">Trang 14</span><div class="page_container" data-page="14">

</div><span class="text_page_counter">Trang 15</span><div class="page_container" data-page="15">

<b>Bài 2: Truy v n các b ng h th</b>ấảệ<b>ống trong cơ sở dữ liệu a) Tên các bảng </b>

<b>Mã nguồn: </b>

USE classicmodels;

SELECTTABLE_NAMEFROM information_schema.tables

WHERE table_schema = 'classicmodels' ORDERBY TABLE_NAME

<b>b) Tên các cột của một bảng và tính chất các cột đó Mã nguồn: </b>

SELECT *FROM information_schema.columns

WHERE table_schema = 'classicmodels'

ORDERBYTABLE_NAME, ordinal_position

<b>Giao diện kết quả: </b>

</div><span class="text_page_counter">Trang 16</span><div class="page_container" data-page="16">

<b>MỘT CƠ SỞ DỮ LIỆU</b>

<b>Bài 1: Thi t l p b</b>ế ậ<b>ảng d li u (PK, UK, DataType, Null, constraint) </b>ữ ệ

<b>Bài 2: Thi t l p quan h d li u (Relationship) </b>ế ậệ ữ ệ

<b>Mã nguồn: </b>

CREATEDATABASE BaiTuan4;

CREATETABLE BaiTuan4.Persons (personID int NOTNULL, name VARCHAR(255) NOT NULL, age , address int VARCHAR(255) NOTNULL, city VARCHAR(255), city VARCHAR(255), CONSTRAINT uc_name_address UNIQUE (name, address),

PRIMARYKEY (personID));

CREATETABLE BaiTuan4.Orders (orderID intNOT NULL, OrderNumber intNOT

NULL, PersonID intNOT NULL, PRIMARYKEY (orderID), FOREIGNKEY (personID) REFERENCES Persons (PersonID));

INSERTINTO BaiTuan4.Persons (personID, name, age, address, city)

VALUES ('101','Hoang','19','Cau Giay','Ha Noi'), ('102','Quang','21','Tay Ho','Ha Noi'), ('103','Huy','17','Tu liem','Ha Noi'), ('104','Mai','19','Cau Giay' 'Ha Noi'), , ('105','Nhat' '19',, 'Hoan Kiem' 'Ha Noi'); ,

SELECT *FROM BaiTuan4.Persons ORDERBY personID

</div><span class="text_page_counter">Trang 17</span><div class="page_container" data-page="17">

<b>Bài 3: Thi t l p khung nhìn (Views) </b>ế ậ

<b>Mã nguồn: </b>

CREATEVIEW members AS

SELECT CONCAT(name, , age) ',' AS 'profile', CONCAT(address, , city) ',' AS 'location'

FROM baituan4.persons;

SELECT *FROM members

<b>Bài 4: Thi t l p th</b>ế ậủ ụ<b> t c và hàm (procedure & function) </b>

<b>Mã nguồn: </b>

</div><span class="text_page_counter">Trang 19</span><div class="page_container" data-page="19">

<b>Bài 1: Th c hi n các câu l nh c p nh t (insert,update, delete d li u) </b>ựệệậậữ ệ

<b>Mã nguồn: </b>

INSERTINTO huy.order (orderID, orderNumber)

VALUES ( , '3' '10');

UPDATE huy.orderSET orderNumber = 3 WHERE orderID = 3

DELETEFROM huy.orders

<b>Bài 2: C p nh t d</b>ậậ<b>ữ li u t 1 b</b>ệừ<b>ảng excel qua sinh t ng SQL Script </b>ự độ

<b>Mã nguồn: </b>

USE huy;

DELETEFROM huy.order;

INSERTINTO huy.order (orderId, orderNumber, personId)

</div><span class="text_page_counter">Trang 20</span><div class="page_container" data-page="20">

INSERTINTO huy.order (orderId, orderNumber, personId)

VALUES ('5','555' '567', );

UPDATE huy.order

SET personID = 1 WHERE orderid = 1

UPDATE huy.orderSET personID = 2 WHERE orderid = 2;

UPDATE huy.orderSET personID = 3 WHERE orderid = 3;

UPDATE huy.orderSET personID = 4 WHERE orderid = 4;

UPDATE huy.orderSET personID = 5

WHERE orderid = 5

</div><span class="text_page_counter">Trang 21</span><div class="page_container" data-page="21">

INNERJOIN orderdetails ON orders.ordernumber = orderdetails.ordernumber

SET status ='sale';

<b>Bài 4: C p nh t d</b>ậậ<b>ữ li u qua m t procedure </b>ệộ

INSERTINTO huy.order (orderid, ordernumber, personid)

VALUES (orderid, ordernumber, personid); END //

DELIMITER ;

CALL insertid_ ('11' '11' '111', , );

SELECT *FROM huy.order

<b>Giao diện kết quả: </b>

</div><span class="text_page_counter">Trang 22</span><div class="page_container" data-page="22">

</div><span class="text_page_counter">Trang 25</span><div class="page_container" data-page="25">

CREATETABLE scores(name VARCHAR(20) PRIMARYKEY score INTNOTNULL);

INSERTINTO score(name, score)

VALUES ('Smith',81), ('Jones',55), ('Williams',55),('Taylor',62),('Brown', 62), ('Davies', ), ('Evans', ), ('Wilson', ); 84 87 72

<b>Bài 4: Date Functions, String Functions </b>

USE classicmodels;

</div><span class="text_page_counter">Trang 27</span><div class="page_container" data-page="27">

EXPLAINSELECT employeeNumber, lastName, firstName FROM employees

WHERE jobTitle = 'Sales Rep';

SHOW INDEXES FROM employees;

</div><span class="text_page_counter">Trang 28</span><div class="page_container" data-page="28">

<b>ĐƠN HÀNG </b>

<b>Bài 1 + 2 : Xây dựng lược đồ R-E và E-R </b>

<b>Bài 3: Thi t k trên h qu n tr</b>ếếệả<b>ị cơ sở ữ li u d</b>ệ

<b>Bài 4: In diagram cơ sở dữ liệu </b>

</div><span class="text_page_counter">Trang 29</span><div class="page_container" data-page="29">

<b>Bài 5: Vi t câu l nh thêm d li u và b ng </b>ếệữ ệả

<b>Bài 6: Vi t câu l nh truy v</b>ếệ<b>ấn Thay đổi c u trúc CSDL </b>ấ

</div><span class="text_page_counter">Trang 31</span><div class="page_container" data-page="31">

<b>TUẦN 8 </b>

Bài 1:

a) D → EG ➔ AD → EG và G → B ➔ EG → B ➔ AD <b>→ </b> BAD → B ➔ AD → AB và AB → C ➔ AB → BC ➔ AD → BC b) {A}<sup>+</sup> = {A, B, C, D, E, G}

c) Phân rã các phụ thuộc hàm có nhiều thuộc tính

F<sub>1</sub> = {AB → C, AC → D, D → E, D → G, G → B, A → D, CG → A}

</div><span class="text_page_counter">Trang 32</span><div class="page_container" data-page="32">

35 Loại các thuộc tính dư thừa

AB → C loại B

F<sub>2</sub> = { A → C, AC → D, D → E, D → G, G → B, A → D, CG → A} AC → D loại C

F<sub>3</sub>= { A → C, A → D, D → E, D → G, G → B, A → D, CG → A} CG → A loại G

F<sub>4</sub>= { A → C, A → D, D → E, D → G, G → B, C → A} Một phủ tối thiếu của F là F<sub>4</sub>.

Bài 2:

a) AB → G, G → H ➔ AB → H

GH → A, A → C và G → H ➔ GH → H ➔ GH → C b) {G}<sup>+</sup> = {A, C, G, H}

Bài 3:

a) I → M ➔ HI → HM mà HN → G ➔ HI → G GH → L, L -> K ➔ GH → GK

GK → I, I → M ➔ GK → M

GH → M và GH → L ➔ GH → KM b) {G,H}<sup>+</sup> = { G, H, L, K, I, M}

Loại các thuộc tính dư thừa, HM → G loại được M vì

{ }H <small>+</small> = {H, G, L, K, I, M} có F<small>1</small> = {GH → L, I → M, L → K, H G, GK → → I, H → L}

Loại bỏ các thuộc tính dư thừa, bỏ được GH → L

F chứa tối thiểu . Một phủ tối thiểu của F là {I → M, L → K, H → G, GK → I, H → L}

Bài 4:

a) Tập nguồn {H} Tập trung gian {I, K, N} Một khóa tối thiểu là {H, I} b) Ta có bảng:

</div><span class="text_page_counter">Trang 33</span><div class="page_container" data-page="33">

{BG}<small>+</small>= BDEGHAC ➔ {B.G} là khóa tối thiểu của quan hệ R{BH}<small>+</small>= BHDE

</div><span class="text_page_counter">Trang 34</span><div class="page_container" data-page="34">

<b>Bài 1: Start, Stop, and Restart MySQL Server </b>

- Start:

- Stop & Restart:

<b>Bài 2: Users, Roles, and Privileges </b>

- Create User:

</div><span class="text_page_counter">Trang 35</span><div class="page_container" data-page="35">

- Grant Privileges:

- Manage Roles:

</div><span class="text_page_counter">Trang 36</span><div class="page_container" data-page="36">

<b>Bài 3: Show commands </b>

- Show Databases and Tables:

- Show Columns:

</div><span class="text_page_counter">Trang 37</span><div class="page_container" data-page="37">

<b>Bài 4: Backup and Restore </b>

<b>Bài 5: Database maintenance </b>

<b> </b>

</div><span class="text_page_counter">Trang 38</span><div class="page_container" data-page="38">

Trong môn học này, ngoài nh ng ki n thữ ế ức đã được thầy gi ng d y trong ti t hả ạ ế ọc thì những bài th c hành hàng tu n thự ầ ầy giao cũng giúp em biết thêm r t nhi u v h c ấ ề ề ọphần Cơ sở dữ liệu này.

- Thực hành các câu lệnh s d ng hàm trong truy vử ụ <b>ấn. </b>

- Thiết k <b>ế cơ sở ữ ệu lưu trữ. </b> d li

- Luyện t p v ph thu c hàm trong h qu n trậ ề ụ ộ ệ ả <b>ị cơ sở ữ ệu. </b> d li- Quản trị <b>cơ sở ữ ệ </b> d liu.

</div><span class="text_page_counter">Trang 39</span><div class="page_container" data-page="39">