"SQL. Basic build-section II (2/4)"

--Tips: Query Basics


--First, SELECT statement basis

--1. Query the specified column: SELECT keyword

--Syntax:
--select <, ...--the name of the column you want to query
--from < table name >--Specify a table for selecting data

--Take 3 columns from the Conbio
Select Conbio_id,conbio_name,conbio_varieties--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 Conbio;

------------------

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

--Grammar
--select *--asterisks (*) represent all columns
--from < table name >;

------------------

SELECT *
From Conbio;

--If you use an asterisk (*) for notes, you cannot set the order in which columns are displayed.

--------------------------------------

--3. Setting aliases for columns: as keyword

SELECT conbio_id as ID, conbio_name as name, Conbio_price1 Price1--without the AS keyword can also
From Conbio;

------------------

Select conbio_id as "number", Conbio_name as ' name ', Conbio_price1 ' price 1 '
From Conbio;

--Set Chinese aliases: Add double quotation marks (") or single quotation marks (')

--------------------------------------

--4. Queries for constants

SELECT ' product ' as product,--' product ': string constant
As Price,--38: numeric constant
' 2016-09-30 ' as ' production date '--' 2016-09-30 ': Date constant

--The "Memo" string and the date use single quotation marks (').

------------------

SELECT * from-the table with the query table named Conbio.
Conbio;

--------------------------------------

--5. Remove duplicate rows from the result: DISTINCT

--(1)
SELECT DISTINCT conbio_varieties from dbo.        Conbio; --Removing duplicates

------------------

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

SELECT DISTINCT Conbio_price2 from Dbo.conbio;

------------------

--(3) Use DISTINCT before multiple columns

SELECT DISTINCT Conbio_price1, conbio_date
FROM dbo. Conbio

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

--the "note" 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 < list of names;
--from < table name >
--where < conditional expressions >;

------------------

Select Conbio_id,conbio_name,conbio_varieties
FROM dbo. Conbio
where conbio_varieties = ' clothes '; --conbio_varieties= ' clothes '; for conditional expressions.

--"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. How to comment

--The comment has no effect on SQL execution.

--Single line comment

/*
Multi-line comments
*/

--------------------------------------

--two, arithmetic operators and comparison operators

--1. Arithmetic operators

Select Conbio_name, Conbio_price1, Conbio_price2 * 2 as ' conbio_price1_x2 '--Parentheses ("(") ") can increase the precedence of an expression.
FROM dbo. Conbio;

--------------------------------------

--2. Need to be aware of NULL

SELECT 5 + NULL, 10-NULL, 1 * null, 4/null, NULL/9;

--"Remarks" all calculations that contain NULL, the result must be null.

--------------------------------------

--3. Comparison operators

--The meaning of the comparison operator operator
--= Equal
--<> Range
-->= greater than or equal
--> Greater than
--<= less than or equal to
--< less than

--Example 1:

SELECT Conbio_name, Conbio_varieties
FROM dbo. Conbio
WHERE Conbio_price1 = 500;

------------------

--Example 2
SELECT Conbio_name, Conbio_varieties
FROM dbo. Conbio
WHERE Conbio_price1 <> 500;

------------------

--Example 3
SELECT Conbio_name, Conbio_varieties
FROM dbo. Conbio
WHERE Conbio_price1! = 500;

------------------

--Example 4
SELECT *
FROM dbo. Conbio
where Conbio_price1-conbio_price2 >=500;

--------------------------------------

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

--Example: Selecting a SELECT statement with data greater than ' 2 '

CREATE TABLE Chars--Creating tables with table name Chars
(
CHR varchar (20)--set column named CHR type varchar (20)
);
------------------

--Insert a value (custom value) into the table named chars table.

Insert into chars values (1)
Insert into chars values (10)
INSERT into Chars values (11)
INSERT into Chars values (2)
INSERT into Chars values (222)
INSERT into Chars values (3)

------------------

SELECT * from Chars; --Query a table named chars

------------------

SELECT *
FROM dbo. Chars
WHERE chr > ' 2 ';

--"note" CHR is a string type, which is different from the number when comparing the size of the data of the string type.

--------------------------------------

--4. Comparison operators cannot be used on NULL

--Example 1:
SELECT Conbio_name, Conbio_price2
FROM dbo. Conbio
WHERE conbio_price2 = NULL; --The wrong SELECT statement

--"note" When you want to select a null record, use is NULL when you want to select a record that isn't (not) "null", or NOT NULL.

--Example 2
SELECT Conbio_name, Conbio_price2
FROM dbo. Conbio
WHERE Conbio_price2 is NULL; --Select a null record (invalid null value)

--Example 3
SELECT Conbio_name, Conbio_price2
FROM dbo. Conbio
WHERE Conbio_price2 is not NULL; --Select a non-null record (not an invalid null value)

--------------------------------------

--Three, logical operators

--1.NOT operator: Take the inverse

--Example:
SELECT *
FROM dbo. Conbio
WHERE not Conbio_price1 >= 1000; --Equivalent to Hanbai_tanka < 1000

--------------------------------------

--2.and operators and OR operators

--AND operator: Also, the entire query condition is established when the query condition < both on both sides is set to >.

--OR Operator: Query conditions on both sides even if < has only one set >, the entire query condition is set up.

--Example
SELECT Conbio_name, Conbio_price2
FROM dbo. Conbio
WHERE conbio_varieties = ' kitchen utensils '
and Conbio_price1 >= 3000;

------------------

SELECT Conbio_name, Conbio_price2
FROM dbo. Conbio
WHERE conbio_varieties = ' kitchen utensils '
OR Conbio_price1 >= 3000;

--"Remarks" when multiple query conditions are combined, you need to use the AND operator or the OR operator.

--------------------------------------

--2. Hardening with parentheses

--Example 1
SELECT Conbio_name,
Conbio_varieties,
Conbio_date
FROM dbo. Conbio
WHERE conbio_varieties = ' office supplies '
and conbio_date = ' 2009-09-11 '
OR conbio_date = ' 2009-09-20 ';

------------------

--Example 2
SELECT Conbio_name,
Conbio_varieties,
Conbio_date
FROM dbo. Conbio
WHERE conbio_varieties = ' office supplies '
and (conbio_date = ' 2009-09-11 ')
or conbio_date = ' 2009-09-20 ');

--the "notes" and operation takes precedence over or operations, and you can use parentheses when you want to perform an OR operation preferentially.

--------------------------------------

--Welcome to Personal public Number: Zkcops

--2018/04/16 13:39
　
By: Zkcops writing (hope to be helpful to you, reprint annotated source!) ）
--------------------------------------

"SQL. Basic build-section II (2/4)"