55 外键的变种 三种关系 单表的查询
主要内容:
1 外键的三种关系
1) 外键的分析步骤:
1 先站在左边的角度去找: 是否左表的多条记录可以对应右表的一条记录,如果是, 则证明左表的一个字段 foreign key, 右边一个字段通常是id.
2 是否右表的多条记录可以对应左表的一条记录, 如果是,则证明右表的一个字段foreign key,左边是一个字段通常是id.
2) 三种关系
多对一:
如果只有步骤一成立, 则是左表多对一右表
如果只有步骤二成立,则是右表多对一左表.
例题:
书和出版社 : 一个出版社可以出版多本书.
create table press(
id int primary key auto_increment,
name varchar(20)
);
create table book(
id int primary key auto_increment,
name varchar(20),
press_id int not null,
constraint fk_book_press foreign key(press_id) references press(id)
on delete cascade
on update cascade
);
多对多:
如果步骤一和步骤二同时成立, 则证明两张表存在一个双向的多对一,即多对多, 则需要定义一个这两张表的关系表来专门存放两者之间的关系.
例题:
作者和书籍的关系:
# 创建被关联表author表，之前的book表在讲多对一的关系已创建
create table author(
id int primary key auto_increment,
name varchar(20)
);
#这张表就存放了author表和book表的关系，即查询二者的关系查这表就可以了
create table author2book(
id int not null unique auto_increment,
author_id int not null,
book_id int not null,
constraint fk_author foreign key(author_id) references author(id)
on delete cascade
on update cascade,
constraint fk_book foreign key(book_id) references book(id)
on delete cascade
on update cascade,
primary key(author_id,book_id)
);
一对一:
如果两个都不成立, 而是左表的一条记录唯一对应右边的一条记录,反之亦然.这种情况很简单,就是在左表foreign key右表的基础上, 将左边的外键字段设置成unique即可.
例题: 用户和博客
#例如： 一个用户只能注册一个博客
#两张表： 用户表 （user）和 博客表(blog)
# 创建用户表
create table user(
id int primary key auto_increment,
name varchar(20)
);
# 创建博客表
create table blog(
id int primary key auto_increment,
url varchar(100),
user_id int unique,
constraint fk_user foreign key(user_id) references user(id)
on delete cascade
on update cascade
)
2 单表的查询
1) 单表查询的语法:
select 字段1,字段2.....from表名
where 条件
group by 字段
having 筛选
order by field 排序 : 升序asc 降序desc
limit 限制条数
2) 关键字的执行优先级:
from :找到表
where : 通过where限制条件, 从表中取出一条条记录
group by: 将取出的一条条记录进行分组, 如果没有group by整体为一组
having : 将分组的结果进行过滤
select : 执行select
order by: 将结果进行排序
limit : 限制结果的显示条数;
3) where 约束:
可以使用比较运算符: > < <> >= <= !=; select * from employee where salary > 1000;
between 80 and 100 : select * from employee where salary between 80 and 100;
in(23, 30, 36) 值是23 30或者36 select * from employee where age in (10,20,30);
select * from employee where age in (10,20,30);
关键字like模糊查询:'%'或者'_'称为速配符
ike 'xiao%' %表示任意多个字符 select * from employee where name like 'xiao%';
like 'xiao_' _ 表示一个字符 select * from employee where name like 'xiao_';
在多个条件之间可以使用逻辑运算符 and or not
select name , salary*12 from employee where post = 'teacher' and name like 'jin%';
select * from employee where post_comment is not null;
4) group by 分组查询
定义: 分组查询时基于where之后, 将所有的记录按照相同的字段分组;
由于sql没有设置ONLY_FULL_GROUP_BY,于是也可以有结果, 但都是默认为组内的第一条数据,其实没有意义.如果要分组, 则必须要设置全局的sql模式为 ONLY_FULL_GROUP_BY
sql语句:set global sql_mode='ONLY_FULL_GROUP_BY';(同时也需要把这个全局变量加入到配置文件中my.ini) 设置成功后. 一定要退出, 重新登录才有效.
注意: 可以按照任意字段分组, 分组后只能查看使用分组的字段, 不能查看组内的信息, 如果要查看组内的信息, 必须要通过聚合函数来完成.
聚合函数有: max() min() sum() avg() group_concat() count()
聚合函数的使用: select post, group_contat(name) from employee group by post
5) having 过滤
过滤与where不同的地方: 执行的优先级 where > group by > having
由于having发生在group by之后,所以having只能使用分组字段,不能直接使用其他的字段,可以使用聚合函数.
having的使用: select post, group_contact(name), count(1) from employe group by post having count(id)>2;
6) 补充知识点:
select id, 1 from employee 相当于添加了一个字段1 , 值也为1, 以后求个数的时候可以使用count(1) 效率高.
7) order by 查询排序
按单列排序: select * from employee order by age asc;升序
select * from employee order by age desc;降序
按多列排序: 先按age升序排序, 如果age相同, 再按照id降序排列
select * from employee order by age asc, id desc;
select post, avg(salary) from employe group by post having avg(salary)>10000 order by asc;
8) limit 限制查询的记录数:
示例：
SELECT * FROM employee ORDER BY salary DESC
LIMIT 3; #默认初始位置为0
SELECT * FROM employee ORDER BY salary DESC
LIMIT 0,5; #从第0开始，即先查询出第一条，然后包含这一条在内往后查5条
SELECT * FROM employee ORDER BY salary DESC
LIMIT 5,5; #从第5开始，即先查询出第6条，然后包含这一条在内往后查5条
