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.