The eighth section of the Oracle Learning Note SQL Statement (Student Guide for Development Course 051)

Development Course do practice Student Guide 051 les01 ppt

Oracle often connects two tables, called a third paradigm, such as something with an ID character

The following is the command for the SQL statement

SELECT * from departments; Department table

SELECT * FROM Employees; Employee table

Select Employee_id,rowid,rownum from Employees

where employee_id>=200; 64 binary internal Operation rownum Nature query the first few lines

Select Employee_id,rowid,rownum from Employees

where rownum<=5; View Top 5 rows

SELECT * FROM Employees e where e.department_id=80; Everyone in this department has a commission.

Select last_name,12*salary* (1+commission_pct) from employees; View Annual income

Select last_name,12*salary* (1+NVL (commission_pct,0)) from employees; View annual revenue, handle the display of empty rows

SELECT * FROM employees where manager_id is null; About the handling of empty

Select last_name,12*salary* (1+NVL (commission_pct,0)) as anaualsal from employees; Column alias processing

Select last_name,12*salary* (1+NVL (commission_pct,0)) "Anaualsal" from employees; double quotation marks if you want to distinguish case

Select Last_Name | | ' XXXX ' | | job_id from Employees; Connected up to show

Select Department_name | | Q ' [Department's manager ID:] ' | |    manager_id as "Department and Manager" from departments; Q ' #xxx # Yes, that's what you want back there.

Select DISTINCT department_id from Employees; There must be sorting in addition to weight (empty display, No. 178th Employee No Department)

CREATE TABLE T05101_distinct (a varchar2 (ten), B varchar2 (10));

INSERT into t05101_distinct values (' A ', ' B ');

SELECT * from T05101_distinct;

Select Distinct A, b from t05101_distinct;

INSERT into t05101_distinct values (' A ', ' B1 '); Except the weight is to remove the whole line

Select Distinct A, b from t05101_distinct;

SELECT * FROM User_tab_cols TC where TC. Table_name= ' LOCATIONS '; View all columns for all tables within a user scope

Method 2:sqlplus Command

Sqlplus/nolog

Conn Hr/oracle_4u

Describe Locations view table structure

Tip: Select SQL to view execution plan by F5

LES02 ppt

There are 6 types of +1 parameters for Oracle variables, two types of host

SELECT * FROM employees where rownum=1;

Alter session set nls_date_format= ' YYYY-MM-DD '; Modify current session default format

Select last_name from Employees where hire_date = to_date (' 17-jun-07 ', ' dd-mon-rr ');

SELECT * FROM Employees e where e.employee_id in (select manager_id from employees); See how many people are bosses.

SELECT * FROM Employees e where e.employee_id not in (select manager_id from Employees); This example is wrong. cannot be in because there is a null value inside or empty

SELECT * FROM Employees e where e.employee_id not in (select manager_id from Employees where manager_id are not null); Showing 89 persons

CREATE TABLE T05102_a (a VARCHAR2 (10));

INSERT into t05102_a values (' a ');

INSERT into t05102_a values (' A1 ');

INSERT into t05102_a values ('% ');

INSERT into t05102_a values ('_');

INSERT into t05102_a values (' _1 ');

SELECT * from T05102_a;

SELECT * from T05102_a where a like ' A% '; A.

SELECT * from T05102_a where a like ' a_ '; see what's A.

SELECT * from T05102_a where a like ' \%% ' escape ' \ '; View% number

SELECT * from T05102_a where a like ' \_% ' escape ' \ '; look at the underscore

INSERT into t05102_a values (""); Insert single quotation marks

INSERT into t05102_a values (Chr (39) | | 1); Man ASCII View-derived

CREATE TABLE T05102_b (a number,b number);

INSERT into t05102_b values (1,999);

INSERT into t05102_b values (1,0);

INSERT into t05102_b values (2,999);

INSERT into t05102_b values (2,0);

SELECT * from T05102_b order by a B;

SELECT * from T05102_b ORDER by a desc, b desc;

Select a "x", B "Y" from the T05102_b Order by "X" desc, "Y" desc; aliases, in order by the world does not matter whether the "X" or a can, but the normal statement of the SQL statement alias does not

Substitution variables

Select Employee_id,salary from Employees where employee_id=100;

Select Employee_id,salary from Employees where employee_id=&s_1; You can go to the pop-up window and choose your ID number

Select Last_name,salary from employees where last_name like ' &s_1% '; access to pop-up windows

Sqlplus/nolog

Select salary from employees where employee_id=&&s_2; ask two times is always this

Define's here by default.

Undefine s_2 Cancel

Select &&s2,salary from Employees where employee_id=&s_2;

Set verify off Configuration this will not be the old new, environment variable

This article is from the Oracle Personal Learning Notes blog, so be sure to keep this source http://wuchunqiang.blog.51cto.com/1022331/1852767

The eighth section of the Oracle Learning Note SQL Statement (Student Guide for Development Course 051)