1、MySQL键值概述
设置在表中的字段上，可以限制字段的赋值，并且有自己的功能
2、键值类型
- 根据数据存储要求，选择键值
- index普通索引
- unique 唯一索引
- fulltext 全文索引
- primary key主键
- foreign key外键
3、索引介绍
- 概念
- 类似于书的目录
- 对表中的字段值进行排序
- 索引类型包括：Btree、B+tree、hash
4、索引优缺点
4.1、优点
- 通过创建唯一性的索引，可以保证数据库表中每一行数据的唯一性
- 可以加快数据的查询进度
4.2、缺点
- 当对表中的数据进行增加、删除和修改的时候，索引也要动态的调整，降低了数据的维护速度
- 索引需要占物理空间
5、MySQL键值使用
5.1、index普通索引
5.1.1、使用规则
- 一个表中可以有多个index字段
- 字段的值允许重复，且可以赋NULL值
- 通常把作为查询条件的字段设置为index字段
- index字段 标志是MUL
5.1.2、查看索引信息
- show index from 表名 \G;
5.1.3、建表时创建索引
- create table 库名.表名 （字段名列表，index（字段名））；
- 示例
mysql> create table db2.t2 (
-> name char(10) , class char(9),age tinyint,
-> index(name),index(class));
mysql> desc db2.t2; //查看表结构
mysql> insert into db2.t2 values("a" , "123", 19); //随便插入几行数据
mysql> insert into db2.t2 values("f" , "123", 19); //随便插入几行数据
mysql> insert into db2.t2 values("r" , "123", 19); //随便插入几行数据
mysql> insert into db2.t2 values("z" , "123", 19); //随便插入几行数据
mysql> show index from db2.t2 \G; //查看索引
[root@db1 db2]# pwd
/var/lib/mysql/db2
[root@db1 db2]# ls //索引的排序信息存储在以.ibd结尾的文件中
db.opt t1.frm t1.ibd t2.frm t2.ibd
5.1.4、删除索引
- drop index 索引名 on 表名；
- 示例
mysql> drop index class on db2.t2;
mysql> desc db2.t2;
mysql> show index from db2.t2 \G;
5.1.5、在已有的表里创建索引
- create index 索引名 on 表名（字段名）；
- 示例
mysql> create index xxx on db2.t2(class);
mysql> desc db2.t2;
mysql> show index from db2.t2 \G;
5.2、primary key主键
5.2.1、使用规则
- 字段值不允许重复，且不允许赋空值
- 一个表中只能有一个primary key字段
- 多个字段都作为主键，成为复合主键，必须一起创建
- 主键字段的标志是PRI
- 主键通常与auto_increment 连用
- auto_increment 通过自己加一的方式赋值
- 通常把表中唯一标识记录的字段设置为主键
5.2.2、创建主键
- 建表时创建
- 示例1
mysql> create table db2.t3(name char(10) primary key ,age int ,class char(7)); //name作为主键
Query OK, 0 rows affected (0.00 sec)
mysql> desc db2.t3;
- 示例2
mysql> create table db2.t4 (
-> name char(10),pay float(7.2) default 20000,
-> primary key(name)
-> );
mysql> desc db2.t4;
5.2.3、创建复合主键
- 示例
mysql> create table t5(
-> cip char(15) , port int , stuat enum("yes","no") ,
-> primary key(cip , port ) );
mysql> desc db2.t5;
+-------+------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+------------------+------+-----+---------+-------+
| cip | char(15) | NO | PRI | NULL | |
| port | int(11) | NO | PRI | NULL | |
| stuat | enum('yes','no') | YES | | NULL | |
+-------+------------------+------+-----+---------+-------+
3 rows in set (0.00 sec)
mysql> insert into t5 values ("1.1.1.1" ,22 ,"yes");
Query OK, 1 row affected (0.00 sec)
mysql> insert into t5 values ("1.1.1.1" ,22 ,"no");
ERROR 1062 (23000): Duplicate entry '1.1.1.1-22' for key 'PRIMARY'
mysql> insert into t5 values ("1.1.1.1" ,80 ,"no");
Query OK, 1 row affected (0.00 sec)
mysql> select * from t5;
5.2.4、删除主键
- 示例
mysql> alter table db2.t4 drop primary key;
mysql> alter table db2.t5 drop primary key;
mysql> desc db2.t4;
mysql> desc db2.t5;
5.2.6、在已有表中添加主键
- 示例
mysql> alter table t4 add primary key(name);
5.2.7、在已有表中添加复合主键
- 示例
mysql> alter table t5 add primary key(cip ,port);
5.2.8、主键与auto_increment连用
字段值自增长 i=i+1
- 示例
mysql> create table t6 ( id int primary key auto_increment ,
-> name char(10) , age tinyint , sex enum("m","g") , homeaddr char(10) );
Query OK, 0 rows affected (0.01 sec)
--id作为主键，字段值自增长
mysql> insert into t6 (name,age,sex,homeaddr)values("tom",19,"m","usa");
Query OK, 1 row affected (0.00 sec)
--只给name,age,sex,homeaddr字段赋值
mysql> insert into t6 (name,age,sex,homeaddr)values("jerry",19,"m","usa");
Query OK, 1 row affected (0.00 sec)
--只给name,age,sex,homeaddr字段赋值
mysql> insert into t6 (name,age,sex,homeaddr)values("lucy",19,"m","usa");
Query OK, 1 row affected (0.00 sec)
--只给name,age,sex,homeaddr字段赋值
mysql> select * from t6;
- 示例，先给id赋值
mysql> insert into t8 values( 8 ,"lucy",29,"m","usa");
Query OK, 1 row affected (0.00 sec)
mysql> select * from t8;
mysql> insert into t8 (name,age,sex,homeaddr)values("jerry",29,"m","usa");
Query OK, 1 row affected (0.01 sec)
5.3、foreign key外键
- 功能：限制字段赋值
插入记录时，字段值在另一个表的字段值范围内选择。
类似于单位内的工资表和员工表
只能给在员工表内的人发工资
在工资表中有的，而员工表中没有。则无法选择
5.3.1、使用规则
- 表存储引擎必须是innodb
- 字段类型要一致
- 被参照字段必须要是索引类型中的一种（primary key）
- 因为必须得是不能重复的，所以被参照的一定带有主键。
5.3.2、创建外键
- 格式
mysql> create table 表名（
-> 字段名列表 ，foreign key（字段名） references 表名（字段名）--指定外键
-> on update cascade --同步更新
-> on dalete cascade --同步删除
-> )engine=innodb； --指定存储引擎
- 示例：新建员工表和工资表
mysql> create table yg_tab(
-> yg_id int primary key auto_increment,
-> user char (10),
-> sex enum("man","woman")
-> )engine=innodb;
mysql> create table gz_tab(
-> gz_id int , pay float(7,2),
-> foreign key(gz_id) references yg_tab(yg_id) --指定外键
-> on update cascade --同步更新
-> on delete cascade ) --同步删除
-> engine=innodb;
mysql> desc gz_tab; --查看表结构
mysql> show create table gz_tab ; --查看创建表的命令
当员工表中没有记录时,工资表中是无法添加数据的
5.3.3、删除外键
- 示例
mysql> alter table gz_tab drop foreign key gz_tab_ibfk_1 ;
Query OK, 0 rows affected (0.00 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> show create table gz_tab ;
5.3.4、再已有表中添加外键
- 示例
mysql> alter table gz_tab add
-> foreign key(gz_id)
-> references yg_tab(yg_id)
-> on update cascade
-> on delete cascade;
5.3.5、验证外键功能
- 示例：插入数据
mysql> insert into yg_tab(user,sex)values("bob","man");
Query OK, 1 row affected (0.00 sec)
--先向员工表中插入数据
mysql> insert into yg_tab(user,sex)values("lucy","woman");
Query OK, 1 row affected (0.00 sec)
--先向员工表中插入数据
mysql> select * from yg_tab;
+-------+------+-------+
| yg_id | user | sex |
+-------+------+-------+
| 1 | bob | man |
| 2 | lucy | woman |
+-------+------+-------+
2 rows in set (0.00 sec)
mysql> insert into gz_tab values (1,20000);
Query OK, 1 row affected (0.00 sec)
mysql> insert into gz_tab values (2,20000);
Query OK, 1 row affected (0.19 sec)
mysql> insert into gz_tab values (3,20000);
--报错,因为员工表中没有3的id
- 示例：同步更新
mysql> update yg_tab set yg_id=8 where yg_id=2 ;
mysql> select * from yg_tab;
mysql> select * from gz_tab;
- 示例：同步删除
mysql> delete from yg_tab where yg_id=8;
Query OK, 1 row affected (0.00 sec)
mysql> select * from yg_tab;
mysql> select * from gz_tab;
5.3.6、优化外键功能
- 工资表现在没有主键约束，同样的id还可以重复插入数据
mysql> insert into yg_tab(user,sex)values("lili","woman");
Query OK, 1 row affected (0.00 sec)
mysql> select * from yg_tab;
+-------+------+-------+
| yg_id | user | sex |
+-------+------+-------+
| 1 | bob | man |
| 3 | lili | woman |
+-------+------+-------+
2 rows in set (0.00 sec)
mysql> insert into gz_tab values (3,40000);
Query OK, 1 row affected (0.00 sec)
mysql> select * from gz_tab;
+-------+----------+
| gz_id | pay |
+-------+----------+
| 1 | 20000.00 |
| 3 | 40000.00 |
+-------+----------+
2 rows in set (0.00 sec)
mysql> insert into gz_tab values (1,20000);
Query OK, 1 row affected (0.00 sec)
mysql> insert into gz_tab values (3,40000);
Query OK, 1 row affected (0.00 sec)
mysql> select * from gz_tab;
+-------+----------+
| gz_id | pay |
+-------+----------+
| 1 | 20000.00 |
| 3 | 40000.00 |
| 1 | 20000.00 |
| 3 | 40000.00 |
+-------+----------+
4 rows in set (0.00 sec)
- 示例：为工资表添加主键
mysql> delete from gz_tab; --先删除全部记录
Query OK, 4 rows affected (0.00 sec)
mysql> alter table gz_tab add primary key(gz_id); --添加主键
mysql> insert into gz_tab values (1,20000); --重复输入两次,只有第一次成功,验证了主键的约束.
mysql> insert into gz_tab values (null,50000); --输入空值也不行
评论（0）
您还未登录，请登录后发表或查看评论