关于mysql练习题的及答案

MySQL练习题及答案一、基础知识篇1.1数据库操作1.创建一个名为`student`的数据库。2.删除名为`teacher`的数据库。3.显示当前MySQL服务器上所有数据库的列表。4.使用`student`数据库。1.2表操作1.在`student`数据库中创建一个名为`student_info`的表，包含字段：`id`（整数型，主键，自增），`name`（字符串型，非空），`age`（整数型，非空），`gender`（字符串型，非空）。2.删除`student`数据库中的`course`表。3.修改`student_info`表，增加一个字段`class_name`（字符串型，非空）。4.显示`student_info`表的结构。1.3数据操作1.向`student_info`表中插入一条记录，姓名为，年龄为20，性别为男，班级为计算机科学与技术1班。2.更新`student_info`表中姓名为的记录，将其年龄改为22。3.删除`student_info`表中姓名为的记录。4.查询`student_info`表中所有学生的姓名和年龄。二、进阶知识篇2.1约束条件1.在`student_info`表中为`age`字段添加约束，使其值在18到30之间。2.为`student_info`表的`name`字段添加唯一约束。3.创建一个名为`teacher_info`的表，包含字段：`id`（整数型，主键，自增），`name`（字符串型，非空），`course_id`（整数型，外键，关联`course`表的`id`字段）。2.2数据查询1.查询`student_info`表中年龄大于20岁的学生姓名和班级。2.查询`student_info`表中男女生各有多少人。3.查询`student_info`表中最年轻的学生姓名和年龄。4.查询`student_info`表中每个班级的平均年龄。2.3连接查询1.查询`student_info`表和`course`表中的学生姓名、课程名称和成绩。2.查询`teacher_info`表和`course`表中的教师姓名和所教授的课程名称。3.查询`student_info`表和`teacher_info`表中的学生姓名、教师姓名和课程名称。三、高级知识篇3.1事务处理1.编写一个事务，实现学生选课功能，包括插入学生选课记录和更新课程剩余名额。2.编写一个事务，实现学生退课功能，包括删除学生选课记录和更新课程剩余名额。3.2视图和索引1.创建一个视图，显示`student_info`表中年龄大于20岁的学生信息。2.为`student_info`表的`name`字段创建索引。3.查询`student_info`表中的学生姓名，并按照年龄升序排序。3.3存储过程和函数1.编写一个存储过程，实现根据学生姓名查询其所在班级的功能。2.编写一个函数，计算两个整数之和。四、数据库安全篇4.1用户管理1.创建一个名为`admin_user`的新用户，并为其设置密码。2.授予`admin_user`用户对所有数据库的查询权限。3.撤销`admin_user`用户对所有数据库的删除权限。4.显示所有用户的权限信息。4.2备份与恢复1.使用`mysqldump`工具备份`student`数据库。2.将备份的`student`数据库恢复到MySQL服务器。3.编写一个定时任务，每天凌晨1点自动备份所有数据库。五、性能优化篇5.1查询优化2.对`student_info`表进行索引优化，以提高查询效率。3.使用`EXPLN`语句查看`JOIN`查询的性能瓶颈。5.2数据库配置优化1.调整MySQL的`innodb_buffer_pool_size`参数，以优化数据库性能。2.配置MySQL的`max_connections`参数，以处理更多的并发连接。3.修改MySQL的日志配置，以记录慢查询日志。六、触发器和事件调度器篇6.1触发器1.创建一个触发器，当向`student_info`表插入新记录时，自动检查年龄是否在合法范围内。2.创建一个触发器，当更新`student_info`表中的`age`字段时，记录更新前后的年龄值。6.2事件调度器1.创建一个事件，每天定时清理`student_info`表中超过30岁的学生记录。2.创建一个事件，每周一统计`student_info`表中各班级的学生人数。七、MySQL特性篇7.1JSON数据类型1.创建一个包含JSON字段的学生信息表，并插入一条包含JSON数据的记录。2.查询JSON字段中的特定数据。7.2全文搜索1.为`student_info`表的`name`字段创建全文索引。2.使用全文搜索功能查询包含“技术”关键词的学生姓名。7.3空间数据类型1.创建一个包含空间数据类型的表，用于存储地理坐标。2.查询距离某个坐标点最近的记录。答案：一、基础知识篇1.1数据库操作1.CREATEDATABASEstudent;2.DROPDATABASEteacher;3.SHOWDATABASES;4.USEstudent;1.2表操作1.CREATETABLEstudent_info(idINTPRIMARYKEYAUTO_INCREMENT,nameVARCHAR(50)NOTNULL,ageINTNOTNULL,genderVARCHAR(10)NOTNULL);2.DROPTABLEcourse;3.ALTERTABLEstudent_infoADDclass_nameVARCHAR(50)NOTNULL;4.DESCstudent_info;1.3数据操作1.INSERTINTOstudent_info(name,age,gender,class_name)VALUES('',20,'男','计算机科学与技术1班');2.UPDATEstudent_infoSETage=22WHEREname='';3.DELETEFROMstudent_infoWHEREname='';4.SELECTname,ageFROMstudent_info;二、进阶知识篇2.1约束条件1.ALTERTABLEstudent_infoADDCHECK(ageBETWEEN18AND30);2.ALTERTABLEstudent_infoADDUNIQUE(name);3.CREATETABLEteacher_info(idINTPRIMARYKEYAUTO_INCREMENT,nameVARCHAR(50)NOTNULL,course_idINT,FOREIGNKEY(course_id)REFERENCEScourse(id));2.2数据查询1.SELECTname,class_nameFROMstudent_infoWHEREage>20;2.SELECTgender,COUNT()FROMstudent_infoGROUPBYgender;3.SELECTname,ageFROMstudent_infoORDERBYageASCLIMIT1;4.SELECTclass_name,AVG(age)FROMstudent_infoGROUPBYclass_name;2.3连接查询1.SELECT,c.course_name,sc.scoreFROMstudent_infosJOINcoursecONs.course_id=c.idJOINstudent_coursescONs.id=sc.student_id;2.SELECT,c.course_nameFROMteacher_infotJOINcoursecONt.course_id=c.id;3.SELECTASstudent_name,ASteacher_name,c.course_nameFROMstudent_infosJOINteacher_infotONs.class_name=t.class_nameJOINcoursecONt.course_id=c.id;三、高级知识篇3.1事务处理1.STARTTRANSACTION;INSERTINTOstudent_course(student_id,course_id)VALUES(1,2);UPDATEcourseSETremaining_seats=remaining_seats1WHEREid=2;COMMIT;2.STARTTRANSACTION;DELETEFROMstudent_courseWHEREstudent_id=1ANDcourse_id=2;UPDATEcourseSETremaining_seats=remaining_seats+1WHEREid=2;COMMIT;3.2视图和索引1.CREATEVIEWview_student_infoASSELECTFROMstudent_infoWHEREage>20;2.CREATEINDEXidx_nameONstudent_info(name);3.SELECTnameFROMstudent_infoORDERBYageASC;3.3存储过程和函数1.DELIMITER//CREATEPROCEDUREGetClassByName(INstudent_nameVARCHAR(50))BEGINSELECTclass_nameFROMstudent_infoWHEREname=student_name;END//DELIMITER;2.DELIMITER//CREATEFUNCTIONAddTwoNumbers(aINT,bINT)RETURNSINTBEGINRETURNa+b;END//DELIMITER;四、数据库安全篇4.1用户管理1.CREATEUSER'admin_user''localhost'IDENTIFIEDBY'password';2.GRANTSELECTON.TO'admin_user''localhost';3.REVOKEDELETEON.FROM'admin_user''localhost';4.SHOWGRANTSFOR'admin_user''localhost';4.2备份与恢复1.mysqldumpuusernamepstudent>student_backup.sql2.mysqluusernamepstudent<student_backup.sql3.crontabe01/usr/bin/mysqldumpuusernameppasswordalldatabases>/path/to/backup/all_databases_backup.sql五、性能优化篇5.1查询优化1.EXPLNSELECTFROMstudent_infoWHEREage>20;（根据实际情况进