sql多表查询

mysql> drop view if exists v_student;

mysql> select * from score;

mysql> select * from student limit 2,4；

    mysql> select * from student
    -> where department='英语系' or department='中文系';

mysql> select count(1) from student
    -> group by department;

mysql> select max(grade),c_name '最高分' from score
    -> group by c_name;

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

mysql> select student.*,score.* from student join score on student.id
    -> =score.stu_id;

mysql> select name,sum(grade) '总成绩' from student join score on student.id=score.stu_id
    -> group by name;

mysql> select department,round(avg(grade),2) '总成绩' from student join score on student.id=
score.stu_id
-> group by department;

mysql> select student.*,c_name,grade from student join score on student.id=
    -> score.stu_id
    -> where c_name='计算机' and grade<95;

mysql> select student.*,c_name,grade from student join score on student.id=
    -> score.stu_id
    -> where c_name='计算机'
    -> order by grade desc;

mysql> select id from student union select stu_id from score;

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

mysql> select name,year(now())-birth '年龄',c_name,grade from student join score on student.
id=score.stu_id
    -> where address like '湖南%';