本篇文章给大家带来了关于mysql中键值的相关知识,其中包括了键值类型、索引介绍、索引优缺点以及mysql键值使用的相关问题,希望对大家有帮助。
推荐学习:mysql视频教程
根据数据存储要求,选择键值
index 普通索引
unique 唯一索引
fulltext 全文索引
primary key 主键
foreign key 外键
索引是什么?
—— 类似于书的目录
—— 对表中字段进行排序
—— 索引类型包括:Btree(二叉树)、B+tree、hash
索引有点
—— 通过创建唯一性索引,可以保证数据库表中每一行数据的唯一性
—— 可以加快数据的查询速度
索引缺点
—— 但对表中的数据进行增加、删除和修改的时候,索引也要动态的调整,降低了数据的维护速度
—— 索引需要占物理空间
使用规则
—— 一个表中可以有多个 index 字段
—— 字段的值允许重复,且可以赋 NULL 值
—— 通常把做为查询条件的字段设置为 index 字段
—— index 字段标志为 MUL
建表时创建索引
—— index(字段名), index(字段名) .. ..
mysql> create table 库名.表名( 字段列表, index(字段名), index(字段名) );
创建一个信息表,指定字段中的name和age作为索引(字段name和age中的值要进行排序)
mysql> create table db2.t2( -> name char(10), -> age int, -> class char(7), -> email char(30), -> index(name),index(age) -> ); mysql> desc db2.t2;
查看索引信息
基本查看
—— desc 库名.表名; //查看key那一列
详细查看
—— show index from 表名 \G; mysql> show index from db2.t2 \G //因为有两个字段作为索引,所以下面有两列,\G以列的形式展现输出的结果
mysql> show index from db2.t2; //下图是不加 \G 的效果
删除索引
—— drop index 索引名 on 库名.表名;
mysql> drop index age on db2.t2; //删除索引age mysql> desc db2.t2;
mysql> show index from db2.t2 \G //只剩下索引name,索引age已被删除
向库db2中的表t2中存入数据:
mysql> insert into db2.t2 values("bob",19,"B180601","stu1@163.com"); mysql> insert into db2.t2 values("tom",19,"B180602","stu2@163.com"); mysql> insert into db2.t2 values("lucy",19,"B180603","stu3@163.com"); mysql> insert into db2.t2 values("jack",19,"B180604","stu4@163.com"); mysql> select * from db2.t2; //表中的数据显示是按照插入表记录的顺序排进行排序的
索引排序的信息在 t2.frm 和 t2.ibd 文件中
[root@DB ~]# ls /var/lib/mysql/db2
注:没有设置索引的时,查询表数据是按照表顺序逐行进行匹配的;设置索引后,是按照索引的排序来进行查找的(如:BTree、B+Tree、hash)
在已有的表里创建索引
—— create index 索引名 on 表名(字段名);
mysql> use db2; mysql> show tables;
mysql> desc db2.stuinfo;
mysql> create index name on db2.stuinfo(name); //索引名一般情况和字段名一样,索引名也可以用别的名字 mysql> desc db2.stuinfo;
mysql> show index from db2.stuinfo \G; //查看索引的详细信息
使用规则
—— 字段值不允许重复,且不允许赋NULL值
—— 一个表中只能有一个primary key字段
—— 多个字段都作为主键,称为复合主键,必须一起创建
—— 主键字段的标志是PRI
—— 主键通常与 auto_increment 连用
—— 通常把表中唯一标志记录的字段设置为主键
[记录编号字段]
建表时创建主键
—— primary key(字段名)
mysql> create table t8( name char(5) primary key, id int );
格式一:
mysql> create table db2.t3(name char(10) primary key,age int); //设置name字段为主键,但设置完主键后,自动将约束条件是否为空设置为NO(即不允许为空) mysql> desc db2.t3;
格式二:
mysql> create table db2.t4(name char(10),age int,primary key(name)); //也可以先将字段创建出来,最后在指定哪个字段为做主键 mysql> desc db2.t4;
向表t3中插入数据
mysql> insert into db2.t3 values("bob",19); mysql> select * from t3;
mysql> insert into db2.t3 values("bob",21); //错误原因,由于字段name是主键,所以字段name中的值不允许重复,表中name字段里面已经有个值为bob了,所以在向字段name赋值时就不能再赋bob值了 ERROR 1062 (23000): Duplicate entry 'bob' for key 'PRIMARY' mysql> insert into db2.t3 values(null,21); //报错原因,由于字段name是主键,所以字段name不允许赋NULL值 ERROR 1048 (23000): Column 'name' cannot be null
在已有表里创建主键
—— 格式
mysql> alter table 库名.表名 add primary key(字段名); mysql> desc db2.t2;
mysql> select * from db2.t2;
//我们要将字段name设置为主键,所以我们要检查原表中name字段的值,确保没有空(NULL)值和重复的值,如上图所示,表t2中name字段没有空值和重复的值,也可以通过下面的方法 mysql> select name from db2.t2 where name is null; //查看name字段中的哪些值为空值,输出结果显示没有 Empty set (0.01 sec) mysql> alter table db2.t2 add primary key(name); //设置字段name为主键 mysql> desc db2.t2;
删除主键
—— alter table 表名 drop primary key;
注:移除主键前,如果有自增属性,必须先去掉
创建复合主键
—— alter table 表名 add primary key(字段名列表);
注:多个字段都作为主键时,称为复合主键,作为主键的这几个字段里的值,在插入记录时不能同时重复。比如字段class有两个值都为classA,字段name的两个值不能都为tom,即字段name中不能有一样的名字,可以为tom和lucy,但不能都为tom
创建复合主键,将class字段和name字段都设置为主键
mysql> create table db2.t5( -> class char(7), -> name char(10), -> money enum("no","yes"), -> primary key(class,name) -> ); mysql> desc db2.t5;
mysql> insert into db2.t5 values("B180601","bob","yes"); mysql> select * from db2.t5;
mysql> insert into db2.t5 values("B180601","bob","yes"); //报错原因,因为字段class和字段name都为主键,所以这两个字段的值不能同时重复 ERROR 1062 (23000): Duplicate entry 'B180601-bob' for key 'PRIMARY' mysql> insert into db2.t5 values("B180602","bob","yes"); //两个主键字段,只要有一个字段的值没有重复,另外的主键字段不管重复没重复都可以插入 mysql> insert into db2.t5 values("B180602","tom","yes"); mysql> select * from db2.t5;
注:此时出现一个问题,若同一个班出现名字相同的两个人,然而class字段和name字段都为主键,由于这两个主键字段的值不能同时重复,此时数据是不能插入的。解决方案就是先删除这两个字段的主键,再删除表数据,然后在创建一个stu_num(学号)字段,设置stu_num、class、name字段一起做主键。
1)删除主键:
mysql> alter table db2.t5 drop primary key(name); //错误原因,由于字段class和字段name一起作为主键,所以删除时不能只删除一个,必须同时删除 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 '(name)' at line 1 mysql> desc db2.t5; //发现主键已被删除
2)创建一个新字段stu_num:
mysql> alter table db2.t5 add stu_num char(9) first; //添加一个新字段stu_num,并放在表的第一列
mysql> select * from db2.t5; //由于要设置字段stu_num为主键,所以字段stu_num的值不能为NULL,所以我们要清空表数据
3)清空表数据:
mysql> delete from db2.t5; mysql> select * from db2.t5; //输出结果显示t5表为空表 Empty set (0.00 sec)
4)在已有表里创建复合主键:
mysql> alter table db2.t5 add primary key(stu_num,class,name); mysql> desc db2.t5;
mysql> insert into db2.t5 values("B18060101","B180601","bob","yes"); mysql> insert into db2.t5 values("B18060102","B180601","bob","yes"); mysql> select * from db2.t5; //三个字段都为主键,只要其中一个主键的值不重复,那么数据就能插入成功
//通过加入学号stu_num字段为,让三个字段都为主键,解决同班同名数据插入的问题
主键与auto_increment(自增) 连用
mysql> create table t8( id int primary key auto_increment, name char(5) );
注:要设置自增的前提是,字段必须为主键才可以设置自增;设置自增的字段必须是数值类型的,字符类型是不能自增的。
示例:创建一个自增主键的表
mysql> create table db2.t6( -> stu_num int primary key auto_increment, -> name char(10), -> age int -> ); mysql> desc db2.t6;
mysql> insert into db2.t6(name,age) values("bob",19); mysql> select * from db2.t6;
mysql> insert into db2.t6(name,age) values("tom",20); mysql> insert into db2.t6(name,age) values("lucy",19); mysql> select * from db2.t6;
mysql> insert into db2.t6 values(3,"lucy",19); //错误原因,因为字段stu_num是主键,所以值是不能重复的 ERROR 1062 (23000): Duplicate entry '3' for key 'PRIMARY' mysql> insert into db2.t6 values(9,"lucy",19); //可以是除了1~3以外的别的数字,可以按顺序,也可以不按顺序 mysql> select * from db2.t6;
mysql> insert into db2.t6(name,age) values("jack",25); //如果在插入一条数据,这条数据不给stu_num赋值,那么字段stu_num的默认值,是从最大数开始自增赋值,即字段stu_num默认值为10,而不是4 mysql> select * from db2.t6;
示例:如果清空表记录,在插入数据,那么字段stu_num的值是从1开始自增,还是从10开始自增呢?
mysql> delete from db2.t6; //清空表记录 mysql> select * from db2.t6; //输出结果显示为空表 Empty set (0.00 sec) mysql> insert into db2.t6(name,age) values("bob",18); //插入一个数据 mysql> select * from db2.t6; //虽然删除了表中的全部数据,但是会记录上一次自增到哪个数字了,然后插入的数据会从上一次所记录的数字开始自增
mysql> insert into db2.t6(name,age) values(null,"tom",18); //主键不能为空,但此处我们给主键字段stu_num赋值为NULL,却不报错,原因是空就相当于与没有给字段stu_num赋值,没赋值所以就会以自增的方式进行赋值,所以此处赋值NULL不会报错
mysql> select * from db2.t6;
示例:通常把表中唯一标识记录的字段设置为主键,如记录编号字段
mysql> alter table db2.stuinfo add id int primary key auto_increment first; mysql> desc db2.stuinfo;
mysql> select * from db2.stuinfo;
外键功能
—— 插入记录时,字段值在另一个表字段值范围内选择
使用规则
—— 表存储引擎必须是innodb
—— create table 库名.表名(... ...) engine=innodb; //指定表存储的引擎
—— 插入记录的表的字段与被参照的表的字段类型要一致
—— 被参照字段的值要唯一且不能为空,所以必须要是索引类型的主键(primary key)
创建外键
—— create table 表名( 字段名列表, foreign key(字段名) references 库名.表名(字段名) //指定外键 on update cascde //同步更新 on delete cascade //同步删除 ) engine=innodb; //指定存储引擎 -5
创建一个员工表作为参考表:
mysql> create table db2.yuangong( -> id int primary key auto_increment, -> name char(20), -> sex enum("boy","girl") -> )engine=innodb; mysql> desc db2.yuangong;
mysql> insert into db2.yuangong(name,sex) values("bob","boy"); mysql> insert into db2.yuangong(name,sex) values("lucy","girl"); mysql> select * from db2.yuangong;
创建一个工资表设置外键,以员工表作为参考表:
mysql> create table db2.gongzi( -> id int, -> salary float(7,2), -> foreign key(id) references db2.yuangong(id) -> on update cascade -> on delete cascade -> ) engine=innodb; mysql> desc db2.gongzi;
//如图所示,外键创建成功后会自动创建索引,对数据进行排序,所以标志就是普通索引的标志,我们可以通过另一种方法进行查看 查看是不是外键的方法: mysql> show create table db2.gongzi; //查看创建表的命令
mysql> insert into db2.gongzi values(1,10000); mysql> insert into db2.gongzi values(2,20000); mysql> select * from db2.gongzi;
mysql> insert into db2.gongzi values(3,20000); //给id为3员工发工资报错,原因:给gongzi表里字段id赋值3,但由于gongzi表设置了外键,将gongzi表的id字段参考了yuangong表的id字段,所以给gongzi表id字段赋值时,这个值必须要在yuangong表的id字段里面有,才能进行赋值。
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`db2`.`gongzi`, CONSTRAINT `gongzi_ibfk_1` FOREIGN KEY (`id`) REFERENCES `yuangong` (`id`) ON DELETE CASCADE ON UPDATE CASCADE)
同步更新
将yuangong表中id字段值为2的改为8:
mysql> select * from db2.yuangong;
mysql> update db2.yuangong set id=8 where id=2; //将yuangong表中id字段的2改为8,同步更新表gongzi表id字段为2的记录
mysql> select * from db2.yuangong;
mysql> select * from db2.gongzi; //发现yuangong表的id字段值从2改为8后,gongzi表的id字段的值也从2变为8
同步删除
将yuangong表中id字段值为8的删除掉:
mysql> delete from db2.yuangong where id=8; //将yuangong表中id字段为8删除掉,同步删除表gongzi表id字段为8的记录 mysql> select * from db2.yuangong;
mysql> select * from db2.gongzi; //发现yuangong表的id字段值为8被删除后,gongzi表的id字段值为8的也被删除
注意事项
mysql> insert into db2.yuangong(name,sex) values("jack","boy"); mysql> select * from db2.yuangong;
mysql> insert into db2.gongzi values(3,30000); mysql> select * from db2.gongzi;
mysql> insert into db2.gongzi values(3,30000);) mysql> insert into db2.gongzi values(3,30000); mysql> insert into db2.gongzi values(null,65000); mysql> select * from db2.gongzi;
注:如上所示,字段id为3的被重复赋值,且字段id也被赋空值。因为参考表yuangong表里的字段id里面的值有1和3,所以gongzi表中的字段id就可以重复赋值1或3。这样赋值在生产环境中很不合理,所以我们通过将gongzi表中的id字段设置为主键,这样就能解决重复赋值和赋值空值的问题。
1)删除gongzi表的数据
mysql> delete from db2.gongzi; //删除gongzi表中的所有记录 mysql> select * from db2.gongzi; //查询表记录为空,说明表记录已被删除 Empty set (0.00 sec)
2)将字段id设置为主键
mysql> desc db2.gongzi;
mysql> alter table db2.gongzi add primary key(id); //将gongzi表中的id字段设置为主键 mysql> desc db2.gongzi;
3)进行赋值测试
mysql> insert into db2.gongzi values(1,10000); mysql> insert into db2.gongzi values(3,30000); mysql> insert into db2.gongzi values(1,10000); //再次重复赋值,出现错误,原因是主键不允许有重复的值 ERROR 1062 (23000): Duplicate entry '1' for key 'PRIMARY' mysql> insert into db2.gongzi values(3,30000); //再次重复赋值,出现错误,原因是主键不允许有重复的值 ERROR 1062 (23000): Duplicate entry '3' for key 'PRIMARY' mysql> insert into db2.gongzi values(null,30000); //赋空值,出现错误,原因是主键不允许赋空值 ERROR 1048 (23000): Column 'id' cannot be null mysql> insert into db2.gongzi values(2,30000); //参考表yuangong表中的id字段没有值为2的记录,不在参考表范围内的值不能进行赋值 ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`db2`.`gongzi`, CONSTRAINT `gongzi_ibfk_1` FOREIGN KEY (`id`) REFERENCES `yuangong` (`id`) ON DELETE CASCADE ON UPDATE CASCADE)
删除外键
—— alter table 库名.表名 drop foreign key 外键名;
mysql> show create table db2.gongzi;
//注:红线圈起来的地方就是外键的名称
mysql> alter table db2.gongzi drop foreign key gongzi_ibfk_1; //删除外键
mysql> show create table db2.gongzi; //查看创建表的命令,发现没有创建外键的记录了
此时我们就可以插入除了yuangong表id字段的值范围以外的值了
mysql> insert into db2.gongzi values(2,30000); mysql> insert into db2.gongzi values(9,30000); mysql> select * from db2.gongzi;
由于gongzi表的id字段还是主键,所以不能赋重复的值和空值
mysql> insert into db2.gongzi values(9,30000); ERROR 1062 (23000): Duplicate entry '9' for key 'PRIMARY' mysql> insert into db2.gongzi values(null,30000); ERROR 1048 (23000): Column 'id' cannot be null
推荐学习:mysql视频教程
以上就是详细介绍MySQL键值(整理总结)的详细内容,更多请关注php中文网其它相关文章!
已抢186982个
抢已抢67462个
抢已抢353655个
抢已抢668163个
抢已抢123572个
抢已抢25450个
抢