MySQL series tutorials for beginners

The MySQL-related knowledge introduced here is not for students who want to study DBA, but for those who want to change careers and engage in data analysis.

The following knowledge points are some of the knowledge points that I think it is necessary to master when learning MySQL database.

1. Basic concepts and basic commands

1) Basic concepts

① Database

A warehouse that stores and manages data according to a certain data structure.

② Classification of databases

The common classification of databases is to divide them into relational databases and non-relational databases.

③ What is a relational database?

Represent complex relationships in a simple binary form, that is, in the form of rows and columns (tabular form), where each row is called a record and each column is called a field. Among them, MySQL is a very commonly used relational database.
Binary form: Similar to Excel spreadsheet data, it is a binary form.

④ Database Management System: DBMS

A database management system is software used to manage and manipulate databases. Among them, relational database management systems (RDBMS) include Oracle, MySQL, SQL Server, DB2, IBM, etc. Non-relational database management systems (Nosql) include redis, Hbase, mongodb, neo4j, etc.

⑤ Introduction to MySQL

MySQL is a relational database management system. It was originally designed by MySQL AB and was later acquired by Oracle Database. The MySQL database uses the "sub-library and sub-table" method to manage data. A MySQL database management system can manage multiple databases; a database can store multiple tables. At the same time, MySQL is open source and free, supports tens of millions of data queries, and supports GPL (open source license).

⑥ What is SQL language?

SQL language, whose full name is "structured query language", is a standardized language for operating databases and can realize access and operations on databases.

⑦ Pay attention to the difference between MySQL and SQL!

There is a misconception here: Do you know MySQL? In fact, this statement is problematic. MySQL refers to a database management system. Asking you if you know MySQL is actually asking whether you can add, delete, modify and query the database. Therefore, MySQL here refers to the "SQL language" mentioned earlier, but people are accustomed to using MySQL instead of SQL.

⑧ Classification of SQL language

⑨ What is a “table”?

A table is a structured list of data of a certain type.
The "certain specific type" mentioned here refers to the data stored in the table, which is a type of data. You can't lump a list of customer information and a list of orders together and store them in the same database table. This makes it difficult to retrieve and access the data later, so we should have one table for each list.
Each database should have a table name to uniquely identify itself. There cannot be two tables with the same name in the same database, but the same table name can be used in different databases.

⑩ What is a “column”?

A table consists of columns, each of which stores a certain part of the information in the table. Each column is a field in the table. A table is composed of one or more columns.
It is important to note that each column in the table should store a specific piece of information. For example, state, city, and zip code should all be separate columns. We cannot combine these three different information in one column, otherwise it will add great trouble to the query.

⑪What is “data type”?

Each column in a table should have a corresponding data type, which indicates what data can be stored in the column.
Commonly used data types include string type, numeric type, and date type. A detailed introduction to data types will be given in the following series of articles.
Data types play an important role in helping to sort data correctly and play an important role in optimizing disk usage.

⑫ What is “行”?

The data in the table is stored in rows, and each row represents a record. If you imagine a table as a grid, then the vertical columns in the grid are table columns, and the horizontal rows are table rows.

2) Basic commands

① Connect to the database

MySQL database is a "client-server" type database management system. Figuratively speaking, a MySQL database management system consists of at least two parts. One is the server side, which we cannot see, but from which we always get data every time we use the database. The second is the client. The clients I often use include CMD window and Navicat. These clients allow us to enter SQL statements to access and obtain data. Therefore, before executing the command, we need to log in to the database management system.

"To log in to the MySQL database, the following information is required"
Host Name Port User Name User Password (Password)
mysql -h localhost -u root -P 3306 -p 123

-h Host name: localhost means the server is local -u User name: User name root
-P port: default port 3306
-p password: password123

In the CMD black window, if you log in to the MySQL database, the interface is as follows:

② Select database

When you first connect to a database, there is no database open for you to use. Before you can perform operations on any database, you need to select a database.

"Basic syntax": use database name;
use huangwei;
"""
Note: In the CMD black window, each code must be followed by a semicolon “;”. However, when using SQL statements in many software, there is no need to use a semicolon ";", and sometimes errors may occur if a semicolon ";" is used.
"""

③ Understand databases and tables

show databases : returns a list of available databases;

show tables : returns a list of database tables;

show columns from 表名: returns the detailed information of a table, which is equivalent to "desc+table name";

2. Writing order and execution order of SQL statements

If you want to learn SQL statements well, you must first understand the writing order and execution order of SQL statements. The inconsistency between the writing order and the execution order of SQL statements is an important reason for SQL writing errors or failure to write SQL. In view of this, in the process of learning SQL statements, you must understand the two concepts of "writing order" and "execution order".

1) The writing order of a complete SQL query statement

-- "mysql statement writing order"
1 select distinct *
2 from table (or result set)
3 where …
4 group by …having…
5 order by …
6 limit start,length
-- Note: 1 and 2 are the most basic statements and must be included.
-- Note: 1 and 2 can be matched with any one of 3, 4, 5, and 6, or with more than one of 3, 4, 5, and 6 at the same time.

2) A complete SQL statement execution order

The explanation of the above figure is as follows:

3) Explanation on the execution order of select and having

Note: If anyone has an opinion that convinces me, please leave a message to let me know, thank you.

The above is the detailed content of the MySQL series of tutorials on database basics for beginners. For more information about MySQL database basics, please pay attention to other related articles on 123WORDPRESS.COM!