MySQL基础-数据操作语言DML

MySQL基础-数据操作语言DML

一、插入数据

1.方式一：VALUES方式添加

使用这种语法一次只能向表中插入一条数据。值列表中需要为表的每一个字段指定值，并且值的顺序必须和数据表中字段定义时的顺序相同。

情况1：为表的所有字段按默认顺序插入数据

1
2
3
4
5
INSERT INTO 表名
VALUES (value1,value2,....);

INSERT INTO departments
VALUES (70, 'Pub', 100, 1700);

情况2: 指定字段名插入数据

为表的指定字段插入数据，就是在INSERT语句中只向部分字段中插入值，而其他字段的值为表定义时的默认值。 在 INSERT 子句中随意列出列名，但是一旦列出，VALUES中要插入的value1,….valueN需要与 column1,…columnN列一一对应。如果类型不同，MySQL会先尝试转型，如果无法转型则报错。

1
2
3
4
5
INSERT INTO 表名 (列名1，列名2...)
VALUES (value1,value2,....);

INSERT INTO departments(department_id, department_name)
VALUES (80, 'IT');

情况3：同时插入多条记录

INSERT语句可以同时向数据表中插入多条记录，插入时指定多个值列表，每个值列表之间用逗号分隔开。

1
2
3
4
5
6
7
8
9
10
11
12
13
INSERT INTO table_name
VALUES
(value1 [,value2, …, valuen]),
(value1 [,value2, …, valuen]),
……
(value1 [,value2, …, valuen]);

INSERT INTO table_name(column1 [, column2, …, columnn])
VALUES
(value1 [,value2, …, valuen]),
(value1 [,value2, …, valuen]),
……
(value1 [,value2, …, valuen]);

使用INSERT同时插入多条记录时，MySQL会返回一些在执行单行插入时没有的额外信息，这些信息的含义如下：

  • Records：表明插入的记录条数。
  • Duplicates：表明插入时被忽略的记录，原因可能是这些记录包含了重复的主键值。
  • Warnings：表明有问题的数据值，例如发生数据类型转换。

一个同时插入多行记录的INSERT语句等同于多个单行插入的INSERT语句，但是多行的INSERT语句在处理过程中效率更高。因为MySQL执行单条INSERT语句插入多行数据比使用多条INSERT语句快，所以在插入多条记录时最好选择使用单条INSERT语句的方式插入。注意：如果同时插入多行记录的INSERT语句是原子性的，其中一条数据插入异常会回滚之前插入的所有数据，MySQL还是很智能的！

2.方式二：将查询结果插入到表中

INSERT还可以将SELECT语句查询的结果插入到表中，此时不需要把每一条记录的值一个一个输入，只需要使用一条INSERT语句和一条SELECT语句组成的组合语句即可快速地从一个或多个表中向一个表中插入多行。

1
2
3
4
5
6
INSET INTO 目标表名
(tar_column1 [, tar_column2, ..., tar_columnn])
SELECT
(src_column1 [, src_column2, …, src_columnn])
FROM 源表名
[WHERE condition]

查询结果列名不一定要与目标表的列名保持一致，但是列数据类型的最好保持一致，因为如果数据类型不同并且目标表的对应列的数据类型的表示范围较小，有可能会发生数据溢出或精度损失导致转型失败，使得插入失败。

二、更新数据

1
2
3
UPDATE table_name
SET column1=value1, column2=value2, ..., column=valuen
[WHERE condition]

需要注意更新中的数据完整性错误，其实不仅仅是更新操作，插入和删除操作也是如此，都需要遵守数据表的约束条件如外键约束！例如外键约束中的child row不能更新为parent row中不存在的值、parent row一旦有child row引用后就不能随意删除。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
mysql> SHOW CREATE TABLE employees;
| employees | CREATE TABLE `employees` (
  `employee_id` int NOT NULL DEFAULT '0',
  `first_name` varchar(20) DEFAULT NULL,
  `last_name` varchar(25) NOT NULL,
  `email` varchar(25) NOT NULL,
  `phone_number` varchar(20) DEFAULT NULL,
  `hire_date` date NOT NULL,
  `job_id` varchar(10) NOT NULL,
  `salary` double(8,2) DEFAULT NULL,
  `commission_pct` double(2,2) DEFAULT NULL,
  `manager_id` int DEFAULT NULL,
  `department_id` int DEFAULT NULL,
  PRIMARY KEY (`employee_id`),
  UNIQUE KEY `emp_email_uk` (`email`),
  UNIQUE KEY `emp_emp_id_pk` (`employee_id`),
  KEY `emp_dept_fk` (`department_id`),
  KEY `emp_job_fk` (`job_id`),
  KEY `emp_manager_fk` (`manager_id`),
  CONSTRAINT `emp_dept_fk` FOREIGN KEY (`department_id`) REFERENCES `departments` (`department_id`),
  CONSTRAINT `emp_job_fk` FOREIGN KEY (`job_id`) REFERENCES `jobs` (`job_id`),
  CONSTRAINT `emp_manager_fk` FOREIGN KEY (`manager_id`) REFERENCES `employees` (`employee_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 ;
1 row in set (0.00 sec)

mysql> UPDATE employees SET department_id = 1000 WHERE department_id = 50;
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`atguigudb`.`employees`, CONSTRAINT `emp_dept_fk` FOREIGN KEY (`department_id`) REFERENCES `departments` (`department_id`))

mysql> UPDATE departments SET department_id = 1000 WHERE department_id = 50;
ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails (`atguigudb`.`employees`, CONSTRAINT `emp_dept_fk` FOREIGN KEY (`department_id`) REFERENCES `departments` (`department_id`))

三、删除数据

1
DELETE FROM table_name [WHERE <condition>];
区别点 DROP TRUNCATE DELETE
执行速度 快 较快 慢
命令分类 DDL（数据定义语言） DDL（数据定义语言） DML（数据操作语言）
删除对象 删除整张表和表结构，以及表的索引、约束和触发器。 只删除表数据，表的结构、索引、约束等会被保留。 只删除表的全部或部分数据，表结构、索引、约束等会被保留。
删除条件(where) 不能用 不能用 可使用
回滚 不可回滚 不可回滚 可回滚
自增初始值 - 重置 不重置

四、计算列

什么叫计算列呢？简单来说就是某一列的值是通过别的列计算得来的。例如，a列值为1、b列值为2，c列不需要手动插入，定义a+b的结果为c的值，那么c就是计算列，是通过别的列计算得来的。在MySQL 8.0中，CREATE TABLE 和 ALTER TABLE 中都支持增加计算列。下面以CREATE TABLE为例进行讲解。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
CREATE TABLE tb1(
a INT,
b INT,
c INT GENERATED ALWAYS AS (a + b) VIRTUAL
);

# 往含有计算列的表中插入数据时，不能指定计算列的值
mysql> INSERT tb1 VALUES(1,1);
ERROR 1136 (21S01): Column count doesn't match value count at row 1

mysql> INSERT tb1 VALUES(1,1,2);
ERROR 3105 (HY000): The value specified for generated column 'c' in table 'tb1' is not allowed.

mysql> INSERT tb1(a,b) VALUES(1,1);
Query OK, 1 row affected (0.01 sec)

mysql> SELECT * FROM tb1;
+------+------+------+
| a    | b    | c    |
+------+------+------+
|    1 |    1 |    2 |
+------+------+------+
1 row in set (0.00 sec)

mysql> UPDATE tb1 SET a = 5;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> SELECT * FROM tb1;
+------+------+------+
| a    | b    | c    |
+------+------+------+
|    5 |    1 |    6 |
+------+------+------+
1 row in set (0.00 sec)