MySQL第二天（MySQL键值，MySQL存储引擎）
day02一、MySQL键值（key） ##设置在字段上，约束如何给字段赋值。
普通索引index
唯一索引unique
主键 primary key
外键 foreignkey
全文索引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(
-> namechar(10) not null,
-> age tinyint(2),
-> sex enum("boy","girl","no"),
-> likesset("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 indexname on studentinfo(name);
Query OK, 0 rows affected (0.41 sec)
Records: 0Duplicates: 0Warnings: 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: 0Duplicates: 0Warnings: 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库.表addprimarykey(字段)；
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> desct14; ##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: 3Duplicates: 0Warnings: 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: 3Duplicates: 0Warnings: 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_idint(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: 2Duplicates: 0Warnings: 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 * frombjb;
+-------+------+
| 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: 1Changed: 1Warnings: 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 fromcwb 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 tablebjbdrop foreign key 外键名
ysql>> Query OK, 0 rows affected (0.12 sec)
Records: 0Duplicates: 0Warnings: 0
外键名可以show create tablebjb 查询
如：
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
default-storage-engine=myisam
#]systemctl stopmysqld
#]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 updatedelete-----> innodb
页:
[1]