目录
单表操作
分组
- 分组: 将记录按照某个相同的字段进行归类
- 聚合函数
- count()
- sum()
- max()
- min()
- avg()
group by
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)