时间:2021-07-01 10:21:17 帮助过:8人阅读
目录
select 聚合函数 [as 列名] from 表名 group by 列名;
- <code>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)</code>
select 聚合函数 [as 列名] from 表名 group by 列名 having 条件;
- <code>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)</code>
order by 列名 asc
升序order by 列名 desc
降序
- <code>
- 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)</code>
限制查询记录的个数
limit offset, size
- <code>
- 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)</code>
where > group by > having > order by > limit
constraint 外键名 foreign key (被约束的字段) references 表名(字段名)
- <code>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)</code>
- <code>
- 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)</code>
- <code>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)</code>
left join on
左外连接, 优先显示左边表的全部记录right join on
右外连接, 优先显示左边表的全部记录inner join on
全外连接, 显示左右两表的全部记录
- <code>
- 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)
- </code>
MySQL 03
标签:record desc jea table int warnings 字段 des 连接