MySQL database terminal - common operation command codes

 //Create a new usercreate user username identified by 'password'; 
//View existing users and host names select user,host from mysql.user; 

 //Change the username rename user 'original username'@'host' to 'new username'@'host'; 

 //Change password mysqladmin -u user -p original password password new password 

 //Delete user drop user username@'host'; 

 //View existing users and host names select user,host from mysql.user;

 //View the database SHOW DATABASES; 

 //Create a database CREATE DATABASE database name; 

 //View the database definition information SHOW CREATE DATABASE database name; 

 //Delete the database DROP DATABASE database name; 

 //Flush the MySQL privilege table FLUSH PRIVILEGES; 

 //Login to mysql -u user -p password mysql -u user -p password -h host IP
 
 
//Change the command name prompt \u@\h \d
prompt mysql \d>
prompt mysql (\d)>
 
//Display the current server versionSELECT VERSION();
//Display the current dateSELECT NOW();
//Display the current userSELECT USER();
//Set the client encoding SET NAMES gbk;
//Show the opened database SELECT DATABASE();
//View the port show global variables like 'port';
 
//View existing users and host names select user,host from mysql.user;
 
//Create a new usercreate user username identified by 'password'; 
 
//Delete user drop user username@'host';
 
//Change the username rename user 'original username'@'host' to 'new username'@'host';
 
//Change password mysqladmin -u user -p original password password new password update mysql.user set password = password('password') where user = 'user' and host = 'host';
 
//Authorize grant all privileges on zhangsanDb.* to zhangsan@'%' identified by 'zhangsan';
all privileges: All privileges.
select: Read permission.
delete: Delete permission.
update: Update permission.
create: Create permission.
drop: delete database and table permissions.
 
Username@host represents the authorized user and the IP address that the user is allowed to log in. There are several types of Host:
localhost: This user is only allowed to log in locally and cannot log in remotely.
%: Allow remote login from any machine except the local machine.
192.168.52.32: The specific IP means that the user is only allowed to log in from the specific IP.
 
//View the information of newly added database permissions select user,Db,host,select_priv,insert_priv,update_priv,delete_priv from mysql.db where user='zhangsan';
 
//Flush the MySQL privilege table FLUSH PRIVILEGES;
 
//Create a database CREATE DATABASE database name;
CREATE DATABASE IF NOT EXISTS database name; -- Create it if it does not exist. CREATE DATABASE IF NOT EXISTS database name CHARACTER SET gbk;
//Modify the database ALTER DATABASE database name CHARACTER SET = utf8;
//View the database SHOW DATABASES;
//View the database definition information SHOW CREATE DATABASE database name;
//Delete the database DROP DATABASE database name;
//If the database exists, delete it DROP DATABASE IF EXISTS database name;
//Switch database USE database name;
//View all tables in the database SHOW TABLES;
//View a database table SHOW TABLES FROM database name;
//View the table structure DESC table name;
SHOW COLUMNS FROM table name;
//View the table statement SHOW CREATE TABLE table name;
//Delete table DROP TABLE table name;
 
Modify table // delete column alter TABLE table name DROP column name;
//Change the name of the table RENAME TABLE table name TO new table name;
//Modify the table character set alter TABLE table name CHARACTER SET character set //Modify the column name alter TABLE table name CHANGE column name new column name column type;
//Add columns alter table table name add column name column type;