试题整理:"/>
mysql 知识和面试题整理:
sql知识点:
SQL基础知识整理:
select 查询结果 如: [学号,平均成绩:组函数avg(成绩)]
from 从哪张表中查找数据 如:[涉及到成绩:成绩表score]
where 查询条件 如:[b.课程号='0003' and b.成绩>80]
group by 分组 如:[每个学生的平均:按学号分组](oracle,SQL server中出现在select 子句后的非分组函数,必须出现 在group by子句后出现),MySQL中可以不用
having 对分组结果指定条件 如:[大于60分]
order by 对查询结果排序 如:[增序: 成绩 ASC / 降序: 成绩 DESC];
limit 使用limt子句返回topN(对应这个问题返回的成绩前两名)如:[ limit 2 ==>从0索引开始读取2个]
limit==>从0索引开始 [0,N-1]
组函数: 去重 distinct() 统计总数sum() 计算个数count() 平均数avg() 最大值max() 最小数min()
多表连接: 内连接(省略默认inner) join ...on..左连接left join tableName as b on a.key ==b.key右连接right join 连接union(无重复(过滤去重))和union all(有重复[不过滤去重])
面试题
简单 例题sql:
查询姓王的同学
查询最后一个字是王的同学
查询姓名中带王的同学
查询选了课程2的总成绩
查询选了课程2的总人数
查询各个课程的总成绩
查询各个课程的总人数
查询各科成绩最高分和最低分
查询每门课程的选修人数
查询两门以上不及格课程的同学的学号及其平均成绩
查询所有课程成绩小于60分学生的学号、姓名
写一条sql语句,检索出学生的各科成绩,要求每一位学生只有一条记录,若学科没有城市,显示NULL
查询学过语文且学过数学课程的同学信息
查询两门及以上不及格的同学的学号,姓名,平均成绩
答案
查询姓王的同学
SELECT * FROM student WHERE `name` LIKE "王%"
查询最后一个字是王的同学
SELECT * FROM student WHERE `name` LIKE "%王"
查询姓名中带王的同学
SELECT * FROM student WHERE `name` LIKE "%王%"
查询选了课程2的总成绩
select sum(score) as tatal from score where cid=2
查询选了课程2的总人数
select count(cid) from score GROUP BY cid;
查询各个课程的总成绩
select cid,sum(score) as tatal from score GROUP BY cid
查询各个课程的总人数
select cid,count(*) as tatal from score GROUP BY cid
查询各科成绩最高分和最低分
SELECT cid, max(score) as '最高分',min(score) as '最低分' FROM `score` GROUP BY cid
查询每门课程的选修人数
SELECT cid,count(*) FROM score GROUP BY cid
查询男生女生人数
SELECT sex, count(*) FROM student GROUP BY sex
select sid,avg(score) score from score group by sid having score >60
select sid,count(cid) 课程 from score group by sid having 课程 >2、
写一条sql语句,检索出学生的各科成绩,要求每一位学生只有一条记录,若学科没有城市,显示NULL
SELECT s.NAME,s.id,GROUP_CONCAT( cid,":",score order by cid asc separator "," ) as "result" FROM `student ` s
left JOIN score on s.id= score.sid GROUP BY s.id ORDER BY s.`name` desc
查询学过语文且学过数学课程的同学信息
select id, name from student where id in (select sid from score where (cid=1 and score>0) or (cid=2 and score >0))
查询两门及以上不及格的同学的学号,姓名,平均成绩:
SELECT
s.NAME,
s.id,
avg( score ) AS avg,
count( * ) AS total
FROM
student s
LEFT JOIN score ON s.id = score.sid
WHERE
score.score < 60 GROUP BY sid HAVING total >=2
表结构:
DROP TABLE IF EXISTS `course`;
CREATE TABLE `course` (`cid` int(10) NOT NULL,`cname` char(50) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,`tid` int(10) NULL DEFAULT NULL,PRIMARY KEY (`cid`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Compact;-- ----------------------------
-- Records of course
-- ----------------------------
INSERT INTO `course` VALUES (1, '语文', 2);
INSERT INTO `course` VALUES (2, '数学', 1);
INSERT INTO `course` VALUES (3, '英语', 3);-- ----------------------------
-- Table structure for score
-- ----------------------------
DROP TABLE IF EXISTS `score`;
CREATE TABLE `score` (`sid` int(10) NULL DEFAULT NULL,`cid` int(10) NULL DEFAULT NULL,`score` int(10) NULL DEFAULT NULL
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Compact;-- ----------------------------
-- Records of score
-- ----------------------------
INSERT INTO `score` VALUES (1, 1, 80);
INSERT INTO `score` VALUES (1, 2, 90);
INSERT INTO `score` VALUES (1, 3, 99);
INSERT INTO `score` VALUES (2, 2, 60);
INSERT INTO `score` VALUES (2, 1, 70);
INSERT INTO `score` VALUES (2, 3, 80);
INSERT INTO `score` VALUES (3, 1, 50);
INSERT INTO `score` VALUES (3, 2, 80);
INSERT INTO `score` VALUES (3, 3, 81);
INSERT INTO `score` VALUES (4, 1, 50);
INSERT INTO `score` VALUES (4, 2, 30);
INSERT INTO `score` VALUES (4, 3, 20);
INSERT INTO `score` VALUES (5, 1, 76);
INSERT INTO `score` VALUES (7, 2, 89);
INSERT INTO `score` VALUES (5, 2, 87);
INSERT INTO `score` VALUES (7, 3, 98);
INSERT INTO `score` VALUES (6, 1, 31);
INSERT INTO `score` VALUES (6, 3, 34);
INSERT INTO `score` VALUES (7, 1, 96);-- ----------------------------
-- Table structure for student
-- ----------------------------
DROP TABLE IF EXISTS `student`;
CREATE TABLE `student` (`id` int(10) NOT NULL,`name` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,`sex` char(10) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,`time` datetime NULL DEFAULT NULL,PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Compact;-- ----------------------------
-- Records of student
-- ----------------------------
INSERT INTO `student` VALUES (1, '张三', '男', NULL);
INSERT INTO `student` VALUES (2, '李四', '男', NULL);
INSERT INTO `student` VALUES (3, '王二', '男', NULL);
INSERT INTO `student` VALUES (4, '麻子', '男', NULL);
INSERT INTO `student` VALUES (5, '胡歌', '男', NULL);
INSERT INTO `student` VALUES (6, '杨幂', '女', NULL);
INSERT INTO `student` VALUES (7, '白冰', '女', NULL);
INSERT INTO `student` VALUES (8, '刘诗诗', '女', NULL);
INSERT INTO `student` VALUES (9, '李王三', '女', NULL);
INSERT INTO `student` VALUES (10, '李闯王', '男', NULL);-- ----------------------------
-- Table structure for teacher
-- ----------------------------
DROP TABLE IF EXISTS `teacher`;
CREATE TABLE `teacher` (`tid` int(11) NOT NULL AUTO_INCREMENT,`Tname` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,PRIMARY KEY (`tid`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 4 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Compact;-- ----------------------------
-- Records of teacher
-- ----------------------------
INSERT INTO `teacher` VALUES (1, '张三');
INSERT INTO `teacher` VALUES (2, '李四');
INSERT INTO `teacher` VALUES (3, '王五');SET FOREIGN_KEY_CHECKS = 1;
更多推荐
mysql 知识和面试题整理:
发布评论