MySQL command line common operations

MySQL command line common operations

One, log in to MySQL database server 1 from the command line, log on using the default 3306 port of MySQL

/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, showing what database currently exists 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 tables exist 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, 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;

MySQL command line common operations