Paradigm, stored procedures, indexes, triggers,

2010-08-27  来源:本站原创  分类:Database  人气:154 

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

相关文章
  • Paradigm, stored procedures, indexes, triggers, 2010-08-27

    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

  • sqlserver stored procedures and triggers a simple example 2010-11-15

    Sqlserver 2000 simple examples of stored procedures and triggers -- Used for testing table if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[TestTru]') and OBJECTPROPERTY(id, N'IsUserTable') = 1) drop table [dbo].[TestTru] GO CREA

  • sqlserver stored procedures and triggers the simple example 2010-11-15

    Simple sqlserver 2000 stored procedures and triggers an example -- Tables for testing if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[TestTru]') and OBJECTPROPERTY(id, N'IsUserTable') = 1) drop table [dbo].[TestTru] GO CREATE TA

  • Correct understanding of SQL views. Stored Procedures and Triggers 2011-03-21

    A view is a commonly used database objects, the query results to a virtual table stored in the data, not to store the data in the data set form. View of the results and content is based on a query based on table, and tables, including rows and column

  • sql server stored procedures and triggers 2010-07-21

    1. Create a SQL stored procedure: CREATE PROCEDURE stu_proc1 / / specify the process name @ Sno varchar (50) = 7000803068, / / define parameters and default values AS / / implementation of the SQL statement select * from Student where Sno = @ Sno GO

  • mysql stored procedures, functions, triggers an example 2010-12-02

    1, split a string with "|||" interval stored procedure. create PROCEDURE sp_InsertJoKe (lastindex int) begin declare i int; set i = 1; while (i <lastindex) do begin declare a varchar (4000); declare p int; declare part1 varchar (4000); set a

  • Stored procedures and triggers 2010-07-21

    Stored procedure: if exists (select [name] from sysobjects where [name]='Validate') drop proc Validate go create proc Validate as declare @baozhi varchar(50), @biaoti varchar(100), @riqi varchar(20) begin select @baozhi=' Qilu Wanbao ',@biaoti= Title

  • Create and use stored procedures 2010-06-02

    1. Create and use stored procedures 1. Create the package, and create the stored procedure in the program create or replace PACKAGE NCS_ICP_TJ AS / * [email protected] * / / * TODO enter the package declaration (type, exception error, method, etc.

  • DB2 stored procedures - based Xiangjie (change) 2010-08-12

    Learn DB2 9.5 SQL Procedural Language, including variables, conditions, procedures, statements, control flow and iterative statements, and error handling mechanism. You will: Learn the basic elements of SQL PL. Understand how to declare variables, co

  • About db2 stored procedures 2011-01-18

    DB2 SQL Procedural Language (SQL PL) SQL Persistent Stored Module is a subset of the language standard. The standard combines the convenience of SQL to access data and programming language flow control. SQL PL statements by the current set of languag

  • Mysql triggers and stored procedures review the basic grammar 2011-05-12

    Do not operate them all day long, somewhat forgotten, review, and notes about mysql triggers, stored procedures; content transferred from another site, belongs to original author. A trigger 1. Syntax : Naming CREATE TRIGGER < Trigger name > <-- {

  • mysql view all stored procedures, functions, views, triggers, table 2011-05-27

    Query the database stored procedures and functions Method One: select `name` from mysql.proc where db = 'your_db_name' and `type` = 'PROCEDURE' // Stored procedure select `name` from mysql.proc where db = 'your_db_name' and `type` = 'FUNCTION' // Fun

  • Oracle database using sql query to get all the triggers, all stored procedures, all views, all the tables 2011-03-02

    Oracle Using a SQL query to get the database of all triggers , All stored procedures, all views , All tables Select object_name From user_objects Where object_type='TRIGGER'; -- All triggers Select object_name From user_objects Where object_type='PRO

  • Views, stored procedures, triggers benefits + disadvantages 2011-03-26

    View of the advantages: Improve data security, you can not let the user see the table in a field. Such as the password, you only give them permission to execute the view, do not give permission to execute the table, they can not see all the data. The

  • Create table, insert data, query, create triggers, stored procedures 2011-04-29

    - Construction of database create database school - Construction of the table use school create table students ( s_id int identity (1,1) primary key, - set the primary key is auto-incremented id s_name varchar (20) not null, s_classId int ) create ta

  • Mysql triggers and stored procedures review basic grammar 2011-05-12

    These do not operate all day long, a little bit forgotten, review and notes about mysql triggers, stored procedures; content transferred from another site, belongs to original author. A trigger 1. Grammar : Naming CREATE TRIGGER < Trigger name > <

  • PLSQL (Syntax - Exception Handling - cursor - Stored Procedures - Triggers - oracle page) 2011-07-08

    "PL / SQL Programming" / * Procedural language / sql * / - 1, procedures, functions, triggers a pl / sql written - 2, procedures, functions, triggers in oracle in - 3, pl / sql is very powerful database procedure language - 4, procedure, functio

  • oracle triggers stored procedures. functions. package 2010-09-09

    1. Oracle trigger to use create or replace trigger tri_userid before insert on t_user for each row declare new_key number; begin select seq_userid.nextval into new_key from dual; :new.id:=new_key; end; 2 Stored Procedures create or replace procedure

  • Views, stored procedures, triggers the benefits of 2011-10-13

    View of the advantages: Improve data security, you can let the user see the table in a field. Such as password, you only give them permission to execute the view, do not give permission to perform the table, they can not see all the data. There you c

  • Detailed stored procedures [Reserved] 2010-12-12

    Stored procedure can make the management of the database, and display user information on the database and its work much easier. A stored procedure is SQL statements and optional control-flow statements of pre-compiled collection, to a name stored an