MySQL学习笔记--多表操作（二）

一、 连接查询

1.1.连接查询分类

1.1.1.根据语法出现年代：
SQL92：一些老的DBA可能还在使用
SQL99：比较新的
1.1.2.根据连接方式

1.1.3.笛卡尔(乘)积现象
当两张表进行连接查询时，没有任何条件进行限制，最终查询结果是两张表记录条数的乘积

1.2.内连接查询

假设A和B表进行连接，凡是A表和B表能够匹配上的记录都查询出来；
A、B两张表没有主副之分；

***************等值连接*************
//语法规则
//别名语法规则：表名 别名
select
	别名.字段
from
	表名A
inner  join      //inner可以省略
	表名B
on
	条件;

************非等值连接**************
//语法规则
//别名语法规则：表名 别名
select
	别名.字段
from
	表名A
inner  join      //inner可以省略
	表名B
on
	条件;

***************自连接*****************
//语法规则
//别名语法规则：表名 别名
select
	a.字段,b.字段
from
	表名A a
inner  join      //inner可以省略
	表名A b
on
	a.字段1 = b.字段2;

1.3.外连接查询

假设A和B表进行连接，A表和B表中有主副之分；主要查询主表中的数据，捎带查询副表，当副表中数据没有能和主表匹配的数据时，副表自动用null与之匹配；
左连接：表示左边是主表;
右连接：表示右边是主表;
左连接 <———相互转换————> 右连接

***************左连接*************
//语法规则
//别名语法规则：表名 别名
select
	别名.字段
from
	表名A
left outer join      //outer可以省略
	表名B
on
	条件;

***************右连接*************
//语法规则
//别名语法规则：表名 别名
select
	别名.字段
from
	表名A
right outer join      //outer可以省略
	表名B
on
	条件;

1.4.子查询

//语法规则
select
	...(select)...
from
	...(select)...
where
	...(select)...

union	把两个查询结果相加（两结果结构应相同）
limit	取结果中的部分数据（分页查询，mysql特有）

//语法规则(最后执行)
limit startIndex,length;              //startIndex:起始位置(从零开始);      length:长度

二、数据操作

2.1.建表

//建表语法格式
create table 表名(
字段1 数据类型,
字段2 数据类型,
字段3 数据类型,
...
);

2.1.1.字段常见数据类型

int	整数型
bigint	长整型
float	浮点型
char	定长字符串
varchar	可变长字符串
date	日期类型
BLOB	二进制大对象（存储图片、视频等流媒体信息：Binary Large OBject）
CLOB	字符大对象 （存储较大文本：Character Large OBject）

2.1.2删表语句
drop table if exists 表名;

2.2.插入数据

insert into 表名(字段1,字段2,字段3,...) values(值1,值2,值3,...);

create table 表名 as select语句;          //表的复制 

insert into 表1 select语句;               //将查询结果插入到表1 

2.3.修改数据

update 
	表名
set
	字段1=值1,字段2=值2,字段3=值3,...
where
	条件;

2.4.删除数据

//没有条件，全部删除
//可以找回数据
delete from
	表名
where
	条件;

//删除大表
//表被截断，删除之后不能找回
truncate table 表名;

2.5.表结构增删改查术语CRUD

增	Create
删	Delete
改	Update
查	Retrieve

2.5.约束作用

非空约束	not null，约束字段不能为空
唯一约束	unique，约束字段不能重复，但可以为null
主键约束	primary key，约束字段既不能为空，也不能重复；auto_increment主键自增
外键约束	foreign key (子表字段) reference 父表(字段：必须具有唯一性)；可以为null
检查约束	check，目前Oracle中有，MySQL没有

2.6.MySQL存储引擎

查看数据库支持的引擎
show engine \G

常见的存储引擎

MyISAM	不支持事务，灵活的auto_increment，可转换为压缩，只读表来节省空间；格式文件(.frm)，数据文件(.MYD)，索引文件(.MYI)
InnoDB	支持事务、行级锁、外键等，安全 ，MySQL默认
MEMORY	查询速度最快

2.7.事务

一个事务是一个完整的业务逻辑单元，不可再分；保证数据的完整性、安全性

事务四大特性：ACID
原子性：最小的工作单元
一致性：比喻保证多条DML语句同时成功同时失败
隔离性：事务A和事务B具有隔离
持久性：

隔开级别
第一级别：读未提交（read uncommitted）
第二级别：读已提交（read committed），不可重复读
第三级别：可重复读（repeatable read）
第四级别：序列化读/串行化读（serializable）；效率低，需要事务排队
MySQL默认自动提交

start transaction	关闭自动提交机制
commit	提交
rollback	回滚到上一次提交点
set global transaction isolation level +级别	设置事务的全局隔离级别
select @@global	查看事务隔离级别

2.8.索引

相当于一本书的目录，通过目录可以快速得找到对应资源；

2.8.1.什么时候加索引

数据量庞大；
该字段很少的DML操作；
该字段经常出现在where子句中；
主键和具有unique字段会自动添加索引；

2.8.2.查看SQL语句执行计划

explain select 字段 from 表名 where 条件;

2.8.3.添加索引

create index 索引名 on 表名(字段);

2.8.4.删除索引

drop index 索引名 on 表名(字段);

2.9.视图

站在不同的角度去看数据

2.9.1.创建视图

create view 视图名 as MQL语句;

2.9.2.删除视图

drop view 视图名;

2.9.3.查看视图

select * from 视图名;

2.9.4.视图的作用

可以隐藏表的实验细节，为了提高数据安全性，只让java程序员对视图对象进行CRUD操作；视图不能提高查询效率

2.10.设计三范式

第一范式：任何一张表都应该有主键，并且每个字段原子性不可再分
第二范式：所有非主键字段完全依赖主键，不能产生部分依赖
第三范式：非主键字段不能传递依赖于主键字段