Linux operations MySQL Common command line

Note: Each command in MySQL is followed by a semicolon;

1. Display Database
mysql> show databases;
+----------+
| Database |
+----------+
| MySQL |
| Test |
+----------+
2 rows in Set (0.04 sec)
MySQL has just finished installing a database of two databases: MySQL and test. MySQL library is very important, it has the MySQL system information, we change the password and new users, in fact, the library with the relevant tables to operate.

2. Display the tables in the database
mysql> use MySQL; (Open library, operate on each library to open this library, similar to FoxPro)
Database changed

Mysql> Show tables;
+-----------------+
| Tables_in_mysql |
+-----------------+
| Columns_priv |
| db |
| Func |
| Host |
| Tables_priv |
| user |
+-----------------+
6 rows in Set (0.01 sec)

3, display the structure of the data table:
describe table name;

4. Display the records in the table:
SELECT * from table name;
Example: Displays the records in the user table in the MySQL library. All users who can operate on the MySQL user are in this table.
Select * from user;

5, build the library:
Create database name;
For example: Create a library with a name-bit AAA
mysql> Create databases AAA;

6, build the table:
Use library name;
CREATE TABLE table name (field settings list);
For example: Create a table in the AAA library that you just created name, the table has ID (ordinal, autogrow), XM (name), XB (gender), CSNY (birth date) four fields
Use AAA;
Mysql> CREATE table name (ID int (3) auto_increment NOT null primary key, XM char (8), XB char (2), CSNY date);
You can use the describe command to view the table structure you just created.
Mysql> describe name;

+-------+---------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+----------------+
| ID |　　　Int (3) | | PRI | NULL | auto_increment |
| XM | CHAR (8) |　　 YES | |　　　　　　　　NULL | |
| XB | char (2) |　　 YES | |　　　　　　　　NULL | |
| CSNY | Date |　　 YES | |　　　　　　　　NULL | |
+-------+---------+------+-----+---------+----------------+

7. Add record
For example: Add a few related records.
mysql> INSERT into name values (' ', ' Zhang San ', ' Male ', ' 1971-10-01 ');
mysql> INSERT into name values (' ', ' white Clouds ', ' female ', ' 1972-05-20 ');
You can use the Select command to verify the results.
Mysql> select * from name;
+----+------+------+------------+
| ID | XM | XB | CSNY |
+----+------+------+------------+
| 1 | Zhang San | Male | 1971-10-01 |
| 2 | Baiyun | Women | 1972-05-20 |
+----+------+------+------------+

8. Change of record
For example: Change Zhang San's birth date to 1971-01-10
Mysql> Update name set csny= ' 1971-01-10 ' where xm= ' Zhang San ';

9. Delete records
For example: Delete the Zhang San record.
mysql> Delete from name where xm= ' Zhang San ';

10. Deleting the library and deleting the table
drop database name;
drop table name;

Add MySQL User

Format: Grant Select on database. * To User name @ login host identified by "password"
Example 1, add a user user_1 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:

Mysql> Grant Select,insert,update,delete on * * to [e-mail protected] "%" identified by "123";
Example 1 added user is very dangerous, if you know the user_1 password, then he can be on any computer on the Internet to log on to your MySQL database and your data to do whatever you like, the solution is shown in Example 2.

Example 2, the addition of a user user_2 password of 123, so that the user can only log on localhost, and the database AAA can query, insert, modify, delete operations (localhost refers to the local host, that is, the MySQL database host), This allows the user to use the password that knows user_2, and he cannot access the database directly from the Internet, only through the MySQL host to operate the AAA library.

Mysql>grant Select,insert,update,delete on aaa.* to [e-mail protected] identified by "123";

With the new user if you can't log in MySQL, log in with the following command:

Mysql-u user_1-p-H 192.168.113.50 (-H followed by the IP address of the host to be logged in)

Backup and Recovery

1. Backup

For example, to back up the AAA library created in the example above to file Back_aaa

[[email protected] root]# Cd/home/data/mysql (go to the library directory, this example library has been transferred from Val/lib/mysql to/home/data/mysql, see part VII above)
[Email protected] mysql]# mysqldump-u root-p--opt aaa > BACK_AAA

2. Recovery

[Email protected] mysql]# mysql-u root-p CCC < BACK_AAA

Linux operations MySQL Common command line