[SQL] SQL Basics Grooming (ii)-Query basics

SELECT Statement Basics

"Bo Master" anti-bone Aberdeen "original" http://www.cnblogs.com/liqingwen/p/5904824.html

Order

Directory

First, SELECT statement basis

1. Query the specified column: SELECT keyword

-- Syntax: -- SELECT < column name;     --Want the name of the query column - -from< table name >            --Specify the table to pick the data

-- take 3 columns from the Shohin SELECT shohin_id, Shohin_mei, Hanbai_tanka--The order of the columns can be arbitrarily specified, separated by commas (","), and the order of the query results is the same as in the SELECT clause  from Shohin;

2. Querying all columns of the table: asterisk (*)

-- Grammar -- SELECT *        --asterisk (*) represents all Columns --from< table name >;

SELECT *--you cannot set the order in which columns are displayed by using an asterisk (*)  from Shohin;

　　

3. Setting aliases for columns: as keyword

SELECT  as  as Name, Shiire_tanka Price  from Shohin;    -- You can also use the AS keyword

SELECT  as  as ' name ' ' Price '  from Shohin;    -- set Chinese aliases: Add double quotation marks (") or single quotation marks (')

4. Queries for constants

SELECT ' Product '  as Product,            - - ' products ': string constant    as Price ,                    --  38: Numeric constant     '2016-09-30' as' production date '        --  ' 2009-02-24 ': Date constant

5. Remove duplicate rows from the results: DISTINCT

Original

(1)

SELECT DISTINCT  from dbo.　　Shohin; -

Use DISTINCT to remove duplicate data from a Shohin_bunrui column

(2) DISTINCT processing of NULL types: rows with multiple null values are combined into one null data.

SELECT DISTINCT  from dbo. Shohin;

(3) Use DISTINCT before multiple columns

SELECT DISTINCT Shohin_bunrui, Torokubi  from dbo. Shohin

DISTINCT combines data from multiple columns to combine duplicate data into one.

Note The DISTINCT keyword can only be used before the first column name.

6. Filtering records: WHERE

The WHERE clause can specify criteria such as "The value of a column is equal to this string" or "the value of a column is greater than this number" to find records that meet only that condition.

-- Syntax: -- SELECT    < column name; -- from      < table name >--WHERE     < conditional expression >;

SELECT shohin_id, Shohin_mei, Shohin_bunrui  from dbo.　　ShohinWHERE=' clothes '; --Shohin_bunrui = ' clothes ': for conditional expressions

After selecting rows, output columns

Memo WHERE clause: First the clause is queried for a record that matches the specified criteria, and then the column specified by the SELECT statement is selected.

Note the writing format of the SQL clause is fixed and cannot be changed arbitrarily. If the WHERE clause must be immediately following the FROM clause.

7. Wording of the annotation

Comments have no effect on the execution of SQL.

--   single -line comment/ *     Multiline Comment * /

Second, arithmetic operators and comparison operation methods

1. Arithmetic operators

SELECT * 2  as ' hanbai_tanka_x2 '  from dbo. Shohin;

Twice times the price of the commodity

Four arithmetic operators

Meaning	Operator
Addition	+
Subtraction	-
Multiplication	*
Division	/

Parentheses ("(") ") can increase the precedence of an expression.

2. Need to be aware of NULL

SELECT 5 + NULL Ten - NULL 1 * NULL 4 / NULL NULL / 9;

Remarks all calculations that contain NULL, the result is definitely null.

3. Comparison operators

Comparison operators

Operator	Meaning
=	Equal
<>	Range
>=	Greater than or equal
>	Greater than
<=	Less than or equal
<	Less than

--Example 1:
SELECT  Shohin_mei, Shohin_bunrui from dbo. ShohinWHERE=;

Select records for Hanbai_tanka column 500

--Example 2
SELECT  Shohin_mei, Shohin_bunrui from dbo. ShohinWHERE<>;

--Example 3
SELECT  Shohin_mei, Shohin_bunrui from dbo. ShohinWHERE!=;

Select a record with a value of Hanbai_tanka column other than 500

--Example 4
SELECT* from dbo. ShohinWHERE->=;

　　

3. Considerations when using a string with a non-equal sign

--DDL: Creating TablesCREATE TABLEChars (CHRCHAR(3) not NULL,PRIMARY KEY(CHR));--DML: Inserting dataINSERT  intoCharsVALUES('1');INSERT  intoCharsVALUES('2');INSERT  intoCharsVALUES('3');INSERT  intoCharsVALUES('Ten');INSERT  intoCharsVALUES(' One');INSERT  intoCharsVALUES('222');

Create a table

Original

　　

-- Example: Select a SELECT statement with data greater than ' 2 ' SELECT *  from dbo. CharsWHERE>'2';

"Note" CHR is a string type and is not the same as a number when comparing the size of the data of a string type.

　　

4. Cannot use comparison operator for NULL

--Example 1:SELECTShohin_mei, Shiire_tanka fromdbo. ShohinWHEREShiire_tanka= NULL;--the wrong SELECT statement--Example 2SELECTShohin_mei, Shiire_tanka fromdbo. ShohinWHEREShiire_tanka is NULL;--Select a record for NULL--Example 3SELECTShohin_mei, Shiire_tanka fromdbo. ShohinWHEREShiire_tanka is  not NULL;--Select a record that is not NULL

"Note" When you want to select a null record, use is NULL when you want to select records that are NOT NULL, using is isn't null.

Third, logical operators

1.NOT operator: Take the inverse

--Example:
SELECT* from dbo.　　ShohinWHEREnot>= 1000; --Equivalent to Hanbai_tanka < 1000

Take Hanbai_tanka columns of no more than 1000 records (Hanbai_tanka < 1000)

2.AND operators and OR operators

And operator: Also, the entire query condition is established when the query conditions on both sides are true.

OR operator: Query conditions on both sides even if only one is established, the entire query condition is set up.

--Example
SELECT  Shohin_mei, Shiire_tanka from dbo. ShohinWHERE=' kitchen utensils 'and    >=  ;

SELECT Shohin_mei, Shiire_tanka  from dbo. ShohinWHERE=' kitchen utensils '    OR>=  ;

"The Venturi Chart"

"Remarks" When multiple query criteria are combined, you need to use the AND operator or the OR operator.

2. Hardening with Brackets

--Example 1
SELECT  Shohin_mei,    Shohin_bunrui,    torokubifrom  dbo. ShohinWHERE=' office supplies 'and    ='  2009-09-11'    OR='2009-09-20' ;

-- Example 2 SELECT Shohin_mei,    Shohin_bunrui,    torokubifrom  dbo. ShohinWHERE=' office supplies 'and    = ' 2009-09-11 '    OR = ' 2009-09-20 ');

Notes and operations take precedence over or operations, and parentheses are used when you want to prioritize or operations.

[SQL] SQL Basics Grooming (ii)-Query basics