在本小节中将在student表和score表上进行查询。student表和score表的定义和记录如下。

表10.3  student表的定义

表10.4   score表的定义

表10.5   student表的记录

表10.6   score表的记录

执行的操作如下：

(1)在查询之前，先按照表10.3和表10.4的内容创建student表和score表。

(2)按照表10.5和表10.6的内容为student表和score表增加记录。

(3)查询student时表的所有记录。

(4)查询student表的第2条到第4条记录。

(5)从student表查询所有学生的学号（id)、姓名（name)和院系（department)的信息。

(6)从student表中查询计算机系和英语系的学生的信息。

(7)从student也表中查询年龄为18〜22岁的学生的信息。

(8)从student时表中查询每个院系有多少人。

(9)从score表中查询每个科目的最高分。

(10)查询李四的考试科目（c_name)和考试成绩（grade)。

(11)用连接查询的方式查询所有学生的信息和考试信息。

(12)计算每个学生的总成绩。

(13)计算每个考试科目的平均成绩。

(14)查询计算机成绩低于95的学生的信息。

(15)查询同时参加计算机和英语考试的学生的信息。

(16)将计算机考试成绩按从高到低进行排序。

(17)从student表和score表中查询出学生的学号，然后合并査询结果。

(18)查询姓张或者姓王的同学的姓名、院系和考试科目及成绩。

(19)查询都是湖南的同学的姓名、年龄、院系和考试科目及成绩。

本实例的执行过程

(1)按照表10.3的内容创建student表。SQL代码如下：

CREATE TABLE student (id INT(10) NOT NULL UNIQUE PRIMARY KEY ,name VARCHAR(20) NOT NULL , sex VARCHAR(4), birth YEAR,department VARCHAR(20), address VARCHAR(50))；

然后，按照表10.4的内容创建score代表。SQL代码如下：

CREATE TABLE score(kl INT(10) NOTNULL UNIQUE PRIMARYKEY AUTOJNCREMENT, stu_id INT(10) NOT NULL , c_name VARCHAR(20),grade INT(10);

(2)为进行查询，需要向student表和score表中插入一些数据。插入数据用INSERT语句。

INSERT INTO student VALUES( 901,'张老大','男',1985,'计算机系', '北京市海淀区');
INSERT INTO student VALUES( 902,'张老二','男',1986,'中文系','北京市昌平区');
INSERT INTO student VALUES( 903,'张三','女',1990,'中文系','湖南省永州市');
INSERT INTO student VALUES( 904,'李四',’男',1990,'英语系',辽宁省阜新市’);
INSERT INTO studentVALUES( 905,'王五','女',1991,'英语系','福建省厦门市’);
INSERT INTO studentVALUES( 906,'王六','男',1988,'计算机系','湖南省衡阳市');

其中，各个字段之间用逗号隔开。字符串需要加上单引号。然后按照表10.6中的内容向score表中插入数据。INSERT语句的代码如下：

INSERT INTO score VALUES(NULL,901, '计算机',98);
INSERT INTO score VALUES(NULL,901, '英语',80);
INSERT INTO score VALUES(NULL,902, '计算机',65);
INSERT INTO score VALUES(NULL,902,'中文',88);
INSERT INTO score VALUES(NULL,903,'中文',95);
INSERT INTO score VALUES(NULL,904, '计算机',70);
INSERT INTO score VALUES(NULL,904,'英语',92);
INSERT INTO score VALUES(NULL,905,'英语',94);
INSERT INTO score VALUES(NULL,906,'计算机'90);
INSERT INTO score VALUES(NULL,906,'英语',85);

因为score表的id字段是自动增加的。将其值赋值为NULL后，ID值会自动从1开始 增加。

(3)查询student表的所有记录。可以通过两种方式进行查询。第一种方法是用"*"来表示所有字段。SQL代码如下：

SELECT * FROM student;

mysql>SELECT *FROM student;
+-----+------+-----+-------+------------+----------+
|  id | name | sex | birth | department |  address  |
| 901 | 张老大|  男 | 1985  | 计算机系   | 北京市海淀区 |
| 902 | 张老二|  男 | 1986  | 中文系     | 北京市昌平区 |
| 903 | 张三  |  女 | 1990  | 中文系     | 湖南省永州市 |
| 904 | 李四  |  男 | 1990  | 英语系     | 辽宁省阜新市 |
| 905 | 王五  |  女 | 1991  | 英语系     | 福建省厦门市 |
| 906 | 王六  |  男 | 1988  | 计算机系   | 湖南省衡阳市 |
+-----+------+-----+-------+------------+----------+
6 rows in set(0.00 sec)

结果显示了student表的所有记录。第二种方法是在SELECT语句中列出STUDENT表的所有字段。SQL代码如下：

SELECT id, name, sex, birth, department, address FROM student;

代码执行结果如下：

mysql>SELECT *FROM student;
+-----+------+-----+-------+------------+----------+
|  id | name | sex | birth | department |  address  |
| 901 | 张老大|  男 | 1985  | 计算机系   | 北京市海淀区 |
| 902 | 张老二|  男 | 1986  | 中文系     | 北京市昌平区 |
| 903 | 张三  |  女 | 1990  | 中文系     | 湖南省永州市 |
| 904 | 李四  |  男 | 1990  | 英语系     | 辽宁省阜新市 |
| 905 | 王五  |  女 | 1991  | 英语系     | 福建省厦门市 |
| 906 | 王六  |  男 | 1988  | 计算机系   | 湖南省衡阳市 |
+-----+------+-----+-------+------------+----------+
6 rows in set(0.00 sec)

查询结果与第一种方法的结果是一样的。

(4)查询student表的第2~4条记录，可以通过LIMIT关键字来实现。SQL代码如下.

SELECT * FROM student LIMIT 1,3;

mysql>SELECT *FROM student;
+-----+------+-----+-------+------------+----------+
|  id | name | sex | birth | department |  address  |
| 902 | 张老二|  男 | 1986  | 中文系     | 北京市昌平区 |
| 903 | 张三  |  女 | 1990  | 中文系     | 湖南省永州市 |
| 904 | 李四  |  男 | 1990  | 英语系     | 辽宁省阜新市 |
+-----+------+-----+-------+------------+----------+
3 rows in set(0.00 sec)

结果显示，查询出student表的第2~4条记录。

(5)要从student表查询所有学生的学号、姓名和院系信息，就必须在SELECT中指定id,name,department字段。SQL代码如下：

SELECT id,name,department FROM student;

代码执行结果如下：

mysql>SELECT id ,name,department FROM student;
+-----+------+-----+------+
|  id | name | department |
| 901 | 张老大| 计算机系  |
| 902 | 张老二| 中文系    |
| 903 | 张三  | 中文系    |
| 904 | 李四  | 英语系    |
| 905 | 王五  |  英语系   |
| 906 | 王六  |  计算机系 |
+-----+------+-----+------+
6 rows in set(0.00 sec)

(6)查询计算机系和英语系的学生的信息有两种方法。第一种是使用IN关键字。SQL代码如下：

SELECT * FROM student WHERE department IN('计算机系','英语系');

代码执行结果如下：

mysql>SELECT *FROM student;
+-----+------+-----+-------+------------+----------+
|  id | name | sex | birth | department |  address  |
| 901 | 张老大|  男 | 1985  | 计算机系   | 北京市海淀区 |
| 904 | 李四  |  男 | 1990  | 英语系     | 辽宁省阜新市 |
| 905 | 王五  |  女 | 1991  | 英语系     | 福建省厦门市 |
| 906 | 王六  |  男 | 1988  | 计算机系   | 湖南省衡阳市 |
+-----+------+-----+-------+------------+----------+
4 rows in set(0.00 sec)

第二种方法是使用OR关键字。SQL代码如下：

SELECT * FROM student WHERE department='计算机系', OR department='英语系';

代码执行结果如下：

mysql>SELECT *FROM student;
+-----+------+-----+-------+------------+----------+
|  id | name | sex | birth | department |  address  |
| 901 | 张老大|  男 | 1985  | 计算机系   | 北京市海淀区 |
| 904 | 李四  |  男 | 1990  | 英语系     | 辽宁省阜新市 |
| 905 | 王五  |  女 | 1991  | 英语系     | 福建省厦门市 |
| 906 | 王六  |  男 | 1988  | 计算机系   | 湖南省衡阳市 |
+-----+------+-----+-------+------------+----------+
4 rows in set(0.00 sec)

从查询结果可以看出，这两种方法可以达到同样的目的。使用OR关键字时，只要满足OR前后的任意一个条件即可。

(7)从student表中查询年龄为18~22岁的学生的信息。首先必须知道学生的年龄。 因为student表中只有出生年份的字段，所以必须用表达式获取学生的年龄。计算年龄的表达式为“当前年份-出生年份”。这里可以用“2009-birth”来计算年龄。而且可以通过AS关键字将2009-birth取名为age。例如，从student表中查询所有学生的姓名和年龄，代码如下：

mysql>SELECT name,2009-birth AS age FROM student;
+------+-------+
| name |  age  |
| 张老大|  24  |
| 张老二|  23  |
| 张三  |  19  |
| 李四  |  19  |
| 王五  |  18  |
| 王六  |  21  |
+-----+-------+
6 rows in set(0.00 sec)

从结果可以看出，通过“2009-birth’表达式可以算出每个学生的年龄。而且结果中显示的名称是age现在，要查询出年龄为18~22岁的学生。可以通过两种方式来查询。第 一种方法是使用BETWEEN AND关键字来查询，其SQL代码如下：

SELECT id, name, sex, 2009-birth AS age, department, address FROM student WHERE 2009-birth BETWEEN 18 AND 22;

代码执行结果如下：

mysql>SELECT id, name, sex, 2009-birth AS age, department, address FROM student WHERE 2009-birth BETWEEN 18 AND 22;
+-----+------+-----+-------+------------+----------+
|  id | name | sex | age | department |  address  |
| 903 | 张三  |  女 | 19  | 中文系     | 湖南省永州市 |
| 904 | 李四  |  男 | 19  | 英语系     | 辽宁省阜新市 |
| 905 | 王五  |  女 | 18  | 英语系     | 福建省厦门市 |
| 906 | 王六  |  男 | 21  | 计算机系   | 湖南省衡阳市 |
+-----+------+-----+-------+------------+----------+
4 rows in set(0.00 sec)

结果显示,成功的查询出了年龄从18〜22岁的所有学生的信息。因为BETWEEN AND关键字设置了年龄的查询范围。第二种方式使用AND关键字和比较运算符。SQL代码如下：

SELECT id, name, sex, 2009-birth AS age, department, address FROM student WHERE 2009-birth>=18 AND 2009-birth<=22;

代码执行结果如下：

mysql>SELECT id, name, sex, 2009-birth AS age, department, address FROM student WHERE 2009-birth>=18 AND 2009-birth<=22;
+-----+------+-----+-------+------------+----------+
|  id | name | sex | age | department |  address  |
+-----+------+-----+-------+------------+----------+
| 903 | 张三  |  女 | 19  | 中文系     | 湖南省永州市 |
| 904 | 李四  |  男 | 19  | 英语系     | 辽宁省阜新市 |
| 905 | 王五  |  女 | 18  | 英语系     | 福建省厦门市 |
| 906 | 王六  |  男 | 21  | 计算机系   | 湖南省衡阳市 |
+-----+------+-----+-------+------------+----------+
4 rows in set(0.00 sec)

这种方法的查询结果与第一种方法是一样的。

(8)student表中查询每个院系有多少人。先必须按院系进行分组。然后，再计算每组的人数。SQL代码如下：

SELECT department, COUNT(id) FROM student GROUP BY department;

代码中使用GROUP BY关键字进行分组，然后使用COUNT()函数计算每组有多少记录。代码执行结果如下：

mysql>SELECT department, COUNT(id) FROM student GROUP BY department;
+----------+----------+
|department| COUNT(id)|
+----------+----------+
| 英文系   |     2    |
| 中文系   |     2    |
| 计算机系 |     2    |
+----------+----------+
3 row in set (0.03 sec)

(9)从score表中查询每个科目的最高分。首先按照c_name字段对score表中的记录进行分组。然后使用MAX()函数计算每组的最大值。SQL代码如下：

SELECT c_name, MAX(grade) FROM score GROUP BY c__name;

代码中使用GROUP BY关键字进行分组，然后使用MAX()函数计算每组的最大值。 代码执行结果如下：

mysql>SELECT c_name, MAX(grade) FROM score GROUP BY c__name;
+----------+----------+
|c_name  | COUNT(id)|
+--------+----------+
| 英文   |     94    |
| 中文   |     95    |
| 计算机 |     98    |
+--------+----------+
3 row in set (0.03 sec)

结果显示，英语的最高分是94；中文的最高分是95；计算机的最高分是98。

(10)查询李四的考试科目(c_name)和考试成绩(grade)。科目和成绩都存储在score表中，但是score表中只有学生的学号，没有学生的姓名。所以必须根据学生姓名从student表中取出学生的学号。然后再从score表中查询该学生的考试科目和成绩。SQL代码如下：

SELECT c_name, grade FROM score WHERE stu_id=(SELECT id FROM student WHERE name='李四');

代码执行结果如下：

mysql>SELECT c_name, grade FROM score WHERE stu_id=(SELECT id FROM student WHERE name='李四');
+----------+----------+
|c_name  | COUNT(id)|
+--------+----------+
| 计算机 |     70   |
| 英语   |     92   |
+--------+----------+
2 row in set (0.03 sec)

査询结果显示，李四参加了计算机和英语的考试。其成绩分别为70和92。

(11)用连接查询的方式查询所有学生的信息和考试信息。因为student表的id字段和score表的stu_id字段都是表示学号。通过这两个字段可以连接这两个表。SQL代码如下：

SELECT student.id, name, sex, birth, department, address, c_name, grade FROM student, score WHERE student.id=score.stu_id;

其中，student.id表示student表中的id字段。代码执行结果如下：

mysql>SELECT student.id, name, sex, birth, department, address, c_name, grade FROM student, score WHERE student.id=score.stu_id;
+-----+------+-----+-------+------------+----------+-----------+---------+
|  id | name | sex | birth | department |  address  |  c_name  | grade   |
| 901 | 张老大|  男 | 1985  | 计算机系   | 北京市海淀区 |计算机|   98    |
| 901 | 张老大|  男 | 1985  | 计算机系   | 北京市海淀区 |英语  |   98    |
| 902 | 张老二|  男 | 1986  | 中文系     | 北京市昌平区 |计算机|    65   |
| 902 | 张老二|  男 | 1986  | 中文系     | 北京市昌平区 |中文  |    88   |
| 903 | 张三  |  女 | 1990  | 中文系     | 湖南省永州市 |中文  |    95   |
| 904 | 李四  |  男 | 1990  | 英语系     | 辽宁省阜新市 |计算机|    70   |
| 904 | 李四  |  男 | 1990  | 英语系     | 辽宁省阜新市 |英语  |    92   |
| 905 | 王五  |  女 | 1991  | 英语系     | 福建省厦门市 |英语  |    94   |
| 906 | 王六  |  男 | 1988  | 计算机系   | 湖南省衡阳市 |计算机|    90   |
| 906 | 王六  |  男 | 1988  | 计算机系   | 湖南省衡阳市 |英语  |    85   |
+-----+------+-----+-------+------------+----------+-----------+---------+
10 rows in set(0.00 sec)

通过连接查询，从student表和score比表中查询出了所有同学的资料和考试信息。但是需要注意的是，student表和score表中都有id字段，而且两者的意义是不一样的。因此，必须要说明字段是属于哪个表的，如score.id表示score表的id字段。

(12)计算每个学生的总成绩。所有学生的成绩都存储在score表中。要计算每个同学的总成绩，必须按学号进行分组。然后用SUM()函数来计算总成绩。SQL代码如下：

SELECT stu_id, SUM(grade) FROM score GROUP BY stu_id;

代码执行结果如下：

mysql>SELECT stu_id, SUM(grade) FROM score GROUP BY stu_id;
+----------+--------------+
|  stu_id  |  SUN(grade)  |
+----------+--------------+
|    901   |     178      |
|    902   |     153      |
|    903   |     95       |
|    904   |     162      |
|    905   |     94       |
|    906   |     175      |
6 rows in set(0.00 sec)

(13)计算每个考试科目的平均成绩。先必须将score表按照科目(c_name)进行分组。然后，再使用AVG()函数计算每组的平均值。这样就可以计算出每科的平均成绩。SQL代码如下：

SELECT c_name, AVG(grade) FROM score GROUP BY c_name;

执行结果如下：

mysql>SELECT c_name, AVG(grade) FROM score GROUP BY c_name;
+-------+--------+
|  英语 | 87.7500|
|  中文 | 91.5000|
| 计算机| 80.7500|
+-------+--------+
3 row in set(0.00 sec)

结果显示了科目和每个科目的平均成绩。

(14)查询计算机成绩低于95的学生的信息。科目和成绩都存储在score表中因此, 先必须从student表中查询出参加了计算机考试，而且成绩低于95的学生的学号。然后根据学号到student表中来查询该学生的信息。这需要使用比较运算符。而且需要在student表和score表两个表之间进行查询。SQL代码如下：

SELECT * FROM student WHERE id IN(SELECT stu_id FROM score WHERE c_name= "计算机" AND grade<95);

(15)查询同时参加计算机和英语考试的学生的信息。先必须从score表中查询谁同时参加了计算机和英语这两门考试。取出该同学的学号，再去student表中查询其信息。SQL 代码如下：

SELECT * FROM student WHERE id =ANY (SELECT stu_id FROM score WHERE stu_id IN (SELECT stu_id FROM score WHERE c_name='计算机'）AND c_name=，英语'）；

上面的例子中使用了，层嵌套的查询。先从score表中查询参加计算机考试的学号， 然后从这些学号中选择参加英语考试的学号，最后，到student表中查询对应学生的信息。 代码执行结果如下：

+-----+------+-----+-------+------------+----------+
|  id | name | sex | birth | department |  address  |
| 901 | 张老大|  男 | 1985  | 计算机系    | 北京市海淀区 |
| 904 | 李四  |  男 | 1990  | 英语系     | 辽宁省阜新市 |
| 906 | 王六  |  男 | 1988  | 计算机系   | 湖南省衡阳市 |
+-----+------+-----+-------+------------+----------+
3 rows in set(0.00 sec)

查询出3条记录。这3个人同时参加了计算机和英语考试。

(16)将计算机成绩按从高到低进行排序。先从score表中查询出所有计算机考试的成绩，然后使用ORDER BY来排序。加上DESC是按照从高到低来排序。SQL代码如下

SELECT stu_id, grade FROM score WHERE c_name= '计算机' ORDER BY grade DESC;

代码执行结果如下:

mysql>SELECT stu_id, grade FROM score WHERE c_name= '计算机' ORDER BY grade DESC;
+--------+---------+
| stu_id | grade   |
+--------+---------+
|    901 |    98   |
|    906 |    90   |
|    904 |    70   |
|    902 |    65   |
+--------+---------+
4 row in set(0.00 sec)

(17)从student表和score表中查询出学生的学号，然后合并查询结果。先从student表中查询出所有学生的学号，然后从score表中查询所有的学号，最后通过UNION来合并查询结果。SQL代码如下：

SELECT id FROM student UNION SELECT stu_id FROM score;

代码执行结果如下：

mysql>SELECT id FROM student UNION SELECT stu_id FROM score;
+----+
| id |
+----+
|901 |
|902 |
|903 |
|904 |
|905 |
|906 |
+----+
6 row in set(0.00 sec)

结果中将两个查询语句的结果合并起来。而且，使用UNION时，自动去除相同的记录。

(18)查询姓张或者姓王的同学的姓名、院系、考试科目和成绩。学生的姓名存储在student表中。先要从score时表中匹配出姓张和姓王的同学的学号。匹配名字是使用LIKE关键字。而且要使用通配符%。然后通过学号从score表中查询考试科目和成绩。

SELECT student.id, name,sex,birth,department, address, c_name,grade
FROM student, score WHERE(name LIKE '张%' OR name LIKE '王$') AND student.id=score.stu_id ;

代码执行结果如下：

mysql>SELECT student.id, name,sex,birth,department, address, c_name,grade
FROM student, score WHERE(name LIKE '张%' OR name LIKE '王$') AND student.id=score.stu_id ;
+-----+------+-----+-------+------------+----------+-----------+---------+
|  id | name | sex | birth | department |  address  |  c_name  | grade   |
| 901 | 张老大|  男 | 1985  | 计算机系   | 北京市海淀区 |计算机|   98    |
| 901 | 张老大|  男 | 1985  | 计算机系   | 北京市海淀区 |英语  |   98    |
| 902 | 张老二|  男 | 1986  | 中文系     | 北京市昌平区 |计算机|    65   |
| 902 | 张老二|  男 | 1986  | 中文系     | 北京市昌平区 |中文  |    88   |
| 903 | 张三  |  女 | 1990  | 中文系     | 湖南省永州市 |中文  |    95   |
| 905 | 王五  |  女 | 1991  | 英语系     | 福建省厦门市 |英语  |    94   |
| 906 | 王六  |  男 | 1988  | 计算机系   | 湖南省衡阳市 |计算机|    90   |
| 906 | 王六  |  男 | 1988  | 计算机系   | 湖南省衡阳市 |英语  |    85   |
+-----+------+-----+-------+------------+----------+-----------+---------+
8 rows in set(0.00 sec)

(19)查询都是湖南的同学的姓名、年龄、院系、考试科目和成绩。先从student表匹配家庭住址是湖南的同学的学号。然后到score表中查询考试科目和成绩。SQL代码如下：

SELECT student.id, name,sex,birth,department, address, c_name,grade FROM student, score WHERE address LIKE '湖南％' AND student.id=score.stu_id ;

代码执行结果如下：

mysql>SELECT student.id, name,sex,birth,department, address, c_name,grade FROM student, score WHERE address LIKE '湖南％' AND student.id=score.stu_id ;
+-----+------+-----+-------+------------+----------+-----------+---------+
|  id | name | sex | birth | department |  address  |  c_name  | grade   |
| 903 | 张三  |  女 | 1990  | 中文系     | 湖南省永州市 |中文  |    95   |
| 906 | 王六  |  男 | 1988  | 计算机系   | 湖南省衡阳市 |计算机|    90   |
| 906 | 王六  |  男 | 1988  | 计算机系   | 湖南省衡阳市 |英语  |    85   |
+-----+------+-----+-------+------------+----------+-----------+---------+
3 row in set(0.00 sec)