? database system Concepts 5-connections, views, and transactions

One, the connection
A) inner join, on, can be substituted with where, but with on to specify the join condition specifically, other conditions written in where are more readable.
b) Outer JOIN
For Ganso that have a null connection field, the inner join is not listed, outer join is different, and the Left/right join retains the empty connection field Ganso on the left (right) side. The process of the left join can be understood as executing the inner join first, and then adding the remaining ganso in the relationship to the result set.
In outer join, on and where are not interchangeable
c) left OUTER join in SQL Server can also be written, but generally omit inner, outer

Second, the view
A) The table in front of the learning process is actually present in the database, but sometimes the user does not need all the information of a table, and sometimes does not want the user to get additional information, such as sometimes only allow users to check the ID of the instructor table, name , or there are some complex queries we don't want to go over and over again every time. You can then use the virtual relation, which is the view, which is not part of the database's table, but can be accessed by the user.
b) Definition of the view
The notation is create view V as <query expression>; where query expression can be any query statement. For example, the previous instructor view can be defined like this:
CREATE VIEW faculty as
Select ID, Name
from instructor;
The user is then provided with access to the faculty view, which can be achieved.
c) When the view is defined, it can be used as if it were a real table, and the view can be nested. When using a view, the database executes the view's definition statement to generate the result set, so the contents of the view are real-time.
D) materialized view (materialized views)
Views are generally used to query the results in real time, although it can guarantee that the data is up-to-date, but the cost is large, in some cases of limited computing resources, or when the view is frequently used, and query speed requirements, the view result set can be saved, this view is called materialized view.
e) Update of the View
Views can be convenient for queries, but the update of views involves multiple issues, and the root cause of these problems is that changes to the view need to be reflected on the real table. Because allowing the view to appear anywhere the real table can appear, you can insert data for the previous faculty view:
INSERT into faculty values (30765, ' Green ');
However, instructor.salary can only insert null, instructor.salary if the provision is not allowed to be null, the operation will be blocked because of a violation of consistency. In addition, considering that the view is associated with multiple tables, the nesting of views, and so on, the problem becomes very complex, so it is usually not allowed to update views.

Iii. Business
A) The SQL standard specifies that the transaction form consists of a query, an UPDATE statement, which must be terminated with a commit work or a rollback, and the ' work ' can be omitted.
b) If an SQL statement can go wrong, it can be included in the transaction, which is similar to saving the edited document, and rollback is equivalent to undoing the changes made to the document. But once a transaction is commit, it can no longer be rollback. Transactions are atomic, and the operations contained by the transaction either succeed or all fail. If an accident occurs during the execution of the SQL statement, the system will be rolled back after the system restarts, as long as no commit has been performed.
c) In many database products, the default of each individual SQL is a transaction, the execution of automatic commit after the end.


Learning materials: Database System concepts, by Abraham Silberschatz, Henry F.korth, S.sudarshan

? database system Concepts 5-connections, views, and transactions