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)
转载于:https://blog.51cto.com/yeyue/1433294