第四章 多表查询

第一节 表与表之间的关系

1. 表与表之间的关系

数据表是用来描述实体信息的，比如可以使用数据表描述学生信息，也可以用数据表来描述班级信息，这样就会存在学生表和班级表显然存在着一种关系

2. 主外键关联关系的定义

create table cls(
    id int(11) auto_increment not null primary key comment '班级编号',
    name varchar(20) not null comment '班级名称',
    grade varchar(20) not null comment '年级'
);
​
create table student(
    number bigint(20) auto_increment not null primary key comment '学号',
    name varchar(20) not null comment '姓名',
    sex varchar(2) default '男' comment '性别',
    age tinyint(3) default 0 comment '年龄',
    cls_number int(11) not null foreign key references cls(id)
);

3. 约束

3.1 主键约束

-- 添加主键约束，保证数据的唯一性
alter table 表名 add primary key(字段名1,字段名2, ... , 字段名n)
-- 删除主键约束
alter table 表名 drop primary key

3.2 外键约束

-- 删除外键约束
alter table 表名 drop foreign key 外键名称;
-- 添加外键约束
alter table 表名 add constraint 约束名称 foreign key(表1字段名1) references 表2(字段名1);

3.3 唯一约束

-- 为字段添加唯一约束
alter table 表名 add constraint 约束名称 unique(字段名1,字段名2,...,字段名n);
-- 删除字段的唯一约束
alter table 表名 drop key 约束名称;

3.4 非空约束

-- 为字段添加非空约束
alter table 表名 modify 字段名 列类型 not null;
-- 删除字段的默认值
alter table 表名 modify 字段名 列类型 null;

3.5 默认约束

-- 为字段添加默认约束
alter table 表名 alter 字段名 set default 值;
-- 为字段删除默认约束
alter table 表名 alter 字段名 drop default;

3.6 自增约束

-- 为字段添加自增约束
alter table 表名 modify 字段名 列类型 auto_increment;
-- 为字段删除自增约束
alter table 表名 modify 字段名 列类型;

第二节 索引

1 什么是索引

在关系数据库中，索引是一种单独的，物理的对数据表中一列或多列的值进行排序的一种存储结构，它是表中一列或多列值的集合和相应的指向表中物理标识这些值的数据页的逻辑指针清单

2 索引的作用

• 保证数据的准确性

• 提高检索速度

• 提高系统性能

3 索引的类型

• 唯一索引(UNIQUE) ：不可以出现相同的值，可以有NULL值

• 普通索引(INDEX)：允许出现相同的索引内容

• 主键索引(PRIMARY KEY)：不允许出现相同的值

• 全文索引(FULLTEXT INDEX)：可以针对值中的某个单词，但效率不高

• 组合索引：实质上是将多个字段建到一个索引里，列值的组合必须唯一

4 索引的创建，查看，删除

-- 创建索引
alter table 表名 add index 索引名称(字段名)
-- 查看索引
show index from 表名;
-- 删除索引
alter table 表名 drop index 索引名称;

注意：

1.索引会提高查询速度，但是更新和修改的操作速度会降慢

2.建立索引会占用磁盘空间，所以索引建立过多会降低数据库性能

3.like操作不建议使用索引，只有like'匹配内容%'的形式索引才会起作用

第三节 多表查询

1 笛卡尔积

每一张表都可以看作是一个数据的集合，多表关联在一起，会形成笛卡尔积

2 内连接

内连接相当于在笛卡尔积的基础上加上了连接条件，当没有连接条件时，内连接上升为笛卡尔积

select 字段名1,字段名2, ..., 字段名n from 表1 inner join 表2 on 连接条件;

3 外连接

外连接涉及两张表，主表和从表，要查询的信息主要来自于哪张表，哪张表就是主表

外连接查询的结果为主表中所有的记录，如果从表中有和它匹配的，则显示匹配的值，没有和它匹配的，则显示为null

3.1 左外连接

select 字段名1,字段名2, ..., 字段名n from 主表 left join 从表 on 连接条件;

3.2 右外连接

select 字段名1,字段名2, ..., 字段名n from 从表 right join 主表 on 连接条件;

第四节 子查询

1. 什么是子查询

子查询就是嵌套查询，子查询可以出现在select......from之间，from.......where之间，where之后

2. FROM...WHERE 之间

执行时机：外层查询之后，再进行内层查询

查询stu表所有学生信息，并将性别按男，女，其他展示

select
    id,
    name,
    (select text from dict where type='sex' and value=sex) sex,
    birthday,
    class
from
    stu;

3. FROM...WHERE 之间

执行时机：先内层查询，再外层查询

4. where之后

查询java成绩最高的学生信息

select * from stu a inner join score b on a.id=b.stu_id
where b.score=(select max(score) from score where course='Java')
and b.course='Java';