数据库学习（二） ------索引，主键，外键,数据导入导出，复制表，表的连接

一.索引概述

1.优点：大大提高MySQL的检索速度

2.缺点：降低了更新表的速度，建立索引也会占用存储空间

3.分类：普通索引，唯一索引，

二.普通索引

1.使用规则

（1）一个表中可以有多个index字段
（2）字段的值可以有重复，也可以为NULL值
（3）经常把查询条件的字段设置为index字段
（4）index字段的key标志为： MUL

2.创建

（1）创建表时创建

create table 表名 (...,...,index(字段名1),index(字段名2)...);

（2）在已有表中添加索引字段

create index 索引名 on 表名(字段名)#索引名一般和字段名一样

3.查看

desc 表名;             #查看索引key的标志为MUL
show index from 表名\G;# 可以将不同索引字段格式化显示，更加清晰

4.删除

drop index 索引名 on 表名; #只能一次删一个字段的索引

三.唯一索引

1.使用规则

（1）一个表中可以有多个unique字段
（2）字段的值不能有重复，可以为NULL值
（3）index字段的key标志为： UNI

2.创建

（1）创建表时创建

create table 表名 (...,...,unique(字段名1),unique(字段名2)...);

（2）已有表中创建

create unique index 索引名 on 表名(字段名)

3.查看和删除

同上面的普通索引

四.主键索引（primary key）和自增长属性（auto_increment）

1.使用规则

（1）一个表中只能有一个主键字段
（2）对应字段的值不允许有重复 且不能为空值
（3）主键字段的key 值为PRI，extra为 auto_increment
（4）把表中能够唯一标识一条记录的字段设置为主键，常把表中记录编号的字段设置为主键

2.创建

（1）创建表时创建

# 第一种方法
create table 表名 (...,...,字段名 数据类型 primary key auto_increment,...)auto_increment=1000;

#第二种方法
create table 表名 (...,...,字段名1 数据类型 auto_increment,...,primary key(字段名1))auto_increment=1000;# 自定义增长步长

示例 ：

mysql> create table stu_1(id int primary key auto_increment, name varchar(20));
Query OK, 0 rows affected (0.09 sec)

mysql>  insert into stu_1 values(1,'王明'),(2,'李莉'),(3,'章鹏');
Query OK, 3 rows affected (0.01 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql>  insert into stu_1(name) values('方明');
Query OK, 1 row affected (0.01 sec)

mysql> select * from stu_1;
+----+--------+
| id | name   |
+----+--------+
|  1 | 王明   |
|  2 | 李莉   |
|  3 | 章鹏   |
|  4 | 方明   |
+----+--------+
4 rows in set (0.00 sec)    #可见即使不给方明的id赋值，它会根据前面的id 值自己增长，如果给id赋值为NULL也是同样的结果

（2）在已有表中添加主键

alter table 表名 add primary key(字段名);

3.删除主键

（1）两个步骤

alter table 表名 modify 字段名 数据类型;#先删除自增长属性
alter table 表名 drop primary key;#删除主键

五.外键

1.定义：让当前表字段的值在另一个表的范围内选择

2.外键的使用规则

（1）两张表被参考字段和参考字段数据类型要一致
（2）被参考字段必须是key的一种，通常是 primary key

3.创建

（1）创建表时添加外键

create table 表名 (...,字段名 数据类型 foreign key(字段名) references 被参考表名(被参考字段名)on delete 级联动作 on update 级联动作,...) 

（2）在已有表中添加

注意：会受到原有数据的影响

alter table 表名 add foreign key(参考字段名)references 被参考表名(被参考字段名)on delete 级联动作 on update 级联动作; 

4.级联动作

（1）cascade

数据级联更新 当主表删除记录 或者 更新被参考字段的值时，从表会级联更新

（2）restrict 默认

当删除主表记录时，如果从表中有相关联记录则不允许主表删除，更新同理.
若要删除，那么必须先删除从表里面的记录

（3）set null

当主表删除记录时，从表中相关联记录的参考字段值自动设置为 null ,更新同理

（4）no action

** 同restrict 都是立即检查外键的限制**

5.删除外键

(1)语法格式

alter table 表名 drop foreign key 外键名;

（2）外键名的查看方式

show create table 表名;#constraint 后面跟的值为外键名

六.数据导入与导出

1.语法

#导入
load data local infile '文件路径' into 库名.表名 
character set UTF8 
fields terminated by '分隔符' 
lines terminated by '分隔符'ignore 1 lines

#导出
select 字段名 from 库名.表名 into outfile '文件路径'
fields terminated by '分隔符' 
lines terminated by '分隔符'

错误记录： Loading local data is disabled; this must be enabled on both the client and server sides
Loading local data is disabled; this must be enabled on both the client and server sides
解决办法

2.步骤

还有记住写文件路径时要用’ \ ‘而不是’ \ '分割。
首先创建表，其次要找到数据库的默认搜索路径，在执行数据库的数据导入语句

七.表的复制

1.语法

全部复制

create table 表名 select 查询命令

复制表结构（不会复制Key属性）

create table 表名 select 字段名 from 要复制的表名称 where False

八.查询

1.子查询

将查询结果作为条件
示例：

select * from t1 where id< (select aver(id) from t1)

2.连接查询

• 内连接：从表中删除与其他被连接的表中没有匹配到的行

语法格式：

select 字段名列表 from 表1 inner join 表2 on 条件 inner join 表3 on 条件

• 外连接：左连接和右连接，会显示所有记录
• 左连接：以左表为主显示查询结果
  语法格式：

select 字段名列表 from 表1 left join 表2 on 条件 left join 表3 on 条件

• 右连接（同左连接）

3.多表查询

select 字段名列表 from 表1,表2 #按照表2的每条记录依次将表1的记录匹配完
select 字段名列表 from 表名列表 where 条件;相当于内连接