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;