rhce-09 第21-22题

题目列表

21.配置一个数据库

22.数据库查询

21.配置一个数据库

在server0上部署MariaDB。要求如下：

1）仅允许从system1系统上使用登陆到数据库。

2）登陆数据库所用的账号为root,密码为redhat。

3）从http://content.example.com/courses/rhce/rhel7.0/materials/mariadb/mariadb.dump上下载文件，并将其恢复为legacy库。

4）为此数据库创建一个名为bob的用户，并要求这个用户对数据库legacy有选择操作权限。

注意事项

　　本题需要注意做题步骤，如果在root文件夹下先下载了mariadb.dump文件的话，就会无法安装mariadb
这时只能删掉mariadb.dump，yum install mariadb才能成功！同理，要yum remove mariadb的话，也要先删掉mariadb.dump才能成功！

安装mariadb

1
2
3

[root@localhost ~]# yum install mariadb* -y
[root@localhost ~]# systemctl restart mariadb
[root@localhost ~]# systemctl enable mariadb

初始化

[root@localhost ~]# mysql_secure_installation 
/usr/bin/mysql_secure_installation: line 379: find_mysql_client: command not found

NOTE: RUNNING ALL PARTS OF THIS SCRIPT IS RECOMMENDED FOR ALL MariaDB
      SERVERS IN PRODUCTION USE!  PLEASE READ EACH STEP CAREFULLY!

In order to log into MariaDB to secure it, we'll need the current
password for the root user.  If you've just installed MariaDB, and
you haven't set the root password yet, the password will be blank,
so you should just press enter here.

Enter current password for root (enter for none): #初始密码为空，因此这里直接回车
OK, successfully used password, moving on...

Setting the root password ensures that nobody can log into the MariaDB
root user without the proper authorisation.

Set root password? [Y/n] y #输入y回车，设置root密码
New password: [redhat]
Re-enter new password: [redhat]
Password updated successfully!

#之后的选项一律回车即可。

导入数据、建立用户

1 2	[root@localhost ~]# wget http://content.example.com/courses/rhce/rhel7.0/materials/mariadb/mariadb.dump [root@localhost ~]# ls

[root@localhost ~]# mysql -u root -predhat
MariaDB [(none)]> create database legacy;
MariaDB [(none)]> use legacy;
MariaDB [legacy]> source /root/mariadb.dump
MariaDB [legacy]> grant select on legacy.* to bob@localhost identified by 'redhat';
MariaDB [legacy]> quit
Bye
[root@localhost ~]# 
[root@localhost ~]# firewall-cmd --add-service=mysql --permanent
[root@localhost ~]# firewall-cmd --reload

22.数据库查询

数据查询填空

在system1上登陆数据库，查看legacy库进行查询，并将结果填入相应的框格中。

查询类别为Servers的产品的数量____。

#首先查看数据库legacy中所有数据表
[root@localhost ~]# mysql -u root -predhat
MariaDB [(none)]> use legacy;
MariaDB [legacy]> show tables;
+------------------+
| Tables_in_legacy |
+------------------+
| category         |
| manufacturer     |
| product          |
+------------------+
3 rows in set (0.02 sec)

MariaDB [legacy]> 




#查看每个表
MariaDB [legacy]> select * from category;
+----+------------+
| id | name       |
+----+------------+
|  1 | Networking |
|  2 | Servers    |
|  3 | Ssd        |
+----+------------+
3 rows in set (0.00 sec)

MariaDB [legacy]> select * from manufacturer;
+----+----------+----------------+-------------------+
| id | name     | seller         | phone_number      |
+----+----------+----------------+-------------------+
|  1 | SanDisk  | John Miller    | +1 (941) 329-8855 |
|  2 | Kingston | Mike Taylor    | +1 (341) 375-9999 |
|  3 | Asus     | Wilson Jackson | +1 (432) 367-8899 |
|  4 | Sony     | Allen Scott    | +1 (876) 213-4439 |
+----+----------+----------------+-------------------+
4 rows in set (0.00 sec)

MariaDB [legacy]> select * from product;
+----+-------------------+---------+-------+-------------+-----------------+
| id | name              | price   | stock | id_category | id_manufacturer |
+----+-------------------+---------+-------+-------------+-----------------+
|  1 | ThinkServer TS140 |  539.88 |    20 |           2 |               4 |
|  2 | ThinkServer RD630 | 2379.14 |    20 |           2 |               4 |
|  3 | RT-AC68U          |  219.99 |    10 |           1 |               3 |
|  4 | X110 64GB         |   73.84 |   100 |           3 |               1 |
+----+-------------------+---------+-------+-------------+-----------------+


#用合适的sql语句查询出结果
MariaDB [legacy]> select sum(stock) from product,category where category.id=product.id_category and category.name="Servers";
+------------+
| sum(stock) |
+------------+
|         40 |
+------------+
1 row in set (0.01 sec)

MariaDB [legacy]> 
MariaDB [legacy]> quit
Bye
[root@localhost ~]# 

#将答案40在网页上填好，提交即可

注意事项

数据库的题每次都会不同，要求考生熟练掌握sql语句。