MYSQL查询检索(2)

x33g5p2x  于2021-08-23 转载在 Mysql  
字(4.8k)|赞(0)|评价(0)|浏览(126)

一 前提准备

先声明一下,下面的库表只是简易的学习示例,不是生产的设计,不要深究,此文我们的目的是学习sql的检索不是库表设计;初学者最好跟着作者的文章一步一步敲一遍,如果没有使用过sql的可以查阅作者SQL系列专栏;

1.1 顾客表

CREATE TABLE `customer` (
  `userId` int(11) NOT NULL AUTO_INCREMENT COMMENT '顾客id',
  `userName` varchar(255) DEFAULT NULL COMMENT '顾客名称',
  `telephone` varchar(255) DEFAULT NULL COMMENT '顾客电话',
  PRIMARY KEY (`userId`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8;

INSERT INTO `springboot`.`customer`(`userId`, `userName`, `telephone`) VALUES (1, 'zxzxz', '1327');
INSERT INTO `springboot`.`customer`(`userId`, `userName`, `telephone`) VALUES (2, 'youku1327', '1996');

1.2 商品表

CREATE TABLE `product` (
  `productId` int(11) NOT NULL AUTO_INCREMENT COMMENT '产品id',
  `productName` varchar(255) DEFAULT NULL COMMENT '产品名称',
  `price` varchar(255) DEFAULT NULL COMMENT '产品价格',
  PRIMARY KEY (`productId`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8;

INSERT INTO `springboot`.`product`(`productId`, `productName`, `price`) VALUES (1, '苹果', '5');
INSERT INTO `springboot`.`product`(`productId`, `productName`, `price`) VALUES (2, '梨', '4');
INSERT INTO `springboot`.`product`(`productId`, `productName`, `price`) VALUES (3, '香蕉', '3');

1.3 订单表

CREATE TABLE `order` (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'id',
  `userId` int(11) DEFAULT NULL COMMENT '客户id',
  `productId` int(11) DEFAULT NULL COMMENT '产品id',
  `orderName` varchar(255) DEFAULT NULL COMMENT '订单名称',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8;

INSERT INTO `springboot`.`order`(`id`, `userId`, `productId`, `orderName`) VALUES (1, 1, 1, '乖乖订单');
INSERT INTO `springboot`.`order`(`id`, `userId`, `productId`, `orderName`) VALUES (2, 2, 2, '悦悦订单');
INSERT INTO `springboot`.`order`(`id`, `userId`, `productId`, `orderName`) VALUES (3, 1, 3, '香香订单');

二 聚集函数的使用

聚集函数的定义就是讲一些行的数据运行某些函数,返回一个期望值;下面讲述的是开发中经常使用到的聚集函数;

2.1 avg()

avg函数也就是计算行的数量,通过计算这些行的特定列值和,计算出平均值(特定列值之和/行数=平均值);使用时注意其会忽略列值为NULL的行;

语句示例:

SELECT AVG(price) FROM product;

语句结果:

4

语句分析:
查询价格平均值来自商品表(5+4+3)/3=4;

2.2 count()

count函数用于计算行数,其中count(*)计算所有行的数目,count("column")会忽略column为NULL的行数;

语句示例:

SELECT count(*) FROM product;

语句结果:

3

语句分析:

查询总行数来自商品表;

2.3 max()

max函数返回特定列值的最大值;忽略特定列为NULL的行;

语句示例:

SELECT max(price) FROM product;

语句结果:

5

语句分析:

查询价格的最大值来自商品表;

2.4 min()

返回特定列的最小值;忽略特定列为NULL的行;

语句示例:

SELECT min(price) FROM product;

语句结果:

3

语句分析:

查询价格的最小值来自商品表;

2.5 sum()

返回特定列的和;忽略特定列为NULL的行;

语句示例:

SELECT sum(price) FROM product;

语句结果:

12

语句分析:

查询价格的总和来自商品表;

三 分组数据

分组定义就是按照特定的列进行分组查询,使用 GROUP BY 子句进行分组查询;注意点:SELEC后面的列必须出现在group by 子句后面,否则报语法错误;通常 group by 子句的位置是where 条件之后,order by 子句之前;

3.1 分组求和

语句示例:

SELECT sum(price) FROM product GROUP BY productName;

语句结果:

4
5
3

语句分析:

先根据商品名称分为三组 苹果 ,梨 , 香蕉 ;再根据不同的分组求和,因为我们表中的数据只有这三条所以就是每行的值;

3.2 分组过滤

语句示例:

SELECT count(*) FROM `order` GROUP BY userId HAVING count(*) > 1;

语句结果:

2

语句分析

查询 条数来自 订单表 根据客户id分组,过滤条件 条数大于2;注意 having 与 where其实差别不大,通常我们讲where当作标准的过滤条件,having用作分组过滤条件;注意有的数据库管理系统having不支持别名作为分组过滤条件中的一部分;

3.3 分组排序

语句示例:

SELECT count(*) as count FROM `order` GROUP BY userId ORDER BY count;

语句结果:

1
2

语句分析
查询 行数 来自 订单表 根据 客户id分组,根据 行数排序;注意点是经过分组后结果看似经过排序,其实并不能确保是排序后的结果,所以要排序一定要使用order by子句;

四 子查询

子查询的定义是在查询中嵌套查询;注意子查询只能返回单列,若企图返回多列会报语法错误;

语句示例:

SELECT
	userName 
FROM
	customer 
WHERE
	userId = ( SELECT userId FROM `order` WHERE orderName = '乖乖订单' )

语句结果:

zxzxz

语句分析:

是执行语句 【SELECT userId FROM order WHERE orderName = '乖乖订单' 】得到结果 userId = '1' ;
然后执行语句 【 SELECT userName FROM customer WHERE userId = '1'】;

五 联结表

联结表也就是我们通常意义上的关联表查询,主要功能是能在多表中使用一条sql检索出期望值,但实际库表中是存在的,只在查询期间存在;其主要分为内联结和外连接使用的 join 关键字;联结表会返回一对多,一对一,多对多关系;联结表不建议超过三张表以上;

5.1 简单联结

语句示例:

SELECT
	userName,
	orderName 
FROM
	customer,
	`order` 
WHERE
	customer.userId = `order`.userId;

语句结果:

zxzxz	乖乖订单
youku1327	悦悦订单
zxzxz	香香订单

语句分析 :

查询 用户名来自用户表,查询订单名称来自订单表,根据 订单表的客户id 等于 客户表的客户id做为联结条件;也就是说会查询出两张表根据userId为等值条件的 userName 和 orderName 的 数据;

注意点 : 简单联结中where子句后面 必须 要带上 两张表的联结关系,否则会出现笛卡尔集(比如3行数据联结另一张表3行数据会产生3*3=9条)

5.2 内联结

内连接(inner join) 又称等值联结,其查询结果跟之前的简单联结一致;

语句示例:

SELECT
	userName,
	orderName 
FROM
	customer
	INNER JOIN `order` ON ( customer.userId = `order`.userId );

语句结果:

zxzxz	乖乖订单
youku1327	悦悦订单
zxzxz	香香订单

语句分析:

跟之前的简单联结稍微不同的是 等值条件 是放在 on 关键字后面,在等值条件后面还可以进行 where 子句过滤条件查询;

5.3 自然联结

自然联结与标准的联结不同就是只返回值唯一的列,不会返回重复的列;

自然联结示例:

SELECT
	userName,
	orderName 
FROM
	customer
	INNER JOIN `order` ON ( customer.userId = `order`.userId );

自然联结结果

zxzxz	乖乖订单
youku1327	悦悦订单
zxzxz	香香订单

非自然联结示例:

SELECT
	* 
FROM
	customer
	INNER JOIN `order` ON ( customer.userId = `order`.userId );	

非自然联结结果:

1	zxzxz	1327	1	1	1	乖乖订单
2	youku1327	1996	2	2	2	悦悦订单
1	zxzxz	1327	3	1	3	香香订单

重复列是 userId;

5.4 外联结

右外联结

语句示例:

SELECT
	* 
FROM
	`order`
	RIGHT OUTER JOIN customer ON ( customer.userId = `order`.userId );	

右外联结是指 相对于 OUTER JOIN 右边的表,那么这会查询出右边表的所有数据 和根据等值条件匹配左边表的数据,如果左边表的数据不匹配,那么其返回列的值是NULL充当;

左外联结

语句示例:

SELECT
	* 
FROM
	customer
	LEFT OUTER JOIN  	`order` ON ( customer.userId = `order`.userId );	

左外联结是指 相对于 OUTER JOIN 左边的表,那么这会查询出左边表的所有数据 和根据等值条件匹配右边表的数据,如果右边表的数据不匹配,那么其返回列的值是NULL充当;

区别:

左外联结和右外联结其实没什么不同,只是查询表顺序不一致,我们通过置换 表的相对位置就可以查询出一样的结果;

六 组合查询

组合查询是指可以执行多条SELECT 语句,其查询的结构是一致的,返回查询结果,通常我们称为复合操作或者并(union)

语句示例:

SELECT
	userId 
FROM
	customer UNION
SELECT
	userId 
FROM
	`order`

返回结果:

1
2

语句分析:

union 关联的字段或者聚合函数在两张表中必须是相同的,其默认会讲结果进行去重处理;如果不去重可以使用 union all

语句示例:

SELECT
	userId 
FROM
	customer UNION ALL
SELECT
	userId 
FROM
	`order`

执行结果:

1
2
1
2
1

语句分析:
等同于讲客户表和订单表的用户id都合并为一个并集查询出来,而且不去重;如果对组合语句进行排序,默认是会作用于组合后的数据字段排序,而不是作用于其中的一条查询语句;

七 结束语

到本文看完,基本的查询语句都已经学会了,后面就是插入,更新,删除,相对于查询比较简单,有空后续会写相关文章;

相关文章