MYSQL-增、删、改

梅尝酥

已于 2023-03-10 23:02:23 修改

阅读量111

点赞数 2

分类专栏： MYSQL数据库文章标签： mysql 数据库 sql

于 2023-03-10 09:50:55 首次发布

本文链接：https://blog.csdn.net/qq_27584491/article/details/129435676

版权

MYSQL数据库专栏收录该内容

9 篇文章 0 订阅

订阅专栏

文章目录

INSERT：向表中插入记录

不指明列

INSERT INTO employees VALUES(1,'refine','2020-12-14',20000);

指明列

推荐使用指明列的方式插入记录

INSERT INTO employees (id,emp_name,hire_date) VALUES(2,'lucy','2006-05-06');

添加多条记录

INSERT INTO employees (id, emp_name, salary)
VALUES
	(3, 'tom', 8000),
	(4, 'jim', 28000),
	(5, 'wang', 80000);

将查询结果插入表中

-- 将查询结果插入到表中
INSERT INTO employees (id, emp_name, salary) SELECT
	employee_id,
	last_name,
	salary
FROM
	atguigudb.employees
WHERE
	department_id IN (20, 30, 40);

UPDATE：更新表记录

格式UPDATE … SET … WHERE …

更新一条记录或批量更新记录

UPDATE employees SET hire_date=NOW() WHERE id=3;
UPDATE employees SET hire_date=STR_TO_DATE('2008-11-05','%Y-%m-%d'),salary=3000 WHERE id=5;
UPDATE employees SET salary=salary+500 WHERE emp_name LIKE '%a%';

由于约束影响，导致更新失败

UPDATE atguigudb.employees
SET department_id = 10000
WHERE
	employee_id = 102;
/*
[Err] 1452 - 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`))

*/

DELETE：删除表记录

删除一条记录或批量删除记录

DELETE FROM employees WHERE id=202;

清空表数据

DELETE FROM employees;

由于约束影响，导致更新失败

DELETE
FROM
	atguigudb.departments
WHERE
	department_id = 50;

计算列

概念：某一列的值是通过别的列计算得来
MySQL不支持MySQL 5.7之前的计算列

-- 计算列
CREATE TABLE employees1(
id INT,
emp_name VARCHAR(15),
salary DOUBLE(10,2),
coefficient DOUBLE(10,2),
bonus DOUBLE(10,2) GENERATED ALWAYS AS (salary*bonus) VIRTUAL
);