Shulou(Shulou.com)06/01 Report--

Case 1:INSERT insert table records case 2:UPDATE and DELETE case 3:SELECT query table record case 4:WHERE simple match case 5:WHERE advanced match case 6: organize query results case 7: backup database data case 8: restore database

1 case 1:INSERT inserts table record

1.1 question

This example requires a variety of different insertion methods for table records based on the stuinfo table of the studb library in the previous chapter to complete the following tasks:

1) enter the studb library and insert the data shown in figure-1 into the stuinfo table in batch.

2) confirm the data content of table stuinfo

1.2 steps

To implement this case, you need to follow these steps.

Step 1: enter the studb library and insert the following data into the stuinfo table in batch

The record is as follows:

NTD2020110007 Zhou Botong male 17012341234 Quanzhen Religion headquarters in Kunyu Mountain

NTD2020110008 Wang Chongyang male 17012340001 Kunyu Mountain Quanzhen Religion headquarters

NTD2020110009 Duan Wang Ye male 17566666666 Taoyuan Mountain, Dali, Yunnan

INSERT insert record operation:

MariaDB [studb] > INSERT INTO stuinfo VALUES-> ('NTD2020110007',' Zhou Botong', 'male', '17012341234', 'Kunyu Mountain Quanzhen Religion headquarters'),-> ('NTD2020110008',' Wang Chongyang', 'male', '17012340001',' Kunyu Mountain Quanzhen Religion headquarters'),-> ('NTD2020110009',' Duan Wang Ye, 'male', '1756666666666', 'Taoyuan Mountain, Yunnan Dali') Query OK, 3 rows affected (0.01sec) Records: 3 Duplicates: 0 Warnings: 0MariaDB [studb] >

Step 2: confirm the data content of table stuinfo

Check the contents of the table to confirm the 3 new records.

MariaDB [studb] > SELECT * FROM stuinfo +-+ | Student number | name | gender | contact number | mailing address | +-- -+-+ | NTD2020110001 | Guo Jing | male | 13145201314 | Peach Blossom Island in East China Sea | | NTD2020110002 | Huangrong | female | 13145201413 | East China Sea Taohuadao | | NTD2020110003 | Huazheng | female | 13705666777 | Mongolian Daying | | NTD2020110004 | Hong Qigong | male | 13888888888 | General helm of Bei Bei Bang in Taihu Lake | | NTD2020110005 | Ouyang Feng | male | 18777777777 | Western White Camel Villa | | NTD2020110006 | Huang Yaoshi | male | 1899999999 | East China Sea Peach Blossom Island | NTD2020110007 | | | Zhou Botong | male | 17012341234 | Kunyu Mountain Quanzhen Education headquarters | | NTD2020110008 | Wang Chongyang | male | 17012340001 | Kunyu Mountain Quanzhen Education headquarters | | NTD2020110009 | Duan Wangye | male | 17566666666 | Taoyuan Mountain, Dali, Yunnan | +-- -+-- + 9 rows in set (0.00 sec) MariaDB [studb] >

2 cases 2:UPDATE and DELETE

2.1 question

This example requires you to learn how to operate UPDATE and DELETE statements to complete the following tasks:

1) Update the data records in the stuinfo table

Change the contact number of the person whose name = 'Huang Yaoshi' to '13566778899' and change the name of the person whose name = 'Hong Qigong' to 'Hong Qi'

2) Delete the data record with the name 'Hong Qigong' in the stuinfo table

3) confirm the data content of table stuinfo

2.2 steps

To implement this case, you need to follow these steps.

Step 1: update the data records in the stuinfo table

1) change the contact number of the person whose name is' Huang Yaoshi'to '13566778899'

MariaDB [studb] > UPDATE stuinfo SET contact number = '13566778899' WHERE name = 'Huang Yaoshi'; Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0MariaDB [studb] >

2) change the name of the person whose name = 'Hong Qi Gong' to 'Hong Qi'.

MariaDB [studb] > UPDATE stuinfo SET name = 'Hong Qi' WHERE name = 'Hong Qi Gong'; Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0MariaDB [studb] >

Step 2: delete the data record with the name 'Hong Qigong' in the stuinfo table

At this time, there is no record named "Hong Qigong" in the form (only "Hong Qi"), and all records will not be deleted.

MariaDB [studb] > DELETE FROM stuinfo WHERE name = 'Hong Qigong'; Query OK, 0 rows affected (0.00 sec) MariaDB [studb] >

Step 3: confirm the data content of table stuinfo

Check the contents of the modified table:

MariaDB [studb] > SELECT * FROM stuinfo +-+ | Student number | name | gender | contact number | mailing address | +-- -+-+ | NTD2020110001 | Guo Jing | male | 13145201314 | Peach Blossom Island in East China Sea | | NTD2020110002 | Huangrong | female | 13145201413 | East China Sea Taohuadao | | NTD2020110003 | Huazheng | female | 13705666777 | Mongolian Daying | | NTD2020110004 | Hongqi | male | 13888888888 | General helm of Bei Bei Bang in Taihu Lake | | NTD2020110005 | Ouyang Feng | male | 18777777777 | Western White Camel Villa | | NTD2020110006 | Huang Yaoshi | male | 13566778899 | Taohua Island in the East China Sea | NTD2020110007 | | | Zhou Botong | male | 17012341234 | Kunyu Mountain Quanzhen Education headquarters | | NTD2020110008 | Wang Chongyang | male | 17012340001 | Kunyu Mountain Quanzhen Education headquarters | | NTD2020110009 | Duan Wangye | male | 17566666666 | Taoyuan Mountain, Dali, Yunnan | +-- -+-- + 9 rows in set (0.00 sec) MariaDB [studb] >

3 case 3:SELECT query table record

3.1 question

This example requires you to learn how to operate the SELECT statement, query the stuinfo table, and complete the following tasks:

1) list the name and contact number of each record in the table

2) list the detailed data records of gender = 'female' in the table

3) list the names and contact numbers of the records with gender = 'female' in the table

4) list the detailed data records of the correspondence address = 'Peach Blossom Island in the East China Sea' in the stuinfo table

3.2 steps

To implement this case, you need to follow these steps.

Step 1: list the name and contact number of each record in the table

MariaDB [studb] > SELECT name, contact number FROM stuinfo +-+ | name | contact number | +-+-+ | Guo Jing | 13145201314 | | Huang Rong | 13145201413 | | Huazheng | 13705666777 | | Hong Qi | 13888888888 | Ouyang Feng | 18777777777 | | Huang Yaoshi | 13566778899 | | Zhou Botong | | 17012341234 | | Wang Chongyang | 17012340001 | | Duan Wangye | 17566666666 | +-+ 9 rows in set (0.00 sec) MariaDB [studb] > |

Step 2: list the detailed data records of gender = 'female' in the table

MariaDB [studb] > SELECT * FROM stuinfo WHERE gender = 'female' +-+ | Student number | name | gender | contact phone number | mailing address | + -+ | NTD2020110002 | Huang Rong | female | 13145201413 | Peach Blossom Island in the East China Sea | | NTD2020110003 | Huazheng | female | 13705666777 | Mongolia Daying | +- -- + 2 rows in set (0.00 sec) MariaDB [studb] >

Step 3: list the names and contact numbers of the records with gender = 'female' in the table

MariaDB [studb] > SELECT name, contact number FROM stuinfo WHERE gender = 'female' +-+ | name | contact number | +-+-+ | Huang Rong | 13145201413 | | Huazheng | 13705666777 | +-+ 2 rows in set (0.00 sec) MariaDB [studb] >

Step 4: list the detailed data records of the correspondence address = 'Peach Blossom Island in the East China Sea' in the stuinfo table.

MariaDB [studb] > SELECT * FROM stuinfo WHERE address = 'Peach Blossom Island in the East China Sea' +-+ | Student number | name | gender | contact number | mailing address | +- +-+ | NTD2020110001 | Guo Jing | male | 13145201314 | Taohuadao in the East China Sea | | NTD2020110002 | Huangrong | female | 13145201413 | Taohuadao in the East China Sea | | NTD2020110006 | Huang Yaoshi | male | 13566778899 | Taohuadao in the East China Sea | | | +-+ 3 rows in set (0.01sec) MariaDB [studb] > |

4 case 4:WHERE simple matching

4.1 question

This example requires you to learn the simple application of WHERE conditional matching, execute queries against the data in the stuinfo table, and complete the following tasks:

1) list the correspondence address in the table! = the data record of 'Peach Blossom Island in the East China Sea'

2) list the data records of mailing address = 'Peach Blossom Island in the East China Sea' and gender = 'female' in the table

3) list the data records of correspondence address = 'Mongolian Camp' or gender = 'female' in the table

4.2 steps

To implement this case, you need to follow these steps.

Step 1: list the correspondence address in the table! = the data record of 'Peach Blossom Island in the East China Sea'

MariaDB [studb] > SELECT * FROM stuinfo WHERE address! = 'Peach Blossom Island in the East China Sea' +-+ | Student number | name | gender | contact number | mailing address | +-- -+-+ | NTD2020110003 | Huazheng | female | 13705666777 | Mongolian Daying | | NTD2020110004 | Hong Qi | male | 13888888888 | Taihu Lake Bei Bei Gang General rudder | | NTD2020110005 | Ouyang Feng | male | 18777777777 | Western White Camel Villa | | NTD2020110007 | Zhou Botong | male | 17012341234 | Kunyu Mountain Quanzhen Education headquarters | | NTD2020110008 | Wang Zhongyang | male | 17012340001 | Kunyu Mountain Quanzhen Education headquarters | | NTD2020110009 | Duan Wangye | male | 17566666666 | Taoyuan Mountain, Dali, Yunnan | +- -+-- + 6 rows in set (0.00 sec) MariaDB [studb] >

Step 2: list the data records of the mailing address = 'Peach Blossom Island in the East China Sea' and gender = 'female' in the table

MariaDB [studb] > SELECT * FROM stuinfo WHERE correspondence address = 'Peach Blossom Island in the East China Sea' AND gender = 'female' +-+ | Student number | name | gender | contact phone number | mailing address | + -+ | NTD2020110002 | Huang Rong | female | 13145201413 | Peach Blossom Island in the East China Sea | +- -+ 1 row in set (0.00 sec) MariaDB [studb] >

Step 3: list the data records of correspondence address = 'Mongolian Camp' or gender = 'female' in the table

MariaDB [studb] > SELECT * FROM stuinfo WHERE correspondence address = 'Mongolian Camp' OR gender = 'female' +-+ | Student number | name | gender | contact phone number | mailing address | + -+ | NTD2020110002 | Huang Rong | female | 13145201413 | Peach Blossom Island in the East China Sea | | NTD2020110003 | Huazheng | female | 13705666777 | Mongolia Daying | +- -- + 2 rows in set (0.00 sec) MariaDB [studb] >

Note: WHERE conditional clauses apply to SELECT, UPDATE, DELETE operations

5 case 5:WHERE Advanced matching

5.1 question

This example requires you to learn the advanced application of WHERE conditional matching, perform queries against the data in the stuinfo table, and complete the following tasks:

1) list the data records named Guo Jing, Huang Rong, Ouyang Feng and Huang Yaoshi in the table.

2) list the data records of the mailing address in the table including the string "Quanzhen Jiao"

3) list the data records with only two words of name in the table

5.2 steps

To implement this case, you need to follow these steps.

Step 1: list the data records named Guo Jing, Huang Rong, Ouyang Feng and Huang Yaoshi in the table.

MariaDB [studb] > SELECT * FROM stuinfo WHERE name IN ('Guo Jing', 'Huang Rong', 'Ouyang Feng', 'Huang Yaoshi') +-+ | Student number | name | gender | contact number | mailing address | +- -+ | NTD2020110001 | Guo Jing | male | 13145201314 | Peach Blossom Island in the East China Sea | | NTD2020110002 | Huangrong | female | 13145201413 | Taohuadao in the East China Sea | NTD2020110005 | Ouyang Feng | male | | 18777777777 | Western White Camel Villa | | NTD2020110006 | Huang Yaoshi | male | 13566778899 | Peach Blossom Island in the East China Sea | +-+ 4 rows in set (0.00 sec) MariaDB [studb] >

Step 2: list the data records of the mailing address in the table, including the string "Quanzhen Jiao"

MariaDB [studb] > SELECT * FROM stuinfo WHERE address LIKE'% Quanzhen%' +-+ | Student number | name | gender | contact number | mailing address | +-- -+-+ | NTD2020110007 | Zhou Botong | male | 17012341234 | Quanzhen Education headquarters in Kunyu Mountain | | NTD2020110008 | Wang Chongyang | male | 17012340001 | Kunqu Mountain Quanzhen headquarters | +-+ 2 rows in set (0.00 sec) MariaDB [studb] >

Step 3: list the data records with only two words in the table

MariaDB [studb] > SELECT * FROM stuinfo WHERE name LIKE'_ _'

+-- +

| | Student number | name | gender | contact number | mailing address |

+-- +

| | NTD2020110001 | Guo Jing | male | 13145201314 | Peach Blossom Island in the East China Sea |

| | NTD2020110002 | Huang Rong | female | 13145201413 | Peach Blossom Island in East China Sea |

| | NTD2020110003 | Huazheng | female | 13705666777 | Mongolian Camp |

| | NTD2020110004 | Hong Qi | male | 13888888888 | General helm of North Beggar gang in Taihu Lake |

+-- +

4 rows in set (0.00 sec)

MariaDB [studb] >

6 case 6: organize the query results

6.1 question

This example requires learning to sort and limit the number of query results, execute queries against the data in the stuinfo table, and complete the following tasks:

1) the number of chivalrous men with gender = 'male' in the query table

2) list the data records of the correspondence address = 'Peach Blossom Island in the East China Sea' in the table, arranged in descending order by student number.

3) list the 4-5 records of gender = 'male' in the table (LIMIT 3, 2)

4) list the first three data records in the table

6.2 steps

To implement this case, you need to follow these steps.

Step 1: query the number of chivalrous men with gender = 'male' in the table

MariaDB [studb] > SELECT count (*) FROM stuinfo WHERE gender = 'male'; +-+ | count (*) | +-+ | 7 | +-+ 1 row in set (0.00 sec) MariaDB [studb] >

Step 2: list the data records of the correspondence address = 'Peach Blossom Island in the East China Sea' in the table, and arrange them in descending order by student number.

MariaDB [studb] > SELECT * FROM stuinfo WHERE correspondence address = 'Peach Blossom Island' ORDER BY student number DESC +-+ | Student number | name | gender | contact number | mailing address | +- +-+ | NTD2020110006 | Huang Yaoshi | male | 13566778899 | Peach Blossom Island in the East China Sea | | NTD2020110002 | Huangrong | female | 13145201413 | Taohuadao in the East China Sea | NTD2020110001 | Guo Jing | male | 13145201314 | Taohuadao in the East China Sea | | +-+ 3 rows in set (0.00 sec) MariaDB [studb] > |

Step 3: list the 4-5 records of gender = 'male' in the table (LIMIT 3, 2)

MariaDB [studb] > SELECT * FROM stuinfo WHERE gender = 'male' LIMIT 3 +-+ | Student number | name | gender | contact number | mailing address | +-- -+-+ | NTD2020110006 | Huang Yaoshi | male | 13566778899 | Taohuadao in the East China Sea | | NTD2020110007 | Zhou Botong | male | 17012341234 | Kunqu Mountain Quanzhen headquarters | +-+ 2 rows in set (0.00 sec) MariaDB [studb] >

Step 4: list the first three data records in the table

MariaDB [studb] > SELECT * FROM stuinfo LIMIT 3 +-+ | Student number | name | gender | contact phone number | mailing address | + -+ | NTD2020110001 | Guo Jing | male | 13145201314 | Peach Blossom Island in the East China Sea | | NTD2020110002 | Huangrong | female | 13145201413 | Taohuadao in the East China Sea | | NTD2020110003 | Zheng | female | 13705666777 | Daying in Mongolia | +- -+ 3 rows in set (0.00 sec) MariaDB [studb] >

7 case 7: backup database data

7.1 question

This example requires you to use mysqldump tool to back up the database, be familiar with the different backup uses of single database and multi-database, and complete the following tasks:

1) back up the studb library and save as / root/studb.sql file

2) back up the studb library and mysql library and save it as / root/studb.sql file

3) back up all libraries and save as / root/alldb.sql file

7.2 steps

To implement this case, you need to follow these steps.

Step 1: back up the studb library and save it as / root/studb.sql file

[root@svr7 ~] # mysqldump-uroot-ppwd@123 studb > / root/studb.sql

Step 2: back up the studb library and mysql library and save it as / root/studb.sql file

[root@svr7] # mysqldump-uroot-ppwd@123-- databases studb mysql > / root/studb+mysql.sql

Step 3: back up all libraries and save as / root/alldb.sql file

[root@svr7] # mysqldump-uroot-ppwd@123-- all-databases > / root/alldb.sql

8 case 8: restore the database

8.1 question

This example requires the use of mysql tools to recover database data, to be familiar with different methods of restoring single-database and multi-database, and to complete the following tasks:

1) make sure that the backup file / root/studb.sql has been done for the studb library

2) delete the library named studb and check the results

3) rebuild the empty library named studb

4) Import the backup file / root/studb.sql into the library named studb

5) check the table data in the studb library

8.2 steps

To implement this case, you need to follow these steps.

Step 1: make sure you have backed up the files / root/studb.sql for the studb library

[root@svr7] # ls-lh / root/studb.sql-rw-r--r--. 1 root root 11K October 24 20:20 / root/studb.sql

Step 2: delete the library named studb and check the results

1) Log in to the database server

[root@svr7] # mysql-uroot-ppwd@123Welcome to the MariaDB monitor. Commands end with; or\ g.Your MariaDB connection id is 31Server version: 5.5.56-MariaDB MariaDB ServerCopyright (c) 2000, 2017, Oracle, MariaDB Corporation Ab and others.Type 'help;' or'\ h' for help. Type'\ c'to clear the current input statement.MariaDB [(none)] >

2) delete the studb library

MariaDB [(none)] > DROP DATABASE studb;Query OK, 9 rows affected (0.00 sec) MariaDB [(none)] >

Step 3: rebuild the empty library named studb

MariaDB [(none)] > CREATE DATABASE studb;Query OK, 1 row affected (0.00 sec) MariaDB [(none)] > MariaDB [(none)] > quitBye [root@svr7 ~] #

Step 4: import the backup file / root/studb.sql into the library named studb

If the target library studb has been lost, the empty library must be built in advance.

[root@svr7] # mysql-uroot-ppwd@123 studb

< /root/studb.sql [root@svr7 ~]# 步骤五：检查 studb 库中的表格数据 [root@svr7 ~]# mysql -uroot -ppwd@123Welcome to the MariaDB monitor. Commands end with ; or \g.Your MariaDB connection id is 33Server version: 5.5.56-MariaDB MariaDB ServerCopyright (c) 2000, 2017, Oracle, MariaDB Corporation Ab and others.Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.MariaDB [(none)]>

SELECT * FROM stuinfo;ERROR 1046 (3D000): No database selectedMariaDB [(none)] > SELECT * FROM studb.stuinfo +-+ | Student number | name | gender | contact number | mailing address | +-- -+-+ | NTD2020110001 | Guo Jing | male | 13145201314 | Peach Blossom Island in East China Sea | | NTD2020110002 | Huangrong | female | 13145201413 | East China Sea Taohuadao | | NTD2020110003 | Huazheng | female | 13705666777 | Mongolian Daying | | NTD2020110004 | Hongqi | male | 13888888888 | General helm of Bei Bei Bang in Taihu Lake | | NTD2020110005 | Ouyang Feng | male | 18777777777 | Western White Camel Villa | | NTD2020110006 | Huang Yaoshi | male | 13566778899 | Taohua Island in the East China Sea | NTD2020110007 | | | Zhou Botong | male | 17012341234 | Kunyu Mountain Quanzhen Education headquarters | | NTD2020110008 | Wang Chongyang | male | 17012340001 | Kunyu Mountain Quanzhen Education headquarters | | NTD2020110009 | Duan Wangye | male | 17566666666 | Taoyuan Mountain, Dali, Yunnan | +-- -+-- + 9 rows in set (0.00 sec) MariaDB [(none)] > quitBye [root@svr7 ~] #

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.