Overview of common operating systems for Mysql command line login

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 MySQL4.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 multiple MySQLs with different ports through ports and IPs

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

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

2. Database operation SQL statements

1. Display what databases currently exist on the server

SHOW DATABASES;

2. Create a name For the rewin database

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. 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, 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 the zhangyan table

SELECT * FROM zhangyan;

4.2. Retrieve specific rows from the zhangyan table : Field username is equal to abc, field number is equal to 1, sorted in descending order by field id

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

4.3, from zhangyan table Retrieve the specified fields in: username and password

SELECT username, password FROM zhangyan;

4.4. Retrieve the only unique record 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 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 of the zhangyan table 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 has 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 can only be logged in from 192.168.1.*** 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

sina 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. 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. You can log in from any IP and can only operate the user sina of 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 user

DROP USER sina@%;

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

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;

Related articles:

Mysql command Commonly used operations

Summary of common command line operations in Mysql

Related videos:

Cheetah.com MySQL video tutorial

The above is the detailed content of Overview of common operating systems for Mysql command line login. For more information, please follow other related articles on the PHP Chinese website!