SQL Basics (v): SQL functions

First, the SQL Aggregate function:

The SQL Aggregate function calculates the value obtained from the column and returns a single value.

1. AVG () function

The AVG () function returns the average of a numeric column.

Syntax: SELECT AVG (column_name) from table_name

// get the average from the Count column of the Access_log table: SELECT AVG (count) as Countaverage from Access_log; // Select "site_id" and "count" with more than the average number of visits: available when conditions   > (SELECT AVG (count) from Access_log);

2.COUNT () function

The count () function returns the number of rows that match the specified criteria.

(1) The count (column_name) function returns the number of values for the specified column (NULL does not count in):

Syntax: SELECT COUNT (column_name) from table_name;

(2) The count (*) function returns the number of records in the table:

Syntax: SELECT COUNT (*) from table_name;

(3) The Count (DISTINCT column_name) function returns the number of different values for the specified column:

Syntax: SELECT COUNT (DISTINCT column_name) from table_name;

notes: COUNT (DISTINCT) applies to ORACLE and Microsoft SQL Server, but not to Microsoft Access.

// calculate the total number of visits to "site_id" =3 in the "Access_log" table: SELECT COUNT (count) as Nums from access_logwhere site_id=3; // To calculate the total number of records in the "Access_log" table: SELECT COUNT (*) as nums from Access_log; // calculate the number of records for different site_id in the "Access_log" table: SELECT COUNT (DISTINCT site_id) as nums from Access_log;

3. First () function

The first () function returns the value of the number one record in the specified column.

Syntax: SELECT first (column_name) from table_name;

Note: only MS Access supports the first () function.

SQL Server Syntax: SELECT TOP 1 column_name from table_name ORDER by column_name ASC;

MySQL syntax: SELECT column_name from table_name ORDER by column_name ASC LIMIT 1;

Oracle syntax: SELECT column_name from Table_nameorder by column_name ASC WHERE ROWNUM <=1;

// The SQL statement selects the value of the first record in the "name" column of the "Websites" table: 1;

4. Last () function

The last () function returns the value of the final record in the specified column.

syntax: SELECT last (column_name) from table_name;

Note: only MS Access supports the last () function.

SQL Server Syntax: SELECT TOP 1 column_name from table_name ORDER by column_name DESC;

MySQL syntax: SELECT column_name from table_name ORDER by column_name DESC LIMIT 1;

Oracle syntax: SELECT column_name from table_name ORDER by column_name DESC WHERE ROWNUM <=1;

// The SQL statement selects the value of the last record in the "name" column of the "Websites" table:  1;

5, MAX () function

The max () function returns the maximum value for the specified column.

Syntax: SELECT MAX (column_name) from table_name;

 

// get the maximum value from the "Alexa" column of the "Websites" table: SELECT MAX (Alexa) as max_alexa from Websites;

6, MIN () function

The min () function returns the minimum value for the specified column.

Syntax: SELECT MIN (column_name) from table_name;

// get the minimum value from the "Alexa" column of the "Websites" table SELECT MIN (Alexa) as min_alexa from Websites;

7. SUM () function

The sum () function returns the total number of numeric columns.

Syntax: SELECT SUM (column_name) from table_name;

// find the total number of "count" fields for the "Access_log" table: SELECT SUM (count) as nums from Access_log;

Second, GROUP by statement:

The GROUP BY statement is used in conjunction with aggregate functions to group result sets based on one or more columns .

Grammar:

SELECT column_name, aggregate_function (column_name)
From table_name
WHERE column_name operator Value
GROUP by column_name;

// Statistics Access_log The number of visits for each site_id: SELECT site_id, SUM (access_log.count) as Numsfrom access_log GROUP by site_id;

Multiple table connections:

// Count the number of records accessed by all sites: SELECT Websites.name,count (access_log.aid) as nums from Access_logleft JOIN websiteson access_log.site_id= Websites.idgroup by Websites.name;

Third, having clauses:

The addition of the HAVING clause in SQL is because the WHERE keyword cannot be used with an aggregate function.

Having clauses allows us to filter groups of data after grouping.

Grammar:

SELECT column_name, aggregate_function (column_name)
From table_name
WHERE column_name operator Value
GROUP by column_name
Having aggregate_function (column_name) operator value;

// Find sites with total traffic greater than 200.  SELECT websites.name, Websites.url, SUM (Access_log.count) as Nums from (Access_loginner JOIN Websiteson Access_ log.site_id=$;

// you want to find sites with total traffic greater than 200, and Alexa rankings are less than 200. We add a common where clause in the SQL statement:SELECT websites.name, SUM (Access_log.count) as nums from Websitesinner JOIN Access_lo GON websites.id=A.

Iv. SQL Scalar functions:

The SQL Scalar function returns a single value based on the input value.

1. UCASE () function

The UCASE () function converts the value of a field to uppercase.

Syntax: SELECT UCASE (column_name) from table_name;

Syntax for SQL Server: SELECT UPPER (column_name) from table_name;

// Choose the "name" and "url" columns from the "Websites" table and convert the value of the "name" column to uppercase: SELECT UCASE (name) as Site_title, Urlfrom Websites;

2. LCASE () function

The LCASE () function converts the value of the field to lowercase.

Syntax: SELECT LCASE (column_name) from table_name;

Syntax for SQL Server: SELECT LOWER (column_name) from table_name;

3, MID () function

The MID () function is used to extract characters from a text field.

Syntax: SELECT MID (Column_name,start[,length]) from table_name;

(1) column_name: Required. The field to extract the characters from.

(2) Start: Required. Specifies the starting position (the starting value is 1).

(3) Length: Optional. The number of characters to return. If omitted, the MID () function returns the remaining text.

// extract the first 4 characters from the "name" column of the "Websites" table: SELECT MID (name,1,4) as Shorttitlefrom Websites;

4. LEN () function

The LEN () function returns the length of the value in the Text field.

Syntax:SELECT LEN(column_name) from table_name;

The function in MySQL is length ():SELECT LENGTH(column_name) from table_name;

// Choose the length of the value in the "name" and "url" columns from the "Websites" table:  as Lengthofurlfrom Websites;

5. ROUND () function

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

Syntax: SELECT ROUND (column_name,decimals) from table_name;

Parameters are required: (1) The field to be rounded, and (2) specify the number of decimal digits to be returned.

//ROUND (x): Returns an integer that is rounded by the parameter X. Mysql>SelectROUND (-1.23); --1MySQL>SelectROUND (-1.58); --2MySQL>SelectROUND (1.58); -2//ROUND (X,D): Returns a number that is rounded by a parameter X that has a D decimal. If D is 0, the result will not have a decimal point or fractional part. Mysql>SelectROUND (1.298,1); -1.3MySQL>SelectROUND (1.298,0); -1

6. Now () function

The now () function returns the date and time of the current system.

Syntax: SELECT now () from TABLE_NAME;

// Select Name,url from the "Websites" table and the date of the day: SELECT name, URL, now () as Datefrom Websites;

7. FORMAT () function

The format () function is used for formatting the display of a field.

Syntax: SELECT FORMAT (Column_name,format) from table_name;

Parameter: column_name: Required. The field to format.

Format: Required. prescribed format.

// choose Name, URL, and date formatted as YYYY-MM-DD from the Websites table: SELECT name, URL, date_format (now (),'%y-%m-%d') as Datefrom Websites;

SQL Basics (v): SQL functions