oracle sql statement notes

2011-08-08  来源:本站原创  分类:Database  人气:214 

Left outer join examples:

select employee_id, last_name, salary, department_id, department_name

from employees left join departments using (department_id)

(Equivalent to two tables connected with department_id)

Right outer join example:

select employee_id, last_name, salary, department_id, department_name

from employees right join departments using (department_id)

(Equivalent to two tables connected with department_id)

Full outer join example:

select employee_id, last_name, salary, department_id, department_name

from employees full outer join departments using (department_id)

(Equivalent to two tables connected with department_id)

Subquery (Sub Query)

Issues into: how to look up any more than "Joe Smith" high wages of employee information

Subquery

Subqueries are executed once before the main query

Main results of the query using subqueries

Syntax:

select field list

from table

where the expression operator (select field list from table);

Using Subqueries Note:

Unknown value in the query is based on the use of a subquery should be considered;

Subquery must be enclosed in parentheses;

Recommended subquery on the right side of comparison operators to enhance readability.

Unless the Top-N (ie the top few) analysis, do not use in order by a subquery clause;

Use of single-row subquery single operator;

Use of multiple-row subqueries multi-line operator;

Single-row subquery

Single-row subquery returns only one row;

For single-row subquery can use the single record is not equal comparison operators ;(=,>,>=,<,<=,<>)

For example: select * from emp where sal> (select sal from emp where empno = 7566);

Subquery null / multi-value problem

If the subquery returns no rows, then the main query does not return any results;

Select * from emp where sal> (select sal from emp where empno = 8888); / / 8888 does not exist

If the subquery returns a single row result, compared with single-row subquery can be used in the main query to its corresponding single record than the operator; select * from emp where sal> (select sal from emp where empno = 7566);

If the subquery returns more choices results, compared with multiple-row subqueries, this time does not allow its comparison with single-line records.

select * from emp where sal> (select avg (sal) from emp group by deptno); / / illegal, can not do

Desc emploree / / query emploree the structure

Multiple-row subqueries

Multi-row subquery returns multiple rows, the subquery can only use multi-line multi-line record comparison (in, any, all)

is equal to the list in any

Any and sub-query returns a value to any more;

All and all values ​​returned by the subquery comparison;

For example:

Select * from emp where sal> any (select avg (sal) from emp group by deptno);

Select * from emp where sal> all (select avg (sal) from emp group by deptno);

Select * from emp where job in (select job from emp ename = 'MARTIN' or ename = 'SMITH');

TopN query

Commonly used in ORACLE subquery way to achieve Top N queries

For example: select * from (select * from emp order by sal desc) where rownum <= 5;

Rownum is the pseudo-column, must have been the result of the query can use rownum, direct use is invalid. For example:

Select * from employee where rownum <= 5 / / illegal, rownum there

Select rownum, a. * from (select * from emp order by sal desc) a; / / example of the proper use of rownum

Select * from

(Select rownum myno, a. * from (select * emp order by sal desc) a)

Where myno> = 5 and myno <= 10;

  1. Chapter DML and transaction control

A data manipulation language DML insert statements, update statements, delete statements, merge statement

2 database transaction control

The concept of transaction, the transaction commit and rollback, autocommit, save points and partial rollbacks.

Insert statement

1 You can insert a record, the default list of field names should be inserted for the new record set a new value for each field

You can also specify the assignment in the insert statement field list, only part of the field shows the setting values. The remaining field is Null.

You can insert statement using a subquery to achieve copy data between tables:

Insert into dept1 (id, name) select deptno, dname from dept;

Note: At this point do not have to give values ​​clause. Subquery in the list of values ​​should correspond with the insert clause field.

Update statement

Each can update multiple records, use the where clause limits the records to be updated, such as the default where clause, then update all the records in the table.

Update emp set sal = sal +99;

Update emp set sal = sal +99 where empno = 7599;

Update student2

Set age = nvl (age, o) +1;

Delete statement

Delete statement to delete multiple records each time

You can use the where clause, limit the records to be deleted

Delete from table

Delete from table where empno = 8888;

Merge statement

Merge statement is used for data consolidation, according to the conditions in the table to perform data modifications or inserts, if you want to insert the records in the target table already exists, then perform the update operation, otherwise the insert operation.

Usage example:

Create table test1 (eid number (10), name varchar);

Insert into test1 values ​​(1001, 'Joe Smith' 20-5 -70 months', 2300);

Insert into test1 values ​​(1002, 'John Doe' 16-5 -70 months', 2600);

Select * from test1;

Create table test2 (eid number (10), name varchar2 (20), birth date, salary number (8,2));

Select * from test2;

Merge into test2

Using test1

On (test1.eid = test2.eid)

When matched then

Update set name = test1.name, birth = test1.birth, salary = test1.salary

When not matched then

Insert (eid, name, birth, salary) values ​​(test1.eid, test1.name, test1.birth, test1, salary);

Select * from test2;

Transaction control

Affairs

Single logical unit of work composed of a series of operations are called transactions.

The transaction must meet the ACID properties:

Atomic (Atomicity), consistency (Consistency), isolation (Isolation), persistence (Durability)

Insert, update, delete, be sure to perform commit; operation. Or will not submit to the database.

Sql Plus autocommit

Sql Plus to execute the SQL statement can set whether to automatically submit a non-default auto-commit.

View settings show autocommit;

Results:

Autocommit OFF;

Autocommit IMMEDIATE

Change the settings:

Set autocommit on;

Set autocommit off;

Commit / rollback state

The current state of affairs changed data can be restored

DML operation in the current transaction results only for the current user (sessions) can be seen, the other user (session) do not see the data in the current state of affairs changes, until the end of the current transaction (ie the implementation of commit).

DML statements in the current transaction involved the row is locked, other users (sessions) can not modify operation.

Only you can view select.

State of affairs after the submission of data

Data changes permanent, can not be withdrawn

Previous state of permanent data loss, can not be recovered

All users (sessions) will see the results after operation

Record lock is released, other users can modify the operation of these

Transaction savepoints (savepoints) is cleared

Save Point

Point by saving the current transaction to create tag can fall back on a specified future mark (save point), to achieve partial rollback transaction.

Usage example:

Insert into dept values ​​(55, 'Adv', 'Beijing');

Insert into dept values ​​(56, 'Seev', 'Beijing');

Savepoint p1;

Insert into dept values ​​(57, 'Acc', 'Tianjin');

... ...

Select * from dept;

Rollback to p1;

Select * from dept;

Once the execution of the commit operation, then the savepoint automatically disappear.

Database Objects

Common database objects

Table: basic database objects stored data by rows (records) and columns (fields) form

Constraints: to perform data validation, a series of rules to ensure data integrity

View: Table shows the logic of the data

Index: According to the fields specified in the table set up in order to improve query performance.

Sequence: a group of a regular integer value

Synonyms: object alias

Naming

Must begin with a letter

Can contain letters, data ,_,$, and #

Under the same user can not duplicate names of objects

Can not use oracle reserved words

Create table

The table must be specified when creating a table name, field names, field types

Create table for DDL statements, irrevocable upon execution.

Create table scott.test (

Eid number (10),

Name varchar2 (20),

Hiredate date default sysdate,

Salary number (8,2) default 0

);

Data dictionary: Select table_name from user_tables;

Create a table using a subquery

When you create the table, while results of the subquery can be directly inserted into it:

New sub-query result table with a list of fields must match

New tables can be the default field list

Create table myemp (number, name, salary)

As select empno, ename, sal * 12 from emp;

Create table myemp2

As select empno, ename, sal * 12 annsal from emp;

Modify table structure

Use alter table statements can modify the table structure, including:

Add fields, change fields, delete fields

Alter statement for DDL statements, irrevocable upon execution.

Alter table test1

Add (

Grade number (3);

Phone varchar2 (20); default 'no'

);

Desc test1; / / Check the structure of the table test1

Modify the field

Alter table test1

Modify (

Grade number (2),

Phone varchar2 (15), default '12345667 '

);

Modify the current operation will be the impact of existing data in the table, when the existing record of the corresponding field contains a null value, type, size can be modified, or modified may fail.

Change the default value is set, only the newly inserted record after this effective.

Clear data in the table

Truncate table statement is used to clear the data in the table

Clear all records in the table

Release form of storage space

For DDL statements, irrevocable upon execution

Truncate table test1;

Remove Table
drop table test1;

Rename Table

Rename old_name to new_name;

Rename test1 to test2;

User-defined table

User-defined table

User to create and maintain a set of tables

Contains the information required by the user

Data dictionary table

By the oracle database automatically creates and maintains a set of tables

Contains database information

What is the data dictionary

Oracle data dictionary is the core of the database used to describe the database and all objects. Data dictionary table by a series of read-only view of the composition, these tables and views are user sys owned by the oracle server is responsible for maintenance, the user can be accessed through the select statement.

The contents of the data dictionary

Database of physical and logical structure, the definition of objects and space allocation, integrity conditions, users, roles, permissions, audit records.

Data dictionary view can be divided into three categories

Dba object information included in all programs

All users can access the object information

User object information for the user program

For example: / / Show the current user has the names of all tables

Select table_name from user_tables;

- View the current user can access the names of all tables

Select table_name from all_tables;

- View the current user owns all objects of type

Select distinct object_type from user_objects;

- View all the user has all object types

Select table_name from dba_tables;

Binding constraint

Not null unique key unique key non-empty primary key foreign key primary key foreign key check check

Instructions

Oracle uses SYS_Cn format name constraints can also be created by a user named binding time.

In the construction of the table at the same time to create, build added separately after the table

You can define the table level or column-level constraints, the data dictionary view to see through the constraints.

The only constraint features:

The only constraint is used to ensure that where the field (or combination of fields) is not a duplicate value. The only constraint fields allow null values. Oracle unique constraint automatically creates a corresponding unique index. The only constraint both in the field-level definitions can also be defined at the table level.

Create table student (

Sid number (3) unique;

Name varchar2 (20)

);

Create table student (

Sid number (3),

Name varchar2 (20),

Constraint studnet_sid_un unique (sid)

);

Create table record (

Sid number (3),

Subject_name varchar2 (20),

Record number (4),

Constraint record1_sid_subName_un unique (sid, subject_name)

); - Sid and subject_name combine two fields can not repeat a single field can be repeated.

Primary key constraint

Joint primary key

Combination of multiple primary key fields, also known as the primary key

The primary key in each field can not be empty

Combination of co-primary key field value can not be duplicated

The primary key can only be defined as a table-level constraint

For example:

Create table record (

Student_id number (3),

Subject_id varchar2 (20),

Record number (3),

Constraint record_stuId_subId_pk primary key (student_id, subject_id)

);

Foreign key constraint foreign key

Features foreign key constraints: foreign key used to ensure that the relevant reference to the relationship between the two fields in order to achieve integrity constraints;

Foreign key constraints are usually built on two fields from different tables between;

Child table foreign key column values ​​in the main table must be within the reference column values, or empty:

Foreign key reference, must be the primary table's primary key or unique key;

Blanket primary key table reference sheet when the corresponding master table records not be deleted

Check constraints check

Definition of each line (field) conditions must be met

The conditional expression in the form of data required to meet the conditions given

Conditional expression does not allow the following

Conditional expression does not allow the following

Currval, nextval, level, rownum pseudo column to be

Sysdate, uid, user, userevn and other functions

Reference values ​​for other fields

Create table test1 (

Name varchar2 (20),

Age number (3) check (age> = 0 and age <= 120)

);

  1. Chapter DDL and Database Objects

View Constraints

Query the user dictionary view user_constrains example: select * from user_constraints;

Available to users of all constraints

Query the user dictionary view user_cons_columns

What can be learned based on field constraints

After construction of the table add constraint

Alter table tablename

Add constraint student_sid_pk primary key (sid);

Alter table student

Modify (name not null); - non-empty constraint must be used to add modify clause

Alter table student

Modify (

Sid not null;

Name default '0 'not null

);

Remove constraints

Alter table table_name

Drop constraint constraint_name;

Alter table student drop constraint student_sid_pk;

Remove the primary key constraint in another way: alter table table_name primary key;

For example: alter table student drop primary key;

Cascade delete constraints

Remove the constraints in, if there is associated with the current constraints of other constraints, the delete operation fails, then the other can be associated with cascade constraints clause be deleted.

Alter table table_name

Drop constraint constraint_name cascade;

Example: create table empinfo (

Eid number (3) constraints empinfo_eid_pk primary key,

Ename varchar2 (20)

);

Create table salary (

Eid number (3) references empinfo (eid)

);

Alter table empinfo drop constraints empinfo_eid_pk cascade;

Delete the table in the field, more fields if the field is in the joint constraints (the primary key, unique key joint, there is a foreign key reference to the current field), then remove fails, then use the cascade constraints clause will be constraints associated with this field be removed.

Create table record (

Student_id number (3),

Subject_id varchar2 (20),

Record number (3),

Constraint record_stuId_subId_pk primary key (student_id, subject_id)

);

Alter table record drop (student_id) cascade constraints;

Disabling Constraints

Lu in the alter table statements, you can use disable constraint clause constraints have been disabled.

Can also be associated with cascade option will also be disabled constraints.

Create table student (

Sid number (10),

Name varchar2 (20),

Constraint student_sid_pk primary key (sid)

);

Alter table student disable constraint student_sid_pk;

View

View by one or more tables to extract data from, is a virtual table, once created can be used as a table.

Advantages: simplify complex data queries, improve operational efficiency, shielding the database table structure, logical data independence, restrict database access, the same data to provide different views to facilitate data sharing.

Simple view: only one base table, no function, no grouping, support for DML operations

Complex view: one or more base tables, use the function, there are groups, not necessarily support the DML operation.

Create view: create view statement by embedding a subquery create view

Create or replace view myview1 (number, name, position, salary) as select empno, ename, job, sal from emp where deptno = 20;

View view structure Desc myview1; deleted view: drop view myview1;

Create or replace - if you do not exist to create, if you replace

View of the principle: in the view query and the query in the table is the same, because in the view query, each table will first check the value of the corresponding field and then query the view.

If the expression appears in the view, then the expression should be given an alias

Create or replace view v1 as select empno, ename, sal * 12 salary, from emp where deptno = 30;

Desc v1; - see the view structure

Create view force

Can use the force option forces the creation of view, create or replace force view myview2 as select empno, ename, job, sal from emp2 where deptno = 20;

Creating a complex view

Create or replace view v_sal (deptno, maxsal, minsal, avgsal) as select deptno, max (sal), min (sal), avg (sal) from emp group by deptno;

Updates view

View can be updated in the DML operations, you can modify the data base table

The definition of updatable views can not use group functions, group by clause, distinct keyword, rownum pseudo column, the field is defined not as an expression

By two or more base tables in the export of view is not updatable, the empty base table column Africa is not included in the view definition is not in view on the insert operation.

DML operations on a view, the syntax is the same operation on the table. Insert, update, delete

Creating a read-only view

Create a view, you can use with read only option of read-only.

Create or replace force view myview2

As select empno, ename, job, sal from emp2 where deptno = 20 with read only;

Temporary view

Embedded SQL statements in the sub-query is a temporary view

Temporary view is not a database object, its definition will not last long stored in the database is cleared after this operation.

Select rownum, a. * from (select * from emp order by sal) a where rownum <= 5 - TOP 5 queries

Index

One for enhancing the efficiency of the database query objects; method by quickly locating data to reduce disk I / O operation; independence index information stored with the table; oracle database use and maintenance of automatic indexing.

Index classification, unique indexes, non-unique index

Creating the index in two ways:

Automatically create, in the definition of primary key or unique key constraint, the system will automatically create the appropriate fields on the unique index.

Created manually, users can be created on columns in other non-unique indexes to accelerate queries.

Create / Delete Index

Create index statement can be used to manually create an index

Create index myindex on emp (ename);

Delete an index

Drop index statement to delete an index using

The operator shall be the index owner, or owner permission to drop the index. Delete table indexes and constraints related would be automatically deleted, but the view and the sequence will retain the drop index myindex;

Create index emp_idx1 on emp (ename);

Select * from emp where ename = 'KING'; - this place will be used in the above index

The principle of creating an index

The following conditions can create an index

Field distribution of a wide range of values, including a large number of null values, often in the where clause or a join condition, the table is accessed frequently, large amounts of data, and often less than the amount of data per visit total of 2 records % -4%

The following are not suitable for creating an index

Table is small, the field does not often appear in the where clause, each data access record of the total is greater than 2% to 4%, the table is updated frequently, being indexed as part of an expression is referenced.

Function-based indexes

The index is based on the expression referred to as function-based indexes, the index expressions from the field in the table, constants, SQL functions and custom functions is built.

Creating a functional index

Create index myindex on emp (lower (ename));

Using the function index select * from emp where lower (ename) = 'king';

Sequence

Automatically generated, do not repeat the integer value, the sequence is a database object that can be shared by multiple users. Typical use is as a master key, it must be unique for each row of.

Application can replace the sequence numbers, sequence values ​​can be stored in the buffer to improve access efficiency.

Create sequence sequence_name

Increment by n - an increase of almost every

Start with n - starting from the number of

Maxvalue n | nomaxvalue - you can set the maximum, you can not set

Minvalue n | nominvalue - whether to use the minimum

Cycle | nocycle - cycle

Cache n | cache - cache

Order | noorder - order

Create sequence mysequence1

Increment by 1

Start with 1

Nomaxvalue nocycle;

Create sequence mysequence2;

Query data dictionary view user_sequences user sequence information available

Using the sequence

Nextval / currval pseudo column

Nextval pseudo-column values ​​from the specified sequence takes the next value

Currval pseudo-column reference is the current value of the specified sequence

Select mysequence1.currval from dual;

Select mysequence1.nextval from dual;

Insert into test1 values ​​(mysequence1.nextval, 'Tom');

Note: Use cache (cache n) can improve the access efficiency.

Sequence in the following cases may occur without a constant: rollback, system abnormalities, multiple tables simultaneously use the same sequence.

Nocache and noorder settings with lower operating efficiency.

Modify the sequence

Alter sequence sequence

Increment by n

Maxvalue n | nomaxvalue

Minvalue n | nomin value

Cycle | nocycle

Cache n | nocache

Order | noorder

Note:

Operation sequence must be the owner, or have permission to alter the sequence; only re-generate the next sequence number to be affected; sequence of initial value can not be changed; some changes will be verified, such as the new if less than the current maxvalue sequence values ​​will give an error.

Delete sequence

Delete statement using the drop sequence sequence, the operator shall be the owner, or have permission to drop.

Synonyms (equivalent alias)

Synonym is equivalent to alias objects, you can use synonyms: easy access to other user objects; shorten the length of the name of the object;

Creating a synonym

Create synonym gt1 for emp; - create an alias for the emp table gt1

Select * from gt1; - using the alias

Drop synonym gt1; - Delete Alias

Create public synonym gt2 for scott.emp; / / must specify the user, the current user is scott

相关文章
  • oracle sql statement notes 2011-08-08

    Left outer join examples: select employee_id, last_name, salary, department_id, department_name from employees left join departments using (department_id) (Equivalent to two tables connected with department_id) Right outer join example: select employ

  • ORACLE SQL statement optimization summarized in 2010-02-23

    ORACLE SQL statement optimization summarized in (1) Select the most efficient sequence table name (only the effective rule-based optimizer): ORACLE parser in accordance with the order processing from right to left in the FROM clause of the table name

  • Oracle SQL statement of the operator Optimization - Optimization Analysis 2010-09-04

    Oracle SQL statement of the operator Optimization - Optimization Analysis Operator optimization IN operator IN written with the advantages of SQL is easier to write and clear easy to understand, is more suitable for modern software development style.

  • Transfer: oracle sql statement optimization 2010-10-25

    Transfer from: http://www.blogjava.net/killme2008/archive/2007/03/02/101434.html (1) choose the most efficient order in the table name (only in the rule-based optimizer valid): ORACLE parser in accordance with the order processing from right to left

  • oracle sql statement Classic 2010-11-09

    1.oracle Multiple lines into one line select d.datagetsource_id,wmsys.wm_concat(d.catagory_id) from datagetsource2catagory d group by d.datagetsource_id 2.oracle The table data import and export C:\Documents and Settings\Administrator>exp irdp/irdp f

  • oracle sql statement to insert the whole date? 2010-11-20

    oracle sql statement to insert the whole date? create table BSYEAR (d date); insert into BSYEAR select to_date('20030101','yyyymmdd')+rownum-1 from all_objects where rownum <= to_char(to_date('20031231','yyyymmdd'),'ddd');

  • Turn: oracle sql statement optimization 2010-10-25

    Transfer: http://www.blogjava.net/killme2008/archive/2007/03/02/101434.html (1) choose the most efficient sequence table name (only in the rule-based optimizer effectively): ORACLE parser in accordance with the order processing from right to left in

  • oracle sql statement to insert the date of the year? 2010-11-20

    oracle sql statement to insert the date of the year? create table BSYEAR (d date); insert into BSYEAR select to_date('20030101','yyyymmdd')+rownum-1 from all_objects where rownum <= to_char(to_date('20031231','yyyymmdd'),'ddd');

  • oracle sql statement optimization (reproduced) 2011-03-01

    (1) choose the most efficient sequence table name (only in the rule-based optimizer effectively): ORACLE parser in accordance with the order processing from right to left in the FROM clause table name, FROM clause written in the last table (base tabl

  • Improving oracle sql statement to optimize the efficiency (34 ways) 2011-04-07

    Improving oracle sql statement to optimize the efficiency (34 ways) (1) choose the most efficient sequence table name (only in the rule-based optimizer effectively): Oracle's parser in accordance with the order processing from right to left in the FR

  • The study notes oracle SQL statement editing 2010-10-28

    First, order a [ppend] text -> will be appended to the text after the current line Second, the command c [hange] / old / new -> old will replace the current new Third, the command cl [ear] buffer -> Clear the buffer so the line Fourth, the comman

  • oracle sql statement (a) 2010-12-10

    Note: The database version is 10g, but also applies to most of 9i, 9i flashback no. 1. Who does not care to develop a database table to delete all the library was scared to death. Results found the following statement to restore the data before 1 hou

  • oracle sql statement in the Plan of Implementation of the method 2011-01-14

    How to generate explain plan? Answer: Run utlxplan.sql. Create plan table For a particular SQL statement, using the explain plan set statement_id = 'tst1' into plan_table for sql statement Run explain plan utlxplp.sql or utlxpls.sql View (Select * fr

  • oracle sql statement execution plan in the analysis method 2011-01-14

    How to generate explain plan? Answer: Run utlxplan.sql. The establishment plan table Specific SQL statement, using the explain plan set statement_id = 'tst1' into plan_table for sql statement Run explain plan look utlxplp.sql or utlxpls.sql (Select *

  • Oracle sql optimization notes (reproduced) 2011-02-25

    http://zmaze.org/?p=193 Basic Sql write notes Minimize the use of the IN operator, essentially all of the IN operator can use EXISTS instead. Do NOT IN operator, you can use NOT EXISTS or outer join + instead. IN subquery in the implementation of Ora

  • ORACLE SQL statement Categories 2010-03-11

    Oracle SQL statements can be divided into the following categories: Data manipulation language statements [Data manipulation language, DML] From one or more tables or views in the query data (SELECT); for operation [fetch] is scrollable [scrollable]

  • Oracle Sql Optimization Notes 2010-03-16

    Note prepared by the basic Sql Minimize the use of IN operator, essentially all of the IN operator can be replaced with EXISTS. NOT IN operators do not, you can use NOT EXISTS or external link + alternatives. IN subquery in the implementation of Orac

  • Review common Oracle SQL statement 2010-04-16

    Long time no write sql statement, and unfamiliar with many, quickly forgot how to write a ... Suddenly remembered today, taking the time to go over to check up ~ The Lost Knowledge Although these are simple statements, but sometimes when used, but ca

  • oracle sql statement Jingjiang 2010-06-12

    Table: A table refers primarily to define the following information: Column table where the table definition of integrity constraints, optional space to store the characteristics of access to data gathered from a query syntax is as follows: CREATE TA

  • Oracle SQL statement in the efficiency of search 2010-09-16

    Statement identifying resource-intensive methods (4 methods) 1. Test group and end-user feedback and slow response-related issues. 2. Using V_ $ SQLAREA view provides the implementation details. (Executive, read the disk and the number of read buffer