1, table: is the basic unit of data stored in the database, a table contains several fields and values 2,To
Create a table:CREATE Table table name (field name 1 database type 1 [constraint 1], field name 2 database type 2 [constraint 2], field name 3 database Type 3 [constraint 3], field name 4 database type 4 [constraint 4],........ ............... );
interpretation: Constraint conditions
| Constraint conditions |
Description |
| PRIMARY KEY |
Identifies the property as a primary key and can uniquely identify the corresponding record |
| FOREIGN KEY |
A foreign key that identifies the property, associated with the primary key of a table |
| Not NULL |
Identifies that the primary key cannot be empty |
| UNIQUE |
The value that identifies the property is unique |
| Auto_increment |
The value that identifies the property is automatically incremented |
| DEFAULT |
Identifies the default value set for this data |
Example: Creating a Diagram Category table: Create TABLE T_booktype (ID int PRIMARY KEY auto_increment,booktypename VARCHAR) not Null,booktypedesc varchar ($) not NULL); Create Book table: T_bookcreate table T_book (ID int PRIMARY KEY auto_increment,booknmae VARCHAR) not Null,au Thor VARCHAR (Ten) not Null,price DOUBLE (6,2), Booktypeid int,constraint ' FK ' FOREIGN KEY (' Booktypeid ') REFERENCES ' T_ BookType ' (' id ')); The Booktypeid in the T_book table is associated with the ID in the T_booktype table, note that the symbol behind the constraint here is the back quotation mark ·
3. View Table StructureDESC T_book;
View Detail Table structure: SHOW CREATE TABLE T_book;
4. Modify the table name:ALTER table Old table name RENAME new table name;To
Modify a field:ALTER TABLE name change old field name new data type ALTER TABLE T_BOOK2 change Booknmae bookName2 VARCHAR (+) not NULL;
Add Field:ALTER table name ADD field name data type [integrity constraint][first| ALTER Property name 2]first: The position where the inserted field should be placed. ALTER TABLE t_book2 ADD addcolumn VARCHAR (a) not NULL; ALTER TABLE t_book2 ADD addcolumn VARCHAR (a) not NULL first;
Add a field after the specified field:ALTER table ' table name ' ADD COLUMN ' field name ' field type after| Beffore ' field name ' ALTER TABLE ' t_book2 ' ADD COLUMN ' produce ' VARCHAR (DEFAULT ' electronics Press ' after ' price ';
to add an index on the specified field:ALTER table ' table name ' ADD primay KEY (• field ·);
To Add a unique index:ALTER table ' table name ' ADD UNIQUE (' field name ');
4. Delete fields:ALTER TABLE name drop FIELD name ALTER TABLE T_BOOK2 drop AddColumn;
5. Delete the table:DROP table name;
Modify field Name:ALTER TABLE ' table name ' Change column ' Old field ' new Field ' new Field ' property ALTER TABLE t_student change column ' name ' ' name1 ' VARCHAR (a) not NULL;
Delete the specified field:ALTER TABLE ' t_student ' DROP COLUMN ' field name ';
To delete a field primary key:ALTER table ' table name ' DROP PRIMARY KEY;
To Delete the specified index:ALTER table ' table name ' DROP index ' name ';
In-depth study of MySQL (ii) Table operation
