Home > Database > Mysql Tutorial > Detailed explanation of MySQL data table operations

Detailed explanation of MySQL data table operations

迷茫
Release: 2017-03-26 14:03:58
Original
1355 people have browsed it

Create data table

Open database

USE database name

mysql> USE D1;
Database changed
Copy after login

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)
Copy after login

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)
Copy after login

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)
Copy after login

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)
Copy after login

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)
Copy after login

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
Copy after login

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)
Copy after login

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)
Copy after login

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)
    -> );
Copy after login

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)
Copy after login

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
Copy after login

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)
Copy after login

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)
Copy after login

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)
    Copy after login

    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)
Copy after login

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!

source:php.cn
Statement of this Website
The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn
Popular Tutorials
More>
Latest Downloads
More>
Web Effects
Website Source Code
Website Materials
Front End Template