MySQL common commands under Linux

Transferred from: http://www.jb51.net/LINUXjishu/36171.html

First, summarize:
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 \ "\";
In the above, the login, add users, password changes and other issues. Let's look at MySQL's operations on the database. Note: You must first log in to MySQL, the following actions are performed at the prompt of MySQL, and each command ends with a semicolon.
First, the Operation skill
1, if you hit the command, enter after the discovery forgot to add a semicolon, you do not have to re-play the command, as long as a semicolon to enter the return on it. This means that you can break a complete command into a few lines and finish it with a semicolon as the end sign.
2. You can use the cursor up and down keys to recall the previous command. But previously I used an old version of MySQL that was not supported. I'm using Mysql-3.23.27-beta-win now.
Second, show the command
1. Display the database list:
show databases;
Just started with two databases: MySQL and test. MySQL Library is very important it has the MySQL system information, we change the password and the new user, is actually using this library to operate.
2. Display the data table in the library:
use MySQL;//Open the library, learn foxbase must not be unfamiliar with it
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;
A city build and build tables and instances of inserting data
Drop database if exists school; Delete if school is present
Create Database School; Building a library School
Use school; Open Library School
CREATE TABLE teacher//Create tables Teacher
(
ID int (3) auto_increment NOT null primary key,
Name Char (TEN) is not NULL,
Address varchar (+) Default ' Shenzhen ',
Year Date
); End of Build table
The following is the Insert field
Insert into teacher values (' ', ' Glchengang ', ' Shenzhen One ', ' 1976-10-10 ');
Insert into teacher values (' ', ' Jack ', ' Shenzhen One ', ' 1975-12-23 ');
Note: In the Build table (1), set the ID to a number field of length 3: Int (3) and let it automatically add one to each record: Auto_increment is not null, and makes him the main field primary Key (2) sets the name to a character field of length 10 (3) To set address to a character field of length 50, and the default value is Shenzhen. What is the difference between varchar and char, only to wait for a later article to say. (4) Set year as the Date field.
It is also possible to type the above commands at the MySQL prompt, but it is not easy to debug. You can write the above command as-is to a text file, assume School.sql, then copy to c:\\, and enter directory \\mysql\\bin in DOS, and then type the following command:
Mysql-uroot-p Password < C:\\school.sql
If successful, empty a row without any display, and if there is an error, there is a hint. (The above command has been debugged, you can use it only if you remove//comment).


Iv. transferring text data to the database
1, the text data should conform to the format: The field data is separated by the TAB key, the null value is replaced by \\n.
Cases:
3 Rose Shenzhen II 1976-10-10
4 Mike Shenzhen one 1975-12-23
2. The data incoming command is the name of the "file name \" Into table of the infile \ "filename \".
Note: You might want to copy the file to the \\mysql\\bin directory, and use the using command to hit the library that contains the table.
Five, BACKUP database:
1, mysqldump--opt school>school.bbb
Mysqldump--opt SCHOOL&GT;SCHOOL.BBB
(commands are executed in the DOS \\mysql\\bin directory)
Note: Back up the database school to the school.bbb file, school.bbb is a text file, the filename is taken, open to see what you will find.
PostScript: In fact, MySQL database operation and other SQL class database is much the same, you'd better find a book to read SQL. I am here only to introduce some basic, in fact, I only understand these, hehe. The best MySQL tutorial or "Yanzi" translated "MySQL Chinese reference manual" not only free every relevant website has downloaded, and it is the most authoritative. Unfortunately not like the "PHP4 Chinese manual \" that is the format of the CHM, in the search function command is not very convenient.
2. Modify Login Password
1) mysqladmin-u username-p Old password password new password:
Example: mysqladmin-u root password 21century
Note: Because Root does not have a password at the beginning, the-p old password can be omitted.
2) directly modify the user table root password:
mysql> user MySQL;
mysql> Update user Set Pasword=password (' 21century ') where user= ' root ';
mysql> flush Privileges;
Note: Flush privileges means forcing the memory authorization table to be flushed or the password in the buffer is used.
3. Test if the password has been modified successfully:
1) Login without password:
[[email protected] local]# MySQL
ERROR 1045:access denied for user: ' [email protected] ' (Using password:no)
An error is displayed stating that the password has been modified.
2) Log in with the modified password:
[Email protected] local]# mysql-u root-p
Enter Password: (Enter the modified password 21century)
Welcome to the MySQL Monitor. Commands End With; or \g.
Your MySQL Connection ID is 177 to server version:3.23.48
Type ' help ', ' or ' \h ' for help. Type ' \c ' to clear the buffer.
Mysql>
Success!
This is done by changing the password with the Mysqladmin command, or by modifying the library.


4. Start and stop:
Startup: MySQL has been changed since version 3.23.15, the default post-installation service is started with a MySQL user and does not allow the root user to start.
If you do not want to start with the root user, you must add the--user=root parameter (./safe_mysqld--user=root &) to stop: mysqladmin-u root-p shutdown.
5. Export meeting Database:
Mysqldump-uroot-p21century Meeting > Db_meeting.sql
Guide dbname Database:
Mysqldump-uroot-p21century dbname < Xxx.sql
The import database can also execute a large number of SQL statements at once, similar to @my_script.sql in Oracle, which is useful when using mysqldump does not work.
Example: #./mysql-uroot-p (Note: The CREATE DATABASE, use DatabaseName, CREATE TABLE, and insert INTO statements are all written in the footstep file above)
6. Renaming the table:
RENAME TABLE ztemp to Ztemp4;
7. Modify Field Properties:
ALTER TABLE bbabase change news_id ID VARCHAR (5) is not NULL;
8. Add a field after the content in the table:
ALTER TABLE bbabase ADD leave_time DATETIME not NULL after

MySQL common commands under Linux