SQL Advanced Chapter

1. Select top

In MySQL:

SELECT * from persons limit 5;

Equivalent to the following in Oracle:

SELECT * FROM Persons <=5;

Often used in the page turn:

SELECT * from persons limit 5, 10;

2. Like

The LIKE operator is used to search for a specified pattern in a column in a WHERE clause.

SELECT * from Websites WHERE name is like ' g% '; by G (%g is the end of G, fuzzy query is%g%)

% is a wildcard

wildcard characters	Description
%	Replace 0 or more characters
_	Replace one character
[charlist]	Any single character of the word columns
[^charlist] Or [! charlist]	Any single character that is not in the word columns

The following SQL statement chooses name to start with "G", then an arbitrary character, then "O", then an arbitrary character, then "le" for all sites:

SELECT * from Websites WHERE name is like ' G_o_le ';

MySQL uses the REGEXP or not REGEXP operator (or rlike and not rlike) to manipulate regular expressions.

The following SQL statement selects all Web sites that have the name start with "G", "F", or "s":

SELECT * from Websites WHERE name REGEXP ' ^[gfs] ';

The following SQL statement selects a Web site whose name begins with a to H letter:

SELECT * from Websites WHERE name REGEXP ' ^[a-h] ';

The following SQL statement selects a Web site whose name does not start with a to H letter:

SELECT * from Websites WHERE name REGEXP ' ^[^a-h] ';

3, in

The in operator allows you to specify multiple values in the WHERE clause

The following SQL statement selects all sites with the name "Google" or "Rookie Tutorial":

SELECT * from Websites WHERE name in (' Google ', ' rookie Tutorial ');

4, between

The between operator is used to select values in the range of data between two values

The following SQL statement selects all Web sites with Alexa between 1 and 20:

SELECT * from Websites WHERE Alexa between 1 and;

To display sites that are not within the scope of the above instances, use not between:

The following SQL statement selects the Alexa between 1 and 20 but country is not a USA and IND for all sites:

EM style= "line-height:1.5;" > SELECT * from websites  WHERE (Alexa between 1 and)   and not country in (' USA ', ' IND ');

Example of a between operator with literal values

The following SQL statement selects all sites with name starting with the letters between ' A ' and ' H ':

SELECT * from Websites WHERE name between ' A ' and ' H ';

The following SQL statement selects all access records from date between ' 2016-05-10 ' and ' 2016-05-14 ':

SELECT * from Access_log WHERE date between ' 2016-05-10 ' and ' 2016-05-14 ';

Please note that in different databases, the between operator produces different results! In some databases, between selects a field between two values but does not include two test values. In some databases, between selects a field between two values and includes two test values. In some databases, between selects a field between two values and includes the first Test value but excludes the last Test value. Therefore, please check how your database handles the between operator!

SQL Advanced Chapter