(Big Data Engineer Learning path) Fourth Step SQL Foundation Course----Others (end of basic practice)

First, prepare

Before formally starting this content, you need to download the relevant code from GitHub and set up a database called Mysql_shiyan (with three tables: Department,employee,project) and insert the data into it.

To do this, first enter the command into the TMP directory:

cd /tmp

Then enter the command to download the code:

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

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用户登录

The SQL6 directory you just downloaded from GitHub has a two file, "Mysql-06.sql" and "In.txt", where the first file is used to create the database and insert data into it, and the second file is used for subsequent experimental steps.

( You can enter the/TMP/SQL6 directory to view the contents of the two files in the gedit. )

Enter a command to run the first file, build the database, and insert the data:

source /tmp/SQL6/MySQL-06.sql

Ii. Contents 1, Index

An index is a table-related structure that acts like a book's directory and can quickly find what you want based on the page numbers in the catalog. When there are a large number of records in the table, to query the table, there is no index for full table search: Take all records one by one out, compare the query criteria one by one, and return the records that meet the criteria. Doing so consumes a lot of database system time and creates a lot of disk I/O operations. If an index is indexed in the table and the index value matches the query criteria, the data in the table can be quickly found by index values, which can greatly speed up the query .

To index a column in a table, the following two statement formats are available:

ALTER TABLE 表名字 ADD INDEX 索引名 (列名);CREATE INDEX 索引名 ON 表名字 (列名);

We use these two types of statements to index each:

ALTER TABLE employee ADD INDEX idx_id (id); #在employee表的id列上建立名为idx_id的索引CREATE INDEX idx_name ON employee (name); #在employee表的name列上建立名为idx_name的索引

The effect of the index is to speed up the query, when the data in the table is not enough to feel its effect. Here we use the command Show index from table name; View the index you just created:

When querying with a SELECT statement, the conditions in the where in the statement automatically determine if there are any indexes available .

2. View

A view is a table that is presented from one or more tables and is a virtual existence . It is like a window through which you can see the data that is specifically provided by the system so that users do not have to see the data in the entire database, but only the data that is useful to them.

Note that the view is a virtual table:

• The database contains only the definition of the view, not the data in the view, the data is stored in the original table;
• When querying data using a view, the database system will fetch the corresponding data from the original table;
• The data in the view depends on the data in the original table, and once the data in the table changes, the data displayed in the view changes;
• When you use a view, you can think of it as a single table.

The statement format for creating the view is:

CREATE VIEW 视图名(列a,列b,列c) AS SELECT 列1,列2,列3 FROM 表名字;

The statement that creates the view is visible, the second half is a SELECT query statement, so the view can also be built on more than one table , using subqueries or connection queries in the SELECT statement, which have been done in previous experiments.

Now we create a simple view named v_emp, which contains v_name,v_age, andv_phone three columns:

3. Import

The import operation allows you to save the data in a file into a single table. The import statement format is:

LOAD DATA INFILE ‘文件路径‘ INTO TABLE 表名字;

Now that there is a file named in.txt in the/tmp/sql6 directory, we try to import the data from this file into the employee table Mysql_shiyan the database.

Press CTRL + Z to exit MySQL, and then use the command gedit/tmp/sql6/in.txt to view the contents of the test.txt file:

Then use the following command to log in to the database as root and connect to the Mysql_shiyan database:

mysql -u rootuse mysql_shiyan

Look at the data in the employee table before the data is imported:

Now execute the import statement, the data in the file is successfully imported into the employee table:

4. Export

Exporting and importing is the opposite process of saving data from a table in a database to a file. The basic format of the export statement is:

SELECT 列1，列2 INTO OUTFILE ‘文件路径和文件名‘ FROM 表名字;

Note: You cannot have a file with the same name under file path in the statement.

Now we export the entire employee table data to the/TMP directory, the export file named OUT.txt specific statement is:

SELECT * INTO OUTFILE ‘/tmp/out.txt‘ FROM employee;

Use Gedit to view the contents of the exported file OUT.txt:

5. Backup

The data in the database may be very important, for security reasons, you should pay attention to using the backup function in the database usage.

The difference between backup and export: The exported file only saves the data in the database, while the backup is the structure of the database, including data, constraints, indexes, views, and so on all save as a file.

mysqldump is the utility that MySQL uses to back up the database. It primarily produces a SQL script file that contains the commands necessary to recreate the database from scratch, create TABLE insert, and so on.

Statements that are backed up using mysqldump:

mysqldump -u root 数据库名>备份文件名;   #备份整个数据库mysqldump -u root 数据库名 表名字>备份文件名;  #备份整个表

We try to back up the entire database Mysql_shiyan, name the backup file bak.sql, Ctrl + Z to quit MySQL, and then use the statement:

mysqldump -u root mysql_shiyan > bak.sql;

Using the command "LS" can be seen to have generated backup files Bak.sql:

You can use Gedit to view the contents of the backup file, and you can see that there are more than just the data stored in it, as well as additional information about the backed up database.

6. Recovery

Restore the database with the backup file, in fact we have already used. At the beginning of this experiment, we used a command like this:

source /tmp/SQL6/MySQL-06.sql

This is a recovery statement that restores the Mysql_shiyan database saved in the Mysql-06.sql file.

There is another way to recover the database, but before we do this, we'll create a new empty database testusing the command:

mysql -u root          #因为在上一步已经退出了MySQL，现在需要重新登录CREATE DATABASE test;  #新建一个名为test的数据库

Again Ctrl + Z exit MySQL, then enter the statement to restore, the bak.sql just backed up to the test database:

test < bak.sql

We enter a command to view the table of the Tset database to verify the success of the recovery:

mysql -u root          #因为在上一步已经退出了MySQL，现在需要重新登录use test               #连接数据库testSHOW TABLES;           #查看test数据库的表

You can see 4 tables and a view of the original database, which are now restored to the test database:

Then check the employee table for recovery status:

(Big Data Engineer Learning path) Fourth Step SQL Foundation Course----Others (end of basic practice)