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