数据操作与事务控制

一、SQL中的DML：INSERT,UPDATE,DELETE
二、插入数据
1 insert into dept values(1,11,111);
2 insert into dept (deptno,loc)values(2,222);
3 插入日期 
insert into emp(empno,ename,job,mgr,hiredate,sal,comm,depno)
values(7196,'GREEN','SALESMAN',7782,sysdate(),2000,null,10);

insert into emp(empno,ename,job,mgr,hiredate,sal,comm,depno)
values(7197,'GREEN','SALESMAN',7782,now(),2000,null,10);

insert into emp(empno,ename,job,mgr,hiredate,sal,comm,depno)
values(7198,'GREEN','SALESMAN',7782,'1998-8-18',2000,null,10);

4批量插入：
insert into emp(empno,ename,job,mgr,hiredate,sal,comm,depno)
values(7199,'GREEN','SALESMAN',7782,now(),2000,null,10),
(7200,'GREEN','SALESMAN',7782,now(),2000,null,10);

5通过子查询插入多行数据
mysql>create table emp9 like emp;
mysql>insert into emp9 select * from emp;

练习3：
2
mysql>create table emp_back like emp;
mysql>insert into emp_back select * from emp where hiredate>'1982-1-1';
三、修改数据
1
mysql>update emp set job='worker',sal=sal*1.1;
update是固定的， emp是表名，set是固定的,job是列名,sal是列名
mysql>update emp set job='worker',sal=sal*1.1 where empno<7200;
练习4:
1
mysql>update emp set hiredate=hiredate+10 where depno=20 && hiredate>='1982-1-1';

2
mysql>update emp set comm=0 where comm is null;

3
mysql>update emp set sal=sal+500 where job in('NEW YORK','CHICAGO'); 

四、删除数据
mysql>delete from emp where empno=7200;
mysql>delete from emp;

mysql>truncate table emp;

五、事务
事务具有以下特性：原子性、一致性、隔离性、持久性。
例题：
mysql>set autocommit=0;
mysql>begin;

练习5:
mysql>create table test(id int primary key,
name varchar(50));
begin;
insert into test(id,name)values(1,'a');
insert into test(id,name)values(2,'b');
savepoint s1;
insert into test(id,name)values(3,'c');
insert into test(id,name)values(4,'d');
delete from test where id in(1,3);
rollback to s1;
delete from test where id in(2,4);
commit;
rollback;

select * from test;

运行结果：

id           name

1               a