Top

NTD WEBSEC DAY02

  1. 案例1：INSERT插入表记录
  2. 案例2：UPDATE和DELETE
  3. 案例3：SELECT查询表记录
  4. 案例4：WHERE简单匹配
  5. 案例5：WHERE高级匹配
  6. 案例6：备份数据库资料
  7. 案例7：恢复数据库资料

1 案例1：INSERT插入表记录

1.1 问题

本例要求以前一章的studb库hero表为基础，学会表格记录的多种不同插入方法，完成下列任务：
1）进入studb库，向 hero表批量插入如图-1所示数据
WEBSEC DAY02 - 图1
图-1
2）确认表格hero的数据内容

1.2 步骤

实现此案例需要按照如下步骤进行。
步骤一：进入studb库，向hero表批量插入数据
记录内容如下：
202012005 黄老邪 男 13500050005 东海桃花岛 48000
202012006 王重阳 男 13600060006 终南山全真教 48000
202012007 段王爷 男 13700070007 云南大理桃源山 48000
INSERT插入记录操作：
MariaDB [(none)]> INSERT INTO studb.hero VALUES (202012005,’黄老邪’,’男’,’13500050005’,’东海桃花岛’,48000),(202012006,’王重阳’,’男’,’13600060006’,’终南山全真教’,48000),(202012007,’段王爷’,’男’,’13700070007’,’云南大理桃源山’,48000);
Query OK, 3 rows affected (0.002 sec)
Records: 3 Duplicates: 0 Warnings: 0

MariaDB [(none)]>

步骤二：确认表格 hero 的数据内容
查看表格内容，确认新增加的3条记录。
MariaDB [(none)]> SELECT * FROM studb.hero;
+—————-+—————-+————+——————-+———————————-+———————+
| 学号 | 姓名 | 性别 | 手机号 | 籍贯 | 期望薪资 |
+—————-+—————-+————+——————-+———————————-+———————+
| 202012001 | 郭靖 | 男 | 13145201314 | 东海桃花岛 | 8000 |
| 202012002 | 黄蓉 | 女 | 13145201413 | 东海桃花岛 | 16000 |
| 202012003 | 华筝 | 女 | 13705666777 | 蒙古大营 | 12000 |
| 202012004 | 洪七公 | 男 | 18888888888 | 太湖北丐帮总舵 | 48000 |
| 202012005 | 黄老邪 | 男 | 13500050005 | 东海桃花岛 | 48000 |
| 202012006 | 王重阳 | 男 | 13600060006 | 终南山全真教 | 48000 |
| 202012007 | 段王爷 | 男 | 13700070007 | 云南大理桃源山 | 48000 |
+—————-+—————-+————+——————-+———————————-+———————+
7 rows in set (0.000 sec)

MariaDB [(none)]>

2 案例2：UPDATE和DELETE

2.1 问题

本例要求学会UPDATE和DELETE的语句的操作方法，完成下列任务：
1）修改 hero 表中的数据记录

  • 将 ‘洪七公’ 的手机号修改为 ‘13400040004’
  • 将 ‘黄老邪’ 的姓名修改为 ‘黄药师’
  • 为 ‘东海桃花岛’ 的人把期望薪资提高20%

2）删除 hero 表中 姓名=’黄老邪’ 的数据记录
3）确认表格 hero 的数据内容

2.2 步骤

实现此案例需要按照如下步骤进行。
步骤一：更新hero表中的数据记录
1）将 ‘洪七公’ 的手机号修改为 ‘13400040004’
MariaDB [(none)]> UPDATE studb.hero SET 手机号=’13400040004’ WHERE 姓名=’洪七公’;
Query OK, 1 row affected (0.001 sec)
Rows matched: 1 Changed: 1 Warnings: 0

MariaDB [(none)]>

2）将 ‘黄老邪’ 的姓名修改为 ‘黄药师’
MariaDB [(none)]> UPDATE studb.hero SET 姓名=’黄药师’ WHERE 姓名=’黄老邪’;
Query OK, 1 row affected (0.001 sec)
Rows matched: 1 Changed: 1 Warnings: 0

MariaDB [(none)]>

3）为 ‘东海桃花岛’ 的人把期望薪资提高20%
MariaDB [(none)]> UPDATE studb.hero SET 期望薪资=期望薪资*1.2 WHERE 籍贯=’东海桃花岛’;
Query OK, 3 rows affected (0.001 sec)
Rows matched: 3 Changed: 3 Warnings: 0

MariaDB [(none)]>

步骤二：删除hero表中 姓名=’黄老邪’ 的数据记录
此时表格中已经没有姓名为“黄老邪”的记录（只有“黄药师”），所以按给定的条件不会有记录被删除。
MariaDB [(none)]> DELETE FROM studb.hero WHERE 姓名=’黄老邪’;
Query OK, 0 rows affected (0.000 sec)

MariaDB [studb]>

步骤四：确认表格hero的数据内容
检查修改后的表格内容：
MariaDB [(none)]> SELECT * FROM studb.hero;
+—————-+—————-+————+——————-+———————————-+———————+
| 学号 | 姓名 | 性别 | 手机号 | 籍贯 | 期望薪资 |
+—————-+—————-+————+——————-+———————————-+———————+
| 202012001 | 郭靖 | 男 | 13145201314 | 东海桃花岛 | 9600 |
| 202012002 | 黄蓉 | 女 | 13145201413 | 东海桃花岛 | 19200 |
| 202012003 | 华筝 | 女 | 13705666777 | 蒙古大营 | 12000 |
| 202012004 | 洪七公 | 男 | 13400040004 | 太湖北丐帮总舵 | 48000 |
| 202012005 | 黄药师 | 男 | 13500050005 | 东海桃花岛 | 57600 |
| 202012006 | 王重阳 | 男 | 13600060006 | 终南山全真教 | 48000 |
| 202012007 | 段王爷 | 男 | 13700070007 | 云南大理桃源山 | 48000 |
+—————-+—————-+————+——————-+———————————-+———————+
7 rows in set (0.000 sec)

MariaDB [(none)]>

3 案例3：SELECT查询表记录

3.1 问题

本例要求学会SELECT语句的操作方法，查询hero表，完成下列任务：
1）列出表中每一条记录的 姓名、手机号
2）找出 性别=’女’ 的详细数据
3）性别=’女’ 的记录的 姓名、手机号
4）找出籍贯为 ‘东海桃花岛’ 的人的详细信息

3.2 步骤

实现此案例需要按照如下步骤进行。
步骤一：列出表中每一条记录的 姓名、手机号
MariaDB [(none)]> SELECT 姓名,手机号 FROM studb.hero;
+—————-+——————-+
| 姓名 | 手机号 |
+—————-+——————-+
| 郭靖 | 13145201314 |
| 黄蓉 | 13145201413 |
| 华筝 | 13705666777 |
| 洪七公 | 13400040004 |
| 黄药师 | 13500050005 |
| 王重阳 | 13600060006 |
| 段王爷 | 13700070007 |
+—————-+——————-+
7 rows in set (0.000 sec)

MariaDB [(none)]>

步骤二：找出 性别=’女’ 的详细数据
MariaDB [(none)]> SELECT * FROM studb.hero WHERE 性别=’女’;
+—————-+————+————+——————-+————————-+———————+
| 学号 | 姓名 | 性别 | 手机号 | 籍贯 | 期望薪资 |
+—————-+————+————+——————-+————————-+———————+
| 202012002 | 黄蓉 | 女 | 13145201413 | 东海桃花岛 | 19200 |
| 202012003 | 华筝 | 女 | 13705666777 | 蒙古大营 | 12000 |
+—————-+————+————+——————-+————————-+———————+
2 rows in set (0.000 sec)

MariaDB [(none)]>

步骤三：性别=’女’ 的记录的 姓名、手机号
MariaDB [(none)]> SELECT 姓名,手机号 FROM studb.hero WHERE 性别=’女’;
+————+——————-+
| 姓名 | 手机号 |
+————+——————-+
| 黄蓉 | 13145201413 |
| 华筝 | 13705666777 |
+————+——————-+
2 rows in set (0.000 sec)

MariaDB [(none)]>

步骤四：找出籍贯为 ‘东海桃花岛’ 的人的详细信息
MariaDB [(none)]> SELECT * FROM studb.hero WHERE 籍贯=’东海桃花岛’;
+—————-+—————-+————+——————-+————————-+———————+
| 学号 | 姓名 | 性别 | 手机号 | 籍贯 | 期望薪资 |
+—————-+—————-+————+——————-+————————-+———————+
| 202012001 | 郭靖 | 男 | 13145201314 | 东海桃花岛 | 9600 |
| 202012002 | 黄蓉 | 女 | 13145201413 | 东海桃花岛 | 19200 |
| 202012005 | 黄药师 | 男 | 13500050005 | 东海桃花岛 | 57600 |
+—————-+—————-+————+——————-+————————-+———————+
3 rows in set (0.000 sec)

MariaDB [(none)]>

4 案例4：WHERE简单匹配

4.1 问题

本例要求学会WHERE条件匹配的简单应用，针对hero表中的数据执行查询，完成下列任务：
1）找出籍贯不在 ‘东海桃花岛’ 的人的记录
2）找出期望薪资超过20000的人的姓名和期望薪资数额
3）找出籍贯为 ‘蒙古大营’ 或者 性别=’女’ 的人的记录
4）找出期望薪资在10000和20000之间的人的记录

4.2 步骤

实现此案例需要按照如下步骤进行。
步骤一：找出籍贯不在 ‘东海桃花岛’ 的人的记录
MariaDB [(none)]> SELECT * FROM studb.hero WHERE 籍贯 != ‘东海桃花岛’;
+—————-+—————-+————+——————-+———————————-+———————+
| 学号 | 姓名 | 性别 | 手机号 | 籍贯 | 期望薪资 |
+—————-+—————-+————+——————-+———————————-+———————+
| 202012003 | 华筝 | 女 | 13705666777 | 蒙古大营 | 12000 |
| 202012004 | 洪七公 | 男 | 13400040004 | 太湖北丐帮总舵 | 48000 |
| 202012006 | 王重阳 | 男 | 13600060006 | 终南山全真教 | 48000 |
| 202012007 | 段王爷 | 男 | 13700070007 | 云南大理桃源山 | 48000 |
+—————-+—————-+————+——————-+———————————-+———————+
4 rows in set (0.000 sec)

MariaDB [(none)]>

步骤二：找出期望薪资超过20000的人的姓名和期望薪资数额
MariaDB [(none)]> SELECT 姓名,期望薪资 FROM studb.hero WHERE 期望薪资 >= 20000;
+—————-+———————+
| 姓名 | 期望薪资 |
+—————-+———————+
| 洪七公 | 48000 |
| 黄药师 | 57600 |
| 王重阳 | 48000 |
| 段王爷 | 48000 |
+—————-+———————+
4 rows in set (0.001 sec)

MariaDB [(none)]>

步骤三：找出籍贯为 ‘蒙古大营’ 或者 性别=’女’ 的人的记录
MariaDB [(none)]> SELECT * FROM studb.hero WHERE 籍贯=’蒙古大营’ OR 性别=’女’;
+—————-+————+————+——————-+————————-+———————+
| 学号 | 姓名 | 性别 | 手机号 | 籍贯 | 期望薪资 |
+—————-+————+————+——————-+————————-+———————+
| 202012002 | 黄蓉 | 女 | 13145201413 | 东海桃花岛 | 19200 |
| 202012003 | 华筝 | 女 | 13705666777 | 蒙古大营 | 12000 |
+—————-+————+————+——————-+————————-+———————+
2 rows in set (0.000 sec)

MariaDB [(none)]>

步骤四：找出期望薪资在10000和20000之间的人的记录
MariaDB [(none)]> SELECT * FROM studb.hero WHERE 期望薪资 >= 10000 AND 期望薪资 <= 20000;
+—————-+————+————+——————-+————————-+———————+
| 学号 | 姓名 | 性别 | 手机号 | 籍贯 | 期望薪资 |
+—————-+————+————+——————-+————————-+———————+
| 202012002 | 黄蓉 | 女 | 13145201413 | 东海桃花岛 | 19200 |
| 202012003 | 华筝 | 女 | 13705666777 | 蒙古大营 | 12000 |
+—————-+————+————+——————-+————————-+———————+
2 rows in set (0.000 sec)

MariaDB [(none)]>

注意：WHERE条件子句适用于SELECT、UPDATE、DELETE操作

5 案例5：WHERE高级匹配

5.1 问题

本例要求学会WHERE条件匹配的高级应用，针对hero表中的数据执行查询，完成下列任务：
1）找出姓名 为 郭靖、华筝、黄药师、段王爷 的数据记录
2）找出籍贯包括 ‘全真教’ 字样的人的记录
3）找出姓名只有两个字的人的记录
4）找出姓黄的人的记录
5）找出手机号以131开头，倒数第二位是1的人的记录

5.2 步骤

实现此案例需要按照如下步骤进行。
步骤一：找出姓名 为 郭靖、华筝、黄药师、段王爷 的数据记录
MariaDB [(none)]> SELECT * FROM studb.hero WHERE 姓名 IN (‘郭靖’, ‘华筝’, ‘黄药师’, ‘段王爷’);
+—————-+—————-+————+——————-+———————————-+———————+
| 学号 | 姓名 | 性别 | 手机号 | 籍贯 | 期望薪资 |
+—————-+—————-+————+——————-+———————————-+———————+
| 202012001 | 郭靖 | 男 | 13145201314 | 东海桃花岛 | 9600 |
| 202012003 | 华筝 | 女 | 13705666777 | 蒙古大营 | 12000 |
| 202012005 | 黄药师 | 男 | 13500050005 | 东海桃花岛 | 57600 |
| 202012007 | 段王爷 | 男 | 13700070007 | 云南大理桃源山 | 48000 |
+—————-+—————-+————+——————-+———————————-+———————+
4 rows in set (0.000 sec)

MariaDB [(none)]>

步骤二：找出籍贯包括 ‘全真教’ 字样的人的记录
MariaDB [(none)]> SELECT * FROM studb.hero WHERE 籍贯 LIKE ‘%全真教%’;
+—————-+—————-+————+——————-+——————————+———————+
| 学号 | 姓名 | 性别 | 手机号 | 籍贯 | 期望薪资 |
+—————-+—————-+————+——————-+——————————+———————+
| 202012006 | 王重阳 | 男 | 13600060006 | 终南山全真教 | 48000 |
+—————-+—————-+————+——————-+——————————+———————+
1 row in set (0.000 sec)

MariaDB [(none)]>

步骤三：找出姓名只有两个字的人的记录
MariaDB [(none)]> SELECT * FROM studb.hero WHERE 姓名 LIKE ‘__’;
+—————-+————+————+——————-+————————-+———————+
| 学号 | 姓名 | 性别 | 手机号 | 籍贯 | 期望薪资 |
+—————-+————+————+——————-+————————-+———————+
| 202012001 | 郭靖 | 男 | 13145201314 | 东海桃花岛 | 9600 |
| 202012002 | 黄蓉 | 女 | 13145201413 | 东海桃花岛 | 19200 |
| 202012003 | 华筝 | 女 | 13705666777 | 蒙古大营 | 12000 |
+—————-+————+————+——————-+————————-+———————+
3 rows in set (0.000 sec)

MariaDB [(none)]>

步骤四：找出姓黄的人的记录
MariaDB [(none)]> SELECT * FROM studb.hero WHERE 姓名 LIKE ‘黄%’;
+—————-+—————-+————+——————-+————————-+———————+
| 学号 | 姓名 | 性别 | 手机号 | 籍贯 | 期望薪资 |
+—————-+—————-+————+——————-+————————-+———————+
| 202012002 | 黄蓉 | 女 | 13145201413 | 东海桃花岛 | 19200 |
| 202012005 | 黄药师 | 男 | 13500050005 | 东海桃花岛 | 57600 |
+—————-+—————-+————+——————-+————————-+———————+
2 rows in set (0.000 sec)

MariaDB [(none)]>

步骤五：找出手机号以131开头，倒数第二位是1的人的记录
MariaDB [(none)]> SELECT * FROM studb.hero WHERE 手机号 LIKE ‘131%1_’;
+—————-+————+————+——————-+————————-+———————+
| 学号 | 姓名 | 性别 | 手机号 | 籍贯 | 期望薪资 |
+—————-+————+————+——————-+————————-+———————+
| 202012001 | 郭靖 | 男 | 13145201314 | 东海桃花岛 | 9600 |
| 202012002 | 黄蓉 | 女 | 13145201413 | 东海桃花岛 | 19200 |
+—————-+————+————+——————-+————————-+———————+
2 rows in set (0.000 sec)

MariaDB [(none)]>

6 案例6：备份数据库资料

6.1 问题

本例要求使用mysqldump工具对数据库进行备份，熟悉单库、多库的不同备份用法，完成下列任务：
1）备份studb库，保存为/opt/studb.sql文件
2）备份studb库和mysql ，保存为/opt/studb+mysql.sql文件
3）比较两个结果文件的主要差别

6.2 步骤

实现此案例需要按照如下步骤进行。
步骤一：备份studb库，保存为/opt/studb.sql文件
root@kali:~# mysqldump -uroot -ppwd@123 studb > /opt/studb.sql

步骤二：备份studb库和mysql库，保存为/opt/studb+mysql.sql文件
root@kali:~# mysqldump -uroot -ppwd@123 —databases studb mysql > /opt/studb+mysql.sql

步骤三：比较两个结果文件的主要差别
1）以单库方式备份时，只备份库中的表格数据，不备份库本身
查看结果文件 /opt/studb.sql 会发现，其中并不包括 CREATE DATABASSE studb 的创建此数据库的操作。
root@kali:~# cat /opt/studb.sql //命令行下可使用cat查看文件
— MySQL dump 10.17 Distrib 10.3.20-MariaDB, for debian-linux-gnu (x86_64)
—
— Host: localhost Database: studb
— ———————————————————————————
— Server version 10.3.20-MariaDB-1

/!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT /;
/!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS /;
/!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION /;
/!40101 SET NAMES utf8mb4 /;
/!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE /;
/!40103 SET TIME_ZONE=’+00:00’ /;
/!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 /;
/!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 /;
/!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE=’NO_AUTO_VALUE_ON_ZERO’ /;
/!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 /;

—
— Table structure for table backup
—

DROP TABLE IF EXISTS backup;
/!40101 SET @saved_cs_client = @@character_set_client /;
/!40101 SET character_set_client = utf8 /;
CREATE TABLE backup (
学号 int(11) DEFAULT NULL,
姓名 varchar(20) DEFAULT NULL,
性别 char(1) DEFAULT NULL,
手机号 char(11) DEFAULT NULL,
籍贯 varchar(24) DEFAULT NULL,
期望薪资 int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
/!40101 SET character_set_client = @saved_cs_client /;

.. ..

如果是在kali图形桌面环境，也可以使用mousepad工具查看文件内容 —— 只需执行 mousepad /opt/studb.sql 就可以查看此文件，如图-1所示。
WEBSEC DAY02 - 图2
图-1
2）以多库方式备份时，既备份库中的表格数据，也备份库本身
查看结果文件 /opt/studb+mysql.sql 会发现，其中包括 CREATE DATABASSE studb 还有 CREATE DATABASE mysql 的创建对应数据库的操作。
root@kali:~# cat /opt/studb+mysql.sql //命令行下可使用cat查看文件
.. ..
CREATE DATABASE /!32312 IF NOT EXISTS/ studb /!40100 DEFAULT CHARACTER SET utf8mb4 /;

USE studb;

.. ..
CREATE DATABASE /!32312 IF NOT EXISTS/ mysql /!40100 DEFAULT CHARACTER SET utf8mb4 /;

USE mysql;

.. ..

或者也可以使用mousepad工具在kali图形桌面下查看文件内容 —— 只需执行 mousepad /opt/studb+mysql.sql 就可以查看此文件，如图-2所示。
WEBSEC DAY02 - 图3
图-2

7 案例7：恢复数据库资料

7.1 问题

本例要求使用mysql工具恢复数据库资料，熟悉恢复单库、多库的不同方法，完成下列任务：
1）确保已经为 studb 库做好备份文件 /opt/studb.sql
2）删除名为 studb 的库，检查结果
3）重建名为 studb 的空库
4）将备份文件 /opt/studb.sql 导入名为 studb 的库
5）检查 studb 库中的表格数据

7.2 步骤

实现此案例需要按照如下步骤进行。
步骤一：确保已经为 studb 库做好备份文件 /opt/studb.sql
root@kali:~# ls -lh /opt/studb.sql
-rw-r—r— 1 root root 4.4K 3月 19 00:34 /opt/studb.sql

步骤二：删除名为 studb 的库，检查结果
1）登入数据库服务器
root@kali:~# mysql -uroot -ppwd@123
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 51
Server version: 10.3.20-MariaDB-1 Debian buildd-unstable

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type ‘help;’ or ‘\h’ for help. Type ‘\c’ to clear the current input statement.

MariaDB [(none)]>

2）删除studb库
MariaDB [(none)]> DROP DATABASE studb;
Query OK, 3 rows affected (0.011 sec)

MariaDB [(none)]>

步骤三：重建名为 studb 的空库
MariaDB [(none)]> CREATE DATABASE studb;
Query OK, 1 row affected (0.000 sec)

MariaDB [(none)]>

MariaDB [(none)]> quit
Bye
root@kali:~#

步骤四：将备份文件 /opt/studb.sql 导入名为 studb 的库
若目标库studb已丢失，则必须提前建好空库
root@kali:~# mysql -uroot -ppwd@123 studb < /opt/studb.sql
root@kali:~#

步骤五：检查 studb 库中的表格数据
root@kali:~# mysql -uroot -ppwd@123
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 51
Server version: 10.3.20-MariaDB-1 Debian buildd-unstable

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type ‘help;’ or ‘\h’ for help. Type ‘\c’ to clear the current input statement.

MariaDB [(none)]> SHOW DATABASES;
+——————————+
| Database |
+——————————+
| information_schema |
| mysql |
| performance_schema |
| studb | //观察studb已经重新出现
+——————————+
4 rows in set (0.000 sec)

MariaDB [(none)]>
MariaDB [(none)]> SELECT * FROM studb.hero; //检查表格内容无误
+—————-+—————-+————+——————-+———————————-+———————+
| 学号 | 姓名 | 性别 | 手机号 | 籍贯 | 期望薪资 |
+—————-+—————-+————+——————-+———————————-+———————+
| 202012001 | 郭靖 | 男 | 13145201314 | 东海桃花岛 | 9600 |
| 202012002 | 黄蓉 | 女 | 13145201413 | 东海桃花岛 | 19200 |
| 202012003 | 华筝 | 女 | 13705666777 | 蒙古大营 | 12000 |
| 202012004 | 洪七公 | 男 | 13400040004 | 太湖北丐帮总舵 | 48000 |
| 202012005 | 黄药师 | 男 | 13500050005 | 东海桃花岛 | 57600 |
| 202012006 | 王重阳 | 男 | 13600060006 | 终南山全真教 | 48000 |
| 202012007 | 段王爷 | 男 | 13700070007 | 云南大理桃源山 | 48000 |
+—————-+—————-+————+——————-+———————————-+———————+
7 rows in set (0.001 sec)

MariaDB [(none)]>
MariaDB [(none)]> quit
Bye
root@kali:~#