Essential MySQL Scripts: Export, Import, and Manage Databases & Tables

Common MySQL Scripts

This document provides ready‑to‑use MySQL command‑line snippets for routine database tasks.

1. Exporting Data

Export entire database :

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

Export a single table :

mysqldump -u <em>username</em> -p <em>database_name</em> <em>table_name</em> > <em>output_file.sql</em>

Export only the database structure (no data) :

mysqldump -u <em>wcnc</em> -p -d --add-drop-table <em>smgp_apps_wcnc</em> > d:wcnc_db.sql

2. Importing Data

Import a .sql file using the MySQL client:

mysql -u <em>username</em> -p <em>database_name</em> < <em>file.sql</em>

Source a script inside MySQL :

source <em>file.sql</em>

3. Starting and Exiting MySQL

Start the MySQL command‑line client and log in with the password; the prompt becomes mysql>. Exit with quit or exit.

4. Database Operations

Create a database: create database <em>dbname</em>; Show all databases: show databases; Drop a database: drop database <em>dbname</em>; Use a database: use <em>dbname</em>; Show current database:

select database();

5. Table Operations (must select a database first)

Create a table:

create table <em>MyClass</em> (
  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: desc <em>MyClass</em>; or show columns from <em>MyClass</em>; Drop a table: drop table <em>MyClass</em>; Insert rows:

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

Select data: select * from <em>MyClass</em>; or with limit: select * from <em>MyClass</em> order by id limit 0,2; Delete rows: delete from <em>MyClass</em> where id=1; Update rows: update <em>MyClass</em> set name='Mary' where id=1; Add a column: alter table <em>MyClass</em> add passtest int(4) default ''; Rename a table: rename table <em>MyClass</em> to <em>YouClass</em>; Rename a column or modify its content using alter table or update ... set column=replace(column,'old','new').

6. Field Types Overview

INT[(M)] – integer

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

DATE – format YYYY‑MM‑DD

CHAR(M) – fixed‑length string

VARCHAR – variable‑length string

BLOB/TEXT – large binary or text data

7. Importing Tables and Data Files

Create a database and a table, then load data from a text file:

load data local infile 'D:/mysql.txt' into table <em>MYTABLE</em>;

Execute a .sql script:

source d:/mysql.sql;

8. Granting Permissions

Grant specific privileges to a user:

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

Grant usage and then specific rights on a database:

grant usage on test.* to testuser@localhost;

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

9. DDL Operations Summary

Show databases: show databases; Create a database: create database MYSQLDATA; Use a database: use MYSQLDATA; Show tables: show tables; Create a table: create table MYTABLE (name varchar(20), sex char(1)); Describe a 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; Source a .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';