数据库综合测试

问答题

1. MySQL默认存储引擎？
   InnoDB

2. 常见的数据库产品有哪些？
   MySQL, SQL server, Oracle, sybase, DB2

3. 写出MySQL常用命令：进入某个库，查询某个库中的所有表，创建数据库
   Use demo， shou table， create database

4. 请写出MySQL常见的数据类型
   数值型，浮点型，字符串型，日期型

5. 请写出MySQL创建表的语法

CREATE TABLE table.name(
	字段名称 字段数据类型[字段约束],
	字段名称 字段数据类型[字段约束]
)

6. MySQL中完整性约束有什么作用
   用来限制表中数据准确性，完整性，一致性，联动性的一套规则

7. MySQL中LIMIT关键字是做什么用的，其参数代表什么意思
   返回表中指定行范围的数据
   第一个参数是起始位置
   第二个参数是需要返回的参数

8. 如何避免重复数据查询，请举例说明
   添加DISTINCT关键字 例如：SELECT DISTINCT clazz FROM stu_info

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

SELECT concat（'￥',price) FROM t_menu; 如￥2000

10. 多表连接操作有哪些，区别是什么
    自然连接：使用WHERE条件讲两个表之间进行关联查询
    内连接：内连接查询是可以使用自然连接查询替代的，但是效率方面，内连接会高
    左外连接：以左表为主表，左表中的数据都会被显示出来，关联的右表中，如果存在符合条件的数据，那么或被关联出来并显示，如果没有，则会显示NULL

编程题

①学生表与分数表相关操作

系统初始了以下脚本数据：
向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);

②表名 student_score

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

回答

1、创建student和score表语句

CREATE TABLE student(
    id              INT(10) PRIMARY KEY 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 AUTO_INCREMENT,
    stu_id    INT(10) NOT NULL,
    c_name    VARCHAR(20),
    grade     INT(10)
);

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表中查询年龄28~32的学生信息（用 BETWEEN AND)

SELECT * FROM student WHERE YEAR(NOW())-birth BETWEEBB 28 ABD 32;

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

SELECT department AS '院系',COUNT(*) AS '该院人数' FROM student GROUP BY department;

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

SELECT c_name AS '科目名称',MAX(grade) AS '最高分' FROM score GROUP BY c_name;

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

SELECT s.name AS '姓名',c.name AS '课程名称',grade AS '课程分数'
FROM score AS c LEFT JOIN student AS s ON c.stu_id = s.id
WHERE s.name = '李四';

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

SELECT * FROM score AS c LEFT JOIN student AS S ON c.stu_id = s.id;

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

SELECT s.name AS '姓名',SUM(c.grade) AS '总成绩' FROM score AS c
LEFT JOIN student AS s ON c.stu_id = s,id GROUP BY s.name;

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

SELECT c_name AS '课程名称',AVG(grade) AS '平均成绩' FROM score GROUP BY c.name;

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

-- 第一种
SELECT s.* FROM score AS c LEFT JOIN student AS s ON c.stu_id = s.id
WHERE c.c_name = '计算机' AND c.grade < 95;

-- 第二种
SELECT * FROM student WHERE id IN (SELECT stu_id FROM score WHERE c.name = '计算机' AND grade < 95);

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

-- 第一种
SELECT * FROM student WHERE id IN
(SELECT stu_id FROM score WHERE c_name = '计算机' OR c_name = '英语' FROUP BY stu_id HAVING COUNT(*) = 2);

-- 第二种
SELECT * FROM student WHERE id IN
(SELECT stu_id FROM score WHERE stu_id IN(SELECT stu_id FROM score WHERE c_name = '计算机') AND c_name = '英语');

-- 第三种
SELECT grade AS '计算机' FROM score WHERE c_name = '英语' ORDER BY grade DESC;

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

SELECT grade AS '计算机考试成绩' FROM score WHERE c_name = '计算机' ORDER BY grade DESE;

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

SELECT id AS '学号' FROM studnet
UNION
SELECT DISTINCT stu_id AS '学号' FROM score;

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

SELECT s,name AS '姓名',s.department AS '院系',c.c_name AS '考试科目',c.grade AS '成绩'
FROM score AS c LEFT JOIN student AS s ON c.stu_id = s.id
WHERE s.name LIKE '张%' OR s.name LIKE '王%';

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

SELECT s.name AS '姓名',YEAR(NOW())-s.birth AS '年龄',s.department AS '院系',c_name AS '考试科目',c.grade AS '成绩'
FROM score AS c LEFT JOIN student AS s ON c.stu_id = s.id
WHERE s.address LIKE '湖南%';

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

-- 第一种
SELECT DISTINCT name FROM student_score
WHERE name NOT IN (SELECT DISTINCT name FROM student_score WHERE score <= 80);

-- 第二种
SELECT name FROM student_score GROUP BY name HAVING MIN(score) > 80;

20、查询出【张】姓学生中平均成绩大于75分的学生信息

SELECT *
FROM student_score
WHERE name IN (SELECT name FROM student_score
WHERE name LIKE '张%'
GROUP BY name 
HAVING AVG(score) > 75);