MySQL Getting started is simple: 4 Create modify Delete table

1. Ways to create tables

1) Create the syntax form of the table

First, select the database:

Use  database name;

To create a table:

CREATE Table Table name (property name data type [integrity constraint],                   property name data type [integrity constraint],                   property name data type                   );

2) Set the primary key of the table

Primary keys are used to identify each record, and the primary key must be unique.

Single Field primary key: A primary key is made up of one

syntax Rules:        property name  data type  PRIMARY key example: CREATE TABLE example1 (stu_id INT PRIMARY key,                      stu_name  VARCHAR (+),                      stu_sex  BOOLEAN                      );

Multi-field Primary key: A primary key is composed of multiple attributes, and the primary key is set uniformly after the property is defined

Syntax:  PRIMARYKEY  (property name 1, property name  2, ...,  property name N) Example:CREATETABLE INT  INTFLOAT,PRIMARYKEY(stu_id, course_id));

3) Set the foreign key of the table

CREATE TABLEExample2 (stu_idINT, course_idINT, GradeFLOAT,PRIMARY KEY(stu_id, course_id)); set the stu_id and course_id as foreign keys in the Example3 table, and the primary key example1 and stu_id in the course_id table associated with itCREATE TABLEExample3 (IDINT PRIMARY KEY, stu_idINT, course_idINT,CONSTRAINTC_fkFOREIGN KEY(stu_id, course_id)REFERENCESexample2 (stu_id, course_id));

After running, the Example3 table contains 3 fields, where the ID field is the primary key, the stu_id and course_id fields are foreign keys, and the C_FK is the alias of the foreign key;
The Example2 table is called the parent table of the Example3 table; The foreign key of the Example3 table depends on the parent table Example3 primary key stu_id and course_id

4) non-null constraints on the table

Non-null constraint: Add a NOT NULL constraint to some special fields that create a table

CREATE TABLE INT  not NULL PRIMARY KEY  VARCHAR(notNULL INT,  CONSTRAINTFOREIGNKEY  (stu_id)REFERENCES  example1 (stu_id)) ;

5) Uniqueness of the table constraint

Uniqueness: So the value of the field in the record cannot recur

CREATE TABLE INT PRIMARY KEY  INTUNIQUEVARCHAR (not NULL) ;

6) Set the property value of the table to automatically increase

Auto_increment is used to automatically generate a unique ID for new records inserted in a table, a table can have only one field using the Auto_increment constraint, and the field must be part of the primary key.

CREATE TABLE INT PRIMARY KEY  INTUNIQUEVARCHAR (not NULL) ;

7) Set default values for the properties of the table

You can specify a default value for a table's field, and if you do not assign a value to the field when inserting a new record, the database system automatically inserts a default value for that field, which is set by the default keyword.

CREATE TABLEExample7 (IDINT PRIMARY KEYauto_increment,stu_idINT UNIQUE, nameVARCHAR( -) not NULL, 中文版VARCHAR( -)DEFAULT 'Zero', MathFLOAT DEFAULT 0, ComputerFLOAT DEFAULT 0);

2. View table structure

View table Basic structure  :   DESCRIBE table name; or abbreviated  DESCcreatetableCreate   Table name \g

3. Ways to modify Tables

Modify Table Name:ALTER TABLEOld table name RENAME[ to]new table name; Modify field data type:ALTER TABLETable name MODIFY property name data type; Modify field name:ALTER TABLETable name change Old property name[New data Types]; Add a field:ALTER TABLETable nameADDProperty name 1 Data type[integrity Constraint conditions] [First | After property name 2]; To delete a field:ALTER TABLETable nameDROPproperty name; Modify the position of the field:ALTER TABLETable name MODIFY property name 1 data type first|after property name 2; (Description: first specifies the position of the table; After property name 2 specifies that the property name 1 is inserted after the property name 2) changes the table's storage engine:ALTER TABLETable name ENGINE=Storage Engine (InnoDB, MyISAM, memory); Delete foreign key constraint for table:ALTER TABLETable nameDROP FOREIGN KEYForeign key aliases;

4. Ways to delete a table

DROP TABLE
The first method: Delete the child table first, and then delete the parent table; The second method: Delete the foreign key constraint of the child table first, then delete the parent table;

MySQL Getting started is simple: 4 Create modify Delete table