MySQL database 1 first acquaintance with MySQL

MySQL database 1 first acquaintance with MySQL


1. What is a database?

Literal meaning: a warehouse for storing data

2. Why use a database? (*****)

Disadvantages of Excel:

1. Unable to manage a large amount of data (data above 10w)

2. Cannot operate the same data table concurrently

3. The database supports some advanced operations: grouping, connecting tables, etc.

3. the classification of the database (*****)

1. Relational database

There are constraints on the data type of each column, ID (integer), name (string), etc.

MySQL, SqlServer, oracle, sqlite, maridb, etc.

MySQL is used more, mainly for free

SqlServer from Microsoft, only used by universities and governments

oracle: Oracle, fees, financial companies (high security), Ali

sqlite: small file database, play with it yourself

maridb: Developed by one person with MySQL

2. Non-relational database

memcache is not resolved

mongodb, a document database, does not use too much, and solves the problem of memory data loss after power failure

Redis: Used on Weibo to solve the problem of memory data loss after power failure

Use {"key":'value'} to store data

3. The difference between relational and non-relational:

Relational database, store data in hard disk

Non-relational, store data in memory (fast speed)

4. the architecture of the database MySQL

Client and server similar to socket


1. The MySQL server is started first, listening on a specific port (3306)

2. The MySQL client connects to the server

3. The MySQL client can send operation commands to manipulate the data on the server.

5. the installation of the database

Windows installation: go to the official website to find information (learning method) to install,

The official download address of MySQL software (, I personally feel that downloading the compressed package version is better than downloading the installation package, because the default installation path of the installation package version is the system disk, and the entire database has About 1.8G, too much system disk storage.

1. Installation steps:

1. Unzip

Decompress the database to which disk you want to install the database to (a separate folder)

2. Add environment variables

[Right-click on the computer]--"[Properties]--"[Advanced System Settings]--"[Advanced]--"(Win10 directly searches for environment variables to get to this step) [Environmental Variables]--"[In the second Find the line with the variable name Path in the content box, double-click] --> [Append the MySQL bin directory path to the variable value, use; split]

The mysql under bin is the client and mysqld is the server

3. Initialization

Enter mysqld --initialize-insecure on the cmd interface to initialize the database.

4. Start the MySQL service

Input: mysqld

5. Start the mysql client and connect to the mysql server

Enter the command: mysql -u root -p

6. Make mysql service into windows service

To make a MySQL Windows service, execute this command in the terminal:

"c:\mysql-5.7.16-winx64\bin\mysqld" --install

To remove the MySQL Windows service, execute this command in the terminal:

"c:\mysql-5.7.16-winx64\bin\mysqld" --remove

Note: The path in quotation marks is the mysqld path under the bin directory and must be an absolute path.

2. Two ways to start mysqd:

1. Start the service cmd and enter net start mysql

Stop the service cmd end enter net stop mysql

2. As shown in the figure below, manually start, stop, and restart the server in the computer management of the computer!

The above is the whole process of the 5.6 and 5.7 version installation.

3. Password modification operation

After the above process is completed, the root permission password of the software is empty by default. When changing the password for the first time, enter the following command (in the cmd window, run cmd as an administrator, enter under cmd, not under mysql):

mysqladmin -uroot -p password "The password to be set"

To change the password for the second time and later, enter the following command

mysqladmin -uroot -p "original password" password "new password"

4. If the password is forgotten, how to change the password:

1) 5.6 version mysql modify password method

1 Shut down the running MySQL service. 2 Open a DOS window and go to the mysql\bin directory. 3 Type mysqld --skip-grant-tables and press Enter. --Skip-grant-tables means to skip the authorization table authentication when starting the MySQL service. 4 Open another DOS window (because the DOS window just now cannot be moved), and go to the mysql\bin directory. 5 Enter mysql and press Enter. If successful, the MySQL prompt> will appear. Connect to the authority database: use mysql;. 6 Change the password: update user set password=password("123") where user="root"; (don't forget to add a semicolon at the end). 7 Flush privileges (required steps): flush privileges;. 8 Exit quit. Log out of the system, enter again, and log in with the username root and the new password 123 just set.

2) Version 5.7

1 Close the mysqld service 2 Execute in cmd: mysqld --skip-grant-tables = "You can log in without a password, bypassing the password verification authority 3 Execute in cmd: mysql =" mysql -uroot -p 4 Execute as follows sql command: (Special attention, version 5.7 here is different from 5.6) update mysql.user set authentication_string=password('') where user ='root';

5 flush privileges; (must be operated) 6 restart mysql service

6. first met Mysql

SQL commands

1) Operating the database

View current user: select user();

The naming convention of the database:

Can be composed of letters, numbers, underscores, @, #, $, case sensitive, keywords such as create, select, etc. cannot be used, numbers cannot be used alone, and the longest cannot exceed 128 digits

Increase the create database database name charset utf8;

Delete the drop database database name;

Modified: Delete first and then add. If the database has data, a direct drop will cause the data in the database to be lost. In the online environment, the data cannot be deleted directly. You need to make a backup before deleting the data.

Check show databases;

Use database use database name;

Determine the currently used database: select database();

View all user permissions

select * from mysql.user\G;

View the permissions of a user

select * from mysql.db where user='user name'\G; User information is stored in a table named mysql.user

select user,password,host from mysql.user;

Reference: MySQL database 1 first getting to know MySQL-Cloud + Community-Tencent Cloud