Truy xuất CSDL: Ngôn ngữ thao tác dữ liệu – DMC và DCL

1 TRUY XUẤT CSDL Ngôn ngữ thao tác dữ liệu – DMC và DCL Bài 6 2 Accessing and Modifying Data 1. Truy xuất dữ liệu – Lệnh Select – Truy vấn đơn giản – Truy vấn từ nhiều bảng – Truy vấn con - SubQuery – Các mệnh đề EXISTS, DISTINCT, COMPUTE BY 2. Sửa chữa dữ liệu – Lệnh INSERT – Lệnh UPDATE – Lệnh DELETE 3 Cú pháp lệnh Select SELECT [ALL | DISTINCT] [TOP n [WITH TIES]] select_list [ INTO new_table ] FROM table_source [ WHERE search_condition ] [ GROUP BY group_by_expression ] [ HAVING search_condition ] [ ORDER BY order_expression [ ASC | DESC ] ] • ORDER BY : Sắp xếp • WHERE: Điều kiện • GROUP BY: Nhóm • HAVING: Điều kiện nhóm 4 Ví dụ lệnh SELECT Column names Table names Condition Sort order 5 Truy vấn đơn giản  Chọn tất cả các cột trong một bảng Syntax SELECT * FROM SELECT * FROM Customers Example 6 Truy vấn đơn giản  Chọn một vài cột trong một bảng Syntax SELECT , FROM SELECT Productid, productName FROM Products Example 7 Truy vấn đơn giản  Kết nối các cột thành một cột Syntax SELECT + FROM SELECT FirstName + „ „ + Lastname FROM Employees Example 8 Truy vấn đơn giản  Đặt tên cho cột mới Syntax SELECT as FROM SELECT FirstName +‟ „ +LastName AS „EmployeeName' FROM Employees Example 9  Tạo cột tính toán SELECT Orderid, Quantity*UnitPrice AS „Total' FROM [Order details] Example Truy vấn đơn giản 10  Loại bỏ những dòng trùng nhau Syntax SELECT DISTINCT FROM SELECT DISTINCT Customerid FROM Orders Example Truy vấn đơn giản diadiem TP HCM HA NOI TP HCM diadiem TP HCM HA NOI SELECT diadiem FROM DIADIEM_PHG SELECT DISTINCT diadiem FROM DIADIEM_PHG 11  Chỉ có n hàng đầu tiên hay n% của các hàng của bảng kết quả được xuất Syntax SELECT TOP n [PERCENT] FROM SELECT TOP 3 Productid, Unitprice FROM [Order Details] SELECT TOP 4 PERCENT Productid FROM Products Examples Truy vấn đơn giản 12 Mệnh đề WHERE • Chứa điều kiện lọc dữ liệu cần trả về • Cú pháp: WHERE 13 Phép toán quan hệ - Relational Operators Operator Not ! Less Than or Equal To <= Greater Than or Equal To >= Less than < Greater Than > Equal To = Meaning 14 Phép toán Logical  Phép toán và AND SELECT Orderid, orderdate, customerid FROM Orders WHERE Month(Orderdate) = 7 AND Year(Orderdate)=1996 Example 15 Phép toán Logical  OR operator SELECT * FROM Orders WHERE Customerid = „TOMSP‟ OR Customerid = „HANAR‟ Example 16 Logical Operators (contd.)  NOT operator SELECT * FROM Orders WHERE NOT Employeeid = 10 Example 17 Wildcard Characters Example SELECT Meal_Code FROM Meal WHERE Meal_Code LIKE ‘C_’ SELECT Meal_Code FROM Meal WHERE Meal_Code LIKE ‘CO_%’ SELECT * FROM flight WHERE aircraft_code LIKE ‘9W0[1-2]’ Wildcard _ % [] [^] Description Represents a single character Represents a string of any length Represents a single character within the range enclosed in the brackets Represents any single character not within the range enclosed in the brackets SELECT * FROM flight WHERE aircraft_code LIKE ‘9W0[^1-2]’ 18 Các toán tử SQL • LIKE: giống 1 chuỗi • IS NOT NULL: không phải giá trị rỗng • BETWEEN…AND: giữa 2 giá trị • IN: đạt giá trị trong 1 danh sách • ALL/ ANY (SOME): được dùng trong lệnh truy vấn con và kết quả là nhiều dòng. <ANY: Nhỏ hơn trị cao nhất >ANY: Lớn hơn trị thấp nhất =ANY: Tương đương với IN >ALL: Lớn hơn trị cao nhất <ALL: Nhỏ hơn trị thấp nhất • EXIST: kiểm tra sự tồn tại của 1 dữ liệu trong nhiều dòng. 19 Các hàm SQL • Day(Biểu thức ngày): Trả về ngày • Month(Biểu thức ngày): Trả về tháng • Year(Biểu thức ngày): Trả về năm • GetDate(): Trả về ngày giờ hệ thống • Max(dãy giá trị): Trả về giá trị lớn nhất trong dãy • Min(dãy giá trị): Trả về giá trị nhỏ nhất trong dãy • Avg(dãy giá trị): Trả về giá trị trung bình của dãy • Count(dãy giá trị): Đếm số dòng. • Sum(dãy giá trị): Trả về tổng các giá trị trong dãy 19 20 So sánh Chuỗi • So sánh gần đúng sử dụng “like” – Hai ký tự thay thế: „_‟ và „%‟ • Tìm tất cả các mã nhân viên có 3 ký tự cuoi cua dia chi la RD. SELECT * FROM EMPLOYEES WHERE ADDRESS LIKE '%RD.' Cho biết tên nhân viên sinh vào những năm 1960 SELECT LastName, FirstName, BirthDate FROM EMPLOYEES WHERE convert(char(8), BIRTHDATE,1) like '______6_' 21 Biến bộ • Cho biết hai nhân viên có cùng lương SELECT NV1.LASTNAME, NV2.LASTNAME,NV1.CITY FROM EMPLOYEES NV1, EMPLOYEES NV2 WHERE NV1.CITY = NV2.CITY • Có thể sử dụng biến bộ bất kỳ lúc nào để thuận tiện và dễ đọc! SELECT NV1.LASTNAME, NV2.LASTNAME,NV1.CITY FROM EMPLOYEES NV1, EMPLOYEES NV2 WHERE NV1.CITY NV2.CITY tuple variable Các thuộc tính Trùng tên 22 • Danh sách các hoá đơn gồm Mahd, tenkh SELECT orderid, Orderid.Customerid, CompanyName FROM Orders, Customers WHERE orders.customerid = Customers.customerid • Danh sách các hoá đơn do nhan viên có tên bắt dầu là D lập SELECT Orders.Orderid, LastName +‟ „+FirstName as EmployeeName FROM Employees, Orders WHERE Employees.employeeid = Orders.Employeeid And LastName like „D%‟ • Danh sách các hoá đơn do nhan viên có country là USA lập SELECT O.Orderid, LastName +‟ „+FirstName as EmployeeName FROM Employees E, Orders O WHERE E.employeeid =O.Employeeid And Country =„USA‟ Truy vấn từ nhiều bảng & Where (điều kiện kết nối) 23 Sắp xếp - ORDER BY Clause Xác định thứ tự của bộ kết quả Cú pháp [ ORDER BY { order_by_expression [ ASC | DESC ] } [ ,...n] ] ASC (ascending) : xếp theo thứ tự tăng DESC (descending): xếp theo thứ tự giảm 24 Nhóm dữ liệu trong bảng kết quả • Những mệnh đề dùng để nhóm dữ trong bảng kết quả: –GROUP BY: tổng hợp bảng kết quả theo nhóm bằng cách dùng các hàm gộp –COMPUTE và COMPUTE BY: mệnh đề COMPUTE trong lệnh SELECT được dùng để phát ra các hàng tổng hợp bằng cách dùng hàm gộp. Mệnh đề COMPUTE BY được dùng để tổng hợp thêm các hàng kết quả theo cột 25 Mệnh đề GROUP BY • Cú pháp: [GROUP BY [ ALL ]group_by_expression [,...n] ALL bảng kết quả sẽ chứa tất cả các nhóm kể cả những nhóm không thỏa mãn điều kiện lọc trong trong mệnh đề WHERE, những nhóm không thoả điều kiện sẽ có giá trị null. • group_by_expression: biểu thức dùng để xác định cột được nhóm 26 Mệnh đề GROUP BY • Ví dụ: SELECT Orderid, SUM(Quantity * UnitPrice) AS „Total„ FROM [Order Details] GROUP BY Orderid SELECT Orderid, AVG(Quantity * UnitPrice) AS „AVG TOTAL„ FROM [Order Details] GROUP BY Orderid 27 Mệnh đề GROUP BY • Ví dụ: SELECT Orderid, MIN(Quantity * UnitPrice) AS „MIN Total„ FROM [Order Details] GROUP BY Orderid SELECT Orderid, MAX(Quantity * UnitPrice) AS „MAX Total„ FROM [Order Details] GROUP BY Orderid 28 Mệnh đề GROUP BY • Ví dụ: SELECT Count(Reportsto) AS „Count Of Orders„ FROM [Orders] SELECT Customerid, Count(Orderid) AS „Count Order of Customers„ FROM Orders GROUP BY Customerid SELECT Count(*) AS „Count Of Orders„ FROM [Orders] 29 Mệnh đề GROUP BY • Ví dụ: SELECT Productid, Sum(Quantity) As Total FROM [Order Details] WHERE Productid =2 GROUP BY Productid SELECT Customerid, Count(Orderid) AS „Count Order of Customers„ FROM Orders WHERE Customerid like „%m‟ GROUP BY Customerid 30 Ví dụ • SELECT Type, Advance = SUM (Advance) FROM Titles WHERE Type IN ('business', 'mod_cook', 'trad_cook') GROUP BY Type Type Advance ------------ --------------------- business 25125.0000 mod_cook 15000.0000 trad_cook 19000.0000 (3 row(s) affected) Mệnh đề GROUP BY 31 Ví dụ 2 • SELECT Type, Advance = SUM (Advance) FROM Titles WHERE Type IN ('business', 'mod_cook', 'trad_cook') GROUP BY ALL Type Type Advance ------------ --------------------- business 25125.0000 mod_cook 15000.0000 popular_comp NULL psychology NULL trad_cook 19000.0000 UNDECIDED NULL (6 row(s) affected) Mệnh đề GROUP BY 32 GROUP BY và HAVING • Có thể hạn chế các nhóm trong bảng kết quả bằng mệnh đề HAVING. • Chỉ sau khi dữ liệu đã được nhóm và tổng hợp , điều kiện trong mệnh đề HAVING mới được áp dụng. • Không thể dùng 1 cột mà nó không tham gia vào hàm gộp của mệnh đề SELECT hay của mệnh đề GROUP BY. • SELECT pub_id, AVG(price) FROM titles GROUP BY pub_id HAVING (AVG(price) > 10) 33 Sử dụng WHERE và HAVING • Mệnh đề HAVING giống như mệnh đề WHERE nhưng chỉ áp dụng cho cả nhóm trong khi mệnh đề WHERE áp dụng cho từng hàng. • Một truy vấn có thể chứa cả mệnh đề WHERE và mệnh đề HAVING. –Mệnh đề WHERE được áp dụng trước cho các hàng trong bảng được truy vấn. Chỉ những hàng nào thoả mãn điều kiện của mệnh đề WHERE mới được nhóm dữ liệu. –Sau đó mệnh đề HAVING sẽ được áp dụng cho các nhóm. Chỉ những nhóm thoả mãn điều kiện HAVING mới được xuất ra bảng kết quả. 34 Ví dụ SELECT titles.pub_id, AVG(titles.price) FROM titles INNER JOIN publishers ON titles.pub_id = publishers.pub_id WHERE publishers.state = 'CA' GROUP BY titles.pub_id HAVING AVG(price) > 10 Sử dụng WHERE và HAVING 35 Ví dụ 2 SELECT Productid, Sum(Quantity) As Total FROM [Order Details] GROUP BY Productid HAVING Sum(Quantity)>=30 SELECT Customerid, Count(Orderid) AS „Count Order of Customers„ FROM Orders GROUP BY Customerid HAVING Count(Orderid) <=5 Sử dụng WHERE và HAVING 36 Sử dụng Group với ROLLUP • Roll Up sẽ chèn thêm các dòng Total nằm trước các nhóm mẫu tin được phân thành Group By SELECT Productid, Orderid, Sum(Quantity) As Total FROM [Order Details] WHERE Orderid <10250 GROUP BY Productid, Orderid ORDER BY Productid, Orderid SELECT Productid, Orderid, Sum(Quantity) As Total FROM [Order Details] WHERE Orderid <10250 GROUP BY Productid, Orderid WITH ROLLUP ORDER BY Productid, Orderid 37 Ví dụ SELECT Productid, Orderid, Sum(Quantity) As Total FROM [Order Details] WHERE Orderid <10250 GROUP BY Productid, Orderid WITH CUBE ORDER BY Productid, Orderid Tương tự như Rollup nhưng thêm các dòng Total của mỗi sự kết hợp có thể có giữa các cột Sử dụng Group với Cube 38 CUBE and ROLLUP Kết quả 39 Mệnh đề COMPUTE • Thường dùng để kiểm tra số liệu, dùng kèm với các hàm thống kê SUM, AVG, MAX, MIN,… • COMPUTE…BY…: Có kết nhóm 1) SELECT c.CustomerID, o.OrderID, (od.quantity * od.unitprice) as 'total‘ FROM Orders o, [order details] od, Customers c WHERE c.CustomerID = o.CustomerID AND o.OrderID = od.OrderID AND c.CustomerID LIKE 'AN%‘ ORDER BY c.customerID COMPUTE SUM( od.quantity * od.unitprice) 2) SELECT c.CustomerID, o.OrderID, (od.quantity * od.unitprice) 'total‘ FROM Orders o, [order details] od, Customers c WHERE c.CustomerID = o.CustomerID AND o.OrderID = od.OrderID AND c.CustomerID LIKE 'AN%‘ ORDER BY c.customerID COMPUTE SUM(od.quantity * od.unitprice) BY c.CustomerID 40 Ví dụ SELECT Productid, Orderid, Quantity FROM [Order Details] ORDER BY Productid, Orderid COMPUTE Sum(Quantity) SELECT Productid, Orderid, Sum(Quantity) As Total FROM [Order Details] ORDER BY Productid, Orderid COMPUTE SUM(Quantity) By Productid COMPUTE SUM(Quantity) Mệnh đề COMPUTE 41 Mệnh đề JOIN-Kết nối nhiều bảng • Mệnh đề join dùng để khôi phục dữ liệu từ nhiều hơn 1 bảng • Cú pháp SELECT column_name [,n..] FROM table_name table_alias [CROSS|INNER|[LEFT | RIGHT]OUTER] JOIN table_name table_alias [ON table_name.ref_column_name join_operator table_name.ref_column_name] [WHERE search_condition] 42 Kết nối các bảng • Kết nối chỉ tồn tại trong thời gian truy vấn. • Kết nối không thay đổi dữ liệu trong các bảng của cơ sở dữ liệu. • Nên tạo bí danh (alias) cho tên bảng để tránh gõ tên dài và làm truy vấn dễ đọc hơn • Ví dụ SELECT t.Title_Id, t.Title, p.Pub_Id from Titles t JOIN Publishers p on t.Pub_Id=p.Pub_Id WHERE Pub_Name='New Moon Books' 43 Các cột tham gia kết nối • Nếu kết nối nhiều hơn 2 bảng thì kết nối 2 bảng trước, sau đó kết nối nhóm này với bảng thứ ba. • Ví dụ SELECT OrderID,p.ProductID, ProductName, CategoryName ,Quantity FROM [Order Details] o JOIN Products p ON o.ProductID = p.ProductID JOIN Categories c ON p.CategoryID = c.CategoryID 44 Các loại kết nối • Inner Join • Outer Join • Cross Join • Equi Join • Natural Join • Self Join 45 Kết nối nội - Inner joins • Trong kết nối nội, dữ liệu từ nhiều bảng được hiển thị sau khi so sánh giá trị trong 1 cột chung. Chỉ những hàng mà có giá trị thoả mãn điều kiện kết nối trong cột chung đó mới được hiển thị. • Tích Cartesian: việc kết nối nhiều bảng mà không có điều kiện kết nối trong mệnh đề ON sẽ tạo ra tích cartesian giữa 2 bảng • Ví dụ: SELECT t.title,t.price, p.pub_name, p.city, t.pubdate from titles t join publishers p on t.pub_id = p.pub_id 46 Kết nối nội - Inner joins 46 SELECT Customers.CustomerID, CompanyName, OrderID, OrderDate FROM Customers INNER JOIN Orders ON Customers.CustomerID = Orders.CustomerID SELECT ProductName, od.OrderID, Quantity, UnitPrice, Quantity * UnitPrice AS [Money] FROM Products AS p INNER JOIN [Order Details] AS od ON p.ProductID = od.ProductID INNER JOIN Orders As o ON od.OrderID = o.OrderID WHERE Month(OrderDate) = 7 47 Kết nối nội với toán tử lớn hơn • Có thể thực hiện kết nối 2 bảng với điều kiện kết nối dùng toán tử không bằng nhau. • Ví dụ: SELECT p.pub_name, p.state, a.au_lname, a.au_fname, a.state FROM publishers p INNER JOIN authors a ON a.state > p.state WHERE p.pub_name = 'New Moon Books' ORDER BY au_lname ASC, au_fname ASC 48 Kết nối ngoại - Outer joins • Kết nối ngoại được dùng để cho ra kết quả chứa tất cả các hàng của 1 bảng và các hàng trùng nhau của bảng còn lại. Những cột mà không có giá trị phù hợp sẽ được hiển thị giá trị NULL. • Cú pháp SELECT column_name, column_name [,column_name] FROM table_name [LEFT | RIGHT] OUTER JOIN table_name ON table_name.ref_column_name join_operator table_name.ref_column_name 49 Kết nối trái - LEFT OUTER JOIN • Tất cả các hàng từ bảng bên trái trong mối kết nối giữa 2 bảng sẽ được hiển thị trong bảng kết quả. • Ví dụ: SELECT a.au_fname, a.au_lname, p.pub_name FROM authors a LEFT OUTER JOIN publishers p ON a.city = p.city ORDER BY p.pub_name ASC, a.au_lname ASC, a.au_fname ASC 50 Cross join • Cross join trả về mọi tổ hợp có thể có của tất cả các hàng trong các bảng kết nối. • Cross join không có mệnh đề ON – Nếu không mệnh đề WHERE, cross join sẽ tạo ra tích Cartesian – Nếu có mệnh đề WHERE, cross join sẽ thực hiện như 1 kết nối nội 51 Kết nối chéo - Cross join • Ví dụ 1: SELECT au_fname, au_lname, pub_name FROM authors CROSS JOIN publishers ORDER BY au_lname DESC Kết quả chứa 184 hàng (authors có 23 hàng và publishers có 8 hàng; 23 x 8 = 184 hàng). • Ví dụ 2: SELECT au_fname, au_lname, pub_name FROM authors CROSS JOIN publishers WHERE authors.city = publishers.city ORDER BY au_lname DESC SELECT au_fname, au_lname, pub_name FROM authors INNER JOIN publishers ON authors.city = publishers.city ORDER BY au_lname DESC 52 Truy vấn tự kết nối (self-join) • Ví dụ: tìm tất cả sinh viên đã học ít nhất là 2 course SELECT t1.StudID FROM Transcript t1 JOIN Transcript t2 ON t1.CrsCode = t2.CrsCode AND t1.StudID = t2.StudID 2( ( [ , 2, 2, 2])) StudID CrsCode CrsCode TRANSCRIPT TRANSCRIPT StudID CrsCode Semester Grade    53 Truy vấn tự kết nối (self-join) SELECT Customers.CustomerID, CompanyName, OrderID, OrderDate FROM Customers INNER JOIN Orders ON Customers.CustomerID = Orders.CustomerID SELECT ProductName, od.OrderID, Quantity, UnitPrice, Quantity * UnitPrice AS [Money] FROM Products AS p INNER JOIN [Order Details] AS od ON p.ProductID = od.ProductID INNER JOIN Orders As o ON od.OrderID = o.OrderID WHERE Month(OrderDate) = 7 54 55 Truy vấn con - Subqueries • Subquery là lệnh SELECT mà kết quả trả về là 1 giá trị đơn (single value) và được đặt lồng vào bên trong các lệnh SELECT, INSERT, UPDATE, hay DELETE, hay bên trong truy vấn con khác. • Subquery có thể được dùng bất kỳ nơi nào mà biểu thức được phép dùng 56 SELECT FROM Table WHERE = (SELECT FROM WHERE = ) Parent Query Subquery Operator A subquery is a SELECT statement inside another SELECT statement. Cú pháp Truy vấn con - Subqueries 57 • Ví dụ 1: SELECT Ord.OrderID, Ord.OrderDate, (SELECT MAX(OrdDet.UnitPrice) FROM Northwind.dbo.[Order Details] AS OrdDet WHERE Ord.OrderID =OrdDet.OrderID) AS MaxUnitPrice FROM Northwind.dbo.Orders AS Ord • Subquery có thể được dùng để khôi phục dữ liệu từ nhiều bảng và có thể được dùng như 1 cách khác của join (alternative to a join) 57 Truy vấn con - Subqueries 58 • Cách 1: dùng subquery. SELECT ProductName FROM Products WHERE UnitPrice = (SELECT UnitPrice FROM Products WHERE ProductName = 'Sir Rodney''s Scones') • Cách 2: dùng join SELECT Prd1.ProductName FROM Products AS Prd1 JOIN Products AS Prd2 ON (Prd1.UnitPrice = Prd2.UnitPrice) WHERE Prd2.ProductName = 'Sir Rodney''s Scones' Truy vấn con - Subqueries 59 • Subquery có thể được dùng theo 1 trong các dạng sau: –WHERE expression [NOT] IN (subquery) –WHERE expression comparison_operator [ANY | ALL] (subquery) –WHERE [NOT] EXISTS (subquery) Truy vấn con - Subqueries 60 Subqueries với toán tử IN • Kết quả của subquery được dùng với IN (hay với NOT IN) thường là 1 danh sách (list) chứa từ 0 đến nhiều giá trị. • Ví dụ: SELECT pub_name FROM publishers WHERE pub_id IN (SELECT pub_id FROM titles WHERE type = 'business') • Dùng cách 2 với mệnh đề join??? 61 Subquery với các toán tử so sánh • Các toán tử so sánh đơn giản (unmodified comparison operator) =, , >, > =, , ! <, or < = • Subquery bắt đầu với toán tử so sánh đơn giản và trả về 1 giá trị đơn (single value) SELECT DISTINCT title FROM titles WHERE price > (SELECT MIN(price) FROM titles) 62 Subquery trả về một giá trị  Use Aggregrate Functions in Subquery as Max(), Min(), Avg(),…  Khi một subquery được sử dụng trong mệnh đề SELECT, nó trả về dữ liệu thay cho một cột.  Loại subquery này được gọi là scalar query bởi vì nó chỉ trả về một giá trị. Ví dụ SELECT ProductName, UnitPrice FROM Products WHERE CategoryID =1 and UnitPrice < (SELECT AVG(Unitprice) FROM Products) 63  Sử dụng toán tử In, Not in, Any, all… Ví dụ 1) SELECT Ordeid FROM [Order details] WHERE ProductId In (SELECT Productid FROM Products WHERE CategoryID =1) 2) SELECT Ordeid, Productid, Quantity FROM [Order details] WHERE ProductId =1 and quantity >=All (SELECT Quantity FROM [Order Details] WHERE CategoryID =1) Subquery trả về nhiều giá trị 64 • Cho biết các sản phẩm có đơn giá cao nhất SELECT * FROM PRODUCTS WHERE Unitprice >= ALL (SELECT unitprice FROM Products) • Cho biết các sản phẩm có đơn vị tính có chữ Box và có đơn giá cao nhất SELECT * FROM PRODUCTS WHERE QuantityPerunit like „%box%‟ and Unitprice >= ALL (SELECT unitprice FROM Products) Subquery trả về nhiều giá trị 65 Subquery với các toán tử so sánh • Subquery trả về một giá trị SELECT ProductName, UnitPrice FROM Products WHERE SupplierID=1 and UnitPrice < ( SELECT AVG(UnitPrice) FROM Products WHERE SupplierID=1 ) • Subquery trả về một dãy giá trị Select OrderID from [order details] Where ProductID IN (select ProductID From Products Where SupplierID=1) 66 Subquery với các toán tử so sánh Select OrderID , productID, quantity from [order deta