09-mysql多表查询
来源：互联网 发布：网络密匙 编辑：程序博客网 时间：2024/05/23 13:32
今日任务
完成对MYSQL数据库的多表查询及建表的操作
教学目标
掌握MYSQL中的表关系分析并能正确建表
掌握MYSQL中多表的查询
一、使用SQL 创建多表及分析多表的关系
需求分析:
分类表和商品表之间是不是有关系? 如果有关系,在数据库中如何表示这种关系
--创建一个新的数据库 day09
create database day09;
--创建分类表 cid分类的ID cname分类名称 cdesc分类描述
create table category(
cid int primary key auto_increment, --分类
cname varchar(20), -- 分类名称
cdesc varchar(30) -- 分类描述
);
-- 插入数据到分类表中
insert into category values(null,'手机数码','黑马生产的小手机');
insert into category values(null,'鞋靴箱包','传智生产的包包');
insert into category values(null,'香烟酒水','黄鹤楼,茅台');
insert into category values(null,'馋嘴零食','卫龙辣条,周黑鸭');
--创建一个商品 product pid pname price pdesc cno
create table product(
pid int primary key auto_increment, -- 商品id
pname varchar(10) unique, -- 商品名称
price double, -- 商品价格
pdesc varchar(30), -- 商品描述
cno int -- 分类id
);
-- 插入数据到商品表中
insert into product values(null,'华为手机',500,'支持国产',1);
insert into product values(null,'锤子',2999,'可以砸榴莲的手机',1);
insert into product values(null,'小米',1599,'为发烧而生',1);
insert into product values(null,'李宁',99,'不走寻常路',2);
insert into product values(null,'耐克',399,'just do it',2);
insert into product values(null,'黄鹤楼',20,'饭后一根烟',3);
insert into product values(null,'卫龙辣条',5,'卫龙辣条加料不加价',4);
insert into product values(null,'老干妈辣椒酱',9,'永远不变的味道',4);
insert into product values(null,'老干娘辣椒酱',null,'永远不变的味道',4);
技术分析:
1多表的关系及创建
1.1多表之间的关系
关联关系:在实际开发中,需要根据实体的内容设计数据表,实体间会有各种关联关系,所以根据实体设计的数据表之间也存在着各种关联关系,MySQL中数据表的关联关系有三种,具体如下:- 一对多/多对一
- 多对一是数据表中最常见的一种关系,比如,员工和部门之间的关系,一个部门可以有多个员工,而一个员工不能属于多个部门,也就是说部门表中的一行在员工表中可以有许多匹配行,但员工表中的一行在在部门表中只能有一个匹配行。
- 表之间的关系通过外键建立的，在多对一的表关系中，应该将外键建在多的一方，否则造成数据的冗余。
- 多对多
- 多对多也是数据表中的一种关系，比如学生与课程之间的关系，一个学生可以选择多门课程，当然一门课程也供多个学生选择，也就是说学生表中的一行在课程表中可以有许多匹配行，课程表中的一行在学生表中的也有许多匹配行。
- 通常情况下，为了实现这种关系，需要定义一张中间表（称为连接表），该表会存在两个外键，分别参考课程表和学生表，在多对多关系中，需要注意的是，连接表的两个外键都是可以重复的，但是两个外键之间的关系是不能重复的，所以这两个外键又是连接表的联合主键
- 一对一
- 一对一关系在实际生活中比较常见，例如人与身份证之间就是一对一的关系，一个人对应一张身份证，一张身份证只能匹配一个人，那么一对一关系的两张表如何建立外键？
- 首先要分清主从关系，从表需要主表的存在才有意义，身份证需要人的存在才有意义。因此人为主表，身份证为从表，要在身份证中建立外键，由实际的经验可知，身份证中的外键必须是非空唯一的，因此会直接用从表（表身份证）的主键作为外键。
1.2多表之间的关系如何来维护
-- 数据库的五大约束:
主键约束: primary key 唯一 / 非空
唯一约束 : unique
非空约束 : not null
检查约束: mysql里面不支持 check
外键约束: foreign key
--外键约束(多表约束): 外键是指引用另一张表中的一列或多列,被引用的列应该具有主键约束或唯一性约束**.外键用于建立和加强两个表数据之间的连接，实际上是用来约束两张表,约束从表中的记录必须存在于主表中
作用:就是确保表中数据的完整性
从表:商品表依赖于分类表,所以商品称为从表
主表:分类表
书写语法:
-- 添加外键约束方式一 语法格式
alter table 表名 add foreign key(外键字段名) references 主表表名(主键字段名);
--添加外键约束方式二 建表时候就添加外键 语法格式
foreign key(列名) references 表名(列名)
注意：
- 插入数据: 数据必须先存在于主表,然后再插入从表中的数据
- 删除主表数据时候: 必须先删除从表中已引用了主表中数据,再删除主表中的数据
- 删除表的时候,如果有从表里面有外键约束,必须先删除从表再删除主表
- 从表在添加外键约束的时候,外键必须是主表中的主键
1.3多表之间的建表原则
- 一对多:
- 建表原则: 在多的一方添加一个键,然后让它作为外键指向一的一方
- 例如:学生和班级,一个班级对多个学生,一个学生只属于一个班级,那么就可以在学生表里面建立外键指向班级表的主键
- 商品分类和商品,一个分类下可以有多个商品,一个商品只属于一个分类,那么就可以在商品表里面建立外键指向商品分类表
- 多对多:
- 建表原则:创建一张中间表,将多对多的关系,拆分成一对多的关系, 中间表至少要包含两个外键,这两个外键分别指向各自原来的表
- 例如:学生和选课
- 一对一:
- 建表原则: 1. 将两张表的主键建立连接, 2. 将两张合并成一张表 3. 将一对一的关系,拆分成一对多的关系
- 合并表,拆表
- 例如公民和身份证号
- 网上商城表实例的分析: 用户购物流程
- 用户表
- 订单表
- 商品表
- 商品分类表
- 用户表 (用户的ID,用户名,密码,手机)
-- 创建用户表
create table user(
userid int primary key auto_increment, -- 用户id
username varchar(20) unique, -- 用户名
password varchar(40) not null, -- 用户密码
phone varchar(11) -- 手机号码
);
insert into user values(null,'zs','333333','333333333');
insert into user values(null,'ls','4444','444444444');
insert into user values(null,'ww','5555','55555555');
insert into user values(null,'zl','6666','66666666');
- 订单表 (订单编号,总价,订单时间 外键-->用户的ID)
-- 创建订单表
create table orders(
oid int primary key auto_increment, -- 订单id
totalprice double, -- 订单金额
createtime timestamp, -- 下单时间
uno int, -- 用户id
foreign key(uno) references user(userid) -- 给用户id添加外键约束
);
订单1: 锤子, 李宁 3100
订单: 黄鹤楼*5
insert into orders values(null,3100,null,1);
insert into orders values(null,100,null,1);
- 分类表
-- 创建商品分类表(分类ID,分类名称,分类描述)
CREATE TABLE category(
cid INT PRIMARY KEY AUTO_INCREMENT, -- 分类id
cname VARCHAR(20), -- 分类名称
cdesc VARCHAR(20) -- 分类描述
);
INSERT INTO category VALUES(NULL,'手机数码','黑马生产的小手机');
INSERT INTO category VALUES(NULL,'鞋靴箱包','传智生产的包包');
INSERT INTO category VALUES(NULL,'香烟酒水','黄鹤楼,茅台');
INSERT INTO category VALUES(NULL,'馋嘴零食','卫龙辣条,周黑鸭');
INSERT INTO category VALUES(NULL,'电脑办公','联想电脑外星人');
- 商品表 (商品ID, 商品名称,商品价格,商品描述,外键cno)
-- 创建商品表
create table product(
pid int primary key auto_increment, -- 商品id
pname varchar(20), -- 商品名称
price int, -- 商品价格
pdesc varchar(40), -- 商品描述
cno int, -- 商品类别
foreign key(cno) references category(cid)
);
insert into product values(null,'锤子',2999,'可以砸榴莲的手机',1);
insert into product values(null,'小米',1599,'为发烧而生',1);
insert into product values(null,'李宁',99,'不走寻常路',2);
insert into product values(null,'耐克',399,'just do it',2);
insert into product values(null,'黄鹤楼',20,'饭后一根烟',3);
insert into product values(null,'黄鹤楼',20,'饭后一根烟',3);
insert into product values(null,'卫龙辣条',5,'卫龙辣条加料不加价',4);
insert into product values(null,'老干妈辣椒酱',9,'永远不变的味道',4);
insert into product values(null,'老干娘辣椒酱',null,'永远不变的味道',4);
insert into product values(null,'热干面',10,'大武汉热干面',null);
- 订单项: 中间表(订单ID,商品ID,商品数量,订单项总价)
-- 创建订单项中间表
create table orderitem(
ono int, -- 订单id
pno int, -- 商品id外键
num int, -- 商品数量
subprice double, -- 订单项总价
foreign key(ono) references orders(oid), -- 给中间表中的订单id添加外键指向订单表中id
foreign key(pno) references product(pid) -- 给中间表中商品id添加外键指向商品表中的商品id
);
订单1: 锤子, 李宁 3100
insert into orderitem values(1,1,1,3000);
订单2: 黄鹤楼*5
insert into orderitem values(2,5,5,100);
二、案例:使用商城表完成对商品信息的多表查询
需求分析:
在我们的商城案例中,我的订单中包含很多信息.打开我的订单需要去查询表技术分析:
2多表查询
概述：在关系型数据库管理系统中，建立表时各个数据之间的关系不必确定，通常将每个实体的所有信息存放在一个表中，当查询数据时候，通过连接操作查询多个表中的实体信息，当两个或多个表中存在相同意义的字段时候，便可以通过这些字段对不同的表进行连接查询，连接查询包括交叉连接查询，内连接查询，外连接查询.2.1 -交叉连接查询 (了解)
概述:交叉连接返回的结果是被连接的两个表中所有数据行的笛卡尔积,也就是返回第一个表中符合查询条件的数据行乘以第二个表中符合查询条件的数据行数,例如 department表中有4个部门,employee表中有4个员工,那么交叉连接的结果有4*4=16条数据，注意:交叉查询得到的是笛卡尔积 : 两张表的乘积, 实际没有意义-- 交叉连接的语法格式如下:
select * from 表1 cross join 表2;
select * from 表1,表2;
-- 以上语法格式 cross join 用于连接两个要查询的表,通过该语句可以查询两个表中所有的数据组合
-- 交叉查询
SELECT * FROM USER CROSS JOIN orders;
SELECT * FROM USER,orders;
2.2 -内连接查询:(重点学习)
概述:内连接(inner join) 又称为简单连接或自然连接,是一种常见的连接查询,内连接使用比较运算符对两个表中的数据进行比较,并列出与连接条件匹配的数据行,组合成新的结果,也就是说在内连接查询中,只有满足条件的记录才能出现在查询结果中,内连接查询的语法如下:
内连接查询语法:
显式内连接: select 查询字段 from 表 1 inner join 表2 on 表1.关系字段 = 表2.关系字段; -- inner 可以省略不写
隐式内连接: select 查询字段 from 表1,表2 where 条件;
注意:
- inner join 用于连接两个表
- on 用于指定连接条件
- 其中inner 可以省略
-- 需求: 查找出1号用户 下了多少订单
-- 显式内连接
SELECT * FROM USER INNER JOIN orders ON user.userid = orders.uno WHERE user.userid = 1;
-- 省略inner
SELECT * FROM USER JOIN orders ON user.userid = orders.uno WHERE user.userid = 1;
-- 隐式内连接
SELECT * FROM USER,orders WHERE user.userid = orders.uno
-- 分析: 从 user表和订单表orders里面查询数据
SELECT * FROM USER,orders WHERE user.userid = orders.uno AND user.username='zs';
-- 需求: 查找出1号用户 下了多少订单
-- 查找1号用户下了多少订单 用户表 订单表
SELECT * FROM USER INNER JOIN orders ON orders.uno = user.userid WHERE user.userid=1
SELECT * FROM USER INNER JOIN orders ON orders.uno = user.userid AND user.userid=1
-- 起别名查询
SELECT * FROM USER AS u,orders AS o WHERE o.uno = u.userid AND u.userid=1;
SELECT * FROM USER u,orders o WHERE o.uno = u.userid AND u.userid=1;
-- 隐式查询
SELECT * FROM USER,orders WHERE orders.uno = user.userid AND user.userid=1
2.3 -外连接查询:(掌握)
概述:内连接查询中,返回的结果只包含符合查询条件和连接条件的数据,然而有时还需要包含没有关联的数据,即返回查询结果中不仅包含符合条件的数据,而且还包括左表(左连接或左外连接)/右表(右连接或右外连接)或者两个表(全外连接)中的所有数据,此时就需要使用外连接查询,外链接分为左连接和右连接.
外连接语法:
select 所查字段 from 表 1 left|right [outer] join 表2 on 表1.关系字段 = 表2.关系字段 where 条件;
- left join(左外连接): 以左表为基础,将左表中所有的记录都查询出来,如果没有对应的记录,用null值填充
- Right join(右外连接):以右表为基础,将右表中的所有记录都查询出来,如果没有对应的记录,用null值填充
-- 使用外连接查询
-- 左外查询
-- 使用左外查询查用户和订单表,即使用户表中有用户没有购买任何商品,也会显式出来
SELECT * FROM USER LEFT OUTER JOIN orders ON user.userid = orders.uno;
-- 右外查询
-- 使用右外查询查用户和订单表,即使用户表中有用户没有购买任何商品,也会显式出来
SELECT * FROM orders RIGHT OUTER JOIN USER ON user.userid = orders.uno;
其他查询:分页查询 : limit
limit : 起始索引, 查询几条; 索引是0开始--请听题: 每页显示3条数数据, 请查询出第2页的所有数据
select * from product limit 0,3; --第1页
select * from product limit 3,3; -- 第2 页
起始索引: (页数-1)*每页显示数量 ,页数是从1开始
(3-1)*3
(4-1)*3
select * from product limit 6,3;
多表查询练习
查询出(商品名称product,商品分类名称category)信息SELECT * FROM product,category WHERE product.cno = category.cid;
SELECT * FROM product p,category c WHERE p.cno = c.cid;
SELECT product.pname,category.cname FROM product,category WHERE product.cno = category.cid;
SELECT p.pname AS 商品名称,c.cname AS 商品类别 FROM product p,category c WHERE p.cno = c.cid;
SELECT pname AS 商品名称,cname AS 商品类别 FROM product p,category c WHERE p.cno = c.cid;
查询分类名称为手机数码(category)的所有商品(product)
SELECT pname AS 商品名称,cname AS 商品类别 FROM product p,category c WHERE p.cno = c.cid AND c.cname = '手机数码';
按照商品分类的名称统计商品的个数: count group by
select cname 商品类别名称,count(1) from category c,product p where c.cid = p.cno group by c.cname ;
select cname 商品类别名称,count(*) from category c,product p where c.cid = p.cno group by c.cname ;
查询1号订单的订单项信息(orderitem)和商品信息(product)
-- 查询1号订单的订单项信息(orderitem)和商品信息(product)
SELECT * FROM orderitem,product WHERE orderitem.ono = product.cno AND orderitem.ono = 1;
SELECT * FROM orderitem oi,product p WHERE oi.ono = p.cno AND oi.ono = 1;
巩固练习
多表查询准备数据
- 员工信息表
--员工信息表
CREATE TABLE emp(
empno INT, /*员工编号*/
ename VARCHAR(50), /*员工姓名*/
job VARCHAR(50), /*员工工作*/
mgr INT, /*员工经理编号*/
hiredate DATE, /*员工入职日期*/
sal DECIMAL(7,2), /*员工工资*/
comm DECIMAL(7,2), /*员工奖金*/
deptno INT /*员工所处的部门编号*/
);
INSERT INTO emp values(7369,'SMITH','CLERK',7902,'1980-12-17',800,NULL,20);
INSERT INTO emp values(7499,'ALLEN','SALESMAN',7698,'1981-02-20',1600,300,30);
INSERT INTO emp values(7521,'WARD','SALESMAN',7698,'1981-02-22',1250,500,30);
INSERT INTO emp values(7566,'JONES','MANAGER',7839,'1981-04-02',2975,NULL,20);
INSERT INTO emp values(7654,'MARTIN','SALESMAN',7698,'1981-09-28',1250,1400,30);
INSERT INTO emp values(7698,'BLAKE','MANAGER',7839,'1981-05-01',2850,NULL,30);
INSERT INTO emp values(7782,'CLARK','MANAGER',7839,'1981-06-09',2450,NULL,10);
INSERT INTO emp values(7788,'SCOTT','ANALYST',7566,'1987-04-19',3000,NULL,20);
INSERT INTO emp values(7839,'KING','PRESIDENT',NULL,'1981-11-17',5000,NULL,10);
INSERT INTO emp values(7844,'TURNER','SALESMAN',7698,'1981-09-08',1500,0,30);
INSERT INTO emp values(7876,'ADAMS','CLERK',7788,'1987-05-23',1100,NULL,20);
INSERT INTO emp values(7900,'JAMES','CLERK',7698,'1981-12-03',950,NULL,30);
INSERT INTO emp values(7902,'FORD','ANALYST',7566,'1981-12-03',3000,NULL,20);
INSERT INTO emp values(7934,'MILLER','CLERK',7782,'1982-01-23',1300,NULL,10);
INSERT INTO emp values(7981,'MILLER','CLERK',7788,'1992-01-23',2600,500,20);
- 部门信息表
--部门表
CREATE TABLE dept(
deptno INT, /*部门的编号*/
dname varchar(14), /*部门的名称*/
loc varchar(13) /*部门所处的位置*/
);
INSERT INTO dept values(10, 'ACCOUNTING', 'NEW YORK');
INSERT INTO dept values(20, 'RESEARCH', 'DALLAS');
INSERT INTO dept values(30, 'SALES', 'CHICAGO');
INSERT INTO dept values(40, 'OPERATIONS', 'BOSTON');
- 基本查询
-- 所有员工的信息
SELECT * FROM emp;
-- 薪资大于等于1000并且小于等于2000的员工信息
SELECT * FROM emp WHERE sal >= 1000 AND sal <= 2000;
SELECT * FROM emp WHERE sal BETWEEN 1000 AND 2000;
-- 从员工表中查询出所有的部门编号
SELECT DISTINCT deptno FROM emp;
-- 查询出emp表中所有的工作种类（无重复）
SELECT DISTINCT job FROM emp;
-- 查询出名字以A开头的员工的信息
SELECT * FROM emp WHERE ename LIKE 'A%';
-- 查询出名字第二个字母是L的员工信息
SELECT * FROM emp WHERE ename LIKE '_L%';
-- 查询出名字中有“A”字符，并且薪水在1000以上（不包括1000）的所有员工信息
SELECT * FROM emp WHERE ename LIKE '%A%'AND sal > 1000;
-- 查询出没有奖金的员工信息
SELECT * FROM emp WHERE comm IS NULL;
-- 所有员工的平均工资
SELECT AVG(sal) AS 平均工资 FROM emp;
-- 所有员工的工资总和
SELECT SUM(sal) 工资总和 FROM emp;
-- 所有员工的数量
SELECT COUNT(1) FROM emp;
-- 最高工资
SELECT MAX(sal) 最高工资 FROM emp;
-- 最少工资
SELECT MIN(sal) 最低工资 FROM emp;
-- 将所有员工按薪水升序排序，薪水相同的按照入职时间降序排序
SELECT * FROM emp ORDER BY sal ASC,hiredate DESC;
-- 查询出薪水在800到2500之间（闭区间）所有员工的信息。（注：使用两种方式实现and以及between and）
SELECT * FROM emp WHERE sal >=800 AND sal <= 2500;
SELECT * FROM emp WHERE sal BETWEEN 800 AND 2500;
-- 查询出emp表中部门编号为20，薪水在2000以上（不包括2000）的所有员工，显示他们的员工号，姓名以及薪水，以如下列名显示：员工编号 员工名字 薪水
-- 员工编号 员工名字 薪水
SELECT empno 员工编号,ename 员工名字,sal 薪水 FROM emp WHERE sal >2000 AND deptno = 20;
-- 查询出员工号为7521，7900，7782的所有员工的信息。（注：使用两种方式实现，or以及in）
SELECT * FROM emp WHERE empno=7521 OR empno=7900 OR empno=7782;
SELECT * FROM emp WHERE empno IN (7521,7900,7782);
- 分组查询
-- 每个部门的平均工资
SELECT deptno 部门编号,AVG(sal) 平均薪资 FROM emp GROUP BY deptno ;
-- 列出在每个部门工作的员工数量、平均工资。
SELECT deptno 部门编号,COUNT(*) 员工数量 ,AVG(sal) 平均薪资 FROM emp GROUP BY deptno;
-- 列出各种工作的最低工资
SELECT job,MIN(sal) FROM emp GROUP BY job;
-- 列出最低薪金大于1500的各种工作
SELECT job,MIN(sal) FROM emp GROUP BY job HAVING MIN(sal)>1500;
- 多表查询
-- 列出所有员工的姓名、部门名称和工资。
SELECT emp.ename 员工姓名,dept.dname 部门名称,emp.sal 工资 FROM emp,dept WHERE emp.deptno = dept.deptno;
-- 查询员工编号,员工姓名,经理编号,经理姓名
SELECT e1.empno 员工编号,e1.ename 员工姓名,e1.mgr 经理编号,e2.ename 经理姓名 FROM emp e1,emp e2 WHERE e1.mgr = e2.empno;
-- 查询员工编号,员工姓名,员工部门名称,经理编号,经理姓名
SELECT e1.empno 员工编号,e1.ename 员工姓名,d.dname 部门名称, e1.mgr 经理编号,e2.ename 经理姓名 FROM emp e1,emp e2,dept d WHERE e1.mgr = e2.empno AND d.deptno = e1.deptno;
- 子查询(理解-----了解)
- 在一个查询语句里面需要依赖于另外一个查询语句结果
- -- 员工信息表 CREATE TABLE emp(
- empno INT, /*员工编号*/
- ename VARCHAR(50), /*员工姓名*/
- job VARCHAR(50), /*员工工作*/
- mgr INT, /*员工经理编号*/
- hiredate DATE, /*员工入职日期*/
- sal DECIMAL(7,2), /*员工工资*/
- comm DECIMAL(7,2), /*员工奖金*/
- deptno INT /*员工所处的部门编号*/
- );
-- 子查询(in not in any all)
-- 列出薪金高于公司平均薪金的所有员工
-- 查询出高于10号部门的平均工资的员工信息
-- 查出最高工资的员工信息
-- 查出最低工资的员工信息
-- 显示出薪水最高人的职位。
-- 查询出最早工作的那个人的名字、入职时间和薪水。
-- 查询出比10号部门任何员工薪资高的员工信息
-- 列出薪金比“SMITH”多的所有员工。
-- 列出受雇日期早于其直接上级的所有员工
-- 列出部门名称和这些部门的员工信息，同时列出那些没有员工的部门。
-- 列出所有“CLERK”（办事员）job=clierk 的姓名及其部门名称。
-- 列出在部门“SALES”（销售部）工作的员工的姓名，假定不知道销售部的部门编号
-- 列出与“SCOTT”从事相同工作的所有员工。
-- 列出至少有一个员工的所有部门
-- 列出薪金等于部门30中员工的薪金的所有员工的姓名和薪金。
阅读全文
0 0
- 09-mysql多表查询
- 【MySQL】MySQL多表查询
- MySQL 多表查询
- mysql多表查询
- Mysql多表查询
- MySQL多表查询
- MYSQL多表查询
- MySQL多表查询
- MySQL多表查询
- MySQL多表查询
- MySQL多表查询
- MySQL多表查询
- MySQL多表查询
- mysql 多表查询
- MySQL多表查询
- MySQL多表查询
- MySQL多表查询
- MySql：多表查询
- 数据结构实验之查找三：树的种类统计
- 利用metasploit通过ms_08_067_netapi漏洞渗透winXp
- Day016
- jQuery中的load
- spring mvc 5.0 模型视图详解 07
- 09-mysql多表查询
- linux 命令（一）
- P1387 dp入门
- TabLayout+ViewPager实现tab和页面联动效果
- ElasticSearch-5.0安装head插件
- 自定义控件之AddItemView
- Day017
- 一行命令搞定node.js 版本安装、升级与卸载
- VMWare中创建用户，并与SecureCRT进行远程连接
