MySQL的五子句查询和回溯统计

太过爱你忘了你带给我的痛 2022-06-07 00:56 371阅读 0赞

**3:五子句**

**1:where子句**

**1:where原理**

1:用来判断数据:筛选数据,where子句返回结果:0或者1,0代表false,1代表true,所以SQL语句是从1开始的

2:where是唯一一个是直接从磁盘获取数据的时候就开始判断的条件,从磁盘取出一条记录,开始进行where判断:判断的结果如果成立就保存到内存中,如果失败则直接放弃

**2:SQL语法**

1:所以条件判断需要运算符: >,<,<=,>=,=,like模糊,between and区域之间,in/not in

2:逻辑运算符

&&(and),||(or),|(not)

**3:案例demo1**

业务需求:

**找出id为1,3,5的学生**

1:使用逻辑判断

select \*from 表名 where id=1 || id=3 || id=5

2:落在集合中in(1,3,5)

select \* from 表名 where id(1,3,5)

![Center][]

![Image 1][]

**4:案例demo2**

找出身高在180到190之间的学生

select \* from my\_student where height>=180 and height<=190;

select \* from my\_student where between 180 and190;

between本身是闭区间;between左边的值必须小于或者等于右边的值

![Image 1][]

![Center 1][]

![Image 1][]

![Center 2][]

**2:group by 分组**

**1:概述**

1:根据某个字段进行分组(相同的放一组,不同的放不同的组)

2:是为了统计数据(按组统计),按分组字段进行数据统计

**2:SQL统计函数**

1:count(): 统计分组后的记录数: 每一组有多少记录

2:Max() 统计每组中最大的值

3:Min() 统计最小值

4:Avg() 统计平均值

5:Sum() 统计和

**1:分组后默认排序**

**3:案例demo**

根据性别分组

**1:创建数据库表**

USE test

/\*\*创建表\*/

CREATE TABLE IF NOT EXISTS test.T\_student(

id INT NOT NULL,PRIMARY KEY (\`id\`),

number INT,

NAME VARCHAR(10),

gender VARCHAR(3),

age INT,

height LONG

)CHARSET utf8;

添加主键

ALTER TABLE t\_student ADD PRIMARY KEY(id);

删除主键

ALTER TABLE t\_student DROP PRIMARY KEY;

3:添加自增主键

ALTER TABLE t\_student ADD PRIMARY KEY (id);

ALTER TABLE t\_student MODIFY id INT AUTO\_INCREMENT;

**2:插入数据**

INSERT INTO t\_student(number,NAME,gender,age,height) VALUES(001,'刘菲菲','女',20,165);

INSERT INTO t\_student(number,NAME,gender,age,height) VALUES(001,'梦梦','男',22,185);

INSERT INTO t\_student(number,NAME,gender,age,height) VALUES(001,'林青霞','女',25,175);

![Center 3][]

![Image 1][]

**3:查询**

1:根据性别进行查询,发现数据丢失

![Center 4][]

![Image 1][]

2:/\*分组统计:身高,平均年龄,平均身高,总年龄\*/

select gender as 性别,count(\*) as 总数,

max(height)as 最高,

min(height)as 最矮,

avg(age)as 平均年龄,

avg(height)as 平均身高,

sum(age) as 总的年龄 from t\_student group by gender;

![Center 5][]

![Image 1][]

**2:group by 分组后会自动排序**

默认是升序,group by 字段\[asc/desc\]; --对分组后的结果合并之后的整个结果进行了排序

**1:降序**

select gender as 性别,count(\*) as 总数,

max(height)as 最高,

min(height)as 最矮,

avg(age)as 平均年龄,

avg(height)as 平均身高,

sum(age) as 总的年龄 from t\_student group by gender desc;

![Center 6][]

![Image 1][]

**3:多个字段分组排序**

1:创建一个班级表

/\*\*创建表\*/

CREATE TABLE IF NOT EXISTS test.T\_class(

NAME VARCHAR(20),

room VARCHAR(10)

)CHARSET utf8;

SELECT \* FROM T\_class;

![Center 7][]

![Image 1][]

2:添加主键

alter table t\_class add id int primary key auto\_increment;

3:插入数据

INSERT INTO T\_class(NAME,room) VALUES('国语','201');

INSERT INTO T\_class(NAME,room) VALUES('java','202');

INSERT INTO T\_class(NAME,room) VALUES('计算机原理','203');

4:学生表添加外键c\_id

ALTER TABLE t\_student ADD c\_id INT;

5:分组多字段查询

1:rand获取随机的id在1-3之间的班级,学生关联的班级是随机的

rand 0-3 ceil 向上取整 1-3

UPDATE T\_student SET c\_id=CEIL(RAND()\*3);

![Center 8][]

![Image 1][]

2:多字段分组,先班级,后按男女

select c\_id,gender,count(\*)from T\_student group by c\_id,gender;

先根据有关字段进行分组,然后对分组后的结果再次按照其他字段进行连接

![Center 9][]

![Image 1][]

3:group\_concat(字段)

完成对分组的结果中的某个字段进行字符串连接(保留改组所有的某个字段,例如学生表中的学生姓名)

SELECT c\_id,gender,COUNT(\*),GROUP\_CONCAT(NAME)

FROM T\_student GROUP BY c\_id,gender;

![Image 1][]

![Center 10][]

**4:回溯统计:with rollup**

任何一个分组后都会有一个小组,最后都需要向上级分组进行汇报统计,根据当前分组的字段

回溯统计会将分组统计的

1:单个字段的回溯

select c\_id,count(\*) from 表名 group by c\_id with rollup;

![Center 11][]

![Image 1][]

2:多字段回溯

分析第一层分组会有此回溯,第二次分组要看到第一次分组的组数,组数是多少,回溯就是多少,然后加上第一层回溯就行了

SELECT c\_id,gender,COUNT(\*),GROUP\_CONCAT(NAME)

FROM T\_student GROUP BY c\_id,gender with rollup;

![Center 12][]

![Image 1][]

**4:hanving 聚合查询**

**1:having与where的区别**

1:having子句同where子句一样,进行条件判断

2:where是针对磁盘进行判断,进入到内存之后,会进行分组操作,分组的结果就需要having进行处理哟

3:having能做where能做的几乎所有的事情,但是where却不能做having能做的很多事情

4:,having能够使用字段别名:where不能,where是从磁盘中取出数据,而名字只可能是字段,别名是字段进行到内存后才会产生

**2:案例demo**

1:业务需求

求出所有班级人数大于等于2的学生人数

2:查询语句

select c\_id,count(\*) from t\_student group by c\_id having count(\*)>=2;

![Center 13][]

![Image 1][]

3:注意问题

假如将where放在前面

SELECT c\_id,COUNT(\*) FROM t\_student WHERE COUNT(\*)>=2 GROUP BY c\_id;

![Center 14][]

![Image 1][]

**3:案例demo2**

having能够使用字段别名:where不能,

SELECT c\_id,COUNT(\*) AS total FROM t\_student GROUP BY c\_id HAVING total>=2;

![Center 15][]

![Image 1][]

where不能使用字段别名,因为它是从表中查找

SELECT c\_id,COUNT(\*) AS total FROM t\_student WHERE total>=2 GROUP BY c\_id;

![Center 16][]

![Image 1][]

**4:order by 子句**

1:分组后就取一条记录,分组是为了统计

select \* from t\_student group by c\_id;

![Image 1][]

2:排序,取出所有的记录

select \* from t\_student order by c\_id;

![Image 1][]

3:排序可以进行多字段排序:

先根据某个字段进行排序,然后排序号的内容,再按照某个字段再次进行排序

1:先班级排序,再性别排序

select \* from t\_student order by c\_id,gender desc;

![Center 17][]

![Image 1][]

**5:Limit子句**

limit子句是一种限制结果的语句:限制数量,主要是为了实现数据的分页:为用户节省时间,提交服务器的响应效率,减少资源的浪费,用户可以点击页面1.2.3.4实现页面的跳转

对于服务器来讲:根据用户选择的页面来获取不同的数据:limit offset,length

length:每页显示的数据量,基本不变的

offset:

offset=(页码-1)\*每页显示条数

![SouthEast][]

**1:limit有两种使用方式**

1:只用来限制长度(数据量);limit数据量

select \* from t\_student limit 6;

![Image 1][]

2:限制起始位置,限制数量:limit 起始位置,长度;

查询学生,前两个,记录数总0开始编号

select \* from t\_student limit 0,2;

![Image 1][]

![SouthEast 1][]

select \* from t\_student limit 2,2; 从3开始找

![Image 1][] ![20171001223357930][]

[Center]: /images/20220607/ba8f87900fcc40998dfaf97dede8f886.png
[Image 1]: 
[Center 1]: /images/20220607/79576bf8626f42689a4b98829d9f81cd.png
[Center 2]: /images/20220607/ebd19178d74243fa916d8ba26dd9f4aa.png
[Center 3]: /images/20220607/bb33ebd99d5a401392d896f77392a6eb.png
[Center 4]: /images/20220607/129b1808b87e445ebf8ee6104b5085c9.png
[Center 5]: /images/20220607/71869c2822b44883845ee400f7efaffb.png
[Center 6]: /images/20220607/1ff0f93a0bc347e8a1607473667c3bce.png
[Center 7]: /images/20220607/78cffce907774a8cac61221c11f81be4.png
[Center 8]: /images/20220607/54aa242327944be28db7afd096572b08.png
[Center 9]: /images/20220607/2461e33061fe4ec4bb426bcdfc9f233c.png
[Center 10]: /images/20220607/bb8d5f6044634a8c88ef644a9227c78c.png
[Center 11]: /images/20220607/f95d30e2201c4df5839c78cd86f5d754.png
[Center 12]: /images/20220607/d9c4abb2aac449bb802b24492c97beb2.png
[Center 13]: /images/20220607/19a6312e3b164404ab71ee1a41708386.png
[Center 14]: /images/20220607/1053e7fd75c140e99a66b346898a54f9.png
[Center 15]: /images/20220607/200ab0eda9614fd5a51a5e44340e9206.png
[Center 16]: /images/20220607/0872248166ad473b8fe704d0fdf15fa7.png
[Center 17]: /images/20220607/76251f4d60394c9ab18c19ea435f7141.png
[SouthEast]: /images/20220607/1070926607844da8b12919c361a3cc5d.png
[SouthEast 1]: /images/20220607/7c72318c33644186b7e4a91319f4624c.png
[20171001223357930]: /images/20220607/ba9a23991a884b399446f3d3d58503a2.png