MySQL SQL语法基础（insert、update、delete语句）

insert三种常规用法：

mysql> desc students;
+---------+-------------+------+-----+---------+----------------+
| Field   | Type        | Null | Key | Default | Extra          |
+---------+-------------+------+-----+---------+----------------+
| sid     | int         | NO   | PRI | NULL    | auto_increment |
| sname   | varchar(10) | YES  |     | NULL    |                |
| gender  | varchar(12) | YES  |     | NULL    |                |
| dept_id | int         | NO   | MUL | NULL    |                |
+---------+-------------+------+-----+---------+----------------+
4 rows in set (0.01 sec)

 1、insert into students values (1,'narisu','man',1);
 2、insert into students values (1,'narisu','man',1),(5,'ccc','man',1),(6,'eeee','man',2);  多个插入括号之间用逗号隔开
 3、insert into students set sid=3,sname='bbb',gender='feman',dept_id=1;
 4、insert into students select * from students_bak;

mysql> select *from students;
+-----+----------+--------+---------+
| sid | sname    | gender | dept_id |
+-----+----------+--------+---------+
|   1 | narisu   | man    |       1 |
|   2 | gutianle | man    |       2 |
|   3 | bbb      | feman  |       1 |
|   4 | ccc      | feman  |       2 |
+-----+----------+--------+---------+
4 rows in set (0.00 sec)

update常规用法：

1、update teacher set name='bbb' where id=10002; ##只更改id=10002的匹配行修改
2、update teacher set name='ccc' order by id limit 2;  ##只更改两条记录
3、update students set sname='abcd',gender='1' where sid=1;##根据sid=1作为一个匹配进行修改
4、update students,students2 set students.sname=students2.sname where students.sid=students2.sid; ##通过students2里面表中根据where条件匹配的值去更改对应students表里面的数据

1、单表修改是指修改指定单个表中的已经存在的数据的一个或多个列的数值；set短语后面要跟修改的列和值
2、where 子句表示限定要修改表中的哪些行数据，如果没有where子句则表示所有行都要修改；order by 子句表示update数据按照指定的顺序执行；limit子句表示限定修改数据的行数
3、多表修改是指修改table_references指定的多个表中满足条件的行数据，多表修改不允许使用order by和limit子句
4、执行update语句需要修改表权限
5、low_priority关键词表示修改语句需要等待其他链接的读此表操作结束后再执行，只要用在MyISAM,MEMORY,and MERGE存储引擎
6、ignore关键词表示当修改语句碰到违反唯一性约束条件等情况时，语句不会报错回退而是报警告知信息

关闭自动提交

mysql> show variables like '%autocommit%';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| autocommit    | ON    |
+---------------+-------+
1 row in set (0.00 sec)

mysql> set autocommit=off;
Query OK, 0 rows affected (0.00 sec)

mysql> show variables like '%autocommit%';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| autocommit    | OFF   |
+---------------+-------+
1 row in set (0.00 sec)

delete 单表删除举例

delete from students;
delete from students where sid=1;
delete from students order by sid;
delete from students limit 1;
delete  from  students where sid=(select sid from students);##结果集是一个值
delete  from  students where sid in(select sid from students);##结果集可以是多个值