Common commands for MySQL Database Management

Guidance:MySQLCommon Database management commands.

Install MySQL using the RPM package and set iptables for TCP port 3306.

Root Password Management sets the root user's password mysqladmin-uroot password 'Password '.

Modify the root user password mysqladmin-uroot-P password 'Password '.

Database and table management go to mysqlmysql-H hostname-uroot-P.

Create mysql> Create Database location.

Import the data structure mysql-uroot-P location <./location. SQL.

View the database mysql> show databases;

Enter a database mysql> use location;

View table information mysql> show tables;

View the table structure mysql> DESC contact;

Change the table name mysql> rename table contact to contact_new.

Delete database mysql> drop database location.

Delete A Table mysql> drop table contact.

In the authorization section, create a user and authorize mysql> grant all on location. * To gk1020 @ '10. 1.11.71 'identified by 'gk1020 '.

Unauthorize mysql> revoke all on location. * From gk1020 @ '10. 1.11.71 '.

Refresh the permission mysql> flush privileges.

The operation statement queries mysql> select * from contact.

Mysql> select count (*) from contact.

Modify mysql> Update contact set regtime = '2017-01-01 00:00:00 'Where id = 1.

Mysql> Update contact set regtime = '2017-01-01 00:00:00 ', cid = 1 where id = 1.

Insert mysql> insert into contact values ('',''...)

Mysql> insert into contact (ID, CID, contact) values ('',''...)

Delete mysql> Delete from contact where id = 1.

Export the exported database locationmysqldump-uroot-P location>./location. SQL.

Export a table mysqldump-uroot-p -- database location -- table contact>./contact. SQL.

Export the database location data structure mysqldump-D-uroot-P location>./location. SQL.

Copy table copy table contact is contact_bakmysql> Create Table contact_bak as select * from contact.

Copy the table contact structure mysql> Create Table contact_bak as select * From contact where 1 = 2.

View the running task mysql> show processlist.

Description:

Location is the database name, and contact is the table name.

/// // MySQL common Stored Procedure commands ////////////

Mysql> show procedure status: displays basic information about all stored procedures in the database. Including the database, stored procedure name, and creation time.

Mysql> show create procedure sp_name displays detailed information about a stored procedure.

1: Create a stored procedure:

Create procedure stored procedure name (

[In | Out | inout] parameter datatype

)

Begin

MySQL statement:

End;
The default value is in, which is used to the "in" parameter ,;

For example:
Create procedure pr_add

(

A int, B INT

)

Begin

Declare C int;

If a is null then:

Set a = 0;

End if;

If B is null then:

Set B = 0;

End if;

Set c = A + B;

Select C as sum

End; (Note: Return C: it cannot be used in MySQL stored procedures. Return can only be used in functions. Declare is the declaration of variables ).

2. // call the stored procedure ://///////////

Call pr_add (10, 20 );

3. Delete the stored procedure:

Drop procedure sp_name;

You cannot delete another stored procedure in one stored procedure. You can only call another stored procedure.

Block definition:

Begin

........

End;

Condition Statement:

If condition then

Statement

Else

Statement

End if;

Loop statement:

While Loop

End while;