Write table of MySQL Foundation (create table)

My blog

Tools

On the market SQL There are many visualization tools , I usually use these two .

Of course ,IDEA It also integrates the database visualization function . In addition to these , also DBeaver、SQLyog wait .

I prefer DataGrip, I'll use this to demonstrate . But this interface doesn't have Navicat It's beautiful , However, I feel that the function is much more powerful .

Write a watch

here , I've created a Demo database .

Let's first understand the syntax of creating tables .

Create table

USE Demo;
#  The grammar is as follows 
# CREATE TABLE [IF NOT EXISTS]  Table name ( Field contents )
CREATE TABLE IF NOT EXISTS class(
    Id INT(4) COMMENT 'ID Number ',
    Name VARCHAR(10) COMMENT ' full name '
);

IF NOT EXISTS You can omit it .

CREATE TABLE class(
    Id INT(4) COMMENT 'ID Number ',
    Name VARCHAR(10) COMMENT ' full name '
);

remember **CREATE TABLE** Used to create tables .

Create fields

() Inside are the fields of the table , The format of the written field is as follows .

#  Field name   value type  COMMENT ' Field notes '
Id INT(4) COMMENT 'ID Number ',
Name VARCHAR(10) COMMENT ' full name '

• If the value type is to be set, the length , We can connect... At the back (), Fill in the length value .
• COMMENT Keywords are the corresponding notes used to create fields , Remarks must be followed by .

Field constraints and attribute settings

We can set constraints and properties for fields through some keywords .

Id INT(4) COMMENT 'ID Number ' PRIMARY KEY ,
id INT(4) COMMENT 'ID Number ' PRIMARY KEY AUTO_INCREMENT NOT NULL  UNIQUE KEY 

such as ,PRIMARY KEY Keyword can set the field as the primary key .

Variable position ！ The corresponding syntax format is enough , Field data type [ Field properties | constraint ] [ Indexes ] [ Field notes ] .

---

Constraints or attributes	explain
Primary key constraint	PRIMARY KEY
Foreign key constraints	FOREIGN KEY
Non empty constraint	NOT NULL
Automatic growth	AUTO_INCREMENT
Unique constraint	UNIQUE KEY
Default constraint	DEFAULT

For these constraints , If you need to know the detailed function , Please go to the browser to view , Hey ！

Common data types

value type

type	size	purpose
TINYINT	1 Bytes	Small integer value
SMALLINT	2 Bytes	Large integer value
MEDIUMINT	3 Bytes	Large integer value
INT or INTEGER	4 Bytes	Large integer value
BIGINT	8 Bytes	Maximum integer value
FLOAT	4 Bytes	Single precision Floating point numbers
DOUBLE	8 Bytes	Double precision Floating point numbers
DECIMAL	Yes DECIMAL(M,D) , If M>D, by M+2 Otherwise D+2	Small value

The date type

type	size ( bytes)	Format	purpose
DATE	3	YYYY-MM-DD	Date value
TIME	3	HH:MM:SS	Time value or duration
YEAR	1	YYYY	The year is worth
DATETIME	8	YYYY-MM-DD HH:MM:SS	Mix date and time values
TIMESTAMP	4	YYYYMMDD HHMMSS	Mix date and time values , Time stamp

String type

type	size	purpose
CHAR	0-255 bytes	Fixed length string
VARCHAR	0-65535 bytes	Variable length string
TINYBLOB	0-255 bytes	No more than 255 Binary string of characters
TINYTEXT	0-255 bytes	Text string
BLOB	0-65 535 bytes	Long text data in binary form
TEXT	0-65 535 bytes	Long text data
MEDIUMBLOB	0-16 777 215 bytes	Medium length text data in binary form
MEDIUMTEXT	0-16 777 215 bytes	Medium length text data
LONGBLOB	0-4 294 967 295 bytes	Maximum text data in binary form
LONGTEXT	0-4 294 967 295 bytes	Large text data