建完庫與建完表后后:

1.分別查詢student表和score表的所有記錄

?student表：

score表:

2.查詢student表的第2條到5條記錄

SELECT * FROM student LIMIT 1,4;

3.從student表中查詢計算機系和英語系的學生的信息

SELECT * FROM student
-> WHERE department IN ('計算機系', '英語系');

4.從student表中查詢年齡小于22歲的學生信息

select * from student
-> where 2025 - birth < 22 ;

5.從student表中查詢每個院系有多少人

select department, count(*) as student_count
-> from student
-> group by department;

6.從score表中查詢每個科目的最高分

select c_name,max(grade) as max_grade
-> from score
-> group by c_name;

7.查詢李廣昌的考試科目(c name)和考試成績(grade)

select s.c_name , s.grade
-> from student st
-> join score s on st.id = s.stu_id
-> where st.name = '李廣昌';

8.用連接的方式查詢所有學生的信息和考試信息

select st.*,s.c_name,s.grade
-> from student st
-> left join score s on st.id = s.stu_id;

9.計算每個學生的總成績

select stu_id,sum(grade) as total_grade
-> from score
-> group by stu_id;

10.計算每個考試科目的平均成績

select c_name,avg(grade) as avg_grade
-> from score
-> group by c_name;

11.查詢計算機成績低于95的學生信息

select st.*
-> from student st
-> join score s on st.id = s.stu_id
-> where s.c_name = '計算機' AND s.grade < 95
-> ;

12,將計算機考試成績按叢高到低進行排序

select * from score
-> where c_name = '計算機'
-> order by grade desc;

13.從student表和score表中查詢出學生的學號，然后合并查詢結果

select id as student_id
-> from ?student
-> union
-> select stu_id from score;

14.查詢姓張或者姓王的同學的姓名、院系和考試科目及成績

select st.name , st.department , s.c_name , s.grade
-> from student st
-> left join score s on st.id = s.stu_id
-> where st.name like '張%' or st.name like '王%';

15.查詢都是湖南的學生的姓名、年齡、院系和考試科目及成績

?select st.name , 2025 - st.birth as age ,st.department , s.c_name , s.grade
-> from student st
-> left join score s on st.id = s.stu_id
-> where st.address like '%湖南省%';