MySQL face test

0. What is the difference between the internal connection, the left join connection and the right link?

Connection type

INNER Join (inner connection)

OUTER join (left OUTER join)

Right OUTER join (left OUTER join)

Full OUTER join (fully outer join)

The connection type is divided into two kinds of inner and outer connections. The inner connection is the equivalent connection, and the outer connection is divided into left, right and completely outside connection three kinds. The inner and outer words in the connection type are not writable.

Inner joins, also known as natural joins, only two tables match the rows to appear in the result set. The returned result set selects all matching data from two tables, discarding unmatched data. Because an inner join removes all rows from the result table that do not match the other connection tables, the internal connection may cause information loss.

An inner join displays only records that meet the join criteria, and the outer join displays records in the table, in addition to the records that match the criteria, for example, if you use a left outer join, the records in the left table are also displayed.

1. How do I round the average of the field Market_price in database table product and keep a decimal?

SELECT ROUND (AVG (Market_price), 1) from PRODUCT;

SQL AVG function

Definition and usage

The AVG function returns the average of a numeric column. NULL values are not included in the calculation.

SQL AVG () syntax

SELECT AVG (column_name) from table_name

ROUND ()

The function ROUND function is used to round a numeric field to a specified number of decimal digits.

SQL ROUND () syntax

SELECT ROUND (column_name,decimals) from table_name

Parameter description

column_name required. The field to be rounded.

Decimals required. Specifies the number of decimal digits to return.

2. How do I implement a query on Rows n to M Records?

SELECT * from TABLE1 LIMIT n-1,m-1;

3. What are the advantages and disadvantages of the index?

Pros: You can create indexes in a table to query data more quickly and efficiently. Users cannot see the index, they can only be used to speed up search/query.

Disadvantage: Updating a table that contains an index requires more time than updating a table with no indexes, because the index itself needs to be updated.

Therefore, it is ideal to create indexes only on columns (and tables) that are often searched.

MySQL face test