MySQL Database Common Command Summary (i)

MySQL Database Common Commands

1. mysql Common commands

Create database name; Create a database

Use DatabaseName; Select Database

Drop database name deletes databases directly and does not alert

Show tables; Show Table

Describe TableName; Detailed description of the table

Add distinct in Select to remove duplicate fields

Mysqladmin drop DatabaseName You are prompted before you delete the database.

Show current MySQL version and current date

Select version (), current_date;

2. Change the password of root in MySQL:

Shell>mysql-u root-p

mysql> Update user Set Password=password ("Xueok654123″) where user= ' root ';

Mysql> flush Privileges//Refresh Database

Mysql>use dbname; Open the database:

Mysql>show databases; Show all databases

Mysql>show tables; Displays all tables in the database MySQL: use MySQL first;

Mysql>describe user; Displays column information for the user table in the MySQL database);

3. Grant

Create a full superuser who can connect to the server from anywhere, but must use a password something do this

Mysql> Grant all privileges on * * to [e-mail protected] identified by ' something ' with

Add new users

Format: Grant Select on database. * To User name @ login host identified by "password"

GRANT all privileges on * * to [email protected] identified by ' something ' with GRANT OPTION;

GRANT all privileges on * * to [e-mail protected] "%" identified by ' something ' with GRANT OPTION;

Remove Authorization:

Mysql> revoke all privileges on * * FROM [email protected] "%";

mysql> Delete from user where user= "root" and host= "%";

mysql> flush Privileges;

Create a user custom login on a specific client it363.com to access a specific database fangchandb

MySQL >grant Select, insert, UPDATE, Delete, Create,drop on fangchandb.* to [e-mail protected] it363.com identified by ' passwd

To rename a table:

mysql > ALTER table t1 rename T2;

4, Mysqldump

Backing Up the database

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

Recovering a 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 build instruction, the command is as follows:

Shell> mysqladmin-u root-p-D databasename > A.sql

If you only want to unload the SQL command that inserts the data, and you do not need to create a table command, the command is as follows:

shell> mysqladmin-u root-p-t databasename > A.sql

So what should I do if I only want the data and I don't want any SQL commands?

mysqldump-t./Phptest Driver

Only the-t parameter is specified to unload the plain text file, which represents the directory where the data is unloaded./represents the current directory, that is, the same directory as mysqldump. If you do not specify a driver table, the data for the entire database is unloaded. Each table generates two files, one for the. sql file, which contains the build table execution. The other is a. txt file that contains only data and no SQL instructions.

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. The shell can be used to type the redirection utility to complete this work.

For example, if a file is stored in the My_file.sql

These queries can be executed as follows:

For example, if you want to write the build statement in advance in Sql.txt:

MySQL > mysql-h myhost-u root-p Database < Sql.txt

reprint: ( This article transferred from http://news.newhua.com/news1/program_database/2009/217/ 0921715343537k7h7idi2cci09jci1dk8fj4b07b3a04219g561c3jab.html)

(Source: http://blog.csdn.net/u010002704/article/details/39062807)

MySQL Database Common Command Summary (i)