8.IN Operator
specify multiple values in a WHERE clause
1.IN Syntax
SELECT column_name(s)
FROM table_name
WHERE column_name IN (value1, value2, ...);
or;
SELECT column_name(s)
FROM table_name
WHERE column_name IN (SELECT STATEMENT);
- Example
method1: IN
SELECT * FROM Customers
WHERE Country IN ('Germany', 'France', 'UK');
method2: NOT IN
SELECT * FROM Customers
WHERE Country NOT IN ('Germany', 'France', 'UK');
method3: IN (SELECT * FROM *)
SELECT * FROM Customers
WHERE Country IN (SELECT Country FROM Suppliers);
9.BETWEEN Operator
1.selects values within a given range AND begin and end values are included.
(The values can be numbers, text, or dates.)
1. BETWEEN Syntax
SELECT column_name(s)
FROM table_name
WHERE column_name BETWEEN value1 AND value2;
2.NOT BETWEEN
SELECT * FROM Products
WHERE Price NOT BETWEEN 10 AND 20;
3.BETWEEN with IN
SELECT * FROM Products
WHERE Price BETWEEN 10 AND 20
AND NOT CategoryID IN (1,2,3);
4.BETWEEN Text Values example
SELECT * FROM Products
WHERE ProductName BETWEEN 'Carnarvon Tigers' AND 'Mozzarella di Giovanni'
ORDER BY ProductName;
5.NOT BETWEEN Text Values Example
SELECT * FROM Products
WHERE ProductName NOT BETWEEN 'Carnarvon Tigers' AND 'Mozzarella di Giovanni'
ORDER BY ProductName;
6.BETWEEN Dates Example
SELECT * FROM Orders
WHERE OrderDate BETWEEN #01/07/1996# AND #31/07/1996#;
and
SELECT * FROM Orders
WHERE OrderDate BETWEEN '1996-07-01' AND '1996-07-31';
Note: the format of date
10 SQL Aliases
give a table, or a column in a table, a temporary name for the duration of the query.
仅在查询期间使用aliases,给予表格或者某列元素进行暂时的别名
Aliases can be useful when:
-There are more than one table involved in a query
-Functions are used in the query
-Column names are big or not very readable
-Two or more columns are combined together
1.Alias Column Syntax
SELECT column_name AS alias_name
FROM table_name;
2.Alias Table Syntax
SELECT column_name(s)
FROM table_name AS alias_name;
3.Example
【1】
SELECT CustomerName AS Customer, ContactName AS [Contact Person]
FROM Customers;
Note: It requires double quotation marks or square brackets if the alias name contains spaces
如果别名名称包含空格,则需要双引号或方括号
【2】an alias named "Address" that combine four columns (Address, PostalCode, City and Country)
SELECT CustomerName, CONCAT(Address,', ',PostalCode,', ',City,', ',Country) AS Address
FROM Customers;
Reference:https://www.w3schools.com/sql/default.asp