02： MySQL 索引类型 、 MySQL 存储引擎

fish3129

　　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 删除主键  >　　2.5复合主键的使用（表中多个字段一起做主键 ，复合主键字段的值不允许同时重复,要一起创建）
　　PRI         PRI

　　name    >　　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   表名 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;
　　使用要注意的事项？

　　删除外键  >　　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存储引擎，并发访问量大。