目录
一、外键约束简介
外键约束是用于多张数据表之间进行关联使用。外键分为主键和外键如下所示:
(1)主键 primary key
(2)外键 foreign key
若还是不太理解。比如,有两张数据表,且它们之间有关联。可以通过了某个字段来建立连接,这个字段在第一张表A中是主键primary key,在第二张表B中,就称为外键约束foreign key。
说明
(1)两张数据表,表A与表B都有主键;
(2)A与B产生关联性,将B表的主键关联到A表中的一个字段名,添加了外键约束,也可以称为外键。
添加外键约束语法:
create table 表名(
...
字段名 数据类型(长度) [约束],
foreign key (字段名) references 另外表名(主键列名)
) engine = InnoDB default charset utf8;
# 通过外部添加外键
alter table 表A add constraint xxxfk foreign key (外键字段名) references 另外表名(主键字段名);
说明:
(1)在设置外键约束时,需要使用到foreign key关键字;
(2)通常情况下,建议设定外键约束放在定义所有字段的结尾处。
二、表与表之间的关系
首先,了解一下表与表之间的关系!
这种关系指的是表与表之间数据的关联性,分别有:
(1)一对一关系(1:1)
(2)一对多关系(1:N) [常见]
(3)多对多关系(M:N) [常见]
2.1 一对一关系
一对一关系,就是在数据表A中的一条数据,在数据表B中都有一个与之对应的结果。
例如,用户表与用户详情表的关系。
两张表连接为一张表,如下:
2.2 一对多关系
一对多关系是实际开发中,是表与表之间关系里应用最多的。例如分类表与商品表。
分类表category与商品表product,其中,商品表里的cno就是外键约束字段。
通常情况下,一对多关系建表原则:在【多】的一方有个字段,作为外键字段指向【一】的一方的主键字段。
对于一对多关系通俗的理解就是有一个分类表,然后还有一个物品表。举个最通俗的例子,比如家具,厨具。而家具又包含很多的物品,例如桌子、椅子、柜子等,厨具又包含锅、碗、瓢、盆等。
一对多关系案例
例如,使用命令完成:
(1)新创建一个数据库班级
db_product3,设定编码为utf8;
(2)分类表category有编号、分类名称等字段,且编号是主键、字符串型;
(3)商品表product有编号、商品名称、价格等字段,且编号是主键、整型;
(4)请在商品表中,设定一个字段为外键,并关联分类表的主键;
(5)查看两个表结构信息,观察结果。
######################添加外键#############################
# 1.新建库
create database if not exists db_product3 charset utf8;
# 2.使用库
use db_product3;
# 3.创建分类表 [一] -主从关系 主表
create table if not exists category(
cid varchar(20) primary key not null ,
cname varchar(20)
) engine = InnoDB default charset utf8;
# 4.创建商品表: 新增一列、外键 [多] 从表
create table if not exists product(
pid int primary key auto_increment not null ,
pname varchar(20),
price double,
category_id varchar(20),
foreign key (category_id) references category(cid)
)engine = InnoDB default charset utf8;
# 5.查看表结构
desc category;
desc product;
-- 分类表数据:
# 添加分类表数据
insert into category values('c100','电器');
insert into category values('c200','服装');
insert into category values('c300','化妆品');
-- 商品表数据
# 添加商品表数据
insert into product(pname,price,category_id) values('海信',4999,'c100');
insert into product(pname,price,category_id) values('格力',2199,'c100');
insert into product(pname,price,category_id) values('海澜之家',699,'c200');
insert into product(pname,price,category_id) values('Nike',1233,'c200');
insert into product(pname,price,category_id) values('一叶子',99,'c300');
insert into product(pname,price,category_id) values('香奈儿',268,'c300');
2.3 多对多关系
多对多关系理解起来比较复杂,但通常会分解成多个一对多关系来关联处理。
比如,老师表与学生表。
而多对多关系创建表原则:
通常需要创建第三张表(综合表),并作为中间表,且该表至少有两个字段(都是外键),分别作为外键字段指向各自一方的主键。
通俗地说,就是将一个多对多关系拆分成:两个一对多关系的数据表。
例如,学生和课程的关系,即多个学生对应多个课程。
说明:
学生表、课程表这两张表都有主键,而第三张表【学生选课表】提供的两个字段sno、cno都是外键字段。
多对多案例
例如,使用命令完成:
(1)新建数据库班级
db_multi_product,并在其中创建货物表、订单表;
(2)货物表的字段信息有编号、名称、品牌、价格,其中货物编号为整型且自动增长的主键;
(3)订单表的字段信息有编号、订单金额,其中订单编号为字符串型的主键;
(4)创建一个中间表,并关联货物表、订单表;
(5)当成功创建表后,观察效果。
####################多对多关系############################
# 1.使用库
use db_product3;
# 2.创建表
# 货物表
create table if not exists tb_goods(
gid int primary key auto_increment,
gname varchar(20),
gbrand varchar(20),
gprice double
)engine = InnoDB default charset utf8;
# 订单表
create table if not exists tb_orders(
oid varchar(40) primary key ,
omoney double
)engine = InnoDB default charset utf8;
# 中间表
create table if not exists tb_item(
id int primary key auto_increment,
goods_id int,
orders_id varchar(40),
foreign key (goods_id) references tb_goods(gid),
foreign key (orders_id) references tb_orders(oid)
)engine = InnoDB default charset utf8;
# 观察关系
desc tb_goods;
desc tb_orders;
desc tb_item;
三、子查询
子查询指的是将一条select语句的结果,作为另一条select语句的一部分(如:查询条件、查询结果、表等),来进行数据查询。
子查询语法:
select *|字段名1,字段名2,.. from 表名 where 字段名 = (另外select语句的结果);
例如,使用命令完成:
(1)使用数据库班级
db_product3下的商品表和分类表来操作;
(2)查询商品表、分类表的所有数据信息;
(3)查询分类为"服装"的所有商品信息;[分类id、商品]
(4)查询商品名称为"格力"的分类信息;
(5)仅查询分类为"化妆品"下的所有商品名称。
########################子查询##################################
# 1.使用库
use db_product3;
# 2.查看所有数据
select * from category;
select * from product;
# 技巧:查找最终的数据内容是谁,就from哪个表
# 3.查看分类为服装的商品信息 --通过分类表来查看商品
# a.先查找分类为服装的所有分类信息
# select * from category where cname='服装';
select cid from category where cname='服装';
# b.查找分类为服装的商品信息
select * from product where category_id='c200';
# 综合
select * from product where category_id=(select cid from category where cname='服装');
# 4.查看格力的分类信息 --通过商品表找分类信息
select * from category where cid=(select category_id from product where pname='格力');
# 5.查看化妆品的所有商品信息 -通过分类表查找商品信息
select
*
from product
where category_id =
(
select
cid
from category
where cname = '化妆品'
);
四、多表查询
多表查询,也可称为连接查询,可用于实现多个数据表之间的数据查询。
注意:当查询的字段数据来自不同的数据表时,就可以使用多表查询来完成。
多表查询可以分为4类:
(1)交叉连接查询
(2)内连接查询
(3)外连接查询
(4)自连接查询
为了便于后续的多表查询操作,我们先准备基础数据并添加至表中。
例如,使用命令完成:
(1)商品类别表信息有分类编号cid、名称cname;
(2)商品表信息有商品编号pid、名称pname、价格price、是否上架flag、外键category_id(关联商品类别表的分类编号cid);
(3)添加数据到两个数据表中。
###################添加数据##########################
create database if not exists db_product4 charset utf8;
use db_product4;
# 多表查询
create table table_category
(
cid varchar(20) primary key,
cname varchar(40)
) engine = InnoDB default charset utf8;
create table table_product
(
pid varchar(20) primary key,
pname varchar(40),
price double,
flag varchar(2), # 是否上架标记为:1表示上架、0表示下架
category_id varchar(20),
foreign key (category_id) references table_category(cid) # constraint products_fk
) engine = InnoDB default charset utf8;
# 分类
insert into table_category(cid, cname) values('c001', '电器');
insert into table_category(cid, cname) values('c002', '服装');
insert into table_category(cid, cname) values('c003', '化妆品');
# 商品
insert into table_product(pid, pname, price, flag, category_id) values('p001', '联想', 5000, '1', 'c001');
insert into table_product(pid, pname, price, flag, category_id) values('p002', '海尔', 3000, '1', 'c001');
insert into table_product(pid, pname, price, flag, category_id) values('p003', '雷神', 5000, '1', 'c001');
insert into table_product (pid, pname, price, flag, category_id) values('p004', 'JACK JONES', 800, '1', 'c002');
insert into table_product (pid, pname, price, flag, category_id) values('p005', '真维斯', 200, '1', 'c002');
insert into table_product (pid, pname, price, flag, category_id) values('p006', '花花公子', 440, '1', 'c002');
insert into table_product (pid, pname, price, flag, category_id) values('p007', '劲霸', 2000, '1', 'c002');
insert into table_product (pid, pname, price, flag, category_id) values('p008', '香奈儿', 800, '1', 'c003');
insert into table_product (pid, pname, price, flag, category_id) values('p009', '相宜本草', 200, '1', 'c003');
insert into table_product (pid, pname, price, flag, category_id) values('p010', '迪奥', 1600, '1', 'c003');
insert into table_product (pid, pname, price, flag, category_id) values('p011', '一叶子', 799, '1', 'c003');
4.1 交叉连接
在MySQL多数据表查询操作中,交叉连接查询操作几乎不会使用,在这里仅作为了解即可。
因为交叉连接没有意义,但是它是所有连接查询的基础。
通常地,交叉连接查询的结果是:两个数据表的并集,也称为笛卡尔积。
交叉连接查询的语法:
select 字段名 from A表名, B表名;
或者:
select 字段名 from A表名 cross join B表名;
例如,使用交叉查询命令完成:
(1)使用语法1查询类别表和商品表中的所有商品信息;
(2)使用语法2查询类别表和商品表中的所有商品信息。
########################交叉连接查询###########################################
# 1.使用库
use db_product4;
select * from table_category;
select * from table_product;
# 2.使用语法1交叉查询结果
# select * from table_category,table_product; # 查询结果没意义
# 3.使用语法2交叉查询结果
# select * from table_category cross join table_product;
# 扩展
select * from table_category,table_product;
# 表名 as 别名
# 表名 别名
select * from table_category as c,table_product as p;
select * from table_category c,table_product p;
# 建议:多表查询时给表名取别名
4,2 内连接查询
内连接查询指的是查询两个数据表中,符合条件的共有记录,即交集。
内连接查询需要使用join关键字。
对于内连接查询,可分为两类,分别是:隐式内连接和显式内连接。
(1)作为了解,隐式内连接是指没有inner join关键字,而条件直接使用where来指定,语法:
select 字段名 from A表名, B表名 where 条件;
(2)显式内连接是指需要使用inner join ... on语句,可以省略inner,语法:
select 字段名 from A表名 别名 inner join B表名 别名 on 条件 [where 查询条件];
select 字段名 from A表名 别名 join B表名 别名 on 条件 [where 查询条件];
例如,使用内连接查询命令完成:
(1)使用隐式内连接查询类别表和商品表的共有数据信息;
(2)使用显式内连接来查询类别表和商品表的公共商品信息;
(3)使用显式内连接来查询类别表中cid与商品表category_id相等的公共商品信息的类别编号信息,并去除重复值。
########################内连接查询###################################
# 1.使用库
use db_product4;
# 2.隐式内连接
# select * from table_category, table_product where table_category.cid = table_product.category_id;
# select * from table_category c, table_product p where c.cid = p.category_id;
# 3.显式内连接
select * from table_category c inner join table_product p on c.cid=p.category_id;
select * from table_category c join table_product p on c.cid=p.category_id;
# DG
select * from table_category c join table_product tp on c.cid = tp.category_id;
# 4.显式内连接,去重
select * from table_category tc join table_product tp on tc.cid = tp.category_id;
select distinct cid from table_category tc join table_product tp on tc.cid = tp.category_id;
4.4 外连接查询
在多表查询操作中,外连接查询需要使用关键字outer join,一般应用中会省略outer,而直接使用join。
此外,外连接查询还可分为两类,分别是:
(1)左外连接查询
(2)右外连接查询
为了区分A表名和B表名,简易命名为左表和右表。
左外连接也简称为左连接,是以左表为主并根据条件来查询右表数据。
左外连接查询需要使用left join关键字,语法:
select 字段名 from A表名 别名 left [outer] join B表名 别名 on 条件;
例如,使用左外连接查询命令完成:
(1)以左表为主,连接查询类别表和商品表中的所有商品数据信息。
################################左连接##############################################
# 1.使用库
use db_product4;
# 2.查询结果
select * from table_category c left outer join table_product p on c.cid=p.category_id;
select * from table_category c left join table_product tp on c.cid = tp.category_id; # 省略outer
4.5 右外连接
右外连接也简称为右连接,以右表为主并根据条件来查询左表数据。
右外连接查询需要使用right join关键字,语法:
select 字段名 from A表名 别名 right [outer] join B表名 别名 on 条件;
例如,使用右外连接查询命令完成:
(1)以右表为主,连接查询类别表和商品表中的所有类别数据信息。
####################右外连接查询##########################
# 1.使用库
use db_product4;
# 2.右外连接查询数据
select * from table_category c right outer join table_product p on c.cid=p.category_id;
select * from table_category c right join table_product p on c.cid=p.category_id;
4.6 左、右外连接的区别
(1)当左表有数据为null时,左外连接查询的结果中,右表的数据结果都为null值;
(2)当右表有数据为null时,右外连接查询的结果中,左表的数据结果都为null值。
例如,使用命令完成:
(1)给类别表添加一条名称为null的数据;
(2)给商品表添加一条外键category_id为null的数据;
(3)查询类别表、商品表的数据结果,观察数据是否已成功添加;
(4)使用左外连接以左表为主,连接查询类别表和商品表中的所有商品数据信息,观察查询结果;
(5)使用右外连接以右表为主,连接查询类别表和商品表中的所有类别数据信息,观察查询结果。
######################了解左右外连接的查询区别###############################
# 1.使用库
use db_product4;
# 2.设定类别表cname=null
desc table_category;
insert into table_category values ('A666',null);
# 3.设定商品表category_id=null
desc table_product;
insert into table_product(pid,pname, price, flag, category_id) values ('Y888','西红柿',5.9,'1',null);
# 4.查询结果
select * from table_category;
select * from table_product;
# 5.左外连接
select * from table_category c left join table_product tp on c.cid = tp.category_id;
# 6.右外连接
select * from table_category c right join table_product tp on c.cid = tp.category_id;
4.7 自连接查询
自连接查询,顾名思义,就是数据表自己和自己连接,然后进行数据比较。
其实,自连接查询指的是:左表和右表都是同一个表,根据连接查询条件来查询两个表中的数据。
特别注意:
在进行连接查询操作时,必须为数据表定义别名!
例如,使用命令完成:
(1)创建一个区域表tb_area,信息有省编号pid为整型自增且主键、名称name为字符串、城市编号city_id为整型;
(2)给区域表tb_area添加批量数据,以(1,'广东省',null)、(4,'深圳市',1)形式让城市和省份进行关联;
(3)查询所有数据结果。
#############################自连接###################################
# 1.新建库
create database if not exists db_area charset utf8;
# 2.使用库
use db_area;
# 3.创建表
create table if not exists tb_area(
pid int primary key auto_increment,
name varchar(20),
city_id int
) engine = InnoDB default charset utf8;
desc tb_area;
# 4.插入数据 广东、河南、湖南
insert into tb_area(name, city_id) values ('广东省',null); # 1
insert into tb_area(name, city_id) values ('河南省',null); # 2
insert into tb_area(name, city_id) values ('湖南省',null); # 3
# 深圳、广州、东莞
insert into tb_area(name, city_id) values ('广州市',1);
insert into tb_area(name, city_id) values ('深圳市',1);
insert into tb_area(name, city_id) values ('东莞市',1);
# 郑州、洛阳、商丘
insert into tb_area(name, city_id) values ('郑州市',2);
insert into tb_area(name, city_id) values ('洛阳市',2);
insert into tb_area(name, city_id) values ('商丘市',2);
# 长沙、岳阳、衡阳
insert into tb_area(name, city_id) values ('长沙市',3);
insert into tb_area(name, city_id) values ('岳阳市',3);
insert into tb_area(name, city_id) values ('衡阳市',3);
# 5.查看数据
select * from tb_area;
查询结果
内连接查询语法:
select *|字段名1,字段名2,... from A表名 a join B表名 b on a.字段名 = b.字段名 [where 条件];
例如,使用自连接查询命令完成:
(1)把区域表tb_area分别理解成两个表:省表province、城市表city;
(2)自连接查询省份编号、省名、城市名、城市编号的展示结果;
(3)自连接查询省的名称为广东省的所有城市信息。
###########################自连接#####################################
# 1.使用库
use db_area;
# 2.自连接: 省表、城市表
select
*
from tb_area province
join tb_area city
on
province.pid = city.city_id;
select
province.pid,
province.name,
city.pid,
city.name
from tb_area province
join tb_area city
on
province.pid = city.city_id;
# 取别名给列
select
province.pid as pid,
province.name as pname,
city.pid as cid,
city.name as cname
from tb_area province
join tb_area city
on
province.pid = city.city_id;
# 扩展:能否给列名取中文别名? --不建议
select
province.pid as 省ID,
province.name as 省名称,
city.pid as 城市ID,
city.name as 城市名称
from tb_area province
join tb_area city
on
province.pid = city.city_id;
# 3.显示名称为: 广东
select
province.pid as pid,
province.name as pname,
city.pid as cid,
city.name as cname
from tb_area province
join tb_area city
on
province.pid = city.city_id
where province.name='广东省';
select
city.pid as cid,
city.name as cname
from tb_area province
join tb_area city
on
province.pid = city.city_id
where province.name='广东省';
# 扩展: 就想要使用别名
select *
from
(
select
province.pid as pid,
province.name as pname,
city.pid as cid,
city.name as cname
from
tb_area province
join tb_area city
on
province.pid = city.city_id
) as temp
where
pname = '广东省';
# 优先级: from > where > select
MySQL基础到此就告一段落了,希望对大家有所帮助,预祝各位考试全过,面试全过!