MySQL User Management, Database and Table Operations Guide

Connecting to MySQL: use mysql -h host -u username -p (or mysql -h 127.0.0.1 -u root -p ) to open a command‑line session; exit with exit .

Creating users: CREATE USER 'username'@'host' IDENTIFIED BY 'password'; where host can be localhost or % for any host.

Granting privileges: GRANT privileges ON databasename.tablename TO 'username'@'host'; Use ALL for full rights and add WITH GRANT OPTION if the user should be able to grant privileges to others.

Changing passwords: SET PASSWORD FOR 'username'@'host' = PASSWORD('newpassword'); or for the current user SET PASSWORD = PASSWORD('newpassword'); .

Revoking privileges: REVOKE privilege ON databasename.tablename FROM 'username'@'host'; Use SHOW GRANTS FOR 'username'@'host'; to view current grants.

Deleting users: DROP USER 'username'@'host'; .

Database operations: SHOW DATABASES; CREATE DATABASE dbname; DROP DATABASE dbname; USE dbname;

Table operations: SHOW TABLES; CREATE TABLE tablename (column definitions); DROP TABLE tablename; DESCRIBE tablename;

Copying tables: CREATE TABLE new_table LIKE old_table; or CREATE TABLE new_table SELECT * FROM old_table; .

Inserting data from one table to another: INSERT INTO target SELECT * FROM source; .

Backup a database: run mysqldump -u username -p dbname > backup.sql from the MySQL bin directory.

Restore a database: create the target database, then execute source /path/to/backup.sql inside the MySQL client.

Data manipulation: DELETE FROM tablename; (removes all rows) or TRUNCATE TABLE tablename; (faster, no log). Use DELETE FROM tablename WHERE condition; to remove specific rows.

Altering tables: rename column ALTER TABLE tbl CHANGE oldcol newcol datatype; , rename table ALTER TABLE old_name RENAME TO new_name; , modify column type ALTER TABLE tbl MODIFY col datatype; , add column ALTER TABLE tbl ADD COLUMN col datatype DEFAULT ''; , drop column ALTER TABLE tbl DROP COLUMN col; , add primary key ALTER TABLE tbl ADD COLUMN id INT AUTO_INCREMENT PRIMARY KEY; .

Exporting data: SELECT columns FROM table INTO OUTFILE '/path/file.txt';

Importing data: LOAD DATA INFILE '/path/file.txt' INTO TABLE table;

Query examples: SELECT * FROM tbl WHERE column > 100; , string matching with LIKE , date queries, logical operators ( AND OR NOT ), limiting results with LIMIT , ordering with ORDER BY , grouping with GROUP BY , using aggregate functions like SUM() , removing duplicates with DISTINCT , and combining conditions with BETWEEN , IS NULL , NOT IN .

Author: Lin Bingwen – source: http://blog.csdn.net/evankaka/article/details/45580845