Database (ii): Advanced Search

• Working with functions in the database
  • 1 Common text-processing functions
  • 2 common numeric processing functions
  • 3 Common aggregation functions
• Grouped data
  • 1 Data grouping
  • 2 filtering of grouped data
  • 3 SELECT clause Order
• junction table
  • 1 Relational tables
  • 2 coupling
    • 21 Inner Coupling
    • 22 Outer Coupling
    • 23 Cross Coupling

1. Working with functions in the database

There are many data processing functions available in the database, and using the functions in the database can simplify the operation, but it can also lead to compatibility problems, because different database management systems have different data processing functions to be aware of.

1.1 Common text-handling functions:

function	Description
Left ()	Returns the character to the left of a string
LENGTH ()	Returns the length of a string
LOWER ()	Convert a string to lowercase
Right ()	Returns the character to the right of a string
UPPER ()	Convert a string to uppercase

1.2 Common numeric processing functions:

function	Description
ABS ()	Returns the absolute value of a number
COS ()	Cosine
EXP ()	The exponential value of a number
SIN ()	Sinusoidal
SQRT ()	Square root

1.3 Common aggregation functions

function	Description
AVG ()	Returns the average of a column
COUNT ()	Returns the number of rows in a column
MAX ()	Returns the maximum value of a column
MIN ()	Returns the minimum value of a column
SUM ()	Returns the sum of a column

When using AVG (), you can also filter with the WHERE statement.

2. Grouping Data 2.1 data grouping

When using some aggregation functions, such as AVG can only calculate the average of the entire column or satisfy the average of a certain condition (where clause), if we now want to have different conditions (the average of different vend_id under the previous example) and statistical processing, You need to use data grouping.

groups are created by the group by clause of the SELECT statement

An example of using grouping:

In this example, the aggregation process is done by different vend_id (averaging)

Some provisions on the use of GROUP by:

• The GROUP by clause must appear after the where clause before theORDER by clause
• If there are NULL columns in the Grouping column, this column is also returned as a grouping

2.2 Filtering of grouped data

When it comes to filtering, the first thought is to use the where clause, but use where in the grouped data to achieve the desired result,where there is no concept of grouping. Group filtering uses the new clause having the having to filter the grouping.

• the Having and WHERE syntax is the same, and the only difference is the difference in the keywords. Where is for filtering rows, having a group for.
• where the data is filtered before grouping, having to filter the data after grouping

In this example, before grouping, the WHERE clause is used to filter, then grouped according to the requirements, and finally in the result after grouping has a having filter.

2.3 SELECT clause Order

SELECT > FROM > WHERE > GROUP BY > HAVING > ORDER BY

3. junction Table 3.1 Relationship table

In the database system, the same data appear in the database multiple times is not a satisfying thing, this is the basis of relational database, relational table is to break the information into multiple tables, a class of data a table, the tables through some common values are interrelated.

The association between a table and a table is linked by a foreign key , for example, a column in table A is a primary key in table B, so that a column can be used as a foreign key for table a , with the addition of the keys A and B tables to connect.

Primary key and foreign key:

3.2 Coupling

There are many ways of joining in sql: inner coupling, outer join, cross-join

3.2.1 Inner Coupling

Internal Connection
The INNER JOIN query operation lists the data rows that match the join criteria, and it uses comparison operators to compare the column values of the concatenated columns.

Direct examples to illustrate:

vendors and products are two tables in the database, which are joined by the vend_id column,vend_id as the primary key in Vendors , and it is a foreign key in the products, and the results are two tables respectively.

3.2.2 Outer coupling

The outer coupling is divided into: Left outer coupling, right outer junction, full coupling

Left Outer junction

• Based on left table, the data of vendors.vend_id = products.vend_id is connected, and then the corresponding item without the left table is displayed, and the column of the right table is null

Right outer junction

• With the right table as the benchmark, connect vendors.vend_id = products.vend_id data to display the corresponding items that are not in the right table, and the column for the left table is null

Full coupling

• A full outer join returns all rows from the left and right tables. When a row does not have a matching row in another table, the selection list column for the other table contains a null value. If there are matching rows between the tables, the entire result set row contains the data values of the base table.

3.2.3 Cross Coupling

The data returned here is the corresponding Cartesian product, and each row in the left table is combined with all the rows in the right table

Database (ii): Advanced Search