Introduction to MySQL (1)-Database Basics

table of Contents


You can understand it first, and it will be clearer to come back after you have learned a certain level

1. Introduction to the database

1. Database management system

"Database" software should be called to be exact: database management system.
Database Management System (DBMS) refers to a large-scale software that operates and manages databases.
Role: used for the establishment, use and maintenance of the database.

2. Database

Database: is a container for storing data, which is essentially a file system that stores data in a specific format.
Users can add, modify, delete and query data in the database.

Insert picture description here

3. Table

Table (table): is a structured file that can be used to store a certain type of data.
It looks similar to using an Excel spreadsheet.

Tables have some characteristics that define how data is stored in the table.
For example: what kind of data can be stored, how to split the data, and so on.

Table name: Each table in the database has a name to identify itself.
In the same database, the table name is unique.
However, the same table name can be used in different databases.

Schema (schema): The information describing the table is the so-called schema, and the schema can be used to describe the information about the layout and characteristics of the database and the table.

4. Columns and data types

A field in the column table. All tables are composed of one or more columns.
Each column in the database has a corresponding data type.
The data type defines the kind of data that the column can store.
The data type (datatype) is the type of data allowed.
Each table column has a corresponding data type, which limits the data stored in the column.

5. OK

A record in the row table.
The data in the table is stored in rows, and each record saved is stored in its own row.
Row is also called record. To a large extent, these two terms are interchangeable, but technically speaking, row is the correct term.

6, the primary key

To distinguish whether two people are the same person, you can use the ID number; to distinguish two cars, you can use the car number; to
distinguish two rows of data, you can use the primary key.
Each row in the table should have a column (or set of columns) that can uniquely identify itself.
The primary key is used to distinguish each row.
This column (or group of columns) that uniquely identifies each row in the table is called the primary key.
The primary key is used to represent a specific row.
Primary key: A column (or multiple columns) whose value can uniquely distinguish each row in the table.

7, SQL

SQL is the abbreviation of Structured Query Language.
SQL is a language specifically used to communicate with databases.

Features of
SQL : -SQL is not a proprietary language of a particular database vendor. Almost all important DBMS support SQL, so learning this language allows you to deal with almost all databases.
——SQL is simple and easy to learn. Its sentences are all composed of highly descriptive English words, and the number of these words is small.
——Although SQL looks very simple, it is actually a powerful language, flexible use of its language elements, very complex and advanced database operations can be carried out.
——SQL is not a patent language, and there is a standards committee. They try to define SQL grammar that can be used by all DBMSs, but in fact, different database manufacturers support SQL statements, but they all have unique content.

SQL classification:
-Data Definition Language: referred to as DDL (Data Definition Language), used to define database objects: databases, tables, columns, etc. Keywords: create, alter, drop, etc.
-Data Control Language: referred to as DCL (Data Control Language), used to define database access rights and security levels, and create users.
——Data Manipulation Language: referred to as DML (Data Manipulation Language), used to update the records of the tables in the database. Keywords: insert, delete, update, etc.
-data query language: DQL (Data Query Language) for short, used to query the records of the table in the database. Keywords: select, from, where etc.

2. Introduction to MySQL

1. Common database software

MySQL: Open source and free database, the most widely used database in domestic enterprises, has been acquired by Oracle.
Oracle: A large-scale database for a fee, a product of Oracle.
DB2: IBM's database product, chargeable. Often used in banking systems.
SQLServer: A medium-sized database charged by MicroSoft. Languages ​​such as C# and .net are often used.
SyBase: Has faded out of the stage of history. Provides a very professional data modeling toolPowerDesigner(You can learn more about this interest).
Commonly used database software: MySQL

2. Database MySQL installation

Databases such as MySQL, Oracle, and Microsoft SQL Server are based on client-server databases.

The server is a piece of software responsible for all data access and processing.
All requests for data, data addition, deletion, and data update are completed by the server software.

The client is the software that deals with users. Used to send commands to the server.
The client can be tools provided by MySQL, scripting languages ​​(such as Perl), Web application development languages ​​(such as ASP, ColdFusion, JSP, and PHP), programming languages ​​(such as C, C++, Java), etc.

The installation steps are as follows: (Version 18.0.25)

sudo apt update     #更新系统软件

sudo apt install mysql-server  #安装

sudo mysql     #进入数据设置root账户的密码和权限

use mysql;     #切换mysql数据库

update user set plugin='mysql_native_password' where user='root';        #修改root用户密码

alter user 'root'@'localhost' identified with mysql_native_password by '这里放你自己的密码'; 

#这里不行看一下下面的参考资料

FLUSH PRIVILEGES;    #刷新权限

exit;    #退出数据库

sudo vim /etc/mysql/mysql.conf.d/mysqld.cnf   #修改配置文件:bind-address = 127.0.0.1

sudo service mysql restart   #重启数据库

mysql -uroot -p                       #方式一,正常登陆
mysql -hIP -P端口 -uroot -p    #方法二,可用于远程登录

查看mysql状态 :sudo service mysql status 
启动mysql服务: sudo service mysql start 
停止mysql服务: sudo service mysql stop 
重启mysql服务: sudo service msyql restart

linux database version 8.0.25 installation reference material
installation successfully started as shown below

Insert picture description here

3. Visualization tools

(1) Remote command connection

mysql -h ip地址 -P 端口 -u root -p 密码

The ip and port can be omitted, and the default connection is 127.0.0.1 and 3306 ports

(2) Visualization tools
Two commonly used database connection tools: Navicat and DataGrip.

Navicat is a classic tool to connect to MySQL, and many programmers are still using it until now.

Insert picture description here


DataGrip is a product of JetBrains, which has many well-known development tools, such as IntelliJ IDEA, PyCharm, CLine, etc. Due to its user-friendly prompts, completions and other functions, more and more programmers are beginning to use it.