mysql系列 - mysql高级查询

最新推荐文章于 2023-07-27 17:21:46 发布

小么嘛小二郎

最新推荐文章于 2023-07-27 17:21:46 发布

阅读量246

点赞数 3

分类专栏： mysql 文章标签：子查询连接查询 mysql高级查询 left join

本文链接：https://blog.csdn.net/mahao25/article/details/100695285

版权

mysql 专栏收录该内容

6 篇文章 0 订阅

订阅专栏

1. 子查询

1. where型子查询

#查询商品id最大的商品信息
select * from goods where id = (select max(id) from goods);

#查询每个栏目的最大id
select * from goods where id in
	(select max(goods_id) from goods group by cat_id)

2. from型子查询

内层结构的查询出的结果集，当做外层查询的表。

查询模型扩充：

查询结果集在结构上可以当做表看。

#查询每个栏目下，goods_id最大的商品
select cat_id,goods_id,goods_name from (select * from goods order by goods_id desc) group by cat_id;

3. exists型子查询

把外层SQL的结果，拿到内层SQL去测试，

如果内层SQL成立，则该行取出。

create table category
(
	cat_id int unsigned not null auto_increment primary key,
    cat_name varchar(20) not null default ''
)engine myisam charset utf8;

#1.取出栏目表信息，而且只取栏目下有商品的栏目表
SELECT * from category where EXISTS 
	(SELECT * from ecs_goods where ecs_goods.cat_id = category.cat_id)
	
分析：
	执行顺序是外层的sql查询时，对每条数据进行遍历查询时，取出数据，然后
	执行exists里面的查询，如果返回数据了，返回true，否则是返回false。

#2.取出不在商品中存在的栏目表
SELECT * from catego ry where not EXISTS 
	(SELECT * from ecs_goods where ecs_goods.cat_id = category.cat_id)

2. 连接查询

1. 笛卡尔积

现在，我们有两个集合A和B。

A = {0,1}     B = {2,3,4}

集合 A×B 和 B×A的结果集就可以分别表示为以下这种形式：

A×B = {（0，2），（1，2），（0，3），（1，3），（0，4），（1，4）}；

B×A = {（2，0），（2，1），（3，0），（3，1），（4，0），（4，1）}；

以上A×B和B×A的结果就可以叫做两个集合相乘的笛卡尔积。

从以上的数据分析我们可以得出以下两点结论：

两个集合相乘，不满足交换率，既 A×B ≠ B×A;
A集合和B集合相乘，包含了集合A中元素和集合B中元素按顺序结合的所有的可能性。既两个集合相乘得到的新集合的元素个数是 A集合的元素个数 × B集合的元素个数;
其实和高中数学里的排列很类似，不过排列里含有（2，0）、（0，2），而笛卡尔积只有其中一个：AxB则是（0，2），BxA则是（2，0）。

数据库表连接数据行匹配时所遵循的算法就是以上提到的笛卡尔积，表与表之间的连接可以看成是在做乘法运算。

CREATE table user 
(
		id int unsigned auto_increment PRIMARY key,
		name CHAR(10) not null DEFAULT '',
		role_id TINYINT UNSIGNED not null default 0
)engine myisam charset utf8;

CREATE table role 
(
		id TINYINT unsigned auto_increment PRIMARY key,
		name CHAR(10) not null DEFAULT ''
)engine myisam charset utf8;

INSERT INTO `user` VALUES ('1', '张三', '1');
INSERT INTO `user` VALUES ('2', '李四', '2');
INSERT INTO `user` VALUES ('3', '王五', '2');


INSERT INTO `role` VALUES ('1', '经理');
INSERT INTO `role` VALUES ('2', '程序员');

##############################想获取到用户的角色信息#####

#两个表做笛卡尔积
SELECT * from user,role;

mysql> SELECT * from user,role;
+----+------+---------+----+--------+
| id | name | role_id | id | name   |
+----+------+---------+----+--------+
|  1 | 张三 |       1 |  1 | 经理   |
|  1 | 张三 |       1 |  2 | 程序员 |
|  2 | 李四 |       2 |  1 | 经理   |
|  2 | 李四 |       2 |  2 | 程序员 |
|  3 | 王五 |       2 |  1 | 经理   |
|  3 | 王五 |       2 |  2 | 程序员 |
+----+------+---------+----+--------+
两表相乘，就是两表的行做相乘
对于列，就是两表的列都取出


#对于无意义的数据，可以使用筛选
	SELECT * from user u,role r where u.role_id = r.id; 
	
+----+------+---------+----+--------+
| id | name | role_id | id | name   |
+----+------+---------+----+--------+
|  1 | 张三 |       1 |  1 | 经理   |
|  2 | 李四 |       2 |  2 | 程序员 |
|  3 | 王五 |       2 |  2 | 程序员 |
+----+------+---------+----+--------+
3 rows in set

对于全相乘，两表相乘会生成一个巨大的表，解决需要左连接解决：

2.左连接

假设A表再左，B表在A表的右面滑动，A表与B表通过一个关系来筛选B表的行。

语法：

A left join B on expr expr为真，则取出行

使用：

Aleft join B on expr 这一块形成的也是一个结果集，可以看成一张表C，

因此，可以对C表做查询，where group 函数 having order by limit都可以使用。

对于上面的查询，则可以

select * from 
	user u left join role r on u.role_id = r.id;


+----+------+---------+----+--------+
| id | name | role_id | id | name   |
+----+------+---------+----+--------+
|  1 | 张三 |       1 |  1 | 经理   |
|  2 | 李四 |       2 |  2 | 程序员 |
|  3 | 王五 |       2 |  2 | 程序员 |
+----+------+---------+----+--------+


##1.如果A表中的数据对应对于B表中，结果是什么，A中是否会显示
mysql> select * from user;
+----+------+---------+
| id | name | role_id |
+----+------+---------+
|  1 | 张三 |       1 |
|  2 | 李四 |       2 |
|  3 | 王五 |       2 |
|  4 | 马六 |       0 |
+----+------+---------+
4 rows in set

mysql> select * from role
    -> ;
+----+--------+
| id | name   |
+----+--------+
|  1 | 经理   |
|  2 | 程序员 |
+----+--------+
2 rows in set

结果
mysql> select * from user left join role on user.role_id = role.id;
+----+------+---------+------+--------+
| id | name | role_id | id   | name   |
+----+------+---------+------+--------+
|  1 | 张三 |       1 |    1 | 经理   |
|  2 | 李四 |       2 |    2 | 程序员 |
|  3 | 王五 |       2 |    2 | 程序员 |
|  4 | 马六 |       0 | NULL | NULL   |
+----+------+---------+------+--------+
将会用NULL补齐所缺的列变量。

#2.对于B表中，有A表的多个对应关系，该如何解决
role表不变，让role表当做A表，User表添加张三的一个职业信息，当B表，这时候关系就是role表的对应
表中user，含有多个对应关系
select * from role r left join user u  on u.role_id = r.id;
+----+--------+----+------+---------+
| id | name   | id | name | role_id |
+----+--------+----+------+---------+
|  1 | 经理   |  1 | 张三 |       1 |
|  2 | 程序员 |  2 | 李四 |       2 |
|  2 | 程序员 |  3 | 王五 |       2 |
|  2 | 程序员 |  5 | 张三 |       2 |
+----+--------+----+------+---------+
会显示出来两个。

3. 内连接

两个表中都有对应的关系，才能出现

#显示用户和角色的关系
select *from user u inner join role r where u.role_id = r.id

+----+------+---------+----+--------+
| id | name | role_id | id | name   |
+----+------+---------+----+--------+
|  1 | 张三 |       1 |  1 | 经理   |
|  2 | 李四 |       2 |  2 | 程序员 |
|  3 | 王五 |       2 |  2 | 程序员 |
|  5 | 张三 |       2 |  2 | 程序员 |
+----+------+---------+----+--------+

练习:

5.4: 用友面试题

根据给出的表结构按要求写出SQL语句。
Match 赛程表
字段名称 字段类型 描述
matchID int 主键
hostTeamID int 主队的ID
guestTeamID int 客队的ID
matchResult varchar(20) 比赛结果，如（2:0）
matchTime date 比赛开始时间

Team 参赛队伍表
字段名称 字段类型 描述
teamID int 主键
teamName varchar(20) 队伍名称

Match的hostTeamID与guestTeamID都与Team中的teamID关联
查出 2006-6-1 到2006-7-1之间举行的所有比赛，并且用以下形式列出：
拜仁 2：0 不来梅 2006-6-21

#两次左外连接，第一次获取到hostId的name，在这个结果集上，第二次left join获取gustId的name
SELECT
	t. NAME,
	m.matchResult,
	t2. NAME
FROM
	Match1 m
LEFT JOIN team t ON m.hostTeamID = t.id
LEFT JOIN team t2 ON m.guestTeamID = t2.id

10. union查询

UNION 操作符用于合并两个或多个 SELECT 语句的结果集。

请注意，UNION 内部的 SELECT 语句必须拥有相同数量的列。列也必须拥有相似的数据类型。同时，每条 SELECT 语句中的列的顺序必须相同。

SQL UNION 语法

SELECT column_name(s) FROM table_name1
UNION
SELECT column_name(s) FROM table_name2

**注释：**默认地，UNION 操作符选取不同的值。如果允许重复的值，请使用 UNION ALL。

mysql> select * from user;
+----+------+---------+
| id | name | role_id |
+----+------+---------+
|  1 | 张三 |       1 |
|  2 | 李四 |       2 |
|  3 | 王五 |       2 |
|  4 | 马六 |       0 |
|  5 | 张三 |       2 |
+----+------+---------+
5 rows in set

mysql> SELECT * from class;
+----+-------+--------+---------+--------+-------+
| id | name  | gender | company | salary | fanbu |
+----+-------+--------+---------+--------+-------+
| 12 | ls    | 女     | 阿里    | 45.2   |    54 |
| 11 | ls    | 女     | 阿里    | 45.2   |    54 |
|  6 | ls    | 女     | 阿里    | 45.2   |    54 |
|  7 | 马豪2 | 男     | gongsi  | 789.3  |    20 |
|  8 | li    | 女     | aa      | 78.2   |    65 |
|  9 | li    | 女     |         | 78.2   |    65 |
|  3 | li    | 女     |         | 78.2   |    65 |
|  2 | li    | 女     |         | 78.2   |    65 |
+----+-------+--------+---------+--------+-------+

mysql> select id ,name from class
    -> union
    -> select id ,name from user;
+----+-------+
| id | name  |
+----+-------+
| 12 | ls    |
| 11 | ls    |
|  6 | ls    |
|  7 | 马豪2 |
|  8 | li    |
|  9 | li    |
|  3 | li    |
|  2 | li    |
|  1 | 张三  |
|  2 | 李四  |
|  3 | 王五  |
|  4 | 马六  |
|  5 | 张三  |
+----+-------+
13 rows in set

#错误用法，内层语句不支持使用order by
 select id ,name from class order by id desc
    -> union
    -> select id ,name from user 
    -> ;
1221 - Incorrect usage of UNION and ORDER BY

#与limit使用时，可以使用
mysql> (select id ,name from class order by id desc limit 3) union (select id,name from user order by id desc limit 3 );
+----+------+
| id | name |
+----+------+
| 12 | ls   |
| 11 | ls   |
|  9 | li   |
|  5 | 张三 |
|  4 | 马六 |
|  3 | 王五 |
+----+------+

SQL UNION ALL 语法

SELECT column_name(s) FROM table_name1
UNION ALL
SELECT column_name(s) FROM table_name2

另外，UNION 结果集中的列名总是等于 UNION 中第一个 SELECT 语句中的列名。

下面的例子中使用的原始表：

Employees_China:

E_ID	E_Name
01	Zhang, Hua
02	Wang, Wei
03	Carter, Thomas
04	Yang, Ming

Employees_USA:

E_ID	E_Name
01	Adams, John
02	Bush, George
03	Carter, Thomas
04	Gates, Bill

使用 UNION 命令

实例

列出所有在中国和美国的不同的雇员名：

SELECT E_Name FROM Employees_China
UNION
SELECT E_Name FROM Employees_USA

结果

E_Name
Zhang, Hua
Wang, Wei
Carter, Thomas
Yang, Ming
Adams, John
Bush, George
Gates, Bill

**注释：**这个命令无法列出在中国和美国的所有雇员。在上面的例子中，我们有两个名字相同的雇员，他们当中只有一个人被列出来了。UNION 命令只会选取不同的值。

UNION ALL

UNION ALL 命令和 UNION 命令几乎是等效的，不过 UNION ALL 命令会列出所有的值。

SQL Statement 1
UNION ALL
SQL Statement 2

使用 UNION ALL 命令

实例：

列出在中国和美国的所有的雇员：

SELECT E_Name FROM Employees_China
UNION ALL
SELECT E_Name FROM Employees_USA

结果

E_Name
Zhang, Hua
Wang, Wei
Carter, Thomas
Yang, Ming
Adams, John
Bush, George
Carter, Thomas
Gates, Bill

面试题

6.2:3期学员碰到的一道面试题
A表:
±-----±-----+
| id | num |
±-----±-----+
| a | 5 |
| b | 10 |
| c | 15 |
| d | 10 |
±-----±-----+

B表:
±-----±-----+
| id | num |
±-----±-----+
| b | 5 |
| c | 15 |
| d | 20 |
| e | 99 |
±-----±-----+

要求查询出以下效果:
±-----±---------+
| id | num |
±-----±---------+
| a | 5 |
| b | 15 |
| c | 30 |
| d | 30 |
| e | 99 |
±-----±---------+

create table a (
id char(1),
num int
)engine myisam charset utf8;

insert into a values (‘a’,5),(‘b’,10),(‘c’,15),(‘d’,10);

create table b (
id char(1),
num int
)engine myisam charset utf8;

insert into b values (‘b’,5),(‘c’,15),(‘d’,20),(‘e’,99);

解答：用union all查询出来工资，然后分组求和;
	select id ,sum(num) from (select id, num from a 
	union all
	select id,num from b) as cs 
	group by id
    
	方法二：使用左连接查询
    select a.id , a.num+IFNULL(b1.num,0)  from a LEFT JOIN b b1 on a.id = b1.id
    union   
    select b.id , b.num+IFNULL(a.num,0)  from a right JOIN b on a.id = b.id

11. mysql中函数

1.流程控制

1. case 函数

 SELECT 
 CASE 1
 	WHEN 1 THEN 'one'
 	WHEN 2 THEN 'two' 
	 ELSE 'more'
  END;

	one

2. if(‘expr’,‘结果1’,‘结果2’)

 SELECT IF(1>2,1,0); //0

3. ifnull (expr,expr2)

假如expr1 不为 NULL，则 IFNULL() 的返回值为 expr1; 否则其返回值为 expr2。IFNULL()的返回值是数字或是字符串，具体情况取决于其所使用的语境。 
mysql> SELECT IFNULL(1,0);
        -> 1

mysql> SELECT IFNULL(NULL,10);
        -> 10

mysql> SELECT IFNULL(1/0,10);
        -> 10

mysql> SELECT IFNULL(1/0,'yes');
        -> 'yes'

小么嘛小二郎

关注

3
点赞
踩
1

收藏

觉得还不错? 一键收藏
0
评论
mysql系列 - mysql高级查询

1. 子查询1. where型子查询#查询商品id最大的商品信息select * from goods where id = (select max(id) from goods);#查询每个栏目的最大idselect * from goods where id in (select max(goods_id) from goods group by cat_id)2. from...
复制链接

扫一扫

专栏目录