MySQL common statement

Database Create

create database if not exists db_name default charset utf8mb4 collate utf8mb4_general_ci;

The database name cannot exceed 64 characters. Names containing special characters or names consisting entirely of numbers or reserved words must be enclosed in single quotes.
The use of if not exists forces the command to be executed if the database does not exist.

Opt in

use db_name;

Delete

drop database if exists db_name;

The use of if exists can avoid error messages when deleting non-existing databases

Export

# Export all tables in the database db_namemysqldump -uroot -p --default-character-set=utf8 db_name > db_name.sql# Export table_name1 and table_name2 in database db_namemysqldump -uroot -p db_name table_name1 table_name2 > db_name.sql

Import

# select databaseuse db_name;# Import Datasource db_name.sql

User Create

create user user_name@localhost identified by 'user_pwd';ORcreate user user_name@'%' identified by 'user_pwd';

Authorization Database

grant all privileges on db_name.* to user_name@localhost;flush privileges;

This user_name user can only be used on localhost

Some permissions

grant select,insert,update,delete on db_name.* to user_name@'%' identified by 'user_password';flush privileges;

Authorize a table to a user

grant select,insert,update on db_name.table_name to user_name@'%';flush privileges;

Authorize view to user Permission to view views

grant show view on db_name.view_name to user_name@'%';flush privileges;

Delete user permissions

revoke all privileges on db_name.* from user_name@localhost;flush privileges;

Change user password

use mysqlupdate user set Password=password('newpassword') where User='user_name';flush privileges;ORupdate mysql.user set password=password('newpassword') where User='test1' and Host='localhost';flush privileges;

Delete user

delete from user where user='user_name' and host='localhost';flush privileges;

Data Sheet Update field names

ALTER TABLE table_name CHANGE `old_col_name` `new_col_name` CHAR(16) DEFAULT NULL;

old_col_name: original field name
new_col_name: updated field name

Only update the field type

ALTER TABLE table_name MODIFY `col_name` INT(11) DEFAULT 0;

The updated field: the type of col_name is: int(11) default 0;

Add field

ALTER TABLE table_name ADD COLUMN `new_col_name` INT(11);

The newly added field is:new_col_name:

Delete field

ALTER TABLE table_name DROP COLUMN `col_name`;

The field to delete is: appid_id;

Query the table name of the database by condition

Must have permission to access the information_schema database

Query tables and views

SELECT * FROM TABLES t WHERE t.table_schema='db_name';

db_name is the database name

query table only

SELECT * FROM TABLES t WHERE t.table_schema='db_name' AND t.table_name LIKE 't_%';

db_name is the database name
t_ is the prefix of the name table