SQL Advanced application (in operator, between, alias: AS)

SQL in operator

The in operator allows the developer to specify multiple values in the WHERE clause, with the following syntax

SELECT column_name (s) from Table_namewhere column_name in (value1, value2,....)

Example: From the persons table, select the person whose surname is Adams and Carter:

SELECT *from personswhere LastName in (' Adams ', ' Carter ')

　　

SQL between operator

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

operator between ... and selects a range of data between two values. These values can be numeric, text, or date, and the syntax for between is as follows

SELECT column_name (s) from Table_namewhere Column_namebetween value1 and value2

Example 1: In the persons table, if you want to show people in alphabetical order between "Adams" (including) and "Carter" (excluding) , use the following SQL

SELECT * from Personswhere lastnamebetween ' Adams ' and ' Carter '

Important: Different databases on between ... The and operator is handled in a different way. Some databases list people between "Adams" and "Carter", but not "Adams" and "Carter," and some databases list people between "Adams" and "Carter" and include "Adams" and "Carter", while others Lists the people between "Adams" and "Carter", including "Adams", but excludes "Carter".

　　So, please check how your database handles between .... And operator!

　　Example 2: In the persons table, use the not operator if you want to use the above example to show people outside the range

SELECT * from Personswhere lastnamenot between ' Adams ' and ' Carter '

　　

SQL alias (alias): as operator

By using SQL, you can specify aliases for column names and table names

SQL alias syntax for tables

SELECT column_name (s) from Table_nameas Alias_name

SQL Alias syntax for columns

　　

Alias instance: Using Table name aliases

Suppose that there are now two tables: "Persons" and "Product_orders", respectively. We assign them the alias "P" and "Po", respectively.

Now, to list all the orders for "John Adams", you can use the following SELECT statement:

SELECT Po. OrderID, P.lastname, P.firstnamefrom Persons as P, product_orders as Ppowhere p.lastname= ' Adams ' and p.firstname= ' John '

If it is a SELECT statement that does not use aliases

SELECT Product_orders.orderid, Persons.lastname, Persons.firstnamefrom Persons, Product_orderswhere Persons.LastName = ' Adams ' and persons.firstname= ' John '

As you can see from the two SELECT statements above, aliases make the query program easier to read and write.

Alias instance: Using one of the column name aliases

Table Persons:

City

Id	LastName	FirstName	Address
1	Adams	John	Oxford Street	London
2	Bush	George	Fifth Avenue	New York
3	Carter	Thomas	Changan Street	Beijing

　　 SQL:

SELECT LastName as Family, FirstName as Namefrom Persons

Results:

Family	Name
Adams	John
Bush	George
Carter	Thomas

SQL Advanced application (in operator, between, alias: AS)