[go] MySQL command basics

"From" http://c.biancheng.net/cpp/u/mysql_ml/

Connect to MySQL Database

MySQL command format: mysql-h host address-u user name-P user Password

1) Connect to MySQL on this computer
First open the DOS window, then enter the directory Mysql\bin, and then type the command Mysql-u root-p, enter after you are prompted to lose the password.

Note that you can have a space before the user name, but there must be no space before the password, or let you re-enter the password.

If you have just installed MySQL, superuser root is no password, so the direct return to enter the MySQL, MySQL prompt 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) quit MySQL command
Exit (Enter)

Modify User Password

mysqladmin command format: Mysqladmin-u user Name----old password password new password

1) Add a password to root ab12
First enter directory Mysql\bin under DOS, and then type the following command:
Mysqladmin-u Root-password AB12
Note: Because Root does not have a password at the beginning, the-p old password can be omitted.

2) then change the root password to djg345
Mysqladmin-u root-p ab12 Password djg345

Add new users and control their permissions

The grant command is used to add new users and control their permissions.

Grant on command format: Grant select on database. * To User name @ login host identified by "password";

1) Add a user test1, password for ABC, so that he can log on any host, and all databases have query, insert, modify, delete permissions. 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 ';

But the added user is very dangerous, you want to like someone to know test1 password, then he can be on any computer on the Internet to log into your MySQL database and your data can do whatever you like, the solution is as follows.

2) Add a user test2 password for ABC, so that he can only log on on localhost, and can query, insert, modify, delete the database mydb (localhost refers to the local host, that is, the MySQL database is located in the 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";

If you do not want to test2 have a password, you can call another command to erase the password.
Grant Select,insert,update,delete on mydb.* to [[E-mail Protected]][email protected][/email] identified by "";

Create a database

Create command format: CREATE DATABASE < DB name >;

Note: Connect to the MySQL server before creating the database.

1) Create a database named XHKDB:
mysql> CREATE DATABASE xhkdb;

2) Create a database and assign users:

1. CREATE database name;
2. GRANT select,insert,update,delete,create,drop,alter on database name. * To database name @localhost identified by ' password ';
3. SET PASSWORD for ' database name ' @ ' localhost ' = old_password (' password ');

Execute 3 commands in turn to complete the database creation.

Show all databases

The show Databases command is used to display all databases.

Show databases command format: show databases; (Note: There is a last s)

Drop DATABASE: Deleting databases

Use: Working with databases

The currently selected (connected) database

The Select command represents the currently selected (connected) database.

Select command format:mysql> Select Database ();

The select command in MySQL is similar to print or write in other programming languages, and you can use it to display the results of a string, a number, a mathematical expression, and so on. How do I use the special features of the Select command in MySQL?

1) Show MySQL version
Mysql> select version ();
+-----------------------+
| Version () |
+-----------------------+
| 6.0.4-alpha-community |
+-----------------------+
1 row in Set (0.02 sec)

2) Show current time
Mysql> Select Now ();
+---------------------+
| Now () |
+---------------------+
| 2009-09-15 22:35:32 |
+---------------------+
1 row in Set (0.04 sec)

3) Display Month Day
SELECT DayOfMonth (current_date);
+--------------------------+
| DayOfMonth (current_date) |
+--------------------------+
| 15 |
+--------------------------+
1 row in Set (0.01 sec)

SELECT MONTH (current_date);
+---------------------+
| MONTH (current_date) |
+---------------------+
| 9 |
+---------------------+
1 row in Set (0.00 sec)

SELECT year (current_date);
+--------------------+
| Year (current_date) |
+--------------------+
| 2009 |
+--------------------+
1 row in Set (0.00 sec)

4) Display string
Mysql> Select "Welecome to my blog!";
+----------------------+
| Welecome to my blog! |
+----------------------+
| Welecome to my blog! |
+----------------------+
1 row in Set (0.00 sec)

5) When the calculator uses
Select ((4 * 4)/10) + 25;
+----------------------+
| ((4 * 4)/10) + 25 |
+----------------------+
| 26.60 |
+----------------------+
1 row in Set (0.00 sec)

6) Threaded string
Select CONCAT (F_name, "", L_name)
As Name
From Employee_data
where title = ' Marketing Executive ';
+---------------+
| Name |
+---------------+
| Monica Sehgal |
| Hal Simlai |
| Joseph Irvine |
+---------------+
3 Rows in Set (0.00 sec)

Note: the concat () function is used here to string strings together. In addition, we used the previously learned as to give the result column ' CONCAT (f_name, "", L_name) ' a pseudonym.

Backing Up the database

The mysqldump command is used to back up the database.

The mysqldump command executes under the DOS \mysql\bin\ directory.

1) Export the entire database (the export file is present in the Mysql\bin directory by default)
Mysqldump-u user name-p database name > exported file name
Mysqldump-u user_name-p123456 database_name > Outfile_name.sql

2) export a table
Mysqldump-u user name-P database name Table name > exported file name
MYSQLDUMP-U USER_NAME-P database_name table_name > OUTFILE_NAME.SQL

3) Export a database structure
Mysqldump-u user_name-p-d–add-drop-table database_name > Outfile_name.sql
-D No data –add-drop-table add a drop table before each CREATE statement

4) Export with language parameters
mysqldump-uroot-p–default-character-set=latin1–set-charset=gbk–skip-opt database_name > Outfile_name.sql

For example, back up the AAA library to file Back_aaa:
[Email protected] root]# Cd/home/data/mysql
[Email protected] mysql]# mysqldump-u root-p--opt aaa > BACK_AAA

[go] MySQL command basics