MySQL基础之数据管理【5】

子查询的使用

select 字段名称 from tbl_name where col_name=(select col_name from tbl_name);
--内层语句查询的结果可以作为外层语句查询的条件

由in引发的子查询

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 * from emp where depId in (1,2,3,4);
select * from emp where depId in (select id from dep);

select * from emp where depId not in (select id from dep);

由比较运算符引发的子查询

create table stu(
	id tinyint unsigned auto_increment key comment '编号',
	username varchar(20) not null unique comment '学员名称',
	score tinyint unsigned comment '学员考试分数'
)engine=innodb charset=utf8;

insert stu(username,score) values('king',95),
('queen',75),
('zhangsan',69),
('lisi',78),
('wangwu',87),
('zhaoliu',88),
('tianqi',98),
('ceshi',99),
('tiancai',50);

create table level(
	id tinyint unsigned auto_increment key comment '编号',
	score tinyint unsigned comment '分数'
)engine=innodb charset=utf8;

insert level(score) values(90),(80),(70);

select id,username,score from stu 
where score>=(select score from level where id=1);

select id,username,score from stu
where score<(select score from level where id=3);

由exists引发的子查询

select * from emp where exists (select depName from dep where id=10); --不存在exists返回假，空集合
select * from emp where exists (select depName from dep where id=1); --存在exists返回真，有集合

带有any、some、all关键字的子查询

运算符关键字	any	some	all
>、>=	最小值	最小值	最大值
<、<=	最大值	最大值	最小值
=	任意值	任意值
<>、!=			任意值

select * from stu where score>= any(select score from level); --大于等于子查询结果的最小值，some、all用法差不多
--显示
+----+----------+-------+
| id | username | score |
+----+----------+-------+
|  1 | king     |    95 |
|  2 | queen    |    75 |
|  4 | lisi     |    78 |
|  5 | wangwu   |    87 |
|  6 | zhaoliu  |    88 |
|  7 | tianqi   |    98 |
|  8 | ceshi    |    99 |
+----+----------+-------+

联合查询的使用

-- insert ... select
-- cerate ... select 
-- updata ... select

--创建一个user1表，id username 
create table user1(
	id int unsigned auto_increment key,
	username varchar(20)
)select id,username from emp;

--将user表中的用户名写入到user1表中
insert user1(username) select username from user;

create table user2 like user1; --创建一个与user1表结构相同的user2
insert user2 select id,username from user1;

--将stu表中的tiancai用户名添加到user2表中
insert user2 set username=(select username from stu where id=9);

select distinct(username) from user2; --去掉字段的重复记录

联合查询

--union(去掉两个表中重复的值)
--将user1和user2数据合并到一起
select 字段名称,... from tbl_name1 
union
select 字段名称,... from tbl_name2;

select * from user1
union
select * from user2;

--union all(简单的合并)
select 字段名称,... from tbl_name1 
union all
select 字段名称,... from tbl_name2;

无限级分类数据表的设计及实现(自身连接查询)

-- pId 0 -- id 1服装
-- pId 1 -- id 4男装 5女装 6内衣

-- pId 0 -- id 2数码
-- pId 2 -- id 7电视 8冰箱 9洗衣机

-- pId 0 -- id 3玩具
-- pId 3 -- id 10爱马仕 11LV 12GUCCI

-- pId 4 -- id 13夹克 14衬衫 15裤子

-- pId 7-- id 16液晶电视 17等离子电视 18背投电视

create table cate(
	id smallint unsigned auto_increment key,
	cateName varchar(100) not null unique,
	pId smallint unsigned not null default 0
)engine=innodb charset=utf8;

insert cate(cateName,pId) values('服装',0),
('数码',0),
('玩具',0);

insert cate(cateName,pId) values('男装',1),
('女装',1),
('内衣',1);

insert cate(cateName,pId) values('电视',2),
('冰箱',2),
('洗衣机',2);

insert cate(cateName,pId) values('爱马仕',3),
('LV',3),
('GUCCI',3);

insert cate(cateName,pId) values('夹克',4),
('衬衫',4),
('裤子',4);

insert cate(cateName,pId) values('液晶电视',7),
('等离子电视',7),
('背投电视',7);

--查询所有的分类信息，并且得到其父分类
select s.id,s.cateName as sCateName,p.cateName as pCateName
from cate as s
left join cate as p 
on s.pId=p.id;

--显示
+----+------------+-----------+
| id | sCateName  | pCateName |
+----+------------+-----------+
|  1 | 服装       | NULL      |
|  2 | 数码       | NULL      |
|  3 | 玩具       | NULL      |
|  4 | 男装       | 服装      |
|  5 | 女装       | 服装      |
|  6 | 内衣       | 服装      |
|  7 | 电视       | 数码      |
|  8 | 冰箱       | 数码      |
|  9 | 洗衣机     | 数码      |
| 10 | 爱马仕     | 玩具      |
| 11 | LV         | 玩具      |
| 12 | GUCCI      | 玩具      |
| 13 | 夹克       | 男装      |
| 14 | 衬衫       | 男装      |
| 15 | 裤子       | 男装      |
| 16 | 液晶电视   | 电视      |
| 17 | 等离子电视 | 电视      |
| 18 | 背投电视   | 电视      |
+----+------------+-----------+

-- 查询所有的分类及其子分类
select p.id,p.cateName as pCateName,s.cateName as sCateName
from cate as s
right join cate as p 
on s.pId=p.id;

--显示
+----+------------+------------+
| id | pCateName  | sCateName  |
+----+------------+------------+
|  1 | 服装       | 男装       |
|  1 | 服装       | 女装       |
|  1 | 服装       | 内衣       |
|  2 | 数码       | 电视       |
|  2 | 数码       | 冰箱       |
|  2 | 数码       | 洗衣机     |
|  3 | 玩具       | 爱马仕     |
|  3 | 玩具       | LV         |
|  3 | 玩具       | GUCCI      |
|  4 | 男装       | 夹克       |
|  4 | 男装       | 衬衫       |
|  4 | 男装       | 裤子       |
|  7 | 电视       | 液晶电视   |
|  7 | 电视       | 等离子电视 |
|  7 | 电视       | 背投电视   |
| 12 | GUCCI      | NULL       |
| 11 | LV         | NULL       |
|  6 | 内衣       | NULL       |
|  8 | 冰箱       | NULL       |
| 13 | 夹克       | NULL       |
|  5 | 女装       | NULL       |
|  9 | 洗衣机     | NULL       |
| 16 | 液晶电视   | NULL       |
| 10 | 爱马仕     | NULL       |
| 17 | 等离子电视 | NULL       |
| 18 | 背投电视   | NULL       |
| 14 | 衬衫       | NULL       |
| 15 | 裤子       | NULL       |
+----+------------+------------+

--查询所有的分类并且得到子分类的数目
select p.id,p.cateName as pCateName, count(s.cateName) as count
from cate as s
right join cate as p 
on s.pId=p.id
group by p.cateName
order by id asc;
--显示
+----+------------+-------+
| id | pCateName  | count |
+----+------------+-------+
|  1 | 服装       |     3 |
|  2 | 数码       |     3 |
|  3 | 玩具       |     3 |
|  4 | 男装       |     3 |
|  5 | 女装       |     0 |
|  6 | 内衣       |     0 |
|  7 | 电视       |     3 |
|  8 | 冰箱       |     0 |
|  9 | 洗衣机     |     0 |
| 10 | 爱马仕     |     0 |
| 11 | LV         |     0 |
| 12 | GUCCI      |     0 |
| 13 | 夹克       |     0 |
| 14 | 衬衫       |     0 |
| 15 | 裤子       |     0 |
| 16 | 液晶电视   |     0 |
| 17 | 等离子电视 |     0 |
| 18 | 背投电视   |     0 |
+----+------------+-------+