MySQL data table basic operations: Table structure operations, field operations

This section describes:

Table structure Operations

• Create a data table,
• View data tables and view fields,
• Modify the data table structure
• Delete a data table

Field operations

• New fields,
• Modify the field data type, location, or property,
• Rename Field
• Delete a field

Starting Time: 2018-02-18 21:31

Table structure Operations

To create a data table:

• Syntax:

Create Table [if not exists] Table name (field name data type, field name data type) [ table Options ];

• Table Options:

Character set: The character set that stores data in a charset table

Proofing set: The proofing set used to proofread data in a colloate table

Storage Engine: Engine for storing data

• Examples of use:

--You must specify the database before you build the table, use using, or explicitly specify the Create tableif  notexists mydatabase.student (name varchar (20), sex varchar (20), number varchar (20), age int) charset UTF8;--Use mydatabase;create tableif  notExistsclass(name varchar (20), the guest (20)) charset UTF8;------------Use mydatabase;create tableif  notExistsclass(name varchar (20), the guest (20));

• Additional notes:
  • If not exists is the first to check if there is a table with the same name, and if it does, no subsequent creation statements are executed.
  • If you do not specify a table option, the default, such as the MySQL default storage engine, is InnoDB

View Data Sheet:

View data tables to view field information for existing data tables and data tables

• Grammar:

--View All Tablesshow tables;--View some tablesShow tables like 'Fuzzy Matching';--View creation statements for a tableShowCreate Tabledata table name;--Rotate View StructureShowCreate Tabledata table name \g;--View table structure: View field information in a table:Desc/desctable name; Describe table name; show Columns fromTable name;

• wildcard characters:

_ Match a single character

% matches multiple characters

• Examples of use:

 like ' my% '  Createtablecreatetable  student\g; desc   from student;

Legend:

1. Show create table student; With show create TABLE sudent\g;

Desc/describe/show columns from table name;

To modify the data table structure:

Modifying the table itself can only modify table names and table options.

• Grammar:

-- Modify Table name: Table  to The name of the new table; -- Modify Table options: Alter Table [=] value;

• Examples of Use:

Table  to  Table to My_class; -- Alter Table my_student charset GBK; Alter Table =Utf8_bin;

To delete a data table:

• Grammar:

Drop  table name 1, table name 2 ...;

• Examples of Use:

Drop Table demo; Drop table Demodata;

• Additional notes:
  • Delete non-recoverable

Field action: New field:

New field is a new field based on the existence of the table

• Grammar:

Alter Table Add [column] [ Column Properties ] [ position ];

• Examples of Use:

Alter TableTable nameAdd [column]Field name data type[Column Properties] [location];Alter TableDemoAdd columnIdintFirst ;Alter TableDemoAddIdint;Alter TableDemoAddClassintAfter age ;Alter TableDemoAdd   Number int  not NULLAfter age;

• Additional notes:
  • Location Common syntax: First field name, after field name;
  • Column properties: Primary key, empty, etc.;

To modify a field:

Modifying a field typically modifies the field data type or field properties

• Grammar:

Alter Table [ properties ] [ position ];

• Examples of Use:

Alter TableMy_student Modify Number Char(Ten) after ID;Alter TableDemo Modify Number int NULL ;--Alter TableStudent Modify Namevarchar( -) not NULL;--Alter TableStudent Modify Namevarchar( -) not NULL Primary Key;

• Additional notes:
  • Field names and data types are required, and properties and locations are optional.
  • If the field itself is with a property, it must be taken with the original, otherwise it will be removed, if you need to add a new attribute based on the original attribute, then fill in, then on the basis of the original property with the new properties can be

To rename a field:

• Grammar:

Alter Table [ properties ] [ position ];

• Examples of Use:

Alter Table varchar (ten); Alter Table varchar (ten);

• Additional notes:
  • Data type is required, but can be a new "rename field can be changed by data type"
  • Renaming can also modify the data type, properties, and location of the field. "If the field has attributes, you can rename the field without the"

To delete a field:

• Grammar:

Alter Table drop field name;

• Examples of Use:

Alter Table Drop Age ; Alter Table drop the hostel;

• Additional notes:
  • Delete requires caution and deleting a field means that all data under that field will be deleted.

MySQL data table basic operations: Table structure operations, field operations