How to create a data Table by Mysql 03/30 Update SLTechnology News&Howtos

How to create a data Table by Mysql

2025-03-30 Update From: SLTechnology News&Howtos shulou NAV: SLTechnology News&Howtos > Database >

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

This article mainly introduces "how to create data tables in Mysql". In daily operation, I believe many people have doubts about how to create data tables in Mysql. The editor consulted all kinds of materials and sorted out simple and easy-to-use operation methods. I hope it will be helpful to answer the doubts about "how to create data tables in Mysql". Next, please follow the editor to study!

C:\ Users\ admin > mysql-h localhost-u root-pmysql

Mysql: [Warning] Using a password on the command line interface can be insecure.

Welcome to the MySQL monitor. Commands end with; or\ g.

Your MySQL connection id is 4

Server version: 5.7.14 MySQL Community Server (GPL)

Oracle is a registered trademark of Oracle Corporation and/or its

Affiliates. Other names may be trademarks of their respective

Owners.

Type 'help;' or'\ h' for help. Type'\ c'to clear the current input statement.

Mysql > show databases

+-+

| | Database |

+-+

| | information_schema |

| | mydata |

| | mysql |

| | performance_schema |

| | sys |

| | test |

+-+

6 rows in set (0.00 sec)

Mysql > use mydata

Database changed

Mysql > create table mydata1 (

-> id int

-> name varchar (20)

-> sex boolean

->)

Query OK, 0 rows affected (0.36 sec)

Mysql > desc mydata1

+-+ +

+-+ +

| | id | int (11) | YES | | NULL |

| | sex | tinyint (1) | YES | | NULL |

+-+ +

3 rows in set (0.02 sec)

Mysql > show tables

+-+

| | Tables_in_mydata |

+-+

| | mydata1 |

+-+

1 row in set (0.00 sec)

5.1 Integrity constraints

Primary key

Primary key, identifying unique

Foreign key

Identify the attribute as the foreign key of the table and the primary key of the contact sheet

Not null

Property cannot be empty

Unique

Property is unique in value

Auto_increment

Automatic increment of value, the characteristic of mysql's sql statement

Default

Column setting default

5.2 Primary key

Single-field primary key and multi-field primary key

Mysql > create table mydata2 (

-> id int primary key, # single field primary key

-> name varchar (20)

-> sex boolean)

Query OK, 0 rows affected (0.23 sec)

Mysql > show tables

+-+

| | Tables_in_mydata |

+-+

| | mydata1 |

| | mydata2 |

+-+

2 rows in set (0.00 sec)

Mysql > desc mydata2

+-+ +

+-+ +

| | id | int (11) | NO | PRI | NULL |

| | sex | tinyint (1) | YES | | NULL |

+-+ +

3 rows in set (0.00 sec)

Mysql > alter table mydata2 drop primary key

Query OK, 0 rows affected (0.68 sec)

Records: 0 Duplicates: 0 Warnings: 0

Mysql > desc mydata2

+-+ +

+-+ +

| | id | int (11) | NO | | NULL |

| | sex | tinyint (1) | YES | | NULL |

+-+ +

3 rows in set (0.00 sec)

Mysql > alter table mydata2 add primary key (id,name); # set multi-field primary key

Query OK, 0 rows affected (0.49 sec)

Records: 0 Duplicates: 0 Warnings: 0

Mysql > desc mydata2

+-+ +

+-+ +

| | id | int (11) | NO | PRI | NULL |

| | sex | tinyint (1) | YES | | NULL |

+-+ +

3 rows in set (0.00 sec)

You can also define primary key in the create table definition

Mysql > create table mydata3 (

-> id int

-> name varchar (20)

-> sex boolean

-> primary key (id,name)

->)

Query OK, 0 rows affected (0.24 sec)

Mysql > desc mydata3

+-+ +

+-+ +

| | id | int (11) | NO | PRI | NULL |

| | sex | tinyint (1) | YES | | NULL |

+-+ +

3 rows in set (0.00 sec)

5.3 Foreign key foreign key

Mysql > create table mydata4 (

-> id int primary key

> name varchar (30)

-> sex boolean

-> constraint my_fk foreign key (id) references mydata3 (id)

->)

Query OK, 0 rows affected (0.26 sec)

Mysql > desc mydata4

+-+ +

+-+ +

| | id | int (11) | NO | PRI | NULL |

| | sex | tinyint (1) | YES | | NULL |

+-+ +

3 rows in set (0.00 sec)

5.4 not null is not empty

Mysql > create table mydata5 (

-> id int primary key

-> name varchar (20) not null)

Query OK, 0 rows affected (0.28 sec)

Mysql > desc mydata5

+-+ +

+-+ +

| | id | int (11) | NO | PRI | NULL |

+-+ +

2 rows in set (0.00 sec)

5.5 unique uniqueness

Mysql > create table mydata6 (

-> id int primary key

-> name varchar (20) unique)

Query OK, 0 rows affected (0.35 sec)

Mysql > desc mydata6

+-+ +

+-+ +

| | id | int (11) | NO | PRI | NULL |

+-+ +

2 rows in set (0.00 sec)

5.6 auto_increment

Must be part of the primary key

Mysql > create table mydata7 (

-> id int primary key auto_increment

-> name varchar (20))

Query OK, 0 rows affected (0.24 sec)

Mysql > desc mydata7

+-+ +

+-+ +

| | id | int (11) | NO | PRI | NULL | auto_increment |

+-+ +

2 rows in set (0.00 sec)

5.7 default value

Mysql > create table mydata8 (

-> id int primary key auto_increment

-> name varchar (20) unique

-> address varchar (100) not null

-> city varchar (20) default 'suzhou'

-> socre float default 0)

Query OK, 0 rows affected (0.35 sec)

Mysql > desc mydata8

+-+ +

+-+ +

| | id | int (11) | NO | PRI | NULL | auto_increment |

| | socre | float | YES | | 0 | |

+-+ +

5 rows in set (0.04 sec)

5.8 View table structure

Mysql > show create table mydata1\ G

* * 1. Row *

Table: mydata1

Create Table: CREATE TABLE `mydata1` (

`id`int (11) DEFAULT NULL

`name` varchar (20) DEFAULT NULL

`sex` tinyint (1) DEFAULT NULL

) ENGINE=InnoDB DEFAULT CHARSET=latin1

1 row in set (0.00 sec)

ERROR:

No query specified

Mysql > desc mydata1

+-+ +

+-+ +

| | id | int (11) | YES | | NULL |

| | sex | tinyint (1) | YES | | NULL |

+-+ +

3 rows in set (0.00 sec)

5.9 modify the table structure

Mysql > alter table mydata1 rename to mydata; # modify the table name

Query OK, 0 rows affected (0.23 sec)

Mysql > alter table mydata1 modify sex varchar (1); # modify column properties

Query OK, 0 rows affected (0.77 sec)

Records: 0 Duplicates: 0 Warnings: 0

Mysql > alter table mydata1 change city address varchar (20)

Mysql > alter table mydata1 change sex city int; # modify column names and attributes

Query OK, 0 rows affected (0.94 sec)

Records: 0 Duplicates: 0 Warnings: 0

Mysql > alter table mydata1 add city int; # add column name

Query OK, 0 rows affected (.53 sec)

Records: 0 Duplicates: 0 Warnings: 0

Mysql > alter table mydata1 add sal int after address; # add a column after the address field

Query OK, 0 rows affected (0.35 sec)

Records: 0 Duplicates: 0 Warnings: 0

Mysql > alter table mydata1 add uid int first; # add as the first column

Query OK, 0 rows affected (0.45 sec)

Records: 0 Duplicates: 0 Warnings: 0

Mysql > alter table mydata1 drop city; # Delete columns

Query OK, 0 rows affected (0.5 sec)

Records: 0 Duplicates: 0 Warnings: 0

Mysql > alter table mydata1 modify sal int after name; # modify the location of the column

Query OK, 0 rows affected (.53 sec)

Records: 0 Duplicates: 0 Warnings: 0

Mysql > alter table mydata1 modify id int first; # modified to the first column

Query OK, 0 rows affected (.54 sec)

Records: 0 Duplicates: 0 Warnings: 0

CHANGE renames the column or changes the column type. You need to give the old column name and the new column name, and the current type MODIFY can change the column type. There is no need to rename the column at this time (no need to give a new column name).

Mysql > alter table mydata1 engine=myisam; # modify the storage engine of the table

Query OK, 0 rows affected (1.47 sec)

Records: 0 Duplicates: 0 Warnings: 0

Mysql > drop table mydata8; # Delete tables

Query OK, 0 rows affected (0.22 sec)

At this point, the study on "how to create data tables in Mysql" is over. I hope to be able to solve your doubts. The collocation of theory and practice can better help you learn, go and try it! If you want to continue to learn more related knowledge, please continue to follow the website, the editor will continue to work hard to bring you more practical articles!

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.

*The comments in the above article only represent the author's personal views and do not represent the views and positions of this website. If you have more insights, please feel free to contribute and share.