SQL Foundation (ii): SQL command

1. SQL SELECT TOP clause

The SELECT TOP clause is used to specify the number of records to return. The SELECT TOP clause is useful for large tables with thousands of records.

Note: Not all database systems support the SELECT TOP clause.

Syntax: SELECT TOP number|percent column_name (s) from table_name;

　　MySQL syntax: SELECT column_name (s) from table_name LIMIT number;

Oracle Syntax: SELECT column_name (s) from table_name WHERE ROWNUM <= number;

2 ; // Select the first two records  PERCENT * from Websites; // using percentages as parameters, select the previous 50% record

2. SQL Like operator

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

Syntax: SELECT column_name (s) from table_name WHERE column_name like pattern;

By using the NOT keyword, you can select records that do not match the pattern.

SELECT *From websiteswhere name like'g%';//Select all customers with name start with the letter "G"SELECT *From websiteswhere name like'%k';//Select all customers with name ending with the letter "K"SELECT *From websiteswhere name like'%oo%';//Select all customers that name contains the schema "oo"SELECT *From websiteswhere name   ' not ' like'%oo%';//Select all customers that name does not contain the schema "oo"

3. SQL wildcard characters

Wildcard characters are used in conjunction with the SQL like operator. SQL wildcard characters are used to search for data in a table. In SQL, you can use the following wildcard characters:

https%: Start with the letter "https"

%oo%: Contains the alphabet oo

_oogle: Start with any character, then all records of "Oogle"

SELECT *'g_o_le'; // choose Name to start with "G", then an arbitrary character, then "O", then an arbitrary character, then "le" for all sites

[charlist] Wildcard: MySQL uses the REGEXP or not REGEXP operator (or rlike and not rlike) to manipulate a regular expression.

SELECT *From websiteswhere name REGEXP'^[gfs]';//Select all sites with name starting with "G", "F", or "s"SELECT *From websiteswhere name REGEXP'^[a-h]';//Select the site whose name starts with a to H letterSELECT *From websiteswhere name REGEXP'^[^a-h]';//Select a site whose name does not start with a to H letter

4, in operator

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

Syntax: SELECT column_name (s) from table_name WHERE column_name in (value1,value2,...);

SELECT * from websiteswhere name in ('Google',' Rookie tutorial ' ); // Choose all sites with Name "Google" or "Rookie tutorial"

5. SQL between operator

The between operator selects a value in the range of data between two values. These values can be numeric, text, or date.

Syntax: SELECT column_name (s) from table_name WHERE column_name between value1 and value2;

SELECT *1; // Select all sites with Alexa between 1 and 20

SELECT *From websiteswhere Alexa not between1and -;//not between 1 and 20.SELECT *From websiteswhere (Alexa between1and -) and not country in ('USA','IND');//Select all websites with Alexa between 1 and 20 but country not for USA and INDSELECT *From websiteswhere name between'A'and'H';//Select all sites with name starting with the letters between ' A ' and ' H 'SELECT *From websiteswhere name not between'A'and'H';//Select all sites with a name that does not start with the letter between ' A ' and ' H '

SELECT *'2016-05-10'2016-05-14'; // Select All Access records from date between ' 2016-05-10 ' and ' 2016-05-14 '

Note: 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!

The following is the data for the 2 tables for the example demo:

6. Aliases

By using SQL, you can specify an alias for the table name or column name. Basically, aliases are created to make the column names more readable.

Alias syntax for column: SELECT column_name as Alias_name from table_name;

　　Table alias Syntax: SELECT column_name (s) from table_name as Alias_name;

Alias instance of the column:

SELECT name as N, country as C from Websites; // two aliases are specified, one is the alias for the Name column and one is the alias for the country column.  // tip: Double quotation marks or square brackets are required if the column name contains spaces

 "'", country) as Site_info from Websites; // combine three columns (URL, Alexa, and country) and create an alias named "Site_info"

Alias Instance of table:

Select W.name, W.url, A.count, a.date from Websites as W, Access_log as a WHERE a.site_id=w.id and w.name="
      Rookie Tutorial ";
// Select the access record for the rookie tutorial. We use "Websites" and "access_log" tables and specify table aliases "W" and "a" for each of them (by using aliases to make SQL shorter)

The same SQL statement without an alias
SELECT Websites.name, Websites.url, Access_log.count, access_log.date
From Websites, Access_log
WHERE websites.id=access_log.site_id and Websites.name= "rookie Tutorial";

Using aliases is useful in the following situations: (1) More than one table is involved in the query (2) The function is used in the query (3) The column name is long or the readability is poor (4) you need to combine two columns or multiple columns

7. SQL JOIN

The SQL join clause is used to combine rows from two or more tables, based on common fields between these tables.

SELECT websites.id, Websites.name, Access_log.count, Access_log.datefrom websitesinner JOIN Access_logon Websites.id=access_log.site_id; // The " ID" column in the "Websites" table points to the field "site_id" in the Access_log table. The above two tables are linked by the "site_id" column.  // related fields

Different types of SQL JOIN:

• INNER JOIN: Returns a row if there is at least one match in the table
• Left JOIN: Returns all rows from the table, even if there is no match in the right table
• Right JOIN: Returns all rows from the correct table even if there is no match in the left table
• Full JOIN: Returns a row if there is a match in one of the tables

8. SQL INNER JOIN keyword

The INNER JOIN keyword returns a row when there is at least one match in the table.

Syntax: SELECT column_name (s) from table1 INNER joins table2 on Table1.column_name=table2.column_name;

Or: SELECT column_name (s) from the Table1 JOIN table2 on Table1.column_name=table2.column_name;

　　Note:1, INNER join is the same as join.

2. The INNER JOIN keyword returns a row when there is at least one match in the table. If the rows in the "Websites" table do not match in "Access_log", the rows are not listed.

9. SQL Left JOIN keyword

The left JOIN keyword returns all rows from the table (table1), even if there is no match in the right table (table2). If there is no match in the right table, the result is NULL.

Syntax: SELECT column_name (s) from table1 left JOIN table2 on Table1.column_name=table2.column_name;

Or: SELECT column_name (s) from table1 to OUTER JOIN table2 on Table1.column_name=table2.column_name;

　　Note: The left JOIN keyword returns all rows from the table (Websites), even if there is no match in the right table (Access_log).

10. SQL Right JOIN keyword

The right JOIN keyword returns all rows from the correct table (table2), even if there is no match in the left table (table1). If there is no match in the left table, the result is NULL.

Syntax: SELECT column_name (s) from table1 right joins table2 on Table1.column_name=table2.column_name;

Or: SELECT column_name (s) from table1 right OUTER JOIN table2 on Table1.column_name=table2.column_name;

Note: The right JOIN keyword returns all rows from the correct table (Websites), even if there is no match in the left table (Access_log).

11. SQL Full OUTER JOIN keyword

The full OUTER JOIN keyword returns a row as long as there is a match in one of the tables in the left table (table1) and the right table (table2).

The full OUTER join keyword combines the results of the left join and right join.

Syntax: SELECT column_name (s) from table1 full OUTER joins table2 on Table1.column_name=table2.column_name;

// Full OUTER JOIN is not supported in MySQL and you can test the following instance in SQL Server.  SELECT websites.name, Access_log.count, Access_log.datefrom websitesfull OUTER JOIN access_logon websites.id< /c7>=Access_log.site_idorder by Access_log.count DESC;

　　Note: The full OUTER JOIN keyword returns all rows in the left table (Websites) and the right table (Access_log). These rows are also listed if the rows in the "Websites" table do not have a match in "access_log" or if the rows in the "access_log" table do not match in the "Websites" table.

　　Several join comparisons:

SQL Foundation (ii): SQL command