Oracle Simple to use

Connection mode

Oracle Simple to use

EMP Data Sheet

1.sqlplus command

1) Scott User login: Sqlplus scott/tiger
Querying the entire contents of an EMP table
Select * from EMP;
Problem:
(1) The display format of the table is confusing
(2) All tables have a header row
Sets the length of data displayed per row: Set Linesize 300;
Set the number of rows per display: Set pagesize 30;

Then execute SELECT * from EMP;

Command format: Col job for A8; Column Space Setting 8

Col job for A8;
SELECT * from EMP;

2) Call Notepad:
Use the "Ed" command to start a native Notepad program (for example, Notepad,vim)

ed Hello; (Write a statement in an open hello.sql and then save it in the C:\Users\Administrator\hello.sql)
@hello; (Execute @ file name)
You can also invoke the file on the disk with the @ designation, and if the suffix is also "*.sql", you may not have to write the suffix.

@d:aa; (execute aa.sql file in D disk)
Ed D:aa; (Opens the file)

Ed a.txt (Open Notepad, write well)
@a.txt (execution to add suffix)

3) display user Show Users
Switch User: Conn username/password
Cut to sys user Conn Sys/change_on_install as Sysdba

     Select  *  from  emp；查询不出  因为该表属于scott用户 为：emp.scott     用  select  *  from emp.soctt   Show  user   Conn sh

4) Call native program
Do not use User login: Sqlplus/nolog
In Sqlplus, consider that a user might use system commands, so use a host command,
Copy file: Copy original file target location file
Copy D:aa.sql C:1.sql; (not copied in user, copy in Nolog)

Sqlplus Scott/tiger
Host copy d:aa.sql D:bb.sql; (copy with host in Sqlplus)

2. Simple query

1) [Projection operation of data]---refers to the data column that the query needs. Employee number, name, salary, position
Select empno,ename,sal,job from EMP;
In addition to the basic query columns, arithmetic is supported in simple queries.
Identify the number, name, and calculate the annual salary as follows:
Select empno,ename,sal fromEMP;
Select empno,ename,salincome from EMP;
Select empno employee number, ename name, sal*12 annual salary from EMP;

2) connection query--description
Select Empno | | Ename from EMP;
You can optimize the connection by using the text description in the middle, "No.: XXX"
Normal numbers: Direct write characters: plus single quotes
Select ename| | 1 from EMP;
Select empno| | ' Hello ' emp;

Select ' number: ' | | empno| | ' Name: ' | | Ename from EMP;

3) Simple query-Eliminate duplicate content:
Select job from EMP;
Select distinc job from EMP; (eliminate the same position)
Select distinc ename,job from EMP, (both columns have all the data present, and in this distinc the name and position are not duplicated at the same time)

3 Scott user table four sheets

1) Department table: Dept
DESC Dept;

Select * FROM Dept;

2) Employee Form EMP (multiple employees in a department)

Select * from EMP;
3) Wage level number (Salgrade)

4) Salary scale (bonus)

The table is empty, no data.

4 SQL qualified query (and OR)
Query wages greater than 1200:select * from EMP where Sal >1200;

Salary greater than 1200 total: selelct count (*) from EMP where Sal >1200;

Wages less than 3000 and not clerks: Select * from emp where job <> ' clerk ' and Sal <3000;

Betwneen. and Range
Payroll at 1500 to 3000 employees
Select from emp where Sal >1500 and sal<3000;
Select from EMP where Sal between and 3000;

Inquire about employment of employees for 1981 years:
Select * from EMP where hiredata between ' January-January -1981 ' End ' 3 January-December-1981 ';

Query all employee information (Comm field commissions) for all commissions, the query is not empty

Select * from EMP where comm are NOT null;

4 SQL qualified query (and OR)
Query wages greater than 1200:select * from EMP where Sal >1200;

Salary greater than 1200 total: selelct count (*) from EMP where Sal >1200;

Wages less than 3000 and not clerks: Select * from emp where job <> ' clerk ' and Sal <3000;

Betwneen. and Range
Payroll at 1500 to 3000 employees
Select from emp where Sal >1500 and sal<3000;
Select from EMP where Sal between and 3000;

Inquire about employment of employees for 1981 years:
Select * from EMP where hiredata between ' January-January -1981 ' End ' 3 January-December-1981 ';

Query all employee information (Comm field commissions) for all commissions, the query is not empty

Select * from EMP where comm are NOT null;

In operator
Query Employee number 7369,7566,7788 employee information
Select * from EMP where empno in (7369,7566,7788);

Query employee number is not 7369,7566,7788 employee information
Select from EMP where empno not in (7369,7566,7788);
Select from EMP where empno not in (7369,7566,7788,null);

Fuzzy query like
Query all employees whose names start with a letter A:
Select * from EMP where ename like ' A% '; The first letter is an A

Select * from EMP where ename like ' _a% '; The second letter is an A

Select * from EMP where ename like '%a% '; There's A.

If the like query does not set a keyword to indicate that the query is all
Select from emp where ename like ' percent ' of the name whatever it is
Select count () from EMP where ename like '% '

Oracle Simple to use