2021-02-28

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