数据库-mysql基本知识-2

数据库-mysql基本知识-2

关于表的别名：
例如：

 select e.ename,d.dname from emp e,dept d;

表起别名的好处：1.执行效率高。2.可读性好。

如何避免笛卡尔积现象：
添加条件进行过滤。
提示：避免笛卡尔积现象，但是不会减少记录的匹配次数。只是显示了有效记录而已。

连接查询：

内连接：

如A和B表进行连接，AB两张表没有主副之分，两张表是平等的。
等值连接：
连接条件的关系是等量关系。
语法：

   ...
     表A
   join
     表B
    on
	字段1=字段2
    where
	... 

非等值连接：

连接条件的关系是非等量关系。
例如：

   select e.ename,e.sal,s.grade
   from emp e
   join salgrade s
   on e.sal between s.losal and hisal;

自连接：

同一张表看成是两张表连接在一起。
例如:

select a.ename as '员工' ,b.ename as '领导名'
   from
     emp a
	join
	 emp b
	on 
	a.mgr = b.empno;

外连接：

如A和B表进行连接，有一张表是主表，一张表是副表，主要查询主表中的数据。捎带查询副表。当主表上的数据和副表上的数据没有匹配上时，副表自动模拟出NULL与之匹配。

左外连接：表示左边的表是主表。
右外连接：表示右边的表是主表。
左连接有右连接的写法，右连接有左连接的写法。

例如：

select a.ename as '员工' ,b.ename as '领导名'
   from
     emp a
	left join
	 emp b
	on 
	a.mgr = b.empno;（左外连接）

全连接：暂时不研究。

多表连接：

...
from 
  表A
 join
  表B
 join
 表C

表示：表A先和表B连接，连接之后表A再和表C连接。
例如：

select 
e.ename '员工',d.dname,s.grade,e1.ename'领导'
from 
  emp e
join 
  dept d
on 
  e.deptno=d.deptno
join
  salgrade s
on  
  e.sal between s.losal and hisal
left join
  emp e1
on 
  e.mgr=e1.empno;

子查询：

即select语句当中嵌套select语句，被嵌套的select语句是子查询。
在select，where，from等中可以嵌套select语句。
例如：
在where中嵌套子查询：

select * from emp where sal >(select avg(sal) from emp);	

在from中嵌套子查询：

select 
  t.*,s.grade
from
  (select deptno, avg(sal) as avgsal from emp group by deptno) t
join
  salgrade s
on
  t.avgsal between s.losal and hisal;

union:

可以将查询结果集相加。
例如：

select ename ,job from emp where job='MANAGER' or job='SALESMAN';

可以写成：

select ename ,job from emp where job='MANAGER';
union
select ename ,job from emp where job='SALESMAN';

当想让两张不相干的数据拼接在一起时，可能要用到union。
注意：使用union时，列数要相同。

limit：

limit是mysql特有的，limit取结果集中的部分数据。
语法机制：

limit startIndex,length

startIndex表示起始位置
length表示取出的长度。
例如：

select ename,sal from emp order by sal desc limit 0,5;

表示取出工资前5的员工。
limit是sql语句最后执行的环节。

建表语句的语法：
create table 表名(
字段1 数据类型,
字段2 数据类型,
字段3 数据类型,
…
);
在开发中，当某个字段中的数据长度不发生改变的时候，是定长的，例如：性别，生日等都采用char
当一个字段的数据长度是不确定的，如简介，姓名等，采用varchar。
注意：表名一般以t_或者tbl_开始。

向表中插入数据：

insert into 表名(字段1，字段2...) values(值1，值2...)

注意:字段的数量和值得数量相同，并且数据类型要对应相同。

表的复制：

create table 表名 as select语句。

将查询结果插入到一张表中：

insert into 表名 select 语句。

注意：这里有表的结构要求。

修改表的数据：

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

注意：没有条件整张表更新。

删除数据：

delete from 表名 where 条件;

注意：没有条件整张表删除。

删除大表的数据：

truncate table 表名;

表被截断，不可回滚，永久丢失。

删除表：

drop table 表名;

增删改查的术语：CRUD操作
（create）增（Retrieve）查（update）修改（delete）删。

约束：

在创建表的时候，可以给表的字段添加相应的约束，添加约束的目的是为了保证表中数据的合法性，有效性，完整性。
常见的约束有：非空约束(not null)，唯一约束(unique)，主键约束(primary key)，外键约束(foreign key)，检查约束(check)。
如果在某一个字段后面加约束：列级约束
在最后将多个字段联合添加约束：表级约束。
注意：not null只有列级约束。

主键：

按主键字段数分：
单一主键（常用的），复合主键（多个字段联合起来添加一个主键约束，违背三范式）
根据主键性质分：
自然主键（推荐使用），业务主键。
主键中的数据不能为null，也不能重复。
一张表中只能有一个主键。
主键值自增：在字段后写primary key auto_increment 可以实现一个自动维护的自增数字，从1开始递增。

外键可为空，外键字段引用其他字段时，该字段必须有唯一性。

存储引擎：

mysql默认使用的存储引擎是InnoDB方式，默认采用的字符集是utf8.
Oracle中称为表的存储方式。
常见的存储引擎：
MyISAM:MYSQL最常用的存储引擎，用三个文件来存储每个表，分别是格式文件，数据文件，索引文件，可被压缩，节省空间，可转换为只读表，不支持事务。
InnoDB：支持事务，这种存储引擎数据的安全得到保障，支持级联删除和级联更新。
MEMORY：不支持事务，数据容易丢失，因为数据和索引都是存储在内存中的，但查询速度最快。

事务(Transaction):

一个事务是一个完整的业务逻辑单元，不可再分。
例如：从A账户向B账户转账，需要从A账户减去余额，在B账户加上对应余额，必须同时成功，或者同时失败。
与事务相关的语句只有DML语句（增删改）
commit：事务提交
rollback：事务回滚
事务的四大特性：ACID
A：原子性 ：事务是最小的工作单元，不可再发
C：一致性 ：事务必须保证多条DML语句同时成功或者同时失败。
I：隔离性 ：事务A和事务B之间具有隔离
D：持久性 ：数据必须持久化到硬盘文件中，事务才算成功结束。

隔离级别：

第一级别：读未提交（read uncommitted),对方事务还没提交，当前事务可以读取对方未提交的数据，存在脏读现象。
第二级别：读已提交（read committed),对方事务已提交的数据，当前事务可以读取，但不可重复读。
第三级别：可重复读（repeatable read），可重复读，存在问题是读到的数据时幻象。
第四级别：序列化读/串行读（serializable)，解决了所有问题，但效率低。

设置隔离级别：set global transaction isolation level xxx（级别）;

索引：

索引相当于一本书的目录，原理是缩小扫描范围，提高检索效率，索引需要维护，故经常被修改的表不适合添加索引。
什么时候添加索引？
数据量庞大，该字段很少DML操作，该字段经常出现在where字句中。
主键和具有unique约束的字段会自动添加索引。
创建索引：

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

删除索引：

drop index 索引名 on 表名；

索引底层采用B+tree，里面存放各个数据的地址，找到那个数据之后，会直接根据地址来找数据。

索引分类：

单一索引：给单个字段添加索引。
复合索引：给多个字段联合起来添加索引。
主键索引：主键上会自动添加索引。
唯一索引：有unique约束的字段会自动添加索引。
注意：模糊查询的时候，第一个通配符使用的是%，这个时候索引会失效。

视图：

创建视图：

create view 视图名 as select 字段 from 表;

删除视图：drop view 视图名;
对视图进行增删改查会影响到原表数据。
视图可以隐藏表的实现细节，

数据库设计三范式（重点）：

设计数据库时为了减少数据的冗余，设计的表要符合三范式。
第一范式：任何一张表都应该有主键，并且每一个字段原子性不可再分。
第二范式：建立在第一范式的基础上，所有非主键字段完全依赖主键，不能产生部分依赖。
口诀：多对多，三张表，关系表，两外键。
第三范式：建立在第二范式的基础上，所有非主键字段直接依赖主键字段，不能产生传递依赖。
口诀：一对多，两张表，多的表加外键。
在实际开发中，为了满足客户需求，有时候会拿冗余换执行速度。

在一对一设计中有两种方案
1.主键共享
2.外键唯一