This article appeared in:Walker AI

Recently, I have learned to write interface automation, and it is inevitable that a lot of test data will be generated during the debugging of the code and the subsequent integration of automation. Over time, these used useless data will be stored in the database for a long time, one will take up space, two pages will be displayed all the time. Since the project itself does not need to delete the historical data, it has to connect to the database and delete the useless data from the database table. Therefore, this paper will simply introduce and operate the database.

background

Today, with the rise of the Internet industry, data is constantly informationized. All walks of life begin to use various programs for data management and transition to paperless storage. A large amount of data will be generated if the program is operated. This data needs to be persisted and maintained later. So the rational use of the database can be efficient, organized storage of data, can enable people to be able to extract from a large amount of information, more quickly what they need.

Compared with traditional paper management, the advantages of database are mainly shown in the following aspects:

(1) Traditional paper can not be quickly retrieved in front of a large amount of data, and the capacity is not enough to compare with the database. Increasing capacity is equivalent to increasing costs. Using the database, the rational design of primary and foreign keys can optimize query efficiency and reduce data redundancy.

(2) In many cases, putting data into a database is also for security reasons. If you put your accounts and passwords on paper, confidentiality will be reduced. Fire and water ruthless, also do not rule out these factors to the damage of paper. It is stored in the database and encrypted, and timely backup of the database, can greatly ensure the security of data.

(3) Database technology can be combined with intelligent analysis to produce new and useful information. Today’s big data analysis is to extract and judge a large amount of real data, so as to produce new conclusions from its analysis. For example, according to users’ personal preferences, users’ browsing and purchasing record information can be extracted, and combined with the recommendation algorithm, products or short videos that meet their tastes can be pushed to the majority of users.

1. Classification of database

In today’s Internet, the most common database models are mainly two kinds, namely relational database and non-relational database:

1.1 Relational database

In simple terms, a relational model refers to a two-dimensional table model, and a relational database is a data organization composed of multiple two-dimensional tables joined by primary and foreign keys.

Concepts commonly used in relational models:

(1) Relationship: can be understood as a two-dimensional table, each relationship has a relationship name, is commonly said table name.

(2) Attribute: can be understood as a column in a two-dimensional table, often referred to as a field in a database.

(3) Domain: The value range of the attribute, that is, the value limit of a column in the database.

(4) Keywords: databases often called primary keys, consisting of one or more columns.

Benefits of relational databases:

  • Easy to understand: The two-dimensional table structure is a concept that is very close to the logical world, and relational models are easier to understand than other models like networks, hierarchies, and so on.

  • Ease of use: The general purpose SQL language makes it very convenient to operate relational databases.

  • Easy to maintain: rich integrity, greatly reduce the probability of data redundancy and data inconsistency.

Of course, relational databases have their own bottlenecks:

Hard disk I/O is a big bottleneck for traditional relational databases at high concurrent reads and writes, which can be up to tens of thousands of reads and writes per second.

When a huge amount of data is generated, for the relational database, the query efficiency will continue to reduce, even if the SQL query optimization, there will be an upper limit, always unable to break through.

1.2 Non-relational database classification

A non-relational database is not strictly a database, but a collection of structured ways to store data.

Key-value Databases for high-performance concurrent reads and writes:

Key-value databases are characterized by high concurrent read and write performance. For example, Redis organizes, indexes, and stores data in the form of key and value pairs, and maps key and value values. You can store it in memory as a cache. This eliminates the need to re-render the page for each individual request, resulting in a fast response.

Document databases for Massive data access:

The characteristic of this kind of database is that it can quickly query data in massive data. The typical representative is MongoDB, which is a product between relational database and non-relational database. Among non-relational databases, it has the most abundant functions and is the most similar to relational database. It supports a JSON-like bSON format, so it can store more complex data types.

2. Common database operations

2.1 Relational database

This section uses MySQL as an example to describe common operation statements. Most servers are now deployed on Linux, so this is done using the directive:

(1) Enable: Enter service mysqld start on the terminal to enable mysql

(2) Check the status: Enter service mysqld status to check the status of mysql

(3) Stop: Enter service mysqld stop on the terminal to stop mysql

(4) Log in to mysql: mysql -u user name -p user password. For example, (user name: zhangsan, password: 123) : mysql -u zhangsan -p 123

(5) Create database: Create database Name

(6) Drop database database name

(7) Use database: Use database name

Create table:

  • Create table table name (
  • -> id int not null auto_increment, // add a null auto_increment
  • -> title varchar(100) not null, // add a string field named title
  • -> primary key (id)
  • -> )ENGINE=InnoDB default charset=utf8; // Use InnoDB database engine to encode database tables in UTF8 (Chinese)

(9) Drop database table: drop table name

Insert into table name (table 1, table 2… Values (1, 2,… And the value of n);

Delete from table where title=” test”

Select * from ‘select * from’ where ‘select * from’;

  • Select * from table_name where id = 1; select * from table_name where id = 1;
  • Select * from table where title=” test “;
  • Select * from ‘A’ where ‘A’ = ‘B’; select * from ‘A’ where ‘A’ = ‘B’;
  • Select * from table_name where title like ‘% test %’;
  • Select * from asc order by title desc/asc; // The default value is asc, but the default value is desc in descending order

2.2 Non-relational databases

This article uses MongoDB as an example to briefly introduce common operation statements. Most servers are now deployed on Linux, so this is done using the directive:

(1) Enable: Go to the terminal, go to the bin folder in the mongodb directory, and run./mongo

(2) to check the status: netstat into terminal input lanp | grep “mongo port”, can view the mongo’s enabled

(3) stop:./ god -shutdown;

(4) Login: mongo –host IP address -u Username –authentication Database username -p password // For example: username zhangsan, password 123: Mongo –host 127.0.0.1 -u zhangsan –authentication Database admin -p 123

(5) Create database: use database name, when use, if the database does not save, the system will automatically create, if there is, will switch

(6) delete database: db.dropdatabase (), must first use the database to be deleted, and then call this statement

CreateCollection: db.createcollection (” set name “)

(8) drop the set: db.set name. Drop ()

Var_data1 = {age:10, age:10, age:10}; Db.set name. insert(var_data1)

Remove ({“name”:” zhang SAN “})

(11) Query documents:

  • Query all documents: db.collection name.find ().pretty();
  • Find ({“age”:10})
  • Find ().sort({“price” : 1}) // 1: sort in ascending order -1: sort in descending order
  • Find ({name: / zhang SAN /});

3. Database remote operation software

Navicat Premium 15 is an example of how to connect to a database using MySQL and mongoDB.

3.1 MySQL Database

(1) Select “File” – “New Link” – “MySQL”

(2) Enter the database information to be connected, click test connection, and use it if the prompt is successful

(3) Click new query and enter SQL statement to execute

3.2 MongoDB Database

(1) Select “Connect” – “MongoDB”

(2) If there is authentication, select Password.

(3) Enter the database information to be connected, click test connection, and use it if the prompt is successful

(4) Click new query and enter SQL statement for execution

4. To summarize

While learning automation, I also have a further understanding of the database. By looking at the design of the database tables, you can gain a deeper understanding of the project’s architecture and business. This can help us find more potential defects in future testing work. This article only briefly describes some commonly used database statements, which are obviously not enough when facing more complex database operations. This also gives me another direction to learn, so that I can constantly improve myself and become more comprehensive.

PS: more dry technology, pay attention to the public, | xingzhe_ai 】, and walker to discuss together!