1. Common MySQL Commands
create database name; Create a database
use databasename; Select a database
drop database name Directly delete a database without prompting
show tables; Display tables
describe tablename; Show a detailed description of the table
Add distinct to a select statement to remove duplicate fields
mysqladmin drop databasename Prompts before deleting the database.
Display the current MySQL version and current date
select version(),current_date;
2. Change the Password for 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 a database:
mysql>show databases; Show all databases
mysql>show tables; Show all tables in the mysql database: use mysql first; then
mysql>describe user; Display column information for the user table in the mysql database);
3. GRANT
Create a full superuser that 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 a 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;
Revoke privileges:
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 from a specific client it363.com, able to access a 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 a database
shell> mysqldump -h host -u root -p dbname >dbname_backup.sql
Restore a 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 dump the table creation commands, the command is as follows:
shell> mysqladmin -u root -p -d databasename > a.sql
If you only want to dump the sql commands for inserting data, without the table creation commands, the command is as follows:
shell> mysqladmin -u root -p -t databasename > a.sql
So, what if I only want the data, without any sql commands? How should I proceed?
mysqldump -T./ phptest driver
Among this, only by specifying the -T parameter can you dump plain text files. It indicates the directory to dump the data into. ./ indicates the current directory, i.e., the same directory as mysqldump. If you don’t specify the driver table, the entire database’s data will be dumped. Each table will generate two files: one .sql file containing the table creation execution, and another .txt file containing only the data, without sql commands.
5. You can store queries in a file and tell mysql to read the queries from the file instead of waiting for keyboard input. You can use the shell to type the redirection utility to accomplish this. For example, if you have queries stored in a file my_file.sql, you can execute these queries as follows:
For example, if you want to write the table creation statements in sql.txt in advance:
mysql > mysql -h myhost -u root -p database < sql.txt