MySQL basics -- 001 database overview and MySQL installation

Cool breeze AAA 2022-02-13 07:35:18 阅读数:742

mysql basics database overview mysql

The first 01 Chapter _ Database Overview

lecturer : Silicon Valley - Song Hongkang ( Jianghu people : a brand of instant noodles )

Official website :http://www.atguigu.com


1. Why use a database

  • Persistence (persistence): Save data to a power down storage device for later use . Most of the time , Especially for enterprise applications , Data persistence means saving the data in memory to the hard disk ” curing ”, The implementation process of persistence is mostly accomplished through various relational databases .
  • The main role of persistence is Store the data in memory in a relational database , Of course, it can also be stored in disk files 、XML In the data file .
  • common sense : If the data is saved in memory , Computers store binary data , Distinguish between low voltage and high voltage 0 and 1, Once the power is There is no voltage, memory can not save data , Therefore, the data should be persisted in the media .

 Insert picture description here

Examples in life :

 Insert picture description here

2. Database and database management system

2.1 Database related concepts

DB: database (Database)
That is, where data is stored “ Warehouse ”, Its essence is a file system . It holds a series of organized data .
DBMS: Database management system (Database Management System)
It is a kind of large-scale software for manipulating and managing database , For building 、 Use and maintain database , Manage and control the database in a unified way . The user accesses the data in the table in the database through the database management system .
SQL: Structured query language (Structured Query Language)
A language designed to communicate with a database .

2.2 The relationship between database and database management system

Database management system (DBMS) You can manage multiple databases , Generally, developers will create a database for each application . To save the data of the entity in the application , In general, multiple tables will be created in the database , To save the data of entity users in the program .

Database management system 、 The relationship between database and table is shown in the figure :

[ Failed to transfer the external chain picture , The origin station may have anti-theft chain mechanism , It is suggested to save the pictures and upload them directly (img-3fOqK5Jz-1639117826004)(images/image-20211013202511233.png)]

[ Failed to transfer the external chain picture , The origin station may have anti-theft chain mechanism , It is suggested to save the pictures and upload them directly (img-WholbrmQ-1639117826005)(images/image-20210915112546261.png)]

2.3 Ranking of common database management systems (DBMS)

At present, the common database management software on the Internet is Oracle、MySQL、MS SQL Server、DB2、PostgreSQL、Access、Sybase、Informix These kinds of . Here are 2021 year DB-Engines Ranking The statistical results of the survey on the popularity of each database :( Check the latest ranking of the database :https://db-engines.com/en/ranking)

 Insert picture description here

...

 Insert picture description here

Corresponding trend chart :(https://db-engines.com/en/ranking_trend)

[ Failed to transfer the external chain picture , The origin station may have anti-theft chain mechanism , It is suggested to save the pictures and upload them directly (img-8nPk7U54-1639117826007)(images/image-20211013203029492.png)]

2.4 Introduction to common databases

Oracle

1979 year ,Oracle 2 Be born , It was the first commercial RDBMS( Relational database management system ). With Oracle Software is becoming more and more famous , The company also changed its name to Oracle company .

2007 year , A total of 85 US $100 million acquisition BEA Systems.

2009 year , A total of 74 US $100 million acquisition SUN. Previous 2008 year ,SUN With 10 US $100 million acquisition MySQL. signify Oracle At the same time, I have MySQL The management of , thus Oracle Become an absolute leader in the field of database .

2013 year , Oracle goes beyond IBM, To become a successor Microsoft The second largest software company in the world .

Now Oracle Our annual income has reached 400 Billion dollars , Enough to prove that ( charge ) The value of database software .

SQL Server

SQL Server It is a large commercial database developed by Microsoft , Born in 1989 year .C#、.net Such language is often used , And WinNT Fully integrated , It can also work well with Microsoft BackOffice Product integration .

DB2

IBM The company's database products , Rechargeable . Often used in banking systems .

PostgreSQL

PostgreSQL Is extremely stable , Most in line with SQL standard , Open source , Commercial grade DBMS quality .PG Large amount of text data SQL Faster processing .

SyBase

Has faded out of the stage of history . Provides a very professional data modeling tool PowerDesigner.

SQLite

Embedded small database , It's used on the mobile phone . Zero configuration ,SQlite3 No need to install , Don't need to configure , No need to start , Close or configure the database instance . When the system crashes, there is no need to do any recovery operation , Automatically recover the next time you use the database .

informix

IBM Company produce , Taken from the Information and Unix The combination of , It was the first to be transplanted to Linux Commercial database products on the Internet . Only run on unix/linux platform , Command line operations . Higher performance , Support clusters , Suitable for systems with high security requirements , Especially banks , Application of securities system .

3. MySQL Introduce

 Insert picture description here

3.1 summary

  • MySQL It's a Open source relational database management system , The Swedish MySQL AB( founder Michael Widenius) company 1995 Annual development , Quickly become the of open source database No.1.
  • 2008 By Sun Acquisition (10 Billion dollars ),2009 year Sun By Oracle Acquisition .MariaDB emerge as the times require .(MySQL The creators of are worried about MySQL There is a risk of closure , So I created MySQL Branch project of MariaDB)
  • MySQL6.x The version is divided into Community Edition and Business Edition .
  • MySQL It's an associated database management system , Save the data in different tables , Instead of putting all the data in one big warehouse , This increases speed and flexibility .
  • MySQL It's open source. , So you don't have to pay extra .
  • MySQL It can be customized , Adopted GPL(GNU General Public License) agreement , You can modify the source code to develop your own MySQL System .
  • MySQL Support large databases . Can handle large databases with tens of millions of records .
  • MySQL Support for large databases , Support 5000 Data warehouse of ten thousand records ,32 Bit system table files can support 4GB,64 The largest table file supported by bit system is 8TB.
  • MySQL Use The standard SQL Data language form .
  • MySQL It can be allowed to run on multiple systems , And support multiple languages . These programming languages include C、C++、Python、Java、Perl、PHP and Ruby etc. .

3.2 MySQL Major events in the history of development

MySQL The history of is the development history of the whole Internet . Internet business from the social field 、 The development from e-commerce to finance , Promote the application's demand for database , It challenges the traditional database service ability . High concurrency 、 High performance 、 High availability 、 Light resources 、 Easy maintenance 、 Scalable requirements , Promote MySQL The rapid development of .

[ Failed to transfer the external chain picture , The origin station may have anti-theft chain mechanism , It is suggested to save the pictures and upload them directly (img-YC4YhnFF-1639117826009)(images/image-20210730161043856.png)]

1.4 About MySQL 8.0

MySQL from 5.7 The version was released directly 8.0 edition , It can be seen that this is an exciting milestone version .MySQL 8 The version has made significant improvements and enhancements in function , Developer pair MySQL The source code is reconstructed , The most prominent point is more MySQL Optimizer The optimizer has been improved . Not only has it improved in speed , It also brings users better performance and better experience .

1.5 Why choose MySQL?

[ Failed to transfer the external chain picture , The origin station may have anti-theft chain mechanism , It is suggested to save the pictures and upload them directly (img-BpVTDwW8-1639117826010)(images/image-20211013210429011.png)]

Why do so many manufacturers choose MySQL? The main reasons for the summary are as follows :

  1. Open source , Low use cost .

  2. Performance is remarkable , Stable service .

  3. The software is small , Easy to use , And easy to maintain .

  4. Has a long history , Community users are very active , Ask for help when you have problems .

  5. Many Internet companies are using , After the verification of time .

1.6 Oracle vs MySQL

Oracle It is more suitable for large multinational enterprises , Because they are not cost sensitive , But there are higher requirements for performance and safety .

MySQL Because of its Small volume 、 Fast 、 Low total cost of ownership , A large database that can handle tens of millions of records , Especially open source , Makes many Internet companies 、 Small and medium-sized websites choose MySQL As website database (Facebook,Twitter,YouTube, Alibaba / The ant gold dress , Where are you going? , Meituan takeout , tencent ).

4. RDBMS And Not RDBMS

We can see from the ranking , Relational databases are definitely DBMS The mainstream of , Among them, the most used DBMS Namely Oracle、MySQL and SQL Server. These are relational databases (RDBMS).

4.1 Relational database (RDBMS)

4.1.1 The essence

  • This type of database is Oldest Database type of , Relational database model is to reduce the complex data structure to simple Binary relationship ( In two-dimensional form ).

 Insert picture description here

  • Relational database to That's ok (row) and Column (column) In the form of data storage , To make it easy for users to understand . This series of rows and columns is called surface (table), A set of tables form a library (database).

  • The data records between tables are related (relationship). All kinds of entities in the real world and all kinds of connections between entities are used relational model To express . Relational database , It's built on relational model Based on the database .

  • SQL It is the query language of relational database .

 Insert picture description here

4.1.2 advantage

  • Complex queries
    It can be used SQL Statement is convenient to do very complex data query between one table and multiple tables .
  • Transaction support
    So that the data access requirements with high security performance can be realized .

4.2 Non relational database ( Not RDBMS)

4.2.1 Introduce

Non relational database , It can be regarded as the function of traditional relational database Castration version , Store data based on key value pairs , No need to go through SQL Layer resolution , Very high performance . meanwhile , By reducing infrequent functions , Further improve performance .

At present, most mainstream non relational databases are free .

4.2.2 What non relational databases are there

Compared with SQL,NoSQL Non relational database in general , Including the key value database on the list 、 Document database 、 Search engine and column storage , In addition, it also includes the graphics database . It's only useful NoSQL The word can include these technologies .

Key database

Key value database through Key-Value Key value to store data , among Key and Value It can be a simple object , It can also be a complex object .Key As a unique identifier , The advantage is that the search speed is fast , In this respect, it is obviously better than relational database , The disadvantage is that you can't use conditional filtering like a relational database ( such as WHERE), If you don't know where to find the data , You have to traverse all the keys , This will consume a lot of computation .

The typical usage scenario of a key value database is as Memory cache .Redis Is the most popular key value database .

 Insert picture description here

Document database

This kind of database can store and obtain documents , It can be XML、JSON Equiform . In a database, a document is the basic unit for processing information , A document is a record . Documents stored in the document database , It is equivalent to that stored in the key value database “ value ”.MongoDB Is the most popular document database . Besides , also CouchDB etc. .

Search engine database

Although relational database adopts index to improve retrieval efficiency , However, the efficiency of full-text indexing is low . Search engine database is a data storage form applied in the field of search engine , Because search engines crawl a lot of data , And stored in a specific format , In this way, the best performance can be guaranteed when searching . The core principle is “ Inverted index ”.

Typical products :Solr、Elasticsearch、Splunk etc. .

Columnar database

A column database is a database relative to a row store ,Oracle、MySQL、SQL Server And so on the database all uses the line type storage (Row-based), The columnar database is to store data into the database according to columns , The advantage of this is that it can reduce the system greatly I/O, Suitable for distributed file systems , The disadvantage is that the function is relatively limited . Typical products :HBase etc. .

[ Failed to transfer the external chain picture , The origin station may have anti-theft chain mechanism , It is suggested to save the pictures and upload them directly (img-zESwcCti-1639117826013)(images/image-20211020173921726.png)]

Graphic database

Graphic database , The data structure of graph is used to store entities ( object ) The relationship between . The most typical example of graphical database is the relationship between people in social networks , The data model is mainly based on nodes and edges ( Relationship ) To achieve , It is characterized by the ability to efficiently solve complex relationship problems .

Graphic database as the name suggests , It is a database that stores graphical relationships . It uses the data structure of graph to store entities ( object ) The relationship between . Relational data is used to store data with explicit relationships , But for the data storage of complex relationships, it is not enough . Such as the relationship between characters in social networks , If you use a relational database, it is very complex , Using a graphics database will be very simple . Typical products **:Neo4J、InfoGrid etc. .**

 Insert picture description here

4.2.3 NoSQL( Also called non relational database ) The evolution of

Just a name to distinguish the type of database :sql Type database ( Relational database )、NoSql Type database ( Non relational database ).

because SQL Always dominate DBMS, So many people are thinking about whether there is a database technology that can stay away from SQL, therefore NoSQL The birth of , But with the development, it is found that it is more and more inseparable from SQL. up to now NoSQL In the camp DBMS There will be implementations similar to SQL The function of . Here is “NoSQL” The interpretation of this noun in different periods , From the changes of these definitions, we can see NoSQL The evolution of function

1970:NoSQL = We have no SQL

1980:NoSQL = Know SQL

2000:NoSQL = No SQL!

2005:NoSQL = Not only SQL

2013:NoSQL = No, SQL!

NoSQL Yes SQL Made a good supplement , For example, in actual development , There are many business needs , In fact, you don't need a complete relational database function , The function of non relational database is enough . In this case , Use Higher performance A lower cost Of course, a non relational database is a wiser choice . such as : Log collection 、 Ranking List 、 Timers, etc .

4.3 Summary

NoSQL There are many categories of , even so , stay DBMS In the rankings , still SQL A larger proportion of the camp , Before influence 5 Of DBMS There is 4 One is a relational database , Top 20 Of DBMS There are also 12 One is a relational database . So , master SQL It's very necessary . The whole course will revolve around SQL an .

5. Relational database design rules

  • The typical data structure of relational database is Data sheet , The composition of these data tables is structured (Structured).

  • Put the data in the table , Put the watch in the library .

  • There can be multiple tables in a database , Every watch has a name , To identify yourself . Table names are unique .

  • Table has some characteristics , These properties define how data is stored in tables , similar Java and Python in “ class ” The design of the .

5.1 surface 、 Record 、 Field

  • E-R(entity-relationship, Entity - contact ) There are three main concepts in the model : Entity set attribute Contact set .

  • A set of entities (class) Corresponding to a table in the database (table), An entity (instance) It corresponds to a row in the database table (row), Also known as a record (record). An attribute (attribute) Corresponds to a column in the database table (column), Also known as a field (field).

 Insert picture description here

ORM thought (Object Relational Mapping) reflect :
A table in the database <---> Java or Python A class in
A piece of data in the table <---> An object in a class ( Or entity )
A column in the table <----> A field in a class 、 attribute (field)

5.2 The relationship between tables

  • The data records between tables are related (relationship). All kinds of entities in the real world and the relationships between them are represented by relational models .

  • Four kinds of : One to one connection 、 One to many connection 、 Many to many connection 、 Self quoting

5.2.1 One to one connection (one-to-one)

  • There are not many applications in the actual development , Because one-to-one can be created as a table .
  • give an example : Design Student list : Student number 、 full name 、 Phone number 、 class 、 Is don't 、 Id card number 、 Home address 、 Native place 、 Emergency contact 、…
    • Split into two tables : The records of the two tables are one-to-one correspondence .

    • Basic information table ( Frequently used information ): Student number 、 full name 、 Phone number 、 class 、 Is don't

    • File information table ( Infrequent information ): Student number 、 Id card number 、 Home address 、 Native place 、 Emergency contact 、…

  • Two principles of table building :
    • The foreign key is unique : Primary key of master table and foreign key of slave table ( only ), Form the main foreign bond relationship , The foreign key is unique .
    • The foreign key is the primary key : Primary key of master table and primary key of slave table , Form the main foreign bond relationship .

 Insert picture description here

5.2.2 One-to-many relation (one-to-many)

  • Common instance scenarios : Customer and order forms , Classification table and commodity table , Department tables and staff tables .
  • give an example :
    • The employee table : Number 、 full name 、…、 Department

    • Departmental table : Number 、 name 、 brief introduction

  • One to many tabulation principle : From table ( In many ways ) Create a field , The field points to the main table as a foreign key ( a party ) Primary key of
     Insert picture description here

[ Failed to transfer the external chain picture , The origin station may have anti-theft chain mechanism , It is suggested to save the pictures and upload them directly (img-0WzvPnaq-1639117826015)(images/image-20210915001013524.png)]
 Insert picture description here

5.2.3 Many to many (many-to-many)

To represent a many to many relationship , You must create a third table , This table is commonly referred to as Connection table , It divides many to many relationships into two one to many relationships . Insert the primary keys of both tables into the third table .

 Insert picture description here

  • give an example 1: Student - Course

    • Student information sheet : One line represents a student's information ( Student number 、 full name 、 Phone number 、 class 、 Is don't …)

    • Course information sheet : One line represents the information of a course ( Course number 、 Teaching teacher 、 brief introduction …)

    • Course selection information table : A student can take multiple courses , A course can be chosen by more than one student

       Student number Course number
      1 1001
      2 1001
      1 1002
      
  • give an example 2: product - Order

    “ Order ” Table and “ product ” Tables have a many to many relationship , This relationship is achieved through a relationship with “ The order details ” A table is defined by establishing two one to many relationships . One order can have multiple products , Each product can appear in multiple orders .

    • Product list :“ product ” Each record in the table represents a product .
    • The order sheet :“ Order ” Each record in the table represents an order .
    • Order details : Each product can be combined with “ Order ” Multiple records in the table correspond to , That is, it appears in multiple orders . An order can be made with “ product ” Multiple records in the table correspond to , That is, it contains multiple products .

 Insert picture description here

  • give an example 3: user - role
  • Many to many relationship table building principle : You need to create a third table , At least two fields in the middle table , These two fields are used as foreign keys to each other's primary keys .

 Insert picture description here

5.3.4 Self quoting (Self reference)

 Insert picture description here

The first 02 Chapter _MySQL Environment building

lecturer : Silicon Valley Song Hongkang ( Jianghu people : a brand of instant noodles )

Official website :http://www.atguigu.com


1. MySQL The uninstall

step 1: stop it MySQL service

Before uninstalling , First stop MySQL8.0 Service for . Press on the keyboard “Ctrl + Alt + Delete” Composite key , open “ Task manager ” Dialog box , Can be in “ service ” List found “MySQL8.0” Service for , If now “ Running ” state , You can right-click Services , choice “ stop it ” Option stop MySQL8.0 Service for , As shown in the figure .

[ Failed to transfer the external chain picture , The origin station may have anti-theft chain mechanism , It is suggested to save the pictures and upload them directly (img-IRsMH8A6-1639117842547)(images/image-20211014153604802.png)]

step 2: Software uninstall

The way 1: Via control panel

uninstall MySQL8.0 Your application can be directly in the same way as other desktop applications “ Control panel ” choice “ Uninstall program ”, And find... In the program list MySQL8.0 Server program , Double click uninstall directly , As shown in the figure . Delete in this way , The data in the data directory will not be deleted .

[ Failed to transfer the external chain picture , The origin station may have anti-theft chain mechanism , It is suggested to save the pictures and upload them directly (img-XFLxdsuT-1639117842548)(images/image-20211014153657668.png)]

The way 2: adopt 360 Or software such as computer manager

A little

The way 3: Uninstall through the uninstall function provided by the installation package

You can also use the installation wizard to MySQL8.0 Uninstall the server program .

① Double click the downloaded... Again mysql-installer-community-8.0.26.0.msi file , Open the setup wizard . The setup wizard will automatically detect installed MySQL Server program .

② Select the to uninstall MySQL Server program , single click “Remove”( remove ), You can uninstall .

[ Failed to transfer the external chain picture , The origin station may have anti-theft chain mechanism , It is suggested to save the pictures and upload them directly (img-YZm1dtPe-1639117842549)(images/image-20211014153722683.png)]

③ single click “Next”( next step ) Button , Confirm uninstall .

[ Failed to transfer the external chain picture , The origin station may have anti-theft chain mechanism , It is suggested to save the pictures and upload them directly (img-fhtgb3f4-1639117842549)(images/image-20211014153747283.png)]

④ A window will pop up to select whether to remove the data directory at the same time . If you want to delete MySQL Data in the server , Then check “Remove the data directory”, As shown in the figure .

⑤ Perform the uninstall . single click “Execute”( perform ) Button to unload .

⑥ Uninstall complete . single click “Finish”( complete ) button . If you want to uninstall at the same time MySQL8.0 Installation wizard for , Check “Yes,Uninstall MySQL Installer” that will do , As shown in the figure .

step 3: Cleaning of residual files

** explain :** Removed the software , But these data will not be deleted , For example, unload the camera of the mobile phone, but these photos won't disappear .

If the installation is not successful again , You can uninstall and clean up the residual files before installing .

(1) Service catalog :mysql The installation directory of the service

(2) Data directory : Default in C:\ProgramData\MySQL

If you have specified the data directory separately , Just find your own data directory and delete it .

Be careful : Please make data backup before unloading

After the operation , Need to restart the computer , Then install it . If the installation still fails , You need to continue with the following steps 4.

step 4: Clean up the registry ( Choose to do )

If you did the previous steps , The installation failed again , Then you can clean the registry .

How to open the registry editor : Enter... In the search box of the system regedit

HKEY_LOCAL_MACHINE\SYSTEM\ControlSet001\Services\Eventlog\Application\MySQL service Directory delete
HKEY_LOCAL_MACHINE\SYSTEM\ControlSet001\Services\MySQL service Directory delete
HKEY_LOCAL_MACHINE\SYSTEM\ControlSet002\Services\Eventlog\Application\MySQL service Directory delete
HKEY_LOCAL_MACHINE\SYSTEM\ControlSet002\Services\MySQL service Directory delete
HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\Eventlog\Application\MySQL Service directory delete
HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\MySQL Service deletion

In the registry ControlSet001,ControlSet002, Is not necessarily 001 and 002, May be ControlSet005、006 And so on

step 5: Delete the environment variable configuration

find path environment variable , Put it all about mysql Delete the environment variable of , Remember not to delete all .

for example : Delete D:\develop_tools\mysql\MySQLServer8.0.26\bin; This part

[ Failed to transfer the external chain picture , The origin station may have anti-theft chain mechanism , It is suggested to save the pictures and upload them directly (img-Jk0xLV9w-1639117842550)(images/1575694476072.png)]

2. MySQL The download 、 install 、 To configure

2.1 MySQL Of 4 The big version

  • MySQL Community Server Community version , Free open source , Free download , But no official technical support , Suitable for most ordinary users .

  • MySQL Enterprise Edition Enterprise version , Pay for , Can't download online , You can try 30 God . It provides more functions and more complete technical support , It is more suitable for enterprise customers with high requirements for database function and reliability .

  • MySQL Cluster Cluster version , Free open source . It is used to set up cluster servers , You can put a few MySQL Server Encapsulate into a Server. It needs to be used on the basis of community version or enterprise version .

  • MySQL Cluster CGE Advanced clustering , Pay for .

  • The latest version is 8.0.27, Release time 2021 year 10 month . before ,8.0.0 stay 2016.9.12 It's released today .

  • Used in this course 8.0.26 edition .

Besides , Officials also provide MySQL Workbench(GUITOOL) One for MySQL The design of the Graphical interface management tools .MySQLWorkbench It's divided into two versions , Namely Community Edition (MySQL Workbench OSS)、 Commercial version (MySQL WorkbenchSE).

2.2 Software download

1. Download address

Official website :https://www.mysql.com

2. Open the website , Click on DOWNLOADS

then , Click on MySQL Community(GPL) Downloads

[ Failed to transfer the external chain picture , The origin station may have anti-theft chain mechanism , It is suggested to save the pictures and upload them directly (img-8AtQmBP2-1639117842551)(images/image-20210817185920150.png)]

3. Click on MySQL Community Server

4. stay General Availability(GA) Releases Select the appropriate version

Windows Two installation files are provided under the platform :MySQL Binary distribution (.msi The installation files ) And installation free version (.zip Compressed files ). In general , Binary distributions should be used , Because this version provides a graphical installation wizard process , Easier to use than other distributions , You don't need other tools to start to run MySQL.

  • Here it is Windows It is recommended to download MSI Erection sequence ; Click on Go to Download Page Download it

[ Failed to transfer the external chain picture , The origin station may have anti-theft chain mechanism , It is suggested to save the pictures and upload them directly (img-cAuJeT58-1639117842551)(images/image-20211014163001964.png)]

  • Windows Under the MySQL8.0 There are two installers installed
    • mysql-installer-web-community-8.0.26.0.msi Download program size :2.4M; Networking installation components are required during installation .
    • mysql-installer-community-8.0.26.0.msi Download program size :450.7M; You can install offline during installation . recommend .
  • If installed MySQL5.7 Version! , choice Archives, Then choose MySQL5.7 The corresponding version of . Download the latest MySQL5.7.34 edition .

[ Failed to transfer the external chain picture , The origin station may have anti-theft chain mechanism , It is suggested to save the pictures and upload them directly (img-lp0HlgEG-1639117842552)(images/image-20211014163228051.png)]

[ Failed to transfer the external chain picture , The origin station may have anti-theft chain mechanism , It is suggested to save the pictures and upload them directly (img-An8XX3aY-1639117842553)(images/image-20211014163353156.png)]

2.3 MySQL8.0 Version installation

Be careful 1: A computer can be equipped with many different versions of mysql, Just make sure that the port number of the installed process is different .

Be careful 2: Do not after installation ctrl+x Clip and move Directory , Some column errors will be caused during login operation .

Be careful 3: If boot is set , When the computer starts, it still can't start successfully , You need to check whether the installation path has Chinese , Space and other issues .

MySQL When the download is complete , Find the download file , Double click to install , The specific operation steps are as follows .

step 1: Double-click the downloaded mysql-installer-community-8.0.26.0.msi file , Open the setup wizard .

step 2: open “Choosing a Setup Type”( Choose the installation type ) window , It lists 5 Installation types , Namely Developer Default( Default installation type )、Server only( Just as a server )、Client only( Just as a client )、Full( Completely installed )、Custom( Custom installation ). Choose here “Custom( Custom installation )” Type button , single click “Next( next step )” Button .

step 3: open “Select Products” ( Choose products ) window , You can customize the list of products to be installed . for example , choice “MySQL Server 8.0.26-X64” after , single click “→” Add a button , You can choose to install MySQL The server , As shown in the figure . Adopt a general method , You can add other products you need to install .

At this point, if you directly “Next”( next step ), The installation path of the product is the default . If you want to customize the installation directory , You can select the corresponding product , Then it will appear below “Advanced Options”( Advanced options ) Hyperlinks for .

single click “Advanced Options”( Advanced options ) The installation directory selection window will pop up , As shown in the figure , At this point, you can set it separately MySQL Service program installation directory and data storage directory . If not set , By default, they are in C Discoid Program Files Contents and ProgramData Catalog ( This is a hidden Directory ). If you customize the installation directory , Please avoid “ chinese ” Catalog . in addition , It is recommended that the service directory and data directory be stored separately .

step 4: After selecting the product to install in the previous step , single click “Next”( next step ) Enter the confirmation window , As shown in the figure . single click “Execute”( perform ) Button to start installation .

step 5: After the installation is completed, please wait “Status”( state ) Below the list will be displayed “Complete”( installation is complete ), As shown in the figure .

2.4 To configure MySQL8.0

MySQL After installation , You need to configure the server . The specific configuration steps are as follows .

step 1: In the last step of the previous section , single click “Next”( next step ) Button , You can enter the product configuration window .

step 2: single click “Next”( next step ) Button , Get into MySQL Server type configuration window , As shown in the figure . Generally, the default port number is selected 3306.

among ,“Config Type” Option is used to set the type of server . Click the bottom triangle button to the right of the option , You can view it 3 An option , As shown in the figure .

  • Development Machine( Developing machines ): This option represents a typical personal desktop workstation . At this point, multiple applications need to run on the machine , that MySQL The server will use the least system resources .

  • Server Machine( The server ): This option represents the server ,MySQL The server can run with other server applications , for example Web The server etc. .MySQL The server is configured with an appropriate proportion of system resources .

  • Dedicated Machine( Dedicated server ): This option represents run only MySQL Servers served .MySQL The server is configured to use all available system resources .

step 3: single click “Next”( next step ) Button , Open the set authorization mode window . among , The options above are MySQL8.0 New ways of Authorization , use SHA256 Basic cryptographic methods ; The following options are traditional authorization methods ( Retain 5.x Version compatibility ).

step 4: single click “Next”( next step ) Button , Open setup server root Super administrator password window , As shown in the figure , You need to enter the same login password twice . It can also be done through “Add User” Add other users , When adding other users , You need to specify a user name 、 The user name is allowed on which computer / Which hosts log in , You can also specify user roles, etc . Users will not be added here , User management in MySQL Advanced features .

step 5: single click “Next”( next step ) Button , Open the set server name window , As shown in the figure . The service name will appear in Windows In the list of services , You can also use the service name in the command line window to start and stop the service . This book sets the service name to “MySQL80”. If you want to start the self starting service , You can also check “Start the MySQL Server at System Startup” Options ( recommend ).

Here is how to run the service ? You can choose “Standard System Account”( Standard system users ) perhaps “Custom User”( Custom user ) One of them . The former is recommended here .

Be careful : If boot is set , When the computer starts, it still can't start successfully , You need to check whether the installation path has Chinese , Space and other issues .

step 6: single click “Next”( next step ) Button , Open the confirm settings server window , single click “Execute”( perform ) Button .

step 7: Complete the configuration , As shown in the figure . single click “Finish”( complete ) Button , The configuration of the server can be completed .

step 8: If there are other products that need to be configured , You can choose other products , Then continue to configure . without , Direct selection “Next”( next step ), Directly complete the whole installation and configuration process .

step 9: End installation and configuration .

2.5 To configure MySQL8.0 environment variable

If you don't configure MySQL environment variable , You can't type... Directly on the command line MySQL Login command . Here's how to configure MySQL Environment variables of :

step 1: Right click on the desktop 【 This computer 】 Icon , Choose... From the shortcut menu that pops up 【 attribute 】 The menu command .
step 2: open 【 System 】 window , single click 【 Advanced system setup 】 link .
step 3: open 【 System attribute 】 Dialog box , choice 【 senior 】 tab , And then click 【 environment variable 】 Button .
step 4: open 【 environment variable 】 Dialog box , Select... In the system variable list path Variable .
step 5: single click 【 edit 】 Button , stay 【 Edit environment variables 】 In the dialog box , take MySQL Application's bin Catalog (C:\Program Files\MySQL\MySQL Server 8.0\bin) Add to variable value , Separate it from other paths with semicolons .
step 6: Add after completion , single click 【 determine 】 Button , This completes the configuration path Variable operation , Then you can directly enter MySQL Command to log in to the database .

2.6 MySQL5.7 Version installation 、 To configure

  • install

The installation process of this version is the same as the above process, except that the version number is different , Other links are the same . So we omit MySQL5.7.34 Screenshot of version installation .

  • To configure

Configuration phase and MySQL8.0 The version is slightly different . In most cases, choose directly “Next” that will do , It does not affect the finishing and use .

Configuration here MySQL5.7 when , Focus on : With the previously installed MySQL8.0 You cannot use the same port number .

2.7 Installation failure problem

MySQL The installation and configuration of is a very simple thing , But there may also be problems during operation , Especially beginners .

problem 1: Unable to open MySQL8.0 Software installation package or failure during installation , How to solve ?

Running MySQL8.0 Before installing the package , Users need to ensure that... Is installed in the system .Net Framework Related software , If this software is missing , Will not install properly MySQL8.0 Software .

Solution : To this address https://www.microsoft.com/en-us/download/details.aspx?id=42642 download Microsoft .NET Framework 4.5 And after installation , Then go install MySQL.

in addition , Also make sure Windows Installer normal setup .windows Installation on mysql8.0 The operating system needs to be installed in advance Microsoft Visual C++ 2015-2019.
 Insert picture description here

 Insert picture description here

The solution is also , Advance to Microsoft's official website https://support.microsoft.com/en-us/topic/the-latest-supported-visual-c-downloads-2647da03-1eea-4433-9aff-95f26a218cc0, Download the corresponding environment .

problem 2: Unload and reload MySQL Failure ?

The problem is usually due to MySQL When uninstalling , Not completely clear the relevant information .

The solution is , Delete the previous installation directory . If the service installation directory has not been specified separately in the previous installation , The default installation directory is “C:\Program Files\MySQL”, Completely delete the directory . At the same time to delete MySQL Of Data Catalog , If the data directory has not been specified separately in the previous installation , The default installation directory is “C:\ProgramData\MySQL”, This directory is generally hidden . After deleting , Just reinstall .

problem 3: How to be in Windows The system deletes the previously uninstalled clean MySQL List of services ?

The operation method is as follows , In system “ Search box ” Input in “cmd”, Press “Enter”( enter ) Key confirmation , The command prompt interface pops up . Then input “sc delete MySQL service name ”, Press “Enter”( enter ) key , You can completely delete the remaining MySQL Yes .

3. MySQL The login

3.1 Start and stop of service

MySQL After installation , The server process needs to be started , Otherwise, the client cannot connect to the database .

In the previous configuration process , Have already put MySQL Installed as Windows service , And check when Windows start-up 、 When it stops ,MySQL It also starts automatically 、 stop it .

The way 1: Use GUI tools

  • step 1: open windows service

    • The way 1: Computer ( Right click )→ management ( Click on )→ Services and Applications ( Click on )→ service ( Click on )
    • The way 2: Control panel ( Click on )→ Systems and security ( Click on )→ Management tools ( Click on )→ service ( Click on )
    • The way 3: The task bar ( Right click )→ Start task manager ( Click on )→ service ( Click on )
    • The way 4: single click 【 Start 】 menu , Type in the search box “services.msc”, Press Enter Key confirmation
  • step 2: find MySQL80( Right click )→ To start or stop ( Click on )

[ Failed to transfer the external chain picture , The origin station may have anti-theft chain mechanism , It is suggested to save the pictures and upload them directly (img-JJVDczKs-1639117842554)(images/image-20211014183908375.png)]

The way 2: Use command line tools

# start-up MySQL The service command :
net start MySQL service name
# stop it MySQL The service command :
net stop MySQL service name

explain :

  1. start and stop The following service name should be consistent with the service name specified during previous configuration .

  2. If when you enter a command , Tips “ Denial of service ”, Please use System administrator Open the command prompt interface and try again .

3.2 Login and exit of the built-in client ( The premise is to start )

When MySQL After service startup , You can log in through the client MySQL database . Be careful : Confirm that the service is on .

Login mode 1:MySQL It comes with its own client

The start menu → All the procedures → MySQL → MySQL 8.0 Command Line Client

explain : Is limited to root user

Login mode 2:windows Command line

  • Format :
mysql -h Host name -P Port number -u user name -p password
  • give an example :
mysql -h localhost -P 3306 -u root -pabc123 # Here I set root The user's password is abc123

Be careful :

(1)-p There must be no space between and password , There may or may not be spaces between other parameter names and parameter values . Such as :

mysql -hlocalhost -P3306 -uroot -pabc123

(2) It is recommended to enter the password on the next line , Keep it safe

mysql -h localhost -P 3306 -u root -p
Enter password:****

(3) The client and server are on the same machine , So the input localhost perhaps IP Address 127.0.0.1. meanwhile , Because it is connected to this machine
-hlocalhost You can omit , If the port number has not been modified :-P3306 You can omit it . In other words, if other computers are connected, it will not be omitted .

Abbreviation :

mysql -u root -p
Enter password:****

After successful connection , About MySQL Server Service version information , And the number of connections id identification .

You can also get... On the command line in the following ways MySQL Server Service version information :

c:\> mysql -V
c:\> mysql --version

or Sign in after , View the current version information in the following ways :

mysql> select version();

Log out

exit
or
quit

4. MySQL Demonstrate the use of

4.1 MySQL Use demonstration of

1、 View all databases

show databases;

“information_schema” yes MySQL The database of the system , The main preservation is MySQL System information of database server , For example, the name of the database 、 The name of the data table 、 Field name 、 Access rights 、 Data files The folder and the folder used by the system , wait

“performance_schema” yes MySQL The database of the system , Can be used to monitor MySQL Various performance indexes .

“sys” The database is MySQL The database of the system , The main function is to show... In a more understandable way MySQL Various performance indexes of database server , Help system administrators and developers monitor MySQL Technical performance .

“mysql” The database holds MySQL System information required by the database server to run , Such as data folders 、 Current character set 、 Constraint check information , wait

Why? Workbench We can only see “demo” and “sys” this 2 How about a database ?

This is because ,Workbench It is a graphical management tool , Mainly for developers member ,“demo” and “sys” this 2 A database is enough . If there are special needs , such as , Need to monitor MySQL Database performance indicators 、 Direct manipulation MySQL Database system files, etc , Can be DBA adopt SQL sentence , Check other system databases .

2、 Create your own database

create database Database name ;
# establish atguigudb database , The name cannot be the same as an existing database .
create database atguigudb;

3、 Use your own database

use Database name ;
# Use atguigudb database 
use atguigudb;

explain : If not used use sentence , The following operations on the database do not add “ Data name ” The limit of , Then it will be reported. “ERROR 1046 (3D000): No database selected”( No database selected )

Use up use After statement , If the next SQL They are all aimed at a database operation , Then don't repeat use 了 , If you want to operate on another database , Then we have to re use.

4、 View all tables in a library

show tables; # It is required to have use sentence 
show tables from Database name ;

5、 Create a new table

create table The name of the table (
Field name data type ,
Field name data type
);

explain : If it is the last field , Then add a comma , Because the comma is used to separate each field .

# Create student table 
create table student(
id int,
name varchar(20) # Say names no longer than 20 Characters 
);

6、 View the data of a table

select * from Database table name ;
# View the data of the student table 
select * from student;

7、 Add a record

insert into The name of the table values( List of values );
# Add two records to student In the table 
insert into student values(1,' Zhang San ');
insert into student values(2,' Li Si ');

Report errors :

mysql> insert into student values(1,' Zhang San ');
ERROR 1366 (HY000): Incorrect string value: '\xD5\xC5\xC8\xFD' for column 'name' at row 1
mysql> insert into student values(2,' Li Si ');
ERROR 1366 (HY000): Incorrect string value: '\xC0\xEE\xCB\xC4' for column 'name' at row 1
mysql> show create table student;

The problem with character sets .

8、 View the table creation information

show create table The name of the table \G
# see student Table creation details 
show create table student\G
# give the result as follows 
*************************** 1. row ***************************
Table: student
Create Table: CREATE TABLE `student` (
`id` int(11) DEFAULT NULL,
`name` varchar(20) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

The results above show student The default character set of the table is “latin1” No Chinese support .

9、 View database creation information

show create database Database name \G
# see atguigudb Database creation details 
show create database atguigudb\G
# give the result as follows 
*************************** 1. row ***************************
Database: atguigudb
Create Database: CREATE DATABASE `atguigudb` /*!40100 DEFAULT CHARACTER SET latin1 */
1 row in set (0.00 sec)

The results above show atguigudb The database does not support Chinese , The character set defaults to latin1.

10、 Delete table

drop table The name of the table ;
# Delete student table 
drop table student;

11、 Delete database

drop database Database name ;
# Delete atguigudb database 
drop database atguigudb;

4.2 MySQL The encoding settings for

MySQL5.7 in

Be careful :

1. If you create a database , Create table time , If the code of the specified library and table is not displayed, the created library , The table is consistent with the database by default .

2. because mysql stay 8.0 The code set by default before is not utf-8 Encoding of type ,mysql It was invented by Sweden in Europe. By default, it uses the European Code latin1 No Chinese , If you want to support Chinese, you need to modify the code manually .mysql8.0 The default encoding is utf-8, So there's no need to modify .

3. If you want to change the original database into utf-8, Solve the problem at the bottom 5 Inside , Modify the code for creating the database .

4. If in Dos There is also a garbled code problem in the window , I need to set to "SET NAMES GBK";

Problem reproduction : Command line operations sql The code problem

mysql> INSERT INTO t_stu VALUES(1,' Zhang San ',' male ');
ERROR 1366 (HY000): Incorrect string value: '\xD5\xC5\xC8\xFD' for column 'sname' at row 1

Problem solving

step 1: Check the code command ( Premise is : Log on first mysql5.7 The client of

show variables like 'character_%';
show variables like 'collation_%';

step 2: modify mysql Under the data directory of my.ini The configuration file

[mysql] # In about 63 Row or so , Add under 
...
default-character-set=utf8 # Default character set 
[mysqld] # In about 76 Row or so , Add under 
...
character-set-server=utf8
collation-server=utf8_general_ci

Be careful : It is recommended to modify the configuration file to use notepad++ And other advanced text editors , After opening and modifying with software such as Notepad, the file code may be changed to “ contain BOM head ” The coding , Thus, the service restart fails .

step 3: Restart the service

step 4: Check the code command

show variables like 'character_%';
show variables like 'collation_%';

 Insert picture description here
 Insert picture description here

  • If the above configuration is correct . Then we can Create a new database 、 Create a new data table , Then add the data containing Chinese .
  • explain : The database code created before is European code and Latin code , Modify the code. The original database code will not change , Want to use utf-8 You can only recreate the database .

MySQL8.0 in

stay MySQL 8.0 Before the release , The default character set is latin1,utf8 The character set points to utf8mb3. Website developers often change the code to utf8 Character set . If you forget to change the default code , There will be a problem of garbled code . from MySQL 8.0 Start , The default code of the database is changed to utf8mb4, Thus, the above garbled code problem is avoided .

5. MySQL Graphical management tools

MySQL Graphical management tools greatly facilitate the operation and management of the database , Common graphical management tools are :MySQL Workbench、phpMyAdmin、Navicat Preminum、MySQLDumper、SQLyog、dbeaver、MySQL ODBC Connector.

Tools 1. MySQL Workbench

MySQL Official graphical management tools MySQL Workbench Fully support MySQL 5.0 Version above .MySQL Workbench It is divided into community version and commercial version , The community edition is completely free , The commercial version is charged annually .

MySQL Workbench For the database administrator 、 Program developers and system planners provide visual design 、 model 、 And database management functions . It includes creating complex data modeling ER Model , Forward and reverse Database Engineering , It can also be used to perform tasks that usually take a lot of time 、 Document tasks that are difficult to change and manage .

Download address :http://dev.mysql.com/downloads/workbench/.

Use :

First , We click Windows Lower left corner “ Start ” Button , If you are Win10 System , You can see all the programs directly . next , find “MySQL”, It opens at , find “MySQL Workbench 8.0 CE”. Click on the open Workbench, As shown in the figure below :

[ Failed to transfer the external chain picture , The origin station may have anti-theft chain mechanism , It is suggested to save the pictures and upload them directly (img-YnYBrFnd-1639117842554)(images/image-20211007153522427.png)]

There is a local connection in the lower left corner , Click on , entry Root Password , Log in locally MySQL database server , As shown in the figure below : Insert picture description here
 Insert picture description here
 Insert picture description here
This is a graphical interface , Let me introduce you to this interface .

  • Above is the menu . At the top left is the navigation bar , Here we can see MySQL The data in the database server library , Include data sheets 、 View 、 Stored procedures and functions ; At the bottom left is the information bar , You can display the data selected above library 、 Information about objects such as data tables .

  • Above the middle is the work area , You can write here SQL sentence , Click the third run button on the left of the upper menu bar and press button , You can execute... In the workspace SQL Statement .

  • Below the middle is the output area , Used to display SQL Statement execution , Including when it started running 、 Running Content 、 Running output , And the length of time spent .

Okay , Let's use Workbench Actually create a database , And import a Excel Data files , To generate a data table . Data table is the carrier for storing data , With the data sheet , We can manipulate the data .

Tools 2. Navicat

Navicat MySQL Is a powerful MySQL Database server management and development tools . It can work with any 3.21 Or above MySQL Working together , Support triggers 、 stored procedure 、 function 、 event 、 View 、 Manage users, etc , It's easy for beginners to learn and use . Its well-designed graphical user interface (GUI) It allows users to quickly and easily create... In a safe and easy way 、 organization 、 Access and share information .Navicat Support Chinese , There's a free version of .
Download address :http://www.navicat.com/.

 Insert picture description here

 Insert picture description here

Tools 3. SQLyog

SQLyog It is famous in the industry Webyog Company produced a simple and efficient 、 Powerful graphics MySQL Database management tools . This tool uses C++ Language development . The tool can easily create a database 、 surface 、 Views and indexes, etc , It is also easy to insert 、 Update and delete operations , At the same time, the database can be easily 、 Backup and restore of data tables . The tool can not only through SQL File to import and export a large number of files , You can also import and export XML、HTML and CSV And so on .
Download address :http://www.webyog.com/, Readers can also search the download address of the Chinese version .

 Insert picture description here

 Insert picture description here

Tools 4:dbeaver

DBeaver Is a general database management tool and SQL client , Support all popular databases :MySQL、PostgreSQL、SQLite、Oracle、DB2、SQL Server、 Sybase、MS Access、Teradata、 Firebird、Apache Hive、Phoenix、Presto etc. .DBeaver Than most SQL Management tools should be lightweight , And support Chinese interface .DBeaver Community Edition as a free and open source product , Compared with other similar software , Not inferior in function and ease of use .

The only thing to note is DBeaver Yes, it is Java Developed by programming language , So you need to have JDK(Java Development ToolKit) Environmental Science . If not on the computer JDK, Choose to install DBeaver When the component , Check “Include Java” that will do .

Download address :https://dbeaver.io/download/

 Insert picture description here

 Insert picture description here
 Insert picture description here
 Insert picture description here

Connection problems may occur :

Some graphical interface tools , Especially the old version of graphical interface tools , In connection MySQL8 When “Authentication plugin ‘caching_sha2_password’ cannot be loaded” error .

 Insert picture description here

The reason for this is MySQL8 In previous versions, the encryption rule was mysql_native_password, And in the MySQL8 after , The encryption rule is caching_sha2_password. There are two ways to solve the problem , The first is to upgrade the graphical interface tool version , The second is to put MySQL8 The encryption rule of user login password is restored to mysql_native_password.

The second solution is as follows , Log in with the command line MySQL After database , Execute the following commands to modify the user password encryption rules and update the user password , Here, change the user name to “[email protected]” The user password rule for is “mysql_native_password”, The password value is “123456”, As shown in the figure .

# Use mysql database 
USE mysql;
# modify 'root'@'localhost' User's password rules and passwords 
ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'abc123';
# Refresh the permissions 
FLUSH PRIVILEGES;

 Insert picture description here

6. MySQL Directory structure and source code

6.1 Main directory structure

MySQL Directory structure of explain
bin Catalog all MySQL The executable of . Such as :mysql.exe
MySQLInstanceConfig.exe Database configuration wizard , What appears during installation
data Catalog The directory where the system database is located
my.ini file MySQL The main configuration file for
c:\ProgramData\MySQL\MySQL Server 8.0\data\ The directory of the database created by the user

6.2 MySQL Source code acquisition

First , You have to enter MySQL Download Interface . Here you don't choose to use the default “Microsoft Windows”, But through the drop-down bar , find “Source Code”, In the following operating system version , choice Windows(Architecture Independent), Then click download .

Next , Unzip the downloaded compressed file , We got it MySQL Source code .

MySQL Yes, it is C++ Developed by , Let me briefly introduce the composition of the source code .

mysql-8.0.22 All subdirectories under the directory , Contains MySQL The source code of each component :

 Insert picture description here

  • sql The subdirectory is MySQL Core code ;

  • libmysql Subdirectories are client programs API;

  • mysql-test Subdirectories are test tools ;

  • mysys Subdirectories are operating system related functions and auxiliary functions ;

The source code can be opened and viewed with Notepad , If you have C++ Development environment of , You can also open and view... In the development environment .

 Insert picture description here

As shown in the figure above , The source code is not mysterious , It's just plain C++ Code , As you are familiar with , And there are a lot of comments , It can help you understand . Reading the source code is like following MySQL Like a developer conversation , Very interesting .

7. Solutions to common problems ( Extracurricular content )

problem 1:root User password forgotten , Reset operation

1: Through task manager or service management , Turn off the mysqld( Service process )
2: From the command line + Special parameters on mysqld
mysqld --defaults-file=“D:\ProgramFiles\mysql\MySQLServer5.7Data\my.ini” --skip-grant-tables

3: here ,mysqld The service process has been opened . And no permission check is required
4: mysql -uroot No password login server . Start another client for
5: Modify the permission table
(1) use mysql;
(2)update user set authentication_string=password(‘ New password ’) where user=‘root’ and Host=‘localhost’;
(3)flush privileges;
6: Through task manager , Turn off the mysqld Service process .
7: Again through service management , open mysql service .
8: You can log in with the new password after modification .

problem 2:mysql Order newspaper “ Not an internal or external command ”

If input mysql Order newspaper “ Not an internal or external command ”, hold mysql Installation directory bin Directory configuration to environment variables path in . as follows :

[ Failed to transfer the external chain picture , The origin station may have anti-theft chain mechanism , It is suggested to save the pictures and upload them directly (img-5xrgBpFo-1639117842557)(images/image-20210914093150145.png)]

problem 3: error ERROR : Operate tables and data without selecting a database

ERROR 1046 (3D000): No database selected
Solution 1 : Is the use of “USE Database name ;” sentence , In this way, the following statements will operate on the database by default
Solution 2 : That is, all table objects are preceded by “ database .”

problem 4: The character set problem of the command-line client

mysql> INSERT INTO t_stu VALUES(1,' Zhang San ',' male ');
ERROR 1366 (HY000): Incorrect string value: '\xD5\xC5\xC8\xFD' for column 'sname' at row 1

reason **: The server thinks your client's character set is utf-8, In fact, the character set of your client is GBK.**

 Insert picture description here

See all character sets :SHOW VARIABLES LIKE ‘character_set_%’;

 Insert picture description here

Solution , Set the client character set of the current connection “SET NAMES GBK;”

 Insert picture description here

problem 5: Modify the character encoding of databases and tables

Modify encoding :

(1) Stop service first ,(2) modify my.ini file (3) Restart the service

explain :

If you are modifying my.ini Previously built libraries and tables , Then the library and table codes are still the same Latin1, Or delete the reconstruction , Or use alter Statement modification code .

mysql> create database 0728db charset Latin1;
Query OK, 1 row affected (0.00 sec)
mysql> use 0728db;
Database changed
mysql> create table student (id int , name varchar(20)) charset Latin1;
Query OK, 0 rows affected (0.02 sec)
mysql> show create table student\G
*************************** 1. row ***************************
Table: student
Create Table: CREATE TABLE `student` (
`id` int(11) NOT NULL,
`name` varchar(20) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)
mysql> alter table student charset utf8; # Modify the character code of the table to UTF8
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> show create table student\G
*************************** 1. row ***************************
Table: student
Create Table: CREATE TABLE `student` (
`id` int(11) NOT NULL,
`name` varchar(20) CHARACTER SET latin1 DEFAULT NULL, # The field is still latin1 code 
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)
mysql> alter table student modify name varchar(20) charset utf8; # Modify the field character encoding to UTF8
Query OK, 0 rows affected (0.05 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> show create table student\G
*************************** 1. row ***************************
Table: student
Create Table: CREATE TABLE `student` (
`id` int(11) NOT NULL,
`name` varchar(20) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)
mysql> show create database 0728db;;
+--------+-----------------------------------------------------------------+
|Database| Create Database |
+------+-------------------------------------------------------------------+
|0728db| CREATE DATABASE `0728db` /*!40100 DEFAULT CHARACTER SET latin1 */ |
+------+-------------------------------------------------------------------+
1 row in set (0.00 sec)
mysql> alter database 0728db charset utf8; # Change the character code of the database to utf8
Query OK, 1 row affected (0.00 sec)
mysql> show create database 0728db;
+--------+-----------------------------------------------------------------+
|Database| Create Database |
+--------+-----------------------------------------------------------------+
| 0728db | CREATE DATABASE `0728db` /*!40100 DEFAULT CHARACTER SET utf8 */ |
+--------+-----------------------------------------------------------------+
1 row in set (0.00 sec)
copyright:author[Cool breeze AAA],Please bring the original link to reprint, thank you. https://en.javamana.com/2022/02/202202130735143545.html