当前位置:Gxlcms > 数据库问题 > MySQL 03

MySQL 03

时间:2021-07-01 10:21:17 帮助过:8人阅读

目录

  • 单表操作
    • 分组
      • group by
      • having
      • order by
      • limit
      • 使用顺序
  • 多表操作
    • 外键
    • 一对多
    • 多对多
    • 一对一
  • 多表联查

单表操作

分组

  • 分组: 将记录按照某个相同的字段进行归类
  • 聚合函数
    • count()
    • sum()
    • max()
    • min()
    • avg()

group by

  • select 聚合函数 [as 列名] from 表名 group by 列名;
  1. <code>mysql> select * from employee;
  2. +----+---------+--------+-----+--------+-----------+
  3. | id | name | gender | age | salary | depart_id |
  4. +----+---------+--------+-----+--------+-----------+
  5. | 1 | alpha | male | 35 | 12000 | 1 |
  6. | 2 | bravo | male | 28 | 10000 | 1 |
  7. | 3 | charlie | female | 22 | 7000 | 1 |
  8. | 4 | delta | female | 37 | 14000 | 1 |
  9. | 5 | echo | male | 24 | 8000 | 2 |
  10. | 6 | foxtrot | female | 42 | 20000 | 2 |
  11. | 7 | golf | female | 29 | 11000 | 2 |
  12. | 8 | hotel | male | 31 | 13000 | 2 |
  13. | 9 | india | male | 40 | 18000 | 3 |
  14. | 10 | juliet | male | 21 | 6000 | 3 |
  15. +----+---------+--------+-----+--------+-----------+
  16. 10 rows in set (0.00 sec)
  17. mysql> select gender, count(id) as total from employee group by gender;
  18. +--------+-------+
  19. | gender | total |
  20. +--------+-------+
  21. | female | 4 |
  22. | male | 6 |
  23. +--------+-------+
  24. 2 rows in set (0.00 sec)
  25. mysql> select depart_id, max(salary) from employee group by depart_id;
  26. +-----------+-------------+
  27. | depart_id | max(salary) |
  28. +-----------+-------------+
  29. | 1 | 14000 |
  30. | 2 | 20000 |
  31. | 3 | 18000 |
  32. +-----------+-------------+
  33. 3 rows in set (0.00 sec)
  34. mysql> select depart_id, avg(salary) from employee group by depart_id;
  35. +-----------+-------------+
  36. | depart_id | avg(salary) |
  37. +-----------+-------------+
  38. | 1 | 10750 |
  39. | 2 | 13000 |
  40. | 3 | 12000 |
  41. +-----------+-------------+
  42. 3 rows in set (0.00 sec)</code>

having

  • having是对group by后的数据进行二次筛选
  • select 聚合函数 [as 列名] from 表名 group by 列名 having 条件;
  1. <code>mysql> select depart_id, avg(salary) from employee group by depart_id;
  2. +-----------+-------------+
  3. | depart_id | avg(salary) |
  4. +-----------+-------------+
  5. | 1 | 10750 |
  6. | 2 | 13000 |
  7. | 3 | 12000 |
  8. +-----------+-------------+
  9. 3 rows in set (0.00 sec)
  10. mysql> select depart_id, avg(salary) from employee group by depart_id having depart_id=1;
  11. +-----------+-------------+
  12. | depart_id | avg(salary) |
  13. +-----------+-------------+
  14. | 1 | 10750 |
  15. +-----------+-------------+
  16. 1 row in set (0.00 sec)</code>

order by

  • order by 列名 asc 升序
  • order by 列名 desc 降序
  1. <code>
  2. mysql> select * from employee order by age asc;
  3. +----+---------+--------+-----+--------+-----------+
  4. | id | name | gender | age | salary | depart_id |
  5. +----+---------+--------+-----+--------+-----------+
  6. | 10 | juliet | male | 21 | 6000 | 3 |
  7. | 3 | charlie | female | 22 | 7000 | 1 |
  8. | 5 | echo | male | 24 | 8000 | 2 |
  9. | 2 | bravo | male | 28 | 10000 | 1 |
  10. | 7 | golf | female | 29 | 11000 | 2 |
  11. | 8 | hotel | male | 31 | 13000 | 2 |
  12. | 1 | alpha | male | 35 | 12000 | 1 |
  13. | 4 | delta | female | 37 | 14000 | 1 |
  14. | 9 | india | male | 40 | 18000 | 3 |
  15. | 6 | foxtrot | female | 42 | 20000 | 2 |
  16. +----+---------+--------+-----+--------+-----------+
  17. 10 rows in set (0.00 sec)
  18. mysql> select * from employee order by salary desc;
  19. +----+---------+--------+-----+--------+-----------+
  20. | id | name | gender | age | salary | depart_id |
  21. +----+---------+--------+-----+--------+-----------+
  22. | 6 | foxtrot | female | 42 | 20000 | 2 |
  23. | 9 | india | male | 40 | 18000 | 3 |
  24. | 4 | delta | female | 37 | 14000 | 1 |
  25. | 8 | hotel | male | 31 | 13000 | 2 |
  26. | 1 | alpha | male | 35 | 12000 | 1 |
  27. | 7 | golf | female | 29 | 11000 | 2 |
  28. | 2 | bravo | male | 28 | 10000 | 1 |
  29. | 5 | echo | male | 24 | 8000 | 2 |
  30. | 3 | charlie | female | 22 | 7000 | 1 |
  31. | 10 | juliet | male | 21 | 6000 | 3 |
  32. +----+---------+--------+-----+--------+-----------+
  33. 10 rows in set (0.00 sec)</code>

limit

  • 限制查询记录的个数

  • limit offset, size
    • offset 表示起始行数, 第一行对应的offset是0
    • size 表示查询记录的个数
  1. <code>
  2. mysql> select * from employee;
  3. +----+---------+--------+-----+--------+-----------+
  4. | id | name | gender | age | salary | depart_id |
  5. +----+---------+--------+-----+--------+-----------+
  6. | 1 | alpha | male | 35 | 12000 | 1 |
  7. | 2 | bravo | male | 28 | 10000 | 1 |
  8. | 3 | charlie | female | 22 | 7000 | 1 |
  9. | 4 | delta | female | 37 | 14000 | 1 |
  10. | 5 | echo | male | 24 | 8000 | 2 |
  11. | 6 | foxtrot | female | 42 | 20000 | 2 |
  12. | 7 | golf | female | 29 | 11000 | 2 |
  13. | 8 | hotel | male | 31 | 13000 | 2 |
  14. | 9 | india | male | 40 | 18000 | 3 |
  15. | 10 | juliet | male | 21 | 6000 | 3 |
  16. +----+---------+--------+-----+--------+-----------+
  17. 10 rows in set (0.00 sec)
  18. mysql> select * from employee limit 2, 3;
  19. +----+---------+--------+-----+--------+-----------+
  20. | id | name | gender | age | salary | depart_id |
  21. +----+---------+--------+-----+--------+-----------+
  22. | 3 | charlie | female | 22 | 7000 | 1 |
  23. | 4 | delta | female | 37 | 14000 | 1 |
  24. | 5 | echo | male | 24 | 8000 | 2 |
  25. +----+---------+--------+-----+--------+-----------+
  26. 3 rows in set (0.00 sec)</code>

使用顺序

  • where > group by > having > order by > limit

多表操作

外键

  • 外键是表中的一个字段, 这个字段与另外一个表中的字段相匹配
  • 作用
    • 减少占用的空间
    • 方便后期修改

一对多

  • constraint 外键名 foreign key (被约束的字段) references 表名(字段名)
  1. <code>mysql> select * from employee;
  2. +----+---------+--------+-----+--------+-----------+
  3. | id | name | gender | age | salary | depart_id |
  4. +----+---------+--------+-----+--------+-----------+
  5. | 1 | alpha | male | 35 | 12000 | 1 |
  6. | 2 | bravo | male | 28 | 10000 | 1 |
  7. | 3 | charlie | female | 22 | 7000 | 1 |
  8. | 4 | delta | female | 37 | 14000 | 1 |
  9. | 5 | echo | male | 24 | 8000 | 2 |
  10. | 6 | foxtrot | female | 42 | 20000 | 2 |
  11. | 7 | golf | female | 29 | 11000 | 2 |
  12. | 8 | hotel | male | 31 | 13000 | 2 |
  13. | 9 | india | male | 40 | 18000 | 3 |
  14. | 10 | juliet | male | 21 | 6000 | 3 |
  15. +----+---------+--------+-----+--------+-----------+
  16. 10 rows in set (0.00 sec)
  17. mysql> select * from department;
  18. +----+------+
  19. | id | dept |
  20. +----+------+
  21. | 1 | RD |
  22. | 2 | IT |
  23. | 3 | HR |
  24. +----+------+
  25. 3 rows in set (0.00 sec)
  26. # 添加外键
  27. mysql> alter table employee add constraint fk_employee_dept foreign key (depart_id) references department(id);
  28. Query OK, 10 rows affected (0.04 sec)
  29. Records: 10 Duplicates: 0 Warnings: 0
  30. # 查看两表描述, depaet_id 和 id 的 key列变成了MUL
  31. mysql> desc employee;
  32. +-----------+-----------------------+------+-----+---------+----------------+
  33. | Field | Type | Null | Key | Default | Extra |
  34. +-----------+-----------------------+------+-----+---------+----------------+
  35. | id | int(10) unsigned | NO | PRI | NULL | auto_increment |
  36. | name | char(10) | NO | | | |
  37. | gender | enum('female','male') | NO | | male | |
  38. | age | int(11) | NO | | 0 | |
  39. | salary | float | NO | | 0 | |
  40. | depart_id | int(10) unsigned | NO | MUL | 1 | |
  41. +-----------+-----------------------+------+-----+---------+----------------+
  42. 6 rows in set (0.00 sec)
  43. mysql> desc department;
  44. +-------+------------------+------+-----+---------+----------------+
  45. | Field | Type | Null | Key | Default | Extra |
  46. +-------+------------------+------+-----+---------+----------------+
  47. | id | int(10) unsigned | NO | PRI | NULL | auto_increment |
  48. | dept | char(10) | NO | | | |
  49. +-------+------------------+------+-----+---------+----------------+
  50. 2 rows in set (0.00 sec)</code>

多对多

  1. <code>
  2. mysql> select * from boy;
  3. +----+----------+
  4. | id | boy_name |
  5. +----+----------+
  6. | 1 | Jack Ma |
  7. | 2 | Pony Ma |
  8. | 3 | Robin Li |
  9. +----+----------+
  10. 3 rows in set (0.00 sec)
  11. mysql> select * from girl;
  12. +----+------------+
  13. | id | girl_name |
  14. +----+------------+
  15. | 1 | Mingzhu Do |
  16. | 2 | Jean Liu |
  17. | 3 | Cathy Meng |
  18. +----+------------+
  19. 3 rows in set (0.00 sec)
  20. mysql> create table boy2girl (
  21. ->id int unsigned auto_increment primary key,
  22. ->boy_id int(10) unsigned not null default 1,
  23. ->girl_id int(10) unsigned not null default 1,
  24. ->constraint fk_boy2girl_boy foreign key (boy_id) references boy(id),
  25. ->constraint fk_boy2girl_girl foreign key (girl_id) references girl(id)
  26. ->);
  27. Query OK, 0 rows affected (0.02 sec)
  28. mysql> desc boy2girl;
  29. +---------+------------------+------+-----+---------+----------------+
  30. | Field | Type | Null | Key | Default | Extra |
  31. +---------+------------------+------+-----+---------+----------------+
  32. | id | int(10) unsigned | NO | PRI | NULL | auto_increment |
  33. | boy_id | int(10) unsigned | NO | MUL | 1 | |
  34. | girl_id | int(10) unsigned | NO | MUL | 1 | |
  35. +---------+------------------+------+-----+---------+----------------+
  36. 3 rows in set (0.00 sec)
  37. mysql> insert into boy2girl (boy_id, girl_id) values (1,1),(1,2),(1, 3),(2, 2), (2,3),(3,3);
  38. Query OK, 6 rows affected (0.00 sec)
  39. Records: 6 Duplicates: 0 Warnings: 0
  40. # 分别对两个字段进行了限制
  41. mysql> select * from boy2girl;
  42. +----+--------+---------+
  43. | id | boy_id | girl_id |
  44. +----+--------+---------+
  45. | 1 | 1 | 1 |
  46. | 2 | 1 | 2 |
  47. | 3 | 1 | 3 |
  48. | 4 | 2 | 2 |
  49. | 5 | 2 | 3 |
  50. | 6 | 3 | 3 |
  51. +----+--------+---------+
  52. 6 rows in set (0.00 sec)</code>

一对一

  1. <code>mysql> select * from employee1;
  2. +----+---------+
  3. | id | name |
  4. +----+---------+
  5. | 1 | alpha |
  6. | 2 | bravo |
  7. | 3 | charlie |
  8. +----+---------+
  9. 3 rows in set (0.00 sec)
  10. mysql> create table salary (
  11. -> id int unsigned auto_increment primary key,
  12. -> salary float not null default 0,
  13. -> employee_id int(10) unsigned not null default 1,
  14. -> constraint fk_employee_salary foreign key (employee_id) references employee1(id)
  15. -> );
  16. Query OK, 0 rows affected (0.02 sec)
  17. mysql> select * from salary;
  18. +----+--------+-------------+
  19. | id | salary | employee_id |
  20. +----+--------+-------------+
  21. | 1 | 10000 | 1 |
  22. | 2 | 12000 | 2 |
  23. | 3 | 14000 | 3 |
  24. +----+--------+-------------+
  25. 3 rows in set (0.00 sec)</code>

多表联查

  • left join on 左外连接, 优先显示左边表的全部记录
  • right join on 右外连接, 优先显示左边表的全部记录
  • inner join on 全外连接, 显示左右两表的全部记录
  1. <code>
  2. mysql> select * from boy2girl left join boy on boy_id = boy.id left join girl on girl_id = girl.id;
  3. +----+--------+---------+------+----------+------+------------+
  4. | id | boy_id | girl_id | id | boy_name | id | girl_name |
  5. +----+--------+---------+------+----------+------+------------+
  6. | 1 | 1 | 1 | 1 | Jack Ma | 1 | Mingzhu Do |
  7. | 2 | 1 | 2 | 1 | Jack Ma | 2 | Jean Liu |
  8. | 3 | 1 | 3 | 1 | Jack Ma | 3 | Cathy Meng |
  9. | 4 | 2 | 2 | 2 | Pony Ma | 2 | Jean Liu |
  10. | 5 | 2 | 3 | 2 | Pony Ma | 3 | Cathy Meng |
  11. | 6 | 3 | 3 | 3 | Robin Li | 3 | Cathy Meng |
  12. +----+--------+---------+------+----------+------+------------+
  13. 6 rows in set (0.00 sec)
  14. mysql> select boy_name,girl_name from boy2girl left join boy on boy_id = boy.id left join girl on girl_id = girl.id;
  15. +----------+------------+
  16. | boy_name | girl_name |
  17. +----------+------------+
  18. | Jack Ma | Mingzhu Do |
  19. | Jack Ma | Jean Liu |
  20. | Jack Ma | Cathy Meng |
  21. | Pony Ma | Jean Liu |
  22. | Pony Ma | Cathy Meng |
  23. | Robin Li | Cathy Meng |
  24. +----------+------------+
  25. 6 rows in set (0.00 sec)
  26. </code>

MySQL 03

标签:record   desc   jea   table   int   warnings   字段   des   连接   

人气教程排行