（数据库六）MySQL事务、数据控制语言与关联查询及练习

TCL:事务控制语言

   用途：有时我们可能需要使用DML进行批量数据的删除，修改，增加。比如，在一个员工系统中，我们想删除一个人的信息。除了删除这个人的基本信息外，还应该删除与此人有关的其他信息，如邮箱，地址等等。那么从开始执行到结束就会构成一个事务。对于事务，我们要保证事务的完整性，要么成功，要么撤回。

   一般情况下事务要符合四个条件(ACID)：
   a.原子性(Atomicity)：事务要么成功，要么撤回。不可切割性。
   b.一致性(Consistency)：事务开始前和结束后，要保证数据的一致性。转账前账号A和账号B的钱的总数为10000;转账后账号A和账号B的前的总数应该还是10000;
   c.隔离性(Isolation):当涉及到多用户操作同一张表时，数据库为会每一个用户开启一个事务。那么当其中一个事务正在进行时，其他事务应该处于等待状态。保证事务之间不会受影响。
   d.持久性(Durability):当一个事务被提交后，我们要保证数据库里的数据是永久改变的。即使数据库崩溃了，我们也要保证事务的完整性。

   关键字：
   commit:提交
   rollback:撤回，回滚。
   savepoint:保存点

   事务:只有DML操作会触发一个事务。
   存储引擎(ENGINE):就是指表类型，当存储引擎为innodb时，才支持事务。有一个默认的存储引擎为Myisam。不支持事务。

   事务的验证：
   第一步:start transaction
   第二步:savepoint 保存点名称。
   第三步:DML
   第四步:commit/rollback;

DCL：数据控制语言

   用途：用来创建用户，给用户授权，撤销权限，删除用户。
   关键字：create user、grant、revoke、drop user

格式:
   1：创建用户：create user username@ip identified by newPwd;
   2：显示用户的权限：show grants for username@ip;
   3：授权：grant 权限1，权限2... on 数据库名.* to username@ip;
      DML权限：insert,delete,update
      DQL权限：select
      DDL权限：create,alter,drop...
   4：撤销权限:revoke 权限1，权限2..on 数据库名.* from username@ip;
   5：删除用户：drop user username;
   使权限立即生效：flush privileges;

关联查询的基础
   概念：当在查询时，我们所需要的数据不在一张表中，可能在两张表或多张表中。此时我们需要同时操作这些表。即关联查询。

   等值连接:在做多张表查询时，这些表中应该存在着有关联的两个字段。我们使用某一张表中的一条记录与另外一张表通过相关联的两个字段进行匹配，组合成一条记录。

   笛卡尔积：在做多张表查询时，将使用的某一张表中的每一条记录都与另外一张表的所有记录进行组合。比如表A有x条，表B有y条件，最终组合数为x*y，这个值就是笛卡尔积，通常没有意义。

   内连接：只要使用了join on。就是内连接。查询效果与等值连接一样。

   内连接格式：表A [inner] join  表B  on 关联条件 

   在做多张表查询时，我们所需要的数据，除了满足关联条件的数据外，还有不满足关联条件的数据。此时需要使用外连接。
   外连接两表关系：
   驱动表(主表):(就是要全部查询的表)除了显示满足条件的数据，还需要显示不满足条件的数据的表
   从表(匹配表)(副表):只显示满足关联条件的数据的表

   外连接分分类及格式:
   左外连接：(左边的表做驱动表)表A left [outer] join 表B on 关联条件。表A是驱动表，表B是从表
   右外连接：(右边的表做驱动表)表A right [outer] join 表B  on 关联条件表B是驱动表，表A是从表
   全外连接：两张表的数据不管满不满足条件，都做显示。表A  full [outer] join 表B on 关联条件

   注:mysql不支持全外连接

   外连接的结果集 = 内连接的结果集+驱动表中不匹配的结果集

   自连接:在多张表进行关联查询时，这些表的表名是同一个，即自连接。

高级关联查询：
   有的时候，我们要查询的数据，一个简单的查询语句满足不了，并且我们使用的数据，表中不能直观体现出来。而是预先经过一次查询才会有所体现。那么先执行的查询，我们称之为子查询。被子查询嵌入的查询语句称之为父查询。例:查询工资大于员工JONES工资的员工的信息。则需要先查到JONES的工资再查询大于JONES工资的员工信息。

   子查询返回的数据特点：
   (1) 可能是单行单列的数据。
   (2) 可能是多行单列的数据
   (3) 可能是单行多列的数据
   (4) 可能是多行多列的数据

   1)子查询可以在where子句中
   2)子查询可以在from子句中
   3)子查询可以在having子句中
   4)子查询可以在select字句中，相当于外连接的另外一种写法。

分页查询
    当每次查询的记录数比较大，通常一页显示不下，此时我们可以进行分页查询。
    关键字：limit
    用法:limit begin,size;
    begin：记录的开始行数、偏移量
    size：每页的最大记录数
    完整的DQL：
    书写顺序：select,from,where,group by,having,order by,limit
    执行顺序：from,where,group by,having,select,order by,limit
    注:limit后可以是一个参数，一个参数时，是指每次最大的查询记录数，limit size，默认记录数从0开始，而不是1。
    第n页，每页显示m条数据的分页写法：select * from tablename limit (n-1)*m,m

    --练习1:查询员工表，查询第一页的数据，每页10条。
    select * from emp limit 0,10;
    --练习2:查询员工表，查询第二页的数据，每页5条。
    select * from emp limit 5,5;
    --练习3:按照部门号升序排序，查询第二页的数据，每页5条
    select * from emp order by deptno  limit 5,5;
    --练习4:按照员工编号降序排序，查询第二页的数据，每页5条。
    select * from emp order by empno desc limit 5,5;
    --练习5:使用子查询，先排序，再查询每页的数据。
    --按照员工工资降序排序，查询第二页的数据，每页6条。
    select e.* from (select * from emp order by sal desc) e limit 6,6;

视图（view）
    VIEW，表的一部分投影。也是数据库里一个对象。(表的虚拟部分)
    作用: 因为视图中含有某表中的部分数据，不在视图中的字段有隐藏效果。相对来说，安全。
    创建视图
    格式：create view ViewName as subQuery 
    注：通过视图进行DML操作，一定会对表有影响。通过表进行DML操作，可能会对视图有影响。
    with check option:对视图的一种检查约束选项，如果在创建视图有此选项时，表示，只能对视图DML操作可见数据。反之，对视图不可见的数据，是不可以通过视图进行DML操作的。
    视图的分类：
    简单视图:subQuery是一个表中的普通查询语句
    复杂视图:subQuery是一个可以带有分组函数的，或者关联查询的语句。
    注：复杂视图不能对视图进行DML操作，只能进行DQL操作
    练习:创建一个复杂视图view_emp_100，是每个部门的平均工资，最高工资，最低工资，及其部门号。
    create view view_emp_100 as select avg(ifnull(sal,0)) avg_sal,max(sal)max_sal,min(sal)     min_sal,deptno from emp group by deptno;

索引（index）
    目的是为了提高查询速度。相当于一本书的目录。
    索引也是数据库的对象，占空间。每张表每个字段都可以设置相应的
    优点:提高查询速度。
    缺点:占空间，每次进行DML操作时，数据库都要(自动)重新维护索引，降低效率.
    总体来说:表中有索引可以提高效率，但不是索引越多越好。当表中的数据量比较小时，无需索引(因为直接查询可以比使用索引更快)，当某个字段的值比较少时，也不需要索引，如性别字段只有'f','m'.只有当数据量比较大，和字段值多时，可以使用索引。
    练习1:给emp表中的empno创建索引。
    create index index_emp_empno on emp(empno);
    练习2:查询empno为9003的数据。
    select * from emp where empno=9003;
    删除索引，删除视图都是用drop
    drop index  indexName
    drop view   viewName

练习：

CREATE TABLE dept(
deptno INT(2),
dname VARCHAR(14),
loc VARCHAR(13)
)
插入数据

10	ACCOUNTING	NEW YORK
20	RESEARCH	DALLAS
30	SALES	CHICAGO
40	OPERATIONS	BOSTON

 
   
     
 

 

 

CREATE TABLE emp(
empno INT(4),
ename VARCHAR(10),
job VARCHAR(9),
mgr INT(4),
hiredate DATE,
sal DOUBLE(7,2),
comm DOUBLE(7,2),
deptno INT(2)
)
插入数据

7369	SMITH	CLERK	7902	17-12-1980	800	NULL	20
7499	ALLEN	SALESMAN	7698	20-2-1981	1600	300	30
7521	WARD	SALESMAN	7698	22-2-1981	1250	500	30
7566	JONES	MANAGER	7839	2-4-1981	2975	NULL	20
7654	MARTIN	SALESMAN	7698	28-9-1981	1250	1400	30
7698	BLAKE	MANAGER	7839	1-5-1981	2850	NULL	30
7782	CLARK	MANAGER	7839	9-6-1981	2450	NULL	10
7788	SCOTT	ANALYST	7566	19-4-1987	3000	NULL	20
7839	KING	PRESIDENT	NULL	17-11-1981	5000	NULL	10
7844	TURNER	SALESMAN	7698	8-9-1981	1500	0	30
7876	ADAMS	CLERK	7788	23-5-1987	1100	NULL	20
7900	JAMES	CLERK	7698	3-12-1981	950	NULL	30
7902	FORD	ANALYST	7566	3-12-1981	3000	NULL	20
7934	MILLER	CLERK	7782	23-1-1982	1300	NULL	10
8002	IRONMAN	MANAGER	7839	9-6-1981	1600	NULL	10
8003	SUPERMAN	MANAGER	7839	9-6-1981	1600	NULL	NULL