MySQL operation under DOS

1. Connect to MySQL on this machine.
First open the DOS window, then go to directory Mysql\bin, and then type the command mysql-u root-p

If you just installed MySQL, Superuser Root does not have a password, so you can enter into MySQL directly.

The prompt for MySQL is: mysql>

2. Connect to MySQL on the remote host. assume the remote host IP is: 110.110.110.110, the user name is root, the password is abcd123. Type the following command:
Mysql-h110.110.110.110-u Root-p 123; (Note: You can not add a space between the root and the other)

3. Exit MySQL command : Exit (Enter)

4, Change password:mysqladmin-u user Name----old password password new password

add a password to root ab12. First Enter directory Mysql\bin under DOS, and then type the following command mysqladmin-u Root-password ab12 (because Root does not have a password at the beginning, so-p old password can be omitted)

then change the root password to djg345. mysqladmin-u root-p ab12 password djg345

5. New User name: Grant Select on database. * To User name @ login host identified by "password";

Add a user test1 password to ABC so that he can log on on any host and have access to queries, insertions, modifications, and deletions to all databases. First connect to MySQL with the root user, and then type the following command:

Grant Select,insert,update,delete on * * to [[email protected] '%][email protected] '%[/email] ' identified by ' ABC ';

Add a user test2 password to ABC, so that he can only login on localhost, and can query, insert, modify, delete the database mydb operation (localhost refers to the local host)

This allows the user to use a password that knows test2, and he cannot access the database directly from the Internet, but only through a Web page on the MySQL host.
Grant Select,insert,update,delete on mydb.* to [[E-mail Protected]][email protected][/email] identified by "ABC";

6. Creating database: Create databases < database name > character set UTF8;

7, display database: show databases;

8, connect the database: use databases;

9, the current database information: Select databases ();

Select version ();

　　　　　　　　　　Select Now ();

Select DayOfMonth (current_date);

Select "Welecome to my blog!";

Select ((4 * 4)/10) + 25;

Select CONCAT (F_name, "", l_name);

10. Create data table: Creation table < table name > (< Field name 1> < type 1> [,.. < Field name N> < type n>]);

11, modify the table name: Rename table name to the new table name;

12. Delete Data sheet: Drop table < table name > ;

13. Table Insert data: INSERT into < table name > [(< Field name 1>[,.. < field name n >])] VALUES (value 1) [, (value N)];

14. Query data:

Query multiple: Select < Field 1, field 2,...> from < table name > where < expression >

strong> query Several lines: SELECT * from < table name;  order by ID limit 0,2;

STRONG>15, delete data in table: Delete from < table name;  where expression;

STRONG>16, modify data: Update < table name;  set field = new value,... where condition;

17. Add field:ALTER TABLE < table name > add field type other (not null ...);

18, back Up the database: (the export file by default exists in the Mysql\bin directory )

Command executed in DOS [Url=file://\\mysql\\bin]\\mysql\\bin[/url] Directory

To export the entire database:　　 mysqldump-u user name-p database name > exported file name

To export a table: mysqldump-u user name-P database name Table name > exported file name

To Export a database structure:mysqldump-u user name  -p-d–add-drop-table database name  >  exported file name ( -D No data – add-drop-table Add a drop table before each create statement )

< EM id= "__mcedel" >       with language parameter export: mysqldump-uroot-p–default-character-set=latin1–set-charset=gbk–skip-opt database name  >  exported file name

19. Delete databases: Drop database if exists < database name > ;

MySQL operation under DOS