Oracle group functions, multi-table queries, set operations, database objects (sequences, views, constraints, indexes, synonyms), etc.

Count Group function: (filter out fields that are empty)

Select count (Address), COUNT (*) from B_user

Max () Avg () min (), SUM ()

Select SUM (age), Max (age), min (age), Avg (NVL (age,0)) from B_user

1 260 70) 10 37.1428571428571

groupby: If the field name is defined previously, groupby must also write the field

Select Name,pwd,sum (age) from B_user Group by name,pwd

If the group by Name,age is canceled, and the execution results are the same, only one record is taken

Select Name,sum (age) from B_user Group by Name,age

You cannot use a group function in a where statement, and a having can

Having can filter grouped results

Select Name,sum (age) from B_user Group by Name,age have sum (age) >=50

Select Name,case when address was null then ' no address ' else address end from B_user

Case-When statement

Select Name,case Name

When ' 1 ' then age+10

else age+100

End Age after change

From BGT

Select Name,decode (Name, ' 1 ', age+10, ' 2 ', age+100) age from BGT

Select Name,decode (Name, ' 1 ', age+10,age+100) age from BGT after being removed indicates that the other default is age+100

Multi-Table query:

Equivalent connection

SELECT * from inter_00201 a,inter_00202 b where a.ahd010401=b.ahd010401

No equivalent connection

SELECT * from res_00201 a,res_00202 b where a.ahd050203 between 300.00 and 10000.00

External connection

Left outer connection

SELECT * from res_00201 a,res_00202 b where a.intype=b.intype (+)

Right outer connection

SELECT * from res_00201 a,res_00202 b where a.intype (+) =b.intype

Self-connect (using small tables)

SELECT * FROM inter_00201 a,inter_00202 b

Hierarchical query

Sub-query:

Subqueries should be inside the parentheses

subquery to right indent relative to main query

A subquery can be added after the where from select has a primary query

SELCT * FROM (SELECT * from user where username= ' wicker ')

The Virgin query must be a single-line subquery, which is a

Select Ename, (select Job from emp where id= ' ") from EMP

Sub-query cannot be placed behind group by of main query

main query and subquery may not be the same table , only the primary query that returns results is available .

SELECT * from user where username= (select username from role where roleid= ' 1012 ')

Null value problem in subquery

If NULL is present, and use not in subquery is equivalent to <> all returns unselected rows

Multiline subquery operator

In equals any one of the list

Any value that is returned by any and a subquery is compared

SELECT * from BGT where age< any (10,20,30,40)

Any qualifying record that is less than 10,20,30,40 is less than 40 of all matching records. If it is greater then the opposite is greater than the smallest

all and all values returned by the subquery are compared .

SELECT * from BGT where age< all (20,30,40)

That is, all records that are less than 20 eligible. If it's greater than that, it's just the opposite.

About line number RowNum

Once the build is not changed

Line numbers can only be used < <= cannot use > >=

SELECT * FROM (select RowNum r,age from (select Rownum,age to BGT order BY age desc) where RowNum <=3) where R >= 2

Can change rownum in this way, query 2--3 bar

Select Name,wm_concat (age) from the BGT group by name can be classified splicing such as a member name of Level 1

GROUP by rollup () Enhanced GROUP by

Select Name,job,sum (SAL) from the EMP Group by rollup (Name,job)

This SQL is equivalent to:

Select Name,job,sum (SAL) from the EMP Group by Name,job

Union

Select Name,to_char (null), SUM (SAL) from the EMP group by name

Union

Select To_char (null), TO_CHAR (null), SUM (SAL) from EMP

Set operators

and set union/union all

Intersection intersect

Difference set minus

General Operations Small table for set operations

Set timing on open execution switch

Set timing off Open execution switch

Set feedback off type has been selected for 14 rows. Tips

Load External SQL

@c:\sql.sql

Special insert (address character):

INSERT INTO tablename (column 1, column 2, column 3) VALUES (& Column 1,& column 2,& column 3)

When you enter, you will be prompted:

Enter the value for column 1: ...

Enter the value for column 2: ...

Enter the value for column 3: ...

Select Name,pwd,&unknown from user

Enter the Unknown value:

Result ....

A special way to create a table structure:

CREATE TABLE User2 as SELECT * from user where 1=2

If the table structure is the same, you can insert it directly (the table will not be created automatically)

INSERT INTO User2 SELECT * from user where username like ' b% '

Enter the value for column 1: ...

Clear table

TRUNCATE TABLE tablename (destroys the table and creates a new , free space , no fragmentation , no return)

Delete from tablename (delete records One by one, may fragment , and does not free up space , revocable operation)

To define a save point:

SavePoint Save Name

Rollback to SavePoint save name

Setting a savepoint in JDBC

Transaction ISOLATION level for Oracle databases

Read commited,serializable two types and their own read only

There are four types of databases available:

Read uncommited (reading uncommitted data)

Read commited (reading the submitted data)

REPEATABLE READ (Repeatable Read)

SERIALIZABLE (serialization)

Oracle Recycle Bin Features

To view the Recycle Bin:

Show RecycleBin

Empty the Recycle Bin:

Purge RecycleBin

To completely delete a table:

DROP TABLE TableName Purge

CREATE TABLE Person (

PID number, PName varchar, gender varchar2 (2) Check (gender in (' Male ', ' female '), age number Check (age>0));

CREATE TABLE Yueshu (

Yid number constraint YUSHU_PK primary key,

Yname varchar2 (constraint) yushu_name NOT NULL,

Email VARCHAR2 (+) constraint Yushu_email unique,

SAL number constraint yushu_sal check (sal>10),

Sex VARCHAR2 (2) constraint yushu_sex check (sex in (' Male ', ' female ')),

DEPTNO number constraint Yushu_deptno references dept (deptno) on DELETE cascade);

Annotation: Deptno foreign key is the primary key of the Dept table Deptno

Table: A basic collection of data stores, consisting of rows and columns.

Views: Logically related collections of data extracted from a table.

SELECT * from User_role_privs; This statement can identify all permissions for the current user

Advantages of the View:

Restrict data access, simplify complex queries, and provide data that is independent of each other, with the same data that can be displayed in different ways.

But the view does not provide performance

A view is a virtual table.

Views are built on existing tables, and the tables on which they are built are called base tables.

The statement that provides the data content to the view is a SELECT statement that can be interpreted as a stored SELECT statement.

A view provides another representation of the base table data to the user.

Create or Replace view myView (column 1, column 2, column 3, column 4 ...) As select column 1, column 2, column 3, column 4...from tablename where column 1=10 with Read only

Plus with Read only means read only

With CHECK option indicates that the detection data conforms to the query criteria, such as when the Insert view cannot insert non-qualifying data. ）

Drop View ViewName

Sequence: Provides a regular value.

A database object that can be used by multiple users to produce unique values.

Automatically provides unique numeric values, shared objects, primarily for providing primary key values, and loading sequence values into memory can improve access efficiency.

Create sequence mysequence increment by 1 start with 4 MaxValue cache 3 cycle;

INSERT INTO Table tablename VALUES (Mysequence.nextval,?,?,?,?)

Alter sequence mysequence increment by 1 start with 4 MaxValue cache 3 cycle;

Drop sequence;

Index: Improve the efficiency of queries.

A table-independent schema object that can be stored in a different disk or table space than the table

The index is corrupted or damaged, and it does not affect the table, it only affects the speed of the query.

Once an index is established, the Oracle Management system automatically maintains it, and the Oracle management system determines when the index is used. The user does not have to specify which index to use in the query statement.

When you delete a table, all indexes that are based on that table are automatically deleted.

Accelerate query speed for Oracle servers with pointers

Reduce disk I/O by quickly locating data.

Auto Create: Define primary key or unique system will automatically create a unique index on the corresponding column

Manually created: Create a non-unique index on another column, accelerated query

Created Index indexname on table (coloumn)

What happens when you create an index:

A wide range of data values are distributed in columns

Columns often appear in a WHERE clause or join condition

Tables are often accessed and data volumes are large, and access to data accounts for roughly the total amount of 2%~4%

Under what circumstances is not appropriate to create an index

The table is small.

Columns are not often used as join conditions or appear in a WHERE clause

Query data greater than 2%~4%

Tables are updated frequently.

You can use the data dictionary view user_indexes and user_ind_columns to view the information for the index

Drop Index IndexName

Synonyms: Aliases an object.

Accessing the same object using synonyms

Easy access to other users ' objects

Shorten the length of the object name

Create [public] synonym Synonymname for object

Drop synonym Name