This is the 13th day of my participation in the November Gwen Challenge. Check out the event details: The last Gwen Challenge 2021

Continue above…

The union operator

The UNION operator is used to join the results of more than two SELECT statements into a result set. Multiple SELECT statements delete duplicate data.

SELECT expression1, expression2, ... expression_n
FROM tables
[WHERE conditions]
UNION [ALL | DISTINCT]
SELECT expression1, expression2, ... expression_n
FROM tables
[WHERE conditions];
Copy the code
parameter
  • expression1, expression2, … Expression_n: Column to be retrieved.
  • Tables: The data table to retrieve.
  • WHERE conditions: Optional.
  • DISTINCT: Deletes duplicate data from the result set. This option is optional. The UNION operator removes duplicates by default, so the DISTINCT modifier has no effect on the result.
  • ALL: ALL result sets, including duplicate data, are returned.

Sort order by

The ORDER BY clause sorts the query data and returns the data:

SELECT field1, field2,... fieldN FROM table_name1, table_name2... ORDER BY field1 [ASC][default ASC]], field2... [ASC [DESC][default ASC]]Copy the code
  • You can use any field as a sort condition to return the sorted query results.
  • You can set multiple fields to sort.
  • You can use the ASC or DESC keyword to set the query results to be sorted in ascending or descending order. By default, it is sorted in ascending order.
  • You can add WHERE… The LIKE clause sets the condition.

Group by group

The GROUP BY statement groups result sets BY one or more columns.

On grouped columns we can use COUNT, SUM, AVG, etc.

SELECT column_name, function(column_name)
FROM table_name
WHERE column_name operator value
GROUP BY column_name;
Copy the code

Example:

SELECT name, COUNT(*) FROM   employee_tbl GROUP BY name;
Copy the code

WITH ROLLUP

WITH ROLLUP allows you to perform the same statistics based on grouped statistics (SUM,AVG,COUNT…) .

For example, we grouped the above data table by name and counted the login times of each person:

SELECT name, SUM(signin) as signin_count FROM  employee_tbl GROUP BY name WITH ROLLUP;
Copy the code

League table query

  • INNER JOIN: Gets a record of the field matching relationship between two tables.
SELECT a.runoob_id, a.runoob_author, b.runoob_count FROM runoob_tbl a INNER JOIN tcount_tbl b ON a.runoob_author = b.runoob_author;
Copy the code
  • LEFT JOIN: Retrieves all records from the LEFT table, even if there are no matching records from the right table.
SELECT a.runoob_id, a.runoob_author, b.runoob_count FROM runoob_tbl a LEFT JOIN tcount_tbl b ON a.runoob_author = b.runoob_author;
Copy the code
  • RIGHT JOIN: In contrast to LEFT JOIN, it is used to get all the records in the RIGHT table, even if there are no matching records in the LEFT table.
SELECT a.runoob_id, a.runoob_author, b.runoob_count FROM runoob_tbl a RIGHT JOIN tcount_tbl b ON a.runoob_author = b.runoob_author;
Copy the code

Regular expression

model describe
^ Matches the start of the input string. If the Multiline property of the RegExp object is set, ^ also matches the position after ‘\n’ or ‘\r’.
$ Matches the end of the input string. If the Multiline property of the RegExp object is set, $also matches the position before ‘\n’ or ‘\r’.
. Matches any single character except “\n”. To match any character including ‘\n’, use a pattern like ‘[.\n]’.
[…]. Collection of characters. Matches any of the contained characters. For example, ‘[ABC]’ can match ‘a’ in” plain”.
[^…]. A collection of negative characters. Matches any character that is not contained. For example, ‘[^ ABC]’ can match ‘p’ in” plain”.
p1 p2
* Matches the preceding subexpression zero or more times. For example, zo* matches “z” and “zoo”. * is equivalent to {0,}.
+ Matches the previous subexpression one or more times. For example, ‘zo+’ matches “zo” and “zoo”, but not “z”. + is equivalent to {1,}.
{n} N is a non-negative integer. Match certain n times. For example, ‘o{2}’ does not match the ‘O’ in “Bob”, but does match two o’s in “food”.
{n,m} Both m and n are non-negative integers, where n <= m. At least n times and at most m times are matched. 
SELECT name FROM person_tbl WHERE name REGEXP '^st';
Copy the code