数据库综合测试

一、简答题（共40分，每题4分）

1. MySQL默认存储引擎？
   解答： InnoDB 和 MyISAM

2. 常见的数据库产品有哪些？
   解答：Oracle MySQL(Oracle) SQLServer(微软) DB2(IBM)。

3. 写出MySQL常用命令：进入某个库，查询某个库中的所有表，创建数据库。
   解答：

问答	解答
进入某个库	use d_name
查询某个库中的所有表	show tables
创建数据库	create database d_name

4. 请写出MySQL常见的数据类型。
   解答：

常用数据类型	含义
int	整型
decimal(size,d)	浮点型
varchar(size)	可变长度字符型
text	文本型
date	日期型
datetime	日期时间型

5. 写出MySQL创建表的语法。
   解答：

create table t_name(
  字段1名 数据类型[约束类型],
  字段2名 数据类型[约束类型]
);

6. MySQL中完整性约束有什么作用？
   解答：
   保证数据合法性及相对完整性。
7. MySQL中LIMIT关键字是做什么的，其参数代表什么意思？
   解答：

-- 限制每页显示的记录数
-- 显示前4条记录数。
select distinct name from menu limit 4; 
-- 从第3+1条开始，本页显示4条记录数
select distinct name from menu limit 3,4; 

8. 如何避免重复数据查询，请举例说明？
   解答：

-- 使用关键字distinct
select distinct name from menu;

9. 如何使用字符串拼接函数，请举例说明？
   解答：

-- 使用关键字concat
select name,concat(price,'$') from menu;

10. 多表连接操作有哪些，区别是什么？
    解答：

多表连接操作	区别
并	具有相同字段数目和字段类型的表合并，表1记录数 + 表2记录数
笛卡尔积	无关系的组合，表1记录数 × 表2记录数
内连接	有关系的笛卡尔积，保留匹配数据记录，舍弃不匹配的数据记录
外连接	有关系的笛卡尔积，保留匹配数据记录，保留主表不匹配的数据记录

二、 编程题（共60分，每题3分）
①学生表与分数表相关操作

系统初始了以下脚本数据

向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);

试题
1、创建student和score表语句

-- 创建student表
create table student(
  id int(10) primary key auto_increment comment '学号',
  name varchar(20) not null comment '姓名',
  sex varchar(4) comment '性别',
  birth year comment '出生年月',
  department varchar(20) not null comment '院系',
  address varchar(50) comment '家庭住址'
)
-- 创建score表
create table score(
  id int(10) primary key auto_increment comment '编号',
  stu_id int(10) not null comment '学号',
  c_name varchar(20) comment '课程名',
  grade int(10) comment '分数'
)

2、查询student表的所有记录

select * from student;

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

select * from student limit 1,3;

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

select id as '学号',name as '姓名',department as '院系' from student;

5、从student表中查询计算机系和英语系的学生的信息（用 IN 关键字）

select * from student where department in('计算机系','英语系');

6、从student表中查询年龄18~22岁的学生信息（用 BETWEEN AND）

select * from student where (year(now())-birth) between 18 and 22;

7、从student表中查询每个院系有多少人

select department,count(*) from student group by department;

8、从score表中查询每个科目的最高分

select c_name,max(grade) from score group by c_name;

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

select * from 
(select name,c_name, grade from student as s left join score as sc on s.id = sc.stu_id) as t_new;
where name = '李四'; 

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

select  * from student as s left join score as sc on s.id = sc.stu_id;

11、计算每个学生的总成绩

select s.name, sum(grade) from student as s left join score as sc on s.id=sc.stu_id
group by s.name;

12、计算每个考试科目的平均成绩

select c_name,avg(grade) from score group by c_name;

13、查询计算机成绩低于95的学生信息

select * from student,score where score.grade < 95 and score.c_name = '计算机' and student.id = score.stu_id ;

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

select * from student 
where student.id in ( select stu_id from score where c_name in ('计算机','英语') 
group by stu_id having count(c_name) > 1);

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

select * from student as s 
left join score as sc on s.id = sc.stu_id 
where sc.c_name = '计算机' 
order by grade DESC;

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

select s.id from student as s union
select sc.stu_id from score as sc;

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

select t_new.name as '姓名',t_new.department as '院系',t_new.c_name as '考试科目',t_new.grade as '成绩' from(
select s.name,s.department,sc.c_name,sc.grade 
from student as s left join score as sc on s.id=sc.stu_id) as t_new
where t_new.name like '张%' or t_new.name like '王%';

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

select t_new.name as '姓名',(year(now()) - t_new.birth) as '年龄',t_new.department as '院系',t_new.c_name as '考试科目',t_new.grade as '成绩' from(
select s.name,s.birth,s.department,sc.c_name,sc.grade,s.address 
from student as s left join score as sc on s.id=sc.stu_id) as t_new
where t_new.address like '湖南%';

②表名 student_score

name	course	score
张三	语文	81
张三	数学	75
李四	语文	76
李四	数学	90
王五	语文	81
王五	数学	100
王五	英语	90

1、用一条 SQL 语句，查询出每门课都大于 80 分的学生姓名。

select name from student_score group by name having min(score) > 80 ;

2、查询出「张」姓学生中平均成绩大于 75 分的学生信息。
解答：

select * from student_score
where name in(
select name from student_score where name like '张%'
group by  name having avg(score) > 75);