Tài liệu Cơ sở dữ liệu sql

ANHMATTROI – TIN5A Cơ sở dữ liệu T-SQL 1. Các kiểu dữ liệu - Các kiểu dữ liệu thường dùng: + Kiểu số: Số nguyên: tinyint(8bit), smallint(16bit), int(32bit), bigint(64bit) Số thực: float(32-64bit), real(32bit), decimal(5-17B), numberic(5-17B), money(8B) + Kiểu chữ: char(n), varchar(n), nchar(n), nvarchar(n), text với n là số kí tự. Kiểu nchar(n) và nvarchar(n) biểu diễn các kí tự mã Unicode. Kiểu char(n), varchar(n) biểu diễn các kí tự mã Ascii Char(n): 1 <= n <= 8000 mỗi kí tự cần 8bit để lưu trữ Nchar(n): 1 <= n <= 4000 mỗi kí tự cần 16bit để lưu trữ Varchar(n): 1 <= n <= 8000 mỗi kí tự cần 8bit để lưu trữ + Ngày giờ: smalldatetime, datetime(8B), date(3B), time(3-5B) + Chuỗi bit: binary, varbinary, image + Kiểu logic: bit những số khác 0 là 1 còn 0 vẫ là 0, chỉ có 3 giá trị 0, 1, null + Kiểu dữ liệu bảng: table - Các kiểu dữ liệu khi biểu diễn cần đặt giữa hai dấu nháy đơn: char, nchar, text, datetime, nchar, nvarvhar. - Chuyển kiểu dữ liệu: dùng cast( as ) hoặc dùng convert(, ) Ví dụ: print 17/9 print 17/cast(9 as float) print 17/9 print 17/convert(float, 9) - Hiển thị ra màn hình: trong sql có hai tab hiện thị 1 ANHMATTROI – TIN5A + Tab results: hiện thị kết quả của câu lệnh truy vấn VD: declare @x int = 9 select @x as N'Giá trị của x là' + Tab messages: hiển thị chuỗi kí tự hoặc số bằng câu lệnh print , chuỗi kí tự đặt giữa hai dấu nháy đơn ‘<đặt chuỗi kí tự ở đây>’. Nếu chuỗi kí tự có mã unicode thì thêm kí tự N đằng trước hai dấu nháy đơn N’ … ‘ VD: print 'sql server' print N'Cơ sở dữ liệu' print 6.78 print 5+6 - Chú thích: dòng chú thích không được biên dịch, nó chỉ để cho dễ hiểu đoạn chương trình này ta đang làm gì. Có hai cách chú thích: + C1: dùng hai dấu gạch ngang viết liền -- cách này chỉ có tác dụng trên một dòng + C2: dùng /* đây là dòng chú thích */ cách này có tác dụng trên nhiều dòng - Trong sql không phân biệt chữ in hoa hay chữ thường 2. Các phép toán - Các phép tính + Cộng hai số hoặc nối hai chuỗi - Trừ hai số * Nhân hai số / Chia hai số, nếu cả hai số có kiểu số nguyên thì kết quả là chia lấy phần nguyên % Chia lấy phần dư: điều kiện hai số có kiểu nguyên 2 ANHMATTROI – TIN5A Ví dụ: print N'Cơ sở dữ liệu' + 'T-SQL' print 2+3 print 8-13 print 3%2 print 4*5 print 8/5 print 8.0/5 - Các phép logic and or 1 1 1 1 1 0 0 1 0 0 0 0 0 1 0 1 not - 0 1 1 0 Các phép so sánh = Phép so sánh bằng > Lớn hơn < Nhỏ hơn <> Khác != khác >= Lớn hơn hặc bằng 3 ANHMATTROI – TIN5A <= Nhỏ hơn hoặc bằng !< Không nhỏ hơn !> Không lớn hơn 3. Các lệnh làm việc với cơ sở dữ liệu và bảng a. Cơ sở dữ liệu - Tạo cơ sở dữ liệu: use master create database on ( name = '', filename = '<đường dẫn tới thư lưu file\.mdf', sixe = , maxsize = , filegrowth = <độ phình> ) log on ( name = '', filename = '<đường dẫn tới thư mục lưu file\.ldf', size = , maxsize = , filegrowth = <độ phình> ) + Tên cơ sở dữ liệu không có dấu cách, không chứa các kí tự đặc biệt, không bắt đầu bằng kí tự số,... + Độ phình: khi kích thước của file lớn hơn kích thước mặc định thì size = size + độ phình(đơn vị là mb hoặc %) sao cho không lớn hơn maxsize Ví dụ: tạo cơ sở dữ liệu quản lí sinh viên use master create database QLSV on ( name = 'Quanlysinhvien', filename = 'C:\Bai tap\Quanlysinvien.mdf', size = 10mb, 4 ANHMATTROI – TIN5A maxsize = 30mb, filegrowth = 5% ) log on ( name = 'Quanlysinhvien_log', filename = 'C:\Bai tap\Quanlysinvien_log.ldf', size = 5mb, maxsize = 20mb, filegrowth = 5% ) - Xóa cơ sở dữ liệu: drop database Ví dụ: xóa cơ sở dữ liệu quản lí sinh viên use master drop database QLSV - Đổi tên cơ sở dữ liệu: alter database modify name = Ví dụ: đổi tên cơ sở dữ liệu quản lí sinh viên use QLSV alter database QLSV modify name = QuanLiSinhVien - Đổi kích thước: alter database modify size = - Thay đổi thuộc tính của cơ sở dữ liệu: exec sp_dbop , 'read only' , 'true/false' b. Bảng - Tạo bảng: use create table 5 ANHMATTROI – TIN5A ( not null (có hoặc không mặc định là null), ................................................. ) + Tên cột không chứa các kí tự đặc biệt, không bắt đầu bằng kí tự số, không có dấu cách,… + Nếu bạn đặt cột có thuộc tính not null lúc chèn dữ - nhập dữ liệu vào bảng bạn phải điền dữ liệu cho cột này không được không được bỏ qua hay để trống Ví dụ: tạo bảng sinh viên trong cơ sở dữ liệu quản lí sinh viên có các cột sau: mã sinh viên có kiểu char, họ và tên sinh viên kiểu nvarchar, ngày sinh kiểu datetime, giới tính kiểu varchar, quê quán kiểu nvarchar. use QLSV create table SinhVien ( MaSV char(11) not null, TenSV nvarchar(20), NgaySinh datetime, GioiTinh nvarchar(4), QueQuan nvarchar(30) ) - Tạo bảng có các thuộc tính như: khóa chính, khóa ngoại, có điều kiện cho cột: use create table ( constraint primary key, , constraint foreign key references () ............................. ) Ví dụ: tạo bảng sinh viên có cột mã sinh viên là khóa chính, tên sinh viên trong cơ sở dữ liệu quản lí sinh viên use QLSV 6 ANHMATTROI – TIN5A create table SinhVien ( MaSV char(10) not null constraint KhoaChinh primary key, TenSV nvarchar(20) ) Ví dụ: tạo bảng lớp học có cột mã lớp là khóa chính, tên lớp và cột mã sinh viên là khóa ngoại tham chiếu đến mã sinh viên ở bảng sinh viên trong cơ sở dữ liệu quản lí sinh viên use QLSV create table LopHoc ( MaLop char(10) not null primary key, TenLop varchar(5), MaSV char(10) not null constraint KhoaNgoai foreign key references SinhVien(MaSV) ) + Các ràng buộc khóa chính và khóa ngoài nếu không có từ khóa constraint tên ràng buộc khóa chính, khóa ngoại sẽ do trình biên dịch đặt tên. + Cột có ràng buộc khóa ngoại khi tham chiếu đến cột của bảng khác thì hai cột này phải cùng kiểu dữ liệu, cùng kích thước. + Nếu trong bảng có từ hai khóa chính trở lên ta có thể dùng constraint primary key () các cột này chỉ khai báo có tên cột và kiểu dữ liệu ở trên dòng này. Mỗi bảng chỉ có một tên khóa chính. + Nếu trong bảng có một cột là khóa chính thì giá trị ở cột này không có giá trị nào bị trùng nhau. Nếu có từ hai khóa chính thì giá trị ở mỗi hàng phải khác nhau không được trùng với hàng nào. + Tạo bảng có thêm kiểm tra dữ liệu cho cột bằng từ khóa check: constraint check( ,<,>=,…> ) + Đặt giá trị mặc định cho cột: default Khi thành lập giá trị mặc định cho cột lúc chèn dữ liệu vào bảng nếu không chèn dữ liệu cho cột này thì trình biên dịch tự động gán giá trị mặc định cho cột đó + Tự động tăng giá trị cho cột có kiểu dữ liệu số dùng từ khóa identity(a,b): 7 ANHMATTROI – TIN5A identity(a,b) Với a là giá trị đầu tiên của cột, b là sau mỗi lần chèn một hàng thành công vào bảng đó a sẽ bằng: a = a + b. Lúc chèn dữ liệu vào bảng ta không chèn dữ liệu cho cột này nữa VD: tạo bảng sinh viên (*) use QLSV create table SinhVien ( MaSV int not null identity(100000,1), TenSV nvarchar(20), NgaySinh datetime not null, Diem float check (Diem>=0 or Diem <=10), GioiTinh nvarchar(4) default N'Nam', QueQuan nvarchar(30), -- giả sử MaSV và NgaySinh là khóa chính constraint KhoaChinh primary key(MaSV,NgaySinh) ) - Xóa bảng: drop table + Nếu đang có cột từ bảng khác tham chiếu đến phải xóa bảng đó trước mới xóa được bảng này Ví dụ: xóa bảng sinh viên vừa tạo ở trên use QLBH drop table SinhVien - Thêm cột: alter table add not null(có hoặc không) Ví dụ: thêm cột tên lớp vào bảng sinh viên use QLSV alter table SinhVien add TenLop varchar(10) - Xóa cột: 8 ANHMATTROI – TIN5A alter table drop column Ví dụ: xóa cột tên lớp trong bảng sinh viên use QLSV alter table SinhVien drop column TenLop - Thay đổi kiểu dữ liệu của cột: alter table alter column not null (có hoặc không) - Đặt hoặc thay đổi khóa chính: alter table add constraint primary key() Chú ý: cột phải có thuộc tính not null mới thêm hoặc thay đổi khóa chính và bảng hiện tại đang rỗng chưa có hàng nào - Kết nối hai cột thuộc hai bảng khác nhau: alter table add constraint foreign key() references () - Đặt giá trị mặc định cho cột: alter table add default for - Xem đặc tính của bảng: exec sp_help - Chèn dữ liệu vào bảng hay nhập dữ liệu vào từng hàng của bảng: insert into ( , , .....) values( , , ...........) + Chèn dữ liệu cho n hàng thì viết n dòng trên và kiểu dữ liệu chèn vào phải phù hợp với từng cột Ví dụ: chền dữ liệu cho bảng sinh viên ở ví dụ (*) use QLBH insert into SinhVien(TenSV,NgaySinh,Diem,GioiTinh,QueQuan) 9 ANHMATTROI – TIN5A values(N'Đoàn Văn Dũng','06/23/1992',9.5,N'Nữ',N'Nam Định') insert into SinhVien(TenSV,NgaySinh,Diem,GioiTinh,QueQuan) values(N'Đỗ Thanh Bình','06/3/1993',9.5,N'Nữ',N'Hà Nội') insert into SinhVien(TenSV,NgaySinh,Diem,GioiTinh,QueQuan) values(N'Nguyễn Thị Đức','12/15/1993',9.5,N'Nữ',N'Hải Phòng') - Chèn dữ liệu từ bảng khác vào: insert into select from + Nếu muốn loại bớt mấy hàng trước khi chèn vào bảng thì thêm where + Số cột chọn ở select == số cột bảng chèn vào và phải cùng kiểu dữ liệu - Lấy các hàng của bảng khác vào bảng: hai bảng có cùng sự phù hợp về kiểu dữ liệu giữa các cột với nhau: select into from - Xóa từng hàng một của bảng: + Trên một bảng: delete where <điều kiện> + Trên nhiều bảng: delete from from …… where <điều kiện> Ví dụ: xóa thông tin sinh viên Đoàn Văn Dũng delete SinhVien where TenSV = N'Đoàn Văn Dũng' - Xóa tất cả các hàng trong bảng: truncate table - Sửa đổi thông tin một ô, nhiều ô hoặc cả cột trong bảng: update set = (nếu lấy dữ liệu từ một cột thuộc bảng khác ta thêm) from where <điều kiện> - Ngắt kết nối ràng buộc hai bảng: khi không chèn được dữ liệu vào bảng alter table nocheck constraint 10 ANHMATTROI – TIN5A Để kết nối lại ràng buộc thay nocheck bằng check - Xem ràng buộc có được tạo thành công không: exec sp_helpconstraint - Xóa ràng buộc: khóa chính, khóa ngoài: alter table drop c. Tạo view - Tạo view: use create view as select from - Xem thông tin view: exec sp_helptext - Sửa view: giống như ở bảng alter view ………………………… - Xóa view: drop view - Truy vấn: select from where <điều kiện> d. Nối hai bảng tạo thành bảng mới select from join/inner join on <điều kiện kết nối> 11 ANHMATTROI – TIN5A where <điều kiện> Ví dụ: ta có hai bảng sau BangA BangB Bảng kết quả: select * from BangA as a inner join BangB as b on a.ma = b.ma select a.ma, a.ten, b.diem from BangA as a inner join BangB as b on a.ma = b.ma 12 ANHMATTROI – TIN5A select a.ma, a.ten, b.diem from BangA as a inner join BangB as b on a.ma = b.ma where a.ma = '1000000001' 4. Một số hàm thông dụng + sum(): tổng các số trong cột này + avg(): giá trị trung bình + count(*): số hàng trong bảng + min(): giá trị nhỏ nhất trong cột + max(): giá trị lớn nhất + getdate(): trả về ngày tháng năm hiện tại + year(): trả lại năm + month(): trả lại tháng + day(): trả lại ngày + sysdatetime(): thời gian SQL Server 13 ANHMATTROI – TIN5A + concat(‘chuỗi 1’,’chuỗi 2’,…): nối các chuỗi lại với nhau + cast( as ): chuyển kiểu dữ liệu + convert(, ): chuyển kiểu dữ liệu + rtrim(): vất bỏ những khoẳng trắng(dấu cách) ở bên phải chuỗi + ltrim(): ------------------------------------------------------- trái chuỗi + left(,): + right(,): + len(): hàm trả về số kí tự của chuỗi + datalength(): hàm trả về số kí tự của chuỗi nhân với kích thước của một kí tự(B) + charindex(,,): hàm trả về vị trí của chuỗi con trong chuỗi mẹ + substring(,,): lấy n kí tự trong chuỗi gốc bắt đầu từ vị trí bắt đầu + choose(,): hàm này chỉ có ở sql server 2012 + reverse(): đảo ngược chuỗi + upper(): chuyển chuỗi đó sang kí tự hoa + lower(): chuyển chuỗi sang kí tự thường + replace(,,): + dateadd(,,): + datediff(,,): sự chênh lệnh + datename(,): trả lại tên + datepart(,): trả lại năm,tháng,… + format( value, format, [culture] ): + abs(): trả lại giá trị tuyệt đối + power(x,y): xy 14 ANHMATTROI – TIN5A + sqrt(x): căn bậc hai của x + square(x): x + round(,): + rand(): tạo ra một số ngẫu nhiên từ 0.0 -> 1.0 + iif(,’true’,’false’): nếu đúng trả lại true sai trả lại false + coalesce(,,’chuỗi a’): nếu cả hai giá trị ở cả hai cột là null thì trả lại chuỗi a + db_name(): tên cơ sở dữ liệu + host_name(): host name + current_user(): người dùng hiện tại + user_name(): tên người dùng + app_name(): tên ứng dụng + isnull(,): 5. Câu lệnh truy vấn a. Khung câu lệnh truy vấn - Tạo định danh cho cột: as VD: MaSV as MaSinhVien hoặc MaSV as N’Mã sinh viên’ - Select + select * : lấy tất cả các cột Ví dụ: cho bảng SinhVien gồm các thông tin sinh viên 15 ANHMATTROI – TIN5A select * from SinhVen được bảng ban đầu + select top ,…….. select top 3 * from SinhVien + select distinct : những giá trị bị trùng trên cột đó chỉ lấy một giá trị select distinct que from SinhVien + select count(*)/avg()/sum()/min()/max(): cột có kiểu số nguyên hoặc số thực 16 ANHMATTROI – TIN5A Ví dụ: cho bảng BangDiem sau select count(*) as N'Số sinh viên' from BangDiem select round(avg(diem),1) as N'Điểm trung bình' from BangDiem select sum(diem) as N'Tổng điểm' from BangDiem select min(diem) as N'Điểm thấp nhất' from BangDiem select max(diem) as N'Điểm cao nhất' from BangDiem 17 ANHMATTROI – TIN5A + Nếu lấy dữ liệu từ nhiều bảng khác nhau phải xác định rõ cột đó thuộc bảng nào bằng cách select . + Có thể có thêm khối chọn select nữa ở khối chọn select ngoài, kết quả ở khối chọn select ở trong phải là 1 giá trị select ,(select from ) from Ví dụ: với dữ liệu của bảng BangDiem select ten as Ten, (select top 1 masv from BangDiem) as MaSV from BangDiem + Tạo định danh cho cột select as Vd: select MaSV as N’Mã sinh viên’ hoặc select MaSV as MSV from SinhVien + Có thể có lệnh chuyển case trong khối select VD: hiện thị tên sv, giới tính nếu là nữ hiển thị là 0 nam là 1 Ta có bảng thông tin sinh viên như sau: 18 ANHMATTROI – TIN5A select masv as N'Mã sinh viên', ten N'Tên sinh viên', ngaysinh as N'Ngày sinh', case gioitinh when N'Nam' then '1' else '0' end as N'Giới tính', que as N'Quê' from SinhVien - From + Nếu lấy dữ liệu từ nhiều bảng bạn có thể tạo bí danh cho bảng ví dụ như: select a.MaSV, a.TenSV, b.DT from SinhVien a, DiemThi b where a.MaSV = b.MaSV Thay cho: select SinhVien.MaSV, SinhVien.TenSV, DiemThi.DT from SinhVien, DiemThi 19 ANHMATTROI – TIN5A where SinhVien.MaSV = DiemThi.MaSV + Lấy bản ghi từ khối chọn select bằng cách tạo định danh VD: select a.masv as N'Mã sinh viên' from ( select masv, ten from SinhVien ) as a - Where <điều kiện để lấy hay không lấy hàng này> Điều kiện ở mệnh đề where: + =,>,<,>=,<=,<>,!>,!<,!= giá trị ô của cột đó so sánh với giá trị nào đó Ví dụ: cho bảng điểm của sinh viên như sau: 20