Essential MySQL Scripts for Export, Import, and Database Management

Exporting Data

Export an entire database:

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

Export a single table:

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

Export only the database structure (no data) and add DROP statements before each CREATE:

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

Importing Data

Import a SQL script using the MySQL client:

mysql -u root -p
mysql> use dbname;
mysql> source dump.sql;

Alternatively, import with the mysql command directly:

mysql -u username -p dbname < dump.sql

MySQL Client Basics

Start the MySQL command‑line client with the password set during installation; the prompt appears as mysql>. Exit with quit or exit.

Database Management Commands

Create a database: create database dbname; List all databases: show databases; Delete a database: drop database dbname; Select a database: use dbname; (returns “Database changed”).

Show the current database: select database(); List tables in the current database:

show tables;

Table Management Commands

Create a table (example MyClass with several columns):

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)
);

Describe a table structure:

desc MyClass;
show columns from MyClass;

Delete a table: drop table MyClass; Insert rows:

insert into MyClass values (1,'Tom',0.45),(2,'Joan',0.99),(3,'Wang',0.59);

Select data (all rows, limited rows, ordered):

select * from MyClass;
select * from MyClass order by id limit 0,2;
select * from MyClass limit 0,2;

Delete specific rows: delete from MyClass where id = 1; Update rows: update MyClass set name='Mary' where id = 1; Add a new column:

alter table MyClass add passtest int(4) default '';

Rename a table:

rename table MyClass to YouClass;

Common Field Types

INT[(M)]

– standard integer. DOUBLE[(M,D)] [ZEROFILL] – floating‑point number. DATE – stored as YYYY‑MM‑DD. CHAR(M) – fixed‑length string, padded with spaces. VARCHAR(M) – variable‑length string. BLOB / TEXT – large binary or text data (max 2^16‑1 characters).

Data Loading and Modification

Create a .sql file, then create a database and import it:

mysqladmin -u root -p create auction;
mysql -u root -p auction < auction.sql;

Load data from a text file into a table:

LOAD DATA LOCAL INFILE 'D:/mysql.txt' INTO TABLE MYTABLE;

User Privilege Management

Grant privileges to a user:

grant select, insert, delete, create, drop on *.* to 'username'@'localhost' identified by 'password';

Grant specific privileges on a particular database:

grant select, insert, delete, update on test.* to 'testuser'@'localhost';

DDL Workflow Overview

Show existing databases: SHOW DATABASES; Create a new database: CREATE DATABASE MYSQLDATA; Select the database: USE MYSQLDATA; Show tables: SHOW TABLES; Create a table: CREATE TABLE MYTABLE (name VARCHAR(20), sex CHAR(1)); Describe the table: DESCRIBE MYTABLE; Insert a record: INSERT INTO MYTABLE VALUES ('hyq','M'); Load data from a file: LOAD DATA LOCAL INFILE 'D:/mysql.txt' INTO TABLE MYTABLE; Import a .sql script: source d:/mysql.sql; Delete a table: DROP TABLE MYTABLE; Empty a table: DELETE FROM MYTABLE; Update records:

UPDATE MYTABLE SET sex='f' WHERE name='hyq';