The Mysql command of
1, connect the Mysql
Format: mysql -h host address - u username - p user password
1, connect to the MYSQL on the unit,
Open a DOS window first, and then enter the directory mysql \ bin, and then type the command mysql -u root -p, carriage return after the prompt you lose the password. Note that user names can have a space before can also be no Spaces, but before the password must be no Spaces, or allow you to enter the password again,
If you just installed MYSQL, super user root is no password, so it can be directly enter into the MYSQL, MYSQL prompt is: mysql>
2, connect to the remote host to MYSQL, assuming that the remote host IP for: 110.110.110.110, user name root password for abcd123, type the following command:
Mysql - h110.110.110.110 -u root - pabcd123; (note: u don't have to add a space between the root and other)
3 from the MYSQL command: exit (enter)
2, change the password
Format: mysqladmin -u username -p
old password password the new password
1, add a password to root ab12,
First under DOS into mysql \ bin directory, and then type the following command
Mysqladmin -u root password ab12
Note: because there is no password root at the beginning, so the -p old password a can be omitted,
2, change the root password to djg345 again,
Mysqladmin -u root - pab12 password djg345
3, add new user
Note: different from the above, the following is because MYSQL commands in the environment, so the back with a semicolon as command terminator
Format: grant select on the database. * to login user name @ host identified by "password"
1, add a user test1 password as an ABC, he can login on any of the hosts, and to all the database query, insert, modify, and delete permissions, first using the root user connected to MYSQL, then type the following command:
Grant, the select, insert, update, and delete on *. * to [email=test1 @ "test1 @ %]" % [/email] "Identified by" ABC ".
But the increasing number of users is very dangerous, you want to know if someone test1 password, then he can on any computer on the Internet for you to log in to your mysql database and the data can do whatever you want, see 2 solution,
2, add a user test2 password as an ABC, he only can login on localhost, and can to query the database mydb, insert, modify, and delete operations (localhost refers to the local host, the host, the place of the MYSQL database), so that users use know test2 password, he can't directly access the database from the Internet, can only through the web page to access MYSQL hosts,,
Grant, the select, insert, update, and delete on mydb. * to [email=test2 @ localhost] test2 @ localhost [/email] identified by "ABC".
If you don't want to be a password test2, you can call a single command will off password,
Grant, the select, insert, update, and delete on mydb. * to [email=test2 @ localhost] test2 @ localhost [/email] identified by "";
4.1 create a database
Note: before I can create the database connect to Mysql server
Command: the create database & lt; The database name & gt;
Case 1: create a database called XHKDB
Mysql> The create database XHKDB;
Example 2: create a database and assign user
(1) the CREATE DATABASE DATABASE name;
(2) GRANT the SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, ALTER ON the database name. * TO the database name @ localhost IDENTIFIED BY 'password'.
(3) SET the PASSWORD FOR 'database name' @ 'localhost'=OLD_PASSWORD (" PASSWORD ");
Follow three orders in order to complete database creation, note: "password" and "database" in Chinese is their needs, set up,
4.2 shows the database
Command: show the databases (note: the last one s)
Mysql> show databases;
Note: in order to show no more noise, to modify the default database code, the following is GBK code page description:
1, modify the configuration files of the MYSQL: modify the default within my. Ini - character set GBK=
-2, the code to run when the modified:
1) Java code: JDBC: mysql://localhost: 3306/test? UseUnicode=true& GBK characterEncoding=
(2) the PHP code: header (" content-type: text/HTML. Charset=gb2312 ");
(3) C language code: int mysql_set_character_set (MYSQL * MYSQL, char * csname);
This function is used to SET the default character SET, for the current connection string csname specifies a valid character SET name, connection proofread become character SET the default collation, this function works like SET NAMES statement, but it can also SET the mysql - & gt; Charset value, thus affected by the mysql_real_escape_string () sets the character set,
4.3 delete database
Command: drop the database & lt; The database name & gt;
For example: delete database called XHKDB
Mysql> The drop database XHKDB;
Example 1: delete an existing database established
Mysql> The drop database drop_database;
Query OK, 0 rows affected (0.00 SEC)
Example 2: delete the database of the existence of an uncertain
Mysql> The drop database drop_database;
ERROR 1008: (HY000) Can 't drop the database' drop_database '; The database doesn 't exist
//an error occurs, can't delete 'drop_database' database, the database does not exist,
Mysql> Drop the database if the exists drop_database;
Query OK, 0 rows affected, 1 warning (0.00 SEC)//generate a warning that the database does not exist
Mysql> The create database drop_database;
Query OK, 1 row affected (0.00 SEC)
Mysql> Drop the database if the exists drop_database;//if the exists to judge the existence of the database and does not exist or not an error
Query OK, 0 rows affected (0.00 SEC)
4.4 connect to the database
Command: use & lt; The database name & gt;
For example: if XHKDB database exists, try to access it:
Mysql> Use XHKDB;
Screen: the Database changed
Use statement can notice the db_name MySQL database used as the default database (current), used in subsequent statements, the database to keep as default database, until the end of the discourse, or until the release a different use statement:
Mysql> USE the db1.
Mysql> SELECT COUNT (*) FROM mytable; # selects the from db1. Mytable
Mysql> USE the db2;
Mysql> SELECT COUNT (*) FROM mytable; # selects the from db2. Mytable
USE USE statement for a specific current database marking, will not hinder you to access other tables in the database, the following example can access the author from the db1 database tables, and from the db2 database access to edit table:
Mysql> USE the db1.
Mysql> SELECT author_name, editor_name FROM the author, the editor
-> WHERE an author. Editor_id=db2. Editor. Editor_id;
USE statement has been set up, for that is compatible with Sybase,
Some net friend asked, after connecting how to exit, in fact, don't back, use database, use the show databases can all database queries, if you want to jump to other database, use the
The use of other database name
Ok,
4.5 check the currently selected database
Command: mysql> Select the database ();
MySQL in the SELECT command is similar to other programming languages in print, or write, you can use it to display a string, number, the results of mathematical expression and so on, how to use the SELECT command of special features in MySQL?
1. According to MYSQL version
Mysql> Select the version ();
+ -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- +
(1) | | version
+ -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- +
| 6.0.4 - alpha - community |
+ -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- +
1 row in the set (0.02 SEC)
Display the current time
2.Mysql> The select now ();
+ -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- +
| | now ()
+ -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- +
22:35:32 | 2009-09-15 |
+ -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- +
1 row in the set (0.04 SEC)
3. According to (date) (month) (year)
The SELECT DAYOFMONTH (CURRENT_DATE);
+ -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- +
| | DAYOFMONTH (CURRENT_DATE)
nullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnull
