MySQL--04(聚合函数&表连接查询)

聚合函数

　　统计函数 count（）

select count(要统计的字段) from 表名 where [条件]

MariaDB [employe]> select count(*) from department;
+----------+
| count(*) |
+----------+
|        6 |
+----------+
1 row in set (0.00 sec)

MariaDB [employe]> select count(id) from department;
+-----------+
| count(id) |
+-----------+
|         6 |
+-----------+
1 row in set (0.00 sec)

MariaDB [employe]> select count(id) from department;
+-----------+
| count(id) |
+-----------+
|         6 |
+-----------+
1 row in set (0.00 sec)

MariaDB [employe]> select count(id) from department where groups='一分司' ;
+-----------+
| count(id) |
+-----------+
|         2 |
+-----------+
1 row in set (0.00 sec)

View Code

　　求最大值

select max(求最大值的字段) from 表名;

-- 注意，max和其它字段不一定是同一条记录
MariaDB [employe]> select max(amount),name from department;
ERROR 1054 (42S22): Unknown column 'name' in 'field list'
MariaDB [employe]> select max(amount),master from department;
+-------------+--------+
| max(amount) | master |
+-------------+--------+
|          10 | 张三丰 |
+-------------+--------+
1 row in set (0.00 sec)
-- 下面的语句求最大值的部门主管
MariaDB [employe]> select master from department order by amount desc limit 1;
+--------+
| master |
+--------+
| 黄山   |
+--------+
1 row in set (0.00 sec)

MariaDB [employe]>

View Code

　　求最小值

select min(要计算最小值的字段) from 表名;

MariaDB [employe]> select min(kpi) from department;
+----------+
| min(kpi) |
+----------+
|     8.00 |
+----------+
1 row in set (0.00 sec)

MariaDB [employe]>

View Code

　　求和 sum

select sum(要求和/总数的字段) from 表名;

MariaDB [employe]> select sum(amount) from department;
+-------------+
| sum(amount) |
+-------------+
|          54 |
+-------------+
1 row in set (0.00 sec)

View Code

　　求平均值

select avg(要求平均值字段) from 表名;

MariaDB [employe]> select avg(kpi) from department;
+----------+
| avg(kpi) |
+----------+
| 8.663333 |
+----------+
1 row in set (0.00 sec)

View Code

having

MariaDB [employe]> select master from department having amount>8;
ERROR 1054 (42S22): Unknown column 'amount' in 'having clause'

MariaDB [employe]> select master,amount from department having amount>8;
+-----------------+--------+
| master          | amount |
+-----------------+--------+
| 黄山            |     10 |
| 黄玉石          |     10 |
| 黄尼古拉斯*赵四 |     10 |
+-----------------+--------+
3 rows in set (0.00 sec)

MariaDB [employe]> select master,amount from department where amount>8;
+-----------------+--------+
| master          | amount |
+-----------------+--------+
| 黄山            |     10 |
| 黄玉石          |     10 |
| 黄尼古拉斯*赵四 |     10 |
+-----------------+--------+
3 rows in set (0.00 sec)

MariaDB [employe]> select master from department where amount>8;
+-----------------+
| master          |
+-----------------+
| 黄山            |
| 黄玉石          |
| 黄尼古拉斯*赵四 |
+-----------------+
3 rows in set (0.00 sec)

View Code

适合用 having,但不适合用where

MariaDB [employe]> select count(id),bumen from department group by bumen;
+-----------+--------+
| count(id) | bumen  |
+-----------+--------+
|         1 | NULL   |
|         3 | 总部   |
|         2 | 一分司 |
+-----------+--------+
3 rows in set (0.00 sec)

MariaDB [employe]> select count(id),bumen from department group by bumen having count(id)>2;
+-----------+-------+
| count(id) | bumen |
+-----------+-------+
|         3 | 总部  |
+-----------+-------+
1 row in set (0.00 sec)

MariaDB [employe]> select count(id),bumen from department group by bumen where count(id)>2;

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'where count(id)>2' at line 1
MariaDB [employe]>

View Code

having 和 where 都可以对记录进行筛选；但是having跟在group by 后面，group by 跟在 where 后面；having 后面条件必须在 select 字段中出现，没有，就会报错；where 是必须是表中字段

where ... group by ... having

表连接

数据准备

create table student(
    id int primary key auto_increment,
    sname varchar(50),
    cls_id int
)engine=innodb default charset=utf8;

create table class(
    id int primary key auto_increment,
    cname varchar(10),
    teacher varchar(20)
)engine=innodb default charset=utf8;

insert into class (cname,teacher)values('1811a','孙静香');
insert into class (cname,teacher)values('1812a','王三多');
insert into class (cname,teacher)values('1813a','王多鱼');

insert  into student(sname,cls_id)values('谢逊',1);
insert  into student(sname,cls_id)values('周大福',1);
insert  into student(sname,cls_id)values('高程远',1);

View Code

1. 内联接查询

select [字段] from 表名1  inner join 表名2 on 表名1.连接字段=表名2.连接字段

MariaDB [shop]> select *  from class c inner join student s on s.cls_id=c.id;
+----+-------+---------+----+--------+--------+
| id | cname | teacher | id | sname  | cls_id |
+----+-------+---------+----+--------+--------+
|  1 | 1811a | 孙静香  |  1 | 谢逊   |      1 |
|  1 | 1811a | 孙静香  |  2 | 周大福 |      1 |
|  1 | 1811a | 孙静香  |  3 | 高程远 |      1 |
+----+-------+---------+----+--------+--------+
3 rows in set (0.00 sec)

insert  into student(sname,cls_id)values('谢三逊',5);

View Code

2. 右联接

select * from student s right join class c on s.cls_id=c.id;

MariaDB [shop]> select * from student s right join class c on s.cls_id=c.id;
+------+--------+--------+----+-------+---------+
| id   | sname  | cls_id | id | cname | teacher |
+------+--------+--------+----+-------+---------+
|    1 | 谢逊   |      1 |  1 | 1811a | 孙静香  |
|    2 | 周大福 |      1 |  1 | 1811a | 孙静香  |
|    3 | 高程远 |      1 |  1 | 1811a | 孙静香  |
| NULL | NULL   |   NULL |  2 | 1812a | 王三多  |
| NULL | NULL   |   NULL |  3 | 1813a | 王多鱼  |
+------+--------+--------+----+-------+---------+
5 rows in set (0.00 sec)

View Code

3. 左联接

select *  from class c left join student s on s.cls_id=c.id;

MariaDB [shop]> select *  from class c left join student s on s.cls_id=c.id;
+----+-------+---------+------+--------+--------+
| id | cname | teacher | id   | sname  | cls_id |
+----+-------+---------+------+--------+--------+
|  1 | 1811a | 孙静香  |    1 | 谢逊   |      1 |
|  1 | 1811a | 孙静香  |    2 | 周大福 |      1 |
|  1 | 1811a | 孙静香  |    3 | 高程远 |      1 |
|  2 | 1812a | 王三多  | NULL | NULL   |   NULL |
|  3 | 1813a | 王多鱼  | NULL | NULL   |   NULL |
+----+-------+---------+------+--------+--------+
5 rows in set (0.00 sec)

View Code

4. 笛卡尔积

select *  from class c join student s;

MariaDB [shop]> select *  from class c join student s;
+----+-------+---------+----+--------+--------+
| id | cname | teacher | id | sname  | cls_id |
+----+-------+---------+----+--------+--------+
|  1 | 1811a | 孙静香  |  1 | 谢逊   |      1 |
|  2 | 1812a | 王三多  |  1 | 谢逊   |      1 |
|  3 | 1813a | 王多鱼  |  1 | 谢逊   |      1 |
|  1 | 1811a | 孙静香  |  2 | 周大福 |      1 |
|  2 | 1812a | 王三多  |  2 | 周大福 |      1 |
|  3 | 1813a | 王多鱼  |  2 | 周大福 |      1 |
|  1 | 1811a | 孙静香  |  3 | 高程远 |      1 |
|  2 | 1812a | 王三多  |  3 | 高程远 |      1 |
|  3 | 1813a | 王多鱼  |  3 | 高程远 |      1 |
|  1 | 1811a | 孙静香  |  4 | 谢三逊 |      5 |
|  2 | 1812a | 王三多  |  4 | 谢三逊 |      5 |
|  3 | 1813a | 王多鱼  |  4 | 谢三逊 |      5 |
+----+-------+---------+----+--------+--------+
12 rows in set (0.00 sec)

View Code

5. 左表独有

select * from class c left join student s on c.id=s.cls_id where s.id is null;

MariaDB [shop]> select * from class c left join student s on c.id=s.cls_id where s.id is null;
+----+-------+---------+------+-------+--------+
| id | cname | teacher | id   | sname | cls_id |
+----+-------+---------+------+-------+--------+
|  2 | 1812a | 王三多  | NULL | NULL  |   NULL |
|  3 | 1813a | 王多鱼  | NULL | NULL  |   NULL |
+----+-------+---------+------+-------+--------+
2 rows in set (0.00 sec)

View Code

6. 右表独有

select *  from student s right join class c on s.cls_id=c.id where s.id is null;

MariaDB [shop]> select *  from student s right join class c on s.cls_id=c.id where s.id is null;
+------+-------+--------+----+-------+---------+
| id   | sname | cls_id | id | cname | teacher |
+------+-------+--------+----+-------+---------+
| NULL | NULL  |   NULL |  2 | 1812a | 王三多  |
| NULL | NULL  |   NULL |  3 | 1813a | 王多鱼  |
+------+-------+--------+----+-------+---------+
2 rows in set (0.00 sec)

View Code

7. 全联接

MariaDB [shop]> select * from class c left join student s on c.id=s.cls_id
    -> union
    -> select * from class c right join student s on c.id=s.cls_id;
+------+-------+---------+------+--------+--------+
| id   | cname | teacher | id   | sname  | cls_id |
+------+-------+---------+------+--------+--------+
|    1 | 1811a | 孙静香  |    1 | 谢逊   |      1 |
|    1 | 1811a | 孙静香  |    2 | 周大福 |      1 |
|    1 | 1811a | 孙静香  |    3 | 高程远 |      1 |
|    2 | 1812a | 王三多  | NULL | NULL   |   NULL |
|    3 | 1813a | 王多鱼  | NULL | NULL   |   NULL |
| NULL | NULL  | NULL    |    4 | 谢三逊 |      5 |
+------+-------+---------+------+--------+--------+
6 rows in set (0.00 sec)

View Code

# # mysql 本身没有全联接，但是我们可以模拟出来

8. 并集去交集

MariaDB [shop]> select * from class c left join student s on s.cls_id=c.id where s.id is null
    ->      union
    ->    select * from class c right join student s on c.id=s.cls_id where c.id is null;
+------+-------+---------+------+--------+--------+
| id   | cname | teacher | id   | sname  | cls_id |
+------+-------+---------+------+--------+--------+
|    2 | 1812a | 王三多  | NULL | NULL   |   NULL |
|    3 | 1813a | 王多鱼  | NULL | NULL   |   NULL |
| NULL | NULL  | NULL    |    4 | 谢三逊 |      5 |
+------+-------+---------+------+--------+--------+
3 rows in set (0.00 sec)

View Code

9. 自联接（自查询)

表和自身的连接，使用 inner join 来完成

MariaDB [shop]> create table areas(
    id int primary key auto_increment,
    area  varchar(10),
    pid int
    )engine=innodb default charset=utf8;
Query OK, 0 rows affected (0.02 sec)

View Code

测试数据

INSERT INTO `areas` VALUES (1,'北京',NULL),
 (2,'上海',NULL),
 (3,'广东',NULL),
 (4,'天津',NULL),
 (5,'重庆',NULL),
 (6,'昌平',1),
 (7,'顺义',1),
 (8,'海淀',1),
 (9,'朝阳',1),
 (10,'河北',NULL),
 (11,'河南',NULL),
 (12,'山西',NULL),
 (13,'山东',NULL),
 (98,'广州市',3),
 (103,'大同',12),
 (104,'朔州',12),
 (105,'忻州',12),
 (106,'阳泉',12),
 (107,'吕梁',12),
 (108,'晋中',12),
 (109,'长治',12),
 (110,'晋城',12),
 (111,'临汾',12),
 (112,'运城',12),
 (230,'越秀区',98),
 (231,'荔湾区',98),
 (232,'海珠区',98),
 (233,'天河区',98),
 (234,'白云区',98),
 (235,'黄埔区',98),
 (236,'番禺区',98),
 (237,'花都区',98),
 (238,'南沙区',98),
 (239,'增城区',98),
 (240,'从化区',98);

View Code

查询1：所有一级地区

MariaDB [shop]> select * from areas  where pid is null;
+----+------+------+
| id | area | pid  |
+----+------+------+
|  1 | 北京 | NULL |
|  2 | 上海 | NULL |
|  3 | 广东 | NULL |
|  4 | 天津 | NULL |
|  5 | 重庆 | NULL |
| 10 | 河北 | NULL |
| 11 | 河南 | NULL |
| 12 | 山西 | NULL |
| 13 | 山东 | NULL |
+----+------+------+
9 rows in set (0.00 sec)

View Code

查询2：查询省的名称为“山西”的所有城市

MariaDB [shop]> select * from areas as p inner join areas c on c.pid=p.id where p.area='山西';
+----+------+------+-----+------+------+
| id | area | pid  | id  | area | pid  |
+----+------+------+-----+------+------+
| 12 | 山西 | NULL | 103 | 大同 |   12 |
| 12 | 山西 | NULL | 104 | 朔州 |   12 |
| 12 | 山西 | NULL | 105 | 忻州 |   12 |
| 12 | 山西 | NULL | 106 | 阳泉 |   12 |
| 12 | 山西 | NULL | 107 | 吕梁 |   12 |
| 12 | 山西 | NULL | 108 | 晋中 |   12 |
| 12 | 山西 | NULL | 109 | 长治 |   12 |
| 12 | 山西 | NULL | 110 | 晋城 |   12 |
| 12 | 山西 | NULL | 111 | 临汾 |   12 |
| 12 | 山西 | NULL | 112 | 运城 |   12 |
+----+------+------+-----+------+------+

View Code

相关阅读:
call()和apply( )
String.prototype.replace( )
Global对象和浏览器的window对象
 ros qt 項目增加新的线程
 ubuntu18.04 在QT中添加ros环境搭建亲测可用
 ubuntu18.04系统下安装Nvidia驱动 + cuda10.0 + cudnn7
【ROS学习】发布自定义数据结构的话题
 Autoware快速使用资料
 TX2-ubuntu无外接显示器远程桌面时分辨率过低
 Jetson TX2 安装远程桌面软件 NoMachine
原文地址：https://www.cnblogs.com/xinzaiyuan/p/13501778.html