Oracle database-Management section for data processing and tables

Oracle database-Management section for data processing and tables Types of *sql

• DML(Data manipulation Language-action language): Select/insert/update/delete
• DDL(data definition Language): Create Table/alter table/truncate table/drop table
• Create/drop view/sequnece/index/synonym (synonyms)
• DCL(Data Control Language language): Grant (Authorization)/revoke (revoke permission)

DML statement (increment/delete/change) INSERT statement for inserting data

• Add a new value for each column
• List the values of individual columns in the default order of columns
• Arbitrarily list column names and their values in the INSERT clause
• character and date type data should be enclosed in single quotes

The syntax uses this syntax to insert only one data sample into a table at a time: inserting a null value using the address character placeholder for a preparedstatement similar to JDBC, using the placeholder "?" To indicate the position of the value to be inserted later in Oracle you can use the "&" symbol to specify the column value, where the position in the values clause typically uses the same address-letter suffix as the column name, such as: Id--&id address characters can be used in almost all SQL statements, not just INSERT statements
such as:Copying data from other tables syntax is to add a subquery after the INSERT statement

• No need to write the values clause
  
• The list of values in the subquery should correspond to the column names to be inserted in the INSERT clause
  
• This way you can insert more than one data at a time
  

This approach is inefficient when you need to insert massive amounts of data, usually using the following methods to solveMass Insert Data

• Oracle provides data pump (plsql program): Dbms_datapump (Package)
  
• Sql*loader
• External table

The UPDATE statement syntax can update multiple data at a time general use the WHERE clause to determine which data needs to be updated in the UPDATE statement using the data integrity error in the subquery updates operationThe integrity of the data refers to the table-to-table defined when the table is created . ConstraintsDelete statement syntax can delete multiple data at a time use a WHERE clause to determine what data integrity errors need to be updated about the delete and truncate differences

• is to delete the data in the table
• Delete is deleted, truncate is the first to destroy the table and rebuild
• Delete faster, truncate slower than delete
• Cause: The Undo Data feature of Oracle
• Delete is a DML (data manipulation language) statement, truncate is a DDL (data definition Language) statement
• The delete operation can roll back the rollback
• The delete operation can be flashed back (Oracle-specific and can be flashed back after the transaction is committed)
• In Oracle, most operations can be flashed back, that is, most operations are reversible
• The delete operation may be fragmented and does not free up space
• About fragmentation
• Fragmentation causes discontinuities between data entries in a table, affecting the speed of queries
• Ways to clean up fragments:

Note: INSERT, UPDATE, and delete operations can cause changes in data that must account for the integrity of the data in Oracle Transaction concept database transaction composition

• One or more DML statements
  
• a DDL(data definition language– words) Statement
• a DCL(Data Control language– language ) Statement

DatabaseExecution flow of a transaction

• in execution of the first DML statement as a start
  
• End with one of the following
  

• Show End :commit rollback
• Implicit end (auto-commit ): DDL language, DCL language, exit (transaction exits normally)
• DDL statements are accompanied by actions that commit previous transactions
• Implicit rollback ( end of system exception): close window, crash or power down, etc.

The Commit and rollback statements use the COMMIT and rollback statements to

• Ensure data integrity
  
• Preview before data changes are submitted
  
• Group logically related actions
  

Controlling transactions through SavePoint

• Use the savepoint statement to create a savepoint in the current transaction.
  
• use the rollback to savepoint statement to roll back to create the Save Point
  
• Rolling back and forth using savepoint to avoid small errors and rollback the entire transaction

The isolation level of a database transaction is for multiple transactions that run concurrently when those transactions access the same data in the database, iffailure to take the necessary isolation mechanisms can lead to a variety of concurrency problems :

• Dirty read: For two things t1,t2,t1 read a field that has been updated by T2 but has not yet been committed. Then, if T2 is rolled back, the content read by T1 is temporary and invalid.
  
• non-repeatable read : for two things t1,t2,t1 read a field and then T2 update the field. after that, T1 reads the same field again, and the value is different.
  
• Phantom Read : for two things t1,t2,t1 read a field from a table, and then T2 in the table a number of new rows were inserted. After that, if T1 reads the same table again, it will have a few more rows
  

The isolation database system for database transactions must have the ability to isolate and run each transaction concurrentlyforces so that they do not interact with each other and avoid various concurrency problemsthe degree to which a transaction is isolated from other transactions is called the isolation levelThe database specifies a variety of transactionsisolation level, different isolation levels correspond to different degrees of interference, higher isolation levels, consistent datasex is better, but the concurrency is weaker.4 Types of Transaction isolation levels provided by the database Oracle supports two types of transaction isolation (or three types)

• Oracle supports 2 levels of transaction isolation: READ commited and serializable (or plus read only three types)
  
• Oracle's default The Transaction isolation level is: READ commited

MySQL supports 4 types of transaction isolation levels. Mysql default service isolation level is: repeatable READ DDL statements (Management and Operations Tables) Common database object Tablespace table space is the logical unit database of an Oracle database-a tablespace can be built into multiple table spaces by associating a database with multiple data files (physical structure), a tablespace can build multiple users, A user can create more than one table to establish a table space statement the user creates a user statement for the user to assign a permission statement that the newly created user does not have any permissions, the login will prompt the role Oracle has three major roles

• Connect role
• Resource role
• DBA role

The following permissions are assigned to the CREATE TABLE statement * Prerequisites:

• User needs to have CREATE TABLE permission
  
• There is enough storage space

Syntax must be specified Table name， Column NameAnd Data TypeAnd size of data typeExample: Default value-defaults When you perform an insert operation, you can specify a default value syntax for it:

• values, expressions, and SQL statements can all be used as default values
  
• Other column names, or pseudo-columns, are illegal.
  
• The type of the default value must be the same as the type of the column
  

Create a table with a SELECT statement the result of a SELECT statement is the equivalent of a table, and we can turn this query result into a table that creates a table that stores the query results of a SELECT statement so that you can easily create a new table syntax: note As keywordExample: Copying an existing table structure (creating a table with the same structure as an existing table) leverages the ability to create a table corresponding to the query results by using the SELECT statement:CREATE table new table as SELECT * from where 1=2;The table will be copied without copying the data in the table similarly, you can add the data type of the table data column required for the filter copy in the Where conditionabout ROWID (line address):

• is a string of strings
  
• Use ROWID to find this line
  

* naming rules for table and column names

• Must start with a letter
  
• Must be between 1–30 characters
  
• Must contain only a–z, A–z, 0–9, _, $, and #
  
• Must not duplicate user-defined other objects
  
• Must not be a reserved word for Oracle
  
• Oracle default storage is all saved as uppercase
  
• The database name can only be a 1~8 bit, datalink may be 128 bits, and its some of his special characters

* Use a subquery to create a table using the AS subquery option, combine the CREATE table with the Insert data syntax: requirements:

• Columns in the specified column and subquery correspond to each other
• Defining columns by column name and default value

Example: Modifying a table an ALTER TABLE statement uses the ALTER TABLE statement to

• Append a new column
• Modify an existing column
• Delete a column

Append/Modify/delete/rename append columns for syntax columns example: Modify a column example to modify the data type of a column, data type size, and default values Note: modifications to the default valuesAffects only FutureModifications to the table, previous data does not affect deleting a column exampleDelete a tableDROP TABLE statement using the DROP TABLE statement

• Data and structure are deleted
• All related things that are running are being submitted
• All related indexes are deleted
• The DROP TABLE statement cannot be rolled back, but can be flashed back

Syntax drop table is not completely deleted, just put the table in the RecycleBin (Recycle Bin) can be added double quotation mark method to find out the deleted table Recycle Bin can be emptiedNote: The Recycle Bin does not exist, and some users do not have a recycle Bin (such as sys-Admin user), be awarePurge keyword can skip the Recycle Bin to completely remove deleted tables from the Recycle Bin There are several ways, such as Flashback Delete: Seven ways: which flash back data archive oracle11g only, The remaining six kinds of 11G and 10G have detailed self-examination rename object Rename statement Execution Rename statement will change the table, view, sequence, or synonymnamePrerequisite: The object must be the owner of the syntax to empty the table TRUNCATE TABLES statement using the TRUNCATE TABLE statement to

• Delete all data in a table
• Freeing the table's storage space

Syntax Note:

• Truncate statement cannot be rolled back
• You can delete data using the DELETE statement instead of the TRUNCATE statement

The concept of constraint constraints

• Constraints are table-level constraints
  
• If there is a dependency, the constraint can prevent the erroneous deletion of the data
  
• Types of Constraints
  
• Not null-non-null constraint
• unique-UNIQUE Constraint
• PRIMARY key-PRIMARY KEY constraint
• FOREIGN key-FOREIGN KEY constraint
• check-CHECK Constraint

Constraint levels have two levels of constraint in Oracle

• Column-level constraints: Typically column-level constraints, if not specifically specified
• Table-level constraints
• Typical: Federated primary key

Constraint rules

• Users can customize constraints, or they can usethe SYS_CN format of Oracle server to name constraints
• Timing of constraint creation:
• When creating a table, create constraints at the same time
• After the table structure is created
• Constraints can be defined at the column level, or at the table level
• Viewing constraints through a data dictionary

The type of constraint not null-a non-null constraint guarantees that the value of the column is not NULL unique-a uniqueness constraint guarantees that the value of the column is unique primary the key-PRIMARY KEY constraint queries the data faster through the primary key, because the primary key is a unique Index, for the principle see the value of the constraint column in the index section below:

• is not empty
• Only

FOREIGN key-FOREIGN KEY constraint is constrained with primary key of main table

• FOREIGN KEY: In a child table, a table-level constraint is defined
• REFERENCES: Specifying columns in tables and parent tables
• On delete CASCADE: When the parent table is deleted, Cascade Delete sub-table records (use cautiously, more dangerous)
• On DELETE SET null: Sets the foreign key value of the dependent dependency record of the child table to NULL, when the child table is not associated with the parent table

the foreign key of the child table must be the primary key of the parent tablecheck-CHECK Constraint

• Define the conditions that must be met for each row of records
• If the salary should be positive (>0), etc.
• The following expression can be used in a check constraint:
• Reference Currval, Nextval, level, and rownum
• Call the Sysdate, UID, user, and Userenv functions
• Query records for another table

It is a good idea to define the name of the check constraint when defining the CHECK constraint, and when the constraint is not met, the name of the Emp_salary_min is the name of the constraint.Summary example: Common database objects
View

• A view is a virtual table
• Views are based on existing tables, and the tables on which the views are built are called base tables .
• The view itself is not storing data, data from the base table
• A statement that provides data content to a view is a SELECT statement that can be understood as a stored select statement
• A view provides another representation of the base table data to the user

View benefits Create a view-basic permissions Generally speaking, ordinary users do not have permission to create views and need to be authorized by the administrator syntax

• Force: Subqueries do not necessarily exist
• Noforce: Child query exists (default)
• CHECK OPTION: Only manipulate the data of the view, if the operation view does not exist data is error
• For example, if the view is a staff view of unit 10th, it is not possible to insert the department number as a different employee entry.
• "Can only be seen in action"
• Working with a view on a table is not recommended
• With READ only: query operation only

a subquery can be a complex SELECT statementExample: Show view structure: use Example 2: Create a view when a subquery defines an alias for a column you should then use an alias when you select a column in the ViewQuery view syntax simple view and complex view substitution (equivalent modification) view use the Create or replace view clause to modify the view creation or replace the aliases of the columns in the CREATE VIEW clause with thecolumn CorrespondsCreate complex view complex views Example: Query the minimum wage for each department, the maximum wage, and the rules for using DML in the average wage viewNote: Views are not recommendedon the table in -line operation because there are too many rules for using DML
DML operations can be performed in a simple view Delete is not available when the view definition contains the following elements

• Group functions
  
• GROUP BY clause
  
• DISTINCT (remove weight) keyword
  
• RowNum Pseudo-Column

Update cannot be used when the view definition contains the following elements

• Group functions
• GROUP BY clause
• Distinct keywords
• RowNum Pseudo-Column
• The definition of a column is an expression

cannot be used when the view definition contains one of the following elementsInsert

• Group functions
• GROUP BY clause
• Distinct keywords
• RowNum Pseudo-Column
• The definition of a column is an expression
• Non-empty columns in the table are not included in the view definition

Masking DML Operations

• You can use the WITH READ only option to mask DML operations on a view
• Any DML operation will return an Oracle Server error error

Deleting a view Deletes a view simply deletes the definition of the view, and does not delete the data series sequence of the base table, which refers to the Multiple UsersUsed to generate Unique ValuesThe database forElephant

• Automatically provide a unique value
• Shared objects
• Primarily used to provide primary key values
• Loading sequence values into memory can improve access efficiency
• * Creating a sequence is equivalent to creating an array

The sequence in Oracle is functionally equivalent to creating a sequence like auto_increment in MySQLcreating a sequence is equivalent to creating an array, and the array length defaults to
Grammar

• INCREMENT by N: Define self-increment (step), default = 1
• Start with N: Defines the starting value, which defaults to 1
• MAXVALUE N/minvalue N | Nomaxvalue/nominvalue: Max value/min value | No maximum/No minimum value, default is no maximum minimum
• CYCLE | Nocycle: Loop (generates duplicate values), default is Nocycle
• CACHE N | NOCACHE: Cache Length (equivalent to the length of this array), default is 20 | No cache, that is, a length of 1

Example:

• Create sequence dept_deptid_seq for table departments provide primary key
  
• Self-increment is 10
• Starting value is 120
• No minimum value by default
• The maximum value is 9999
• No loops
• Do not define Cache

Query sequence sample query data dictionary view user_sequences get orderColumn Definition InformationIf the NoCache option is specified, the columnLast_number shows the next valid value in the sequenceNextval and Currval pseudo-columns (equivalent to manipulating an array of pointers) this pointer is by default in front of the 1th element (that is, currval is meaningless at this time)

• Nextval Returns the next valid value in the sequence, any user can all be referenced
  
• Current value of the stored sequence in the Currval
  
• Nextval should be specified before Currval, both should be effective
  

Features of the sequence of application examples of sequences

• Loading sequence values into memory improves access efficiency
  
• The sequence cracks (discontinuous) in the following cases
  

• Rolling back
• System exceptions
• Multiple tables use the same sequence at the same time
• If you do not load the value of the sequence into memory (NOCACHE), you can use the table user_sequences Viewing the current valid values for a sequence
  

The Modify sequence example modifies the increment, maximum, minimum, loop options, orno load in memoryConsiderations for Modifying a sequence

• The user who modifies the sequence must be the owner of the sequence or have ALTER permission on the sequence
• Only future sequence values will be changed.
• Changing the initial value of a sequence can only be achieved by removing the sequence after the series is reconstructed

Delete a sequence

• To delete a sequence using the drop sequence statement
  
• After deletion, the sequence cannot be referenced again
  

Example: Indexed indexes are data objects used to speed up data access, and proper use of indexes can significantly reduce I/O times, resulting in improved data access performance

• A table-independent schema object that can be stored in a different disk or table space than the table
• The index is deleted or corrupted and does not affect the table, it only affects the speed of the inquiry
• Once an index is established, the Oracle Management system automatically maintains it, and the Oracle management system determines when the index is used, and the user does not 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
• " a catalogue like a book."

Types of Index indexes created

• Single-column indexes: indexes based on individual columns
• such as CREATE index index name on table name (column name)
• Composite index (Multilevel index): An index based on two or more columns that can have multiple indexes on the same table, but requires that the combination of columns must be different
• Such as: Create INDEX emp_idx1 on EMP (ename,job); and create index emp_idx2 on EMP (job,ename);

Index underlying principle type

• B-Tree (default)
• Bitmap

Automatically created in the definition primary KEY or unique aboutThe system automatically creates a unique index on the corresponding column after the bundleManually created users can create non-unique indexes on other columns.to speed up the querySample execution plan (efficiency comparison-do not create indexes and create indexes) cost (%CPU) is as low as possible when creating an index the following conditions can be used to 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 the data accessed is roughly 2% to 4% of the total data

You should not create an index in the following situations

• The table is small.
  
• Columns are not often used as join conditions or appear in a WHERE clause
  
• Data queried is greater than 2% to 4%
  
• Tables are updated frequently (the columns corresponding to the indexes are updated frequently)
  

The query index sample can use the data dictionary view user_indexes andUser_ind_columns Viewing the information for an indexDelete IndexTo delete an index by using the DROP INDEX commandDelete Index Upper_last_name_idxonly the owner of the index or the drop any index permissionusers can delete the index
Synonyms (aliases) use synonyms to access the same object

• Easy access to other users ' objects
  
• Shorten the length of the object name
  

Whether the syntax has a public or private synonym (for the current user) creating and deleting synonyms requires permission to create synonyms to delete synonyms

Oracle database-Management section for data processing and tables