Mysql > select * from ‘SQL’;

preface

Mysql > insert into mysql

• Mysql operation 01 – Basic instructions, SQL library operations
• Mysql > select * from ‘SQL’;
• Mysql operation 03 – SQL data operation
• Mysql > alter table SQL > alter table SQL > alter table SQL > alter table SQL > alter table SQL
• Mysql Operations 05 – SQL advanced data operations

1. Add data tables

Basic syntax:

Create table [if not exists] table name (field name datatype, field name datatype -- last line does not need comma)[table option];Copy the code

Explanation:

If not exists: create a table if the name does not exist, otherwise do not execute the create code (check function)

Table options: Controls the table’s performance

Charset: charset/charset set Specifies the character set. Ensure the character set of the data stored in the table

1. Collate A specific collation set;

Specific storage engines (InnoDB and MyISAM)

The design of any table must specify the database.

1. Scenario 1: The database to which the specified table is displayed

   Create tabe Specifies the name of the database. The name of the table (); -- Creates the current table into the specified databaseCopy the code

   Ex. :

   Create table if not exists mydatabase.student(name varchar(10), name varchar(10), gender varchar(12), number int age int )charset utf8;Copy the code

2. Scheme 2: Implicitly specify the owning database of a table: Enter a database environment first, and then the created table automatically belongs to a specified database.

   Enter the database environment: use the database name;

   Ex. :

   Use mydatabase; Create table class(name varchar(10), room varchar(10))charset utf8;Copy the code

   What happens after the SQL instruction to create the table is executed?

   • Specifies that the corresponding table already exists in the database
   • In the folder corresponding to the database, the corresponding table data structure file will be generated (related to the storage engine)

2. View the data table

1. View all tables

   Basic syntax:

   show tables;
   Copy the code

2. View part of the table: Fuzzy matching

   Basic syntax:

   show tables like 'pattern';
   Copy the code

   Ex. :

   Show table like '%s';Copy the code

3. View the table creation statement

   Basic syntax:

   Show create table name;Copy the code

   Ex. :

   Show create table student\g -- \g =; Show create table student\G -- Turn the found structure 90 degrees to vertical.Copy the code

4. View the table structure: View the field information in the table

   Basic syntax:

   ~~~ desc/describe/show columns from ~~~ -- check table structure desc calss; describe calss; show columns from class; // These three expressions all mean the same thing ~~~Copy the code

3. Modify the data table

The table itself exists and contains fields. Modifying a table consists of two parts: modifying the table itself and modifying the fields.

1. Modify the table itself

The table itself can be modified: table name and table options

• Alter table name:

  Rename table old table name to new table name;Copy the code

• Modify table options: character set, collate set, and storage engine

  Alter table table_name [=];Copy the code

  Ex. :

  Alter table my_student clarset = GBK;Copy the code

2. Modify the fields

Field operations are many: add, modify, rename, delete

• New fields:

  Alter table table_name add [column]; Position: the field name can be stored anywhere in the table first: the first position after: the field name after; The default is after the last field.Copy the code

  Ex. :

  Alter table my_student add column ID int first;Copy the code

• Modifying fields: Modifying is usually a property or data type

  Alter table name alter table name alter table name alter table nameCopy the code

  Ex. :

  Alter table my_student modify number char(10) after ID; alter table my_student modify number char(10) after ID;Copy the code

• Rename field

  Alter table name alter table name alter table name alter table name alter table nameCopy the code

  Ex. :

  Alter table my_student change gender sex varchar(10);Copy the code

• Delete the field

  Alter table drop name;Copy the code

Caution: If data already exists in the table, deleting a field empties all data for that field (irreversible)

4. Delete the data table

Basic syntax:

Drop table 1; drop table 2; You can delete multiple tables at onceCopy the code

What happens when the delete table instruction is executed?

• In table space, there is no specified table (data is also missing)
• In the folder corresponding to the database, the files corresponding to the table (related to the storage engine) are also deleted.

Caution: Delete dangerous operations with caution (irreversible)