数据处理之增删改
插入数据(增)
前提:创建一个空表:id,name,hire_data,salary,
- 方法一:逐一添加数据
[En]
method 1: add data one by one*
1.没有指明添加的字段,*必须* 按照表中字段的顺序添加INSERT INTO emp1VALUES(1,'Tom','2000-12-03',2300);2.指明要添加的字段,表中字段可以不写全INSERT INTO emp1(id,hire_data,salary,name)VALUES(2,'1999-02-03',3500,'Petter');3.同时插入多条记录INSERT INTO emp1(id,hire_data,salary,name)VALUES(3,'2001-01-02',3000,'Jim'),(4,'1998-12-05',4000,'托尼');
- 将查询结果插入列表中
#查询的表的字段一定要与添加的字段 *一一对应*
#提前检查字段内容是否 *越界*,如果越界,有添加不成功的风险,则要修改
INSERT INTO emp1(id,hire_data,salary,name)
SELECT employee_id,hire_data,salary,name
FROM employees
WHERE department_id IN (60,70);
更新数据(改)
UPDATE … SET …WHERE
#修改一行数据
UPDATE emp1
SET hire_data = '1990-01-23'
WHERE id =4;
#修改多行数据
UPDATE emp1
SET salary = 5600,hire_data = '1996-02-24'
WHERE id =3;
修改时,有可能修改不成功(可能是受约束的原因)。
[En]
When modifying, it is possible that the modification is not successful (possibly caused by constraints).
也可以试一下输入
set sql_safe_updates =0;(执行前)
删除数据
DELETE FROM … WHERE
DELETE FROM emp1
WHERE id = 1;
删除时,可能是删除不成功(可能是受限制)
[En]
When deleting, it is possible that the deletion is not successful (possibly caused by constraints)
注:关于删除后是否会回滚,可以看我的上一篇文章:https://www.cnblogs.com/wht-de-bk/p/15982191.html 情况表和拓展一
MySQL8新特性:计算列
alter table emp1 #注意:最好用大写
add add
int GENERATED always as (id + salary) virtual;
Original: https://www.cnblogs.com/wht-de-bk/p/15986888.html
Author: T,a,o
Title: MySQL增删改
原创文章受到原创版权保护。转载请注明出处:https://www.johngo689.com/507768/
转载文章受原作者版权保护。转载请注明原作者出处!