Oracle basic query statements and Instances

Oracle basic query statements and Instances
1. query all columns

Select * from table name;

2. query the table structure

Desc table name;

3. query specified Columns

Select ename, sal, job from table name;

4. View All Tables and fields in oracle

Get table:

Select table_name from user_tables; // The table of the current user

Select table_name from all_tables; // tables of all users

Select table_name from dba_tables; // includes the system table

Select table_name from dba_tables where owner = 'username'

5. where statements

Select * from table name where field> value;

Select * from indicates where to_char (field, 'yyyy-mm-dd')> '2017-1-1 '; to_char Conversion Function

Select * from indicates where to_char (field, 'yyyy') = '000000 ';

Select * from indicates where to_char (field, 'mm') = '4 ';

Show salary between 2000 and 2500

Select * from table name where field> = 2000 and field <= 2500;

Select * from indicates the where field between 2000 and 2500;

6. fuzzy query like

%: Represents any 0 to multiple characters; _: represents any single character

How to display the name and salary of an employee whose first letter is S

Select eaname, sal from table name where eaname like's % ';

How to display the name and salary of all employees whose third letter is O

Select eaname, sal from table name where eaname like '_ O % ';

7. Use the where statement in

How to display the employees with empno 123,345,678

1. select * from indicates where empno = 123 or empno = 345 or empno = 678;

Select * from indicates where empno in (123,345,678 );

2. is null Query

Select * from indicates that the where field name is null;

3. oracle logical operators

Query employees whose salaries are higher than 500 or whose positions are MSN, and satisfy their first letter of university J.

Select * from indicates where (sal> 500 or job = 'msn ') and (enname like 'J % ');