Mysql-数据操作详解-增删改


表结构样例

DROP TABLE IF EXISTS `employee`;
CREATE TABLE `employee` (
  `id` bigint NOT NULL AUTO_INCREMENT,
  `mobile` varchar(20) DEFAULT NULL COMMENT '手机号',
  `name` varchar(32) DEFAULT NULL COMMENT '姓名',
  `gender` char(2) DEFAULT '未知' COMMENT '性别',
  `age` tinyint DEFAULT '0' COMMENT '年龄',
  `dept` varchar(32) DEFAULT NULL COMMENT '部门',
  `post` varchar(32) DEFAULT NULL COMMENT '岗位',
  `salary` decimal(6,2) DEFAULT '0.00' COMMENT '薪资',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='雇员表';

INSERT基本语法

INSERT VALUES 的语法格式为:

INSERT INTO <table> ( <field1> , … ,<fieldN> )
VALUES (<value1>,… , <valueN>) ;

语法说明:
<table>:指定被操作的表名。
<field>:指定需要插入数据的列名。
<value>: 要插入的数据清单, 数据清单中数据的顺序要和列的顺序相对应。

INSERT INTO `employee` (`mobile`, `name`, `gender`, `age`, `dept`, `post`, `salary`) 
VALUES ( '13101010001', '张三', '男', 24, '市场部', '市场总监', 8888.88);
-- 插入全列字段,列表可省略
INSERT INTO `employee`  VALUES (NULL,  '13101020001', '张三丰', '男', 30, '市场部', '市场总监', 9999.99);

INSERT SET 的语法格式为:

INSERT INTO <table>
SET <field1> = <value1>,… , <fieldN> = <valueN> ;
语法说明:
<table>:指定被操作的表名。
<field>:指定需要插入数据的列名。
<value>: 要插入的字段对应的数据。

INSERT INTO `employee` 
SET mobile = '13101010002', name = '李四', gender = '女', dept = '销售部', post = '销售顾问';

INSERT INTO … SELECT ... FROM 的语法格式为:

INSERT INTO <table> ( <field1> , … ,<fieldN> )
SELECT <field1> , … ,<fieldN> FROM <table_2>;
语法说明:
<table>:指定被操作的表名。
<field>:指定需要插入数据的列名。
SELECT .... FROM <table_2>: 从一个或多个表中取出数据,结果集中的每行数据的字段数、字段的数据类型都必须与被操作的表完全一致

INSERT INTO `employee_new`   (`id`, `mobile`, `name`, `gender`, `age`, `dept`, `post`, `salary`)
SELECT `id`, `mobile`, `name`, `gender`, `age`, `dept`, `post`, `salary` FROM `employee`;
INSERT INTO `employee_new` SELECT * FROM `employee`;

SELECT 子句返回的是一个查询到的结果集,INSERT 语句将这个结果集插入指定表中,结果集中的每行数据的字段数、字段的数据类型都必须与被操作的表完全一致。

CREATE TABLE employee_new LIKE employee;

UPDATE基本语法

UPDATE SET 的语法格式为:

UPDATE <table>
SET <field1> = <value1>,… , <fieldN> = <valueN>  WHERE <expression>;
语法说明:
<table>:指定被操作的表名。
<field>:指定需要更新数据的列名。
<value>: 待修改字段的数据值。
WHERE <expression>: 条件表达式

UPDATE employee SET age = 50 WHERE `name` = '王二';

批量更新:
[方法1] REPLACE INTO :
操作本质是对重复的记录先delete 后insert,如果更新的字段不全会将缺失的字段置为缺省值。(*慎用)

REPLACE INTO employee (id, mobile, name, gender, age)
VALUES
(1, '13101010001', '张三', '男',35),
(2, '13101010005', 'TOM', '女',22),
(3, '13101010006', 'JACK', '未知',31);

[方法2] INSERT INTO ...ON DUPLICATE KEY UPDATE:
不存在则插入,存在时只update重复记录,不会改变其它字段。

INSERT INTO employee (id, mobile, name, gender, age)
VALUES
(1, '13101010001', '张三', '男',35),
(2, '13101010005', 'TOM Sing', '女',22),
(6, '13101010016', 'JACK Chow', '未知',66)
ON DUPLICATE KEY UPDATE
name = VALUES(name),
gender = VALUES(gender);

[方法3]MySql 自带语句:

UPDATE <table>
SET <field1> = CASE id WHEN <id1> THEN <value1> END ,… , <fieldN> = <valueN>  
WHERE id IN (<id1>, ... ,<idN>);
语法说明:
<table1>:指定被操作的表名。
<field>:指定需要更新数据的列名。
<value>: 待修改字段的数据值。

UPDATE employee SET age = CASE id WHEN 1 THEN 50  WHEN 2 THEN 33 ELSE 22 END
WHERE id IN (1,2,3);

[方法4] 利用备份表更新:
在数据迁移中,常用到该方法操作数据。 (*字符集需保持一致)

UPDATE employee e,employee_new ew
SET e.gender = ew.gender, e.age = ew.age, ew.dept = e.dept
WHERE e.id = ew.id;

DELETE基本语法

DELETE 的语法格式为:

DELETE FROM <table> WHERE <expression>;
语法说明:
<table>:指定被操作的表名。
<expression>: 条件表达式,当表达式为真(true)时,则删除数据。

DELETE FROM employee WHERE id = 6;

TRUNCATE基本语法

TRUNCATE 的语法格式为:

TRUNCATE TABLE <table>;
语法说明:
<table>:指定被操作的表名。

TRUNCATE TABLE employee;

truncate与delete的区别

声明:微默网|版权所有,违者必究|如未注明,均为原创|本网站采用BY-NC-SA协议进行授权

转载:转载请注明原文链接 - Mysql-数据操作详解-增删改


不以物喜,不以己悲! 不忘初心,方得始终!