Oracle_sql (1) Basic query

Source: Internet
Author: User

Installation and uninstallation of 1.oracle

Installation of 2.pl/sql Developer

3. Log in to PL/SQL Developer

4.SCOTT user's introduction to the following table

5. Basic Query Statements
Query all information about an employee:
SELECT * from EMP;
* Indicates all columns
Query statement syntax:
Select *| column name,... from table name;

6. Returns the query statement for the specified column
Query employee's number, name, salary
Select Empno,ename,sal from EMP;
Use between multiple columns, separating

7. Remove Duplicate rows
Find All jobs:
Select Job from EMP;
SELECT distinct job from EMP;
SELECT distinct Job,deptno from EMP;
Distinct is best when only one column is followed,
When a distinct is followed by more than one column, it is the combination of multiple column values before it is re-weighed.

8. Conditional query (query for rows that meet certain criteria)
Query employee information for wages greater than 1500, return employee number, employee name, salary
Select Empno,ename,sal from emp where sal>1500;
Conditional query Syntax:
Select *| column name from table name where condition;

9. Comparison operators
> Greater than
< less than
= equals
>= greater than or equal to
<= less than or equal to
! = or <> not equal to

10.IS null and is NOT NULL
Find employees who can get a monthly bonus
Select Empno,ename,comm from EMP where comm are NOT null;
Inquire about employees without bonuses
Select Empno,ename,comm from emp where comm is null;
Null in the database indicates unknown, and unknown value is the result of the operation or unknown.
Select Empno,ename,sal,comm,sal+comm from EMP;
The SQL statement supports subtraction arithmetic, with the following operators: +,-, *,/

11. Multi-criteria queries (with and, or or, not with multiple criteria)
Query for employees with a salary greater than 1500 and less than 3000
Select Empno,ename,sal,comm from emp
where sal>1500 and sal<3000;
Query for employees with a salary of less than 1500, or greater than 3000
Select Empno,ename,sal,comm from emp
where sal<1500 or sal>3000;
Query employees with wages not less than 1500
Select Empno,ename,sal,comm from emp
Where not (sal<1500);

12.>= and <= Special Between...and ...
Query for employees with a salary greater than or equal to 1500 and less than or equal to 3000
Select Empno,ename,sal,comm from emp where sal>=1500 and sal<=3000;
Select Empno,ename,sal,comm from emp where Sal between and 3000;

13. String comparisons
Find out all about the ' SMITH ' employees
SELECT * from emp where ename= ' SMITH ';
Strings are strictly case-sensitive.

14. Multiple value-taking queries
Find out all about the ' SMITH ' and ' SCOTT ' employees
SELECT * from EMP where ename in (' SMITH ', ' SCOTT ');

15. Queries that are not within multiple values
Query all information except ' SMITH ' and ' SCOTT ' for the rest of the employees
SELECT * from emp where ename isn't in (' SMITH ', ' SCOTT ');

16. Fuzzy Query
Fuzzy query keyword like,
Fuzzy query Match _ and%, where _ matches one character,% matches 0 or more characters,
Fuzzy query classification: Right blur, left Blur, full blur.
Query all employee information with name beginning with ' S '
SELECT * from emp where ename like ' s% ';
Query all employee information with name ending with ' S '
SELECT * from emp where ename like '%s ';
Query for all employee information whose name contains ' S '
SELECT * from emp where ename like '%s% ';
Query employee information for the second character in the employee's name, "M":
Select Empno,ename,sal from emp where ename like ' _m% ';
Query payroll contains 5 of employee information
Select Empno,ename,sal from emp where Sal like '%5% ';

17. Sort the results
Sort syntax: ORDER by column name 1 asc/desc, column name 2 Asc/desc ...
ASC Ascending (default ascending), desc descending
Query employee information and output in descending order of wages:
Select Empno,ename,sal from emp order by Sal Desc;
Query employee information with a salary greater than 1500 and output in ascending order of salary:
Select Empno,ename,sal from EMP where sal>1500 order by Sal;
Query employee information for department numbers 20 and 30, and export by department ascending, payroll descending:
Select Empno,ename,deptno,sal from emp
where Deptno in (20,30) Order by deptno,sal Desc;
Select Empno,ename,sal from emp order by Sal Desc,empno;


1.cmd-----Ping IP address to see network problems, see if you can ping
2.cmd-----tnsping IP address (or the server's instance name SID) if the "TNS-12535: Operation timeout" is reported, the server-side firewall may not be turned off
3.cmd----Netstat-na See if Port 1521 is off, set 1521 port as exception if you turn off firewall settings in Windows XP
4.cmd----LSNRCTL Status Lsnrctl is an abbreviation for the Listener-control listener to view the status of the listener

Oracle_sql (1) Basic query

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.