前言
本篇我们面向三国学SQL,让学习更有趣。
数据
1、country表
DROP TABLE IF EXISTS `country`;
CREATE TABLE `country` (
`cno` int(11) NOT NULL,
`cname` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
`capital` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '定都地址',
PRIMARY KEY (`cno`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = Dynamic;
数据:
INSERT INTO `country` VALUES (10, '曹魏', '洛阳');
INSERT INTO `country` VALUES (20, '蜀汉', '成都');
INSERT INTO `country` VALUES (30, '东吴', '建业');
2、userinfo表
DROP TABLE IF EXISTS `userinfo`;
CREATE TABLE `userinfo` (
`uid` int(11) NOT NULL COMMENT '编号',
`name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '姓名',
`brains` int(11) NULL DEFAULT NULL COMMENT '智力值',
`power` int(11) NULL DEFAULT NULL COMMENT '武力值',
`cno` int(11) NULL DEFAULT NULL COMMENT '国家编号',
PRIMARY KEY (`uid`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = Dynamic;
数据:
INSERT INTO `userinfo` VALUES (99, '吕布', 30, 100, 0);
INSERT INTO `userinfo` VALUES (1, '夏侯渊', 92, 91, 10);
INSERT INTO `userinfo` VALUES (2, '夏侯惇', 60, 90, 10);
INSERT INTO `userinfo` VALUES (3, '司马懿', 63, 96, 10);
INSERT INTO `userinfo` VALUES (4, '荀彧', 95, 14, 10);
INSERT INTO `userinfo` VALUES (5, '郭嘉', 98, 15, 10);
INSERT INTO `userinfo` VALUES (6, '曹操', 91, 72, 10);
INSERT INTO `userinfo` VALUES (7, '许褚', 36, 96, 10);
INSERT INTO `userinfo` VALUES (8, '张郃', 69, 89, 10);
INSERT INTO `userinfo` VALUES (9, '张辽', 78, 92, 10);
INSERT INTO `userinfo` VALUES (10, '徐晃', 74, 90, 10);
INSERT INTO `userinfo` VALUES (11, '刘备', 76, 74, 20);
INSERT INTO `userinfo` VALUES (12, '关羽', 75, 98, 20);
INSERT INTO `userinfo` VALUES (13, '张飞', 33, 97, 20);
INSERT INTO `userinfo` VALUES (14, '诸葛亮', 100, 38, 20);
INSERT INTO `userinfo` VALUES (15, '赵云', 76, 96, 20);
INSERT INTO `userinfo` VALUES (16, '马超', 44, 96, 20);
INSERT INTO `userinfo` VALUES (17, '黄忠', 63, 93, 20);
INSERT INTO `userinfo` VALUES (18, '魏延', 69, 92, 20);
INSERT INTO `userinfo` VALUES (19, '姜维', 90, 89, 20);
INSERT INTO `userinfo` VALUES (20, '法正', 94, 47, 20);
INSERT INTO `userinfo` VALUES (21, '孙权', 80, 67, 30);
INSERT INTO `userinfo` VALUES (22, '周瑜', 96, 71, 30);
INSERT INTO `userinfo` VALUES (23, '陆逊', 95, 69, 30);
INSERT INTO `userinfo` VALUES (24, '吕蒙', 89, 81, 30);
INSERT INTO `userinfo` VALUES (25, '鲁肃', 92, 71, 30);
INSERT INTO `userinfo` VALUES (26, '张昭', 88, 3, 30);
INSERT INTO `userinfo` VALUES (27, '程普', 79, 79, 30);
INSERT INTO `userinfo` VALUES (28, '周泰', 48, 92, 30);
INSERT INTO `userinfo` VALUES (29, '甘宁', 76, 94, 30);
INSERT INTO `userinfo` VALUES (30, '小乔', 74, 13, 30);
开始学习
1、分组查询 GROUP BY、HAVING
GROUP BY 语句用于结合聚合函数,根据一个或多个列对结果集进行分组。
比如这里对人员信息表中各国武力值大于90的人数做个统计
那此时还想统计武力值大于90的人数超过5个的有哪些国家,就需要HAVING登场了
HAVING 子句可以让我们筛选分组后的各组数据。
2、连接查询
此处应该了解笛卡尔乘积现象: 表1有m行记录,表2有n行记录,那么结果集就有 mXn 行记录,如何解决这个问题呢?
在查询时给加上限定条件,这种查询方式是sql92的标准
再来看一下sql99的语法
select 查询列表
from 表1 别名 【连接类型】表2 别名 on 连接条件
【where 筛选条件】
【group by 分组】
【having 筛选条件】
【order by 排序列表】
左外连接的显示效果,与上图中的结合看一下
3、子查询
出现在其他语句中的select语句,称为子查询或内查询,而子查询应该写在()中
例1:查出比张飞武力值高的人员
例2:查询最低武力值大于曹魏最低武力值的国家编号和其最低武力值 这种是分组之后再使用子查询
以上我们的子查询结果集都是单行的,那么多行子查询又该如何去做?使用多行比较操作符
操作符 | 含义 |
IN / NOT IN | 等于列表中的任意一个 |
ANY / SOME | 和子查询返回的某一个值比较 |
ALL | 和子查询返回的所有值比较 |
例3:找出比蜀国所有人智力值都低的
4、分页查询
LIMIT 子句用于限制由 SELECT 语句返回的数据数量。用法就是从下标多少开始,取多少条记录,下标从0开始。
SELECT column1, column2, columnN
FROM table_name
LIMIT [no of rows]
如果是与前端一起使用,那么前端需要传page(页数),和size(每页的记录数)
SELECT column1, column2, columnN
FROM table_name
LIMIT (page-1)*size,size
5、联合查询
UNION 操作符合并两个或多个 SELECT 语句的结果。
注意:UNION 内部的每个 SELECT 语句必须拥有相同数量的列。列也必须拥有相似的数据类型。同时,每个 SELECT 语句中的列的顺序必须相同。
6、TRUNCATE 与 DELETE的区别
TRUNCATE | DELETE | |
条件删除 | 否 | 是 |
事务回滚 | 否 | 是 |
清理速度 | 快 | 慢 |
高水位重置 | 是 | 否 |
随着不断地进行表记录的DML操作,会不断提高表的高水位线(HWM),DELETE操作之后虽然表的数据删除了,但是并没有降低表的高水位,随着DML操作数据库容量也只会上升,不会下降。所以如果使用DELETE,就算将表中的数据减少了很多,在查询时还是很和DELETE操作前速度一样。
而TRUNCATE操作会重置高水位线,数据库容量也会被重置,之后再进行DML操作速度也会有提升。
7、系统变量
查看所有的系统变量:
SHOW GLOBAL VARIABLES
SHOW SESSION VARIABLES
查看满足条件的部分系统变量
SHOW GLOBAL VARIABLES LIKE '%bin%'
SHOW SESSION VARIABLES LIKE '%char%'
为某个系统变量赋值
SET GLOBAL | [SESSION] 系统变量名 = 值
8、自定义变量
赋值的操作符 = 或者 :=
声明并且初始化
SET @用户变量名 = 值
或者
SET @用户变量名 := 值
或者
SELECT @用户变量名 = 值
局部变量的声明 作用域:仅仅在定义它的begin end中有效
DECLARE 变量名 类型
DECLARE 变量名 类型 DEFAULT 值
赋值:通过SET 或 SELECT
SET 局部变量名 = 值;
SET 局部变量名 := 值;
SELECT @局部变量名 := 值