Database Application (concept)

In the same dataset, different selection conditions correspond to different output results. A database is a warehouse that organizes, stores, and manages data according to the data structure. A program or user can use it to access and modify data. It is the soul of data storage. Data management has gone through three stages: manual management, file system to database system. Database

In the same dataset, different selection conditions correspond to different output results. A database is a warehouse that organizes, stores, and manages data according to the data structure. A program or user can use it to access and modify data. It is the soul of data storage. Data management has gone through three stages: manual management, file system to database system. Database

In the same dataset, different selection conditions correspond to different output results. A database is a warehouse that organizes, stores, and manages data according to the data structure. A program or user can use it to access and modify data. It is the soul of data storage.

Data management has gone through three stages: manual management, file system to database system. A database is a combination of logical relationships and deterministic data. It can overcome a series of problems arising from traditional file organizations and reduce data redundancy, the relational database management system is widely used in actual system development because of its great flexibility and simple design for information query.

• What is a trigger?
  • What are the differences between trigger beforehand and trigger afterwards? What are the differences between statement-Level Trigger and row-Level Trigger?
• What is an index?
• What is rollback?
• Types of data backup
• What is a cursor?
• What happens if the database logs are full?
• How to determine who has inserted a row of data into the database

For practical applications, basic database operations cannot meet the actual needs. As the number of data tables increases and the amount of data increases, the efficiency and security of databases become increasingly prominent, the database introduces triggers, cursors, indexes, and other content to meet these requirements.

What is a trigger?

A trigger is a special type of stored procedure that is triggered by an event rather than being called or manually started by a program. When a database has special operations, these operations are triggered by database events and these SQL statements are automatically completed. The trigger can be used to ensure the validity and integrity of data and complete data constraints that are more complex than constraints.

Depending on the SQL statement, triggers can be divided into two types: DML triggers and DLL triggers.

DML triggers are stored in the current database server when a data operation language event occurs. There are two types of triggers: After and Instead. When an After trigger is activated, a trigger is triggered After the record changes. Before recording changes, the Instead trigger executes the operations defined by the trigger rather than the operations in the original SQL statement. DLL triggers are stored in response to data definition language events.

The trigger is mainly used in the following aspects:
(1) increased security
(2) Use a trigger to record the changes made and related information, track user operations on the database, and implement audit
(3) maintain the integrity constraints of the values that cannot be implemented by the declared constraints during table creation, and monitor and respond to specific events in the database.
(4) implement complex and non-standard database integrity rules, and assign values to the data in the table in real time.
(5) triggers are automatic and will be activated after any changes are made to the table data. For example, the data value can be automatically calculated. If the data value meets certain requirements, specific processing will be performed. Take financial management of an enterprise as an example. If the capital chain of an enterprise is short and reaches a certain level, a warning is sent.

The following is an example of a trigger. The trigger is used to update data tables every weekend. If the current user does not have the permission to access the WEEKEND_UPDATE_ OK table, he or she needs to re-grant the permission.

CREATE OR REPLACE TRIGGER updata_on_weekends_checkBEFORE UPDATE OF sal ON EMPFOR EACH ROWDECLAREmy_count number(4);BEGINSELECT COUNT(u_name)FROM WEEKEND_UPDATE_OK INTO my_countWHERE u_name = user_name;IF my_count=0 THENRAISE_APPLICATION_ERROR(20508,'Update not allowed');END IF;END;

Extended: triggers are divided into trigger beforehand and trigger afterwards. What is the difference between the two? What is the difference between statement-level triggering and row-level triggering?

Trigger occurred beforehand

Verify some conditions or make some preparations before the occurrence; trigger after the occurrence of the event, complete the work to ensure the integrity of the transaction. The previous and new field values can be obtained through pre-triggering. A statement-Level Trigger can be executed before or after the statement is executed, and a row-Level Trigger is triggered once for each row affected by the trigger.

What is an index?

An index is a mechanism to increase the speed of database queries. It is an object created in ascending or descending order of the database table or the value of a certain key field. When you query an index field, it can quickly perform the search operation. With the help of the index, you can quickly find the required data without scanning the entire table. An index is a disk structure associated with a table or view, that is, a structure that sorts the column values in a table. It can speed up row retrieval from a table or view, when performing a query, you do not need to scan the entire table to quickly access information in the database.

Create unique clustered index PK _ commodity on commodity (commodity) withpad_index, fillfactor = 10, drop_existing

An index record contains key values and logical pointers. When creating an index, the system allocates an index page. Insert a row of data into the table, and insert a row of index records into the index page. The index field value in the index record is smaller than the actual data volume, saving space.

The index types include focused index and non-focused index. Focusing indexes are the physical order of rows in a table, which is the same as the logical order of key values. A table can only have one focusing index. Compared with non-focused indexes, focused indexes generally provide faster data access speeds. Non-focused indexes are different from index storage. There is a pointer in the index, which points to the data storage location. The items in the index are stored in the order before the index, and the information in the table is stored in another order.

Creating an index can greatly improve the system performance, mainly in the following aspects:
(1) by creating a unique index, You can ensure the uniqueness of each row of data in the database table.
(2) Data Indexing can be greatly accelerated through Indexing
(3) using indexes can accelerate the connection between tables, so as to effectively implement reference integrity of data.
(4) Data Retrieval Using grouping and sorting clauses can significantly reduce the time for grouping and sorting in queries.
(5) by using the index, you can use the optimization hiding tool during the query process to improve the system performance.

Index can effectively improve query efficiency. Why not index all columns? In fact, although the index can bring convenience, the more the better, the more index pages will bring many unfavorable problems.
(1) Creating and maintaining indexes takes time and space. When the data volume is small, this problem is not prominent enough. When the data volume is large, this defect will be obvious and the efficiency will be very low.
(2) In addition to the data space occupied by data tables, each index also occupies a certain amount of physical space. If you want to create a clustered index, the required space will be larger, resulting in unnecessary space waste.
(3) When adding, deleting, and modifying data in a table, indexes must also be dynamically maintained, reducing the Data Maintenance speed.

What is rollback?

To ensure that the database can be restored after an error occurs in an application, database, or system, to ensure database integrity, rollback is required. Rollback means to restore the database data to the database data status before the transaction is committed.

Roll back and execute the opposite operation. You can cancel the wrong operation to ensure data integrity. For example, if user A transfers money to user B, you need to modify (update) the account information of user A and user B in the database, these two SQL statements must be executed either or not.

Pay attention to the difference between rollback and revocation. Rollback refers to restoring the database status to the status before the transaction is executed, where UNDO logs may be used for rollback. Undo is a way to record logs. It is not mainly used for transaction rollback, but mainly used for System Recovery from faults. For example, if the system suddenly loses power, the system must process unfinished transactions according to the UNDO log to ensure that the database status is the status before executing these transactions.

Types of data backup

Data backup is the backup of stored data to prevent data losses caused by disasters. It generally includes full backup, differential backup, transaction log backup, and Incremental backup.

(1) Full backup
Is to back up all the information in the database, it is the baseline for recovery, during the full backup, not only backup the database data files, log files, you also need to back up the storage location information of files and all objects and related information in the database. When the database is fully backed up, all unfinished transactions or transactions that occur during the backup process will be ignored. If the full database backup type is used, any changes made to the database during the period from the start of backup to the start of recovery will not be restored. Therefore, this backup type is used only under certain requirements or conditions.

(2) Differential backup
It is the modification made to the data after the recent full backup. It uses the full backup as the reference point, after the backup is complete, the changed data files, log files, and other modified content in the database are backed up. Differential backup takes less time than full backup, but it also takes some time. Just like full backup, differential backup also allows users to access the database and perform operations on the data, these operations are also backed up during differential backup.

(3) Transaction Log backup
It is the log record after the last backup. By default, the log is truncated after the transaction log is backed up. The transaction log backup records the user's modification operations on the data. Over time, the number of logs will increase, and the capacity is sometimes larger than the database backup, which will occupy the entire disk space. Therefore, in order to avoid this situation, unnecessary logs must be cleared regularly to save space. The process of clearing useless logs is called Log truncation.

(4) Incremental Backup
For the last backup, all files changed after the last backup are backed up. During the Incremental backup process, only the marked selected files and folders are backed up. The Marked files are cleared after backup.

Data Recovery corresponds to data backup. Data Recovery refers to the state before the accident, which can be considered as the inverse process of data backup operations. Data backup is the prerequisite for data recovery, and data recovery is the purpose of data backup. Data Backup that cannot be recovered is meaningless.

What is a cursor?

In a database, a cursor provides a flexible means to operate the data retrieved from a table. It is actually a mechanism that can extract a record from the result set that contains multiple data records.

A cursor is always associated with an SQL selection statement, because the cursor is retrieved from the result set (it can be zero, one, or multiple records retrieved by the relevant selection statement) and the cursor position in the result set pointing to a specific record. When processing a result set, a cursor pointing to the result set must be declared.

The cursor allows the application to perform the same or different operations on each row in the row result set returned by the select statement, rather than performing the same operation on the entire result set at a time. It also provides the ability to delete or update table data based on the cursor position. In addition, it is the cursor that links the database management system for collection and the row-oriented programming to enable communication between the two data processing methods.

What happens if the database logs are full?

Log Files record all changes to database data. They are mainly used to protect the database against faults and recover data. They have the following features:

(1) Each database must contain at least two Log File groups. Each log file group must contain at least two log file members.
(2) log file groups are written cyclically.
(3) Each log file member corresponds to a physical file.

Using Log files to record database transactions can ensure data consistency and security to the maximum extent possible. However, once the database is full of logs, you can only perform read operations such as queries, and cannot perform backup changes. The reason is that logs must be recorded for any write operation, that is, logs are basically unavailable.

How to determine who has inserted a row of data into the database

The following three methods are used to achieve this goal: Enable the audit function in advance, create a trigger on the table, or view logmnr.

(1) Audit Function
Set the audit_trail parameter to determine the location where the audit results are stored, and then run audit insert on schema. table_name whenever successful; when an insert operation is executed, the audit results can be viewed based on the audit_trail parameter.