day02 一、mysql索引 二、MySQL存储引擎 +++++++++++++++++++++++++++++++++++ 一、mysql索引 1.1 索引介绍 ： 相当于 “书的目录”
5000页 1~200 目录信息 拼音排序 部首排序 笔画排序
201~5000 正文
1.2 索引的优点与缺点？ 优点 加快查询的速度 缺点 占用物理存储空间,减慢写的速度。
姓名 性别 班级 年龄 jim jim NULL
1.3 使用普通索引index：（在表中的字段上创建索引） 使用规则？ 查看 desc 表名； key ----> MUL show index from 表名； Table: t23 Key_name: name Column_name: name Index_type: BTREE ( B+TREE Hash ) 二叉树 1-10 1-5 6-10 1-2.5 2.6-5
创建index 索引： 创建表时 指定索引字段 create table 表名( 字段名列表， index(字段名1), index(字段名2), ); create table db1.t23( name char(10) , sex enum("boy","girl") default "boy" , age tinyint(2) unsigned not null default 18, index(name), index(sex) ); desc db1.t23; show index from db1.t23\G;
在已有表里创建index索引字段
create index 索引名 on 表名(字段);
删除index索引 drop index 索引名 on 表名;
二、主键primary key 的使用 2.1 使用规则？
2.2 查看
desc 表名； key ----> MUL
show index from 表名；
2.3 创建 2.3.1 建表时创建 create table t25( stu_id char(9) primary key , name char(10), sex enum("boy","girl"), age tinyint(2) unsigned ); create table t24( stu_id char(9), name char(10), sex enum("boy","girl"), age tinyint(2) unsigned, primary key(stu_id) ); 2.3.2 在已有表里创建 alter table 表名 add primary key(字段名); 2.4 删除主键 alter table 表名 drop primary key;
2.5复合主键的使用（表中多个字段一起做主键 ，复合主键字段的值不允许同时重复,要一起创建） PRI PRI name class pay jim nsd1710 yes jim nsd1712 no 建表时创建 create table db1.xfb( name char(10), class char(7), pay enum("yes","no") default "no", primary key(name,class) ); 验证 insert into db1.xfb values("jim","nsd1710","yes"); insert into db1.xfb values("jim","nsd1710","yes"); insert into db1.xfb values("jim","nsd1710","no"); insert into db1.xfb values("bob","nsd1710","yes"); insert into db1.xfb values("bob","nsd1703","no"); 删除复合主键 alter table 表名 drop primary key; 在已有表里添加复合主键。 alter table 表名 add primary key(字段名列表); ++++++++++++++++++++++++++++++++++++++++++ 2.6 primary key 与 auto_increment 连用 字段的值自动增长i++ i=$i+1 数值类型 primary key
id name age sex 1 jim 21 boy 2 tom 19 boy create table db1.t26( id int(2) zerofill primary key auto_increment, name char(10), age tinyint(2) unsigned, sex enum("boy","girl","no") );
insert into db1.t26(name,age,sex) values("bob",21,"boy"); insert into db1.t26(name,age,sex) values("bob",21,"boy"); insert into db1.t26(name,age,sex) values("bob",21,"boy"); select * from db1.t26;
唯一索引 unique
pri pri
姓名 护照编号 驾驶证号
null null
使用规则？ 查看 desc 表名； key ----> UNI
创建 建表时创建 create table db1.t27( name char(10), hz_id char(5), jsz_id char(5), unique(hz_id), unique(jsz_id) ); desc db1.t27; insert into db1.t27 values("jim","aaa","bbb"); insert into db1.t27 values("jim","aaa","bbb"); insert into db1.t27 values("jim","aaab","bbbc"); insert into db1.t27 values("jim",null,null);
create table db1.t28( name char(10), hz_id char(5) not null, jsz_id char(5), unique(hz_id), unique(jsz_id) ); desc db1.t28;
在已有表里创建unique create unique index 索引名 on 表名(字段名);
删除 drop index 索引名 on 表名； +++++++++++++++++++++++++++++++++++++++ 三、外键的使用 外键作用？ 外键的使用规则？ 创建外键： foreign key(字段名) references 表名(字段名) on update cascade on delete cascade
jfb 缴费表 学号 jfb_id name pay
create table db1.jfb( jfb_id int(2) primary key auto_increment, name char(10), pay float(7,2) default 20000 )engine=innodb;
insert into db1.jfb(name)values("bob"),("tom");
bjb 班级表 外键 学号 bjb_id name pay
create table db1.bjb( bjb_id int(2) , name char(10), pay float(7,2) default 20000, foreign key(bjb_id) references jfb(jfb_id) on update cascade on delete cascade )engine=innodb;
alter table bjb add primary key(bjb_id);
use db1; show create table bjb;
验证外键？ insert into bjb values(1,"bob",20000); insert into bjb values(3,"lucy",20000); insert into jfb(name)values("lucy"); insert into bjb values(3,"lucy",20000);
update 表名 set 字段名=值 where 条件;
update jfb set jfb_id=8 where jfb_id=2;
select * from jfb;
select * from bjb;
delete from 表名 where条件;
delete from jfb where jfb_id=3;
select * from jfb;
select * from bjb;
使用要注意的事项？
删除外键 alter table 表名 drop foreign key 外键; show create table bjb; alter table bjb drop foreign key bjb_ibfk_1; 在已有表里创建外键： alter table bjb add foreign key(bjb_id) references jfb(jfb_id) on update cascade on delete cascade;
+++++++++++++++++++++++++++++++++++ 四、MySQL存储引擎 4.1 MySQL存储引擎介绍：是数据库服务自带的功能程序， 处理表的处理器 每种存储引擎的功能和数据存储方式都不同 4.2 查看 表使用的存储引擎 show create table 表名；
数据服务使用的存储引擎
show engines;
InnoDB | DEFAULT
4.3 修改 表使用的存储引擎? alter table 表名 engine=存储引擎名；
建表时指定表使用的存储引擎? create table 表名( 字段名列表 ..... )engine=存储引擎名； 数据服务使用的存储引擎? vim /etc/my.cnf [mysqld] default-storage-engine=myisam ..... :wq #systemctl restart mysqld ++++++++++++++++++++++++++++++++ 4.4 生产环境中常用存储引擎及特点 myisam特点 支持表级锁 不支持外键 、事务、事务回滚 数据存储方式 .frm .MYI .MYD 表结构 索引 数据 innodb特点 支持行级锁、 外键 、事务、事务回滚 数据存储方式 .frm .ibd 表结构 索引+数据
锁的作用：解决并发访问的冲突问题 锁类型：读锁（共享锁） 写锁（排它锁） 锁粒度：表锁 行锁 (页锁)
事务：一次sql操作从连接到断开连接的过程称为事务。要么全部执行成功，任意一步错误，执行都失败。
ATM 插卡 转账： 对方卡号 11111 汇款金额 50000 转账中。。。。。
退卡
事务回滚：事务执行过程，任意一步执行不成功，会恢复所有的操作。
innodb存储引擎的表使用事务文件记录执行过的sql操作。 cd /var/lib/mysql/db1/t1.* ls ib_logfile0 ib_logfile1 ibdata1
insert into t1 values(101),(102);
4.5 建表时如何决定表使用哪种存储引擎 接收查访问多的表，适合使用myisam存储引擎，节省系统资源。 接收写访问多的表，适合使用innodb存储引擎，并发访问量大。
