MySQL中几种插入和批量语句实例详解

INSERT IGNORE INTO user (username,sex,address) VALUES ('hanpang','boy','HongKong')

INSERT IGNORE INTO user (username,sex,address) VALUES ('hanpang','boy','HongKong')
on duplicate key update
SEX='boy',address='HongKong'

REPLACE INTO user (username,sex,address) VALUES ('hanpang','boy','HongKong')

INSERT  INTO user (username,sex,address) 
SELECT 'hanpang','boy','HongKong' FROM user
WHERE NOT EXISTS (SELECT username FROM user WHERE username='hanpang')

CREATE TABLE example (
    example_id INT NOT NULL,
    name VARCHAR( 50 ) NOT NULL,
    value VARCHAR( 50 ) NOT NULL,
    other_value VARCHAR( 50 ) NOT NULL
)

INSERT INTO example
VALUES
(100, 'Name 1', 'Value 1', 'Other 1'),
(101, 'Name 2', 'Value 2', 'Other 2'),
(102, 'Name 3', 'Value 3', 'Other 3'),
(103, 'Name 4', 'Value 4', 'Other 4');

INSERT INTO example
VALUES
(100, 'Name 1', 'Value 1', 'Other 1'),
(101, 'Name 2', 'Value 2', 'Other 2'),
(102, 'Name 3', 'Value 3', 'Other 3'),
(103, 'Name 4', 'Value 4', 'Other 4');

INSERT INTO T(A,B,C,D) VALUES (a,b,c,d) ON DUPLICATE KEY UPDATE C=C+1，D=d 

UPDATE yoiurtable
	SET dingdan = CASE id 
		WHEN 1 THEN 3 
		WHEN 2 THEN 4
		WHEN 3 THEN 5 
		END
WHERE id IN (1,2,3)

create temporary table tmp(id int(4) primary key,dr varchar(50));
insert into tmp values  (0,'Gone'), (1,'xx'),...(m,'yy');
update test_tbl, tmp set test_tbl.dr=tmp.dr where test_tbl.id=tmp.id;