Shulou(Shulou.com)05/31 Report--

This article mainly introduces the relevant knowledge of "what are the methods of MySQL data table operation". The editor shows you the operation process through an actual case. The operation method is simple, fast and practical. I hope this article "what are the methods of MySQL data table operation" can help you solve the problem.

Create data table 1, basic syntax create table table name (field name field type [field property], field name field type [field property],...) [table options]

Note: the table needs to be placed under the corresponding database.

2. Creation method 1-first select database use mydatabase;-- to create data table create table user (name varchar (10)); 3. Creation method 2-directly hang data table under database create table mydatabase.user (name varchar (10)); 4. Table options

Engine storage engine

Charset character set

Collate proofreading set

Specify the character set of the table

Create table user (name varchar (10)) charset utf8;5, copy existing table structure create table table name like table name;-- eg copy table create table user like test.user; II from test database, display data table-show all tables mysql > show tables +-- + | Tables_in_mydatabase | +-- + | t_author | | user | +-- +-- display matching table mysql > show tables like'% author'

Basic syntax:

Desc table name (commonly used); describe table name; show columns from table name

Example:

Mysql > desc user +-+ | Field | Type | Null | Key | Default | Extra | +-+- -+ | name | varchar (10) | YES | | NULL | | +-+-+ 1 row in set (0.00 sec) mysql > describe user +-+ | Field | Type | Null | Key | Default | Extra | +-+- -+ | name | varchar (10) | YES | | NULL | | +-+-+ 1 row in set (0.01sec) mysql > show columns from user +-+ | Field | Type | Null | Key | Default | Extra | +-+- -+ | name | varchar (10) | YES | | NULL | | +-+-+ 1 row in set (0.01sec)

Field meaning:

Field field name Type field type Null whether to allow an empty Key index Default default value Extra additional properties 4. Display table creation statement

Basic syntax:

Show create table table name

Example:

Mysql > show create table user;+-+-+ | Table | Create Table | +-+-+ | user | CREATE TABLE `user` (`name` varchar (10) COLLATE utf8mb4_general_ci DEFAULT NULL) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci | +-+-+ 1 row in set (0.00 sec)

Statement Terminator:

;\ g the effect is the same, with fields at the top and data at the bottom

\ G field is on the left, data is on the right

Mysql > show create table user\ gateway * 1. Row * * Table: userCreate Table: CREATE TABLE `user` (`name` varchar (10) COLLATE utf8mb4_general_ci DEFAULT NULL) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci1 row in set (0.00 sec) 5. Set table properties

Engine

Charset

Collate

If the data table already has data, do not easily modify the table properties

6. Modify the table structure 1. Modify the table name-- basic syntax rename table old table name to new table name;-- eg:rename table user to tb_user;2, new field-- basic syntax alter table table name add [column] field name field type [field attribute] [location first/after field name]; mysql > desc user +-+ | Field | Type | Null | Key | Default | Extra | +-+- -+ | name | varchar (10) | YES | | NULL | | +-add an age field to the student table Add to the last mysql > alter table tb_user add age int of the table by default Mysql > desc tb_user +-+ | Field | Type | Null | Key | Default | Extra | +-+- -+ | name | varchar (10) | YES | | NULL | | age | int (11) | YES | | NULL | | +-add an id field mysql > alter table tb_user add id int first at the beginning Mysql > desc tb_user +-+ | Field | Type | Null | Key | Default | Extra | +-+- -+ | id | int (11) | YES | | NULL | name | varchar (10) | YES | | NULL | | age | int (11) | YES | | NULL | | +-+-+

Field location:

First put the first name alter field name after a field 3, modify the field name-basic syntax alter table table name change old field name new field name field type [field properties] [new location]-modify the age field to oldmysql > alter table tb_user change age old int;mysql > field +-+ | Field | Type | Null | Key | Default | Extra | +-+- -+ | id | int (11) | YES | | NULL | name | varchar (10) | YES | | NULL | | old | int (11) | YES | | NULL | | +-+-+ 4. Modify field properties-basic syntax alter table table name modify field name new field type [new field property] [new location]-eg changes the length of name from 10 to 20mysql > alter table tb_user modify name varchar (20) Mysql > desc tb_user +-+ | Field | Type | Null | Key | Default | Extra | +-+- -+ | id | int (11) | YES | | NULL | name | varchar (20) | YES | | NULL | | old | int (11) | YES | | NULL | | +-+-+ 5, Delete field-basic syntax alter table table name drop field name-eg delete old field alter table tb_user drop old 7. Delete table structure-basic syntax, you can delete multiple tables drop table table name [, table name.];-- eg: delete tb_user table mysql > drop table tb_user;mysql > show tables +-- + | Tables_in_mydatabase | +-- + | t_author | +-- + this is the end of the content about "what are the methods for operating MySQL data tables". Thank you for your reading. If you want to know more about the industry, you can follow the industry information channel. The editor will update different knowledge points for you every day.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.