本文共 4636 字，大约阅读时间需要 15 分钟。

mysql建表

mysql学生表

CREATE TABLE `courses` (`id` INT(11) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '自增id',`student` VARCHAR(255) DEFAULT NULL COMMENT '学生',`class` VARCHAR(255) DEFAULT NULL COMMENT '课程',`score` INT(255) DEFAULT NULL COMMENT '分数',PRIMARY KEY (`id`),UNIQUE KEY `course` (`student`, `class`)) ENGINE=InnoDB DEFAULT CHARSET=utf8;

插入数据

INSERT INTO `courses`(`student`, `class`, `score`) VALUES('A', 'Math', 90);INSERT INTO `courses`(`student`, `class`, `score`) VALUES('A', 'Chinese', 80);INSERT INTO `courses`(`student`, `class`, `score`) VALUES('A', 'English', 70);INSERT INTO `courses`(`student`, `class`, `score`) VALUES('A', 'History', 80);INSERT INTO `courses`(`student`, `class`, `score`) VALUES('B', 'Math', 73);INSERT INTO `courses`(`student`, `class`, `score`) VALUES('B', 'Chinese', 60);INSERT INTO `courses`(`student`, `class`, `score`) VALUES('B', 'English', 70);INSERT INTO `courses`(`student`, `class`, `score`) VALUES('B', 'History', 90);INSERT INTO `courses`(`student`, `class`, `score`) VALUES('C', 'Math', 70);INSERT INTO `courses`(`student`, `class`, `score`) VALUES('C', 'Chinese', 50);INSERT INTO `courses`(`student`, `class`, `score`) VALUES('C', 'English', 20);INSERT INTO `courses`(`student`, `class`, `score`) VALUES('C', 'History', 10);INSERT INTO `courses`(`student`, `class`, `score`) VALUES('D', 'Math', 53);INSERT INTO `courses`(`student`, `class`, `score`) VALUES('D', 'Chinese', 32);INSERT INTO `courses`(`student`, `class`, `score`) VALUES('D', 'English', 99);INSERT INTO `courses`(`student`, `class`, `score`) VALUES('D', 'History', 100);

建表，学生和id

CREATE TABLE `phone` (`id` INT(11) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '自增id',`student` VARCHAR(255) DEFAULT NULL COMMENT '学生',`phone` VARCHAR(255) DEFAULT NULL COMMENT '电话',PRIMARY KEY (`id`)) ENGINE=InnoDB DEFAULT CHARSET=utf8;

group by

mysql> select * from courses;+----+---------+---------+-------+| id | student | class   | score |+----+---------+---------+-------+|  1 | A       | Math    |    90 ||  2 | A       | Chinese |    80 ||  3 | A       | English |    70 ||  4 | A       | History |    80 ||  5 | B       | Math    |    73 ||  6 | B       | Chinese |    60 ||  7 | B       | English |    70 ||  8 | B       | History |    90 ||  9 | C       | Math    |    70 || 10 | C       | Chinese |    50 || 11 | C       | English |    20 || 12 | C       | History |    10 || 13 | D       | Math    |    53 || 14 | D       | Chinese |    32 || 15 | D       | English |    99 || 16 | D       | History |   100 |+----+---------+---------+-------+16 rows in set (0.00 sec)mysql> select class, min(score) from courses group by class;+---------+------------+| class   | min(score) |+---------+------------+| Math    |         53 || Chinese |         32 || English |         20 || History |         10 |+---------+------------+4 rows in set (0.00 sec)mysql> select class from courses group by class;+---------+| class   |+---------+| Chinese || English || History || Math    |+---------+4 rows in set (0.00 sec)

inner join

mysql> select *  from phone;+----+---------+-------------+| id | student | phone       |+----+---------+-------------+|  1 | A       | 123123123   ||  2 | B       | 12312312322 ||  3 | C       | 321321      |+----+---------+-------------+3 rows in set (0.00 sec)mysql> select *  from courses;+----+---------+---------+-------+| id | student | class   | score |+----+---------+---------+-------+|  1 | A       | Math    |    90 ||  2 | A       | Chinese |    80 ||  3 | A       | English |    70 ||  4 | A       | History |    80 ||  5 | B       | Math    |    73 ||  6 | B       | Chinese |    60 ||  7 | B       | English |    70 ||  8 | B       | History |    90 ||  9 | C       | Math    |    70 || 10 | C       | Chinese |    50 || 11 | C       | English |    20 |+----+---------+---------+-------+11 rows in set (0.00 sec)mysql> select a.phone, b.class from phone a inner join courses b on a.student= b.student;+-------------+---------+| phone       | class   |+-------------+---------+| 123123123   | Chinese || 123123123   | English || 123123123   | History || 123123123   | Math    || 12312312322 | Chinese || 12312312322 | English || 12312312322 | History || 12312312322 | Math    || 321321      | Chinese || 321321      | English || 321321      | Math    |+-------------+---------+11 rows in set (0.00 sec)mysql> select distinct a.phone, b.student from phone a inner join courses b on a.student= b.student;+-------------+---------+| phone       | student |+-------------+---------+| 123123123   | A       || 12312312322 | B       || 321321      | C       |+-------------+---------+3 rows in set (0.00 sec)

转载地址：http://nzorb.baihongyu.com/