神出鬼没的Enderman

数据库

索引操作

索引是对数据库表中一列或多列的值进行排序的一种结构，使用索引可快速访问数据库表中的特定信息

• 优点 ：加快数据检索速度,提高查找效率
• 缺点 ：占用数据库物理存储空间，当对表中数据更新时,索引需要动态维护,降低数据写入效率

注意

1. 通常我们只在经常进行查询操作的字段上创建索引
2. 对于数据量很少的表或者经常进行写操作而不是查询操作的表不适合创建索引

索引分类

• 普通索引 ：字段值无约束,KEY标志为 MUL
• 唯一索引(unique) ：字段值不允许重复,但可为 NULL,KEY标志为 UNI
• 一个表中只能有一个主键字段, 主键字段不允许重复,且不能为NULL，KEY标志为PRI。通常设置记录编号字段id,能唯一锁定一条记录

索引创建

创建表时直接创建索引

create table 表名(
字段名 数据类型，
字段名 数据类型，
index 索引名(字段名),
index 索引名(字段名),
unique 索引名(字段名)
);

-- e.g.
-- 创建表同时添加索引
create table index_test (
id int primary key auto_increment,
name varchar(30),
unique nameIndex(name)
);

在已有表中创建索引

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

--e.g.
create index nameIndex on class(name);

主键索引添加

alter table 表名 add primary key(id);

查看索引

1. desc 表名;  --> KEY标志为：MUL 、UNI。
2. show index from 表名;

删除索引

drop index 索引名 on 表名;
alter table 表名 drop primary key;  # 删除主键

扩展： 借助性能查看选项去查看索引性能

set  profiling = 1； 打开功能 （项目上线一般不打开）
show profiles  查看语句执行信息

外键约束和表关联关系

外键约束

• 约束 : 约束是一种限制，它通过对表的行或列的数据做出限制，来确保表的数据的完整性、关联性
• foreign key 功能 : 建立表与表之间的某种约束的关系，由于这种关系的存在，能够让表与表之间的数据，更加的完整，关连性更强，为了具体说明创建如下部门表和人员表

示例

-- 创建部门表
CREATE TABLE dept (id int PRIMARY KEY auto_increment,dname VARCHAR(50) not null);

insert into dept values
(1,"技术部"),
(2,"销售部"),
(3,"市场部"),
(4,"行政部"),
(5,'财务部'),
(6,'总裁办公室');

-- 创建人员表
CREATE TABLE person (
  id int PRIMARY KEY AUTO_INCREMENT,
  name varchar(32) NOT NULL,
  age tinyint unsigned,
  salary decimal(8,2),
  dept_id int
) ;

insert into person values
(1,"Lily",29,20000,2),
(2,"Tom",27,16000,1),
(3,"Joy",30,28000,1),
(4,"Emma",24,8000,4),
(5,"Abby",28,17000,3),
(6,"Jame",32,22000,3);

上面两个表中每个人员都应该有指定的部门，但是实际上在没有约束的情况下人员是可以没有部门的或者也可以添加一个不存在的部门，这显然是不合理的

• 主表和从表：若同一个数据库中，B表的外键与A表的主键相对应，则A表为主表，B表为从表

-- foreign key 外键的定义语法

[CONSTRAINT symbol] FOREIGN KEY（外键字段） 

REFERENCES tbl_name (主表主键)

[ON DELETE {RESTRICT | CASCADE | SET NULL | NO ACTION}]

[ON UPDATE {RESTRICT | CASCADE | SET NULL | NO ACTION}]


-- 该语法可以在 CREATE TABLE 和 ALTER TABLE 时使用

-- 创建表时直接简历外键
CREATE TABLE person (
id int PRIMARY KEY AUTO_INCREMENT,
name varchar(32) NOT NULL,
age tinyint unsigned,
salary decimal(10,2),
dept_id int ,
constraint dept_fk foreign key(dept_id) references dept(id)
);

建立表后增加外键

alter table person add
constraint dept_fk
foreign key(dept_id)
references dept(id);

注意

1. 并不是任何情况表关系都需要建立外键来约束，如果没有类似上面的约束关系时也可以不建立。
2. 从表的外键字段数据类型与指定的主表主键应该相同。

通过外键名称解除外键约束

alter table person drop foreign key dept_fk;

-- 查看外键名称
show create table person;

注意：删除外键后发现desc查看索引标志还在，其实外键也是一种索引，需要将外键名称的索引删除之后才可以

级联动作

• restrict(默认) : on delete restrict on update restrict
• 当主表删除记录时，如果从表中有相关联记录则不允许主表删除
• 当主表更改主键字段值时，如果从表有相关记录则不允许更改
• cascade ：数据级联更新 on delete cascade on update cascade
• 当主表删除记录或更改被参照字段的值时,从表会级联更新

alter table person add
constraint dept_fk
foreign key(dept_id)
references dept(id)
on delete cascade on update cascade;

• set null : on delete set null on update set null
• 当主表删除记录时，从表外键字段值变为null
• 当主表更改主键字段值时，从表外键字段值变为null

alter table person add
constraint dept_fk
foreign key(dept_id)
references dept(id)
on delete set null on update set null;

表关联关系

• 当我们应对复杂的数据关系的时候，数据表的设计就显得尤为重要，认识数据之间的依赖关系是更加合理创建数据表关联性的前提。一对多和多对多是常见的表数据关系：

一对多关系

• 一张表中有一条记录可以对应另外一张表中的多条记录；但是反过来，另外一张表的一条记录
• 只能对应第一张表的一条记录，这种关系就是一对多或多对一
• 举例： 一个人可以拥有多辆汽车，每辆车登记的车主只有一人

create table person(
  id varchar(32) primary key,
  name varchar(30),
  age int
);

create table car(
  id varchar(32) primary key,
  brand varchar(30),
  price decimal(10,2),
  pid varchar(32),
  foreign key(pid) references person(id)
);

多对多关系

• 一对表中（A）的一条记录能够对应另外一张表（B）中的多条记录；同时B表中的一条记录
• 也能对应A表中的多条记录
• 举例：一个运动员可以报多个项目，每个项目也会有多个运动员参加,这时为了表达多对多关系需要单独创建关系表

CREATE TABLE athlete (
  id int primary key AUTO_INCREMENT,
  name varchar(30),
  age tinyint NOT NULL,
  country varchar(30) NOT NULL
);

CREATE TABLE item (
  id int primary key AUTO_INCREMENT,
  rname varchar(30) NOT NULL
);

CREATE TABLE athlete_item (
   id int primary key auto_increment,
   aid int NOT NULL,
   tid int NOT NULL,
   FOREIGN KEY (aid) REFERENCES athlete (id),
   FOREIGN KEY (tid) REFERENCES item (id)
);

-- 关系表中添加排名字段
alter table athlete_item add ranking int after tid;

表关联查询

如果多个表存在一定关联关系，可以多表在一起进行查询操作，其实表的关联整理与外键约束之间并没有必然联系，但是基于外键约束设计的具有关联性的表往往会更多使用关联查询查找数据

• 简单多表查询

多个表数据可以联合查询，语法格式如下

select  字段1,字段2... from 表1,表2... [where 条件]

--e.g.
select c.name,c.score,h.hobby from class as c,hobby as h 
where c.name=h.name;

select name,salary,dname from person,dept
where person.dept_id = dept.id;

select name,salary,dname from person,dept 
where person.dept_id = dept.id and salary>=20000;

笛卡尔积现象就是将A表的每一条记录与B表的每一条记录强行拼在一起。所以，如果A表有n条记录，B表有m条记录，笛卡尔积产生的结果就会产生n*m条记录

select * from class,hobby;

内连接查询只会查找到符合条件的记录，其实结果和表关联查询是一样的,官方更推荐使用内连接查询。

SELECT 字段列表
    FROM 表1  INNER JOIN  表2
ON 表1.字段 = 表2.字段;

select name,salary,dname from person inner join dept
on person.dept_id = dept.id
where salary>=20000;

左表全部显示，显示右表中与左表匹配的项

SELECT 字段列表
    FROM 表1  LEFT JOIN  表2
ON 表1.字段 = 表2.字段;

--e.g. 
select name,salary,dname
from person left join dept
on person.dept_id = dept.id
where salary>=20000;

-- 查询每个部门员工人数
select dname,count(name) from dept left join person on dept.id=person.dept_id group by dname;

右表全部显示，显示左表中与右表匹配的项

SELECT 字段列表
    FROM 表1  RIGHT JOIN  表2
ON 表1.字段 = 表2.字段;

--e.g.
select dname,count(name)
from person right join dept
on person.dept_id = dept.id
group by dname;