Bài giảng Table

1Chương 3: TABLE Giáo viên: Tạ Thúc Nhu Khoa CNTT trường ĐH Lạc Hồng HỆ QUẢN TRỊ CSDL SQL SERVER SQL Server2 NỘI DUNG I. Các kiểu dữ liệu II. Các phép toán III. Tạo Table trong một CSDL IV. Thay đổi cấu trúc và ràng buộc V. Xóa Table VI. Đổi tên Table VII.Mô hình quan hệ dữ liệu SQL Server3 I- CÁC KiỂU DỮ LiỆU 1. Exact Numerics: Số Chính Xác 2. Approximate Numerics: Số gần đúng 3. Date and Time - Ngày giờ 4. Character strings - Chuỗi ký tự ASCII 5. Unicode character strings - Chuổi ký tự Unicode 6. Binary Strings - Chuỗi số nhị phân 7. Other Data Types SQL Server4 1- Số Chính Xác - Exact Numerics • Bigint (8 bytes) giá trị từ - 263 đến 263-1 • Int :(4 bytes) giá trị từ - 231 đến 231 - 1. • SmallInt : (2 bytes) giá trị từ - 215 đến 215 - 1. • Tinyint : (1 byte) giá trị từ 0 đến 255. • Bit : có giá trị 0, 1 hoặc NULL. • Decimal [ ( p [, s ]) ] – P (precision) có giá trị từ 1 đến 38, là tổng số chữ số tối đa bên phải và bên trái dấu chấm thập phân. Mặc định là 18. – S (Scale) có giá trị từ 0 đến P, là số chữ số lẻ bên phải dấu chấm thập phân. Mặc định bằng 0 • Numeric [ ( p [, s ] ) ]: tương tự Decimal • Money :-922,337,203,685,477.5808 đến 922,337,203,685,477.5807 • SmallMoney : -214,748.3648 đến 214,748.3647 2SQL Server5 2- Số gần đúng - Approximate Numerics a) Float [( n )] : • Miền giá trị tuyết đối từ 2.23E- 308 đến 1.79E +308 • n là số bít dùng lưu phần định trị, có giá trị từ 1 – 53, mặc định là 53. • Số byte lưu trữ phụ thuộc vào giá trị của n. – Với n  [1, 24]: 4 byte, với độ chính xác là 7 chữ số thập phân. – Với n  [25, 53]: 8 byte, với độ chính xác là 15 chữ số thập phân. b) Real : 4 byte, Miền giá trị tuyệt đối 1.18E- 38 đến 3.40E +38. SQL Server6 3- Ngày giờ - Date And Time a) DateTime : • Miền giá trị : từ 1-1- 1753 đến 31-12- 9999 b) SmallDateTime : • Miền giá trị: 1-1- 1900 đến 6-6- 2079 Chú ý: Hằng ngày giờ bao trong dấu nháy đơn Ví dụ: ‘1998-01-20 23:59:59.997’ SQL Server7 4- Chuỗi ký tự - Character Strings • Char [(n)] : độ dài cố định, tối đa là 8000 byte. • Varchar [( n )]: độ dài không cố định, tối đa là 8000 byte. • Varchar( max ) | Text: độ dài không cố định, tối đa là 231-1 byte. SQL Server8 5- Chuổi ký tự Unicode - Unicode Character Strings • nChar [( n )] : độ dài cố định, tối đa là 4000 ký tự. • nVarchar[( n )] : độ dài không cố định, tối đa là 4000 ký tự. • nVarchar(max) | nText : độ dài không cố định, tối đa là 2^31–1 byte. Chú ý: Hằng chuỗi Unicode bao trong dấu nháy đơn và phải bắt đầu bằng chữ N: N’sssss’ 3SQL Server9 6- Dữ liệu nhị phân - Binary Strings • Binary [( n )] : độ dài cố định, n có tối đa 8000 bytes. • Varbinary [( n )] : độ dài thay đổi, n có tối đa 8000 bytes. • Varbinary( max ) | Image : độ dài thay đổi, lưu trong 2^31 – 1 bytes. SQL Server10 7- Other Data Types • Sql_Variant : dùng lưu giá trị của các kiểu dữ liệu khác trong SQL Server ngoại trừ các kiểu text, ntext, Image, timestamp, và sql_variant. • Timestamp : kiểu số (binary(8) hay varbinary(8)). Cột khai báo kiểu này sẽ được tự động cập nhật với giá trị phân biệt mỗi khi thêm mẫu tin mới.. • UniqueIdentifier : 16 bytes GUID (Globally Unique Identifier). Nhận giá trị này bằng hàm NewID(). • XML : dùng lưu dữ liệu XML SQL Server11 II- CÁC PHÉP TOÁN 1. Các phép toán số học: * (nhân), / (chia nguyên hay thực), % (phần dư); + (cộng), - (trừ) 2. Phép nối chuỗi : + 3. Các phép toán so sánh: , >=, !>, =, hay != 4. Các phép toán lôgic: NOT, AND (conjunction), OR (disjunction). SQL Server12 II- Các phép toán (tt) 5. Các phép toán phạm vi: a) IS [NOT] NULL b) [NOT] IN () c) [NOT] BETWEEN AND d) [NOT] LIKE ‘Mẫu v.bản’ Ký tự đơn bất kỳ không nằm trong giới hạn được chỉ định ví dụ [^a-f] hay một tập [^abcdef] [^…] Ký tự đơn bất kỳ trong giới hạn được chỉ định ví dụ [a-f] hay một tập [abcdef] [ … ] Ký tự đơn bất kỳ_ Chuỗi ký tự bất kỳ gồm không hoặc nhiều ký tự% Ý nghĩa đại diệnKý tự 4SQL Server13 III- TẠO TABLE 1. Bằng Lệnh CREATE TABLE 2. Sử dụng Object Explorer - Khai báo các cột - Khai báo ràng buộc dữ liệu SQL Server14 1- Bằng Lệnh CREATE TABLE: Cú pháp : CREATE TABLE TableName ( column_name data_type [] | computer_column_name AS [PERSISTED] | [ , ...n] ) [ON { file_group | PRIMARY } ] SQL Server15 1.1 Khai báo cột dữ liệu • CREATE TABLE SanPham ( MaSP Smallint , TenSP varchar(30) , DonGia Money , SoTon Real ) • CREATE TABLE DDH ( MaDH Int , MaNCC Int , NgayLap DateTime , NgayYCGiao DateTime ) SQL Server16 1.2 Khai báo cột biểu thức computer_column_name AS expression [PERSISTED] • Cột tính toán, là cột chỉ đọc, chứa dữ liệu tính toán từ những cột khác. Mặc định không lưu giá trị cột tính toán trong table. CREATE TABLE CTDH ( MaDH Int , MaSP SmallInt , SL float , DGBan Money , ThanhTien As SL * DGBan ) • Dùng PERSISTED để chỉ định lưu giá trị cột tính toán trong table và tự động cập nhật giá trị. Cột tính toán PERSISTED có thể được tham gia vào khóa của bảng. CREATE TABLE mytable ( Low int, High int, myavg AS (low + high)/2 PERSISTED ) 5SQL Server17 1.3 Khai báo ràng buộc dữ liệu (Constraint) Có 2 cách khai báo ràng buộc trên table phụ thuộc vào số cột tham gia điều kiện của ràng buộc: a) Ràng buộc dữ liệu trên một cột (Column Constraint) b) Ràng buộc dữ liệu trên nhiều cột của Table (Table Constraint) SQL Server18 Được viết ngay sau lời khai báo cột dữ liệu Cú pháp: [CONSTRAINT constraint_name ] NULL | NOT NULL | PRIMARY KEY | UNIQUE | REFERENCES ref_table [(ref_column)] [ON DELETE {CASCADE | NO ACTION | SET NULL | SET DEFAULT } ] [ON UPDATE {CASCADE | NO ACTION | SET NULL | SET DEFAULT } ] | CHECK (logical_expression) | DEFAULT a) Các loại ràng buộc trên một cột SQL Server19 Ví dụ: Khai báo ràng buộc trên 1 cột CREATE TABLE SanPham ( MaSP Smallint Primary Key , TenSP varchar(30) Not Null Unique, DonGia Money Default 0 Check (DonGia >=0), SoTon Real Default 0 Check (SoTon >=0), MaLoaiSP Smallint Not Null References LoaiSP(MaLoaiSP) On Delete No Action On Update Cascade ) SQL Server20 Ví dụ: Đặt tên ràng buộc trên một cột CREATE TABLE SanPham ( MaSP SmallInt Constraint pkMaSP Primary Key , TenSP varchar(30) Constraint uniTenSP Unique , DonGia Money Constraint chkDonGia Check (DonGia >=0) , SoTon Real Constraint dfSoTon Default 0 , MaLoaiSP SmallInt Constraint nnMaLoaiSP Not Null Constraint fkMaLSP References LoaiSP (MaLoaiSP) On Delete No Action On Update Cascade ) 6SQL Server21 b- Các loại ràng buộc trên nhiều cột [CONSTRAINT constraint_name ] PRIMARY KEY ( têncột [ ASC | DESC ] [ ,...n ] ) | UNIQUE ( têncột [ ASC | DESC ] [ ,...n ] ) | FOREIGN KEY ( têncột [,...] ) REFERENCES ref_table [( )] [ ON DELETE { CASCADE | NO ACTION | SET NULL | SET DEFAULT } ] [ ON UPDATE { CASCADE | NO ACTION | SET NULL | SET DEFAULT} ] | CHECK ( logical_expression ) Được viết thành một mệnh đề riêng biệt Cú pháp SQL Server22 Ví dụ - Khai báo khóa chính gồm nhiều cột CREATE TABLE CTDH ( MaDH Int Not Null Reference DDH, MaSP SmallInt Not Null Reference SanPham, SoLuong float Default 0 Check (SoLuong > 0), DGBan Money Default 0 Check (DonGia > 0), ThanhTien As SL * DGBan, Constraint pk_CTHD Primary Key (MaDH, MaSP) ) SQL Server23 Ví dụ - Khai báo ràng buộc liên thuộc tính CREATE TABLE DDH ( MADH SmallInt Constraint pkDDH Primary Key , NgayDH DateTime Default GetDate( ) Not Null , NgayYCGiao DateTime DEFAULT GetDate( ) Not Null , Constraint chkNgayYCGiao Check (NgayDH <= NgayYCGiao) , MaNCC SmallInt Not Null Constraint fk_DHH_NCC References NHACC(MaNCC) ) SQL Server24 1.4- Khai báo cột tự động điền giá trị phân biệt Có các cách sau: • Dùng kiểu Timestamp • Dùng thuộc tính IDENTITY [(seed, increment )] : điền tự động một số nguyên cho mẫu tin mới. Người dùng không thể nhập dữ liệu cho cột có thuộc tính này. Mỗi table chỉ có một cột khai báo thuộc tính Identity. • Dùng kiểu UniqueIdentifier với chỉ định giá trị Default từ hàm NEWID() hay hàm NEWSEQUENTIALID() để nhận giá trị phân biệt toàn cầu. 7SQL Server25 Ví dụ: Tạo cột có giá trị phân biệt CREATE TABLE NhaCungCap ( MaNCC int IDENTITY(1, 1) PRIMARY KEY, TenNCC char(30) NOT NULL ) CREATE TABLE KhachHang ( MaKH uniqueidentifier DEFAULT NEWID() PRIMARY KEY, TenKH char(30) NOT NULL ) SQL Server26 1.5- Tạo Table tạm • Table tạm lưu trữ bên trong CSDL TempDB • Table tạm cục bộ được khai báo với 1 dấu #, chỉ được truy xuất bởi kết nối vừa tạo ra nó. • Table tạm toàn cục được khai báo với 2 dấu #, được truy xuất bởi các kết nối hiện hành CREATE TABLE #BangTam ( Cot1 int PRIMARY KEY, Cot2 char(10) NOT NULL, Cot3 Datetime ) SQL Server27 2- Sử dụng Object Explorer • Click phải mục Tables trong CSDL và chọn New Table… SQL Server28 • Lần lượt khái báo các cột. Với mỗi cột, nhập tên cột, chọn kiểu dữ liệu và chỉ định các thuộc tính cần thiết. • Click nút Save, đặt tên table và click nút OK 8SQL Server29 IV- THAY ĐỔI CẤU TRÚC - RÀNG BUỘC • Hiệu chỉnh, thêm, xóa cột và ràng buột • Kiểm tra hay ngưng kiểm tra ràng buộc 1. Bằng Lệnh ALTER TABLE 2. Sử dụng Object Explorer SQL Server30 1- Sử dụng lệnh ALTER TABLE Cú pháp chung: ALTER TABLE table_name ALTER COLUMN col_name data_type[(p [, s])] [NULL | NOT NULL ] | ADD | DROP CONSTRAINT constraint_name | DROP COLUMN col_name [,...n] | {CHECK | NOCHECK} CONSTRAINT { ALL | constraint_name[,...n] } SQL Server31 1.1- Đổi kiểu dữ liệu hoặc kích thước của cột ALTER TABLE table_name ALTER COLUMN col_name data_type [ NULL | NOT NULL] Ví dụ:ALTER TABLE KhachHang ALTER COLUMN TenKH CHAR (40) NULL Chú ý: Không thể Giảm kích thước của cột nhỏ hơn kích thước dữ liệu hiện có Thay đổi kiểu dữ liệu của cột khi cột đó đã có dữ liệu hoặc có ràng buộc. Thay đổi kiểu của cột đã dùng trong cột tính toán Từ NULL thành NOT NULL SQL Server32 1.2- Thêm cột mới ALTER TABLE ADD [,...] CREATE TABLE NhanVien ( MaNV Int CONSTRAINT pk_NV Primary Key, TenNV VarChar(40) NOT NULL) GO ALTER TABLE NhanVien ADD NoiSinh Varchar(20) DEFAULT ‘Đồng Nai’, MaNVQL Int NULL Constraint fk_MaNV References NhanVien(MaNV) 9SQL Server33 Chú ý khi thêm cột NOT NULL Trong trường hợp Table đã có dữ liệu, nếu bạn cần thêm cột với ràng buộc NOT NULL thì có thể thực hiện một trong 2 cách sau: • Cách 1: thực hiện qua 3 bước: (i) thêm cột với thuộc tính NULL, (ii) Điền đầy đủ các giá trị cho cột, (iii) Đổi lại thuộc tính của cột thành NOT NULL. • Cách 2: Thêm cột mới có ràng buộc Default và thuộc tính WITH VALUES để điền tự động giá trị cho các dòng đang tồn tại trong bảng. ALTER TABLE SanPham ADD SoTon Float NOT NULL CONSTRAINT df_ST DEFAULT 0 WITH VALUES SQL Server34 3- Xóa cột : ALTER TABLE DROP COLUMN ColName [,...n] Ví dụ: ALTER TABLE SanPham DROP COLUMN SoTon Chú ý: Không thể xóa những cột có ràng buộc CHECK, DEFAULT, FOREIGN KEY, UNIQUE, PRIMARY KEY SQL Server35 4- Thêm RBTV cho bảng ALTER TABLE table_name [WITH CHECK | WITH NOCHECK] ADD [,...n] •WITH NOCHECK: Không kiểm tra ràng buộc đối với dữ liệu đã nhập •Table_constraint = PRIMARY KEY( column_name [ , ...n ] ) | UNIQUE (column_name [ , ...n ] ) | FOREIGN KEY (column_name [ , ...n ] ) REFERENCES ref_table ( ref_col [ ,...n ] ) [ON DELETE {CASCADE | NO ACTION | SET NULL | SET DEFAULT} ] [ON UPDATE {CASCADE | NO ACTION | SET NULL | SET DEFAULT} ] | DEFAULT FOR column_name [ WITH VALUES ] | CHECK( BT Điều kiện ) SQL Server36 Ví dụ: CREATE TABLE DDH ( MADH SmallInt Not Null, MaNCC SmallInt, NgayDH DateTime, NgayYCGiao DateTime ) Go ALTER TABLE DDH ADD Constraint pk_DDH Primary Key(MaDH) , Constraint df_NgayDH Default GetDate( ) For NgayDH , Constraint chk_NgayYCGiao Check (NgayDH <= NgayYCGiao) , Constraint fk_DDH_NhaCC Foreign Key(MaNCC) References NHACC 10 SQL Server37 5- Hủy bỏ RBTV khỏi bảng: ALTER TABLE table_name DROP CONSTRAINT Ví dụ: ALTER TABLE SanPham DROP CONSTRAINT df_SoTon SQL Server38 6- Kích hoạt hay tạm ngưng kiểm tra ràng buộc Foreign key và Check ALTER TABLE table_name {CHECK | NOCHECK} CONSTRAINT {ALL | tênRB [,...n]} Ví dụ : -- Tạm ngưng kiểm tra ràng buộc. ALTER TABLE SanPham NOCHECK CONSTRAINT chk_DonGia -- Kích hoạt kiểm tra constraint. ALTER TABLE SanPham CHECK CONSTRAINT chk_DonGia SQL Server39 7- Đổi tên cột : SP_RENAME 'TableName.OldName', 'NewName', 'COLUMN' Ví dụ: SP_RENAME ‘SanPham.DonGia', ‘DonGiaSP', 'COLUMN' SQL Server40 2- Sử dụng Object Explorer • Click phải vào tên bảng và chọn Modify Table… 11 SQL Server41 V- XEM THÔNG TIN TABLE: 1. Table Sys.Objects chứa danh sách các table, view và các ràng buộc trên các table trong CSDL hiện hành. – Thuộc tính Name chứa tên đối tượng – Thuộc tính Object_ID chứa giá trị nhận diện đối tượng. Có thể dùng thủ tục Object_ID('TênĐốitượng') để trả về giá trị Object_ID của đối tượng – Thuộc tính Type chứa mã loại đối tượng: U - User's table, S - System table; V - View; Tr - Trigger, D - Default, C - Check, F - Foreign Key, PK – Primary Key, Uq - Unique Key , P – Procedure, Fn – Function • Bạn có thể liệt kê danh sách các table đã tạo bằng lệnh: Select * From Sys.Objects Where Type = 'U‘ SQL Server42 IV- XEM THÔNG TIN TABLE: 2. Table Sys.Columns chứa danh sách các cột trong các table hay view; các tham số trong các thủ tục hay hàm của CSDL hiện hành – Thuộc tính Name chứa tên cột hay tên tham số. – Thuộc tính Object_ID chứa định danh đối tượng Table, View, thủ tục hay hàm. Dùng thủ tục Object_ID('object_name') để trả về giá trị Object_ID của các đối tượng này Ví dụ: Liệt kê thông tin các cột trong table 'HoaDon' Select * From sys.Columns Where Object_ID= Object_ID('HoaDon') SQL Server43 3- Sử dụng Hàm hoặc thủ tục: • SP_HELP [ ‘Tên’ ] : Xem thông tin các đối tượng trong CSDL, các kiểu dữ liệu. • Hàm Object_ID('TênĐốitượng'): trả về giá trị ID của đối tượng trong table SysObjects • Hàm ObjectProperty( Object_ID, 'IsUserTable'): trả về giá trị 1 nếu Object_ID là của table ngược lại bằng 0. Ví dụ: Liệt kê danh sách các Table đã tạo Select * From Sys.Objects Where ObjectProperty(Object_ID, 'IsUserTable’ ) = 1 • Hàm @@IDENTITY dùng nhận giá trị identity của mẫu tin được chèn cuối cùng. SQL Server44 V- XÓA TABLE : DROP TABLE [, …n] Chú ý: Không thể xóa Table cha được tham chiếu bởi Table khác. 12 SQL Server45 VI- ĐỔI TÊN BẢNG: EXEC SP_RENAME 'OldName', 'NewName' Ví dụ: Đổi tên table NCC thành NhaCungCap. EXEC sp_rename ‘NCC', ‘NhaCungCap' SQL Server46 VII- MÔ HÌNH QUAN HỆ • Biểu diễn mối quan hệ dữ liệu giữa các bảng • Khai báo, hiệu chỉnh các ràng buộc khóa ngoại • Tạo, sửa và xóa cấu trúc bảng SQL Server47 1- Tạo mới mô hình quan hệ B1: Click phải vào Database Diagram và chọn New Database Diagram B2: Chọn bảng cho mô hình B3: Click nút Close kết thúc quá trình tạo mô hình SQL Server48 L o a i H a n g M a LH T e n LH M o T a H in h M a t H a n g M a M H T e n M H M a N C C M a LH D VT D o n G ia S o T o n f k _ M a t H a n g _ Lo a iH a n g K h u V u c * M a K V T e n K V M a N VQ L K h u V u c L o a i H a n g M a K V M a LH F K _ K h u V u c Lo a iH a n g _ K h u V u c 1 F K _ K h u V u c Lo a iH a n g _ Lo a iH a n g 1 N h a n V i e n * M a N V H o T e n P h a i D ia C h i D ie n T h o a i Lu o n g C B C o n g V ie c M a K V F K _ N h a n V ie n _ K h u V u c N h a C u n g C a p M a N C C T e n N C C D ia C h i D ie n T h o a i f k _ M a t H a n g _ N h a C C F K _ K h u V u c _ N h a n V ie n 13 SQL Server49 2- Menu tắt của Mô hình quan hệ: Click phải vào màn hình trống của Diagram SQL Server50 3- Menu tắt của Table: Click phải trên Table SQL Server51 Bài tập • Thực hiện bài tập trong file TheThao.DOC SQL Server52 QUẢN LÝ MUA BÁN HÀNG HÓA 1. LOAIHANG( MaLH, TenLH, MoTa) 2. MATHANG( MaMH, TenMH, MaLH, DVT, DonGia, SoTon) 3. NHANVIEN(MaNV, HoTenNV, NgaySinh, GioiTinh) 4. HOADON( SoHD, NgayHD, MaNV, TienHD) 5. CTHD(SoHD, MaMH, DGBan, SL) 6. NHACUNGCAP( MaNCC, TenNCC, DiaChi, DienThoai ) 7. CUNGUNG(MaNCC, MaMH) 8. DDH(MaDH, NgayDH, NgayYCGiao, MaNCC, TIENDH) 9. CTDH(MaDH, MaMH, SLDat, DonGiaDat) 10. GIAOHANG(MaGH , NgayGH, MaDH) 11. CTGH(MaGH, MaMH, SLGiao) 12. TONKHO(THANG, NAM, MaMH,TonDauKy,NhapTrongKy,XuatTrongKy)