Shulou(Shulou.com)05/31 Report--

This article mainly shows you "how to use command-line management of MySQL database", the content is easy to understand, clear, hope to help you solve your doubts, the following let the editor lead you to study and learn "how to use command-line management of MySQL database" this article.

Log in to the MySQL server from the command line

1. Log in to the MySQL using the default port 3306.

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

2. Manage multiple MySQL of different ports through TCP connection (Note: this feature is only available in MySQL4.1 and above)

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

3. Manage multiple MySQL of different ports through socket sockets

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

4. Manage multiple MySQL of different ports through port and IP

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

II. SQL statement for database operation

1. Show 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

Table operation SQL statement (after logging in, you must use the above USE command to select a database, and then perform table operation)

1. Show what tables exist 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 `zhangyan` (

`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 zhangyan table structure

DESCRIBE zhangyan

4. Retrieve information from the table

4.1. retrieve all records from the zhangyan table

SELECT * FROM zhangyan

Retrieve specific rows from the zhangyan table: field username equals abc, field number equals 1, sorted in descending order by field id

SELECT * FROM zhangyan WHERE username = abc AND number=1 ORDER BY id DESC

Retrieve the specified fields from the zhangyan table: username and password

SELECT username, password FROM zhangyan

4.4.Retrieval of the unique non-repeating record from the zhangyan table:

SELECT DISTINCT username FROM zhangyan

5. Insert information into 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 zhangyan table

UPDATE zhangyan SET content = hello china WHERE username = abc

7. Delete the specified information in 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 to change the field type of the username field of zhangyan table to CHAR (25)

ALTER TABLE zhangyan CHANGE username username CHAR (25)

11. Import mysql.sql from the current directory into the database

SOURCE. / mysql.sql

4. SQL statement for database permission operation

1. Create a user sina with root permission and login from any IP with a password of zhangyan

GRANT ALL PRIVILEGES ON *. * TO sina@% IDENTIFIED BY zhangyan

2. Create a user sina with the permissions of "data operation" and "structure operation", and can only log in from 192.168.1.roomoperations * with a password of 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 sina who only has the permission of "data manipulation", can only log in from 192.168.1.24, and can only operate the zhangyan table of the rewin database, with a password of zhangyan.

GRANT SELECT, INSERT, UPDATE, DELETE ON rewin.zhangyan TO sina@192.168.1.24 IDENTIFIED BY zhangyan

4. Create a user sina with the permissions of "data operation" and "structure operation", which can log in from any IP and can only operate the rewin database. The password is 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@%

The SQL statement in 6.MySQL that replaces the string aaa with bbb in batch

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

7. Repair the damaged table

①, log in to MySQL from the command line with your root account:

Mysql-u root-p

②, enter the password of the root account.

③, selected database name (in this case, the database name is student):

Use student

④, repair the damaged table (in this case, the table to be repaired is smis_user_student):

Repair table smis_user_student;udent

The above is all the contents of the article "how to use the command line to manage MySQL database". Thank you for reading! I believe we all have a certain understanding, hope to share the content to help you, if you want to learn more knowledge, welcome to follow the industry information channel!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.