目录
3.从student表查询所有学生的学号(id)、姓名(name)和院系(department)的信息
5.从student表中查询年龄18~22岁的学生信息 假如现在是2008
8.查询李四的考试科目(c_name)和考试成绩(grade)
15.从student表和score表中查询出学生的学号,然后合并查询结果
所需表
1.student表
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)
);
创建score表。SQL代码如下:
create table score(
id int(10) not null unique primary key auto_incrament,
stu_id int(10) not null,
c_name varchar(20),
grade int(10));
2.为student表和score表增加记录
向student表插入记录的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 student VALUES( 905,'王五', '女',1991,'英语系', '福建省厦门市');
INSERT INTO student VALUES( 906,'王六', '男',1988,'计算机系', '湖南省衡阳市');
向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);
mysql> select *from student;
题目
1.查询student表的所有记录
2.查询student表的第2条到4条记录
3.从student表查询所有学生的学号(id)、姓名(name)和院系(department)的信息
4.从student表中查询计算机系和英语系的学生的信息
5.从student表中查询年龄18~22岁的学生信息
6.从student表中查询每个院系有多少人
7.从score表中查询每个科目的最高分
8.查询李四的考试科目(c_name)和考试成绩(grade)
9.用连接的方式查询所有学生的信息和考试信息
10.计算每个学生的总成绩
11.计算每个考试科目的平均成绩
12.查询计算机成绩低于95的学生信息
13.查询同时参加计算机和英语考试的学生的信息
14.将计算机考试成绩按从高到低进行排序
15.从student表和score表中查询出学生的学号,然后合并查询结果
16.查询姓张或者姓王的同学的姓名、院系和考试科目及成绩
17.查询都是湖南的学生的姓名、年龄、院系和考试科目及成绩
答案
1.查询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)
2.查询student表的第2条到4条记录
SELECT * FROM student
LIMIT 1, 3;
mysql> SELECT * FROM student
-> LIMIT 1, 3;
+-----+-----------+------+-------+------------+--------------------+
| id | name | sex | birth | department | address |
+-----+-----------+------+-------+------------+--------------------+
| 902 | 张老二 | 男 | 1986 | 中文系 | 北京市昌平区 |
| 903 | 张三 | 女 | 1990 | 中文系 | 湖南省永州市 |
| 904 | 李四 | 男 | 1990 | 英语系 | 辽宁省阜新市 |
+-----+-----------+------+-------+------------+--------------------+
3 rows in set (0.00 sec)
3.从student表查询所有学生的学号(id)、姓名(name)和院系(department)的信息
mysql> select id,name,department from student;
+-----+-----------+--------------+
| id | name | department |
+-----+-----------+--------------+
| 901 | 张老大 | 计算机系 |
| 902 | 张老二 | 中文系 |
| 903 | 张三 | 中文系 |
| 904 | 李四 | 英语系 |
| 905 | 王五 | 英语系 |
| 906 | 王六 | 计算机系 |
+-----+-----------+--------------+
6 rows in set (0.00 sec)
mysql>
4.从student表中查询计算机系和英语系的学生的信息
select * from student where department='计算机系' or department='英语系';
mysql> select * from student where department='计算机系' or department='英语系';
+-----+-----------+------+-------+--------------+--------------------+
| id | name | sex | birth | department | address |
+-----+-----------+------+-------+--------------+--------------------+
| 901 | 张老大 | 男 | 1985 | 计算机系 | 北京市海淀区 |
| 904 | 李四 | 男 | 1990 | 英语系 | 辽宁省阜新市 |
| 905 | 王五 | 女 | 1991 | 英语系 | 福建省厦门市 |
| 906 | 王六 | 男 | 1988 | 计算机系 | 湖南省衡阳市 |
+-----+-----------+------+-
5.从student表中查询年龄18~22岁的学生信息 假如现在是2008
select *from student where birth >1984 and birth <1990;
mysql> select *from student where birth >1984 and birth <1990;
+-----+-----------+------+-------+--------------+--------------------+
| id | name | sex | birth | department | address |
+-----+-----------+------+-------+--------------+--------------------+
| 901 | 张老大 | 男 | 1985 | 计算机系 | 北京市海淀区 |
| 902 | 张老二 | 男 | 1986 | 中文系 | 北京市昌平区 |
| 906 | 王六 | 男 | 1988 | 计算机系 | 湖南省衡阳市 |
6.从student表中查询每个院系有多少人
select department ,count(*) as zong
from student
group by department;
mysql> select department ,count(*) as zong
-> from student
-> group by department;
+--------------+------+
| department | zong |
+--------------+------+
| 计算机系 | 2 |
| 中文系 | 2 |
| 英语系 | 2 |
+--------------+------+
3 rows in set (0.00 sec)
mysql>
使用GROUP BY关键字按照院系列进行分组,以便在每个院系中计数学生数量。
7.从score表中查询每个科目的最高分
select c_name,max(grade) as zuigao from score group by c_name;
mysql> select c_name,max(grade) as zuigao from score group by c_name;
+-----------+--------+
| c_name | zuigao |
+-----------+--------+
| 计算机 | 98 |
| 英语 | 94 |
| 中文 | 95 |
+-----------+--------+
3 rows in set (0.00 sec)
8.查询李四的考试科目(c_name)和考试成绩(grade)
select student.name,score.grade,score.c_name
from student
join score on student.id=score.stu_id
where name='李四';
mysql> select student.name,score.grade,score.c_name
-> from student
-> join score on student.id=score.stu_id
-> where name='李四';
+--------+-------+-----------+
| name | grade | c_name |
+--------+-------+-----------+
| 李四 | 70 | 计算机 |
| 李四 | 92 | 英语 |
+--------+-------+-----------+
2 rows in set (0.00 sec)
9.用连接的方式查询所有学生的信息和考试信息
select stu.id,stu.name,stu.sex,stu.birth,stu.department,stu.address,sc.c_name,sc.grade
from student as stu
join score as sc
on stu.id=sc.stu_id;
mysql> select stu.id,stu.name,stu.sex,stu.birth,stu.department,stu.address,sc.c_name,sc.grade
-> from student as stu join score as sc on stu.id=sc.stu_id;
+-----+-----------+------+-------+--------------+--------------------+-----------+-------+
| id | name | sex | birth | department | address | c_name | grade |
+-----+-----------+------+-------+--------------+--------------------+-----------+-------+
| 901 | 张老大 | 男 | 1985 | 计算机系 | 北京市海淀区 | 计算机 | 98 |
| 901 | 张老大 | 男 | 1985 | 计算机系 | 北京市海淀区 | 英语 | 80 |
| 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.计算每个学生的总成绩
select stu.name,sum(grade)as zong
from student as stu
join score as sc
on stu.id=sc.stu_id group by stu.name;
mysql> select stu.name,sum(grade)as zong
-> from student as stu
-> join score as sc
-> on stu.id=sc.stu_id group by stu.name;
+-----------+------+
| name | zong |
+-----------+------+
| 张老大 | 178 |
| 张老二 | 153 |
| 张三 | 95 |
| 李四 | 162 |
| 王五 | 94 |
| 王六 | 175 |
+-----------+------+
6 rows in set (0.00 sec)
最后,我们使用GROUP BY子句按照学生姓名(stu.name)进行分组,以便在每个学生上计算总成绩。
11.计算每个考试科目的平均成绩
select stu.name,avg(grade)as zong
from student as stu
join score as sc
on stu.id=sc.stu_id group by stu.name;
mysql> select stu.name,avg(grade)as zong
-> from student as stu
-> join score as sc
-> on stu.id=sc.stu_id group by stu.name;
+-----------+---------+
| name | zong |
+-----------+---------+
| 张老大 | 89.0000 |
| 张老二 | 76.5000 |
| 张三 | 95.0000 |
| 李四 | 81.0000 |
| 王五 | 94.0000 |
| 王六 | 87.5000 |
+-----------+---------+
6 rows in set (0.00 sec)
12.查询计算机成绩低于95的学生信息
select stu.id,stu.name,stu.sex,stu.birth,stu.department,stu.address,sc.c_name,sc.grade
from student as stu
join score as sc on stu.id=sc.stu_id
where sc.grade < 95 and sc.c_name='计算机';
mysql> select stu.id,stu.name,stu.sex,stu.birth,stu.department,stu.address,sc.c_name,sc.grade
-> from student as stu
-> join score as sc on stu.id=sc.stu_id
-> where sc.grade < 95 and sc.c_name='计算机';
+-----+-----------+------+-------+--------------+--------------------+-----------+-------+
| id | name | sex | birth | department | address | c_name | grade |
+-----+-----------+------+-------+--------------+--------------------+-----------+-------+
| 902 | 张老二 | 男 | 1986 | 中文系 | 北京市昌平区 | 计算机 | 65 |
| 904 | 李四 | 男 | 1990 | 英语系 | 辽宁省阜新市 | 计算机 | 70 |
| 906 | 王六 | 男 | 1988 | 计算机系 | 湖南省衡阳市 | 计算机 | 90 |
+-----+-----------+------+-------+--------------+--------------------+-----------+-------+
3 rows in set (0.00 sec)
mysql>
13.查询同时参加计算机和英语考试的学生的信息
SELECT stu.id, stu.name, stu.sex, stu.birth, stu.department, stu.address
FROM student AS stu
JOIN score AS sc1 ON stu.id = sc1.stu_id
JOIN score AS sc2 ON stu.id = sc2.stu_id
WHERE sc1.c_name = '计算机' AND sc2.c_name = '英语';
mysql> SELECT stu.id, stu.name, stu.sex, stu.birth, stu.department, stu.address
-> FROM student AS stu
-> JOIN score AS sc1 ON stu.id = sc1.stu_id
-> JOIN score AS sc2 ON stu.id = sc2.stu_id
-> WHERE sc1.c_name = '计算机' AND sc2.c_name = '英语';
+-----+-----------+------+-------+--------------+--------------------+
| id | name | sex | birth | department | address |
+-----+-----------+------+-------+--------------+--------------------+
| 901 | 张老大 | 男 | 1985 | 计算机系 | 北京市海淀区 |
| 904 | 李四 | 男 | 1990 | 英语系 | 辽宁省阜新市 |
| 906 | 王六 | 男 | 1988 | 计算机系 | 湖南省衡阳市 |
+-----+-----------+------+-------+--------------+--------------------+
3 rows in set (0.00 sec)
mysql> 这个查询中,我们将学生表格(student)重命名为stu,成绩表格(score)
重命名为sc1和sc2。然后我们使用两个JOIN语句将学生表格与成绩表格连接两次,
分别连接计算机成绩和英语成绩,连接条件是学生ID(stu.id)
等于成绩表格中的学生ID(sc1.stu_id和sc2.stu_id)。
在WHERE子句中,我们筛选出参加计算机考试的成绩记录(sc1.c_name = ‘计算机’)和
参加英语考试的成绩记录(sc2.c_name = ‘英语’),
这样就能筛选出同时参加计算机和英语考试的学生。
方法二:
mysql> SELECT stu.id, stu.name, stu.sex, stu.birth, stu.department, stu.address
-> FROM student AS stu
-> WHERE stu.id IN (
-> SELECT stu_id
-> FROM score
-> WHERE c_name IN ('计算机', '英语')
-> GROUP BY stu_id
-> HAVING COUNT(DISTINCT c_name) = 2
-> );
+-----+-----------+------+-------+--------------+--------------------+
| id | name | sex | birth | department | address |
+-----+-----------+------+-------+--------------+--------------------+
| 901 | 张老大 | 男 | 1985 | 计算机系 | 北京市海淀区 |
| 904 | 李四 | 男 | 1990 | 英语系 | 辽宁省阜新市 |
| 906 | 王六 | 男 | 1988 | 计算机系 | 湖南省衡阳市 |
+-----+-----------+------+-------+--------------+--------------------+
使用了学生表格(student)并将其重命名为stu。然后我们使用子查询来筛选同时参加计算机和英语考试的学生。
子查询中,我们首先选择成绩表格(score)中的成绩。使用WHERE子句,我们限定了课程名称为"计算机"或"英语"的成绩(c_name IN ('计算机', '英语'))。再使用GROUP BY子句根据学生ID进行分组。
接下来,我们使用HAVING子句和COUNT(DISTINCT c_name) = 2条件,确保学生同时参加了两门课程(计算机和英语)。即只选择有两门不同课程的学生ID。
最后,在外部的主查询中,我们选择学生表格中的学生ID、姓名、性别、出生日期、院系和地址信息。使用WHERE stu.id IN (subquery)子句,将子查询的结果作为条件,以获取同时参加计算机和英语考试的学生信息。
通过这样的查询,您可以获得同时参加计算机和英语考试的学生的相关信息。
14.将计算机考试成绩按从高到低进行排序
select *from student as stu
join score as sc
on stu.id=sc.stu_id
where sc.c_name='计算机'
ORDER BY sc.grade DESC;
mysql> select *from student as stu join score as sc on stu.id=sc.stu_id where sc.c_name='计算机' ORDER BY sc.grade DESC;
+-----+-----------+------+-------+--------------+--------------------+----+--------+-----------+-------+
| id | name | sex | birth | department | address | id | stu_id | c_name | grade |
+-----+-----------+------+-------+--------------+--------------------+----+--------+-----------+-------+
| 901 | 张老大 | 男 | 1985 | 计算机系 | 北京市海淀区 | 1 | 901 | 计算机 | 98 |
| 906 | 王六 | 男 | 1988 | 计算机系 | 湖南省衡阳市 | 9 | 906 | 计算机 | 90 |
| 904 | 李四 | 男 | 1990 | 英语系 | 辽宁省阜新市 | 6 | 904 | 计算机 | 70 |
| 902 | 张老二 | 男 | 1986 | 中文系 | 北京市昌平区 | 3 | 902 | 计算机 | 65 |
+-----+-----------+------+-------+--------------+--------------------+----+--------+-----------+-------+
4 rows in set (0.00 sec)
mysql>
15.从student表和score表中查询出学生的学号,然后合并查询结果
select stu.id,stu.name from student as stu
join score as sc
on stu.id=sc.stu_id;
mysql> select stu.id,stu.name from student as stu
-> join score as sc
-> on stu.id=sc.stu_id;
+-----+-----------+
| id | name |
+-----+-----------+
| 901 | 张老大 |
| 901 | 张老大 |
| 902 | 张老二 |
| 902 | 张老二 |
| 903 | 张三 |
| 904 | 李四 |
| 904 | 李四 |
| 905 | 王五 |
| 906 | 王六 |
| 906 | 王六 |
+-----+-----------+
10 rows in set (0.00 sec)
mysql>
16.查询姓张或者姓王的同学的姓名、院系和考试科目及成绩
select stu.name,stu.department,sc.c_name,sc.grade from student as stu
join score as sc
on stu.id=sc.stu_id
where stu.name like'张%' or stu.name like'王%';
mysql> select stu.name,stu.department,sc.c_name,sc.grade from student as stu
-> join score as sc
-> on stu.id=sc.stu_id
-> where stu.name like'张%' or stu.name like'王%';
+-----------+--------------+-----------+-------+
| name | department | c_name | grade |
+-----------+--------------+-----------+-------+
| 张老大 | 计算机系 | 计算机 | 98 |
| 张老大 | 计算机系 | 英语 | 80 |
| 张老二 | 中文系 | 计算机 | 65 |
| 张老二 | 中文系 | 中文 | 88 |
| 张三 | 中文系 | 中文 | 95 |
| 王五 | 英语系 | 英语 | 94 |
| 王六 | 计算机系 | 计算机 | 90 |
| 王六 | 计算机系 | 英语 | 85 |
+-----------+--------------+-----------+-------+
17.查询都是湖南的学生的姓名、年龄、院系和考试科目及成绩
select stu.name,stu.department,sc.c_name,sc.grade,stu.address from student as stu
join score as sc
on stu.id=sc.stu_id
where stu.address like '湖南%';
mysql> select stu.name,stu.department,sc.c_name,sc.grade,stu.address from student as stu
-> join score as sc
-> on stu.id=sc.stu_id
-> where stu.address like '湖南%';
+--------+--------------+-----------+-------+--------------------+
| name | department | c_name | grade | address |
+--------+--------------+-----------+-------+--------------------+
| 张三 | 中文系 | 中文 | 95 | 湖南省永州市 |
| 王六 | 计算机系 | 计算机 | 90 | 湖南省衡阳市 |
| 王六 | 计算机系 | 英语 | 85 | 湖南省衡阳市 |
+--------+--------------+-----------+-------+--------------------+
3 rows in set (0.00 sec)
mysql>