数据库题目和答案

Mysql练习题

总结：drop 删库，删表，删字段
rename 修改表名
change 修改 字段名
delect from biaoming where 删掉具体的表里面的内容
update biaoming set 修改内容1，修改内容2 where 具体修改表里的内容

1.在10.0.0.51以root账户登入mysql
[root@web03 ~]# mysql -uroot -p1
=========================================================================
# 建表数据属性参考：
not null： 		非空
primary key： 	主键（唯一且非空的）
auto_increment： 自增（此列必须是：primary key或者unique key）
unique key： 	单独的唯一的
default： 		默认值
unsigned： 		无符号，非负数		#添加unsigned属性，会加到数据属性中，所以把这个属性写到数据属性后面
comment： 		注释
primary key = unique key + not null
=========================================================================


2.新建库名为oldboy的库，库内建三个表，如下（建表附属要求：尾列加入每行的注释，如：name最后注释为：姓名）

%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%
#创建oldboy数据库并制定字符集和校验规则
mysql> create database  if not exists oldboy collate utf8_general_ci charset utf8;
Query OK, 1 row affected (0.00 sec)
#查看创建的oldboy库
mysql> show create database oldboy;
+----------+-----------------------------------------------------------------+
| Database | Create Database                                                 |
+----------+-----------------------------------------------------------------+
| oldboy   | CREATE DATABASE `oldboy` /*!40100 DEFAULT CHARACTER SET utf8 */ |
+----------+-----------------------------------------------------------------+
1 row in set (0.00 sec)
%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%


2.1 表1为：student，包含以下内容	(已知学生名称：yao，daji，agui，wukong)  # 含学生的基本信息

	stu_id			int			   注释为：'学生编号'	# 主键（唯一且非空的）；且自增
	stu_name		varchar(10)		注释为：'姓名'
	stu_age			tinyint			注释为：'年龄'
	stu_gender		men，women，中性  注释为：'性别'
	stu_tel			char(11)		注释为：'手机号'
	stu_cometime	datetime		注释为：'入学时间'
	
%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%
#带数据属性创建student表
mysql> create table oldboy.student(stu_id int not null primary key auto_increment comment'学生编号',stu_name varchar(10) not null comment '学生姓名', 
       stu_age tinyint unsigned not null comment '年龄', 
       stu_gender enum('man','girl') not null default 'man' comment '性别',  
       stu_tel char(11) not null comment '手机号码',  
       stu_cometime datetime  default now() comment '入学时间');
#给student表插入相应数据     
mysql> insert into oldboy.student values('1','yao','16','girl','12345678910',now()),('2','daji','17','girl','12345678911',now()),('3','agui','18','girl','12345678912',now()),('4','wukong','19','man','12345678913',now());
Query OK, 4 rows affected (0.02 sec)
Records: 4  Duplicates: 0  Warnings: 0
#查看student表数据
mysql> select * from oldboy.student;
+--------+----------+---------+------------+-------------+---------------------+
| stu_id | stu_name | stu_age | stu_gender | stu_tel     | stu_cometime        |
+--------+----------+---------+------------+-------------+---------------------+
|      1 | yao      |      16 | girl       | 12345678910 | 2021-02-25 19:21:28 |
|      2 | daji     |      17 | girl       | 12345678911 | 2021-02-25 19:21:28 |
|      3 | agui     |      18 | girl       | 12345678912 | 2021-02-25 19:21:28 |
|      4 | wukong   |      19 | man        | 12345678913 | 2021-02-25 19:21:28 |
+--------+----------+---------+------------+-------------+---------------------+
4 rows in set (0.01 sec)
%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%

       
2.2 表2为kcb，包含以下内容	# 含学生的科目与教学老师信息

	kcb_id		注释为：'课程编号'
	kcb_name	注释为：'课程名称'
	kcb_techer	注释为：'教学老师'
	
	需对应教学科目信息：
	老刘	语文
	egon  数学
	陈阳	英语
	崔萌	化学
	
%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%	
#带数据属性创建kcb表
mysql> create table oldboy.kcb(kcb_id int not null primary key auto_increment comment '课程编码',
    -> kcb_name varchar(10) not null comment '课程名称',
    -> kcb_techer varchar(10) not null comment '教学老师');
Query OK, 0 rows affected (0.02 sec)
#给kcb表插入相应数据
mysql> insert into oldboy.kcb values ('1','语文','老刘'),('2','数学','egon'),('3',' 英语','陈阳'),('4','化学','崔萌');
Query OK, 4 rows affected (0.00 sec)
Records: 4  Duplicates: 0  Warnings: 0
#查看kcb表数据
mysql> select * from oldboy.kcb;
+--------+----------+------------+
| kcb_id | kcb_name | kcb_techer |
+--------+----------+------------+
|      1 | 语文     | 老刘       |
|      2 | 数学     | egon       |
|      3 | 英语     | 陈阳       |
|      4 | 化学     | 崔萌       |
+--------+----------+------------+
4 rows in set (0.00 sec)
%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%	


2.3 表3为score，包含学生的分数信息
	score_id	注释为：'成绩编号'
	stu_id		注释为：'学生编号'
	kcb_id		注释为：'课程编号'
	score_fs 	注释为：'考试分数'
已知学生名称：yao，daji，agui，wukong	#成绩示例：（分数单独放在表3内）
		语文		数学		英语		化学
yao ：	40		  70	    80		 20
daji：	50		  70	    27		 20
agui：	60		  38	    80		 18
wukong：	60		  70	    98		 20

%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%	
#带数据属性创建score表
mysql> create table oldboy.score(score_id int not null primary key auto_increment comment '成绩编号',
    -> stu_id int not null comment '学生编号',
    -> kcb_id int not null comment '课程编号',
    -> score_fenshu int not null comment '分数');
Query OK, 0 rows affected (0.01 sec)
#给score表插入相应数据
mysql> insert into oldboy.score values(1,1,1,40),(2,1,2,70),(3,1,3,80),(4,1,4,20),(5,2,1,50),(6,2,2,70),(7,2,3,27),(8,2,4,20),(9,3,1,60),(10,3,2,38),(11,3,3,80),(12,3,4,18),(13,4,1,60),(14,4,2,70),(15,4,3,98),(16,4,4,20);
Query OK, 16 rows affected (0.01 sec)
Records: 16  Duplicates: 0  Warnings: 0
#查看score表的数据
mysql> select * from oldboy.score;
+----------+--------+--------+--------------+
| score_id | stu_id | kcb_id | score_fenshu |
+----------+--------+--------+--------------+
|        1 |      1 |      1 |           40 |
|        2 |      1 |      2 |           70 |
|        3 |      1 |      3 |           80 |
|        4 |      1 |      4 |           20 |
|        5 |      2 |      1 |           50 |
|        6 |      2 |      2 |           70 |
|        7 |      2 |      3 |           27 |
|        8 |      2 |      4 |           20 |
|        9 |      3 |      1 |           60 |
|       10 |      3 |      2 |           38 |
|       11 |      3 |      3 |           80 |
|       12 |      3 |      4 |           18 |
|       13 |      4 |      1 |           60 |
|       14 |      4 |      2 |           70 |
|       15 |      4 |      3 |           98 |
|       16 |      4 |      4 |           20 |
+----------+--------+--------+--------------+
16 rows in set (0.00 sec)
%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%	



3.新建用户wp，并针对oldboy库给wp用户授权，其权限如下；授权网段为10.0.0.%，且密码为1
	select,delete,update,insert
#创建wp用户
mysql> create user wp@'10.0.0.%' identified by '1';
Query OK, 0 rows affected (0.02 sec)
#给wp用户授权限


4.退出mysql，用wp用户登入mysql，用wp用户来执行后续操作
	# PS：期间wp登录报错的问题自己解决

5.1 查看agui的所有科目分数、教学老师

5.2 查看agui的所有科目分数、教学老师，且只显示agui的一个名字

6.查看低于60分的名字、科目、教学老师

7.查看agui低于60分的名字、科目，并改为70分

8.查看所有低于50分同学的名字、科目、教学老师，并将分数置零

9.查看所有分数为0的同学名字、科目，并改为60分

10.将agui的语文分数改为59.9分，并将教学老师改为cuimeng


 

Mysql练习题答案

1.登录数据库

[root@web01 ~]# mysql -uroot -p3308 -h127.0.0.1 -P 3308 -S /data/3308/mysql.socket

2.创建数据库并制定校验规则和字符集

mysql> create database if not exists oldboy charset utf8 collate utf8_general_ci;
Query OK, 1 row affected (0.00 sec)

3.查看已经创建的库

mysql> show create database oldboy;
+----------+-----------------------------------------------------------------+
| Database | Create Database                                                 |
+----------+-----------------------------------------------------------------+
| oldboy   | CREATE DATABASE `oldboy` /*!40100 DEFAULT CHARACTER SET utf8 */ |
+----------+-----------------------------------------------------------------+
1 row in set (0.00 sec)

4.创建student表

mysql> create table oldboy.student(stu_id int primary key auto_increment not null comment '学生编号',  stu_name varchar(20) not null comment '学生姓名',  stu_gender enum('boy','girl') default 'boy' not null comment '性别',  stu_age tinyint unsigned not null comment '年龄',  stu_tel char(11) not null comment '手机号',  stu_cometime datetime not null default now() comment '入学时间');
Query OK, 0 rows affected (0.02 sec)

5.插入数据

mysql> insert into oldboy.student(stu_id,stu_name,stu_gender,stu_age,stu_tel,stu_cometime) values(1,'yao','boy',16,'12345678901',now()),(2,'daji','girl',17,'12345678902',now()),(3,'agui','girl',18,'12345678903',now()),(4,'wukong','boy',19,'12345678904',now());
Query OK, 4 rows affected (0.01 sec)

6.查看表

mysql> select * from oldboy.student;
+--------+----------+------------+---------+-------------+---------------------+
| stu_id | stu_name | stu_gender | stu_age | stu_tel     | stu_cometime        |
+--------+----------+------------+---------+-------------+---------------------+
|      1 | yao      | boy        |      16 | 12345678901 | 2021-02-28 11:30:47 |
|      2 | daji     | girl       |      17 | 12345678902 | 2021-02-28 11:30:47 |
|      3 | agui     | girl       |      18 | 12345678903 | 2021-02-28 11:30:47 |
|      4 | wukong   | boy        |      19 | 12345678904 | 2021-02-28 11:30:47 |
+--------+----------+------------+---------+-------------+---------------------+

7.创建课程表

mysql> create table oldboy.kcb(kcb_id int not null primary key auto_increment comment '课程编号',
    -> kcb_name varchar(20) not null comment '课程名称',
    -> kcb_teacher varchar(20) not null comment '授课老师');
Query OK, 0 rows affected (0.01 sec)


mysql> desc oldboy.kcb;
+-------------+-------------+------+-----+---------+----------------+
| Field       | Type        | Null | Key | Default | Extra          |
+-------------+-------------+------+-----+---------+----------------+
| kcb_id      | int(11)     | NO   | PRI | NULL    | auto_increment |
| kcb_name    | varchar(20) | NO   |     | NULL    |                |
| kcb_teacher | varchar(20) | NO   |     | NULL    |                |
+-------------+-------------+------+-----+---------+----------------+

8.插入课程表信息

mysql> insert into oldboy.kcb(kcb_id,kcb_name,kcb_teacher) values(1,'语文','老刘'),  (2,'数学','egon'),  (3,' 英语','陈阳'),  (4,'化学','崔萌');
Query OK, 4 rows affected (0.00 sec)

mysql> select * from oldboy.kcb;
+--------+----------+-------------+
| kcb_id | kcb_name | kcb_teacher |
+--------+----------+-------------+
|      1 | 语文     | 老刘        |
|      2 | 数学     | egon        |
|      3 | 英语     | 陈阳        |
|      4 | 化学     | 崔萌        |
+--------+----------+-------------+

9.创建score表并查看表结构

mysql> create table oldboy.score(score_id int not null primary key auto_increment comment '成绩编号',  stu_id int not null comment '学生编号',  kcb_id int not null comment '课程编号',  score_fenshu int not null comment '学生分数');
Query OK, 0 rows affected (0.01 sec)


mysql> desc oldboy.score;
+--------------+---------+------+-----+---------+----------------+
| Field        | Type    | Null | Key | Default | Extra          |
+--------------+---------+------+-----+---------+----------------+
| score_id     | int(11) | NO   | PRI | NULL    | auto_increment |
| stu_id       | int(11) | NO   |     | NULL    |                |
| kcb_id       | int(11) | NO   |     | NULL    |                |
| score_fenshu | int(11) | NO   |     | NULL    |                |
+--------------+---------+------+-----+---------+----------------+

10.插入数据

mysql> insert into oldboy.score(score_id,stu_id,kcb_id,score_fenshu) values(1,1,1,'40'),(2,1,2,'70'),(3,1,3,'80'),(4,1,3,'20'),
    -> (5,2,1,'50'),(6,2,2,'70'),(7,2,3,'27'),(8,2,4,'20'),
    -> (9,3,1,'60'),(10,3,2,'38'),(11,3,3,'80'),(12,3,4,'18'),
    -> (14,4,1,'60'),(15,4,2,'70'),(16,4,3,'98'),(17,4,4,'20');
Query OK, 16 rows affected (0.00 sec)

11.查看score表数据

mysql> select * from oldboy.score;
+----------+--------+--------+--------------+
| score_id | stu_id | kcb_id | score_fenshu |
+----------+--------+--------+--------------+
|        1 |      1 |      1 |           40 |
|        2 |      1 |      2 |           70 |
|        3 |      1 |      3 |           80 |
|        4 |      1 |      3 |           20 |
|        5 |      2 |      1 |           50 |
|        6 |      2 |      2 |           70 |
|        7 |      2 |      3 |           27 |
|        8 |      2 |      4 |           20 |
|        9 |      3 |      1 |           60 |
|       10 |      3 |      2 |           38 |
|       11 |      3 |      3 |           80 |
|       12 |      3 |      4 |           18 |
|       14 |      4 |      1 |           60 |
|       15 |      4 |      2 |           70 |
|       16 |      4 |      3 |           98 |
|       17 |      4 |      4 |           20 |
+----------+--------+--------+--------------+
注意：
mysql> select * from oldboy.score where score_id=4;（发现kcb_id=3 显然错误了应该为4，所以修改）
+----------+--------+--------+--------------+
| score_id | stu_id | kcb_id | score_fenshu |
+----------+--------+--------+--------------+
|        4 |      1 |      3 |           20 |
+----------+--------+--------+--------------+

mysql> update oldboy.score set kcb_id=4  where score_id=4;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from oldboy.score where score_id=4;
+----------+--------+--------+--------------+
| score_id | stu_id | kcb_id | score_fenshu |
+----------+--------+--------+--------------+
|        4 |      1 |      4 |           20 |
+----------+--------+--------+--------------+

12.发现错误score_id在12以后，突然跳过13了，我们要修改过来

mysql> select * from oldboy.score;
+----------+--------+--------+--------------+
| score_id | stu_id | kcb_id | score_fenshu |
+----------+--------+--------+--------------+
|        1 |      1 |      1 |           40 |
|        2 |      1 |      2 |           70 |
|        3 |      1 |      3 |           80 |
|        4 |      1 |      4 |           20 |
|        5 |      2 |      1 |           50 |
|        6 |      2 |      2 |           70 |
|        7 |      2 |      3 |           27 |
|        8 |      2 |      4 |           20 |
|        9 |      3 |      1 |           60 |
|       10 |      3 |      2 |           38 |
|       11 |      3 |      3 |           80 |
|       12 |      3 |      4 |           18 |
|       14 |      4 |      1 |           60 |
|       15 |      4 |      2 |           70 |
|       16 |      4 |      3 |           98 |
|       17 |      4 |      4 |           20 |
+----------+--------+--------+--------------+

13.修改

mysql> select * from oldboy.score where score_id>=14;
+----------+--------+--------+--------------+
| score_id | stu_id | kcb_id | score_fenshu |
+----------+--------+--------+--------------+
|       14 |      4 |      1 |           60 |
|       15 |      4 |      2 |           70 |
|       16 |      4 |      3 |           98 |
|       17 |      4 |      4 |           20 |
+----------+--------+--------+--------------+


mysql> delete from oldboy.score where score_id>=14;
Query OK, 4 rows affected (0.00 sec)(哇靠直接删除下面，重新插入！！！！看好了delete from 里面是没有*的，闪瞎眼睛了吧！！1!)
mysql> insert into oldboy.score(score_id,stu_id,kcb_id,score_fenshu) values(13,4,1,'60'),(14,4,2,'70'),(15,4,3,'98'),(16,4,4,'20');

mysql> select * from oldboy.score;                                                                            
+----------+--------+--------+--------------+
| score_id | stu_id | kcb_id | score_fenshu |
+----------+--------+--------+--------------+
|        1 |      1 |      1 |           40 |
|        2 |      1 |      2 |           70 |
|        3 |      1 |      3 |           80 |
|        4 |      1 |      4 |           20 |
|        5 |      2 |      1 |           50 |
|        6 |      2 |      2 |           70 |
|        7 |      2 |      3 |           27 |
|        8 |      2 |      4 |           20 |
|        9 |      3 |      1 |           60 |
|       10 |      3 |      2 |           38 |
|       11 |      3 |      3 |           80 |
|       12 |      3 |      4 |           18 |
|       13 |      4 |      1 |           60 |
|       14 |      4 |      2 |           70 |
|       15 |      4 |      3 |           98 |
|       16 |      4 |      4 |           20 |
+----------+--------+--------+--------------+

现在三个表都建立好了，接下来创建用户wp，并授权在10.0.0.%登录授予 select,delete,update,insert权限做题目

mysql> create user wp;
Query OK, 0 rows affected (0.01 sec)
mysql> create user wp@'10.0.0.%' identified by '123';
Query OK, 0 rows affected (0.00 sec)

1. 查看agui的所有科目分数、教学老师

   mysql> select a.stu_name as '学生姓名',b.kcb_teacher as '课程老师',b.kcb_name as '课程名字',c.score_fenshu '课程分数' from student a,kcb  b,score c where a.stu_id=c.stu_id and b.kcb_id=c.kcb_id  and a.stu_id=3;
   +--------------+--------------+--------------+--------------+
   | 学生姓名     | 课程老师     | 课程名字     | 课程分数     |
   +--------------+--------------+--------------+--------------+
   | agui         | 老刘         | 语文         |           60 |
   | agui         | egon         | 数学         |           38 |
   | agui         | 陈阳         | 英语         |           80 |
   | agui         | 崔萌         | 化学         |           18 |
   +--------------+--------------+--------------+--------------+
   

2.查看agui的所有科目分数、教学老师，且只显示agui的一个名字

3.查看低于60分的名字、科目、教学老师

mysql> select a.stu_name as '学生名称',b.kcb_name as '课程名称',b.kcb_teacher as '代课老师' from student a,kcb b,score c  where a.stu_id=c.stu_id and b.kcb_id=c.kcb_id and c.score_fenshu<60;
+--------------+--------------+--------------+
| 学生名称     | 课程名称     | 代课老师     |
+--------------+--------------+--------------+
| yao          | 语文         | 老刘         |
| yao          | 化学         | 崔萌         |
| daji         | 语文         | 老刘         |
| daji         | 英语         | 陈阳         |
| daji         | 化学         | 崔萌         |
| agui         | 数学         | egon         |
| agui         | 化学         | 崔萌         |
| wukong       | 化学         | 崔萌         |
+--------------+--------------+--------------+
8 rows in set (0.00 sec)

4.查看agui低于60分的名字、科目，并改为70分

mysql> select a.stu_id as '学生姓名',b.kcb_name as '课程名称'，c.score_fenshu as '课程分数' from student a,kcb b,score c where a.stu_id=c.stu_id and b.kcb_id=c.kcb_id  and a.stu_name='agui' and c.score_fenshu<60
+--------------+--------------+--------------+
| 学生姓名     | 课程名称     | 课程分数     |
+--------------+--------------+--------------+
| agui         | 数学         |           38 |
| agui         | 化学         |           18 |
+--------------+--------------+--------------+
2 rows in set (0.00 sec)


mysql> update score set score_fenshu='70' where stu_id=3 and kcb_id=2;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> update score set score_fenshu='70' where stu_id=3 and kcb_id=4;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from score;
+----------+--------+--------+--------------+
| score_id | stu_id | kcb_id | score_fenshu |
+----------+--------+--------+--------------+
|        1 |      1 |      1 |           40 |
|        2 |      1 |      2 |           70 |
|        3 |      1 |      3 |           80 |
|        4 |      1 |      4 |           20 |
|        5 |      2 |      1 |           50 |
|        6 |      2 |      2 |           70 |
|        7 |      2 |      3 |           27 |
|        8 |      2 |      4 |           20 |
|        9 |      3 |      1 |           60 |
|       10 |      3 |      2 |           70 |
|       11 |      3 |      3 |           80 |
|       12 |      3 |      4 |           70 |
|       13 |      4 |      1 |           60 |
|       14 |      4 |      2 |           70 |
|       15 |      4 |      3 |           98 |
|       16 |      4 |      4 |           20 |
+----------+--------+--------+--------------+

5.查看所有低于50分同学的名字、科目、教学老师，并将分数置零

mysql> select a.stu_name as '学生姓名', b.kcb_teacher as '代课老师',b.kcb_name as '课程名字',c.score_fenshu as '学生分数' from student a, kcb b,score c  where a.stu_id=c.stu_id and b.kcb_id=c.kcb_id and c.score_fenshu<50;
+--------------+--------------+--------------+--------------+
| 学生姓名     | 代课老师     | 课程名字     | 学生分数     |
+--------------+--------------+--------------+--------------+
| yao          | 老刘         | 语文         |           40 |
| yao          | 崔萌         | 化学         |           20 |
| daji         | 陈阳         | 英语         |           27 |
| daji         | 崔萌         | 化学         |           20 |
+--------------+--------------+--------------+--------------+
4 rows in set (0.00 sec)


mysql> update score c set c.score_fenshu='0' where c.stu_id=3 and  c.kcb_id=4 or c.kcb_id=1;
Query OK, 5 rows affected (0.01 sec)
Rows matched: 5  Changed: 5  Warnings: 0

mysql> select * from score;
+----------+--------+--------+--------------+
| score_id | stu_id | kcb_id | score_fenshu |
+----------+--------+--------+--------------+
|        1 |      1 |      1 |            0 |
|        2 |      1 |      2 |           70 |
|        3 |      1 |      3 |           80 |
|        4 |      1 |      4 |           20 |
|        5 |      2 |      1 |            0 |
|        6 |      2 |      2 |           70 |
|        7 |      2 |      3 |           27 |
|        8 |      2 |      4 |           20 |
|        9 |      3 |      1 |            0 |
|       10 |      3 |      2 |           70 |
|       11 |      3 |      3 |           80 |
|       12 |      3 |      4 |            0 |
|       13 |      4 |      1 |            0 |
|       14 |      4 |      2 |           70 |
|       15 |      4 |      3 |           98 |
|       16 |      4 |      4 |          100 |
+----------+--------+--------+--------------+
16 rows in set (0.00 sec)

mysql> update score c set c.score_fenshu='0' where c.stu_id=2 and  c.kcb_id=4 or c.kcb_id=3;
Query OK, 5 rows affected (0.00 sec)
Rows matched: 5  Changed: 5  Warnings: 0

mysql> select * from score;
+----------+--------+--------+--------------+
| score_id | stu_id | kcb_id | score_fenshu |
+----------+--------+--------+--------------+
|        1 |      1 |      1 |            0 |
|        2 |      1 |      2 |           70 |
|        3 |      1 |      3 |            0 |
|        4 |      1 |      4 |           20 |
|        5 |      2 |      1 |            0 |
|        6 |      2 |      2 |           70 |
|        7 |      2 |      3 |            0 |
|        8 |      2 |      4 |            0 |
|        9 |      3 |      1 |            0 |
|       10 |      3 |      2 |           70 |
|       11 |      3 |      3 |            0 |
|       12 |      3 |      4 |            0 |
|       13 |      4 |      1 |            0 |
|       14 |      4 |      2 |           70 |
|       15 |      4 |      3 |            0 |
|       16 |      4 |      4 |          100 |
+----------+--------+--------+--------------+
16 rows in set (0.00 sec)

6.查看所有分数为0的同学名字、科目，并改为60分

查看：
mysql> select a.stu_name,b.kcb_name,c.score_fenshu from student a,kcb b,score c where a.stu_id=c.stu_id and b.kcb_id=c.kcb_id and c.score_fenshu=0;
+----------+----------+--------------+
| stu_name | kcb_name | score_fenshu |
+----------+----------+--------------+
| yao      | 语文     |            0 |
| yao      | 英语     |            0 |
| daji     | 语文     |            0 |
| daji     | 英语     |            0 |
| daji     | 化学     |            0 |
| agui     | 语文     |            0 |
| agui     | 英语     |            0 |
| agui     | 化学     |            0 |
| wukong   | 语文     |            0 |
| wukong   | 英语     |            0 |
+----------+----------+--------------+
10 rows in set (0.00 sec)

修改：

mysql> update student a,kcb b,score c  set c.score_fenshu=60  where a.stu_id=c.stu_id and b.kcb_id=c.kcb_id and c.score_fenshu=0;
Query OK, 10 rows affected, 1 warning (0.00 sec)
Rows matched: 10  Changed: 10  Warnings: 0

mysql> select * from score;
+----------+--------+--------+--------------+
| score_id | stu_id | kcb_id | score_fenshu |
+----------+--------+--------+--------------+
|        1 |      1 |      1 |           60 |
|        2 |      1 |      2 |           70 |
|        3 |      1 |      3 |           60 |
|        4 |      1 |      4 |           20 |
|        5 |      2 |      1 |           60 |
|        6 |      2 |      2 |           70 |
|        7 |      2 |      3 |           60 |
|        8 |      2 |      4 |           60 |
|        9 |      3 |      1 |           60 |
|       10 |      3 |      2 |           70 |
|       11 |      3 |      3 |           60 |
|       12 |      3 |      4 |           60 |
|       13 |      4 |      1 |           60 |
|       14 |      4 |      2 |           70 |
|       15 |      4 |      3 |           60 |
|       16 |      4 |      4 |          100 |
+----------+--------+--------+--------------+
16 rows in set (0.00 sec)

7.将agui的语文分数改为59.9分，并将教学老师改为cuimeng

mysql> select a.stu_name,b.kcb_teacher,c.score_fenshu from student a,kcb b,score c where a.stu_id=c.stu_id and b.kcb_id=c.kcb_id and b.kcb_id=1 and a.stu_name='agui';
+----------+-------------+--------------+
| stu_name | kcb_teacher | score_fenshu |
+----------+-------------+--------------+
| agui     | 老刘        |           60 |
+----------+-------------+--------------+
1 row in set (0.00 sec)

mysql>  update student a,kcb b,score c set b.kcb_teacher='yuzhaoyang',c.score_fenshu='59' where a.stu_id=c.stu_id and b.kcb_id=c.kcb_id and b.kcb_id=1 and a.stu_name='agui';
Query OK, 2 rows affected, 1 warning (0.01 sec)
Rows matched: 2  Changed: 2  Warnings: 0