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 code1.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 code1.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 codeSet it to an empty password to remove unnecessary hassle.
mysqld --initialize-insecure
Copy the code1.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 codeIf 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 codeNet 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 codeDelete service sc delete mysql
# delete service
sc delete mysql
Mysql > remove mysqld mysql > remove mysqld
mysqld remove mysql
Copy the code1.5. Precautions
MySQL8 = MySQL8Computer \ HKEY_LOCAL_MACHINE \ SYSTEM \ ControlSet001 \ Services \ MySQL8# 或者是MySQLComputer \ HKEY_LOCAL_MACHINE \ SYSTEM \ ControlSet001 \ Services \ MySQLCopy the codeThe 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 codeCommand to change the password
ALTER USER 'root'@'localhost' IDENTIFIED BY 'New password'
Copy the codeThe 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 code2.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 code2.2.2 Redhat7 needs to uninstall mariadB-libs and replace it with mysql-community-libs
$ yum remove mariadb-libs
Copy the code2.2.3 Formally installing the Server
$RPM - the ivh mysql - community - server - 8.0.28-1. El7. X86_64. RPMCopy the codeCheck 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 code2.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 codeTips: 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 code2.4. Start the service and view the service status
The Redhat7 series uses commands to start the MySQL service
$ systemctl start mysqld
Copy the codeSet boot to start automatically
$ systemctl enable mysqld
Copy the codeClose the service
$ systemctl stop mysqld
Copy the codeRestart the service
$ systemctl restart mysqld
Copy the codeLog on to the mysql
$ mysql -uroot -p
Copy the code2.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 codeOr temporarily turn off firewall tests
$ systemctl stop firewalld.service
Copy the code2.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 code2. Set the MySQL environment parameters and skip the permission table validation
$ systemctl set-environment MYSQLD_OPTS="--skip-grant-tables"
Copy the codeOr 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 code3. Start mysql with the Settings above
$ systemctl start mysqld
Copy the code4. Log in to root
$ mysql -u root
Copy the code5. 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 codeA. This approach may not work
UPDATE user SET authentication_string = PASSWORD('123456') WHERE User = 'root' AND Host = 'localhost';
Copy the codeB. Set the password to blank
update user set authentication_string = ' ' where user = 'root';
Copy the code6, 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 codeMySQL8.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 code7. Stop the mysql service
$ systemctl stop mysqld
Copy the codeReset mysql environment variable parameters
$ systemctl unset-environment MYSQLD_OPTS
Copy the code9. Start mysql again
$ systemctl start mysqld
Copy the codeLog in to mysql successfully
$ mysql -u root -p
Copy the codeMySQL8.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 codeTo 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 codeI 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 codeUse 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 code2.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 codeGRANT 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 codeTo 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 codeGRANT 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 codeYou can also specify Settings in my.ini or my.cnf configuration files:
[mysqld]
mandatory_roles='role1,role2@localhost,r3@%.example.com'
Copy the codeIt can also be SET using the SET command in command mode:
SET PERSIST mandatory_roles = 'role1,role2@localhost,r3@%.example.com';
Copy the codeCheck 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 codeCreate 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 codeLOCK a user. LOCK a user. Blind guess unlocking is UNLOCK
ALTER USER 'old_app_dev'@'localhost' ACCOUNT LOCK;
Copy the codeGrant 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 codeThe 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 codeGrant 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 code3.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 codeVerify 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 codeThis 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 codeChange 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 codeSecond 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 codeThe default authentication cache algorithm for MySQL8.0 is caching_sha2_password
ALTER USER userIDENTIFIEDWITH caching_sha2_password BY 'password';
Copy the codeAuthorization 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 codeExample: 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 codeRefresh the permissions
flush privileges;
Copy the code4. How to use official documents efficiently
Log in to MySQL8 and specify port 3307, or log in using the default port.
mysql -uroot -p -P 3307
mysql -uroot -p
Copy the codeUse 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 codeWhen 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 codeMysql5.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 code3. Insert data
INSERT INTO STUDY VALUES(1.Mysql > update mSYql8.0.28);
Copy the code4. Query data
SELECT * FROM STUDY;
Copy the code5. Modify data
UPDATE STUDY S SET S.NAMES='Mysql's default storage engine is InnoDB' WHERE S.ID=1;
Copy the code6. Delete all data
DELETE FROM STUDY;
Copy the codeSo 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 code2, 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 code2.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 codeConnect 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.