目录

  • 多表间的建表原则
  • 一对多
  • 多对多
  • 一对一
  • 实例演示多表查询
  • 数据准备
  • 多表查询
  • 笛卡尔积
  • 内连接
  • 隐式内连接
  • 显式内连接
  • 外连接
  • 左外连接
  • 右外连接
  • 子查询
  • 子查询的多种情况


多表间的建表原则

一对多

如分类与商品是一对多的关系（一个分类对应多个商品）

建表原则：在“多”的一方添加一个外键，指向“一”的一方的主键

多对多

如老师和学生，学生和课程

建表原则：多建一张中间表，将多对多的关系拆成一对多关系，中间表至少要有两个字段，作为中间表的外键：这两个外键分别指向两张表的主键。

mysql用select建表 mysql建表查询_mysql

一对一

如班级和班长，公民和身份证，国家和国旗
建表原则：

  • 将一对一的情况当成一对多情况处理，在任意一张表添加一个外键，并且这个外键要唯一，指向另外一张表
  • 将两张表合并成一张表
  • 将两张表的主键建立起连接，让两张表里面主键相等

实例演示多表查询

网上商城表实例分析：用户购物流程

数据准备

以一个实例来展示多表间的操作。

各个表之间的关系如下图：

mysql用select建表 mysql建表查询_mysql用select建表_02


下面需要进行数据准备，也就是建表与插入数据。

  • 用户表 (用户的ID,用户名,密码,手机)
create table user(
	uid int primary key auto_increment,
  	username varchar(31),
  	password varchar(31),
  	phone  varchar(11)
);

insert into user values(1,'zhangsan','123','13811118888');
  • 订单表(订单编号,总价,订单时间 ,地址,外键-用户的ID)
create table orders(
  	oid int primary key auto_increment,
    	sum int not null,
      otime timestamp not null default CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    	address varchar(100),
    	uno int,
    	foreign key(uno) references user(uid)
  );
  insert into orders (oid,sum,address,uno) values(1,200,'中科大666号',1);
  insert into orders (oid,sum,address,uno) values(2,250,'苏大199号',1);
  • 商品表(商品ID, 商品名称,商品价格,外键cno)
create table product(
	pid int primary key auto_increment,
	pname varchar(10),
	price double,
	pdate timestamp not null default CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
	cno int，
	foreign key(cno) references category(cid)
);
insert into product (pid,pname,price,cno)values(null,'华为p30',4999,1);
insert into product (pid,pname,price,cno)values(null,'红米note4',1999,1);
insert into product (pid,pname,price,cno)values(null,'耐克',499,2);
insert into product (pid,pname,price,cno)values(null,'老村长',88,3);
insert into product (pid,pname,price,cno)values(null,'劲酒',35,3);
insert into product (pid,pname,price,cno)values(null,'小熊饼干',4,4);
insert into product (pid,pname,price,cno)values(null,'卫龙辣条',2,5);
insert into product (pid,pname,price,cno)values(null,'旺旺雪饼',3,5);
  • 订单项（(订单ID,商品ID,商品数量,订单项总价)
    这个表就相当于订单表与商品表之间的中间表，将多对多的关系转换成一对多的关系。
create table orderitem(
	ono int,
  	pno int,
  	foreign key(ono) references orders(oid),
  	foreign key(pno) references product(pid),
  	ocount int,
  	subsum double
);
--给1号订单添加商品 200块钱的商品
insert into orderitem values(1,7,100,100);
insert into orderitem values(1,8,101,100);

--给2号订单添加商品 250块钱的商品 ()
aainsert into orderitem values(2,5,1,35);
insert into orderitem values(2,3,3,99);

多表查询

需求分析：
在我们的商城案例中,我的订单中包含很多信息，打开我的订单需要去查询表

笛卡尔积

一种交叉连接查询 。
现在这两张表长这样：

select * from product;
select * from category;

mysql用select建表 mysql建表查询_数据库_03


笛卡尔积，查出来的是两张表的乘积（10 x 6 X 8），这样的结果是没有意义的

select * from product,category;

mysql用select建表 mysql建表查询_数据库_04


所以，要进行过滤（符合条件的有8行，因此结果为8 X 8）：

select * from product,category where cno=cid;

mysql用select建表 mysql建表查询_数据库_05


为了提高语句可读性，可以采用别名：

select * from product as p,category as c where p.cno=c.cid;

省略as后:

select * from product p,category c where p.cno=c.cid;

其实这种写法就是下面的“隐式内连接”。

内连接

应用场景:

mysql用select建表 mysql建表查询_mysql_06


这种场景下得到的是满足某一条件的A，B内部的数据；正因为得到的是内部共有数据，所以连接方式称为内连接。

隐式内连接
select * from product p,category c where p.cno=c.cid;
显式内连接
select * from product p inner join category c on p.cno=c.cid;

mysql用select建表 mysql建表查询_多表查询_07


查询结果和隐式内连接是一样的。

（3）区别

隐式：在查询出结果的基础上去做where条件过滤

显示：带着条件查询结果，效率比较高

外连接

左外连接

应用场景:

mysql用select建表 mysql建表查询_多表查询_08


这种场景下得到的是A的所有数据，和满足某一条件的B的数据;

左外连接会将左表中的所有数据都查询出来，如果右表中没有对应的数据，用null代替.

先看两张表（10 X 5） (6 X 3)：

mysql用select建表 mysql建表查询_数据库_03


左外连接（10 X 8）：

select * from product p left outer join category c on p.cno=c.cid;

mysql用select建表 mysql建表查询_多表查询_10


注意其中回力这一条数据，右表中并没有找到对应条件的结果（cid = cno = NULL），于是用NULL填充了。

右外连接

应用场景:

mysql用select建表 mysql建表查询_sql_11


这种场景下得到的是B的所有数据，和满足某一条件的A的数据；

先看两张表（10 X 5） (6 X 3)：

mysql用select建表 mysql建表查询_数据库_03


与左外连接类似，右外连接会将右表数据全部查询出来。（10 X 8）

select * from product p right outer join category c on p.cno=c.cid;

mysql用select建表 mysql建表查询_sql_13


左表并没有cno=cid=6的数据，所以用NULL填充了。

ps:以上查询语句中的"outer"是可以省略的

子查询

概念：查询中嵌套查询，称嵌套查询为子查询。
下面通过实例体会：
一、查询分类名称为“手机数码”的所有商品
注意，这无法只从一个表中得出结果

1.查询分类名为“手机数码”的ID

select cid from category where cname='手机数码';

2.得出ID为1的结果

select * from product where cno=( select cid from category where cname='手机数码');

二、 查询出（商品名称，商品分类名称）信息
有两种方法来做：

  • 左连接：
select p.pname,c.cname from product p left outer join category c on p.cno=c.cid;

mysql用select建表 mysql建表查询_sql_14

  • 子查询
select pname,(select cname from category c where p.cno=c.cid) as 商品分类名称 
from product p ;

mysql用select建表 mysql建表查询_mysql用select建表_15

子查询的多种情况
  1. 子查询的结果是单行单列的：
    子查询可以作为条件，使用运算符去判断。 运算符： > >= < <= =

例如：查询员工工资小于平均工资的人

SELECT * FROM emp WHERE emp.salary < (SELECT AVG(salary) FROM emp);
  1. 子查询的结果是多行单列的：
    子查询可以作为条件，使用运算符in来判断
    例如：查询’财务部’和’市场部’所有的员工信息
SELECT id FROM dept WHERE NAME = '财务部' OR NAME = '市场部';
SELECT * FROM emp WHERE dept_id = 3 OR dept_id = 2;

– 子查询

SELECT * FROM emp WHERE dept_id IN (SELECT id FROM dept WHERE NAME = '财务部' OR NAME = '市场部');
  1. 子查询的结果是多行多列的：
    子查询可以作为一张虚拟表参与查询
    例如：查询员工入职日期是2011-11-11日之后的员工信息和部门信息

– 子查询

SELECT * FROM dept t1 ,(SELECT * FROM emp WHERE emp.`join_date` > '2011-11-11') t2
WHERE t1.id = t2.dept_id;

– 普通内连接

SELECT * FROM emp t1,dept t2 WHERE t1.`dept_id` = t2.`id` AND t1.`join_date` >  '2011-11-11'