Cool breeze AAA 2022-02-13 07:35:18 阅读数:742
lecturer ： Silicon Valley - Song Hongkang （ Jianghu people ： a brand of instant noodles ）
Official website ：http://www.atguigu.com
Examples in life ：
|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 .|
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 ：
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）
Corresponding trend chart ：（https://db-engines.com/en/ranking_trend）
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 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 .
IBM The company's database products , Rechargeable . Often used in banking systems .
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 .
Has faded out of the stage of history . Provides a very professional data modeling tool PowerDesigner.
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 .
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 .
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.
SunAcquisition （10 Billion dollars ）,2009 year Sun By
MariaDBemerge 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）
GPL（GNU General Public License）agreement , You can modify the source code to develop your own MySQL System .
4GB,64 The largest table file supported by bit system is
The standard SQL Data languageform .
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 .
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 .
Why do so many manufacturers choose MySQL？ The main reasons for the summary are as follows ：
Open source , Low use cost .
Performance is remarkable , Stable service .
The software is small , Easy to use , And easy to maintain .
Has a long history , Community users are very active , Ask for help when you have problems .
Many Internet companies are using , After the verification of time .
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 ）.
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）.
OldestDatabase type of , Relational database model is to reduce the complex data structure to simple
Binary relationship（ In two-dimensional form ）.
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 .
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 .
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 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 .
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. .
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. .
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. .**
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 .
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 .
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 .
E-R（entity-relationship, Entity - contact ） There are three main concepts in the model ：
Entity set 、
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）.
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)
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
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 、…
Customer and order forms,
Classification table and commodity table,
Department tables and staff tables.
The employee table ： Number 、 full name 、…、 Department
Departmental table ： Number 、 name 、 brief introduction
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 .
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 .
lecturer ： Silicon Valley Song Hongkang （ Jianghu people ： a brand of instant noodles ）
Official website ：http://www.atguigu.com
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 .
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 .
The way 2： adopt 360 Or software such as computer manager
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-220.127.116.11.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 .
③ single click “Next”（ next step ） Button , Confirm uninstall .
④ 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 .
** 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.
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
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
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
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）.
1. Download address
Official website ：https://www.mysql.com
2. Open the website , Click on DOWNLOADS
then , Click on
MySQL Community(GPL) Downloads
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.
MSI Erection sequence; Click on
Go to Download PageDownload it
mysql-installer-web-community-18.104.22.168.msiDownload program size ：2.4M; Networking installation components are required during installation .
mysql-installer-community-22.214.171.124.msiDownload program size ：450.7M; You can install offline during installation . recommend .
Archives, Then choose MySQL5.7 The corresponding version of . Download the latest MySQL5.7.34 edition .
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-126.96.36.199.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 .
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 .
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 .
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 .
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 .
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.
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 .
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 .
step 1： open windows service
step 2： find MySQL80（ Right click ）→ To start or stop （ Click on ）
# start-up MySQL The service command ： net start MySQL service name # stop it MySQL The service command ： net stop MySQL service name
start and stop The following service name should be consistent with the service name specified during previous configuration .
If when you enter a command , Tips “ Denial of service ”, Please use
System administrator Open the command prompt interface and try again .
When MySQL After service startup , You can log in through the client MySQL database . Be careful ： Confirm that the service is on .
The start menu → All the procedures → MySQL → MySQL 8.0 Command Line Client
explain ： Is limited to root user
mysql -h Host name -P Port number -u user name -p password
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 .
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();
exit or quit
1、 View all 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;
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
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_%';
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 .
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.
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/.
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 ：
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 ：
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 .
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/.
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 .
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/
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 .
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;
|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|
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 ：
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 .
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 .
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’;
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 .
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 ：
|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 .”|
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.**
See all character sets ：SHOW VARIABLES LIKE ‘character_set_%’;
Solution , Set the client character set of the current connection “SET NAMES GBK;”
Modify encoding ：
（1) Stop service first ,（2） modify my.ini file （3） Restart the service
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