Home | 简体中文 | 繁体中文 | 杂文 | Github | 知乎专栏 | Facebook | Linkedin | Youtube | 打赏(Donations) | About
知乎专栏

第 23 章 DML (Data Manipulation Language)

目录

23.1. INSERT 插入
23.1.1. INSERT INTO ... SELECT
23.1.2. INSERT IGNORE
23.1.3. INSERT...ON DUPLICATE KEY UPDATE
23.2. REPLACE 替换
23.3. DELETE 删除
23.3.1. 删除重复数据
23.3.2. 删除重复数据保留第一条
	
SELECT - retrieve data from the a database
INSERT - insert data into a table
UPDATE - updates existing data within a table
DELETE - deletes all records from a table, the space for the records remain
CALL - call a PL/SQL or Java subprogram
EXPLAIN PLAN - explain access path to data
LOCK TABLE - control concurrency
	
	

23.1. INSERT 插入

23.1.1. INSERT INTO ... SELECT

			
SET @OLDTMP_SQL_MODE=@@SQL_MODE, SQL_MODE='';
DELIMITER //
CREATE TRIGGER `members_mobile_insert` BEFORE INSERT ON `members_mobile` FOR EACH ROW BEGIN
	insert into members_location(id,province,city) select NEW.id,mobile_location.province,mobile_location.city from  mobile_location where mobile_location.id = md5(LEFT(NEW.number, 7));
END//
DELIMITER ;
SET SQL_MODE=@OLDTMP_SQL_MODE;
			
			

23.1.2. INSERT IGNORE

INSERT IGNORE 与INSERT INTO的区别就是INSERT IGNORE会忽略数据库中已经存在 的数据,如果数据库没有数据,就插入新的数据,如果有数据的话就跳过这条数据。

			
insert ignore into table(name)  select  name from table2
			
			

23.1.3. INSERT...ON DUPLICATE KEY UPDATE

插入 Neo, 如果数据不存在就插入,存在就更新 weight 字段,数值+1

			
INSERT INTO user_persona (device_id, hobby,weight) VALUES ((SELECT id FROM device where sn='test')  , 'Neo', 1) ON DUPLICATE KEY UPDATE weight = weight + 1;			
			
			
			
create table foo (id serial primary key, u int, unique key (u));

insert into foo (u) values (10);
insert into foo (u) values (10) on duplicate key update u = 20;

mysql> select * from foo;
+----+------+
| id | u    |
+----+------+
|  1 |   20 |
+----+------+
			
			
			
DROP TRIGGER IF EXISTS `cms`.`jc_content_BEFORE_DELETE`;

DELIMITER $$
USE `cms`$$
CREATE DEFINER=`5kwords`@`%` TRIGGER `jc_content_BEFORE_DELETE` BEFORE DELETE ON `jc_content`
FOR EACH ROW BEGIN

    insert into `cms`.elasticsearch_trash(id) values(OLD.content_id) on duplicate key update ctime = now();
    insert into `cms`.trash(id,`type`, site_id) values(OLD.content_id, "delete", OLD.site_id) on duplicate key update `type`="delete", ctime = now();

END$$
DELIMITER ;