Learn to use simple MySQL operations

Learn to use simple MySQL operations
Chapter 2 Learn how to use simpleMySQLOperation

The installation of MySQL has been introduced in the previous two chapters, but it is not enough to install it. Some basic operations are required. Of course, there are a lot of MySQL content, but for the linux system administrator, some basic operations can already cope with daily management work, it is more advanced for DBA (technical personnel dedicated to database management.

【ChangeMysqlDatabaseRootPassword]

Password is not required for first database access.

/Usr/local/mysql/bin/mysql-u root

Welcome to the MySQL monitor. Commands end with; or \ g.

Your MySQL connection id is 2

Server version: 5.0.86 MySQL Community Server (GPL)

Type 'help; 'or' \ H' for help. Type '\ C' to clear the buffer.

Mysql>

Now you have entered the mysql operation interface. To exit, enter exit.

Mysql> Exit

Bye

First, explain the meaning of the above command.-u is used to specify the user to log on to. The root user is the administrator account of mysql, and no password by default,HowRootUser Password? Follow these steps:

/Usr/local/mysql/bin/mysqladmin-u root password '000000'

In this way, you can set the password of the root user. Mysqladmin is used to set the password.-u specifies the user. passwod is followed by the password to be defined. The password must be enclosed in single quotation marks or double quotation marks. In addition, you may find that the command is always followed by/usr/local/mysql/bin. But mysql cannot be used directly, because the/usr/local/mysql/bin/directory is not in the $ PATH system variable, therefore, you need to perform this operation (if you can directly run the mysql command in linux, do not perform this operation ):

Vim/etc/profile

Add a row at the end:

Export PATH = $ PATH:/usr/local/mysql/bin/

Save and run

Source/etc/profile

After setting the password, run the command to enter the mysql database operation interface:

Mysql-u root

ERROR 1045 (28000): Access denied for user 'root' @ 'localhost' (using password: NO)

An error is reported because the root user has a password.

Mysql-u root-p

Enter password:

Welcome to the MySQL monitor. Commands end with; or \ g.

Your MySQL connection id is 5

Server version: 5.0.86 MySQL Community Server (GPL)

Type 'help; 'or' \ H' for help. Type '\ C' to clear the buffer.

Mysql>

If you need to add the-p option to specify the password, you will be prompted to enter the password.

After setting the password, How can I change the password??

Mysqladmin-u root-p password "123456789"

Enter password:

Enter the original root password to change the password.

【Connect to database]

I just talked about usingMysql-u root-pYou can connect to the database, but this is only the connection to the local database 'localhost', and many times it is connected to mysql on a host in the network.

Mysql-u user1-p-P 3306-h 10.0.2.69

-P (uppercase) specifies the port bound to the remote host mysql, Which is 3306 by default;-h specifies the IP address of the remote host

【Some basicMySQLOperation Command]

1. query all current databases

Mysql>Show databases;

+ -------------------- +

| Database |

+ -------------------- +

| Information_schema |

| Mysql |

| Test |

+ -------------------- +

2. query the tables of a Database

Mysql>Use mysql;

Database changed

Mysql> Show tables;

+ --------------------------- +

| Tables_in_mysql |

+ --------------------------- +

| Columns_priv |

| Db |

| Func |

| Help_category |

| Help_keyword |

| Help_relation |

| Help_topic |

| Host |

| Proc |

| Procs_priv |

| Tables_priv |

| Time_zone |

| Time_zone_leap_second |

| Time_zone_name |

| Time_zone_transition |

| Time_zone_transition_type |

| User |

+ --------------------------- +

3. View fields in a table

Mysql>Desc func;// Func is the table name

+ ------- + ------------------------------ + ------ + ----- + --------- + ------- +

| Field | Type | Null | Key | Default | Extra |

+ ------- + ------------------------------ + ------ + ----- + --------- + ------- +

| Name | char (64) | NO | PRI |

| Ret | tinyint (1) | NO | 0 |

| Dl | char (128) | NO |

| Type | enum ('function', 'aggregate') | NO | NULL |

+ ------- + ------------------------------ + ------ + ----- + --------- + ------- +

4. view the table structure of a table (detailed structure during table creation)

Mysql>Show create table func;

| Table | CreateTable |

| Func | create table 'function '(

'Name' char (64) collate utf8_bin not null default '',

'Ret 'tinyint (1) not null default '0 ',

'Dl 'Char (128) collate utf8_bin not null default '',

'Type' enum ('function', 'aggregate') character set utf8 not null,

Primary key ('name ')

) ENGINE = MyISAM default charset = utf8 COLLATE = utf8_bin COMMENT = 'user defined functions' |

+ ------- + Response ----------------------------------------------------------------------------------------------------------------------

5. view the current user

Mysql>Select user ();

+ ---------------- +

| User () |

+ ---------------- +

| Root @ localhost |

+ ---------------- +

6. view the current database

Mysql>Select database ();

+ ------------ +

| Database () |

+ ------------ +

| Mysql |

+ ------------ +

7. Create a new database

Mysql>Create database db1;

Query OK, 1 row affected (0.04 sec)

8. Create a table

Mysql>Create table t1 ('id' int (4), 'name' char (40 ));

Query OK, 0 rows affected (0.02 sec)

Mysql>Desc t1;

+ ------- + ---------- + ------ + ----- + --------- + ------- +

| Field | Type | Null | Key | Default | Extra |

+ ------- + ---------- + ------ + ----- + --------- + ------- +

| Id | int (4) | YES | NULL |

| Name | char (40) | YES | NULL |

+ ------- + ---------- + ------ + ----- + --------- + ------- +

9. view the current database version

Mysql>Select version ();

+ ----------- +

| Version () |

+ ----------- +

| 5.0.86 |

+ ----------- +

10. view the current system time

Mysql>Select current_date, current_time;

+ -------------- +

| Current_date | current_time |

+ -------------- +

| 08:52:50 |

+ -------------- +

11. view the current mysql status

Mysql> show status;

+ ----------------------------------- + ---------- +

| Variable_name | Value |

+ ----------------------------------- + ---------- +

| Aborted_clients | 0 |

| Aborted_connects | 1 |

| Binlog_cache_disk_use | 0 |

| Binlog_cache_use | 0 |

| Bytes_received | 664 |

| Bytes_sent | 6703 |

This command is used to display your mysql status.

12. View mysql Parameters

Mysql>Show variables;

Many parameters can be defined in/etc/my. cnf.

13. Create and authorize a common user

Mysql>Grant all on *. * to user1 identified by '201312 ';

Query OK, 0 rows affected (0.01 sec)

All indicates all permissions (read, write, query, delete, and so on ),*. * The preceding "*" indicates all databases, and "*" indicates all tables. "identified by" is followed by a password and enclosed in single quotes. Here, user1 refers to user1 on localhost. If it is to authorize a user on another machine on the network, it is as follows:

Mysql>Grant all on db1. * to 'user2' @ '10. 0.2.100 'identified by '123 ';

Query OK, 0 rows affected (0.00 sec)

There is an @ between the user and the host's IP address. In addition, the host IP address can be replaced by %, indicating all hosts. For example:

Mysql>Grant all on db1. * to 'user3' @ '%' identified by '123 ';

Query OK, 0 rows affected (0.00 sec)

【Some commonSQL]

1. query statement

Mysql>Select count (*) from mysql. user;

Mysql. user indicates the user table of the mysql database; count (*) indicates the total number of rows in the table.

Mysql>Select * from mysql. db;

Query all data in the db table of the mysql database

Mysql>Select db from mysql. db;

Query the db segment of the mysql database table.

Mysql>Select * from mysql. db where host like '10. 0.% ';

Query the rows with the host field like 10.0.% in the mysql database. % indicates that all rows are matched, similar to the wildcards described above.

2. Insert a row

Mysql>Insert into db1.t1 values (1, 'abc ');

Query OK, 1 row affected (0.00 sec)

Table t1 has been created before.

Mysql>Select * from db1.t1;

+ ------ +

| Id | name |

+ ------ +

| 1 | abc |

+ ------ +

3. Change a row

Mysql>Update db1.t1 set name = 'aaa' where id = 1;

Query OK, 1 row affected (0.02 sec)

Rows matched: 1 Changed: 1 Warnings: 0

In this way, the name in the row with the original id 1 is changed to 'aaa'

4. delete a table

Mysql>Drop table db1.t1;

Query OK, 0 rows affected (0.01 sec)

5. delete a database

Mysql>Drop database db1;

Query OK, 0 rows affected (0.07 sec)

6. backup and recovery database

Mysqldump-uroot-p mysql> mysql. SQL

Here, mysqldump is the backup tool. mysql after-p refers to the mysql database and redirects the backup file to mysql. SQL. If it is restored, you only need:

Mysql-uroot-p mysql <mysql. SQL

I have introduced so many basic MySQL operations. Of course, it is far from enough. I hope you can learn more in your work, if you are very interested in MySQL, you may wish to study it in depth. After all, there is always no harm in learning more. If you want to learn more, go to the MySQL official Chinese Reference Manual (5.1 ).