Detailed explanation of MySQL data table operations

Create data table

Open database

USE database name

mysql> USE D1;
Database changed

Use USE D1; means open database D1 , we can view the currently open database through SELECT DATABASE();:

mysql> SELECT DATABASE();
+------------+
| DATABASE() |
+------------+
| d1         |
+------------+1 row in set (0.00 sec)

Create data table

CREATE TABLE [IF NOT EXISTS] table_name (
column_name datatype,
......
)

This structure is very simple, for [IF NOT EXISTS], in the first article "MySQL Basic Operations" has already been explained and will not be repeated here.

Let’s create a data tabletable1:

mysql> CREATE TABLE table1(
    -> username VARCHAR(20),
    -> age TINYINT UNSIGNED,
    -> salary FLOAT(8,2) UNSIGNED
    -> );
Query OK, 0 rows affected (0.74 sec)

Note that UNSIGNED here represents an unsigned value, which is a positive number. You can review the "MySQL basic data types" to view , TINYINT UNSIGNED represents a value between 0 ~ 255.

This prompts that the creation is successful. We can verify it through the following statement:

SHOW TABLES [FROM db_name][LIKE 'pattern' | WHERE expr]

mysql> SHOW TABLES FROM D1;
+--------------+
| Tables_in_d1 |
+--------------+
| table1       |
+--------------+1 row in set (0.00 sec)

Here we can see that table1 is created.

View the data table structure

SHOW COLUMNS FROM tbl_name

mysql> SHOW COLUMNS FROM table1;
+----------+---------------------+------+-----+---------+-------+
| Field    | Type                | Null | Key | Default | Extra |
+----------+---------------------+------+-----+---------+-------+
| username | varchar(20)         | YES  |     | NULL    |       |
| age      | tinyint(3) unsigned | YES  |     | NULL    |       |
| salary   | float(8,2) unsigned | YES  |     | NULL    |       |
+----------+---------------------+------+-----+---------+-------+3 rows in set (0.10 sec)

Insert records

After creating the table, you need to write the data Now, insert records through the following statement:

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

here[(col_name,...)] is optional. If it is not added, the values ​​in VALUE must correspond to the fields of the data table one by one, otherwise it cannot be inserted. Let’s take a look:

mysql> INSERT table1 VALUE("LI",20,6500.50);
Query OK, 1 row affected (0.14 sec)

The VALUE brackets here correspond to the fields of table1 one-to-one, which are username="LI", age=20, salary=6500.50

We will insert another piece of data below, but there is no correspondence:

mysql> INSERT table1 Value("Wang",25);
ERROR 1136 (21S01): Column count doesn't match value count at row 1

cannot be inserted because no salary value is given.

By adding [(col_name,...)], you can flexibly insert data:

mysql> INSERT table1(username,age) VALUE("Wang",25);
Query OK, 1 row affected (0.11 sec)

table1 corresponds to VALUE one-to-one.

Looking up table data

Two pieces of data have been inserted previously. You can look up table data through the following statement:

SELECT expr,... FROM tbl_name

For the database search statement SELECT, there is a lot of content. The following article will explain it in detail. We use a simple statement to find the contents of the table:

mysql> SELECT * FROM table1
    -> ;
+----------+------+---------+
| username | age  | salary  |
+----------+------+---------+
| LI       |   20 | 6500.50 |
| Wang     |   25 |    NULL |
+----------+------+---------+2 rows in set (0.00 sec)

Note that the MySQL statement starts with "; "At the end, if you forget to write, the statement cannot be executed, just add a semicolon after the arrow; here we can see that there are two pieces of data just written in the table.

Basic constraints on table creation

NULL and NOT NULL in fields

When creating a table, we can set whether the field can be empty. If it cannot be empty, , then when inserting data, it cannot be empty.

Let’s create a data tabletable2:

mysql> CREATE TABLE table2(
    -> username VARCHAR(20) NOT NULL,
    -> age TINYINT UNSIGNED NULL,
    -> salary FLOAT(8,2)
    -> );

Here username is non-empty, age is NULL, salary is not written, let’s check the table structure:

mysql> SHOW COLUMNS FROM table2;
+----------+---------------------+------+-----+---------+-------+
| Field    | Type                | Null | Key | Default | Extra |
+----------+---------------------+------+-----+---------+-------+
| username | varchar(20)         | NO   |     | NULL    |       |
| age      | tinyint(3) unsigned | YES  |     | NULL    |       |
| salary   | float(8,2)          | YES  |     | NULL    |       |
+----------+---------------------+------+-----+---------+-------+3 rows in set (0.01 sec)

From here we can see that NULL for username is NO, and the other two fields are YES. For fields that can be empty, writing NULL or not means they can be empty.

AUTO_INCREMENT

AUTO_INCREMENT

auto_increment, auto automatic, increment means increase. When combined, it means automatic increase, that is, it can automatically increase according to the to the highest sequential number.

• can only be used for primary keys (the primary key represents the unique representation of the data in the table, and the data in the table can be distinguished by the primary key)

• Default In this case, it is 1, and the increment is 1

Let’s do the following:

mysql> CREATE TABLE table3(
    -> id SMALLINT UNSIGNED AUTO_INCREMENT,
    -> username VARCHAR(20)
    -> );
ERROR 1075 (42000): Incorrect table definition; there can be only one auto column and it must be defined as a key

An error is reported because the id is not set as the primary key.

Set the primary key

PRIMARY KEY

• ##Primary key constraints

• Each The table can only have one primary key

• The primary key ensures the uniqueness of the record

• The primary key is automatically NOT NULL

Then we add the primary key and do it again:

mysql> CREATE TABLE table3(
    -> id SMALLINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    -> username VARCHAR(20)
    -> );
Query OK, 0 rows affected (0.42 sec)

Pay attention to the order, PRIMARY KEY should be placed last.

In this way, we have created it successfully. Let’s insert the data one by one and check the results:

mysql> INSERT table3(username) VALUES("Zhang");
Query OK, 1 row affected (0.09 sec)

mysql> INSERT table3(username) VALUES("Weng");
Query OK, 1 row affected (0.07 sec)

mysql> INSERT table3(username) VALUES("Chen");
Query OK, 1 row affected (0.09 sec)

mysql> SELECT * FROM table3;
+----+----------+
| id | username |
+----+----------+
|  1 | Zhang    |
|  2 | Weng     |
|  3 | Chen     |
+----+----------+3 rows in set (0.00 sec)

We can see that the IDs are automatically numbered, from small to large.

Unique Constraint

UNIQUE KEY

##Unique Constraint

• ##Unique Constraint Ensure that records are non-repeatable (unique)

• The unique constraint can be empty (NULL)

• There can be multiple unique constraints

• mysql> CREATE TABLE table4(
      -> id SMALLINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
      -> username VARCHAR(20) UNIQUE KEY,
      -> age TINYINT UNSIGNED
      -> );
  Query OK, 0 rows affected (0.43 sec)
  
  mysql> INSERT table4(username) VALUE("Li");
  Query OK, 1 row affected (0.11 sec)
  
  mysql> INSERT table4(username) VALUE("Li");
  ERROR 1062 (23000): Duplicate entry 'Li' for key 'username'
  
  mysql> INSERT table4(username) VALUE("Chen");
  Query OK, 1 row affected (0.10 sec)

  For username, we set it as a unique constraint, so Li cannot be created repeatedly, just change it to "Chen". Note that this is just an experiment. In actual operation, the same names are common, and the data table should be established according to the actual situation.

Default value DEFAULT

Set the default value through

DEFAULT

. If the corresponding value is not given when inserting data, then the default value will be used. The following example That is to set the default value of number to 3. When inserting data, because number is not given, the default value is 3.

mysql> CREATE TABLE table5(
    -> number ENUM("1","2","3") DEFAULT "3",
    -> username VARCHAR(20)
    -> );
Query OK, 0 rows affected (0.41 sec)

mysql> INSERT table5(username) VALUES("Luo");
Query OK, 1 row affected (0.10 sec)

mysql> INSERT table5(username) VALUES("Fang");
Query OK, 1 row affected (0.15 sec)

mysql> SELECT * FROM table5;
+--------+----------+
| number | username |
+--------+----------+
| 3      | Luo      |
| 3      | Fang     |
+--------+----------+2 rows in set (0.00 sec)

The above is the detailed content of Detailed explanation of MySQL data table operations. For more information, please follow other related articles on the PHP Chinese website!