Some simple query syntax

Computed columns: The specified fields of some records in the table are taken out for operation.

Select *  fromEMP; --* indicates all the    --from EMP indicates a query from an EMP tableSelectEmpno,ename fromEMP;SelectEname,sal fromEMP;SelectEname,sal* A  asAnnual salary fromEMP; --As can omit record "annual salary" do not write ' annual salary ' also don't write annual salarySelectEname,sal* A  as"Annual salary", Sal "monthly salary", job fromEMP;Select 888  fromEMP; --OK    --The number of rows in the output is the number of lines of EMP, with only one field per line and a value of 888Select 5;--OK            --Not recommended

Distinct is used to filter duplicate records before the first field is added.

SelectDeptno fromEmp--14 Row Records are not 3 rowsSelect distinctDeptno fromEmp--DISTINCT DEPTNO will filter the repetitive deptnoSelect distinctComm fromEmp--distinct can also filter out duplicate nullSelect distinctComm,deptno fromEmp--Filter the combination of Comm and DeptnoSelectDeptnodistinctComm fromEmp--There is a conflict in the error logicSelect 10000  fromEmp--14 rows of records

Between: Delimit data within a range.

--find information about all employees who have wages between 1500 and 3000 (including 1500 and 3000)Select *  fromEMPwhereSal>=  the  andSal<=  the--equivalent toSelect *  fromEMPwhereSalbetween  the  and  the--find information about all employees with wages that are less than 1500 or greater than 3000 (including 1500 and 3000)Select *  fromEMPwhereSal<  the orSal>  the--equivalent toSelect *  fromEMPwhereSal not between  the  and  the

In: Specifies whether the value of the column belongs to one or more orphaned values?

Select *  fromEmpwhereSalinch( the, the, the)--equivalent toSelect *  fromEMPwhereSal=  the orSal=  the orSal=  theSelect *  fromEmpwhereSal not inch( the, the, the)--The Sal is neither 1500 nor 3000, nor is it a 5000 record output--equivalent toSelect *  fromEMPwhereSal<>  the  andSal<>  the  andSal<>  the        --There are two types of representations in the database:! = <> The second type is recommended        --to or take counter is and to and take against IS or

Top: A number of previous records.

Select *  from EMP; Select Top 5 *  from EMP;  -- Check out the top 5 records Select Top  the percent *  from -- the output is three, not two (rounding up)

Null:

IsNull function: isnull (field A, value B), returns the value B if the specified field A is null. Otherwise, the value of field A is returned.

Select *  fromEMP--output bonus information for employees who are flying emptySelect *  fromEmpwhereComm<> NULL;--output is empty errorSelect *  fromEmpwhereComm!= NULL;--output is empty errorSelect *  fromEmpwhereComm= NULL;--output is empty error    --NULL cannot participate <>! = = Operation--NULL can participate in is isn't, isSelect *  fromEmpwhereComm is NULL;--information of employees who output bonuses as emptySelect *  fromEmpwhereComm is  not NULL;--output bonuses are not empty information for employees--any type of data is allowed to be nullCreate TableT1 (namenvarchar( -), CNTint, Riqidatetime );Insert  intoT1Values(NULL,NULL,NULL );Select *  fromT1; --output Each employee's name annual salary (including bonus)SelectEname,sal* A +Comm asAnnual salary fromEMP;--This procedure proves that NULL cannot participate in any data operation, otherwise the result is always empty--output Each employee's name annual salary (including bonus)SelectEname,sal* A + IsNull(Comm,0) asAnnual salary fromEMP;--IsNull (comm,0) returns zero if Comm is null, otherwise returns a specific value.

ORDER BY: Sort by a field.

If you do not specify a sort criterion, the default is ascending, ascending with ASC, and default to no write.

The sort criteria that you specify for one field do not affect the other field. (It is strongly recommended that you set the sorting criteria for each field

--ASC is the ascending meaning default can not write Desc is descendingSelect *  fromEmpOrder  bySal--The default is to sort in ascending orderSelect *  fromEmpOrder  byDeptno,sal;--sort in ascending order of Deptno first, if Deptno is the same, then sort in Sal ascending orderSelect *  fromEmpOrder  byDeptnodesc, Sal; --sort in descending order of Deptno first, if Deptno is the same, then sort by Sal Ascending    --Remember that Sal is ascending, not descending .    --ORDER by a desc,b,c,d des only has an effect on a and does not affect the back b.c.dSelect *  fromEmpOrder  byDeptno,saldesc    --question: Does DESC have an impact on DEPTNO?     --Answer: No    --follow Deptno Ascending, if Deptno is the same, then by Sal descending

Note: This article refers to the Hao bin of the teacher's SQL tutorial, but also added some of their own understanding of SQL, there is a wrong place to write the hope that we can point out.

Some simple query syntax