Mysql command line

Read More

Kết nối và khoá trong SQL nối 2 bảng với nhau bằng JOIN hoặc WHERE

Kết nối và khoá

Đôi khi chúng ta phải lấy dữ liệu từ hai bảng cùng một lúc, chúng ta thực hiện một kết nối.

Các bảng trong CSDL có thể quan hệ ràng buộc với nhau thông qua các khoá. Một khoá chính (primary key)  là một cột mà trong đó mỗi giá trị của hàng phải là duy nhất. Mục đích của khoá là kết nối dữ liệu lại với nhau, từ nhiều bảng khác nhau mà không gây trùng lắp dữ liệu giữa các bảng.

Trong bảng Employees (nhân viên) ở ví dụ dưới đây có cột Employees_ID là khoá chính, bảo đảm rằng không thể có hai dòng nào có trùng Employees_ID. Employees_ID dùng để phân biệt hai nhân viên khi họ trùng tên.

Trong ví dụ dưới đây:

• Employee_ID là khoá chính của bảng Employees.
• Prod_ID là khoá chính của bảng Orders.
• Cột Employeed_ID trong bảng Orders được sử dụng để kết nối với bảng Employees, chỉ đến nhân viên trong bảng Employees.

Bảng Employees:

Employees_ID	Name
01	Hansen, Ola
02	Svendson, Tove
03	Svendson, Stephen
04	Pettersen, Kari

Bảng Orders:

Prod_ID	Product	Employee_ID
234	Printer	01
657	Table	03
865	Chair	03

Kết nối hai bảng với nhau

Chúng ta có thể lấy dữ liệu từ hai bảng bằng cách kết nối chúng, tương tự như sau:

Sử dụng Where để nối 2 bảng (Cách 1)

Ví dụ: Tìm xem ai đã đặt hàng sản phẩm và họ đã đặt món hàng gì:

SELECT Employees.Name, Orders.Product
FROM Employees, Orders
WHERE Employees.Employee_ID = Orders.Employee_ID

Hoặc

SELECT Employees.Name, Orders.Product
FROM Employees A, Orders B
WHERE A.Employee_ID = B.Employee_ID

kết quả trả về:

Name	Product
Hansen, Ola	Printer
Svendson, Stephen	Table
Svendson, Stephen	Chair

Ví dụ: Tìm xem ai đã đặt hàng máy in:

SELECT Employees.Name
FROM Employees, Orders
WHERE Employees.Employee_ID = Orders.Employee_ID
AND Orders.Product = 'Printer'

kết quả trả về:

Name
Hansen, Ola

Sử dụng JOIN để nối 2 bảng (Cách 2)

Ta có thể sử dụng từ khoá JOIN để kết nối dữ liệu từ hai bảng.

Ví dụ: INNER JOIN

Cú pháp:

SELECT cột_1, cột_2, cột_3
FROM bảng_1
INNER JOIN bảng_2
ON bảng_1.khoá_chính = bảng_2.khoá_ngoại

Ai đã đặt hàng và họ đã đặt món hàng nào:

SELECT Employees.Name, Orders.Product
FROM Employees
INNER JOIN Orders
ON Employees.Employee_ID = Orders.Employee_ID

INNER JOIN trả về tất cả các dòng ở cả hai bảng khi chúng tương ứng với nhau. Nếu có một dòng ở bảng Employees không ứng với dòng nào ở bảng Orders, dòng đó sẽ không được tính.

kết quả trả về:

Name	Product
Hansen, Ola	Printer
Svendson, Stephen	Table
Svendson, Stephen	Chair

Ví dụ: LEFT JOIN

Cú pháp:

SELECT cột_1, cột_2, cột_3
FROM bảng_1
LEFT JOIN bảng_2
ON bảng_1.khoá_chính = bảng_2.khoá_ngoại

Liệt kê tất cả các nhân viên và món hàng mà họ đặt (nếu có):

SELECT Employees.Name, Orders.Product
FROM Employees
LEFT JOIN Orders
ON Employees.Employee_ID = Orders.Employee_ID

LEFT JOIN trả về tất cả các dòng của bảng thứ nhất (Employees), ngay cả khi các dòng đó không ứng với dòng nào ở bảng thứ hai (Orders). Nếu có một dòng nào ở bảng Employees không ứng với bất cứ dòng nào ở bảng Orders thì dòng đó cũng vẫn được tính.

kết quả trả về:

Name	Product
Hansen, Ola	Printer
Svendson, Tove
Svendson, Stephen	Table
Svendson, Stephen	Chair
Pettersen, Kari

Ví dụ: RIGHT JOIN

Cú pháp:

SELECT cột_1, cột_2, cột_3
FROM bảng_1
RIGHT JOIN bảng_2
ON bảng_1.khoá_chính = bảng_2.khoá_ngoại

Liệt kê tất cả các mặt hàng được đặt và tên người đặt hàng (nếu có):

SELECT Employees.Name, Orders.Product
FROM Employees
RIGHT JOIN Orders
ON Employees.Employee_ID = Orders.Employee_ID

RIGHT JOIN trả về tất cả các dòng ở bảng thứ hai (Orders), ngay cả khi các dòng đó không ứng với dòng nào ở bảng thứ nhất (Employees). Nếu có một dòng nào ở bảng Orders không ứng với bất cứ dòng nào ở bảng Employees thì dòng đó cũng vẫn được tính.

kết quả trả về:

Name	Product
Hansen, Ola	Printer
Svendson, Stephen	Table
Svendson, Stephen	Chair

Ví dụ: Ai đã đặt hàng máy in:

SELECT Employees.Name
FROM Employees
INNER JOIN Orders
ON Employees.Employee_ID = Orders.Employee_ID
WHERE Orders.Product = 'Printer'

kết quả trả về:

Name
Hansen, Ola

Các Loại Join Trong MySQL, SQL Cần Biết

[1] + [4] Basic Join (Không điều kiện)

[1] - Inner Join (MySQL)
[2] - Left Join (MySQL) = Left Outer Join (SQL)
[3] - Right Join (MySQL) = Right Outer Join (SQL)
[4] - Full Join (MySQL) = Full Outer Join (SQL)


Ví dụ: MY SQL sử dụng `LEFT JOIN` trong khi SQL Server sử dụng `LEFT OUTER JOIN ` .

[5] + [7] Excluding Join (Có điều kiện)

[5] - Left excluding join
[6] - Outer excluding join
[7] - Right excluding join

[8] Natural Join (MySQL, SQL)

Là các cột có cùng tên của các bảng được liên kết sẽ xuất hiện một lần duy nhất.

SELECT *
FROM foods 
NATURAL JOIN company;

[9] Cross Join (MySQL, SQL) (A * B = C)

SELECT * 
FROM table1 
CROSS JOIN table2;

[10] Self Join (MySQL, SQL)

Là Bảng liên kết chính nó. Có nghĩa là 1 bảng ta tạo thành hai bảng và Join chúng lại

VD: như quan hệ nhân viên cấp trên và nhân viên cấp dưới here

SELECT a.column_name, b.column_name... 
FROM table1 a, table1 b 
WHERE a.common_filed = b.common_field;

[11] Joining Three or More table (MySQL, SQL)

Khi muốn liên kết 2 - 3 bảng với nhau

Select *
From
    tableA a
        INNER JOIN
    tableB b
        ON a.common = b.common
        INNER JOIN 
    TableC c
        ON b.common = c.common

SQL Server

Read More

Tài liệu hướng dẫn Stored Procedure

[1] Stored Procedure với Mysql

[2] Call Stored Procedure với Java Swing

[3] Web chi tiết Stored Procedure

Read More

How to create foreign key in mysql

Create Foreign Key in mysql workbench

USE sinhvien;

CREATE TABLE city (

  id int(11) NOT NULL,

  name varchar(45) DEFAULT NULL,

  PRIMARY KEY (id)

);

CREATE TABLE sinhviencity(

    id INT(11) NOT NULL PRIMARY KEY AUTO_INCREMENT,

    idname VARCHAR(50) NOT NULL ,

    class VARCHAR (50) NOT NULL ,

    cityID INT(11),

    CONSTRAINT city_fk FOREIGN KEY (cityID) REFERENCES city(id)

);

How to Replace ` ?

Ctrl + F > Find & Replace > ` and  "" > Done

Home > Models > Create EER models from database > next...choice table ...

Tạo trong lệnh tạo bảng create table

Chúng ta sẽ tạo trực tiếp trong lệnh tạo bảng và cú pháp của nó cũng tương tự như lệnh tạo khóa chính, nghĩa là sẽ đặt ở cuối danh sách các fields.

Ví dụ không đặt tên:

1 2 3 4 5 6 7 8 9 10 11 12 13

CREATE TABLE Groups (     groupid INT(11) NOT NULL PRIMARY KEY,     title INT(11) NOT NULL,     LEVEL TINYINT(1) DEFAULT 1 NOT NULL ); CREATE TABLE Users(     userid INT(11) NOT NULL PRIMARY KEY AUTO_INCREMENT,     username VARCHAR(50) NOT NULL ,     email VARCHAR (50) NOT NULL ,     groupid INT(11),     FOREIGN KEY (groupid) REFERENCES Groups(groupid) );

Các bạn thấy tôi đã sử dụng từ khóa FOREIGN KEY (groupid) REFERENCES Groups(groupid) để tạo khóa chính, trong đó:

• FOREIGN KEY (groupid): là field ở bảng Users
• REFERENCES Groups(groupid): là tham chiếu đến field groupid trong bảng Groups

Sau khi tạo xong bạn vào PHPMYADMIN và chọn database, chọn diagram ở thanh tools bạn sẽ thấy một sơ đồ như sau:

Như vậy là bạn đã tạo thành công rồi đấy.

Ví dụ có đặt tên:

Tương tự các phần trước, để đặt tên thì ta phải sử dụng từ khóa CONSTRAINT.

Ví dụ:

1 2 3 4 5 6 7 8 9 10 11 12 13

CREATE TABLE Groups (     groupid INT(11) NOT NULL PRIMARY KEY,     title INT(11) NOT NULL,     LEVEL TINYINT(1) DEFAULT 1 NOT NULL ); CREATE TABLE Users(     userid INT(11) NOT NULL PRIMARY KEY AUTO_INCREMENT,     username VARCHAR(50) NOT NULL ,     email VARCHAR (50) NOT NULL ,     groupid INT(11),     CONSTRAINT fk_group FOREIGN KEY (groupid)  REFERENCES Groups(groupid) );

Tạo bằng lệnh ALTER TABLE

Với cách này ta phải tạo hai bảng trước, sau đó sẽ dùng lệnh ALTER TABLE để thêm FOREIGN KEY.

1 2 3 4 5 6 7 8 9 10 11 12 13 14

CREATE TABLE Groups (     groupid INT(11) NOT NULL PRIMARY KEY,     title INT(11) NOT NULL,     LEVEL TINYINT(1) DEFAULT 1 NOT NULL ); CREATE TABLE Users(     userid INT(11) NOT NULL PRIMARY KEY AUTO_INCREMENT,     username VARCHAR(50) NOT NULL ,     email VARCHAR (50) NOT NULL ,     groupid INT(11) ); ALTER TABLE Users ADD FOREIGN KEY(groupid)  REFERENCES Groups(groupid);

Hoặc:

1 2 3 4 5 6 7 8 9 10 11 12 13 14

CREATE TABLE Groups (     groupid INT(11) NOT NULL PRIMARY KEY,     title INT(11) NOT NULL,     LEVEL TINYINT(1) DEFAULT 1 NOT NULL ); CREATE TABLE Users(     userid INT(11) NOT NULL PRIMARY KEY AUTO_INCREMENT,     username VARCHAR(50) NOT NULL,     email VARCHAR (50) NOT NULL,     groupid INT(11) ); ALTER TABLE Users ADD CONSTRAINT fk_group  FOREIGN KEY(groupid)  REFERENCES Groups(groupid);

Tạo khóa ngoại trường hợp tham chiếu chính nó

Trường hợp này ta cũng sử dụng cú pháp tương tự, sự khác nhau chỉ là thay vì tham chiếu tới bảng nào đó thì nó tham chiếu đến chính tên bảng của nó.

Ví dụ:

1 2 3 4 5 6 7

CREATE TABLE Employee(     id INT(11) NOT NULL PRIMARY KEY AUTO_INCREMENT,     fullname VARCHAR(50) NOT NULL,     email VARCHAR (50) NOT NULL,     leader_id INT (11) NOT NULL,     CONSTRAINT pk_self FOREIGN KEY (leader_id)  REFERENCES Employee(id) );

3. Xóa 
Foreign Key (DROP)

Để xóa được Foreign Key thì bạn phải biết tên của nó là gì, mà tên của nó thì chỉ tồn tại trong trường hợp ta có sử dụng từ khóa CONSTRAINT để tạo. Chính vì vậy khuyến khích bạn sử dụng CONSTRAINTđể tạo khóa ngoại nhé.

Sau đây là cú pháp xóa Foreign Key:

1	ALTER TABLE Users DROP FOREIGN KEY fk_group;

Trong đó fk_group là tên của khóa ngoại.

Lưu ý quan trọng:

Khóa ngoại ở bảng orders sẽ tham chiếu đến khóa chính của bảng customers. Lúc này bảng customers gọi là bảng cha và bảng order gọi là bảng con. Đây chính là điều BẮT BUỘC của khóa ngoại.

# Lời kết

Trong bài này chủ yếu tìm hiểu định nghĩa Khóa ngoại (Foreign key) là gì và tìm hiểu một số cách tạo khóa ngoại thông dụng, cách tạo khóa ngoại cho một bảng và cho nhiều bảng. Thông thường khi làm việc với các ứng dụng web thì ta ít khi sử dụng khóa ngoại bởi vì sẽ rất chậm, vì vậy người ta sẽ cố gắng thiết kế CSDL làm sao tối ưu để không tồn tại khóa ngoại.

Read More