Test the database in the eye

Objective:

For a tester, the use of the database is also a very basic skill requirement. Because the vast majority of applications are closely related to data, such as WEIXIN,QQ, need to store a large amount of data information: contact information, sent information, friends Circle information and so on. Most of this information is stored in a relational database.

Therefore, the Software Test Engineer's understanding of the database is the basic requirement.

first, the nature of the database (Mysql)

The relational database of the database.

The so-called relational database is a database based on the relational model, which uses mathematical concepts and methods such as set algebra to process data in the database.

Features of the 1.1 RDBMS, the relational database management system (relational DB Management system)

• 1. The data appears in tabular form
• 2. Various record names for each behavior
• 3. Data fields corresponding to record names for each column
• 4. A number of rows and columns form a single sheet
• 5. A number of forms form database

Some terms about the RDBMS

• database: A database is a collection of some associated tables.
• data table: a table is a matrix of data. Tables in a database look like a simple spreadsheet.
• columns: a column (the data element) contains the same data, such as the postal code.
• row: a row (= tuple, or record) is a set of related data, such as a user's subscribed data.
• Redundancy : stores twice times the data, redundancy reduces performance, but improves the security of the data.
• PRIMARY KEY : The primary key is unique. Only one primary key can be included in a data table. You can use the primary key to query the data.
• foreign Key: A foreign key is used to correlate two tables.
• Composite key : Composite key (key combination) Multiple columns are used as an index key, and are typically applied to composite indexes.
• index: use an index to quickly access specific information in a database table. An index is a structure that sorts the values of one or more columns in a database table. A directory similar to a book. (On the index, a lot of ask)
• referential integrity: referential integrity requires that references to non-existent entities are not allowed in the relationship. and entity integrity are the integrity constraints that the relational model must satisfy in order to ensure the consistency of the data.

1.2 Database storage follows paradigm

Database paradigm (Normal forms): is a guideline for standardizing relational database design to reduce the occurrence of errors. A paradigm is a collection of relational patterns that conform to a certain level. Relationships in relational databases must meet certain requirements and meet different levels of requirements for different paradigms.

1. First Paradigm (1NF)

The first paradigm is the basic condition of a relational database.

The first paradigm can be summed up as:

(1) Duplicate rows are not allowed;

(2) no duplicate columns;

(3) Each column (or each attribute) is the smallest data unit that can not be divided, that is, atomic, but not a set, array, record and other non-atomic data items;

For example: Senior Senior (1) class of Xiao Hong

This is not in accordance with the first paradigm, the class can be split into two fields: Grade + Class

2. Second paradigm (2NF)

The second paradigm is that any one field is dependent on the same field in the table, that is, the property depends entirely on the primary key. The second paradigm is to first satisfy the first paradigm, and there is no partial dependency on the primary key in the table's non-primary key columns.

The primary key in the table is (study number, course), and you can see that all non-primary key columns (grades, course credits) are dependent on the primary key. However, there is another dependency in the table: (Course)--(course credits). So the non-primary key column ' course credits ' depends on the partial primary key column ' course ', so the above table is not satisfied with the second paradigm.

In the above 2 tables, the student selected the primary key for the timetable (school number, course), the Course Information table primary key is (course), all non-primary key columns in the table are completely dependent on the primary key. Not only conforms to the second paradigm, but also conforms to the third paradigm.

In the table above, the primary key is: study number, all fields (name, gender, class, head teacher) are dependent on the primary key, there is no partial dependency on the primary key. So is to satisfy the second paradigm.

3. The third paradigm (3NF)

The third paradigm is defined as satisfying the second paradigm, and the columns in the table do not have a transitive dependency on the non-primary key columns, that is, to eliminate the transitive dependency on the primary key, in short, the third normal form (3NF) requires that a database table not contain non-primary key information already contained in other tables.

For the Student information table above, although the second paradigm is satisfied, all fields depend on the primary key (student number), but there is a transitive dependency on the table, (student number)---(Class)--(teacher in charge). That is, (class teacher) This non-primary key column is dependent on another non-primary key column (class). So the third paradigm is not symbolic.

Student Information Form

Class Information table

Thus, the transitive dependency on the primary key disappears. All 2 of the above tables conform to the 3rd paradigm.

1.3 Storage of database data types

1. The essence of database data storage is

The essence of database data storage is the existence of disk inside.

The data is stored on disk as a disk block. The block is distributed on concentric annular tracks of one or more platters. Tracks can be recorded on a single or double side of the platter.

The set of all tracks of the same diameter is called a cylinder.

The size of the disk block can be set to a multiple of the sector size when the disk is initialized.

Each recorded surface has a disk head array. When reading and writing a piece, the head must be positioned in the block position.

The main reason for not reading and writing in parallel is that it is difficult to ensure that all heads are precisely positioned on the corresponding tracks.

A disk controller is an interface between a disk drive and a computer.

2. Data stored Procedure

In large database systems, stored procedures and triggers play an important role. Both a stored procedure and a trigger are collections of SQL statements and Process Control statements. In essence, a trigger is also a stored procedure. The stored procedure generates execution at the time of operation, so it executes quickly when it is run again.

The concept of stored procedures

A stored procedure (Stored Procedure) is a set of SQL statements that are compiled and stored in a database in order to accomplish a specific function. The user executes it by specifying the name of the stored procedure and giving the parameter (if the stored procedure has parameters).

Specific reference: 1795619

second, the database operation 2.1 Database and table structure operations 1. Creating databases and Tables

Create Database dbname and CREATE TABLE Tbname

2, to the table field additions and deletions to change the search

The ALTER table statement is used to add, modify, or delete columns in an existing table.

Test the database in the eye