mysql学习笔记-CSDN博客

本文链接：https://blog.csdn.net/kiven12/article/details/47781653

一些命令

#插入
insert into table_name(id,name...) values(1,'zhanghua'....)；
 
#更新
update table_name set age = age+1;
 
#删除
delete from table_name where id=1; #删除某条记录
drop table table_name; #删除表
truncate table table_name; #清空表
 
#查询
selec * from table_name where....；
 
#排序
select * from table_name order by id; #按照id顺序排序
selecy * from table_name group by id; #按照id进行分组
 
#索引
primary key #主键
unique key  #唯一键
key #普通索引，用来对数据完整性和唯一性做约束
 
#联结
join...on...   #联接两张表
left join      #左联接，联接两张表并返回左边表存在的列
right join     #右联接，联接两张表并返回右边表存在的列
inner join     #内联接，联接两张表并返回左右两张表都存在的列
 
#using()用于两张表的join查询，要求using()指定的列在两个表中均存在，并使用之用于join的条件。
select a.*, b.* from a left join b using(colA);
#等同于：
select a.*, b.* from a left join b on a.colA = b.colA;
 
#如果您指定了ON DUPLICATE KEY UPDATE，并且insert行后会导致在一个UNIQUE索引或PRIMARY KEY中出现重复值，则执行旧行UPDATE。
insert into table_name values(a,b) on duplicate key update a=a+1;  #a为unique key或者primary key，若a已经存在，则实现update
 
#索引和锁
select * from table_name where id<4 and id>1 for update #for update选择特定行进行加排他锁，与开事务管理类似，commit后即可解锁

对key、index的理解

1，key 是数据库的物理结构，它包含两层意义，一是约束（偏重于约束和规范数据库的结构完整性），二是索引（辅助查询用的）。包括primary key, unique key, foreign key 等。 primary key 有两个作用，一是约束作用（constraint），用来规范一个存储主键和唯一性，但同时也在此key上建立了一个index； unique key 也有两个作用，一是约束作用（constraint），规
primary key 有两个作用，一是约束作用（constraint），用来规范一个存储主键和唯一性，但同时也在此key上建立了一个index；
unique key 也有两个作用，一是约束作用（constraint），规范数据的唯一性，但同时也在这个key上建立了一个index； foreign key也有两个作用，一是约束作用（constraint），规
foreign key也有两个作用，一是约束作用（constraint），规范数据的引用完整性，但同时也在这个key上建立了一个index；可见，mysql的key是同时具有constraint和index的意义，这点和其他数据库表现的可能有区别。（至少在oracle上建立
可见，mysql的key是同时具有constraint和index的意义，这点和其他数据库表现的可能有区别。（至少在oracle上建立外键，不会自动建立index），因此创建key也有如下几种方式：（1），在字段级以key方式建立，如 create table t (id int not null primary key); （2），在表级以constraint方式建立，如create table t(id int, CONSTRAINT pk_t_id PRIMARY key (id)); （3），在表级以key方式建立，如create table t(id int, primary key (id));其它key创建类似，但不管那种方式，既建立了constraint，又建立了index，只不过index使用的就是这个constraint或key。2， index是数据库的物理结构，它只是辅助查询的，它创建时会在另外的
（1），在字段级以key方式建立，如 create table t (id int not null primary key);
（2），在表级以constraint方式建立，如create table t(id int, CONSTRAINT pk_t_id PRIMARY key (id));
（3），在表级以key方式建立，如create table t(id int, primary key (id));
其它key创建类似，但不管那种方式，既建立了constraint，又建立了index，只不过index使用的就是这个constraint或key。
2， index是数据库的物理结构，它只是辅助查询的，它创建时会在另外的表空间（mysql中的innodb表空间）以一个类似目录的结构存储。索引要分类的话，分为前缀索引、全文本索引等；因此，
因此，索引只是索引，它不会去约束索引的字段的行为（那是key要做的事情）。如，create table t(id int, index inx_tx_id (id));3 ，最后的释疑：（1），我们说索引分类，分为主键索引、唯一索引、普通索引(这才是纯粹的index)等，也是基于是不是把index看作了key。比如 create table t(id int, unique index inx_tx_id (id)); --index当作了key使用
如，create table t(id int, index inx_tx_id (id));
3 ，最后的释疑：
（1），我们说索引分类，分为主键索引、唯一索引、普通索引(这才是纯粹的index)等，也是基于是不是把index看作了key。
比如 create table t(id int, unique index inx_tx_id (id)); --index当作了key使用

索引类型

索引（在mysql中也叫'key'）是存储引擎用于快速找到记录的一种数据结构。索引有很多种类型，可以为不同的场景提供更好的性能。在mysql中，索引是在存储引擎层而不是服务器层实现的，但在其他数据库则工作方式不一定一样，这里以mysql为例。

B-Tree索引（最常用）:

以下图为开始，这是建立在B-Tree结构上的索引

由图可以看到，B-Tree索引可以加快访问数据的速度，因为存储引擎不再需要进行全表扫描来获取需要的数据，取而代之的是从索引的根节点（图示未画出）开始搜索。这种搜索方式有点类似于二分法，通过比较当前节点页上的key和要查找的值，来决定是往左指针方向进入下层子节点还是往右指针方向进入下层子节点，或者当前节点页的值即为对应的值，则返回该节点所指向的数据。
另外，B-Tree对索引列是顺序组织存储的，所以很适合查找范围数据，如以下例子：
假如有一张数据表：

CREATE TABLE peop(
    last_name VARCHAR(50) NOT NULL,
    first_name VARCHAR(50) NOT NULL,
    dob DATE NOT NULL,
    gender enum('m','f') NOT NULL,  #enum表示枚举
    key(last_name,first_name,dob)
);

对于表中的每一行数据，索引中包含了last_name，first_name，dob列的值，如下显示了该索引的组织结构：

由图可以看到，索引对多个值排序的依据是定义索引时列的顺序。以下介绍使用B-Tree索引的查询类型：
全值匹配：
指和索引中所有列进行匹配；
匹配最左前缀：
指只使用索引第一列；
匹配列前缀：
指可以只匹配某一列的值的开头部分，例如前面提到last_name以J开头的姓的人。注意这里只能使用索引第一列；
匹配范围值：
指可以匹配某一列的值在某个范围内，例如前面提到l查找姓在Allen和Barrymore之间的人。同样，这里只能使用索引第一列；
精确匹配某一列并范围匹配另外一列：
指某一列全匹配，另一列范围匹配。
上面查询类型中有一些我们家里限制，这也是B-Tree索引的限制，有如下几个：
1，如果不是按照索引的最左列开始查找，则无法使用索引；
2，不能跳过索引中的列，即索引第一列，第三列是不行的，不能跳过第二列索引；
3，如果查询中有某个列的范围查询，则其右边所有列都无法使用索引优化查找。

聚簇索引：

MySQL聚簇索引并不是一种单独的索引类型，而是一种数据存储方式。当表有聚簇索引时，她的数据行实际上存放在索引的叶子页（B-Tree索引叶子页是存放指向数据的指针），这样它保证关键字的值相近的元组存储的物理位置也相同（所以字符串类型不宜建立聚簇索引，特别是随机字符串，会使得系统进行大量的移动操作），且一个表只能有一个聚簇索引。因为由存储引擎实现索引，所以，并不是所有的引擎都支持聚簇索引。目前，只有solidDB和InnoDB支持。
下图是聚簇索引的数据分布图：

注：InnoDB对主键建立聚簇索引。如果你不指定主键，InnoDB会用一个具有唯一且非空值的索引来代替。如果不存在这样的索引，InnoDB会定义一个隐藏的主键，然后对其建立聚簇索引。

覆盖索引：

select的数据列属于索引列，这时候取数据不需要从数据堆里面查找，直接在b-tree节点上就能获取数据，这就叫做覆盖索引。（而聚簇索引是在叶子页上存放所有数据，要弄清两者的区别）
注：没有任何索引可以覆盖所有的列（如果selec *，则覆盖索引是无法使用的）；另外Mysql不能在索引中执行LIKE操作。
延迟关联：如果无法在一次查询中使用覆盖索引，则我们可以通过在from中使用子查询，这里使用覆盖索引找出要匹配的主键值，然后再在主查询中通过主键值找出所有列的值。

使用索引扫描做排序：

mysql可以使用同一个索引既满足排序，又用于查找行，下面介绍几种方法：
1，只有当索引的列顺序和order by子句的顺序完全一致，并且所有列的排序方向都一样时，mysql才能使用索引来对结果做排序；
2，如果查询需要关联多张表，则只有当order by子句引用的字段全部为第一个表时，才能使用索引做排序；
3，order by子句需要满足索引的最左前缀要求，或者前导列为常量时，可以不满足最左前缀；

三大范式

第一范式:确保每列的原子性.

如果每列(或者每个属性)都是不可再分的最小数据单元(也称为最小的原子单元),则满足第一范式.
例如:顾客表(姓名、编号、地址、……)其中"地址"列还可以细分为国家、省、市、区等。

第二范式:在第一范式的基础上更进一层,目标是确保表中的每列都和主键相关.

如果一个关系满足第一范式,并且除了主键以外的其它列,都依赖于该主键,则满足第二范式.
例如:订单表(订单编号、产品编号、定购日期、价格、……)，"订单编号"为主键，"产品编号"和主键列没有直接的关系，即"产品编号"列不依赖于主键列，应删除该列。

第三范式:在第二范式的基础上更进一层,目标是确保每列都和主键列直接相关,而不是间接相关.

如果一个关系满足第二范式,并且除了主键以外的其它列都不依赖于主键列,则满足第三范式.

为了理解第三范式，需要根据Armstrong公里之一定义传递依赖。假设A、B和C是关系R的三个属性，如果A-〉B且B-〉C，则从这些函数依赖中，可以得出A-〉C，如上所述，依赖A-〉C是传递依赖。
例如:订单表(订单编号，定购日期，顾客编号，顾客姓名，……)，初看该表没有问题，满足第二范式，每列都和主键列"订单编号"相关，再细看你会发现"顾客姓名"和"顾客编号"相关，"顾客编号"和"订单编号"又相关，最后经过传递依赖，"顾客姓名"也和"订单编号"相关。为了满足第三范式，应去掉"顾客姓名"列，放入客户表中。