Them 1 dong' trong table: Alter Table Nhanvien Add HSLuong real

  Su dung lenh Alter de chinh sua du lieu bang

+Thêm 1 cot vào bang :

Alter Table Nhanvien

Add Madv int Not Null

+Loai bo 1 cot trong bang :

Alter Table Nhanvien

Drop Column  Madv

+Xoa bang voi lenh Drop

Drop Table Tenbang

1.Doi ten cac cot khi truy van :

2.Tu khoa Distinct

Select Distinct Cac cot

From TenBang

3.Xac dinh bang trong menh de from

VD: Select N.Hoten,N.Ngaysinh,N.Diachi

    from Nhanvien N

4.Manh de Where

Xac dinh dieu kien cac ham duoc truy van,Bieu thuc trong menh de Where xac dinh theo bieu thuc logic

- Cac phep toan

+Phep toan so sanh =,<,>,>,<,!>,!<

+Tu khoa xac dinh pham vi Between, Not Between

+Danh sach In , Not In

+Theo mau dinh dang like , Not like

+Gia tri Null :is null, is not null

+Phep toan logic : And , or

- Tu khoa Between

Select * from Sanpham

Where Dongia Between 15.000 And 25000

- Tu khoa In

Select * from Sanpham   

Where Masp In ('2','4','6','8')

- Tu khoa Like

Select * from Sanpham

Where Tensp like '%kem%'

5.Toan tu Union

Select * from table1

Union   

Select * from table2

6.Truy van du lieu tu nhieu bang

* Theo Dieu kien lien ket :

- Lien ket bang nhau

VD:Select S.TenSP,S.Soluong,S.Dongia,D.TenDM

From San pham S, Danhmucsp D

Where S.MaDM = D.MaDM

7.Cac loai phep noi

-Phep noi bang va phep noi tu nhien

 * Select * from Nhanvien N , Donvi D

   Where N MaDV= D MaDV

 * Select MaNV,Hoten,Ngaysinh,Diachi,HSLuong,TenDV from Nhanvien N, donvi D

 Where N.Madv - D.Madv

-Phep noi voi cac dieu kien bo xung

    Select * from Nhanvien N,donvi D

    Where N.MaDV = D.MaDV    AND    N.HSLuong>2.10

 - Tinh' luong : Select Hoten,HSLuong*1250000 As Luong, TenDV

    From Nhanvien N,Donvi D

    Where M.MaDV=D.MaDV

-Phat bieu Inner Join

Select * from Nhanvien N

Inner join Donvi D

On N.Madv= D.Madv

8.Thong ke du lieu voi Group by va Having

Cac ham :

Sum = TInh tong cac gia tri

Avg = Tinh trung binh cua cac gia tri

Count = So cac gia tri trong bieu thuc

Max = Tinh gia tri lon nhat

Min = Tinh gia tri nho nhat

VD:Tinh he so luong trung binh cua cac nhan hieu theo tung don vi

Select D.Madv,tendv,Avg(HsLuong)

From Nhanvien N,Donvi D

Where N.Madv=D.MaNV

Group by D.Madv,tendv

Having Avg(HSLuong)>1.92

Chu' y' :Danh sach ten cac cot trong danh sach chon cua cau lenh Select ca group by phai giong nhau

 neu khong cau lenh se khong hop le

Ket hop 3 bang :

VD: Select P.Name,C.Name from Product P,Category C,ProductCategory PC

Where C.CategoryID=PC.CategoryID AND P.ProductID=PC.ProductID

Ket hop 4 bang :

VD: Select D.name As Departmentname,C.name As Categoryname,p.name As Productname

from Department D, Category C,Productcategory PC,Product P

Where D.DepartmentID=C.CategoryID AND C.CategoryID=PC.CategoryID

    AND PC.ProductID=P.ProductID AND D.DepartmentID=1

Xoa 1 ban ghi :

Delete Product

Where ProductID In(Select PC.ProductID

from Department D, Category C,Productcategory PC

Where D.DepartmentID=C.CategoryID AND C.CategoryID=PC.CategoryID AND D.DepartmentID=1)

== Khung nhin View ==

2.Tao View

Create View tenview As

Cau lenh Select

Create View vm_categoryProduct As

Select C.Name CategoryName,CP.Name ProductName,P.Description,P.Price

From Category C,Product P, ProductCategory PC

Where Pc.ProductID=P.ProductID AND PC.CategoryID=C.CategoryID

VD2:tat ca cac lop

Create View vm_Lophoc As

Select C.masv,C.ten,C.hodem,C.ngaysinh,C.noisinh,C.cmt,c.malop

From Sinhvien C,Lophoc P

Where C.malop=P.malop .

+ Su dung View

2:36 AM 6/20/2011 Vm_CategoryProduct

== Thu tuc Luu tru == Stroe Procedure

Thiet lap thu tuc luu tru :

+ Thu tuc khong co tham so :

Create Procedure Tenthutuc

As

Cau lenh T-SQL

VD: Create Procedure au_pro_CategoryProduct

As

Select C.CategoryID,C.Name CategoryName,P.ProductID,P.Name ProductName,P.Description,P.Price

From Category C, Product P, ProductCategory PC

Where C.CategoryID=Pc.CategoryID AND P.ProductID=PC.ProductCategory

Exec au_pro_CategoryProduct

+ Thu tuc co tham so :

Create Procedure Au_SanphamtrongDanhmuc @tendanhmuc varchar(50)

As

Select C.Name Category,P.Name ProductName

From Category C, Product P, ProductCategory PC

Where C.CategoryID = PC.CategoryID AND P.ProductID=Pc.ProductID

    AND C.Name=@tendanhmuc

EXECUTE Au_SanphamtrongDanhmuc 'Birthdays'

Create Procedure Au_Sanphammoi23 @tendanhmuc varchar(50),@tendanhmuc2 varchar(50)

As

Select C.Name Category,P.Name ProductName

From Category C, Product P, ProductCategory PC

Where C.CategoryID = PC.CategoryID AND P.ProductID=Pc.ProductID

    AND C.Name=@tendanhmuc OR C.Name=@tendanhmuc2

EXECUTE Au_Sanphammoi23 'Weddings','Cartoons'

Create Table Phongban

(

MaPhong char(5) not null,

TenPhong nvarchar(50)

)