SQL query Basic syntax

1. Computed ColumnsSELECT * FROM EMP--* indicates that all--from EMP represents the query from the EMP table for select Empno,ename from EMP; Select Ename,sal*12 as "annual salary"From EMP--as can be omitted, remember "annual salary" do not write ' annual salary ', also do not write annual salary,Easy to transplantSelect Ename,sal*12 as "annual salary", Sal "monthly salary", Job from EMP select 5 from EMP;     --ok, output a constant value--the number of rows of output is the number of rows of the EMP table, each row has only one field, the value is 5select 5; --ok-Not recommended, no practical significance note: Aliases for fields in Oracle are not allowed in single quotes but SQL Server 2005 allows for compatibility, the best field aliases are enclosed in double quotes, do not use a single Quotes  2, distinct "not allowed to repeat"SelectDistincT deptno from EMP;         --distinct Deptno will filter out duplicate DEPTNO values select DISTINCT comm from EMP; --distinct can filter out duplicate null, or if there is more than one null, an output Selectdistinct Comm,deptnofrom EMP; --distinct the combination of Comm and Deptno, to find out 22 distinct records, select distinct Deptno,comm from emp--meaning: The combination of DEPTNO and comm are output select             DEPTNO,DISTINCT comm from EMP--error,deptno elements in multiple comm Select Deptno,distinct Comm from EMP; --error, there's a logical conflict.How to learn: Think more3.between "Within a range"--find information for all employees with wages between 1500 and 3000 (including 1500 to 3000) SELECT * from EMP where Sal between and 3000;--equivalent to select * From EMP where sal>=1500 and sal<=3000; --Find information for all employees with a salary of less than 1500 or greater than 3000 (including 1500 to 3000) SELECT * from emp where sal<1500 or sal>3000; SELECT * from emp where Sal is not between and 3000; 4.in "belongs to several isolated values"--outputs the elements of sal=1500 and sal=3000 select * from emp where Sal in (1500,3000);--equivalent to select * from EMP where sal=1500 or sal=3000--the element that Sal is not equal to 1500 and Sal is not equal to 3000 output SELECT * from emp where Sal not in(1500,3000);--equivalent to select * from EMP where sal!=1500 and Sal!=3000select * from EMP where Sal<>sal<>3000 and---the database does not equal two kinds of representations:!       =, <>, the second type is recommended-multiple or inverse yes and no and the inverse is or 5, top: "The first number of verses" can be used for paging select top 2 * from EMP;       --Filter out the first two rows of data select top 2 from EMP; --errorselect Top percent * from EMP; --If the percentage is not an integer then rounding up--the information output of the top 4 of the highest paid employees in the employee from 1500 to 3000 (including 1500 and 300) SelectTop4 * from EMPwhereSalbetween andORDER BY Sal--the execution process from which table->where->order sort->top-> All bytes--desc Descending, do not write the default is ascending (ASC) 6, NULL "No value, null value" ①, 0, and human null are not the same, null means null, no value, 0 is a definite value②, NULL cannot participate in the following operations:<>! =③, NULL can participate in the following operations: Is isn't is

-

-output bonus non-empty employee information-

SELECT * from emp where comm <> null;--Output is empty

select * from emp where Comm!=null; --Output is empty

select * from EMP where comm=null;--Output is empty error

-

-Summary: null does not participate <>! = = Operation

--null can participate in

the IS isn't is

a select * from EMP where comm is null;--output bonus is empty for employees ' information

SELECT * from emp Where comm is not null; --Information about employees who output bonuses that are not empty

④, any type of data is allowed to be null CREATE TABLE T1 (name nvarchar (), CNT int, Riq        I datetime) Insert T1 VALUES (null,null,null); SELECT * from T1; ⑤, any number with NULL the result of participating in a mathematical operation is always NULL

--Output each employee's name annual salary (including bonus) Comm hypothesis is one year's bonus

select Empno, ename,sal*12+comm "annual salary" from EMP;

-

This program proves that any number with null participation in mathematical operations is always null -the correct wording is: select Empno, Ename,sal*12+isnull (comm,0) "Annual salary" fro M EMP; --isnull (comm,0) returns zero if Comm is NULL, otherwise returns the value of Comm

7. Order BY "Sort by a field"                                Order By a                --a and B are ascending         ORDER by a, a, and Desc &nbs P      --a Ascending, descending         ORDER by a desc,b        --a Descending, b ascending   &nbsp ;     ORDER BY a DESC, b desc    --a Descending, B descending                 Word description                 If you do not specify a sorting criteria, the default is ascending, ascending with ASC, and default can not write         &N Bsp       Sorting criteria specified for one field does not affect another field          It is strongly recommended that you specify a standard   example of sorting for each field:

--asc is the meaning of ascending, default can not write, desc is descending select * from emp ORDER by SAL--default is ascending sort by the select * from emp order by deptno,sal;--follow DEP first  TNO sorted in ascending order, if Deptno same, then sort by sal Ascending select * from emp ORDER by DEPTNO desc,Sal; --Sort in descending order of deptno, if Deptno is the same, then sort by Sal ascending--Remember that Sal is ascending, not descending--order by a desc,b,c,d desc only affects a, and does not affect the subsequent B, C, d. SELECT * F ROM emp order by deptno,sal Desc; --Question: Does DESC have an impact on DEPTNO? ---The answer: No,--first press Deptno ascending, if Deptno is the same, then by Sal Descending 8, fuzzy query format :

The collection from table name of the Select field where the name of a field like matches the content

A matching condition usually contains a wildcard character

%

Represents any 0 or more characters
SELECT * from emp where ename like '%a% '; --ename as long as it contains the character a, the output
SELECT * from emp where ename like ' A% '; --ename, as long as the first letter is a, the output
SELECT * from emp where ename like '%A '; --ename output If the letter is a

_[This is an underscore is not a minus sign]

represents a single character

SELECT * from emp where ename like ' _a ';--ename as long as the second letter is a, the output

[A-f]

any single character in A to F, can only be any of a B C D E F

SELECT * from emp where ename like ' _[a-f]% ';
--Put the second letter of the ename in the data output of a or B or C or D or E or F

[A,f]

A or F

[^A-c]

not A, nor B, nor any single character of C
SELECT * from emp where ename like ' _[^a-f]% ';--put the second letter in ename not a nor B nor C nor D nor e nor F data output

Attention:
The matching criteria must be enclosed in single quotation marks and cannot be omitted or used in double quotation marks.

Wildcard characters

the use of wildcards as different characters

SELECT * from indicates where name like '%\%% ' escape '--the output containing the% in name
The select * from indicates the where name like '%m%% ' escape 'm'//Escape after the content indicates that the element after it is treated as an escape character

SQL query Basic syntax