Bài giảng Hệ cơ sở dữ liệu - Chương 7: Ngôn ngữ SQL

Bài 7Bài 7

ữ ữ

Ngôn Ng SQL Ngôn Ng SQL

ầ

Tr n Thi Kim Chi

Nội Dung Nội Dung

ữ ữ

i thi u Ngôn Ng SQL i thi u Ngôn Ng SQL

ệ ệ

• Gi ệ ớ Gi ớ ệ ệ Nhóm l nh DDL ệ Nhóm l nh DDL ệ Nhóm l nh DML ệ Nhóm l nh DML Câu l nh Select Câu l nh Select

ầ

Tr n Thi Kim Chi

Giới thiệu Ngôn Ngữ SQL Giới thiệu Ngôn Ngữ SQL

Microsoft SQL Server là một hệ quản trị cơ sở dữ liệu Microsoft SQL Server là một hệ quản trị cơ sở dữ liệu quan hệ (Relation database management system – quan hệ (Relation database management system – RDBMS) chạy trên hệ thống mạng Windows NT 4 hay RDBMS) chạy trên hệ thống mạng Windows NT 4 hay Windows.. Windows

ượ ượ ố ố c công b vào năm 1989, 1992 và c công b vào năm 1989, 1992 và

ẩ Chu n ANSI SQL đ ẩ Chu n ANSI SQL đ 1999 1999

ặ ặ ủ ủ Đ c đi m c a SQL: Đ c đi m c a SQL:

ữ ữ

Tr n Thi Kim Chi

ể ể ữ ự ế Là ngôn ng t a ti ng Anh ữ ự ế Là ngôn ng t a ti ng Anh ủ ụ Ngôn ng phi th t c ủ ụ Ngôn ng phi th t c ầ

ớ ớ

Gi Gi

ệ i thi u ệ i thi u

ệ ệ

ữ ị ữ ị

ữ ữ

ữ ề ữ ề

• Các l nh trong SQL có th phân làm 3 lo i: ạ ể Các l nh trong SQL có th phân làm 3 lo i: ạ ể – Ngôn ng đ nh nghĩa d li u (Data Definition ữ ệ Ngôn ng đ nh nghĩa d li u (Data Definition ữ ệ Language commands DDL) Language commands DDL) – Ngôn ng thao tác d li u (Data Manipulation ữ ệ Ngôn ng thao tác d li u (Data Manipulation ữ ệ Language commands DML) Language commands DML) – Ngôn ng đi u khi n d li u (Data Control ể ữ ệ Ngôn ng đi u khi n d li u (Data Control ể ữ ệ Language commands DCL) Language commands DCL)

ầ

Tr n Thi Kim Chi

ớ ớ

Gi Gi

ệ i thi u SQL ệ i thi u SQL

– Data Definition Language Statements (DDL) Data Definition Language Statements (DDL)

– Data Control Language Statements (DCL) Data Control Language Statements (DCL)

ầ

Tr n Thi Kim Chi

– Data Manipulation Language Statements (DML) Data Manipulation Language Statements (DML)

Creating a New Database Creating a New Database

 Cú pháp lệnh tạo CSDL :

CREATE DATABASE database_name [ ON [ < filespec > [ ,...n ] ] [ , < filegroup > [ ,...n ] ] ] [ LOG ON { < filespec > [ ,...n ] } ]

 Cú pháp Filespec:

(NAME = logical_name, FILENAME = 'path\filename', SIZE = size_in_MB, MAXSIZE = size_in_MB | UNLIMITED, ầ Tr n Thi Kim Chi FILEGROWTH = %_or_MB)

Creating a New Database Creating a New Database

ầ

Tr n Thi Kim Chi

• Some arguments: Some arguments: – The name of the database The name of the database – The size of the database The size of the database – The files where the database will reside The files where the database will reside CREATE DATABASE Sample CREATE DATABASE Sample ON ON PRIMARY ( NAME=SampleData, PRIMARY ( NAME=SampleData, FILENAME='c:\Program Files\..\..\Data\Sample.mdf', FILENAME='c:\Program Files\..\..\Data\Sample.mdf', SIZE=10MB, SIZE=10MB, MAXSIZE=15MB, MAXSIZE=15MB, FILEGROWTH=20%) FILEGROWTH=20%) LOG ON LOG ON ( NAME=SampleLog, ( NAME=SampleLog, FILENAME= 'c:\Program Files\..\..\Data\Sample.ldf', FILENAME= 'c:\Program Files\..\..\Data\Sample.ldf', SIZE=3MB, SIZE=3MB, MAXSIZE=5MB, MAXSIZE=5MB, FILEGROWTH=1MB) FILEGROWTH=1MB) COLLATE SQL_Latin1_General_Cp1_CI_AS COLLATE SQL_Latin1_General_Cp1_CI_AS

Creating a New Database Creating a New Database

CREATE DATABASE Sales CREATE DATABASE Sales ON PRIMARY ON PRIMARY ( NAME = Sales1_dat, FILENAME = ‘D:\BTSQL\Sales_dat.mdf', ( NAME = Sales1_dat, FILENAME = ‘D:\BTSQL\Sales_dat.mdf',

SIZE = 10, MAXSIZE = 50, FILEGROWTH = 15% ), SIZE = 10, MAXSIZE = 50, FILEGROWTH = 15% ),

( NAME = Sales2_dat, FILENAME = ‘D:\BTSQL\ ( NAME = Sales2_dat, FILENAME = ‘

Sales2_dat.ndf', D:\BTSQL\Sales2_dat.ndf',

SIZE = 10, MAXSIZE = 50, FILEGROWTH = 15% ), SIZE = 10, MAXSIZE = 50, FILEGROWTH = 15% ),

FILEGROUP SalesGroup1 FILEGROUP SalesGroup1 ( NAME = Sales3_dat, FILENAME = ‘D:\BTSQL\ ( NAME = Sales3_dat, FILENAME = ‘

Sales3_dat.ndf', D:\BTSQL\Sales3_dat.ndf',

SIZE = 10, MAXSIZE = 50, FILEGROWTH = 5 ) SIZE = 10, MAXSIZE = 50, FILEGROWTH = 5 )

Sales4_dat.ndf', D:\BTSQL\Sales4_dat.ndf',

( NAME = Sales4_dat, ( NAME = Sales4_dat, FILENAME = ‘D:\BTSQL\ FILENAME = ‘ SIZE = 10, MAXSIZE = 50, FILEGROWTH = 5 ) SIZE = 10, MAXSIZE = 50, FILEGROWTH = 5 )

LOG ON LOG ON ( NAME = 'Sales_log', ( NAME = 'Sales_log',

ầ

Tr n Thi Kim Chi

FILENAME = ‘D:\BTSQL\salelog.ldf', FILENAME = ‘D:\BTSQL\salelog.ldf', SIZE = 5MB, SIZE = 5MB, MAXSIZE = 25MB, MAXSIZE = 25MB, FILEGROWTH = 5MB ) FILEGROWTH = 5MB )

ừ ừ

Creating a New Database (t Creating a New Database (t menu)menu)

ầ

Tr n Thi Kim Chi

Managing Database Managing Database

• M CSDL ở ởM CSDL

USE TenCSDl USE TenCSDl Ví d :ụVí d :ụ

use Sales use Sales

ự ồ ạ ủ ự ồ ạ ủ

ể ể

• Ki m tra s t n t Ki m tra s t n t

i c a CSDL i c a CSDL sp_helpdb TenCSDL sp_helpdb TenCSDL ử ụ ử ụ

ủ ủ

ể ể

• Ki m tra không gian s d ng c a CSDL Ki m tra không gian s d ng c a CSDL sp_spaceused sp_spaceused

ầ

Tr n Thi Kim Chi

Hiển thị thông tin DB

Managing Databases Managing Databases

filespec [[TO FILEGROUP

ầ

database_name ALTER DATABASE database_name ALTER DATABASE filegroup_name]] TO FILEGROUP filegroup_name ADD FILE filespec ADD FILE filespec ADD LOG FILE filespec ADD LOG FILE logical_filename | | REMOVE FILE REMOVE FILE logical_filename filegroup_name ADD FILEGROUP filegroup_name | | ADD FILEGROUP filegroup_name REMOVE FILEGROUP filegroup_name | | REMOVE FILEGROUP filespec | | MODIFY FILE MODIFY FILE filespec filegroup_name MODIFY FILEGROUP filegroup_name | | MODIFY FILEGROUP filegroup_property filegroup_property ||SETSET optionspec

Tr n Thi Kim Chi termination]] optionspec [[WITHWITH termination

Cú pháp lệnh thay đổi cấu trúc CSDL

Managing Databases Managing Databases

ỉ ỉ

ử ử

ủ ậ ủ ậ

a) Ch nh s a Size c a t p tin a) Ch nh s a Size c a t p tin ALTER DATABASE Sales ALTER DATABASE Sales MODIFY FILE (NAME = ‘Sales_log MODIFY FILE (NAME = ‘

’, size =10MB) Sales_log’, size =10MB)

ữ ệ ữ ệ

ộ ậ ộ ậ

ổ ổ

b) B sung thêm m t t p tin d li u b) B sung thêm m t t p tin d li u

ALTER DATABASE Sales ALTER DATABASE Sales ADD File (Name =Sales_data2, Filename ADD File (Name =Sales_data2, Filename =‘=‘D:\BTSQL\

Sales_data2.mdf,SIZE =10 MB, Maxsize =20MB) D:\BTSQL\Sales_data2.mdf,SIZE =10 MB, Maxsize =20MB)

ầ

Tr n Thi Kim Chi

Ví dụ thay đổi cấu trúc CSDL

Managing Data and Log File Growth Managing Data and Log File Growth

ALTER DATABASE Sample ALTER DATABASE Sample MODIFY FILE ( NAME = 'SampleLog', MODIFY FILE ( NAME = 'SampleLog', SIZE = 15MB) SIZE = 15MB) GO GO

ầ

Tr n Thi Kim Chi

ALTER DATABASE Sample ALTER DATABASE Sample ADD FILE ADD FILE (NAME = SampleData2, (NAME = SampleData2, FILENAME='c:\Program Files\..\..\ FILENAME='c:\Program Files\..\..\ Data\Sample2.ndf', Data\Sample2.ndf', SIZE=15MB, SIZE=15MB, MAXSIZE=20MB) MAXSIZE=20MB) GO GO

Managing Database Managing Database

ủ ủ

• Xem các thu c tính c a CSDL ộ Xem các thu c tính c a CSDL ộ

SELECT DATABASEPROPERTYEX(‘databasename’, SELECT DATABASEPROPERTYEX(‘databasename’,

‘property’) ‘property’)

Property: IsAutoShrink, IsCloseCursorsOnCommitEnabled, Property: IsAutoShrink, IsCloseCursorsOnCommitEnabled, Recovery, Updateability, UserAccess Recovery, Updateability, UserAccess

ầ

Tr n Thi Kim Chi

Managing Data and Log File Growth Managing Data and Log File Growth

Thay đổi thuộc tính DB

Option Option

ALTER DATABASE database_name ALTER DATABASE database_name SET option [, status] SET option [, status]

ULTI_USER ULTI_USER

Example: Example:

ầ

Tr n Thi Kim Chi

READ_ONLY | READ_WRITE READ_ONLY | READ_WRITE

ALTER DATABASE Sales ALTER DATABASE Sales SET Read_Only SET Read_Only

Managing Data and Log File Growth Managing Data and Log File Growth

• Đ i tên c s d li u: ơ ở ữ ệ ổ Đ i tên c s d li u: ơ ở ữ ệ ổ sp_renamedb [ @dbname = ] 'old_name', [ sp_renamedb [ @dbname = ] 'old_name', [ @newname = ] 'new_name‘ @newname = ] 'new_name‘

VD: Sp_ReNamedb ‘Sales’, ‘Banhang’ VD: Sp_ReNamedb ‘Sales’, ‘Banhang’

ầ

Tr n Thi Kim Chi

Managing Data and Log File Growth Managing Data and Log File Growth

ấ ả ấ ả

ủ ủ

ậ ậ

t c các file v t lý c a t c các file v t lý c a

ơ ở ữ ệ Xóa c s d li u: ơ ở ữ ệ Xóa c s d li u: • Khi 1 CSDL b xóa thì t ị Khi 1 CSDL b xóa thì t ị ẽ ị nó s b xóa ẽ ị nó s b xóa • Cú pháp: Cú pháp:

database_name DROP DATABASE database_name DROP DATABASE

• Ví d :ụVí d :ụ

Drop database Banhang Drop database Banhang

ể ể

ầ

Chú ý: Không th xóa các CSDL master, model, Chú ý: Không th xóa các CSDL master, model, tempdb tempdb Tr n Thi Kim Chi 17

System Data Types System Data Types

ể ể

ữ ệ ữ ệ

Có 2 nhóm: Có 2 nhóm: • SystemSupplied datatype:

c h tr b i SQL Server. c h tr b i SQL Server.

ữ ệ ữ ệ

ơ Các ki u d li u c ơ SystemSupplied datatype: Các ki u d li u c ả ượ ỗ ợ ở b n đ ả ượ ỗ ợ ở b n đ • Userdefined datatype:

ủ ủ ể ữ ệ ể ữ ệ

ự ị ự ị

ự ự

i dùng t i dùng t

ể Các ki u d li u c a ể Userdefined datatype: Các ki u d li u c a ườ đ nh nghĩa d a trên các ki u d li u ng ườ đ nh nghĩa d a trên các ki u d li u ng ơ ả c b n. ơ ả c b n.

ầ

Tr n Thi Kim Chi

System Data Types System Data Types

ầ

Tr n Thi Kim Chi

System Data Types System Data Types

ầ

Tr n Thi Kim Chi

ả ả

ữ ệ ữ ệ B ng d li u Table B ng d li u Table

ộ ả ộ ả

ị ị

c t o m t b ng c t o m t b ng ộ ể ữ ệ ủ Xác đ nh ki u d li u c a các c t. ộ ể ữ ệ ủ Xác đ nh ki u d li u c a các c t. ể ộ Xác đ nh các c t có th ho c không th có ể ộ Xác đ nh các c t có th ho c không th có

ể ể ặ ặ

ị ị null value).). null value

ộ ộ ả ả

ị ị ị ị ị ị

ộ ộ ị ị ấ ị Xác đ nh các c t ph i có các giá tr duy nh t. ấ ị Xác đ nh các c t ph i có các giá tr duy nh t. ạ Xác đ nh khóa chính – khóa ngo i. ạ Xác đ nh khóa chính – khóa ngo i. ị ặ ị Xác đ nh các giá tr m c đ nh. ị ặ ị Xác đ nh các giá tr m c đ nh. ộ Xác đ nh các ràng bu c trên các c t (mô t ộ Xác đ nh các ràng bu c trên các c t (mô t ả ả – B – B – B – B

ỉ ụ ủ ả ỉ ụ ủ ả ạ ạ ả ả T o b ng và các ch m c c a b ng. T o b ng và các ch m c c a b ng.

• Các b ướ ạ Các b ướ ạ – B ướ ướB c 1: c 1: – B ướ ướB c 2: c 2: ị ỗ giá tr r ng ( ị ỗ giá tr r ng ( ướ ướB c 3: c 3: ướ ướB c 4: c 4: ướ ướB c 5: c 5: ướ ướB c 6: c 6: ị ề mi n tr ). ị ề mi n tr ). ướ ướB c 7: c 7:

ầ

Tr n Thi Kim Chi

– B

Tạo bảng - CREATE TABLE

owner ] .|] .|owner able_name owner.] t.] table_name

CREATE TABLE CREATE TABLE database_name.[ .[ owner [ [ database_name ({ < column_definition > ({ < column_definition > omputed_column_expression column_name ASAS c computed_column_expression ||column_name | < table_constraint > ::= [ CONSTRAINT | < table_constraint > ::= [ CONSTRAINT constraint_name ] }] } constraint_name [ { PRIMARY KEY | UNIQUE [ { PRIMARY KEY | UNIQUE } [ ,... } [ ,...n n ]]

))

ầ

[ [ ONON { { filegroup [ [ TEXTIMAGE_ON filegroup | | DEFAULT } ] DEFAULT } ] TEXTIMAGE_ON { { filegroup } ] DEFAULT } ] filegroup | | DEFAULT Tr n Thi Kim Chi

Tạo bảng - CREATE TABLE

Cú pháp

Ví dụ

ầ

CREATE TABLE ( ,….)

CREATE TABLE Sanpham ( Masp CHAR(5), Tensp VARCHAR(15), Dvt VARCHAR(10), Dongia SMALLMONEY, SlTon INT ) Tr n Thi Kim Chi

Tạo bảng - CREATE TABLE

IDENTITY [ ( seed , increment )]  Tạo giá trị gia tăng duy nhất cho 1 cột, và cột này

 Giá trị được gán thường là các kiểu dữ liệu sau: tinyint,

thường được dùng khoá chính cho bảng.

ầ

Tr n Thi Kim Chi

smallint, int, bigint, decimal(p,0), hay numeric(p,0).  Trong mỗi bảng chỉ cho phép 1 cột là identity mà thôi.  Seed: là giá trị đầu tiên được tạo.  Increment:là bước tăng để tạo ra giá trị kế tiếp.  Giá trị mặc định thường là (1,1).

Tạo bảng - CREATE TABLE

Cú pháp : Tạo cột có giá trị phát sinh tự động

Ví dụ

CREATE TABLE ( INDENTITY(seed[, Increment]) NOT NULL….)

ầ

Tr n Thi Kim Chi

CREATE TABLE NhaCungCap (MaNCC int Identity NOT NULL Primary key, TenNCC VarChar(25))

Tạo bảng - CREATE TABLE

Cột tính toán - Computed column  Cú pháp:

column_name AS computed_column_expression

 Là một cột ảo không được lưu trữ vật lý trong bảng. Nó được tính toán dựa vào các cột khác trong cùng bảng thông qua 1 biểu thức. Ví dụ : cost AS price * qty.

 Được dùng trong mệnh đề SELECT, WHERE, hay ORDER BY.

Không thể dùng trong lệnh INSERT hay UPDATE

 Được dùng như giá trị khóa trong chỉ mục hay 1 phần của các ràng buộc PRIMARY KEY hay UNIQUE nếu giá trị của nó được định nghĩa bởi 1 biểu thức xác định và kiểu dữ liệu của giá trị trả về hợp lệ.

ầ

 Ví dụ: Cột tính toán a+b có thể được dùng làm chỉ mục nhưng Tr n Thi Kim Chi

a+DATEPART(dd, GETDATE()) không thể dùng làm chỉ mục

Tạo bảng - CREATE TABLE

Cột tính toán - Computed column  Ví dụ 1

CREATE TABLE cthoadon ( sohd int NOT NULL,

MaHang char(5) NOT NULL, SoLuong int NOT NULL, DonGia money, ThanhTien AS SoLuong*DonGia

ầ

Tr n Thi Kim Chi

)

ẹ ữ ệ Toàn v n d li u (Data ẹ ữ ệ Toàn v n d li u (Data integrity) integrity)

ả ả

ể ả ể ả

ẹ ữ ệ ẹ ữ ệ

ơ ở ữ ệ ơ ở ữ ệ

ủ ữ ệ ủ ữ ệ ể ả ể ả

ủ ụ ủ ụ

ẹ ẹ

trigger, trigger,

ẹ ẹ

ủ ơ ở ữ ệ ủ ơ ở ữ ệ

ặ ặ

ộ ộ

• Tính toàn v n d li u đ đ m b o ch t ấ Tính toàn v n d li u đ đ m b o ch t ấ ượ ng c a d li u trong c s d li u. l ượ ng c a d li u trong c s d li u. l • Có hai cách đ đ m b o tính toàn v n d ẹ ữ ả Có hai cách đ đ m b o tính toàn v n d ẹ ữ ả li u:ệli u:ệ – Toàn v n th t c (Procedural integrity: Toàn v n th t c (Procedural integrity: stored procedure…) stored procedure…) – Toàn v n khai báo (Declarative integrity): khai Toàn v n khai báo (Declarative integrity): khai báo các thu c tính c a c s d li u ho c báo các thu c tính c a c s d li u ho c table (contraint, default, rule) (contraint, default, rule) table

ầ

Tr n Thi Kim Chi

ẹ ữ ệ Toàn v n d li u (Data ẹ ữ ệ Toàn v n d li u (Data integrity) integrity)

ạ ả ạ ả

ả ả

ề ề

ự ự

ả ả

ị ị

• Có 4 lo i b o toàn d li u ữ ệ ữ ệ Có 4 lo i b o toàn d li u – Domain integrity (B o toàn mi n) Domain integrity (B o toàn mi n) – Entity integrity (B o toàn th c th ) ể ả Entity integrity (B o toàn th c th ) ả ể – Referential integrity (B o toàn tham chi u) ế Referential integrity (B o toàn tham chi u) ế – User – defined integrity (B o toàn do ng ườ i User – defined integrity (B o toàn do ng ườ i dùng qui đ nh) dùng qui đ nh)

ầ

Tr n Thi Kim Chi

Constrans – Các ràng bu cộ Constrans – Các ràng bu cộ

ộ ộ

ỗ ợ ỗ ợ

Domain integrity

Entity integrity

Ràng Bu cộ m c ứ c tộ

• SQL h tr các lo i ràng bu c sau: ạ ạ SQL h tr các lo i ràng bu c sau: – Default Default – Check Check – Unique Unique – Primary key Primary key – Foregin key Foregin key

ả

ộ

ậ ả

ầ

Referential integrity ị ể ả Các ràng bu c dùng đ đ m b o các giá tr ủ ữ ệ ạ c a d li u không vi ph m qui lu t b o ữ ệ toàn d li u. Tr n Thi Kim Chi

Ràng Bu cộ m c ứ B ngả

ự ự

ả ả

ể ể

B o toàn th c th B o toàn th c th

ự ự

xác xác

ẹ ẹ ộ ộ

ự ự

ể ể

ẹ ẹ ủ ả ủ ả

• Toàn v n th c th (Entity Integrity): ể ể Toàn v n th c th (Entity Integrity): ư ị ộ đ nh m t dòng nh là m t th c th duy ư ị ộ đ nh m t dòng nh là m t th c th duy ụ ể ộ ả ấ nh t trong m t b ng c th . ụ ể ộ ả ấ nh t trong m t b ng c th . – Tính toàn v n th c th th hi n tính toàn v n ể ể ệ ự ẹ Tính toàn v n th c th th hi n tính toàn v n ể ể ệ ự ẹ ặ ộ ị ằ b ng c t đ nh danh ho c khóa chính c a b ng. ộ ị ặ ằ b ng c t đ nh danh ho c khóa chính c a b ng.

ầ

Tr n Thi Kim Chi

ự ự

ả ả

ể ể

B o toàn th c th B o toàn th c th

chính chính

ộ ộ

ấ ấ

• Ràng bu c khóa ộ ộ Ràng bu c khóa (Primary Key (Primary Key ộ ộ ả : khóa chính trong m t b ng là m t ộ ộ ả Constraints): khóa chính trong m t b ng là m t Constraints) ộ ậ ể ượ ử ụ ặ ộ c s d ng đ c t ho c m t t p các c t mà nó đ ộ ậ ượ ử ụ ặ ể ộ c s d ng đ c t ho c m t t p các c t mà nó đ ộ ả ộ ị xác đ nh m t dòng duy nh t trong m t b ng. ộ ả ộ ị xác đ nh m t dòng duy nh t trong m t b ng. – Cú pháp Cú pháp: :

CREATE TABLE table_name (

column_name data_type NOT NULL [CONSTRAINT constraintname] PRIMARY KEY

ầ

Tr n Thi Kim Chi

)

ự ự

ả ả

ể ể

B o toàn th c th B o toàn th c th

ộ ậ ộ ậ

ế ế

• N u khóa chính là m t t p nhi u c t: ề ộ N u khóa chính là m t t p nhi u c t: ề ộ

CREATE TABLE table_name

(

column_name data_type[,…]

[CONSTRAINT constraintname]

PRIMARY KEY{(column1[ASC|DESC][,…

columnN])}

ầ

Tr n Thi Kim Chi

)

ự ự

ả ả

ể ể

B o toàn th c th B o toàn th c th

 Ví d : ụVí d : ụ CREATE TABLE Monhoc CREATE TABLE Monhoc ((

MaMH char(10) NOT NULL CONSTRAINT MaMH char(10) NOT NULL CONSTRAINT PK_MonHoc PRIMARY KEY PK_MonHoc PRIMARY KEY ););

CREATE TABLE Ketqua CREATE TABLE Ketqua ((

ầ

Tr n Thi Kim Chi

masv char(10) not null, masv char(10) not null, mamh varchar(40) not null, mamh varchar(40) not null, Diem float not null, Diem float not null, Primary key (masv, mamh) Primary key (masv, mamh)

););

Bảo toàn thực thể

• Ví d 3ụVí d 3ụ

CREATE TABLE DEAN CREATE TABLE DEAN ( (

MADA smallint PRIMARY KEY, MADA smallint PRIMARY KEY, TENDA varchar(50) NOT NULL TENDA varchar(50) NOT NULL

))

ầ

Tr n Thi Kim Chi

Bảo toàn thực thể

Ví d 4: ụVí d 4: ụ CREATE TABLE PHANCONG ( CREATE TABLE PHANCONG (

ầ

Tr n Thi Kim Chi

, Mada smallint, Sonc int, Manv int NOT NULL, Mada smallint, Sonc int, Manv int NOT NULL primary key (Manv,Mada)) primary key (Manv,Mada))

ự ự

ả ả

ể ể

B o toàn th c th B o toàn th c th

ợ ợ

ộ ộ ộ ộ

• Unique Constraints ệ ệ

ể ể

ề ề

ị ị

ỉ ỉ

ặ ặ

ộ ộ

ỉ ỉ

ể ể ề ộ ề ộ

ị ị

ị ị ấ ấ ế ế

ộ ộ

ị ị

ị ể Unique Constraints: : đ duy trì các giá tr đ duy trì các giá tr ể ị ộ ậ t trong m t c t hay t p h p các c t riêng bi ộ ậ t trong m t c t hay t p h p các c t riêng bi không tham gia vào khóa chính. không tham gia vào khóa chính. – Có th ch đ nh nhi u Unique constraint trên m t ộ Có th ch đ nh nhi u Unique constraint trên m t ộ b ngảb ngả – Có th ch đ nh Unique constraint trên m t ho c Có th ch đ nh Unique constraint trên m t ho c ậ nhi u c t ch p nh n giá tr NULL. ậ nhi u c t ch p nh n giá tr NULL. – Tuy nhiên n u ch đ nh Unique constraint trên m t ộ ị ỉ Tuy nhiên n u ch đ nh Unique constraint trên m t ỉ ộ ị ậ ỉ ấ ộ c t thì c t đó ch ch p nh n m t giá tr NULL. ậ ỉ ấ ộ c t thì c t đó ch ch p nh n m t giá tr NULL.

ầ

Tr n Thi Kim Chi

ự ự

ả ả

ể ể

B o toàn th c th B o toàn th c th

• Cú pháp khai báo unique constraint Cú pháp khai báo unique constraint CREATE TABLE table_name column_name data_type ( [CONSTRAINT constraint_name] UNIQUE

Tr n Thi Kim Chi

) Ví d :ụVí d :ụ HoaDon CREATE TABLE HoaDon CREATE TABLE (MaHD int NOT NULL CONSTRAINT PK_ORDERS (MaHD int NOT NULL CONSTRAINT PK_ORDERS PRIMARY KEY, SoHD int NULL CONSTRAINT PRIMARY KEY, SoHD int NULL CONSTRAINT UNIQUE)) UQ_ORDER_NUMBER UNIQUE UQ_ORDER_NUMBER ầ

ự ự

ả ả

ể ể

B o toàn th c th B o toàn th c th

Ràng buộc Unique ị ị

ụ ụ

ứ ộ ứ ộ Ví d 2: Đ nh nghĩa m c c t Ví d 2: Đ nh nghĩa m c c t CREATE TABLE Events ( CREATE TABLE Events (

int NOT NULL UNIQUE,, EventID int NOT NULL UNIQUE EventID

EventTitle nvarchar (100) NULL , EventTitle nvarchar (100) NULL ,

EventDescription ntext NULL) EventDescription ntext NULL) ứ ả ứ ả

ụ ụ

ị ị

Ví d 3: Đ nh nghĩa m c b ng Ví d 3: Đ nh nghĩa m c b ng CREATE TABLE Orders ( CREATE TABLE Orders (

OrderID int IDENTITY (1, 1) NOT NULL, OrderID int IDENTITY (1, 1) NOT NULL,

UNIQUE NONCLUSTERED (OrderID) WITH CustomerID nchar (5), UNIQUE NONCLUSTERED (OrderID) WITH CustomerID nchar (5), FILLFACTOR=90 ) ) FILLFACTOR=90

ầ

Tr n Thi Kim Chi

ề ề

ả ả

ị B o toàn mi n giá tr ị B o toàn mi n giá tr

ẹ ẹ

ộ ộ

ỏ ỏ

ệ ệ

ầ ầ ẹ ẹ

ị ự ị ự

ề ề

ạ ạ

ị ị

• Toàn v n mi n giá tr (Domain Integrity): ị ề ị ề Toàn v n mi n giá tr (Domain Integrity): ậ ữ ệ ể ki m tra d li u nh p vào các c t có th a ậ ữ ệ ể ki m tra d li u nh p vào các c t có th a ề đi u ki n ban đ u không ề đi u ki n ban đ u không • Ki m tra toàn v n mi n giá tr d a vào: ể Ki m tra toàn v n mi n giá tr d a vào: ể – Ki u d li u. ể ữ ệ Ki u d li u. ể ữ ệ – Đ nh d ng : thông qua CHECK constraints và rules ạ ị Đ nh d ng : thông qua CHECK constraints và rules ạ ị – Ph m vi giá tr thông qua FOREIGN KEY Ph m vi giá tr thông qua FOREIGN KEY constraints, CHECK constraints, DEFAULT, NOT constraints, CHECK constraints, DEFAULT, NOT NULL, rules. NULL, rules.

ầ

Tr n Thi Kim Chi

ề ề

ả ả

ị B o toàn mi n giá tr ị B o toàn mi n giá tr

• Check Constraints:

ề ề

ậ ậ

c chèn vào m t c t tr c chèn vào m t c t tr ề ề

ể

ẹ ự giúp th c thi toàn v n ẹ ự Check Constraints: giúp th c thi toàn v n ể ặ ằ mi n b ng cách xác nh n ho c ki m tra ể ặ ằ mi n b ng cách xác nh n ho c ki m tra ộ ộ ướ ữ ệ ượ c các d li u đ ộ ộ ướ ữ ệ ượ c các d li u đ ị ậ ấ ể khi ch p nh n giá tr . Có th có nhi u ậ ấ ị ể khi ch p nh n giá tr . Có th có nhi u ộ ộ ràng bu c ộ trong m t c t trong m t c t ểki m tra ộ ộ ràng bu c ộ ki m tra

CREATE TABLE table_name (column_name data_type [CONSTRAINT constraint_name] CHECK (logical expression)

ầ

Tr n Thi Kim Chi

ề ề

ả ả

ị B o toàn mi n giá tr ị B o toàn mi n giá tr

• Ví dụVí dụ:: CREATE TABLE nhanvien CREATE TABLE nhanvien ( ( manv smallint PRIMARY KEY CLUSTERED, manv smallint PRIMARY KEY CLUSTERED, tennv varchar(50) NOT NULL , tennv varchar(50) NOT NULL , CHECK(tuoimin>=18),), tuoimin tinyint NOT NULL CHECK(tuoimin>=18 tuoimin tinyint NOT NULL CHECK(tuoimax<=40)) tuoimax tinyint NOT NULL CHECK(tuoimax<=40 tuoimax tinyint NOT NULL ) )

ầ

Tr n Thi Kim Chi

ề ề

ả ả

ị B o toàn mi n giá tr ị B o toàn mi n giá tr

CREATE TABLE Orders ( CREATE TABLE Orders (

OrderID int IDENTITY (1, 1) NOT NULL, OrderID int IDENTITY (1, 1) NOT NULL,

CustomerID nchar (5) CHECK (CustomerID LIKE ‘[AZ][AZ] CustomerID nchar (5) CHECK (CustomerID LIKE ‘[AZ][AZ]

[AZ][AZ][AZ]’), [AZ][AZ][AZ]’),

EmployeeID int NULL, OrderDate datetime NULL EmployeeID int NULL, OrderDate datetime NULL

CHECK (OrderDate BETWEEN ‘01/01/70’ AND GETDATE()), CHECK (OrderDate BETWEEN ‘01/01/70’ AND GETDATE()),

RequiredDate datetime NULL, ShipVia int NULL RequiredDate datetime NULL, ShipVia int NULL

CHECK (ShipVia IN (1, 2, 3, 4)), CHECK (ShipVia IN (1, 2, 3, 4)),

Freight money NULL CHECK (Freight>=0), Freight money NULL CHECK (Freight>=0),

ShipCountry nvarchar (15), ShipCountry nvarchar (15),

ầ

Tr n Thi Kim Chi

CHECK (RequiredDate>OrderDate)) CHECK (RequiredDate>OrderDate))

ề ề

ả ả

ị B o toàn mi n giá tr ị B o toàn mi n giá tr

ị ặ ị ị ặ ị

ể ể ả ả

ạ ạ ừ ộ ừ ộ ụ ụ ể ể ộ ộ

ị ằ ị ằ ư ư ỗ ỗ ự , ự ,

ầ

• constant_expression: c ệ ố ệ ố • Default constraint: ộ ộ Gán giá tr m c đ nh cho m t c t ộ ộ Default constraint: Gán giá tr m c đ nh cho m t c t • DEFAULT có th áp d ng cho b t k c t nào trong b ng ấ ỳ ộ DEFAULT có th áp d ng cho b t k c t nào trong b ng ấ ỳ ộ ngo i tr c t có ki u timestamp hay có thu c tính ngo i tr c t có ki u timestamp hay có thu c tính IDENTITY. IDENTITY. ỉ constant_expression: ch có giá tr h ng nh chu i ký t h có giá tr h ng nh chu i ký t ỉ ị hàm h th ng, hay giá tr NULL ị hàm h th ng, hay giá tr NULL

CREATE TABLE Table_name (Column_name Datatype [NULL| NOT NULL] [CONSTRAINT Constraint_name] Tr n Thi Kim Chi expression[…]) DEFAULT

ề ề

ả ả

ị B o toàn mi n giá tr ị B o toàn mi n giá tr

Ví d 1ụ

int, LoaiHD Char(1) DEFAULT

‘X’, NgayLap

CREATE TABLE HoaDon (MaHD DateTime NOT NULL)

ầ

Tr n Thi Kim Chi

ả ả

ế B o toàn tham chi u ế B o toàn tham chi u

ế

ộ

các m i quan h đ các m i quan h đ ả ảb ng khi các b ng khi

ậ ậ

ế ượ ự ế ượ ự

ố c d a trên m i ố c d a trên m i khóa ngo iạ và các khóa và các khóa ạ ữ khóa ngo i và khóa duy khóa ngo i và khóa duy ạ ữ

ấ

• Referential Integrity ẹ Tính toàn v n tham Referential Integrity:: Tính toàn v n tham ẹ ệ ượ ố c xác ếchi u, duy trì ệ ượ ố c xác chi u, duy trì c ượ ữ các ị c ượđ ộm t record đ nh gi a ữ ị đ m t record đ nh gi a ặ nh p vào ho c xóa. ặ nh p vào ho c xóa. • Toàn v n tham chi u đ ẹ Toàn v n tham chi u đ ẹ ệ ữ khóa ngo iạ quan h gi a ệ ữ quan h gi a ặ chính ho c gi a ặ chính ho c gi a .. ấnh t (unique keys) nh t (unique keys)

ầ

Tr n Thi Kim Chi

ả ả

ế B o toàn tham chi u ế B o toàn tham chi u

ộ ộ

ể ể

ế ế

ườ i ườ i

h ệh ệ n u ến u ế

ả ả

ộ ả vào m t b ng quan record vào m t b ng quan ộ ả liên quan trong b ng chính. record liên quan trong b ng chính. ộ ả ộ ả

t o ạt o ạ

ộ ả ộ ả

ồ ồ

ế ế

• Ràng bu c tham chi u đ tránh cho ng Ràng bu c tham chi u đ tránh cho ng dùng: dùng: – Thêm m t ộ Thêm m t ộ record không có record không có – Thay đ i các giá tr trong m t b ng chính ị ổ Thay đ i các giá tr trong m t b ng chính ị ổ m côi trong m t b ng liên quan. các record m côi trong m t b ng liên quan. các record – Xóa các b n ghi t ừ ộ ả ả m t b ng chính n u có Xóa các b n ghi t ừ ộ ả ả m t b ng chính n u có quaquan hện hệ record trong b ng ả record trong b ng ả

ầ

Tr n Thi Kim Chi

ả ả

ế B o toàn tham chi u ế B o toàn tham chi u

ộ ộ

Ràng bu c Foreign key Ràng bu c Foreign key

ữ ữ ả c t o ra gi a các b ng trong cùng 1 ả c t o ra gi a các b ng trong cùng 1

ộ ộ ộ ộ ế ế ế ế ể ể ỉ ỉ

ầ ủ ầ ủ • Quan h ch có th đ ể ượ ạ ệ ỉ Quan h ch có th đ ể ượ ạ ệ ỉ CSDL và trên cùng 1 server. CSDL và trên cùng 1 server. • Khoá ngo i ch có th tham chi u đ n m t c t sau trong ạ Khoá ngo i ch có th tham chi u đ n m t c t sau trong ạ ả ảb ng chính: b ng chính: – Là 1 c t hay 1 ph n c a ộ Là 1 c t hay 1 ph n c a ộ

ộ ộ

ầ

Tr n Thi Kim Chi

ể ể ạ i đa 253 khoá ngo i và có th tham ạ i đa 253 khoá ngo i và có th tham

khoá chính khoá chính – Là c t có ràng bu c unique ộ Là c t có ràng bu c unique ộ – Là c t có ch m c unique ỉ ụ ộ Là c t có ch m c unique ỉ ụ ộ • M t b ng có th có t ố ể ộ ả M t b ng có th có t ố ể ộ ả ả ế ế chi u đ n 253 b ng khác nhau. ả ế ế chi u đ n 253 b ng khác nhau.

ả ả

ế B o toàn tham chi u ế B o toàn tham chi u

Ràng buộc Foreign key

ị ị ạ ả ạ ả

Đ nh nghĩa FOREIGN KEY CONSTRAIT khi t o b ng Đ nh nghĩa FOREIGN KEY CONSTRAIT khi t o b ng CREATE TABLE TableName CREATE TABLE TableName

ef_column [,..n])]) ) ]]

ầ

Tr n Thi Kim Chi

(columnName datatype [,…], (columnName datatype [,…], [CONSTRAINT constraintName] [CONSTRAINT constraintName] FOREIGN KEY[(column[,..n])] FOREIGN KEY[(column[,..n])] ref_table [ [ ( r( ref_column [,..n])] REFERENCES ref_table REFERENCES [ ON DELETE { CASCADE | NO ACTION } ] [ ON DELETE { CASCADE | NO ACTION } ] [ ON UPDATE { CASCADE | NO ACTION } ] [ ON UPDATE { CASCADE | NO ACTION } ] [ NOT FOR REPLICATION] [ NOT FOR REPLICATION]

ả ả

ế B o toàn tham chi u ế B o toàn tham chi u

Ràng buộc Foreign key

ị ị ầ ầ ự ự

ạ ạ ộ ộ ế ế ệ ệ ệ ệ

ị ị ỏ ỏ ế ế ị ị ặ ặ

ậ ậ ị ị

ẽ ị ậ ẽ ị ậ ậ ậ ị ậ ị ậ ể ể ế ế ế ế

ệ ệ i và vi c i và vi c

ON UPDATE|DELETE {CASCADE | NO ACTION} ON UPDATE|DELETE {CASCADE | NO ACTION} •Xác đ nh hành đ ng c n ph i th c hi n cho 1 hàng trong ả Xác đ nh hành đ ng c n ph i th c hi n cho 1 hàng trong ả ả b ng đang t o n u hàng đó có quan h tham chi u và hàng ả b ng đang t o n u hàng đó có quan h tham chi u và hàng ả ế tham chi u b xoá kh i b ng chính. M c đ nh là NO ế ả tham chi u b xoá kh i b ng chính. M c đ nh là NO ACTION. ACTION. •CASCADE: dùng đ xác đ nh là hàng s b c p nh t/xoá CASCADE: dùng đ xác đ nh là hàng s b c p nh t/xoá ỏ ỏ ả kh i b ng tham chi u n u hàng đó b c p nh t/xóa kh i ỏ ỏ ả kh i b ng tham chi u n u hàng đó b c p nh t/xóa kh i ả ảb ng chính b ng chính •NO ACTION: SQL Server s đ a ra thông báo l ỗ NO ACTION: SQL Server s đ a ra thông báo l ỗ ả xoá hàng trên b ng chính s b t ả xoá hàng trên b ng chính s b t

ẽ ư ẽ ư ẽ ị ừ ố ch i. ẽ ị ừ ố ch i. ầ Tr n Thi Kim Chi

ả ả

ế B o toàn tham chi u ế B o toàn tham chi u

• Ví d :ụVí d :ụ Create table Phongban Create table Phongban int, ((Mapb Mapb int, Tenpb varchar(30), Tenpb varchar(30), Constraint pb_PK primary key (mapb) Constraint pb_PK primary key (mapb) )) Create table nhanvien Create table nhanvien ((manv manv int, Hoten varchar(40), Constraint manv_PK primary key (manv), Constraint manv_PK primary key (manv), Contraint mapb_Fk foreign key(mapb) references Contraint mapb_Fk foreign key(mapb) references

ầ

Tr n Thi Kim Chi

int, int, Hoten varchar(40), Mapb Mapb int,

phongban(mapb) phongban(mapb)

))

ả ả

ế B o toàn tham chi u ế B o toàn tham chi u Ràng buộc Foreign key

• Ví d 1ụVí d 1ụ

CREATE TABLE VITRI CREATE TABLE VITRI (MaVt int Primary key, DiaChi varchar(40)) (MaVt int Primary key, DiaChi varchar(40))

CREATE TABLE PhongBan CREATE TABLE PhongBan

int primary key, ( Mapb int primary key, ( Mapb TenPb varchar(30), TenPb varchar(30), int REFERENCES VITRI(MaVt)) MaVTMaVT int REFERENCES VITRI(MaVt

ầ

Tr n Thi Kim Chi

))

ả ả

ế B o toàn tham chi u ế B o toàn tham chi u

Ràng buộc Foreign key

• Ví d 2ụVí d 2ụ

ầ

CREATE TABLE NHANVIEN ( CREATE TABLE NHANVIEN (

CHAR(9) NOT NULL, manvmanv CHAR(9) NOT NULL, honv VARCHAR(15) NOT NULL, honv VARCHAR(15) NOT NULL, tennv VARCHAR(15) NOT NULL, tennv VARCHAR(15) NOT NULL, ngsinh DATETIME, diachi VARCHAR(30), ngsinh DATETIME, diachi VARCHAR(30), phai CHAR(1), ma_nql CHAR(9), phai CHAR(1), ma_nql CHAR(9), phg INT NOT NULL, phg INT NOT NULL, CONSTRAINT Nv_PK PRIMARY KEY (manv), CONSTRAINT Nv_PK PRIMARY KEY (manv), FOREIGN KEY (phg) CONSTRAINT Nv_fk FOREIGN KEY (phg) CONSTRAINT Nv_fk Tr n Thi Kim Chi

REFERENCES PHONGBAN(mapb)) REFERENCES PHONGBAN(mapb))

ả ả

ế B o toàn tham chi u ế B o toàn tham chi u Ràng buộc Check

• Ví d 4:ụVí d 4:ụ

CREATE TABLE PHANCONG( CREATE TABLE PHANCONG(

ma_nvien CHAR(9) NOT NULL, ma_nvien CHAR(9) NOT NULL, soda INT NOT NULL, soda INT NOT NULL, thoigian DECIMAL(3,1) NOT NULL, thoigian DECIMAL(3,1) NOT NULL, PRIMARY KEY (ma_nvien, soda), PRIMARY KEY (ma_nvien, soda), FOREIGN KEY (ma_nvien) REFERENCES FOREIGN KEY (ma_nvien) REFERENCES

NHANVIEN), NHANVIEN),

Tr n Thi Kim Chi

(cid:0) FOREIGN KEY (soda) REFERENCES DEAN(mada), FOREIGN KEY (soda) REFERENCES DEAN(mada), CHECK (thoigian (cid:0) CHECK (thoigian 0)) 0)) ầ

ả ả

ế B o toàn tham chi u ế B o toàn tham chi u

Ví d :ụVí d :ụ

Create table nhanvien Create table nhanvien

((

int, Hoten varchar(40), manv manv int, Hoten varchar(40), int, Mapb Mapb int, Constraint manv_PK primary key (manv), Constraint manv_PK primary key (manv), Contraint mapb_Fk foreign key(mapb) Contraint mapb_Fk foreign key(mapb) on delete set references phongban(mapb) on delete set references phongban(mapb) default on update cascade default on update cascade

ầ

Tr n Thi Kim Chi

););

ử ấ ử ấ

ả ả

S a c u trúc b ng S a c u trúc b ng

Cú pháp

ALTER TABLE

{ALTER COLUMN }

| {ADD [ ]}

ầ

Tr n Thi Kim Chi

| {DROP COLUMN }

ệ ệ

ả ả

ỉ ỉ

ấ Hi u ch nh c u trúc b ng ấ Hi u ch nh c u trúc b ng

ộ ộ

• Thêm thu c tính (thêm c t) Thêm thu c tính (thêm c t) Cú pháp: Cú pháp:

ộ

ALTER TABLE ả ể ữ ệ ADD [NOT NULL] [CONSTRAINT…]

Ví dụ: thêm cột

ầ

Tr n Thi Kim Chi

ALTER TABLE SanPham ADD NgayNhap SmallDateTime

ệ ệ

ả ả

ỉ ỉ

ấ Hi u ch nh c u trúc b ng ấ Hi u ch nh c u trúc b ng

ộ ộ

• Xóa thu c tính (xóa c t) Xóa thu c tính (xóa c t) Cú pháp: Cú pháp:

ALTER TABLE ả DROP COLUMN ộ [CONSTRAINT…]

ầ

Tr n Thi Kim Chi

ALTER TABLE Sanpham DROP COLUMN NgayNhap

ệ ệ

ả ả

ỉ ỉ

ấ Hi u ch nh c u trúc b ng ấ Hi u ch nh c u trúc b ng

ổ ể ữ ệ ủ ổ ể ữ ệ ủ

ộ ộ

• Thay đ i ki u d li u c a thu c tính Thay đ i ki u d li u c a thu c tính Cú pháp: Cú pháp:

ộ

ể ữ ệ

ALTER TABLE ả ALTER COLUMN [CONSTRAINT…]

ể ữ ệ ụ ử ộ Ví d : s a ki u d li u cho c t

Tr n Thi Kim Chi

ALTER TABLE SanPham ALTER COLUMN NgayNhap DateTime NOT NULL ầ

ệ ệ

ả ả

ỉ ỉ

ấ Hi u ch nh c u trúc b ng ấ Hi u ch nh c u trúc b ng

• Thêm ràng bu cộ Thêm ràng bu cộ Cú pháp: Cú pháp:

ALTER TABLE ả ADD CONSTRAINT…

ầ

Tr n Thi Kim Chi

ệ ệ

ả ả

ỉ ỉ

ấ Hi u ch nh c u trúc b ng ấ Hi u ch nh c u trúc b ng

int, LoaiHD Char(1) DEFAULT

‘X’, NgayLap

CREATE TABLE HoaDon (MaHD DateTime NOT NULL)

ALTER TABLE HoaDon ADD DEFAULT Getdate() FOR NgayLap

Hay

ALTER TABLE HoaDon ADD CONSTRAINT Ngay_DF DEFAULT ầ

Tr n Thi Kim Chi

Getdate() FOR NgayLap

• Thêm ràng bu cộ Thêm ràng bu cộ Ví d 1ụVí d 1ụ ::

ệ ệ

ả ả

ỉ ỉ

ấ Hi u ch nh c u trúc b ng ấ Hi u ch nh c u trúc b ng

• Thêm ràng bu cộ Thêm ràng bu cộ Ví d 2ụVí d 2ụ ::

CREATE TABLE NhanVien CREATE TABLE NhanVien (MaNV char(4) CHECK (Manv LIKE '[09][09][0 9][0 (MaNV char(4) CHECK (Manv LIKE '[09][09][0 9][0 9]‘, Hoten Varchar(40), LCB int CHECK (LCB BETWEEN 9]‘, Hoten Varchar(40), LCB int CHECK (LCB BETWEEN real, Thanhpho varchar(10) 0 AND 50000, HSPC 0 AND 50000, HSPC real, Thanhpho varchar(10) CONSTRAINT chkCity CHECK(Thanhpho IN ('Berkeley', CONSTRAINT chkCity CHECK(Thanhpho IN ('Berkeley', 'Boston', 'Chicago', ' Dallas‘)) 'Boston', 'Chicago', ' Dallas‘))

• HayHay

ALTER TABLE Nhanvien ALTER TABLE Nhanvien

ầ

Tr n Thi Kim Chi

ADD CONSTRAINT NV_HSPC ADD CONSTRAINT NV_HSPC

CHECK (HSPC>=0.1 AND HSPC<0.5) CHECK (HSPC>=0.1 AND HSPC<0.5)

ệ ệ

ả ả

ỉ ỉ

ấ Hi u ch nh c u trúc b ng ấ Hi u ch nh c u trúc b ng

• Thêm ràng bu cộ Thêm ràng bu cộ Ví d 2ụVí d 2ụ ::

• HayHay

ALTER TABLE Nhanvien ALTER TABLE Nhanvien

ầ

Tr n Thi Kim Chi

ADD CONSTRAINT NV_HSPC ADD CONSTRAINT NV_HSPC

CHECK (HSPC>=0.1 AND HSPC<0.5) CHECK (HSPC>=0.1 AND HSPC<0.5)

ệ ệ

ả ả

ỉ ỉ

ấ Hi u ch nh c u trúc b ng ấ Hi u ch nh c u trúc b ng

• Xóa ràng bu cộ Xóa ràng bu cộ Cú pháp: Cú pháp:

ALTER TABLE ả

DROP CONSTRAINT Constraint_name

Ví dụVí dụ

ALTER TABLE Table3

ầ

Tr n Thi Kim Chi

DROP CONSTRAINT Table3_PK

Xóa b ngả Xóa b ngả

ộ ữ ệ ộ ữ ệ

ả ả

• Xóa b ng và toàn b d li u trong b ng ả Xóa b ng và toàn b d li u trong b ng ả Cú pháp: Cú pháp:

DROP TABLE ả

Ví d :ụVí d :ụ

DROP TABLE DEPARTMENT DROP TABLE DEPARTMENT

ầ

Tr n Thi Kim Chi

ả ả

Xóa b ng (Truncate table) Xóa b ng (Truncate table)

ệ ệ

t c các t c các

ả ả

ấ ả ấ ả i phóng i phóng

ồ ồ ữ ả ữ ả

• L nh TRUNCATE TABLE xóa t L nh TRUNCATE TABLE xóa t ờ ả dòng trong b ng đ ng th i gi ờ ả dòng trong b ng đ ng th i gi ư không gian l u tr b ng. ư không gian l u tr b ng. Cú pháp: Cú pháp: TRUNCATE TABLE Tên b ngả

ầ

Tr n Thi Kim Chi

ữ ữ

ữ ệ Ngôn ng thao tác d li u ữ ệ Ngôn ng thao tác d li u

ớ ớ

• Thêm m t record m i vào b ng ả :: ộ ộ ả Thêm m t record m i vào b ng Cú pháp: Cú pháp:

INSERT INTO tablename VALUES ('value1', 'value2',. . . ,'valueN')

– Ví d :ụVí d :ụ Insert into sinhvien values (’01’,’Le van Insert into sinhvien values (’01’,’Le van

A',‘CDTH1A') A',‘CDTH1A')

ầ

Tr n Thi Kim Chi

ữ ữ

ữ ệ Ngôn ng thao tác d li u ữ ệ Ngôn ng thao tác d li u

ậ ữ ệ ậ ữ ệ

• C p nh t d li u trong b ng ả :: ậ ậ ả C p nh t d li u trong b ng – Cú pháp: Cú pháp:

UPDATE table name SET attribute value=new value WHERE condition

– Ví d :ụVí d :ụ

Tr n Thi Kim Chi

update SinhVien update SinhVien set MaLop=‘CDTH1A' set MaLop=‘CDTH1A' ầ where MaSV=‘A01' where MaSV=‘A01'

ữ ữ

ữ ệ Ngôn ng thao tác d li u ữ ệ Ngôn ng thao tác d li u

• Xóa các record trong b ng:ả Xóa các record trong b ng:ả – Cú pháp: Cú pháp:

DELETE FROM table name WHERE condition

• Ví d :ụVí d :ụ

ầ

– Delete from Sinhvien Delete from Sinhvien Where Malop=‘CDTH1A’ Where Malop=‘CDTH1A’ Tr n Thi Kim Chi

Xem Tables Xem Tables

Cú pháp: Xem thông tin Table

sp_help

ữ ệ

Cú pháp: Xem d li u Table

Sp_help cthoadon

ầ

SELECT FROM

Select * from cthoadonTr n Thi Kim Chi

ữ ệ ố ữ ệ ố

ủ ụ ư ủ ụ ư

Th t c l u tr h th ng Th t c l u tr h th ng

sp_help System stored procedure sp_help System stored procedure

ả ả

ượ ạ ượ ạ

c t o hay c t o hay

• Đ ki m tra xem b ng đã đ ể ể Đ ki m tra xem b ng đã đ ể ể ch a?ưch a?ư

ườ i ườ i

ể ữ ệ ủ ể ữ ệ ủ ư ư

sp_help table_name sp_help table_name • Đ ki m tra xem ki u d li u c a ng ể ể Đ ki m tra xem ki u d li u c a ng ể ể dùng đã đ dùng đã đ

ượ ạ c t o hay ch a? ượ ạ c t o hay ch a? sp_help datatype_name sp_help datatype_name

ầ

Tr n Thi Kim Chi

Xem Constraints Xem Constraints

– Viewing Constraints Viewing Constraints • Sp_helpConstraint Events Sp_helpConstraint Events

– Verify constraints by inserting data Verify constraints by inserting data

ầ

• INSERT Events DEFAULT VALUES INSERT Events DEFAULT VALUES

• SELECT * FROM Events SELECT * FROM Events Tr n Thi Kim Chi

BBàài t pậi t pậ

Example

ạ

ạa) T o Table có khóa chính a) T o Table có khóa chính CREATE TABLE KhachHang CREATE TABLE KhachHang (Makh char(5), Tenkh Varchar(40), DiaChi Varchar(50), DienThoai Varchar(50), DienThoai (Makh char(5), Tenkh Varchar(40), DiaChi Nvarchar(10) CONSTRAINT Makh_pk Primary key(Makh)) Nvarchar(10) CONSTRAINT Makh_pk Primary key(Makh)) ạ ạ

ạ ạ b) T o Table có khóa ngo i b) T o Table có khóa ngo i CREATE TABLE HoaDon CREATE TABLE HoaDon

(Mahd Char(5), NgayLap Datetime, Makh Char(5) CONSTRAINT (Mahd Char(5), NgayLap Datetime, Makh Char(5) CONSTRAINT

Mahd_pk Primary key(Mahd) Mahd_pk Primary key(Mahd)

Makh_fk Foreign key References KhachHang CONSTRAINT Makh_fk Foreign key References KhachHang CONSTRAINT (Makh)) (Makh))

ầ

Tr n Thi Kim Chi

Modifyling Table_Defining Constraints

Example

a) ALTER TABLE Sanpham a) ALTER TABLE Sanpham ADD CONSTRAINT Masp_pk Primary ADD CONSTRAINT Masp_pk Primary

key(Masp) key(Masp)

b) ALTER TABLE ChiTietHoaDon b) ALTER TABLE ChiTietHoaDon

ADD CONSTRAINT Masp_Mahd_pk Primary key(Mahd,Masp) ADD CONSTRAINT Masp_Mahd_pk Primary key(Mahd,Masp)

c) ALTER TABLE ChiTietHoaDon c) ALTER TABLE ChiTietHoaDon

ADD CONSTRAINT Masp_fk Foregin key (Masp) References ADD CONSTRAINT Masp_fk Foregin key (Masp) References Sanpham(Masp) Sanpham(Masp)

d) ALTER TABLE ChiTietHoaDon d) ALTER TABLE ChiTietHoaDon

ADD CONSTRAINT Mahd_fk Foregin key(Mahd) References ADD CONSTRAINT Mahd_fk Foregin key(Mahd) References HoaDon(Mahd) HoaDon(Mahd)

ầ

Tr n Thi Kim Chi

Bài 7Bài 7

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

ầ

Tr n Thi Kim Chi

Accessing and Modifying Data Accessing and Modifying Data

ệ

ấ ữ ệ – L nh Select 1. Truy xu t d li u ả ơ ấ Truy v n đ n gi n ề ấ ừ nhi u b ng Truy v n t ấ 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

ầ

Tr n Thi Kim Chi

– – – ệ ệ ệ

ệ CCúú ph phááp l nh Select ệp l nh Select

SELECT [ALL | DISTINCT] [TOP n [WITH TIES]] select_list SELECT [ALL | DISTINCT] [TOP n [WITH TIES]] select_list

[ INTO new_table ] [ INTO new_table ] FROM table_source FROM table_source [ WHERE search_condition ] [ WHERE search_condition ] [ GROUP BY group_by_expression ] [ GROUP BY group_by_expression ] [ HAVING search_condition ] [ HAVING search_condition ] [ ORDER BY order_expression [ ASC | DESC ] ] [ ORDER BY order_expression [ ASC | DESC ] ]

•

ắ ế ORDER BY : S p x p ắ ế ORDER BY : S p x p

ề ề

• WHERE: Đi u ki n ệ WHERE: Đi u ki n ệ

•

GROUP BY: Nhóómm GROUP BY: Nh

•

ề ề

ệ ệ

óómm

HAVING: Đi u ki n nh HAVING: Đi u ki n nh

ầ

Tr n Thi Kim Chi

ụ ệ ụ ệ

Ví d l nh SELECT Ví d l nh SELECT

Column names

Table names

Condition

ầ

Tr n Thi Kim Chi

Sort order

ấ ấ

ơ ơ

ả Truy v n đ n gi n ả Truy v n đ n gi n

ọ ấ ả

ộ ả

ộ

 Ch n t

t c các c t trong m t b ng

Syntax

SELECT * FROM < tablename>

Example

ầ

Tr n Thi Kim Chi

SELECT * FROM [Khach Hang]

ấ ấ

ơ ơ

ả Truy v n đ n gi n ả Truy v n đ n gi n

ộ ả

ọ

ộ

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

Syntax

SELECT , FROM

Example

ầ

Tr n Thi Kim Chi

SELECT Masp, Tensp FROM [San Pham]

ấ ấ

ơ ơ

ả Truy v n đ n gi n ả 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

ầ

Tr n Thi Kim Chi

SELECT HoNV+ ‘ ‘ + TenNv FROM [Nhan vien]

ấ ấ

ơ ơ

ả Truy v n đ n gi n ả Truy v n đ n gi n

ặ

ộ

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

Syntax

SELECT as <'alias'> FROM

Example

ầ

Tr n Thi Kim Chi

SELECT Honv +’ ‘ +Tennv AS ‘HOTEN' FROM [Nhan vien]

ấ ấ

ơ ơ

ả Truy v n đ n gi n ả Truy v n đ n gi n

ạ ộ

 T o c t tính toán

Example

ầ

Tr n Thi Kim Chi

SELECT Mahd, Soluong*Dongia AS ‘Tong Tien' FROM [Chi Tiet Hoa Don]

ấ ấ

ơ ơ

ả Truy v n đ n gi n ả Truy v n đ n gi n

ạ ỏ ữ

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

SELECT diadiem FROM DIADIEM_PHG

diadiem diadiem

TP HCM TP HCM

HA NOI HA NOI

TP HCM TP HCM

SELECT DISTINCT FROM

SELECT FROM

DISTINCT diadiem DIADIEM_PHG

Example

diadiem diadiem

TP HCM TP HCM

ầ

Tr n Thi Kim Chi

HA NOI HA NOI

SELECT DISTINCT Makh FROM [hoa don]

ấ ấ

ơ ơ

ả Truy v n đ n gi n ả 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

ủ ủ ả ỉ

ả ượ ầ ấ c xu t ế k t qu đ

Syntax

SELECT TOP n [PERCENT] FROM

Tr n Thi Kim Chi

Examples

SELECT TOP 3 masp, dongia FROM [Chi tiet hoa don] SELECT TOP 4 PERCENT masp FROM [san pham] ầ

ấ ấ

ơ ơ

ả Truy v n đ n gi n ả Truy v n đ n gi n

 Ví d : li t kê 3 hóa đ n có c Select top 3 with ties OrderID, Freight From Orders Order by Freight DESC

ầ

Tr n Thi Kim Chi

ơ ướ ụ ệ ấ c phí cao nh t

ệ ệ

ề M nh đ WHERE ề M nh đ WHERE

ệ ọ ữ ệ ầ ệ ọ ữ ệ ầ

ầ

Tr n Thi Kim Chi

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

ệ ệ

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

Operator

Meaning

== Equal To Equal To

>> Greater Than Greater Than

<< Less than Less than

>=>= Greater Than or Equal To Greater Than or Equal To

<=<= Less Than or Equal To Less Than or Equal To

ầ

Tr n Thi Kim Chi

!! NotNot

Phép toán Logical Phép toán Logical

 Phép toán và AND

Example

ầ

Tr n Thi Kim Chi

SELECT Mahd, NgayLapHD, Makh FROM [Hoa don] WHERE Month(NgayLapHD) = 3 AND Year(NgayLapHD)=1992

Phép toán Logical Phép toán Logical

 OR operator

Example

ầ

Tr n Thi Kim Chi

SELECT * FROM [Hoa don] WHERE Makh = ‘FISC’ OR Makh = ‘HUNSAN’

Logical Operators (contd.) Logical Operators (contd.)

 NOT operator

Example

ầ

Tr n Thi Kim Chi

SELECT * FROM [Hoa don] WHERE NOT Manv= 10

ử ử

Các toán t Các toán t

SQL SQL

ả

ữ

ị

ạ

ượ

ế

ệ

ấ

c dùng trong l nh truy v n con và k t

ả

ớ ươ ớ

ữ ệ

ề

• 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): đ ề qu là nhi u dòng. ị ấ ỏ ơ ANY: L n h n tr th p nh t ớ ươ ng v i IN ng đ =ANY: T ấ ơ ị >ALL: L n h n tr cao nh t ấ ỏ ơ ị ấ

i c a 1 d li u trong nhi u dòng. ầ Tr n Thi Kim Chi

Wildcard Characters Wildcard Characters

Wildcard

Description

Example

Represents a single character

SELECT Meal_Code FROM Meal WHERE Meal_Code LIKE ‘C_’

Represents a string of any length

[]

Represents a single character within the range enclosed in the brackets

SELECT Meal_Code FROM Meal WHERE Meal_Code LIKE ‘CO_%’ SELECT * FROM flight WHERE aircraft_code LIKE ‘9W0[12]’

[^]

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

SELECT * FROM flight WHERE aircraft_code LIKE ‘9W0[^12]’

ầ

Tr n Thi Kim Chi

Các Hàm Functions Các Hàm Functions

- AVG() - MIN() - MAX() - SUM() - COUNT() - SQUARE() - SQRT() - ROUND()

- ASCII() - CHAR() - UPPER() - LOWER() - LEN() - LTRIM() - RTRIM() - LEFT() - RIGHT()

- GETDATE() - DATEPART(YY,g etdate()) - DATEDIFF(X,Y,Z ) - DAY(),MONTH(), YEAR()

ầ

Tr n Thi Kim Chi

Maths String Time

Các Hàm Functions Các Hàm Functions

ISDATE(exp) ISDATE(exp)

Returns 1 if exp is a valid date Returns 1 if exp is a valid date

General General Functions Functions

ISNULL(exp1,exp2) ISNULL(exp1,exp2)

ISNUMERIC(exp) ISNUMERIC(exp)

Returns Null if exp1 is NULL, Returns Null if exp1 is NULL, otherwise exp1 returned otherwise exp1 returned Returns 1 if exp is a number type Returns 1 if exp is a number type

ASCII(char) ASCII(char)

String String Functions Functions

CHAR(int) CHAR(int)

Returns the ASCII value of a Returns the ASCII value of a Character. Character. Returns the character value for Returns the character value for

CHARINDEX(string1 CHARINDEX(string1 , string2, start) , string2, start)

an ASCII integer value. an ASCII integer value. Returns the starting position for Returns the starting position for string1 in string2 optionally starting at string1 in string2 optionally starting at position start. position start.

ầ

Tr n Thi Kim Chi

Function Function Description Description

Các Hàm Functions Các Hàm Functions

NCHAR(int) NCHAR(int)

Returns the UNICODE character Returns the UNICODE character

String String Functions Functions

represented by int. represented by int.

LEN(string) LEN(string)

Returns the length of the string. Returns the length of the string.

LOWER(string) LOWER(string)

Returns the string passed in with all Returns the string passed in with all

characters converted to lowercase. characters converted to lowercase.

UPPER(string) UPPER(string)

characters characters

converted converted

Returns the string passed in with Returns the string passed in with to all all to uppercase. uppercase.

ầ

Tr n Thi Kim Chi

Function Function Description Description

Các Hàm Functions Các Hàm Functions

REVERSE(string) REVERSE(string)

String String Functions Functions

Returns the reverse of a character Returns the reverse of a character expression. expression.

Returns the int number of RIGHT( string, int) Returns the int number of RIGHT( string, int) characters from the right side of characters from the right side of the string. the string.

LEFT(string, int) LEFT(string, int)

Returns the first int characters from Returns the first int characters from String. String.

ầ

Tr n Thi Kim Chi

Function Function Description Description

Các Hàm Functions Các Hàm Functions

String String Functions Functions

SUBSTRING(string, SUBSTRING(string, start, int) start, int)

Returns a portion of the string string Returns a portion of the string string starting at position start and starting at position start and continuing for int characters. continuing for int characters.

RTRIM(string) RTRIM(string)

Returns the string with all blank Returns the string with all blank spaces from the end of the string spaces from the end of the string Removed. Removed.

LTRIM(string) LTRIM(string)

Returns the string with all blank Returns the string with all blank spaces from the left side of the string spaces from the left side of the string removed. removed.

ầ

Tr n Thi Kim Chi

Function Function Description Description

Các Hàm Functions Các Hàm Functions

SPACE(int) SPACE(int)

Returns int number of spaces. Returns int number of spaces.

String String Functions Functions

Converts a numeric value to a string. Converts a numeric value to a string.

STR(float, length, STR(float, length, decimal) decimal)

STUFF(string, start, STUFF(string, start, length, char) length, char)

Removes length characters from string Removes length characters from string starting with character start and starting with character start and replaces them with char. replaces them with char.

ầ

Tr n Thi Kim Chi

Function Function Description Description

Các Hàm Functions Các Hàm Functions

String String Functions Functions

UNICODE(Unicod UNICODE(Unicod e string) e string)

Returns the numeric value of the Returns the numeric value of the first character of a UNICODE first character of a UNICODE Expression. Expression.

ầ

Tr n Thi Kim Chi

100

Function Function Description Description

Các Hàm Functions Các Hàm Functions

Function Function

Description Description

DATEPART(day DATEPART(day /month/..,day) /month/..,day)

Returns the specific part of the date Returns the specific part of the date as an integer. as an integer.

Date and Date and Time Time Functions Functions

DAY(date) DAY(date)

Returns the numeric day of the Returns the numeric day of the week for date. week for date.

ầ

Tr n Thi Kim Chi

101

Các Hàm Functions Các Hàm Functions

GETDATE() GETDATE()

GETDATE() Returns the current GETDATE() Returns the current time.. server date and time server date and

MONTH(datedate)) MONTH(

Returns the numeric month number Returns the numeric month number of of datedate..

YEAR (datedate)) YEAR (

Returns the numeric year number Returns the numeric year number of of datedate..

ầ

Tr n Thi Kim Chi

102

Function Function Description Description

So sánh Chu iỗ So sánh Chu iỗ

ị

• Tìm t

ự

ậ qu n 1.

• So sánh g n đúng s d ng “like” ử ụ ế thay th : ‘_’ và ‘%’ ỉ ở t c các mã nhân viên có đ a ch

ầ – Hai ký t ấ ả SELECT * FROM [Nhan vien] WHERE diachi LIKE '%Q1'

SELECT Honv, Tennv, NgaySinh FROM [Nhan vien] WHERE convert(char(8), NgaySinh) like '______6_'

ầ

Tr n Thi Kim Chi

103

ế ữ Cho bi t tên nhân viên sinh vào nh ng năm 1960

tuple variable

ộ ộ

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

NV1.TenNv, NV1.TenNv,

ố Bi n bế ộ • Cho bi t hai nhân viên có cùng thành ph

[Nhan vien] NV1, [Nhan vien] NV2 [Nhan vien] NV1, [Nhan vien] NV2 NV1.Thanhpho=NV2.Thanhpho NV1.Thanhpho=NV2.Thanhpho

ế SELECT SELECT NV2.TenNv,NV1.ThanhPho,NV2.Thanhpho NV2.TenNv,NV1.ThanhPho,NV2.Thanhpho FROM FROM WHERE WHERE

ầ

104

ể ử ụ ể ế ệ ậ • Có th s d ng bi n b b t k lúc nào đ thu n ti n và ộ ấ ỳ

ễ ọ d đ c! NV1.TenNv, SELECT NV1.TenNv, SELECT NV2.TenNv,NV1.ThanhPho,NV2.Thanhpho NV2.TenNv,NV1.ThanhPho,NV2.Thanhpho FROM FROM WHERE WHERE [Nhan vien] NV1, [Nhan vien] NV2 [Nhan vien] NV1, [Nhan vien] NV2 Tr n Thi Kim Chi NV1.Thanhpho=NV2.Thanhpho NV1.Thanhpho=NV2.Thanhpho

ả ả

ề ề

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

ồ

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

SELECT Mahd, [Hoa Don].Makh, Tenkh FROM WHERE

[Hoa don], [Khach hang] [Hoa don].makh= [khach hang].Makh

ắ ầ

• Danh sách các hoá đ n do nhan viên có tên b t d u là D l p ậ [Hoa don].Mahd, Honv +’ ‘+Tennv as Hoten [Nhan vien], [Hoa don] [Nhan vien].manv = [Hoa don].Manv And TenNV like

SELECT FROM WHERE ‘D%’

• Danh sách các hoá đ n do nhan viên có Thành ph là HCM l p ậ

ố O.Mahd, Honv +’ ‘+Tennv as HoTen [Nhan vien] E, [Hoa don] O E.Manv =O.Manv And Thanhpho =‘HCM’

SELECT FROM WHERE

ầ

Tr n Thi Kim Chi

105

ế ế

ắ ắ

S p x p ORDER BY Clause S p x p ORDER BY Clause

ả

ứ ự ủ ộ ế

c a b k t qu

ế

tăng ứ ự ả

ế

ị Xác đ nh th t Cú pháp [ ORDER BY { order_by_expression [ ASC | DESC ] } [ ,...n] ] ứ ự ASC (ascending) : x p theo th t DESC (descending): x p theo th t

gi m

ầ

Tr n Thi Kim Chi

106

ữ ệ ữ ệ

ả ả

ế Nhóm d li u trong b ng k t ế Nhóm d li u trong b ng k t quả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ộ

ề

ệ ể ượ

ệ ằ

ể ổ ượ

ầ

Tr n Thi Kim Chi

107

– COMPUTE và COMPUTE BY: m nh đ COMPUTE ệ c dùng đ phát ra các hàng trong l nh SELECT đ ề ộ ợ ổ t ng h p b ng cách dùng hàm g p. M nh đ ợ c dùng đ t ng h p thêm các hàng COMPUTE BY đ ộ ả ế k t qu theo c t

ề ề

ệ ệ

M nh đ GROUP BY 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 ộ

ầ

Tr n Thi Kim Chi

108

ượ đ c nhóm

ậ ậ

ợ Các Hàm t p h p ợ Các Hàm t p h p

Tên Hàm Tên Hàm Ý nghĩa Ý nghĩa

ố ố

SUMSUM

ổ ổ Tính t ng các s Tính t ng các s

ố ầ ử ố ầ ử

ế ế

COUNT COUNT

Đ m s ph n t Đ m s ph n t

AVGAVG

ị ị Tính giá tr trung bình Tính giá tr trung bình

ả ề ả ề

MINMIN

ị ỏ ấ ị ỏ ấ Tr v giá tr nh nh t Tr v giá tr nh nh t

ả ề ả ề

ị ớ ị ớ

MAXMAX

ấ ấ Tr v giá tr l n nh t Tr v giá tr l n nh t

ầ

Tr n Thi Kim Chi

109

ề ề

ệ ệ

M nh đ GROUP BY M nh đ GROUP BY

• Ví d :ụ SELECT Mahd, SUM(Soluong* Dongia) AS ‘Thanh tien‘ FROM [Chi tiet hoa don] GROUP BY mahd

ầ

Tr n Thi Kim Chi

110

SELECT Mahd, AVG(Soluong * DonGia) AS ‘Trung Binh‘ FROM [Chi Tiet hoa don] GROUP BY Mahd

ề ề

ệ ệ

M nh đ GROUP BY M nh đ GROUP BY

• Ví d :ụ

SELECT Mahd, MIN(Soluong * Dongia) AS ‘Thanh tien nho nhat‘ FROM [Chi Tiet hoa don] GROUP BY Mahd

ầ

Tr n Thi Kim Chi

111

SELECT Mahd, MAX(Soluong * Dongia) AS ‘Thanh Tien Lon Nhat‘ FROM [Chi Tiet Hoa Don] GROUP BY Mahd

ề ề

ệ ệ

M nh đ GROUP BY M nh đ GROUP BY

• Ví d :ụ SELECT Count(Mahd) AS ‘So Hoa Don‘ FROM [Hoa don]

SELECT Count(*) AS ‘So Hoa Don‘ FROM [Hoa Don]

ầ

Tr n Thi Kim Chi

112

SELECT Makh, Count(Makh) –count(mahd) AS ‘So HD cua tung khach hang‘ FROM [Hoa don] GROUP BY Makh

ệ ệ

ề ề

M nh đ GROUP BY M nh đ GROUP BY

• Ví d :ụ SELECT Masp, Sum(Soluong) As Total FROM [Chi Tiet Hoa Don] WHERE Masp=2 GROUP BY Masp

ầ

Tr n Thi Kim Chi

113

SELECT Makh, Count(Mahd) AS ‘So HD cua khach hang‘ FROM [Hoa don] WHERE Makh like ‘%o’ GROUP BY Makh

GROUP BY và HAVING GROUP BY và HAVING

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

ể ạ ề ệ

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

ỉ ệ ệ ớ ượ m nh đ HAVING. ữ ệ ề 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 Masp, AVG(Dongia) FROM [San pham]

ầ

Tr n Thi Kim Chi

114

GROUP BY Masp HAVING (AVG(Dongia) > 10)

ử ụ ử ụ

S d ng WHERE và HAVING 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à ứ

ề

ệ ướ ượ

ượ ệ ủ ớ ượ ề ụ c áp d ng tr c cho các hàng ả ữ ỉ ấ c truy v n. Ch nh ng hàng nào tho c nhóm

ệ m nh đ HAVING. – M nh đ WHERE đ ề ả trong b ng đ ệ ề mãn đi u ki n c a m nh đ WHERE m i đ ữ ệ 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 115 m i đ ệ ỉ Tr n Thi Kim Chi ả ả ế c xu t ra b ng k t qu .

ử ụ ử ụ

S d ng WHERE và HAVING S d ng WHERE và HAVING

Ví d 1ụ

SELECT Masp, Sum(Soluong) As Total FROM [Chi tiet hoa don] GROUP BY masp HAVING Sum(soluong)>=30

Tr n Thi Kim Chi

116

SELECT Makh, Count(Mahd) AS ‘So hoa don cua kH‘ FROM [Hoa don] GROUP BY Makh HAVING Count(Mahd)<=5 ầ

ề ề

ệ ệ

M nh đ COMPUTE M nh đ COMPUTE

ườ

ể ể

ố ệ

ố

ớ

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

Ví dụ

SELECT Masp, Mahd, Soluong FROM [Chi Tiet Hoa Don] ORDER BY Masp, Mahd

COMPUTE Sum(Soluong)

SELECT Masp, Mahd, Soluong As Total FROM [Chi Tiet Hoa Don] ORDER BY Masp, Mahd COMPUTE SUM(Soluong) By Masp COMPUTE SUM(Soluong)

ầ

Tr n Thi Kim Chi

117

ệ ệ

ề ề

M nh đ COMPUTE M nh đ COMPUTE

ế

• COMPUTE…BY…: Có k t nhóm 1) SELECT c.Makh, o.Mahd, (od.Soluong * od.Dongia) as 'total‘

FROM [Hoa don] o, [Chi Tiet hoa don] od,[Khach hang] c

WHERE c.Makh = o.Makh AND o.Mahd = od.Mahd AND c.Makh LIKE ‘T%‘ ORDER BY c.Makh COMPUTE SUM( od.Soluong * od.Dongia)

2) SELECT c.Makh, o.Mahd, (od.Soluong * od.Dongia) as ‘Tong‘

FROM [Hoa don] o, [Chi Tiet Hoa Don] od, [Khach hang] c

WHERE c.Makh = o.Makh AND o.Mahd = od.Mahd AND c.Makh LIKE ‘T%‘ ORDER BY c.Makh COMPUTE SUM(od.soluong * od.dongia) BY c.Makh

ầ

Tr n Thi Kim Chi

118

ệ ệ

ề ề

M nh đ COMPUTE M nh đ COMPUTE

ầ ầ

ề ề

ệ ệ

ử ụ ử ụ

i đây: i đây:

ộ ộ

ớ ớ

ử ụ ử ụ

c s d ng trong COMPUTE. c s d ng trong COMPUTE.

ả ả

ộ ộ

ở ấ ở ấ

ể ử ụ ể ử ụ ẩ ẩ

ề ề

ệ ệ

ấ ấ

ả ả

ọ ọ

ử ụ ử ụ

ệ ệ

ộ ộ

ệ ệ

ượ ượ ắ ầ ắ ầ

ệ ệ ệ ệ ả ả ỏ ỏ

ệ c li ệ c li ớ ớ

ứ ứ

ể ể

ứ ứ

• Khi s d ng m nh đ COMPUTE ... BY c n tuân theo các qui Khi s d ng m nh đ COMPUTE ... BY c n tuân theo các qui ắ ướ t c d ắ ướ t c d – T khóa DISTINCT không cho phép s d ng v i các hàm g p dòng ừ T khóa DISTINCT không cho phép s d ng v i các hàm g p dòng ừ – Hàm COUNT(*) không đ ượ ử ụ Hàm COUNT(*) không đ ượ ử ụ – Sau COMPUTE có th s d ng nhi u hàm g p, khi đó các hàm ph i phân ề Sau COMPUTE có th s d ng nhi u hàm g p, khi đó các hàm ph i phân ề cách nhau b i d u ph y. cách nhau b i d u ph y. – Các c t s d ng trong các hàm g p xu t hi n trong m nh đ COMPUTE ộ ộ ử ụ Các c t s d ng trong các hàm g p xu t hi n trong m nh đ COMPUTE ộ ộ ử ụ ặ ph i có m t trong danh sách ch n. ặ ph i có m t trong danh sách ch n. – Không s d ng SELECT INTO trong m t câu l nh SELECT có s d ng ử ụ Không s d ng SELECT INTO trong m t câu l nh SELECT có s d ng ử ụ COMPUTE. COMPUTE. – N u s d ng m nh đ COMPUTE ... BY thì cũng ph i s d ng m nh đ ề ề ế ử ụ ả ử ụ N u s d ng m nh đ COMPUTE ... BY thì cũng ph i s d ng m nh đ ề ế ử ụ ề ả ử ụ ộ ệ ố ả t kê trong COMPUTE … BY ph i gi ng h t hay là ORDER BY. Các c t li ộ ệ ố ả t kê trong COMPUTE … BY ph i gi ng h t hay là ORDER BY. Các c t li ộ ậ ữ ủ m t t p con c a nh ng gì đ t kê sau ORDER BY. Chúng ph i có cùng ữ ộ ậ ủ m t t p con c a nh ng gì đ t kê sau ORDER BY. Chúng ph i có cùng ả ứ ự ừ ấ ộ th t t trái qua ph i, b t đ u v i cùng m t bi u th c và không b qua b t ả ứ ự ừ ấ ộ trái qua ph i, b t đ u v i cùng m t bi u th c và không b qua b t th t t ể ỳ ộ k m t bi u th c nào. ể ỳ ộ k m t bi u th c nào.

ầ

Tr n Thi Kim Chi

119

ế ố ế ố

ề ề

ệ ệ

ả ả

M nh đ JOINK t n i nhi u b ng M nh đ JOINK t n i nhi u b ng

ể ế ố ữ ệ ừ ề ả ơ nhi u h n 1 b ng

• M nh đ join dùng đ k t n i d li u t ệ ề • 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]

ầ

Tr n Thi Kim Chi

120

[WHERE search_condition]

ế ố ế ố

ả ả

K t n i các b ng K t n i các b ng

ỉ ồ ạ ấ . i trong th i gian truy v n

ổ ữ ệ ả • K t n i ch t n t ờ • 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

SELECT

t.Mahd, NgaylapHD, Masp, Soluong, dongia,

Thanhtien as Soluong*Dongia

ầ

from [Chi tiet hoa don] t JOIN [Hoa don] h on t.mahd=h.mahd Tr n Thi Kim Chi WHERE Makh=‘SJC’

121

• Ví d ụ

ộ ộ

ế ố Các c t tham gia k t n i ế ố Các c t tham gia k t n i

ế ế ố ế ố ề ả ơ ướ c, sau

• N u k t n i nhi u h n 2 b ng thì k t n i 2 b ng tr ả ớ ả ế ố ứ đó k t n i nhóm này v i b ng th ba.

SELECT o.Mahd,c.makh, p.Masp, Tensp, Soluong, o.dongia, Thanhtien =soluong*o.dongia FROM [Chi tiet Hoa don] o JOIN SanPham p ON o.Masp = p.Masp JOIN [Hoa don] c ON o.Mahd = c.Mahd

ầ

Tr n Thi Kim Chi

122

• Ví dụ

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

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

ầ

Tr n Thi Kim Chi

123

ế ố ộ ế ố ộ

K t n i n i Inner joins 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 :ụ

Thanhtien as Soluong*Dongia from [Chi tiet hoa don] t

ầ

JOIN [Hoa don] h on t.mahd=h.mahd Tr n Thi Kim Chi

124

SELECT t.Mahd, h.NgaylapHD, masp, Soluong, dongia,

ế ố ộ ế ố ộ

K t n i n i Inner joins K t n i n i Inner joins

ầ

SELECT K.Makh, TenKH, Mahd, NgaylapHD FROM [Khach hang] K INNER JOIN [Hoa don] h ON K.Makh = H.Makh

Tr n Thi Kim Chi

125 125

SELECT Tensp, c.Mahd, Soluong, dongia, Soluong * DonGia AS [Thanhtien] FROM [San pham] AS s INNER JOIN [Chi tiet hoa don] AS c ON s.Masp = c.Masp INNER JOIN [Hoa don] As h ON h.mahd =c.mahd WHERE Month(NgayLapHD) = 3

ế ố ộ ớ ế ố ộ ớ

ử ớ ử ớ

K t n i n i v i toán t K t n i n i v i toán t

ơ l n h n ơ 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.

Tensp, c.Mahd, Soluong, c.dongia, SELECT Tensp, c.Mahd, Soluong, c.dongia,

• Ví dụ: • SELECT • •

ầ

Tr n Thi Kim Chi

126

[Thanhtien] Soluong * c.DonGia ASAS [Thanhtien] Soluong * c.DonGia [Chi tiet hoa INNER JOIN [Chi tiet hoa FROMFROM [San pham] [San pham] ASAS s s INNER JOIN s.Masp > c.Masp don] ASAS c c ONON s.Masp > c.Masp don]

ế ố ộ ế ố ộ

K t n i n i – Dùng where K t n i n i – Dùng where

ầ

Tr n Thi Kim Chi

127

ế ố ế ố

ạ K t n i ngo i Outer joins ạ 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

ạ ượ ả ộ ợ ẽ ượ ạ ị

ị

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

ầ

Tr n Thi Kim Chi

128

ả ứ ấ ả ể t c ủ ả ủ các hàng c a 1 b ng và các hàng trùng nhau c a b ng ữ c i. Nh ng c t mà không có giá tr phù h p s đ còn l ể ị hi n th giá tr NULL. • Cú pháp

ế ố ế ố

ạ K t n i ngo i Outer joins ạ K t n i ngo i Outer joins

ố ố

ố ố ế ế ể ể ữ ệ ủ ả ữ ệ ủ ả

ữ ữ ố ố

ố ố

ể ể ữ ệ ủ ả ữ ệ ủ ả ố ố ế ế

ữ ữ

ầ

Tr n Thi Kim Chi

129

ố ố ố ố ạ ấ SQL cung c p các lo i phép n i ngoài sau đây: ạ ấ SQL cung c p các lo i phép n i ngoài sau đây: •Phép n i ngoài trái ệ ị (ký hi u: *=): Phép n i này hi n th ệ ị (ký hi u: *=): Phép n i này hi n th Phép n i ngoài trái ấ ấ ả ả t c các dòng d li u c a b ng trong k t qu truy v n t ấ ấ ả ả trong k t qu truy v n t t c các dòng d li u c a b ng ệ ề ằ n m bên trái trong đi u ki n n i cho dù nh ng dòng này ề ệ ằ n m bên trái trong đi u ki n n i cho dù nh ng dòng này ố ệ ủ ề ả không tho mãn đi u ki n c a phép n i ố ả ệ ủ ề không tho mãn đi u ki n c a phép n i •Phép n i ngoài ph i ị ả (ký hi u: =*): Phép n i này hi n th ệ (ký hi u: =*): Phép n i này hi n th ệ ị ả Phép n i ngoài ph i ấ ấ ả ả t c các dòng d li u c a b ng trong k t qu truy v n t ấ ấ ả ả t c các dòng d li u c a b ng trong k t qu truy v n t ệ ề ả ằ n m bên ph i trong đi u ki n n i cho dù nh ng dòng này ệ ề ả ằ n m bên ph i trong đi u ki n n i cho dù nh ng dòng này ệ ủ ả ề không tho đi u ki n c a phép n i. ệ ủ ả ề không tho đi u ki n c a phép n i.

ế ố ế ố

ạ ạ

K t n i ngo i – Left Outer joins K t n i ngo i – Left Outer joins

ầ

Tr n Thi Kim Chi

130

ế ố ế ốt n i trái KK t n i trái

LEFT OUTER JOIN 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 Manv, Hoten, cv, d.macv,tench SELECT Manv, Hoten, cv, d.macv,tench FROM Nhanvien n INNER JOIN ChucVu d ON FROM Nhanvien n INNER JOIN ChucVu d ON

n.cv=d.macv n.cv=d.macv

SELECT Manv, Hoten, cv, d.macv,tench SELECT Manv, Hoten, cv, d.macv,tench FROM Nhanvien n LEFT OUTER JOIN ChucVu d ON FROM Nhanvien n LEFT OUTER JOIN ChucVu d ON

ầ

Tr n Thi Kim Chi

131

n.cv=d.macv n.cv=d.macv

ế ố ế ố

ạ ạ

K t n i ngo i – Right Outer K t n i ngo i – Right Outer joins joins

ầ

Tr n Thi Kim Chi

132

ế ố ế ốt n i trái KK t n i trái

LEFT OUTER JOIN 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 Manv, Hoten, cv, d.macv,tench SELECT Manv, Hoten, cv, d.macv,tench FROM Nhanvien n RIGHT OUTER JOIN ChucVu d ON FROM Nhanvien n RIGHT OUTER JOIN ChucVu d ON

n.cv=d.macv n.cv=d.macv

ầ

Tr n Thi Kim Chi

133

SELECT Manv, Hoten, cv, d.macv,tench SELECT Manv, Hoten, cv, d.macv,tench FROM Nhanvien n FULL JOIN ChucVu d ON n.cv=d.macv FROM Nhanvien n FULL JOIN ChucVu d ON n.cv=d.macv

ế ố ế ốt n i trái KK t n i trái

FULL OUTER JOIN FULL OUTER JOIN

ầ

Tr n Thi Kim Chi

134

Cross join Cross join

ủ ấ ả ủ ấ ả t c các t c các v m i t v m i t ể h p có th có c a t ể h p có th có c a t

• Cross join kh ế ế ẽ ạ ẽ ạ ệ ệ

ẽ ự ẽ ự ệ ệ

ầ

Tr n Thi Kim Chi

135

ế ế ư ư • Cross join tr ả ề ọ ổ ợ Cross join tr ả ề ọ ổ ợ ế ố . . ả hàng trong các b ng k t n i ế ố ả hàng trong các b ng k t n i ề ệ ông có m nh đ ON Cross join không có m nh đ ON ề ệ – N u không m nh đ WHERE, cross join s t o ra ề N u không m nh đ WHERE, cross join s t o ra ề tích Cartesian tích Cartesian – N u có m nh đ WHERE, cross join s th c hi n ề ệ N u có m nh đ WHERE, cross join s th c hi n ề ệ ế ố ộ nh 1 k t n i n i ế ố ộ nh 1 k t n i n i

ế ố ế ố

K t n i chéo Cross join K t n i chéo Cross join

SELECT [San pham].Masp, tensp, soluong, c.dongia FROM [San pham] CROSS JOIN [Chi tiet hoa don]

• Ví d 1:ụ

SELECT [San pham].Masp, tensp, soluong, dongia FROM [San pham] CROSS JOIN [Chi tiet hoa don] as c WHERE [San pham].masp = c.Masp

ầ

Tr n Thi Kim Chi

136

• Ví d 2:ụ

́ ́

ự ự

Truy vâ n t Truy vâ n t

kê t nô i (selfjoin) kê t nô i (selfjoin)

̣ ̉ ́ ́ ̀ ̀ ́ ́ ơ ơ ấ ấ ̣ ̉

(

CrsCode CrsCode ><

TRANSCRIPT

[

StudID CrsCode Semester Grade 2,

2]))

ầ

Tr n Thi Kim Chi

137

(cid:0) • Vi du: ti m tâ t ca các khách hàng mua ít nh t 2 đ n hàng Vi du: ti m tâ t ca các khách hàng mua ít nh t 2 đ n hàng SELECT t1.Makh, t1.MAHD,t2.MAHD SELECT t1.Makh, t1.MAHD,t2.MAHD FROM [Hoa don] t1 JOIN [Hoa don] t2 FROM [Hoa don] t1 JOIN [Hoa don] t2 ON t1.Mahd<> t2.Mahd AND ON t1.Mahd<> t2.Mahd AND t1.Makh = t2.Makh t1.Makh = t2.Makh s StudID

́ ́

Truy vâ n select merge Truy vâ n select merge

ầ

Tr n Thi Kim Chi

138

A new feature in SQL2008 is merge statement. You can merge 2 or more tables

́ ́

ị ị

ớ Truy vâ n v i giá tr Null ớ Truy vâ n v i giá tr Null

ị ố

ế ộ ủ ệ ủ ề ả

ượ ố ị ị

ư ằ

ầ

Tr n Thi Kim Chi

139

ư ả s ta có hai b ng TABLE1 và TABLE2 nh sau: Phép n i và các giá tr NULL N u trong các c t c a các b ng tham gia vào đi u ki n c a phép n i có các giá tr NULL thì các giá tr NULL đ c xem nh là không b ng nhau. ả ử Ví d : ụ Gi

ấ ấ

Truy v n con Subqueries Truy v n con Subqueries

ệ

ế ặ ồ ượ

• Subquery là l nh SELECT mà k t qu tr v là 1 giá tr ị ả ả ề ơ c đ t l ng vào bên trong các đ n (single value) và đ ệ 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

ầ

Tr n Thi Kim Chi

140

ứ ượ ấ ể ượ c phép dùng th c đ

ấ ấ

Truy v n con Subqueries Truy v n con Subqueries

A subquery is a SELECT statement inside another SELECT statement.

Cú pháp

Parent Query SELECT FROM Table

Operator

WHERE =

ầ

Tr n Thi Kim Chi

Subquery

141

(SELECT FROM

WHERE = )

ấ ấ

ặ ặ

ắ ắ

c vi c vi

ộ ộ

ả ả

ườ ườ

ả ả

ệ ệ

ng h p, m t truy v n con th ng h p, m t truy v n con th ấ ỉ ỉ ấ ề ề

ượ ượ

ọ ọ ử ụ c phép s d ng trong ử ụ c phép s d ng trong

ộ ủ ộ ủ

ể ể

ả ả

ấ ấ

ượ ử ụ ượ ử ụ

ườ ườ

ệ ệ

ộ ộ

ề c s d ng làm đi u ki n trong m nh đ ề c s d ng làm đi u ki n trong m nh đ ộ ộ

ng đ ng đ ủ ủ

ấ ấ ấ ấ ặ ặ ấ ấ

ư ư

ế ế

ể ử ụ ể ử ụ ấ ấ

ả ề ả ề ộ ộ

ấ ấ ị ị ẳ ẳ

ể ể

ệ ệ

ạ ạ

ằ ằ

Truy v n con Subqueries Truy v n con Subqueries ầ ư ấ ử ụ Khi s d ng truy v n con c n l u ý m t s quy t c sau: ầ ư ấ ử ụ Khi s d ng truy v n con c n l u ý m t s quy t c sau: •M t truy v n con ph i đ ầ ế ặ ế ả ượ ấ ộ t trong c p d u ngo c. Trong h u h t M t truy v n con ph i đ ầ ế ế ả ượ ộ ặ ấ t trong c p d u ngo c. Trong h u h t ộ ộ ế ấ ợ ng ph i có k t qu là m t c t các tr ộ ộ ế ấ ợ ng ph i có k t qu là m t c t các tr ộ ộ ứ (t c là ch có duy nh t m t c t trong danh sách ch n). ứ ộ ộ (t c là ch có duy nh t m t c t trong danh sách ch n). •M nh đ COMPUTE và ORDER BY không đ M nh đ COMPUTE và ORDER BY không đ ấ truy v n con. ấ truy v n con. •Các tên c t xu t hi n trong truy v n con có th là các c t c a các b ng ấ ệ ộ Các tên c t xu t hi n trong truy v n con có th là các c t c a các b ng ấ ệ ộ trong truy v n ngoài. trong truy v n ngoài. •M t truy v n con th ề M t truy v n con th ề WHERE ho c HAVING c a m t truy v n khác. WHERE ho c HAVING c a m t truy v n khác. •N u truy v n con tr v đúng m t giá tr , nó có th s d ng nh là m t ộ ộ N u truy v n con tr v đúng m t giá tr , nó có th s d ng nh là m t ộ ộ ộ ứ ầ thành ph n bên trong m t bi u th c (ch ng h n xu t hi n trong m t ộ ầ ứ thành ph n bên trong m t bi u th c (ch ng h n xu t hi n trong m t phép so sánh b ng) phép so sánh b ng)

ầ

Tr n Thi Kim Chi

142

ộ ố ộ ố ấ ấ

ấ ấ

Truy v n con Subqueries Truy v n con Subqueries

ể ấ • TH1: Dùng đ l y các field

SELECT h.Mahd, h.NgayLapHD, (SELECT

MAX(C.Dongia) FROM [Chi tiet hoa don] AS c

WHERE h.Mahd =c.Mahd) AS MaxUnitPrice

FROM [Hoa don] AS h

c dùng đ khôi ph c d li u t

ể ượ ể ượ ư ể c dùng nh 1 ụ ữ ệ ừ ề nhi u ủ cách khác c a join

ầ

Tr n Thi Kim Chi

143 143

• Subquery có th đ ả b ng và có th đ (alternative to a join)

ấ ấ

Truy v n con Subqueries Truy v n con Subqueries

ấ ấ ả ả

ơ t nh ng s n ph m nào có dongia b ng đ n

SELECT Tensp FROM [San pham]

WHERE Dongia > (SELECT Dongia FROM [San pham] WHERE TenSp like ‘N%') • Cách 2: dùng join

SELECT P.Tensp FROM [San pham] AS p JOIN [San pham] AS P2 ON (P.Dongia >P2.Dongia) WHERE P2.TenSp = ‘Nem'

ầ

Tr n Thi Kim Chi

144

• TH2: ế C1: Cho bi ủ ả ố ớ ế ố ớ ế TH2: Phép so sánh đ i v i k t qu truy v n con Phép so sánh đ i v i k t qu truy v n con ằ ẩ ả ắ ầ ữ ẩ ữ ằ giá c a s n ph m có tên b t đ u b ng ch N

ấ ấ

Truy v n con Subqueries Truy v n con Subqueries

ả ả

ấ ấ ắ ầ ế • TH2: C2: Cho bi ữ t nh ng s n ph m có thên b t đ u b ng ch

ố ớ ế ố ớ ế TH2: Phép so sánh đ i v i k t qu truy v n con Phép so sánh đ i v i k t qu truy v n con ằ ẩ ả ơ ủ ả ữ ằ ẩ N và dongia b ng đ n giá c a s n ph m khác

WHERE TenSp like ‘N%’ And Dongia > (SELECT Dongia FROM [San pham])

SELECT Tensp FROM [San pham]

SELECT P.Tensp FROM [San pham] AS p JOIN [San pham] AS P2 ON (P.Dongia >P2.Dongia) WHERE P2.TenSp = ‘Nem'

ầ

Tr n Thi Kim Chi

145

• Cách 2: dùng join

ấ ấ

Truy v n con Subqueries Truy v n con Subqueries

ể ượ ể ượ ạ ạ • Subquery có th đ Subquery có th đ c dùng theo 1 trong các d ng sau: c dùng theo 1 trong các d ng sau:

WHERE expression – WHERE expression [NOT] IN subquery)) [NOT] IN ((subquery

expression comparison_operator [ANY | ALL] [ANY | ALL]

– WHERE WHERE expression comparison_operator subquery)) ((subquery

ầ

Tr n Thi Kim Chi

146

– WHERE [NOT] EXISTS WHERE [NOT] EXISTS ((subquery subquery))

ớ ớ

ử ử

Subqueries v i toán t Subqueries v i toán t

IN IN

ế ế

ượ ượ ng là 1 danh sách (list) ch a t ng là 1 danh sách (list) ch a t ớ ớ ớ ớ c dùng v i IN (hay v i c dùng v i IN (hay v i ế ứ ừ ế ứ ừ 0 đ n 0 đ n

ả ủ ả ủ TH3: K t qu c a subquery đ TH3: K t qu c a subquery đ ườ ườ NOT IN) th NOT IN) th ị ề ề ị nhi u giá tr . nhi u giá tr . •Ví d :ụVí d :ụ

ầ

Tr n Thi Kim Chi

147

ệ ệ ề ề ớ ớ SELECT Mahd, NgayLapHD FROM [Hoa don] SELECT Mahd, NgayLapHD FROM [Hoa don] WHERE Makh IN (SELECT Makh FROM [Khach hang] WHERE Makh IN (SELECT Makh FROM [Khach hang] WHERE Diachi like ‘%Q1’) WHERE Diachi like ‘%Q1’) •Dùng cách 2 v i m nh đ join??? Dùng cách 2 v i m nh đ join???

ử ử

ớ Subquery v i các toán t ớ Subquery v i các toán t

so sánh so sánh

ử ả ơ so sánh đ n gi n (unmodified comparison

• Các toán t operator)

=, < >, >, > =, <, ! >, ! <, or < = ớ ắ ầ ả ơ so sánh đ n gi n và

• TH4: Subquery b t đ u v i toán t ử ị ơ ả ề giá tr đ n (single value)

ầ

Tr n Thi Kim Chi

148

tr v 1 SELECT Masp FROM [San pham] WHERE dongia > (SELECT MIN(Dongia) FROM [Chi tiet hoa don])

Subquery trả về một giá trị 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 đ

ộ

vì nó ch tr v

ỉ ả ề m t giá tr

ọ là scalar query b i ở c g i ị.

Ví dụ

SELECT Tensp, Dongia FROM [San pham]

ầ

Tr n Thi Kim Chi

149

WHERE DVT =‘kg’ and Dongia < (SELECT AVG(Dongia) FROM [San pham])

ề ề

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

ử ụ

ử

In, Not in, Any, all…

 S d ng toán t Ví dụ

1) SELECT Mahd FROM [Chi tiet hoa don]

WHERE Masp In (SELECT Masp FROM [San Pham] WHERE DVT =‘Kg’)

ầ

Tr n Thi Kim Chi

150

2) SELECT Mahd, Masp, Soluong FROM [Chi tiet hoa don] WHERE Soluong >=All (SELECT Soluong FROM [Chi tiet hoa don])

ề ề

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

ế

ả

• Cho bi

ấ t các s n ph m có đ n giá cao nh t

SELECT FROM WHERE

ẩ * [San Pham] Dongia >= ALL (SELECT Dongia FROM [San pham])

ế

ữ

ẩ

ả

ị

• Cho bi

ấ t các s n ph m có đ n v tính có ch a và có đ n giá cao nh t

SELECT * FROM WHERE

[San pham] DVT like ‘%a%’ and Dongia >= ALL (SELECT Dongia FROM [san pham])

ầ

Tr n Thi Kim Chi

151

ử ử

ớ Subquery v i các toán t ớ Subquery v i các toán t

so sánh so sánh

ả ề ộ

• Subquery tr v m t giá tr ị

SELECT Tensp, Dongia FROM [San pham]

WHERE Dongia< ( SELECT AVG(Dongia) FROM [San

pham] WHERE Masp<5)

ả ề ộ

• Subquery tr v m t dãy giá tr ị

ầ

Tr n Thi Kim Chi

152

Select Mahd, s.MASP,tensp from [SAN PHAM]s join [ Chi Chi Select Mahd, s.MASP,tensp from [SAN PHAM]s join [ tiet hoa don] c on s.MASP=c.MASP tiet hoa don] c on s.MASP=c.MASP Where s.Masp IN (select Masp Where s.Masp IN (select Masp From [Chi Tiet Hoa don] From [Chi Tiet Hoa don] Where Masp<5) Where Masp<5)

ử ử

ớ Subquery v i các toán t ớ Subquery v i các toán t

so sánh so sánh

ứ ứ ơ ơ ừ ừ ả ả so sánh đ n gi n có kèm theo các t so sánh đ n gi n có kèm theo các t

• Toán t ử so sánh ph c (Modified Comparison operators) Toán t ử so sánh ph c (Modified Comparison operators) ử All, any là toán t ử là toán t All, any hay some hay some

s/sánh+ [All,Any,some]) s/sánh+ [All,Any,some])

ứ ẽ ả ề ứ ẽ ả ề ử ử ớ ớ

ủ ủ ể ể ị ị

ầ

Tr n Thi Kim Chi

153

ả ệ ả ệ ề ề (=toán t (=toán t • Subquery b t đ u v i toán t ử ắ ầ so sánh ph c s tr v 1 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 ề 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. ồ g m c m nh đ GROUP BY hay HAVING.

ử ử

ớ Subquery v i các toán t ớ Subquery v i các toán t

so sánh 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

• Ví d :ụ

ầ

Tr n Thi Kim Chi

154

SELECT Manv, Honv,Tennv FROM [Nhan vien] WHERE Thanhpho<> ALL (SELECT thanhpho FROM [Khach hang])

ử ử

ớ Subquery v i các toán t ớ Subquery v i các toán t

so sánh so sánh

Select Masp, tensp, dongia from [San pham] Where dongia>ALL (Select dongia from [San pham]

where tensp like ‘B%’)

Select Masp, tensp, dongia from [San pham] Where dongia>ANY (Select dongia from [San pham]

where tensp like ‘B%’)

Select Masp, tensp, dongia from [San pham] Where dongia=ANY (Select dongia from [San pham]

ầ

Tr n Thi Kim Chi

155

where tensp like ‘B%’))

Using EXISTS and NOT EXISTS Using EXISTS and NOT EXISTS

ả ề

ế

ả

ấ

ộ

 Đ ki m tra xem m t truy v n con có tr v dòng k t qu nào hay

ả ề

ứ

ươ EXISTS (t ế

ả ủ

ị ấ

ế

ấ

ộ

ừ ng t ng ng NOT EXISTS) tr v giá tr True ứ ng ng False) n u k t qu c a truy v n con có ít nh t m t ươ ứ

ng ng không có dòng nào).

ể ể không. ượ  L ươ (t dòng (t ề

ệ ủ

ớ

 Đi u khác bi

ở t c a vi c s d ng EXISTS v i hai cách đã nêu ơ

ệ ử ụ ủ ọ

ề

ể

ấ

trên là trong danh sách ch n c a truy v n con có th có nhi u h n hai c t. ộ Example

SELECT Mahd, Makh FROM [Hoa don]

ầ

Tr n Thi Kim Chi

156

WHERE EXISTS (SELECT Makh FROM [Khach hang])

Using EXISTS and NOT EXISTS Using EXISTS and NOT EXISTS

ớ ử • Subquery dùng v i toán t EXISTS

Select * from [Khach Hang] as k

where NOT EXISTS ( SELECT * from [Hoa don] as h where k.Makh= h.makh )

Select * from [Khach Hang] as k

where EXISTS ( SELECT * from [Hoa don] as h

where k.Makh= h.makh )

ầ

Tr n Thi Kim Chi

157

where k.Makh= h.makh )

Using EXISTS and NOT EXISTS Using EXISTS and NOT EXISTS

ế ữ ư ả ẩ ượ • Cho bi t nh ng s n ph m nào ch a bán đ c dùng 3 cách

left outer join, not in và not exists

1)Select * from [San Pham] s where NOT EXISTS ( SELECT * from [Chi Tiet Hoa don] c

where s.masp=c.masp)

2)Select * from [San Pham] where Masp NOT IN ( SELECT masp from [Chi Tiet Hoa

don])

3) Select * from [San Pham] s Left outer join [Chi Tiet Hoa

Tr n Thi Kim Chi

158

don] c ON s.Masp=c.Masp WHERE c.Masp is null ầ

Using EXISTS and NOT EXISTS Using EXISTS and NOT EXISTS

ẩ ả ố ượ ượ ng bán đ c

ơ ố ượ • Cho bi ữ ế ớ l n h n s l ổ t nh ng s n ph m nào có t ng s l ng trung bình bán ra

1)Select masp, tensp, TongSL=sum(Soluong) from [San Pham] s, [Chi Tiet Hoa Don] c where s.masp=c.masp Group by c.masp, tensp

ầ

Tr n Thi Kim Chi

159

having sum(soluong)>(Select AVG(soluong) form [Chi tiet hoa don])

Nested Subqueries Nested Subqueries

Nested subqueries are subqueries within other subqueries

Example

SELECT Makh, Tenkh FROM [Khach hang] WHERE Makh in (SELECT Makh from [Hoa don] WHERE Mahd in

ầ

Tr n Thi Kim Chi

160

(SELECT Mahd FROM [Chi tiet hoa don] where masp>3))

Correlated Subqueries Correlated Subqueries

 A subquery refers to the parent query  A subquery is reevaluated for every iteration of the

parent query

Example

ầ

Tr n Thi Kim Chi

161

SELECT Makh,Tenkh, Thanhpho FROM [Khach hang] WHERE Makh IN (SELECT Makh FROM [Nhan vien], [hoa don] WHERE [Hoa don].Manv = [Nhan vien].Manv and [Nhan vien].Thanhpho =[Khach hang].Thanhpho)

Unions – Phép hợp Unions – Phép hợp

ể ế ố ệ Union đ k t n t 2 câu l nh Select

ử Toán t Cú pháp

SELECT statement

UNION [ALL]

SELECT statement

Ví dụ

Tr n Thi Kim Chi

162

SELECT c.Thanhpho FROM [Khach hang] c UNION SELECT e.Thanhpho FROM [Nhan vien] e ầ

Unions – Phép hợp Unions – Phép hợp

ể ự

ử

ệ

ầ

ợ

UNION đ th c hi n phép h p, ta c n chú ý các

ấ

ầ

ả

ộ

ử ụ Khi s d ng toán t ắ nguyên t c sau: • Danh sách c t trong các truy v n thành ph n ph i có cùng s ố

ng.

ng ng trong t

ấ ỳ ầ

ả

ừ

ủ

ấ

ượ l • Các c t t ứ ộ ươ ặ ậ ả ấ ả t c các b ng, ho c t p con b t k các ấ ỗ ả ộ ượ ử ụ c s d ng trong b n thân m i truy v n thành ph n ph i c t đ ể ữ ệ cùng ki u d li u. ộ ươ ứ ệ

• Các c t t ộ

ả ả

ộ ừ

ấ

ỗ

ầ ng ng trong b n thân t ng truy v n thành ph n c a ư ứ ự ệ ấ nh nhau. m t câu l nh UNION ph i xu t hi n theo th t ứ ộ ộ ợ Nguyên nhân là do phép h p so sánh các c t t ng c t m t theo th ự ượ đ t

c cho trong m i truy v n.

ữ ệ

ể

ế ợ

ớ

• Khi các ki u d li u khác nhau đ

ẽ ượ

ữ ệ

ượ ể

c k t h p v i nhau trong câu ể c chuy n sang ki u d li u cao h n

ế

ệ l nh UNION, chúng s đ ể ượ (n u có th đ

c).

ợ ẽ

ề ộ

ề ộ ượ

ế

ỉ c ch 163

ấ

ầ

ị

ầ Tr n Thi Kim Chi • Tiêu đ c t trong k t qu c a phép h p s là tiêu đ c t đ ả ủ đ nh trong truy v n đ u tiên.

Unions – Phép hợp Unions – Phép hợp

ợ

ầ

ệ

ử

ể ự

UNION đ th c hi n phép h p, ta c n chú ý các

ớ

ầ

ể

ể ạ

ộ ả

ợ

k t ệ

ả ủ ề

ặ

ị ố

ượ ử ụ

ấ

ở

ể ượ ử ụ

ệ

ỉ

ử ụ

ượ

ấ

c s d ng trong c phép s d ng

ầ ủ

ế

ợ

ộ

ể ượ ử ụ c s d ng bên trong câu l nh INSERT. ượ

ử ụ

ệ ệ

c s d ng trong câu l nh CREATE

ử ụ Khi s d ng toán t ắ nguyên t c sau: •Truy v n thành ph n đ u tiên có th có INTO đ t o m i m t b ng ấ ừ ế qu c a chính phép h p. t •M nh đ ORDER BY và COMPUTE dùng đ s p x p k t qu truy ả ế ế ể ắ ở ố ấ ỉ ượ ử ụ v n ho c tính toán các giá tr th ng kê ch đ cu i câu c s d ng ấ ỳ ệ trong b t k truy v n c s d ng l nh UNION. Chúng không đ ầ thành ph n nào. •M nh đ GROUP BY và HAVING ch có th đ ề ả ừ b n thân t ng truy v n thành ph n. Chúng không đ ả ể đ tác đ ng lên k t qu chung c a phép h p. •Phép toán UNION có th đ •Phép toán UNION không đ VIEW.

ầ

Tr n Thi Kim Chi

164

ệ ệ

ả ả

ạ ạ

L nh SELECT INTO – T o b ng 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 ầ

Tr n Thi Kim Chi

165

GROUP BY FieldGroupName

ạ ạ

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

ạ ạ ạ ạ T o Table T m 1)1) T o Table T m

SELECT Tenkh as Ten, ThanhPho SELECT Tenkh as Ten, ThanhPho

INTO #Temp_Customer INTO #Temp_Customer

FROM [Khach hang] FROM [Khach hang]

ả ả Xem k t quế Xem k t quế

ầ

Tr n Thi Kim Chi

166

Select * From #Temp_Customer Select * From #Temp_Customer

ạ ạ

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

Ví d 2ụ

SELECT c.Makh As Name, Mahd, NgayLapHD

INTO Customer_Order

FROM [Khach hang] as c INNER Join [Hoa don] As o

ON c.Makh=o.Makh

ầ

Tr n Thi Kim Chi

167

WHERE Month(NgayLapHD) =7

ạ ạ

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

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

USE SalesDB

SELECT CompanyName As Name, Phone

INTO KhachHang

ầ

Tr n Thi Kim Chi

168

FROM NorthWind.dbo.Customers

ử ử

ữ ữ ệ S a ch a d li u ữ ữ ệ S a ch a d li u

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

ầ

Tr n Thi Kim Chi

169

ệ

ệL nh Insert L nh Insert

ữ ệ ề ả ộ • Thêm m t hay nhi u dòng d li u và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á ỉ

ộ ả

ể

ờ

ộ

ỉ

– T i m t th i đi m ch có th chèn vào m t b ng duy

ỉ ộ tr .ị ạ nh t.ấ

ầ

Tr n Thi Kim Chi

170

view. • L u ý:ư

ệ

ệL nh INSERT L nh INSERT

• Cú pháp 1 Cú pháp 1

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

ầ

Tr n Thi Kim Chi

171

• Ví d 1ụVí d 1ụ INSERT [San pham](Masp, Tensp) INSERT [San pham](Masp, Tensp) VALUES (123, 'Ice Tea') VALUES (123, 'Ice Tea')

ệ

ệL nh Insert L nh Insert

Create table t1 (col1 int identity, col2 varchar(30) default('my column default'), col3 timestamp, col4 varchar(40) null);

insert into t1 (col4) values('Explicit value') Select * from t1

insert into t1 (col2,col4) values('Explicit value','Explicit value' ) Select * from t1

ầ

Tr n Thi Kim Chi

172

insert into t1 (col2) values('Explicit value') Select * from t1

Insert into t1 default values; Select * from t1

ệ

ệL nh Insert L nh Insert

ầ

Tr n Thi Kim Chi

173

Create table t2(col1 int identity, col2 varchar(30)) Go insert t2 values('Row #1') Go Select * from t2 Go set identity_insert t2 ON Go insert into t2 (col1,col2) values (99,'Explicit') Go select * from t2

ệ

ệL nh Insert L nh Insert

ả ộ ế ả ừ ộ ề m t hay nhi u b ng chèn

• Cú pháp 2: t o 1 b k t qu t ạ ả vào 1 b ng khác INSERT [(Col1, Col2, … )] SELECT (Value1, Value2,…)

• Ví d 2ụ create table SPMOI (Masp int, tensp nvarchar(20)) create table SPMOI (Masp int, tensp nvarchar(20)) INSERT SPMOI(Od.Masp, P.Tensp) INSERT SPMOI(Od.Masp, P.Tensp)

ầ

Tr n Thi Kim Chi

174

SELECT OD.Masp, Tensp SELECT OD.Masp, Tensp FROM [San pham] as P INNER JOIN [Chi tiet hoa FROM [San pham] as P INNER JOIN [Chi tiet hoa

don] AS Od ON P. Masp = Od. Masp don] AS Od ON P. Masp = Od. Masp Select * from SPMOI Select * from SPMOI

L nh ệL nh ệ UPDATE UPDATE

ổ ậ

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

UPDATE SET = [ FROM ] [ WHERE

• Ví dụ

UPDATE [Chi Tiet Hoa don] SET Dongia=

( SELECT Dongia+ Dongia*0.2 FROM [San pham]) where

Masp=2

ầ

Tr n Thi Kim Chi

175

UPDATE [Chi tiet hoa don] SET Thue=Dongia+0.1*Dongia WHERE Masp<5

ệ

ệL nh DELETE 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

•

ầ

Tr n Thi Kim Chi

176

Ví dụ DELETE FROM [Chi Tiet Hoa Don] WHERE Mahd =10148

ệ

ệL nh DELETE L nh DELETE

• Xóa dòng trùng nhau (Duplicate row • Ba cách:

ế

ầ

Tr n Thi Kim Chi

177

ệ – Dùng Windowing – Dùng khóa thay th (surrogate key) – Dùng l nh select distant into

ệ

ệL nh DELETE L nh DELETE

ầ

Tr n Thi Kim Chi

178

Dùng Windowing

ệ

ệL nh DELETE L nh DELETE

ầ

Ví dụ create table T3(col1 int null, col2 char(5) null) insert T3(col1,col2) values (1,'abc'),(2,'abc'),(2,'abc'), (2,'abc'), (7,'abc'),(7,'abc') select * from T3Tr n Thi Kim Chi

179

ệ

ệL nh DELETE L nh DELETE

Ví dụ

ầ

Tr n Thi Kim Chi

180

Select col1,col2, ROW_NUMBER() Over(partition by col1,col2 order by col1) as RN from T3

ệ

ệL nh DELETE L nh DELETE

ụ ằ Ví d : xóa hàng trùng nhau b ng Surrogate Key

alter table t3 add pk1 int identity not null

constraint pk_Khoa Primary Key

ầ

Tr n Thi Kim Chi

181

Select * from T3

ệ

ệL nh DELETE L nh DELETE

ụ ằ Ví d : xóa hàng trùng nhau b ng Select Distant Into

ầ

Tr n Thi Kim Chi

182

Select distinct col1,col2 into NoDups from T3

ệ

ệL nh TRUNCATE TABLE L nh TRUNCATE TABLE

• L nh TRUNCATE TABLE d ùng để

ộ ả ộ ữ ệ à không ghi

ư ệ ố àn toàn gi ng nh câu l nh

ệ ẫ ỗ i trigger

ệ – Xóa toàn b d li u trong m t b ng m ạ ậ nh t ký l i – V ch c năng ho ề ứ Delete – Nhanh h n câu l nh delete ơ – Không b t cậ ác b y l TRUNCATE TABLE table_name

ầ

Tr n Thi Kim Chi

• Ví dụ

183

TRUNCATE TABLE NewProducts

Merging Data Merging Data

ầ

Tr n Thi Kim Chi

184

ể ự ể ự

ụ VVíí d Merging Data đ th c thi d Merging Data đ th c thi ụ Insert vàà Update Update Insert v

ầ