2021-03-02

select * from Student t where t.s_name like '王%';

SELECT COUNT(s_name) '学生的姓名' from Student WHERE s_name LIKE '王%';

SELECT s_name,COUNT(s_name) '出现的次数' FROM Student GROUP BY s_name HAVING COUNT(s_name) < 0;

SELECT s_name,COUNT(s_name) '出现的次数' FROM Student GROUP BY s_name HAVING s_name LIKE '王%';

-- from on jion where GROUP BY having SELECT DISTINCT ORDER BY LIMIT;

SELECT * from Score;

SELECT SUM(s_score) FROM Score WHERE c_id = '02';

SELECT * FROM Student;

SELECT * FROM Course;

SELECT * from Score;

SELECT count(DISTINCT(s_id)) from Score;

-- SELECT DISTINCT *  from Score;

--

SELECT * from Student t WHERE t.s_id in (SELECT DISTINCT(s_id) FROM Score);

SELECT c_id,MAX(s_score) FROM Score GROUP BY c_id;

SELECT

*

FROM

Course c

LEFT JOIN (

SELECT

c_id,

MAX( s_score ),

MIN(s_score)

FROM

Score

GROUP BY

c_id

) s ON s.c_id = c.c_id;

SELECT * FROM Course;

SELECT c_id,count(c_id) from Score GROUP BY c_id;

SELECT * from Student;

SELECT

a.s_sex,a.partition,b.total

FROM

(SELECT s_sex,COUNT(s_sex) As 'partition' FROM Student GROUP BY s_sex) a

(SELECT COUNT(s_sex) As 'total' FROM Student) b

SELECT

s_sex,

n,

ROUND( n / s * 100, 4)

FROM

(

SELECT

*

FROM

( SELECT s_sex, count( 1 ) n FROM Student GROUP BY s_sex ) t1

INNER JOIN ( SELECT count( 1 ) s FROM Student ) t2 ON 1 = 1

)a;

SELECT s_id,AVG(s_score) from Score GROUP BY s_id HAVING AVG(s_score) > 60;

SELECT s_id,COUNT(c_id) FROM Score GROUP BY s_id HAVING COUNT(c_id) >2;

SELECT s_name,COUNT(s_name) FROM Student GROUP BY s_name HAVING COUNT(s_name) =2;