11 trang 2 lượt tải

Hướng Dẫn Truy Vấn Cơ Bản về CSDL | Hệ thống máy tính | Trường Đại học Khoa học Tự nhiên, Đại học Quốc gia Hà Nội

3

Cài đặt cở sở dữ liệu mẫu Cơ sở dữ liệu mẫu bao gồm các bảng sau: • Customers: Lưu trữ thông tin về khách hàng. • Products: Lưu trữ danh sách về các sản phẩm. • ProductLines: Lưu trữ danh mục các loại sản phẩm • Orders: Lưu trữ các đơn hàng được đặt bởi các khách hàng. • OrderDetails: Lưu trữ về chi tiết các dòng đơn hàng • Payments: Lưu trữ các thanh toán của khách hàng • Employees: Lưu trữ thông tin về các nhân viên của tổ chức • Offices: Lưu thông tin về các văn phòng của tổ chức. Tài liệu được sưu tầm và soạn thảo dưới dạng file PDF để gửi tới các bạn cùng tham khảo, ôn tập đầy đủ kiến thức, chuẩn bị cho các buổi học thật tốt. Mời bạn đọc đón xem!

Môn: Hệ thống máy tính (HUS) 10 tài liệu

Trường: Trường Đại học Khoa học tự nhiên, Đại học Quốc gia Hà Nội 687 tài liệu

Tác giả:

Profile

Mai Nguyệt

1 tuần trước
Danh sách Quiz
/11
28#
#
Bài thực hành số 3
Truy vấn cơ bản (phần 1)
v Nội dung chính
- Câu l nh Selectệ : cú pháp và cách sử dụng
- Mệnh đề where
- Loại bỏ dữ liệu k p vế ả ặt qu trùng l ới DISTINCT
1. Cài đặt cở s d liở ữ ệu mẫu
Cơ s dở ữ li u m u bao gệ ẫ ồm các b ng sauả :
• Customers: L ưu trữ thông tin v khách hàng.ề
• Products: L ưu tr n phữ danh sách v các sề ả ẩm.
• ProductLines: L m ưu tr n phữ danh mục các lo i sạ ả ẩ
• Orders: L u tr t bư ữ đơ đượ đặ các n hàng c ởi các khách hàng.
• OrderDetails: L u tr v chi tiư ữ ề ế đơt các dòng n hàng
• Payments: L ưu trữ các thanh toán của khách hàng
• Employees: L c ưu trữ thông tin v các nhân viên cề ủa tổ chứ
• Offices: L c. ưu thông tin v n phòng cề các vă ủa tổ chứ
Hình dưới minh h gi li s d liọ ố ệa m i quan h ữ ảa các b ng dữ ệu trong cơ ở ữ ệu
29#
#
Tải file script sampledatabase.sql để t t ạo CSDLvề ừ địa chỉ:
http://www.mysqltutorial.org/mysql-sample-database.aspx
Giả s ử file sampledatabase.sql được đặt trong th mư ục g c ố ổ C:
Đăng nhậ ảp vào MySQL server từ ử chương trình khách mysql.exe s dụng tài kho n root
Từ d ấu nh nh sau:ắc mysql> thi hành câu lệ
source c:\sampledatabase.sql
Cơ sở dữ li c tệ đượu ạo ra có tên là classicmodels
2. Thực hi n truy v n v nh SELECTệ ấ ới câu lệ
Trong phần này, s h c cách s d truy v li các bẽ ọ ử ụng mệnh đề SELECT để ấn dữ ệu từ ảng
cơ s dở ữ liệu.
Cú pháp SELECT
30#
#
SELECT tên c t 1, tên c t 2,ộ ộ ...
FROM các bả ng
[WHERE i u ki n chđ ề ệ ọn]
[GROUP BY nhóm]
[HAVING i u ki n ch n nhómđ ề ệ ọ ]
[ORDER BY các c t s p xộ ắ ếp]
[LIMIT gi i h n s lớ ạ ố ượng];
• Trong m n SELECT có nhi u yột truy vấ ề ếu tố tùy chọn mà có thể sử dụng. Các
tùy ch n c u ngo c vuông []ọ đượ đặt trong dấ ặ .
• Th xuứ tự ấ ệt hi n của các từ khoá WHERE, GROUP BY, HAVING, ORDER BY
và LIMIT ph úng thải theo đ ứ tự trên.
Để chọn tất cả các c t trong m t b ng có th sộ ộ ả ể ử d ng d u sao (*) kụ ấ ý hiệ ệu thay vì li t kê
tất c t sau tả ộ các tên c ừ khoá SELECT.
Ví dụ: nế ầ ảu c n ph i truy v n t t c nhân viên, có th s dấ ấ ả ề các thông tin v ể ử ụ ấng truy v n
sau đây:
SELECT * FROM employees
cũ ể ệ ộ ầ ủ ộ ảng có th xem dữ li u m t ph n c a m t b ng bằng cách liệt kê tên các cột sau từ khóa
SELECT. Điề đượ ọu này c g i là phépchiếu.
31#
#
Ví dụ: n cếu c n phầ ải xem tên, họ và vị trí công việc ủ ể ụa nhân viên, có th sử d ng truy
vấn sau đây:
SELECT lastname, firstname, jobtitle
FROM Employees
Ví dụ: Muốn lấ ềy ra thông tin v mã sả ẩn ph m và tên s n phả ẩm, th c hiự ện truy vấ ưn nh
sau:
SELECT ProductCode, ProductName
FROM Products
32#
#
3. Mệnh đề WHERE
Mệnh đề WHERE c a câu lủ ệnh SELECT cho phép ch n các hàng c thọ ụ ể phù h p vợ ới
đ ề ệ ặ ế ựi u ki n ho c tiêu chí tìm ki m. Sử ọ dụng mệnh đề WHERE để l c dcác b n ghiả a trên
một i t đ ề ệ ấu ki n nh định.
Ví dụ: có th tìm th y các ch công ty b ng cách s d ng truy v n sau ể ấ ủ t a ịch củ ằ ử ụ ấ đây:
SELECT FirstName, LastName, email
FROM Employees
WHERE jobtitle = "President"
Hoă ểc có th tìm ra các thông tin v tên c 112 bề ủa khách hàng có mã số ằng truy v n nhấ ư
sau:
33#
#
SELECT *
FROM Customers
WHERE customerNumber=112
Ví dụ sau a ra các n hàng có mã khách hàng là 181đư đơ
SELECT *
FROM orders
WHERE customerNumber = 181
4. Kết nối các i i toán t AND và ORđ ều ki n vệ ớ ử
Chúng ta có thể k ết h i WHERE, sợ ề đp hai hay nhi u ề ệ ệ đều ki n khác nhau trong m nh ử
dụng các toán tử AND, OR. Với hai u ki n n i b n c hai úng u ki n điề ệ ố ởi AND, cầ ả đ để điề ệ
kết hợp là úng. V u ki n n u ki n k úng n u m c cđ ới hai điề ệ ối bởi OR, điề ệ ết hợp là đ ế ột hoặ ả
hai u kiđiề ện là đúng
Ví dụ: đư ạa ra các khách hàng t i M c i ỹ ủ ườa ng ch m sóc kháchă hàng có mã là 1165
SELECT *
FROM customers
WHERE country ='USA' and salesRepEmployeeNumber = 1165
34#
#
Ví dụ: đưa ra các n hàng có tr ng thái là ‘On Hold’ ho c ‘In Process’đơ ạ ặ
SELECT *
FROM orders
WHERE status = 'On Hold' or status ='In Process'
5. IS NULL các giá tr: tìm ị không xác định
Với các tr li là chườ ư đượng ch a c nhập dữ ệu (coi giá trị ưa xác nh), SQL coi giá tr ó là đị ị đ
NULL. m tra mĐể kiể ột trường có giá trị là NULL hay không, thay vì s ng phép so ử dụ
sánh =, SQL s ng phép toán ử dụ is NULL
Ví dụ: Đưa ra các khách hàng ch a c gán ư đượ nhân viên chăm sóc
SELECT customerName, salesRepEmployeeNumber
FROM customers
WHERE salesRepEmployeeNumber = NULL
35#
#
Nế ửu s d trên, s t qu v . Nụng phép so sánh = như ẽ không có dòng kế ả nào c trđượ ả ề ếu
thay phép so sánh = bởi is NULL
SELECT customerName, salesRepEmployeeNumber
FROM customers
WHERE salesRepEmployeeNumber is NULL
6. Từ khoá DISTINCT
Với từ khóa DISTINCT, có th lo i b d u trùng l p t câu lể ạ ỏ ữ liệ ặ ừ ệnh SELECT.
Ví dụ: để tìm thấy bao nhiêu v trí công viị ệc củ ấ ả ụa t t c các nhân viên, sử d ng từ khóa
DISTINCT trong câu l sauệnh SELECT như :
SELECT DISTINCT jobTitle FROM Employees;
36#
#
Hoặ ểc có th tìm ra mã số các s n ph ã ả ẩm đ đượ ằ ấ ưc mua b ng truy v n nh sau:
SELECT DISTINCT productCode FROM OrderDetails;
7. Giới h l v i LIMITạ ốn s ượng kết quả ớ
Trong hầ ếu h t các lầ ấn truy v n, khi làm việ ớ ả ữc v i các b ng d liệu có ch a ứ hàng ghìn n n đế
hàng tri u b n ghi và không mu n vi n ệ ả ố ết một truy vấ để có được t các d u ó ất cả ữ liệ đ để
đả ả ữm b o hiệ ấ ư ượu su t và l u l ng truy cập gi a các máy chủ cơ sở ệ dữ li u và máy ch ủ ứng
37#
#
dụng . MySQL hỗ trợ một tính năng là LIMIT cho phép hạn chế các bả ản ghi tr lại với
câu lệnh SELECT.
Giả thi t ta có m t b sế ộ ả ơng c ở dữ liệ ớ ảu v i 10.000 b n ghi và muốn nh n c N b n ghi ậ đượ ả
đầ ể ử ấ đu tiên, có th s dụng truy v n sau ây:
SELECT * FROM table_name
LIMIT N
LIMIT c y ra m t s l t m t v trí nào ũng cho phép lấ ộ ố ượng bản ghi nhấ định tính từ ộ ị đó:
LIMIT S, N
Trong câu truy v n trên, S là ấ đi t ểm bắ đầu ghi chỉ số. MySQL xác định rằng vị trí
đầu tiên đượ ạc ghi l i bắt đầu với 0; N là số ượ ả l ng b n ghi muốn chọn.
Ví dụ: Có th y ra thông tin v a 5 s n ph u tiên trong b ng Product b ng ể lấ ề tên củ ả ẩm đầ ả ằ
truy vấ ưn nh sau:
SELECT productName FROM Products
LIMIT 5;
Hoặ ểc có th lấ ềy ra thông tin v 10 khách hàng u tiên hi n đầ ệ đ ằ ấang ở Pháp b ng truy v n
như sau:
select * from customers
where country='France'
limit 10;
38#
#
v Bài tập : thực hành
1. Đưa ra danh sách các nhân viên có tr ng reportsTo ch a xác ườ ư định.
2. ch. Đưa ra danh sách các CustomerNumber ã có th c hiđ ự ện giao dị
3. Đưa ra danh sách các đơn hàng có ngày yêu c u v n chuy n là ‘18/1/2003’. ầ ậ ể L u ư
ý: MySQL l u d u ngày tháng theo nh d ng nư ữ liệ đị ạ ăm/tháng/ngày
(YYYY/MM/DD).
4. Đưa ra danh sách các n hàng có ngày t trong tháng 4 n m 2005 và đơ đặ ă có tr ng ạ
thái là ‘Shipped’
5. Đưa ra danh sách các s n ph c nhóm ‘Classic Cars’ả ẩm thuộ .

Tài liệu khác của Trường Đại học Khoa học tự nhiên, Đại học Quốc gia Hà Nội

Preview text:

Bài thực hành số 3
Truy vấn cơ bản (phần 1) v Nội dung chính
- Câu lệnh Select: cú pháp và cách sử dụng - Mệnh đề where
- Loại bỏ dữ liệu kết quả trùng lặp với DISTINCT
- Giới hạn các bản ghi trả về bằng LIMIT
1. Cài đặt cở sở dữ liệu mẫu
Cơ sở dữ liệu mẫu bao gồm các bảng sau: •
Customers: Lưu trữ thông tin về khách hàng. •
Products: Lưu trữ danh sách về các sản phẩm. •
ProductLines: Lưu trữ danh mục các loại sản phẩm •
Orders: Lưu trữ các đơn hàng được đặt bởi các khách hàng. •
OrderDetails: Lưu trữ về chi tiết các dòng đơn hàng •
Payments: Lưu trữ các thanh toán của khách hàng •
Employees: Lưu trữ thông tin về các nhân viên của tổ chức •
Offices: Lưu thông tin về các văn phòng của tổ chức.
Hình dưới minh họa mối quan hệ giữa các bảng dữ liệu trong cơ sở dữ liệu 28# #
Tải file script sampledatabase.sql để tạo CSDLvề từ địa chỉ:
http://www.mysqltutorial.org/mysql-sample-database.aspx
Giả sử file sampledatabase.sql được đặt trong thư mục gốc ổ C:
Đăng nhập vào MySQL server từ chương trình khách mysql.exe sử dụng tài khoản root
Từ dấu nhắc mysql> thi hành câu lệnh sau: source c:\sampledatabase.sql
Cơ sở dữ liệu được tạo ra có tên là classicmodels
2. Thực hiện truy vấn với câu lệnh SELECT
Trong phần này, sẽ học cách sử dụng mệnh đề SELECT để truy vấn dữ liệu từ các bảng cơ sở dữ liệu. Cú pháp SELECT 29# #
SELECT tên cột 1, tên cột 2, ... FROM các bảng [WHERE điều kiện chọn] [GROUP BY nhóm]
[HAVING điều kiện chọn nhóm]
[ORDER BY các cột sắp xếp]
[LIMIT giới hạn số lượng];
• Trong một truy vấn SELECT có nhiều yếu tố tùy chọn mà có thể sử dụng. Các
tùy chọn được đặt trong dấu ngoặc vuông [].
• Thứ tự xuất hiện của các từ khoá WHERE, GROUP BY, HAVING, ORDER BY
và LIMIT phải theo đúng thứ tự trên.
Để chọn tất cả các cột trong một bảng có thể sử dụng dấu sao (*) ký hiệu thay vì liệt kê
tất cả các tên cột sau từ khoá SELECT.
Ví dụ: nếu cần phải truy vấn tất cả các thông tin về nhân viên, có thể sử dụng truy vấn sau đây: SELECT * FROM employees
cũng có thể xem dữ liệu một phần của một bảng bằng cách liệt kê tên các cột sau từ khóa
SELECT. Điều này được gọi là phépchiếu. 30# #
Ví dụ: nếu cần phải xem tên, họ và vị trí công việc của nhân viên, có thể sử dụng truy vấn sau đây:
SELECT lastname, firstname, jobtitle FROM Employees
Ví dụ: Muốn lấy ra thông tin về mã sản phẩm và tên sản phẩm, thực hiện truy vấn như sau:
SELECT ProductCode, ProductName FROM Products 31# # 3. Mệnh đề WHERE
Mệnh đề WHERE của câu lệnh SELECT cho phép chọn các hàng cụ thể phù hợp với
điều kiện hoặc tiêu chí tìm kiếm. Sử dụng mệnh đề WHERE để lọc các bản ghi dựa trên
một điều kiện nhất định.
Ví dụ: có thể tìm thấy các chủ tịch của công ty bằng cách sử dụng truy vấn sau đây:
SELECT FirstName, LastName, email FROM Employees WHERE jobtitle = "President"
Hoăc có thể tìm ra các thông tin về tên của khách hàng có mã số 112 bằng truy vấn như sau: 32# # SELECT * FROM Customers WHERE customerNumber=112
Ví dụ sau đưa ra các đơn hàng có mã khách hàng là 181 SELECT * FROM orders WHERE customerNumber = 181
4. Kết nối các điều kiện với toán tử AND và OR
Chúng ta có thể kết hợp hai hay nhiều điều kiện khác nhau trong mệnh đề WHERE, sử
dụng các toán tử AND, OR. Với hai điều kiện nối bởi AND, cần cả hai đúng để điều kiện
kết hợp là đúng. Với hai điều kiện nối bởi OR, điều kiện kết hợp là đúng nếu một hoặc cả hai điều kiện là đúng
Ví dụ: đưa ra các khách hàng tại Mỹ của người chăm sóc khách hàng có mã là 1165 SELECT * FROM customers
WHERE country ='USA' and salesRepEmployeeNumber = 1165 33# #
Ví dụ: đưa ra các đơn hàng có trạng thái là ‘On Hold’ hoặc ‘In Process’ SELECT * FROM orders
WHERE status = 'On Hold' or status ='In Process'
5. IS NULL: tìm các giá trị không xác định
Với các trường chưa được nhập dữ liệu (coi giá trị là chưa xác định), SQL coi giá trị đó là
NULL. Để kiểm tra một trường có giá trị là NULL hay không, thay vì sử dụng phép so
sánh =, SQL sử dụng phép toán is NULL
Ví dụ: Đưa ra các khách hàng chưa được gán nhân viên chăm sóc
SELECT customerName, salesRepEmployeeNumber FROM customers
WHERE salesRepEmployeeNumber = NULL 34# #
Nếu sử dụng phép so sánh = như trên, sẽ không có dòng kết quả nào được trả về. Nếu
thay phép so sánh = bởi is NULL
SELECT customerName, salesRepEmployeeNumber FROM customers
WHERE salesRepEmployeeNumber is NULL 6. Từ khoá DISTINCT
Với từ khóa DISTINCT, có thể loại bỏ dữ liệu trùng lặp từ câu lệnh SELECT.
Ví dụ: để tìm thấy bao nhiêu vị trí công việc của tất cả các nhân viên, sử dụng từ khóa
DISTINCT trong câu lệnh SELECT như sau:
SELECT DISTINCT jobTitle FROM Employees; 35# #
Hoặc có thể tìm ra mã số các sản phẩm đã được mua bằng truy vấn như sau:
SELECT DISTINCT productCode FROM OrderDetails;
7. Giới hạn số lượng kết quả với LIMIT
Trong hầu hết các lần truy vấn, khi làm việc với các bảng dữ liệu có chứa hàng nghìn đến
hàng triệu bản ghi và không muốn viết một truy vấn để có được tất cả các dữ liệu đó để
đảm bảo hiệu suất và lưu lượng truy cập giữa các máy chủ cơ sở dữ liệu và máy chủ ứng 36# #
dụng . MySQL hỗ trợ một tính năng là LIMIT cho phép hạn chế các bản ghi trả lại với câu lệnh SELECT.
Giả thiết ta có một bảng cơ sở dữ liệu với 10.000 bản ghi và muốn nhận được N bản ghi
đầu tiên, có thể sử dụng truy vấn sau đây: SELECT * FROM table_name LIMIT N
LIMIT cũng cho phép lấy ra một số lượng bản ghi nhất định tính từ một vị trí nào đó: LIMIT S, N
Trong câu truy vấn trên, S là điểm bắt đầu ghi chỉ số. MySQL xác định rằng vị trí
đầu tiên được ghi lại bắt đầu với 0; N là số lượng bản ghi muốn chọn.
Ví dụ: Có thể lấy ra thông tin về tên của 5 sản phẩm đầu tiên trong bảng Product bằng truy vấn như sau:
SELECT productName FROM Products LIMIT 5;
Hoặc có thể lấy ra thông tin về 10 khách hàng đầu tiên hiện đang ở Pháp bằng truy vấn như sau: select * from customers where country='France' limit 10; 37# #
v Bài tập thực hành:
1. Đưa ra danh sách các nhân viên có trường reportsTo chưa xác định.
2. Đưa ra danh sách các CustomerNumber đã có thực hiện giao dịch.
3. Đưa ra danh sách các đơn hàng có ngày yêu cầu vận chuyển là ‘18/1/2003’. Lưu
ý: MySQL lưu dữ liệu ngày tháng theo định dạng năm/tháng/ngày (YYYY/MM/DD).
4. Đưa ra danh sách các đơn hàng có ngày đặt trong tháng 4 năm 2005 và có trạng thái là ‘Shipped’
5. Đưa ra danh sách các sản phẩm thuộc nhóm ‘Classic Cars’. 38# #

Tài liệu liên quan: