Oracle_sql (1) Basic query

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