Shulou(Shulou.com)05/31 Report--

This article mainly shows you "what are the common commands of MySQL database", the content is simple and clear, and I hope it can help you solve your doubts. Let me lead you to study and learn what are the common commands of MySQL database.

1. MySQL common commands

Create database name; creates a database

Use databasename; Select Database

Drop database name deletes the database directly without reminding you

Show tables; display table

Detailed description of the describe tablename; table

Add distinct to select to remove duplicate fields

Mysqladmin drop databasename is prompted before deleting the database.

Displays the current mysql version and current date

Select version (), current_date

2. Modify 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 the database

Mysql > use dbname; Open the database:

Mysql > show databases; shows all databases

Mysql > show tables; displays all the tables in the database mysql: use mysql; first and then

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

3 、 grant

Create a full superuser that can connect to the server from anywhere, but you must do this with a password something

Mysql > grant all privileges on *. * to 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 IDENTIFIED BY 'something' WITH GRANT OPTION

GRANT ALL PRIVILEGES ON *. * TO "IDENTIFIED BY 'something' WITH GRANT OPTION

Delete Authorization:

Mysql > revoke all privileges on *. * from "

Mysql > delete from user where user= "root" and host= ""

Mysql > flush privileges

Create a user custom to log in to a specific client it363.com and access a specific database fangchandb

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

Rename the table:

Mysql > alter table T1 rename T2

4 、 mysqldump

Backup database

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

Restore the database

Shell > mysqladmin-h myhost-u root-p create dbname

Shell > mysqldump-h host-u root-p dbname

Mysqladmin-u root-p-d databasename > a.sql

If you only want to uninstall the sql command that inserts the data, and you don't need the table creation 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 any sql commands?

Mysqldump-T. / test driver

Where the plain text file can be unloaded only if the-T parameter is specified, which represents the directory where the data was unloaded, and. / represents the current directory, that is, the same directory as mysqldump. If you do not specify the driver table, the entire data will be unloaded. Each table generates two files, one of which is a .sql file that contains the execution of the table creation. 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 you to read the query from the file instead of waiting for keyboard input. You can use the shell to type the redirect utility to do this. For example, if there is a check in the file my_file.sql

Query, you can execute these queries as follows:

For example, if you want to write the table statement in advance in sql.txt:

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

The above is all the contents of the article "what are the common commands in MySQL database?" Thank you for reading! I believe we all have a certain understanding, hope to share the content to help you, if you want to learn more knowledge, welcome to follow the industry information channel!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.