增(Create)
插入新记录到表中:
-- 插入单条记录
INSERT INTO table_name (column1, column2, column3, ...)
VALUES (value1, value2, value3, ...);
-- 插入多条记录
INSERT INTO table_name (column1, column2, column3, ...)
VALUES (value1a, value2a, value3a, ...),
(value1b, value2b, value3b, ...),
(value1c, value2c, value3c, ...);
删(Delete)
从表中删除记录:
-- 删除满足条件的记录
DELETE FROM table_name WHERE condition;
-- 删除表中的所有记录
DELETE FROM table_name;
改(Update)
更新表中的记录:
-- 更新满足条件的记录
UPDATE table_name
SET column1 = value1, column2 = value2, ...
WHERE condition;
查(Retrieve)
从表中检索记录:
-- 查询所有列
SELECT * FROM table_name;
-- 查询特定列
SELECT column1, column2, ... FROM table_name;
-- 带条件的查询
SELECT column1, column2, ... FROM table_name WHERE condition;
-- 排序查询结果
SELECT column1, column2, ... FROM table_name ORDER BY column1 ASC|DESC;
-- 聚合查询
SELECT COUNT(column1), SUM(column2), AVG(column3), ... FROM table_name WHERE condition;
-- 连接查询
SELECT table1.column, table2.column FROM table1
INNER JOIN table2 ON table1.common_field = table2.common_field;
示例
插入示例
INSERT INTO employees (first_name, last_name, age)
VALUES ('John', 'Doe', 30);
更新示例
UPDATE employees
SET age = 31
WHERE first_name = 'John' AND last_name = 'Doe';
查询示例
-- 查询所有员工
SELECT * FROM employees;
-- 查询年龄大于30的员工
SELECT first_name, last_name FROM employees WHERE age > 30;
-- 查询员工并按年龄排序
SELECT first_name, last_name, age FROM employees ORDER BY age DESC;