Shulou(Shulou.com)06/01 Report--

The contents that will be learned in this chapter are:

How to use the collection operator, which is used to combine rows returned by two or more queries

Use the TRANSLATE function

1. Use 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 the common row of two retrieval locks

MINUS

Returns the row after the row retrieved by the second query is removed from the row retrieved by the first query

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

Use INTERSECT to find the same result in two search results

Use the MINUS operation to return the result of the first result minus the second query result. (interested children's shoes can be tested by themselves.)

2. Use the TRANSLATE function

The TRANSLATE function looks for characters in from_string in x and converts them to corresponding characters in to_string.

3. Use the DECODE () function

DECODE (value,search_value,result,default_value) compares value with search_value. If the two values are equal, DECODE () returns result, otherwise default_value.

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

4. Use CASE expressions

Case expressions can implement if-then- else-type logic in SQL, and case expressions work like decode (), but we should use case because it is ANSI-compliant and has become part of the SQL/92 standard and case expressions are easy to read. Syntax:

Case search_expression

When expression1 then result1

When expression2 then result2

...

When expressionN then resultN

Else default_result

End

5. Use the analysis function

There are many built-in analysis functions in the database that can perform complex calculations, such as finding the highest monthly sales line, the best salesperson, and so on. Analysis functions can be divided into the following categories.

Rating function: calculable grade, percentage, etc.

Inverse percentage function: the value corresponding to the percentage point can be calculated

Window function: calculates cumulative and mobile totals

Report function: results such as injected market share can be calculated

Delay and lead function: you can get the value of that record that is several records with the current record

First function and last function: you can get the first and last return values that have been sorted

Linear regression function: a set of numerical pairs can be fitted by an ordinary least square regression curve.

5. Modify the table contents

Add rows using the INSERT statement

When omitting the list of columns, the order specified must be the same as the order of the columns displayed in the output of the DESCRIBE command

To specify a null value for the control

Copy rows from one table to another. Requirements: the number and type of columns in the source and target tables must match

SQL > INSERT INTO cboss.fault_barrier_base_code (code_type,code_id) SELECT c. Codekeeper type FROM cboss.cboss_base_code WHERE c.serialno c. Codekeeper id < 100

Modify a row using an update statement

Using the RETURNING clause, returns the result calculated using an aggregate function such as AVG ().

Delete rows using the delete statement

6. Integrity of the database

6.1, primary key constraint. Each value of the primary key must be a unique primary key

6.2. Foreign key constraints. Foreign key . . References

6.3.Use the default value default

6.4. merge rows using MERGE

Database transaction (transaction) is a set of SQL statements, this set of SQL statements is a logical unit of work. Transactions are indivisible SQL statements, and the results should permanently modify the contents of the database as a whole.

Or cancel the modification to the database

6.5.1, commit and rollback of transactions

Transaction commit requires the execution of COMMIT statements to commit (commit) the transaction. To cancel the results of the SQL statement, you need to execute the ROLLBACK statement to rollback the transaction and reset all rows to their original state.

6.52. The beginning and end of the transaction

A transaction is a logical unit of work used to split SQL statements. A transaction has both a starting point and an end. The transaction starts when the DML statement is executed. The transaction ends when COMMIT or ROLLBACK is executed. Execute DDL statements, such as CREATE auto-commit transactions. Execute DCL statements, such as GRANT statements, to automatically commit transactions.

6.5.3. Set the SavePoint, SAVEPOINT, to roll back the changes to the SavePoint. This is very useful for a big business.

6.5.4. ACID characteristics of transactions

Atomicity (Atomic) transactions are atomic and indivisible

Consist transactions must ensure that the state of the database is consistent, the transaction ends, the database state ends, the transaction begins, and the database state begins

Isolation (Isolated) multiple transactions run independently and do not affect each other

Durale) once a transaction is committed, changes to the data are retained forever.

6.5.5, concurrent transactions

Oracle database software supports 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, Oracle database software must ensure that the data in the table is always valid, which can be achieved through Lock.

7. Users, privileges and roles

Create a user:

CREATE USER user_name IDENTIFIED BY password [DEFAULT TABLESPACE default_tablespace] [TEMPORARY TABLESPACE temporary_tablespace]

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 sample REMOVE CREATE,CONNECT FRMO USER_NAME

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

The advantages and characteristics of a character:

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

When you add and remove a privilege for a role, all users and roles granted to that role automatically receive the new privilege or lose it automatically

You can grant multiple roles to a user or role

You can set a password for a role

Create role CREATE ROLE user_manager; set password CREATE ROLE admin_manager IDENTIFIED BY admin_password;# authorize role GRANT PRIVILEGES ON TABLE_NAME TO USER_MANAGER;# to grant role to user GRANT ROLE_NAME TO USER_NAME

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.