Oracle Basic Learning Simple Query and Limited Query

This article mainly introduces simple queries and limited queries in Oracle. I won’t say much below, let’s take a look together.

SQL:

1. DML (data manipulation language): mainly refers to the query and update operations of the database. The query operation is the most troublesome in the entire SQL syntax and the most commonly used in the written test. part.

2. DDL (data definition language): mainly refers to the creation of data objects (tables, users,). For example: creat. requires relevant design paradigms.

3. DCL (Data Control Language): Mainly performs permission operations (needs to be observed in combination with users), this part is responsible for the DBA.

Simple query:

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

select empno,ename,ename,job,sal from emp;

2, yes Use distinct to eliminate duplicate data rows:

select distinct job from emp;

3. The select clause can perform four arithmetic operations and can directly output constant content, but for strings Single quote numbers are written directly, and the date format follows the character format:

select empno,ename,(sal*15+(200+100)) income from emp;

4, || is responsible for outputting content connections. Such operations rarely appear directly in queries. :

select empno||ename from emp;

5. The where clause is generally written after the from clause, but it is executed immediately after the from clause. The where clause controls The operation of displaying data rows, while select controls data columns, and the select clause lags behind the execution of the where clause, so the aliases defined in the select clause cannot be used in where.

Limited query:

1, relational operator:

select * from emp where sal>1500;
 
select * from emp where ename ='SMITH'
 
select empno,ename,job from emp where job<>&#39;SALESMAN&#39;;

2, logical operator:

select * from emp where sal>1500 and sal<3000;
select * from emp where sal>2000 or job=&#39;CLERK&#39;;
select * from emp where not sal >=2000;

3, range query:

select * from emp where sal between 1500 and 2000;
select * from emp where hiredate between '01-1月-1981'and'31-12月-1981';

4, empty judgment (empty means no on the database Make sure, if you use null in the data column, it does not mean 0)

select * from emp where comm is not null;

5, IN operator (similar to between and and in gives the specified range ):

select * from emp where empno in (7369,7566,7788,9999);

Regarding the issue of not in and null:

When using not in for range judgment, if the range contains null, Then no results will be returned.

6, fuzzy query:

"-": match any one character;

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

Query the information of employees whose names start with the letter A:

select * from emp where ename like 'A%'

Query the information of employees whose names start with the letter A:

select * from emp where ename like '_A%';

Query employee information whose name is A at any position:

select * from emp where ename like '%A%';

Query sorting:

ASC (default): Sort in ascending order;

DESC: Sort in descending order;

Query all employee information, requiring salary from high to low:

select * from emp order by sal desc;

Query the number, name, annual salary of each employee, sorted by annual salary from low to high:

select empno ,ename,sal*12 income from emp order by income;

Execution order of statements: from - where -select - order by

Basic exercises:

1, select all employees in department 30:

select * from emp where deptno=30;

2, list the names, numbers, and department numbers of all clerks:

select ename,empno,deptno from emp
where job='CLERK';

3. Find 60% of employees whose commission is higher than salary:

select * from emp where comm>sal*0.6 ;

4. Find all managers in department 10 and department 20 All clerks in department:

select *
from emp
where (deptno=10 and job='MANAGER' )or(deptno=20 and job='CLERK'   );

5, find all managers in department 10 and all clerks in department 20 , and the information of all employees who are neither managers nor clerks but whose salary is higher than or equal to 2,000:

select *
from emp
where (deptno=10 and job='MANAGER')or(deptno=20 and
job='CLERK')or(job! ='MANAGER'and 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 employees who receive commissions:

select distinct job
 from emp
 where comm is not null;

7. Find employees who charge commissions or whose commissions are less than 100:

select distinct job
 from emp
where comm is null or comm<100;

8, display the names of employees without "R":

select *
from emp
where ename not like '%R%';

9, display the names of all employees whose name field contains A, displayed The results are sorted according to the basic salary from high to low. If the salaries are the same, they are sorted by the employment years from early to late. If the employment dates are the same, they are sorted by position:

select * from emp where ename like '%A%' order by sal desc,hiredate
asc,job;

Summary

The above is the entire content of this article. I hope the content of this article can bring some help to everyone's study or work. If you have any questions, you can leave a message to communicate.

For more articles related to Oracle basic learning simple query and limited query, please pay attention to the PHP Chinese website!