mysql on primary,初识Mysql（二）

1 mysql索引

2 主键的使用(primary key)

3 外键(foreign key)

4 存储引擎

1 mysql索引

1.1 索引概述

相当与“书的目录”

1.2 索引优点与缺点

优点：加快查询的速度

缺点：占用物理存储空间；减慢写的速度；

1.3 使用索引

在表中的字段上创建索引

普通索引 index

规则：一个表中可以有多个index字段；字段的值允许重复，可以赋空值

key标志是MUL

index_type:BTREE(二叉树)  ；B+TREE ；Hash

Key_name: abc  #索引名称

Column_name: std_id  #字段名

show index from 表名\G；#查看索引详细信息\分行

创建索引：

创建时指定索引字段：

create table 表名(字段1,字段2, ...index(字段1),index(字段2) );

已有表中指定索引字段：

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

删除索引：

drop index 索引名 on 表名;

1.4 唯一索引(unique)

一个表中可以有多个unique字段；

对应的字段值不允许重复；

KEY的标志是UNI；

字段的值允许为NULL，当修改为不允许为NULL，则此字段限制与主键相同

创建唯一索引：

创建时指定索引字段：

create table 表名(字段1,字段2, ... unique(字段1),unique(字段2) );

已有表中指定索引字段：

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

案例：新建table t27表，字段名为姓名，护照，驾驶证

mysql> create table t27(name char(10),

-> passport_id char(5),

-> drive_id char(5),

-> unique(passport_id),

-> unique(drive_id);

此时 desc t27 显示的Key为UNI

mysql> insert into t27 values("bob","aaa","bbb");

mysql> insert into t27 values("bob","asaa","sbbb");

passport_id列的值 ，drive_id列值各自不能相同

mysql> insert into t27 values("lisi",null,null);  #不允许赋空值

ERROR 1048 (23000): Column 'passport_id' cannot be null

mysql> alter table t27 modify passport_id char(5) not null ;

此时 desc t27 显示的passport_id Key为PRI

mysql> alter table t27 drop primary key; #使用删除主键的命令不能删除

mysql> drop index passport_id on t27; #使用该命令删除

2 主键的使用(primary key)

2.1 使用规则

一个表中只能有一个主键字段；

对应的字段值不允许有重复，且不允许赋NULL值；

如果有多个字段都作为primary key，称为复合主键，必须一起创建；

主键的标志是PRI；

通常与auto_increment连用。

2.2 创建

建表时创建：

create table 表名(字段1  primary key, ...);

create table 表名(字段1 ,字段2, ... primary key(字段));

在已有表中设置主键(考虑创建主键的条件，原字段为空，但是主键不允许为空)

alter table 表名 add primary key(字段名);

2.3 删除主键：

alter table 表名 drop primary key;

2.4 复合主键

复合主键字段的值不允许同时重复；

必须同时创建；

建表时创建：

create table 表名(字段1 ,字段2, ... primary key(字段1，字段2));

在已有表中设置主键(考虑创建主键的条件)

alter table 表名 add primary key(字段名);

删除复合主键

alter table 表名 drop primary key;

2.5 primary key与auto_increment连用

字段的值自动增长i++;

案例：

mysql> create table t26(id int(1) zerofill

primary key  auto_increment,

-> name char(10),

-> age tinyint(2) unsigned,

-> sex enum("boy","girl")

-> );

mysql> insert into t26(name,age,sex) values("jim",18,"boy");

总结：id值不设置时，默认1，添加的自增加1；如果自己设置从最后的值+1

3 外键(foreign key)

3.1 外键：

让当前表字段的值在另一个表中字段值的范围内选择。

使用外键的条件：

表的存储引擎必须是innodb;

字段类型要一致；

被参考字段必须要是索引类型的一种(如：primary key).

3.2 创建外键：

foreign key(字段名)  references  表名(字段名)

on update cascade 同步更新  on delete cascade  同步删除

已有表创建外键

alter table 表名 add foreign key(字段名)  references  表名(字段名)

on update cascade  on delete cascade;

3.3 删除外键：

alter table 表名 drop foreign key 约束名;

案例：创建2个表，分别为财务表和班级表，财务表记录了整个年级学生的缴费情况，

如果想要统计班级内的学生的缴费情况，只需要查看std_id，

没有缴费的学生在班级表内就写不进去信息。

mysql> create table finace(std_id int(2) primary key auto_increment,

-> name char(10),

-> money float(7,2) default 5000)

-> engine=innodb;

mysql> create table stdinfo(std_id  int(2),

-> name char(10),

-> money float(7,2) default 5000,

-> foreign key(std_id) references finace(std_id)

on update cascade on delete cascade)engine=innodb;

对finace表写入数据，stdinfo表的std_id范围必须在finace表的std_id范围内，

且stdinfo表的std_id字段值会随着finace表的std_id的字段值改变而改变。

(从属表随着主表改变，主表不会随从属表改变)

update finace set std_id=新值 where std_id=旧值;

delete from finace where std_id=值;

3.4 show create table 表名; #查看建表命令

| stdinfo | CREATE TABLE `stdinfo` (

`std_id` int(2) DEFAULT NULL,

`name` char(10) DEFAULT NULL,

`money` enum('yes','no') DEFAULT 'no',

KEY `std_id` (`std_id`),

CONSTRAINT `stdinfo_ibfk_1` FOREIGN KEY (`std_id`) REFERENCES `finace` (`std_id`) ON DELETE CASCADE ON UPDATE CASCADE

) ENGINE=InnoDB DEFAULT CHARSET=latin1 |

删除外键字段：

alter table 表名 drop foreign key 约束名；

alter table stdinfo drop foreign key stdinfo_ibfk_1;

4 存储引擎

4.1 mysql存储引擎介绍

是数据库服务自带的功能程序，处理表的处理器；

存储方式不一样(在/var/lib/mysql/数据库名/ 内存放的数据格式不同)

4.2 查看

查看表使用的存储引擎：show create table 表名;

查看系统的存储引擎：show engines; 或show engines\G;

4.3 修改

修改默认使用的存储引擎：

vim /etc/my.cnf

[mysqld]

default-storage-engine=myisam

systemctl restart mysqld

建表指定使用的存储引擎：

create table 表名(字段...)engine=引擎;

已有表修改：

alter table 表名 engine=引擎;

4.4 常用的存储引擎以及特点

4.4.1 mysql锁的机制：

锁粒度

表级锁：一次直接对整张表进行加锁；

行级锁：只锁定某一行；

页级锁：对整个页面进行加锁；

锁类型

读锁(共享锁)：支持并发读；

写锁(互斥锁)：是独占锁，上锁期间其他线程不能读表或写表；

4.4.2 myisam:

支持表级锁定，不支持行级锁定；

不支持事务；独享表空间；多用在查询，插入操作较多的表；

数据存储方式：

3个表文件：包括xxx.frm:存放表结构;xxx.MYI:存放索引;xxx.MYD:存放数据

4.4.3 innodb:

支持表级/行级锁定；

支持外键，支持事务回滚；共享表空间；

#事务(一次sql操作连接到断开的过程，要么成功，要么失败)

#事务回滚(事务执行过程，任意一步执行不成功，会恢复所有操作)

#innodb存储引擎的表使用事务文件记录执行过的sql操作，

#在/var/lib/mysql/下的ib_logfile0 和ib_logfile1  ibdata1

数据存储方式：

2个表文件：包括：xxx.frm:存放表结构;xxx.ibd:存放索引和数据

4.4.4 查看锁的状态：

show status like ‘Table_lock%’;# %作为通佩符

4.5 建表时如何决定表使用哪种存储引擎

接收查访问多的表，适合使用myisam存储，节省系统资源。

接收写访问多的表，适合使用innodb存储，并发访问量大。