Database version type: MYSQL 5.0.x community version database important commands: mysqldump;mysql
System environment: Windows Xp Sp2
Sample database: Demo (internal tables can be created arbitrarily)
Database username: root (can also be a username with administrative permissions)
Database login password: dbuserkey
In the test environment, mysql has been set successfully in the environment variable. You can enter mysql on the command line to start the mysql database engine.
A. Completely export all data and structures of the Demo (full backup)
Create a batch file backup.bat in any directory (such as D:mydb) with the following content:
mysqldump -uroot -pdbuserkey --add-locks --opt Demo>D:mydbDemo.sql;
@pause
B. Completely import all Demo data and structures (complete recovery)
Create a batch file restore.bat in the same directory as backup.bat with the following content:
mysql -uroot -pdbuserkey demo<D:mydbDemo.sql;
@pause
C. Test to open the mysql database engine net start mysql
When you run the batch process in A, you will see that Demo.sql is generated under D:mydb.
Then execute the Drop Database Demo command to delete the Demo database and create the Demo database Create Database Demo;
Run B medium ratio processing to recover all data and structures
D. Common commands to log in or connect to mysql database service
mysql -h host address -u username -p user password;
Launch mysql database connection
Exit
Stop the local mysql database service
Net stop mysql
Change password
mysqladmin -u username -p old password password new password;
Show database list
Show Database;
Use database
Use DatabaseName;
Show currently selected database
Select Database();
open table
Show TableName;
Show the structure of the data table
Describe TableName;
Table information contained in the current database:
Show Tables; (note: there is an s at the end)
Create database
Create DataBase databaseName;
Create table
Use databaseName;
Create Table tableName (field setting list);
Delete database and table
Drop Database databaseName;
Drop Table tableName;
Clear the records in the table
Delete From tableName;
Table name change
Rename Table tableA to tableB;
For SQL structured query statement commands, see the mysql doc document
Mysql create and delete users
Grant select,insert,update,delete on *[(database)].* to username@loginhostidentified by "password"
In command mode. Note that each line is followed by ; to indicate the end of a command statement.
Format: grant select on database.* to username@login host identified by "password"
==The following is from the Internet==
Example 1, add a user test1 with the password abc, so that he can log in on any host and have query, insert, modify, and delete permissions on all databases. First, connect to MYSQL as the root user, and then type the following command:
grant select,insert,update,delete on *.* to test1@"% " Identified by "abc";
The user added in Example 1 is very dangerous. If someone knows the password of test1, then he can log in to your mysql database on any computer on the Internet and do whatever he wants with your data. See the solution Example 2.
Example 2, add a user test2 with the password abc, so that he can only log in on localhost, and can query, insert, modify, and delete the database mydb (localhost refers to the local host, that is, the host where the MYSQL database is located) , in this way, even if the user knows the password of test2, he cannot directly access the database from the Internet, and can only access it through the web page on the MYSQL host.
grant select,insert,update,delete on mydb.* to test2@localhost identified by "abc";
If you don’t want test2 to have a password, you can type another command to eliminate the password.
grant select,insert,update,delete on mydb.* to test2@localhost identified by "";
This creates a user named: phplamp with password: 1234.
Then log in.
mysql>exit;
mysql -u phplamp -p
Enter password
mysql>Login successful
2. Authorize users.
//Log in to MYSQL (with ROOT permissions). I log in as ROOT.
mysql -u root -p
password
//First create a database (phplampDB) for the user
mysql>create database phplampDB;
//Authorize the phplamp user to have all permissions on the phplamp database.
>grant all privileges on phplampDB.* to phplamp@localhost identified by '1234';
//Refresh the system permission table
mysql>flush privileges;
mysql>Other operations
/*
If you want to specify some permissions to a user, you can write like this:
mysql>grant select,update on phplampDB.* to phplamp@localhost identified by '1234';
//Refresh the system permission table.
mysql>flush privileges;
*/
3. Delete the user.
mysql -u root -p
password
mysql>Delete FROM user Where User="phplamp" and Host="localhost";
mysql>flush privileges;
//Delete the user's database
mysql>drop database phplampDB;
4. Modify the password of the specified user.
mysql -u root -p
password
mysql>update mysql.user set password=password('new password') where User="phplamp" and Host="localhost";
mysql>flush privileges;
5. List all databases
mysql>show database;
6.Switch database
mysql>use 'database name';
7. List all tables
mysql>show tables;
8. Display the data table structure
mysql>describe table name;
9. Delete database and data table
mysql>drop database database name;
mysql>drop table data table name;
10. Display the structure of the table
describe "tableName";
Connect to mysql under cmd:
mysql -uroot -p Press Enter and be careful not to type the password after -p Note: - There is no space between the parameter character and the value
=========
Reprinted from Happy Rambler Garden
This article comes from the CSDN blog. Please indicate the source when reprinting: http://blog.csdn.net/ljw_army/archive/2009/12/22/5053682.aspx
-