Database Guide for Newcomers

In the data analyst career, database and SQL will be one of their most cost-effective skills.

Database is a logical concept, it is a bunch of interrelated data, placed on the physical entity, is a bunch of files written on disk, the file has data. The most basic data forms a table, and we think of it as an Excel sheet.

The abstract concept of a data table is as follows.

An ID is an important concept in a database, called a unique identifier/primary key, to represent the uniqueness of the data. We interpret it as the ID number of the data. I know the ID card and I know where the data is.

ID does not have a realistic business meaning, it is a string of simple numbers, each table can have only one primary key.

A database is a collection of tables, a database can put more than one table, we give each table name, table and table can be linked to each other.

Contact is that the data can match, the formal name is called a join, the corresponding operation is called join, we think of excel in the VLOOKUP.

The above is two tables, users and educational background, it seems to be able to use names to match, but here there are two Zhang three, one is Peking University, one is Shanghai College, which Zhang San can and user table Zhang San corresponding? Can not.

Perhaps Tsinghua University also has Zhang San, Fudan University, Jiaotong University also have, after all, the whole Chinese name is so many people. The correct usage should be to use the ID join instead of the name. ID is the product, operation and data personnel in the work of one of the most contact content, user ID article ID Product ID, etc., if you do not understand, here need to master.

Is the correct usage, the user table and the education table can be connected through the ID, there may be questions, the user ID is 1 and 6, or can not correspond? This does not matter, because in the database, not all tables can correspond, there is some possibility of matching, perhaps that is called Qin Lu, he did not read the book, the educational background is naturally not recorded in the table.

Of course, the database design is more rigorous, generally each table will have a primary key (the primary key is not necessary, but from the efficiency optimization will definitely add), this primary key can not be the primary key of other tables, to prevent conflict.

This is true, the user ID is the primary key of the user table, education background ID is the primary key of the education background table, we add a field in the Education background table called the user ID, it is specifically used to join the user table, it is not a primary key.

Isn't it a little bit around? In fact, not all names with IDs are called primary keys, and this is something to figure out.

In the database, the name and field of the table should be in English. If it is a primary key, just call the ID and you want to associate the other table with the table name _id"as the join.

The logic of the association is as follows, this is called the full join, the match is not on the null representation, the match on the I in Orange. Join is the core concept of relational database, is the basis of operation, now confused does not matter, practice a few times just fine.

If now I have a new table, need to associate education, smart You also must think of methods.

To understand the concept above, you know what a relational database is, simply put, it is a database of multiple, two-dimensional rows and columns that can be connected to one another.

The relational database is developed based on the relational algebra model. Commonly used databases are SQL Server, MySQL, Oracle, etc., we follow the study of MySQL-based.

The next step is to install MySQL.

We go to MySQL's official website to download, version 5.6 or more.

After the tutorial is completed, we know through the command line whether the success, win and Mac operation is not the same.

We certainly do not operate MySQL on such an ugly interface, we also need to install the GUI, which is to provide us with the interface.

Mac user recommended Sequelpro,win user recommended Workbench. Subsequent I will operate in Sequelpro. MySQL's default user name and password are root, and it is recommended to reset it.

If you have questions, you can go online search related tutorials, do not install MySQL is also possible. Because most of the data analysts are using online data query platform operation, installation is just for the convenience of demonstration. Do not want to install the classmate, you can practice in Sqlzoo, I will explain later.

Enter the account password after entering the operation interface, the database I named Qin. The left column is the table list in the database and is now empty. The right side is table-related operations and information.

Because there is no data now, we load the practice data that we have used. The data analyst does not need to learn how to create a delete add-on table operation, because I can only explain the query, we are interested to understand.

Any MySQL GUI tool can load CSV, usually called import.

Encoding Select AutoDetect, rest by default.

Next you need to set the data type of each field, the system will help us to automatically select, here do not change. int represents an integer value, varchar represents a string (such as Chinese English punctuation), the number in parentheses is allowed to store bytes, this block is not in-depth understanding, as long as you know the commonly used field types.

Give the table a name, loaded, the CSV successfully imported into the database, the ability to perform data query operations.

This table does not have a primary key set, mainly practice, so skip this step.

Everyone may encounter the import data is garbled, this is the difference between the system encoding, is the conflict between UTF8 and GBK, online has relevant answers, search keywords can.

Here, you have successfully created a database on your computer. We've done Excel operations and BI Analytics for practice data in the previous article, and then we'll learn about SQL. As you may have guessed, there will be statistics and Python programming in the future.

Qin Lu

Database Guide for Newcomers