mysql學習筆記-day2

標籤：

1、查詢“001”課程比“002”課程成績高的所有學生的學號；
select a.`S#` from (select `s#`,score from SC where `C#`="001") a,
(select `s#`,score from SC where `C#`="002") b
where a.score>b.score and a.`s#`=b.`s#`;

2、查詢平均成績大於60分的同學的學號和平均成績；
select `S#`,avg(score)
from sc
group by `S#` having avg(score) >60;

3、查詢所有同學的學號、姓名、選課數、總成績；
select Student.`S#`,Student.Sname,count(SC.`C#`),sum(score)
from Student left Outer join SC on Student.`S#`=SC.`S#`
group by Student.`S#`,Sname ;

4、查詢姓“李”的老師的個數；
select count(distinct(Tname)) from Teacher where Tname like "李%";

5、查詢沒學過“葉平”老師課的同學的學號、姓名；
select Student.`S#`,Student.Sname
from Student
where `S#` not in (select distinct( SC.`S#`) from SC,Course,Teacher where SC.`C#`=Course.`C#` and Teacher.`T#`=Course.`T#` and Teacher.Tname="葉平");

6、查詢學過“001”並且也學過編號“002”課程的同學的學號、姓名；
select Student.`S#`,Student.Sname
from Student,SC
where Student.`S#`=SC.`S#` and SC.`C#`="001"and exists( Select * from SC as SC_2 where SC_2.`S#`=SC.`S#` and SC_2.`C#`="002");

7、查詢學過“葉平”老師所教的所有課的同學的學號、姓名；
select `S#`,Sname from Student where `S#` in (select `S#`from SC ,Course ,Teacher
where SC.`C#`=Course.`C#` and Teacher.`T#`=Course.`T#` and Teacher.Tname="葉平" group by `S#`
having count(SC.`C#`)=(select count(`C#`) from Course,Teacher where Teacher.`T#`=Course.`T#` and Tname="葉平"));

8、查詢所有課程成績小於60分的同學的學號、姓名；
select `S#`,Sname from Student
where `S#` not in (select Student.`S#` from Student,SC where Student.`S#`=SC.`S#` and score>60);

9、查詢沒有學全所有課的同學的學號、姓名；
select Student.`S#`,Student.Sname from Student,SC where Student.`S#`=SC.`S#`
group by Student.`S#`,Student.Sname having count(`C#`) <(select count(`C#`) from Course);

10、更改表欄位類型

alter table student modify `s#` varchar(15);

PS.今天面試回來，完全蒙圈的感覺，四年的工作經驗僅僅價值不到1000，好吧，行業或許真的很重要，要選擇一個行業，堅持走下去，而不是再換行業或者半途而廢了。

走到無路可走，還是要堅持走下去，一個人又如何？誰不是自己殺出一條血路，然後才有了陪伴的人？又或許，現在也會有一類人，根本無須陪伴，只是要孤軍奮戰。

 

mysql學習筆記-day2