Third lecture design details
It is already the third saying, I don't know how to listen to the world ... If you say good, send flowers, say bad, lose your egg! I also made me Chair3 knew that there were a few people listened. Ok, nonsense is less, now begin:
Important:
1, constraint 2, default 3, calculating field 4, index
The above is the most common part of the database design and programming, listen to me will come one by one.
1, constraint.
constraint? What is constraint? That is, the value is limited to a certain field. Maintain the most important pureness of database data. First-class programmers should find a range of fill in a field. Forget it, theory does not say, give example:
Create Table People (Name Varchar (20) Not Null, - Name AGE INT NOT NULL CHECK (AGE> 0) - Age)
Everyone saw AGE INT NOT NULL CHECK (AGE> 0), and the CHECK (AGE> 0) is to prevent the user from accidentally fill in the value of <0. Haha, is there anything in the mother? Obviously the State Council does not have this regulations. Therefore, Age> 0 must be forced.
2, the default value.
What is the default value, I don't have to say it. In the data sheet design, try to avoid the field of NULL. Use the default value.
Still, an example is persuasive! Look:
Create Table People (Name Varchar (20) Not Null, - Name Sex Bit Not Null Default 1, - Sexy Age Int Not Null Check (AGE> 0) - Age)
did you see it? Sex bit NOT NULL Default 1, gender, also "male" or "female", using a number represents 1 or 0. In prevention of more situations (such as null), NOT NULL must be used. Take care of many lazy customers (it seems to be self), give him a "man"! Hey, after all, men and women are not flat, many places are many men. (Pain ...) This is just an example, and many places can be used, such as the date. Please try to avoid NULL, and NOT NULL DEFAULT can be used more purely your database.
3, calculate field
Excellent designers should know how to take into account the issues of future use. For example, in a student's table (I am from this example, I don't know the quantity of the Subject) Create Table Student (StudientId Int Primary Key, ...
Chinese Float Not Null Default 0, English Float Not Null Default 0, Mathematics Float Not Null Default 0 Sum as Chinese ENGLISH MATHEMATICS, AVERAGE AS (Chinese ENGLISH MATHEMATICS) / 3, ....)
I believe that there are many smart people, what is this good? The stomach is a bit hungry ... stick to it, write the 4th point to eat!
4, index this is the moster the moster and the moster and mosterorous and most mature and most mature part! ! The performance of the database depends on the design of the index. I will give you a roughly specifically of the species of the index: Cluster index, the cluster index and nonclustered index cluster index is usually created on the primary key, mainly created in these fields 1, primary key, and built 2, return a range Data, etc.
Non-cluster index is usually used for 1, messy data, many are different D 2, and data often want to change D
This means that everyone doesn't seem very well?
Come, come, meet the DEVCLU!
CREATE TABLE OPERATERECORD (- Operation Record)
OperateRecordid Int Primary Key, - Decoction Operatorid Int Not Null, - Operator ID Operation VARCHAR (100), - Operation OperateDate DateTime, - Time Memo Varchar (30) - Remarks)
For example, here, if you often ask the operator to query, the OperatorID should use a cluster index. If the operation content is often queried, then Operation should use a non-cluster index.
So: CREATE UNIQUE Clustered Index IDxOperateRecord_operateRecordIdID IN OperateRecord (OperateRecordid) Go Create Index IdxoperateRecord_operation On OperateRecord (Operation) GO
Use an index:
Select * from operaterecord with (IdxOperateRecord_operaterecordid) - Specifies the index query WHERE OperateRecordid BetWeen 1 and 20000 - Condition is OperateRecordidId. Order by OperateRecordId
Everyone knows? I am very hungry, I am not so hard: (: (Test prove, excellent index will greatly improve the speed of the query.
