1, Common field type classification  

Common field types

type	classification	describe
Numerical type	integer
	decimal	① Fixed point number ② Floating point number
character	Shorter text	①char ②varchar
	Long text	①text ② Blob (binary data)
Date time type

1.1 numerical type  

1.1.1 integer

characteristic:

1. The integer type is signed by default. If you want to set it to unsigned, you need to add it after the integer type   UNSIGNED
2. If the inserted value exceeds the integer range, an error will be reported (out of range), and the inserted value is a critical value (see the database version for details)
3. If the length is not set, there will be a default length

integer

Integer type	byte	Signed and unsigned representations	Range
Tinyint	1	Signed Tinyint	-128~127
		Unsigned Tinyint   UNSIGNED	0~255
Smallint	2	Signed Smallint	-32768 ~ 32767
		Unsigned   Smallint   UNSIGNED	0~65535
Mediumint	3	Signed Mediumint	-8388608 ~ 8388607
		Unsigned Mediumint   UNSIGNED	0 ~ 1677215
Int integer	4	Signed Int	-2147483648 ~ 2147483647
		Unsigned Int   UNSIGNED	0~ 4294967295
Bigint	8	Signed Bigint	-9223372036854775808~9223372036854775807
		Unsigned Bigint   UNSIGNED	0 ~ 2**64-1

1.1.1.1 set unsigned and signed for integer fields  

Syntax: integer type unsigned     For example: INT UNSIGNED

1.1.1.2 meaning of field type length

Description: when creating a table, the length of the field is usually set when setting the field type. The length here does not refer to the representation range of the value of the field. The representation range of the field is determined by the type of the field. The length here actually refers to the display width of the data, that is, when the width is not enough, 0 will be added to the left of the value.

For example: field type (length)   ZEROFILL

Note: ZEROFILL automatically converts numeric values to unsigned integers

As follows:

1.1.2 decimals  

characteristic:

1. M represents   : Integer part + decimal part
2. D means decimal position
3. When out of range (when integer part length   > M-D   )， The specific error warning depends on the specific version   mysql8 version is   When integer part length   > M-D     Will report an error
4. Both M and D can be omitted. If decimal omits m and D, m defaults to 10 and D defaults to 0, that is, only integer parts will be retained; float and double determine the accuracy according to the range
5. The precision of fixed-point type is high. If the precision of the inserted value is required to be high, the fixed-point type, such as currency operation, is used

1.1.2.1 floating point number type

Floating Point Types

Floating Point Types	byte	Range
float(M,D)	4	±1.75494321E-38 ~ ±3.402823466E+38
double(M,D)	8	±2.2250738585072014E-308 ~ ±1.7976931348623157E+308

1.1.2.2 fixed point number type (higher precision)

characteristic:

Fixed point number type

Fixed point number type	byte	Range
DEC(M,D) decimal(M,D)	M+2	The maximum value range is the same as double, and the effective value range of a given decimal is determined by M and D

1.2 character type  

1.2.1 short text

Shorter text

Character type	Maximum number of characters	describe	characteristic	Space utilization	Time efficiency
char(M)	M  M can be omitted and is 1 by default	M is an integer between 0 and 255	Fixed length characters	It consumes more space, and the space utilization rate is low	high
varchar(M)	M M cannot be omitted	M is an integer between 0 and 65535	Variable length characters	It saves space and has high space utilization	low

1.2.1.1 char

1.2.1.2 varchar

1.2.2 long text

1.2.2.1 text

1.2.2.2 blob

1.2.3 other strings

Other strings

Character type	classification	describe
Binary string	binary Save a shorter binary string	Similar to char and varchar, the difference is binary. Varbinary only contains binary strings and does not contain non binary strings
	varbinary Save long binary string
enum type	Specific usage example 1	Also known as enumeration type The value required to be inserted must belong to one of the values specified in the list For example, if the list member is 1 ~ 255, only 1 byte storage is required
set type	Specific usage example 2	Similar to enum type, it can hold 0 ~ 64 members The biggest difference between set type and enum type is that set type can select multiple members at a time, while enum type can only select one member Depending on the number of members, the bytes occupied by storage are also different

Example 1:

Example 2:

one point three   Date time type  

Date time data

Date time type	byte	minimum value	Maximum
date	4	1000-01-01	9999-12-31
datatime	8	1000-01-01 00:00:00	9999-12-31 23:59:59
timestamp	4	19700101080001	Sometime in 2038
time	3	-838:59:59	838:59:59
year	1	1901	2155

The difference between datetime and timestamp

1. Timestamp supports a small time range, with a value range of 1970011080001   To 2038   A time of year; value range of Datetime:   1000-1-1 to   9999-   12-31

2. timestamp is related to the actual time zone and can better reflect the actual date, while datetime can only reflect the local time zone at the time of insertion   For example: Example 1

3. The timestamp attribute is greatly affected by the version of Mysq and SQLMode

Example 1:

 

2, Constraint type

Description: a constraint condition when creating a table structure. It is used to limit the data in the table. The purpose is to ensure the reliability and accuracy of inserted data  

Classification (six constraints):

1. NOT   NULL: non NULL, used to ensure that the value of this field cannot be NULL
2. DEFAULT: DEFAULT, used to ensure that the field has a DEFAULT value
3. PRIMARY   KEY: primary KEY, used to ensure that the value of this field is unique and non empty
4. UNIQUE: UNIQUE. It is used to ensure that the value of this field is UNIQUE. It can be blank
5. CHECK: CHECK constraints [not supported in mysql]
6. FOREIGN   KEY: foreign KEY. It is used to restrict the relationship between two tables. It is used to ensure that the value of this field must come from the value of the associated column of the primary table. When adding a foreign KEY constraint from the secondary table, it is used to reference the value of a column in the primary table

When to add constraints:

1. When creating a table
2. When modifying a table

Add classification of constraints

1. Column level (field) constraints: the six constraints are syntactically supported, but foreign key constraints have no effect
2. Table level constraints: except non empty (NOT)   NULL), default (DEFAULT), and others are supported

CREATE TABLE Table name(
Field name field type column level constraint,
Field name field type column level constraint,
...
Field name field type column level constraint,
Table level constraints
);

The unique difference between primary key and primary key:  

Primary key and unique difference

Constraint type	Guarantee uniqueness	Allow null	Are there multiple	Allow combination (Union key)
Primary key   PRIMARY   KEY	√	×	There can be at most one primary key field	Yes (but not recommended)
only   UNIQUE	√	√	There can be multiple unique fields	Yes (but not recommended)

2.1 column level constraints

Syntax:

CREATE TABLE Table name(
Field name field type column level constraint column level constraint column level constraint...,
Field name field type column level constraint column level constraint column level constraint...,
...
Field name field type column level constraint column level constraint column level constraint...,
);

Note:

1. Only non empty, default, unique and primary key are supported
2. A field can have multiple constraints

Example 1:

CREATE TABLE stuinfo(
id INT PRIMARY KEY, #Primary key
stuname VARCHAR(20) NOT NULL,#Non empty
gender CHAR(1) CHECK(gender="male" OR gender="female"),#inspect
seat INT UNIQUE,#only
age TINYINT DEFAULT 18,#default
majorid TINYINT REFERENCES major(id) #Foreign key
);


CREATE TABLE major(
id TINYINT PRIMARY KEY,#Primary key
majorname VARCHAR(20)
);

2.2 table level constraints  

Syntax:

CREATE TABLE Table name(
Field name field type,
Field name field type,
...
Field name field type,


[CONSTRAINT Constraint name] constraint type(Field name),
[CONSTRAINT Constraint name] constraint type(Field name),
...
[CONSTRAINT Constraint name] constraint type(Field name),
[CONSTRAINT Constraint name] FOREIGN KEY(Associated table name) REFERENCES Associated table name(Associated field name) #Foreign key
);

Example 1:

CREATE TABLE stuinfo(
id INT, 
stuname VARCHAR(20) NOT NULL,#Non empty
gender CHAR(1),
seat INT,
age TINYINT DEFAULT 18, #default
majorid TINYINT,

CONSTRAINT pk PRIMARY KEY(id),#Primary key
CONSTRAINT ck CHECK(gender="male" OR gender="female"),#inspect
CONSTRAINT uq UNIQUE(seat),#only
CONSTRAINT fk_stuinfo_major FOREIGN KEY(majorid) REFERENCES major(id) #Foreign key
);

2.2.1 federated PK

Joint primary key: two or more fields form a primary key

CREATE TABLE stuinfo (
	id INT,
	stuname VARCHAR ( 20 ) NOT NULL,#Non empty
	gender CHAR ( 1 ),
	seat INT,
	age TINYINT DEFAULT 18,#default
	majorid TINYINT,
	
	PRIMARY KEY (id,stuname),#composite keys 
	CHECK ( gender = "male" OR gender = "female" ),#inspect
	UNIQUE ( seat ),#only
	FOREIGN KEY ( majorid ) REFERENCES major ( id ) #Foreign key
);

How to determine whether the primary key is duplicate  

2.2.2 joint unique key  

CREATE TABLE stuinfo (
	id INT,
	stuname VARCHAR ( 20 ) NOT NULL,#Non empty
	gender CHAR ( 1 ),
	seat INT,
	age TINYINT DEFAULT 18,#default
	majorid TINYINT,
	
	PRIMARY KEY (id),#Primary key
	CHECK ( gender = "male" OR gender = "female" ),#inspect
	CONSTRAINT Union unique key UNIQUE (seat,stuname),#Union unique key
	FOREIGN KEY ( majorid ) REFERENCES major ( id ) #Foreign key
);

  2.2.3 foreign keys

characteristic:

1. Requires a foreign key relationship to be set in the slave table
2. The foreign key field type of the slave table should be consistent or compatible with the associated field type of the master table, but the foreign key field name of the slave table can be different from the associated field name of the master table
3. The associated field of the main table must be a key (primary key or unique)
4. When inserting data, insert the master table first and then the slave table
5. When deleting data, delete the secondary table first, and then the primary table

2.3 adding constraint types when modifying tables

Syntax:

① Column level constraint

ALTER TABLE Table name MODIFY COLUMN Field name new constraint type;

For example:   ALTER TABLE table name MODIFY COLUMN id PRIMARY KEY# Add primary key constraint

② Table level constraints

ALTER TABLE Table name ADD [CONSTRAINT Constraint name] constraint type(Field name);

  For example: ALTER TABLE table name ADD PRIMARY KEY(id);   # Add primary key constraint

2.3.1 adding non empty constraints

 ALTER TABLE Table name MODIFY COLUMN Field name field type NOT NULL;

2.3.2 adding default constraints

 ALTER TABLE Table name MODIFY COLUMN Field name field type DEFAULT Default value;

2.3.3 add primary key constraint

① Column level constraint

ALTER TABLE Table name MODIFY COLUMN Field name PRIMARY KEY;

② Table level constraints

ALTER TABLE Table name ADD [CONSTRAINT Constraint name] PRIMARY KEY(Field name);

2.3.4 adding unique constraints

① Column level constraint

ALTER TABLE Table name MODIFY COLUMN Field name Field type UNIQUE;

② Table level constraints

ALTER TABLE Table name ADD [CONSTRAINT Constraint name]  UNIQUE(Field name);

2.3.5 adding foreign keys

ALTER TABLE Table name ADD [CONSTRAINT Constraint name]  FOREIGN KEY (Main table name)  REFERENCES  Main table name( Associated field name );

2.4 deleting constraint types when modifying tables

2.4.1 delete non empty constraint

ALTER TABLE Table name MODIFY COLUMN Field name field type NULL;

2.4.2 delete default constraint

 ALTER TABLE Table name MODIFY COLUMN Field name field type;

2.4.3   Delete primary key

ALTER TABLE Table name DROP PRIMARY KEY

2.4.4 delete unique key

ALTER TABLE Table name DROP INDEX Unique key field name;

2.4.5 deleting foreign keys

ALTER TABLE Table name DROP FOREIGN KEY Foreign key name

3, Cascade delete and cascade empty

three point one   cascading deletion

When deleting a table with foreign keys, the related data of the slave table is generally deleted first, and then the related data of the primary table.

However, if you want to delete the related data of the primary table first, you can set a cascade relationship for the secondary table

Syntax 1:

Cascade deletion: when deleting the related data of the primary table, the corresponding data of the secondary table will also be deleted

ALTER TABLE From table name ADD CONSTRAINT Foreign key name FOREIGN KEY(Main table name) REFERENCES Main table name(Foreign key field) ON DELETE CASCADE;

3.2 cascade emptying

Cascade empty: when deleting the related data of the primary table, the corresponding data of the secondary table will not be deleted

ALTER TABLE From table name ADD CONSTRAINT Foreign key name FOREIGN KEY(Main table name) REFERENCES Main table name(Foreign key field) ON DELETE SET NULL;