一.面试题
创建模拟数据库
- 1.学生表-t_mysql_student
-- sid 学生编号,sname 学生姓名,sage 学生年龄,ssex 学生性别
-- 2.教师表-t_mysql_teacher
-- tid 教师编号,tname 教师名称
-- 3.课程表-t_mysql_course
-- cid 课程编号,cname 课程名称,tid 教师名称
-- 4.成绩表-t_mysql_score
-- sid 学生编号,cid 课程编号,score 成绩
学生表t_mysql_student
insert into t_mysql_student values('01' , '赵雷' , '1990-01-01' , '男'); insert into t_mysql_student values('02' , '钱电' , '1990-12-21' , '男'); insert into t_mysql_student values('03' , '孙风' , '1990-12-20' , '男'); insert into t_mysql_student values('04' , '李云' , '1990-12-06' , '男'); insert into t_mysql_student values('05' , '周梅' , '1991-12-01' , '女'); insert into t_mysql_student values('06' , '吴兰' , '1992-01-01' , '女'); insert into t_mysql_student values('07' , '郑竹' , '1989-01-01' , '女'); insert into t_mysql_student values('09' , '张三' , '2017-12-20' , '女'); insert into t_mysql_student values('10' , '李四' , '2017-12-25' , '女'); insert into t_mysql_student values('11' , '李四' , '2012-06-06' , '女'); insert into t_mysql_student values('12' , '赵六' , '2013-06-13' , '女'); insert into t_mysql_student values('13' , '孙七' , '2014-06-01' , '女');
教师表-t_mysql_teacher
insert into t_mysql_teacher values('01' , '张三'); insert into t_mysql_teacher values('02' , '李四'); insert into t_mysql_teacher values('03' , '王五');
课程表-t_mysql_course
insert into t_mysql_course values('01' , '语文' , '02'); insert into t_mysql_course values('02' , '数学' , '01'); insert into t_mysql_course values('03' , '英语' , '03');
成绩表-t_mysql_score
insert into t_mysql_score values('01' , '01' , 80); insert into t_mysql_score values('01' , '02' , 90); insert into t_mysql_score values('01' , '03' , 99); insert into t_mysql_score values('02' , '01' , 70); insert into t_mysql_score values('02' , '02' , 60); insert into t_mysql_score values('02' , '03' , 80); insert into t_mysql_score values('03' , '01' , 80); insert into t_mysql_score values('03' , '02' , 80); insert into t_mysql_score values('03' , '03' , 80); insert into t_mysql_score values('04' , '01' , 50); insert into t_mysql_score values('04' , '02' , 30); insert into t_mysql_score values('04' , '03' , 20); insert into t_mysql_score values('05' , '01' , 76); insert into t_mysql_score values('05' , '02' , 87); insert into t_mysql_score values('06' , '01' , 31); insert into t_mysql_score values('06' , '03' , 34); insert into t_mysql_score values('07' , '02' , 89); insert into t_mysql_score values('07' , '03' , 98);
面试题讲解
01)查询" 01 "课程比" 02 "课程成绩高的学生的信息及课程分数
查询语句:
SELECT st.*, s1.score '01课程', s2.score '02课程' FROM ( SELECT * FROM t_mysql_score WHERE cid = "01" ) s1, ( SELECT * FROM t_mysql_score WHERE cid = "02" ) s2, t_mysql_student st WHERE s1.sid = s2.sid AND s1.sid = st.sid AND s1.score > s2.score
02)查询同时存在" 01 "课程和" 02 "课程的情况
查询语句:
SELECT s.*, ( CASE WHEN s1.cid = "01" THEN s1.score END ) '01课程', ( CASE WHEN s2.cid = "02" THEN s1.score END ) '02课程' FROM ( SELECT * FROM t_mysql_score WHERE cid = "01" ) s1, ( SELECT * FROM t_mysql_score WHERE cid = "02" ) s2, t_mysql_student s WHERE s1.sid = s2.sid AND s1.sid = s.sid
03)查询存在" 01 "课程但可能不存在" 02 "课程的情况(不存在时显示为 null )
查询语句:
SELECT s1.sid, s1.cid, s1.score '01课程', s2.score '02课程' FROM ( SELECT * FROM t_mysql_score sc WHERE sc.cid = "01" ) s1 LEFT JOIN ( SELECT * FROM t_mysql_score sc WHERE sc.cid = "02" ) s2 ON s1.sid = s2.sid
04)查询不存在" 01 "课程但存在" 02 "课程的情况
查询语句:
SELECT * FROM t_mysql_score WHERE sid IN ( SELECT sid FROM t_mysql_student WHERE sid NOT IN ( SELECT sid FROM t_mysql_score WHERE cid = '01' ) ) AND cid = '02'
05)查询平均成绩大于等于 60 分的同学的学生编号和学生姓名和平均成绩
查询语句:
SELECT s.sid, s.sname, ROUND( AVG( c.score ), 2 ) '平均成绩' FROM t_mysql_student s, t_mysql_score c WHERE s.sid = c.sid GROUP BY s.sid, s.sname HAVING avg( c.score ) >= 60
06)查询在t_score表存在成绩的学生信息
查询语句:
SELECT s.* FROM t_mysql_score sc, t_mysql_student s WHERE sc.sid = s.sid GROUP BY s.sid
07)查询所有同学的学生编号、学生姓名、选课总数、所有课程的总成绩(没成绩的显示为 null )
查询语句:
SELECT s.sid, s.sname, count( sc.cid ) '选课总数', sum( sc.score ) '总成绩' FROM t_mysql_student s, t_mysql_score sc WHERE s.sid = sc.sid GROUP BY s.sid, s.sname
08)查询「李」姓老师的数量
查询语句:
SELECT * FROM t_mysql_teacher t WHERE tname LIKE '李%'
09)查询学过「张三」老师授课的同学的信息
查询语句:
SELECT * FROM t_mysql_student WHERE sid IN ( SELECT sc.sid FROM t_mysql_teacher t, t_mysql_score sc, t_mysql_course c WHERE t.tid = c.tid AND sc.cid = c.cid AND t.tname = "张三" GROUP BY sc.sid)
10)查询没有学全所有课程的同学的信息
查询语句:
SELECT s.*, count( sc.score ) '课程数量' FROM t_mysql_student s, t_mysql_score sc WHERE s.sid = sc.sid GROUP BY s.sid, s.sname HAVING count( sc.score ) < ( SELECT count( 1 ) FROM t_mysql_course )
11)查询没学过"张三"老师讲授的任一门课程的学生姓名
查询语句:
SELECT sname FROM t_mysql_student WHERE sid NOT IN ( SELECT sc.sid FROM t_mysql_score sc, t_mysql_course c, t_mysql_teacher t WHERE t.tid = c.tid AND sc.cid = c.cid AND t.tname = "张三" )
12)查询两门及其以上不及格课程的同学的学号,姓名及其平均成绩
查询语句:
SELECT s.sid, s.sname, ROUND( AVG( sc.score ), 2 ) 平均成绩 FROM t_mysql_student s, t_mysql_score sc WHERE sc.sid = s.sid AND sc.score < 60 GROUP BY s.sid, s.sname HAVING count( sc.score ) >= 2
13)检索" 01 "课程分数小于 60,按分数降序排列的学生信息
查询语句:
SELECT s.*, sc.score FROM t_mysql_student s, t_mysql_score sc WHERE s.sid = sc.sid AND sc.cid = "01" AND sc.score < 60 ORDER BY sc.score DESC
14)按平均成绩从高到低显示所有学生的所有课程的成绩以及平均成绩
查询语句:
SELECT s.sid, s.sname, IF ( max( CASE WHEN sc.cid = '01' THEN sc.score END ) > 0, max( CASE WHEN sc.cid = '01' THEN sc.score END ), 0 ) 语文, IF ( max( CASE WHEN sc.cid = '02' THEN sc.score END ) > 0, max( CASE WHEN sc.cid = '02' THEN sc.score END ), 0 ) 数学, IF ( max( CASE WHEN sc.cid = '03' THEN sc.score END ) > 0, max( CASE WHEN sc.cid = '03' THEN sc.score END ), 0 ) 英语, ROUND( AVG( sc.score ), 2 ) 平均成绩 FROM t_mysql_student s, t_mysql_score sc WHERE s.sid = sc.sid GROUP BY s.sid, s.sname
15)查询各科成绩最高分、最低分和平均分:
以如下形式显示:课程 ID,课程 name,最高分,最低分,平均分,及格率,中等率,优良率,优秀率及格为>=60,中等为:70-80,优良为:80-90,优秀为:>=90
要求输出课程号和选修人数,查询结果按人数降序排列,若人数相同,按课程号升序排列查询语句:
SELECT c.cid, c.cname, max( sc.score ) '最高分', min( sc.score ) '最低分', ROUND( AVG( sc.score ), 2 ) '平均分', CONCAT(ROUND(sum( IF ( sc.score >= 60, 1, 0 ) ) / COUNT( sc.score ) * 100,2 ),'%' ) 及格率, CONCAT(ROUND(sum( IF ( sc.score >= 70 AND sc.score < 80, 1, 0 ) ) / COUNT( sc.score ) * 100,2 ),'%' ) 中等率, CONCAT(ROUND(sum( IF ( sc.score >= 80 AND sc.score < 90, 1, 0 ) ) / COUNT( sc.score ) * 100,2 ),'%' ) 优良率, CONCAT(ROUND(sum( IF ( sc.score >= 90, 1, 0 ) ) / COUNT( sc.score ) * 100,2 ),'%' ) 优秀率, COUNT( sc.score ) 选修人数 FROM t_mysql_score sc, t_mysql_course c WHERE sc.cid = c.cid GROUP BY sc.cid ORDER BY 选修人数 DESC, c.cid ASC;
知识点行转列
在MySQL中将行转列是通过使用聚合函数和条件判断来实现的。具体步骤如下:
1. 创建一个新的查询,使用聚合函数和条件判断来转换行为列。例如,使用CASE语句将不同的行值转换为列值。
sql SELECT MAX(CASE WHEN row_type = 'row1' THEN row_value END) AS column1, MAX(CASE WHEN row_type = 'row2' THEN row_value END) AS column2, MAX(CASE WHEN row_type = 'row3' THEN row_value END) AS column3 FROM your_table
在上面的示例中,假设你要将行类型(row_type)为'row1'、'row2'和'row3'的行值(row_value)转换为列(column1、column2和column3)。
2. 使用GROUP BY子句,根据需要对结果进行分组。如果不需要分组,则可以省略这一步。
sql SELECT grouping_column, MAX(CASE WHEN row_type = 'row1' THEN row_value END) AS column1, MAX(CASE WHEN row_type = 'row2' THEN row_value END) AS column2, MAX(CASE WHEN row_type = 'row3' THEN row_value END) AS column3 FROM your_table GROUP BY grouping_column
在这个示例中,假设你要根据一个分组列(grouping_column)对行进行分组。
请注意,上述示例中的"your_table"应替换为你的实际表名,"row_type"和"row_value"应替换为你的实际列名。
这种方式可以将行转换为列。然而,这不是MySQL的特定功能,而是使用SQL语言的功能来实现的。具体实现可能会因数据库结构和需求而有所不同,你需要根据自己的情况进行适当的调整
使用工具Navica
Navicat是一个流行的数据库管理工具,提供了各种功能,用于连接、管理和维护不同类型的数据库,如MySQL、PostgreSQL、Oracle等。Navicat具有直观的用户界面和丰富的功能,使数据库管理变得更加简单和高效。
下面是一些Navicat的常见功能:
1. 连接数据库:
Navicat允许你连接到多个数据库,并提供了多种连接选项,包括本地或远程服务器、SSH隧道、SSL等。
2. 数据库管理:
你可以创建、修改和删除数据库、表、视图、索引、存储过程等。Navicat还提供了数据导入/导出功能,可以方便地将数据从一个数据库迁移到另一个数据库。
3. SQL开发:
Navicat提供了一个强大的SQL编辑器,支持代码自动完成、语法高亮、语法检查等功能。你可以方便地编写和执行SQL查询、脚本和存储过程。
4. 数据可视化:
Navicat提供了直观的用户界面和数据可视化工具,可以帮助你浏览和编辑数据。你可以使用数据网格、表格视图和图表来分析和操作数据。
5. 数据同步和备份:
Navicat支持数据同步和备份功能,可以帮助你在不同的数据库之间同步数据,并定期备份数据库以保护数据安全。
6. 安全管理:
Navicat提供了用户和权限管理功能,可以控制用户对数据库的访问权限,并管理数据库对象的安全性。
Navicat还有许多其他功能,如数据建模、任务计划、数据转换等。它是一个功能强大、易于使用的数据库管理工具,广泛用于开发人员、数据库管理员和数据分析师等角色。