Comprehensive MySQL Database Operations: Export, Import, DDL, DML, and User Management

This article presents a complete reference for MySQL database management, covering data export, import, and routine database and table operations.

Exporting data

1. Export an entire database:

mysqldump -u username -p --default-character-set=latin1 dbname > dump.sql

2. Export a single table:

mysqldump -u username -p dbname tablename > table_dump.sql

3. Export only the database structure (no data):

mysqldump -u username -p -d --add-drop-table dbname > schema.sql

Importing data

Use the MySQL client to execute a script file:

mysql -u username -p dbname < dump.sql

Or, after logging into the MySQL console, run:

source dump.sql

Starting and exiting MySQL

mysql -u username -p   # start
quit;   # exit

Database operations

CREATE DATABASE dbname;

SHOW DATABASES;

DROP DATABASE dbname;

USE dbname;

SELECT DATABASE();

Table operations

CREATE TABLE MyClass (id INT NOT NULL PRIMARY KEY AUTO_INCREMENT, name CHAR(20) NOT NULL, sex INT NOT NULL DEFAULT '', degree DOUBLE(16,2));

DESC MyClass;   # or SHOW COLUMNS FROM MyClass;

DROP TABLE MyClass;

INSERT INTO MyClass (id, name, degree) VALUES (1, 'Tom', 0.45), (2, 'Joan', 0.99), (3, 'Wang', 0.5);

SELECT * FROM MyClass;

SELECT * FROM MyClass ORDER BY id LIMIT 0,2;

DELETE FROM MyClass WHERE id = 1;

UPDATE MyClass SET name='Mary' WHERE id = 1;

ALTER TABLE MyClass ADD passtest INT(4) DEFAULT '';

RENAME TABLE MyClass TO YouClass;

Field types reference

INT[(M)], DOUBLE[(M,D)], DATE, CHAR(M), VARCHAR(M), BLOB/TEXT

DDL workflow example

CREATE DATABASE auction;
USE auction;
CREATE TABLE auction (id INT AUTO_INCREMENT PRIMARY KEY, item VARCHAR(100), price DOUBLE);
INSERT INTO auction (item, price) VALUES ('Painting', 1500.00);
SHOW TABLES;
DESCRIBE auction;

User privileges

GRANT SELECT, INSERT, DELETE, CREATE, DROP ON *.* TO 'testuser'@'localhost' IDENTIFIED BY 'password';
FLUSH PRIVILEGES;

These commands together provide a practical toolkit for managing MySQL databases in development and production environments.