你可能不知道的mysql语句

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;