MySQL Basics-Modification and deletion of databases and tables

Basic article-Modification and deletion of database and table modification and deletion I. Introduction to experiments

In this section of the experiment, we will learn and practice how to modify, delete, rename, and manipulate the contents of the database.

II. Preparation of the experiment

Before you can formally start this experiment, you need to download the relevant code first.

The code can create a new two databases, named test_01 and mysql_shiyan , and mysql_shiyan build 4 tables (department,employee,project,table_1) in the database, and then insert data into them.

To do this, first enter the command into the /home/shiyanlou/Desktop directory:

cd /home/shiyanlou/Desktop

Then enter the command to download the code:

git clone http://git.shiyanlou.com/shiyanlou/SQL5

When the download is complete, enter the command to open the MySQL service and log in with the root user:

#打开 MySQL 服务sudo service mysql start        #使用 root 用户登录mysql -u root

Download the SQL5 directory, there are 1 files MySQL-05.sql (SQL5 directory on the desktop, you can use Gedit to view, edit the files inside. ）

To load the data in the file, you need to enter the command in the MySQL console to complete the experiment preparation:

source /home/shiyanlou/Desktop/SQL5/MySQL-05.sql

Third, the experiment content 1, to the database modification

Use SHOW DATABASES; the command to see MySQL-05.sql the two databases that were just running the file generation:

Now we run the command to delete test_01 the database named:

DROP DATABASE test_01;

Now use the command again SHOW DATABASES; to discover that the test_01 database has been successfully deleted:

2. Modification of a table (1) Renaming a table

There are several forms of renaming a table, and the following 3 formatting effects are the same:

RENAME TABLE 原名 TO 新名字;ALTER TABLE 原名 RENAME 新名;ALTER TABLE 原名 RENAME TO 新名;

Use the command to try to modify table_1 the name as table_2 :

(2) Delete a table

Delete a table statement, similar to the one you just used to delete a database, in the following format:

DROP TABLE 表名字;

For example table_2 , we delete the table:

3. Modification of a column (i.e., modification of the table structure)

Modification of the table structure is the difficulty of this section of the experiment, and sometimes some small mistakes can cause irreversible consequences, so please be careful to operate. Also note that it is not necessary to avoid changing the table structure as much as possible.

(1) Add a column

The statement format for adding a column to the table is:

ALTER TABLE 表名字 ADD COLUMN 列名字 数据类型 约束;或： ALTER TABLE 表名字 ADD 列名字 数据类型 约束;

Now that there are 6 columns in the Employee table id、name、age、salary、phone、in_dpt , we try to add height a column (height) and specify the default constraint:

You can see that the newly added columns are placed by default at the far right of the table. If you want to insert the added column at the specified location, you need to use the After keyword at the end of the statement ("after column 1" means that the new column is placed after "column 1").

For example, we add a column weight (weight) placed age behind (age):

The effect above is to add the new column to the back of a position, if you want to place in the first column, then use FIRST keywords, such as statements:

ALTER TABLE employee ADD test INT(10) DEFAULT 11 FIRST;

The effect is as follows:

(2) Delete a column

Deleting a column in a table is very similar to the statement format for the new column you just used, but instead of changing the keyword to ADD DROP a data type, constraint, or location information after the statement. Specific Statement format:

ALTER TABLE 表名字 DROP COLUMN 列名字;或： ALTER TABLE 表名字 DROP 列名字;

We have just added the test deletion:

(3) Renaming a column

This statement can actually be used not only to rename a column, but to say exactly that it is modifying a column (change):

ALTER TABLE 表名字 CHANGE 原列名 新列名 数据类型 约束;

Note: the "data type" After this rename statement cannot be omitted, otherwise renaming fails.

When the original column name and the new column name are the same, specifying a new data type or constraint can be used to modify the data type or constraint. It is important to note that modifying the data type may result in data loss, so use it sparingly.

We use this statement to rename the "height" column to the Hanyu Pinyin "Shengao", the effect is as follows:

(4) Changing the data type

To modify the data type of a column, you can use the Modify statement in addition to the change statement that you just made:

ALTER TABLE 表名字 MODIFY 列名字 新数据类型;

Again, you must be careful about modifying data types, as this can result in data loss. Please consider carefully before attempting to modify the data type.

4. Modify the contents of the table (1) Modify a value in the table

Most of the time we need to make changes not the entire database or the entire table, but one or more of the data in the table, which requires us to use this command to achieve precise modification:

UPDATE 表名字 SET 列1=值1,列2=值2 WHERE 条件;

For example, we want to change Tom's age to 21,salary to 3000:

Note: Be sure to have a where condition, or there will be consequences you don't want to see

(2) Delete a row of records

Deleting a row of data in a table must also be accompanied by a where condition, otherwise the entire column of data will be deleted. Delete statement:

DELETE FROM 表名字 WHERE 条件;

We tried to delete Tom's data:

Iv. Summary

In this section of the experiment, we learned how to modify and delete databases, database tables, database table entries, and records.

V. Homework

1. Follow the procedure: Create a database with the downloaded code, insert the data, and try out various modification statements. The experimental process.
2. Use an UPDATE statement without a WHERE condition to see what the undesirable consequences (and descriptions) are.

MySQL Basics-Modification and deletion of databases and tables