Table operation example for beginners of MySQL learning notes

Last time we introduced: the database operation example of MySQL learning notes for beginners. This article introduces some simple table operation examples. Let's take a look at this part. 1. Table creation command: createtable table name (field name 1 Type 1 [,... field name n Type n]); mysqlcreatetableMyClass (idint

Last time we introduced: the database operation example of MySQL learning notes for beginners. This article introduces some simple table operation examples. Let's take a look at this part. 1. table creation command: create table Name (field name 1 Type 1 [,... field name n Type n]); mysqlcreatetableMyClass (idint

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 <表名> ( <字段名1> <类型1> [,.. <字段名n> <类型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 <表名>

For example, delete a table named MyClass.

Mysql> drop table MyClass;

　　4. insert data

Command: insert <表名> [( <字段名1> [,.. <字段名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, 'job', 82.99), (2, 'wang', 96.59 );

　　5. query the data in the table

1) query all rows

Command: select <字段1，字段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!