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)
-
查看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