数据库查询和数据操纵

本次实验所用表

1.student(sno,name,sex,class,data,phone)

+------+--------+------+--------+-----------+-------------+
| 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)

+------+----------------+-------+-------+---------+
| cno  | cname          | score | xtime | teacher |
+------+----------------+-------+-------+---------+
| K001 | 计算机图形学   |   5.0 | 40    | 胡晶晶  |
| K002 | 计算机应用基础 |   6.0 | 48    | 任泉    |
| K006 | 数据结构       |   8.0 | 64    | 马跃先  |
| M001 | 政治经济学     |   8.0 | 64    | 孔繁新  |
| S001 | 高等数学       |   6.0 | 48    | 赵晓尘  |
+------+----------------+-------+-------+---------+

3.stuwork(suno,sno,oscore,tscore,thscore)

+------+------+--------+--------+---------+
| suno | sno  | oscore | tscore | thscore |
+------+------+--------+--------+---------+
| 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.查询与“张志国”同一班级的学生信息（使用连接查询和子查询方式）。

连接查询

mysql> select student.sno,name,sex,class,data,phone,cno,cname,score,xtime,teacher,oscore,tscore,thscore from student,subject,stuwork where class = '生物05'
    -> and student.sno = stuwork.sno and subject.cno = stuwork.suno and name != '张志国';
+------+--------+------+--------+-----------+-------------+------+----------------+-------+-------+---------+--------+--------+---------+
| sno  | name   | sex  | class  | data      | phone       | cno  | cname          | score | xtime | teacher | oscore | tscore | thscore |
+------+--------+------+--------+-----------+-------------+------+----------------+-------+-------+---------+--------+--------+---------+
| 0538 | 于兰兰 | 女   | 生物05 | 1984-2-20 | 1331200**** | K002 | 计算机应用基础 |   3.0 | 48    | 任泉    |     65 |     75 |      85 |
| 0538 | 于兰兰 | 女   | 生物05 | 1984-2-20 | 1331200**** | S001 | 高等数学       |   3.0 | 48    | 赵晓尘  |     60 |   NULL |      80 |
+------+--------+------+--------+-----------+-------------+------+----------------+-------+-------+---------+--------+--------+---------+
2 rows in set (0.00 sec)

子查询

mysql> select student.sno,name,sex,class,data,phone,cno,cname,score,xtime,teacher,oscore,tscore,thscore
    -> from student,subject,stuwork
    -> where name != '张志国' and student.sno = stuwork.sno and subject.cno = stuwork.suno and class = (select class
    -> from student
    -> where name = '张志国');
+------+--------+------+--------+-----------+-------------+------+----------------+-------+-------+---------+--------+--------+---------+
| sno  | name   | sex  | class  | data      | phone       | cno  | cname          | score | xtime | teacher | oscore | tscore | thscore |
+------+--------+------+--------+-----------+-------------+------+----------------+-------+-------+---------+--------+--------+---------+
| 0538 | 于兰兰 | 女   | 生物05 | 1984-2-20 | 1331200**** | K002 | 计算机应用基础 |   6.0 | 48    | 任泉    |     65 |     75 |      85 |
| 0538 | 于兰兰 | 女   | 生物05 | 1984-2-20 | 1331200**** | S001 | 高等数学       |   6.0 | 48    | 赵晓尘  |     60 |   NULL |      80 |
+------+--------+------+--------+-----------+-------------+------+----------------+-------+-------+---------+--------+--------+---------+
2 rows in set (0.00 sec)

2.查询比“计算机应用基础”学时多的课程信息（使用连接查询和子查询方式）。

连接查询

mysql> select student.sno,name,sex,class,data,phone,cno,cname,score,xtime,teacher,oscore,tscore,thscore
    -> from student,subject,stuwork
    -> where student.sno = stuwork.sno and subject.cno = stuwork.suno and xtime > 48;
+------+--------+------+--------+-----------+-------------+------+------------+-------+-------+---------+--------+--------+---------+
| sno  | name   | sex  | class  | data      | phone       | cno  | cname      | score | xtime | teacher | oscore | tscore | thscore |
+------+--------+------+--------+-----------+-------------+------+------------+-------+-------+---------+--------+--------+---------+
| 0496 | 李越   | 男   | 电子04 | 1984-2-23 | 1381290**** | M001 | 政治经济学 |   8.0 | 64    | 孔繁新  |     70 |     70 |      80 |
| 0531 | 张志国 | 男   | 生物05 | 1986-9-10 | 1331256**** | K006 | 数据结构   |   8.0 | 64    | 马跃先  |     80 |     80 |      90 |
| 0591 | 王丽丽 | 女   | 电子05 | 1984-3-20 | 1332080**** | M001 | 政治经济学 |   8.0 | 64    | 孔繁新  |     65 |     75 |      75 |
| 0591 | 王丽丽 | 女   | 电子05 | 1984-3-20 | 1332080**** | K006 | 数据结构   |   8.0 | 64    | 马跃先  |     80 |     80 |      80 |
+------+--------+------+--------+-----------+-------------+------+------------+-------+-------+---------+--------+--------+---------+
4 rows in set (0.00 sec)

 子查询

mysql> select student.sno,name,sex,class,data,phone,cno,cname,score,xtime,teacher,oscore,tscore,thscore
    -> from student,subject,stuwork
    -> where student.sno = stuwork.sno and subject.cno = stuwork.suno and (xtime > any (select xtime
    -> from subject
    -> where cname = '计算机应用基础'));
+------+--------+------+--------+-----------+-------------+------+------------+-------+-------+---------+--------+--------+---------+
| sno  | name   | sex  | class  | data      | phone       | cno  | cname      | score | xtime | teacher | oscore | tscore | thscore |
+------+--------+------+--------+-----------+-------------+------+------------+-------+-------+---------+--------+--------+---------+
| 0496 | 李越   | 男   | 电子04 | 1984-2-23 | 1381290**** | M001 | 政治经济学 |   8.0 | 64    | 孔繁新  |     70 |     70 |      80 |
| 0531 | 张志国 | 男   | 生物05 | 1986-9-10 | 1331256**** | K006 | 数据结构   |   8.0 | 64    | 马跃先  |     80 |     80 |      90 |
| 0591 | 王丽丽 | 女   | 电子05 | 1984-3-20 | 1332080**** | M001 | 政治经济学 |   8.0 | 64    | 孔繁新  |     65 |     75 |      75 |
| 0591 | 王丽丽 | 女   | 电子05 | 1984-3-20 | 1332080**** | K006 | 数据结构   |   8.0 | 64    | 马跃先  |     80 |     80 |      80 |
+------+--------+------+--------+-----------+-------------+------+------------+-------+-------+---------+--------+--------+---------+
4 rows in set (0.01 sec)

3.查询选修课程号为K002的学生的学号、姓名（使用连接查询、普通子查询、相关子查询、使用exists关键字的相关子查询）。

连接查询

mysql> select student.sno,name
    -> from student,stuwork
    -> where student.sno = stuwork.sno and suno = 'K002';
+------+--------+
| sno  | name   |
+------+--------+
| 0496 | 李越   |
| 0529 | 赵欣   |
| 0531 | 张志国 |
| 0538 | 于兰兰 |
| 0592 | 王海强 |
+------+--------+
5 rows in set (0.00 sec)

普通子查询

mysql> select sno,name
    -> from student
    -> where (sno = any (select sno
    -> from stuwork
    -> where suno = 'K002'));
+------+--------+
| sno  | name   |
+------+--------+
| 0496 | 李越   |
| 0529 | 赵欣   |
| 0531 | 张志国 |
| 0538 | 于兰兰 |
| 0592 | 王海强 |
+------+--------+
5 rows in set (0.00 sec)

相关子查询

mysql> select sno,name
    -> from student
    -> where ('K002' in (select suno
    -> from stuwork
    -> where student.sno = stuwork.sno));
+------+--------+
| sno  | name   |
+------+--------+
| 0496 | 李越   |
| 0529 | 赵欣   |
| 0531 | 张志国 |
| 0538 | 于兰兰 |
| 0592 | 王海强 |
+------+--------+
5 rows in set (0.02 sec)

使用exists关键字的相关子查询

mysql> select sno,name
    -> from student
    -> where exists (select *
    -> from stuwork
    -> where student.sno = stuwork.sno and suno = 'K002');
+------+--------+
| sno  | name   |
+------+--------+
| 0496 | 李越   |
| 0529 | 赵欣   |
| 0531 | 张志国 |
| 0538 | 于兰兰 |
| 0592 | 王海强 |
+------+--------+
5 rows in set (0.00 sec)

4.查询没有选修K001和M001课程的学号、课程号和三次成绩（使用子查询）。

mysql> select suno,sno,oscore,tscore,thscore
    -> from stuwork
    -> where (suno = any (select cno
    -> from subject
    -> where cno != 'K001' and cno != 'M001'));
+------+------+--------+--------+---------+
| suno | sno  | oscore | tscore | thscore |
+------+------+--------+--------+---------+
| 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 |
| S001 | 0531 |     80 |     80 |      80 |
| S001 | 0538 |     60 |   NULL |      80 |
+------+------+--------+--------+---------+
9 rows in set (0.01 sec)

数据操纵

1.在学生表中添加一条学生记录，其中，学号为0593，姓名为张乐，性别为男，专业班级为电子05。

mysql> use stuworkdata;
Database changed
mysql> insert into student values('0593','张乐','男','电子05',null,null);
Query OK, 1 row affected (0.04 sec)

2.将所有课程的学分数变为原来的两倍。

mysql> update subject set score = score*2;
Query OK, 5 rows affected (0.02 sec)

3.删除张乐的信息。

mysql> delete from student where name = '张乐';
Query OK, 1 row affected (0.03 sec)