数据库查询练习例题及答案

数据库查询练习例题及答案

Student表的定义

字段名	字段描述	数据类型	主键	外键	非空	唯一	自增
Id	学号	INT(10)	是	否	是	是	是
Name	姓名	VARCHAR(20)	否	否	是	否	否
Sex	性别	VARCHAR(4)	否	否	否	否	否
Birth	出生年份	YEAR	否	否	否	否	否
Department	院系	VARCHAR(20)	否	否	是	否	否
Address	家庭住址	VARCHAR(50)	否	否	否	否	否

Score表的定义

字段名	字段描述	数据类型	主键	外键	非空	唯一	自增
Id	编号	INT(10)	是	否	是	是	是
Stu_id	学号	INT(10)	否	否	是	否	否
C_name	课程名	VARCHAR(20)	否	否	否	否	否
Grade	分数	INT(10)	否	否	否	否	否

1.写出创建 student 和 score 表的 SQL 语句，并在自己数据库上执行

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

CREATE TABLE score (
id INT ( 10 ) PRIMARY KEY NOT NULL UNIQUE auto_increment,
stu_id INT ( 10 ) NOT NULL,
c_name VARCHAR ( 20 ),
grade INT ( 10 )
);

2.为 student 表和 score 表增加记录，写出 SQL 语句以及在自己的数据库上执行
向 student 表插入记录的 INSERT 语句如下：
‘张老大’, ‘男’,1985,‘计算机系’, ‘北京市海淀区’
‘张老二’, ‘男’,1986,‘中文系’, ‘北京市昌平区’
‘张三’, ‘女’,1990,‘中文系’, ‘湖南省永州市’);
李四’, ‘男’,1990,‘英语系’, ‘辽宁省阜新市’);
王五’, ‘女’,1991,‘英语系’, ‘福建省厦门市’);
王六’, ‘男’,1988,‘计算机系’, ‘湖南省衡阳市’);

insert into student(id,name,sex,birth,department,address)values(‘801’,‘张老大’, ‘男’,1985,‘计算机系’, ‘北京市海淀区’),
(‘802’,‘张老二’, ‘男’,1986,‘中文系’, ‘北京市昌平区’),
(‘803’,‘张三’, ‘女’,1990,‘中文系’, ‘湖南省永州市’),
(‘804’,‘李四’, ‘男’,1990,‘英语系’, ‘辽宁省阜新市’),
(‘805’,‘王五’, ‘女’,1991,‘英语系’, ‘福建省厦门市’),
(‘806’,‘王六’, ‘男’,1988,‘计算机系’, ‘湖南省衡阳市’);

或者

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 语句如下：
张老大,‘计算机’,98);
张老大,‘英语’, 80);
张老二, ‘计算机’,65);
张老二, ‘中文’,88);
张三, ‘中文’,95);
李四, ‘计算机’,70);
李四, ‘英语’,92);
王五, ‘英语’,94);
王五, ‘计算机’,90);
王六, ‘英语’,85);
insert into score (stu_id,c_name,grade)values(801,‘计算机’,98),
(901,‘英语’, 80),
(902, ‘计算机’,65),
(902, ‘中文’,88),
(903, ‘中文’,95),
(904, ‘计算机’,70),
(904, ‘英语’,92),
(905, ‘英语’,94),
(905, ‘计算机’,90),
(906, ‘英语’,85);

或者

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,905, ‘计算机’,90);

INSERT INTO score VALUES(NULL,906, ‘英语’,85);

3.查询 student 表的所有记录

SELECT * FROM student;

4.查询 student 表的第 2 条到 4 条记录

SELECT * FROM student LIMIT 1,3;

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

SELECT id,name,department FROM student;

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

SELECT * FROM student WHERE department = ‘计算机系’ OR department = ‘英语系’;

或

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

7.从 student 表中查询年龄 28~32 岁的学生信息

SELECT * FROM student WHERE 2020-birth >=28 AND 2020-birth <= 32;

或

SELECT * FROM student WHERE 2020-birth BETWEEN 28 AND 32;

8.从 student 表中查询每个院系有多少人

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

巩固练习：查询score表学习每个课程的人数

SELECT c_name,COUNT( 1 ) FROM score GROUP BY c_name;

9.从 score 表中查询每个科目的最高分

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

10.查询李四的考试科目（c_name）和考试成绩（grade）

SELECT c_name,grade FROM student,score WHERE name = ‘李四’ AND student.id = stu_id;

或

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

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

交叉连接：笛卡尔积（表数据未展示完全）

SELECT * FROM student,score ;

内连接：只列出连接表条件匹配行。ON或WHERE都行

1）等值连接（JOIN可以省略）

SELECT * FROM student JOIN score WHERE student.id = stu_id ;

2）自然连接（相同字段为id，无相同值，所以为空）

SELECT * FROM student NATURAL JOIN score;

外连接：必须用ON，可以用WHERE做再过滤

1）左外连接，左表为主表，显示全部记录，未匹配显示补NULL

SELECT * FROM student LEFT JOIN score on student.id = stu_id;

2）右外连接

SELECT * FROM student RIGHT JOIN score on student.id = stu_id;

自连接：参与连接的表都是同一个表（通过给表取别名虚拟出）

SELECT * FROM student s1,student s2 WHERE s1.id = s2.id;

12.计算每个学生的总成绩

SELECT name,SUM(grade) FROM student,score WHERE student.id = stu_id GROUP BY name;

13.计算每个考试科目的平均成绩

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

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

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

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

SELECT
*
FROM
student
WHERE
id IN (
SELECT
s1.stu_id
FROM
score s1,
score s2
WHERE
s1.c_name = ‘计算机’
AND s2.c_name = ‘英语’
AND s1.stu_id = s2.stu_id
);

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

SELECT grade FROM score WHERE c_name =‘计算机’ ORDER BY grade DESC;

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

SELECT id FROM student UNION SELECT stu_id FROM score;

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

注：AND的优先级大于OR，所以要正确执行顺序必须给OR带上括号。

SELECT
name,
department,
c_name,
grade
FROM
student,
score
WHERE
( name LIKE ‘张%’ OR name LIKE ‘王%’ )
AND student.id = stu_id;

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

SELECT
name,
2020-birth age,
department,
c_name,
grade
FROM
student,
score
WHERE
address LIKE ‘湖南%’
AND student.id = stu_id;