Mysql的几种连接和引擎

发表于 | 分类于

Mysql的连接

内连接

结果： 从左表中取出每一条记录，去右表中与所有的记录进行匹配； 匹配必须是某个条件是左表中与右表中相同，才会保留结果，否则不保留；

  • 等值连接：在连接条件中使用等于号(=)运算符比较被连接列的列值，其查询结果中列出被连接表中的所有列，包括其中的重复列。
  • 不等值连接：在连接条件使用除等于运算符以外的其它比较运算符比较被连接的列的列值。

基本语法：左表[inner] join 右表on左表.字段 = 右表.字段，如下：

1
select * from book as a,stu as b where a.sutid = b.stuid

外连接

结果：以某张表为主，取出里面的所有记录， 然后每条与另外一张表进行连接， 不管能不能匹配上条件，最终都会保留， 能匹配，正确保留； 不能匹配其他表的字段都置空null

  • 左联接：是以左表为基准，将a.stuid = b.stuid的数据进行连接，然后将左表没有的对应项显示，右表的列为NULL
  • 右连接：是以右表为基准，将a.stuid = b.stuid的数据进行连接，然以将右表没有的对应项显示，左表的列为NULL

基本语法：左表 left/right join右表on 左表.字段 = 右表.字段；——条件必须有，如下：

1
2
select * from book as a right join stu as b on a.sutid = b.stuid  （右连接）
select * from book as a left join stu as b on a.sutid = b.stuid    （左连接）

交叉连接

结果：交叉联接返回左表中的所有行，左表中的每一行与右表中的所有行组合。交叉联接也称作笛卡尔积。

1
select * from book as a cross join stu as b order by a.id

自然连接

结果：在连接条件中使用等于(=)运算符比较被连接列的列值，但它使用选择列表指出查询结果集合中所包括的列，并删除连接表中的重复列。

MySql中主要使用的引擎

InnoDB 引擎

MySQL 5.5 及以后版本中的默认存储引擎，它的优点如下：灾难恢复性好，支持事务，使用行级锁，支持外键关联，支持热备份。

InnoDB引擎中的表，其数据的物理组织形式是簇表（Cluster Table），主键索引和数据是在一起的，数据按主键的顺序物理分布。实现了缓冲管理，不仅能缓冲索引也能缓冲数据，并且会自动创建散列索引以加快数据的获取。

MyISAM引擎

它的特性如下：不支持事务；使用表级锁，并发性差；主机宕机后，MyISAM表易损坏，灾难恢复性不佳；可以配合锁，实现操作系统下的复制备份、迁移；只缓存索引，数据的缓存是利用操作系统缓冲区来实现的。可能引发过多的系统调用且效率不佳；数据紧凑存储，因此可获得更小的索引和更快的全表扫描性能。

上面这两个是最常用的Mysql引擎。它们两者的主要区别：InnoDB支持事务，MyISAM不支持；MyISAM适合查询以及插入为主的应用，InnoDB适合频繁修改以及涉及到安全性较高的应用；InnoDB支持外键，MyISAM不支持；清空整个表时，InnoDB是一行一行的删除，效率非常慢。MyISAM则会重建表；InnoDB支持行锁，MyISAM的全表锁；InnoDB中不保存表的行数，如select count(*) from table时，InnoDB需要扫描一遍整个表来计算有多少行，但是MyISAM只要简单的读出保存好的行数即可。注意的是，当count(*)语句包含where条件时MyISAM也需要扫描整个表。

总之：MyISAM类型不支持事务处理等高级处理，而InnoDB类型支持；MyISAM类型的表强调的是性能，其执行速度比InnoDB类型更快，但是不提供事务支持，而InnoDB提供事务支持已经外部键等高级数据库功能。

数据库范式

  • 第一范式：当关系模式R的数据表中的每一列（每个字段）必须是不可拆分的最小单元，也就是确保每一列的原子性，称R是满足第一范式的，简记为1NF。满足第一范式是关系模式规范化的最低要求，否则，将有很多基本操作在这样的关系模式中实现不了。
  • 第二范式：如果关系模式R满足第一范式，并且R得所有非主属性都完全依赖于R的每一个候选关键属性，称R满足第二范式，简记为2NF。也就是要求每张表只描述一件事情。
  • 第三范式：设R是一个满足第一范式条件的关系模式，X是R的任意属性集，如果X非传递依赖于R的任意一个候选关键字，称R满足第三范式，简记为3NF.

范式的作用：三大范式是一般设计数据库的基本理念，有助于可以建立冗余较小、结构合理的数据库。

数据库五大约束？

  • primary KEY:设置主键约束；
  • UNIQUE：设置唯一性约束，不能有重复值；
  • DEFAULT 默认值约束，height DOUBLE(3,2)DEFAULT 1.2 height不输入是默认为1,2
  • NOT NULL：设置非空约束，该字段不能为空；
  • FOREIGN key :设置外键约束