数据操作与事务控制

插入数据

-- DQL: select、 from、  where、 group by 、  order by  、 having...
-- DDL: create、drop、alter
---------------------------------------------------------------------
-- DML:数据操作语言 ：insert、update、delete
-- TPL: 事务处理语言：commit、rollback
--------------------------------------------------------------------

-- 插入数据
-- 语法： insert into 表名(列名列表) values(值列表)
-- ①值列表的个数必须和列名列表的一致
-- ②值列表的数据类型必须和列名列表的一致
-- ③插入和更新数据时，外键列的数据必须来至于主键表
-- ④ 字符和日期型数据必须要用单引号括起来。
1、在dept表中插入一条数据
insert into dept(deptno,dname,loc,depttypeid)  values(55,'teaching','neusoft',1);
insert into dept(deptno,dname,loc,depttypeid)  values(66,'teaching','neusoft',3);
-- 不指定列名列表：默认是按照顺序插入全部列
insert into dept values(66,'teaching','neusoft',2);
-- 隐式的插入null
insert into dept(dname,loc)  values('teaching1','neusoft1');
-- 显示的插入null ，给指定可以为null的列插入null或者'';
insert into dept(dname,loc)  values('teaching2',null);
insert into dept(deptno,loc)  values(69,'neusoft');
-- 向emp表中插入一条数据
insert into emp values(null,'zhangan',null,null,now(),null,1000,10);
-- 向表中插入日期
insert into emp values(null,'zhangan1',null,null,'2019-10-12',null,1000,10);

-- 批量插入数据 使用insert语句可以一次性地向表批量插入多条记录，语法格式如下。
insert into emp values
(null,'zhangan1',null,null,'2019-10-12',null,1000,10),
(null,'zhangan2',null,null,'2019-10-13',null,1000,10),
(null,'zhangan3',null,null,'2019-10-12',null,1000,10);

--  通过子查询插入多行数据
-- 不必书写values子句
-- INSERT子句和数据类型必须和子查询中列的数量和类型相匹配中列的数量、
-- 复制一张emp_bak表，结构和emp一样
create table emp_bak select * from emp where 1=0
2、将10号部门员工的所有信息插入到emp_bak表中
insert into emp_bak  select *from emp where deptno=10;
insert into emp_bak(empno,ename)  select empno,ename from emp where deptno=20;

更新数据

-- 修改数据
-- 语法： update 表名 set 列名=值[,列名=值..] where 表达式
修改部门20的员工信息，把82年之后入职的员工入职日期向后调整10天
update  emp set hiredate=DATE_ADD(hiredate,INTERVAL 10 day) 
where deptno=20 and 1982< EXTRACT(year from hiredate)
select EXTRACT(year from hiredate) from emp;
修改奖金为null的员工，奖金设置为0
update emp set comm=0 where comm is null;
修改工作地点在NEW YORK或CHICAGO的员工工资，工资增加500
-- 嵌套update
update emp set sal=sal+500 where deptno in (select deptno from dept where loc in('NEW YORK','CHICAGO'))

删除数据

-- 删除数据 
-- 语法 delete from 表名 where 表达式
删除经理编号为7566的员工记录
delete from emp where mgr=7566;
delete from emp where mgr is null;
删除工作在NEW YORK的员工记录
delete from emp where deptno=(select deptno from dept where loc='NEW YORK')
删除工资大于所在部门平均工资的员工记录
-- 相关delete -- mysql delete数据时不支持表别名 
delete from emp w where (select avg(ifnull(sal,0)) from emp where deptno=w.deptno)<sal 
delete from emp e1 where sal>(select avg(sal) from emp e2 where e2.deptno=e1.deptno)
-- 嵌套delete -- mysql delete数据时不支持表别名
delete from emp  where empno in (select empno from emp w,(select avg(sal) avgsal,deptno from emp where deptno is not null group by deptno) e where w.deptno=e.deptno and avgsal<sal)
-- 相关delete 
delete from emp  where (select a.avgsal from (select avg(ifnull(sal,0)) avgsal from emp e where e.deptno=deptno) a)<sal
delete from emp where empno in(7566,7499);

事务处理

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-68b1nwKs-1599478183408)(image/事务.png)]

-- 事务：由一个或多个sql组成的事件单元集合
-- 一条DML语句称为一个事件单元
-- 类似于转账：两条更新处于同一个事务中,一个人转出(update)一个人转入
-- mysql的事务是自动提交

-- 事务处理语言 commit:提交   rollback:回滚
-- 事务的作用：通过事务的使用，能防止数据库中出现数据不一致现象
-- 事务的开始：当上一个事务结束，遇到第一个DML语句时事务开启
-- 事务的结束：事务开启之后一定要结束
				隐式结束：
						服务器崩溃或者直接退出nacicat。。。
						遇到DDL或则DCL语句(commit)
				显示结束：commit 或者 rollback;

-- 查看mysql的事务
show VARIABLES like '%COMMIT%';
-- autocommit on 自动提交
-- autocommit off 手动提交
-- 设置mysql事务为手动提交
set autocommit=0
update emp_bak set sal=sal+1000 where empno=7782;
update emp_bak set sal=sal-1000 where empno=7839;
-- 提交事务
commit;
-- 回滚：避免误操作
ROLLBACK;
--------------------------------------------------------------------
-- 设置保存点

insert into test values(1,'zhangsan');
insert into test values(2,'zhangsan');
SAVEPOINT s1;
delete from test where id=1;
insert into test values(3,'zhangsan');
insert into test values(4,'zhangsan');
SAVEPOINT s2;
delete from test where id=3;

ROLLBACK to s1;
commit;
--------------------------------------------------------------------
--- 截断表 TRUNCATE类似于delete
-- TRUNCATE TABLE table; 
-- delete 和truncate都能清空表的数据
-- delete可以指定删除某行数据,truncate只能全部删除表中的数据
-- delete是DML语句可以回滚，truncate是DDL语句，一旦操作不能回滚
-- truncate的原理是先将表删除（drop）和清空，创建新的表
-- truncate释放存储空间，delete不释放存储空间
delete from test;
truncate table test;
drop table test;
--------------------------------------------------------------------

-- 查看数据库的事务隔离级别
SELECT @@tx_isolation-- REPEATABLE-READ可重复读

show VARIABLES like '%commit%';
set autocommit=0;
-- 行级锁：当一个会话对数据库表中的一行数据进行操作时，另一个会话再对这行数据进行操作会等待，等待会话一将锁释放,当会话1结束事务时释放锁
update emp set sal=sal+1000 where empno=7844;
commit;

-- 设置数据库的事务隔离级别
SET [SESSION | GLOBAL] TRANSACTION ISOLATION LEVEL {READ UNCOMMITTED | READ COMMITTED | REPEATABLE READ | SERIALIZABLE}
-- 设置全局的事务隔离级别
-- READ UNCOMMITTED 脏读
SET GLOBAL TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

-- 设置单个会话的事务隔离级别
SELECT @@tx_isolation
select * from emp;