Recently in learning MySQL, or only rookie, find the following article for beginners very useful, so share under
1.linux commands to start MySQL:
Mysqladmin start
/ect/init.d/mysql start (front of MySQL installation path)
2.linux Restart MySQL command:
Mysqladmin restart
/ect/init.d/mysql Restart (front of MySQL installation path)
3.linux the command to turn off MySQL:
Mysqladmin shutdown
/ect/init.d/mysql shutdown (front of MySQL installation path)
4. Connect to MySQL on this computer:
Enter the directory Mysql\bin, then type the command mysql-uroot-p, enter the password after entering.
Exit MySQL command: Exit (enter)
5. Modify the MySQL password:
Mysqladmin-u username-p Old password password new password
or go to MySQL command line set PASSWORD for Root=password ("root");
6. Add new users. (Note: Commands in the MySQL environment are followed by a semicolon as a command terminator)
Grant SELECT on database. * To User name @ login host identified by "password"
If you 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;
II. operations related to MySQL database
You must log in to MySQL first, and the operation is performed at the prompt of MySQL, and each command ends with a semicolon
1. Display the list of databases.
show databases;
2. Display the data table in the library:
use MySQL;//Open Library
Show tables;
3, display the structure of the data table:
describe table name;
4, build the library:
Create database name;
5, build the table:
Use library name;
CREATE TABLE table name (field settings list);
6. Deleting the library and deleting the table:
drop database name;
drop table name;
7. Empty the records in the table:
Delete from table name;
8. Display the records in the table:
SELECT * from table name;
9, the revision of the Code
If you want to change the entire MySQL encoding format:
When you start MySQL, the Mysqld_safe command line joins
--default-character-set=gbk
If you want to change the encoding format for a library: Enter a command after the MySQL prompt
ALTER DATABASE db_name default character set GBK;
Iii. Import and export of data
1. The 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. Cases:
1 Name Duty 2006-11-23
Data incoming command load data local infile "file name" into table name;
2. 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.) ）
3. Import data
MySQL < all-databases.sql (Import Database)
Mysql>source News.sql; (executes under MySQL command, can import tables)
One, connect MySQL
Format: mysql-h host address-u user name-P user Password
1. Example 1: Connect to MySQL on this machine.
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>.
2. Example 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-uroot-pabcd123
(Note: You and root can be used without spaces, others are the same)
3. Exit MySQL command: Exit (enter).
Second, change the password
Format: Mysqladmin-u username-P Old password password new password
1, 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.
2, Example 2: Then change the root password to djg345.
MYSQLADMIN-UROOT-PAB12 Password djg345
Third, add new users. (Note: Unlike the above, the following is because it is a command in a MySQL environment, so it is followed by a semicolon as a 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 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] identified by \ "\";
