单表操作
group by
-
分组: 将记录按照某个相同的字段进行归类
-
聚合函数
- count()
- sum()
- max()
- min()
- avg()
-
select 聚合函数 [as 列名] from 表名 group by 列名;
mysql> select * from employee;
+----+---------+--------+-----+--------+-----------+
| id | name | gender | age | salary | depart_id |
+----+---------+--------+-----+--------+-----------+
| 1 | alpha | male | 35 | 12000 | 1 |
| 2 | bravo | male | 28 | 10000 | 1 |
| 3 | charlie | female | 22 | 7000 | 1 |
| 4 | delta | female | 37 | 14000 | 1 |
| 5 | echo | male | 24 | 8000 | 2 |
| 6 | foxtrot | female | 42 | 20000 | 2 |
| 7 | golf | female | 29 | 11000 | 2 |
| 8 | hotel | male | 31 | 13000 | 2 |
| 9 | india | male | 40 | 18000 | 3 |
| 10 | juliet | male | 21 | 6000 | 3 |
+----+---------+--------+-----+--------+-----------+
10 rows in set (0.00 sec)
mysql> select gender, count(id) as total from employee group by gender;
+--------+-------+
| gender | total |
+--------+-------+
| female | 4 |
| male | 6 |
+--------+-------+
2 rows in set (0.00 sec)
mysql> select depart_id, max(salary) from employee group by depart_id;
+-----------+-------------+
| depart_id | max(salary) |
+-----------+-------------+
| 1 | 14000 |
| 2 | 20000 |
| 3 | 18000 |
+-----------+-------------+
3 rows in set (0.00 sec)
mysql> select depart_id, avg(salary) from employee group by depart_id;
+-----------+-------------+
| depart_id | avg(salary) |
+-----------+-------------+
| 1 | 10750 |
| 2 | 13000 |
| 3 | 12000 |
+-----------+-------------+
3 rows in set (0.00 sec)
having
- having是对group by后的数据进行二次筛选
select 聚合函数 [as 列名] from 表名 group by 列名 having 条件;
mysql> select depart_id, avg(salary) from employee group by depart_id;
+-----------+-------------+
| depart_id | avg(salary) |
+-----------+-------------+
| 1 | 10750 |
| 2 | 13000 |
| 3 | 12000 |
+-----------+-------------+
3 rows in set (0.00 sec)
mysql> select depart_id, avg(salary) from employee group by depart_id having depart_id=1;
+-----------+-------------+
| depart_id | avg(salary) |
+-----------+-------------+
| 1 | 10750 |
+-----------+-------------+
1 row in set (0.00 sec)
order by
order by 列名 asc升序order by 列名 desc降序
mysql> select * from employee order by age asc;
+----+---------+--------+-----+--------+-----------+
| id | name | gender | age | salary | depart_id |
+----+---------+--------+-----+--------+-----------+
| 10 | juliet | male | 21 | 6000 | 3 |
| 3 | charlie | female | 22 | 7000 | 1 |
| 5 | echo | male | 24 | 8000 | 2 |
| 2 | bravo | male | 28 | 10000 | 1 |
| 7 | golf | female | 29 | 11000 | 2 |
| 8 | hotel | male | 31 | 13000 | 2 |
| 1 | alpha | male | 35 | 12000 | 1 |
| 4 | delta | female | 37 | 14000 | 1 |
| 9 | india | male | 40 | 18000 | 3 |
| 6 | foxtrot | female | 42 | 20000 | 2 |
+----+---------+--------+-----+--------+-----------+
10 rows in set (0.00 sec)
mysql> select * from employee order by salary desc;
+----+---------+--------+-----+--------+-----------+
| id | name | gender | age | salary | depart_id |
+----+---------+--------+-----+--------+-----------+
| 6 | foxtrot | female | 42 | 20000 | 2 |
| 9 | india | male | 40 | 18000 | 3 |
| 4 | delta | female | 37 | 14000 | 1 |
| 8 | hotel | male | 31 | 13000 | 2 |
| 1 | alpha | male | 35 | 12000 | 1 |
| 7 | golf | female | 29 | 11000 | 2 |
| 2 | bravo | male | 28 | 10000 | 1 |
| 5 | echo | male | 24 | 8000 | 2 |
| 3 | charlie | female | 22 | 7000 | 1 |
| 10 | juliet | male | 21 | 6000 | 3 |
+----+---------+--------+-----+--------+-----------+
10 rows in set (0.00 sec)
limit
-
限制查询记录的个数
-
limit offset, size- offset 表示起始行数, 第一行对应的offset是0
- size 表示查询记录的个数
mysql> select * from employee;
+----+---------+--------+-----+--------+-----------+
| id | name | gender | age | salary | depart_id |
+----+---------+--------+-----+--------+-----------+
| 1 | alpha | male | 35 | 12000 | 1 |
| 2 | bravo | male | 28 | 10000 | 1 |
| 3 | charlie | female | 22 | 7000 | 1 |
| 4 | delta | female | 37 | 14000 | 1 |
| 5 | echo | male | 24 | 8000 | 2 |
| 6 | foxtrot | female | 42 | 20000 | 2 |
| 7 | golf | female | 29 | 11000 | 2 |
| 8 | hotel | male | 31 | 13000 | 2 |
| 9 | india | male | 40 | 18000 | 3 |
| 10 | juliet | male | 21 | 6000 | 3 |
+----+---------+--------+-----+--------+-----------+
10 rows in set (0.00 sec)
mysql> select * from employee limit 2, 3;
+----+---------+--------+-----+--------+-----------+
| id | name | gender | age | salary | depart_id |
+----+---------+--------+-----+--------+-----------+
| 3 | charlie | female | 22 | 7000 | 1 |
| 4 | delta | female | 37 | 14000 | 1 |
| 5 | echo | male | 24 | 8000 | 2 |
+----+---------+--------+-----+--------+-----------+
3 rows in set (0.00 sec)
使用顺序
where > group by > having > order by > limit
多表操作
外键
- 外键是表中的一个字段, 这个字段与另外一个表中的字段相匹配
- 作用
- 减少占用的空间
- 方便后期修改
一对多
constraint 外键名 foreign key (被约束的字段) references 表名(字段名)
mysql> select * from employee;
+----+---------+--------+-----+--------+-----------+
| id | name | gender | age | salary | depart_id |
+----+---------+--------+-----+--------+-----------+
| 1 | alpha | male | 35 | 12000 | 1 |
| 2 | bravo | male | 28 | 10000 | 1 |
| 3 | charlie | female | 22 | 7000 | 1 |
| 4 | delta | female | 37 | 14000 | 1 |
| 5 | echo | male | 24 | 8000 | 2 |
| 6 | foxtrot | female | 42 | 20000 | 2 |
| 7 | golf | female | 29 | 11000 | 2 |
| 8 | hotel | male | 31 | 13000 | 2 |
| 9 | india | male | 40 | 18000 | 3 |
| 10 | juliet | male | 21 | 6000 | 3 |
+----+---------+--------+-----+--------+-----------+
10 rows in set (0.00 sec)
mysql> select * from department;
+----+------+
| id | dept |
+----+------+
| 1 | RD |
| 2 | IT |
| 3 | HR |
+----+------+
3 rows in set (0.00 sec)
# 添加外键
mysql> alter table employee add constraint fk_employee_dept foreign key (depart_id) references department(id);
Query OK, 10 rows affected (0.04 sec)
Records: 10 Duplicates: 0 Warnings: 0
# 查看两表描述, depaet_id 和 id 的 key列变成了MUL
mysql> desc employee;
+-----------+-----------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-----------+-----------------------+------+-----+---------+----------------+
| id | int(10) unsigned | NO | PRI | NULL | auto_increment |
| name | char(10) | NO | | | |
| gender | enum('female','male') | NO | | male | |
| age | int(11) | NO | | 0 | |
| salary | float | NO | | 0 | |
| depart_id | int(10) unsigned | NO | MUL | 1 | |
+-----------+-----------------------+------+-----+---------+----------------+
6 rows in set (0.00 sec)
mysql> desc department;
+-------+------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+------------------+------+-----+---------+----------------+
| id | int(10) unsigned | NO | PRI | NULL | auto_increment |
| dept | char(10) | NO | | | |
+-------+------------------+------+-----+---------+----------------+
2 rows in set (0.00 sec)
多对多
mysql> select * from boy;
+----+----------+
| id | boy_name |
+----+----------+
| 1 | Jack Ma |
| 2 | Pony Ma |
| 3 | Robin Li |
+----+----------+
3 rows in set (0.00 sec)
mysql> select * from girl;
+----+------------+
| id | girl_name |
+----+------------+
| 1 | Mingzhu Do |
| 2 | Jean Liu |
| 3 | Cathy Meng |
+----+------------+
3 rows in set (0.00 sec)
mysql> create table boy2girl (
->id int unsigned auto_increment primary key,
->boy_id int(10) unsigned not null default 1,
->girl_id int(10) unsigned not null default 1,
->constraint fk_boy2girl_boy foreign key (boy_id) references boy(id),
->constraint fk_boy2girl_girl foreign key (girl_id) references girl(id)
->);
Query OK, 0 rows affected (0.02 sec)
mysql> desc boy2girl;
+---------+------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+---------+------------------+------+-----+---------+----------------+
| id | int(10) unsigned | NO | PRI | NULL | auto_increment |
| boy_id | int(10) unsigned | NO | MUL | 1 | |
| girl_id | int(10) unsigned | NO | MUL | 1 | |
+---------+------------------+------+-----+---------+----------------+
3 rows in set (0.00 sec)
mysql> insert into boy2girl (boy_id, girl_id) values (1,1),(1,2),(1, 3),(2, 2), (2,3),(3,3);
Query OK, 6 rows affected (0.00 sec)
Records: 6 Duplicates: 0 Warnings: 0
# 分别对两个字段进行了限制
mysql> select * from boy2girl;
+----+--------+---------+
| id | boy_id | girl_id |
+----+--------+---------+
| 1 | 1 | 1 |
| 2 | 1 | 2 |
| 3 | 1 | 3 |
| 4 | 2 | 2 |
| 5 | 2 | 3 |
| 6 | 3 | 3 |
+----+--------+---------+
6 rows in set (0.00 sec)
一对一
mysql> select * from employee1;
+----+---------+
| id | name |
+----+---------+
| 1 | alpha |
| 2 | bravo |
| 3 | charlie |
+----+---------+
3 rows in set (0.00 sec)
mysql> create table salary (
-> id int unsigned auto_increment primary key,
-> salary float not null default 0,
-> employee_id int(10) unsigned not null default 1,
-> constraint fk_employee_salary foreign key (employee_id) references employee1(id)
-> );
Query OK, 0 rows affected (0.02 sec)
mysql> select * from salary;
+----+--------+-------------+
| id | salary | employee_id |
+----+--------+-------------+
| 1 | 10000 | 1 |
| 2 | 12000 | 2 |
| 3 | 14000 | 3 |
+----+--------+-------------+
3 rows in set (0.00 sec)
多表联查
left join on左外连接, 优先显示左边表的全部记录right join on右外连接, 优先显示右边表的全部记录inner join on全外连接, 显示左右两表的全部记录
mysql> select * from boy2girl left join boy on boy_id = boy.id left join girl on girl_id = girl.id;
+----+--------+---------+------+----------+------+------------+
| id | boy_id | girl_id | id | boy_name | id | girl_name |
+----+--------+---------+------+----------+------+------------+
| 1 | 1 | 1 | 1 | Jack Ma | 1 | Mingzhu Do |
| 2 | 1 | 2 | 1 | Jack Ma | 2 | Jean Liu |
| 3 | 1 | 3 | 1 | Jack Ma | 3 | Cathy Meng |
| 4 | 2 | 2 | 2 | Pony Ma | 2 | Jean Liu |
| 5 | 2 | 3 | 2 | Pony Ma | 3 | Cathy Meng |
| 6 | 3 | 3 | 3 | Robin Li | 3 | Cathy Meng |
+----+--------+---------+------+----------+------+------------+
6 rows in set (0.00 sec)
mysql> select boy_name,girl_name from boy2girl left join boy on boy_id = boy.id left join girl on girl_id = girl.id;
+----------+------------+
| boy_name | girl_name |
+----------+------------+
| Jack Ma | Mingzhu Do |
| Jack Ma | Jean Liu |
| Jack Ma | Cathy Meng |
| Pony Ma | Jean Liu |
| Pony Ma | Cathy Meng |
| Robin Li | Cathy Meng |
+----------+------------+
6 rows in set (0.00 sec)