"Oracle Statement"

Select Query Statements

View the table under the user name to unlock the HR User:

Enter with sys status

method One: [Email protected] ~]$ Sqlplus/as SYSDBA

Method two:sql> Conn/as sysdba

View Identities

Sql> Show User

USER is "SYS"

Sql> Select table_name from dba_tables where owner= ' HR ';

Sql> select * from Hr.employees;

sql> alter user HR account unlock identified by HR; Unlocking Users

Switch User hr

$ sqlplus hr/hr or sql> conn hr/hr

Sql> Show User

Sql> select * from tab;

Sql> desc Employees View Employees table structure

The format of the table to be queried for information display

The Select column * represents all columns of multiple columns: column 1,+ empty columns 2

From table

where row

Practice:

Check the table under the Scott user's name to unlock the Scott User:

Scott user /tiger password

1. Use Sqlplus's full-screen editing features:

$ echo $EDITOR

Sql> select * from Hr.employees;

Sql> Ed

sql>/execution

1. Base SELECT statement:

Sql> Select last_name,salary* (1+commission_pct) *12 from employees;

"Can give salary* (1+commission_pct) *12 individual name, format: formerly + space + new alias "

Last_Name salary* (1+commission_pct) *12

------------------------- ----------------------------

OConnell

Grant

Whalen

Hartstein

Fay

Mavris

Baer

Higgins

Gietz

King

Kochhar

Sql> SELECT * FROM Employees;

Sql> DESC Employees

Sql> Select Last_Name, SALARY, commission_pct from employees;

Sql> DESC Departments

Sql> Select department_id, department_name from departments;

Sql> SELECT distinct department_id from employees;

Sql> Select Last_Name, salary*12* (1+commission_pct) total_salary, department_id from employees;                                                                                  formerly known as        alias                                           

Sql> Select First_name| | ', ' | | Last_Name from Employees;

Sql> Select First_name| | ', ' | | Last_Name fullname from Employees;

Practice:

Output the following employee information:

Eleni (first_name) Zlotkey (last_name) EmployeeID is ... at department. Total salary are ...

For:

Sql> Select First_Name, last_name, ' EmployeeID is: ' | | employee_id| |

', at department: ' | | department_id| | ', total salary: ' | | Salary*12 from Employees;

Alana,walsh

EmployeeID Is:196,at Department:50,total salary:37200

Kevin,feeney

EmployeeID Is:197,at Department:50,total salary:36000

1. To construct a statement using hyphens:

Sql> Select table_name from User_tables;

Sql> Select ' Grant Select on hr. ' | | table_name| | "to Scott;" From User_tables;

Sql> Spool/home/oracle/grant.sql

Sql> Select ' Grant Select on hr. ' | | table_name| | "to Scott;" From User_tables;

Sql> Spool Off

$ vi/home/oracle/grant.sql removal of useless--line

Sql> @/home/oracle/grant.sql

1. Single-Quote processing:

sql> select ' I ' M teaher ' from dual;

ERROR:

Ora-01756:quoted string not properly terminated

sql> select ' I ' M teaher ' from dual;

Sql> Select Q ' {I ' m teaher} ' from dual; {},[],<>, () all can

Remove duplicate value:distinct

Sql>select deoatments_id from Employees;

Sql>select distinct deoatments_id from employees;

where and the Order by

Number conditions:

Sql> Select salary from employees where employee_id=100;

String case Sensitivity:

Sql> Select Last_Name, salary from employees where last_name= ' King ';

Sql> Select Last_Name, salary from employees where lower (last_name) = ' King ';

Sql> Select table_name, tablespace_name from User_tables where table_name= ' EMPLOYEES ';

Date is format sensitive: Alter session: only for current session

Sql> alter sysdate from dual;

Sql> alter session set nls_date_format= ' RR-MON-DD ';

Sql> alter sysdate from dual;

Sql> Select last_name from Employees where hire_date= ' 2006-05-23 '; Error

Sql> Select last_name from Employees where hire_date= ' 23-may-06 ';

Sql> Select last_name from Employees where hire_date=to_date (' 2006-05-23 ', ' yyyy-mm-dd ');

Explain the date format for easy unification

Interval query:

Sql> Select last_name from Employees where salary>=3400 and salary<=4000;

Sql> Select last_name from employees where salary between 3400 and 4000;

Sql> Select last_name from employees where salary between and department_id=50;

In

Sql> Select last_name from Employees where department_id=30 or department_id=40 or department_id=50;

Sql> Select last_name from Employees where department_id in (30, 40, 50);

Wildcard characters:

Sql> Select Last_Name, job_id from employees where job_id like '%\_man ' escape ' \ ';

"\: escape character, mask \ The special meaning of the symbol behind it, so that its symbol just means ordinary meaning"

Null as Condition:

Sql> Select last_name from Employees where commission_pct is null;

Sql> Select last_name from employees where commission_pct are not null;

And/or/not:

Sql> Select Last_Name, job_id, department_id, salary from employees where job_id= ' sa_rep ' or department_id=50 and Salar y>=8000;

Sql> Select Last_Name, job_id, department_id, salary from employees where (job_id= ' sa_rep ' or department_id=50) and Sal ary>=8000;

Sort:

Sql> Select Last_Name, salary from employees order by salary;

Sql> Select Last_Name, salary from Employees order by salary Desc;

Sql> Select Last_Name, salary from Employees order by Last_Name;

Sql> Select Last_Name, hire_date from Employees order by Hire_date;

Sql> Select Last_Name, salary, commission_pct from employees order by salary DESC, commission_pct desc;

Sql> Select Last_Name, salary*12* (1+commission_pct) from Employees order by 2;

Sql> Select Last_Name, salary*12* (1+commission_pct) Total_salary from Employees order by Total_salary;

or and and both appear, precedence and, in or

Practice:

Select an employee in Department 30

Sql> Select Last_Name, department_id from employees where department_id= ' 30 ';

Lists the names, numbers, and departments of all staff (clerk)

Sql> Select Last_Name, employee_id, department_id from employees;

Find employees with salaries greater than 5000

Sql> Select last_name from Employees where salary>5000;

Find employees with bonuses above 0.1

Sql> Select last_name from Employees where commission_pct>0.1;

Find out more about all the employees in department 50 and the manager in department 30

Sql> SELECT * FROM Employees where department_id= ' or department_id= ' "and job_id like ' man% ';

Find out the different job positions for the employees who receive the bonus show once for each position

Sql> SELECT distinct job_id from employees where commission_pct>0;

Find employees who do not receive bonuses or receive less than 5000 of their wages

Sql> Select last_name from Employees where commission_pct is null or salary<5000;

Show last_name employee names without ' R '

Sql> Select last_name from Employees where last_name does like ' r% ';

"Oracle Statement"