Bài 6 TRUY XUẤT CSDL Ngôn ngữ thao tác dữ liệu

Bài 6

TRUY XUẤT CSDL Ngôn ngữ thao tác dữ liệu – DMC và DCL

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

2. Sửa chữa dữ liệu

Lệnh INSERT Lệnh UPDATE Lệnh DELETE

– – –

– – – – Các mệnh đề EXISTS, DISTINCT, COMPUTE BY

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

Ví dụ lệnh SELECT

Table names

Column names

Condition

Sort order

Truy vấn đơn giản

 Chọn tất cả các cột trong một bảng

SELECT * FROM < tablename>

Syntax

SELECT * FROM Customers

Example

Truy vấn đơn giản

 Chọn một vài cột trong một bảng

Syntax

SELECT , FROM

Example

SELECT Productid, productName FROM Products

Truy vấn đơn giản

 Kết nối các cột thành một cột

Syntax

SELECT +<'constant' > FROM

Example

SELECT FirstName + „ „ + Lastname FROM Employees

Truy vấn đơn giản

 Đặt tên cho cột mới

Syntax

SELECT as <'alias'> FROM

Example

„ +LastName AS

SELECT FirstName +‟ „EmployeeName' FROM Employees

Truy vấn đơn giản

 Tạo cột tính toán

SELECT Orderid, Quantity*UnitPrice AS „Total' FROM [Order details]

Example

Truy vấn đơn giản

 Loại bỏ những dòng trùng nhau Syntax

SELECT diadiem FROM DIADIEM_PHG

diadiem

TP HCM

HA NOI

TP HCM

SELECT DISTINCT FROM

SELECT DISTINCT diadiem FROM

DIADIEM_PHG

Example

diadiem

TP HCM

HA NOI

SELECT DISTINCT Customerid FROM Orders

Truy vấn đơn giản

 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

Examples

SELECT TOP 3 Productid, Unitprice FROM [Order Details] SELECT TOP 4 PERCENT Productid FROM Products

Mệnh đề WHERE

• Chứa điều kiện lọc dữ liệu cần trả về • Cú pháp: WHERE

Phép toán quan hệ - Relational Operators

Operator

Meaning

Greater Than

Equal To =

Less than <

>= Greater Than or Equal To

Less Than or Equal To <=

Not !

Phép toán Logical

 Phép toán và AND

Example

SELECT Orderid, orderdate, customerid FROM Orders WHERE Month(Orderdate) = 7 AND Year(Orderdate)=1996

Phép toán Logical

 OR operator

Example

SELECT * FROM Orders WHERE Customerid = „TOMSP‟ OR Customerid = „HANAR‟

Logical Operators (contd.)

 NOT operator

SELECT * FROM Orders WHERE NOT Employeeid = 10

Example

Wildcard Characters

Wildcard _

Description Represents a single character

Example SELECT Meal_Code FROM Meal WHERE Meal_Code LIKE ‘C_’

Represents a string of any length

SELECT Meal_Code FROM Meal WHERE Meal_Code LIKE ‘CO_%’

single

[]

SELECT * FROM flight WHERE aircraft_code LIKE ‘9W0[1-2]’

[^]

SELECT * FROM flight WHERE aircraft_code LIKE ‘9W0[^1-2]’

character Represents a within the range enclosed in the brackets Represents any single character not within the range enclosed in the brackets

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: 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

• EXIST: kiểm tra sự tồn tại của 1 dữ liệu trong nhiều dòng.

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

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_'

tuple variable

Các thuộc tính Trùng tên

Biến bộ • Cho biết hai nhân viên có cùng lương

EMPLOYEES NV1, EMPLOYEES NV2

SELECT NV1.LASTNAME, NV2.LASTNAME,NV1.CITY FROM 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ễ

WHERE

NV1.LASTNAME, NV2.LASTNAME,NV1.CITY EMPLOYEES NV1, EMPLOYEES NV2 NV1.CITY <> NV2.CITY

đọc! SELECT FROM

Truy vấn từ nhiều bảng & Where (điều kiện kết nối)

• Danh sách các hoá đơn gồm Mahd, tenkh

SELECT FROM WHERE

orderid, Orderid.Customerid, CompanyName Orders, Customers 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 WHERE

Employees, Orders 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

Employees E, Orders O E.employeeid =O.Employeeid And Country =„USA‟

SELECT O.Orderid, LastName +‟ „+FirstName as EmployeeName FROM WHERE

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

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

– 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

cách dùng các hàm gộp

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

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

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

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]

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

Mệnh đề GROUP BY Ví dụ

('business',

• SELECT Type, Advance = SUM (Advance) FROM 'mod_cook', IN Titles WHERE Type 'trad_cook') GROUP BY Type

Advance --------------------- 25125.0000 15000.0000 19000.0000

Type ------------ business mod_cook trad_cook (3 row(s) affected)

Mệnh đề GROUP BY

Advance

25125.0000 15000.0000 NULL NULL 19000.0000 NULL

Type ------------ business mod_cook popular_comp psychology trad_cook UNDECIDED (6 row(s) affected)

Ví dụ 2 • SELECT Type, Advance = SUM (Advance) FROM Titles WHERE Type IN ('business', 'mod_cook', 'trad_cook') GROUP BY ALL Type ---------------------

GROUP BY và HAVING

• Có thể hạn chế các nhóm trong bảng kết quả bằng mệnh đề

HAVING.

trong mệnh đề HAVING mới được áp dụng.

• Chỉ sau khi dữ liệu đã được nhóm và tổng hợp , điều kiện

• 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)

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ả.

Sử dụng WHERE và HAVING Ví dụ

SELECT titles.pub_id, AVG(titles.price)

publishers.state = 'CA'

FROM titles INNER JOIN publishers ON titles.pub_id = publishers.pub_id WHERE GROUP BY titles.pub_id HAVING AVG(price) > 10

Sử dụng WHERE và HAVING

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 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

Sử dụng Group với Cube

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

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

CUBE and ROLLUP

Kết quả

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

AND o.OrderID = 'AN%‘ ORDER BY

WHERE c.CustomerID = o.CustomerID od.OrderID AND c.CustomerID LIKE 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 = 'AN%‘ ORDER BY od.OrderID AND c.CustomerID LIKE c.customerID COMPUTE SUM(od.quantity * od.unitprice) BY c.CustomerID

Mệnh đề COMPUTE

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 đề 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]

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ữ

• Nên tạo bí danh (alias) cho tên bảng để tránh gõ tên dài và

liệu.

làm truy vấn dễ đọc hơn

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'

• Ví dụ

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

Các loại kết nối

• Inner Join • Outer Join • Cross Join • Equi Join • Natural Join • Self Join

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

Kết nối nội - Inner joins

SELECT Customers.CustomerID, CompanyName, SELECT ProductName, od.OrderID, Quantity, UnitPrice,

OrderID, OrderDate FROM Customers INNER JOIN Orders ON Customers.CustomerID = Orders.CustomerID

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

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.

SELECT p.pub_name, p.state, a.au_lname, a.au_fname, a.state

FROM publishers p INNER JOIN authors a

• Ví dụ:

ON a.state > p.state

WHERE p.pub_name = 'New Moon Books'

ORDER BY au_lname ASC, au_fname ASC

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

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ả.

SELECT a.au_fname, a.au_lname, p.pub_name

• Ví dụ:

FROM authors a LEFT OUTER JOIN publishers p ON

ORDER BY p.pub_name ASC, a.au_lname ASC,

a.city = p.city

a.au_fname ASC

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.

– Nếu không mệnh đề WHERE, cross join sẽ tạo ra tích

• Cross join không có mệnh đề ON

Cartesian

– Nếu có mệnh đề WHERE, cross join sẽ thực hiện như

1 kết nối nội

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).

SELECT au_fname, au_lname, pub_name FROM authors INNER JOIN publishers ON authors.city = publishers.city ORDER BY au_lname DESC 51

• 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

Truy vấn tự kết nối (self-join)

SELECT t1.StudID FROM Transcript t1 JOIN Transcript t2 ON t1.CrsCode = t2.CrsCode AND t1.StudID = t2.StudID

• Ví dụ: tìm tất cả sinh viên đã học ít nhất là 2 course

Truy vấn tự kết nối (self-join)

SELECT Customers.CustomerID,

ON Customers.CustomerID = Orders.CustomerID

CompanyName, OrderID, OrderDate FROM Customers INNER JOIN Orders

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

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

Truy vấn con - Subqueries

A subquery is a SELECT statement inside another SELECT statement.

Cú pháp

Parent Query SELECT FROM Table

WHERE =

Operator

Subquery

(SELECT FROM

WHERE = )

Truy vấn con - Subqueries

• Ví dụ 1:

MAX(OrdDet.UnitPrice)

SELECT Ord.OrderID, Ord.OrderDate, (SELECT

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)

Truy vấn con - Subqueries

WHERE ProductName = 'Sir Rodney''s Scones')

• Cách 1: dùng subquery. SELECT ProductName FROM Products WHERE UnitPrice = (SELECT UnitPrice FROM Products

• Cách 2: dùng join SELECT Prd1.ProductName FROM Products AS Prd1 JOIN

ON (Prd1.UnitPrice = Prd2.UnitPrice) WHERE Prd2.ProductName = 'Sir Rodney''s Scones'

Products AS Prd2

Truy vấn con - Subqueries

• 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)

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???

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)

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)

Subquery trả về nhiều giá trị

 Sử dụng toán tử In, Not in, Any, all…

Ví dụ

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)

1) SELECT Ordeid FROM [Order details]

Subquery trả về nhiều giá trị

• Cho biết các sản phẩm có đơn giá cao nhất

* PRODUCTS Unitprice >= ALL

(SELECT unitprice FROM Products)

SELECT FROM WHERE

• Cho biết các sản phẩm có đơn vị tính có chữ Box và có đơn giá cao

PRODUCTS QuantityPerunit like „%box%‟ and Unitprice >= ALL

nhất SELECT * FROM WHERE (SELECT unitprice FROM Products)

Subquery với các toán tử so sánh

WHERE SupplierID=1 and UnitPrice < ( SELECT AVG(UnitPrice) FROM Products

WHERE SupplierID=1 )

• Subquery trả về một giá trị SELECT ProductName, UnitPrice FROM Products • Subquery trả về một dãy giá trị Select OrderID from [order details]

Where ProductID IN (select ProductID From Products Where SupplierID=1)

Subquery với các toán tử so sánh

From [order details] Where ProductID=1)

Select OrderID , productID, quantity from [order details] Where ProductID =1 and quantity >= all (select quantity • Subquery dùng với toán tử EXISTS Select * from Customers as mC where NOT EXISTS ( SELECT * from [Orders] as o where o.customerid = mC.customerid )

Subquery với các toán tử so sánh

• Toán tử so sánh phức (Modified Comparison operators) là toán tử so sánh đơn giản có kèm theo các từ All, any hay some (=toán tử s/sánh+ [All,Any,some])

• Subquery bắt đầu với toán tử so sánh phức sẽ trả về 1 danh sách (list) của 0 hay nhiều gia trị và có thể bao gồm cả mệnh đề GROUP BY hay HAVING.

Subquery với các toán tử so sánh

• >ALL có nghĩa lớn hơn mọi giá trị. • >ANY có nghĩa lớn hơn ít nhất 1 giá trị

Vd: >ALL (1, 2, 3) lớn hơn 3 >ANY (1, 2, 3) lớn hơn 1

publishers)

• Ví dụ: SELECT au_lname, au_fname FROM authors WHERE city <> ALL (SELECT city FROM

Subquery với các toán tử so sánh

Using EXISTS and NOT EXISTS

 EXISTS checks the existence of rows that the

subquery returns

 The subquery does not return any data, instead it

returns TRUE or FALSE

Example

SELECT Orderid, Customerid FROM Orders WHERE EXISTS (SELECT Customerid FROM Customers WHERE City =„London‟)

Using EXISTS and NOT EXISTS

 EXISTS checks the existence of rows that the

subquery returns

 The subquery does not return any data, instead it

returns TRUE or FALSE

Example

WHERE NOT EXISTS (SELECT Customerid FROM Customers WHERE City =„London‟)

SELECT Orderid, Customerid FROM Orders

Nested Subqueries

Nested subqueries are subqueries within other subqueries

Example

(SELECT orderid FROM [Order details] where

SELECT customerid,ComPanyname FROM Customers WHERE Customerid >=all (SELECT Customerid from Orders WHERE orderid>=all Productid =11 ))

Correlated Subqueries

 A subquery refers to the parent query  A subquery is re-evaluated for every iteration of the

parent query

Example

city FROM

SELECT Customerid,CompanyName, Customers WHERE customerid IN (SELECT customerid FROM Employees,orders WHERE orders.employeeid = employees.employeeid and Employees.city =Customers.city)

Unions

UNION loại bỏ các bản ghi trùng lặp trước khi trả lại kết quả UNION ALL giữ lại tất cả các bản ghi từ hai tập ban đầu.

Toán tử Union để kết nốt 2 câu lệnh Select Cú pháp

SELECT statement

UNION [ALL]

SELECT statement

Ví dụ

SELECT c.City FROM Customers c UNION SELECT e.City FROM Employees e

Lệnh SELECT INTO – Tạo bảng

• Ta có thể tạo table mới dựa vào tập kết quả của câu lệnh Select. Table mới có thể là table tạm hay là một table thực sự trong DB.

• Cú pháp:

SELECT *|ColumnNam1, ColumnName2,…

INTO TableName

FROM Tables

WHERE Condition

ORDER By SortFieldName

GROUP BY FieldGroupName

Lệnh SELECT INTO – Tạo bảng

1) Tạo Table Tạm

SELECT CompanyName as name, Phone

INTO #Temp_Customer

FROM Customers

Xem kết quả

Select * Form #Temp_Customer

Lệnh SELECT INTO – Tạo bảng

Ví dụ 2

SELECT c.Customerid As Name, Orderid, OrderDate

INTO Customer_Order

FROM Customers as c INNER Join Orders As o

WHERE Month(OrderDate) =7

ON c.Customerid =o.Customerid

Lệnh SELECT INTO – Tạo bảng

Ví dụ 3 : Tạo bảng dữ liệu từ DataBase khác

USE SalesDB

SELECT CompanyName As Name, Phone

FROM NorthWind.dbo.Customers

INTO KhachHang

Sửa chữa dữ liệu

– The INSERT Statement – The UPDATE Statement – The DELETE Statement – Modifying Data Using XML

Lệnh Insert

• Thêm một hay nhiều dòng dữ liệu bào bảng hay một

– Dữ liệu chèn phải đúng kiểu dữ liệu của cột. – Không nhập giá trị Null vào cột Not Null. – Tuân thủ đúng các toàn vẹn dữ liệu, bẫy lỗi. – Chỉ định danh sách các giá trị ứng với các cột. – Cột có thuộc tính Identity thì không cần chỉ định giá trị. – Tại một thời điểm chỉ có thể chèn vào một bảng duy

nhất.

view. • Lưu ý:

Lệnh INSERT

• Cú pháp 1

INSERT [(Col1, Col2, … )] VALUES (Value1, Value2,…)

• Ví dụ 1 INSERT Products (ProductID, ProductName)

VALUES (123, 'Ice Tea')

Lệnh Insert

INSERT [(Col1, Col2, … )] SELECT (Value1, Value2,…)

• Cú pháp 2 • Ví dụ 2 INSERT NewProducts (Od.ProductID, P.ProductName) SELECT OD.ProductID, ProductName FROM Products as P INNER JOIN [Order Details] AS Od ON P. ProductID = Od. ProductID WHERE CategoryID<>2

Lệnh UPDATE

• Thay đổi/cập nhật dữ liệu trong một bảng

UPDATE SET = [ FROM ] [ WHERE

• Ví dụ

WHERE Discount<>0 AND ProductId=2

UPDATE [Order Details] SET UnitPrice=

( SELECT UnitPrice+ UnitPrice*0.2 FROM Products

WHERE ProductId=2)

WHERE ProductId=2

UPDATE [Order Details] SET Discount=Discount+0.05

Lệnh DELETE

• Xóa một hay nhiều dòng trong một bảng hay một truy

•

vấn DELETE [ FROM ] [ WHERE Ví dụ DELETE [Order Details] FROM [Order Details] od, Orders o WHERE o.CustomerID = „ALFKI‟ AND o.OrderID = od.OrderID

Lệnh TRUNCATE TABLE

• Lệnh TRUNCATE TABLE dùng để

– Xóa toàn bộ dữ liệu trong một bảng – Về chức năng hoàn toàn giống như câu lệnh DElete – Nhanh hơn câu lệnh delete – Không bật các bẫy lỗi trigger TRUNCATE TABLE table_name

• Ví dụ

TRUNCATE TABLE NewProducts

Viewing Tables

Syntax: Viewing table information

sp_help

Example: Viewing table information

Sp_help Customers

Removing tables

Syntax

DROP TABLE

Example

DROP TABLE Sanpham

Default Object

• Default objects offer another way of defining a

default value for a column. Default objects, called “defaults,” have been in SQL Server since the very first version. Defaults are not really part of declarative integrity because they are not part of the table structure; they are actually part of the database schema, which is the overall definition of every database object.