Software testing MySQL

1 Introduction to MySQL database

MySQL is a relational database. Due to its small size, fast speed, low total cost of ownership and open source, many small and medium-sized websites choose MySQL as the website database in order to reduce the total cost of ownership.
Other advantages: it can be combined with other development languages, portability (cross platform), can be used in different operating systems, lightweight database

2. MySQL database operation

To view an existing database: show databases;
New database: create database Library name;
Delete database: drop database Library name;
Switch database: use Library name;

3. Methods and principles of database table creation

3.1 data types supported by MySQL

  • numerical value
  • Date / time
    Each time type has a valid range and a "zero" value. The "zero" value is used when specifying illegal values that MySQL cannot represent.
  • String (character)

    Char is fixed length and varchar is variable length. When char type storage is selected, when the data length is less than the length of char, the remaining length will be filled with spaces.

3.2 create and drop of tables

  • Table creation
create table Table name(
             Property name 1 data type[constraint condition],
             Property name 2 data type[constraint condition],
             Property name 3 data type[constraint condition],
             ......
         );

  • Table deletion
drop table Table name 1,Table name 2,......

3.3 table modification (alter)

Add column (attribute)

alter table Table name add Property name data type;

Delete column (attribute)

alter table Table name drop Attribute name;

Modify the data type of the attribute

alter table Table name modify Property name new data type;

Modify field name

alter table Table name change Old field name new field name data type;

Display table structure

desc Table name;

4 constraints of relationship

4.1 physical integrity

  • Entity integrity is the uniqueness of records in relationships
  • Defines the unique identifier of all rows in the table
  • The primary property (field) in the table cannot be Null and cannot have the same value
  • It is generally represented by primary key, unique index and unique keyword

Primary key constraint

primary key is one or more fields in a table. Its value is used to uniquely identify a record in the table.

  • Unique, non duplicate, and cannot be empty;
  • There can only be one primary key constraint in a table.
  • Set primary key constraint:
    1) Create a primary key constraint while creating a table
Format I:
create table Table name(
         Column name 1 data type primary key,
         Column name 2 data type
)；

Format 2: name of primary key constraint: PK_Listing
create table Table name(
         Column name 1 data type,
         Column name 2 data type,
         constraint The name of the primary key constraint primary key(Column name 1)
)；

Format 3:
create table Table name(
         Column name 1 data type,
         Column name 2 data type,
         primary key(Column name 1)
)；

2) Set primary key constraints on existing tables

Format I:
alter table Table name modify Column name data type primary key;

Format 2:
alter table Table name add primary key(Listing);

Format 3:
alter table Table name add constraint The name of the primary key constraint primary key(Listing);

3) Delete the set primary key

alter table Table name drop primary key;
  • Set joint primary key constraint: only format 2 and 3 can be used to set joint primary keys, which are listed in sequence in the column name.
    Joint primary key: refers to treating two columns as a whole. The whole cannot be empty, unique or duplicate.

Unique constraint

unique

  • The unique constraint is that the column or column combination of the specified table cannot be repeated to ensure the uniqueness of the data;
  • The unique constraint does not allow duplicate values, but can be multiple null s;
  • The same table can have multiple unique constraints and multiple column combination constraints;
  • If you do not give a unique constraint name, it is the same as the column name by default;
  • MySQL will create a unique index on the columns with unique constraints by default.
  • Set unique constraint
    1) Create unique constraints while creating tables
Format I:      Unique constraint name format: UN_Listing
create table Table name(
     Column name 1 data type,
     Column name 2 data type,
     constraint Unique constraint name unique(Column name 1)
);

Format 2:
create table Table name(
     Column name 1 data type unique,
     Column name 2 data type unique,
     Column name 3 data type
);

2) Add unique constraint to existing table

Format:
alter table Table name add unique(Column name 1[,Column name 2]);
Brackets are optional

3) Delete unique constraint

Format:
alter table Table name drop index Unique constraint name;

4.2 domain integrity

  • Domain integrity is a constraint on field properties in a data table
  • It is determined by the attributes of the fields defined when determining the table structure
  • Restrict whether the data type, default value, rule and constraint can be blank
  • Domain integrity ensures that invalid values are not entered

Default constraint

default
When the default constraint modifies a column, the modified column defaults to a value even if it does not write data

1) Create default constraints while creating tables

Format:
create table Table name(
       Column name 1 data type default  'Default value of string type or date type',
       Column name 2 data type default numerical value,
       Column name 3 data type
);

2) Add default constraint to existing table

Format:
alter table Table name modify Column name data type default Default value;

3) Delete default constraint

Format:
alter table Table name modify Column name data type;

Non NULL constraint

not null - the current column must have a value

1) Create a non empty constraint when creating a table

Format:
create table Table name(
      Column name 1 data type not null,
      Column name 2 data type not null,
      Column name 3 data type
);

2) A non empty constraint already exists for table settings

Format:
alter table Table name modify Column name data type not null;

3) Delete non empty constraints

Format:
alter table Table name modify Column name data type;

4.3 referential integrity

  • Refers to the data reference between tables
  • Implementation using foreign key constraints

Foreign key constraint

foreign key

  • A foreign key is a reference relationship between two fields in two tables;
  • The foreign key value of the table must be found in the main table;
  • When the records of the master table are referenced from the slave table, the records of the master table cannot be deleted directly unless the corresponding data in the slave table is deleted first;
  • The columns of the referenced primary table must be primary keys or unique constraints.

1) Set foreign key constraints when creating tables

 create table Main table(
          Column name 1 data type primary key,
          Column name 2 data type
 );
 create table From table(
          Column name 1 data type,
          Column name 2 data type,
          constraint The name of the foreign key constraint foreign key(Column name 1 from table) references Main table name(Column name 1)
 )

Name of foreign key constraint: FK_Listing

Note: the column names of the slave table and the primary table can be different, but the data types and contents of the two columns must be consistent.
2) Add foreign key constraint to existing table

Format:
alter table From table name add constraint The name of the foreign key constraint foreign key(Column name from table) references Main table name(Referenced column name);

3) Delete foreign key constraint

Format:
alter table From table name drop foreign key The name of the foreign key constraint;

5 DML language in SQL

5.1 concept of SQL language

  • SQL is a database query language and design language
  • So that the database can add, delete, modify and query the table through the command line rather than the graphical interface
  • SQL is an independent language. SQL statements can be embedded in the development language to operate the database

5.2 DML language

Insert

Format I:
insert into Table name(Column name 1,Column name 2,Column name 3,......)  values(Value 1,Value 2,Value 3,......):

Format 2:
insert into Table name values(Value 1,Value 2,Value 3,......);

Format 3: insert multiple records
insert into Table name values(Value 1,Value 2,Value 3,......),(Value 1,Value 2,Value 3,......),......);

Delete

Format 1: delete all data in the table(wipe data )
delete from Table name;

Format 2: delete data according to conditions
delete from Table name where condition;

Delete by condition:

  • When multiple conditions need to be met at the same time, the conditions are connected with and;
  • When multiple conditions only need to meet one, the conditions are connected by or.

Update

Format 1: change all values of the column
update Table name set Listing=value;

Format 2: change multiple column values
update Table name set Column name 1=Value 1,Column name 2=Value 2,......;

Format 3: conditional change
update Table name set Listing=value where condition;

Select

Basic format:
select Listing from Table name;    -- Query column

select * from Table name;    -- View all information in the table

select Column name 1,Column name 2,Column name 3,......from Table name;    -- Query multiple columns

To duplicate query

select distinct Listing from Table name;

Alias query

Format I:
select Column name 1 'Alias 1',Column name 2 'Alias 2',Column name 3 'Alias 3',...... from Table name;

Format 2:
select Column name 1 as 'Alias 1',Column name 2 as 'Alias 2',Column name 3 as 'Alias 3',...... from Table name;

Calculate column during alias query: refers to the calculation of query results

Condition query

select Listing from Table name where query criteria;

Comparison operators: =, >, <, > =, < =, < > (not equal to)= (not equal to)
Logical operators: and, or

select Listing from Table name where Condition 1 and Condition 2 and Condition 3 and ......;
select Listing from Table name where Condition 1 or Condition 2 or Condition 3 or ......;

Scope search criteria: between, not between

select Listing from Table name where between Start value and End value;
select Listing from Table name where not between Start value and End value;

List search criteria: in, not in
Match the in keyword with any value in the list. As long as one is satisfied, there will be query results

select Column name 1,Column name 2,...... from list where Listing in(Value 1,Value 2,Value 3,......);
select Column name 1,Column name 2,...... from list where Listing not in(Value 1,Value 2,Value 3,......);

Character matching: like (fuzzy query)
The like keyword searches for a string, date, or time value that matches a specified pattern. The pattern contains the string to search, which may contain any combination of 2 wildcards.

wildcardmeaning
%Represents 0 or more characters
_Represents a character
select Listing from Table name where Listing like 'Character mode';


Null query: null

Judged to be empty:
select Listing from Table name where Listing is null;

Judgment not null:
select Listing from Table name where Listing is not null;

Aggregate function

Calculate columns

sum([distinct] <Listing>): Calculate the sum of column values
avg([distinct] <Listing>): Calculate the average value, with 4 decimal places reserved by default
max([distinct] <Listing>): Find the maximum value
min([distinct] <Listing>): Find the minimum value
count(*): Number of records in the statistical table
count([distinct] <Listing>): Count the number of values in a column
 Above division count(*)No null value will be counted outside

Format:
select Aggregate function from Table name;

Row limit

limit, which limits the range of query rows

select Listing from Table name limit [start,] nums;

start: The number of starting lines. When not writing, it defaults to the beginning of line 0
nums: Total number of query rows

Grouping query

group by to group the query results

select Listing,Aggregate function from Table name group by Listing;

having: used together with group by, it is equivalent to a where clause for grouping, specifying the search criteria for grouping.
The having clause can contain aggregate functions, but where cannot.

select Listing,Aggregate function from Table name group by Listing having Aggregate function;

sort

order by, used to sort the result set

  • Sort default ascending (asc)
  • desc keyword can be used in descending order
select Listing from Table name order by Listing;       --Ascending order
select Listing from Table name order by Listing desc;  --Descending order

5.3 MySQL functions

String function

  • Length (string): returns the byte length of the string
    In MySQL, a Chinese character occupies 3 bytes and a letter occupies 1 byte.
  • char_ Length: returns the character length of a string
  • Mid (original string, start position of interception, length of interception): obtain characters of a certain length from a certain position

Mathematical function

  • Round (numeric value, reserved digits): rounding is realized, and the integer part is reserved by default
  • Least (value 1, value 2,...): find the minimum number
  • Greatest (value 1, value 2,...): find the largest number

Date time function

  • now(): get the current date and time
  • current_date(): get the current date
  • current_time(): get the current time
  • to_ Days: converts the date to the total number of days
  • Dayofyear: find the number of days that have passed in the year
  • Week: returns the current week

Control flow function

  • If (Boolean expression, parameter 1, parameter 2)
  • if(null, parameter 1, parameter 2)
    When Boolean expression is true, output parameter 1 is false and output parameter 2 is false; When null, output parameter 2.
  • Ifnull (parameter 1, parameter 2)
  • ifnull(null, parameter 2)
    When parameter 1 is null, parameter 2 is output; otherwise, parameter 1 is output

5.4 meter connection

Table join is required for multi table query.

Internal connection (internal connection)

The column names of the associated columns between tables can be different, but the data types and contents need to be consistent.
The inner join query only displays the data information that can be matched in the table and its associated columns

Format 1:
select Table name 1.Column name 1,Table name 1.Column name 2,Table name 2.Column name 1,Table name 2.Listing 2
from Table name 1,Table name 2
where Table name 1.Column name 1=Table name 2.Column name 1;

Format 2:
select Table name 1.Column name 1,Table name 1.Column name 2,Table name 2.Column name 1,Table name 2.Listing 2
from Table name 1 [inner] join Table name 2
on Table name 1.Column name 1=Table name 2.Column name 1;

Note: if the column names in the two tables are not duplicate, the table name can be omitted
  • where table connection
select Table name.Listing
from Table name 1,Table name 2
where Table name 1.column=Table name 2.column [and Condition 1 and Condition 2...]
[group by Listing]
[having Grouping conditions]
[order by Listing asc | desc];
  • inner join on is divided into:
    • Equivalent connection: the connection condition after on is only '='
    • Non equivalent connection: the connection condition after on has other comparison operator symbols besides' = '

give an example:
Format I:

Format 2:

  • Multi table connection
Format 1:
select Table name.Listing
from Table name 1,Table name 2,Table name 3
where Table name 1.column=Table name 2.column and Table name 2.column=Table name 3.column;

Format 2:
select Table name.Listing
from Table name 1 [inner] join Table name 2
on Table name 1.column=Table name 2.column inner join Table 3
on Table name 2.column=Table name 3.column;

External connection (external connection)

  • Return all contents of at least one table

  • classification

    • Left join: no restrictions are placed on the left table in the join condition, and all rows in the left table are returned
    • Right join: no restrictions are placed on the right table in the join condition, and all rows in the right table are returned

    After from, the table written first is the left table, and then the right table

Left connection: all contents of the left table will be returned. If there is no match in the right table, it will be displayed in the corresponding position null
select Table name.Listing
from Table name 1 left outer join Table name 2
on Table name 1.column=Table name 2.column

Right connection: all contents of the right table will be returned. If there is no match in the left table, it will be displayed in the corresponding position null
select Table name.Listing
from Table name 1 right outer join Table name 2
on Table name 1.column=Table name 2.column

5.5 sub query

Subquery refers to nested query statements in query statements.

  • When the query requires no explicit prompt results, you can use sub query.
  • When the aggregate function cannot be written out directly, the results of the aggregate function can be found with the help of query statements.
Format:
select Listing from Table name where Listing in(select Query statement);

Note: where The sub query will check what the post condition requires

nested subqueries

It means that the subquery also contains other subqueries

Related sub query

Also known as single value sub query, it returns only one value, and then compares a column of values with the values returned by the query.

5.5 combination of insert and select

The combination of insert and select is to insert the query results into a table

Format:
insert into Table name select Query statement

5.6 combination of update and select

Update query results

6 view

6.1 definitions

view:

  • A view is a virtual table generated based on the query results of an actual table (real table).
  • Its function is to facilitate users to operate data.

6.2 creating views

create view View name as select sentence;

Note: the addition, deletion and modification of virtual table will also change the real table.

6.3 modifying views

alter view View name as select sentence;

alter changes the view structure.

6.4 deleting views

drop view View name;
drop view View name 1,View name 2,......;

6.5 view operation

insert data

insert into View name values(Value 1,Value 2,......);

Modify data

update View name set Listing=value where condition;

Delete data

delete from View name where condition;

7 index

7.1 index definition

MySQL index is a structure that sorts the values of one or more columns in a database table. Indexing helps to get query information faster for positioning.

Role of index creation

  • Speed up data retrieval (positioning);
  • Ensure data uniqueness (uniqueness);
  • Realize the referential integrity between tables (columns referenced by foreign key constraints);
  • When using group by and order by clauses to query, using indexes can reduce the time of grouping and sorting.

Disadvantages of creating indexes

  • Slow down data modification;
  • Occupy data space and spend some time;
  • Consume certain system performance.

Indexed columns

  • Define columns with primary keys and foreign keys;
  • Columns that are queried quickly or frequently in the specified range;
  • Frequently used columns in links;
  • Columns that need to be retrieved quickly or frequently in sort order.

7.2 index classification

Physically, it is divided into clustered index and non clustered index
Clustered index: the logical order of the key values of the index determines the physical order of the corresponding rows in the table.
Nonclustered index: nonclustered indexes access data in a table by recording addresses. The logical order of indexes is different from the physical storage order of rows in the table.

primary key

The value of a column in an index must be unique and not empty. The primary key index is a clustered index.

General index

The basic index type in MySQL allows you to insert duplicate values and null values into the columns defining the index.

unique index

The value in the index column must be unique, but can be empty. It can be a clustered index or a nonclustered index.

Full text index

By establishing inverted index, the retrieval efficiency can be greatly improved and the problem of judging whether the field is included can be solved.
Inverted index, also known as reverse index, put file or reverse file, is an index method, which is used to map the storage location of a word in a document or a group of documents under full-text search. It is the most commonly used data structure in document retrieval system.

single column

An index that contains a column field.

Multi column index

An index that contains multiple column fields.

Spatial index

Spatial index refers to a data structure arranged in a certain order according to the position and shape of spatial objects or some spatial relationship between spatial objects, which contains the summary information of spatial objects, such as object identification, circumscribed rectangle and pointer to spatial object entity.

7.3 index creation

Create normal index
create index Index name on Table name(Listing);

Create unique index
create unique index Index name on Table name(Listing); 

Create full-text index
create full text index index catalogue on Article name;

7.4 deleting indexes

drop index Index name on Table name;

Keywords: Database MySQL SQL

Added by doreg28 on Tue, 28 Sep 2021 12:07:44 +0300

Popular Keywords

©2024 Programming VIP