Overview 1. What is a database? A repository of data, such as: In the ATM example we created a DB directory and called it a database

MySQL, Oracle, SQLite, Access, MS SQL Server, etc. They are all one piece of software with two main functions:

A. Save the data to a file or memory

B. Receive the specified command and perform operations on the file

3. What is SQL?

As mentioned above, MySQL and other software can accept commands and perform corresponding operations. Commands can contain many operations such as deleting files and obtaining file contents, so the commands written are SQL statements. SQL, short for Structured Query Language, is a Language specifically designed to communicate with databases.

Download and install

MySQL is a relational database management system developed by MySQL AB, a Swedish company owned by Oracle. MySQL is the most popular Relational Database Management System. In terms of WEB applications, MySQL is one of the best RDBMS (Relational Database Management System) applications.

If you want to use MySQL to store and manipulate data, you need to do several things: a. Install MySQL server b. Install MySQL client B. Connect to MySQL server C. Send commands to MySQL server and perform corresponding operations (add, delete, change, query, etc.)

Windows version

1, download

MySQL Community Server

http://dev.mysql.com/downloads/mysql/

2, decompression

If you want MySQL to be installed in the specified directory, then move the decompressed folder to the specified directory, for example: C:\mysql-5.7.16-winx64

3. Initialization

The bin directory of MySQL contains a large number of executable files. Run the following command to initialize the data:

CD c: \ mysql – 5.7.16 – winx64 \ bin mysqld — the initialize – insecure

4. Start the MySQL service

Go to the executable directory

CD c: \ mysql – 5.7.16 – winx64 \ bin

Start the MySQL service

mysqld

5. Start MySQL client and connect to MySQL service

Due to the [mysqld –initialize-insecure] command used during initialization, the root account does not have a password by default

Go to the executable directory

CD c: \ mysql – 5.7.16 – winx64 \ bin

Connect to the MySQL server

mysql -u root -p

When prompted for your password, press Enter

Enter Enter to see the following figure, indicating that the installation is successful:

The MySQL server has been installed successfully and the client can connect to the MySQL server. You only need to repeat steps 4 and 5 above before operating MySQL. However, it is cumbersome to repeatedly enter the directory of executable files in Steps 4 and 5. To simplify operations in the future, you can perform the following operations.

A. Add environment variables

Add the MySQL executable file to the environment variable to execute the execute command

[right-click computer] – > [properties] – “, “advanced system Settings” – > “advanced” – > “environment variables”, “[in the second content boxes found variable called the Path of a line, double-click] – > [MySQL appended to the bin directory Path variable values value, use; segmentation 】, such as: C:\Program Files (x86)\Parallels\Parallels Tools\Applications; %SystemRoot%\system32; %SystemRoot%; %SystemRoot%\System32\Wbem; % SYSTEMROOT % \ System32 \ WindowsPowerShell \ v1.0; C:\Python27; C:\Python35; C: \ mysql – 5.7.16 – winx64 \ bin

This way, when you start the service again and connect later, you only need:

Start the MySQL service and enter it on the terminal

mysqld

Connect to MySQL server, enter:

mysql -u root -p

B. Create the MySQL service as a Windows service

The previous step solved some problems, but not completely, because the current terminal will hang when executing MySQL server [mysqd], so do some Settings to solve this problem:

To create a MySQL Windows server, run the following command on a terminal:

“C: \ mysql – 5.7.16 – winx64 \ bin \ mysqld” – install

To remove MySQL from Windows, run the following command on the terminal:

“C: \ mysql – 5.7.16 – winx64 \ bin \ mysqld” – remove

After being registered as a service, you only need to run the following commands to start or stop the MySQL service:

Start the MySQL service

net start mysql

Disabling the MySQL service

net stop mysql

Linux version

Installation:

Yum install mysql server

Server startup

mysql.server start

1, SHOW DATABASES;

Default database: mysql – user permission related data test – used for user testing data

Information_schema – Data related to the MySQL schema

2. Create a database

utf-8

CREATE DATABASE DATABASE name DEFAULT CHARSET UTF8 COLLATE UTF8_general_CI;

gbk

CREATE DATABASE DATABASE name DEFAULT CHARACTER SET GBK COLLATE GBk_chinese_ci;

3. Use a database

USE db_name;

To display all TABLES in the current database: SHOW TABLES;

4. User management

Create user create user'Username'@'IP address' identified by 'password'; Delete the user drop user'Username'@'IP address'; Example Modify the user rename user'Username'@'IP address'; to 'New username'@'IP address';; Change the passwordset password for 'Username'@'IP address' = Password('New password'Mysql > alter table user; mysql > alter table user; mysql > alter table userCopy the code

5. Authorization management

show grants for 'users'@'IP address'Grant grant on database. Table to'users'@'IP address'Revoke permissions on database. Watch the from'users'@'IP address'-- Cancel permissionCopy the code

For permissions

All PRIVILEGES except grant SELECT Query privileges select, INSERT query and insert privileges... Usage No access permission ALTER Use ALTER TABLE ALTER routine Use ALTER PROCEDURE and DROP PROCEDURE CREATE Use CREATE table create routine Use create procedure create temporary tables Use create temporary tables create user Use create user, drop user, and rename User and REVOKE all PRIVILEGES create view create view delete Delete DROP Drop table execute Use call and stored procedure file use SELECT Into outfile and load data infile grant option Grant index insert Insert lock tables Lock table Process run show full processlist select run show databases run show databases show view run show view update run update Reload flush shutdown mysqladmin shutdown super 􏱂􏰈 change master,kill, logs, Purge, master and purgesetGlobal. It also allows mysqladmin to debug access to the replication Client server location. Replication slave is used by replication slavesCopy the code

For the database

For target databases and others internally:

Database name.* All database names in the database. Table Specifies the name of a table database in the database. Stored procedures specify stored procedures *.* all databases in the databaseCopy the code

For users and IP

Username@IP Address A user can access only when the IP address is changed. [email protected].% A user can access only when the IP address is changed (wildcard % indicates any character).Copy the code

The sample

grant all privileges on db1.tb1 TO 'Username'@'IP'
grant select on db1.* TO 'Username'@'IP'
grant select,insert on *.* TO 'Username'@'IP'
revoke select on db1.tb1 from 'Username'@'IP'Flush PRIVILEGES, which reads data into memory, effective immediately.Copy the code

Forgot password

# Start the unauthorized server
mysqld --skip-grant-tables
# client
mysql -u root -p
Change the user name and password
update mysql.user set authentication_string=password('666') where user='root';
flush privileges;
Copy the code