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

Editor to share with you an example of Mysql command line login to common operating systems, I believe that most people do not know much about it, so share this article for your reference, I hope you can learn a lot after reading this article, let's learn about it!

Log in to the MySQL database server from the command line

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

/ usr/local/mysql/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 table SHOW TABLES exists in the current database

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 table 4.1, retrieve all records from 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. Database permissions operate SQL statement 1. Create a user sina with root permission and can log in 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 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.

Sina, password is zhangyan

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

Zhangyan

4. Create a user sina who has the permissions of "data operation" and "structure operation", can log in from any IP and can only operate the rewin database with a password of

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 ① and log in to MySQL: mysql-u root-p from the command line with your root account

②, enter the password of the root account.

③, selected database name (in this case, the database is called 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 this article entitled "examples of Mysql command line login to common operating systems". 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.