mysql day类型_MySQL基础day02_表结构和索引类型-MySQL 5.6

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);

具体操作：mysql>create table stu_tab(

-> name char(3) not null default '',

-> sex enum('boy','gril') not null    default 'boy'

-> );

Query OK, 0 rows affected (0.05    sec)

mysql>insert into stu_tab values('zsp','boy',19910520);

Query OK, 1 row affected (0.00    sec)

mysql>insert into stu_tab values('lxl','gril',19890218);

Query OK, 1 row affected (0.00    sec)

mysql>insert into stu_tab(name,birthday) values('gbw',19910815);

Query OK, 1 row affected (0.02    sec)

mysql>select * from stu_tab;

+------+------+------------+

| name | sex  | birthday   |

+------+------+------------+

| zsp  | boy     | 1991-05-20 |

| gbw  | boy     | 1991-08-15 |

| lxl  | gril | 1989-02-18 |

+------+------+------------+

3 rows in set (0.00 sec)

mysql>

二  修改表结构

注：在修改之前，最好清空数据库：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存储引擎的学习中学习外键的具体内容！