MySQL Learning 7: Operational Data Sheet (i)

Data Sheet

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

First to solve the problem of entering database validation at the end of the last blog post:

Verify which MySQL command is the current database:

SELECT DATABASE ();

Example:

SHOW DATABASES;

Use T1;

SELECT DATABASE ();

Create a data table

The syntax format for creating data tables in MySQL syntax is:

CREATE TABLE [IF not EXISTS] table_name (

column_name Data_type,

            ....

);

Example:

<span style= "FONT-SIZE:18PX;" >create TABLE tb1 (          username VARCHAR), age          TINYINT UNSIGNED,          salary FLOAT (8,2) UNSIGNED); </span >

     

Two viewing data sheets

To view the list of data tables in the current database, the syntax format is:

SHOW TABLES [from db_name] [like ' pattern ' | WHERE expr];

Example:

By default, if you do not write the database name, you view the list of data tables under the current database (database T1):

SHOW TABLES;

We are here to not only the current database, but also to view the list of data tables in other databases, and the current database is the number of open

Database (that is, T1), and it does not change.

Example: Here we query a list of data tables in MySQL database that comes with MySQL service

SHOW TABLES from MySQL;

SELECT DATABASE ();

Three view data table structure

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

SHOW COLUMNS from table_name;

Elsewhere, I also saw another syntax for viewing the structure of a data table:

DESC table_name;

Verified, the same applies.

Example:

SHOW COLUMNS from TB1;

DESC tb1;

four insertion and lookup of records (1) Insert command

Syntax format for inserting records:

INSERT [INTO] table_name [(Col_name,...)] VALUES (Val,...);

Example:

If you omit all fields, you assign values to all fields:

INSERT tb1 VALUES (' Tom ', 25,7334.25);

If we omit the value of a field, the error is not written,

INSERT tb1 VALUES (' Tom ', +);

If we just want to assign a value to one or some of the fields, we'll write out the names of those assigned fields:

INSERT tb1 (username,age) VALUES (' John ', ');

(2) secect command

Find the syntax format of the record (here is just a simple record view, followed by a detailed look at the syntax format of the record):

SELECT Expr,... from table_name;

Example:

Lists all the fields of a data table, described in more detail later.

SELECT * from TB1;

Five null and non-empty

NULL, which indicates that the field value can be empty.

Not NULL, which indicates that the field value is forbidden to be empty.

Example:

<span style= "FONT-SIZE:18PX;" >create TABLE TB2 (            username VARCHAR) not NULL, age            TINYINT UNSIGNED NULL);</span>

SHOW COLUMNS from TB2;

Let's say we insert a record now:

INSERT tb2 VALUES (' Tom ', NULL);

SELECT * from TB2;

INSERT tb2 VALUES (null,23);

Next MySQL article we continue to operate the data sheet, and will initially design the knowledge of constraints.

MySQL Learning 7: Operational Data Sheet (i)