2021-02-28

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