Basic operations for mysql Data Tables: Table Structure operations, field operations, and basic mysql operations

Basic operations for mysql Data Tables: Table Structure operations, field operations, and basic mysql operations
This section introduces:

 

Table Structure operations

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

Field operations

• New field,
• Modify the field data type, position, or attribute,
• Rename a field
• Delete Field

 

 

Start Time:

Table Structure operations

Create a data table:

• Syntax:

Create table [if not exists] table Name (field name word data type, field name word data type) [table options];

• Table options:

Character Set: character set for storing data in the charset table

Checking set: the checking set used to proofread data in the colloate table

Storage engine: the engine that stores data

• Example:

-- You must specify a database before creating a table, use it, or explicitly specify create table if not exists mydatabase. student (name varchar (20), sex varchar (20), number varchar (20), age int) charset utf8; -- use mydatabase; create table if not exists class (name varchar (20), room varchar (20) charset utf8; ------------ use mydatabase; create table if not exists class (name varchar (20 ), room varchar (20 ));

• Note:
  • If not exists is to check whether a table with the same name exists. if yes, the subsequent creation statement is not executed.
  • If the table option is not specified, the default option is used. For example, the default storage engine of mysql is innodb.

 

 

 

View the data table:

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

• Syntax:

-- View All tables show tables; -- view some tables show tables like 'fuzzy Match'; -- view the table creation statement show create table data table name; -- rotating viewing structure show create table data table name \ G; -- Viewing table structure: Viewing the field information in the table: Desc/desc table name; describe table name; show columns from table name;

• Wildcard:

_ Matching a single character

% Matches multiple characters

• Example:

show tables;show tables like 'my%';show create table student;show create table student\G;desc student;describe student;show columns from student;

Legend:

Desc/describe/show columns from table name;

 

Modify the data table structure:

Only the table name and table option can be modified.

• Syntax:

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

• Example:

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 A data table:

• Syntax:

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

• Example:

drop table demo;drop table demodata;

• Note:
  • Unrecoverable deletion

Field Operation: Add a field:

Adding a field is based on the existence of the table.

• Syntax:

Alter table name add [column] field name data type [column attribute] [location];

• Example:

Alter table name add [column] field name data type [column attribute] [location]; 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;

• Note:
  • Common location Syntax: first field name, after field name;
  • Column attributes: primary key, null, etc;

 

 

Modify Field:

Modifying a field generally modifies the field data type or field attribute.

• Syntax:

Alter table name modify Field Name Data Type [attribute] [location];

• Example:

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;

• Note:
  • Field Names and data types are required, and attributes and locations are optional.
  • If the field contains attributes, the original attributes must be included; otherwise, the fields will be removed. If you need to add new attributes based on the original attributes, add new attributes on the basis of the original attributes.

 

 

 

 

Rename field:

• Syntax:

Alter table Name change New Field Data Type [attribute] [location];

• Example:

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

• Note:
  • The data type is required, but it can be a new one. You can change the data type for the duplicate field]
  • The data type, attribute, and location of a field can also be modified. [If a field has an attribute, it can be left blank when a field is renamed]

 

 

Delete field:

• Syntax:

Alter table Name drop field name;

• Example:

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

• Note:
  • Exercise caution when deleting a field. Deleting a field means that all data under this field will be deleted.