MySQL Common operations

Tag: Key equals bin name 127.0.0.1 database name Mail prim

One, log in to the MySQL database server from the command line

1. Log in with MySQL using the default 3306 port

/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. Show what database is currently on the server

SHOW DATABASES;

2. Create a database named Dbtest

CREATE DATABASE dbtest;

3. Delete the database named Dbtest

DROP DATABASE dbtest;

4. Enter the Dbtest database

Use dbtest;

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

Third, table operation SQL statement (after login must use the above command to select a database, and then table operation)

1. Show what table exists in the current database

SHOW TABLES;

2. Create database table User: Paste the following SQL statement after mysql>, the storage engine is MyISAM, the field ID is the primary key, and the unique index.

CREATE TABLE ' user ' (' ID ' INT (5) UNSIGNED not NULL auto_increment, ' username ' VARCHAR "not null, ' password ' CHAR (+) Not NULL, ' time ' DATETIME is not null, ' number ' FLOAT (TEN) not null, ' content ' TEXT is not null, PRIMARY KEY (' id ') ) ENGINE = MYISAM;

3. View the user table structure

DESCRIBE user;

4. Retrieving information from the table 4.1, retrieving all records from the user table

SELECT * from user;

4.2. Retrieving specific rows from the User table: Field username equals ABC, field number equals 1, sorted by field ID Descending

SELECT * FROM user WHERE username = abc and number=1 ORDER by ID DESC;

4.3. Retrieve the specified field from the User table: username and password

SELECT username, password from user;

4.4. Retrieve unique non-duplicate records from the user table:

SELECT DISTINCT username from user;

5. Inserting information into user table

INSERT into user (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 user table

UPDATE user SET content = Hello China WHERE username = ABC;

7. Delete the specified information from the user table

DELETE from user WHERE id = 1;

8. Clear User Table

DELETE from user;

9. Delete User table

DROP TABLE User;

10, change the table structure, changes the field type of the User table username field to char (25)

ALTER TABLE User change username username CHAR (25);

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

SOURCE./mysql.sql;

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

IV. Database Permissions Operations SQL statements

1, create a user with root privileges, can login from any IP Sina, password is Zhangyan

GRANT all privileges on * * to [email protected]% identified by Zhangyan;

2, create a "data operation", "Structure operation" permission, only from 192.168.1.*** login user sina, 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 [E- Mail protected]% identified by Zhangyan;

3, create a only "data operation" permission, only from 192.168.1.24 login, can only operate Rewin database Zhangyan table users

Sina, password is Zhangyan

GRANT SELECT, INSERT, UPDATE, DELETE on Rewin.zhangyan to [email protected] identified by

Zhangyan;

4, create a "data operation", "Structure operation" permission, can log in from any IP, only operate Rewin database user Sina, password for

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

[email protected]% identified by Zhangyan;

5. Delete users

DROP USER [email protected]%;

6.MySQL Replace string AAA batch with BBB SQL statement

UPDATE table name SET field name = REPLACE (field name, AAA, BBB);

7. Repairing a damaged table

①, log in from the command line with the root account mysql:mysql-u root-p

②, enter the password for the root account.

③, selected database name (the database name in this example is student): Use student;

④, repairing the damaged table (the table to be repaired in this example is Smis_user_student): Repair table smis_user_student;udent;

MySQL Common operations