MySQL 03
内容导读
互联网集市收集整理的这篇技术教程文章主要介绍了MySQL 03,小编现在分享给大家,供广大互联网技能从业者学习和参考。文章包含12298字,纯文字阅读大概需要18分钟。
内容图文
![MySQL 03](/upload/InfoBanner/zyjiaocheng/515/89399750fe6c4a36b12152f6cc9f9dfe.jpg)
目录
- 单表操作
- 分组
- group by
- having
- order by
- limit
- 使用顺序
- 分组
- 多表操作
- 外键
- 一对多
- 多对多
- 一对一
- 多表联查
单表操作
分组
- 分组: 将记录按照某个相同的字段进行归类
- 聚合函数
- 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)
MySQL 03
标签:record desc jea table int warnings 字段 des 连接
本文系统来源:https://www.cnblogs.com/bigb/p/11767883.html
内容总结
以上是互联网集市为您收集整理的MySQL 03全部内容,希望文章能够帮你解决MySQL 03所遇到的程序开发问题。 如果觉得互联网集市技术教程内容还不错,欢迎将互联网集市网站推荐给程序员好友。
内容备注
版权声明:本文内容由互联网用户自发贡献,该文观点与技术仅代表作者本人。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容, 请发送邮件至 gblab@vip.qq.com 举报,一经查实,本站将立刻删除。
内容手机端
扫描二维码推送至手机访问。
来源:【匿名】