Frequently-used MySQL database commands-MySQL

Frequently-used MySQL database commands: sharing bitsCN.com 1. frequently-used MySQL 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 .txtbitsCN.com