MySQL 8 database | MySQL tuning | underlying principles of MySQL | MySQL zero foundation novice tutorial

Time:2022-1-5

MySQL 8 database installation

1、 Install in Windows Environment

A、Download MySQL

Select Operating System:
Microsoft Windows

Quick download:mysql-8.0.22-winx64.zip

B. Extract and configure MySQL environment variables

MYSQL_HOME:
C:\MySQL\mysql-8.0.22-winx64

C. Create in the unzipped root directorymy.iniconfiguration file

[mysqld]
#Set 3306 port
port = 3306
#Set MySQL installation directory
basedir=C:/MySQL/mysql-8.0.22-winx64
#Set the storage directory of MySQL database data
datadir=C:/MySQL/mysql-8.0.22-winx64\data
#Maximum connections allowed
max_connections=200
#Number of connection failures allowed. This is to prevent someone from trying to attack the database system from the host
max_connect_errors=10
#The character set used by the server is utf8 by default
character-set-server=utf8mb4
#The default storage engine that will be used when creating new tables
default-storage-engine=INNODB
#The "mysql_native_password" plug-in authentication is used by default
default_authentication_plugin=mysql_native_password

[mysql]
#Set the default character set of MySQL client
default-character-set=utf8mb4

[client]
#Set the default port used by MySQL client when connecting to the server
port=3306
#Set the default character set used when connecting the MySQL client to the server
default-character-set=utf8mb4

D. Install MySQL (the following operations must be administrator)

  1. Initialize MySQL
mysqld --defaults-file=C:\MySQL\mysql-8.0.22-winx64\my.ini --initialize --console

be careful: copy and save MySQL initialization passwordfVdpg:bM9pAk

  1. Install MySQL service
mysqld --install mysql8
  1. Start MySQL service
net start mysql8

E. Login and password modification

  1. Log in to MySQL
MySQL - U account - P password

Solutions that cannot log in using the above method

1. Stop mysql8net stop mysql8

2. Start without passwordmysqld --console --skip-grant-tables --shared-memory

3. The front window cannot be closed. Open a new window to log in without passwordmysql -u root -p

4. Clear passwordupdate mysql.user set authentication_string='' where user='root' and host='localhost;'

5. Refresh permissionsplush privileges;

6. Restart the MySQL service and log in to MySQL without password

  1. After logging in, use Mysql to change the password
ALTER USER [email protected] IDENTIFIED BY '123456';
  1. Turn on remote access
CREATE USER 'root' @'%' IDENTIFIED BY '123456'; --  It doesn't matter if this step fails

GRANT ALL ON *.* TO 'root' @'%';

# alter user 'root'@'%' identified with mysql_native_password by '123456';

FLUSH privileges;

2、 Installation in Linux Environment

A、Download MySQL

Select Operating System:
Source Code

Select OS Version:
Generic Linux (Architecture Independent)

Quick download:mysql-8.0.22.tar.gz

B. Upload the downloaded MySQL compressed package to the Linux server

C. Decompressmysql-8.0.22.tar.gz

tar -zxvf mysql-8.0.22.tar.gz

D. Move the extracted files to the / usr / local directory

mv mysql-8.0.22 /usr/local/mysql

E. Add a MySQL composite user (it will be added by default, and it will be added manually if it is not added)

groupadd mysql
useradd -r -g mysql mysql

F. Enter/usr/local/mysqlDirectory, modify related permissions

cd /usr/local/mysql
chown -R mysql:mysql ./

G. MySQL initialization operation, record temporary password

cd /usr/local/mysql/bin
./mysqld --initialize --user=mysql --basedir=/usr/local/mysql --datadir=/usr/local/mysql/data

be careful: copy and save MySQL initialization passwordfVdpg:bM9pAk

H. Create MySQL profile/etc/my.cnf

cd /etc
vi my.cnf

my.cnf

[mysqld]
port = 3306
basedir=/usr/local/mysql
datadir=/usr/local/mysql/data
max_connections=200
max_connect_errors=10
character-set-server=utf8mb4
default-storage-engine=INNODB
default_authentication_plugin=mysql_native_password

[mysql]
default-character-set=utf8mb4

[client]
port=3306
default-character-set=utf8mb4

1. Start MySQL service

cd /usr/local/mysql/support-files
./mysql.server start

J. Log in to MySQL with a temporary password and change the password

cd /usr/local/mysql/bin
. / MySQL - U temporary password generated by root - P 
ALTER USER 'root' @'localhost' IDENTIFIED BY '123456';

K. Turn on remote access

CREATE USER 'root' @'%' IDENTIFIED BY '123456';  --  It doesn't matter if this step fails

GRANT ALL ON *.* TO 'root' @'%';

FLUSH privileges;

Mysql database operation

Database operation

Create database

CREATE DATABASE db_name DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;

query data base

--Query all databases
SHOW DATABASES;
--SQL script for querying database tables
SHOW CREATE DATABASE db_name;

Delete database

DROP DATABASE db_name;

modify the database

--Modify the character encoding and sorting method of the database
ALTER DATABASE db_name DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;

Select database

USE db_name;

Sets the encoding format for the operation

SET NAMES utf8;

Table operation

Create table

CREATE TABLE tb_ Name (field, type, length, constraint, default, comment for creating table)

constraint

  • Non emptyNOT NULL
  • NonnegativeUNSIGNED
  • Primary keyPRIMARY KEY
  • Self increasingAUTO_INCREMENT
  • defaultDEFAULT
  • notesCOMMENT
--Delete database if it exists
DROP DATABASE IF EXISTS testdb;
--Create database operation
CREATE DATABASE IF NOT EXISTS testdb;
--Use database
USE testdb;
--Delete data table if it exists
DROP TABLE IF EXISTS testdb;
--Create table operation
CREATE TABLE IF NOT EXISTS tb_test 
( 
	test_id INTEGER ( 10 ), 
	test_name VARCHAR ( 50 ) 
);
--Use database
USE testdb;
--Delete data table if it exists
DROP TABLE IF EXISTS testdb;
--Create table operation
CREATE TABLE IF NOT EXISTS tb_test 
( 
	test_ id INTEGER (10) AUTO_ Increment primary key comment 'test ID', 
	test_ Name varchar (50) not null comment 'test name',
	test_ Password varchar (20) not null default '123456' comment 'test password'
);

Common types

  • Minimal shapingTIYINT1 byte, unsigned, maximum 256 (2 ^ 8 – 1), plus or minus – 128 ~ 127 (- 2 ^ 7 – 1 ~ 2 ^ 7 – 1)
  • Small plasticSMALLINT2 bytes, unsigned, maximum 65535 (2 ^ 16 – 1), plus or minus – 32768 ~ 32767 (- 2 ^ 15 – 1 ~ 2 ^ 15 – 1)
  • Medium plasticMEDIUMINT3 bytes, unsigned, maximum 16777215 (2 ^ 24 – 1), plus or minus (- 2 ^ 23-1 ~ 2 ^ 23-1)
  • plasticINT4 bytes, unsigned, max. 2 ^ 32 – 1, plus or minus (- 2 ^ 31-1 ~ 2 ^ 31-1)
  • Long shapingBIGINT8 bytes, unsigned, max. 2 ^ 64 – 1, plus or minus (- 2 ^ 63-1 ~ 2 ^ 63-1)
  • Single precisionFLOAT4 bytes float [(m, d)] – 3.4e + 38 ~ 3.4e + 38 (about)
  • Double precisionDOUBLE8 bytes double [(m, d)] – 1.79e + 308 ~ 1.79e + 308 (about)
  • Small valueDECIMAL M>D ? M+2 : D+2Bytes decimal [(m, d)] Note: m is length and D is decimal
  • Fixed length stringCHARThe maximum storage is 255 bytes. If the value does not reach the given length, use spaces to supplement
  • Variable length stringVARCHARThe maximum storage length is 255 bytes. How long does it take
  • Minimal textTINYTEXTMaximum length 255 bytes (2 ^ 8-1)
  • Chinese textMEDIUMTEXTMaximum length 16777215 bytes (2 ^ 24-1)
  • textTEXTMaximum length 65535 bytes (2 ^ 16-1)
  • Long textLONGTEXTMaximum length 4294967295 bytes (2 ^ 32-1)
  • dateDATEDate (yyyy MM DD)
  • timeTIME Time (HH: mm: SS)
  • Date timeDATETIMEDate and time combination (yyyy MM DD HH: mm: SS)
  • time stampTIMESTAMP yyyymmddhhmmss
  • particular yearYEARYear YYYY
--Create table operation
CREATE TABLE IF NOT EXISTS tb_user
( 
	user_ id int(11) AUTO_ Increment primary key comment 'user ID', 
	user_ Name varchar (30) not null comment 'user name',
	user_ Birthday date comment 'user's birthday',
	user_ Gender char (3) comment 'user gender',
	user_ Status tinyint (1) not null comment 'user status',
	user_ Height decimal (4,1) not null comment 'user height',
    user_ Desc text comment 'user profile'
);

Table field index

  • Primary key index: alter tabletable_name ADD PRIMARY KEY (column)Used to uniquely identify a record
  • Unique index: alter tabletable_name ADD UNIQUE (column)Often not to improve access speed, but to avoid data duplication
  • General index: alter tabletable_name ADD INDEX index_name (column), the only task is to speed up access to data
  • Full text index: alter tabletable_name ADD FULLTEXT index_name (column1, column2), which can only be used for MyISAM tables. For large data, generating full-text indexes takes a lot of time and space
  • Federated index: alter tabletable_name ADD INDEX index_name (column1, column2, column3), in order to improve MySQL efficiency
#Delete primary key index
ALTER TABLE `table_name` DROP PRIMARY KEY

#Delete unique index
ALTER TABLE `table_name` DROP INDEX unique_index_name;
ALTER TABLE `table_name` DROP INDEX cloumn;

#Delete normal index
ALTER TABLE `table_name` DROP INDEX index_name;

#Delete full-text index
ALTER TABLE `table_name` DROP INDEX fulltext_index_name;
ALTER TABLE `table_name` DROP INDEX cloumn;
Modify table

Field add

# ALTER TABLE tb_ Name add field type non empty constraint default value comment
ALTER TABLE tb_ Name add address varchar (100) not null default comment 'user address';

Field type modification

# ALTER TABLE tb_ Name modify field new field type non NULL constraint default value comment
ALTER TABLE tb_ Name modify address varchar (50) not null default comment 'user address';

Field name type modification

# ALTER TABLE tb_ Name modify old field new field new field type non empty constraint default value comment
ALTER TABLE tb_ Name change address addr varchar (50) not null default comment 'user address';

Field type query

DESC tb_name;

Field deletion

# ALTER TABLE tb_ Name DROP field
ALTER TABLE tb_name DROP addr;

Table name modification

#Alter table old table name rename to new table name
ALTER TABLE tb_name RENAME TO tb_name1

Table engine modification

# ALTER TABLE tb_ Name engine = new engine
ALTER TABLE tb_name ENGINE = MyISAM;
Delete table
#Drop table table name
DROP TABLE tb_name;
#Delete if table exists
DROP TABLE IF EXISTS tb_name;
Query table
#Query all tables
SHOW TABLES;
#Script for query table creation
SHOW CREATE TABLE tb_name;

MySQL DML operation

New data
#Insert into table name (field name: field 1, field 2,... Field n) values (value 1, value 2,... Value n);

#Full table insert
INSERT INTO `tb_ User ` (` user _name `, ` user _birthday `, ` user _gender `, ` user _status `, ` user _height `, ` user _desc `) values ('zeng Xiaoxian ',' 2020-11-22 ',' male ', 1, 174.5,' a good man is me, I am a good man Zeng Xiaoxian ');

#Specify column insertion, provided that other columns have no non empty constraints
INSERT INTO `tb_ User ` (` user_name `, ` user_birthday `, ` user_gender `, ` user_status `, ` user_height `) values ('hu Xiaomei ',' 2020-11-22 ',' female ', 1, 174.5);
Modify data
#Update table name set field 1 = new value 1, field 2 = new value 2 Field n = new value n where condition
UPDATE `tb_user` SET user_birthday='1995-10-20' WHERE user_id=2;
UPDATE `tb_user` SET user_birthday='1995-10-20', user_status = 2 WHERE user_id=2;

UPDATE `tb_user` SET user_status = 1 where user_id > 1;

UPDATE `tb_user` SET user_status = 1;
Delete data
#Delete from table name where condition
DELETE FROM `tb_user` WHERE user_id=2;
Query data
#Select field 1, field 2 Field n from table name where condition

#Query without conditions
select * from tb_user;
select user_id,user_name from tb_user;

#Query with conditions (comparison operation >, <, > =, < =,! =, < >, =)
select * from tb_user where user_id > 1;

#Query with logical conditions (and, or)
select * from tb_user where user_status = 1 and user_id > 1;
select * from tb_ user where user_ id = 1 or user_ Name = 'Hu Xiaomei';

#Fuzzy query (like%%)
select * from tb_ user where user_ Name like '%';
select * from tb_ user where user_ Name like '% free';
select * from tb_ user where user_ Name like '% small%';

#Range query
select * from tb_user where tb_status in (0,1,2);

#Aggregate function
-- count(field)
Select count (user_id) number of users from TB_ user;
-- sum(field)
Select sum (user_height) total height from TB_ user;
-- avg(field)
Select AVG (user_height) average height from TB_ user;

...

#Grouping query
--Group by counts the average height of men and women: the fields in the group by query must be the fields after group by
select user_ Gender as gender, AVG (user_height) average height from TB_ user group by user_ gender;

select user_ status,user_ Gender as gender, AVG (user_height) average height from TB_ user group by user_ gender,user_ status;

select user_ Gender as gender, AVG (user_height) average height, sum (user_height), count (user_id) number of users from TB_ user group by user_ gender;

#Sort query
--Order by defaults to asc ascending and desc descending; Order by is placed after group by
select * from tb_user order by user_id asc;

select * from tb_user order by user_id desc;

select * from tb_user where user_id < 10 order by user_id desc;

select * from tb_user where user_id < 10 order by user_id,user_status desc;

select user_ Gender as gender, AVG (user_height) average height, sum (user_height), count (user_id) number of users from TB_ user group by user_ Gender order by number of users;
#Create score table
CREATE TABLE `tb_score` (
  `id` int(11) NOT NULL PRIMARY KEY AUTO_INCREMENT,
  `stu_id` int(11) NOT NULL,
  `cou_id` int(11) NOT NULL,
  `score` decimal(4,1) NOT NULL
);

--Insert test data 
INSERT INTO tb_score (`stu_id`, `cou_id`, `score`) VALUES(1,1,89.0);
INSERT INTO tb_score (`stu_id`, `cou_id`, `score`) VALUES(1,2,78.0);
INSERT INTO tb_score (`stu_id`, `cou_id`, `score`) VALUES(1,3,94.0);
INSERT INTO tb_score (`stu_id`, `cou_id`, `score`) VALUES(1,4,77.0);
INSERT INTO tb_score (`stu_id`, `cou_id`, `score`) VALUES(1,5,99.0);
INSERT INTO tb_score (`stu_id`, `cou_id`, `score`) VALUES(3,1,90.0);
INSERT INTO tb_score (`stu_id`, `cou_id`, `score`) VALUES(3,2,88.0);
INSERT INTO tb_score (`stu_id`, `cou_id`, `score`) VALUES(3,3,69.0);
INSERT INTO tb_score (`stu_id`, `cou_id`, `score`) VALUES(3,4,83.0);
INSERT INTO tb_score (`stu_id`, `cou_id`, `score`) VALUES(3,5,92.0);
INSERT INTO tb_score (`stu_id`, `cou_id`, `score`) VALUES(2,1,77.0);
INSERT INTO tb_score (`stu_id`, `cou_id`, `score`) VALUES(2,2,84.0);
INSERT INTO tb_score (`stu_id`, `cou_id`, `score`) VALUES(2,3,91.0);
INSERT INTO tb_score (`stu_id`, `cou_id`, `score`) VALUES(2,4,80.0);
INSERT INTO tb_score (`stu_id`, `cou_id`, `score`) VALUES(2,5,99.0);

#Paging query
--Query the highest score with account ID 1
select max(score) from tb_score where course_id = 1;
select * from tb_score where course_id = 1 limit 1;
--Query the top five grades with course ID 4
select * from tb_score where course_id = 4 order by score limit 5;
--Limit paging. The starting value is 0: (PageIndex - 1) * PageSize, PageSize
select * from tb_score limit 0,10
select * from tb_score limit 10,10
select * from tb_score limit 20,10