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 mysql57
enabled=0
Modified intoenabled=1
- Will the mysql80
enabled=1
Modified 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 automaticallydefault
: Default value inserted by mysql when no value is inserted into this fieldcomment
: 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:
set
Represents the field to be updated and the new value, update multiple fields through.
separatewhere
Represents 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