Frequently-used MySQL database commands, Super Ultimate version _ MySQL

Common MySQL database commands. share bitsCN.com

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;

2. modify the root password in mysql:

Shell> mysql-u root-p

Mysql> update user set password = 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;

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

BitsCN.com