Msql多表查询

1.建库建表

(1)创建数据库mydb11_stu并使用数据库

(2)创建student表

 (2)创建score表

27.查询

281.分别查询student表和score表的所有记录

2.查询student表的第2条到5条记录

3.从student表中查询计算机系和英语系的学生的信息

4.从student表中查询年龄小于22岁的学生信,息

5.从student表中查询每个院系有多少人

6.从score表中查询每个科目的最高分

7.查询李广昌的考试科目(c_name)和考试成绩(grade)

8.用连接的方式查询所有学生的信息和考试信息

9.计算每个学生的总成绩

10.计算每个考试科目的平均成绩

11.查询计算机成绩低于95的学生信息

12.将计算机考试成绩按从高到低进行排序

13.从student表和score表中查询出学生的学号，然后合并查询结果

14.查询姓张或者姓王的同学的姓名、院系和考试科目及成绩

15.查询都是湖南的学生的姓名、年龄、院系和考试科目及成绩

1.mysql>select * from student;

mysql> select * from score;

2.mysql> select * from student limit 4 offset 1;

3.mysql> select * from student where department   in('计算机系','英语系');

4.mysql> select * from student where year(now())-birth<22;

5.mysql> select department, count(*) as '院系人数' from student group by department;

6.mysql> select c_name,max(grade) from score group by c_name;

7.

mysql> select s.name, sc.c_name, sc.grade from student s join score sc on s.id=sc.stu_i

d where s.name ='李广昌';

8.

mysql> select s.id, s.name,s.department,sc.c_name'科目名称',sc.grade'成绩' from student

 s inner join score sc on s.id=sc.stu_id;

9.

mysql> select s.id,s.name,sum(sc.grade)'总成绩' from student s join score sc on s.id=sc

.stu_id group by s.id,s.name;

10.mysql> select c_name'科目', avg(grade)'平均成绩' from score group by c_name;

11.mysql> select s.id,s.name,sc.grade'计算机成绩' from student s join score sc on s.id=sc.stu_id where sc.c_name='计算机' and sc.grade <95;

12.

mysql> select s.id,s.name,sc.grade'计算机考试成绩' from student s join score sc on s.id

=sc.stu_id where sc.c_name='计算机' order by sc.grade desc;

13.

mysql> select * from student left outer join score on student.id=score.stu_id union sel

ect * from student right outer join score on student.id=score.stu_id;

14.mysql> select s.name,s.department,sc.c_name '姓名',sc.grade from student s join score sc on s.id=sc.stu_id where s.name like'张%' or s.name like'王%';

15.

mysql> select s.name,s.birth,s.department,sc.c_name,sc.grade from student s join score

sc on s.id=sc.stu_id where s.address like '湖南%';