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

This article is about how to create a data table in Mysql. The editor thinks it is very practical, so I share it with you to learn. I hope you can get something after reading this article.

Create a datasheet

In Mysql, the data table is created through the SQL statement CREATE TABLE:

CREATE TABLE table name (attribute name data type [integrity constraint]

Attribute name data type [integrity constraint]

、

Attribute name data type [integrity constraint]

)

The Table name is the name of the data table to create, the property name parameter represents the name of the field in the table, and the data Type parameter specifies the data type of the field.

Note: before creating a data table, you must use the USE statement to select it in the format; USE database name

Example 1:

[sql]

CREATE TABLE example (id INT

Name VARCHAR (20)

Sex BOOLEAN

);

Set the primary key of the table

1. Single-field primary key

When the primary key consists of a field, you can directly add PRIMARY KEY to the end of the field to set the primary key:

Attribute name data type PRIMARY KEY

Example 2:

[sql]

CREATE TABLE example1 (stu_id INT PRIMARY KEY

Stu_name VARCHAR (20)

Stu_sex BOOLEAN

);

2. Multi-field primary key

The primary key is composed of multiple attributes, and the primary key is set uniformly after the attribute is defined, as follows:

Example 3:

[sql]

CREATE TABLE example2 (stu_id INT

Course_id INT

Grade FLOAT

PRIMARY KEY (stu_id, course_id)

);

3. Set the foreign key of the table

The principle of setting a foreign key is that you must rely on the primary key of the parent table that already exists in the database, and the foreign key can be null. The role of a foreign key is to establish an association between the table and its parent table. When a piece of information is deleted from the parent table, the corresponding information in the child table must be changed accordingly.

The basic syntax for setting a foreign key is:

CONSTRAINT foreign key alias FOREIGN KEY (attribute 1.1, attribute 1.2, attribute 1.n)

REFERENCES table name (attribute 2.1,2.2, attribute 2.n)

Where the "foreign key alias" parameter is the code of the foreign key, the "attribute 1" parameter list is the foreign key set in the child table, the "table name" parameter refers to the name of the parent table, and the "attribute 2" parameter list is the primary key of the parent table.

Example 4:

[sql]

CREATE TABLE example3 (id INT PRIMARY KEY

Stu_id INT

Course_id INT

CONSTRAINT c_fk FOREIGN KEY (stu_id, course_id)

REFERENCES example2 (stu_id, course_id)

);

Explanation: the id field is the primary key, the stu_id and course_id fields are foreign keys, c_fk is the foreign key alias, the example2 table becomes the parent of the example3 table, and the stu_id and course_id fields in example3 depend on the corresponding fields in the example2 table.

4. Set the non-empty constraint of the table

Setting the non-null constraint of a table refers to adding NOT NULL constraints to some special fields in the table when creating the table. The basic syntax is as follows:

Attribute name data type NOT NULL

Example 5:

[sql]

CREATE TABLE example4 (id INT NOT NULL PRIMARY KEY

Name VARCHAR (20) NOT NULL

Stu_id INT

CONSTRAINT d_fk FOREIGN KEY (stu_id)

REFERENCES example1 (stu_id)

);

5. Set the uniqueness constraint of the table

Uniqueness means that the value of this field cannot be repeated in all records. Setting the only row constraint for a table is to add UNIQUE constraints to some special fields of the table when the table is created. The basic syntax is:

Attribute name data type UNIQUE

Example 6:

[sql]

CREATE TABLE example5 (id INT PRIMARY KEY

Stu_id INT UNIQUE

Name VARCHAR (20) NOT NULL

);

6. Set the attribute value of the table to increase automatically

AUTO_INCREMENT is a special constraint in My. It is mainly used to automatically generate a unique ID for new records inserted in the table. Only one field in a table can use the AUTO_INCREMENT constraint, and the field must be part of the primary key. By default, the value of this field increases from 1. The basic syntax is:

Attribute name data type AUTO_INCREMENT

Example 7:

[sql]

CREATE TABLE example6 (id INT PRIMARY KEY AUTO_INCREMENT

Stu_id INT UNIQUE

Name VARCHAR (20) NOT NULL

);

7. Set the default value of the properties of the table

The default value is set through the DEFAULT keyword. The basic syntax is:

Attribute name data type DEFAULT default value

Example 8:

[sql]

CREATE TABLE example7 (id INT PRIMARY KEY AUTO_INCREMENT

Stu_id INT UNIQUE

Name VARCHAR (20) NOT NULL

English VARCHAR (20) DEFAULT 'zero'

Math FLOAT DEFAULT 0

Computer FLOAT DEFAULT 0

);

The above is how to create datasheets in Mysql. The editor believes that there are some knowledge points that we may see or use in our daily work. I hope you can learn more from this article. For more details, please follow the industry information channel.

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.