Oracle Basic query Statements and examples

1. Query all Columns

SELECT * from table name;

2. Query table structure

DESC table name;

3. Query the specified column

Select Ename,sal,job from table name;

4. View all tables and fields in Oracle

Get table:

Select table_name from User_tables; The current user's table

Select table_name from All_tables; All users ' tables

Select table_name from Dba_tables; including system tables

Select table_name from dba_tables where owner= ' user name '

5. Where sentence

SELECT * from table name where field > value;

SELECT * from indicates where to_char (field, ' Yyyy-mm-dd ') > ' 1982-1-1 '; To_char conversion function

SELECT * from indicates where to_char (field, ' yyyy ') = ' 1980 ';

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

Show wages at 2000 to 2500 wages

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

SELECT * from indicates where field between and 2500;

6, Fuzzy query like

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

How to display the employee's name and salary for the first letter S

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

How to display all employee names and wages for the third letter O

Select Eaname, sal from table name where Eaname like ' __o% ';

7. Where statement uses in

How to display an employee with a empno of 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 empty value query

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

3. Oracle Logical operators

Check your salary above 500 or post for MSN employees, and also meet their initials University J

SELECT * from indicates where (sal>500 or job= ' MSN ') and (Enname like ' j% ');

Oracle Basic query Statements and examples