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
Tags: , , , ,

Recommended Today

(Do not request a bean from a BeanFactory in a destroy method implementation!)

Exception: Singleton bean creation not allowed while singletons of this factory are in destruction (do not request a bean from a beanfactory in a destruction method implementation!) If your project references spring cloud starter Zipkin jar and spring boot starter data redis jar at the same time, redis will not be connected and this exception […]