Introduction to Mysql data table operation methods

This article brings you an introduction to the Mysql data table operation method. It has certain reference value. Friends in need can refer to it. I hope it will be helpful to you.

Data table interpolation

The most important operation in operating the data table is to save our website data and user data. Let’s take a look at the command rules first:

INSERT [INTO] tbl_name [(col_name,col_name,...)] VALUES(val,val,...)

From the above rules we can see that [(col_name,col_name,...)] can be filled in or not. The difference is; if not filled in [(col_name, col_name,...)] must pass in the values ​​of all fields at once. Fill in [(col_name, col_name,...)] to pass in the corresponding col_name value. (Recommended course: MySQL Tutorial)

So, let’s check the structure of the data table user we built last time. After passing the value, enter the command

SHOW COLUMNS FROM user;

We can see that there are four fields usename,age,passwrod,gz, Let’s interpolate all fields at once.

INSERT user VALUES('Tom',25,'abc123456',12000);

Running command~successful.

##Now let’s give usename,passwrodInterpolation between two fields

INSERT user (usename,passwrod) VALUES('Jieke','101010');

Run command~success

Find the record value

We have just inserted several values, now let’s query whether the value of the field is the same as what we inserted.

Find record value command rules:

SELECT expr,... FROM tbl_name;

Enter the command:

//当然实际上的查询命令非常，现在只是演示简单查找命令
SELECT * FROM user;

You can see that the values ​​just inserted exist. in the datasheet.

Null and non-null values

In the website registration information, there are settings for required fields and fillable fields. This setting is also available in mysql, which is null and non-null values ​​

NULL, NOT NULL. Now let's create a new data table and create fields.

CREATE TABLE newuser(
    name VARCHAR(20) NOT NULL,
    age TINYINT UNSIGNED NULL
)

We set two fields above, the name field cannot be empty, and the age field can be empty. Now let’s insert the field value

INSERT newuser (name,age) VALUES('Timo',null);

The value was inserted successfully.

**Now, let’s try what happens if the value of the
name field is NULL.

INSERT newuser (name,age) VALUES(NULL,NULL);

You can see the error,

Columns 'name' cannot be null The name field cannot be set to a null value. So the null and non-null values ​​we set have successfully taken effect.

Default value

We can set the default value for the field

DEFAULT. When inserting a record, if there is no explicit value assigned, the set default value will be automatically assigned.
Now let’s re-create a data table tb2 and set the default value for sex in name,sex. Enter the command line:

CREATE TABLE tb2(
    name VARCHAR(20),
    sex ENUM('1','2','3') DEFAULT '3'
    );

Insert record name, do not insert record for sex

INSERT tb2(name) VALUES('ggb');

The insertion is successful, we record the output in the data table and see if sex has a value

##You can see that the

sex

value is 3, which has been assigned the default value.

The above is the detailed content of Introduction to Mysql data table operation methods. For more information, please follow other related articles on the PHP Chinese website!