初识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>> 此时 desc t27 显示的passport_id Key为PRI
mysql>> mysql> drop index passport_id on t27; #使用该命令删除
2 主键的使用(primary key)
2.1 使用规则
一个表中只能有一个主键字段；
对应的字段值不允许有重复，且不允许赋NULL值；
如果有多个字段都作为primary key，称为复合主键，必须一起创建；
主键的标志是PRI；
通常与auto_increment连用。
2.2 创建
建表时创建：
create table 表名(字段1primary 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 keyauto_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 cascadeon 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_idint(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
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_logfile1ibdata1
数据存储方式：
2个表文件：包括：xxx.frm:存放表结构;xxx.ibd:存放索引和数据
4.4.4 查看锁的状态：
show status like ‘Table_lock%’;# %作为通佩符
4.5 建表时如何决定表使用哪种存储引擎
接收查访问多的表，适合使用myisam存储，节省系统资源。
接收写访问多的表，适合使用innodb存储，并发访问量大。
页:
[1]