Common operations on Mysql command line

1. Log in to the MySQL database server from the command line 1. Log in to MySQL using the default port 3306

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

2. Manage multiple MySQLs with different ports through TCP connections (Note: This function is only available in MySQL 4.1 or above)

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

3. Manage multiple MySQLs of different ports through sockets

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

4. Manage different ports through ports and IPs Multiple MySQL

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

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

2. Database operation SQL statement 1. Show what databases currently exist 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 the rewin database

USE rewin;

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

3. Table operation SQL statements (after logging in, you must use the above USE command to select a database and then perform table operations) 1. Display 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, 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 zhangyan table structure

DESCRIBE zhangyan;

4. Retrieve information from the table 4.1. Retrieve all records from zhangyan table

SELECT * FROM zhangyan;

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

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 the unique and non-duplicate fields from the zhangyan table Record:

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 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 username field of the zhangyan table Change the type to CHAR(25)

ALTER TABLE zhangyan CHANGE username username CHAR(25);

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

SOURCE ./mysql.sql;

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

4. Database permission operation SQL statement 1. Create a user sina with root permissions and can log in from any IP , the password is zhangyan

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

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

sina , 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 "data operation" and "structure operation" permissions, which can be obtained from Any IP login can only operate the rewin database user sina, 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 user

DROP USER sina@%;

6. MySQL SQL to replace string aaa with bbb in batches Statement

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 the root account: 　　 mysql -u root -p

　 ②. Enter The password of the root account.

　 ③ Select the database name (the database name in this example is student): use student;

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