Cách chèn dữ liệu vào SQL

Ngôn ngữ truy vấn có cấu trúc , thường được gọi là SQL , cung cấp rất nhiều tính linh hoạt về cách nó cho phép bạn chèn dữ liệu vào bảng. Ví dụ: bạn có thể chỉ định các hàng dữ liệu riêng lẻ bằng từ khóa VALUES , sao chép toàn bộ tập dữ liệu từ các bảng hiện có bằng truy vấn SELECT , cũng như xác định các cột theo cách khiến SQL tự động chèn dữ liệu vào chúng.

---

Trong hướng dẫn này, ta sẽ xem xét cách sử dụng cú pháp INSERT INTO của SQL để thêm dữ liệu vào bảng với mỗi phương thức này.

Yêu cầu

Để làm theo hướng dẫn này, bạn cần một máy tính chạy một số loại hệ quản trị database quan hệ (RDBMS) sử dụng SQL. Các hướng dẫn và ví dụ trong hướng dẫn này đã được kiểm nghiệm bằng cách sử dụng môi trường sau:

• Server chạy Ubuntu 20.04, với user không phải root có quyền quản trị và firewall được cấu hình bằng UFW, như được mô tả trong hướng dẫn cài đặt server ban đầu của ta cho Ubuntu 20.04 .
• MySQL được cài đặt và bảo mật trên server , như được nêu trong Cách cài đặt MySQL trên Ubuntu 20.04 . Hướng dẫn này đã được xác minh với user MySQL không phải root, được tạo bằng quy trình được mô tả trong Bước 3 .

Lưu ý : Xin lưu ý nhiều RDBMS sử dụng các triển khai SQL duy nhất của riêng họ. Mặc dù các lệnh được nêu trong hướng dẫn này sẽ hoạt động trên hầu hết các RDBMS, nhưng cú pháp hoặc kết quả chính xác có thể khác nếu bạn kiểm tra chúng trên một hệ thống không phải MySQL.

Bạn cũng cần một database và bảng để bạn có thể thực hành chèn dữ liệu. Nếu chưa có, bạn có thể đọc phần Kết nối với MySQL và Cài đặt Database Mẫu sau đây để biết chi tiết về cách tạo database và bảng mà hướng dẫn này sẽ sử dụng trong các ví dụ xuyên suốt.

Kết nối với MySQL và cài đặt database mẫu

Nếu hệ thống database SQL của bạn chạy trên một server từ xa, hãy SSH vào server của bạn từ máy local của bạn:

ssh sammy@your_server_ip 

Sau đó, mở dấu nhắc server MySQL, thay thế sammy bằng tên account user MySQL của bạn:

mysql -u sammy -p 

Tạo database có tên insertDB :

CREATE DATABASE insertDB; 

Nếu database được tạo thành công, bạn sẽ nhận được kết quả như sau:

Output
Query OK, 1 row affected (0.01 sec) 

Để chọn database insertDB , hãy chạy câu lệnh USE sau:

USE insertDB; 

Output
Database changed 

Sau khi chọn database insertDB , hãy tạo một bảng bên trong nó. Ví dụ: giả sử bạn sở hữu một nhà máy và muốn tạo một bảng để lưu trữ một số thông tin về nhân viên của bạn. Bảng này sẽ có năm cột sau:

• name : name mỗi nhân viên, được thể hiện bằng kiểu dữ liệu varchar với tối đa 30 ký tự
• position : cột này sẽ lưu trữ chức danh công việc của mỗi nhân viên, được thể hiện bằng kiểu dữ liệu varchar với tối đa 30 ký tự
• department : bộ phận mà mỗi nhân viên làm việc, được thể hiện bằng kiểu dữ liệu varchar nhưng chỉ có tối đa 20 ký tự
• hourlyWage : cột để ghi lại tiền lương theo giờ của mỗi nhân viên, nó sử dụng kiểu dữ liệu decimal với bất kỳ giá trị nào trong cột này được giới hạn ở độ dài tối đa là bốn chữ số với hai trong số đó ở bên phải dấu thập phân. Do đó, phạm vi giá trị được phép trong cột này là -99.99 đến 99.99
• startDate : ngày mỗi nhân viên được thuê, được biểu thị bằng kiểu dữ liệu date . Các giá trị thuộc loại này phải tuân theo định dạng YYYY-MM-DD

Tạo một bảng có tên factoryEmployees có năm cột sau:

CREATE TABLE factoryEmployees ( 
name varchar(30), 
position varchar(30), 
department varchar(20), 
hourlyWage decimal(4,2), 
startDate date 
); 

Như vậy, bạn đã sẵn sàng làm theo phần còn lại của hướng dẫn và bắt đầu tìm hiểu về cách chèn dữ liệu bằng SQL.

Chèn dữ liệu theo cách thủ công

Cú pháp chung để chèn dữ liệu trong SQL trông giống như sau:

INSERT INTO table_name 
(column1, column2, . . . columnN) 
VALUES 
(value1, value2, . . . valueN); 

Để minh họa, hãy chạy INSERT INTO sau để tải bảng factoryEmployees với một hàng dữ liệu:

INSERT INTO factoryEmployees 
(name, position, department, hourlyWage, startDate) 
VALUES 
('Agnes', 'thingamajig foreman', 'management', 26.50, '2017-05-01'); 

Output
Query OK, 1 row affected (0.00 sec) 

Câu lệnh này bắt đầu bằng các từ khóa INSERT INTO , theo sau là tên của bảng mà bạn muốn chèn dữ liệu. Theo sau tên bảng là danh sách các cột mà câu lệnh sẽ thêm dữ liệu, được gói trong dấu ngoặc đơn. Sau danh sách cột là từ khóa VALUES , sau đó là một tập hợp các giá trị được bao bọc trong dấu ngoặc đơn và được phân tách bằng dấu phẩy.

Thứ tự mà bạn liệt kê các cột không quan trọng. Điều quan trọng cần nhớ là thứ tự của các giá trị bạn cung cấp phù hợp với thứ tự của các cột. SQL sẽ luôn cố gắng chèn giá trị đầu tiên đã cho vào cột đầu tiên được liệt kê, giá trị thứ hai vào cột tiếp theo, v.v. Để minh họa, INSERT sau đây thêm một hàng dữ liệu khác, nhưng liệt kê các cột theo một thứ tự khác:

INSERT INTO factoryEmployees 
(department, hourlyWage, startDate, name, position) 
VALUES 
('production', 15.59, '2018-04-28', 'Jim', 'widget tightener'); 

Output
Query OK, 1 row affected (0.00 sec) 

Nếu bạn không căn chỉnh các giá trị một cách chính xác, SQL có thể nhập dữ liệu vào các cột sai. Ngoài ra, nó sẽ gây ra lỗi nếu bất kỳ giá trị nào xung đột với kiểu dữ liệu của cột, như trong ví dụ sau:

INSERT INTO factoryEmployees 
(name, hourlyWage, position, startDate, department) 
VALUES 
('Louise', 'doodad tester', 16.50, '2017-05-01', 'quality assurance'); 

Output
ERROR 1366 (HY000): Incorrect decimal value: 'doodad tester' for column 'hourlyWage' at row 1 

Lưu ý mặc dù bạn phải cung cấp giá trị cho mọi cột bạn chỉ định, nhưng bạn không nhất thiết phải chỉ định mọi cột trong bảng khi thêm một hàng dữ liệu mới. Miễn là không có cột nào bạn bỏ qua có ràng buộc sẽ gây ra lỗi trong trường hợp này (chẳng hạn như NOT NULL ), MySQL sẽ thêm NULL vào bất kỳ cột không xác định nào:

INSERT INTO factoryEmployees 
(name, position, hourlyWage) 
VALUES 
('Harry', 'whatzit engineer', 26.50); 

Output
Query OK, 1 row affected (0.01 sec) 

Nếu bạn định nhập một hàng có giá trị cho mọi cột trong bảng, bạn không cần phải bao gồm tên cột. Lưu ý các giá trị bạn nhập vẫn phải phù hợp với thứ tự các cột được xác định trong định nghĩa của bảng.

Trong ví dụ này, các giá trị được liệt kê phù hợp với thứ tự các cột được xác định trong câu CREATE TABLE của bảng factoryEmployee :

INSERT INTO factoryEmployees 
VALUES 
('Marie', 'doodad welder', 'production', 27.88, '2018-03-29'); 

Output
Query OK, 1 row affected (0.00 sec) 

Bạn cũng có thể thêm nhiều bản ghi cùng một lúc bằng cách tách từng hàng bằng dấu phẩy, như sau:

INSERT INTO factoryEmployees 
VALUES 
('Giles', 'gizmo inspector', 'quality assurance', 26.50, '2019-08-06'), 
('Daphne', 'gizmo presser', 'production', 32.45, '2017-11-12'), 
('Joan', 'whatzit analyst', 'quality assurance', 29.00, '2017-04-29'); 

Query OK, 3 rows affected (0.00 sec) Records: 3  Duplicates: 0  Warnings: 0 

Sao chép dữ liệu với SELECT

Thay vì chỉ định từng hàng dữ liệu, bạn có thể sao chép nhiều hàng dữ liệu từ một bảng và chèn chúng vào bảng khác bằng truy vấn SELECT .

Cú pháp cho loại hoạt động này trông giống như sau:

INSERT INTO table_A (col_A1, col_A2, col_A3) 
SELECT col_B1, col_B2, col_B3 
FROM table_B; 

Thay vì theo sau danh sách cột với từ khóa VALUES , cú pháp ví dụ này theo sau nó với SELECT . Câu SELECT trong cú pháp ví dụ này chỉ bao gồm mệnh đề FROM , nhưng bất kỳ truy vấn hợp lệ nào cũng có thể hoạt động.

Để minh họa, hãy chạy thao tác CREATE TABLE sau để tạo một bảng mới có tên là showroomEmployees . Lưu ý các cột của bảng này có cùng tên và kiểu dữ liệu với ba cột từ bảng factoryEmployees được sử dụng trong phần trước:

CREATE TABLE showroomEmployees ( 
name varchar(30), 
hourlyWage decimal(4,2), 
startDate date 
); 

Output
Query OK, 0 rows affected (0.02 sec) 

Đến đây bạn có thể tải bảng mới này với một số dữ liệu từ bảng factoryEmployees đã tạo trước đó bằng cách đưa truy vấn SELECT vào INSERT INTO .

Nếu truy vấn SELECT trả về cùng một số cột theo cùng thứ tự với các cột của bảng đích và chúng cũng có kiểu dữ liệu phù hợp tương thích, bạn có thể bỏ qua danh sách cột khỏi INSERT INTO :

INSERT INTO showroomEmployees 
SELECT 
factoryEmployees.name, 
factoryEmployees.hourlyWage, 
factoryEmployees.startDate 
FROM factoryEmployees 
WHERE name = 'Agnes'; 

Output
Query OK, 1 row affected (0.01 sec) Records: 1  Duplicates: 0  Warnings: 0 

Lưu ý : Các cột được liệt kê trong truy vấn SELECT của thao tác này được đặt trước tên bảng factoryEmployees và một factoryEmployees . Khi bạn chỉ định tên bảng khi tham chiếu đến một cột như thế này, nó được gọi là tham chiếu cột đủ điều kiện . Điều này không cần thiết trong trường hợp cụ thể này. Trên thực tế, INSERT INTO ví dụ sau sẽ tạo ra kết quả giống như câu trước:

INSERT INTO showroomEmployees 
SELECT 
name, 
hourlyWage, 
startDate 
FROM factoryEmployees 
WHERE name = 'Agnes'; 

Các ví dụ trong phần này sử dụng tham chiếu cột đủ điều kiện để làm rõ ràng, nhưng làm như vậy có thể là một thói quen tốt để thực hành. Chúng không chỉ có thể giúp làm cho SQL của bạn dễ hiểu hơn và khắc phục sự cố, các tham chiếu cột đủ điều kiện trở nên cần thiết trong các hoạt động nhất định tham chiếu đến nhiều hơn một bảng, chẳng hạn như truy vấn bao gồm mệnh đề JOIN .

Câu SELECT trong thao tác này bao gồm WHERE khiến truy vấn chỉ trả về các hàng từ bảng factoryEmployees có cột name chứa giá trị Agnes . Bởi vì chỉ có một hàng như vậy trong bảng nguồn, chỉ hàng đó sẽ được sao chép qua bảng showroomEmployees .

Để xác nhận điều này, hãy chạy truy vấn sau để trả về mọi bản ghi trong bảng Nhân viên showroomEmployees :

SELECT * FROM showroomEmployees; 

Output
+-------+------------+------------+ | name  | hourlyWage | startDate  | +-------+------------+------------+ | Agnes |      26.50 | 2017-05-01 | +-------+------------+------------+ 1 row in set (0.00 sec) 

Bạn có thể chèn nhiều hàng dữ liệu với bất kỳ truy vấn nào sẽ trả về nhiều hàng từ bảng nguồn. Ví dụ: truy vấn trong câu lệnh sau sẽ trả về mọi bản ghi trong database factoryEmployees trong đó giá trị trong cột name không bắt đầu bằng J :

INSERT INTO showroomEmployees 
SELECT 
factoryEmployees.name, 
factoryEmployees.hourlyWage, 
factoryEmployees.startDate 
FROM factoryEmployees 
WHERE name NOT LIKE 'J%'; 

Output
Query OK, 5 rows affected (0.01 sec) Records: 5  Duplicates: 0  Warnings: 0 

Chạy truy vấn này để trở lại mọi kỷ lục trong showroomEmployees bảng:

SELECT * FROM showroomEmployees; 

+--------+------------+------------+ | name   | hourlyWage | startDate  | +--------+------------+------------+ | Agnes  |      26.50 | 2017-05-01 | | Agnes  |      26.50 | 2017-05-01 | | Harry  |      26.50 | NULL       | | Marie  |      27.88 | 2018-03-29 | | Giles  |      26.50 | 2019-08-06 | | Daphne |      32.45 | 2017-11-12 | +--------+------------+------------+ 6 rows in set (0.00 sec) 

Lưu ý có hai hàng giống hệt nhau với Agnes trong cột tên. Mỗi khi bạn chạy INSERT INTO sử dụng SELECT , SQL sẽ xử lý kết quả từ truy vấn như một tập dữ liệu mới. Trừ khi bạn áp đặt một số ràng buộc nhất định lên bảng của bạn hoặc phát triển các truy vấn chi tiết hơn, không có gì ngăn database của bạn bị tải với các bản ghi trùng lặp khi thêm dữ liệu như thế này.

Chèn dữ liệu tự động

Khi tạo bảng, bạn có thể áp dụng các thuộc tính nhất định cho các cột sẽ khiến RDBMS tự động điền dữ liệu vào chúng.

Để minh họa, hãy chạy câu lệnh sau để xác định một bảng có tên là các interns . Thao tác này sẽ tạo một bảng có tên các interns có ba cột. Cột đầu tiên trong ví dụ này, internID , chứa dữ liệu kiểu int . Tuy nhiên, hãy lưu ý nó cũng bao gồm thuộc tính AUTO_INCREMENT . Thuộc tính này sẽ khiến SQL tự động tạo một giá trị số duy nhất cho mọi hàng mới, bắt đầu bằng 1 theo mặc định và sau đó tăng dần lên với mỗi bản ghi tiếp theo.

Tương tự, cột thứ hai, department , bao gồm từ khóa DEFAULT . Điều này sẽ khiến RDBMS tự động chèn giá trị mặc định - 'production' trong ví dụ này nếu bạn bỏ qua department khỏi danh sách cột của INSERT INTO :

CREATE TABLE interns ( 
internID int AUTO_INCREMENT PRIMARY KEY, 
department varchar(20) DEFAULT 'production', 
name varchar(30) 
); 

Lưu ý : Thuộc tính AUTO_INCREMENT là một tính năng dành riêng cho MySQL, nhưng nhiều RDBMS có phương thức riêng để tăng số nguyên. Để hiểu rõ hơn về cách RDBMS của bạn quản lý việc tăng dần tự động, bạn nên tham khảo tài liệu chính thức của nó.

Đây là tài liệu chính thức về chủ đề này cho một số database nguồn mở phổ biến:

• MySQL AUTO_INCREMENT Tài liệu thuộc tính
• Tài liệu về kiểu dữ liệu serial PostgreSQL
• Tài liệu về Từ khoá Autoincrement SQLite

Để chứng minh các tính năng này, hãy tải bảng interns với một số dữ liệu bằng cách chạy INSERT INTO sau đây. Thao tác này chỉ xác định các giá trị cho cột name :

INSERT INTO interns (name) VALUES ('Pierre'), ('Sheila'), ('Francois'); 

Output
Query OK, 3 rows affected (0.01 sec) Records: 3  Duplicates: 0  Warnings: 0 

Sau đó, chạy truy vấn này để trả về mọi bản ghi từ bảng:

SELECT * FROM interns; 

Output
+----------+------------+----------+ | internID | department | name     | +----------+------------+----------+ |        1 | production | Pierre   | |        2 | production | Sheila   | |        3 | production | Francois | +----------+------------+----------+ 3 rows in set (0.00 sec) 

Đầu ra này cho biết do định nghĩa của các cột, INSERT INTO trước đó đã thêm các giá trị vào cả internID và department mặc dù chúng không được chỉ định.

Để thêm một giá trị khác với giá trị mặc định vào cột department bạn cần chỉ định cột đó trong INSERT INTO , như sau:

INSERT INTO interns (name, department) 
VALUES 
('Jacques', 'management'), 
('Max', 'quality assurance'), 
('Edith', 'management'), 
('Daniel', DEFAULT); 

Output
Query OK, 4 rows affected (0.00 sec) Records: 4  Duplicates: 0  Warnings: 0 

Lưu ý hàng giá trị cuối cùng được cung cấp trong ví dụ này bao gồm từ khóa DEFAULT thay vì giá trị chuỗi. Điều này sẽ khiến database chèn giá trị mặc định ( 'production' ):

SELECT * FROM interns; 

Output
+----------+-------------------+----------+ | internID | department        | name     | +----------+-------------------+----------+ |        1 | production        | Pierre   | |        2 | production        | Sheila   | |        3 | production        | Francois | |        4 | management        | Jacques  | |        5 | quality assurance | Max      | |        6 | management        | Edith    | |        7 | production        | Daniel   | +----------+-------------------+----------+ 7 rows in set (0.00 sec) 

Kết luận

Bằng cách đọc hướng dẫn này, bạn đã học được một số cách khác nhau để chèn dữ liệu vào bảng, bao gồm việc chỉ định các hàng dữ liệu riêng lẻ với từ khóa VALUES , sao chép toàn bộ tập dữ liệu bằng truy vấn SELECT và xác định các cột mà SQL sẽ tự động chèn dữ liệu vào.

Các lệnh được nêu ở đây sẽ hoạt động trên bất kỳ hệ quản trị database nào sử dụng SQL. Lưu ý mọi database SQL sử dụng cách triển khai ngôn ngữ duy nhất của riêng nó, vì vậy bạn nên tham khảo tài liệu chính thức của DBMS để có mô tả đầy đủ hơn về cách nó xử lý INSERT INTO và những tùy chọn nào có sẵn cho nó.

Nếu bạn muốn tìm hiểu thêm về cách làm việc với SQL, ta khuyến khích bạn xem các hướng dẫn khác trong loạt bài này về Cách sử dụng SQL .

---

---

Các tin liên quan