Hệ quản trị cơ sở dữ liệu sql server

HỆ QUẢN TRỊ CƠ SỞ DỮ LIỆUSQL SERVERNgôn ngữ định nghĩa dữ liệuTạo CSDLTạo và quản lý các bảngTạo lập CSDLCác đối tượng của CSDL:Các bảng dữ liệu (Tables)Các chỉ mục (Index)Các khung nhìn (Views)Các thủ tục lưu trú (Store Procedures)Các bẫy lỗi (Triggers)Tạo lập CSDLCác thông tin cần xác định khi tạo CSDL:Tên CSDLKích thướcTệp, nhóm tệp lưu trữ CSDLTạo lập CSDLTạo CSDL bằng T_SQLCREATE DATABASE [ ON     [ [ ,...n ] ]     [ , [ ,...n ] ] ] [ LOG ON { [ ,...n ] } ] ------- ::= [ PRIMARY ]( [ NAME = logical_file_name , ]     FILENAME = 'os_file_name'     [ , SIZE = size ]     [ , MAXSIZE = { max_size | UNLIMITED } ]     [ , FILEGROWTH = growth_increment ] ) [ ,...n ]-------- ::= FILEGROUP filegroup_name [ ,...n ]Tạo lập CSDLTạo CSDL bằng T_SQLDatabase_name: Duy nhất trong serverTuân theo luật đặt định danhĐộ dài lớn nhất là 128 kí tựON Xác định các tệp (primary file, secondary file) và nhóm tệp lưu trữ CSDLLOG ON Xác định các log fileNếu mệnh đề này không có thì một log file sẽ tụ động được tạo ra.Tạo lập CSDLTạo CSDL bằng T_SQLVí dụ 1: tạo CSDL có xác định tệp primary và tệp transaction log:USE master GO CREATE DATABASE qlnv ON ( NAME = qlnv_dat, FILENAME = 'c:\program files\microsoft sql server\mssql\data\qlnvdat.mdf', SIZE = 10, MAXSIZE = 50, FILEGROWTH = 5 ) LOG ON ( NAME = qlnv_log, FILENAME = 'c:\program files\microsoft sql server\mssql\data\qlnvlog.ldf', SIZE = 5MB, MAXSIZE = 25MB, FILEGROWTH = 5MB ) GO Tạo lập CSDLTạo CSDL bằng Enterprise Manager (EM)Chọn server group và mở rộngNhấp chuột phải trên nút Database/ chọn New Database => Xuất hiện cửa sổ New DatabaseNhập tên cho DB cần tạoThay đổi một số tham số của DBTạo lập CSDLChỉnh sửa DBALTER DATABASEVí dụ 2 USE master GO CREATE DATABASE Test1 ON ( NAME = Test1dat1, FILENAME = 'c:\Program Files\Microsoft SQL Server\MSSQL\Data\t1dat1 . ndf', SIZE = 5MB, MAXSIZE = 100MB, FILEGROWTH = 5MB ) GO ALTER DATABASE Test1 ADD FILE ( NAME = Test1dat2, FILENAME = 'c:\Program Files\Microsoft SQL Server\MSSQL\Data\t1dat2.ndf', SIZE = 5MB, MAXSIZE = 100MB, FILEGROWTH = 5MB ) GO Tạo lập CSDLXem thông tin về các CSDLsp_helpdb [ [ @dbname= ] 'name' ]Ví dụ 3exec sp_helpdb qlnv exec sp_helpdb Tạo lập CSDLXoá CSDLDROP DATABASE database_name [ , ]Ví dụ 4DROP DATABASE qlnv Kiểu dữ liệuSystem data types (Kiểu dữ liệu hệ thống)User-defined data type CLR typesSpatial dataFilestreams XMLKiểu dữ liệu hệ thốngExact numericbit, tinyint, smallint, int, bigint, numeric, decimal, smallmoney, moneyApproximate numeric float, RealDate & Timedatetime, smalldatetimeCharacter stringchar, varchar, textUnicode character string nchar, nvarchar, ntextBinary character string binary, varbinary, imageOthers sql_variant, timestamp, xml..***Tạo lập các bảng dữ liệuTạo bảng bằng T_SQL:CREATE TABLE [. [. ]] ( (kích thước 1),  (kích thước 2),.., (kích thước n)) ;Tạo lập các bảng dữ liệuTạo bảng bằng T_SQL- Ví dụ 5CREATE TABLE qlnv.nhan_vien (manv Smallint,ho_ten VARCHAR(25),ngay_sinh DATETIME,gioi_tinh CHAR(3),ma_dv CHAR(2),luong Int ); Tạo lập các bảng dữ liệuTạo bảng bằng EMChọn CSDLChọn mục TablesNhấp chuột phải trên Tables và chọn New TablesTạo lập các bảng dữ liệuSửa đổi cấu trúc bảng (Thêm, xoá, sửa cột):Tạo lập các bảng dữ liệuXem thông tin bảngsp_help [ [ @objname = ] name ]Xoá bảngDROP TABLE table_name Ngôn ngữ thao tác dữ liệuChèn thêm bản ghi vào bảng dữ liệuSửa nội dung trong bảng dữ liệuXóa các bản ghi trong bảng dữ liệuTìm kiếm thông tin Chèn thêm bản ghi vào bảng dữ liệu INSERT INTO [()] VALUES ;  Chèn thêm một bản ghi vào cuối bảng INSERT INTO [()] SELECT . . .  Chèn thêm một số bản ghi vào cuối bảngSửa nội dung trong bảng dữ liệu UPDATE SET =  [, = , . . . ][WHERE (s)] Cập nhật thông tin trên các cột col_name1, col_name2, . . . cho các bản ghi thỏa mãn điều kiện sau WhereXóa các bản ghi trong bảng dữ liệu DELETE FROM [WHERE (s)]Xóa tất cả các bản ghi thỏa mãn điều kiện sau mệnh đề WhereTRUNCATE TABLE  Xoá tất cả bản ghi trong bảng, lược đồ của bảng vẫn giữ nguyênTìm kiếm thông tinCSDL Quản lý thông tin về các nhân viênPHONG(Maphong, Tenphong, Diachi, SDT)DMNN(Mann, Tennn)NHANVIEN(Manv, Hoten, Ngaysinh, Gioitinh, Luong, Sdt, Maphong)TDNN(Manv, Mann, Tdo)Tìm kiếm thông tinTruy vấn tìm kiếm tổng quát: SELECT *|{[DISTINCT] column|expression [alias],...}FROM table(s)[WHERE condition(s)] [ORDER BY {column, expr, alias} [ASC|DESC]];Nghĩa là: Lấy Từ [Thoả mãn ] [Sắp xếp ]Tìm kiếm thông tinhoặc: SELECT column,group_function(column)FROM tables[WHERE condition][GROUP BY group_by_expression][HAVING group_condition][ORDER BY column];Tìm kiếm không điều kiện SELECT *|{[DISTINCT] column|expression [alias],...}FROM table(s) Ví dụ 6: Cho biết thông tin về các phòng ban? SELECT Maphong,Tenphong, DiaChi, Tel  FROM Phonghoặc: SELECT *  FROM Phong* : thay thế tập tên cột của một bảngTìm kiếm không điều kiệnVí dụ 7: Cho biết danh sách thưởng của các nhân viên bao gồm các thông tin mã nhân viên, họ tên, lương, và thưởng (bằng lương + 40% lương)?Câu lệnh: SELECT Manv, Hoten, Luong, 1.4*Luong as ThuongFROM NhanvienSau Select có thể chứa một biểu thức. Biểu thức và tên trường đó có thể được đặt tên giả: [As] hoặc Biểu thức chứa giá trị NULL  bt có giá trị NULLTìm kiếm không điều kiệnVí dụ 8: Cho biết mã các ngoại ngữ mà các nhân viên trong cơ quan đã học? Câu lệnh: Select Mann  From TDNN hoặc Select Distinct Mann From TDNNDistinct: đưa ra bản ghi đại diện của nhóm bản ghi giống nhauTìm kiếm không điều kiệnVí dụ 9: Đưa ra thông tin về 2 nhân viên đầu tiên trong bảng nhân viên? Câu lệnh: Select top 2*  From NHANVIENtop n: đưa ra bản ghi n bản ghi đầu tiênTìm kiếm có điều kiệnSELECT *|{[DISTINCT] column|expression [alias],...}FROM table(s)[WHERE condition(s)] Các phép so sánh trong Condition(s): >, =, , !> (không lớn hơn), !=2250000 Truy vấn nhóm dữ liệuChú ý: Tất cả các cột hoặc biểu thức sau SELECT nếu không nằm trong hàm mhóm dữ liệu đều phải xuất hiện sau GROUP BYCác cột hoặc biểu thức xuất hiện sau GROUP BY không nhất thiết phải xuất hiện sau SELECTCác điều kiện nhóm dữ liệu phải đặt sau HAVING (không đặt sau WHERE)Truy vấn có liên kết Các loại truy vấn liên kết:Liên kết trong (inner join)Liên kết ngoài (outer join)Liên kết chéo (cross join)Tự liên kết (self_join)Truy vấn có liên kết Truy vấn liên kết trong (inner join): SELECT *|{[DISTINCT] column|expression [alias],...}FROM table1, table2WHERE table1.col1 table2.col2  [and condition(s)][ORDER BY column asc|desc, . . .]; Hoặc: Truy vấn có liên kết SELECT *|{[DISTINCT] column|expression [alias],...}FROM table1INNER JOIN table2ON table1.col1 table2.col2[WHERE condition(s)][ORDER BY column asc|desc, . . .];  Trả ra các bản ghi của hai bảng mà giá trị của col1 liên kết được với ít nhất một giá trị ở col2Truy vấn có liên kết Ví dụ 22: Với mỗi nhân viên, cho biết mã nhân viên, họ tên, mã phòng và tên phòng họ làm việc? Select manv, hoten, nhanvien.maphong, tenphong From Phong, nhanvien Where phong.maphong=nhanvien.maphong Hoặc: Select manv, hoten, nv.maphong, tenphong From nhanvien nvInner join phong p On nv.maphong=p.maphongTruy vấn có liên kết Truy vấn có liên kết Ví dụ 23: Với mỗi nhân viên (mã nhân viên, họ tên) cho biết các ngoại ngữ mà nhân viên đó học (tên ngoại ngữ, trình độ)? Select manv, hoten, tennn,tdo From nhanvien nv, tdnn td, dmnn dm Where nv.manv =td.manv and dm.mann = td.mannHoặc: Select manv, hoten, tennn,tdo From nhanvien nv Inner join tdnn td On nv.manv =td.manv Inner join dmnn dm On dm.mann = td.mannTruy vấn có liên kết Truy vấn liên kết ngoài: SELECT *|{[DISTINCT] column|expression [alias],...}FROM table1LEFT| RIGHT| FULL OUTER JOIN table2ON table1.col1 table2.col2[WHERE condition(s)][ORDER BY column(s)]; Truy vấn có liên kết Ví dụ 24: Đưa ra danh sách tất cả các nhân viên cùng với thông tin về phòng ban của họ (kể cả các nhân viên chưa ở phòng nào)? SELECT *FROM Nhanvien nv LEFT OUTER JOIN phong p ON nv.maphong = p.maphong Trả ra tất cả các bản ghi của nhanvien và chỉ các bản ghi trong phong liên kết với ít nhất một bản ghi bên bảng nhanvien.Truy vấn có liên kết Ví dụ 25: Đưa ra danh sách tất cả các phòng cùng với thông tin về các nhân viên của các phòng (kể cả các phòng chưa có nhân viên nào)? SELECT *FROM nhanvien nv RIGHT OUTER JOIN phong p ON nv.maphong = p.maphong Trả ra tất cả các bản ghi của phong và chỉ các bản ghi so khớp trong nhanvien.Truy vấn có liên kết Ví dụ 26: SELECT *FROM nhanvien nv FULL OUTER JOIN phong p ON nv.maphong = p.maphong Kết hợp của right và left outer joinTruy vấn có liên kết Truy vấn liên kết chéo (cross join): SELECT *|{[DISTINCT] column|expression [alias],...}FROM table1CROSS JOIN table2 [WHERE condition(s)][ORDER BY column(s)];  Trả ra tích đề các của hai bảngTruy vấn có liên kết Truy vấn tự liên kết (self_join): là truy vấn liên kết mà một bảng lại liên kết với chính nóVí dụ 27: Dua ra nhan vien co luong lon hon luong mot nhan vien nao do cung phong Select  from inner join on  andRÀNG BUỘC DỮ LIỆU (CONSTRAINS)Các thao tác làm biến đổi dữ liệu của bảng: cập nhật, thêm bản ghi, xóa bản ghi.Dữ liệu của một số bảng khi có những thay đổi phải tuân theo một số quy luật nào đó nhằm đảm bảo dữ liệu đang tồn tại trong CSDL luôn nhất quán. Các quy luật đó được gọi là các ràng buộc dữ liệu.Mỗi khi có một thao tác nào đó xảy ra làm thay đổi dữ liệu thì tất cả các ràng buộc sẽ được tự động được kiểm tra lại. Nếu dữ liệu mới không thỏa mãn các ràng buộc thì SQL server sẽ trả về một thông báo lỗi.RÀNG BUỘC DỮ LIỆU (CONSTRAINS)Các loại ràng buộcRàng buộc khai báo:ràng buộc cột (miền giá trị): xác định miền giá trị mà thuộc tính sẽ nhận được.ràng buộc về tính toàn vẹn của thực thể: chỉ rõ khóa của bảngràng buộc toàn vẹn tham chiếu: liên quan đến việc quản lý các mối quan hệ giữa các bản ghi của hai bảng (mối quan hệ giữa khóa chính và khóa phụ).Ràng buộc phức tạp: sự thay đổi DL của một bảng  sự thay đổi DL của một loạt các bảng khác mà phải thực hiện theo một giải thuật (trigger, store procedure).RÀNG BUỘC DỮ LIỆU (CONSTRAINS)a. Ràng buộc miền giá trịNot nullNếu trong khai báo thuộc tính có mệnh đề Not Null thì bắt buộc mỗi bản ghi tại thuộc tính đó phải có chứa giá trị (không được để trống).Mặc định khi khai báo cột là Null.Default : đặt giá trị mặc định cho thuộc tínhĐược dùng trong trường hợp thêm mới bản ghi.Không liên quan đến hành động nhập hay xóa.Nếu giá trị đưa vào khác Null thì giá trị mặc định bị thay thế, ngược lại thì giá trị mặc định không thay đổi.RÀNG BUỘC DỮ LIỆU (CONSTRAINS)Các cột dữ liệu tham gia vào quá trình tính toán nên cho giá trị mặc định.Các thuộc tính không chấp nhận giá trị Null nên đặt giá trị mặc định.Unique: Các giá trị trong cột phải duy nhất.Cho phép xác định khóa dự tuyển cho bảng.Primary key: thiết đặt khóa chính cho bảngCheck (): giới hạn tập giá trị nhận được của một thuộc tính.RÀNG BUỘC DỮ LIỆU (CONSTRAINS)Ví dụ: CREATE TABLE giang_vien(Magv char(5),Ho_ten varchar(35) constrains NN_HT_GV NOT NULL,Ngay_sinh datetime,Gioi_tinh varchar(3) DEFAULT ‘Nam’,Ma_khoa char(2) REFERENCES KHOA(ma_khoa),Luong bigint check(luong>=1000000),Trinh_do varchar(15) NOT NULLCHECK(trinh_do IN (‘Đại học’, ‘Thạc sĩ’, “Tiến sĩ”))Constrains PK_GV primary key(Magv, hoten))RÀNG BUỘC DỮ LIỆU (CONSTRAINS)Thêm ràng buộc vào bảng đã tồn tại: ALTER TABLE ADD CONSTRAINT Ví dụ:ALTER TABLE giang_vienADD CONSTRAINT PK PRIMARY KEY(Magv)RÀNG BUỘC DỮ LIỆU (CONSTRAINS)Loại bỏ ràng buộc trong bảng: ALTER TABLE DROP CONSTRAINT Ví dụ:ALTER TABLE giang_vienDROP CONSTRAINT PK RÀNG BUỘC DỮ LIỆU (CONSTRAINS)b. Ràng buộc toàn vẹn tham chiếu: dùng để thiết lập mối quan hệ giữa hai bảng[CONSTRAINT ][FOREIGN KEY ]REFERENCES [()][ON UPDATE {CASCADE| NO ACTION}][ON DELETE {CASCADE| NO ACTION}]CASCADE: các bản ghi trong bảng tham chiếu được xóa hoặc được cập nhật theo bản ghi ở bảng được tham chiếu. NO ACTION: khóa chính bảo toàn.RÀNG BUỘC DỮ LIỆU (CONSTRAINS)Ví dụ:CREATE TABLE giang_vien(Magv char(5) PRIMARY KEY,Ho_ten varchar(35) NOT NULL,Ngay_sinh datetime,Gioi_tinh vachar(3) DEFAULT ‘Nam’,Ma_khoa char(2) REFERENCES KHOA(ma_khoa),Luong bigint,Trinh_do varchar(15) NOT NULLCHECK(trinh_do IN (“Đại học”, “Thạc sĩ”, “Tiến sĩ”)))VII. THỦ TỤC THƯỜNG TRÚ (STORE PROCEDURE)Khai báo biến trong SQL serverCú phápDECLARE @ [, @ , . . .]Ví dụ:DECLARE @x1 char(4)DECLARE @x2 int, @a floatDECLARE @Tong_gia_tri numeric; Gán giá trị cho biến:SET = Ví dụ:SET @x1 =‘CNTT’;SET @a = 200;SET @Tong_gia_tri = (SELECT sum(soluong*dongia)/1000000 from CPP);VII. THỦ TỤC THƯỜNG TRÚ (STORE PROCEDURE)In giá trị biến:Print(‘Tổng giá trị các chuyến hàng:’ + str(@Tong_gia_tri)); Chú ý: các biến khai báo trong cùng một bó chỉ có tác dụng trong bó đó.VII. THỦ TỤC THƯỜNG TRÚ (STORE PROCEDURE)SP được xây dựng từ các câu lệnh T-SQL và được lưu trú trên SQL server.Muốn thực hiện một SP, NSD chỉ cần thực hiện một lời gọi hàm.Khi SP được chạy lần đầu tiên nó sẽ được biên dịch qua 5 bước và sinh ra một mô hình truy vấn. Mô hình này sẽ được đặt trong một CSDL của SQL server, lần sau chạy lại thủ tục sẽ không phải dịch lại nữa.VII. THỦ TỤC THƯỜNG TRÚ (STORE PROCEDURE)Năm bước biên dịch thủ tục:Thủ tục được phân tích ra thành nhiều phần Kiểm tra sự tồn tại của các đối tượng (view, table, ) mà thủ tục tham chiếu tới.Lưu trữ tên thủ tục vào bảng sysobject, lưu trữ các mã lệnh của thủ tục vào bảng syscomments.Sinh ra mô hình truy vấn của thủ tục và lưu vào bảng sysprocedureKhi SP được chạy lần đầu tiên, cây truy vấn sẽ được đọc và được tối ưu thành một kế hoạch thủ tục và chạy  tiết kiệm thời gian tái phân tích, biên dịch cây truy vấn mỗi khi chạy thủ tục.VII. THỦ TỤC THƯỜNG TRÚ (STORE PROCEDURE)Trong một phiên làm việc, nếu SP được thực hiện, nó sẽ được lưu trữ vào vùng nhớ đệm. Những lần sau nếu SP được gọi thực hiện lại thì nó sẽ được đọc trực tiếp ra từ vùng nhớ đệm  nâng cao hiệu suất chạy truy vấn.VII. THỦ TỤC THƯỜNG TRÚ (STORE PROCEDURE)Cú pháp lệnh tạo SP:CREATE PROC[EDURE] [@ [= ] [OUTPUT], . . .][WITH RECCOMPILE|ENCRYPTION|RECOMPILE, ENCRYPTION]AS Giải thích:=: gán giá trị ban đầu cho tham sốOUTPUT: giá trị thay đổi của tham số ở trong thủ tục được đưa ra bên ngoài thủ tục.Ví dụVII. THỦ TỤC THƯỜNG TRÚ (STORE PROCEDURE)Thực hiện thủ tục: EXEC Thay đổi thủ tụcALTER PRO[CEDURE] . . .Xóa thủ tụcDROP PRO[CEDURE] Bó thủ tụcVII. THỦ TỤC THƯỜNG TRÚ (STORE PROCEDURE)Sử dụng tùy chọn RECOMPILE trong câu lệnh CREATE PRO: Thủ tục sẽ được biên dịch lại từ đầu mỗi khi thực hiệnKhi biên dịch lại các thông tin mới của SP được cập nhật và tối ưu lại.Sử dụng tùy chọn RECOMPILE trong câu lệnh EXECThủ tục sẽ được biên dịch lại trong lần thực thi đó và lưu lại kế hoạch thực thi mới của nó vào vùng nhớ đệm.Biên dịch lại tất cả các thủ tục của bảng:EXEC sp_recompile VIII. HÀM CỦA NSD(USER DEFINED FUNCTIONS-UDFs)UDFs giống như SP nhưng khác ở các điểm sau:UDFSP- Giá trị các tham số không được truyền ra ngoài.- Trả ra một giá trị bằng mệnh đề RETURN- Có thể trả về một giá trị vô hướng hoặc một bảng dữ liệu.- Có thể đưa giá trị của tham số ra ngoài bằng thuộc tính OUTPUT - Chỉ trả về kiểu DL giá trị kiểu nguyênVIII. HÀM CỦA NSD(USER DEFINED FUNCTIONS-UDFs)Cú pháp câu lệnh:CREATE FUNCTION ([@ [= ] [, . . .]])RETURNS |[WITH ENCRYPTION]AS BEGIN [] RETURN | ()