Bài 2 – Các đối tượng trong CSDL
Bài 2 – Các đối tượng trong CSDL
Cùng tìm hiểu và cách tạo ra các đối tượng chính trong SQL: Database, Table, View …
Khái niệm về cơ sở dữ liệu
- Database dùng để
- Chứa các bảng, bảng ảo, thủ tục nội,…
- Mỗi database có một danh sách các người dùng
- Người dùng phải có quyền truy cập database
- Có thể phân nhóm người dùng để cấp quyền
- Tử phiên bản SQL Server 2000 hỗ trợ Application Role
- Các database hệ thống Master, Model, Tempdb, msdb
- Các database ví dụ AdventureWorks, AdventureWorkDWs
Các tập tin vật lý lưu trữ cơ sở dữ liệu
- Một database bao gồm tối thiểu hai file
- .mdf: lưu trữ các đối tượng trong database như table, view, …
- Có thể bổ sung thêm các tập tin lưu trữ khác
- Tổ chức tốt các tập tin lưu trữ giúp tăng tốc độ xử lý
- .ldf: lưu trữ quá trình cập nhật/thay đổi dữ liệu
- Hỗ trợ phục hồi dữ liệu
- Hỗ trợ backup/restore dữ liệu
- .mdf: lưu trữ các đối tượng trong database như table, view, …
- Các thông số về kích thước
- Initial size
- File growth
- Maximum file size
Tạo database
USE master
GO
--Nếu đã tồn tại database QLBanHang rồi thì xóa đi và tạo mới
IF EXISTS (SELECT * FROM MASTER.sys.DATABASES WHERE NAME=N'QLBanHang')
--Xóa Database
DROP DATABASE QLBanHang
GO
--Tạo database mới
CREATE DATABASE QLBanHang
Khái niệm về bảng
- Bảng dùng để lưu trữ các thông tin của một đối tượng trong thực tế
- Gồm có các dòng và các cột
- Bảng trong CSDL thường có khoá chính (primary key)
- Các bảng thường liên hệ với nhau bằng các mối quan hệ
- Bảng được tạo trong các Schema (mặc định là schema dbo)
- Bảng có thể có các ràng buộc (constraint), trigger
Các thuộc tính của bảng
- Tên bảng
- Tên cột
- Kiểu dữ liệu
- Độ dài dữ liệu
- Số ký số lưu trữ
- Số số lẻ lưu trữ
- Thuộc tính trên cột
- Allow null
- Identity
- Default value
Tạo cấu trúc bảng đơn giản
CREATE TABLE Tên_schema.Tên_bảng
(
Tên_cột1 Kiểu_dữ_liệu [NOT NULL] ,
Tên_cột2 Kiểu_dữ_liệu [NOT NULL] [, ...]
)
Ví dụ tạo bảng
CREATE TABLE vattu
(
mavtu CHAR(4),
tenvtu nvarchar(100) NOT NULL,
dvtinh nvarchar(10),
phantram REAL,
--Tạo khóa chính
CONSTRAINT pk_vattu PRIMARY KEY(mavtu)
)
Khái niệm về bảng ảo
- Được xây dựng từ câu truy vấn SELECT để hiển thị dữ liệu từ một hay nhiều bảng
- Tập hợp dữ liệu, thể hiện cùng một dữ liệu nhưng theo nhiều cách khác nhau
- Làm việc tương tự như một bảng nhưng không lưu trữ dữ liệu
- Cho phép thêm/xoá/sửa
- Bảo mật dữ liệu, bảo mật nội dung câu truy vấn dữ liệu
- Một số hạn chế trong câu lệnh SELECT
- Order By
- Compute
- Compute By
Tạo mới bảng ảo bằng CREATE VIEW
CREATE VIEW Tên_bảng_ảo
[(Tên_các_cột)]
[WITH ENCRYPTION]
AS Câu_lệnh_SELECT
[WITH CHECK OPTION]
Ví dụ tạo bảng ảo
CREATE VIEW vw_Vattu AS
SELECT * FROM vattu
Xem và cập nhật dữ liệu bảng ảo
- Làm việc như một bảng thông thường sử dụng câu SELECT để xem dữ liệu Select * From vw_DonDH
- Sử dụng INSERT/UPDATE để cập nhật dữ liệu
- Chỉ có thể cập nhật vào một bảng
- Để INSERT dữ liệu vào bảng, bảng ảo phải thỏa mãn các yêu cầu về khóa, ràng buộc khóa ngọai, các cột NOT NULL, các cột tính toán, order by, group by, distinct
- Sử dụng Delete để xoá dữ liệu: bảng ảo tạo từ hai hay nhiều bảng không thể xoá
- Có thể xây dựng các trigger trên bảng ảo
Cập nhật dữ liệu qua bảng ảo sử dụng trigger INSTEAD OF
- Bảng ảo có nhiều hạn chế khi thực hiện cập nhật dữ liệu
- Group By, Order By, Distinct
- Thiếu cột khoá
- Ràng buộc toàn vẹn
- SQL Server cung cấp loại trigger INSTEAD OF
- Cơ chế tương tự như trigger thông thường
- Mở rộng khả năng cập nhật, tính toán dữ liệu, đặc biệt với bảng ảo
- Xem thêm phần TRIGGER
Bài tập: Các đối tượng trong CSDL
Tạo các đối tượng trong CSDL sau:
Danh mục Vật Tư (VATTU)
| Tên cột | Ý nghĩa | Kiểu | Độ rộng |
| MaVtu | Mã vật tư | Char | 4 |
| TenVTu | Tên vật tư | VarChar | 100 |
| DvTinh | Đơn vị tính | VarChar | 10 |
| PhanTram | Tỷ lệ phần trăm | Real |
Danh mục Nhà Cung Cấp (NHACC)
| Tên cột | Ý nghĩa | Kiểu | Độ rộng |
| MaNhaCC | Mã nhà cung cấp | Char | 3 |
| TenNhaCC | Tên nhà cung cấp | NVarChar | 100 |
| DiaChi | Địa chỉ | NVarChar | 200 |
| DienThoai | Điện thoại | NVarChar | 20 |
go
create table nhacc
(
manhacc char(3),
tennhacc nvarchar(100) not null,
diachi nvarchar(200),
dienthoai nvarchar(20),
constraint pk_nhacc primary key(manhacc)
)
Đơn đặt hàng (DONDH)
| Tên cột | Ý nghĩa | Kiểu | Độ rộng |
| SoDh | Số đơn đặt hàng | Char | 4 |
| NgayDh | Ngày đặt hàng | DateTime | |
| MaNhaCC | Mã nhà cung cấp | Char | 3 |
go
create table dondh
(
sodh char(4),
ngaydh datetime,
manhacc char(3),
constraint pk_dondh primary key(sodh)
)
Chi tiết Đơn đặt hàng (CTDONDH)
| Tên cột | Ý nghĩa | Kiểu | Độ rộng |
| SoDh | Số đơn đặt hàng | Char | 4 |
| MaVTu | Mã vật tư | Char | 4 |
| SlDat | Số lượng đặt hàng | Int |
GO
CREATE TABLE ctdondh
(
sodh CHAR(4),
mavtu CHAR(4),
sldat INT,
CONSTRAINT pk_ctdondh PRIMARY KEY(sodh,mavtu)
)
Phiếu nhập hàng (PNHAP)
| Tên cột | Ý nghĩa | Kiểu | Độ rộng |
| SoPn | Số phiếu nhập hàng | Char | 4 |
| NgayNhap | Ngày nhập hàng | Datetime | |
| SoDh | Số đơn đặt hàng | Char |
go
create table pnhap
(
sopn char(4) ,
ngaynhap datetime default getdate(),--Ngày ngập sẽ bằng ngày hiện tại
sodh char(4),
constraint pk_pnhap primary key(sopn)
)
Chi tiết nhập hàng (CTPNHAP)
| Tên cột | Ý nghĩa | Kiểu | Độ rộng |
| SoPn | Số phiếu nhập hàng | Char | 4 |
| MaVTu | Mã vật tư | Char | 4 |
| SlNhap | Số lượng nhập hàng | Int | |
| DgNhap | Đơn giá nhập hàng | Money |
go
create table ctpnhap
(
sopn char(4),
mavtu char(4),
slnhap int,
dgnhap money,
constraint pk_ctpnhap primary key(sopn,mavtu)
)
Phiếu xuất hàng (PXUAT)
| Tên cột | Ý nghĩa | Kiểu | Độ rộng |
| SoPx | Số phiếu xuất | Char | 4 |
| NgayXuat | Ngày xuất hàng | Datetime | |
| TenKh | Tên khách hàng | NVarchar | 100 |
go
create table pxuat
(
sopx char(4),
ngayxuat datetime default getdate(),--Ngày xuất bằng ngày hiện tại
tenkh nvarchar(100),
constraint pk_pxuat primary key(sopx)
)
Chi tiết xuất hàng (CTPXUAT)
| Tên cột | Ý nghĩa | Kiểu | Độ rộng |
| SoPx | Số phiếu xuất | Char | 4 |
| MaVTu | Mã vật tư | Char | 4 |
| SlXuat | Số lượng xuất hàng | Int | |
| DgXuat | Đơn giá xuất hàng | Money |
go
create table ctpxuat
(
sopx char(4),
mavtu char(4),
slxuat int,
dgxuat money,
constraint pk_ctpxuat primary key(sopx,mavtu)
)
Tồn kho (TONKHO)
| Tên cột | Ý nghĩa | Kiểu | Độ rộng |
| NamThang | Năm tháng | Char | 6 |
| MaVTu | Mã vật tư | Char | 4 |
| SLDau | Số lượng tồn đầu kỳ | Int | |
| TongSLN | Tổng số lượng nhập trong kỳ | Int | |
| TongSLX | Tổng số lượng xuất trong kỳ | Int | |
| SLCuoi | Số lượng tồn cuối kỳ, cột Formula | Int |
GO
CREATE TABLE tonkho
(
namthang CHAR(6),
mavtu CHAR(4),
sldau INT,
tongsln INT,
tongslx INT,
slcuoi AS sldau+tongsln-tongslx,
CONSTRAINT pk_tonkho PRIMARY KEY(namthang,mavtu)
)
Sử dụng câu lệnh INSERT INTO VALUES để thêm các mẩu tin vào bảng
INSERT INTO nhacc
VALUES('C01', N'Lê Minh Trí', N'54 Hậu Giang Q6 HCM' ,'8781024')
INSERT INTO nhacc
VALUES('C02', N'Trần Minh Thạch', N'145 Hùng Vương Mỹ Tho', '7698154')
INSERT INTO nhacc
VALUES('C03', N'Hồng Phương', N'154/85 Lê Lai Q1 HCM', '9600125')
INSERT INTO nhacc
VALUES('C04', N'Nhật Thắng', N'198/40 Hương Lộ 14 QTB HCM', '8757757')
INSERT INTO nhacc
VALUES('C05', N'Lưu Nguyệt Quế',N'178 Nguyễn Văn Luông Đà Lạt','7964251')
INSERT INTO nhacc
VALUES('C07',N'Cao Minh Trung', N'125 Lê Quang Sung Nha Trang', N'Chưa có')
GO
--Table Vattu
INSERT INTO vattu
VALUES('DD01',N'Đầu DVD Hitachi 1 đĩa',N'Bộ','40')
INSERT INTO vattu
VALUES('DD02', N'Đầu DVD Hitachi 3 đĩa', N'Bộ','40')
INSERT INTO vattu
VALUES('TL15', N'Tủ lạnh Sanyo 150 lit', N'Cái', '25')
INSERT into vattu
VALUES('TL90', N'Tủ lạnh Sanyo 90 lit', N'Cái', '20')
INSERT INTO vattu
VALUES('TV14', N'Tivi Sony 14 inches', N'Cái', '15')
INSERT INTO vattu
VALUES('TV21', N'Tivi Sony 21 inches', N'Cái', '10')
INSERT INTO vattu
VALUES('TV29' ,N'Tivi Sony 29 inches', 'Cái' ,'10')
INSERT INTO vattu
VALUES('VD01', N'Đầu VCD Sony 1 đĩa',N'Bộ', '30')
INSERT INTO vattu
VALUES('VD02',N'Đầu VCD Sony 3 đĩa', N'Bộ', '30')
GO
--Table Dondh
INSERT INTO dondh
VALUES('D001', '01/15/2009', 'C03')
INSERT INTO dondh
VALUES('D002', '01/30/2009', 'C01')
INSERT INTO dondh
VALUES('D003' ,'02/10/2009' ,'C02')
INSERT INTO dondh
VALUES('D004', '02/17/2009' ,'C05')
INSERT INTO dondh
VALUES('D005' ,'03/01/2009', 'C02')
INSERT INTO dondh
VALUES('D006', '03/12/2009' ,'C05')
GO
--Table Pnhap
INSERT INTO pnhap
VALUES('N001', '01/17/2009', 'D001')
INSERT INTO pnhap
VALUES('N002', '01/20/2009', 'D001')
INSERT INTO pnhap
VALUES('N003', '01/31/2009', 'D002')
INSERT INTO pnhap
VALUES('N004','02/15/2009', 'D003')
GO
--Table ctdondh
INSERT INTO ctdondh
VALUES('D001', 'DD01', '10')
INSERT INTO ctdondh
VALUES('D001' ,'DD02' ,'15')
INSERT INTO ctdondh
VALUES('D002' ,'VD02', '30')
INSERT INTO ctdondh
VALUES('D003' ,'TV14' ,'10')
INSERT INTO ctdondh
VALUES('D003' ,'TV29', '20')
INSERT INTO ctdondh
VALUES('D004' ,'TL90' ,'10')
INSERT INTO ctdondh
VALUES('D005' ,'TV14', '10')
INSERT INTO ctdondh
VALUES('D005', 'TV29' ,'20')
INSERT INTO ctdondh
VALUES('D006' ,'TV14', '10')
INSERT INTO CTDONDH
VALUES('D006' ,'TV29' ,'20')
INSERT INTO CTDONDH
VALUES('D006' ,'VD01' ,'20')
GO
--Table ctpnhap
INSERT INTO ctpnhap
VALUES('N001', 'DD01', '8' ,'2,500,000')
INSERT INTO ctpnhap
VALUES('N001' ,'DD02' ,'10' ,'3,500,000')
INSERT INTO ctpnhap
VALUES('N002', 'DD01', '2', '2,500,000')
INSERT INTO ctpnhap
VALUES('N002' ,'DD02' ,'5', '3,500,000')
INSERT INTO ctpnhap
VALUES('N003', 'VD02', '30', '2,500,000')
INSERT INTO ctpnhap
VALUES('N004', 'TV14', '5', '2,500,000')
INSERT INTO ctpnhap
VALUES('N004' ,'TV29' ,'12', '3,500,000')
GO
--Table pxuat
INSERT INTO pxuat
VALUES('X001', '01/17/2009', N'Nguyễn Ngọc Phương Nhi')
INSERT INTO pxuat
VALUES('X002', '01/25/2009', N'Nguyễn Hồng Phương')
INSERT INTO pxuat
VALUES('X003', '01/31/2009', N'Nguyễn Tuấn Tú')
GO
--Table ctpxuat
INSERT INTO ctpxuat
VALUES('X001', 'DD01', '2', '3,500,000')
INSERT INTO ctpxuat
VALUES('X002' ,'DD01', '1' ,'3,500,000')
INSERT INTO ctpxuat
VALUES('X002' ,'DD02', '5', '4,900,000')
INSERT INTO ctpxuat
VALUES('X003', 'DD01' ,'3' ,'3,500,000')
INSERT INTO ctpxuat
VALUES('X003' ,'DD02', '2', '4,900,000')
INSERT INTO ctpxuat
VALUES('X003' ,'VD02', '10' ,'3,250,000')
GO
--Table tonkho
INSERT INTO tonkho
VALUES('200901' ,'DD01', '0', '10', '6')
INSERT INTO tonkho(namthang,mavtu,sldau,tongsln,tongslx)
VALUES('200901', 'VD02', '0' ,'30' ,'10' )
INSERT INTO tonkho(namthang,mavtu,sldau,tongsln,tongslx)
VALUES('200902', 'DD01' ,'4' ,'0', '0')
INSERT INTO tonkho(namthang,mavtu,sldau,tongsln,tongslx)
VALUES('200902', 'DD02' ,'8' ,'0', '0' )
INSERT INTO tonkho(namthang,mavtu,sldau,tongsln,tongslx)
VALUES('200901' ,'DD02', '0', '15' ,'7' )
INSERT INTO tonkho(namthang,mavtu,sldau,tongsln,tongslx)
VALUES('200902', 'VD02', '20', '0' ,'0' )
INSERT INTO tonkho(namthang,mavtu,sldau,tongsln,tongslx)
VALUES('200902', 'TV14', '5', '0', '0')
INSERT INTO tonkho(namthang,mavtu,sldau,tongsln,tongslx)
VALUES('200902', 'TV29' ,'12' ,'0', '0' )
GO
Đối tượng bảng ảo (view)
- Tạo view có tên vw_CTPNHAP bao gồm các thông tin sau: số phiếu nhập hàng, mã vật tư, số
lượng nhập, đơn giá nhập, thành tiền nhập - Tạo view có tên vw_CTPNHAP_VT bao gồm các thông tin sau: số phiếu nhập hàng, mã vật tư, tên vật tư, số lượng nhập, đơn giá nhập, thành tiền nhập
- Tạo view có tên vw_CTPNHAP_VT_PN bao gồm các thông tin sau: số phiếu nhập hàng, ngày nhập hàng, số đơn đặt hàng, mã vật tư, tên vật tư, số lượng nhập, đơn giá nhập, thành tiền nhập
- Tạo view có tên vw_CTPNHAP_VT_PN_DH bao gồm các thông tin sau: số phiếu nhập hàng, ngày nhập hàng, số đơn đặt hàng, mã nhà cung cấp, mã vật tư, tên vật tư, số lượng nhập, đơn giá nhập, thành tiền nhập
- Tạo view có tên vw_CTPNHAP_loc bao gồm các thông tin sau: số phiếu nhập hàng, mã vật tư, số lượng nhập, đơn giá nhập, thành tiền nhập. Và chỉ liệt kê các chi tiết nhập có số lượng nhập > 5
- Tạo view có tên vw_CTPNHAP_VT_loc bao gồm các thông tin sau: số phiếu nhập hàng, mã vật tư, tên vật tư, số lượng nhập, đơn giá nhập, thành tiền nhập. Và chỉ liệt kê các chi tiết nhập vật tư có đơn vị tính là Bộ
- Tạo view có tên vw_CTPXUAT bao gồm các thông tin sau: số phiếu xuất hàng, mã vật tư, số lượng xuất, đơn giá xuất, thành tiền xuất
- Tạo view có tên vw_CTPXUAT_VT bao gồm các thông tin sau: số phiếu xuất hàng, mã vật tư, tên vật tư, số lượng xuất, đơn giá xuất
- Tạo view có tên vw_CTPXUAT_VT_PX bao gồm các thông tin sau: số phiếu xuất hàng, tên khách hàng, mã vật tư, tên vật tư, số lượng xuất, đơn giá xuất
- Sửa view vw_CTPXUAT_VT_PX để bổ sung thêm cột thành tiền xuất
- Xem nội dung câu lệnh SELECT của view vw_CTPXUAT
- Xoá view vw_CTPXUAT
mysite 
Trả lời