MySQL Common commands

Check out some MySQL commands in Linux: (Note: commands in the MySQL environment are followed by a semicolon as a command terminator)

Remote connection command:
Mysql-h 192.168.0.201-p 3306-u root-p123

command to start MySQL: mysqladmin start/ect/init.d/mysql start (the installation path in front of MySQL)

Restart MySQL command mysqladmin restart/ect/init.d/mysql restart (front of MySQL installation path)

command to turn off MySQL: mysqladmin shutdown/ect/init.d/mysql shutdown (front mysql installation path)

Connect the MYSQL:CD on this computer mysql\binmysql-uroot-p enter the password to exit the MySQL command: exit

Modify MySQL Password: mysqladmin-u username-P Old password password new password ormysql command line set password for Root=password ("root");

Add new user grant Select on database. * To username @ Login host identified by "password" eg: Add a user test password of 123, so that he can log on any host, and all databases have query, insert, modify, delete permissions. First, use the root user to connect to MySQL, and then type the following command: Grant Select,insert,update,delete on *. * to ' identified by ' 123;

For MySQL database operations, you must first log in to MySQL, and the operation is performed at the prompt of MySQL, and each command ends with a semicolon

Show Database list: show databases;

Display the data table in the library: use MySQL;//Open Library

Show tables;

Display the structure of the data table: describe table name;

Build database: Create database name;

Build table: Use library name;

CREATE TABLE table name (field settings list);

Deletion and deletion of tables: drop database name;

drop table name;

Empty the records in the table: Delete from table name;

Show Records in table: SELECT * from table name;

Code modification If you want to change the entire MySQL encoding format: When you start MySQL, mysqld_safe command line joins--DEFAULT-CHARACTER-SET=GBK

If you want to change the encoding format of a library: Enter the command after the MySQL prompt ALTER DATABASE db_name default character set GBK;

Import and export of data

Text data goes to the database

The format that text data should conform to: The field data is separated by the TAB key, and the null value is used instead. Example: 1 name duty 2006-11-23 data incoming command load data local infile "file name" into table name;

exporting databases and tables

Mysqldump--opt News > News.sql (back up all the tables in database news to the News.sql file, News.sql is a text file, and the file name is taken.) ) Mysqldump--opt News Author article > Author.article.sql (back up author tables and article tables in database news to author.article.sql files, Author.article.sql is a text file, and the file name is either taken. ) mysqldump--databases db1 DB2 > News.sql (Database dbl and DB2 back to News.sql file, News.sql is a text file, filename is taken.) ) mysqldump-h host-u user-p pass--databases dbname > File.dump

is to import the name of the host on the user, password pass database dbname into the file file.dump

Mysqldump--all-databases > All-databases.sql (Back up all databases to the All-databases.sql file, All-databases.sql is a text file, and the file name is taken.) ）

Import data

MySQL < all-databases.sql (Import database) Mysql>source News.sql; (execute under mysql command, import table)

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

Example 1: Connect to MySQL on this computer. First open the DOS window, and then enter the directory Mysqlbin, and then type the command mysql-uroot-p, enter after the prompt you to lose the password, if just installed MySQL, superuser root is no password, so directly enter into MySQL, The prompt for MySQL is: mysql>.

Example 2: Connect to MySQL on a 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-uroot-pabcd123 (Note: U and root can be used without spaces, and others are the same)

Exit MySQL command: Exit (enter).

Change Password format: mysqladmin-u user Name----old password password new password

Example 1: Add a password to root ab12. First enter directory Mysqlbin under DOS, and then type the following command:

Mysqladmin-uroot-password ab12 Note: Because Root does not have a password at the beginning, the-p old password can be omitted.

Example 2: Then change the root password to djg345.

MYSQLADMIN-UROOT-PAB12 Password djg345

Add new users. (Note: Commands in the MySQL environment are followed by a semicolon as the command Terminator) format: Grant Select on database. * To User name @ login host identified by \ "Password \"

Example 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, use the root user to connect to MySQL, and then type the following command:

Grant Select,insert,update, delete on *. * to [email protected]\ "%\" identified by \ "Abc\";

But example 1 increases the 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 on your MySQL database and to your data can do whatever, workaround see Example 2.

Example 2, add a user test2 password for ABC, so that he can only login on localhost, and the database mydb can query, insert, modify, delete operations (localhost refers to the local host, that is, the MySQL database 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] identified by \ "Abc\"; if you don't want the test2 to have a password, you can use a command to erase the password. Grant Select,insert,update,delete on mydb.* to [email protected] identified by \ "\";

MySQL Common commands