MySQL foundation - basic operation of tables

Keywords: Database MySQL SQL

After the database is built, we can then create tables that actually store data. When creating a table, you first need to describe what the table looks like, what columns it has, what types of data these columns are used to store, etc. this description of the table is called the structure or definition of the table. With the structure of the table, we can start to plug the data into the table. A row in a table is called a record and a column is called a field.

Displays the tables in the current database

The following statements are used to show which tables are in the current database:

SHOW TABLES;

Our current database is xiaohaizi, and then use the above statement to view the following tables in xiaohaizi database:

mysql> SHOW TABLES;
Empty set (0.00 sec)

mysql>

Sorry, there is no table in xiaohaizi database, so the result is Empty set. We quickly create several tables in the current database.

Create table

Basic grammar

When creating a table, you need to do at least the following:

Give the watch a name.
Define some columns for the table and give them names.
Each column needs to define a data type.
If necessary, you can define some column properties for these columns, such as not storing NULL, setting default values, etc. we will talk about which properties can be set for specific columns later.

The basic syntax for creating tables in MySQL is as follows:

CREATE TABLE Table name (
    Column name 1    data type    [Column properties],
    Column name 2    data type    [Column properties],
    ...
    Listing n    data type    [Column properties]
);

in other words:

After creating table, write the name of the table we want to create.
Then define the information of each column of the table in parentheses (), including the name of the column and the data type of the column. If necessary, you can also define the attributes of the column (the attributes of the column are enclosed in brackets [], which means that this part is optional, that is, optional).
The column name, data type and column attributes should be separated by white space characters, and then the information of each column should be separated by commas.

Tip: we can also put the statement to create the table in a single line. In the example, the statement to create the table is divided into multiple lines and indented just for beauty

Without much nonsense, quickly define a super simple watch:

CREATE TABLE first_table (
    first_column INT,
    second_column VARCHAR(100)
);

The name of this table is called first_table, which has two columns:

The name of the first column is first_column. Its data type is INT, which means that only integers can be stored.
The name of the second column is second_column, whose data type is VARCHAR(100), means that this column can store strings with a length of no more than 100 characters.

Let's execute this statement on the client (the current database is xiaohaizi):

mysql> CREATE TABLE first_table (
    ->     first_column INT,
    ->     second_column VARCHAR(100)
    -> );
Query OK, 0 rows affected (0.02 sec)

mysql>

Outputting Query OK, 0 rows affected (0.02 sec) means that the creation is successful, and it takes 0.02 seconds.

Add comments for the table creation statement

When creating a table, we can add the use of the table to the statement in the form of comments, as long as the COMMENT statement is added at the end of the statement, as follows:

CREATE TABLE Table name (
    Information for each column ...
) COMMENT 'Comment information for table';

For example, we can write first like this_ Table creation statement of table:

CREATE TABLE first_table (
    first_column INT,
    second_column VARCHAR(100)
) COMMENT 'First table';

The notes don't need to be too long. They can be concise and comprehensive. After all, they are for people to see. It's good to let people see what they mean. For our own convenience and for the convenience of reading the people you created, please abide by professional ethics and write a note ~

Create real-life tables

With create first_ Based on the experience of table, we can start to create the previously mentioned student basic information table and grade table with MySQL. First move down the student basic information table and have a look:

Student basic information form

Student number	full name	Gender	ID number	college	major	Admission time
20180101	Du Ziteng	male	158177199901044792	school of computing	Computer science and Engineering	2018/9/1
20180102	Du Qiyan	female	151008199801178529	school of computing	Computer science and Engineering	2018/9/1
20180103	Fan Tong	male	17156319980116959X	school of computing	software engineering	2018/9/1
20180104	Shi Zhenxiang	female	141992199701078600	school of computing	software engineering	2018/9/1

Obviously ID number name gender gender ID number college degree and admission time are the number of the students. The student ID number is integer type. The enrollment time is the date type. Since the ID card number is fixed 18, we can define the ID card number as a fixed length string type. So we define it here as ENUM type, and the other columns are variable length string types. Take a look at the statement to create the student basic information table:

CREATE TABLE student_info (
    number INT,
    name VARCHAR(5),
    sex ENUM('male', 'female'),
    id_number CHAR(18),
    department VARCHAR(30),
    major VARCHAR(30),
    enrollment_time DATE
) COMMENT 'Student basic information form';

Then look at the student transcript:

Student transcript

Student number	subject	achievement
20180101	Postpartum care of sows	78
20180101	On Saddam's war preparation	88
20180102	Postpartum care of sows	100
20180102	On Saddam's war preparation	98
20180103	Postpartum care of sows	59
20180103	On Saddam's war preparation	61
20180104	Postpartum care of sows	55
20180104	On Saddam's war preparation	46

The table has columns of student number, subject and grade. The student number and grade are of integer type and the subject is of string type. Therefore, we can write the table creation statement as follows:

CREATE TABLE student_score (
    number INT,
    subject VARCHAR(30),
    score TINYINT
) COMMENT 'Student transcript';

After these tables are created successfully, let's use the SHOW TABLES statement to see which tables are in the current database (xiaohaizi database):

mysql> SHOW TABLES;
+---------------------+
| Tables_in_xiaohaizi |
+---------------------+
| first_table         |
| student_info        |
| student_score       |
+---------------------+
3 rows in set (0.01 sec)

mysql>

The tables we just created are displayed. Hurry to your own client to fill in these statements ~

IF NOT EXISTS

Like creating a database repeatedly, if you create an existing table, an error will be reported. Let's try creating first repeatedly_ Table:

mysql> CREATE TABLE first_table (
    ->     first_column INT,
    ->     second_column VARCHAR(100)
    -> ) COMMENT 'First table';
ERROR 1050 (42S01): Table 'first_table' already exists
mysql>

The execution result prompts an ERROR, which means first_table already exists! Therefore, if you want to avoid this ERROR, you can use this form when creating a table:

CREATE TABLE IF NOT EXISTS Table name(
    Information for each column ...
);

The statement with IF NOT EXISTS means that if the specified table name does not exist, the table will be created. If it does exist, nothing will be done. We use this IF NOT EXISTS syntax to create first again_ Statement of table:

mysql> CREATE TABLE IF NOT EXISTS first_table (
    ->     first_column INT,
    ->     second_column VARCHAR(100)
    -> ) COMMENT 'First table';
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql>

You can see that the statement is executed successfully, but there is only one warning in the result.

Delete table

If we don't think we can use a table in the future, we can delete it. When deleting a table in a real working environment, you must be careful. What you lose will never come back. Take a look at the deletion syntax:

DROP TABLE Table 1, Table 2, ..., surface n;

That is, we can delete multiple tables at the same time. Let's put first now_ Delete the table:

mysql> DROP TABLE first_table;
Query OK, 0 rows affected (0.01 sec)

mysql> SHOW TABLES;
+---------------------+
| Tables_in_xiaohaizi |
+---------------------+
| student_info        |
| student_score       |
+---------------------+
2 rows in set (0.00 sec)

mysql>

You can see that there is no first in the database xiaohaizi_ Table, indicating that the deletion was successful!

IF EXISTS

If we try to delete a non-existent table, we will report an error:

mysql> DROP TABLE first_table;
ERROR 1051 (42S02): Unknown table 'xiaohaizi.first_table'
mysql>

The execution result prompts an ERROR, indicating that the table to be deleted does not exist. If you want to avoid ERROR reporting, you can use this deletion syntax:

DROP TABLE IF EXISTS Table name;

Then delete the nonexistent first_table:

mysql> DROP TABLE IF EXISTS first_table;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql>

So you don't report an error ~

View table structure

Sometimes we may forget the table structure defined by ourselves. You can use the following statements to view it. They all have the same effect:

DESCRIBE Table name;
DESC Table name;
EXPLAIN Table name;
SHOW COLUMNS FROM Table name;
SHOW FIELDS FROM Table name;

For example, let's take a look at student_info table structure:

mysql> DESC student_info;
+-----------------+-------------------+------+-----+---------+-------+
| Field           | Type              | Null | Key | Default | Extra |
+-----------------+-------------------+------+-----+---------+-------+
| number          | int(11)           | YES  |     | NULL    |       |
| name            | varchar(5)        | YES  |     | NULL    |       |
| sex             | enum('male','female')   | YES  |     | NULL    |       |
| id_number       | char(18)          | YES  |     | NULL    |       |
| department      | varchar(30)       | YES  |     | NULL    |       |
| major           | varchar(30)       | YES  |     | NULL    |       |
| enrollment_time | date              | YES  |     | NULL    |       |
+-----------------+-------------------+------+-----+---------+-------+
7 rows in set (0.00 sec)

mysql>

As you can see, this student_ The names, types, and properties of the columns in the info table are displayed. Of course, we haven't learned the properties of columns yet (we'll talk about it in the next chapter), so we just need to look at the Field and Type columns in the results.

If you don't like the way to display the information of each column in the form of a table, we can also use the following statement to view the table structure:

SHOW CREATE TABLE Table name;

For example:

mysql> SHOW CREATE TABLE student_info;
+--------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table        | Create Table                                                                                                                                                                                                                                                                                                                                                          |
+--------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| student_info | CREATE TABLE `student_info` (
  `number` int(11) DEFAULT NULL,
  `name` varchar(5) DEFAULT NULL,
  `sex` enum('male','female') DEFAULT NULL,
  `id_number` char(18) DEFAULT NULL,
  `department` varchar(30) DEFAULT NULL,
  `major` varchar(30) DEFAULT NULL,
  `enrollment_time` date DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='Student basic information form'          |
+--------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql>

Because this line of data is too long, the output effect is not very good. We can use the semicolon originally used to mark the end of the statement; Change to \ G, and the effect of displaying each column of data in a vertical way may be better:

mysql> SHOW CREATE TABLE student_info\G
*************************** 1. row ***************************
       Table: student_info
Create Table: CREATE TABLE `student_info` (
  `number` int(11) DEFAULT NULL,
  `name` varchar(5) DEFAULT NULL,
  `sex` enum('male','female') DEFAULT NULL,
  `id_number` char(18) DEFAULT NULL,
  `department` varchar(30) DEFAULT NULL,
  `major` varchar(30) DEFAULT NULL,
  `enrollment_time` date DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='Student basic information form'
1 row in set (0.00 sec)

mysql>

You can see that the table structure displayed by using the SHOW CREATE TABLE statement is the statement we usually create a table, and some attributes that we haven't talked about are automatically added to each column (don't worry about the meaning of those attributes now, we'll talk about them later).

Tip: you may wonder what ENGINE=InnoDB DEFAULT CHARSET=utf8 at the end of the table definition means. This specifies the storage engine and default character set of the table. These contents are not the contents that Xiaobai should learn from. Interested students can learn from how MySQL works: understanding MySQL from the root. You don't need to care about them now

Operation on the table when the current database is not selected

Sometimes we do not USE the USE statement to select the current database, or the tables encountered in a statement are scattered in different databases. If we want to USE these tables in the statement, we must explicitly specify the database to which these tables belong. For example, whether the current database is xiaohaizi or not, we can call this statement to display the tables in the database xiaohaizi:

mysql> SHOW TABLES FROM xiaohaizi;
+---------------------+
| Tables_in_xiaohaizi |
+---------------------+
| first_table         |
| student_info        |
| student_score       |
+---------------------+
3 rows in set (0.00 sec)

mysql>

If the table name is used elsewhere, the database to which the table belongs needs to be explicitly specified in the following way:

Database name.Table name

For example, we want to view first in xiaohaizi database_ The structure of the table, but the current database is not specified by the USE xiaohaizi statement. In this case, you can write the following statement:

SHOW CREATE TABLE xiaohaizi.first_table\G

Modify table

After the table is created, if you are not satisfied with the table structure, such as adding or deleting a column, modifying the data type or attribute of a column, and renaming the table name or column name, these operations are all regarded as modifying the table structure. MySQL provides us with a series of statements to modify the table structure.

Modify table name

We can modify the name of the table in the following two ways:

Mode 1:

ALTER TABLE Old table name RENAME TO New table name;

We put first_ Change the name of the table to first_table1 (the current database is xiaohaizi):

mysql> ALTER TABLE first_table RENAME TO first_table1;
Query OK, 0 rows affected (0.01 sec)

mysql> SHOW TABLES;
+---------------------+
| Tables_in_xiaohaizi |
+---------------------+
| first_table1        |
| student_info        |
| student_score       |
+---------------------+
3 rows in set (0.00 sec)

mysql>

Through the SHOW TABLES command, you can see that the name has been changed successfully.

Mode 2:
```
RENAME TABLE Old table name 1 TO New table name 1, Old table name 2 TO New table name 2, ... Old table name n TO New table name n;
```
The great thing about this renaming method is that it can modify the names of multiple tables in one statement. There is no example here. Test it yourself.

If the database name is specified when modifying the table name, you can also transfer the table to the corresponding database. For example, we can create a database dahaizi first:

mysql> CREATE DATABASE dahaizi;
Query OK, 1 row affected (0.00 sec)

mysql>

Then put the first_ Table 1 is transferred to this dahaizi database:

mysql> ALTER TABLE first_table1 RENAME TO dahaizi.first_table1;
Query OK, 0 rows affected (0.01 sec)

mysql> SHOW TABLES FROM dahaizi;
+-------------------+
| Tables_in_dahaizi |
+-------------------+
| first_table1      |
+-------------------+
1 row in set (0.00 sec)

mysql> SHOW TABLES FROM xiaohaizi;
+---------------------+
| Tables_in_xiaohaizi |
+---------------------+
| student_info        |
| student_score       |
+---------------------+
2 rows in set (0.00 sec)

mysql>

You can see first_table1 is transferred from the database xiaohaizi to dahaizi. We can modify the table name again, and then transfer the table to xiaohaizi database, and rename it first_table:

mysql> RENAME TABLE dahaizi.first_table1 TO xiaohaizi.first_table;
Query OK, 0 rows affected (0.00 sec)

mysql>

Add column

We can use the following statement to add columns in the table:

ALTER TABLE Table name ADD COLUMN Column name data type [Column properties];

For example, we report to first_ Add a name called third to the table_ The column of column can be written as follows:

mysql> ALTER TABLE first_table ADD COLUMN third_column CHAR(4) ;
Query OK, 0 rows affected (0.05 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> SHOW CREATE TABLE first_table\G
*************************** 1. row ***************************
       Table: first_table
Create Table: CREATE TABLE `first_table` (
  `first_column` int(11) DEFAULT NULL,
  `second_column` varchar(100) DEFAULT NULL,
  `third_column` char(4) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='First table'
1 row in set (0.01 sec)

mysql>

You can see the third by looking at the structure of the table_ Column column has been added successfully.

Add columns to specific locations

By default, the following are added to the last column of the existing column. We can also specify its position when adding a column. The common methods are as follows:

Add to first column:

ALTER TABLE Table name ADD COLUMN Column name the type of the column [Column properties] FIRST;

Let's take fourth_ Insert column into the first column:

mysql> ALTER TABLE first_table ADD COLUMN fourth_column CHAR(4) FIRST;
Query OK, 0 rows affected (0.04 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> SHOW CREATE TABLE first_table\G
*************************** 1. row ***************************
   Table: first_table
Create Table: CREATE TABLE `first_table` (
`fourth_column` char(4) DEFAULT NULL,
`first_column` int(11) DEFAULT NULL,
`second_column` varchar(100) DEFAULT NULL,
`third_column` char(4) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='First table'
1 row in set (0.01 sec)

mysql>

See that the insertion succeeded.

Add to the back of the specified column:

ALTER TABLE Table name ADD COLUMN Column name the type of the column [Column properties] AFTER Specify column name;

Insert another fifth_column to first_column:

mysql> ALTER TABLE first_table ADD COLUMN fifth_column CHAR(4) AFTER first_column;
Query OK, 0 rows affected (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> SHOW CREATE TABLE first_table\G
*************************** 1. row ***************************
       Table: first_table
Create Table: CREATE TABLE `first_table` (
  `fourth_column` char(4) DEFAULT NULL,
  `first_column` int(11) DEFAULT NULL,
  `fifth_column` char(4) DEFAULT NULL,
  `second_column` varchar(100) DEFAULT NULL,
  `third_column` char(4) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='First table'
1 row in set (0.00 sec)

mysql>

You can see fifth_ The column is inserted into first_ The column is behind the column.

Delete column

We can use the following statement to delete the columns in the table:

ALTER TABLE Table name DROP COLUMN Listing;

We just told first_ Add several columns to the table and delete them:

mysql> ALTER TABLE first_table DROP COLUMN third_column;
Query OK, 0 rows affected (0.05 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> ALTER TABLE first_table DROP COLUMN fourth_column;
Query OK, 0 rows affected (0.05 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> ALTER TABLE first_table DROP COLUMN fifth_column;
Query OK, 0 rows affected (0.04 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> SHOW CREATE TABLE first_table\G
*************************** 1. row ***************************
       Table: first_table
Create Table: CREATE TABLE `first_table` (
  `first_column` int(11) DEFAULT NULL,
  `second_column` varchar(100) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='First table'
1 row in set (0.00 sec)

mysql>

From the results, we can see that third_column,fourth_column,fifth_column these columns have been deleted.

Modify column information

There are two ways to modify column information:

Mode 1:
```
ALTER TABLE Table name MODIFY Column name new data type [new property];
```
Let's modify first_ Second of table_ Column, change its data type to VARCHAR(2):
```
mysql> ALTER TABLE first_table MODIFY second_column VARCHAR(2);
Query OK, 0 rows affected (0.04 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> SHOW CREATE TABLE first_table\G
*************************** 1. row ***************************
       Table: first_table
Create Table: CREATE TABLE `first_table` (
  `first_column` int(11) DEFAULT NULL,
  `second_column` varchar(2) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='First table'
1 row in set (0.00 sec)    

mysql>
```
You can see second_ The data type of column has been changed to VARCHAR(2). However, when modifying column information, you should note that the modified data types and attributes must be compatible with the existing data in the table! For example, the original first_ The type of table is VARCHAR(100), which can store up to 100 characters if the second of a record in the table_ The column value is' aaa ', that is, it takes three characters. At this time, we try to use the above statement to set second_ If the data type of column is changed to VARCHAR(2), an error will be reported because VARCHAR(2) cannot store 3 characters.

Mode 2:

ALTER TABLE Table name CHANGE Old column name new column name new data type [new property];

You can see that this modification method requires us to fill in two column names, that is, you can modify column names while modifying data types and attributes! For example, we modify second_ The column name of column is second_column1:

mysql> ALTER TABLE first_table CHANGE second_column second_column1 VARCHAR(2)\G
Query OK, 0 rows affected (0.04 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> SHOW CREATE TABLE first_table\G
*************************** 1. row ***************************
       Table: first_table
Create Table: CREATE TABLE `first_table` (
  `first_column` int(11) DEFAULT NULL,
  `second_column1` varchar(2) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='First table'
1 row in set (0.00 sec)

mysql>

You can see second in the result_ The column name of column has been changed to second_column1, but we haven't changed the data type and attribute of this column, so just copy the old data type and attribute directly.

Modify column arrangement position

If we think there is a problem with the current column order, we can use the following statements to modify it:

Set the column as the first column of the table:

ALTER TABLE Table name MODIFY Column name column type column properties FIRST;

Let's look at the present table first_ Order of columns in table:

mysql> SHOW CREATE TABLE first_table\G
*************************** 1. row ***************************
       Table: first_table
Create Table: CREATE TABLE `first_table` (
  `first_column` int(11) DEFAULT NULL,
  `second_column1` varchar(2) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='First table'
1 row in set (0.00 sec)

mysql>

You can see that the order of columns is: first_column,second_column1. Now we want to put second_column is placed in the first column, which can be written as follows:

mysql> ALTER TABLE first_table MODIFY second_column1 VARCHAR(2) FIRST;
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> SHOW CREATE TABLE first_table\G
*************************** 1. row ***************************
       Table: first_table
Create Table: CREATE TABLE `first_table` (
  `second_column1` varchar(2) DEFAULT NULL,
  `first_column` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='First table'
1 row in set (0.00 sec)

mysql>

See second_column1 has become the first column!

Place the column behind the specified column:

ALTER TABLE Table name MODIFY Column name column type column properties AFTER Specify column name;

For example, we want to put second_ Put column1 to first_ The following can be written after column:

mysql> ALTER TABLE first_table MODIFY second_column1 VARCHAR(2) AFTER first_column;
Query OK, 0 rows affected (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> SHOW CREATE TABLE first_table\G
*************************** 1. row ***************************
       Table: first_table
Create Table: CREATE TABLE `first_table` (
  `first_column` int(11) DEFAULT NULL,
  `second_column1` varchar(2) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='First table'
1 row in set (0.00 sec)

mysql>

A statement contains multiple modification operations

If there are multiple modification operations on the same table, we can execute them in one statement, like this:

ALTER TABLE Table name operation 1, Operation 2, ..., operation n;

Above, we used three statements to delete third when demonstrating the operation of deleting columns_ column,fourth_column and fifth_column. In fact, these three statements can be combined into one:

ALTER TABLE first_table DROP COLUMN third_column, DROP COLUMN fourth_column, DROP COLUMN fifth_column;

In this way, there are fewer statements typed by people, and the server does not need to execute multiple times, so the efficiency is also high

Posted by madmega on Wed, 10 Nov 2021 16:34:38 -0800

Programmer Group