Đề tài SQL trong MySQL

SQL trong MySQL Để thực hiện nội dung phần này, đầu tiên sử dụng MySQL Query Browser để kết nối đến Server Nội dung Kết nối đến Server Các Query (SQL) thao tác trên Database Các Query (SQL) thao tác trên bảng Các Query (SQL) quản lý người dùng Kết nối đến Server Kết nối đến Server Sử dụng MySQL Command Line Client Sử dụng MySQL Query Browser MySQL Command Line Client  Vào Start/All programs/MySQL/MySQL Server 5.1 (tùy theo phiên bản cài đặt)/MySQL Command Line Client Nhập Password của User root MySQL Query Browser (1) Vào Start/All programs/MySQL/MySQL Query Browser 1 2 3 4 5 6 7 MySQL Query Browser (1) Tên kết nối Tên Server muốn kết nối đến Username của người dùng Password của người dùng CSDL mặc định khi kết nối đến Tạo một kết nối lưu trữ sẵn Gửi thông tin yêu cầu kết nối. Các Query (SQL) thao tác trên Database Truy vấn thông tin chung select version()// để xem phiên bản MySQL đang sử dụng Select user()// xem thông tin người dùng hiện đang kết nối Xem thông tin tất cả người dùng Chọn mysql làm cơ sở mặc định sau đó thực hiện câu truy vấn như hình bên dưới Tạo một CSDL mới Xem thông tin CSDL hiện có Xoá CSDL hiện có Cú pháp: DROP DATABASE database_name Các query (SQL) thao tác trên bảng Hiển thị các bảng hiện có show tables; DDL (Data Definition Language) Tạo bảng mới CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name (create_definition,...) // định nghĩa bảng [table_options] // tùy chọn bảng [partition_options] // tùy chọn phân vùng Ví dụ 1 B1: Chọn CSDL cần tạo bảng B2: Nhập vào đoạn lệnh SQL để tạo bảng Ví dụ 2 CREATE TABLE sinhvien ( id_sv int primary key, ho_sv varchar(50), ngay_sinh datetime, noi_sinh varchar(100), dchi varchar(100), hoc_bong double, id_khoa int, constraint pk_sinhvien_khoa foreign key (id_khoa) references dmkhoa(id_khoa)); Ví dụ 3 CREATE TABLE diem_thi ( id_sv int, id_mon int, diem float, constraint pk_diem_thi primary key (id_sv, id_mon), constraint pk_dthi_sv foreign key (id_sv) references sinhvien(id_sv), constraint pk_dthi_mh foreign key (id_mon) references monhoc (id_mon) ); SQL ALTER TABLE Thêm một cột mới Cú pháp: ALTER TABLE table_name ADD column_name datatype Ví dụ: ALTER TABLE Persons ADD DateOfBirth date Thay đổi kiểu dữ liệu Cú pháp: ALTER TABLE table_name MODIFY COLUMN column_name datatype Ví dụ: ALTER TABLE Persons MODIFY COLUMN DateOfBirth year Xoá một cột Cú pháp: ALTER TABLE table_name DROP COLUMN column_name Ví dụ: ALTER TABLE Persons DROP COLUMN DateOfBirth SQL CONSTRAINTS • Ràng buộc được sử dụng để giới hạn các kiểu dữ liệu được nhập vào bảng. • Được thiết đặt ở câu lệnh Create Table hoặc Alter Table • Các constraints thường được sử dụng: – NOT NULL – UNIQUE – PRIMARY KEY – FOREIGN KEY – CHECK – DEFAULT Ràng buộc Not Null Thiết đặt constraint Not Null Mặc định một cột trên bảng có thể giữ giá trị Null Vd1: CREATE TABLE Persons ( P_Id int NOT NULL, LastName varchar(255) NOT NULL, FirstName varchar(255), Address varchar(255), City varchar(255) ) Ràng buộc duy nhất (Unique Constraint) Thiết đặt constraint Unique • Ràng buộc tính duy nhất cho một cột hoặc một tập hợp các cột. • Một ràng buộc khoá chính tự động có ràng buộc duy nhất được định nghĩa trên nó. • Có thể có nhiều ràng buộc duy nhất trên một bảng, nhưng chỉ có một ràng buộc khoá chính Unique Constraint on Create Table Ví dụ: CREATE TABLE Persons ( P_Id int NOT NULL, LastName varchar(255) NOT NULL, FirstName varchar(255), Address varchar(255), City varchar(255), UNIQUE (P_Id) ) Unique Constraint on Create Table Ví dụ: CREATE TABLE Persons ( P_Id int NOT NULL, LastName varchar(255) NOT NULL, FirstName varchar(255), Address varchar(255), City varchar(255), CONSTRAINT uc_PersonID UNIQUE (P_Id,LastName) ) Unique Constraint on Alter Table Ví dụ 1: ALTER TABLE Persons ADD UNIQUE (P_Id) Ví dụ 2: ALTER TABLE Persons ADD CONSTRAINT uc_PersonID UNIQUE (P_Id,LastName) To DROP a UNIQUE Constraint Ví dụ: ALTER TABLE Persons DROP INDEX uc_PersonID Ràng buộc khoá chính (PRIMARY KEY Constraint) Dùng để xác định duy nhất một mẫu tin trong bảng. Khoá chính phải chứa giá trị duy nhất. Một cột khoá chính không được chứa giá trị Null. Mỗi một bảng nên có và chỉ cần một khoá chính. SQL PRIMARY KEY Constraint on CREATE TABLE Ví dụ 1: CREATE TABLE Persons ( P_Id int NOT NULL, LastName varchar(255) NOT NULL, FirstName varchar(255), Address varchar(255), City varchar(255), PRIMARY KEY (P_Id) ) SQL PRIMARY KEY Constraint on CREATE TABLE Ví dụ 2: CREATE TABLE Persons ( P_Id int NOT NULL, LastName varchar(255) NOT NULL, FirstName varchar(255), Address varchar(255), City varchar(255), CONSTRAINT pk_PersonID PRIMARY KEY (P_Id,LastName) ) SQL PRIMARY KEY Constraint on ALTER TABLE Ví dụ 1: ALTER TABLE Persons ADD PRIMARY KEY (P_Id) Ví dụ 2: ALTER TABLE Persons ADD CONSTRAINT pk_PersonID PRIMARY KEY (P_Id,LastName) To DROP a PRIMARY KEY Constraint Ví dụ 1: ALTER TABLE Persons DROP PRIMARY KEY Ràng buộc khoá ngoại SQL FOREIGN KEY Constraint SQL FOREIGN KEY Constraint on CREATE TABLE Ví dụ 1: CREATE TABLE Orders ( O_Id int NOT NULL, OrderNo int NOT NULL, P_Id int, PRIMARY KEY (O_Id), FOREIGN KEY (P_Id) REFERENCES Persons(P_Id) ) SQL FOREIGN KEY Constraint on CREATE TABLE Ví dụ 2: CREATE TABLE Orders ( O_Id int NOT NULL, OrderNo int NOT NULL, P_Id int, PRIMARY KEY (O_Id), CONSTRAINT fk_PerOrders FOREIGN KEY (P_Id) REFERENCES Persons(P_Id) ) SQL FOREIGN KEY Constraint on ALTER TABLE Ví dụ 1: ALTER TABLE Orders ADD FOREIGN KEY (P_Id) REFERENCES Persons(P_Id) Ví dụ 2: ALTER TABLE Orders ADD CONSTRAINT fk_PerOrders FOREIGN KEY (P_Id) REFERENCES Persons(P_Id) To DROP a FOREIGN KEY Constraint Ví dụ: ALTER TABLE Orders DROP FOREIGN KEY fk_PerOrders SQL CHECK Constraint SQL CHECK Constraint on CREATE TABLE Ví dụ 1: CREATE TABLE Persons ( P_Id int NOT NULL, LastName varchar(255) NOT NULL, FirstName varchar(255), Address varchar(255), City varchar(255), CHECK (P_Id>0) ) Ví dụ 2: CREATE TABLE Persons ( P_Id int NOT NULL, LastName varchar(255) NOT NULL, FirstName varchar(255), Address varchar(255), City varchar(255), CONSTRAINT chk_Person CHECK (P_Id>0 AND City='Sandnes') ) SQL CHECK Constraint on CREATE TABLE SQL CHECK Constraint on ALTER TABLE Ví dụ 1: ALTER TABLE Persons ADD CHECK (P_Id>0) Ví dụ 2: ALTER TABLE Persons ADD CONSTRAINT chk_Person CHECK (P_Id>0 AND City='Sandnes') SQL DEFAULT Constraint SQL DEFAULT Constraint on CREATE TABLE Ví dụ 1: CREATE TABLE Persons ( P_Id int NOT NULL, LastName varchar(255) NOT NULL, FirstName varchar(255), Address varchar(255), City varchar(255) DEFAULT 'Sandnes' ) SQL DEFAULT Constraint on CREATE TABLE Ví dụ 2: CREATE TABLE Orders ( O_Id int NOT NULL, OrderNo int NOT NULL, P_Id int, OrderDate date DEFAULT GETDATE() ) SQL DEFAULT Constraint on ALTER TABLE Ví dụ: ALTER TABLE Persons ALTER City SET DEFAULT 'SANDNES' To DROP a DEFAULT Constraint Ví dụ: ALTER TABLE Persons ALTER City DROP DEFAULT Xem cấu trúc bảng describe table_name; Show columns from table_name; Vd: describe mon_hoc; Cấu trúc bảng mon_hoc DML (Data Manipulation Language) Thêm dữ liệu vào bảng Cú pháp: Chèn mẫu tin với tất cả các trường trong bảng Chèn mẫu tin với các giá trị chỉ định Thêm dữ liệu vào bảng Ví dụ: Xem thông tin bảng Cú pháp: Chỉ định các cột cần hiển thị Hiển thị tất cả các cột của bảng Xem thông tin bảng Xem kết quả bảng dữ liệu Thực thi câu Query Vùng hiển thị kết quả Alias: Bí danh Đặt bí danh (tên khác) cho bảng hoặc cột. Sử dụng cho các cột hoặc bảng có tên dài và phức tạp. Bí danh thường ngắn. Cú pháp đặt bí danh cho bảng: SELECT column_name(s) FROM table_name AS alias_name Alias: Bí danh Cú pháp đặt bí danh cho cột: SELECT column_name AS alias_name FROM table_name Alias: Bí danh – ví dụ  Ví dụ 1: SELECT po.OrderID, p.LastName, p.FirstName FROM Persons AS p,Product_Orders AS po WHERE p.LastName='Hansen' AND p.FirstName='Ola‘  Ví dụ 2: không đặt bí danh SELECT Product_Orders.OrderID, Persons.LastName, Persons.FirstName FROM Persons,Product_Orders WHERE Persons.LastName='Hansen' AND Persons.FirstName='Ola' SQL JOIN  Từ khoá Join được sử dụng để truy vấn dữ liệu từ 2 bảng trở lên dựa trên mối quan hệ được thiết lập giữa các bảng.  Các kiểu Join: Join: trả về dòng khi có ít nhất một giá trị chung trong cả 2 bảng. Left Join: trả về tất cả các dòng ở bảng bên trái ngay cả khi nó không có mẫu tin tương ứng với bảng bên phải. Right Join: Trả về tất cả các dòng ở bảng bên phải ngay cả khi nó không có mẫu tin tương ứng với bảng bên trái. Full Join: trả về tất cả các dòng là kết quả của tích đề các Inner Join Kiểu kết nối giống với Join  Cú pháp: SELECT column_name(s) FROM table_name1 INNER JOIN table_name2 ON table_name1.column_name=table_name2.column_name  Ví dụ: SELECT Persons.LastName, Persons.FirstName,Orders.OrderNo FROM Persons INNER JOIN Orders ONPersons.P_Id=Orders.P_Id ORDER BY Persons.LastName Left Join  Cú pháp: SELECT column_name(s) FROM table_name1 LEFT JOIN table_name2 ON table_name1.column_name=table_name2.column_name  Ví dụ: SELECT Persons.LastName, Persons.FirstName,Orders.OrderNo FROM Persons LEFT JOIN Orders ON Persons.P_Id=Orders.P_Id ORDER BY Persons.LastName Right Join  Cú pháp: SELECT column_name(s) FROM table_name1 RIGHT JOIN table_name2 ON table_name1.column_name=table_name2.column_name  Ví dụ: SELECT Persons.LastName, Persons.FirstName, Orders.OrderNo FROM Persons RIGHT JOIN Orders ON Persons.P_Id=Orders.P_Id ORDER BY Persons.LastName FULL JOIN  Cú pháp: SELECT column_name(s) FROM table_name1 FULL JOIN table_name2 ON table_name1.column_name=table_name2.column_name  Ví dụ: SELECT Persons.LastName, Persons.FirstName,Orders.OrderNo FROM Persons FULL JOIN Orders ON Persons.P_Id=Orders.P_Id ORDER BY Persons.LastName SQL UNION Operator Dùng để nối 2 hoặc nhiều tập kết quả của câu lệnh Select lại với nhau Lưu ý:  Mỗi câu lệnh Select trong phép toán Union phải có số cột giống nhau.  Các cột cùng vị trí phải có cùng kiểu dữ liệu, các cột trong mỗi câu lệnh Select phải có cùng thứ tự cột.  Tên các cột trong tập kết quả được đặt theo câu lệnh Select đầu tiên tham gia vào phép hợp. SQL UNION Operator Cú pháp: // không hiển thị các dòng trùng SELECT column_name(s) FROM table_name1 UNION SELECT column_name(s) FROM table_name2 // cho phép hiển thị các dòng trùng nhau SELECT column_name(s) FROM table_name1 UNION ALL SELECT column_name(s) FROM table_name2 SQL UNION Operator – Ví dụ Ví dụ 1: SELECT E_Name FROM Employees_Norway UNION SELECT E_Name FROM Employees_USA Ví dụ 2: SELECT E_Name FROM Employees_Norway UNION ALL SELECT E_Name FROM Employees_USA SQL SELECT INTO Statement  Lấy dữ liệu từ một bảng và chèn dữ liệu đó vào một bảng khác. Sử dụng để tạo ra các bảng coppies dữ liệu. SQL SELECT INTO Statement Cú pháp 1: SELECT * INTO new_table_name [IN externaldatabase] FROM old_tablename Ví dụ 1: SELECT * INTO Persons_Backup FROM Persons SQL SELECT INTO Statement Cú pháp 2: SELECT column_name(s) INTO new_table_name [IN externaldatabase] FROM old_tablename Ví dụ 2: SELECT LastName,FirstName INTO Persons_Backup FROM Persons SQL SELECT INTO Statement Ví dụ 3: SELECT LastName,Firstname INTO Persons_Backup FROM Persons WHERE City='Sandnes' Ví dụ 4: SELECT Persons.LastName,Orders.OrderNo INTO Persons_Order_Backup FROM Persons INNER JOIN Orders ON Persons.P_Id=Orders.P_Id Hiển thị giá trị phân biệt Chọn các mẫu tin thỏa điều kiện Cú pháp: Sử dụng mệnh đề WHERE để chọn lấy những mẫu tin thỏa điều kiện Chọn các mẫu tin thỏa điều kiện Vd: Hiển thông tin thông tin chi tiết về môn 'Tóan rời rạc' Toán tử AND & OR Chọn các mẫu tin với hơn 1 điều kiện: đk1 AND đk2 hoặc đk1 OR đk2 AND: hiển thị mẫu tin khi đk1 và đk2 là true OR: hiển thị mẫu tin khi đk1 là true hoặc đk2 là true Toán tử AND & OR – ví dụ Vd: tìm thông tin của môn ' toán rời rạc' và có số tiết là 60 Vd: tìm thông tin của môn 'toán rời rạc' hoặc môn có số tiết là 60 Sắp xếp kết quả Cú pháp: ASC: sắp xếp theo thứ tự tăng (mặc định) DESC: sắp xếp theo thứ tự giảm Cập nhật giá trị cho mẫu tin Cú pháp: Lưu ý: mệnh đề WHERE xác định mẫu tin thỏa điều kiện sẽ được cập nhật. Nếu không có toàn bộ các mẫu tin trong bảng sẽ được cập nhật Cập nhật giá trị cho mẫu tin Vd: Chỉnh sửa số tiết của môn ' toán rời rạc' thành 70 Xóa mẫu tin Cú pháp: Lưu ý: Mệnh đề WHERE dùng để chỉ ra mẫu tin thỏa điều kiện sẽ được xóa. Nếu không có mệnh đề WHERE tất cả các mẫu tin của bảng sẽ được xóa. Xóa mẫu tin Vd: xóa môn ' toán rời rạc'