Database (11) mysqlsql Functions

Database (11) mysqlsql Functions

. 1) count

Syntax:

select count(*） from tableName;select count(column_name) from tableName;select count(DISTINCT column_name) from tableName;

Note:
The count (column_name) function returns the number of values in the specified column (NULL is not counted ):
The count (distinct column_name) function returns the number of different values in the specified column.

. 2) group

Grouping functions are used to group multiple result sets of one or more columns. They are often used in combination with Aggregate functions.

Syntax:

SELECT column_name, aggregate_function(column_name)FROM table_nameWHERE column_name operator valueGROUP BY column_name

. 3) having

The reason for having function in SQL is that where cannot be used together with aggregate function. In general, having is used to filter the query results under where conditions.

Syntax:

SELECT column_name, aggregate_function(column_name)FROM table_nameWHERE column_name operator valueGROUP BY column_nameHAVING aggregate_function(column_name) operator value

For example, the tt table contains the following data:

The tt2 data in the database table is as follows:

 

The sum of tt_value of each index under name in the tt statistical table, and the value of the statistical result is not less than 20;

select name,sum(tt_value) from tt group by name having sum(tt_value)>=20;

The result set is as follows:

Note:

When the where clause, group by clause, having clause, and aggregate function are included at the same time, the execution sequence is as follows:
(1) execute the where clause to search for qualified data;
(2) Use the group by clause to group data. Run the clustering function on the group formed by the group by clause to calculate the values of each group;
(3) Use the having clause to remove groups that do not meet the conditions.

. 4) avg

The AVG function returns the average value of the value column (the NULL value is not included in the calculation ).

Syntax:

    SELECT AVG(column_name) FROM table_name

For example, the SQL statement of the column in tt table that is greater than the average value of tt_value is as follows:

select * from  tt where tt_value >(select avg(tt_value) from tt);

The average value of tt_value is 35, and the columns greater than 35 are as follows:

. 4) join

Multiple tables are connected by fields in the table to return results. join has the following types:

JOIN: If the table has at least one match, the row is returned.
Left join: returns all rows from the LEFT table even if no match exists in the right table.
Right join: returns all rows from the RIGHT table even if no match exists in the left table.
Full join: if one of the tables matches, the row is returned.
The following describes the usage of the above methods:

(A) inner join

The result is the intersection of two tables.

(B) left join

The left join keyword returns all rows from the left table (tt), even if no matching row exists in the right table (tt2.

(C) right join

Right join is the opposite of left join z. All rows in the right table are returned even if no matching rows exist in the left mark.

(D) full join

It can be understood as the set of left join and right join, that is, the Union of tt table and tt2 table result set.

. 5) union

Combine the results of the two SQL statements. The results of the two SQL statements must be consistent, and the column sequence must be consistent.

Syntax:

   select column1,column2 from table1 union select column1,column2 from table2

 

Run the following SQL statement:

 

select name,status from tt union select name,status from tt2;

The result set is as follows:

 

Through the tt and tt2 table data, we analyze the union data results and find that the union function deletes repeated records and the results are sorted by name.

Union all

Union all combines the results of two SQL statements without removing duplicate data. We execute the following SQL statement:

 

select name,status from tt union all select name,status from tt2;

The result set is as follows:

 

It can be seen that, compared with union all, union needs to sort and remove duplicate data, so the performance is much higher than union all.