Master MySQL: Essential Commands for Export, Import, and Database Management

Common MySQL Scripts

1. Export the whole database

mysqldump -u <username> -p --default-character-set=latin1 <database_name> > <output_file.sql>
mysqldump -u wcnc -p smgp_apps_wcnc > wcnc.sql

2. Export a single table

mysqldump -u <username> -p <database_name> <table_name> > <output_file.sql>
mysqldump -u wcnc -p smgp_apps_wcnc users > wcnc_users.sql

3. Export only the database structure

mysqldump -u wcnc -p -d --add-drop-table smgp_apps_wcnc > d:wcnc_db.sql
-d means no data; --add-drop-table adds a DROP TABLE before each CREATE.

4. Import a database

Method A – use the source command inside the MySQL console:

mysql -u root -p
mysql> use <database_name>;
mysql> source wcnc_db.sql;

Method B – pipe the dump file to mysql:

mysql -u <username> -p <database_name> < <filename.sql>

Method C – pipe the dump file to mysqldump (reverse direction):

mysqldump -u <username> -p <database_name> < <filename.sql>

Startup and Exit

Start MySQL Command Line Client and enter the password; the prompt becomes mysql>.

Exit with quit or exit.

Database Operations

-- Create a database
create database <database_name>;
-- Show all databases
show databases;
-- Drop a database
drop database <database_name>;
-- Use a database
use <database_name>;
-- Show current database
select database();
-- Show tables in the current database
show tables;

Table Operations (must select a database first)

-- Create a table
create table MyClass (
  id int not null primary key auto_increment,
  name char(20) not null,
  sex int(4) not null default '',
  degree double(16,2)
);
-- Describe a table
describe MyClass;
-- Drop a table
drop table MyClass;
-- Insert rows
insert into MyClass (id, name, sex, degree) values (1,'Tom',0.45),(2,'Joan',0.99),(3,'Wang',0.59);
-- Select all rows
select * from MyClass;
-- Select first N rows
select * from MyClass order by id limit 0,2;
-- Delete rows
delete from MyClass where id=1;
-- Update rows
update MyClass set name='Mary' where id=1;
-- Add a column
alter table MyClass add passtest int default '';
-- Rename a table
rename table MyClass to YouClass;
-- Update a column value
update MyClass set field_name = new_value where condition;
-- Update using replace()
update MyClass set field_name = replace(field_name,'old','new');
-- Concatenate content
update article set content = concat('　　',content);

Field Types Overview

INT[(M)] – integer

DOUBLE[(M,D)] [ZEROFILL] – floating‑point

DATE – stored as YYYY‑MM‑DD

CHAR(M) – fixed‑length string

BLOB/TEXT – up to 2^16‑1 characters

VARCHAR – variable‑length string

DDL Operations

-- Show databases
SHOW DATABASES;
-- Create a 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 table
DESCRIBE MYTABLE;
-- Insert a record
INSERT INTO MYTABLE VALUES ('hyq','M');
-- Load data from a text file
LOAD DATA LOCAL INFILE 'D:/mysql.txt' INTO TABLE MYTABLE;
-- Source an .sql file
SOURCE d:/mysql.sql;
-- Drop a table
DROP TABLE MYTABLE;
-- Delete all rows
DELETE FROM MYTABLE;
-- Update rows
UPDATE MYTABLE SET sex='f' WHERE name='hyq';

Granting Privileges

GRANT SELECT,INSERT,DELETE,CREATE,DROP ON *.* TO <username>@localhost IDENTIFIED BY 'password';
GRANT USAGE ON test.* TO testuser@localhost;
GRANT SELECT,INSERT,DELETE,UPDATE ON test.* TO testuser@localhost;

After granting, the user can connect from localhost and perform the specified operations on the chosen databases.