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:
Create Table [if not exists] Table name (field name data type, field name data type) [ 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
--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
--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;
_ Match a single character
% matches multiple characters
like ' my% ' Createtablecreatetable student\g; desc from student;
Legend:
- 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.
-- Modify Table name: Table to The name of the new table; -- Modify Table options: Alter Table [=] value;
Table to Table to My_class; -- Alter Table my_student charset GBK; Alter Table =Utf8_bin;
To delete a data table:
Drop table name 1, table name 2 ...;
Drop Table demo; Drop table Demodata;
Field action: New field:
New field is a new field based on the existence of the table
Alter Table Add [column] [ Column Properties ] [ position ];
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
Alter Table [ properties ] [ position ];
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:
Alter Table [ properties ] [ position ];
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:
Alter Table drop field name;
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