MySQL第三次作业

查询

1.

2.

select * from student limit 2,4；

3.

select * from student where department='计算机系' or department='英语系';

4.

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

5.

mysql> select count(id), department from student group by department;

6.

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

7.

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

或

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

8.

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

9.

mysql> select sum(grade),name'姓名' from score join student on score.stu_id=student.id group by name;

10.

mysql> select round(avg(grade),2),c_name from student join score on student.id=score.stu_id group by c_name;

11.

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

12.

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

13.

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

14.

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

15.

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