Long time no write error summed up and tips the. Thought about something yesterday, and the database still quite feel the Origin, from the website to make the development of Android on a phone book, has not left the use of the database, although the database is a familiar piece of me at best, but a person mining potential is unlimited, so I decided after the main assault on the embedded system database applications.
Today, a database study of several of the more important concepts: paradigm, stored procedures, indexes, triggers. When the school only a few concepts to understand some of this fur, and there is no depth to learn that no battlefield, but in reality is his own shallow handsome.
1: Paradigm first paradigm: each column of the database table are integral and fundamental data items, the same column can not have more than one value, that entity can not have more than one attribute value or can not have duplicate attributes. If repeated properties, it may need to define a new entity, the new entity constituted by the repetition of the property, the new entity and the original as-many relationship between entities. In short, the first paradigm is no repeat of the column.
The second paradigm: the attributes required entities totally dependent on primary key. The so-called totally dependent is dependent on primary key can not exist only as part of the property, if it exists, then this property and this part of the main keyword should be separated to form a new entity, the new entity and the original entities is one to many relations. In short, the second paradigm is the part of non-primary attributes of non-dependent on primary key.
The third paradigm: requires a database table does not contain the table has been included in other non-primary key information. There is no transfer dependency.
2: stored procedure:
Frequently used or very complicated job, with the SQL statement written in advance with a specified name stored, then later to call database stored procedures and well defined functions of the same services, just call the execute, you can auto-complete command.
The advantages of stored procedures:
1. Stored procedure only when creating compiled, after every execution of stored procedures do not require re-compilation, and general SQL statement once for each implemented once compiled, so the database using Storage process which can increase execution speed.
2. When complex database operations (such as multiple tables Update, Insert, Query, Delete time), this complex operation can be packaged together with stored procedures and database transaction will join together to use.
3. Stored procedure can be reused, can reduce the workload of database developers.
4. Safe, can be set only before the user has a stored procedure on the right to use specified.
3: index (Note: SQL Server example)
Body content is itself a directory arranged according to certain rules known as "clustered index." Directory is purely directory, the body is simply the body of the sort referred to as "non-clustered index."
In fact, we can gather in front of an index and non-clustered index to understand the definition of the example. Such as: the data back to a certain range. For example one of your table has a time out, just you to aggregate index based on the column, when you query on Jan. 1, 2004 to October 1, 2004 all data between, this rate will very fast, because your body is the dictionary sort by date, the clustering index to retrieve only need to find all of the data in the beginning and end of the data can be; rather than non-clustered index, you must first find found in the directory corresponding to each data page, and then found according to the specific content page.
4: Trigger Trigger is a special stored procedure. There are three common triggers: were used in Insert, Update, Delete events.
Understand the trigger inside the two temporary tables: Deleted, Inserted. Note Deleted and Inserted tables, respectively, trigger events, "old record" and "a new record."
A database system, there are two virtual tables used to store records in the table change information, namely:
Inserted virtual table virtual table Deleted
New record store in the table when new records are not kept when stored records modify the new record for update before updating the record store to delete stored records when not kept a record is deleted