SQL语言INSERT语句如何添加记录 SQL语言最基础的数据插入操作

INSERT INTO table_name (column1, column2, column3, ...)
VALUES (value1, value2, value3, ...);

table_name

column1, column2, ...

value1, value2, ...

INSERT INTO table_name
VALUES (value1, value2, value3, ...);

INSERT INTO Students (StudentID, Name, Age, Major)
VALUES (101, '张三', 20, '计算机科学');

INSERT INTO Students (StudentID, Name, Major)
VALUES (102, '李四', '软件工程');

VALUES

INSERT INTO Students (StudentID, Name, Age, Major)
VALUES
    (103, '王五', 22, '电子工程'),
    (104, '赵六', 21, '数据科学'),
    (105, '钱七', 23, '市场营销');

INSERT INTO ... SELECT

INSERT INTO NewStudents (StudentID, Name, Major)
SELECT StudentID, Name, Major
FROM Students
WHERE Age > 21;

NewStudents

WHERE

JOIN

GROUP BY

INSERT ... ON DUPLICATE KEY UPDATE

UPDATE

INSERT INTO Products (ProductID, ProductName, Price, Stock)
VALUES (1, '笔记本电脑', 8000.00, 100)
ON DUPLICATE KEY UPDATE
    ProductName = VALUES(ProductName),
    Price = VALUES(Price),
    Stock = Stock + VALUES(Stock); -- 如果冲突，库存累加

VALUES(column_name)

INSERT ... ON CONFLICT DO UPDATE

UPSERT

INSERT INTO Users (id, name, email)
VALUES (1, 'Alice', 'alice@example.com')
ON CONFLICT (id) DO UPDATE SET
    name = EXCLUDED.name,
    email = EXCLUDED.email;

ON CONFLICT (id)

id

DO UPDATE SET

EXCLUDED

MERGE INTO TargetTable AS T
USING SourceTable AS S
ON T.ID = S.ID
WHEN MATCHED THEN
    UPDATE SET T.Name = S.Name, T.Value = S.Value
WHEN NOT MATCHED THEN
    INSERT (ID, Name, Value) VALUES (S.ID, S.Name, S.Value);

INSERT OR REPLACE

OR REPLACE

INSERT OR REPLACE INTO Config (KeyName, Value)
VALUES ('LogLevel', 'DEBUG');

''

0

NOT NULL

NULL

-- 显式插入NULL
INSERT INTO Employees (EmployeeID, Name, DepartmentID)
VALUES (1, '张三', NULL);

-- 不指定DepartmentID，如果该列允许NULL且没有默认值，则会是NULL
INSERT INTO Employees (EmployeeID, Name)
VALUES (2, '李四');

WHERE DepartmentID IS NULL

WHERE DepartmentID = NULL

-- 创建表时定义默认值
CREATE TABLE Orders (
    OrderID INT PRIMARY KEY,
    OrderDate DATETIME DEFAULT CURRENT_TIMESTAMP, -- 默认值为当前时间
    Status VARCHAR(50) DEFAULT 'Pending'
);

-- 插入数据时，OrderDate和Status将自动填充默认值
INSERT INTO Orders (OrderID)
VALUES (1001);

'Pending'

-- 显式提供值，覆盖默认值
INSERT INTO Orders (OrderID, Status)
VALUES (1002, 'Completed');

'Completed'