Shulou(Shulou.com)06/01 Report--

This article is mainly about the summary of common knowledge points in mysql, if you are interested, let's take a look at this article. I believe it is of some reference value to everyone after reading the summary of common knowledge points in mysql.

Common commands for 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 super user who can connect to the cloud server from anywhere, but you must do this with a password something

Mysql > grant all privileges on *. * 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 on *. * from root@ "%"

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. / phptest 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 data from the entire database 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 mysql 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

Is it helpful for everyone to summarize the details of the above common knowledge points about mysql? If you want to know more about it, you can continue to follow our industry information section.

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.