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分，并将教学老师改为cuimengMysql练习题答案
1.登录数据库
[root@web01 ~]# mysql -uroot -p3308 -h127.0.0.1 -P 3308 -S /data/3308/mysql.socket2.创建数据库并制定校验规则和字符集
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)- 查看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
本文章为转载内容，我们尊重原作者对文章享有的著作权。如有内容错误或侵权问题，欢迎原作者联系我们进行内容更正或删除文章。
