1. delete和 update语句可以指定删除数量
DELETE FROM student WHERE age=10 order by id desc limit 1;
UPDATE student SET age = 11 WHERE age=10 order by id desc limit 1;
2.delete语句可以使用join删除多张表数据
DELETE student, subject FROM student INNER JOIN subject ON student.id = subject.studentid where student.id = 1;
3.查询并插入 insert ... select syntax
INSERT INTO a (id,name) select 1,'aa'
INSERT INTO a (id,name) select id,name from a
4. 当不存在记录时插入,否则更新记录 INSERT ... ON DUPLICATE KEY UPDATE Syntax
INSERT INTO a(id,last_time) values (1, '2015-01-01') ON DUPLICATE KEY UPDATE last_time = '2015-01-02'
5.从文本导入数据 LOAD DATA INFILE Syntax
可以指定数据格式,速度比insert 语句快20倍
6.使用临时变量 SELECT .. INTO val
select id,username INTO @id,@nickname from `juzi_admin_user` limit 1;
select @id,@nickname;