mysql --学习笔记04

今天看到一句令人深思的话：
怎样让世界变得美好？
先让自己变得更美好。

分组

在实际业务中，经常会对数据进行汇总分析，通过 group by 分组查询可解决该需求

1. group by

• group by的含义:将查询结果按照1个或多个字段进行分组，字段值相同的为一组
• group by可用于单个字段分组，也可用于多个字段分组

select * from students;
+----+-----------+------+--------+--------+--------+-----------+
| id | name      | age  | height | gender | cls_id | is_delete |
+----+-----------+------+--------+--------+--------+-----------+
|  1 | 小明      |   18 | 180.00 | 女     |      1 |           |
|  2 | 小月月    |   18 | 180.00 | 女     |      2 |          |
|  3 | 彭于晏    |   29 | 185.00 | 男     |      1 |           |
|  4 | 刘德华    |   59 | 175.00 | 男     |      2 |          |
|  5 | 黄蓉      |   38 | 160.00 | 女     |      1 |           |
|  6 | 凤姐      |   28 | 150.00 | 保密   |      2 |          |
|  7 | 王祖贤    |   18 | 172.00 | 女     |      1 |          |
|  8 | 周杰伦    |   36 |   NULL | 男     |      1 |           |
|  9 | 程坤      |   27 | 181.00 | 男     |      2 |           |
| 10 | 刘亦菲    |   25 | 166.00 | 女     |      2 |           |
| 11 | 金星      |   33 | 162.00 | 中性   |      3 |          |
| 12 | 静香      |   12 | 180.00 | 女     |      4 |           |
| 13 | 周杰      |   34 | 176.00 | 女     |      5 |           |
| 14 | 郭靖      |   12 | 170.00 | 男     |      4 |           |
+----+-----------+------+--------+--------+--------+-----------+
select gender from students group by gender;
+--------+
| gender |
+--------+
| 男     |
| 女     |
| 中性   |
| 保密   |
+--------+

2. group by + group_concat()

• group_concat(字段名)可以作为一个输出字段来使用
  表示分组之后，根据分组结果，使用
• group_concat()来放置每一组的某字段的值的集合

select gender,group_concat(name) from students group by gender;
+--------+-----------------------------------------------------------+
| gender | group_concat(name)                                        |
+--------+-----------------------------------------------------------+
| 男     | 彭于晏,刘德华,周杰伦,程坤,郭靖                                 |
| 女     | 小明,小月月,黄蓉,王祖贤,刘亦菲,静香,周杰                        |
| 中性   | 金星                                                       |
| 保密   | 凤姐                                                       |
+--------+-----------------------------------------------------------+

统计出同种性别的学生id

select gender,group_concat(id) from students group by gender;
+--------+------------------+
| gender | group_concat(id) |
+--------+------------------+
| 男     | 3,4,8,9,14       |
| 女     | 1,2,5,7,10,12,13 |
| 中性   | 11               |
| 保密   | 6                |
+--------+------------------+

1. group by + 聚合函数
   
   • 通过group_concat()的启发，我们既然可以统计出每个分组的某字段的值的集合，那么我们也可以通过聚合函数来对这个值的集合做一些操作

elect gender,group_concat(age) from students group by gender;
+--------+----------------------+
| gender | group_concat(age)    |
+--------+----------------------+
| 男     | 29,59,36,27,12       |
| 女     | 18,18,38,18,25,12,34 |
| 中性   | 33                   |
| 保密   | 28                   |
+--------+----------------------+

4. group by + having

• having 条件表达式：分组查询完后，再指定一些条件对分组后的查询结果进行过滤
• having作用和where相似，但having只能用于group by，且having可以使用聚合函数

select gender ,count(*) from students group by gender having avg(age)>30;
+--------+----------+
| gender | count(*) |
+--------+----------+
| 男     |        5 |
| 保密   |        1 |
+--------+----------+

5. group by + with rollup

• with rollup的作用是：在最后新增一行，来记录当前列里所有记录的总和

select gender,count(*) from students group by gender with rollup;
+--------+----------+
| gender | count(*) |
+--------+----------+
| 男     |        5 |
| 女     |        7 |
| 中性   |        1 |
| 保密   |        1 |
| NULL   |       14 |
+--------+----------+

获取部分行

当数据量过大时，通过分批、分页加载数据既能提升加载速度，也可更好显示查询结果

语法

select * from 表名 limit start,count
说明
从start位置开始，获取count条数据
limit 必须放在查询的最后面

查询前3行男生信息

select * from students where gender=1 limit 3; # 默认0可以省略
select * from students where gender=1 limit 0,3;

分页

已知：每页显示m条数据，当前显示第n页
求第n页的数据

select * from students where is_delete=0 limit (n-1)*m,m

连接查询

• 内连接查询：查询的结果为两个表匹配到的数据
• 左连接查询：查询的结果为两个表匹配到的数据，左表特有的数据，对于右表中不存在的数据使用null填充
• 右连接查询：查询的结果为两个表匹配到的数据，右表特有的数据，对于左表中不存在的数据使用null填充

语法

select * from 表1 inner或left或right join 表2 on 表1.列 = 表2.列

使用内连接查询班级表与学生表

select * from students inner join classes on students.cls_id = classes.id;

使用左连接查询班级表与学生表

此处使用了as为表起别名，目的是编写简单

select * from students as s left join classes as c on s.cls_id = c.id;

使用右连接查询班级表与学生表

select * from students as s right join classes as c on s.cls_id = c.id;

查询学生姓名及班级名称

select s.name,c.name from students as s inner join classes as c on s.cls_id = c.id;

自关联查询（不太懂

设计省信息的表结构provinces
id
title
proid
设计市信息的表结构citys

id
title
proid
设计区县信息表结构areas

id
title
proid
title表示名称，proid表示所属上一级的id值，比如citys.proid 是对应所属省份的id，areas.proid对应的是所属城市的id值

# 表的设计 

create table areas(
    aid int primary key,
    atitle varchar(20),
    pid int
);

查询省的名称为“山西省”的所有城市
select city.* from areas as city
inner join areas as province on city.pid=province.aid
where province.atitle='山西省';

查询市的名称为“广州市”的所有区县
select dis.* from areas as dis
inner join areas as city on city.aid=dis.pid
where city.atitle='广州市';

子查询

理解：在一个 select 语句中,嵌入了另外一个 select 语句, 那么被嵌入的 select 语句称之为子查询语

1. 主查询和子查询的关系

子查询是嵌入到主查询中
子查询是辅助主查询的,要么充当条件,要么充当数据源
子查询是可以独立存在的语句,是一条完整的 select 语句

子查询分类

标量子查询: 子查询返回的结果是一个值(一行一列)
列子查询: 返回的结果是一列(一列多行)
行子查询: 返回的结果是一行(一行多列)
表子查询: 返回的结果是一个临时表(多行多列)

查询大于平均年龄的学生
select * from students where age > (select avg(age) from students);

列级子查询
例2: 查询有班级名称的学生信息
找出班级表中的班级 id
找出学生表中有对应班级的名字
select name from students where cls_id in (select id from classes);

行级子查询(了解)
例3: 查找班级年龄最大,且身高最高的学生
select * from students where (height,age) = (select max(height),max(age) from students);

表子查询(了解)
例4: 查询编号小于6的男性同学的姓名
  select name from (select gender,name from students where id<6) as s where gender="男";

总结

查询的完整格式 ^^ 不要被吓到 其实很简单 ! !

SELECT select_expr [,select_expr,...] [      
      FROM tb_name
      [WHERE 条件判断]
      [GROUP BY {col_name | postion} [ASC | DESC], ...] 
      [HAVING WHERE 条件判断]
      [ORDER BY {col_name|expr|postion} [ASC | DESC], ...]
      [ LIMIT {[offset,]rowcount | row_count OFFSET offset}]
]

完整的select语句
select distinct *
from 表名
where ....
group by ... having ...
order by ...
limit start,count

• 执行顺序为：
  from 表名
  where ….
  group by …
  select distinct *
  having …
  order by …
  limit start,count
  实际使用中，只是语句中某些部分的组合，而不是全部