本次实验所用表
1.student(sno,name,sex,class,data,phone)
+------+--------+------+----------+-----------+-------------+
| 学号 | 姓名 | 性别 | 专业班级 | 出生日期 | 联系电话 |
+------+--------+------+----------+-----------+-------------+
| 0433 | 张艳 | 女 | 生物04 | 1986-9-13 | NULL |
| 0496 | 李越 | 男 | 电子04 | 1984-2-23 | 1381290**** |
| 0529 | 赵欣 | 男 | 会计05 | 1984-1-27 | 1350222**** |
| 0531 | 张志国 | 男 | 生物05 | 1986-9-10 | 1331256**** |
| 0538 | 于兰兰 | 女 | 生物05 | 1984-2-20 | 1331200**** |
| 0591 | 王丽丽 | 女 | 电子05 | 1984-3-20 | 1332080**** |
| 0592 | 王海强 | 男 | 电子05 | 1986-11-1 | NULL |
+------+--------+------+----------+-----------+-------------+
2.subject(cno,cname,score,xtime,teacher)
+--------+----------------+--------+--------+----------+
| 课程号 | 课程名 | 学分数 | 学时数 | 任课教师 |
+--------+----------------+--------+--------+----------+
| K001 | 计算机图形学 | 2.5 | 40 | 胡晶晶 |
| K002 | 计算机应用基础 | 3.0 | 48 | 任泉 |
| K006 | 数据结构 | 4.0 | 64 | 马跃先 |
| M001 | 政治经济学 | 4.0 | 64 | 孔繁新 |
| S001 | 高等数学 | 3.0 | 48 | 赵晓尘 |
+--------+----------------+--------+--------+----------+
3.stuwork(suno,sno,oscore,tscore,thscore)
+--------+------+-----------+-----------+-----------+
| 课程号 | 学号 | 作业1成绩 | 作业2成绩 | 作业3成绩 |
+--------+------+-----------+-----------+-----------+
| K001 | 0433 | 60 | 75 | 75 |
| K001 | 0529 | 70 | 70 | 60 |
| K001 | 0531 | 70 | 80 | 80 |
| K001 | 0591 | 80 | 90 | 90 |
| K002 | 0496 | 80 | 80 | 90 |
| K002 | 0529 | 70 | 70 | 85 |
| K002 | 0531 | 80 | 80 | 80 |
| K002 | 0538 | 65 | 75 | 85 |
| K002 | 0592 | 75 | 85 | 85 |
| K006 | 0531 | 80 | 80 | 90 |
| K006 | 0591 | 80 | 80 | 80 |
| M001 | 0496 | 70 | 70 | 80 |
| M001 | 0591 | 65 | 75 | 75 |
| S001 | 0531 | 80 | 80 | 80 |
| S001 | 0538 | 60 | NULL | 80 |
+--------+------+-----------+-----------+-----------+
查询表的相关操作
1.进入你所使用表所在的数据库
use stuworkdata;
2.查询各位学生的学号、班级和姓名
mysql> select sno,class,name from student;
+------+--------+--------+
| sno | class | name |
+------+--------+--------+
| 0433 | 生物04 | 张艳 |
| 0496 | 电子04 | 李越 |
| 0529 | 会计05 | 赵欣 |
| 0531 | 生物05 | 张志国 |
| 0538 | 生物05 | 于兰兰 |
| 0591 | 电子05 | 王丽丽 |
| 0592 | 电子05 | 王海强 |
+------+--------+--------+
7 rows in set (0.00 sec)
3.查询课程的全部信息
mysql> select*from subject;
+------+----------------+-------+-------+---------+
| cno | cname | score | xtime | teacher |
+------+----------------+-------+-------+---------+
| K001 | 计算机图形学 | 2.5 | 40 | 胡晶晶 |
| K002 | 计算机应用基础 | 3.0 | 48 | 任泉 |
| K006 | 数据结构 | 4.0 | 64 | 马跃先 |
| M001 | 政治经济学 | 4.0 | 64 | 孔繁新 |
| S001 | 高等数学 | 3.0 | 48 | 赵晓尘 |
+------+----------------+-------+-------+---------+
5 rows in set (0.01 sec)
4.查询数据库中有哪些专业班级
mysql> select distinct class from student;
+--------+
| class |
+--------+
| 生物04 |
| 电子04 |
| 会计05 |
| 生物05 |
| 电子05 |
+--------+
5 rows in set (0.00 sec)
distinct起去重作用,就是可以去掉重复的
5.查询学时数大于60的课程信息
mysql> select*from subject where xtime > 60;
+------+------------+-------+-------+---------+
| cno | cname | score | xtime | teacher |
+------+------------+-------+-------+---------+
| K006 | 数据结构 | 4.0 | 64 | 马跃先 |
| M001 | 政治经济学 | 4.0 | 64 | 孔繁新 |
+------+------------+-------+-------+---------+
2 rows in set (0.00 sec)
6.查询在1986年出生的学生的学号、姓名和出生日期
mysql> select sno,name,data from student where data = 1986;
+------+--------+-----------+
| sno | name | data |
+------+--------+-----------+
| 0433 | 张艳 | 1986-9-13 |
| 0531 | 张志国 | 1986-9-10 |
| 0592 | 王海强 | 1986-11-1 |
+------+--------+-----------+
3 rows in set, 7 warnings (0.00 sec)
7.查询三次作业的成绩都在80分以上的学号、课程号
mysql> select sno,suno from stuwork where oscore >= 80 and tscore >= 80 and thscore >= 80;
+------+------+
| sno | suno |
+------+------+
| 0591 | K001 |
| 0496 | K002 |
| 0531 | K002 |
| 0531 | K006 |
| 0591 | K006 |
| 0531 | S001 |
+------+------+
6 rows in set (0.00 sec)
8.查询姓张的学生的学号、姓名和专业班级
mysql> select sno,name,class from student where name like '张%';
+------+--------+--------+
| sno | name | class |
+------+--------+--------+
| 0433 | 张艳 | 生物04 |
| 0531 | 张志国 | 生物05 |
+------+--------+--------+
2 rows in set (0.00 sec)
9.查询05级的男生信息
mysql> select*from student where sno like '05%' and sex = '男';
+------+--------+------+--------+-----------+-------------+
| sno | name | sex | class | data | phone |
+------+--------+------+--------+-----------+-------------+
| 0529 | 赵欣 | 男 | 会计05 | 1984-1-27 | 1350222**** |
| 0531 | 张志国 | 男 | 生物05 | 1986-9-10 | 1331256**** |
| 0592 | 王海强 | 男 | 电子05 | 1986-11-1 | NULL |
+------+--------+------+--------+-----------+-------------+
3 rows in set (0.00 sec)
10.查询没有作业成绩的学号和课程号
mysql> select sno,suno from stuwork where oscore is null or tscore is null or thscore is null;
+------+------+
| sno | suno |
+------+------+
| 0538 | S001 |
+------+------+
1 row in set (0.00 sec)
11.查询学号为0538的学生的作业1总分
mysql> select sum(oscore) from stuwork where sno = 0538;
+-------------+
| sum(oscore) |
+-------------+
| 125 |
+-------------+
1 row in set (0.01 sec)
12.查询选修了K001课程的学生人数
mysql> select count(suno) from stuwork where suno = 'K001';
+-------------+
| count(suno) |
+-------------+
| 4 |
+-------------+
1 row in set (0.00 sec)
13.查询数据库中共有多少个班级
mysql> select count(distinct class) from student;
+-----------------------+
| count(distinct class) |
+-----------------------+
| 5 |
+-----------------------+
1 row in set (0.01 sec)
distinct可以去掉重复的
14.查询选修三门以上(含三门)课程的学生的学号和作业1平均分、作业2平均分和作业3平均分
mysql> select sno,avg(oscore),avg(tscore),avg(thscore) from stuwork group by sno having count(suno) >= 3;
+------+-------------+-------------+--------------+
| sno | avg(oscore) | avg(tscore) | avg(thscore) |
+------+-------------+-------------+--------------+
| 0531 | 77.5000 | 80.0000 | 82.5000 |
| 0591 | 75.0000 | 81.6667 | 81.6667 |
+------+-------------+-------------+--------------+
2 rows in set (0.01 sec)
15.查询于兰兰的选课信息,列出学号、姓名、课程名(使用两种连接查询的方式)
内连接查询
mysql> select student.sno,name,cname from student,subject,stuwork where student.sno = stuwork.sno and subject.cno = stuw
ork.suno and name = '于兰兰';
+------+--------+----------------+
| sno | name | cname |
+------+--------+----------------+
| 0538 | 于兰兰 | 计算机应用基础 |
| 0538 | 于兰兰 | 高等数学 |
+------+--------+----------------+
2 rows in set (0.01 sec)
左外部连接查询
mysql> select student.sno,name,cname from student left outer join stuwork on student.sno = stuwork.sno left outer join s
ubject on subject.cno =stuwork.suno where name = '于兰兰';
+------+--------+----------------+
| sno | name | cname |
+------+--------+----------------+
| 0538 | 于兰兰 | 高等数学 |
| 0538 | 于兰兰 | 计算机应用基础 |
+------+--------+----------------+
2 rows in set (0.00 sec)