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

HỆ QUẢN TRỊ CƠ SỞ DỮ LIỆU SQL SERVER 2005 Phan Hiền GIỚI THIỆU HQTCSDL  Tổng quát  Chức năng chính  Tạo lập & quản lý dữ liệu.  Lập cáo loại báo cáo & tổng hợp dự liệu.  Quản lý quyền hạn sử dụng.  Tương tác các hệ quản lý khác.  Hệ quản trị SQL Server 2005 HỆ QUẢN TRỊ SQL SERVER 2005  Tổng quát Sự kết hợp nhiều thành phần khác nhau với mục đích chung là quản lý dữ liệu cho thật hiệu quả.  Cài đặt HỆ QUẢN TRỊ SQL SERVER 2005  Thành phần chính  Database Engine (Quản lý & tạo lập & khai thác dử liệu)  Analysis Services (Cung cấp giải pháp phân tích và khai mỏ dữ liệu)  Replication (Cung cấp giải pháp tạo lập bản sao & đồng bộ cho dữ liệu phân tán)  Integration Services (Dịch vụ chuyển đổi sang các kiểu dữ liệu khác)  Report (Báo cáo)  Full-Text Search (Tìm kiếm chuổi)  Service Broker (Trung gian truyền thông điệp)  Notification services (Trung gian nhắc thông tin) DATABASE ENGINE  Quản lý dữ liệu  Tạo lập và quản lý dữ liệu theo mô hình Quan Hệ  Khai thác dữ liệu (Dùng ngôn ngữ truy vấn) - SQL (Structure Query Language) - Ngôn ngữ lập trình của SQL Server  Quản lý quyền hạn sử dụng  Một số chức năng khác như tìm kiếm, NGÔN NGỮ SQL Một số quy ước ký hiệu Từ khoá : TUKHOA Mệnh đề : Tên riêng : ten_rieng Hoặc : | Lặp lại nhiều lần : [,..n] Mô tả tập hợp {a|b|c} : tồn tại ít nhất 1 trong 3 thành phần a,b,c. [a|b|c] : tồn tại ít nhất 0 trong 3 thành phần a,b,c. NGÔN NGỮ SQL Cú pháp tạo lập dữ liệu : CREATE DATABASE ten_database USE ten_database Cú pháp tạo lập bảng : CREATE TABLE ten_bang ( { | } [,.. n] ) -------------------- dinh_nghia_cot ::= {ten_cot kieu_dl} [DEFAULT giatri_hang] [IDENTITY [(hat_giong , muc_tang)]] [] NGÔN NGỮ SQL [] ::= [CONSTRAINT ten_rang_buoc] { {NULL | NOT NULL} | CHECK (ten_cot {=|>|<|!=|≥|≤} gia_tri)| {PRIMARY KEY | UNIQUE} | REFERENCES ten_bang_quan_he (ten_cot_quan_he [,..n]) [ON {DELETE|UPDATE} CASCADE ] } NGÔN NGỮ SQL [] ::= [CONSTRAINT ten_rang_buoc] { CHECK (bieu_thuc_tren_cot {=|>|<|!=|≥|≤} gia_tri) | {PRIMARY KEY | UNIQUE} (ten_cot [,..n]) | FOREIGN KEY (ten_cot [,..n]) RREFERENCES ten_bang_quan_he (ten_cot_quan_he [,..n]) [ON {DELETE|UPDATE} CASCADE ] } NGÔN NGỮ SQL Cú pháp thay đổi cấu trúc bảng : ALTER TABLE ten_bang { ALTER COLUMN ten_cot { DROP DEFAULT | SET DEFAULT gia_tri_hang | IDENTITY (hat_giong , muc_tang) } | ADD { | } [,..n] | DROP {CONSTRAINT ten_rb | COLUMN ten_cot} } NGÔN NGỮ SQL Cú pháp xoá bảng : DROP TABLE ten_bang Luật trên dữ liệu : CREATE RULE ten_luat AS @VALUE { IN | LIKE | = | > | < | != | ≥ | ≤ } {gia_tri , tap_hop} Áp dụng luật : EXEC sp_bindrule ‘ten_luat’,‘ten_bang.ten_cot’ [furtureonly] EXEC sp_unbindrule ‘ten_bang.ten_cot’ NGÔN NGỮ SQL - SELECT Cú pháp : SELECT [[ALL | DISTINCT]|[TOP [PERCENT] n]] FROM WHERE ORDER BY { ten_cot [,..n] | thu_tu_cot [,..n] } ------------------ ::= {{ [ten_bang. | bi_danh.]ten_cot | bieu_thuc_tinh_toan_tren_cot } [[AS] ten_cot_moi] } [,..n] NGÔN NGỮ SQL - SELECT ::= { [AS bi_danh] [,..n] | } ::= ( [AS bi_danh] {INNER | RIGHT | LEFT} JOIN [AS bi_danh] ON dieu_kien ) [AS bi_danh] | ( { | [AS bi_danh]} {INNER | RIGHT | LEFT} JOIN { | [AS bi_danh]} ON dieu_kien ) [AS bi_danh] ::= {ten_bang , SELECT } NGÔN NGỮ SQL - SELECT ::= [NOT] {gia_tri | ten_cot | bieu_thuc} { { = | > | < | != | ≥ | ≤ } {gia_tri | ten_cot | bieu_thuc} | [NOT] IN ( { gia_tri [,..n] | SELECT 1_cot } ) | [NOT] BETWEEN {gia_tri | ten_cot | bieu_thuc} AND {gia_tri | ten_cot | bieu_thuc} | [NOT] LIKE chuoi_co_ky_tu_dai_dien } NGÔN NGỮ SQL - SELECT Chuổi ký tự đại diện : % : Bất cứ ký tự gì của hơn 0 ký tự _ : Bất cứ ký tự gì của 1 ký tự [] : Một giá trị đơn trong đoạn giá trị hay tập giá trị. Ví dụ : [a-b] hay [abef] [^] : Một giá trị đơn không thuộc vào trong đoạn giá trị hay tập giá trị. Ví dụ : [^a-b] hay [^abef] NGÔN NGỮ SQL - SELECT SOME | ANY & ALL {gia_tri | ten_cot | bieu_thuc} {= | > | < | != | ≥ | ≤} {SOME | ANY | ALL} SELECT 1_cot Hàm thông dụng : EXISTS (SELECT ) : Trả về true/false nếu select có kết quả NGÔN NGỮ SQL - SELECT GROUP Cú pháp : SELECT [ALL | DISTINCT] FROM WHERE GROUP BY HAVING NGÔN NGỮ SQL - SELECT GROUP ::= {[ten_bang. | bi_danh.] {ten_cot_gom_nhom | bieu_thuc_tren_cot} }[,..n] ::= , {tinh_toan_tong_hop_tren_cot} [,..n] SQL 2005- BẢNG TẠM with tscv as ( select manv , count(*) ts from phancong group by manv ) select manv , tennv (select ts from tscv where manv=nv.manv) / (select sum(ts) from tscv) AS xxx from nhanvien nv select manv , count(*)/(select sum(ts) from tscv) from phancong group by manv SQL 2005- BẢNG TẠM – ĐỆ QUY create table quanhe_nhanvien (manv char(5) primary key,tennv char(20), manvct char(5)) ------------------------------- manv tennv manvct Nv001 A null Nv002 B null Nv003 C Nv001 Nv004 D Nv001 Nv005 E Nv003 Nv006 F Nv005 SQL 2005- BẢNG TẠM – ĐỆ QUY with truy (manv,tennv,manvct,mucdo,cay,cayten) as ( select a.manv,a.tennv,a.manvct,1 as mucdo, cast('' as varchar(max)) as cay, cast('' as varchar(max)) as cayten from quanhe_nhanvien a where a.manvct is null union all select b.manv,b.tennv,b.manvct,mucdo + 1 as mucdo, (truy.cay + '\' + b.manvct) as cay, cayten + '\' + cast(truy.tennv as varchar(20)) as cayten from quanhe_nhanvien b inner join truy on truy.manv = b.manvct ) select * from truy SQL 2005- BẢNG TẠM – ĐỆ QUY with truy (manv,tennv,manvct,kq) as ( select a.manv,a.tennv,a.manvct, cast('' as varchar(max)) as kq from quanhe_nhanvien a where a.manvct is null union all select b.manv,b.tennv,b.manvct, (truy.kq + '\' + b.manvct) as kq from quanhe_nhanvien b inner join truy on truy.manv = b.manvct ) select * from truy SQL 2005- BẢNG TẠM – ĐỆ QUY Manv Tennv Manvct Mucdo cay cayten Nv001 A null 1 Nv002 B Null 1 Nv003 C Nv001 2 \Nv001 \A Nv004 D Nv001 2 \Nv001 \A Nv005 E Nv003 3 \Nv001\Nv003 \A\C Nv006 F Nv005 4 \Nv001\Nv003\Nv005 \A\C\E SQL 2005- BẢNG TẠM – ĐỆ QUY with truy (manv,tennv,manvct,mucdo,cay,cayten) as ( select a.manv,a.tennv,a.manvct,1 as mucdo, cast('' as varchar(max)) as cay, cast('' as varchar(max)) as cayten from quanhe_nhanvien a where a.manvct = a.manv union all select b.manv,b.tennv,b.manvct,mucdo + 1 as mucdo, (truy.cay + '\' + b.manvct) as cay, cayten + '\' + cast(truy.tennv as varchar(20)) as cayten from quanhe_nhanvien b inner join truy on truy.manv = b.manvct where b.manv != b.manvct ) select * from truy SQL 2005 - PIVOT create table baocaods ( nam int, mahang char(5), sl int, daily char(5), constraint kc_baocaods primary key (nam,mahang,daily) ) (1998,'G1',3,'D1') (1999,'G1',4,'D1') (1997,'G1',2,'D1') (1998,'G2',9,'D1') (1999,'G2',4,'D1') (1997,'G2',6,'D1') (1998,'G1',32,'D2') (1999,'G1',42,'D2') (1997,'G1',22,'D2') (1998,'G2',30,'D2') (1999,'G2',40,'D2') (1997,'G2',20,'D2') SQL 2005- PIVOT select mahang,[1997],[1998],[1999] from (select mahang,sl,nam from baocaods) as a pivot (sum(sl) for nam in [1997],[1998],[1999])) as b -------------------------- [1997],[1998],[1999] : là các giá trị mahang,sl,nam : chỉ gồm đúng 3 cột gồm Header row,Header column, Value. Mệnh đề From và Pivot phải đặt lại tên mới. -------------------------- Mahang 1997 1998 1999 G1 24 35 46 G2 26 39 44 SQL 2005- EXCEPT - INTERSECT create table congviec (macv char(5) primary key,tencv char(20)) create table phancong (manv char(5), macv char(5),constraint pk_phancong primary key (manv,macv)) create table nhanvien (manv char(5) primary key, tennv char(20)) --------------------------- manv macv manv macv nv001 cv001 nv002 cv001 nv001 cv002 nv002 cv002 nv002 cv003 SQL 2005- EXCEPT - INTERSECT EXCEPT : Lấy ra manv mà chỉ có làm công việc cv001 mà không làm công việc cv003. Select manv from phancong where macv = ‘cv001’ Except Select manv from phancong where macv = ‘cv003’  nv001 INTERSECT : Lấy ra manv mà làm cả 2 công việc ‘cv001’ & ‘cv003’ Select manv from phancong where macv = ‘cv001’ Intersect Select manv from phancong where macv = ‘cv003’  nv002 SQL 2005- CROSS – OUTER APPLY select * From nhanvien as nv cross apply ( select count(*) as ts from phancong as pc where pc.manv = nv.manv ) as tongsoviec Làm lại truy vấn count cho từng manv (có thể thay câu count thành hàm tính toán cho ra Table) Select * from nhanvien as nv inner join (select manv,count(*) ts from phancong group by manv) as tscv On tscv.manv = nv.manv SQL 2005- UPDATE – DELETE TOP UPDATE TOP 2 FROM NHANVIEN SET TENNV = TENNV + ‘A’ DELETE TOP 2 NHANVIEN INSERT TOP 2 INTO bang1 SELECT (TOP 2 : Là lấy 2 dòng đầu tiên trong dữ liệu insert vào)