In a database, a data table consists of three parts: the table name, the fields in the table, and the records of the table. Before creating a table, you must first design its structure. The table structure describes the framework of a table. Designing the table structure actually means defining the number of fields that make up a table, and the name, data type, length and other information of each field.
Before creating a table, you must first design its structure. The table structure describes the framework of a table. Designing the table structure actually means defining the number of fields that make up a table, and the name, data type, length and other information of each field.
Designing the data table structure is to define the data table file name, determine which fields the data table contains, the field name, field type, and width of each field, and input these data into the computer.
Design table structure
1. Determine the table name. The table name must be unique, consistent with its purpose, concise, intuitive, and meaningful.
2. Determine the field name.
⑴The field name length is less than 64 characters.
⑵Field names can include letters, Chinese characters, numbers, spaces and other characters.
⑶Field names cannot include periods (.), exclamation points (!), square brackets ([]) and accent marks (,).
⑷Field names cannot start with leading spaces.
3. Determine the field type. Access provides 10 data types to meet the different needs of fields.
4. Determine the field attributes. Such as field size, format, default value, required fields, validation rules, validation text and index, etc.
5. Determine the only primary key field in the table that can identify the record, that is, the primary key.
Data types
The following 10 data types are commonly used in databases.
Text type
Text type is the default data type, with a maximum length of 255 characters and a default length of 50 characters. You can set the maximum number of characters allowed in a Text field by setting the FieldSize property. When the text contains Chinese characters, each Chinese character only occupies one character. If the length of the input data does not exceed the defined field length, the system only saves the characters entered into the field, and the content of the unused positions in the field is not saved. Text type is usually used to represent text or numbers that do not require calculation, such as name, address, student number, and zip code.
Remark type
The remark type allows storage of up to 65,535 characters. It is essentially the same as text data and is suitable for storing things. Detailed description information, such as resume, notes and summary, etc.
Numeric type
consists of numbers 0~9, decimal point and positive and negative signs. It is used for data for arithmetic operations. Numerical fields are subdivided into integers. , long integer, byte type, single precision type and double precision type, the length is set by the system to 2, 4, 1, 4, 8 bytes respectively.
The system default numeric field length is long integer. The number of decimal places is accurate to 7 digits for single-precision type and 15 digits for double-precision type. Byte type can only store integers from 0 to 255.
Date and time type
is used to represent any combination of date and time between 100 and 9999. The storage and display format of date/time data depends entirely on the user-defined format. According to different storage and display formats, it is divided into regular date, long date, medium date, short date, long time, medium time and short time. The system default length is 8 bytes.
Currency type
is used to store currency values. When entering data into this field, the system will automatically add currency symbols and thousand separators. The storage and display format of currency data depends entirely on the user-defined format. According to different storage and display formats, it is divided into types such as regular data, currency, euro, fixed and standard.
The maximum length of the integer part of currency data is 15 digits, and the length of the decimal part cannot exceed 4 digits.
Auto numbering type
is used to store incremental data and random data. When adding a record to the table, the system assigns a unique sequence number to the field. There are two ways to determine the sequence number, namely incremental and random.
The incremental method is the default setting. Every time a new record is added, the value of this field is automatically increased by 1.
When using the random method, each time a new record is added, the data in this field is designated as a random long integer data.
Once the value of this field is specified by the system, it cannot be deleted or modified. Therefore, for tables containing this type of field, you should pay attention to the following issues when operating:
(1) If you delete a record, the value of the field in other records will not be adjusted;
(2) If a new record is added to the table, the field will not use the value that has been used in the deleted record;
(3) The user cannot specify or modify the value of this field.
Only one automatic numbering field is allowed in each data table, and its length is set by the system to 4 bytes, such as sequence number, product number, code, etc.
Yes\No type
Data used to determine whether the logical value is true or false, expressed as Yes/No, True/False or On/Off. The field length is set by the system to one byte. Such as whether to pass, whether to get married, etc.
OLE Object type
OLE (Object Linking and Embedding, object linking and embedding) is used to link or embed objects created by other applications. For example, if you embed sounds, pictures, etc. in the database, its size can reach 1GB.
The linking and embedding methods can be selected when entering data. The link object is to insert a picture representing the file content into the document. Only the link between the picture and the source file is saved in the database, so that the source file is Any changes made to the file can be reflected in the document; embedded objects insert the contents of the file into the document as an object, and the object is also saved in the database. At this time, the inserted object has nothing to do with the source file.
Hyperlink type
is used to store hyperlink addresses, linked to the Internet, LAN or local computer, and the size does not exceed 2048 bytes.
Lookup wizard type
is used to create lookup wizard fields. Users can use list boxes or combo boxes to check the values of other tables or other fields in this table. Generally 4 bytes.
Related tutorial recommendations: "Database Video Tutorial", "PHP Tutorial"
The above is the detailed content of What does a data table in a database consist of?. For more information, please follow other related articles on the PHP Chinese website!