preface

For MySQL8.0.28 installation tutorial, I have spent a day in the official MySQL documentation. Here is dedicated to beginners who want to start MySQL8.0. Take the latest version of MySQL8.0.28 as an example for the installation and preliminary use of the detailed tutorial for beginners. Whether it’s on Windows or Linux, we can. This is perhaps the most detailed installation and use of MySQL8.0.28 on the web to date.

Review the old and learn the new, can be a teacher. Is it a way to learn that we lack? The answer is not, but lack of learning methods. Official documents are very detailed and authoritative, and other books and blog posts are based on official documents and their own experience. The purpose of writing this article is to hope that you can summarize your own learning methods and be good at using official documents to improve yourself.

Beginners are advised to practice in the command line window. Practice makes perfect. Reach a certain level of proficiency, and then use the client tools to improve our work efficiency. What is the end goal? Stay alive. Improve your earning power. Just kidding, of course. Back to business. Keep reading.

From download to install to forget your password solution. Learn the basics step by step using the command line window, and then use the client remote connection tool. Finally, with the popular Java language, we demonstrate how to use JDBC to connect to the latest MySQL8.0 database, and execute the query to return results.

The body of the

Za also not much beep beep, directly on dry goods, is practical and cost-effective!

MySQL8.0.28下载

You can download the MSI file for one-click installation or decompress the ZIP file for command line initial installation.

MySQL official website download address

Dev.mysql.com/downloads/m…

1. Download the Windows version

On Windows, you can choose to download the MSI file or decompress the ZIP file. For general use, select the one I selected with the purple box. For the Debug Test Suite below, there are a number of Test suites available for download for those who need to Test.

2. Download Linux version

Select MySQL based on your desired Linux distribution version. For example, I personally choose Redhat7 series that I am familiar with to download. There is also a bundle version, which contains plug-ins and dependencies for easy installation using RPM packages. Installing a single server requires the installation of other dependency packages. For starters, it is recommended to download the RPM Bundle version directly. If I don’t, I’m gonna do it. That’s fine, please read on, also provides detailed installation steps.

3. Precautions

Ordinary people can not look carefully, the official will prompt to log in, add button font is very conspicuous, while the following I need to download immediately, the font is small. So pay attention, select No Thanks below, I need to download it now. The community version is available as a free download.

Install MySQL8.0

For beginners, it is recommended to install it under Windows.

Typically, a MySQL version service instance is installed by default. You can also test on a budget by installing multiple instances on the same server. The default port is 3306. If you are in a release (production) environment, you are advised to change the default port to prevent others from guessing. Next, install and test multiple MySQL service versions in one operating system. Only install multiple MySQL services in Windows (without using virtual machine tools, test in real machine environment). Linux has convenient YUM source and APT installation. One-click installation requires dependence, but there are also tedious RPM package installation.

1. Install in Windows

To configure environment variables, choose Control Panel > All Control Panel items > System > Advanced System Configuration > System Environment Variables.

# variable name
MySQL_HOME
# a variable's valueD: \ work \ mysql - 8.0.28 - winx64 \ binCopy the code

Msi file installation will not be introduced, fool type one key installation, pay attention to the choice of path.

This section describes the installation and instantiation of the decompressed version ZIP:

Mysql - 8.0.28 - winx64. ZipCopy the code

1.1. Decompress the installation package mysql-8.0.28-winx64.zip

Add my.ini file to D:\work\mysql-8.0.28-winx64. Then add the following configuration:

[client]
Mysql client default character set
default-character-set=utf8
 
[mysqld]
Set port 3306 to 3307 for multiple services
port=3307
Set mysql installation directoryBasedir = D: \ \ work \ \ mysql - 8.0.28 - winx64Mysql > create mysql database directoryDatadir = D: \ \ work \ \ mysql - 8.0.28 - winx64 \ \ data# Maximum number of connections allowed
max_connections=20
The default character set used by the server is the 8-bit latin1 character set
character-set-server=utf8
The default storage engine to use when creating new tables
default-storage-engine=INNODB
Copy the code

1.2. Instantiation

Run the CMD command as the administrator to switch to the decompressed mysql directory:

-- Go to d: to switch to drive D: -- Go to step 2cdCommand to switch to the bin directory of personal mysql installationcdD: \ work \ mysql - 8.0.28 - winx64 \Copy the code

Set it to an empty password to remove unnecessary hassle.

mysqld --initialize-insecure
Copy the code

1.3. Installation Services

Go to the bin directory of the decompressed MySQL server and run the following commands to install the MySQL server:

cd D:\work\mysql-8.0.28-winx64\bin
mysqld install
mysqld install --service -mysql8
Copy the code

If multiple services are not installed, use mysqld install. The default service name is MySQL.

1.4 to use the service command, you need to run the CMD command as the administrator. Notice that my path is executed in the bin directory

I didn’t configure system environment variables for MySQL8, so I run commands in the MySQL bin directory.

Net start mysql

# start serviceNet start mysql D:\work\mysql-8.0.28-winx64\bin>net start mysql8 mysql D:\work\mysql-8.0.28-winx64\bin>net start mysql8 mysql D:\work\mysql-8.0.28-winx64\bin>net start mysql8 mysqlCopy the code

Net stop mysql

# stop serviceNet stop mysql D:\work\mysql-8.0.28-winx64\bin>net stop mysql8 mysql8 service is stopping.. The MySQL8 service has stopped successfully.Copy the code

Delete service sc delete mysql

# delete service
sc delete mysql
Mysql > remove mysqld mysql > remove mysqld
mysqld remove mysql
Copy the code

1.5. Precautions

MySQL8 = MySQL8Computer \ HKEY_LOCAL_MACHINE \ SYSTEM \ ControlSet001 \ Services \ MySQL8# 或者是MySQLComputer \ HKEY_LOCAL_MACHINE \ SYSTEM \ ControlSet001 \ Services \ MySQLCopy the code

The installation service specifies the service name MySQL8. As mentioned below, delete the original residual registry, restart the computer, and install it again.

Msvcr120.dll is missing. Install the following components to solve the problem

vcredist_x64.exe

vcredist_x86.exe

Download Visual C++ Redistributable Packages for Visual Studio 2013 from Official Microsoft Download Center

Note: with mysqlD-initialize, the password is randomly generated and can be found in the mysql error log

For example, my logs (files that end with.err in mysql data)

A temporary password is generated for root@localhost: 6hk20yueza=M
Copy the code

Command to change the password

ALTER USER 'root'@'localhost' IDENTIFIED BY 'New password'
Copy the code

The reason is introduced in Windows so detailed, because our usual work environment is more carried out under Windows. Even if you use a Linux environment, you usually use virtual machines with Linux distributions, and then cloud servers. If you are familiar with some of the MySQL commands, you can install them in Linux. Just look at the official documents.

2. Install in Linux

It is recommended that beginners do not install RPM packages like I did. You can download RPM bundles directly, or use the official mysql yum source. I have many years of Linux use experience, as well as some practical work experience; Know how to determine which packages are necessary and which need to be replaced.

Pay attention to the permissions of the Linux operating system. Ensure that the permissions are within the minimum range.

2.1. Prepare the installation package

You can download it directly from the official website or use the wget command to download it. You can also use the yum source of the official website to install it. Or use apt to get the installation. As to why some Linux distributions removed MySQL from the default, it was because of the closed source risk of MySQL being acquired by Oracle. She was replaced by her sister MariaDB, which is why I mentioned MariaDB when I installed it.

2.2. Install the RPM package

The system will prompt you which dependency packages are required, so I prepare the required dependency packages in advance.

[mysql@localhost ~]$RPM -ivh mysql-community-server-8.0.28-1.el7.x86_64. RPM X86_64. RPM: header V4 RSA/SHA256 Signature, key ID 3a79bd29: NOKEY error: dependency detection failed: Mysql-community-client (x86-64) >= 8.0.11 required by mysql-community-server-8.0.28-1.el7.x86_64 mysql-community-common(x86-64) El7 is used by mysql-community-server-8.0.28-1.el7.x86_64 requires mysql-community-ICU -data-files = 8.0.28-1.el7 is used by mysql-community-server-8.0.28-1.el7.x86_64 requires mysql-community-ICU -data-files = 8.0.28-1.el7 is used by mysql-community-server-8.0.28-1.el7.x86_64 requires mysql-community-ICU -data-files = 8.0.28-1.el7 Mysql-community-server-8.0.28-1.el7.x86_64 need [root@localhost mysql]# RPM - the ivh mysql - community - icu - data - files - 8.0.28-1. El7. X86_64. RPMWarning: mysql-community-ICU-data-files-8.0.28-1.el7.x86_64. RPM: header V4 RSA/SHA256 Signature, key ID 3a79bd29: NOKEY Preparing...# # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # [100%]Upgrading/installing... 1:mysql-community-icu-data-files-8.# # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # [100%]
[root@localhost mysql]# RPM - the ivh mysql - community - common - 8.0.28-1. El7. X86_64. RPMWarning: mysql-community-common-8.0.28-1.el7.x86_64. RPM: header V4 RSA/SHA256 Signature, key ID 3a79bd29: NOKEY Preparing...# # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # [100%]Upgrading/installing... 1: mysql - community - common - 8.0.28-1. E# # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # [100%]

[root@localhost mysql]# RPM - the ivh mysql - community - the client - 8.0.28-1. El7. X86_64. RPMRPM: header V4 RSA/SHA256 Signature, key ID 3a79bd29: NOKEY error: dependency detection failed: Mysql-community-client-plugins = 8.0.28-1.el7 was required by mysql-community-client-8.0.28-1.el7.x86_64 Mysql-community-libs (x86-64) >= 8.0.11 is required by mysql-community-client-8.0.28-1.el7.x86_64Copy the code

2.2.1, installation depend on the package, and then use the RPM – qa | grep mysql query which was installed. How to upload to the server, a simple point SCP command can be.

X86_64 RPM -ivh mysql-community-common-8.0.28-1.el7.x86_64 RPM -ivh mysql-community-libs-8.0.28-1.el7.x86_64 RPM -ivh Ql-community-icu -data-files-8.0.28-1.el7.x86_64 RPM -ivh ql-community-client-8.0.28-1.el7.x86_64 RPM -ivh Mysql - community - the client - plugins - 8.0.28-1. El7. X86_64 / root @ localhost mysql# rpm -qa | grep mysql
Copy the code

2.2.2 Redhat7 needs to uninstall mariadB-libs and replace it with mysql-community-libs

$ yum remove mariadb-libs
Copy the code

2.2.3 Formally installing the Server

$RPM - the ivh mysql - community - server - 8.0.28-1. El7. X86_64. RPMCopy the code

Check the installed version Ver 8.0.28 for Linux on x86_64

[mysql@localhost ~]$mysqladmin --version mysqladmin Ver 8.0.28for Linux on x86_64 (MySQL Community Server - GPL)
Copy the code

2.2.4 Assign the owner of the mysql installation directory to the mysql user. The default RPM package installation path is /var/lib/mysql. After granting mysql user owner and group permissions, you can log in as mysql user or start and stop services.

Add mysql group
$ groupadd mysql
Add mysql user to mysql group
$ useradd -g mysql mysql
Change the password of the mysql user
$ mysql passwd
# change owner
$ chown -R mysql:mysql /var/lib/mysql
Copy the code

Tips: You can also add mysql users to the /etc/sudoers configuration file to limit the permissions of mysql users.

2.3. Initialization

Set the password to empty so that you can change the password upon subsequent login

$ mysqld --initialize-insecure
Copy the code

2.4. Start the service and view the service status

The Redhat7 series uses commands to start the MySQL service

$ systemctl start mysqld
Copy the code

Set boot to start automatically

$ systemctl enable mysqld
Copy the code

Close the service

$ systemctl stop mysqld
Copy the code

Restart the service

$ systemctl restart mysqld
Copy the code

Log on to the mysql

$ mysql -uroot -p
Copy the code

2.5. Configure the firewall

Add mysql service and required port 3306

$ firewall-cmd --zone=public --add-port=3306/tcp --permanent
$ firewall-cmd --zone=public --add-service=mysql --permanent
$ firewall-cmd --reload
Copy the code

Or temporarily turn off firewall tests

$ systemctl stop firewalld.service
Copy the code

2.6. Test remote login

The firewall is enabled. The 3306/ TCP protocol rules and mysql service rules are added. Mysql_native_password, to resolve the problem that the caching_sha2_password authentication plug-in cannot be loaded.

mysql> CREATE USER 'root'@The '%' IDENTIFIED BY 'Mysql@123456'; Step 1 Create a user
mysql> GRANT ALL ON *.* TO root@The '%'; -- The second step is authorization
mysql> ALTER USER 'root'@The '%' IDENTIFIED WITH mysql_native_password BY 'Mysql@123456'; - Step 3 Change the password authentication mode
mysql> flush privileges; Step 4 Refresh permissions
Copy the code

3. The solution for forgetting passwords

Many of you have probably forgotten your password after setting it. The solution in this paper is fully applicable to the latest version MySQL8.0.28, which has been tested and verified by myself.

Refer to the MySQL8.0 documentation and stackOverflow workaround. The result is a roundabout return to skipping login password permissions. The pre-8.0 method is invalid and WE are not keeping pace with MySQL updates.

MySQL > disable MySQL;

$ systemctl stop mysqld
Copy the code

2. Set the MySQL environment parameters and skip the permission table validation

$ systemctl set-environment MYSQLD_OPTS="--skip-grant-tables"
Copy the code

Or add it to /etc/my.cnf to have the same effect. Finally, forget to skip validation. Add skip-grant-tables to the my.ini file on Windows.

[mysqld]
skip-grant-tables
Copy the code

3. Start mysql with the Settings above

$ systemctl start mysqld
Copy the code

4. Log in to root

$ mysql -u root
Copy the code

5. Run the following command to change the password of user root

mysql> UPDATE mysql.user SET authentication_string = PASSWORD('MyNewPassword')
 -> WHERE User = 'root' AND Host = 'localhost';
mysql> FLUSH PRIVILEGES;
mysql> quit
Copy the code

A. This approach may not work

UPDATE user SET authentication_string = PASSWORD('123456') WHERE User = 'root' AND Host = 'localhost';
Copy the code

B. Set the password to blank

update user set authentication_string = ' ' where user = 'root';
Copy the code

6, modify password, solution, set stronger password rules

As mentioned my shokulei in the comments, for 5.76. and later, you should use 
   mysql> ALTER USER 'root'@'localhost' IDENTIFIED BY '123456';
Or you'll get a warning
Copy the code

MySQL8.0 if this is the case, please set a more secure password rules such as: Mysql@123456, you will be successful.

ERROR 1819 (HY000): Your password does not satisfy the current policy requirements
mysql> ALTER USER 'root'@'localhost' IDENTIFIED BY 'Mysql@123456';
Copy the code

7. Stop the mysql service

$ systemctl stop mysqld
Copy the code

Reset mysql environment variable parameters

$ systemctl unset-environment MYSQLD_OPTS
Copy the code

9. Start mysql again

$ systemctl start mysqld
Copy the code

$ mysql -u root -p
Copy the code

MySQL8.0

Mainly based on Windows10, some commands are also applicable to Linux.

1, Windows multiple MySQL service instances coexist

MySQL server 1067 = 1068; MySQL server 1067 = 1068;

net start mysql8
net start mysql
Copy the code

To test and demonstrate the latest version, I changed the service name to MySQL8.

MySQL8 = MySQL8Computer \ HKEY_LOCAL_MACHINE \ SYSTEM \ ControlSet001 \ Services \ MySQL8# 或者是MySQLComputer \ HKEY_LOCAL_MACHINE \ SYSTEM \ ControlSet001 \ Services \ MySQLCopy the code

I previously installed MariaDB10.5.6. I want to keep working with MariaDB and play with the latest version of MySQL8.0.28, so I chose this option.

1.1. Login and specify port 3307. Default is 3306, my MariaDB already uses port 3306. Personal test demonstrating multiple instances co-existing changed port to 3307.

Note: In Windows, use the CMD command window to log in as the administrator. If no environment variables are configured, go to the bin directory of MySQL installation and run the command.

-- Perform d: to switch to drive D
d:
Step 2 Run the CD command to switch to the bin directory of the personal mysql installation
cd D:\work\mysql8.028.-winx64\bin
Run the login command and specify a port
mysql -uroot -p -P 3307
-- Query database version` ` `sql
mysql> select version();
+-----------+
| version() |
+-----------+
| 8.028.    |
+-----------+
1 row in set (0.00 sec)
```
Copy the code

To sum up:

Step 1 Execute d: to switch to drive D.
Step 2 Run the CD command to switch to the bin directory of the personal mysql installation.
Step 3 Run the login command and specify a port to log in to mysql.
Finally, a simple interaction and a database version query.

1.2. Initially use the command line mode for interaction

2. Permission setting

2.1. Refer to official documents

Setting the remote login permission and password verification rules are related to the default authentication plug-in used by the database version

Refer to the MySQL official documentation: dev.mysql.com/doc/refman/…

01, Authentication plugin'caching_sha2_password'Is not supported 02, Authentication plugin'caching_sha2_password' cannot be loaded:
dlopen(/usr/local/ mysql/lib/plugins/caching_sha2_password. So, 2) : the image not found 03, Warning: mysqli_connect () : The server requested authentication method unknown to the client [caching_sha2_password]Copy the code

Use the password cache validation algorithm above. The caching_sha2_password authentication plug-in does not support caching_sha2_password and cannot be loaded. The service connection request warns that the authentication method cannot identify the client. A simple explanation: password validation rules are not allowed when connecting with clients such as SQLyog and MySQL Workbench. You need to change the authentication mode or solve the authentication in other ways. The solution is presented below.

2.2. Change the password of user root

Alter user alter user

ALTER USER 'root'@'localhost' IDENTIFIED BY 'New password';
Copy the code

2.3. Create regular users and authorize them (frequently used by developers or DBAs)

For starters, you can skip the licensing step and use root to hone your skills. Even before you get started, there is no scalp tingling licensing model to crash, haha.

GRANT command, REVOKE permission command Create a user and authorize official documents: dev.mysql.com/doc/refman/…

To CREATE a ROLE: CREATE ROLE command

CREATE ROLE 'app_developer'.'app_read'.'app_write';
Copy the code

GRANT role permissions: GRANT command

GRANT ALL ON app_db.* TO 'app_developer';
GRANT SELECT ON app_db.* TO 'app_read';
GRANT INSERT, UPDATE, DELETE ON app_db.* TO 'app_write';
Copy the code

To CREATE a USER: CREATE USER command

CREATE USER 'dev1'@'localhost' IDENTIFIED BY 'dev1pass';
CREATE USER 'read_user1'@'localhost' IDENTIFIED BY 'read_user1pass';
CREATE USER 'read_user2'@'localhost' IDENTIFIED BY 'read_user2pass';
CREATE USER 'rw_user1'@'localhost' IDENTIFIED BY 'rw_user1pass';
Copy the code

GRANT to the created user: GRANT command

GRANT 'app_developer' TO 'dev1'@'localhost';
GRANT 'app_read' TO 'read_user1'@'localhost'.'read_user2'@'localhost';
GRANT 'app_read'.'app_write' TO 'rw_user1'@'localhost';
Copy the code

You can also specify Settings in my.ini or my.cnf configuration files:

[mysqld]
mandatory_roles='role1,role2@localhost,r3@%.example.com'
Copy the code

It can also be SET using the SET command in command mode:

SET PERSIST mandatory_roles = 'role1,role2@localhost,r3@%.example.com';
Copy the code

Check the permissions of role dev1. I will not list the permissions. Please refer to the official documentation for details.

mysql> SHOW GRANTS FOR 'dev1'@'localhost';
+-------------------------------------------------+
| Grants for dev1@localhost                       |
+-------------------------------------------------+
| GRANT USAGE ON *.* TO `dev1`@`localhost`        |
| GRANT `app_developer`@`%` TO `dev1`@`localhost` |
+-------------------------------------------------+
Copy the code

Create user and grant all permissions:

CREATE USER 'old_app_dev'@'localhost' IDENTIFIED BY 'old_app_devpass';
GRANT ALL ON old_app.* TO 'old_app_dev'@'localhost';
Copy the code

LOCK a user. LOCK a user. Blind guess unlocking is UNLOCK

ALTER USER 'old_app_dev'@'localhost' ACCOUNT LOCK;
Copy the code

Grant permissions to new development accounts, and grant some permissions:

CREATE USER 'new_app_dev1'@'localhost' IDENTIFIED BY 'new_password';
GRANT 'old_app_dev'@'localhost' TO 'new_app_dev1'@'localhost';
Copy the code

The above provides official documents for reference, and instead of looking for the east and the west, it is better to read official documents more direct and more accurate. What we lack is not the means of learning, but the means of learning.

3, test create user

3.1. Create a common user and authorize remote login

Create a common user test

mysql> CREATE USER 'test'@'localhost' IDENTIFIED BY '123456';
Query OK, 0 rows affected (0.00 sec)
Copy the code

Grant localhost privileges to test, SELECT only query privileges, grant ALL privileges to test

mysql> GRANT SELECT ON *.* TO test@'localhost';
Query OK, 0 rows affected (0.01 sec)
Copy the code

3.2. Authorize user root

Create the root user to authorize all IP addresses to log in and change the password cache authentication mode.

mysql> CREATE USER 'root'@The '%' IDENTIFIED BY 'Mysql@123456';
mysql> GRANT ALL ON *.* TO root@The '%';
mysql> ALTER USER 'root'@The '%' IDENTIFIED WITH mysql_native_password BY 'Mysql@123456';
mysql> flush privileges;
Copy the code

Verify the login result in the third-party tool. The login succeeds in localhost:

Select (select); insert (select);

mysql> insert into study values(7.'the beautiful orange');
ERROR 1142 (42000) :INSERT command denied to user 'test'@'localhost' for table 'study'
Copy the code

This was verified with the SQLyog tool. Beginners are advised to practice in the command line window. Practice makes perfect.

3.2. Authorize root user to remotely log in to MySQL8.0

User authorization has become more stringent in MySQL8.0, and the way authorization can be performed in MySQL5.6 or 5.7 has changed. After personal test, the operation is as follows.

MySQL8.0 authorization mode, remember to flush PRIVILEGES

mysql> CREATE USER 'root'@The '%' IDENTIFIED BY 'Mysql@123456'; Step 1 Create a user
mysql> GRANT ALL ON *.* TO root@The '%'; -- The second step is authorization
mysql> ALTER USER 'root'@The '%' IDENTIFIED WITH mysql_native_password BY 'Mysql@123456'; - Step 3 Change the password authentication mode
mysql> flush privileges; Step 4 Refresh permissions
Copy the code

Change the password authentication mode (8.0 uses the SHA2 algorithm for cache authentication by default). The first solution is as follows.

ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY '123456';
ALTER USER 'root'@The '%' IDENTIFIED WITH mysql_native_password BY '123456';
Copy the code

Second solution: Add the following configuration to the my.ini or my.cnf configuration file, restart the service and load the configuration file. After testing it did not work, it seems that the configuration file was not read, but the strange thing is that the 3307 port and default storage engine and encoding format I set are working. (Solutions found on the official website)

[mysqld]
default_authentication_plugin=mysql_native_password
Copy the code

The default authentication cache algorithm for MySQL8.0 is caching_sha2_password

ALTER USER userIDENTIFIEDWITH caching_sha2_password BY 'password';
Copy the code

Authorization prior to MySQL8.0 (supported by 5.6 or 5.7)

GRANT ALL PRIVILEGES ON *.* TO 'Your username'@'Your IP address' IDENTIFIED BY 'Set password' WITH GRANT OPTION;
Copy the code

Example: Authorize the root user to connect to all IP addresses.

GRANT ALL PRIVILEGES ON *.* TO 'root'@The '%' IDENTIFIED BY '123456' WITH GRANT OPTION;
Copy the code

Refresh the permissions

flush privileges;
Copy the code

4. How to use official documents efficiently

mysql -uroot -p -P 3307
mysql -uroot -p
Copy the code

Use the help command to **? Find the system help command in the form create contents**.

? create contents;
? create user;
? create database;
? create table;
? select;
? insert;
? update;
? delete;
URL: https://dev.mysql.com/doc/refman/8.0/en/select|insert|update|delete.html
Copy the code

When you use the local help file, you will find that the system automatically prompts the address of the official document dev.mysql.com/doc.

Example: Query help for creating a table **? Create table** shows only part of the content.

mysql> ? create table
Name: 'CREATE TABLE'
Description:
Syntax:
CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name
    (create_definition,...)
    [table_options]
    [partition_options]
data_type:
    (see https://dev.mysql.com/doc/refman/8.0/en/data-types.html)
index_type:
    USING {BTREE | HASH}
index_option: {
    KEY_BLOCK_SIZE [=] value
  | index_type
 ...
}
URL: https://dev.mysql.com/doc/refman/8.0/en/create-table.html
Copy the code

Name: indicates the Name of the help command
Description: describe
Syntax: sample
Data_type: indicates the supported data type
Index_type: available index type

I only listed some parts to illustrate, more detailed can be tested by yourself.

Use uppercase to create users, databases, tables and fields because MySQL itself is case-sensitive and is case-sensitive under Linux and Unix.

1. Create a database

CREATE DATABASE TEST;
USE TEST;
Copy the code

Mysql5.6 and later use InnoDB as the default storage ENGINE. Mysql5.6 and later use InnoDB as the default storage ENGINE.

CREATE TABLE STUDY(
    ID INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
    NAMES VARCHAR(64) NOT NULL
)ENGINE=MyISAM;
Copy the code

3. Insert data

INSERT INTO STUDY VALUES(1.Mysql > update mSYql8.0.28);
Copy the code

4. Query data

SELECT * FROM STUDY;
Copy the code

5. Modify data

UPDATE STUDY S SET S.NAMES='Mysql's default storage engine is InnoDB' WHERE S.ID=1;
Copy the code

6. Delete all data

DELETE FROM STUDY;
Copy the code

So far the basic create user, create database, add, delete, change and check will be used.

MySQL connection tool

Made a hyperlink to facilitate access to the official website.

phpMyAdmin
MYSQL workbench
SQLyog

Several common tools are recommended: phpMyAdmin, SQLyog, MySQL Workbench, Navicat visual tools for connection operations. The use of tools is secondary, more important lies in the use of MySQL command statements.

SQLyog is included with some of the installation packages and examples of Sakila and World provided by MySQL.

Link: pan.baidu.com/s/11gIlZKxo… Extraction code: NTU7

Here is a model of a sample database world that is reverse-generated using Navicat:

If you really want to build physical models: It is recommended that you learn to use the Sybase PowerDesigner design tool and understand the three paradigms that relational database design follows. At present, the database design can meet 3NF at most, which is generally considered to be too high. Although it has better constraint on data relationship, it also leads to the increase of data relationship tables and makes database IO more busy. The relationship constraint that was originally handled by the database is now more completed in the database using program.

MySQL JDBC

1. Official Connector-J

MySQL8.0 maven installation JDBC:dev.mysql.com/doc/connect…

A JDBC connection driver management: dev.mysql.com/doc/connect…

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;

// Notice, do not import com.mysql.cj.jdbc.*
// or you will have problems!

public class LoadDriver {
    public static void main(String[] args) {
        try {
            // The newInstance() call is a work around for some
            // broken Java implementations
            Class.forName("com.mysql.cj.jdbc.Driver").newInstance();
        } catch (Exception ex) {
            // handle the error
        }
    }
}
Connection conn = null; . try { conn= DriverManager.getConnection("jdbc:mysql://localhost/test?" +
                                   "user=root&password=123456");
    // Do something with the Connection
   ...
} catch (SQLException ex) {
    // handle any errors
    System.out.println("SQLException: " + ex.getMessage());
    System.out.println("SQLState: " + ex.getSQLState());
    System.out.println("VendorError: " + ex.getErrorCode());
}
Copy the code

2, JDBC test connect to MySQL8.0 database

2.1. Maven Configuration

Set up the pom. XML configuration file and use the latest MySQL version 8.0.28 for connection tests. Maven’s mirror repository can use Ali’s mirror source address.

<dependency>
    <groupId>mysql</groupId>
    <artifactId>mysql-connector-java</artifactId>
    <version>8.0.28</version>
</dependency>

<dependency>
    <groupId>mysql</groupId>
    <artifactId>mysql-connector-java</artifactId>
    <version>8.0.28</version>
    <exclusions>
        <exclusion>
            <groupId>com.google.protobuf</groupId>
            <artifactId>protobuf-java</artifactId>
        </exclusion>
    </exclusions> 
</dependency>
Copy the code

2.2. Write Java code

Use the editor STS (Spring Tool Suite4 or IDEA)

Create a normal Maven project or a SpringBoot project and configure POM.xml.

Objective: To use pure JDBC testing, or ORM framework Mybatis, JPA, or Hibernate, and finally achieve the most basic add, delete, change and check database.

package com.example.demo.dao;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

public class TestConnMySQL8 {
    
	public static void main(String[] args) throws ClassNotFoundException, SQLException {	
		TestSQLConnMySQL();
	}
	private static final Logger log = LoggerFactory.getLogger(TestConnMySQL8.class);
	// Initialize parameters
	static Connection conn = null;
	static PreparedStatement ps = null;
	static ResultSet rs = null;
	/ * * *@throws SQLException
	 * @throws ClassNotFoundException
	 */
	private static void TestSQLConnMySQL(a) throws SQLException, ClassNotFoundException {
		try {
			Class.forName("com.mysql.cj.jdbc.Driver");
			/ * * * 1. Get the connection parameters url, username, password, the default port is 3306 * MySQL: url = "JDBC: MySQL: / / 127.0.0.1:3306 / test". * /
			/** MySQL concatenate url **/
			String url = "JDBC: mysql: / / 192.168.245.147:3306 / TEST? useUnicode=true&characterEncoding=utf-8";
			String username = "root";
			String password = "Mysql@123456";
			// Get the connection
			conn = DriverManager.getConnection(url, username, password);
			if(null! = conn) { log.info("connect database success...");
			}else {
				log.error("connect database failed...");
			}
			// Query the database
			String sql = "SELECT * FROM STUDY";
			// 3. Execute SQL in preparedStatement
			ps = conn.prepareStatement(sql);	
			// 4. Run the query to obtain the result set
			rs = ps.executeQuery();
			// 5. Iterate through the result set if your database has tables and data
			while (rs.next()) {
				Int uses getInt and vARCHAR uses getString
				System.out.println("ID:" + rs.getInt("ID"));
				System.out.println("Name:" + rs.getString("NAMES")); }}finally {
			// 6. Close the connection to release resourcesrs.close(); ps.close(); conn.close(); }}}Copy the code

Connect in the STS editing tool and return the test results

conclusion

MySQL8.0.28 installation and use of the entire content, hope to help you work and learn. Feel good, take out your one button three link. Update in the public number may be a little faster, is still in perfect.All the people who can see here are pretty guys. If I feel that the summary is not in place, I also hope to leave your valuable opinions. I will adjust and optimize the article.

Original is not easy, reprint also please indicate the source and author, respect the original. Upload to Github or Gitee from time to time. Look for longteng Wanli Sky, if you see other platforms not this ID issued my article, is reprinted. MySQL development: Does storage engine choice Really Matter? It has been uploaded to github and Gitee repository SQL-Study. Personal Github repository address, usually update PDF file first, then upload Markdown file. If accessing Github is too slow, clone using Gitee.

Tips: Static blogs built with Hexo are also regularly updated and maintained.

mo4tech.com (Moment For Technology) is a global community with thousands techies from across the global hang out!Passionate technologists, be it gadget freaks, tech enthusiasts, coders, technopreneurs, or CIOs, you would find them all here.

Install MySQL8.0.28 by referring to the official MySQL documentation

preface

The body of the

MySQL8.0.28下载

1. Download the Windows version

2. Download Linux version

3. Precautions

Install MySQL8.0

1. Install in Windows

2. Install in Linux

3. The solution for forgetting passwords

MySQL8.0

1, Windows multiple MySQL service instances coexist

2. Permission setting

3, test create user

4. How to use official documents efficiently

MySQL connection tool

MySQL JDBC

1. Official Connector-J

2, JDBC test connect to MySQL8.0 database

conclusion

Author: Longteng Wanli Sky

Install MySQL8.0.28 by referring to the official MySQL documentation

preface

The body of the

MySQL8.0.28下载

1. Download the Windows version

2. Download Linux version

3. Precautions

Install MySQL8.0

1. Install in Windows

2. Install in Linux

3. The solution for forgetting passwords

MySQL8.0

1, Windows multiple MySQL service instances coexist

2. Permission setting

3, test create user

4. How to use official documents efficiently

MySQL connection tool

MySQL JDBC

1. Official Connector-J

2, JDBC test connect to MySQL8.0 database

conclusion

Author: Longteng Wanli Sky

Related Posts

Golang Performance Optimization Analysis Tool PPROF (Part 1)

SRS(Simple Live Video Service) Notes (2) – Port and URL description

SpringBoot is docker-oriented