Linux operation MySQL database common simple steps

To connect to the MySQL database:

The main view of MySQL installed in which directory:

Mysql-h host Address-u user name-P user password or mysql-h IP address-u zaiai-p zaiai or/var/email/mysql/bim/mysql

SQL Modify field type in MySQL database

First of all: in the MySQL database you can modify the field type of the table, the advantage is that the original data will not be lost under normal circumstances.

Its syntax rules are: ALTER TABLE newexample Modify ID char (20);

Here is a detailed explanation, where the same modification table is started with ALTER TABLE and then the table name followed by the name of the field to be modified, followed by the type to be modified later.

Let's take a look at the example: ALTER TABLE newexample Modify ID char (20);

Simple Steps for database operation

1. Display Database

show databases;

2. Select Database

Use database name;

3. Display the tables in the database

Show tables;

4, display the structure of the data table

describe table name;

5. Display the records in the table

SELECT * FROM table name

6. Build the Library

Create DATABSE library name;

7. Build a table


CREATE TABLE table name (field set list);mysql> CREATE TABLE name (ID int auto_increment NOT NULL primary key, Uname char ( 8), Gender char (2), birthday date); Query OK, 0 rows affected (0.03 sec) mysql> show tables; +------------------+ | Tables_in_userdb | +------------------+ | name | +------------------+ 1 row in Set (0.00 sec) mysql> describe name; +----------+---------+------+-----+---------+----------------+ | field| type| Null | Key | Default | extra| +----------+---------+------+-----+---------+----------------+ | ID | Int (11) | NO | PRI | null| auto_increment | | uname| CHAR (8) | yes| | null| | | Gender | char (2) | yes| | null| | | Birthday | date| yes| | null| | +----------+---------+------+-----+---------+----------------+ 4 rows in Set (0.00 sec) Note: auto_increment self-increment primary Key PRIMARY Key


8. Add record

INSERT into name (Uname,gender,birthday) VALUES (' Zaizai ', ' Male ', '1991-10-01 ');

9, change the record

Update name set birthday= '1991-01-10 ' where Uname= ' Zhang San ';

10. Delete Records

Delete from name where Uname= ' Zhang San ';

11. Delete a table

DROP table Name

12. Delete Library

drop database name;

13. Backing Up the database

Mysqldump-u root-p--opt database name > backup name; Go to library Directory

14. Recovery

Mysql-u ROOT-P Database name < backup name; Database must exist at restore time, can be an empty database

15. Database Authorization
Format: Grant Select on database. * To User name @ login host identified by "password"
Example 1, add a user user001 password for123456, 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 [email protected] '% ' identified by '123456 ";


Example 2, add a user user008 password for345678, so that the user can only log on localhost, you can also set the specified IP, and can query the database test, 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 user_2, and he cannot access the database directly from the Internet, but only through the MySQL host to manipulate the test library.
First, use the root user to connect to MySQL, and then type the following command:
Mysql>grant Select,insert,update,delete on test.* to [e-mail protected] identified by "345678 ";


Note: Second, you can also use the way to modify the table to handle the user's logon method:
Database: Mysql
Table: User
Modify: The value of the host column in the user table to reality login entry
For operation please refer to: Centos 6.2 install MySQL Note

===============================================

MySQL commands the user to connect to the database.

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

1) Connect to MySQL on this computer
First open the DOS window, then enter the directory Mysql\bin, and then type the command Mysql-u root-p, enter after you are prompted to lose the password.

Note that you can have a space before the user name, but there must be no space before the password, or let you re-enter the password.

If you have just installed MySQL, superuser root is no password, so the direct return to enter the MySQL, MySQL prompt is: mysql>

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-u Root-p 123; (Note: You can not add a space between the root and the other)

3) quit MySQL command
Exit (Enter)

Linux operation MySQL database common simple steps