Oracle Advanced Queries

What we will learn in this chapter are:

• How to use Set operators, which combine the rows returned by two or more queries

• Using the Translate function
  

1. Using the SET operator

Operator	Description
UNION All	Returns all rows retrieved by each query, including duplicate rows
UNION	Returns all rows retrieved by each query, excluding duplicate rows
INTERSECT	Returns a common row of two search locks
Minus	Returns the row after the row retrieved by the second query is stripped from the row retrieved by the first query

Note: When using the set operator, it is important to keep in mind that the number of columns returned by all queries and the type of the columns must match, but the column names can be different.

use intersect to find the same results in two search results

650) this.width=650; "Title=" collection operator error example. PNG "src=" Http://s1.51cto.com/wyfs02/M01/82/CD/wKioL1dhQISDcgorAAAk8vgPR8c926.png-wh_500x0-wm_3-wmp_4-s_ 334652676.png "alt=" Wkiol1dhqisdcgoraaak8vgpr8c926.png-wh_50 "/>

650) this.width=650; the "title=" collection operator is the correct example. PNG "src=" Http://s5.51cto.com/wyfs02/M01/82/CE/wKiom1dhP_6wE3CjAAAmPWzjel8543.png-wh_500x0-wm_3-wmp_4-s_ 43772369.png "alt=" Wkiom1dhp_6we3cjaaampwzjel8543.png-wh_50 "/>

Use the minus operation to return the result of the first result after the second query result is removed. (Children's shoes with interest can be tested by themselves)

2. Using the Translate function

The TRANSLATE (x,from_string,to_string) function finds the characters in the from_string in X and converts them to the corresponding characters in to_string. 650) this.width=650; "title=" traslate example. PNG "src=" Http://s4.51cto.com/wyfs02/M02/82/CD/wKioL1dhRBnh1paKAABQBHFdto4629.png-wh_500x0-wm_3-wmp_4-s_ 1560454068.png "alt=" Wkiol1dhrbnh1pakaabqbhfdto4629.png-wh_50 "/>

3. Using the Decode () function

DECODE (Value,search_value,result,default_value) Compare value to Search_value. If the two values are equal, DECODE () returns result, otherwise Default_value is returned.

DECODE () allows you to perform logical processing of if-then-else types in SQL.

650) this.width=650; example of "title=" decode function. PNG "src=" Http://s2.51cto.com/wyfs02/M00/82/CD/wKioL1dhRTaD-rGEAAAp25Yq-RU675.png-wh_500x0-wm_3-wmp_4-s_ 2837608078.png "alt=" Wkiol1dhrtad-rgeaaap25yq-ru675.png-wh_50 "/>

4. Use case expressions

The case expression can implement If-then-else logic in SQL, and the case expression works like decode (), but we should use case because it is ANSI compliant and has become a sql/ 92 part of the standard another case expression is easy to read. Syntax:

Case Search_expression

When Expression1 then RESULT1

When Expression2 then RESULT2

...

When Expressionn then RESULTN

else Default_result

End

5. Using Analytic functions

     database, Ability to perform complex calculations, such as finding the highest-selling commodity lines per month, the best performers, and more. Analysis functions can be divided into the following categories.

• Rating function: Can calculate rank, percentage, etc.

• Inverse percent function: calculates the value corresponding to the percentile

• Window functions: Cumulative and moving totals can be calculated

• Report functions: Can calculate injection market share and the like results

• Delay and lead function: The value of the record that can be recorded with the current record as several records

• First and last functions: You can get the first and final return values that have been sorted

• Linear regression function: a common minimum square regression curve can be used to fit a group of values

5. Modify the contents of the table

To add a row using the INSERT statement

When you omit a list of columns, the order that you specify must match the order of the columns displayed by the output of the describe command

To specify a null value for control

Copy rows from one table to another. Requirement: The number of columns for the source and destination tables and the type of the columns must match

Sql>insert to Cboss.fault_barrier_base_code (code_type,code_id) SELECT c.code_type,c.code_id from Cboss.cboss_ba Se_code WHERE C.serialno < 100

modifying rows using the UPDATE statement

Use the returning clause to return the result computed using an aggregate function such as AVG ().

Delete a row by using the DELETE statement

6, the integrity of the database

6.1, PRIMARY KEY constraints. Each value of a primary key must be unique primary key

6.2, FOREIGN KEY constraints. FOREIGN key. References

6.3. Using default defaults

6.4. Merge rows with merge

6.5. Database transactions (transaction) are a set of SQL statements that are a logical unit of work. A transaction is an indivisible SQL statement whose results should be permanently modified to the contents of the database as a whole

or cancel the modification to the database

6.5.1, Commit and rollback of transactions

A transaction commit requires a COMMIT statement to commit the (commit) transaction. To cancel the result of the SQL statement, you need to execute the ROLLBACK statement to roll back (rollback) the transaction and reset all rows back to their original state.

6.52. Start and end of business

A transaction is a logical unit of work used to split an SQL statement. A transaction has both a starting point and an end point. The transaction begins when the DML statement is executed. The transaction ends when a commit or rollback is executed. Executes a DDL statement, such as create auto-commit transaction. Executes a DCL statement, such as a grant statement, to automatically commit a transaction.

6.5.3, set the save point, savepoint, you can roll the changes back to the save point. This is useful for a big business.

ACID properties of 6.5.4 and transactions

Atomic (Atomic) transactions are atomic, indivisible

Consistency (consist) transactions must ensure that the state of the database is consistent, that the transaction is complete, that the database state ends, that the transaction begins, and that the database state is started

Isolation (Isolated) Multiple transactions are run independently of each other and do not affect each other

Persistence (Durale) Once a transaction is committed, the data changes are preserved forever.

6.5.5, concurrent transactions

Oracle database software enables multiple users to interact with the database at the same time, and each user can run their own transactions at the same time. This transaction becomes a concurrent transaction (concurrent transaction).

To support concurrent transactions, the Oracle database software must ensure that the data in the table is always valid, which can be achieved by locking (lock).

7. Users, privileges and roles

To create a user:

CREATE USER user_name identified by Password[default tablespace default_tablespace][temporary tablespace temporary_ Tablespace]

To authorize the user:

Grant Privilige to User; example: Grant Create,drop,connect to user_name; View User System privileges SELECT * from User_sys_privs ORDER by PRIVILEGE; View User Object Privileges select * from USER_TAB_PRIVS; Revoke user privileges Remove Priveles from user_name example remove Create,connect Frmo user_name;

A role is a set of privileges that can be assigned to a user or other role

Advantages and characteristics of the role:

• Instead of granting privileges directly to one user at a time, you first create a role, grant the role some privileges, and then grant the role to multiple users and roles

• When you add or remove a privilege from a role, all users and roles that are granted that role automatically gain new privileges or automatically lose this privilege

• You can grant multiple roles to a single user or role

• You can set a password for a role

Creating roles create role User_manager; Set password create role Admin_manager identified by Admin_password; #为角色授权GRANT privileges on Table_ NAME to User_manager, #将角色授予用户GRANT role_name to user_name;

This article is from the "Ah Cool blog source" blog, please make sure to keep this source http://aku28907.blog.51cto.com/5668513/1789687

Oracle Advanced Queries