Oracle Database Single Table query

Then the previous share, today mainly to share is about the data of single-table query, single-table query is very basic, but also very important, but any beginner must master the posture, single-table query is to operate on a single table, query the data we want. The contents of a single-table query are also more numerous, such as single-table queries combined with comparison operators, logical operators, and in-not, between...and, fuzzy lookups, sort lookups, de-lookup, NULL handling, any, and all. Then take a look at the use of single-table query!

We use Oracle's own user Scrott user, who is locked in the database and we need to unlock it. The unlocked SQL statement is:

alter user Scott account unlock identified by test1; --scott is locked out and set the login password for the Scott user test1 password can be set freely

After unlocking, you can use the table below Scott. Let's take a look at what tables are available under Scott users?

Let's first use the SQL statement to query the EMP sheet.

Select  from EMP;

The query results are as follows:

1. Combine comparison operators with

Tapping notes: > Greater than < less than >= is equal to <= less than equals = = Not equal to <> not equal to

--find employees with more than 3000 paySelect* fromEmpwhereSal> the;--find employees with a salary greater than 3000 and a job that is ClarkSelect* fromEmpwhereSal> theor job='Clerk';--find employees who pay more than 1500 or do not pay equal to 7566Select* fromEmpwhereSal> theor Sal! =7566;--query employees with wages less than 1600Select* fromEmpwheresal<= the;

Example: querying an employee with a wage less than or equal to 1600

Select  from where sal<=;

Query Result:

2. Combine logical operators with and OR

Tap notes: And priority is greater than or and (the two conditions of the link are met) or (the two conditions of the link have one satisfied)

Look at the SQL using or to query first

Select  from EMP; -- query an EMP table with an employee with a salary greater than 3000 or a number greater than 6000 select from emp  where sal>  or  empno> and empno<6000;

Query Result:

Looking at SQL with and query

-- Query the EMP table salary greater than 3000 and number greater than 6000 for employees Select from emp  where  sal> and empno>6000;  

The query results are as follows:

3. Combine in not with in

In what's inside what isn't in is not what what inside

 the  1400 Employee Information Select  from where inch (1400,N/a);

Query Result:

instance of not in:

 the  - of employee Information Select  from where inch (a);

Query Result:

4. Combining Between...and ... Use

Between ... and what's going on between

--Querythe employee information for payroll at 1500-3000 Selectfromwhere;

Query Result:

In an example:

--Query employee information for payroll at 1500-3000 and position is Salesaman employee Selectfromwhere3000 and job='salesman';

Query Result:

5. Fuzzy Search

% represents a wildcard character. That is, you can match any character, _ means match one character.

--Query the name of the EMP table containing employee information for a selectfromwhere'%a%';

Query Result:

Continue to an example:

- -Query the employee information in the MGR number that begins with 7 with a 8-terminated character selectfromwhere'7__8  ';

Query Result:

6. Sort Find

The order by is sorted by what, the default is ascending, or you can use ASC descending is desc.

-- Sort by sal default ascending select from emp order by Sal;

View results:

Sort by Sal, default ascending

--Sort Descending by Sal sort Select from emp order by Sal Desc;

View results:

7. Go to re-search

Using distinct to remove duplicate values

-- Remove the salesman from the job to repeat the select from EMP;

View results:

Remove the salesman repeatedly and keep only one. To remove duplicate values in accordance with ENAME:

-- Remove the ename duplicate value Select from EMP;

View results:

Remove duplicate ename value success, remove is not to show the meaning of duplicate name. Don't make it too big to understand.

An example of a de-weight failure is shown:

-- the job and the ename to go heavy, fail, can only remove a column name select from EMP;

View results:

8. Null value Processing

Is null empty value

-- Query MGR for employee information with a value of NULL selectfromwhereisnull;

Query Result:

is not null value

-- query Sal is NOT NULL employee information Selectfromwhereisnull;

View results:

NOTE: The query null value is NULL instead of = NULL

9. Combining the use of any and all

--all means that all conditions are fulfilled: greater than the maximum value, all

   --All--  an any that satisfies any one of the Select from where Sal>=all (1500 ,+,+); -- greater than the maximum value to satisfy all values selectfromwhere sal>=;  --Equal to >=all (,+,+)

Query Result:

--any means satisfying any one of the routines: greater than the minimum value, which satisfies all

Select  from where Sal>=any (on theother); --greater than the minimum value, which satisfies all

Equivalent to:

Select  from where Sal>=any (on theother); -- greater than the minimum value, satisfies all selectfromwhere sal>=;  --equivalent to >=

Query Result:

About the single-table query, here we are finished, the next one to share about the date of operation;

Oracle Database Single Table query