MySQL Database common commands

1, MySQL common commands

Create database name; Creating a Database

Use DatabaseName; Select Database

Drop database name deletes databases directly, without alerting

Show tables; Show Table

Describe TableName; Detailed description of the table

Add distinct in Select to remove duplicate fields

Mysqladmin drop databasename Before you delete a database, you are prompted.

Display current MySQL version and current date

Select version (), current_date;

2, modify the MySQL root password:

Shell>mysql-u root-p

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

Mysql> flush Privileges//Refresh Database

Mysql>use dbname; To open a database:

Mysql>show databases; Show all databases

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

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

3. Grant

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

Mysql> grant all privileges in *.* to user@localhost 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 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 to *.* from root@ "%";

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

mysql> flush Privileges;

Create a user named It363.com Log on to a specific client to access a specific database fangchandb

MySQL >grant Select, insert, UPDATE, Delete, Create,drop on fangchandb.* to custom@ it363.com identified by ' passwd '

To rename a table:

mysql > ALTER table t1 rename T2;

4, Mysqldump

Backing up a 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 want to unload only the build instructions, the command is as follows:

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

If you want to unload only the SQL commands that insert data without having to create a table command, the command is as follows:

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

So what if I just want data and don't want SQL commands?

mysqldump-t./Phptest Driver

In which, only the-t parameter is specified to unload a plain text file, indicating 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, and contains the build table execution. The other is a. txt file that contains only data and no SQL directives.

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 work. For example, if you have a check in the file My_file.sql

Inquiries, you can execute these queries as follows:

For example, if you want to write a table statement in advance in Sql.txt:

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

        note : More wonderful tutorials Please pay attention to the triple graphics tutorial channel,