MySQL Command Daquan classic edition

The following articles mainly introduce MySQL commands, including common MySQL database commands, modifying the actual root password in the MySQL database, and introducing the practical application of grant, the following describes the specific content of the article. I hope you will gain some benefits.

MySQL Command Daquan 1. MySQL Common commands

Create database name; create a database

Use databasename; select database

Drop database name directly deletes the database, no reminder

Show tables; displays tables

Describe tablename; Detailed description of the table

Add distinct to the select statement to remove duplicate fields.

Before MySQLadmin drop databasename deletes a database, a message is displayed.

Display current MySQL version and current date

 
 

  
  
select version(),current_date;  
 
 

MySQL Command Daquan 2. Modify the root password in MySQL:

 
 

  
  
shell>MySQL -u root -p   
  
  
MySQL> update user set passwordpassword=password(”xueok654123″) where user=’root’;   
 
 

MySQL> flush privileges // refresh the database

MySQL> use dbname; open the database:

MySQL> show databases; displays all databases

MySQL> show tables; display all tables in MySQL: use MySQL first; then

MySQL> describe user; displays the column information of the user table in the MySQL database );

3. grant

Create a full Super User that can connect to the server from anywhere, but you must use a password something to do this

MySQL> grant all privileges on *. * to user @ localhost identified by 'something'

Add new users

Format: grant select on database. * to username @ login host identified by "password"

 
 

  
  
GRANT ALL PRIVILEGES ON *.* TO monty@localhost IDENTIFIED BY ’something’ WITH GRANT OPTION;   
  
  
GRANT ALL PRIVILEGES ON *.* TO monty@”%” IDENTIFIED BY ’something’ WITH GRANT OPTION;   
 
 

Delete authorization:

 
 

  
  
MySQL> revoke all privileges on *.* from root@”%”;   
  
  
MySQL> delete from user where user=”root” and host=”%”;   
  
  
MySQL> flush privileges;   
 
 

Create a User custom to log on to it363.com on a specific client and access the specific database fangchandb.

 
 

  
  
MySQL >grant select, insert, update, delete, create,drop on fangchandb.* to custom@ it363.com identified by ‘ passwd’  
 
 

Rename a table:

 
 

  
  
MySQL > alter table t1 rename t2;  
 
 

MySQL Command Daquan 4. MySQLdump

Back up database

 
 

  
  
shell> MySQLdump -h host -u root -p dbname >dbname_backup.sql  
 
 

Restore database

 
 

  
  
shell> MySQLadmin -h myhost -u root -p create dbname   
  
  
shell> MySQLdump -h host -u root -p dbname < dbname_backup.sql   
 
 

If you only want to unload the table creation command, the command is as follows:

 
 

  
  
shell> MySQLadmin -u root -p -d databasename > a.sql  
 
 

If you only want to unload the SQL command for inserting data without the table creation command, the command is as follows:

 
 

  
  
shell> MySQLadmin -u root -p -t databasename > a.sql  
 
 

What should I do if I only want data and do not want any SQL commands?

 
 

  
  
MySQLdump -T./ phptest driver  
 
 

Only when the-T parameter is specified can the plain text file be detached, indicating the directory where the data is detached and./indicates the current directory, that is, the same directory as MySQLdump. If no driver table is specified, the data of the entire database is detached. Each table generates two files, one of which is a. SQL file, including table creation and execution. The other is a. txt file that only contains data and does not contain SQL commands.

5. You can store the query in a file and tell MySQL to read the query from the file instead of waiting for keyboard input. You can use the shell to type the redirection utility to do this. For example, if the file my_file. SQL contains a query

Query:

For example, if you want to write a table prefix in SQL .txt:

 
 

  
  
MySQL > MySQL -h myhost -u root -p database < sql.txt  
 
 

The above content is an introduction to the MySQL command list, and I hope you will get some benefits.