Secular wanderer: a programmer who focuses on technical research

Say what I said before

Last stage about Java foundation, almost related basic knowledge has talked about, so this stage we mainly talk about MySQL database.

Introduction to MySQL

A relational database management system (RDBMS) supports a relational (table-oriented) data model. The schema of a table (relational schema) is defined by the table name and a fixed number of properties with fixed data types. A record (entity) corresponds to a row in the table and consists of the values of each attribute. Thus, a relationship consists of a uniform set of records.

MySQL is an open source relational database, second only to Oracle database in popularity

Single machine configuration

To do a good job, you must sharpen your tools. Let’s install them first

Fast installation

Based on the RPM installation mode, download the package here:

MySQL > download

The environment

System: CentOS 7

IP: 192.168.87.201

I’m not gonna talk about uploading files, okay

Verify that the current system does not have MySQL

rpm -qa | grep mysql
Copy the code

Nothing came out, which means nothing. If so, you need to uninstall it first

RPM -e Specifies the service nameCopy the code

The RPM parsing

rpm -ivh mysql80-community-release-el7-3.noarch.rpm
Copy the code

Select version

vim /etc/yum.repos.d/mysql-community.repo
Copy the code

If you have MySQL5.7 installed, follow these changes

In the mysql57enabled=0Modified intoenabled=1
Will the mysql80enabled=1Modified intoenabled=0

I’m installing MySQL8, which is enabled by default, so go to the next step

Installation services

yum install mysql-community-server
Copy the code

After a series of downloads, MySQL is installed here

Start the service

systemctl start mysqld
Copy the code

At this point we can only say that it is started, but not finished

The basic configuration

A temporary password

grep 'temporary password' /var/log/mysqld.log
Copy the code

This password is a temporary password generated by MySQL for us. The current password does nothing

Change the password

mysql -uroot -p
Copy the code

Run the current command + the password found in the previous step to access MySQL, as shown in the following figure

When you come in here, you’ll see that every command you execute will have this error, which means we have to set a password to execute it, so let’s do this

You must reset your password using ALTER USER statement before executing this statement
Copy the code

ALTER USER USER(a)IDENTIFIED BY 'Passwords with combinations of case, number, and special characters';
Copy the code

Mysql -uroot -p mysql -uroot -p mysql -uroot -p mysql -uroot -p mysql -uroot -p

Then we can execute the command from the command line:

List all library names
show databases;
Switch to the mysql library
use mysql;
Copy the code

Of course, now only the local can operate, but can not connect to the remote, let’s continue to set up

The remote connection

Now that we’ve switched to the mysql library, let’s go straight to it

select host, user,authentication_string, plugin from user;
Copy the code

The main modification of the underlined two places

Modify the host to%, indicating that all connections are available and refreshed

update user set host=The '%' where user='root';
flush privileges;
Copy the code

Modify the authentication plug-in

# set password not to expire
alter user 'root'@The '%' identified by 'Password of case, number, special character, same as above' password expire never;
# Modify the authentication plug-in
alter user 'root'@The '%' identified with mysql_native_password by 'Password of case, number, special character, same as above';
flush privileges;
Copy the code

After the above two steps, you can connect through other remote tools, as shown in the figure

plugin

As of MySQL 8.0.4, the default authentication plugin for MySQL server has been changed from mysql_native_password to caching_sha2_password. Other remote tools have not tried, if you have tried, welcome to leave a message, I timely correction.

For more details, see here:

MySQL authentication plug-in tweaks

Introduction to SQL

The installation is complete, of course, can’t wait to use, let’s look at some simple SQL:

Manipulation library

Create the library

create database test character set='utf8mb4';
Copy the code

Sometimes we don’t know how to use a particular command, so what should we do? In addition to Baidu, the most direct way is to check the help documentation provided by MySQL:

List all commands associated with create
help create;
# describe the current command, such as options, reference url, etc
help create database;
Copy the code

First hand information we need to know how to get, and that’s one of the ways we can learn a skill quickly, right

Delete library

drop database test;
Copy the code

Recommended caution, no means of recovery, suggested running

The operating table

Create a table

create table if not exists user(
	id bigint primary key auto_increment comment 'ID number',
    login_name varchar(64) default ' ' comment 'Login account',
    login_pwd varchar(64) default ' ' comment 'Login password'
) engine=innodb character set='utf8mb4';
Copy the code

Bit by bit

Create table is a fixed format. For more details, see Help Create table
**[if not exists]**

[IF not EXISTS] does not report an error when a table is created that already exists in the database and does not interrupt SQL execution, but does if it is not added

[Err] 1050 - Table 'user' already exists
Copy the code

This is followed by the table name
The fields inside the parentheses belong to the entire table, which is the overall schema of the table
- Field name. Enter the field name as required
- Field types, there are many types of field types, the above is just a simple use of two, there will be a special introduction
- primary key auto_increment: indicates that the field is set to the primary key and the primary key increases automatically
- default: Default value inserted by mysql when no value is inserted into this field
- comment: represents a comment for this field, the same as a comment in Java development
Engine: Specifies which storage engine is used for this table
- InnoDB
- MyISAM
Character set: character set

Run show tables to view the tables that have been created

Insert data

Insert into fixed format, for details

See help Insert for more detailed instructions

insert into 
	user(login_name, login_pwd) 
value ('[email protected]'.md5('123456'));

insert into 
	user(login_name, login_pwd) 
values 
('[email protected]'.md5('123456')),
('[email protected]'.md5('123456'));
Copy the code

Here I say two things:

user(login_name, login_pwd)

If a field is specified, data needs to be inserted into only the specified field. If no field is specified, data needs to be inserted into all fields

The difference between value and values

The former inserts a single record, while the latter can insert multiple records

ON DUPLICATE KEY UPDATE

If the current data already exists, the update operation will be performed. In this case, the primary key value must be passed as the identification when the data is inserted

insert into 
	user
value (3.'[email protected]'.md5('123456'))
on DUPLICATE key update 
login_name = values(login_name)
Copy the code

This is the update operation

Modify the data

Update table;

update user set login_name = '[email protected]' where id = 1;
Copy the code

Among them:

setRepresents the field to be updated and the new value, update multiple fields through.separate
whereRepresents a condition, if none, then all values in the table are updated

A simple query

The query is the most common and complex part of the entire SQL operation:

select * from user;
Copy the code

If you want to see more specific instructions, you can use help Select. We will also introduce select in more detail later

Delete the data

delete from user where id  = 3;
Copy the code

Do not forget to add conditions, or delete all data in the table

Ok, here are some simple operations to give you an idea, and there will be more details later

The last word

So that’s the end of MySQL quick start, and we’ll move on

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.

MySQL In Depth: Quick installation of MySQL

Say what I said before

Introduction to MySQL

Single machine configuration

Fast installation

Verify that the current system does not have MySQL

The RPM parsing

Select version

Installation services

Start the service

The basic configuration

A temporary password

Change the password

The remote connection

Introduction to SQL

Manipulation library

The operating table

Create a table

Insert data

Modify the data

A simple query

Delete the data

The last word

MySQL In Depth: Quick installation of MySQL

Say what I said before

Introduction to MySQL

Single machine configuration

Fast installation

Verify that the current system does not have MySQL

The RPM parsing

Select version

Installation services

Start the service

The basic configuration

A temporary password

Change the password

The remote connection

Introduction to SQL

Manipulation library

The operating table

Create a table

Insert data

Modify the data

A simple query

Delete the data

The last word

Related Posts

Concurrency Programming in Java – Visibility, Atomicity, Orderliness & How does the Java Memory Model address Visibility and Orderliness

Pit of Redis: The difference between Redis and MySQL transactions

【Spring source 】Spring overview and environment construction