Research on database query statement

Focus on query data, other statements only do understand!!!
"""
Some of the most important SQL commands:
Select-Extract data from the database
Update-updating data
Delete-deleting data
Insert-Inserting data
Create database-Creating new databases
ALTER DATABASE-Modify databases
CREATE table-Creating tables
ALTER TABLE-Change database table
drop table-Delete tables
Create index-Creating indexes (search key)
Drop INDEX-Delete indexes
"""


First: SELECT statement
#select-Extracts data from the database, and the results are stored in a result table called a result set
#column_name (s): Names of all columns in a database table
#column_name: Column Name
#group by: Grouping
#table_name: Table Name
Select key from table name
Select Key,where from table name
SELECT * FROM table name
Select key from table name where Condition 1 and|or condition 2

#列名位于两者之间的数据
Select column name (s) from table name where column name between value1 and value2
#合计函数 (such as SUM) often need to add a GROUP by statement
Select Column Name 1, SUM (column name 2) from table name Group BY column name 1

Select column name (s) from table name where column name in (value1, value2,..)


Second: SELECT DISTINCT statement
#distinct关键词用于返回唯一不同的值, play a heavy role
SELECT DISTINCT Column_name,column_name from table_name


Third: where
1) Meaning: clause used to filter records
#column_name operator Value expression also contains: like, and various operation symbols, <,>=, etc.
The WHERE clause is used to extract records that meet the specified criteria.
TABLE_NAME: Table name
COLUMN_NAME: Column Name
column_name operator value: expression of column name
Select Column_name,column_name from table_name where column_name operator value
Example:
SELECT * from Customers WHERE country= ' Mexico '
SELECT * from Customers WHERE customerid=1


IV: The and & or operators are used to filter records based on more than one condition.
1) and, requires 2 conditions at the same time to meet the output of the results
2) or, only 2 conditions are required to meet 1
3) Example:
SELECT * FROM table name WHERE column name 1= ' Germany ' and column name 2= ' Berlin '
SELECT * FROM table name WHERE column name 1= ' Berlin ' OR column name 2= ' München '
SELECT * FROM table name WHERE column name 1= ' Germany ' and (column name 2= ' Berlin ' or column name 2= ' München '), meaning: Find column name 1= ' Germany ' data from table and (column name 2= ' Berlin ' or column name 2 = ' München ' Meet one of the data)


V: ORDER BY
1) Meaning: Keywords are used to sort the result set by one column or multiple columns
1) The records are sorted by default in ascending order. If you need to sort records in descending order, you can use the DESC keyword
2) Syntax: SELECT column name 1, column name 2 from table name ORDER by column name 1, column name 2 asc| DESC
3) Example
SELECT * FROM table name ORDER by country #默认升序
SELECT * FROM table name ORDER by country ESC #降序
SELECT * FROM table name ORDER by country, CustomerName #按照2个列名的判断条件来排序


Sixth: Specify the number of records to return (different server type statements will have a certain difference)
1) SELECT TOP number|percent column_name (s) from table_name
2) SELECT column_name (s) from table_name limit number
Number: Numbers of data bars to return
3) Example:
SELECT * FROM Persons limit 5
Select TOP 2 * from Customers Select first 2 records
Select top percent * from Customers Select the top 50% record from the table Customers



Seventh: Like
1) Meaning: operator used to search for a specified pattern in a column in a WHERE clause
1) Syntax: SELECT column_name (s) from table_name WHERE column_name like pattern
2) Example:
SELECT * FROM table name WHERE city like ' s% ' selects all customers starting with City with the letter "S",
SELECT * FROM table name WHERE city like '%s ' selects all customers with City ending with the letter "s"
SELECT * FROM table name WHERE city like '%enshi% ' Select all customers in city containing mode "Enshi"
SELECT * FROM table name WHERE city not like '%enshi% ' Select all customers that city does not contain the pattern "Enshi"
3)%: Define a wildcard character



Eighth: The study of wildcard characters
1)% instead of 0 or more characters
2) _ Underline instead of one character
3) Any single character of [charlist] word columns
4) [^charlist] or [!charlist] any single character not in Word columns
5) Example:
SELECT * from Customers WHERE city like ' ber% '
SELECT * from Customers WHERE city like '%es% '
SELECT * FROM Customers WHERE city like ' _erlin ' selects City to start with an arbitrary character, then all customers "Erlin"
SELECT * from Customers WHERE city like ' l_n_on '
SELECT * FROM Customers the WHERE city like ' [bsp]% ' Select all customers starting with ' B ', ' s ' or ' P '
SELECT * FROM Customers WHERE city like ' [a-c]% ' selects all customers who start city with ' A ', ' B ' or ' C '
SELECT * FROM Customers WHERE city like ' [!bsp]% ' selects all customers that city does not start with ' B ', ' s ' or ' P '



Nineth: In operator
1) allows you to specify multiple values in the WHERE clause
2) Syntax: SELECT COLUMN_NAME (s) from table_name where column_name in (Value1,value2,...)
3) Example:
SELECT * from Customers (' Paris ', ' London ') Select all customers of city as "Paris" or "London"
Find the city data from the table, which is limited to Paris fire London.



Tenth: between
1) Meaning: The between operator is used to select values in the range of data between two values, which can be numeric, text, or date
2) Syntax: SELECT COLUMN_NAME (s) from table_name where column_name between value1 and value2
3) Example:
SELECT * FROM table name WHERE price between and 20 Select all products between 10 and 20
SELECT * FROM table name WHERE price is not between and 20 products not in range, with not between
SELECT * FROM table name WHERE (price between and a) and not CategoryID in (all-in-one) pick prices between 10 and 20 but CategoryID not 1, 2, or 3 All Products
SELECT * FROM table name WHERE ProductName not between ' C ' and ' M ' selects all products that ProductName do not start with letters between ' C ' and ' m '
SELECT * FROM table name WHERE OrderDate between #07/04/1996# and #07/09/1996# select OrderDate between ' 04-july-1996 ' and ' 09-july-1996 ' All orders between the
4) Note: whether to include the critical value itself, need to test according to the situation


11th: Aliases
1) Meaning: Using SQL, you can specify aliases (alias) for the table name or column name, and basically create aliases to make the column names more readable
2) Use:
More than one table is involved in the query
A function was used in the query
Column name is long or poor readability
You need to combine two columns or multiple columns
3) Syntax:
SELECT column_name as Alias_name create alias from table_name column name
SELECT column_name (s) from table_name as ALIAS_NAME table name Create Alias
4) Example:
SELECT CustomerName as Customer, ContactName as [contact person] from Customers;
Alias for the #CustomerName column, ContactName the alias of the column. Tip: If the column name contains spaces, double quotation marks or square brackets are required
SELECT CustomerName, address+ ', ' +city+ ', ' +postalcode+ ', ' +country as Address from Customers
#把四个列 (Address, City, PostalCode, and Country) together and create an alias named "Address":

Research on database query statement