MySQL 7: operation data table (1)

A data table (or table) is one of the most important components of a database and is the basis of other objects.

A data table (or table) is one of the most important components of a database and is the basis of other objects.

Data Table

A data table (or table) is one of the most important components of a database and the basis of other objects.

Solve the Problem of entering database verification mentioned at the end of the previous blog:

Verify which MySQL Command of the current database is:

Select database ();

Example:

Show databases;

USE t1;

Select database ();

1. Create a data table

The syntax format for creating a data table in MySQL syntax is:

Create table [if not exists] table_name (

Column_name data_type,

....

);

Example:

CREATE TABLE tb1 (          username VARCHAR(20),          age TINYINT UNSIGNED,          salary FLOAT(8,2) UNSIGNED);

2. View data tables

The syntax format for viewing the list of data tables in the current database is:

Show tables [FROM db_name] [LIKE 'pattern' | WHERE expr];

Example:

By default, if you do not enter a database name, you can view the list of data tables in the current database (Database t1:

Show tables;

Here we can not only view the current database, but also the list of data tables in other databases, and the current database is still open

The database (that is, the database t1) will not change.

Example: here we query the list of data tables in the MySQL database that comes with mysql service.

Show tables from mysql;

Select database ();

3. view the data table structure

The syntax format for viewing the structure of a data table is:

Show columns from table_name;

In other places, I also see another syntax for viewing the data table structure:

DESC table_name;

Verify that the application is the same.

Example:

Show columns from tb1;

DESC tb1;

IV Record insertion and search (1) INSERT command

Syntax format of the insert record:

INSERT [INTO] table_name [(col_name,...)] VALUES (val ,...);

Example:

If all fields are omitted, values are assigned to all fields:

INSERT tb1 VALUES ('Tom ', 25,733 4.25 );

If we omit a field value and do not write it, an error is returned,

INSERT tb1 VALUES ('Tom ', 25 );

If we only want to assign values to one or more fields, we need to write the names of the assigned fields:

INSERT tb1 (username, age) VALUES ('john', 22 );

(2) SECECT command

Query the syntax format of a record (Here we only view a simple record, and we will introduce the detailed syntax format of record view later ):

SELECT expr,... FROM table_name;

Example:

List all fields of a data table (for details later ).

SELECT * FROM tb1;

Five null values and non-empty values

NULL indicates that the field value can be NULL.

Not null, indicating that the field value cannot be blank.

Example:

CREATE TABLE tb2(            username VARCHAR(20) NOT NULL,            age TINYINT UNSIGNED NULL);

Show columns from tb2;

Suppose we insert a record now:

INSERT tb2 VALUES ('Tom ', NULL );

SELECT * FROM tb2;

INSERT tb2 VALUES (NULL, 23 );

In the next MySQL article, we will continue to operate on the data table, and will first learn about the design constraints.