MySQL common statement
Database Create
create database if not exists db_name default charset utf8mb4 collate utf8mb4_general_ci;Opt inThe 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.
use db_name;drop database if exists db_name;ExportThe use of if exists can avoid error messages when deleting non-existing databases
# 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# select databaseuse db_name;# Import Datasource db_name.sqlcreate user user_name@localhost identified by 'user_pwd';ORcreate user user_name@'%' identified by 'user_pwd';grant all privileges on db_name.* to user_name@localhost;flush privileges;Some permissionsThis user_name user can only be used on localhost
grant select,insert,update,delete on db_name.* to user_name@'%' identified by 'user_password';flush privileges;grant select,insert,update on db_name.table_name to user_name@'%';flush privileges;grant show view on db_name.view_name to user_name@'%';flush privileges;revoke all privileges on db_name.* from user_name@localhost;flush privileges;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 from user where user='user_name' and host='localhost';flush privileges;ALTER TABLE table_name CHANGE `old_col_name` `new_col_name` CHAR(16) DEFAULT NULL;Only update the field typeold_col_name: original field name
new_col_name: updated field name
ALTER TABLE table_name MODIFY `col_name` INT(11) DEFAULT 0;Add fieldThe updated field: the type of col_name is: int(11) default 0;
ALTER TABLE table_name ADD COLUMN `new_col_name` INT(11);Delete fieldThe newly added field is:
new_col_name:
ALTER TABLE table_name DROP COLUMN `col_name`;Query the table name of the database by conditionThe field to delete is:
appid_id;
Must have permission to access the information_schema database
Query tables and viewsSELECT * FROM TABLES t WHERE t.table_schema='db_name';query table onlydb_name is the database name
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
你可能想看：