MySQL开发面试题

mysql> select * from afinfo    -> order by birth;

mysql> select * from afinfo    -> where name like '徐%';

mysql> update afinfo    -> set age=45    -> where name='李达康';

mysql> delete from afinfo    -> where name='王芳';

mysql> select * from student;

mysql> insert into student values('小明','005');

mysql> update exam,student    -> set exam.score=85    -> where student.code=exam.code    ->   and student.name='李四'    ->   and exam.subject='语文';

mysql> select subject 学科,avg(score) 平均分    -> from exam    -> group by subject;

mysql> select s.name 姓名,s.code 学号,e.subject 学科,e.score 成绩    -> from student s    -> left join exam e    ->    on s.code=e.code    -> order by 学号,学科;

mysql> select s.name 姓名,s.code 学号,e.subject 学科,e.score 成绩    -> from student s    -> join exam e    ->    on s.code=e.code    -> where (e.subject,e.score) in    -> (    ->   select subject,max(score)    ->   from exam    ->   group by subject    -> );

mysql> select s.name 姓名,s.code 学号,    -> sum(if(e.subject='语文',e.score,0)) 语文成绩,    -> sum(if(e.subject='数学',e.score,0)) 数学成绩,    -> sum(if(e.subject='英语',e.score,0)) 英语成绩    -> from student s    -> left join exam e    ->    on s.code=e.code    -> group by s.name,s.code;

mysql> select * from student;+------+--------+-------+------+| s_no | s_name | s_age | sex  |+------+--------+-------+------+| 1001 | 张三    |    23 | 男   || 1002 | 李四    |    19 | 女   || 1003 | 马五    |    20 | 男   || 1004 | 甲六    |    17 | 女   || 1005 | 乙七    |    22 | 男   |+------+--------+-------+------+5 rows in set (0.00 sec)mysql> select * from teacher;+------+--------+| t_no | t_name |+------+--------+| 2001 | 叶平   || 2002 | 赵安   || 2003 | 孙顺   || 2004 | 刘六   |+------+--------+4 rows in set (0.00 sec)mysql> select * from course;+------+--------------+------+| c_no | c_name       | t_no |+------+--------------+------+|  001 | 企业管理      | 2001 ||  002 | 马克思        | 2002 ||  003 | UML          | 2003 ||  004 | 数据库        | 2004 |+------+--------------+------+4 rows in set (0.05 sec)mysql> select * from sc;+------+------+-------+| s_no | c_no | score |+------+------+-------+| 1001 | 001  |    93 || 1001 | 002  |    86 || 1001 | 004  |    92 || 1002 | 003  |   100 || 1003 | 001  |    93 || 1003 | 004  |    99 || 1004 | 002  |    75 || 1004 | 003  |    59 || 1002 | 002  |    50 || 1005 | 003  |    60 || 1005 | 002  |    60 |+------+------+-------+11 rows in set (0.00 sec)

mysql> select a.s_no    -> from    -> 　　(select s_no,score from sc where c_no='001') a,    -> 　　(select s_no,score from sc where c_no='002') b    -> where a.score>b.score    -> and a.s_no=b.s_no;

mysql> select s_no,avg(score)    -> from sc    -> group by s_no    -> having avg(score)>60;

mysql> select student.s_no,student.s_name,count(sc.c_no),sum(sc.score)    -> from student    -> left join sc    -> on student.s_no=sc.s_no    -> group by student.s_no,student.s_name;

mysql> select count(*)    -> from teacher    -> where t_name like '李%';

mysql> select student.s_no,student.s_name    -> from student    -> where student.s_no not in    -> (    -> 　　select distinct(sc.s_no)    -> 　　from sc    -> 　　join course    -> 　　on course.c_no=sc.c_no    -> 　　join teacher    -> 　　on teacher.t_no=course.t_no    -> 　　where t_name='叶平'    -> );

mysql> select student.s_no,student.s_name    -> from student    -> join sc    -> on sc.s_no=student.s_no    -> where c_no='001'    -> and exists    -> (select * from sc where sc.s_no=student.s_no and c_no='002');

mysql> select student.s_no,student.s_name    -> from student    -> join sc    ->    on sc.s_no=student.s_no    -> join course    ->    on course.c_no=sc.c_no    -> join teacher    ->    on teacher.t_no=course.t_no    -> where teacher.t_name='叶平';

mysql> select student.s_no,student.s_name    -> from student    -> join (select s_no,score from sc where c_no='001') a    ->   on a.s_no=student.s_no    -> join (select s_no,score from sc where c_no='002') b    ->   on b.s_no=student.s_no    -> where a.s_no=b.s_no    -> and a.score>b.score;

mysql> select student.s_no,student.s_name    -> from student    -> join sc    ->   on sc.s_no=student.s_no    -> where sc.score<60;

mysql> select student.s_no 学号,student.s_name 姓名    -> from student    -> left join sc    ->   on sc.s_no=student.s_no    -> group by student.s_no,student.s_name    -> having count(*) < (    -> 　　select count(*) from course);

mysql> select student.s_no,student.s_name    -> from student    -> join sc    -> on sc.s_no=student.s_no    -> where sc.c_no in    -> (    -> 　　select c_no    -> 　　from sc    -> 　　where s_no='1001'    -> )    -> and student.s_no != '1001';

mysql> select distinct sc.s_no,s_name    -> from student,sc    -> where student.s_no=sc.s_no    -> and c_no in    -> (select c_no from sc where s_no=1001)    -> and student.s_no != '1001';

mysql> set @ye_avg_score=    -> (    -> 　　select avg(score)    -> 　　from    ->    (    -> 　　　　select sc.score    -> 　　　　from sc    -> 　　　　join course    -> 　　　　on course.c_no=sc.c_no    -> 　　　　join teacher    -> 　　　　on teacher.t_no=course.t_no    -> 　　　　where teacher.t_name='叶平'    -> 　　) azi    -> );mysql> update sc    -> set score=@ye_avg_score    -> where c_no in    -> (    -> 　　select c_no    -> 　　from course    ->    join teacher    -> 　　on teacher.t_no=course.t_no    ->    where teacher.t_name='叶平'    -> );

mysql> select s_no,s_name    -> from student    -> where s_no in (    -> 　　select distinct s_no from sc where c_no in    -> 　　　　(select c_no from sc where s_no='1002')    -> group by s_no    -> having count(*)=(select count(*) from sc where s_no='1002')    -> and s_no<>'1002'    -> );

mysql> set @ye_c_no=(select c_no from course,teacher where course.t_no=teacher.t_no and t_name=’叶平’);mysql> delete from sc    -> where c_no=@ye_c_no;

mysql> select distinct s_no from sc    -> where c_no not in (select c_no from sc where c_no='003')    -> and s_no not in (select s_no from sc where c_no='003');

mysql> select c_no 课程ID,max(score) 最高分,min(score) 最低分    -> from sc    -> group by c_no;

mysql> select sc.s_no 学号,    -> max(case c_name when '数据库' then score end) 数据库,    -> max(case c_name when '企业管理' then score end) 企业管理,    -> max(case c_name when '马克思' then score end) 马克思,    -> count(sc.s_no) 有效课程数,    -> avg(ifnull(score,0)) 有效平均分    -> from sc,course    -> where sc.c_no=course.c_no    -> group by sc.s_no    -> order by 6 desc;

mysql> select c_no,avg(score)    -> from sc    -> group by c_no    -> order by 2 desc;

mysql> (select student.s_no,s_name,c_no,score from student,sc where student.s_no=sc.s_no and c_no=001 order by score desc limit 2,4)    -> union    -> (select student.s_no,s_name,c_no,score from student,sc where student.s_no=sc.s_no and c_no=002 order by score desc limit 2,4)    -> union    -> (select student.s_no,s_name,c_no,score from student,sc where student.s_no=sc.s_no and c_no=003 order by score desc limit 2,4)    -> union    -> (select student.s_no,s_name,c_no,score from student,sc where student.s_no=sc.s_no and c_no=004 order by score desc limit 2,4);

mysql> select course.c_no 课程ID,c_name 课程名称,    -> count(case when score>85 and score<=100 then score end) '[85-100]',    -> count(case when score>70 and score<=85 then score end) '[70-85]',    -> count(case when score>=60 and score<=70 then score end) '[60-70]',    -> count(case when score<60 then score end) '[<60]'    -> from course,sc    -> where course.c_no=sc.c_no    -> group by course.c_no,c_name;

mysql> select c_no 课程ID,count(s_no) 学生人数    -> from sc    -> group by c_no;

mysql> select student.s_no 学号,student.s_name 姓名,count(c_no) 选课数    -> from student    -> join sc    -> on sc.s_no=student.s_no    -> group by student.s_no,student.s_name    -> having count(c_no)=1;

mysql> select s_name 姓名,count(*)    -> from student    -> group by s_name    -> having count(*)>1;

mysql> select * from student    -> where year(curdate())-s_age='1994';

mysql> select c_no 课程ID,avg(score)    -> from sc    -> group by c_no    -> order by avg(score) asc,c_no desc;

mysql> select student.s_no 学号,s_name 姓名,avg(score) 平均成绩    -> from student,sc    -> where student.s_no=sc.s_no    -> group by student.s_no,s_name    -> having avg(score)>85;

mysql> select s_name 学生姓名,score 分数    -> from student,sc,course    -> where student.s_no=sc.s_no and sc.c_no=course.c_no    -> and c_name='数据库'    -> and score<60;

mysql> select student.s_no 学号,student.s_name 姓名,group_concat(c_no) 所选课程ID    -> from student,sc    -> where student.s_no=sc.s_no    -> group by student.s_no,student.s_name;

mysql> select s_name 姓名,c_name 课程名称,score 分数    -> from student,sc,course    -> where student.s_no=sc.s_no and sc.c_no=course.c_no    -> and score > 90    -> order by s_name;

mysql> select s_no 学生ID,c_no 不及格课程ID    -> from sc    -> where score<60    -> order by c_no desc;

mysql> select count(*) 已选课程人数    -> from    -> (    ->    select distinct(sc.s_no) from student    ->    left join sc    -> 　　on sc.s_no=student.s_no    -> 　　where c_no is not null    -> ) as  ayixuan;

mysql> select s_name 学生姓名,score 成绩    -> from student,sc,course,teacher    -> where student.s_no=sc.s_no and sc.c_no=course.c_no and course.t_no=teacher.t_no    -> and t_name='冯老师'    -> order by score    -> limit 1;

mysql> select course.c_no 课程ID,course.c_name 课程名,count(s_no) 选修人数    -> from course    -> join sc    -> on course.c_no=sc.c_no    -> group by course.c_no,course.c_name;

mysql> select a.s_no 学号,group_concat(a.c_no) 课程号,a.score 学生成绩    -> from sc a,sc b    -> where a.score=b.score and a.c_no<>b.c_no    -> group by a.s_no,a.score;

mysql> select a.s_no,a.c_no,a.score    -> from sc a    -> where    -> 　　(select count(distinct score) from sc b where b.c_no=a.c_no and b.score>=a.score)<=2    -> order by a.c_no,a.score desc;

mysql> select s_no 学生学号    -> from sc    -> group by s_no    -> having count(*)>=2;

mysql> select course.c_no 课程号,c_name 课程名    -> from course    -> join sc on course.c_no=sc.c_no    -> join (    ->   select c_no,count(s_no) from sc group by c_no    ->   having count(s_no)=(select count(*) from student)) as a    -> on course.c_no=a.c_no;

mysql> select student.s_no 学号,student.s_name 姓名    -> from student    -> join sc    -> on sc.s_no=student.s_no    -> where sc.s_no not in    -> (    -> 　　select s_no    -> 　　from course,teacher,sc    -> 　　where course.t_no=teacher.t_no and sc.c_no=course.c_no    -> 　　and teacher.t_name='叶平'    -> );

mysql> select s_no 学号,avg(score) 平均成绩    -> from sc    -> where s_no in (    -> 　　select s_no from sc    -> 　　where score<60    ->    group by s_no    ->    having count(*)>2)    -> group by s_no; 

mysql> select distinct cno 出版社编号    -> from books    -> where tcategory='计算机';

mysql> select *    -> from books,book_concern    -> where books.cno=book_concern.cno    -> and cname='南开大学出版社'    -> and tcategory in ('数学','经济');

mysql> select cno 出版社编号,avg(tprice) 图书均价    -> from books    -> where cno='20001';

mysql> select b.cno 出版社编号,cname 出版社名称,count(*) 图书套数    -> from books b,book_concern c    -> where b.cno=c.cno    -> group by b.cno,cname    -> having count(*)>20    -> order by b.cno;

mysql> select cno 出版社编号    -> from books    -> group by cno    -> having count(*)>(select count(*) from books where cno='20001');

mysql> select * from shop;+---------+--------+-------+| article | author | price |+---------+--------+-------+|    0001 | B      |  9.95 ||    0002 | A      | 10.99 ||    0003 | C      |  1.69 ||    0004 | B      | 19.95 ||    0005 | A      |  6.96 |+---------+--------+-------+5 rows in set (0.02 sec)

mysql> select article,author,price    -> from shop s1    -> where price = (    -> select max(s2.price)    -> from shop s2    -> where s1.author=s2.author); 

mysql> select article,s1.author,s1.price    -> from shop s1    -> join (    -> select author,max(price) price    -> from shop    -> group by author) s2    -> on s1.author=s2.author and s1.price=s2.price;

mysql> select s1.article,s1.author,s1.price    -> from shop s1    -> left join shop s2    -> on s1.author=s2.author and s1.price<s2.price    -> where s2.article is null;

mysql> select a.name 姓名                                                                      -> from    -> (select name,count(*) anum from NO_6 where fenshu>80 group by name) a,    -> (select name,count(*) bnum from NO_6 group by name) b    -> where a.name=b.name    -> and a.anum=b.bnum;

mysql> select year,    -> sum(if(month=1,amount,0)) M1,    -> sum(if(month=2,amount,0)) M2,    -> sum(if(month=3,amount,0)) M3,    -> sum(if(month=4,amount,0)) M4    -> from NO_7    -> group by year;

mysql> select IP from login_ftp    -> group by IP    -> having count(*)>3;

CREATE PROCEDURE curdemo()BEGIN  DECLARE done INT DEFAULT FALSE;  DECLARE a CHAR(16);  DECLARE b, c INT;  DECLARE cur1 CURSOR FOR SELECT id,data FROM test.t1;  DECLARE cur2 CURSOR FOR SELECT i FROM test.t2;  DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;  OPEN cur1;  OPEN cur2;  read_loop: LOOP    FETCH cur1 INTO a, b;    FETCH cur2 INTO c;    IF done THEN      LEAVE read_loop;    END IF;    IF b < c THEN      INSERT INTO test.t3 VALUES (a,b);    ELSE      INSERT INTO test.t3 VALUES (a,c);    END IF;  END LOOP;  CLOSE cur1;  CLOSE cur2;END;