Common MySQL Commands and Operations Guide

This guide lists frequently used MySQL command-line operations, organized by purpose.

1. Export an entire 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 (DDL)

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

mysql -u root -p
use
database_name
;
source
script.sql
;
# alternative using mysqldump output
mysqldump -u username -p dbname < filename.sql
mysql -u username -p -D dbname < filename.sql

5. Start and exit MySQL client

# Start
mysql -u
user
-p
# Exit
quit
exit

6. Basic database operations

create database
mydb
;
show databases;
drop database
mydb
;
use
mydb
;
select database();
show tables;

7. Table operations

# Create table
create table
MyClass
(
  id int(4) not null primary key auto_increment,
  name char(20) not null,
  sex int(4) not null default '',
  degree double(16,2)
);
# Describe table
desc MyClass;
show columns from MyClass;
# Drop table
drop table MyClass;
# Insert data
insert into MyClass (id, name, sex, degree) values (1,'Tom',96.45),(2,'Joan',82.99),(3,'Wang',96.59);
# Select data
select * from MyClass;
select * from MyClass order by id limit 0,2;
# Delete data
delete from MyClass where id=1;
# Update data
update MyClass set name='Mary' where id=1;

8. Alter table – add column or rename

# Add column
alter table MyClass add passtest int(4) default '';
# Rename table
rename table MyClass to YouClass;

9. Field types reference

# Common MySQL data types
INT[(M)]          -- integer
DOUBLE[(M,D)]     -- floating point
DATE              -- date in YYYY-MM-DD
CHAR(M)           -- fixed‑length string
VARCHAR(M)        -- variable‑length string
BLOB/TEXT         -- large text or binary

10. Importing data from files

# Load data from a local file
LOAD DATA LOCAL INFILE "D:/mysql.txt" INTO TABLE MyTable;
# Execute a .sql script
source D:/mysql.sql;

11. DDL workflow examples

# Show existing databases
SHOW DATABASES;
# Create a new database
CREATE DATABASE MYSQLDATA;
# Select it
USE MYSQLDATA;
# Show tables in the current database
SHOW TABLES;
# Create a table
CREATE TABLE MYTABLE (name VARCHAR(20), sex CHAR(1));
# Describe the table
DESCRIBE MYTABLE;
# Insert a row
INSERT INTO MYTABLE VALUES ("hyq","M");
# Delete the table
DROP TABLE MYTABLE;
# Clear all rows
DELETE FROM MYTABLE;
# Update rows
UPDATE MYTABLE SET sex="f" WHERE name='hyq';

12. Managing user privileges

# Grant basic privileges to a user
GRANT SELECT,INSERT,DELETE,CREATE,DROP ON *.* TO
username
@localhost IDENTIFIED BY 'password';
# Grant usage on a specific database
GRANT USAGE ON test.* TO testuser@localhost;
# Grant DML privileges on a specific database
GRANT SELECT,INSERT,DELETE,UPDATE ON test.* TO testuser@localhost;