Mysql常见的面试总结

第一题

CREATE TABLE `table1` (
  `name` VARCHAR(20) DEFAULT NULL,
  `kecheng` VARCHAR(20) DEFAULT NULL,
  `fenshu` INT(10) DEFAULT NULL
);


INSERT INTO table1(NAME,kecheng,fenshu) VALUES('张三','语文',81);
INSERT INTO table1(NAME,kecheng,fenshu) VALUES('张三','数学',75);
INSERT INTO table1(NAME,kecheng,fenshu) VALUES('李四','语文',76);
INSERT INTO table1(NAME,kecheng,fenshu) VALUES('李四','数学',76);
INSERT INTO table1(NAME,kecheng,fenshu) VALUES('王五','语文',81);
INSERT INTO table1(NAME,kecheng,fenshu) VALUES('王五','数学',100);

#求出每门课都大于80分的学生姓名

第一种方法:

SELECT NAME FROM table1 GROUP BY NAME 
HAVING MIN(fenshu)>80

第二种方法:

SELECT NAME FROM table1 GROUP BY NAME 
HAVING COUNT(1)=SUM(CASE WHEN fenshu>80 THEN 1 ELSE 0 END)

第三种方法:

SELECT NAME FROM table1 GROUP BY NAME 
HAVING NAME NOT IN (SELECT NAME FROM table1 WHERE fenshu<=80)

第二题

CREATE TABLE `table2` (
  `语文` INT(10) DEFAULT NULL,
  `数学` INT(10) DEFAULT NULL,
  `英语` INT(10) DEFAULT NULL
);
INSERT INTO table2(语文,数学,英语) VALUES(58,70,80);

有一张表,3个字段是语文,数学,英语, 有1条记录分别表示语文68,数学70,英语80, 得出结果分数变等级(>=80分是优秀, >=60分是及格, <60是不及格)

SELECT CASE WHEN 语文>=80 THEN '优秀' WHEN 语文>=60 THEN '及格' WHEN 语文<60 THEN '不及格' END 语文,
CASE WHEN 数学>=80 THEN '优秀' WHEN 数学>=60 THEN '及格' WHEN 数学<60 THEN '不及格' END 数学,
CASE WHEN 英语>=80 THEN '优秀' WHEN 英语>=60 THEN '及格' WHEN 英语<60 THEN '不及格' END 英语 
FROM table2

第三题

CREATE TABLE `table3` (
  `date` VARCHAR(20) DEFAULT NULL,
  `result` VARCHAR(10) DEFAULT NULL
)

#查询出来的结果：
#date 胜 负
#2011-02-01 2 1
#2011-02-02 1 1

SELECT DATE, 
(SELECT COUNT(1) FROM table3 WHERE DATE=t.date AND result='胜') AS 胜, 
(SELECT COUNT(1) FROM table3 WHERE DATE=t.date AND result='负') AS 负
FROM table3 t GROUP BY DATE

第四题

#1 2005001 张三 0001 数学 69
#2 2005002 李四 0001 数学 89
#3 2005001 张三 0001 数学 69

CREATE TABLE IF NOT EXISTS `student_test` (
	id INT UNSIGNED AUTO_INCREMENT,
	stu_id VARCHAR(10) NULL,
	NAME VARCHAR(10) NULL,
	couser_id VARCHAR(10) NULL,
	couser VARCHAR(10) NULL,
	score DECIMAL(10,2) NULL,
	PRIMARY KEY (id) 
)
INSERT INTO student_test(id,stu_id,NAME,couser_id,couser,score) VALUES(NULL,'2005001','张三','0001','数学',69);
INSERT INTO student_test(id,stu_id,NAME,couser_id,couser,score) VALUES(NULL,'2005002','李四','0001','数学',89);
INSERT INTO student_test(id,stu_id,NAME,couser_id,couser,score) VALUES(NULL,'2005001','张三','0001','数学',69);

#删除冗余字段, 保留ID最小的那个

DELETE FROM student_test WHERE id NOT IN (
 SELECT * FROM (
  SELECT MIN(id) FROM student_test GROUP BY stu_id,NAME,couser_id,couser,score
 ) s
)