Table operation example for beginners of MySQL learning notes

Last time we introduced: suitableBeginnersOfMySQL Study NotesLibrary operation example. This article describes some simpleTable operationsFor example, let's take a look at this part.

1. Create a table

Command: create table <table Name> (<field name 1> <type 1> [,... <field name n> <type n>]);

 
 

  
  
mysql> create table MyClass(  
  
  
> id int(4) not null primary key auto_increment,  
  
  
> name char(20) not null,  
  
  
> sex int(4) not null default '0',  
  
  
> degree double(16,2));  
  
  
 
 
 

2. Get the table structure

Command: desc table name or show columns from Table Name

 
 

  
  
mysql>DESCRIBE MyClass  
  
  
mysql> desc MyClass;  
  
  
mysql> show columns from MyClass;  
  
  
 
 
 

3. delete a table

Command: drop table <table Name>

For example, delete a table named MyClass.

 
 

  
  
mysql> drop table MyClass; 
 
 

4. insert data

Command: insert into <Table Name> [(<field name 1> [,... <field name n>])] values (value 1) [, (value n)]

For example, insert two records into the MyClass table. The two records indicate that the result of Tom numbered 1 is 96.45, and the result of Joan numbered 2 is 82.99, wang, numbered 3, scored 96.5.

 
 

  
  
mysql> insert into MyClass values(1,'Tom',96.45),(2,'Joan',82.99), (2,'Wang', 96.59); 
 
 

5. query the data in the table

1) query all rows

Command: select <Field 1, Field 2,...> from <Table Name> where <expression>

For example, you can view all data in the MyClass table.

 
 

  
  
mysql> select * from MyClass; 
 
 

2) query the first few rows of data

For example, view the first two rows of data in the MyClass table.

 
 

  
  
mysql> select * from MyClass order by id limit 0,2; 
 
 

Or:

 
 

  
  
mysql> select * from MyClass limit 0,2; 
 
 

6. Delete table data

Command: delete from table name where expression

For example, delete the record numbered 1 in MyClass.

 
 

  
  
mysql> delete from MyClass where id=1; 
 
 

8. Modify Table data:

Update table name set field = new value ,... Where condition

 
 

  
  
mysql> update MyClass set name='Mary' where id=1; 
 
 

9. Add fields to the table:

Command: alter table name, add, other field types;

For example, a passtest field is added to the MyClass table. The type is int (4) and the default value is 0.

 
 

  
  
mysql> alter table MyClass add passtest int(4) default '0' 
 
 

10. Change the table name:

Command: rename table original table name to new table name;

For example, the MyClass name in the table is changed to YouClass.

 
 

  
  
mysql> rename table MyClass to YouClass; 
 
 

Update field content

Update table name set field name = new content

Update table name set field name = replace (field name, 'old content', 'new content ');

Add four spaces before the article

 
 

  
  
update article set content=concat('',content); 
 
 

Field Type

1. INT [(M)] type: normal Integer type.

2. DOUBLE [(M, D)] [ZEROFILL] type: normal size (DOUBLE Precision) floating point number type.

3. DATE type: the supported range is 1000-01-01 to 9999-12-31. MySQL displays the DATE value in YYYY-MM-DD format, but allows you to assign the value to the DATE column using strings or numbers.

4. CHAR (M) type: fixed-length string type. When stored, it always fills the Right to the specified length with spaces.

5. blob text type, with a maximum length of 65535 (2 ^ 16-1) characters.

6. VARCHAR: variable-length string type.

Here is an introduction to the operations on MySQL database tables. I hope this introduction will be helpful to you!