(Big Data Engineer Learning path) Fourth Step SQL Basic Course----Modification and deletion

First, prepare

Before you can formally start this content, you need to download the relevant code from GitHub first. The code can create two new databases, named test_01 and Mysql_shiyan , and build 4 tables in the Mysql_shiyan database (Department,employee, Project,table_1), and then insert the data into it.

To do this, first enter the command into desktop:

cd Desktop

Then enter the command to download the code:

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

When the download is complete, enter "CD ~" (note that there are spaces) to return to the original directory, then enter the command to open the MySQL service and log in with the root user:

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

Just download the SQL5 directory from GitHub, there are 1 files mysql-05.sql (SQL5 directory on the desktop, you can use Gedit to view, edit the files inside. )

Enter the command to run the file and complete the experiment preparation:

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

Ii. content 1, modification of the database

Use the command SHOW DATABASES; you can see the two databases that were just running the mysql-05.sql file:

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

DROP DATABASE test_01;

Now use the command again to show DATABASES; you can see that thetest_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 's name to 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, we delete the table_2 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.

(1) Add a column

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

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

Now that the employee table has 6 columns of ID, name, age, salary, phone, IN_DPT, we try to add a column of height (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 of weight (weight ) to the back of 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 the primary keyword, such as statement:

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

The effect is as follows:

(2) Delete a column

The deletion of a column in a table is similar to the one for the new column you just used, except that the keyword ADD is changed to DROP and no data type, constraint, or location information is required after the statement. Specific Statement format:

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

Let's delete the test we just added:

(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:

(Big Data Engineer Learning path) Fourth Step SQL Basic Course----Modification and deletion