This article uses examples to describe the basic operations of MySQL data tables, including table structure operations and field operations. Share with you for your reference, the details are as follows:

This section describes:

Table structure operations

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

Field Operations

• Add new fields,
• Modify field data type, location or properties,
• Rename fields
• Delete a field

First release time: 2018-02-18 21:31

Table structure operations

Create a data table:

• grammar :

create table [if not exists] table name (
Field name data type,
Field Name Data Type)[Table Options];

• Table options:

  • Character set: The character set in which the data is stored in the charset table
  • Collation set: The collation set used to collate data in the collation table
  • Storage engine: engine The storage engine that stores data
  • The difference between table options and library options is that if table options are not set, the settings of library options will be adopted, just like a "local variable".

• Example of use:

-- Before creating a table, you must specify a database. You can use use to specify which database subsequent operations are based on, or you can use the database name as a prefix to specify which database the data table is created in.
 -- Use the database name as a prefix to specify in which database the table is created.
create table if not exists mydatabase.student(
name varchar(20),
sex varchar(20),
number varchar(20),
age int
)charset utf8;

-- Use use to specify which database subsequent operations are based on use mydatabase;
create table if not exists class(
name varchar(20),
room varchar(20)
)charset utf8;

-- Demonstrate the creation of a table without table options use mydatabase;
create table if not exists class(
name varchar(20),
room varchar(20)
);

• Additional notes:
  • if not exists first checks whether there is a table with the same name. If so, the subsequent creation statement will not be executed. Highly recommended. If you are sure that this table does not exist, you can not use it.
  • If no table options are specified, the default will be used, such as the default storage engine for MySQL is InnoDB.

View the datasheet:

View the data table to view the existing data table and the field information of the data table

• grammar :

-- View all tables show tables;
-- View some tables show tables like 'Fuzzy Match';
-- View the table creation statement show create table data table name;
-- Rotate to view the structure show create table data table name\G;
-- View the table structure: View the field information in the table:
Desc/desc table name;
describe table name;
show columns from table name;

• Fuzzy matching:
  • _ matches a single character
  • % matches multiple characters

• Example of use:

show tables;

show tables like 'my%';

show create table student;

show create table student\G;

desc student;
describe student;
show columns from student;

legend:

1. show create table student;follow show create table sudent\G;

Desc/describe /show columns from table name;

Modify the data table structure:

When modifying a table, you can only modify the table name and table options.

• grammar :

-- Modify the table name:
rename table old table name to new table name;
--Modify table options:
Alter table table name table option [=] value;

• Example of use:

rename table student to my_student;
rename table class to my_class;
-- 
Alter table my_student charset gbk;
Alter table my_collation_bin collate =utf8_bin;

Delete the data table:

• grammar :

Drop table tablename1, tablename2...;

• Example of use:

drop table demo;
drop table demodata;

• Additional notes:
  • Deletion is irreversible, so delete with caution.

Field operations:

New fields:

New fields are added based on the existing table.

• grammar :

Alter table table name add [column] field name data type [column attributes] [position];

• Example of use:

Alter table table name add [column] field name data type [column attributes] [position];
Alter table demo add column id int first;
Alter table demo add id int;
Alter table demo add class int after age;
Alter table demo add number int not null after age;

• Additional notes:
  • Common syntax for positions:
    • first: indicates that it is in the first column.
    • after field name: represents after a certain field;
  • Column attributes: primary key, null value, etc.

Modify the fields:

Modifying a field generally involves modifying the field data type or field attributes

• grammar :

Alter table table name modify field name data type [attribute] [position];

• Example of use:

Alter table my_student modify number char(10) after id;
Alter table demo modify number int null;
--
alter table student modify name varchar(20) not null;

--
alter table student modify name varchar(20) not null primary key;

• Additional notes:
  • Field name and data type are required, attributes and position are optional.
  • If the field itself has attributes, you must bring the original ones, otherwise they will be removed; if you need to add new attributes based on the original attributes, when filling in, just add the new attributes based on the original attributes.
  • 

Rename fields:

• grammar :

Alter table table name change old field new field data type [attribute] [position];

• Example of use:

alter table demo change class room varchar(10);
Alter table my_student change sex gender varchar(10);

• Additional notes:
  • The data type is required, but can be new [Fields with the same name can change their data type]
  • When you rename a field, you can also modify its data type, attributes, and position. [If the field has attributes, you can omit them when renaming the field]

Delete a field:

• grammar :

Alter table table name drop field name;

• Example of use:

Alter table my_student drop age;
alter table demo drop room;

• Additional notes:
  • Be careful when deleting a field. Deleting a field means that all data under the field will be deleted.

Readers who are interested in more MySQL-related content can check out the following topics on this site: "MySQL query skills", "MySQL transaction operation skills", "MySQL stored procedure skills", "MySQL database lock related skills summary" and "MySQL common function summary"

I hope this article will be helpful to everyone's MySQL database design.