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)
求最大值
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]>
求最小值
select min(要计算最小值的字段) from 表名;
MariaDB [employe]> select min(kpi) from department; +----------+ | min(kpi) | +----------+ | 8.00 | +----------+ 1 row in set (0.00 sec) MariaDB [employe]>
求和 sum
select sum(要求和/总数的字段) from 表名;
MariaDB [employe]> select sum(amount) from department; +-------------+ | sum(amount) | +-------------+ | 54 | +-------------+ 1 row in set (0.00 sec)
求平均值
select avg(要求平均值字段) from 表名;
MariaDB [employe]> select avg(kpi) from department; +----------+ | avg(kpi) | +----------+ | 8.663333 | +----------+ 1 row in set (0.00 sec)
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)
适合用 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]>
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);
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);
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)
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)
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)
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)
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)
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)
# # 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)
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)
测试数据
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);
查询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)
查询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 | +----+------+------+-----+------+------+
10. Update 联表修改
UPDATE A a,B b SET a.name = b.name WHERE a.id = b.id;
