SQL-SQL查询检索阶段二
# 一 前提准备
先声明一下,下面的库表只是简易的学习示例,不是生产的设计,不要深究,此文我们的目的是学习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都合并为一个并集查询出来,而且不去重;如果对组合语句进行排序,默认是会作用于组合后的数据字段排序,而不是作用于其中的一条查询语句;
七 结束语
到本文看完,基本的查询语句都已经学会了,后面就是插入,更新,删除,相对于查询比较简单,有空后续会写相关文章;