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 '湖南%';