MySQL数据库基础与实例教程练习题参考答案

MySql数据库基础题库及答案一、填空题1.数据库是按照某种数据结构对数据进行____、_____和______的容器。

组织、存储、管理2.结构化查询语言SQL 分为_______、_______、_______和________四种语言。

DDL、DML、DQL、DCL3.MySQL中常用的约束有_____、_____、_____、_____、_____。

主键、外键、唯一、默认、非空4.MySQL配置文件my.ini常用的参数选项组有_____、_____、_____。

[client]、[mysql]、[mysqld]5.MySQL字符序命名规则中：ci 表示_____、cs 表示_____、bin 表示_____。

大小写不敏感、大小写敏感、二进制编码值比较6.查看MySQL 字符集的命令是：___________________________。

show variables like 'character%’7.MySQL5.6默认支持的存储引擎是______。

InnoDB8.显示表结构的命令_________、_____________。

desc <table_name> 、show create table <table_name>9.查看全局系统变量的命令_________，查看当前会话系统变量的命令_________。

show global variables; show session variables;10.MySQL提供的数据类型包括：_____、_____、_____、_____和_____。

数值类型、字符串类型、日期类型、复合类型、二进制类型11.MySQL 支持两种复合数据类型：_____和_____。

enum枚举类型、set集合类型12.默认情况下，MySQL自增型字段的值从1开始递增，且步长为1，设置自增字段的语法：______。

<字段名> <数据类型> auto_increment13.创建表时，设置表的字符集语法格式：____________________。

MySQL数据库基础实例教程试题及答案一、选择题（每题2分，共60分）1、SQL语言通常称为( A )A、结构化查询语言B、结构化控制语言C、结构化定义语言D、结构化操纵语言2、目前三种基本的数据模型是 ( A )。

A、层次模型、网络模型、关系模型B、对象模型、网络模型、关系模型C、网络模型、对象模型、层次模型D、层次模型、关系模型、对象模型3、关系模型的基本结构是( A )。

A、二维表B、树形结构C、无向图D、有向图4、下列四项中，不属于数据库特点的是( B )。

A、数据共享B、数据冗余很高C、数据独立性高D、数据完整性5、SQL语言是( C )语言A、层次数据库B、网络数据库C、关系数据库D、非数据库6、DBMS是什么( B )A、操作系统B、数据库管理系统C、数据库D、数据库管理员7、MySQL是一个( C )的数据库系统。

A、网状型B、层次型C、关系型D、以上都不是8、如果要修改表的结构，应该使用SQL语言的命令(C )A、UPDATE TABLEB、MODIFY TABLEC、ALTER TABLED、CHANGE TABLE9、数据库中存放两个关系：教师(教师编号，姓名)和课程(课程号，课程名，教师编号)，为快速查出某位教师所讲授的课程，应该(C )A、在教师表上按教师编号建索引B、在课程表上按课程号建索引C、在课程表上按教师编号建索引D、在教师表上按姓名建索引10、下列关于索引的说法中正确的是( C )A、一个表可以建立多个主键索引B、索引只能建立在1个字段上C、索引可以加快表之间连接的速度D、可以使用ADD INDEX语句创建索引11、下列对DELETE权限的叙述中，正确的是( C )A、允许删除数据B、允许删除关系C、允许对数据库模式进行删除D、和DROP权限等价12、数据库的完整性是指数据的( A )。

A、正确性和相容性B、合法性和不被恶意破坏C、正确性和不被非法存取D、合法性和和相容性13、MySQL中的Create Database语句可以创建一个( C )。

MySQL数据库基础与实例教程练习题参考答案由于时间仓促，中难免存在错误，不妥之处恳请读者批评指正！第一章答案1．数据库管理系统中常用的数学模型有哪些？数据库管理系统通常会选择某种“数学模型”存储、组织、管理数据库中的数据，常用的数学模型包括“层次模型”、“网状模型”、“关系模型”以及“面向对象模型”等。

2．您听说过的关系数据库管理系统有哪些？数据库容器中通常包含哪些数据库对象？目前成熟的关系数据库管理系统主要源自欧美数据库厂商，典型的有美国微软公司的SQL Server、美国IBM公司的DB2和Informix、德国SAP公司的Sybase、美国甲骨文公司的Oracle。

数据库容器中通常包含表、索引、视图、存储过程、触发器、函数等数据库对象。

3．通过本章知识的讲解，SQL与程序设计语言有什么关系？SQL并不是一种功能完善的程序设计语言，例如，不能使用SQL构建人性化的图形用户界面（Graphical User Interface，GUI），程序员需要借助Java、VC++等面向对象程序设计语言或者HTML的FORM表单构建图形用户界面（GUI）。

如果选用FORM表单构建GUI，程序员还需要使用JSP、PHP或者.NET编写Web应用程序，处理FORM表单中的数据以及数据库中的数据。

其他答案：1、首先SQL语言是数据库结构化查询语言，是非过程化编程语言。

而程序设计语言则有更多的面向对象及逻辑程序设计。

比如用SQL语言编写图形用户界面（例如窗口、进度条），是无法实现的。

2、SQL语言可以说是，程序设计语言和数据库之间的一个翻译官。

程序设计语言需要操作数据库时，需要借助（或者说调用）SQL语言来翻译给数据库管理系统。

3、不同数据库管理系统会有一些特殊的SQL规范，比如limit关键词在SQL Server 中无法使用。

而这些规范与程序设计语言无关。

4．通过本章的学习，您了解的MySQL有哪些特点？与题目2中列举的商业化数据库管理系统相比，MySQL具有开源、免费、体积小、便于安装，但功能强大等特点。

●锁的粒度越小，应用系统的并发性能就越高。

由于InnoDB支持行级锁，如果需要提高应用系统的并发性能，建议选用InnoDB存储引擎。

●如果事务的隔离级别无法解决事务的并发问题，数据库开发人员只有在完全了解锁机制的情况下，才能在SQL语句中手动设置锁，否则应该使用事务的隔离级别。

●使用事务时，尽量避免在一个事务中使用不同存储引擎的表。

●尽量缩短锁的生命周期。

例如，在事务中避免使用长事务，可以将长事务拆分成若干个短事务。

在事务中避免使用循环语句。

●优化表结构，优化SQL语句，尽量缩小锁的作用范围。

例如，可以将大表拆分成小表，从而缩小锁的作用范围。

● InnoDB默认的事务隔离级别是repeatable read，而repeatable read隔离级别使用间隙锁实现InnoDB的行级锁。

不合理的索引可能导致行级锁升级为表级锁，从而引发严重的锁等待问题。

●对于InnoDB行级锁而言，设置锁等待超时参数为合理范围，编写锁等待超时异常处理程序，解决发生锁等待问题（甚至死锁）。

●为避免死锁，一个事务对多条记录进行更新操作时，当获得所有记录的排他锁后，再进行更新操作。

●为避免死锁，一个事务对多个表进行更新操作时，当获得所有表的排他锁后，再进行更新操作。

●为避免死锁，确保所有关联事务均以相同的顺序访问表和记录。

●必要时，使用表级锁来避免死锁。

●避免在一个单独事务中混合使用存储引擎。

在服务器级别，MySQL不能管理事务，事务是由存储引擎实现的，尽量避免在一个单独事务中混合使用存储引擎。

如果在一个单独事务中混合了事务表和非事务表，若一切正常，这个事务就没有问题；但是如果执行回滚操作，非事务表改变的数据并不会回滚。

若数据库的一致性遭到了破坏，则很难恢复和呈现完整的事务。

习 题1．请简单描述事务的必要性。

2．关闭MySQL自动提交的方法有哪些？您推荐数据库开发人员使用哪一种方法？3．关闭MySQL自动提交后，提交更新语句的方法有哪些？您推荐数据库开发人员使用哪一种方法？4．请简单描述典型的事务处理使用方法。

实践与练习答案第1章1.选择题BBACA2.概念题1）数据库（Dstabase DB）是“按照某种数据结构对数据进行组织、存储和管理的容器”，简单的说就是用来存储、管理数据的容器。

数据库系统（Dstabase System，DBS）是指在计算机中引入数据库后的系统，一般由数据库、数据库管理系统、应用程序、数据库管理员组成。

数据库管理系统（Dstabase Management System，DBMS）是一个管理、控制数据库容器中各种数据库对象的系统软件。

2）使用关系模型对数据进行组织、存储和管理的数据库称为关系数据库，关系数据库系统是支持关系数据模型的数据库系统。

3）表1-1SQL功能及包含的主要动词4）数据库设计分为如下6个阶段：需求分析阶段、概念结构设计阶段、逻辑结构设计阶段、数据库物理结构设计阶段、数据库实施阶段以及数据库运行于维护阶段。

各阶段内容（略）。

5）E-R图中的实体表示现实世界具有相同属性描述的事物的集合，不是某一个具体事物，而是一类事物的统称。

E-R图中的实体通常使用矩形表示。

E-R图中的属性通常表示实体的某种特征，也可以使用属性表示实体间关系的特征。

一个实体通常包含多个属性，每个属性由属性名唯一标记，画在椭圆内。

3.操作题（1）学生：学好、姓名、年龄、性别、班级名班级：班级名、班主任、班级人数（2）一个学生只能属于一个班级，一个班级可以有很多学生，多以班级和学生之间是1对多的关心，即1：n。

（3）拥有（4）E-R图如下：12 班班班班班班1n 班班班班班班班班班班班班班班班班班班班。

MySql数据库基础题库及答案一、填空题1.数据库是按照某种数据结构对数据进行____、_____和______的容器。

组织、存储、管理2.结构化查询语言SQL 分为_______、_______、_______和________四种语言。

DDL、DML、DQL、DCL3.MySQL中常用的约束有_____、_____、_____、_____、_____。

主键、外键、唯一、默认、非空4.MySQL配置文件my.ini常用的参数选项组有_____、_____、_____。

[client]、[mysql]、[mysqld]5.MySQL字符序命名规则中：ci 表示_____、cs 表示_____、bin 表示_____。

大小写不敏感、大小写敏感、二进制编码值比较6.查看MySQL 字符集的命令是：___________________________。

show variables like 'character%’7.MySQL5.6默认支持的存储引擎是______。

InnoDB8.显示表结构的命令_________、_____________。

desc <table_name> 、show create table <table_name>9.查看全局系统变量的命令_________，查看当前会话系统变量的命令_________。

show global variables; show session variables;10.MySQL提供的数据类型包括：_____、_____、_____、_____和_____。

数值类型、字符串类型、日期类型、复合类型、二进制类型11.MySQL 支持两种复合数据类型：_____和_____。

enum枚举类型、set集合类型12.默认情况下，MySQL自增型字段的值从1开始递增，且步长为1，设置自增字段的语法：______。

<字段名> <数据类型> auto_increment13.创建表时，设置表的字符集语法格式：____________________。

mysql教程习题答案MySQL教程习题答案MySQL是一个开源的关系型数据库管理系统，广泛应用于网站开发和数据存储。

学习MySQL对于想要从事数据库管理和开发工作的人来说是非常重要的。

为了帮助大家更好地掌握MySQL，下面我们将提供一些常见的MySQL教程习题答案，希望能够帮助大家更好地理解和掌握MySQL。

1. 创建一个名为"students"的数据库，其中包含学生的姓名、年龄和成绩三个字段，分别为varchar、int和float类型。

答案：```sqlCREATE DATABASE students;USE students;CREATE TABLE student_info (name VARCHAR(50),age INT,score FLOAT);```2. 向students表中插入一条记录，学生姓名为"张三"，年龄为20岁，成绩为85.5分。

答案：```sqlINSERT INTO student_info (name, age, score) VALUES ('张三', 20, 85.5);```3. 查询students表中所有学生的信息。

答案：```sqlSELECT * FROM student_info;```4. 查询成绩大于90分的学生信息。

答案：```sqlSELECT * FROM student_info WHERE score > 90;```5. 修改学生"张三"的成绩为88分。

答案：```sqlUPDATE student_info SET score = 88 WHERE name = '张三';```通过以上习题答案的学习，相信大家对于MySQL的基本操作和语法有了更清晰的认识。

当然，要想真正掌握MySQL，还需要不断地练习和实践。

希望大家能够通过不断地学习和实践，掌握MySQL这一重要的数据库管理工具。

mysql数据库考试试题及答案MySQL数据库考试试题及答案随着信息技术的不断发展，数据库管理系统成为了企业和组织中不可或缺的一部分。

而MySQL作为一种开源的关系型数据库管理系统，在企业中得到了广泛的应用。

为了评估和验证对MySQL数据库的掌握程度，许多企业和学术机构都会组织MySQL数据库的考试。

本文将介绍一些常见的MySQL数据库考试试题，并给出相应的答案。

一、基础知识题1. 什么是数据库？答案：数据库是指存储和管理大量结构化数据的系统，它可以提供数据的快速访问和高效的数据处理能力。

2. 什么是关系型数据库？答案：关系型数据库是一种基于关系模型的数据库，其中数据以表的形式存储，表之间通过主键和外键建立关联。

3. 什么是SQL？答案：SQL（Structured Query Language）是一种用于管理关系型数据库的语言，它可以用于查询、插入、更新和删除数据等操作。

4. 什么是主键？答案：主键是用于唯一标识表中每一行数据的字段，它具有唯一性和非空性的特点。

5. 什么是外键？答案：外键是用于建立表与表之间关联关系的字段，它引用了其他表的主键。

二、SQL语句题1. 编写一个SQL语句，创建一个名为"students"的表，包含字段："id"（主键，整数类型）、"name"（字符串类型）、"age"（整数类型）。

答案：CREATE TABLE students (id INT PRIMARY KEY, name VARCHAR(50), age INT);2. 编写一个SQL语句，查询"students"表中年龄大于18岁的学生的姓名和年龄。

答案：SELECT name, age FROM students WHERE age > 18;3. 编写一个SQL语句，向"students"表中插入一条记录，学生姓名为"张三"，年龄为20岁。

第一章一、选择题1．数据库系统的核心是________。

A．数据模型B．数据库管理系统C．数据库D．数据库管理员2．E-R图提供了表示信息世界中实体、属性和________的方法。

A．数据B．联系C．表D．模式3．E-R图是数据库设计的工具之一，它一般适用于建立数据库的________。

A．概念模型B．结构模型C．物理模型D．逻辑模型4．将E．R图转换到关系模式时，实体与联系都可以表示成________。

A．属性B．关系C．键D．域5．在关系数据库设计中，设计关系模式属于数据库设计的________。

A．需求分析阶段B．概念设计阶段C．逻辑设计阶段D．物理设计阶段6．从E—R模型向关系模型转换，一个M:N的联系转换成一个关系模式时，该关系模式的键是________。

A．M端实体的键B．Ⅳ端实体的键C．M端实体键与N端实体键组合D．重新选取其他属性二、填空题1．数据库系统的三级模式结构是指数据库系统是由________、________和________三级构成。

2．数据库系统的运行与应用结构有客户／服务器结构(C／S结构)和________两种。

3．在数据库的三级模式体系结构中，外模式与模式之间的映射实现了数据库的________独立性。

4．用二维表结构表示实体以及实体间联系的数据模型称为________数据模型。

5．数据库设计包括概念设计、________和物理设计。

6．在E-R图中，矩形表示________。

三、简答题1．请简述什么是数据库管理系统，以及它的主要功能有哪些?2．请简述什么是数据库系统?3．请简述什么是模式、外模式和内模式?4．请简述C／S结构与B／S结构的区别。

5．请简述关系规范化过程。

参考答案一、选择题1．A 2．B 3．A 4．B 5．C 6．C二、填空题1．模式外模式内模式2．浏览器／服务器结构(B／S结构) 3．逻辑4。

关系5．逻辑设计6．实体三、简答题1．略。

可参考第1章1．1节内容。

mysql试题及答案MySQL作为一种常用的关系型数据库管理系统，在企业和个人应用中广泛使用。

通过进行MySQL试题的练习和答案的解析，可以帮助读者加深对MySQL的理解和熟练应用。

本文将提供一系列MySQL试题及答案，旨在帮助读者提高MySQL技能。

1. 试题一：MySQL基础1.1 MySQL是什么？它有哪些特点？MySQL是一种开源的关系型数据库管理系统，具有以下特点：- 开源免费：用户可以免费使用和修改MySQL，而且还能获得MySQL 的源代码。

- 跨平台：MySQL支持多种操作系统，如Windows、Linux、MacOS等。

- 高性能：MySQL具有高效的数据存储和检索能力，能够处理大量数据。

- 可扩展性：MySQL支持可扩展性，可以通过添加更多的硬件资源来提高系统性能。

- 安全性：MySQL提供了访问控制和数据加密等安全措施，保护数据的安全性。

1.2 如何在Windows系统上安装MySQL？在Windows系统上安装MySQL，可以按照以下步骤进行：1. 下载MySQL安装包，选择合适的版本和安装包类型。

2. 双击安装包，根据安装向导进行安装。

3. 配置MySQL安装参数，如设置root密码、端口号等。

4. 完成安装后，启动MySQL服务。

1.3 如何连接MySQL数据库？连接MySQL数据库，可以使用MySQL提供的命令行工具或者其他可视化管理工具。

- 命令行连接：打开命令行界面，输入`mysql -u 用户名 -p 密码`，即可连接到MySQL数据库。

- 可视化管理工具连接：根据所使用的工具类型和版本，在连接设置中填写相关信息，如主机名、端口号、用户名、密码等。

2. 试题二：MySQL查询语句2.1 如何查询表中的所有数据？要查询表中的所有数据，可以使用SELECT语句：```sqlSELECT * FROM 表名;```这条语句将返回指定表中的所有数据。

2.2 如何查询表中特定条件的数据？要查询表中特定条件的数据，可以使用WHERE子句来指定条件：```sqlSELECT * FROM 表名 WHERE 条件;```其中，条件可以是等于、大于、小于、不等于等比较运算符，也可以是逻辑运算符（AND、OR、NOT）的组合。

mysql数据库的相关练习题及答案表结构⽰意图：表结构创建语句：class表创建语句create table class(cid int not null auto_increment primary key, caption varchar(32) not null)engine=innodb default charset=utf8;student表创建语句create table student(-> sid int not null auto_increment primary key,-> sname varchar(32) not null,-> gender varchar(8) not null,-> class_id int not null)engine=innodb default charset=utf8;teacher表创建语句create table teacher(-> tid int not null auto_increment primary key,-> tname varchar(32) not null)engine=innodb default charset=utf8;course表创建语句create table course(-> cid int not null auto_increment primary key,-> cname varchar(16) not null,-> teacher_id int not null)engine=innodb default charset=utf8;score表创建语句create table score(-> sid int not null auto_increment primary key,-> student_id int not null,-> corse_id int not null,-> number int not null)engine=innodb default charset=utf8;1、⾃⾏创建测试数据2、查询“⽣物”课程⽐“物理”课程成绩⾼的所有学⽣的学号；select A.student_id,sw,ty from (select student_id,number as sw from score left join course on score.corse_id = course.cid where ame = '⽣物') as A left join (select student_id,number as ty from score left join course on score.corse_id = 3、查询平均成绩⼤于60分的同学的学号和平均成绩；思路：产寻学⽣的id，和成绩，然后⽤avg函数来求得同⼀id号的学⽣平均成绩，并⽤having进⾏成绩的筛选select student_id,avg(number) from score group by student_id having avg(number)>60;增加显⽰学⽣名select student_id,student.sname,avg(number) from score left join student on score.student_id=student.sid group by student_id having avg(number)>60;第⼆种实现⽅式个⼈觉得这种⽅式好理解⼀些，语法结构是先通过select student_id,avg(number) as stu_num from score group by student_id语句分组将数据取出并起临时表别名为SCORE，然后在和student表进⾏连表。

MySQL练习题及答案⼀题⽬1、查询所有的课程的名称以及对应的任课⽼师姓名2、查询学⽣表中男⼥⽣各有多少⼈3、查询物理成绩等于100的学⽣的姓名4、查询平均成绩⼤于⼋⼗分的同学的姓名和平均成绩5、查询所有学⽣的学号，姓名，选课数，总成绩6、查询姓李⽼师的个数7、查询没有报李平⽼师课的学⽣姓名8、查询物理课程⽐⽣物课程⾼的学⽣的学号9、查询没有同时选修物理课程和体育课程的学⽣姓名10、查询挂科超过两门(包括两门)的学⽣姓名和班级、查询选修了所有课程的学⽣姓名12、查询李平⽼师教的课程的所有成绩记录13、查询全部学⽣都选修了的课程号和课程名14、查询每门课程被选修的次数15、查询之选修了⼀门课程的学⽣姓名和学号16、查询所有学⽣考出的成绩并按从⾼到低排序（成绩去重）17、查询平均成绩⼤于85的学⽣姓名和平均成绩18、查询⽣物成绩不及格的学⽣姓名和对应⽣物分数19、查询在所有选修了李平⽼师课程的学⽣中，这些课程(李平⽼师的课程，不是所有课程)平均成绩最⾼的学⽣姓名20、查询每门课程成绩最好的前两名学⽣姓名21、查询不同课程但成绩相同的学号，课程号，成绩22、查询没学过“叶平”⽼师课程的学⽣姓名以及选修的课程名称；23、查询所有选修了学号为1的同学选修过的⼀门或者多门课程的同学学号和姓名；24、任课最多的⽼师中学⽣单科成绩最⾼的学⽣姓名⼆答案#1、查询所有的课程的名称以及对应的任课⽼师姓名SELECTame,teacher.tnameFROMcourseINNER JOIN teacher ON course.teacher_id = teacher.tid;#2、查询学⽣表中男⼥⽣各有多少⼈SELECTgender 性别,count(1) ⼈数FROMstudentGROUP BYgender;#3、查询物理成绩等于100的学⽣的姓名SELECTsid IN (SELECTstudent_idFROMscoreINNER JOIN course ON score.course_id = course.cidWHEREame = '物理'AND score.num = 100);#4、查询平均成绩⼤于⼋⼗分的同学的姓名和平均成绩SELECTstudent.sname,t1.avg_numFROMstudentINNER JOIN (SELECTstudent_id,avg(num) AS avg_numFROMscoreGROUP BYstudent_idHAVINGavg(num) > 80) AS t1 ON student.sid = t1.student_id;#5、查询所有学⽣的学号，姓名，选课数，总成绩(注意：对于那些没有选修任何课程的学⽣也算在内) SELECTstudent.sid,student.sname,t1.course_num,t1.total_numFROMstudentLEFT JOIN (SELECTstudent_id,COUNT(course_id) course_num,sum(num) total_numFROMscoreGROUP BYstudent_id) AS t1 ON student.sid = t1.student_id;#6、查询姓李⽼师的个数SELECTcount(tid)FROMteacherWHEREtname LIKE '李%';#7、查询没有报李平⽼师课的学⽣姓名(找出报名李平⽼师课程的学⽣，然后取反就可以)SELECTstudent.snameFROMstudentWHEREsid NOT IN (SELECT DISTINCTstudent_idFROMscoreWHEREcourse_id IN (INNER JOIN teacher ON course.teacher_id = teacher.tidWHEREteacher.tname = '李平⽼师'));#8、查询物理课程⽐⽣物课程⾼的学⽣的学号(分别得到物理成绩表与⽣物成绩表，然后连表即可)SELECTt1.student_idFROM(SELECTstudent_id,numFROMscoreWHEREcourse_id = (SELECTcidFROMcourseWHEREcname = '物理')) AS t1INNER JOIN (SELECTstudent_id,numFROMscoreWHEREcourse_id = (SELECTcidFROMcourseWHEREcname = '⽣物')) AS t2 ON t1.student_id = t2.student_idWHEREt1.num > t2.num;#9、查询没有同时选修物理课程和体育课程的学⽣姓名(没有同时选修指的是选修了⼀门的，思路是得到物理+体育课程的学⽣信息表，然后基于学⽣分组，统计count(课程)=1) SELECTstudent.snameFROMstudentWHEREsid IN (SELECTstudent_idFROMscoreWHEREcourse_id IN (SELECTcidFROMcourseWHEREcname = '物理'OR cname = '体育')GROUP BYstudent_idHAVINGCOUNT(course_id) = 1);FROMstudentINNER JOIN (SELECTstudent_idFROMscoreWHEREnum < 60GROUP BYstudent_idHAVINGcount(course_id) >= 2) AS t1INNER JOIN class ON student.sid = t1.student_idAND student.class_id = class.cid;#11、查询选修了所有课程的学⽣姓名(先从course表统计课程的总数，然后基于score表按照student_id分组，统计课程数据等于课程总数即可) SELECTstudent.snameFROMstudentWHEREsid IN (SELECTstudent_idFROMscoreGROUP BYstudent_idHAVINGCOUNT(course_id) = (SELECT count(cid) FROM course));#12、查询李平⽼师教的课程的所有成绩记录SELECT*FROMscoreWHEREcourse_id IN (SELECTcidFROMcourseINNER JOIN teacher ON course.teacher_id = teacher.tidWHEREteacher.tname = '李平⽼师');#13、查询全部学⽣都选修了的课程号和课程名(取所有学⽣数，然后基于score表的课程分组，找出count(student_id)等于学⽣数即可) SELECTcid,cnameFROMcourseWHEREcid IN (SELECTcourse_idFROMscoreGROUP BYcourse_idHAVINGCOUNT(student_id) = (SELECTCOUNT(sid)FROMstudent)#14、查询每门课程被选修的次数SELECTcourse_id,COUNT(student_id)FROMscoreGROUP BYcourse_id;#15、查询之选修了⼀门课程的学⽣姓名和学号SELECTsid,snameFROMstudentWHEREsid IN (SELECTstudent_idFROMscoreGROUP BYstudent_idHAVINGCOUNT(course_id) = 1);#16、查询所有学⽣考出的成绩并按从⾼到低排序（成绩去重）SELECT DISTINCTnumFROMscoreORDER BYnum DESC;#17、查询平均成绩⼤于85的学⽣姓名和平均成绩SELECTsname,t1.avg_numFROMstudentINNER JOIN (SELECTstudent_id,avg(num) avg_numFROMscoreGROUP BYstudent_idHAVINGAVG(num) > 85) t1 ON student.sid = t1.student_id;#18、查询⽣物成绩不及格的学⽣姓名和对应⽣物分数SELECTsname 姓名,num ⽣物成绩FROMscoreLEFT JOIN course ON score.course_id = course.cidLEFT JOIN student ON score.student_id = student.sid WHEREame = '⽣物'AND score.num < 60;WHEREsid = (SELECTstudent_idFROMscoreWHEREcourse_id IN (SELECTcourse.cidFROMcourseINNER JOIN teacher ON course.teacher_id = teacher.tidWHEREteacher.tname = '李平⽼师')GROUP BYstudent_idORDER BYAVG(num) DESCLIMIT 1);#20、查询每门课程成绩最好的前两名学⽣姓名#查看每门课程按照分数排序的信息，为下列查找正确与否提供依据SELECT*FROMscoreORDER BYcourse_id,num DESC;#表1：求出每门课程的课程course_id,与最⾼分数first_numSELECTcourse_id,max(num) first_numFROMscoreGROUP BYcourse_id;#表2：去掉最⾼分，再按照课程分组，取得的最⾼分，就是第⼆⾼的分数second_numSELECTscore.course_id,max(num) second_numFROMscoreINNER JOIN (SELECTcourse_id,max(num) first_numFROMscoreGROUP BYcourse_id) AS t ON score.course_id = t.course_idWHEREscore.num < t.first_numGROUP BYcourse_id;#将表1和表2联合到⼀起，得到⼀张表t3，包含课程course_id与该们课程的first_num与second_num SELECTt1.course_id,t1.first_num,t2.second_numFROMscoreGROUP BYcourse_id) AS t1INNER JOIN (SELECTscore.course_id,max(num) second_numFROMscoreINNER JOIN (SELECTcourse_id,max(num) first_numFROMscoreGROUP BYcourse_id) AS t ON score.course_id = t.course_idWHEREscore.num < t.first_numGROUP BYcourse_id) AS t2 ON t1.course_id = t2.course_id;#查询前两名的学⽣（有可能出现并列第⼀或者并列第⼆的情况）SELECTscore.student_id,t3.course_id,t3.first_num,t3.second_numFROMscoreINNER JOIN (SELECTt1.course_id,t1.first_num,t2.second_numFROM(SELECTcourse_id,max(num) first_numFROMscoreGROUP BYcourse_id) AS t1INNER JOIN (SELECTscore.course_id,max(num) second_numFROMscoreINNER JOIN (SELECTcourse_id,max(num) first_numFROMscoreGROUP BYcourse_id) AS t ON score.course_id = t.course_idWHEREscore.num < t.first_numGROUP BYcourse_id) AS t2 ON t1.course_id = t2.course_id) AS t3 ON score.course_id = t3.course_idWHEREscore.num >= t3.second_numAND score.num <= t3.first_num;#排序后可以看的明显点FROMscoreINNER JOIN (SELECTt1.course_id,t1.first_num,t2.second_numFROM(SELECTcourse_id,max(num) first_numFROMscoreGROUP BYcourse_id) AS t1INNER JOIN (SELECTscore.course_id,max(num) second_numFROMscoreINNER JOIN (SELECTcourse_id,max(num) first_numFROMscoreGROUP BYcourse_id) AS t ON score.course_id = t.course_idWHEREscore.num < t.first_numGROUP BYcourse_id) AS t2 ON t1.course_id = t2.course_id) AS t3 ON score.course_id = t3.course_idWHEREscore.num >= t3.second_numAND score.num <= t3.first_numORDER BYcourse_id;#可以⽤以下命令验证上述查询的正确性SELECT*FROMscoreORDER BYcourse_id,num DESC;-- 21、查询不同课程但成绩相同的学号，课程号，成绩-- 22、查询没学过“叶平”⽼师课程的学⽣姓名以及选修的课程名称；-- 23、查询所有选修了学号为1的同学选修过的⼀门或者多门课程的同学学号和姓名；-- 24、任课最多的⽼师中学⽣单科成绩最⾼的学⽣姓名。

mysql数据库考试试题及答案一、选择题（共10题，每题2分）1. 数据库是用来存储数据的______。

A. 软件B. 硬件C. 语言D. 操作系统2. 在MySQL中，一个数据库可以包含多个______。

A. 数据表B. 数据记录C. 数据字段D. 数据行3. 在MySQL中，使用______命令可以查询数据库中的表。

A. INSERTB. SELECTC. DELETED. UPDATE4. MySQL的数据类型中，用于存储货币金额的类型是______。

A. VARCHARB. INTC. DECIMALD. TEXT5. 在MySQL中，以______开头的注释语句会被MySQL解释器忽略。

A. //B. **C. #D. --6. 在MySQL中，使用______关键字可以删除表中的所有数据。

A. DELETEB. DROPC. TRUNCATED. REMOVE7. MySQL中，使用______命令可以将表中的数据按照指定的条件进行排序。

A. SELECTB. ORDER BYC. GROUP BYD. UPDATE8. 在MySQL中，使用______命令可以创建一个新的数据库。

A. CREATEB. INSERTC. UPDATED. DELETE9. 在MySQL中，使用______命令可以修改表的结构。

A. ALTERB. UPDATEC. TRUNCATED. DROP10. 在MySQL中，使用______命令可以将多个表进行连接查询。

A. UNIONB. JOINC. MERGED. LINK二、填空题（共5题，每题4分）1. 在MySQL中，______命令用于创建一个新的数据库。

2. SQL是______查询语言。

3. 在MySQL中，使用______关键字可以对查询结果进行分组。

4. MySQL中，______命令可以删除指定的数据表。

5. 在MySQL中，______函数用来统计指定字段的平均值。

《MySQL数据库应用案例教程》知识考核答案第1章一、填空题1．长期存储在计算机内，有组织的、可共享的2．数据结构、数据操作、数据完整性约束3．硬件、软件、数据库、用户4．层次模型、网状模型、关系模型5．关系模型数据库管理6．数据定义语句（DDL）、数据操作语句（DML）、数据控制语句（DCL）、事务处理语句二、简答题1．略2．略3．略第2章一、填空题1．二进制分发版（.msi文件），免安装版（.zip压缩文件）2．net start mysql，net stop mysql3．mysql –h localhost –u root -p4．RPM包，二进制包，源码包5．service mysqld start;，service mysqld stop;二、简答题略参考文献第3章填空题1．存储数据库对象，系统数据库，用户数据库2．CREATE DATABASE database_name;3．SHOW DATABASES;4．USE database_name;5．DROP DATABASE database_name;第4章一、填空题1．SHOW V ARIABLES LIKE 'default_storage_engine';2．数值类型，日期和时间类型，字符串类型，JSON类型3．显示4．服务器，数据库，数据表，字段5．SHOW V ARIABLES LIKE 'character_set_server';二、简答题1．略2．略3．略第5章填空题1．PRIMARY KEY，AUTO_INCREMENT，NOT NULL，UNIQUE，UNSIGNED，DEFAULT，FOREIGN KEY，2．DESCRIBE table_name;3．SHOW CREATE TABLE table_name \G4．ALTER TABLE table_name CHANGE old_col_name new_col_name data_type;5．ALTER TABLE table_name ADD col_name data_type;第6章填空题1．INSERT，UPDATE，DELETE2．INSERT INTO table_name(col_name1,col_name2,……col_namen)V ALUES(value1,value2,……valuen);3．UPDATE table_nameSET col_name1=value1,col_name2=value2……,col_namen=valuen;4．DELETE FROM table_name WHERE condition;第7章填空题1．SELECT {*|col_list} FROM table_name;2．SELECT col_name1[,col_name2,……,col_namen] FROM table_name;3．SELECT {*|col_list} FROM table_nameWHERE col_name IN (value1,value2,……valuen);4．SELECT {*|col_list} FROM table_name WHERE col_name IS NULL;5．SELECT DISTINCT col_list FROM table_name;6．SELECT {*|col_list} FROM table_nameWHERE col_namea BETWEEN value1 AND value2;7．SELECT {*|col_list} FROM table_name WHERE col_namea LIKE valueb;8．SELECT {*|col_list} FROM table_name ORDER BY col_namea [ASC|DESC];9．SELECT {*|col_list} aggregate_func FROM table_nameGROUP BY col_namea [HA VING condition];10．SELECT {*|col_list} FROM table_name LIMIT [offset_start,]row_count;参考文献第8章填空题1．SELECT {*|col_list} FROM table_name1 INNER JOIN table_name2 ON condition;2．SELECT {*|col_list} FROM table_name1{LEFT|RIGHT} [OUTER] JOIN table_name2 ON condition;3．SELECT {*|col_list} FROM (SELECT * FROM table_name) AS table_alias[WHERE condition];第9章填空题1．72．03．14．4第10章填空题1．ABS(x)2．NOW()3．将多个字符串拼接成为一个字符串4．MAX()5．根据表达式的结果判断返回值6．创建数组形式的JSON值7．INET_ATON(IP)第11章一、填空题1．数据库结构，引用指针2．单个列，多个字段3．CHAR，V ARCHAR，TEXT4．SHOW INDEX FROM Table_name \G5．ALTER TABLE 表名DROP INDEX 索引名;6．DROP INDEX 索引名ON 表名;二、简答题1．略2．略第12章填空题1．CREATE [OR REPLACE] VIEW view_name [(column_list)] AS SELECT_statement;2．DESC，DESC 视图名;3．SHOW CREATE VIEW4．CREATE OR REPLACE VIEW，ALTER5．DROP VIEW第13章填空题1．CREATE PROCEDURE proc_name ( [proc_parameter[,…] )[characteristic…] routine_body2．CREATE FUNCTION func_name ( [ parameter_name [,…] )RETURNS TYPE[characteristic…] routine_body参考文献3．CALL，SELECT4．用户变量，局部变量，系统变量5．DECLARE，OPEN，FETCH，CLOSE6．IF语句，CASE语句，LOOP语句，REPEAT语句，WHILE语句，LEA VE语句7．SHOW {PROCEDURE | FUNCTION} STA TUS [LIKE 'pf_name'] ;8．ALTER {PROCEDURE | FUNCTION} pf_name [characteristic…];9．DROP {PROCEDURE|FUNCTION} [IF EXISTS] pf_name;第14章填空题1．CREATE TRIGGER trigger_name trigger_time trigger_eventON table_name FOR EACH ROW trigger_body2．BEFORE触发器，AFTER触发器3．INSERT型，UPDA TE型，DELETE型4．SHOW TRIGGERS \G第15章一、填空题1．错误日志，二进制日志，通用查询日志，慢查询日志2．log_bin [=path / [filename] ]server_id = 13．general_log=1和general_log=04．slow_query_log=1和slow_query_log=0二、简答题略第16章简答题1．略2．略第17章填空题1．user，db，mysql2．权限，查询，修改，关闭3．GRANT，CREATE USER4．DROP USER5．密码6．mysqladmin，SET7．SET，UPDATE，GRANT8．SHOW GRANTS，SELECT9．GRANT，REVOKE。

mysql练习题及答案（MySQL exercises and answers）MySQL query exercisesThe definition of the Sutdent tableThe field name description data type key non empty only incrementId number INT (10) is whether it isThe name of Name VARCHAR (20) whether or notSex VARCHAR (4) sex no no no no noYear of birth Birth YEAR no no no no noDepartment and VARCHAR (20) whether or notAddress home address: VARCHAR (50) no no no no noThe definition of the Score tableThe field name description data type key non empty only incrementId number INT (10) is whether it isStu_id number INT (10) whether or notC_name VARCHAR (20) no no no no noThe Grade fraction of INT (10) no no no no no1. create student and score tableCREATE TABLE (studentID INT (10) NOT NULL UNIQUE PRIMARY KEY,Name VARCHAR (20) NOT NULL,Sex VARCHAR (4),Birth YEAR,Department VARCHAR (20),Address VARCHAR (50));Create table score. The SQL code is as follows: CREATE TABLE (scoreID INT (10) NOT NULL UNIQUE PRIMARY KEY AUTO_INCREMENT, Stu_id INT (10) NOT NULL,C_name VARCHAR (20),Grade INT (10));2. for the student and score tables add recordTo the student table INSERT statement to insert records as follows:INSERT INTO student (VALUES 901, a 'boss',' male ',' 1985, Department of computer ',' Beijing Haidian District ');INSERT INTO student VALUES (902, Zhang 'Dick', 'male', 1986 ', Chinese', 'Beijing Changping District');INSERT INTO student VALUES (903 ', three', 'female', 1990 ', Chinese', 'Hunan Yongzhou');INSERT INTO student VALUES (904, four 'Lee', 'male', 1990 '', 'the English Department, Liaoning province Fuxin city');INSERT INTO student VALUES (905, five 'King' and 'female', 1991 '', 'the English Department, Fujian province Xiamen city');INSERT INTO student VALUES (906, six 'King', 'male', 1988 '' ', Department of computer, Hunan city of Hengyang province');To the score table INSERT statement to insert records as follows:INSERT INTO score VALUES (NULL, 901, the 'computer', 98);INSERT INTO score VALUES (NULL, 901, 'English', 80); INSERT INTO score VALUES (NULL, 902, the 'computer', 65); INSERT INTO score (VALUES NULL, 902,“中文'，88）；插入评分值（null，903，“中文'，95）；插入评分值（null，904，“计算机'，70）；插入评分值（null，904，“英语'，92）；插入评分值（null，905，“英语'，94）；插入评分值（null，906，“计算机'，90）；插入评分值（null，906，“英语'，85）；3。

MYSQL练习题及标准答案题⽬⼀?问题描述:?为管理岗位业务培训信息,建⽴3个表：S (S#，SＮ，SＤ,SA) Ｓ＃，ＳN,ＳＤ，SA 分别代表学号、学员姓名、所属单位、学员年龄? C (C#，CＮ）Ｃ＃,CN 分别代表课程编号、课程名称?SＣ（Ｓ#,Ｃ#，G ) Ｓ#,C#,G 分别代表学号、所选修的课程编号、学习成绩?1．使⽤标准SQＬ嵌套语句查询选修课程名称为’税收基础’的学员学号和姓名?--实现代码:?SELＥCT SN,S# FROM Ｓ?WHERE ［Ｓ#] IN（? SELECＴ [S#] FRＯM C,SC? WHＥRE Ｃ.[Ｃ#]＝SC．[C#]AＮＤ CN＝N\＇税收基础\')2．使⽤标准ＳQL嵌套语句查询选修课程编号为’C２’的学员姓名和所属单位?-－实现代码:ＳELＥCT S.SN,Ｓ.SD FＲＯM S,SC?WＨERＥＳ.[S＃]=SＣ.[Ｓ#]? AND SC.[C#]＝\'Ｃ2\＇?３．使⽤标准SQL嵌套语句查询不选修课程编号为’C5’的学员姓名和所属单位?-－实现代码：SELECＴ SN,ＳＤ FROM S?WＨERＥ [S#] NOＴ IN(SEＬEＣT [S#] FROM SCWHＥＲE [C#]＝\'C5＼＇)?4. 使⽤标准SQL嵌套语句查询选修全部课程的学员姓名和所属单位－-实现代码:?SELEＣT SＮ,SＤ FROM S?WHＥRＥ [S＃］ IN(SＥLEＣT [S#] FROM SＣRIGHＴ JOIN ?Ｃ ON SＣ.[C#］=C．[C#］ GRＯUP BY ［S#]HＡVＩNG COＵNT（*)=COＵNＴ(［Ｓ＃］）)５. 查询选修了课程的学员⼈数?--实现代码：ＳELECT 学员⼈数=ＣOUNT（DISTINCT [Ｓ＃]) FROM SＣ?6. 查询选修课程超过5门的学员学号和所属单位?--实现代码:? SEＬECT SN,SＤＦROＭ SWHEＲＥ [S＃］ IN(ＳELECT [Ｓ#] FROＭ SC ? GRＯＵP BＹ [S#]HAVINＧＣＯUＮＴ（DＩSTINCＴ [C＃])>５)题⽬⼆?问题描述：本题⽤到下⾯三个关系表：?CARＤ借书卡。

MySql习题和答案MySQL测试题⼀、表关系请创建如下表，并创建相关约束⼆、操作表1、⾃⾏创建测试数据2、查询“⽣物”课程⽐“物理”课程成绩⾼的所有学⽣的学号。

ps：针对的是⾃⼰的⽣物成绩⽐物理成绩⾼,再把符合条件的学⽣的学号查出来；3、查询平均成绩⼤于60分的同学的学号和平均成绩；4、查询所有同学的学号、姓名、选课数、总成绩；5、查询姓“李”的⽼师的个数；6、查询没学过“叶平”⽼师课的同学的学号、姓名；7、查询学过“001”并且也学过编号“002”课程的同学的学号、姓名；8、查询学过“叶平”⽼师所教的所有课的同学的学号、姓名；9、查询课程编号“002”的成绩⽐课程编号“001”课程低的所有同学的学号、姓名；10、查询有课程成绩⼩于60分的同学的学号、姓名；11、查询没有学全所有课的同学的学号、姓名；12、查询⾄少有⼀门课与学号为“001”的同学所学相同的同学的学号和姓名；13、查询⾄少学过学号为“001”同学所选课程中任意⼀门课的其他同学学号和姓名；14、查询和“002”号的同学学习的课程完全相同的其他同学学号和姓名；15、删除学习“叶平”⽼师课的SC表记录；16、向SC表中插⼊⼀些记录，这些记录要求符合以下条件：①没有上过编号“002”课程的同学学号；②插⼊“002”号课程的平均成绩；17、按平均成绩从低到⾼显⽰所有学⽣的“语⽂”、“数学”、“英语”三门的课程成绩，按如下形式显⽰：学⽣ID,语⽂,数学,英语,有效课程数,有效平均分；18、查询各科成绩最⾼和最低的分：以如下形式显⽰：课程ID，最⾼分，最低分；19、按各科平均成绩从低到⾼和及格率的百分数从⾼到低顺序；20、课程平均分从⾼到低显⽰（现实任课⽼师）；21、查询各科成绩前三名的记录:(不考虑成绩并列情况)22、查询每门课程被选修的学⽣数；23、查询出只选修了⼀门课程的全部学⽣的学号和姓名；24、查询男⽣、⼥⽣的⼈数；25、查询姓“张”的学⽣名单；26、查询同名同姓学⽣名单，并统计同名⼈数；27、查询每门课程的平均成绩，结果按平均成绩升序排列，平均成绩相同时，按课程号降序排列；28、查询平均成绩⼤于85的所有学⽣的学号、姓名和平均成绩；29、查询课程名称为“数学”，且分数低于60的学⽣姓名和分数；30、查询课程编号为003且课程成绩在80分以上的学⽣的学号和姓名；31、求选了课程的学⽣⼈数32、查询选修“杨艳”⽼师所授课程的学⽣中，成绩最⾼的学⽣姓名及其成绩；33、查询各个课程及相应的选修⼈数；34、查询不同课程但成绩相同的学⽣的学号、课程号、学⽣成绩；35、查询每门课程成绩最好的前两名；36、检索⾄少选修两门课程的学⽣学号；37、查询全部学⽣都选修的课程的课程号和课程名；38、查询没学过“叶平”⽼师讲授的任⼀门课程的学⽣姓名；39、查询两门以上不及格课程的同学的学号及其平均成绩；40、检索“004”课程分数⼩于60，按分数降序排列的同学学号；41、删除“002”同学的“001”课程的成绩；MySql练习题参考答案/*Navicat Premium Data TransferSource Server : localhostSource Server Type : MySQLSource Server Version : 50624Source Host : localhostSource Database : sqlexamTarget Server Type : MySQLTarget Server Version : 50624File Encoding : utf-8Date: 10/21/2016 06:46:46 AM*/SET NAMES utf8;SET FOREIGN_KEY_CHECKS = 0;-- ------------------------------ Table structure for `class`-- ----------------------------DROP TABLE IF EXISTS `class`;CREATE TABLE `class` (`cid` int(11) NOT NULL AUTO_INCREMENT,`caption` varchar(32) NOT NULL,PRIMARY KEY (`cid`)) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8;-- ------------------------------ Records of `class`-- ----------------------------BEGIN;INSERT INTO `class` VALUES ('1', '三年⼆班'), ('2', '三年三班'), ('3', '⼀年⼆班'), ('4', '⼆年九班');COMMIT;-- ------------------------------ Table structure for `course`-- ----------------------------DROP TABLE IF EXISTS `course`;CREATE TABLE `course` (`cid` int(11) NOT NULL AUTO_INCREMENT,`cname` varchar(32) NOT NULL,`teacher_id` int(11) NOT NULL,PRIMARY KEY (`cid`),KEY `fk_course_teacher` (`teacher_id`),CONSTRAINT `fk_course_teacher` FOREIGN KEY (`teacher_id`) REFERENCES `teacher` (`tid`)) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8;-- ------------------------------ Records of `course`-- ----------------------------BEGIN;INSERT INTO `course` VALUES ('1', '⽣物', '1'), ('2', '物理', '2'), ('3', '体育', '3'), ('4', '美术', '2');COMMIT;-- ------------------------------ Table structure for `score`-- ----------------------------DROP TABLE IF EXISTS `score`;CREATE TABLE `score` (`sid` int(11) NOT NULL AUTO_INCREMENT,`student_id` int(11) NOT NULL,`course_id` int(11) NOT NULL,`num` int(11) NOT NULL,PRIMARY KEY (`sid`),KEY `fk_score_student` (`student_id`),KEY `fk_score_course` (`course_id`),CONSTRAINT `fk_score_course` FOREIGN KEY (`course_id`) REFERENCES `course` (`cid`),CONSTRAINT `fk_score_student` FOREIGN KEY (`student_id`) REFERENCES `student` (`sid`)) ENGINE=InnoDB AUTO_INCREMENT=53 DEFAULT CHARSET=utf8;-- ------------------------------ Records of `score`-- ----------------------------BEGIN;INSERT INTO `score` VALUES ('1', '1', '1', '10'), ('2', '1', '2', '9'), ('5', '1', '4', '66'), ('6', '2', '1', '8'), ('8', '2', '3', '68'), ('9', '2', '4', '99'), ('10', '3', '1', '77'), ('11', '3', '2', '66'), ('12', '3', '3', '87'), ('13', '3', '4', '99'), ('14', '4', '1', '79'), ('15', '4', '2', '11'), ('16', '4', '3', '67'), ('1 COMMIT;-- ------------------------------ Table structure for `student`-- ----------------------------DROP TABLE IF EXISTS `student`;CREATE TABLE `student` (`sid` int(11) NOT NULL AUTO_INCREMENT,`gender` char(1) NOT NULL,`class_id` int(11) NOT NULL,`sname` varchar(32) NOT NULL,PRIMARY KEY (`sid`),KEY `fk_class` (`class_id`),CONSTRAINT `fk_class` FOREIGN KEY (`class_id`) REFERENCES `class` (`cid`)) ENGINE=InnoDB AUTO_INCREMENT=17 DEFAULT CHARSET=utf8;-- ------------------------------ Records of `student`-- ----------------------------BEGIN;INSERT INTO `student` VALUES ('1', '男', '1', '理解'), ('2', '⼥', '1', '钢蛋'), ('3', '男', '1', '张三'), ('4', '男', '1', '张⼀'), ('5', '⼥', '1', '张⼆'), ('6', '男', '1', '张四'), ('7', '⼥', '2', '铁锤'), ('8', '男', '2', '李三'), ('9', '男', '2', '李⼀'), ('10', '⼥', '2', '李⼆'), ('11', '男', '2', '李四'), ( COMMIT;-- ------------------------------ Table structure for `teacher`-- ----------------------------DROP TABLE IF EXISTS `teacher`;CREATE TABLE `teacher` (`tid` int(11) NOT NULL AUTO_INCREMENT,`tname` varchar(32) NOT NULL,PRIMARY KEY (`tid`)) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8;-- ------------------------------ Records of `teacher`-- ----------------------------BEGIN;INSERT INTO `teacher` VALUES ('1', '张磊⽼师'), ('2', '李平⽼师'), ('3', '刘海燕⽼师'), ('4', '朱云海⽼师'), ('5', '李杰⽼师');COMMIT;SET FOREIGN_KEY_CHECKS = 1;/*Navicat Premium Data TransferSource Server : localhostSource Server Type : MySQLSource Server Version : 50624Source Host : localhostSource Database : sqlexamTarget Server Type : MySQLTarget Server Version : 50624File Encoding : utf-8Date: 10/21/2016 06:46:46 AM*/SET NAMES utf8;SET FOREIGN_KEY_CHECKS = 0;-- ------------------------------ Table structure for `class`-- ----------------------------DROP TABLE IF EXISTS `class`;CREATE TABLE `class` (`cid` int(11) NOT NULL AUTO_INCREMENT,`caption` varchar(32) NOT NULL,PRIMARY KEY (`cid`)) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8;-- ------------------------------ Records of `class`-- ----------------------------BEGIN;INSERT INTO `class` VALUES ('1', '三年⼆班'), ('2', '三年三班'), ('3', '⼀年⼆班'), ('4', '⼆年九班');COMMIT;-- ------------------------------ Table structure for `course`-- ----------------------------DROP TABLE IF EXISTS `course`;CREATE TABLE `course` (`cid` int(11) NOT NULL AUTO_INCREMENT,`cname` varchar(32) NOT NULL,`teacher_id` int(11) NOT NULL,PRIMARY KEY (`cid`),KEY `fk_course_teacher` (`teacher_id`),CONSTRAINT `fk_course_teacher` FOREIGN KEY (`teacher_id`) REFERENCES `teacher` (`tid`)) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8;-- ------------------------------ Records of `course`-- ----------------------------BEGIN;INSERT INTO `course` VALUES ('1', '⽣物', '1'), ('2', '物理', '2'), ('3', '体育', '3'), ('4', '美术', '2');COMMIT;-- ------------------------------ Table structure for `score`-- ----------------------------DROP TABLE IF EXISTS `score`;CREATE TABLE `score` (`sid` int(11) NOT NULL AUTO_INCREMENT,`student_id` int(11) NOT NULL,`course_id` int(11) NOT NULL,`num` int(11) NOT NULL,PRIMARY KEY (`sid`),KEY `fk_score_student` (`student_id`),KEY `fk_score_course` (`course_id`),CONSTRAINT `fk_score_course` FOREIGN KEY (`course_id`) REFERENCES `course` (`cid`),CONSTRAINT `fk_score_student` FOREIGN KEY (`student_id`) REFERENCES `student` (`sid`)) ENGINE=InnoDB AUTO_INCREMENT=53 DEFAULT CHARSET=utf8;-- ------------------------------ Records of `score`-- ----------------------------BEGIN;INSERT INTO `score` VALUES ('1', '1', '1', '10'), ('2', '1', '2', '9'), ('5', '1', '4', '66'), ('6', '2', '1', '8'), ('8', '2', '3', '68'), ('9', '2', '4', '99'), ('10', '3', '1', '77'), ('11', '3', '2', '66'), ('12', '3', '3', '87'), ('13', '3', '4', '99'), ('14', '4', '1', '79'), ('15', '4', '2', '11'), ('16', '4', '3', '67'), ('1 COMMIT;-- ------------------------------ Table structure for `student`-- ----------------------------DROP TABLE IF EXISTS `student`;CREATE TABLE `student` (`sid` int(11) NOT NULL AUTO_INCREMENT,`gender` char(1) NOT NULL,`class_id` int(11) NOT NULL,`sname` varchar(32) NOT NULL,PRIMARY KEY (`sid`),KEY `fk_class` (`class_id`),CONSTRAINT `fk_class` FOREIGN KEY (`class_id`) REFERENCES `class` (`cid`)) ENGINE=InnoDB AUTO_INCREMENT=17 DEFAULT CHARSET=utf8;-- ------------------------------ Records of `student`-- ----------------------------BEGIN;INSERT INTO `student` VALUES ('1', '男', '1', '理解'), ('2', '⼥', '1', '钢蛋'), ('3', '男', '1', '张三'), ('4', '男', '1', '张⼀'), ('5', '⼥', '1', '张⼆'), ('6', '男', '1', '张四'), ('7', '⼥', '2', '铁锤'), ('8', '男', '2', '李三'), ('9', '男', '2', '李⼀'), ('10', '⼥', '2', '李⼆'), ('11', '男', '2', '李四'), ( COMMIT;-- ------------------------------ Table structure for `teacher`-- ----------------------------DROP TABLE IF EXISTS `teacher`;CREATE TABLE `teacher` (`tid` int(11) NOT NULL AUTO_INCREMENT,`tname` varchar(32) NOT NULL,PRIMARY KEY (`tid`)) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8;-- ------------------------------ Records of `teacher`-- ----------------------------BEGIN;INSERT INTO `teacher` VALUES ('1', '张磊⽼师'), ('2', '李平⽼师'), ('3', '刘海燕⽼师'), ('4', '朱云海⽼师'), ('5', '李杰⽼师');COMMIT;SET FOREIGN_KEY_CHECKS = 1;表结构和数据练习题答案：2、查询“⽣物”课程⽐“物理”课程成绩⾼的所有学⽣的学号；思路：获取所有有⽣物课程的⼈（学号，成绩） - 临时表获取所有有物理课程的⼈（学号，成绩） - 临时表根据【学号】连接两个临时表：学号物理成绩⽣物成绩然后再进⾏筛选select A.student_id,sw,ty from(select student_id,num as sw from score left join course on score.course_id = course.cid where ame = '⽣物') as Aleft join(select student_id,num as ty from score left join course on score.course_id = course.cid where ame = '体育') as Bon A.student_id = B.student_id where sw > if(isnull(ty),0,ty);3、查询平均成绩⼤于60分的同学的学号和平均成绩；思路：根据学⽣分组，使⽤avg获取平均值，通过having对avg进⾏筛选select student_id,avg(num) from score group by student_id having avg(num) > 604、查询所有同学的学号、姓名、选课数、总成绩；select score.student_id,sum(score.num),count(score.student_id),student.snamefromscore left join student on score.student_id = student.sidgroup by score.student_id5、查询姓“李”的⽼师的个数；select count(tid) from teacher where tname like '李%'select count(1) from (select tid from teacher where tname like '李%') as B6、查询没学过“叶平”⽼师课的同学的学号、姓名；思路：先查到“李平⽼师”⽼师教的所有课ID获取选过课的所有学⽣ID学⽣表中筛选select * from student where sid not in (select DISTINCT student_id from score where score.course_id in (select cid from course left join teacher on course.teacher_id = teacher.tid where tname = '李平⽼师'))7、查询学过“001”并且也学过编号“002”课程的同学的学号、姓名；思路：先查到既选择001⼜选择002课程的所有同学根据学⽣进⾏分组，如果学⽣数量等于2表⽰，两门均已选择select student_id,sname from(select student_id,course_id from score where course_id = 1 or course_id = 2) as Bleft join student on B.student_id = student.sid group by student_id HAVING count(student_id) > 18、查询学过“叶平”⽼师所教的所有课的同学的学号、姓名；同上，只不过将001和002变成 in (叶平⽼师的所有课)9、查询课程编号“002”的成绩⽐课程编号“001”课程低的所有同学的学号、姓名；同第1题10、查询有课程成绩⼩于60分的同学的学号、姓名；select sid,sname from student where sid in (select distinct student_id from score where num < 60)11、查询没有学全所有课的同学的学号、姓名；思路：在分数表中根据学⽣进⾏分组，获取每⼀个学⽣选课数量如果数量 == 总课程数量，表⽰已经选择了所有课程select student_id,snamefrom score left join student on score.student_id = student.sidgroup by student_id HAVING count(course_id) = (select count(1) from course)12、查询⾄少有⼀门课与学号为“001”的同学所学相同的同学的学号和姓名；思路：获取 001 同学选择的所有课程获取课程在其中的所有⼈以及所有课程根据学⽣筛选，获取所有学⽣信息再与学⽣表连接，获取姓名select student_id,sname, count(course_id)from score left join student on score.student_id = student.sidwhere student_id != 1 and course_id in (select course_id from score where student_id = 1) group by student_id13、查询⾄少学过学号为“001”同学所有课的其他同学学号和姓名；先找到和001的学过的所有⼈然后个数＝ 001所有学科＝＝》其他⼈可能选择的更多select student_id,sname, count(course_id)from score left join student on score.student_id = student.sidwhere student_id != 1 and course_id in (select course_id from score where student_id = 1) group by student_id having count(course_id) ＝ (select count(course_id) from score where student_id = 1) 14、查询和“002”号的同学学习的课程完全相同的其他同学学号和姓名；个数相同002学过的也学过select student_id,sname from score left join student on score.student_id = student.sid where student_id in (select student_id from score where student_id != 1 group by student_id HAVING count(course_id) = (select count(1) from score where student_id = 1)) and course_id in (select course_id from score where student_id = 1) group by student_id HAVING count(course_id) = (select count(1) from score where student_id = 1)15、删除学习“叶平”⽼师课的score表记录；delete from score where course_id in (select cid from course left join teacher on course.teacher_id = teacher.tid where = '叶平')16、向SC表中插⼊⼀些记录，这些记录要求符合以下条件：①没有上过编号“002”课程的同学学号；②插⼊“002”号课程的平均成绩；思路：由于insert ⽀持inset into tb1(xx,xx) select x1,x2 from tb2;所有，获取所有没上过002课的所有⼈，获取002的平均成绩insert into score(student_id, course_id, num) select sid,2,(select avg(num) from score where course_id = 2)from student where sid not in (select student_id from score where course_id = 2)17、按平均成绩从低到⾼显⽰所有学⽣的“语⽂”、“数学”、“英语”三门的课程成绩，按如下形式显⽰：学⽣ID,语⽂,数学,英语,有效课程数,有效平均分；select sc.student_id,(select num from score left join course on score.course_id = course.cid where ame = "⽣物" and score.student_id=sc.student_id) as sy,(select num from score left join course on score.course_id = course.cid where ame = "物理" and score.student_id=sc.student_id) as wl,(select num from score left join course on score.course_id = course.cid where ame = "体育" and score.student_id=sc.student_id) as ty,count(sc.course_id),avg(sc.num)from score as scgroup by student_id desc18、查询各科成绩最⾼和最低的分：以如下形式显⽰：课程ID，最⾼分，最低分；select course_id, max(num) as max_num, min(num) as min_num from score group by course_id;19、按各科平均成绩从低到⾼和及格率的百分数从⾼到低顺序；思路：case when .. thenselect course_id, avg(num) as avgnum,sum(case when score.num > 60 then 1 else 0 END)/count(1)*100 as percent from score group by course_id order by avgnum asc,percent desc;20、课程平均分从⾼到低显⽰（现实任课⽼师）；select avg(if(isnull(score.num),0,score.num)),teacher.tname from courseleft join score on course.cid = score.course_idleft join teacher on course.teacher_id = teacher.tidgroup by score.course_id21、查询各科成绩前三名的记录:(不考虑成绩并列情况)select score.sid,score.course_id,score.num,T.first_num,T.second_num from score left join(selectsid,(select num from score as s2 where s2.course_id = s1.course_id order by num desc limit 0,1) as first_num,(select num from score as s2 where s2.course_id = s1.course_id order by num desc limit 3,1) as second_numfromscore as s1) as Ton score.sid =T.sidwhere score.num <= T.first_num and score.num >= T.second_num22、查询每门课程被选修的学⽣数；select course_id, count(1) from score group by course_id;23、查询出只选修了⼀门课程的全部学⽣的学号和姓名；select student.sid, student.sname, count(1) from scoreleft join student on score.student_id = student.sidgroup by course_id having count(1) = 124、查询男⽣、⼥⽣的⼈数；select * from(select count(1) as man from student where gender='男') as A ,(select count(1) as feman from student where gender='⼥') as B25、查询姓“张”的学⽣名单；select sname from student where sname like '张%';26、查询同名同姓学⽣名单，并统计同名⼈数；select sname,count(1) as count from student group by sname;27、查询每门课程的平均成绩，结果按平均成绩升序排列，平均成绩相同时，按课程号降序排列；select course_id,avg(if(isnull(num), 0 ,num)) as avg from score group by course_id order by avg asc,course_id desc;28、查询平均成绩⼤于85的所有学⽣的学号、姓名和平均成绩；select student_id,sname, avg(if(isnull(num), 0 ,num)) from score left join student on score.student_id = student.sid group by student_id;29、查询课程名称为“数学”，且分数低于60的学⽣姓名和分数；select student.sname,score.num from scoreleft join course on score.course_id = course.cidleft join student on score.student_id = student.sidwhere score.num < 60 and ame = '⽣物'30、查询课程编号为003且课程成绩在80分以上的学⽣的学号和姓名；select * from score where score.student_id = 3 and score.num > 8031、求选了课程的学⽣⼈数select count(distinct student_id) from scoreselect count(c) from (select count(student_id) as c from score group by student_id) as A32、查询选修“杨艳”⽼师所授课程的学⽣中，成绩最⾼的学⽣姓名及其成绩；select sname,num from scoreleft join student on score.student_id = student.sidwhere score.course_id in (select course.cid from course left join teacher on course.teacher_id = teacher.tid where tname='张磊⽼师') order by num desc limit 1;33、查询各个课程及相应的选修⼈数；select ame,count(1) from scoreleft join course on score.course_id = course.cidgroup by course_id;34、查询不同课程但成绩相同的学⽣的学号、课程号、学⽣成绩；select DISTINCT s1.course_id,s2.course_id,s1.num,s2.num from score as s1, score as s2 where s1.num = s2.num and s1.course_id != s2.course_id;35、查询每门课程成绩最好的前两名；select score.sid,score.course_id,score.num,T.first_num,T.second_num from score left join(selectsid,(select num from score as s2 where s2.course_id = s1.course_id order by num desc limit 0,1) as first_num,(select num from score as s2 where s2.course_id = s1.course_id order by num desc limit 1,1) as second_numfromscore as s1) as Ton score.sid =T.sidwhere score.num <= T.first_num and score.num >= T.second_num36、检索⾄少选修两门课程的学⽣学号；select student_id from score group by student_id having count(student_id) > 137、查询全部学⽣都选修的课程的课程号和课程名；select course_id,count(1) from score group by course_id having count(1) = (select count(1) from student);38、查询没学过“叶平”⽼师讲授的任⼀门课程的学⽣姓名；select student_id,student.sname from scoreleft join student on score.student_id = student.sidwhere score.course_id not in (select cid from course left join teacher on course.teacher_id = teacher.tid where tname = '张磊⽼师')group by student_id39、查询两门以上不及格课程的同学的学号及其平均成绩；select student_id,count(1) from score where num < 60 group by student_id having count(1) > 240、检索“004”课程分数⼩于60，按分数降序排列的同学学号；select student_id from score where num< 60 and course_id = 4 order by num desc;41、删除“002”同学的“001”课程的成绩；delete from score where course_id = 1 and student_id = 2。