MySQL (iii)

1 Field Properties

• Primary key, unique key, and self-growth.

1.1 Primary Key

• Primary key: Primary key, only one field in a table can use the corresponding key, which is used to constrain the data within the field and cannot be duplicated.
• A table can have up to one primary key.

1.1.1 Add primary key

• There are several ways to add a primary key to a table in SQL operations in Central Europe, broadly divided into three types:

• Scenario 1: When creating a table, directly after the field, follow the primary key keyword (the primary key itself cannot be empty).

-- Add primary Key Create Table My_pri (   intprimarykey,   varchar(  not NULL ' name ' ) charset UTF8;

• • Pros: very straightforward; disadvantage: only one field can be used as the primary key.

• Scenario 2: When creating the table, after all the fields, use primary key (the primary key field list) to create the primary key, and if there are multiple fields as primary keys, it can be a composite primary key.

Create TableMy_pri2 ( Number Char(Ten) Comment'School Number', CourseChar(Ten) Comment'Course Code: 3901+0000', scoretinyintUnsigneddefault  -Comment'Achievements',   --increase the primary key limit: The study number and course code should be unique   Primary Key( Number, course)) CharSet UTF8;

• Scenario 3: After the table has been created, append additional primary keys: You can either Modify table field properties or append directly.

Alter Table Add Primary Key (field list);

• Premise: The data in the table is independent (not duplicated).

1.1.2 PRIMARY KEY constraints

• Data in the field corresponding to the primary key is not allowed to repeat, and once repeated, the data operation fails (increment and that).

1.1.3 PRIMARY key update & Delete primary key

• There is no way to update the primary key: The primary key must be deleted before it can be incremented.

Drop Table Drop key;

1.1.4 PRIMARY KEY classification

• In the process of actually creating tables, it is very rare to use real business data as the primary key field (business key, such as school number, course number).
• Most of the time, the logical field is used (the field has no business meaning, the value is nothing), and the primary key of this field is called the logic primary key.

1.2 Auto-growth

• Self-growth: when the corresponding field, do not give a value, or give the default value, or give null, will automatically be triggered by the system, the system will be from the current field of the maximum value of +1 operation, to get a new different field.

• Usually auto-grow and primary key collocation.

1.2.1 Added self-growth

• Self-growth features: auto_increment
  • Any field to be self-grown must be an index (the key column has a value).

• • Self-growth must be a number.

• • A table can have a maximum of one self-growth.

1.2.2 Self-growing use

• Auto-growth is triggered when the value given by the self-growth is null or the default value.

1.2.3 Modify self-growth

• Self-growth if it involves field changes: You must first delete the self-growth, then increase (a thin sheet can only have one self-growth).

• Modify the value that the current self-growth already exists: the modification can only be larger than the current self-growth maximum value, and cannot be laughed (small does not take effect).

-- modifying the values of table options Alter Table = value;

• Thinking: Why is self-growth starting from 1? Why does it increase by 1 each time?
  • The monetization of all systems, such as character sets and proofing sets, is controlled by variables within the system.
  • View the variables corresponding to the growth: show variables like ' auto_increment% ';

• Variables can be modified to achieve different effects, but modifications are made to the entire database, not to a single table, and are not recommended for modification.

-- modification is not recommended Set = 5;

1.2.4 Delete self-growth

• Self-growth is a property of a field: it can be modified by modify (guaranteed field is not auto_increment)

Alter  Table name modify field type;

1.3 Unique Keys

• A table often has a number of fields that need to be unique, data cannot be duplicated, but only one primary key can be in a table, so a unique key solves a constraint that has multiple fields in the table that need to be unique.

• The nature of the unique key is almost the same as the primary key, with the unique key default allowed to be empty automatically, and multiple nullable.

1.3.1 Adding unique Keys

• Basically the same as the primary key: three kinds of programs.

• Scenario One: When creating a table, the fields are followed directly by Unique/unque key.

• Scenario Two: Add a unique key (field list) after all fields;--composite Unique key

• Scenario Three: Add a unique key after creating the table.

MySQL (iii)