【数据库设计和SQL基础语法】--表的创建与操作--插入、更新和删除数据

一、插入数据

1.1 INSERT INTO语句基本语法

INSERT INTO语句是用于向数据库表中插入新记录的SQL语句。其基本语法如下：

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

• table_name: 要插入数据的目标表的名称。
• (column1, column2, column3, ..., columnN): 可选项，要插入数据的特定列，如果省略，则默认为表中所有列。
• VALUES (value1, value2, value3, ..., valueN): 插入的具体数值，按照列的顺序对应。

示例：

INSERT INTO employees (employee_id, first_name, last_name, hire_date, salary, department_id) 
VALUES (1, 'John', 'Doe', '2023-01-01', 50000, 101);

在这个例子中，数据将被插入名为employees的表，具体数值分别对应该表的各个列。

1.2 插入单行数据

插入单行数据时，使用INSERT INTO语句的基本语法，指定目标表的列和相应的数值。以下是一个插入单行数据的示例：

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

示例：

INSERT INTO students (student_id, first_name, last_name, age, class) 
VALUES (1, 'Alice', 'Smith', 20, 'Math101');

在这个例子中，数据将被插入名为students的表，指定了student_id, first_name, last_name, age, class这些列的数值。

1.3 插入多行数据

插入多行数据时，可以使用单个INSERT INTO语句并提供多个值集。以下是插入多行数据的基本语法：

INSERT INTO table_name (column1, column2, column3, ..., columnN)  
VALUES
    (value1_1, value2_1, value3_1, ..., valueN_1),
    (value1_2, value2_2, value3_2, ..., valueN_2),
    ...,
    (value1_M, value2_M, value3_M, ..., valueN_M);

示例：

INSERT INTO students (student_id, first_name, last_name, age, class) 
VALUES
    (1, 'Alice', 'Smith', 20, 'Math101'),
    (2, 'Bob', 'Johnson', 22, 'English201'),
    (3, 'Charlie', 'Brown', 21, 'History301');

在这个例子中，多个值集被提供，每个值集对应一行数据，插入到名为students的表中。

1.4 插入特定列的数据

要插入特定列的数据，可以在INSERT INTO语句中指定要插入的列，然后提供相应的值。以下是基本语法：

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

示例：

INSERT INTO employees (employee_id, first_name, last_name, hire_date)
VALUES (101, 'John', 'Doe', '2023-01-15');

在这个例子中，数据插入了employees表的特定列（employee_id, first_name, last_name, hire_date）。确保提供的值与指定的列相匹配。如果省略列名，则假定值按照表中列的顺序进行插入。

1.5 插入默认值

要插入默认值，可以使用DEFAULT关键字或者不提供列名和对应的值，让数据库使用默认值。以下是两种方法的示例：

1. 使用 DEFAULT 关键字：

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

示例：

INSERT INTO employees (employee_id, first_name, last_name, hire_date, salary) 
VALUES (102, 'Jane', 'Smith', DEFAULT, DEFAULT);

在这个例子中，hire_date和 salary 列将被设置为它们在表定义中指定的默认值。

1. 不提供列名和对应的值：

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

示例：

INSERT INTO employees
VALUES (103, 'Bob', 'Johnson', '2023-02-20', 60000.00);

在这个例子中，假设 employees 表的定义包括 DEFAULT 值，如果某些列没有提供值，将使用其默认值。

二、更新数据

2.1 UPDATE语句基本语法

UPDATE 语句用于修改表中的数据。其基本语法如下：

UPDATE table_name 
SET column1 = value1, column2 = value2, ...
WHERE condition;

• table_name: 要更新的表的名称。
• column1, column2, …: 要更新的列名。
• value1, value2, …: 新值。
• WHERE condition: 指定要更新的行的条件。如果省略 WHERE 子句，则将更新表中所有行。

示例：

UPDATE employees
SET salary = 65000.00
WHERE employee_id = 101;

这个示例将 employees 表中 employee_id 为 101 的员工的工资更新为 65000.00。 你也可以同时更新多列：

UPDATE employees
SET salary = 70000.00, department_id = 2
WHERE employee_id = 102;

这个示例将 employee_id 为 102 的员工的工资更新为 70000.00，并将部门编号更新为 2。

2.2 更新整个表的数据

如果你想更新整个表的数据，可以使用不带 WHERE 子句的 UPDATE 语句。例如：

UPDATE employees
SET department_id = 3;

这个示例将 employees 表中所有行的 department_id 列的值都更新为 3。请小心使用这样的语句，确保你确实想要更新整个表的所有行。

2.3 更新特定行的数据

要更新特定行的数据，你需要使用带有 WHERE 子句的 UPDATE 语句，以指定要更新的行的条件。例如：

UPDATE employees 
SET department_id = 3
WHERE employee_id = 101;

这个示例将 employees 表中 employee_id 为 101 的员工的 department_id 更新为 3。通过在 WHERE 子句中指定条件，你可以确保只有符合条件的行会被更新。

2.4 更新特定列的数据

要更新特定列的数据，你可以在 SET 子句中指定要更新的列及其新值。以下是一个示例：

UPDATE employees
SET salary = 60000, job_title = 'Manager'
WHERE employee_id = 102;

在这个例子中，employees 表中 employee_id 为 102 的员工的 salary 被更新为 60000，job_title 被更新为 ‘Manager’。通过在 WHERE 子句中指定条件，确保只有符合条件的行会被更新。

2.5 使用条件进行更新

使用条件进行更新是非常常见的需求，可以通过 WHERE 子句来实现。以下是一个示例：

UPDATE students
SET grade = 'A'
WHERE score > 90;

在这个例子中，students 表中所有分数大于90的学生的成绩将被更新为 ‘A’。通过在 WHERE 子句中添加条件，你可以精确指定哪些行应该被更新。 请确保在更新操作中使用条件，以免无意中更新了整个表的数据。

三、删除数据

3.1 DELETE语句基本语法

DELETE 语句用于删除表中的记录。以下是基本的语法：

DELETE FROM table_name
WHERE condition;

• table_name: 要删除记录的表名。
• condition: 指定要删除的记录的条件。如果省略 WHERE 子句，将删除表中的所有记录。

以下是一些示例：

1. 删除整个表的所有记录：

DELETE FROM table_name;

1. 删除满足特定条件的记录：

DELETE FROM students
WHERE score < 60;

在这个例子中，删除 students 表中分数低于60的学生记录。

1. 删除表中的所有记录（谨慎使用）：

DELETE FROM table_name;

在执行删除操作时，请务必小心，并确保你真的想要删除的是指定的记录，避免不必要的数据丢失。

3.2 删除整个表的数据

要删除整个表的数据，可以使用以下 DELETE 语句：

DELETE FROM table_name;

这将从指定的表中删除所有记录，但保留表的结构。谨慎使用这个语句，因为它会删除表中的所有数据，而不仅仅是某些条件下的数据。在执行此操作之前，请确保你理解其影响，并且已经备份了重要的数据。

3.3 删除特定行的数据

要删除特定行的数据，可以使用 DELETE 语句并结合 WHERE 子句来指定删除的条件。以下是一个基本示例：

DELETE FROM table_name
WHERE condition;

在这里，table_name 是你要删除数据的表的名称，condition 是一个条件，用于指定要删除的行。例如，如果要删除表中满足某个条件的所有员工记录，可以这样写：

DELETE FROM employees
WHERE department_id = 3;

这将删除 employees 表中 department_id 为 3 的所有员工记录。确保在执行 DELETE 语句之前，你已经确认删除条件，以免删除了不需要删除的数据。

3.4 使用条件进行删除

使用条件进行删除是通过在 DELETE 语句中添加 WHERE 子句来实现的。WHERE 子句允许你指定删除的条件，只有满足条件的行才会被删除。以下是一个示例：

DELETE FROM table_name
WHERE condition;

在这里，table_name 是要删除数据的表的名称，而 condition 是一个用于过滤要删除行的条件。例如，如果你想删除员工表中薪水低于 50000 的所有记录，可以这样写：

DELETE FROM employees  
WHERE salary < 50000;

这将删除 employees 表中所有薪水低于 50000 的员工记录。确保在执行 DELETE 语句之前，你已经仔细考虑并确认删除条件，以免误删重要数据。

3.5 删除表的注意事项

在删除表时，有一些重要的注意事项需要考虑，以避免意外数据损失或其他潜在问题：

1. 备份数据： 在删除表之前，务必先备份表中的数据。这样，如果删除后发现有误，可以从备份中还原数据。
2. 依赖关系： 检查该表是否有外键关联到其他表，或者其他表是否依赖于该表。如果有，需要考虑解除依赖关系或采取适当的措施，以免破坏数据完整性。
3. 索引和约束： 删除表可能会导致与该表相关的索引和约束失效。在删除表之前，应该考虑删除相关的索引和约束，或者在删除后重新创建它们。
4. 事务处理： 如果在删除表的操作中使用了事务，请确保事务的处理符合预期。在事务中删除表，如果事务被回滚，可能导致表未被删除，但是其他事务中的修改已经生效。
5. 权限： 确保执行删除表的用户具有足够的权限。通常，删除表需要数据库管理员或具有足够权限的用户执行。
6. 关联视图和存储过程： 如果有与表关联的视图或存储过程，删除表可能会影响到这些对象。在删除表之前，需要检查和更新相关的视图和存储过程。
7. 数据库引擎差异： 不同的数据库管理系统可能对删除表的语法和行为有所不同。确保你使用的语法符合数据库引擎的要求。
8. 数据完整性： 删除表会导致表中的所有数据丢失。确保在执行删除操作之前，你不再需要表中的数据或已经备份了需要的数据。
9. 系统负载： 在高负载的生产环境中，删除大型表可能会影响数据库性能。最好在低负载时执行这样的操作，以减少对系统的影响。

综合考虑这些注意事项，并在删除表之前仔细检查和计划，可以帮助确保操作的安全性和可靠性。

四、示例

4.1 插入数据的实例

以下是一个插入数据的SQL实例，假设我们有一个名为students的表，该表用于存储学生的信息：

-- 创建学生表
CREATE TABLE students ( 
    student_id INT PRIMARY KEY,
    first_name VARCHAR(50),
    last_name VARCHAR(50),
    age INT,
    class VARCHAR(20)
);

-- 插入单行数据
INSERT INTO students (student_id, first_name, last_name, age, class)
VALUES (1, 'John', 'Doe', 20, 'Mathematics');

-- 插入多行数据
INSERT INTO students (student_id, first_name, last_name, age, class)
VALUES (2, 'Jane', 'Smith', 22, 'History'),
       (3, 'Bob', 'Johnson', 21, 'Physics'),
       (4, 'Alice', 'Williams', 19, 'Chemistry');

在这个例子中：

• 我们首先创建了一个名为students的表，定义了学生的各种属性，包括学生ID、姓名、年龄和所在班级。
• 然后，使用INSERT INTO语句插入了单行数据，表示一个学生的信息。
• 接着，使用INSERT INTO语句插入了多行数据，表示多个学生的信息。

这是一个简单的插入数据的例子，通过类似的方式，你可以插入适合你表结构的实际数据。

4.2 更新数据的实例

以下是一个更新数据的SQL实例，假设我们有一个名为students的表，该表用于存储学生的信息：

-- 创建学生表
CREATE TABLE students (    
    student_id INT PRIMARY KEY,
    first_name VARCHAR(50),
    last_name VARCHAR(50),
    age INT,
    class VARCHAR(20)
);

-- 插入一些学生数据
INSERT INTO students (student_id, first_name, last_name, age, class)
VALUES (1, 'John', 'Doe', 20, 'Mathematics'),
       (2, 'Jane', 'Smith', 22, 'History'),
       (3, 'Bob', 'Johnson', 21, 'Physics'),
       (4, 'Alice', 'Williams', 19, 'Chemistry');

-- 更新整个表的数据（将所有学生的年龄增加1）
UPDATE students
SET age = age + 1;

-- 更新特定行的数据（将学生ID为2的学生的班级改为'Computer Science'）
UPDATE students
SET class = 'Computer Science'
WHERE student_id = 2;

-- 更新特定列的数据（将学生ID为3的学生的姓氏改为'Miller'）
UPDATE students
SET last_name = 'Miller'
WHERE student_id = 3;

在这个例子中：

• 我们首先创建了一个名为students的表，定义了学生的各种属性，包括学生ID、姓名、年龄和所在班级。
• 然后，使用INSERT INTO语句插入了一些学生的信息。
• 使用UPDATE语句更新了整个表的数据，将所有学生的年龄增加了1岁。
• 使用UPDATE语句更新了特定行的数据，将学生ID为2的学生的班级改为’Computer Science’。
• 使用UPDATE语句更新了特定列的数据，将学生ID为3的学生的姓氏改为’Miller’。

这是一个简单的更新数据的例子，通过类似的方式，你可以根据实际需求更新表中的数据。

4.3 删除数据的实例

以下是一个删除数据的SQL实例，假设我们有一个名为students的表，该表用于存储学生的信息：

-- 创建学生表
CREATE TABLE students (     
    student_id INT PRIMARY KEY,
    first_name VARCHAR(50),
    last_name VARCHAR(50),
    age INT,
    class VARCHAR(20)
);

-- 插入一些学生数据
INSERT INTO students (student_id, first_name, last_name, age, class)
VALUES (1, 'John', 'Doe', 20, 'Mathematics'),
       (2, 'Jane', 'Smith', 22, 'History'),
       (3, 'Bob', 'Johnson', 21, 'Physics'),
       (4, 'Alice', 'Williams', 19, 'Chemistry');

-- 删除整个表的数据
DELETE FROM students;

-- 插入更多学生数据
INSERT INTO students (student_id, first_name, last_name, age, class)
VALUES (5, 'Eva', 'Brown', 23, 'Computer Science'),
       (6, 'Chris', 'Jones', 20, 'Biology');

-- 删除特定行的数据（删除学生ID为3的学生）
DELETE FROM students
WHERE student_id = 3;

-- 删除特定条件下的数据（删除年龄小于 21 岁的学生）
DELETE FROM students
WHERE age < 21;

在这个例子中：

• 我们首先创建了一个名为students的表，定义了学生的各种属性，包括学生ID、姓名、年龄和所在班级。
• 然后，使用INSERT INTO语句插入了一些学生的信息。
• 使用DELETE FROM语句删除了整个表的数据。
• 通过再次使用INSERT INTO语句插入了更多学生的信息。
• 使用DELETE FROM语句删除了特定行的数据，具体是删除了学生ID为3的学生。
• 使用DELETE FROM语句删除了满足特定条件的数据，具体是删除了年龄小于21岁的学生。

这是一个简单的删除数据的例子，通过类似的方式，你可以根据实际需求删除表中的数据。

五、总结

在SQL中，插入数据使用INSERT INTO语句，可插入单行或多行数据，指定列和对应数值。更新数据使用UPDATE语句，可更新整个表、特定行或列，通过条件更新。删除数据使用DELETE语句，可删除整个表、特定行或满足条件的数据。谨慎操作删除，需备份数据、处理依赖关系、考虑权限等。通过实例，展示了插入、更新、删除数据的基本语法和常见用法。