MySQL第二天（MySQL键值，MySQL存储引擎）

李斯特

　　day02
　　一、MySQL键值（key） ##设置在字段上，约束如何给字段赋值。
　　普通索引  index
　　唯一索引  unique
　　主键      primary key
　　外键      foreign  key
　　全文索引  fulltext
　　+++++++++++++++++++++++++++++++++++
　　1.1普通索引 index 的使用
　　1.1.1什么是索引？
　　相当于"书的目录"
　　1.1.2索引的优点与缺点
　　优点:加快查询记录的速度
　　缺点:占物理存储空间，减慢写的速度（insert update delete）
　　100页
　　1-10目录页（记录目录信息）
　　11-100正文
　　1.1.3索引使用规则？
　　1.1.4创建索引字段
　　1)建表 创建索引字段
　　create table 表名（
　　字段列表，
　　index(字段名)，
　　index(字段名)
　　);
　　mysql> create table t10(
　　-> name  char(10) not null,
　　-> age tinyint(2),
　　-> sex enum("boy","girl","no"),
　　-> likes  set("it","film","game","music"),
　　-> index(name),
　　-> index(sex)
　　-> );
　　Query OK, 0 rows affected (0.20 sec)
　　mysql> desc t10;                  ##查看t10表结构
　　+-------+---------------------------------+------+-----+---------+-------+
　　| Field | Type                            | Null | Key | Default | Extra |
　　+-------+---------------------------------+------+-----+---------+-------+
　　| name  | char(10)                        | NO   | MUL | NULL    |       |
　　| age   | tinyint(2)                      | YES  |     | NULL    |       |
　　| sex   | enum('boy','girl','no')         | YES  | MUL | NULL    |       |
　　| likes | set('it','film','game','music') | YES  |     | NULL    |       |
　　+-------+---------------------------------+------+-----+---------+-------+
　　4 rows in set (0.00 sec)
　　mysql> show index from t10\G;      ##查看t10表索引
　　 1. row
　　Table: t10
　　Non_unique: 1
　　Key_name: name
　　Seq_in_index: 1
　　Column_name: name
　　Collation: A
　　Cardinality: 0
　　Sub_part: NULL
　　Packed: NULL
　　Null:
　　Index_type: BTREE
　　Comment:
　　Index_comment:
　　 2. row
　　Table: t10
　　Non_unique: 1
　　Key_name: sex
　　Seq_in_index: 1
　　Column_name: sex
　　Collation: A
　　Cardinality: 0
　　Sub_part: NULL
　　Packed: NULL
　　Null: YES
　　Index_type: BTREE
　　Comment:
　　Index_comment:
　　2 rows in set (0.00 sec)
　　ERROR:
　　No query specified
　　2)把表中已有的字段设置为索引字段
　　create index 索引名称 on 表名（字段名）;  ##最好索引名 和字段名一样
　　1.1.5查看表中是否有索引字段
　　desc  表面；   key ----->MUL
　　show index from 表名；
　　mysql> create index  name on studentinfo(name);
　　Query OK, 0 rows affected (0.41 sec)
　　Records: 0  Duplicates: 0  Warnings: 0
　　mysql> desc studentinfo;
　　+-------+-----------------------------+------+-----+---------+-------+
　　| Field | Type                        | Null | Key | Default | Extra |
　　+-------+-----------------------------+------+-----+---------+-------+
　　| name  | char(10)                    | YES  | MUL | NULL    |       |
　　| sex   | enum('boy','girl','secret') | YES  |     | NULL    |       |
　　| age   | tinyint(2) unsigned         | YES  |     | 21      |       |
　　| mail  | char(30)                    | YES  |     | NULL    |       |
　　+-------+-----------------------------+------+-----+---------+-------+
　　4 rows in set (0.00 sec)
　　mysql> show index from studentinfo\G;     ##字段竖着显示
　　 1. row
　　Table: studentinfo
　　Non_unique: 1
　　Key_name: name
　　Seq_in_index: 1
　　Column_name: name
　　Collation: A
　　Cardinality: 2
　　Sub_part: NULL
　　Packed: NULL
　　Null: YES
　　Index_type: BTREE
　　Comment:
　　Index_comment:
　　1 row in set (0.00 sec)
　　ERROR:
　　No query specified
　　1.1.6删除索引
　　mysql> drop index name on studentinfo;
　　Query OK, 0 rows affected (0.09 sec)
　　Records: 0  Duplicates: 0  Warnings: 0
　　mysql> show index from studentinfo\G;
　　Empty set (0.00 sec)
　　ERROR:
　　No query specified
　　+++++++++++++++++++++++++++++++++++++++++++++++++++
　　主键  primary key 的使用  (主键的字段不能重复且不能为null)  主键标志是PRI
　　1.1 使用规则？
　　1.1.1
　　1)键表时创建主键字段
　　第一种方法：
　　mysql> create table t11(
　　-> name char(10) primary key,
　　-> age int(2),
　　-> sex enum("b","g")
　　-> );
　　Query OK, 0 rows affected (0.15 sec)
　　mysql> desc t11;
　　+-------+---------------+------+-----+---------+-------+
　　| Field | Type          | Null | Key | Default | Extra |
　　+-------+---------------+------+-----+---------+-------+
　　| name  | char(10)      | NO   | PRI | NULL    |       |
　　| age   | int(2)        | YES  |     | NULL    |       |
　　| sex   | enum('b','g') | YES  |     | NULL    |       |
　　+-------+---------------+------+-----+---------+-------+
　　3 rows in set (0.01 sec)
　　第二种方法：
　　mysql> create table t12(
　　-> name char(10),
　　-> sex enum("b","g"),
　　-> primary key(name)
　　-> );
　　Query OK, 0 rows affected (0.33 sec)
　　mysql> desc t12;
　　+-------+---------------+------+-----+---------+-------+
　　| Field | Type          | Null | Key | Default | Extra |
　　+-------+---------------+------+-----+---------+-------+
　　| name  | char(10)      | NO   | PRI | NULL    |       |
　　| sex   | enum('b','g') | YES  |     | NULL    |       |
　　+-------+---------------+------+-----+---------+-------+
　　2 rows in set (0.00 sec)
　　删除主键
　　alter table 库.表  drop primary key;
　　2)在表中已有的字段中创建主键
　　alter table  库.表  add  primary  key(字段)；
　　1.1.2创建复合键 （复合键是两个主键）  (当有两个值可能相同，可以用复合键防止两个值都相同，可以一个值相同)
　　mysql> create table t14(
　　-> cip char(15),
　　-> port smallint(2),
　　-> status enum("deny","allow"),
　　-> primary key(cip,port)
　　-> );
　　Query OK, 0 rows affected (0.23 sec)
　　mysql> insert into t13 values("1.1.1.1",21,"deny");
　　ERROR 1146 (42S02): Table 'db.t13' doesn't exist
　　mysql> insert into t14 values("1.1.1.1",21,"deny");
　　Query OK, 1 row affected (0.08 sec)
　　mysql> insert into t14 values("1.1.1.1",22,"deny");
　　Query OK, 1 row affected (0.02 sec)
　　mysql> insert into t14 values("1.1.1.2",22,"deny");
　　Query OK, 1 row affected (0.02 sec)
　　mysql> insert into t14 values("1.1.1.2",22,"allow");
　　ERROR 1062 (23000): Duplicate entry '1.1.1.2-22' for key 'PRIMARY'
　　mysql> desc  t14;    ##key中有PRI 表示主键
　　+--------+----------------------+------+-----+---------+-------+
　　| Field  | Type                 | Null | Key | Default | Extra |
　　+--------+----------------------+------+-----+---------+-------+
　　| cip    | char(15)             | NO   | PRI | NULL    |       |
　　| port   | smallint(2)          | NO   | PRI | NULL    |       |
　　| status | enum('deny','allow') | YES  |     | NULL    |       |
　　+--------+----------------------+------+-----+---------+-------+
　　3 rows in set (0.00 sec)
　　mysql> select * from t14;
　　+---------+------+--------+
　　| cip     | port | status |
　　+---------+------+--------+
　　| 1.1.1.1 |   21 | deny   |
　　| 1.1.1.1 |   22 | deny   |
　　| 1.1.1.2 |   22 | deny   |
　　+---------+------+--------+
　　3 rows in set (0.00 sec)
　　删除复合键

　　mysql>>　　ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '(port)' at line 1

　　mysql>>　　Query OK, 3 rows affected (0.50 sec)
　　Records: 3  Duplicates: 0  Warnings: 0
　　primary key 通常与auto_increment连用(字段值自动+1)
　　mysql> create table t18(

　　->>　　-> name char(10),
　　-> age tinyint(2) unsigned,
　　-> sex enum("boy","girl"),
　　-> likes set("film","book"),
　　-> index(name));
　　Query OK, 0 rows affected (0.17 sec)
　　mysql> desc t18;
　　+-------+--------------------------+------+-----+---------+----------------+
　　| Field | Type                     | Null | Key | Default | Extra          |
　　+-------+--------------------------+------+-----+---------+----------------+

　　|>　　| name  | char(10)                 | YES  | MUL | NULL    |                |
　　| age   | tinyint(2) unsigned      | YES  |     | NULL    |                |
　　| sex   | enum('boy','girl')       | YES  |     | NULL    |                |
　　| likes | set('film','book')       | YES  |     | NULL    |                |
　　+-------+--------------------------+------+-----+---------+----------------+
　　5 rows in set (0.00 sec)
　　mysql> insert into t18(name,age,sex,likes) values("tom",21,"boy","film,book");
　　Query OK, 1 row affected (0.03 sec)
　　mysql> select * from t18;
　　+----+------+------+------+-----------+

　　|>　　+----+------+------+------+-----------+
　　| 01 | tom  |   21 | boy  | film,book |
　　+----+------+------+------+-----------+
　　1 row in set (0.00 sec)
　　mysql> insert into t18(name,age,sex,likes) values("jim",21,"boy","film,book");
　　Query OK, 1 row affected (0.05 sec)
　　mysql> insert into t18(name,age,sex,likes) values("harry",21,"boy","film,book");
　　Query OK, 1 row affected (0.05 sec)
　　mysql> select * from t18;
　　+----+-------+------+------+-----------+

　　|>　　+----+-------+------+------+-----------+
　　| 01 | tom   |   21 | boy  | film,book |
　　| 02 | jim   |   21 | boy  | film,book |
　　| 03 | harry |   21 | boy  | film,book |
　　+----+-------+------+------+-----------+
　　3 rows in set (0.00 sec)
　　mysql> insert into t18 values(13,"harry",21,"boy","film,book");
　　Query OK, 1 row affected (0.03 sec)
　　mysql> select * from t18;
　　+----+-------+------+------+-----------+

　　|>　　+----+-------+------+------+-----------+
　　| 01 | tom   |   21 | boy  | film,book |
　　| 02 | jim   |   21 | boy  | film,book |
　　| 03 | harry |   21 | boy  | film,book |
　　| 13 | harry |   21 | boy  | film,book |
　　+----+-------+------+------+-----------+
　　4 rows in set (0.00 sec)
　　查找自己想要的记录(索引查找)

　　mysql> select * from t18 where>　　+----+-------+------+------+-----------+

　　|>　　+----+-------+------+------+-----------+
　　| 03 | harry |   21 | boy  | film,book |
　　+----+-------+------+------+-----------+
　　1 row in set (0.00 sec)
　　外键 foreign 可以的使用
　　外键使用规则？
　　创建外键命令格式
　　create table 库.表1(
　　字段列表，
　　foreign key(字段名) references 表2(字段名)
　　on update cascade on delete cascade
　　)engine=innodb;  ##同步删除同步更新参考 表2
　　财务 100
　　姓名   学号               pay
　　bob   nsd171108      2w
　　参考表为cwb
　　外键表为bjb
　　mysql> create table cwb(                         ##创建参考表cwb
　　-> cw_id int(2) primary key auto_increment,
　　-> name char(10),
　　-> pay float(7,2)
　　-> )engine=innodb;
　　Query OK, 0 rows affected (0.18 sec)
　　mysql> desc cwb;                               ##查看参考表cwb结构
　　+-------+------------+------+-----+---------+----------------+
　　| Field | Type       | Null | Key | Default | Extra          |
　　+-------+------------+------+-----+---------+----------------+
　　| cw_id | int(2)     | NO   | PRI | NULL    | auto_increment |
　　| name  | char(10)   | YES  |     | NULL    |                |
　　| pay   | float(7,2) | YES  |     | NULL    |                |
　　+-------+------------+------+-----+---------+----------------+
　　3 rows in set (0.01 sec)
　　mysql> insert into cwb(name,pay) values("bob",20000),("lucy",18000),("jack",16000);   ##插入参考表cwb的记录
　　Query OK, 3 rows affected (0.08 sec)
　　Records: 3  Duplicates: 0  Warnings: 0
　　mysql> select * from cwb;
　　+-------+------+----------+
　　| cw_id | name | pay      |
　　+-------+------+----------+
　　|     1 | bob  | 20000.00 |
　　|     2 | lucy | 18000.00 |
　　|     3 | jack | 16000.00 |
　　+-------+------+----------+
　　3 rows in set (0.00 sec)
　　mysql> create table bjb(
　　-> bj_id  int(2),
　　-> name char(10),
　　-> foreign key(bj_id) references cwb(cw_id)
　　-> on update cascade on delete cascade    ##和cwb表同步更新和同步删除
　　-> )engine=innodb;
　　Query OK, 0 rows affected (0.41 sec)
　　mysql> show create table bjb;  ##显示创建表格bjb外键的命令
　　mysql> insert into bjb values(1,"bob"),(2,"lucy");    ##插入id 为1和2的记录
　　Query OK, 2 rows affected (0.03 sec)
　　Records: 2  Duplicates: 0  Warnings: 0
　　mysql> insert into bjb values(7,"harry");           ##不成功，因为参考表中没有id为7
　　ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (db.bjb, CONSTRAINT bjb_ibfk_1 FOREIGN KEY (bj_id) REFERENCES cwb (cw_id) ON DELETE CASCADE ON UPDATE CASCADE)
　　mysql> select * from  bjb;
　　+-------+------+
　　| bj_id | name |
　　+-------+------+
　　|     1 | bob  |
　　|     2 | lucy |
　　+-------+------+
　　2 rows in set (0.00 sec)
　　mysql> insert into cwb(cw_id,name,pay) values(7,"harry",21000);  ##参考表中插入id为7的记录
　　Query OK, 1 row affected (0.02 sec)
　　mysql> select * from cwb;       ##查看参考表，记录有了
　　+-------+-------+----------+
　　| cw_id | name  | pay      |
　　+-------+-------+----------+
　　|     1 | bob   | 20000.00 |
　　|     2 | lucy  | 18000.00 |
　　|     3 | jack  | 16000.00 |
　　|     7 | harry | 21000.00 |
　　+-------+-------+----------+
　　4 rows in set (0.00 sec)
　　update cwb set cw_id=8 where cw_id=2;   修改参照表中的记录
　　mysql> update cwb set cw_id=8 where cw_id=2;    ##把参照表记录中id为2的改为id为8
　　Query OK, 1 row affected (0.02 sec)
　　Rows matched: 1  Changed: 1  Warnings: 0
　　mysql> select * from cwb;
　　+-------+-------+----------+
　　| cw_id | name  | pay      |
　　+-------+-------+----------+
　　|     1 | bob   | 20000.00 |
　　|     3 | jack  | 16000.00 |
　　|     7 | harry | 21000.00 |
　　|     8 | lucy  | 18000.00 |
　　+-------+-------+----------+
　　4 rows in set (0.00 sec)
　　delete from  cwb where cw_id=3          ##删除参照表中的记录
　　mysql> delete from cwb where cw_id=3;   ##删除参照表id为3的记录
　　Query OK, 1 row affected (0.02 sec)
　　mysql> select * from cwb;
　　+-------+-------+----------+
　　| cw_id | name  | pay      |
　　+-------+-------+----------+
　　|     1 | bob   | 20000.00 |
　　|     7 | harry | 21000.00 |
　　|     8 | lucy  | 18000.00 |
　　+-------+-------+----------+
　　3 rows in set (0.00 sec)
　　删除外键
　　alter table  bjb  drop foreign key 外键名

　　ysql>>　　Query OK, 0 rows affected (0.12 sec)
　　Records: 0  Duplicates: 0  Warnings: 0
　　外键名可以  show create table  bjb 查询
　　如：
　　mysql> show create table bjb;
　　+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
　　| Table | Create Table                                                                                                                                                                                                                                                       |
　　+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
　　| bjb   | CREATE TABLE bjb (
　　bj_id int(2) DEFAULT NULL,
　　name char(10) DEFAULT NULL,
　　KEY bj_id (bj_id),
　　CONSTRAINT bjb_ibfk_1 FOREIGN KEY (bj_id) REFERENCES cwb (cw_id) ON DELETE CASCADE ON UPDATE CASCADE
　　) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
　　+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
　　1 row in set (0.00 sec)
　　在已有的表里添加外键字段
　　alter table 表1 add foreign key(字段名) references 表2(字段名) on update cascade on delete cascade;
　　二、MySQL存储引擎
　　2.1什么是存储引擎
　　是表的处理器
　　2.2查看数据库服务支持哪些存储引擎
　　mysql>show engines;
　　2.3查看表使用的存储引擎
　　mysql>show create table t1;
　　2.4修改 服务 和表使用的存储引擎
　　mysql>alter table 表名 engine=存储引擎;
　　改默认存储引擎
　　vim  /etc/my.cnf
　　[mysqld]
　　default-storage-engine=myisam
　　#]systemctl stop  mysqld
　　#]systemctl start mysqld
　　2.5生产环境中常用哪种存储引擎，有什么特点？
　　myisam特点（查询次数的比较多适合这个，支持高并发读）
　　不支持事务、外键、事物回滚
　　支持表级锁（一个人访问就锁这个表,表锁的次数少,cpu消耗少）
　　每个表对应3个表文件
　　表名.frm   表名.MYI   表名.MYD
　　表结构         索引       数据
　　innodb特点（写的次数比较多适合这个，高并发写)
　　支持事物、外键、事物回滚
　　支持行级锁（一个人访问这个行就锁这个行，其他人可以访问其他行，行锁的次数多）
　　每个表对应2个表文件
　　表名.frm  表名.ibd
　　表结构     索引+数据
　　锁类型
　　读锁（共享锁）select：支持并发读
　　写锁（排它锁或互斥锁）insert update delete：上锁期间其他线程不能读表或写表
　　事务？建立连接，操作数据，断开连接的过程
　　事务回滚？一次事务执行过程中，任何一步操作失败，都会恢复之前的所有操作
　　事务日志文件：
　　ib_logfile0
　　ib_logfile1
　　ibdata1
　　2.6如何决定表使用哪种存储引擎
　　select ---->myisam
　　insert update  delete  -----> innodb