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

第 5 章 DML (Data Manipulation Language)

目录

5.1. INSERT 插入
5.1.1. INSERT INTO ... SELECT
5.1.2. INSERT IGNORE
5.1.3. INSERT...ON DUPLICATE KEY UPDATE
5.2. REPLACE 替换
5.3. DELETE 删除
5.3.1. 删除重复数据
5.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
	
	

5.1. INSERT 插入

5.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;
			
			

5.1.2. INSERT IGNORE

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

			
insert ignore into table(name)  select  name from table2
			
			

5.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 ;