First, the complete syntax of creating a table

1. Syntax for creating tables

create table table name (

​ Field name 1 Field type (number) Constraints,

​ Field Name 2 Field Type (Number) Constraints,

​ field name 3 field type (number) constraints, ...)

2. Conditions for creating tables

• Field name and field type are required
• Numbers and constraints are optional and can be left blank
• Multiple constraints can also be written, separated by spaces

3. The meaning of numbers

​ When creating a table, the data type will be added after the field name, and the number will be added after the data type. Numbers are used in many places to limit the length of stored data, but in integers, numbers are used To control the length of the display, so when writing integers, you usually don't need to add numbers

create table t12(id int(3));  not to limit the length
insert into t12 values(12345);

create table t13(id int(5) zerofill);  but to control the length of the display
insert into t13 values(123),(123456789);

create table t14(id int);

Two, MySQL data type

1. Integer of field type

Types of integers:

• tinyint

  • Features: It can store up to 1 bytes (the plus and minus sign occupies one bit)

• smallint

  • Features: It can store up to 2 biyts (the plus and minus sign occupies one bit)

• int

  • Features: It can store up to 4 biyts (the plus and minus sign occupies one bit)

• bigint

  • Features: Up to 8 biyts can be stored (the plus and minus sign occupies one bit)

​ Integers in MysSQL have their own sign by default, and the sign will occupy one bit, that is to say, the maximum value of the integer will be affected by the sign. Next, you can verify the existence of the sign by adding data.

Cancel sign:

​ At the stage of creating a table, add keywords after the integer type to cancel the sign

​ create table table name (field name integer type unsigned);

2. Float type of field type

Types of floating point types:

• float(m,d)
  • Features: single-precision floating-point type 8-bit precision (4 bytes) m total number, d decimal places
• double(m,d)
  • Features: double-precision floating-point type 16-bit precision (8 bytes) m total number, d decimal places
• decimal(m,d)
  • Features: DECIMAL is a fixed-point type, that is, all values ​​have a fixed number of decimal places. The importance of this is that DECIMAL values ​​do not suffer from rounding problems like floating point numbers
  • The value range of M is 1~65, the value range of D is 0~30, and does not exceed M

3. The character type of the field type

Types of character types:

• char(n)
  • Features: fixed length, up to 255 characters
• carchar(n)
  • Features: variable length, up to 65535 characters

the difference:

​ char(n) has a fixed length, char(4) will occupy 4 bytes no matter how many characters are stored, varchar is the actual number of characters stored + 1 byte (n<=255) or 2 Bytes (n>255), so varchar(4), storing 3 characters will occupy 4 bytes.

4. Enumeration and collection of field types

• Enumeration (choose one more)
  • Enumeration means that when creating a table, you can fill in the enumeration type after the specified field name, and fill in multiple data values ​​​​in the square brackets after the keyword. When adding data to the field name later, you can only add enumeration types. Use one of the multiple data values ​​in the back brackets, otherwise an error will be reported
  • Keywords: enum( data value 1, data value 2,...)

enumerate:
	choose one
	create table t15(
    	id int,
      	name varchar(32),
       gender enum('male','female','others')
    );
 	insert into t15 values(1,'tony','macho');
  	insert into t15 values(2,'jason','male');
 	insert into t15 values(3,'kevin','others');

• Collection (Multiple Choices/Multiple Choices)
  • The functions of collection and enumeration are similar. In the stage of defining the field name, add the collection keyword after the field name, and fill in multiple data values ​​in the brackets. When adding the data of the field name later, you can fill in the specified One or more data values, data values ​​other than those specified cannot be added
  • Keywords: set( data value 1, data value 2,...)

gather
	multiple choice/choose one
	create table t16(
    	id int,
      	name varchar(16),
       hobbies set('basketabll','football','doublecolorball')
    );
 	insert into t16 values(1,'jason','study');
 	insert into t16 values(2,'tony','doublecolorball');
	insert into t16 values(3,'kevin','doublecolorball,football');

5. Date type of field type

Kind of type:

datetime		year month day hour minute second
date			year month day
time			Minutes and seconds
year			year

Code usage:

create table t17(
	id int,
  	name varchar(32),
 	register_time datetime,
 	birthday date,
 	study_time time,
 	work_time year
);
insert into t17 values(1,'jason','2000-11-11 11:11:11','1998-01-21','11:11:11','2000');

Generally, we do not need to manually add the time type, the system will add it automatically