Shulou(Shulou.com)06/01 Report--

Some database concepts:

1.1. PRIMARY KEY

What is a primary key: a unique key composed of one or more columns in a table, that is, a record can be uniquely identified by this one or more columns (normally implemented with one column).

Features: 1. Primary key column cannot contain null. 2. Primary keys are often set to integer, long integer, 3. and auto-increment AUTO_INCREMENT.

There can be no primary key in the table, but in general table design, there will be a primary key.

1.2 index

What an index does: it can be thought of as a catalog of a large dictionary for quick retrieval. Space for time, significantly improve query efficiency.

Side effects: delete, modify, add efficiency reduction

You can index one or more columns.

Index Category:

Primary key index: The primary key will automatically create a primary key index, and the primary key itself is used to quickly locate unique records.

Unique index: The index composed of indexes in the table must be unique, but can be null (NONE), and the non-null value must be unique.

General index: There is no requirement for uniqueness, just a directory of dictionaries.

1.3 Constraint

UNIQUE constraint (unique key constraint)

Defining a unique key index defines a unique key constraint

PRIMARY KEY constraint

When a primary key is defined, a primary key constraint is defined.

Foreign key constraint:

Foreign keys, columns in table B, associate primary keys in table A, columns in table B are foreign keys.

1. If you insert a piece of data into table B, the foreign key column of B inserts a value, which must be the primary key value existing in table A. The same is true for modifying the foreign key value of table B. The foreign key value must also exist in table A.

2. If table A wants to delete a record, it is equivalent to deleting a primary key. If table B references this primary key, you must delete the record in table B that references this primary key before deleting the record in table A. Otherwise, deletion fails.

3. Modify the primary key of table A. Because of the uniqueness of the primary key, the modified primary key is equivalent to inserting a new primary key. In this case, the primary key referenced by table B will prevent the primary key of table A from being modified. Only after deleting the relevant records of table B can the primary key of table A be modified.

Foreign key constraints, in order to ensure data integrity, consistency, eliminate data redundancy, data corruption.

Foreign key constraints are not easy to reuse.

1.4 view

View: Also called a virtual table, it looks like a table. It is generated by query statements. CRUD operations can be performed through views.

The role of the view:

Simplifying operations, defining complex query SQL statements as views can simplify queries. Data safety: Views can show only some columns of the real table, or calculated results, hiding the data of the real table.

(Views can be added, deleted, or modified. It is recommended that queries be performed using views. Views can be completed using tools.)

2. Data Type:

Data types in MYSQL:

Type:

Tinyint 1 byte, signed range-128 to 127, unsigned range 0 to 255. bool or bollean, is tinyint, 0 means false, non-0 means true Smailint 2 bytes, signed range is-32768 to 32767. unsigned range is 0 to 65535Int integer 4 bytes, same as integer, signed range-2147483648 to 2147483647. unsigned range is 0 to 4294967295 (maximum 10-bit ID) Bigint long integer, 8 bytes, Signed range is-9223372036854775808 to 9223372036854775807 unsigned range is 0 to 18446744073709551615 (20 bits) Float single-precision floating point numbers accurate to approximately 7 decimal places Double double-precision floating point numbers accurate to approximately 15 decimal places DATE date Supported range 1000-01-01 to 9999-12-31DATETIME Supported range 1000-01-01 00:00:00 to 9999-12-21 23:59:59TIMESTAMP timestamp, range 1970-01-01 00:00:00 to 2037 Char (M) Fixed length, right padding has reached the length requirement. M is the length and ranges from 0 to 255. M refers to the number of characters. Varchar(M) Variable length string. M represents the maximum column length. M ranges from 0 to 65535. But do not exceed the maximum number of bytes 65535Text large text. Maximum length 65535 (2^16-1)

BLOB large byte. BLOB column with maximum length of 65535 (2^16-1) bytes

The LENGTH function returns the number of bytes, while M defined by char and varchar is the character limit.

Char can make strings of equal length, space for time, slightly more efficient;varchar becomes longer, saving space.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.