Basic concepts of databases: mandatory for interviews, and basic concepts of databases

Basic concepts of databases: mandatory for interviews, and basic concepts of databases

Today, I will share with you the basic concepts related to database collection, which will be very helpful for your future work.

1. superkeys, candidate keys, primary keys, and foreign keys

Superkey: the attribute set that uniquely identifies a tuples in a link is called the superkey of The Link mode. An attribute can be used as a super key, and multiple attributes can be combined as a super key. The superkeys include candidate keys and primary keys.

Candidate Key: it is the smallest super key, that is, the super key without redundant elements.

Primary Key: A combination of data columns or attributes that are unique and fully identified for the stored data objects in the database table. A data column can only have one primary key, and the value of the primary key cannot be missing, that is, it cannot be Null ).

Foreign key: The primary key of another table that exists in one table is called the foreign key of this table.

2. What is a transaction? What is a lock?

Transaction: it is the SQL statement group bound together as a logical unit of work. If any statement operation fails, the entire operation will fail, and subsequent operations will roll back to the pre-operation status, or there is a node on it. Transactions can be used to ensure that they are either executed or not executed. To consider a group of statements as a transaction, ACID testing is required, that is, atomicity, consistency, isolation, and durability.

Locks: in DBMS, locks are the key to implementing transactions. Locks can ensure transaction integrity and concurrency. Like a lock in real life, it can make the owner of some data unable to use some data or data structures for a certain period of time. Of course, the lock is classified.

3. Four features and meanings of database transactions

Atomicity: All the operations in the entire transaction are either completely completed or not completed, and it is impossible to stop at a stage in the middle. When a transaction encounters an error during execution, it will be rolled back to the state before the start of the transaction, just as this transaction has never been executed.

Consistency: The integrity constraints of the database are not damaged before and after the transaction starts.

Isolation: the isolation status executes transactions, making them seem to be the only operations performed by the system within a given time. If two transactions run at the same time and perform the same functions, the isolation of the transaction will ensure that each transaction is considered to be only the transaction in the system. This type of attribute is sometimes called serialization. To prevent confusion between transaction operations, you must perform serialization or serialization requests so that only one request is used for the same data at the same time.

Durability: after the transaction is completed, the changes made by the transaction to the database are permanently stored in the database and will not be rolled back.

4. What is a view?

A view is a virtual table with the same functions as a physical table. You can add, modify, query, and operate a view to try to use a subset of rows or columns of one or more tables. Modifying a view does not affect the basic table. It makes it easier for us to obtain data than to query multiple tables.

Views are generally used in the following two scenarios:

(1) If you do not want the visitor to obtain the information of the entire table, but expose some fields to the visitor, a virtual table is created, which is a view.

(2) The queried data comes from different tables, and the queryer wants to query the data in a unified manner. In this way, a view can be created to combine the query results of multiple tables, the queryer only needs to obtain data directly from the view without considering the differences between data sources from different tables.

Note: This view is created in the database instead of using code.

5. What is the role of a trigger?

A trigger is a special stored procedure and is executed mainly through events. It can enhance constraints to maintain data integrity and consistency, and track operations in the database so that unauthorized updates and changes are not allowed. Cascade operations are supported. For example, a table trigger contains data operations on another table, which triggers the table trigger.

6. Maintain database integrity and consistency. Do you like to use triggers or write business logic? Why?

Use constraints such as check, primary key, foreign key, and non-empty fields as much as possible. This is the most efficient and convenient. The second is to use triggers. This method ensures that data integrity and consistency can be ensured for any business system to access the database. The last consideration is the self-writing business logic, but this is troublesome, complicated programming, and inefficient.

7. What is the role of indexes? And what are its advantages and disadvantages?

Database index is a Sort data structure in the database management system. It helps you quickly query and update data in database tables. The implementation of indexes usually uses the B tree and Its Variant B + tree.

In addition to data, the database system also maintains data structures that meet specific search algorithms. These data structures reference (point to) data in a certain way, in this way, you can implement advanced search algorithms on these data structures. This data structure is an index.

The cost for setting indexes for a table is: first, the storage space of the database is increased, second, it takes a lot of time to insert and modify data (because the index also needs to change ).

Creating an index can greatly improve the system performance (advantages ):

First, you can create a unique index to ensure the uniqueness of each row of data in the database table.

Second, it can greatly speed up data retrieval, which is also the main reason for creating an index.

Third, it can accelerate the connection between tables, especially in achieving Data Reference integrity.

Fourth, when you use grouping and sorting clauses to retrieve data, you can also significantly reduce the time for grouping and sorting in queries.

Fifth, by using indexes, you can use the optimizer during the query process to improve system performance.

Some may ask: why not create an index for each column in the table because increasing Indexes has so many advantages? Because adding Indexes has many disadvantages:

First, it takes time to create and maintain indexes. This time increases with the increase of data volume.

Second, indexes occupy physical space. In addition to data tables, each index occupies a certain amount of physical space. To create a clustered index, the required space is larger.

Third, when adding, deleting, and modifying data in the table, the index must also be dynamically maintained, which reduces the Data Maintenance speed.

Indexes are created on certain columns in the database table. When creating an index, you should consider which columns can create an index and which Columns cannot create an index.

In general, you should create an index on these columns:

(1) you can speed up the search on columns that frequently need to be searched;

(2) force the uniqueness of the column as the primary key and the data arrangement structure in the organization table;

(3) These columns are often used in connection columns. These columns are mainly foreign keys, which can speed up the connection;

(4) Create an index on a column that often needs to be searched by range. Because the index has been sorted, the specified range is continuous;

(5) Create an index on the columns that frequently require sorting. Because the index has been sorted, you can use the index sorting to speed up the sorting query time;

(6) create indexes on columns in the WHERE clause frequently to speed up condition judgment.

Similarly, indexes should not be created for some columns:

First, indexes should not be created for columns that are rarely used or referenced in queries. This is because, since these columns are rarely used, there is an index or no index, and the query speed cannot be improved. On the contrary, the addition of indexes reduces the system maintenance speed and space requirements.

Second, indexes should not be added to columns with only few data values. This is because these columns have very few values, such as gender columns in the personnel table. In the query results, the data rows in the result set account for a large proportion of the data rows in the table, that is, the proportion of data rows to be searched in the table is large. Adding indexes does not significantly accelerate the search speed.

Third, indexes should not be added for columns defined as text, image, and bit data types. This is because the data volume of these columns is either large or small.

Fourth, when the modification performance is far greater than the retrieval performance, you should not create an index. This is because the modification performance and retrieval performance are inconsistent. When an index is added, the search performance is improved, but the modification performance is reduced. When the index is reduced, the modification performance is improved and the retrieval performance is reduced. Therefore, when the modification performance is much higher than the retrieval performance, you should not create an index.

8. Differences between drop, delete and truncate

Drop directly deletes the table.
Truncate deletes the data in the table, and the auto-increment id starts from 1 when the data is inserted.
Delete: delete the data in the table. You can add the where clause to delete the data in the table.

(1) The DELETE statement deletes a row from the table each time, and saves the DELETE operation of the row as a transaction record in the log for rollback. Truncate table deletes all data from the TABLE at a time and does not store the delete operation records in logs. Deleting rows cannot be recovered. In addition, table-related deletion triggers are not activated during the deletion process. Fast execution speed.

(2) space occupied by tables and indexes. When a table is TRUNCATE, the space occupied by the table and index will be restored to the initial size, and the DELETE operation will not reduce the space occupied by the table or index. The drop statement releases all the space occupied by the table.

(3) In general, drop> truncate> delete

(4) application scope. TRUNCATE can only be set to TABLE. DELETE can be set to table or view.

(5) TRUNCATE and DELETE only DELETE data, while DROP deletes the entire table (structure and data ).

(6) truncate and delete without where: delete data only, but not the table structure (Definition) drop statement will delete the constraints (constrain) on which the table structure is dependent ), trigger index; the stored procedure/function dependent on the table will be retained, but its status will change to: invalid.

(7) When the delete statement is DML (data maintain Language), this operation will be placed in the rollback segment and take effect after the transaction is committed. If there is a corresponding tigger, it will be triggered during execution.

(8) truncate and drop are DLL (data define language). The operation takes effect immediately. The original data is not stored in rollback segment and cannot be rolled back.

(9) exercise caution when using drop and truncate without backup. To delete some data rows, use delete and use where to restrict the impact scope. The rollback segment must be large enough. To delete a table, use drop. If you want to retain the table and delete the table data, use truncate if it is unrelated to transactions. If it is related to the transaction or the instructor wants to trigger the trigger, delete is used.

(10) Truncate table names are fast and efficient because:
The truncate table function is the same as the DELETE statement without the WHERE clause: both DELETE all rows in the table. However, truncate table is faster than DELETE and uses less system and transaction log resources. The DELETE statement deletes a row at a time and records one row in the transaction log. Truncate table deletes data by releasing the data pages used to store TABLE data, and only records the release of pages in transaction logs.

(11) truncate table deletes all rows in the TABLE, but the TABLE structure and its columns, constraints, and indexes remain unchanged. The Count value used by the new row ID is reset to the seed of the column. To retain the ID Count value, use DELETE instead. To delete TABLE definitions and data, use the drop table statement.

(12) for tables referenced by the foreign key constraint, the truncate table cannot be used, but the DELETE statement without the WHERE clause should be used. Because the truncate table is not recorded in the log, it cannot activate the trigger.

9. Common SQL commands:

Create table Student (id number primary key, NAME VARCHAR2 (50) not null); // create table create view view_name AS Select * FROM Table_name; // Create unique index index_name ON TableName (col_name); // Create an index insert into tablename {column1, column2 ,...} Values (exp1, exp2 ,...); // Insert into Viewname {column1, column2 ,...} Values (exp1, exp2 ,...); // Insert View actually affects the table UPDATE tablename SET name = 'zang 3' condition; // UPDATE data delete from Tablename WHERE condition; // delete GRANT (Select, DELETE ,...) ON (object) TO USER_NAME [with grant option]; // authorize REVOKE (permission table) ON (object) FROM USER_NAME [with revoke option] // REVOKE permission

List the names of the staff and their leaders:

Select E.NAME, S.NAME FROM EMPLOYEE E S WHERE E.SUPERName=S.Name 

The above content is the basic concepts of the database that I want to share with you!

Articles you may be interested in:

• Several confusing concepts in XML databases
• In-depth discussion: Concepts of solutions in oracle and the relationship between solutions and databases
• DBA_Oracle Startup/Shutdown (concept) (various maintenance operations on the database)