MYSQL

Introduction to the functions of MySQL database management system

​​​high speed

High speed is a notable feature of MySQL. In MySQL, extremely fast "B-tree" disk tables (MyISAM) and index compression are used; by using optimized "single scan multiple connections", extremely fast connections can be achieved.

​​​Support multiple platforms

​​​MySQL supports more than 20 development platforms, including Linux, Windows, FreeBSD, IBM AIX, HP-UX, Mac OS, OpenBSD, Solaris, etc.

​​​Support various development languages

​​​MySQL provides support for various popular programming languages ​​and provides them with many API functions, including C, C++, Java, Perl, PHP, etc.

​​​Multiple memory engines available

MySQL provides a variety of database storage engines. Each engine has its own strengths and is suitable for different applications. Users can choose the appropriate engine to obtain high performance.

​​​Powerful

The powerful storage engine enables MySQL to be effectively applied to any database application system and complete various tasks efficiently.

​​​Support large databases

The InnoDB storage engine stores InnoDB tables in a table space, which can be created from several files.

​​​Safety

​​​​Flexible and secure permissions and password system that allows host-based authentication.

​​​low price

​​MySQL is licensed under the GPL. In many cases, users can use MySQL for free.

MYSQL database management system installation tutorial

​ ​ 1. Use compression software to decompress the MYSQL database management system software you downloaded from Huajun Software Park, and double-click with the left button to run the installation program in the package. The following is a screenshot of the interface that appears after the editor installed it:

​​ 2. We directly select [next] to start the installation. After starting the installation, we will enter the MYSQL database management system installation agreement interface. Please understand the installation agreement before agreeing to continue [next].

3. After agreeing to the installation agreement, enter the MYSQL database management system installation type selection interface. Here, the editor recommends that if you use MYSQL software for the first time, please select "Typical" (typical) installation. If you need other options, please select "Complete" ( Complete) or "Custom" type for installation settings. The editor here recommends that you set the installation program location outside the C drive when choosing a custom installation.

​​ 4. After installing and setting up the MYSQL database management system information, you will enter the installation program interface, just wait patiently for the installation to complete.

5. After installation, enter the MYSQL database management system configuration guidance interface and select according to your needs.

​​ 6. Finally, select [finish] in the following pages to complete the installation.

MYSQL database usage tutorial

1. Start the MySQL server

How to start MySQL. Two methods:

​ ​ ​ First, use winmysqladmin. If it runs automatically when the machine starts, you can directly proceed to the next step.

The second is to run in DOS mode

                                                                                                                                                                              d:/mysql/bin/mysqld

​​​2. Enter the mysql interactive operation interface

In DOS mode, run:

D:/mysql/bin/mysql -u root -p

A prompt appears, and you have entered the interactive operation mode of mysql.

If "ERROR 2003: Can‘t connect to MySQL server on 'localhost‘ (10061)" appears, it means that your MySQL has not been started yet.

​​​3. Exit the MySQL operation interface

You can exit the interactive interface at any time by typing quit at the mysql> prompt:

mysql> quit

Bye

You can also use control-D to exit.

​​​4. The first command

Mysql> select version(),current_date();

+----------------+-----------------+

| version() | current_date() |

+----------------+-----------------+

| 3.23.25a-debug | 2001-05-17 |

+----------------+-----------------+

1 row in set (0.01 sec)

This command asks the mysql server to tell you its version number and current date. Try the above command with different case and see what the results are.

The result shows that the uppercase and lowercase results of the mysql command are consistent.

​​​5. Multi-line statements

A command can be entered in multiple lines until a semicolon ";" appears:

mysql> select

-> USER()

-> ,

-> now()

->;

+--------------------+--------------------------------- -+

| USER() | now() |

+--------------------+--------------------------------- -+

| ODBC@localhost| 2001-05-17 22:59:15 |

+--------------------+--------------------------------- -+

​​​6. Use the SHOW statement to find out what database currently exists on the server:

Mysql> SHOW DATABASES;

+----------+

| Database |

+----------+

| mysql |

| test |

+----------+

3 rows in set (0.00 sec)

​​​7. Create a database abccs

Mysql> CREATE DATABASE abccs;

                                                                                                                                            and                                 Note that different operating systems are case-sensitive.

​8. Select the database you created

Mysql> USE abccs

Database changed

At this point you have entered the database abccs you just created.

​​​9. Create a database table

​ ​ First, look at what tables currently exist in your database:

Mysql> SHOW TABLES;

Empty set (0.00 sec)

It means that there are no database tables in the database just created.

Commonly used statements in mysql database

1. Use the SHOW statement to find out what database currently exists on the server:

Mysql> SHOW DATABASES;

2. Create a database MYSQLDATA

Mysql> CREATE DATABASE MYSQLDATA;

3. Select the database you created

mysql> USE MYSQLDATA; (When you press Enter and Database changed appears, the operation is successful!)

4. Check what tables exist in the current database

Mysql> SHOW TABLES;

5. Create a database table

Mysql> CREATE TABLE MYTABLE (name VARCHAR(20), sex CHAR(1));

​​ 6. Display table structure:

Mysql> DESCRIBE MYTABLE;

7. Add records to the table

Mysql> insert into MYTABLE values ​​("hyq","M");

8. Load the data into the database table in text mode (for example, D:/mysql.txt)

mysql> LOAD DATA LOCAL INFILE “D:/mysql.txt” INTO TABLE MYTABLE;

9. Import .sql file command (such as D:/mysql.sql)

Mysql>use database;

Mysql>source d:/mysql.sql;

10. Delete table

Mysql>drop TABLE MYTABLE;

11. Clear the table

Mysql>delete from MYTABLE;

12. Update the data in the table

Mysql>update MYTABLE set sex=”f” where name=’hyq’;

MYSQL database andNavicat for MySQLcontrast

Navicat for MySQL supports various editing functions and data management, supports data transmission/import/export, data or structure synchronization, etc. It has strong compatibility and powerful functions.

​​​​MySQL database is small in size, fast in operation, supports more than 20 development platforms, and supports large databases. Especially because of its open source characteristics, it is very popular among small and medium-sized websites.

The official download address of Navicat for MySQL is:http://softwaredownload4.com/soft/87702.htm

MYSQL database FAQ

​​​How to open mysql?

Click the start button to see if there is a mysql work bench. If there is, it can be visualized. If not, press win+r to build at the same time, enter cmd, press enter, enter mysql -u root -p password, press enter, and enter.

      How to enable mysql scheduled events?

First, query the status of the planned event in SQL: SHOW VARIABLES LIKE 'event_scheduler'. If the returned value is off, it means that it is currently closed. If it is on, the scheduled task has been turned on. Find the my.ini file in the directory of the mysql program and add an item: event_scheduler = 1. Save and restart the mysql service. Restarting the service can be found in the service management.

      or how to express it in mysql?

In the where condition, the link between each condition can be and or or. or means or, but be careful to put it in the last condition, otherwise there will be problems.

      The mysql service cannot start, the service did not report any errors? Please type NET HELPMSG 3534 for more help?

​​ 1. There is no data folder in the mysql5.7 directory. Comment out the datadir in my.ini.

​ ​ 2. Do the following:

D:/MYSQL-5.7.12-winx64/bin>MYSQLd --initialize

D:/MYSQL-5.7.12-winx64/bin>net start MYSQL

The mysql service is starting..

The mysql service has been started successfully.

MYSQL database update log

MYSQL database has improved the dynamic control of triggers: it supports applying multiple triggers to one event type in the same table;

Real-time execution analysis;

Improved JSON Explain Data: You can add total query cost, single table query cost, total data volume and other parameters to the optimizer to obtain more detailed information.