Manage MySQL database instances using command lines

1. log on to the MySQL database server from the command line
1. log on to MySQL with the default port 3306

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

2. manage multiple MySQL instances with different ports through TCP connections (note: this function is available in MySQL or later versions)

/Usr/local/mysql/bin/mysql-u root-p -- protocol = tcp -- host = localhost -- port = 3307

3. manage multiple MySQL instances on different ports through socket sockets

/Usr/local/mysql/bin/mysql-u root-p -- socket =/tmp/mysql3307.sock

4. manage multiple MySQL instances on different ports through ports and IP addresses

/Usr/local/mysql/bin/mysql-u root-p-P 3306-h 127.0.0.1


--------------------------------------------------------------------------------

Ii. Database Operation SQL statements
1. display the current database on the server

Show databases;

2. Create a database named rewin

Create database rewin;

3. delete a database named rewin

Drop database rewin;

4. Select rewin Database

USE rewin;


--------------------------------------------------------------------------------

3. Operate SQL statements in tables (after logon, you must USE the preceding USE command to select a database before performing table operations)
1. display the tables in the current database

Show tables;

2. Create a database table zhangyan: paste the following SQL statement after mysql>. The storage engine is MYISAM, and the field id is the primary key and unique index.

Create table 'hangday '(
'Id' INT (5) unsigned not null AUTO_INCREMENT,
'Username' VARCHAR (20) not null,
'Password' CHAR (32) not null,
'Time' datetime not null,
'Number' FLOAT (10) not null,
'Content' text not null,
Primary key ('id ')
) ENGINE = MYISAM;

3. view the structure of the zhangyan table

DESCRIBE zhangyan;

4. Retrieve Information from the table
4.1 retrieve all records from the zhangyan table

SELECT * FROM zhangyan;

4.2 retrieve specific rows from the zhangyan table: The field username is equal to abc, and the field number is equal to 1, which are sorted in descending order by field id

SELECT * FROM zhangyan WHERE username = abc AND number = 1 order by id DESC;

4.3 retrieve the specified fields from the zhangyan table: username and password

SELECT username, password FROM zhangyan;

4.4 retrieve unique non-repeated records from the zhangyan table:

Select distinct username FROM zhangyan;

5. Insert information to the zhangyan table

Insert into zhangyan (id, username, password, time, number, content) VALUES (, abc, 123456,200 14:32:12, 23.41, hello world );

6. Update the specified information in the zhangyan table.

UPDATE zhangyan SET content = hello china WHERE username = abc;

7. Delete the specified information in the zhangyan table

Delete from zhangyan WHERE id = 1;

8. Clear the zhangyan table

Delete from zhangyan;

9. Delete the zhangyan table

Drop table zhangyan;

10. Change the table structure and change the field type of the username field in the zhangyan table to CHAR (25)

Alter table zhangyan CHANGE username CHAR (25 );

11. Import mysql. SQL in the current directory to the database

SOURCE./mysql. SQL;


--------------------------------------------------------------------------------

Iv. Database permission operation SQL statements
1. Create a user sina with root permission and can log on from any IP address. The password is zhangyan.

Grant all privileges on *. * TO sina @ % identified by zhangyan;

2. Create a user with the "data operation" and "structure operation" permissions. The password is zhangyan.

Grant select, INSERT, UPDATE, DELETE, FILE, CREATE, DROP, INDEX, ALTER, create temporary tables, create view, show view, create routine, alter routine, execute on *. * TO sina@192.168.1. % identified by zhangyan;

3. Create a user that only has the "data operation" permission and can only log on from 192.168.1.24, and can only operate on the user sina in the zhangyan table of the rewin database. The password is zhangyan.

Grant select, INSERT, UPDATE, delete on rewin. zhangyan TO sina@192.168.1.24 identified by zhangyan;

4. Create a user with the "data operation" and "structure operation" permissions, which can log on from any IP address and can only operate on sina of the rewin database with the password zhangyan

Grant select, INSERT, UPDATE, DELETE, CREATE, DROP, INDEX, ALTER, create temporary tables, create view, show view, create routine, alter routine, execute on rewin. * TO sina @ % identified by zhangyan;

5. delete a user

Drop user sina @ %;

6. SQL statements in MySQL that replace string aaa with bbb in batches

UPDATE table name SET field name = REPLACE (field name, aaa, bbb );

7. repair damaged tables

① Use the root account to log on to MySQL from the command line:
Mysql-u root-p

② Enter the password of the root account.

③ Select the Database Name (student in this example ):
Use student;

④ Repair the damaged table (in this example, the table to be repaired is smis_user_student ):
Repair table smis_user_student; udent;