MySQL database common commands (use)

MySQL database common commands are all relatively basic. For more commands, you can view the text in related articles.

1. MySQL common commands
create database name; Create database
use databasename; Select database
drop database name Delete the database directly without prompting
show tables; show the table
describe tablename; detailed description of the table
Add distinct to select to remove duplicate fields
mysqladmin drop databasename There is a prompt before deleting the database .
Display 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; Show all databases
mysql>show tables; Show all tables in database mysql: first use mysql; then
mysql>describe user; Show tablesmysql databaseColumn information of the user table in #);
3, grant
Create a complete super user who can connect to the server from anywhere, but must use a password something to do this
mysql> grant all privileges on *.* to user@localhost identified by 'something' with
Add new user
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 in to the specific client it363.com and access the specific database fangchandb
mysql >grant select, insert, update, delete, create,drop on fangchandb.* to custom@ it363.com identified by ' passwd'
Rename table:
mysql > alter table t1 rename t2;
4, mysqldump
Backup 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 instructions, the command is as follows:
shell> mysqladmin -u root -p -d databasename > a.sql
If you only want to unload the Insert data sql command, without the need to create a table command, the command is as follows:
shell> mysqladmin -u root -p -t databasename > a.sql
So if I only want data, but not anything How should I operate the sql command?
Mysqldump -T./ phptest driver
Among them, only when the -T parameter is specified, the plain text file can be unloaded, which indicates the directory where the data is unloaded. ./ indicates the current directory, which is the same directory as mysqldump. If the driver table is not specified, the entire database data will be unloaded. Each table will generate two files, one is a .sql file, including table creation execution. The other is a .txt file that only contains 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 type redirection utility to do this. For example, if there are queries
stored in the file my_file.sql, you can execute these queries as follows:
For example, if you want to write the table creation statement in sql.txt in advance:
mysql > mysql -h myhost -u root -p database < sql.txt

The above is the detailed content of MySQL database common commands (use). For more information, please follow other related articles on the PHP Chinese website!