Oracle Basic Learning Simple Query and qualification query _oracle

This article mainly describes the Oracle in the simple query and limited query, the following words not to say, come and see together.

Sql:

1,DML (Data Manipulation language): mainly refers to the database query and update operation, query operation is the entire SQL grammar is the most troublesome is the most commonly used part of the written test.

2,DDL (data definition Language): mainly refers to the creation of data objects (tables, users,) such as: creat. Need the relevant design paradigm.

3,DCL (Data Control Language): The main permissions of the operation (need to be observed with the user), this part is the responsibility of the DBA.

Simple query:

1, use select the clause to control the data columns to be displayed:

 Select Empno,ename,ename,job,sal from EMP;

2, you can use distinct to eliminate duplicate rows of data display:

 SELECT distinct job from EMP;

3, the select clause can be arithmetic, you can directly output the constant content, but for strings using single quote numbers directly written, date format according to character formatting:

Select Empno,ename, (sal*15+ (200+100)) income from EMP;

4,| | Operations that are responsible for the output connection are rarely directly present in the query:

 Select Empno| | Ename from EMP;

5, the where clause is generally written from after the clause, but is followed from by the clause followed, where clause control the operation of the display data rows, and select control data columns, select clauses to lag behind the where execution of clauses, so in select The alias defined in the clause cannot be where used in.

To qualify a query:

1, relational operators:

 SELECT * from emp where sal>1500;

 SELECT * from emp where ename = ' SMITH '

 select Empno,ename,job from emp where job<> ' salesman ';

2, logical operators:

 SELECT * from emp where sal>1500 and sal<3000;
 SELECT * from emp where sal>2000 or job= ' clerk ';
 SELECT * from emp where not Sal >=2000;

3, the scope of the query:

 SELECT * from EMP where Sal between 1500 and;
 SELECT * from emp where hiredate between ' January-January -1981 ' and ' 3 January-December-1981 ';

4, NULL judgement (NULL on the database to indicate uncertainty, if the data column using null does not represent 0)

SELECT * FROM EMP where Comm was not null;

The 5,in operator (similar to in- between and given is the specified range):

 SELECT * from EMP where empno in (7369,7566,7788,9999);

About not in null the problem with:

When used not in for scoping, if the scope is there null , then no result will be returned.

6, Fuzzy query:

"-": match any one character;

"%": Match any 0, 1,, or multiple characters;

The query name is employee information that begins with the letter A:

 SELECT * from emp where ename like ' a% '

Query Name The second letter is employee information for a:

SELECT * from emp where ename like ' _a% ';

Query name any location is employee information for a:

 SELECT * from emp where ename like '%a% ';

Query sort:

ASC (default): in ascending order;

DESC: in descending order;

Query all employee information, and ask for high to low wages:

 SELECT * from emp order BY Sal Desc;

Check each employee's number, name, yearly salary, sorted by annual salary from low to High:

  Select Empno, ename,sal*12 income from EMP order by income;

The order in which statements are executed:-- from where select order by

Basic exercises:

1, select all employees in Department 30:

 SELECT * from EMP where deptno=30;

2, list the name, number, and department number of all Clerks (clerk):

Select Ename,empno,deptno from emp 
where job= ' clerk ';

3, find 60% of the employees with a higher commission than the salary:

 SELECT * from EMP where comm>sal*0.6;

4, find all the Managers (manager) in department 10 and all the clerks in department 20 (clerk):

 SELECT * FROM 
 EMP 
 where (deptno=10 and job= ' MANAGER ') or (deptno=20 and job= ' clerk '   );

5, find all managers in department 10, all clerks in Department 20 (clerk), and all employee information that is neither a manager nor a clerk but has a salary above equal to 2000:

 SELECT * FROM
 EMP
 where (deptno=10 and job= ' manager ') or (deptno=20 and
 job= ' clerk ') or (job! = ' manager ') or job!= ' clerk ' and sal>=2000);

  SELECT * FROM
  EMP
  where (deptno=10 and job= ' MANAGER ') or (deptno=20 and job= ' clerk ') or (Job not
  in ' clerk ', ' MANAGER ') and sal>=2000);

6, find out the different jobs of the employees who charge the Commission:

Select DISTINCT job from
 EMP
 where Comm was not null;

7, find a commission or a charge of less than 100 of the employees:

  Select DISTINCT job from
  EMP
 where comm is null or comm<100;

8, showing the name of the employee without "R":

 SELECT * FROM
 EMP
 where ename isn't like '%r% ';

9, show the name of the first name paragraph contains a of all the employee names, the results shown in accordance with the basic wages from high to low, if the same salary, according to the length of employment from early to late, if the date of employment is the same as the position of rank:

 SELECT * from emp where ename like '%a% ' ORDER by Sal Desc,hiredate
 Asc,job;

Summarize

The above is the entire content of this article, I hope the content of this article for everyone's study or work can bring certain help, if you have questions you can message exchange.