Shulou(Shulou.com)06/01 Report--

The difference and use between UNION and UNION ALL

The UNION operator is used to merge the result sets of two or more select statements.

Note that select statements within UNION must have the same number of columns. Columns must also have similar data types, and the order of columns in select statements must be the same

The difference between UNION ALL and UNION is that UNION ALL queries all information, that is, duplicate values are allowed, and UNION removes duplicate values by default; the DISTINCT keyword

The DISTINCT keyword is used to return a unique different value.

Syntax: SELECT DISTINCT column name FROM table name

Note: DISTINCT must be placed in front of the field.

The difference between DISTINCT and GROUP BY:

When the amount of data is very large, for example, 3 million of the 10 million pieces of data are duplicate data. The performance of using DISTINCT is better than that of GROUP BYl; for relatively small amounts of data, such as 10,000 of 10 million, the performance of GROUP BY will be much better than that of DISTINCT. TOP sentence

The TOP statement is used to specify the number of records to return.

Top clauses are very useful for large tables with thousands of items.

Note: not all database systems support the TOP clause.

For example, the first two records are selected in the query table: SELECT TOP 2 * FROM Persons

Select 50% of the records in the query table:

SELECT TOP 50 PERCENT * FROM PersonLIKE operator

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

For example: SELECT name FROM table_name WHERE name LIKE 'king'

Tip:% can be used to define wildcards (missing letters in the pattern)

By using the NOT keyword, you can query data that does not contain the letter in the table

JOIN is used based on the relationship between columns in two or more tables

Get data from two tables by using the JOIN keyword

For example: SELECT Customer.name,Orders.name FROM Customer INNER JOIN Orders ON Customer.id = Orders.id GROUP BY Orders.name

INNER JOIN (internal link)

LEFT JOIN: returns all rows from the left table even if there is no match in the right table

RIGHT JOIN: returns all rows from the right table even if there is no match in the left table

FULL JOIN: returns a row as long as there is a match in one of the tables

The SELECT INTO statement is used to create a backup copy of the table

Select data from one table and insert it into another table

Commonly used to create backup copies of tables or for archiving records

Syntax: SELECT * INTO new_table_name FROM old_table_name

The IN clause can be used to copy a table to another database

SELECT * INTO username IN 'Backup.mdb' FROM user

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.