最佳网站制作模板,用手机能创建网站吗,触屏网站模板,wordpress中文字体库目录
表与表的关联关系#xff1a;
一对一 一对多
多对多
多表查询
交叉连接
内连接
外连接
全连接#xff08;union、union all#xff09;
自连接
子查询 为什么要有多表#xff1a; 当一张表中的数据比较多的时候#xff0c;如果只建立一张表#x…目录
表与表的关联关系
一对一 一对多
多对多
多表查询
交叉连接
内连接
外连接
全连接union、union all
自连接
子查询 为什么要有多表 当一张表中的数据比较多的时候如果只建立一张表可能会有大量的数据重复冗余站在设计的角度上来讲将不同的数据放在对应的独立的表中可以减少冗余
外键foreign key 用来描述多张表之间的关联关系
表与表的关联关系
一对一
一对一 如用户和用户详情 一对一关系多用于表的拆分将一个实体中经常使用的字段放一张表中不经常使用的数据放在另一张表中用于提升查询性能。实现方式 外键可以放在任何一张表都行。 而在真正使用过程中发现 id、photo、nickname、age、gender 字段比较常用此时就可以将这张表查分成两张表。 建表如下
create table tb_user_desc (id int primary key auto_increment,city varchar(20),edu varchar(10),income int,status char(2),des varchar(100)
);create table tb_user (id int primary key auto_increment,photo varchar(100),nickname varchar(50),age int,gender char(1),desc_id int unique,-- 添加外键CONSTRAINT fk_user_desc FOREIGN KEY(desc_id) REFERENCES tb_user_desc(id)
); 一对多
一对多 如部门与员工 一个部门对应多个员工一个员工对应一个部门实现方式 在多的一方建立外键指向一的一方主键 经分析员工属于多的一方而部门表属于一的一方此时我们会在员工表中添dep_id指向部门表的主键id 见表语句
-- 部门表
CREATE TABLE tb_dept(id int primary key auto_increment,dep_name varchar(20),addr varchar(20)
);-- 员工表
CREATE TABLE tb_emp(id int primary key auto_increment,name varchar(20),age int,dep_id int,-- 添加外键 dep_id,关联 dept 表的id主键CONSTRAINT fk_emp_dept FOREIGN KEY(dep_id) REFERENCES tb_dept(id)
); 多对多
多对多 如商品和订单 一个商品对应多个订单一个订单包含多个商品实现方式 外键不管放在哪一方都会产生冗余 建议建立第三张中间表中间表至少包含两个外键分别关联两方主键 经过分析发现订单表和商品表都属于多的一方此时需要创建一个中间表在中间表中添加订单表的外键和商品表的 外键指向两张表的主键 建表语句如下
-- 订单表
CREATE TABLE tb_order(id int primary key auto_increment,payment double(10,2),payment_type TINYINT,status TINYINT
);-- 商品表
CREATE TABLE tb_goods(id int primary key auto_increment,title varchar(100),price double(10,2)
);-- 订单商品中间表
CREATE TABLE tb_order_goods(id int primary key auto_increment,order_id int,goods_id int,count int
);-- 建完表后添加外键
alter table tb_order_goods add CONSTRAINT fk_order_id FOREIGN key(order_id) REFERENCES
tb_order(id);
alter table tb_order_goods add CONSTRAINT fk_goods_id FOREIGN key(goods_id) REFERENCES
tb_goods(id);
注意数据库中可以通过多个字段共同作用充当主键 多表查询
多表查询顾名思义就是从多张表中一次性的查询出我们想要的数据。先准备环境如下
# 创建部门表
CREATE TABLE dept(did INT PRIMARY KEY AUTO_INCREMENT,dname VARCHAR(20));# 创建员工表
CREATE TABLE emp (id INT PRIMARY KEY AUTO_INCREMENT,NAME VARCHAR(10),gender CHAR(1), -- 性别salary DOUBLE, -- 工资join_date DATE, -- 入职日期dep_id INT,FOREIGN KEY (dep_id) REFERENCES dept(did) -- 外键关联部门表(部门表的主键));-- 添加部门数据
INSERT INTO dept (dNAME) VALUES (研发部),(市场部),(财务部),(销售部);
-- 添加员工数据
INSERT INTO emp(NAME,gender,salary,join_date,dep_id) VALUES
(孙悟空,男,7200,2013-02-24,1),
(猪八戒,男,3600,2010-12-02,2),
(唐僧,男,9000,2008-08-08,2),
(白骨精,女,5000,2015-10-07,3),
(蜘蛛精,女,4500,2011-03-14,1),
(小白龙,男,2500,2011-02-14,null);
交叉连接 不适合任何匹配条件生成笛卡尔积。不告诉数据库表与表的关系数据库底层会进行全匹配。在真实开发中一定不要使用交叉连接
执行语句
-- 方式一
select * from dept,emp;-- 方式二
select * from dept cross join emp;从上面的结果我们可以看出一些无效的数据如孙悟空 这个员工属于1号部门但是也同时关联了2、3、4号部们。 内连接
将需要的表中的数据通过外键 一 一 关联显示
语法
-- 隐式内连接
SELECT 字段列表 FROM 表1,表2... WHERE 条件;-- 显示内连接 关键字inner可以省略
SELECT 字段列表 FROM 表1 [INNER] JOIN 表2 ON 条件 [where 条件]
内连接相当于查询AB交集数据 案例
隐式内连接
select * from emp,dept where emp.dep_id dept.did;
执行上述语句如下 查询emp的namegender dept表的dname
SELECT emp. NAME,emp.gender,dept.dname FROM emp, dept WHERE emp.dep_id dept.did;
执行语句如下 上面语句中使用表名指定字段所属有点麻烦sql也支持给表指别名上述语句可以改进为
SELECT t1. NAME, t1.gender, t2.dname FROM emp t1, dept t2 WHERE t1.dep_id t2.did;
显示内连接
select * from emp inner join dept on emp.dep_id dept.did;
-- 上面语句中的inner可以省略可以书写为如下语句
select * from emp join dept on emp.dep_id dept.did;
执行结果如下 外连接
语法
-- 左外连接
SELECT 字段列表 FROM 表1 LEFT [OUTER] JOIN 表2 ON 条件;-- 右外连接
SELECT 字段列表 FROM 表1 RIGHT [OUTER] JOIN 表2 ON 条件; 左外连接相当于查询A表所有数据和交集部分数据 右外连接相当于查询B表所有数据和交集部分数据 案例
查询emp表所有数据和对应的部门信息左外连接
select * from emp left outer join detp on emp.dep_id dept.did;
执行语句结果如下 结果查询显示查询到了左表emp中所有数据及两张表能关联的数据。 查询dept表所有数据和对应的员工数据信息右外连接
select * from emp right join dept on emp.dep_id dept.did;
执行结果如下 结果显示查询到了右表dept中所有的数据及两张表能关联的数据
要查询出部门表中所有的数据也可以通过左外连接实现只需要将两个表的位置进行互换
select * from dept left join emp on emp.dep_id dept.did; 全连接union、union all 将多余SQL查询的结果映射为一个结果集显示。意思就是将SQL查询的多个结果合并起来返回一个结果给用户 union all将结果全部展示如果重复也展示重复部分 union会去重不会展示重复部分。
案例
union
-- union的用法
select * from emp where id 1
union
select * from emp where id 2
union
select * from emp where id 5;
查询结果 union all
-- union all
select * from emp where id 1
union
select * from emp where id 2
union all
select * from emp where id 5;
查询结果 练习 将所有员工的部门展示出来并且也要将所有部门的员工展示出来
代码如下
select * from emp left join dept on emp.dep_id dept.d_id
union
select * from emp right join dept on emp.dep_id dept.d_id ;
执行结果如下 自连接 有时候可能需要将一张表当作多张表用。通常情况下该存在外键且外键关联自身。
当我们做项目时要做一个板块板块里有一个子版块子版块里可能还有子版块等等如果有n个板块的话我们可以建n张表并使用外键一一将他们关联起来。但是我们发现在这些板块中除了外键关联的主键不同外其他的字段名包括约束都是一样的这样不仅会使代码冗余而且浪费了标识符。所以我们可以建一张表。
语句如下
CREATE TABLE board(id int primary key auto_increment,name varchar(100) not null unique, -- 模块名intro text, -- 模块介绍parent_id int -- 外键); 而想要查询第一板块也很简单查询第一模块的查询条件就是parent_id的只为null
语句如下 select * from board where parent_id is null;
执行结果: 查询第二版块的话需要借助子查询
代码如下
select name from board
where
parent_id
( select id from board where name Java);
执行结果 子查询
概念
1.第一次查询的结果当做表或者条件进行第二次查询。
2一般而言子查询的效率较差。至少需要两次查询。
3.子查询中包含IN、NOT IN、ANY、ALL、EXISTS和NOT EXISTS 等关键字。
4.还可以包含比较运算符、!、、等。
注意
1.子查询要包含在括号内。
2.将子查询放在比较条件的右侧
3.单行操作符对应单行子查询多行操作对应多行子查询
案例查询工资高于猪八戒的员工信息
第一步先查询出来猪八戒的工资
select salary from emp where name 猪八戒
第二步将第一步查询出来的猪八戒工资作为条件 查询工资高于猪八戒的员工信息。
select * from emp where salary (select salary from emp where name 猪八戒); 执行结果 子查询根据查询结果不同作用不同 子查询语句结果是单行单列子查询语句作为条件值使用 ! 等进行条件判断子查询语句结果是多行单列子查询语句作为条件值使用 in 等关键字进行条件判断子查询语句结果是多行多列子查询语句作为虚拟表 案例
查询‘财务部’和‘市场部’所有的员工信息
-- 查询 财务部 或者 市场部 所有的员工的部门did
select did from dept where dname 财务部 or dname 市场部;
select * from emp where dep_id in (select did from dept where dname 财务部 or dname
市场部);
查询入职日期是‘2011-11-11’之后的员工信息和部门信息
-- 查询入职日期是 2011-11-11 之后的员工信息
select * from emp where join_date 2011-11-11 ;
-- 将上面语句的结果作为虚拟表和dept表进行内连接查询
select * from (select * from emp where join_date 2011-11-11 ) t1, dept where
t1.dep_id dept.did;
