MySQL 8.0.15 installation graphic tutorial and database basics

MySQL software installation and database basics are for your reference. The specific contents are as follows

1. Mission

Task 1

MySQL software installation and database basics

Task time

Please complete it before 22:00 on February 26 and check in on the comment section of this article. Those who fail to clock in after the deadline will be cleared out.

What you will learn

1. Software installation and server setup

Tutorial MySQL Installation | Novice Tutorial

2. (Optional, but highly recommended) Use the graphical interface software Navicat for SQL

Planet provides Navicat for SQL
Easy steps:
Unzip the file and copy the key
Open navicat.exe in the folder
The username is arbitrary, enter the key, and then connect to the database
Enter the password and change the connection name to your favorite
The rest is up to you to explore, how to create databases, tables, etc. in navicat

3. Database Basics

Database definition
Relational Database
Two-dimensional table
OK
List
Primary Key
Foreign Keys

4.MySQL database management system

database
Data Sheet
view
Stored Procedures

2. Software Installation and Server Setup

System environment: win10 home edition

2.1 MySQL Installation

2.1.1 Download the Windows version of MySQL installation package from the MySQL official website

Address: MySQL Download

After clicking Download, the following interface will pop up. Click No thanks, just start my download

2.1.2 After downloading, unzip the zip package to the directory you want to install. My path is as follows:

2.1.3 Configure environment variables:

Go to Computer - Properties - Advanced System Settings - Environment Variables and add the path where MySQL is unzipped.

2.1.4 Next, configure the MySQL configuration file

Open the folder you just unzipped D:\Software\Mysql\mysql-8.0.15-winx64\mysql-8.0.15-winx64, create a configuration file called my.ini, edit my.ini and configure the following basic information:

[mysql]
default-character-set=utf8

[mysqld]
port = 3306
basedir=D:\Software\Mysql\mysql-8.0.15-winx64\mysql-8.0.15-winx64
datadir=D:\Software\Mysql\mysql-8.0.15-winx64\mysql-8.0.15-winx64\data
character-set-server=utf8
default-storage-engine=INNODB 

1. Next, let's start the MySQL database:
Open the cmd command line tool as an administrator and switch directories:

D:
cd D:\Software\Mysql\mysql-8.0.15-winx64\mysql-8.0.15-winx64\bin 

Enter the initialization command:

mysqld --initialize-insecure --user=mysql 

Generate the data directory in the D:\Software\Mysql\mysql-8.0.15-winx64\mysql-8.0.15-winx64\bin directory

To start, enter the following command:

net start mysql 

This proves that the connection has been successful.

2.2 MySQL password reset

2.2.1 Log in to MySQL

enter:

mysql -u root -p

Because no password was set before, the password is empty, just press Enter:

2.2.2 Query User Password

Query user password command:

mysql> select host,user,authentication_string from mysql.user;

Host: The IP address that allows the user to log in;
user: the user name of the current database;
authentication_string： user password;
If there is no password, the root line should be empty.

2.2.3 Set (or modify) the root user password:

Note: The password field and password() function are deprecated in MySQL 5.7.9 and later.

Do not set passwords in the following ways:

use mysql; 
update user set authentication_string="newpassword" where user="root";

This will set the newpassword value under the authentication_string field of the root user in the user table;

The correct steps to change the root password are:

1. If there is content under the authentication_string field of the current root user, set it to empty first. If not, skip to step 2.

use mysql; 
update user set authentication_string='' where user='root'

2. Use ALTER to modify the root user password as follows:

use mysql;
ALTER user 'root'@'localhost' IDENTIFIED BY 'new password';
FLUSH PRIVILEGES;

So far, the local MySQL you created has been created! ! !
Just reconnect! ! !

3. Use the graphical interface software Navicat for SQL

Navicat for SQL installation package has been saved in Baidu Netdisk: navicat+for+mysql10.0.11 Simplified Chinese

1. Unzip the file and copy the key
2. Open navicat.exe in the folder
3. The user name is arbitrary, enter the key, and then connect to the database
4. Enter the password (if you have reset the password for MySQL before, just fill in the new password here), and change the connection name to your favorite

Problem: The connection fails here, as shown in the figure

Solution:

The problem here is that the encryption method ALTER user 'root'@'localhost' IDENTIFIED BY 'new password'; used by MySQL when setting the password before is strong encryption, which will result in connection failure. If the second encryption method ALTER user 'root'@'localhost' IDENTIFIED WITH mysql_native_password by 'new password'; is used, the connection can be made directly.

Successfully connected! ! !

At this point, the installation of the required software and the environment configuration are all completed, and you can happily explore the world of MySQL~~

4. Database Basics

4.1 Database definition

According to the book "SQL Must Know (4th Edition)", from the perspective of SQL, a database is a collection of data stored in an organized way, and is a container (usually a file or a group of files) that stores organized data.

Note: People often use the term database to refer to the database software they use. This is incorrect and causes a lot of confusion. To be more precise, database software should be called a database management system (DBMS). A database is a container created and manipulated by a DBMS, but what it is and what form it takes vary from database to database.

4.2 Relational Database

The most typical data structure of a relational database is a table, which is a data organization consisting of two-dimensional tables and the connections between them.

advantage:

1. Easy to maintain: all use table structure with consistent format;
2. Easy to use: SQL language is universal and can be used for complex queries;
3. Complex operations: Supports SQL and can be used for very complex queries within a table or between multiple tables.

shortcoming:

4. The reading and writing performance is relatively poor, especially the efficient reading and writing of massive data;
5. Fixed table structure, slightly less flexible;
6. With high concurrent read and write requirements, for traditional relational databases, hard disk I/O is a big bottleneck.

4.3 Two-dimensional table

A table is a structured file that can be used to store a specific type of data. A table can hold a list of customers, a catalog of products, or some other list of information. A table is a structured list of data of a certain type.

The data stored in a table is a list of the same type of data. A list of customers should never be stored in the same database table as a list of orders; otherwise, later retrieval and access will be difficult. Two tables should be created, one for each inventory.

Each table in a database has a name to identify itself. This name is unique , i.e. there is no other table in the database with the same name. Although you cannot use the same table name twice in the same database , it is perfectly possible to use the same table name in different databases .

A schema can be used to describe a specific table in a database, or it can be used to describe the entire database (and the relationships between the tables in it). A schema is information about the layout and characteristics of a database and its tables.

4.4 Row

The data in the table is stored in rows , and each saved record is stored in its own row.

You may hear users refer to rows as database records. The two terms are often used interchangeably, but technically , row is the correct term.

4.5 Columns

Tables consist of columns . A column stores a portion of information in a table. A column is a field in a table. All tables are composed of one or more columns.

Each column in the database has a corresponding data type . The data type defines what kind of data a column can store. The data type restricts the kind of data that can be stored in a column (for example, preventing character values ​​from being entered into a numeric field).

Data types and their names are a major source of SQL incompatibilities .

4.6 Primary Key

Each row in a table should have one (or more) columns that uniquely identify it. The customer table could use the customer ID, and the order table could use the order ID. The employee table can use either the employee ID or the employee social security number.

A primary key is a column (or set of columns) whose value uniquely identifies each row in a table. Without a primary key, updating or deleting specific rows in a table is extremely difficult because you cannot guarantee that the operation involves only related rows.

Tip: You should always define a primary key Although a primary key is not always required, most database designers will ensure that each table they create has a primary key to facilitate future data operations and management.

Any column in a table can be used as a primary key as long as it meets the following conditions:

• No two rows have the same primary key value;
• Each row must have a primary key value (NULL values ​​are not allowed in primary key columns);
• The values ​​in the primary key column cannot be modified or updated;
• Primary key values ​​cannot be reused (if a row is deleted from a table, its primary key cannot be assigned to a new row later).
  

The primary key is usually defined on one column of the table, but it is not required to do so, and multiple columns can be used as primary keys together. When using multiple columns as primary keys, the above conditions must be applied to all columns, and the combination of all column values ​​must be unique (but the value of a single column does not need to be unique).

4.7 Foreign Keys

A foreign key is a column in a table whose values ​​must be listed in the primary key of another table. Foreign keys are an extremely important part of ensuring referential integrity.

Foreign keys help prevent accidental deletions. After a foreign key is defined, the DBMS does not allow deletion of a row that has a related row in another table. For example, you cannot delete a customer who has an associated order. The only way to delete the customer is to first delete the related order (which means also deleting the related order items). Since a series of deletions is required, foreign keys can prevent accidental deletion of data.

5. MySQL database management system

5.1 Database (DB)

A database is a warehouse for storing data. This warehouse is organized and stored according to a certain data structure (data structure refers to the organizational form of data or the connection between data). We can manage the data in the database through the various methods provided by the database. To put it in a simpler way, a database is the same as a warehouse that stores miscellaneous items in our lives. The only difference is the things stored there.

5.2 Data Table

A data table is a very important object in a relational database. It is the basis of other objects and a collection of two-dimensional arrays used to store and operate the logical structure of data.

According to the classification of information, a database may contain several data tables. Each table is composed of rows and columns . When one piece of data is recorded, a row is added to the data table. Each column is composed of a field name and a set of field data . The column is called a field. Each column also has its own multiple attributes , such as whether it is allowed to be empty, default value, length, type, storage code, comments, etc.

5.3 A database system has three main components

1. Database System: A place for storing data.
2. Database Management System (DBMS): software that users use to manage databases.
3. Database Application: Software supplement for managing database used to improve the processing power of database system.

5.4 Views

A view is a virtual table that has the same functionality as a physical table. Views can be added, modified, checked, and operated. Views are usually subsets of rows or columns of one or more tables. Modifications to the view do not affect the base table. It makes it easier for us to retrieve data compared to multi-table queries.

Views are generally used in the following two scenarios :

We do not want visitors to obtain the information of the entire table, but only expose some fields to visitors, so we create a virtual table, which is a view.
The queried data comes from different tables, and the queryer hopes to query in a unified way. In this case, a view can be created to combine the query results of multiple tables. The queryer only needs to obtain data directly from the view without having to consider the differences caused by the data coming from different tables.

Note: This view is created in the database, not in code.

5.5 Stored Procedure

MySQL version 5.0 began to support stored procedures.

A stored procedure is one or more SQL statements that are saved for later use. Think of them as batch files, although their use is not limited to batch processing. A stored procedure is a database object that stores complex programs in the database so that they can be called by external programs. The idea of ​​stored procedures is very simple, which is to encapsulate and reuse codes at the database SQL language level.

advantage:

• Stored procedures can encapsulate and hide complex business logic.
• Stored procedures can return values ​​and can accept parameters.
• A stored procedure cannot be run using the SELECT command because it is a subprogram, unlike a table, data table, or user-defined function.
• Stored procedures can be used for data validation, enforcing business logic, etc.
  

shortcoming:

• Stored procedures are often customized to specific databases because they support different programming languages. When switching to a database system from another manufacturer, the original stored procedure needs to be rewritten.
• The performance tuning and writing of stored procedures are limited by various database systems.

Wonderful topic sharing:

MySQL different versions installation tutorial

MySQL 5.7 installation tutorials for various versions

MySQL 5.6 installation tutorials for various versions

mysql8.0 installation tutorials for various versions

The above is the full content of this article. I hope it will be helpful for everyone’s study. I also hope that everyone will support 123WORDPRESS.COM.