????3.????
??????????????????????????????????????λ???У????????????У???> /< /= />= /<=/ !=/ <> /between..and.. /like/ in /all /any /exists/not exists??????????????????У?andor!
?????????
????--??????????28???????????????
????SELECT * FROM student WHERE sage>28;
????--?????????????28?????????????????
????SELECT * FROM student WHERE sage>=28;
????--??????????????
????SELECT * FROM student WHERE ssex='M';
????SELECT * FROM student WHERE ssex!='F';
????SELECT * FROM student WHERE ssex<>'F';
????--???????s0010???????????????
????SELECT * FROM student WHERE sid<'s0010';
????1??between..and..
?????????????? between ?1 and ?2  ????? ???>=?1 and ???<=?2?????????????????????Σ????????
?????????--?????????23-28?????????????????????
????SELECT * FROM student WHERE sage>=23 AND sage<=28;
????SELECT * FROM student WHERE sage BETWEEN 23 AND 28;
????--?????????23-28?????????????????????
????SELECT * FROM student WHERE sage BETWEEN 23 AND 28 AND ssex='F';
????SELECT * FROM student WHERE sage>=23 AND ssex='F'AND sage<=28;
????2??like:???????????????????????????????????????%????????????????????_??????????????
?????????--???????????????????
????SELECT *  FROM student WHERE sname LIKE '??%';
????--????????????????????2????????????
????SELECT *  FROM student WHERE sname LIKE '??_';
????--????????????????????3????????????
????SELECT *  FROM student WHERE sname LIKE '??__';
????SELECT *  FROM student WHERE sname LIKE '%??';
????3)in???????????÷??????in???1???2??......???n??????? ???=?1 or ???=?2 or ... or ???=?n??
?????????--??????????27?????28?????????????
????SELECT * FROM student WHERE sage=27 OR sage=28;
????SELECT * FROM student WHERE sage IN (27??28);
????4)distinct????????????Σ??????????????????
?????????--??????????????????????
????SELECT  sid FROM score;
????SELECT  DISTINCT sid FROM score;
????5)??????????????????????????С?
??????????? ??? as ??????????????as??????? ??? ??????
?????????SELECT  sname??sage FROM student;
????SELECT  sname as ??????sage ???? FROM student;
????SELECT  sname??sage+1 sage FROM student;
????SELECT  sname sage FROM student; --???????????ò?????????????????????????????????????????sage??
????6?????
????SQL????У??????????????????á?????????????()????????????????????????????????Щ??????????????????????????????????
????--??????????oracle?γ?????????????
????SELECT sname FROM student WHERE sid IN (SELECT sid FROM score WHERE cid=(SELECTcid FROM course WHERE cname='oracle'));
????--?????λ????????γ?????????????????????
????SELECT cname FROM course WHERE cid IN (SELECT cid FROM score WHERE sid in (SELECT sid FROM student WHERE sname='????'));
????7)all: ???and??????????з??
????--??? > all(?1???2???????????N)  ?????? ???>?1 and ???>?2 and... and ???>?N??
????--??? < all(?1???2???????????N)  ?????? ???<?1 and ???<?2 and... and ???<?N??
?????????????????????????????????????
????SELECT * FROM student WHERE ssex='M' AND sage>39;
????SELECT sage FROM student WHERE ssex='F'; --212539
????SELECT * FROM student WHERE ssex='M' AND sage>ALL(SELECT sage FROM student WHERE ssex='F');
????--?????  SELECT *FROM student WHERE ssex='M' AND sage>21 AND sage>25 AND sage>39;
????8??any: ????or??????????з??
????--??? > any(?1???2???????????N)  ?????? ???>?1 or ???>?2 or ... or ???>?N??
?????????--???????????????????????????
????SELECT * FROM student WHERE ssex='M' AND sage>ANY(SELECT sage FROM student WHERE ssex='F');
????4.???????????麯??/?麯????
????????????????У?count()avg()min()max()sum()?? ?????????λ???α?????
?????????--?????????????
????SELECT count(*) FROM student WHERE ssex='M';
????--????г?????????????
????SELECT count(distinct sid) FROM score;
????--????????С????
????SELECT min(sage) FROM student WHERE ssex='F';
????--?????λ???????????
????SELECT avg(grade) FROM score WHERE sid='s0001';
????--??????????????????
????SELECT* FROM student WHERE sage=(SELECT max(sage) FROM student);
????5.??????
??????????? group by ???????
??????????????????????е?????????????ν??з??鴦?????????ε????????л?????????????????飻???????????????????????????????????????????????????????????ο?????????????????
?????????--??????????????????
????SELECT ssex??COUNT(*) FROM student GROUP BY ssex;
????--???????????????????????????С????
????SELECT ssex??AVG(sage)??MAX(sage)??min(sage) FROM student GROUP BY ssex;
????--???????γ?????????
????SELECT cid??COUNT(DISTINCT sid) FROM score GROUP BY cid;
???????where??????????????having???????????????ζ???麯??????????????????????????where???????????????having???
?????????--??????п??????????????sid??
????SELECT sid FROM score WHERE grade>=60 GROUP BY sid; --?????д??????????????????????????
????SELECT sid FROM score GROUP BY sid HAVING MIN(grade)>=60; --?????????飬?趨??????????????????????60.