MySQL (9): Column Properties

---restore content starts---

1. First we look at this diagram:

1, null| Not NULL is empty

Specifies whether the value of a field is null

2. Default Value field Defaults property

It is common that a field cannot be empty and has a default value

CREATE TABLE PhP2 (
A int not null default 10,
b int not NULL default 21
);

INSERT into PHP2 (a) values (a); A default is 10
INSERT INTO PHP2 (b) values (b);//b default is 21

3, PRIMARY key |unique key (keyword)

Main Index | Unique index

A field that can uniquely identify a record, or a collection of fields , that is, the primary key

The primary key can be a property of a real entity

But the usual good solution :

Use a property that is not related to entity information as a unique identifier for a record (such as),

To set the primary key syntax:PRIMARY KEY complete

Two scenarios:

(1). Settings on the field:

CREATE TABLE Teacher (
t_id int primary Key,//The type of the primary key here is int, which means that the primary key can be positive or negative
T_name varchar (5),
Class_name varchar (6),
Days tinyint unsigned
);

The default cannot be null after T_ID is set as the primary key.

The primary key is the keyword used when searching :

(2). After the field has been defined, it can be defined as:

CREATE TABLE Teacher1 (
t_id int,
T_name varchar (5),
Class_name varchar (6),
Days tinyint unsigned,
Primary KEY (T_ID)
);

Below you can see clearly that t_id is no null

You can also set multiple fields to form a unique primary key (each table: The primary key is unique ), as follows:

CREATE TABLE Teacher2 (
t_id int,
T_name varchar (5),
Class_name varchar (6),
Days tinyint unsigned,
Primary KEY (t_id,class_name)//t_id and class_name two fields
);

4. Automatic growth

Purpose: To provide a unique indication for each record

Automatically increments the value of a field by 1 each time you insert a record;

Using the Auto_increment logo

An integral type is required, and an index is required ....

CREATE TABLE Teacher3 (
t_id int primary key auto_increment,
T_name varchar (5),
Class_name varchar (6),
Days tinyint unsigned
);

Add two statements:

INSERT into TEACHER3 values (null, ' Hebao ', ' 0228 ', 34);

INSERT into TEACHER3 values (null, ' Bufan, ' 0223 ', 45);

Such as: The discovery that this t_id is the only , auto-growing

ALTER TABLE Teacher3 Auto_inscrement 10;

INSERT into TEACHER3 values (null, ' Fei ', ' 0115 ', 32);

If this time we again:

ALTER TABLE Teacher3 Auto_increment 5;
INSERT into TEACHER3 values (null, ' Fly ', ' 0115 ', 98);

The result is: You'll find that the new additions are indexed with 11 ID.

Of course, the primary key can also be initialized

INSERT into TEACHER3 values (5, ' flrt ', ' 0123 ', 56);
The inserted element is behind the ' Bufan '

1

2

5

.

.

.

As long as the key value only does not conflict, it can also be modified, as follows:

Update Teacher3 set t_id=21 where t_name= ' Zhao a ' ;

---restore content ends---

MySQL (9): Column Properties