Mysql database is already the most popular database at present. Because its community version is open source, free, cross platform, easy to install and configure, rich and powerful functions, it is regarded as the preferred database by most enterprises. Especially for NET developers, enter In the era of. NET Core, most people have also changed from SQL Server to MySQL. The main reason is that SQL Server started too late in cross platform technology, and there are too few companies and personnel using Linux platform. Especially in today's era of popular microservices, there are too few technical materials and mature solutions, and it is not free and open source, Therefore, the market share of SQL Server is gradually shrinking.

Today's blog is not a summary of the basic SQL statements added, deleted, modified and checked in MySQL, but a summary of the SQL statements that you usually need, but can't remember at once and are not easy to find on the Internet. The main purpose is to make it convenient for me to find it quickly and save time.

1, Operation database

-- query mysql What databases are currently available
SHOW DATABASES;

-- View the character set of the database in the following format:
-- SHOW CREATE DATABASE Database name;
SHOW CREATE DATABASE testdb;

-- Create a database in the following format:
-- CREATE DATABASE IF NOT EXISTS Database name CHARACTER SET Character set name
CREATE DATABASE IF NOT EXISTS testdb CHARACTER SET utf8;

-- Modify the character set of the database in the following format:
-- ALTER DATABASE Database name CHARACTER SET Character set name;
ALTER DATABASE testdb CHARACTER SET gbk;

-- Use a database to make subsequent SQL Statement runs on this database by default
USE testdb;

-- View which database is currently in use
SELECT DATABASE();

-- Delete the database in the following format:
-- DROP DATABASE IF EXISTS Database name;
DROP DATABASE IF EXISTS testdb;

2, Operation data sheet

-- Take the database I created myself as an example
USE testdb;

-- see testdb What tables are there in the database
SHOW TABLES;

-- View the table structure of a specific table
-- The format is: DESC Table name;
DESC users;

-- View the character set of a specific table
-- The format is: SHOW TABLE STATUS FROM Library name LIKE 'Table name';
SHOW TABLE STATUS FROM testdb LIKE 'users';

-- Modify the character set of a table
-- The format is: ALTER TABLE Table name CHARACTER SET Character set name;
ALTER TABLE users CHARACTER SET gbk;

-- Create a table with the same structure by referring to a table in the existing database (only referring to the structure and excluding data)
-- The format is: CREATE TABLE Table name LIKE Referenced table name;
CREATE TABLE my_user LIKE users;

-- Modify the name of a table
-- The format is: ALTER TABLE Table name RENAME TO New table name;
ALTER TABLE my_user RENAME TO custom_user;

-- Add a column to the table
-- The format is: ALTER TABLE Table name ADD Column name data type;
ALTER TABLE users ADD gender VARCHAR(10);

-- Modify the name of a column in a table
-- The format is: ALTER TABLE Table name MODIFY Column name new data type;
ALTER TABLE users MODIFY gender INT;

-- Modify the name and data type of a column at the same time
-- The format is: ALTER TABLE Table name CHANGE Column name new column name new data type;
ALTER TABLE users CHANGE gender company VARCHAR(100);

-- Delete a column in a table
-- The format is: ALTER TABLE Table name DROP Listing;
ALTER TABLE users DROP company;

-- Delete a table
-- The format is: DROP TABLE IF EXISTS Table name;
DROP TABLE IF EXISTS custom_user;

3, Command line backup restore

In most cases, we use graphical tools to back up and restore a database in mysql, but if you want to back up a database in MySQL regularly through the program, you can consider implementing the backup and restore through the program execution command line.

Note: backing up a database in mysql through the command line is not to log in to mysql to execute the backup command, but to run the executable file mysqldump in the bin directory of mysql to back up the database. In windows operating system, you need to connect to the server where mysql is installed remotely. In linux, you need to connect to the server where mysql is installed through Xshell or SecureCRT tool.

-- The backup statement format is: mysqldump -u root -p Database name > File save path

-- windows The following backup example:
mysqldump -u root -p testdb > d:\testdb20220131.sql
-- linux The following backup example:
mysqldump -u root -p testdb > /databak/testdb20220131.sql

To restore the database, you need to log in to mysql and run it. Generally, it is recommended to create a new database, restore the backup to the new database, and then switch the database by modifying the database connection string of the program.

mysql official does not provide the command to modify the database name. If you have to make the restored database name the same as the original database name, you can only delete the original database first, then create a new database with the same name, and then restore the backup to the new database. This operation is relatively dangerous.

In the Windows operating system, if we are still remotely connected to the server where mysql is installed, enter the bin directory of mysql through the command line of cmd window. In linux, we need to connect to the server where mysql is located through Xshell or SecureCRT tool.

-- Log in to mysql After, the format of the restore command is: source Backup file path;

-- Sign in mysql The format is: mysql {-h host ip} {-P Port number} -u account number -p {Database name used}
-- among {} The content of can be omitted. The 3306 port is used by default to log in localhost of mysql database 
mysql -u root -p
-- data root After the password of the account, log in to mysql in
-- Create a new database
CREATE DATABASE IF NOT EXISTS testdb2 CHARACTER SET utf8;
-- Use new database
USE testdb2;
-- windows Restore example below:
SOURCE d:\testdb20220131.sql
-- linux Restore example below:
SOURCE /databak/testdb20220131.sql

4, Operating user

The vast majority of commands of mysql operation users need to use the root account to operate, and other accounts do not have permission.

-- View current login user
select user();

-- Log in to mysql After that, modify the password of the currently logged in user
mysql -u root -p
-- After entering the correct password, log in to mysql Then modify the password through the following statement
SET PASSWORD = '123456';

--------------------------------

-- Create new user: authorization must be specified ip 192.168.1.44 To access mysql User wolfer
create user 'wolfer'@'192.168.1.44' identified by '123456';

-- Create new user: authorization must be specified ip Segment 192.168.1.* To access mysql User monkey
create user 'monkey'@'192.168.1.%' identified by '123456';

-- Create new user: authorize any ip Can access mysql User alpha
create user 'alpha'@'%' identified by '123456';

-- delete user
drop user 'wolfer'@'192.168.1.44';

-- Modify user name
rename user 'alpha'@'%' to 'jobs'@'%';

-- Change a user's password
set password for 'monkey'@'192.168.1.%' = 'fastorder';

--------------------------------

-- View permissions
show grants for 'jobs'@'%'

-- to grant authorization jobs User only testdb.user Tables can be queried, inserted and updated
grant select,insert,update on testdb.user to 'jobs'@'%';

-- to grant authorization jobs User pair testdb Any operation of all tables under
grant all privileges on testdb.* to 'jobs'@'%';

-- to grant authorization jobs The user has any operation on all files in the database
grant all privileges  on *.*  to 'jobs'@'%';

-- cancel jobs User pair testdb of user Any operation of the table
revoke all on testdb.user from 'jobs'@'%';

-- Cancel from remote server jobs User to database testdb All permissions for all tables
revoke all on db1.* from 'jobs'@'%'; 

-- Cancel from remote server jobs User permissions for all tables in all databases
revoke all privileges on *.* from 'jobs'@'%';

-- Note: the above authorization operations and cancellation of authorization operations,
-- If the operated account does not log in again, the following statements need to be executed to take effect
flush privileges;

OK, it has been summarized. There is not much content. I hope the above SQL statements can be helpful to you.