MySQL Administrator's Bible- P8

MySQL Administrators Bible- P8: Với tập trung đặc biệt vào việc phát hành lớn tiếp theo của MySQL, nguồn tài nguyên này cung cấp một khuôn khổ vững chắc cho bất cứ ai mới vào MySQL hoặc chuyển từ một nền tảng cơ sở dữ liệu, cũng như các quản trị MySQL kinh nghiệm. Các bộ đôi tác giả cao hồ sơ cung cấp bảo hiểm thiết yếu của các nguyên tắc cơ bản của phạm vi bảo hiểm cơ sở dữ liệu quản lý, bao gồm cả cách tiếp cận độc đáo MySQL của các tính năng cơ sở...
Nội dung trích xuất từ tài liệu:
MySQL Administrators Bible- P8 MySQL Views 8Note that updating the view presents a logical error. Even if Benjamin had privileges to the basetables, updating the view would generate no warning or error, but produces incorrect results.Changing a View DefinitionThere are two ways to change a view. One method has already been discussed — using theCREATE OR REPLACE when defining a view. In addition, MySQL has an ALTER VIEW command.ALTER VIEW works much like ALTER TABLE. The SELECT statement that defines the view mustalways be included in the ALTER VIEW statement, even if that part of the view definition is notbeing modified.You may have noticed that in the CREATE VIEW statement, four different clauses may comebetween the words CREATE and VIEW: CREATE [OR REPLACE] [ALGORITHM = {UNDEFINED | MERGE | TEMPTABLE}] [DEFINER = { user | CURRENT_USER} ] [SQL SECURITY { DEFINER | INVOKER }] VIEW view_name [(column_list)] AS select_statement [WITH [CASCADED | LOCAL] CHECK OPTION]Similarly, the syntax of the ALTER VIEW statement is: ALTER [ALGORITHM = {UNDEFINED | MERGE | TEMPTABLE}] [DEFINER = { user | CURRENT_USER} ] [SQL SECURITY { DEFINER | INVOKER }] VIEW view_name [(column_list)] AS select_statement [WITH [CASCADED | LOCAL] CHECK OPTION]Only the view’s definer or a user with the SUPER privilege can ALTER a view.Replication and ViewsIn both row- and statement-based replication, MySQL treats a view the same way it treats a basetable. In statement-based replication, CREATE VIEW, ALTER VIEW, and DROP VIEW statementsare written to the binary log, and thus replicated. In row-based replication, the underlying datais replicated.The replicate-do-table and replicate-ignore-table replication options are applied toviews and tables in both statement- and row-based replication, with the following outcomes: 317Part II Developing with MySQL ■ A view that matches a replicate-do-table pattern will be written to the binary log, and thus replicated. ■ A view that matches a replicate-ignore-table pattern will not be written to the binary log, and thus not be replicated. ■ replicate-do-table and replicate-ignore-table patterns match the object name only. Therefore, those options will only be applied to views matching the pattern — even if the view references a matching table name. Summary In this chapter, we have described: ■ How to create, change, and drop views ■ Invalid SELECT statements in view definitions ■ Using views to limit field and row data for security purposes ■ How views can simplify and abstract queries ■ Performance implications of views ■ Using views as check constraints ■ How to update underlying base tables using an updatable view ■ Reasons a view may not be updatable ■ Logical errors that may occur when updating data using an updatable view ■ How replication handles views 318 Transactions in MySQLW hen reading about relational database management systems (RDBMSs), you will see the terms transaction and ACID IN THIS CHAPTER compliance. A transaction is a set of SQL statements that are Understanding ACIDexecuted as if they were one statement. For a transaction to be finished complianceand save data changes permanently, all the statements in the transactionhave to be completed. If a transaction is not completed for any reason, Using transactional statementsthe changes to the dataset that the transaction already made are removed, Using isolation levelsplacing the database in its original state before the transaction began. Explaining locking andA transaction is a transaction only if it is ACID-compliant. ACID is an deadlocksacronym that stands for atomicity, consistency, isolation, and durability. Aproper implementation of these properties guarantees reliable processing of Recovering MySQL transactionsdatabase transactions. The properties of ACID are explained in detail in thenext section.To begin understanding what transactions are and why they are important,it will be helpful to walk through an example of how transactions are used.The classic transaction example is the database system used by a bank.Consider the following situat ...