MySQL command line login common Operating system overview

One, log in to the MySQL database server from the command line

1. Log in with MySQL using the default 3306 port

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

2. Manage multiple MySQL on different ports via TCP connection (note: This feature is only available for MySQL4.1 or later versions)

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

3. Manage multiple MySQL on different ports via socket socket

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

4. Manage multiple MySQL on different ports via port and IP

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

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

II. database Operation SQL statement

1. Show what database is currently on the server

SHOW DATABASES;

2. Create a database named Rewin

CREATE DATABASE Rewin;

3. Delete the database named Rewin

DROP DATABASE Rewin;

4. Select Rewin Database

Use Rewin;

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

Third, table operation SQL statement (after login must use the above command to select a database, and then table operation)

1. Show what table is present in the current database show TABLES;

2. Create database table Zhangyan: Paste the following SQL statement after mysql>, the storage engine is MyISAM, the field ID is the primary key, and the unique index.

CREATE TABLE ' Zhangyan ' (' ID ' INT (5) UNSIGNED not NULL auto_increment, ' username ' VARCHAR (a) Not NULL, ' password ' CHAR (+) is not null, ' time ' DATETIME is not null, ' number ' FLOAT (TEN) is not null, ' content ' TEXT is not null, PRIMARY KEY (' Id ')) ENGINE = MYISAM;

3. View Zhangyan Table structure

DESCRIBE Zhangyan;

4. Retrieving information from the table 4.1, retrieving all records from the Zhangyan table

SELECT * from Zhangyan;

4.2. Retrieving specific rows from the Zhangyan table: Field username equals ABC, field number equals 1, sorted by field ID Descending

SELECT * from Zhangyan WHERE username = abc and number=1 ORDER by ID DESC;

4.3. Retrieving the specified fields from the Zhangyan table: username and password

SELECT username, password from Zhangyan;

4.4. Retrieve unique non-duplicate records from the Zhangyan table:

SELECT DISTINCT username from Zhangyan;

5. Insert information into the Zhangyan table

INSERT into Zhangyan (ID, username, password, time, number, content) VALUES (, ABC, 123456,

2007-08-06 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 from the Zhangyan table

DELETE from Zhangyan WHERE id = 1;

8. Empty the Zhangyan table

DELETE from Zhangyan;

9. Delete Zhangyan table

DROP TABLE Zhangyan;

10, change the table structure, the Zhangyan Table username field field type to char (25)

ALTER TABLE Zhangyan change username username CHAR (25);

11. Import the Mysql.sql in the current directory into the database

SOURCE./mysql.sql;

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

IV. Database Permissions Operations SQL statement 1, create a user with root privileges, can log on from any IP Sina, password is Zhangyan

GRANT all privileges on *. * to sina@% identified by Zhangyan;

2, create a "data operation", "Structure operation" permission, only from 192.168.1.*** login user sina, 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 only "data operation" permission, only from 192.168.1.24 login, can only operate Rewin database Zhangyan table users

Sina, password is Zhangyan

GRANT SELECT, INSERT, UPDATE, DELETE on Rewin.zhangyan to sina@192.168.1.24 identified by

Zhangyan;

4, create a "data operation", "Structure operation" permission, can log in from any IP, only operate Rewin database user Sina, password for

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 users

DROP USER sina@%;

6.MySQL Replace string AAA batch with BBB SQL statement

UPDATE table name SET field name = REPLACE (field name, AAA, BBB);

7. Repair the corrupted table ①, log in from the command line with the root account mysql:mysql-u root-p

②, enter the password for the root account.

③, selected database name (the database name in this example is student): Use student;

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

Related articles:

MySQL command line common operations

MySQL common command Line Operations summary

Related videos:

Cheetah Web MySQL Video tutorial