MySQL数据库的索引类型及数据的导入导出

一.MySQL数据库的索引类型

不同的索引有不同的约束方式和作用，使用的规则也不同。

1.普通索引 index

使用规则：表中可以有多个index字段

               对应的字段值允许有重复

               把经常做查询条件的字段设置为index字段

               index字段的key标志是null

使用方法：

1）.建表时，设置index字段

例：

mysql> create table t1(


   -> name varchar(10) not null,


   -> sex enum("b","g"),


   -> index(name)

         --索引字段名默认为第一个字段名，并且多个索引字段，列表内只显示在第一处。

   -> );


mysql> desc t1

；

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


| Field | Type          | Null | Key | Default | Extra |


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


| name | varchar(10)   | NO   | MUL | NULL    |      |


| sex  | enum('b','g') | YES  |     | NULL   |       |


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


2）.把已有表中的某个字段设置为索引字段

create index 索引名称on 表名（字段名，字段名n）；


show index from 表名 --查看表的索引信息


索引信息包括 索引名（key_name）索引字段算法（index_key）


                                                           BTREE（二叉树算法）逐级对半分流

例：

mysql> create index sex on t1(sex);


mysql> desc t1

；

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


| Field | Type          | Null | Key | Default | Extra |


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


| name | varchar(10)   | NO   | MUL | NULL    |      |


| sex  | enum('b','g') | YES  | MUL |NULL    |       |


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


3）.删除索引字段

drop index 索引名on 表名

2.唯一索引 unique

使用规则： 一个表中可以有多个unique字段


               对应的字段值不允许有重复


               unique字段的key标志是uni


               unique字段的值允许为null，当将其修改为不允许为null，则此字段限制与主键相同

使用方法：

1）.建表时，设置index字段

例：

mysql> create table t3(


   -> name varchar(10),

                   --默认允许为空时，索引为唯一索引

   -> sex enum("b","g"),


   -> unique(name));

    

Query OK, 0 rows affected (0.79 sec)


 

 

mysql> desc t3;


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


| Field | Type          | Null | Key | Default | Extra |


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


| name | varchar(10)   | YES  | UNI | NULL    |      |


| sex  | enum('b','g') | YES  |     | NULL   |      

 

 

mysql> create table t1(


   -> name varchar(10) not null,

         --当将其修改为不允许为null，则此字段限制与主键相同

   -> sex enum("b","g"),


   -> unique(name));

 

mysql> desc t2

 ;

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


| Field | Type          | Null | Key | Default | Extra |


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


| name | varchar(10)   | NO   | PRI| NULL    |       |


| sex  | enum('b','g') | YES  |     | NULL   |       |


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

 

2.把已有表中的某个字段设置为索引字段


     createunique index 索引名称 on 表名（字段名，字段名n）；

3）.删除unique字段


     dropindex 索引名 on 表名

3.主键*  primary key


把能唯一标识一条记录的字段做成主键字


使用规则：


       每个表内只能有一个primary字段


       对应的字段值不允许重复


       要多个字段为primary，称复合主键，必须在建表时一起创建***


       主键字段key标志为pri


       通常与auto_increment连用***

批注：auto_increment：


让字段的值自动增长


每插入一条新记录，当前字段的最大值自动加1


初始值默认是0


添加auto_increment属性，字段必须为主键且类型必须是整数数值类型

1）.在建表时创建主键字段

例：

mysql> create table t4(


   -> id int(3),


   -> name varchar(10) not null,


   -> sex enum("b","g"),


   -> index(name),


   -> primary key(id));


 

mysql> desc t4

；


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


| Field | Type          | Null | Key | Default | Extra |


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


| id   | int(3)        | NO   | PRI | 0       |      |


| name | varchar(10)   | NO   | MUL | NULL    |      |


| sex  | enum('b','g') | YES  |     | NULL   |       |


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


添加符合主键与添加主键的自动增长属性：

例：

mysql> create table t5(


   -> id int(3)not null auto_increment,


   -> name char(10)not null,


   -> age int(2)not null,


   -> sex enum("b","g")default "b",

 --设置sex字段默认为b

   -> index(name),


   -> primary key(id,age));


 

 

mysql> desc t5

；

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


| Field | Type          | Null | Key | Default | Extra          |


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


| id   | int(3)        | NO   | PRI | NULL    | auto_increment |


| name | char(10)      | NO   | MUL | NULL    |                |


| age  | int(2)        | NO   | PRI | NULL    |                |


| sex  | enum('b','g') | YES  |     |b       |                |


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


 

mysql> insert into t5(name,age)values("yeyu",27);


mysql> insert into t5(name,age)values("ll",23);


mysql> select * from t5;


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


| id | name | age | sex  |


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


| 1 | yeyu |  27 | b    |

                   --通过auto_increment自动添加数值

| 2 | ll   |  27 | b   |

                   --符合主键生效，两个主键字段内容不同时出现相同时可添加。

2）添加主键

alter table 表名add primary key（字段名）

3）删除字段的主键属性


alter table 表名drop primary key；

4.外键* foregin key


作用：当把表中的字段指定为外键字段后，此字段值，受参考表中字段值的限制。


外键使用规则：

外键字段类型要一致


                      表的存储引擎必须是innodb


                      被参考字段要有明确的索引


foreign key（当前表的字段名）


references 参考表（参考表中要参考的字段名）


on update cascade         --同步更新


on delete cascade          --同步删除


例：新建员工表（yg）和工资表（gz）。设置gz表中的id字段为外键

mysql> create table yg(


   -> id int(2) primary key auto_increment,


   -> name varchar(10) not null);


Query OK, 0 rows affected (0.71 sec)


mysql> create table gz(


   -> id int(2) not null,


   -> gz float(7,2),


   -> foreign key(id) references yg(id) on update cascade on deletecascade)engine=innodb;


mysql> insert into yg(id,name)values(1,"yy");

   --在yg表中添加一条记录

mysql> insert into gz values(3,10000);

              --添加外链条目时，受yg表外链字段限制

ERROR 1452 (23000): Cannot add or updatea child row: a foreign key constraint fails (`yeyue`.`gz`, CONSTRAINT`gz_ibfk_1` FOREIGN KEY (`id`) REFERENCES `yg` (`id`) ON DELETE CASCADE ONUPDATE CASCADE)


mysql> insert into gz values(1,10000);


Query OK, 1 row affected (0.07 sec)

 

修改表中记录值:


update表名set 字段名=值（值为字符时加“”） where 条件表达式；

二.数据的导入与导出

1.数据导入：把系统文件的内容保存到表里


*默认情况下，只有数据库管理员从数据库服务器本机登录，才有数据导入权限。


命令格式：


load data infile ‘文件名’ into table 数据库名.表名


fields terminated by '分割符'


lines terminated by '\n'


注意事项：


字段分割符要与文件内的一致


指定导入文件的绝对路径


导入数据的表字段类型要与文件字段匹配



例：将/etc/passwd文件中的内容导入到数据库内

新建表：uesr

mysql> create table user(


   -> name varchar(20) not null,


   -> pwd char(1) not null,


   -> uid int(4) not null,


   -> gid int(4) not null,


   -> des varchar(50),


   -> home varchar(50),


   -> shell varchar(20) not null,


   -> index(name),


   -> unique(uid));


mysql> load data infile '/etc/passwd'into table user fields terminated by ':' lines terminated by '\n';

Query OK, 30 rows affected (0.05 sec)


Records: 30  Deleted: 0 Skipped: 0  Warnings: 0

2.数据导出：把表里的记录保存到系统文件里


select * from 表名into outfile ‘文件名’ fieldsterminated by ‘分割符’linesterminated by '\n';


导出数据的注意事项：


     导出的内容由SQL查询语句决定


     若不指定路径，默认会放在执行导出操作时，所在库对应的目录里


     应确保mysql用户对目标文件夹可写


 

 

将uesr表的内容保存到/lianxi内

[root@yeyue ~]# mkdir /lianxi


[root@yeyue /]# ll |grep lianxi


drwxr-xr-x.   2 root root  4096 7月   1 01:34 lianxi

 --练习没有写入的权限

mysql> select * from user intooutfile "/lianxi/lixi.txt" fields terminated by "#" linesterminated by "\n";


ERROR 1 (HY000): Can't create/write tofile '/lianxi/lixi.txt' (Errcode: 13 - Permission denied)

需要为其设置权限

[root@yeyue /]# chmod 777 lianxi/


mysql> select * from user intooutfile "/lianxi/lixi.txt" fields terminated by "#" linesterminated by "\n";


Query OK, 30 rows affected (0.00 sec)