Shulou(Shulou.com)06/01 Report--

1. Display the database

Mysql > show databases

+-+

| | Database |

+-+

| | mysql |

| | test |

+-+

2 rows in set (0.04 sec)

Mysql has just been installed with two databases: mysql and test. The mysql library is very important. It contains the system information of MySQL. When we change passwords and add users, we actually use the related tables in this library to operate.

2. Display the tables in the database

Mysql > use mysql; (opening the library and operating on each library requires opening the 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

For example: display records in the user table in the mysql library. All users who can operate on MySQL users are in this table.

Select * from user

5. Build the database:

Create database library name

For example: create a library with the first name bit aaa

Mysql > create databases aaa

6. Create a table:

Use library name

Create table table name (list of field settings)

For example, create a table name in the newly created aaa library. There are four fields in the table: id (serial number, automatic increment), xm (name), xb (gender) and csny (birth date).

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 that 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 records

For example: add several related records.

Mysql > insert into name values (', 'Zhang San', 'male', '1971-10-01')

Mysql > insert into name values (', 'Baiyun', '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 | female | 1972-05-20 | |

+-- +

8. Modify the record

For example, change Zhang San's date of birth to 1971-01-10.

Mysql > update name set csny='1971-01-10 'where xm=' Zhang San'

9. Delete the record

For example: delete Zhang San's record.

Mysql > delete from name where xm=' Zhang San'

10. Delete libraries and tables

Drop database library name

Drop table table name

Increase MySQL users

Format: grant select on database. * to user name @ login host identified by "password"

Example 1. Add a user's user_1 password to 123, so that he can log in on any host and have the authority to query, insert, modify and delete all databases. First use the root user to connect to MySQL, and then type the following command:

Mysql > grant select,insert,update,delete on *. * to user_1@ "%" Identified by "123"

Example 1 the increase in users is very dangerous, if he knows the password of user_1, then he can log into your MySQL database on any computer on the Internet and do whatever he wants with your data. The solution is in example 2.

Example 2, add a user user_2 password to 123so that the user can only log in on localhost, and can query, insert, modify and delete the database aaa (localhost refers to the local host, that is, the host where the MySQL database is located), so that even if the user knows the user_2 password, he can not directly access the database from the network, can only operate the aaa library through the MYSQL host.

Mysql > grant select,insert,update,delete on aaa.* to user_2@localhost identified by "123"

If the new user cannot log in to MySQL, use the following command when logging in:

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: back up the aaa library created in the above example to the file back_aaa

[root@test1 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)

[root@test1 mysql] # mysqldump-u root-p-- opt aaa > back_aaa

2. Restore

[root@test mysql] # mysql-u root-p ccc < back_aaa

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.