MySQL基础day02_表结构和索引类型-MySQL 5.6
一:修改表结构
二:数据库索引类型
一:修改表结构
*修改表结构会影响表中的记录!
* 注:在修改表结构之前,如果表中有内容,则需要备份;最好是在插入字段之前,把表结构修改好!
*默认只有数据库管理员root有权限
alter table 表名处理动作:
*多个动作之间用“,”间隔;
alter table game add id char(10) not null first,add sex enum(‘boy’,’gril’) not null after age; 添加id和sex字段,id字段放在前面,sex字段放在age字段的后面。 |
处理动作包括:
1,添加新字段:add
add 字段名 类型(宽度) 约束条件;
2,修改字段名:change
change 原字段名 新字段名 类型(宽度) 约束条件;
3,修改字段类型:modify
modify字段名 类型(宽度) 约束条件;
4,删除已有字段:drop
drop 字段名;
指定添加字段的位置:first、after
如果不指定:默认添加在已有字段的末尾;
使用first指定添加在所有字段的前面;
alter table game add id char(10) not null first; |
指定将字段添加到一个字段的下面;
alter table game add sex enum(‘boy’,’girl’) not null after age; |
复制表:
create table 表名 查询语句;
create table t100 select * from t10;
复制表结构:
create table 新表名 不成立的查询条件;
create table t200 select * from t10 where 1=2;
注:复制表的时候,无论是复制表还是复制表结构,源表key属性会被忽略!
修改表名:
alter table 源表名 rename 新表名;
alter table myuser rename users;
二:在表中创建索引字段
1,索引:相当于书的目录
索引的缺点:
减慢了插入数据、删除数据和更新数据
占用物理存储空间
索引的优点:
加快查询速度
2,如何使用索引?
经常用来做查询条件的字段可以作为索引。
3,mysql支持的索引类型
index:普通索引
unique:唯一索引
fulltext:全文索引
primary key:主键
foreign key:外键
注:
不同的索引有不同的约束方式;
索引字段可以在建表时指定,也可以把表中的某个字段修改为索引字段。
3.1:index字段的属性:使用btree(二叉树)算法
一个表中可以有多个字段作为index字段;
对应的字段值允许有重复;
把经常做查询条件的字段设置为index字段;
index字段的key标志是MUL。
示例:创建一个表,指定普通索引字段
create table studb.t1( name varchar(10) not null, sex enum('boy','girl') not null default 'boy', age tinyint(2) unsigned not null default 21, index(name,sex) ); |
查看一个表的索引信息;
当有两个作为索引的时候,只有第一个有表示,可以使用下面的来查看详细信息!
show index from studb.t1;
删除普通索引:
drop index 索引名 on 库.表;
drop index name on studb.t1;
创建一个普通索引:
create index 索引名 on 表名(字段名);如果是多个,用逗号隔开
create index name on studb.t1(name);
create index sex on studb.t1(name,sex);
注:在创建表的时候或者使用create创建普通索引可以同时指定多个字段,注意普通索引的名字不要重复!
使用desc studb.t1\G查看索引;
如果在创建index索引的时候指定多个字段,那么只有一个只会为MUL,其他的需要使用desc studb.t1\G查看具体信息。
3.2:unique索引:
一个表中也可以吧多个字段设置成unique索引;
对应的字段值不能重复;
允许插入空值null;(unique字段修改为不允许为null,此字段限制与主键限制相同)
unique字段的key标志是uni。
默认情况下unique索引名和字段名相同;
create table studb.t3( stu_id char(4), name varchar(10) not null, sex enum("boy","gril") not null default "boy", index(name), unique(stu_id) ); |
创建一个unique索引:
create unique index 索引名 on 表名(字段);
create unique index stu_id on studb.t3(stu_id);
注:如果是多个的话,需要分别设置:
create unique index s1 on studb.stu_tab(stu_id);
create unique index s2 on studb.stu_tab(QQ);
create unique index s3 on studb.stu_tab(手机号);
删除一个unique索引:
drop index s1 on studb.stu_tab;
drop index s2 on studb.stu_tab;
drop index s3 on studb.stu_tab;
3.3:主键 primary key
一个表中只能有一个primary key字段;
字段值不允许有重复,且不允许为空;
如果有多个字段作为primary key,成为复合主键,必须在建表时一起创建;
主键字段的key标志是PRI,通常与auto_increment连用。
注:auto_increment使用时,必须字段必须为主键,且类型为int类型。
用主键字段作为操作的条件,能够唯一定位一条记录,通常把记录的编号字段设置为主键字段。
创建一个主键:
mysql> create table studb.t5( -> id int(2) auto_increment, -> name varchar(10) not null, -> age tinyint(2) unsigned not null default 21, -> primary key(id), //指定主键 -> index(name) -> ); Query OK, 0 rows affected (0.24 sec) mysql> desc studb.t5; +-------+---------------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +-------+---------------------+------+-----+---------+----------------+ | id | int(2) | NO | PRI | NULL | auto_increment | | name | varchar(10) | NO | MUL | NULL | | | age | tinyint(2) unsigned | NO | | 21 | | +-------+---------------------+------+-----+---------+----------------+ 3 rows in set (0.06 sec) mysql> |
或者使用
create table studb.t5( id int(2) primary key auto_increment, //在这里指定主键 name varchar(10) not null, age tinyint(2) unsigned not null default 21, index(name) ); |
删除一个主键:
alter table stu_tab modify id int(2) not null; //先去掉额外定义的内容auto...
alter table stu_tab drop primary key;
mysql> alter table studb.t5 drop primary key; //删除主键时提醒不能删,需要先删除auto...
ERROR 1075 (42000): Incorrect table definition; there can be only one auto column and it must be defined as a key
mysql> alter table studb.t5 modify id int(2) not null; //先把额外属性auto...去掉
Query OK, 1 row affected (0.09 sec)
Records: 1 Duplicates: 0 Warnings: 0
mysql> alter table studb.t5 drop primary key; //删除主键
Query OK, 1 row affected (0.08 sec)
Records: 1 Duplicates: 0 Warnings: 0
mysql> desc studb.t5; //可以看到id字段不再为主键了
+-------+---------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+---------------------+------+-----+---------+-------+
| id | int(2) | NO | | NULL | |
| name | varchar(10) | NO | MUL | NULL | |
| age | tinyint(2) unsigned | NO | | 21 | |
+-------+---------------------+------+-----+---------+-------+
3 rows in set (0.01 sec)
mysql> alter table studb.t5 add primary key(id); //将id字段设置为主键
Query OK, 0 rows affected (0.07 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql>
注:清空表中所有记录:
delete from 表名
delete from studb.stu_tab;
作业:示例:
一 创建学生信息表。 在 在studb库里创建学生信息表stu_tab。 自定义表结构。 把5个学员的信息插入表里并查看是否插入成功。 create table stu_tab( name char(3) not null default '', sex enum('boy','gril') not null default 'boy' ); insert into stu_tab values('zsp','boy',19910520); insert into stu_tab values('lxl','gril',19890218); insert into stu_tab(name,birthday) values('gbw',19910815); 具体操作:
二 修改表结构 注:在修改之前,最好清空数据库:delete from sta_tab; 1:学号字段 stu_num 在所有字段上方,定长字符类型 宽度4 不允许为空 alter table stu_tab add stu_num char(4) not null first; 2:添加爱好字段 loves 多选 book film music football 默认值是 film music 不允许为空 添加在姓名字段的下方 alter table stu_tab add loves set('book','film','music','football') default 'film,music' after name; 3:把年龄字段age 设置为 tinyint 类型 且不允许输入负数、默认值位 22 岁 不允许为空 alter table stu_tab add age tinyint(2); alter table stu_tab modify age tinyint(2) unsigned not null default 22; 4:添加 qq 字段 和 mail字段在表结构的末尾。 alter table stu_tab add qq char(10),add mail char(30); 5:添加性别字段 sex 在年龄字段的下方 字段的值只能在 m w x 内任选一个值默认值 为m 不允许字段插入空值 alter table stu_tab modify sex enum('m','w','x') default 'm' not null; 6:把存放学生学号的字段名修改为stu_id 。 alter table stu_tab change stu_num stu_id char(3) not null; 7:在性别字段下方添加birthday字段 存储学生的生日 alter table stu_tab add birthday date not null after sex; 8:在生日字段下方添加startday字段 存储学生的出生年份 alter table stu_tab add startday year after birthday; 9:在邮箱字段下方添加xf字段 存放学生的学费。要求能保存2位小数为整数位的最大存储单位是万。 alter table stu_tab add xf float(7,2) after mail; 10:添加tel字段 存放手机号 alter table stu_tab add tel char(11); 11: 把自己信息存放到表里 insert into stu_tab values ('001','zsp','book,film,music',1,19910520,1991,24,'91712000','zhangsp.520@qq.com',15800, '15239064000'); 三 设置学生信息表 0:删除学生表里的所有记录 delete from 数据库名.表名; delete from 表名; delect from test.stu_tab; 1:学生信息表里的每条记录加编号字段 id, 在所有字段的上方,是主键字段且要自动增长 alter table stu_tab add id int(2) primary key auto_increment; 2:学生信息表里的姓名字段和性别字段设置为index字段 create index name on stu_tab(name,sex); 3:把学生信息表里的学号字段、电话字段、QQ字段 设置为 unique字段 create unique index stu_id on stu_tab(stu_id); create unique index qq on stu_tab(qq); create unique index mail on stu_tab(mail); 4:设置完以上设置后,添加一条新的学生信息。 insert into stu_tab values (1,'001','zsp','book,film,music',1,19910520,1991,24,'917124333','zhangsp.520@qq.com',15800, '15239064000'); 5:删除1-3步 添加的索引类型。 -2:drop index name on stu_tab; -3:drop index stu_id on stu_tab; drop index qq on stu_tab; drop index mail on stu_tab; -1:alter table stu_tab modify id int(2) not null; //先删除附加条件 alter table stu_tab drop primary key; //删除主键 |
3.4:foreign key:外键
让当前表里指定字段的值,参考另外一个表里字段的值。
由于完全理解外键需要先了解mysql的存储引擎,所以将在mysql存储引擎的学习中学习外键的具体内容!
mysql的存储引擎:http://murongqingqqq.blog.51cto.com/2902694/1378306
转载于:https://blog.51cto.com/murongqingqqq/1378301