MySQL学习总结（三）

1.复合查询

使用oracle 9i的经典测试表进行测试

1.1 多表查询

案例：

• 显示雇员名、雇员工资以及所在部门的名字

select EMP.ename, EMP.sal, DEPT.dname from EMP, DEPT where EMP.deptno = DEPT.deptno;

• 显示部门号为10的部门名，员工名和工资

select dname,ename,sal from EMP, DEPT where EMP.deptno=DEPT.deptno and DEPT.deptno=10;

• 显示各个员工的姓名，工资，及工资级别

select ename, sal, grade from EMP, SALGRADE where EMP.sal between losal and hisal;

1.2 自连接

自连接是指在同一张表连接查询

案例：

显示员工FORD的上级领导的编号和姓名

• 使用子查询：

select empno,ename from EMP where EMP.empno=(select mgr from EMP where ename='FORD');

• 使用多表查询（自查询）：

--使用表的别名
select leader.empno,leader.ename from EMP leader, EMP worker where leader.empno=worker.mgr and worker.ename='FORD';

1.3 子查询

子查询是指嵌入在其他sql语句中的select语句，也叫嵌套查询

1.31 单行子查询

• 显示SMITH同一部门的员工

select * from EMP where deptno=(select deptno from EMP where ename='SMITH');

1.32 多行子查询

• in关键字；查询和10号部门的工作相同的雇员的名字，岗位，工资，部门号，但是不包含10自己的

select ename,job,sal,empno from emp where job in (select distinct job from emp
where deptno=10) and deptno<>10;

• all关键字；显示工资比部门30的所有员工的工资高的员工的姓名、工资和部门号

select ename,sal,deptno from EMP where sal > all(select sal from EMP where deptno=30);

• any关键字；显示工资比部门30的任意员工的工资高的员工的姓名、工资和部门号

select ename,sal,deptno from EMP where sal > any(select sal from EMP where deptno=30);

1.33 多列子查询

单行子查询是指子查询只返回单列，单行数据；多行子查询是指返回单列多行数据，都是针对单列而言的，而多列子查询则是指查询返回多个列数据的子查询语句

• 查询和SMITH的部门和岗位完全相同的所有雇员，不含SMITH本人

select ename from EMP where (deptno,job)=(select deptno,job from EMP where ename='SMITH') and ename <> 'SMITH';

1.34 在from子句中使用子查询

• 显示高于自己部门平均工资的员工的姓名、部门、工资、平均工资

--获取各个部门的平均工资，将其看做临时的表
select ename,deptno,sal from EMP,(select avg(sal) asal, deptno dt from EMP group by deptno) tmp where EMP.sal > tmp.asal and EMP.deptno=tmp.dt;

• 查找每个部门工资最高的人的姓名、工资、部门、最高工资

select EMP.ename,EMP.sal,EMP.deptno,ms from EMP, (select max(sal) ms,deptno from EMP group by deptno) tmp where EMP.deptno=tmp.deptno and EMP.sal=tmp.ms;

1.35 合并查询

• union：该操作符用于取得两个结果集的并集。当使用该操作符时，会自动去掉结果集中的重复行
• union all : 该操作符用于取得两个结果集的并集。当使用该操作符时，不会去掉结果集中的重复行

2.表的内外连接

2.1 内连接

内连接实际上就是利用where子句对两种表形成的笛卡儿积进行筛选

语法：

select 字段 from 表1 inner join 表2 on 连接条件 and 其他条件；

2.2 外连接

2.21 左外连接

语法：

select 字段名 from 表名1 left join 表名2 on 连接条件

2.22 右外连接

语法：

select 字段 from 表名1 right join 表名2 on 连接条件；

3.索引

索引可以提高数据库的性能，提升查询速度，但是是以插入、更新、删除的速度为代价的，这些写操作，增加了大量的IO

常见索引分类：

• 主键索引（primary key）
• 唯一建索引（unique）
• 普通索引（index）

3.1 基本原理

3.2 创建索引

3.2.1 创建主键索引

• 第一种方式：

-- 在创建表的时候，直接在字段名后指定 primary key
create table user1(id int primary key, name varchar(30));

• 第二种方式：

-- 在创建表的最后，指定某列或某几列为主键索引
create table user2(id int, name varchar(30), primary key(id));

• 第三种方式：

create table user3(id int, name varchar(30));
-- 创建表以后再添加主键
alter table user3 add primary key(id);

主键索引特点：

• 一个表中，最多只能有一个主键
• 主键索引效率高（不可重复）
• 创建主键索引的列不能为空且不能重复
• 主键索引的列基本上是int

3.2.2 唯一索引的创建

• 第一种方式：

-- 在表定义时，在某列后直接指定unique唯一属性。
create table user4(id int primary key, name varchar(30) unique);

• 第二种方式：

-- 创建表时，在表的后面指定某列或某几列为unique
create table user5(id int primary key, name varchar(30), unique(name));

• 第三种方式：

create table user6(id int primary key, name varchar(30)）；
alter table user6 add unique(name);

唯一索引的特点：

• 一个表中，可以有多个唯一索引
• 查询效率高
• 如果在某一列建立唯一索引，必须保证这列不能有重复数据
• 如果一个唯一索引上指定not null，等价于主键索引

3.2.3 普通索引的创建

• 第一种方式：

create table user8(id int primary key,
name varchar(20),
email varchar(30),
index(name) --在表的定义最后，指定某列为索引
);

• 第二种方式：

create table user9(id int primary key, name varchar(20), email varchar(30));
alter table user9 add index(name); --创建完表以后指定某列为普通索引

• 第三种方式：

create table user10(id int primary key, name varchar(20), email varchar(30));
-- 创建一个索引名为 idx_name 的索引
create index idx_name on user10(name);

普通索引的特点：

• 一个表中可以有多个普通索引
• 创建普通索引的列可以有重复值

3.3 查询索引

• 第一种方法：

show keys from 表名

• 第二种方法：

show index from 表名;

• 第三种方法：

desc 表名；

3.4 删除索引

• 删除主键索引：

alter table 表名 drop primary key;

• 删除其他索引：

alter table 表名 drop index 索引名； 索引名就是show keys from 表名
中的 Key_name 字段

• 第三种方法：

drop index 索引名 on 表名

4.事务管理

事务就是一组DML语句组成，这些语句在逻辑上存在相关性，这一组DML语句要么全部成功，要么全部失败，是一个整体

4.1 事务的基本操作

• 开始一个事务：start transaction;
• 创建一个保存点：savepoint 保存点名;
• 回到保存点：rollback to 保存点名;

4.2 事务的隔离级别

无隔离性会出现的问题：

• 脏读

是指一个事务正在访问数据，并且对数据进行了修改，而这个修改还没有提交到数据库中，这是，另一个事务也访问这个数据，然后使用了这个数据

• 不可重复读

是指在一个事务内，多次读同一数据。在这个事务还没有结束时，另外一个事务也访问该同一数据。那么，在第一个事务中的两次读数据之间，由于第二个事务的修改，那么第一个事务两次读到的的数据可能是不一样的。这样就发生了在一个事务内两次读到的数据是不一样的，因此称为是不可重复读。（即不能读到相同的数据内容）

• 幻读

是指当事务不是独立执行时发生的一种现象，例如第一个事务对一个表中的数据进行了修改，这种修改涉及到表中的全部数据行。同时，第二个事务也修改这个表中的数据，这种修改是向表中插入一行新数据。那么，以后就会发生操作第一个事务的用户发现表中还有没有修改的数据行，就好象发生了幻觉一样

事务的四种隔离级别：

• 设置事务隔离级别：

set session transaction isolation level 隔离级别;

• 查看当前隔离级别：

select @@tx_isolation;

4.3 事务的ACID特性

• 原子性(Atomicity)

事务是应用中最小的执行单位，就如原子是自然界的最小颗粒，具有不可再分的特征一样，事务是应用中不可再分的最小逻辑执行体

• 一致性(Consistency)

事务执行的结果，必须使数据库从一个一致性状态，变到另一个一致性状态。当数据库只包含事务成功提交的结果时，数据库处于一致性状态。如果系统运行发生中断，某个事务尚未完成而被迫中断，而改未完成的事务对数据库所做的修改已被写入数据库，此时数据库就处于一种不正确（不一致）的状态。因此一致性是通过原子性来保证的

• 隔离性(Isolation)

各个事务的执行互不干扰，任意一个事务的内部操作对其他并发事务都是隔离的。也就是说，并发执行的事务之间不能看到对方的中间状态，并发执行的事务之间不能互相影响

• 持久性(Durability)

持久性是指一个事务一旦被提交，它对数据库所做的改变都要记录到永久存储其中（如：磁盘）

5.视图

视图是一个虚拟表，其内容由查询定义。同真实的表一样，视图包含一系列带有名称的列和行数据。视图的数据变化会影响到基表，基表的数据变化也会影响到视图

• 创建视图

create view 视图名 as select语句；

• 删除视图

drop view 视图名；

视图使用规则和限制：

• 与表一样，必须唯一命名（不能出现同名视图或表名）
• 创建视图数目无限制，但要考虑复杂查询创建为视图之后的性能影响
• 视图不能添加索引，也不能有关联的触发器或者默认值
• 视图可以提高安全性，必须具有足够的访问权限
• order by 可以用在视图中，但是如果从该视图检索数据 select 中也含有 order by ,那么该视图中的order by 将被覆盖
• 视图可以和表一起使用

6.用户管理

• 创建用户

语法：

create user ‘用户名’@‘登陆主机/ip’ identified by ‘密码’;

• 删除用户

语法：

drop user ‘用户名’@‘主机名’

• 修改密码

语法：

用户修改自己密码
set password=password(‘新的密码’);

root用户修改其他用户密码
set password for ‘用户名’@‘主机名’=password(‘新的密码’)；

• 给用户授权

语法：

grant 权限列表 on 库.对象名 to ‘用户名’@‘登陆位置’ [identified by ‘密码’]

• 回收权限

语法：

revoke 权限列表 on 库.对象名 from ‘用户名’@‘登陆位置’；

---