MySQL基础之数据管理【3】

MySQL中的多表联查

--查询emp的id username age depName
create table emp(
	id int unsigned auto_increment key,
	username varchar(20) not null unique comment '编号',
	age tinyint unsigned not null default 18 comment '年龄',
	sex enum('男','女','保密') not null default '保密' comment '性别',
	addr varchar(20) not null default '北京',
	depId tinyint unsigned not null comment '部门对应的编号'
)engine=innodb charset=utf8;

insert emp(username,age,depId) values('king',24,1),
('queen',25,2),
('imooc',26,1),
('lily',27,1),
('rose',28,3),
('john',29,3);

create table dep(
	id tinyint unsigned auto_increment key,
	depName varchar(50) not null unique,
	depDesc varchar(100) not null
)engine=innodb charset=utf8;

insert dep(depName,depDesc) values('PHP教学部','研发PHP课件'),
('JAVA教学部','研发JAVA课件'),
('WEB教学部','研发WEB课件'),
('IOS教学部','研发IOS课件');

笛卡尔积的形式(不常用)

select emp.id,emp.username,emp.age,dep.depName from emp,dep;
--显示
+----+----------+-----+------------+
| id | username | age | depName    |
+----+----------+-----+------------+
|  1 | king     |  24 | IOS教学部  |
|  1 | king     |  24 | JAVA教学部 |
|  1 | king     |  24 | PHP教学部  |
|  1 | king     |  24 | WEB教学部  |
|  2 | queen    |  25 | IOS教学部  |
|  2 | queen    |  25 | JAVA教学部 |
|  2 | queen    |  25 | PHP教学部  |
|  2 | queen    |  25 | WEB教学部  |
|  3 | imooc    |  26 | IOS教学部  |
|  3 | imooc    |  26 | JAVA教学部 |
|  3 | imooc    |  26 | PHP教学部  |
|  3 | imooc    |  26 | WEB教学部  |
|  4 | lily     |  27 | IOS教学部  |
|  4 | lily     |  27 | JAVA教学部 |
|  4 | lily     |  27 | PHP教学部  |
|  4 | lily     |  27 | WEB教学部  |
|  5 | rose     |  28 | IOS教学部  |
|  5 | rose     |  28 | JAVA教学部 |
|  5 | rose     |  28 | PHP教学部  |
|  5 | rose     |  28 | WEB教学部  |
|  6 | john     |  29 | IOS教学部  |
|  6 | john     |  29 | JAVA教学部 |
|  6 | john     |  29 | PHP教学部  |
|  6 | john     |  29 | WEB教学部  |
+----+----------+-----+------------+

内连接的形式(查询两个表中符合连接条件的记录)

--select 字段名称,... from tbl_name1 [inner] join tbl_name2 on 连接条件;
select e.id,e.username,e.age,d.depName
from emp as e
inner join dep as d
on e.depId=d.id;
--显示
+----+----------+-----+------------+
| id | username | age | depName    |
+----+----------+-----+------------+
|  1 | king     |  24 | PHP教学部  |
|  2 | queen    |  25 | JAVA教学部 |
|  3 | imooc    |  26 | PHP教学部  |
|  4 | lily     |  27 | PHP教学部  |
|  5 | rose     |  28 | WEB教学部  |
|  6 | john     |  29 | WEB教学部  |
+----+----------+-----+------------+

外连接的形式

--左外连接(select 字段名称,... from tbl_name1 left [outer] join tbl_name2 on 条件;)
--先显示左表中的全部记录，再去右表中查询符合条件的记录，不符合的以null代替
select e.id,e.username,e.age,d.depName,d.depDesc
from emp as e
left outer join dep as d
on e.depId=d.id;
--显示
+----+----------+-----+------------+--------------+
| id | username | age | depName    | depDesc      |
+----+----------+-----+------------+--------------+
|  1 | king     |  24 | PHP教学部  | 研发PHP课件  |
|  2 | queen    |  25 | JAVA教学部 | 研发JAVA课件 |
|  3 | imooc    |  26 | PHP教学部  | 研发PHP课件  |
|  4 | lily     |  27 | PHP教学部  | 研发PHP课件  |
|  5 | rose     |  28 | WEB教学部  | 研发WEB课件  |
|  6 | john     |  29 | WEB教学部  | 研发WEB课件  |
+----+----------+-----+------------+--------------+

--右外连接(select 字段名称,... from tbl_name1 right [outer] join tbl_name2 on 条件;)
--先显示右表中的全部记录，再去左表中查询符合条件的记录，不符合的以null代替
select e.id,e.username,e.age,d.depName
from emp as e
right outer join dep as d
on e.depId=d.id;
--显示
+------+----------+------+------------+
| id   | username | age  | depName    |
+------+----------+------+------------+
|    1 | king     |   24 | PHP教学部  |
|    2 | queen    |   25 | JAVA教学部 |
|    3 | imooc    |   26 | PHP教学部  |
|    4 | lily     |   27 | PHP教学部  |
|    5 | rose     |   28 | WEB教学部  |
|    6 | john     |   29 | WEB教学部  |
| NULL | NULL     | NULL | IOS教学部  |
+------+----------+------+------------+

多表联查的操作

create table user(
	id tinyint unsigned auto_increment key comment '编号',
	username varchar(20) not null unique comment '用户名',
	email varchar(50) not null default '2214@qq.com' comment '邮箱',
	proId tinyint unsigned not null comment '用户所在省份的编号'
)engine=innodb charset=utf8;

insert user(username,proId) values('a','1'),
('b','1'),
('c','1'),
('d','2'),
('e','3'),
('f','1'),
('g','1');

create table pro(
	id tinyint unsigned auto_increment key comment '编号',
	proName varchar(10) not null unique comment '省份名称'
)engine=innodb charset=utf8;

insert pro(proName) values('北京'),('上海'),('深圳');

--查询user: id,username pro: proName
select u.id,u.username,p.proName
from user as u
join pro as p
on u.proId=p.id;

--显示
+----+----------+---------+
| id | username | proName |
+----+----------+---------+
|  1 | a        | 北京    |
|  2 | b        | 北京    |
|  3 | c        | 北京    |
|  4 | d        | 上海    |
|  5 | e        | 深圳    |
|  6 | f        | 北京    |
|  7 | g        | 北京    |
+----+----------+---------+

--修改北京为首都
update pro set proName='首都' where id=1;
--显示
+----+----------+---------+
| id | username | proName |
+----+----------+---------+
|  1 | a        | 首都    |
|  2 | b        | 首都    |
|  3 | c        | 首都    |
|  4 | d        | 上海    |
|  5 | e        | 深圳    |
|  6 | f        | 首都    |
|  7 | g        | 首都    |
+----+----------+---------+

四个表关联的查询

create table pro2(
	id tinyint unsigned not null auto_increment key comment '编号',
	proName varchar(10) not null unique comment '省份名称'
)engine=innodb charset=utf8;

insert pro2(proName) values('北京'),('上海'),('深圳');

create table admin(
	id tinyint unsigned auto_increment key comment '编号',
	username varchar(20) not null unique comment '用户名',
	email varchar(50) not null default '24235@qq.com' comment '邮箱',
	proId tinyint unsigned not null comment '用户所在省份的编号'
)engine=innodb charset=utf8;

insert admin(username,proId) values('king',1),('queen',2);

create table cate(
	id tinyint unsigned auto_increment key comment '编号',
	cateName varchar(50) unique comment '商品分类名称',
	cateDesc varchar(100) not null default '好东西' comment '商品分类描述'
)engine=innodb charset=utf8;

insert cate(cateName) values('母婴'),('服装'),('电子');

create table product(
	id int unsigned auto_increment key comment '编号',
	productName varchar(50) not null unique comment '商品名称',
	price float(8,2) not null default '12' comment '价格',
	cateId tinyint unsigned not null comment '商品所在分类的编号',
	adminId tinyint unsigned not null comment '管理员编号'
)engine=innodb charset=utf8;

insert product(productName,price,cateId,adminId) values('iphone9',9888,3,1),
('adidas',388,2,2),
('nike',888,2,2),
('奶瓶',288,1,1);

--查询product:id productName price  cate:catename(两个表)
select p.id,p.productName,p.price,c.cateName
from product as p
join cate as c
on p.cateId=c.id;

--查询管理员 id username email  pro:proName(两个表)
select a.id,a.username,a.email,p.proName
from admin as a
join pro2 as p
on a.proId=p.id;

--查询product:id productName price
--cate:cateName
--admin:username email
--pro2:proName(四表查询)
select p.id,p.productName,p.price,c.cateName,a.username,a.email,pr.proName
from product as p
join admin as a
on p.adminId=a.id 
join cate as c 
on p.cateId=c.id
join pro2 as pr
on a.proId=pr.Id
where p.price<1000
order by p.price desc
limit 0,2;
--显示
+----+-------------+--------+----------+----------+--------------+---------+
| id | productName | price  | cateName | username | email        | proName |
+----+-------------+--------+----------+----------+--------------+---------+
|  3 | nike        | 888.00 | 服装     | queen    | 24235@qq.com | 上海    |
|  2 | adidas      | 388.00 | 服装     | queen    | 24235@qq.com | 上海    |
+----+-------------+--------+----------+----------+--------------+---------+