表格结构

20231121_152758 mysql 查询大综合 三国英雄表的创建

https://blog.csdn.net/ifubing/article/details/134534100

查询所有数据

查询所有的国家

SELECT * FROM country

查询所有的兵种

SELECT * FROM solder

查询数据限定列

显示兵种表的id，名称，介绍

SELECT id,NAME,detail FROM solder

显示国家表的编号，名称，兵力

SELECT id,NAME,POWER FROM country;

给显示的列起别名

用as起别名

SELECT id AS 编号,NAME AS 国家名,POWER AS 兵力 FROM country;

用空格起别名

SELECT id 编号,NAME 国家名,POWER 兵力 FROM country;

去重查询

查询英雄中登场的英雄有哪些国家的，显示国家编号

SELECT DISTINCT country_id FROM hero;

查询班级表中所有同学来自哪个城市

select distinct place from student;

条件运算符

>
>=
<
<=
=
!=
<>

查询id小于3的所有国家数据

SELECT * FROM country WHERE id < 3;

查询id不等于2的所有数据

SELECT * FROM country WHERE id != 2;
SELECT * FROM country WHERE id <> 2;

逻辑运算符

and
&&
or
||

查询英雄表，id大于1并且姓别为1的数据

SELECT * FROM hero WHERE id>1 AND gender=1;

查询国家表，要求id小于4或兵力小于40

SELECT * FROM country WHERE id > 2 OR POWER < 30;

范围查找

列名称 between 最小值 and 最大值

最小值与最大值都是包含的

查找英雄表中攻击力大于等于80小于等92的所有数据

SELECT * FROM hero WHERE attack >= 80 AND attack <= 92;
SELECT * FROM hero WHERE attack BETWEEN 80 AND 92;

成员查找

字段 in (值1，值n)

查找英雄表中士兵编号为1的或士兵编号为3的所有数据

SELECT * FROM hero WHERE solder_id=1 OR solder_id=3;
SELECT * FROM hero WHERE solder_id IN (1,3);

空查询

字段 is null
字段 is not null

查出英雄表中士兵编号为空的所有数据

SELECT * FROM hero WHERE solder_id IS null

查出英雄表中士兵编号不为空的所有数据

SELECT * FROM hero WHERE solder_id IS NOT null

模糊查找

列名 like 查询字符串

_，代表一个符号
%，代表0个1个或多个符号

查询英雄表中所有姓刘的数据

SELECT * FROM hero WHERE NAME LIKE '刘%'

查询英雄表中所有姓张的且名字长度为2的

SELECT * FROM hero WHERE NAME LIKE '张_'

查询姓名中包含刘的数据

SELECT * FROM hero WHERE NAME LIKE '%刘%'

查询名字中包含孙包含香的所有数据

SELECT * FROM hero WHERE NAME LIKE '%孙%' AND NAME LIKE '%香%';

排序查找 单列

升序

order by 列名 
order by 列名 asc
order by 列名 desc

查询所有英雄数据，并且按照攻击力由小到大排序

SELECT * FROM hero ORDER BY attack 
SELECT * FROM hero ORDER BY attack asc

查询所有英雄数据，按照颜值由大到小排序

SELECT * FROM hero ORDER BY beauty DESC 

排序查询 多列

order by 列1 规则, 列2 规则

查询英雄表中所有数据，按颜值降序排序，如果颜值相等就按武力值升序排序

SELECT * FROM hero ORDER BY beauty DESC, attack ASC

分组查询

select 分组依据 from 表名 group by 分组依据

对英雄表进行性别上的分组

SELECT gender FROM hero GROUP BY gender

对英雄表进行国家上的分组

SELECT country_id FROM hero GROUP BY country_id

对英雄表的国家进行分组并查看每一个组的英雄名称

SELECT country_id,GROUP_CONCAT(NAME,attack) FROM hero GROUP BY country_id

聚合函数

count
sum
avg
max
min

查英雄表，对国家进行分组，求每一个国家有多少人

SELECT country_id,COUNT(1),GROUP_CONCAT(NAME,attack) FROM hero GROUP BY country_id

查英雄表，对国家进行分组，求国家的总战力是多少

SELECT country_id,SUM(attack),GROUP_CONCAT(NAME,attack) FROM hero GROUP BY country_id

查英雄表，对国家进行分组，求国家的平均战力是多少

SELECT country_id,AVG(attack),GROUP_CONCAT(NAME,attack) FROM hero GROUP BY country_id

查英雄表，对国家进行分组，求国家的最小战力

SELECT country_id,MIN(attack),GROUP_CONCAT(NAME,attack) FROM hero GROUP BY country_id

查英雄表，对国家进行分组，求国家的最强战力

SELECT country_id,MAX(attack),GROUP_CONCAT(NAME,attack) FROM hero GROUP BY country_id

关于聚合参数的注意事项

如果聚合函数的参数值为null

那么它可能是不参与运算

示例

SELECT country_id,COUNT(solder_id),GROUP_CONCAT(NAME,attack) FROM hero GROUP BY country_id

查询结果中，4号国家有3个英雄，但是两个英雄的solder_id值为null，所以呈现出来的count(solder_id)的结果为1个

因为只统计到了一个

所以为了避免误统计

可考虑使用非空的字段作为参数，或其它方式解决

分组筛选

group by 分组依剧 having 聚合条件

查询英雄表，找出总战力在240以上的团体并显示出来

SELECT country_id,SUM(attack),GROUP_CONCAT(NAME,attack) FROM hero GROUP BY country_id HAVING SUM(attack) > 240

查询英雄表，找出平均战力大于81.2的小组并呈现出来

SELECT country_id,AVG(attack),GROUP_CONCAT(NAME,attack) FROM hero GROUP BY country_id HAVING AVG(attack) > 81.2

联合分组

group by 分组依据1，分组依据2

将英雄表进行联合分组，按国家与姓别进行分组

SELECT country_id AS 国家,gender AS 性别,AVG(attack),GROUP_CONCAT(NAME,attack) FROM hero GROUP BY country_id,gender

代码缩进

改良一下查询代码的写法

原写法在一行

SELECT country_id AS 国家,gender AS 性别,AVG(attack),GROUP_CONCAT(NAME,attack) FROM hero GROUP BY country_id,gender HAVING AVG(attack) > 80

改良后

SELECT
	country_id AS 国家,
	gender AS 性别,
	AVG(attack),
	GROUP_CONCAT(NAME,attack) 
FROM 
	hero 
GROUP BY 
	country_id,
	gender 
HAVING 
	AVG(attack) > 80

说明

改良前适合后端语言拼字符串

改良后适合纯mysql语法观赏

连表查询 笛卡尔积现象

select * from 表1 inner join 表2

会产生联表的效果

会形成一张超大的表

超大表的行数 = 表1的行数 * 表2的行数
超大表的列数 = 表1的列数 + 表2的列数

连表查询 内联连 inner join

特点：左右表公平的显示，只要符合关联的数据就显示出来，不符合的不显示

查询三国英雄表，显示每一个英雄的带兵情况

SELECT * FROM 表1 INNER JOIN 表2 ON 表1.外键列 = 表2.主键列;
SELECT * FROM hero INNER JOIN solder ON hero.solder_id = solder.id;

联表查询 左连接 left join

特点，联表的结果偏向左边表，哪怕左表有不符合关联的数据，也不丢弃，无法关联的字段以null来占位

查询三国英雄表，显示每一个英雄的带兵情况

SELECT * FROM 表1 LEFT JOIN 表2 ON 表1.外键列 = 表2.主键列;
SELECT * FROM hero LEFT JOIN solder ON hero.solder_id = solder.id;

联表查询 右连接 right join

特点，联表的结果偏向右边表，哪怕右表有不符合关联的数据，也不丢弃，无法关联的字段以null来占位

查询三国英雄表，显示每一个英雄的带兵情况

SELECT * FROM 表1 RIGHT JOIN 表2 ON 表1.外键列 = 表2.主键列;
SELECT * FROM hero RIGHT JOIN solder ON hero.solder_id = solder.id;

连表查询的列投影

连表查询三国英雄的兵种，只显示关键的列

SELECT 表名.列名,表名.列名 FROM 表1 INNER JOIN 表2 ON 表1.外键列 = 表2.主键列;
SELECT hero.id,hero.name,solder.name,solder.detail FROM hero INNER JOIN solder ON hero.solder_id = solder.id;

连表查询 多表连接

查询三国英雄表，获取国家信息，获取兵种信息

SELECT 表名.列名,表名.列名 FROM 表1 INNER JOIN 表2 ON 表1.外键列 = 表2.主键列 INNER JOIN 表3 ON 表1.外链列2 = 表3.主键列;
SELECT hero.id,hero.name,country.name,solder.name FROM hero INNER JOIN solder ON hero.solder_id = solder.id INNER JOIN country ON hero.country_id = country.id;

联表查询 自关联查询

关于自关联

自关联与外键关联不同

外键关联是与别的表产生数据的关联

自关联是与本本产生数据的关联

体验自关联

显示每一个兵种的信息，包括它是什么兵种，它克什么兵种，它怕什么兵种

SELECT 别名1.列名,别名n.列名 FROM 本表 AS 别名1 INNER JOIN 本表 AS 别名2 ON 别名1.外键列 = 别名2.主键列;
SELECT base.id,base.name AS 兵种,ke.name AS 克制,pa.name AS 被克 FROM solder AS base INNER JOIN solder AS ke ON base.like_id =ke.id INNER JOIN solder AS pa ON base.hate_id = pa.id;

自关联重点

自关联是自己联自己

自关联的本表一定要起别名

本表起别名后都用别名（*的位置还是on的位置，要用表不要用本表名，要用别名）

自关联 城市级别

需求 建表 城市表

CREATE TABLE city(
	id INT PRIMARY KEY AUTO_INCREMENT,
	NAME VARCHAR(8) NOT NULL,
	pid INT
);

需求 添加数据

需求 查询结果

查出每一个地点的父级是谁

SELECT base.id,base.name,parent.name FROM city AS base LEFT JOIN city AS parent ON base.pid = parent.id;

认识子查询

子查询是用一个查询语句的结果作为查询条件

查出其它结果的一种查询方式

根据子查询的结果的不同主要分为三个类别：

结果为单行单列的情况

结果为多行单列的情况

结果为多行多列的情况

子查询 结果为单行单列的情况

查询石家庄有哪些下级地点

1，查出石家庄的id是多少。4

2，查询pid=4的地点并列出来

单行单列的查询结果，我们把它作为一个数值来对待

一般使用运算符来连接

SELECT id FROM city WHERE NAME = '石家庄市';
结果为
1

SELECT * FROM city WHERE pid = (SELECT id FROM city WHERE NAME = '石家庄市');

子查询 多行单列的情况

多行单列的查询结果

我们把它视为一个元组

通常情况下会与成员运算符 in 搭配

查出所有的蜀国与魏国的英雄

1，获取蜀国与魏国的国家编号

2，SELECT * FROM hero WHERE country_id IN (1,2);

查询国家编号

SELECT id FROM country WHERE NAME IN ('魏','蜀');

得到多行单列的查询结果

多行单列的结果用法

我们会把多行单列的结果作为一个元组来使用

当前的结果我们把它视为 （1，2）

最终的语句

SELECT * FROM hero WHERE country_id IN (SELECT id FROM country WHERE NAME IN ('魏','蜀'));

子查询 多行多列的情况

多行多列的查询结果

我们把它当作一张表

表的用法多用于连表查询

一般配合inner join来使用

显示兵种表中id小于3的所有兵种

列出带领这些兵种的英雄

多行多列查询语句

SELECT id,NAME FROM solder WHERE id < 3

查出带这些兵的英雄

SELECT * FROM hero INNER JOIN (SELECT id,NAME FROM solder WHERE id < 3) AS newSolder ON hero.solder_id = newSolder.id

综合演练 联表+自关联

目标

找出可以对付孙尚香的所有武将

拆解

• 查出孙尚香带的是什么兵种（兵种id)
• 查出这个兵种的克制兵种(hate_id)
• 查出英雄表中可装备了克制兵种的所有英雄（英雄.兵种id=hate_id）

  查孙尚香所带兵种的id

  SELECT solder_id FROM hero WHERE NAME = '孙尚香'
  

  

  查这个兵种的克制兵种

  SELECT * FROM solder WHERE id = (SELECT solder_id FROM hero WHERE NAME = '孙尚香')
  

  

  只提取克制兵种的id

  SELECT hate_id FROM solder WHERE id = (SELECT solder_id FROM hero WHERE NAME = '孙尚香')
  

  

  找出带领着克制兵种的武将

  SELECT * FROM hero WHERE solder_id = (SELECT hate_id FROM solder WHERE id = (SELECT solder_id FROM hero WHERE NAME = '孙尚香'))
  

  

  扩展

  需求追加

  需要列出的兵种中不能包含与孙尚香同属一国的武将

  子查询演练 查同胞

  需求

  查出与刘备同属一个国家的英雄（不包含刘备自己）

  拆解

  1，先查刘备是哪个国家的，得到国家编号

  2，查出刘备的id

  3，根据国家编号查英雄排除id为刘备id的

  语句

  SELECT id FROM hero WHERE NAME = '刘备'
  SELECT country_id FROM hero WHERE NAME = '刘备'
  SELECT * FROM hero WHERE country_id = (SELECT country_id FROM hero WHERE NAME = '刘备') AND id != (SELECT id FROM hero WHERE NAME = '刘备');
  

  显示部分查询结果

  从查询结果的第0条开始显示3条数据

  SELECT * FROM hero LIMIT 起始下标,显示个数;
  SELECT * FROM hero LIMIT 0,3;
  

  分页查询

  显示所有数据

  

  设计每页显示3条数据

  第1页，要显示 孙尚香，刘备，张飞

  第2页，要显示 张星彩，甘宁，曹操

  依次类推

  递推法 编写每一页的sql语句

  第1页

  SELECT * FROM hero LIMIT 0,3;
  

  第2页

  SELECT * FROM hero LIMIT 3,3;
  

  第3页

  SELECT * FROM hero LIMIT 6,3;
  

  第N页

  SELECT * FROM hero LIMIT (N-1)*每页显示条目数,每页显示条目数;
  

  分页公式（记住）

  SELECT * FROM hero LIMIT (N-1)*pageNum,pageNum;
  

  分页最大页码数

  算法

  num = 0
  if(总条数%每页条数!=0){
  	num = 1;
  }
  zheng = (int)(总条数/每页条数)
  page = zheng + num
  

  联合查询

  体验联合查询

  

  表头的决定权

  

  共用表的列的情况

  

  联合查询的应用