HowTo MySQL

The following are example commands common to doing database operations in MySQL. AnhD V Nguyen contributed this helpful guide.

-- Same as sysobjects in MS SQL
show tables;

-- Show all Database in MySQL
show databases;

-- Show table detail same as sp_help tblOrders
show columns from Orders;

-- Create Database
CREATE DATABASE Northwind;

-- Drop Database
Drop DATABASE mycs416;

-- Create table Categories
create table Categories
(
	CategoryID INT AUTO_INCREMENT PRIMARY KEY,
	CategoryName varchar (15),
	Description TEXT,
	Picture TEXT
);

-- Create table Customers
create table Customers
(
	CustomerID varchar (40) PRIMARY KEY,
	CompanyName varchar (40),
	ContactName varchar (30),
	ContactTitle varchar (30),
	Address varchar (60),
	City varchar (15),
	Region varchar (15),
	PostalCode varchar (10),
	Country varchar (15),
	Phone varchar (24),
	Fax varchar (24)
);


-- Create table Employees
create table Employees
(
	EmpoyeeID INT AUTO_INCREMENT PRIMARY KEY,
	LastName varchar (20),
	FirstName varchar (10),
	Title varchar (30),
	TitleOfCourtesy varchar (25),
	BirthDate date,
	HireDate date,
	Address varchar (60),
	City varchar (15),
	Region varchar (15),
	PostalCode varchar (10),
	Country varchar (15),
	HomePhone varchar (24),
	Extension varchar (4),
	Photo TEXT,
	Notes TEXT,
	ReportsTo Int
);

-- Create table Order Details
create table Order Details
(
	OrderID INT PRIMARY KEY,
	ProductID INT,
	UnitPrice Decimal(9,2),
	Quantity Integer,
	Discount INT
);


-- Create table Orders
create table Orders
(
	OrderID INT AUTO_INCREMENT PRIMARY KEY,
	CustomerID Text,	
	EmployeeID Int,
	OrderDate DateTime,
	ReqireDate DateTime,
	ShippedDate DateTime,
	ShipVia Int,
	Freight Decimal (9,2),
	ShipName Text,
	ShipAddress varchar (60),
	ShipCity varchar (15),
	ShipRegion varchar (15),
	ShippPostalCode varchar (10),
	ShipCountry varchar (15)
);


-- Create table Products
create table Products
(
	ProductID INT AUTO_INCREMENT PRIMARY KEY,
	ProductName Text,	
	SupplierID Int,
	CategoryID Int,
	QuantityPerUnit Text,
	UnitPrice Decimal (9,2),
	UnitsInStock Int,
	UnitsOnOrder Int,
	ReorderLevel Int,
	Discontinued varchar (3)
);

-- Create table Shippers
create table Shippers
(
	ShipperID INT AUTO_INCREMENT PRIMARY KEY,
	CompanyName varchar (40),	
	Phone varchar (24)
);


-- Create table Suppliers
create table Suppliers
(
	SupplierID INT AUTO_INCREMENT PRIMARY KEY,
	CompanyName varchar (40),	
	ContactName varchar (30),
	ContactTitile varchar (30),
	Address varchar (60),
	City varchar (15),
	Region varchar (15),
	PostalCode varchar (10),
	Country varchar (15),
	Phone varchar (24),
	Fax varchar (24),
	HomePage varchar (50)
);


-- Create table tblCust
create table tblCust
(
	CustomerID varchar (30) PRIMARY KEY,
	UserName varchar (30),	
	Pass varchar (30)
);

-------------------------------------------------------

/* Use the Alter table statement to add the 
   following constraints to (PK)Categories ---> (FK)Products:         	*/
        alter table Products
        add foreign key(CategoryID) 
        references Categories (CategoryID);

/* Use the Alter table statement to add the 
   following constraints to (PK)Supplies ---> (FK)Products:         	*/
        alter table Products
        add foreign key(SupplierID) 
        references Suppliers (SupplierID);

/* Use the Alter table statement to add the 
   following constraints to (PK)Products ---> (FK)OrderDetails:         */
        alter table OrderDetails
        add foreign key(ProductID) 
        references Products (ProductID);


/* Use the Alter table statement to add the 
   following constraints to (PK)Orders ---> (FK)OrderDetails:         	*/
        alter table OrderDetails
        add foreign key(OrderID) 
        references Orders (OrderID);

/* Use the Alter table statement to add the 
   following constraints to (PK)Orders ---> (FK)Employees:    	 	*/
        alter table Employees
        add foreign key(EmployeeID) 
        references Orders (EmployeeID);

/* Use the Alter table statement to add the 
   following constraints to (PK)Shippers ---> (FK)Orders:    	 	*/
        alter table Orders
        add foreign key(ShipperID) 
        references Shippers (ShipVia);


/* Use the Alter table statement to add the 
   following constraints to (PK)Customers ---> (FK)Orders:    	 	*/
        alter table Orders
        add foreign key(CustomerID) 
        references Customers (CustomerID);


/* Use the Alter table statement to add the 
   following constraints to (PK)tblCust ---> (FK)Customers:    	 	*/
        alter table Customers
        add foreign key(CustomerID) 
        references tblCust (CustomerID);

--=======================Load Data from Text File ========================

load data infile 'c:categories.txt' into table Categories(CategoryID,
CategoryName, Description, Picture);


load data infile 'c:suppliers.txt' into table Suppliers(
	CompanyName,	
	ContactName,
	ContactTitile,
	Address,
	City,
	Region,
	PostalCode,
	Country,
	Phone,
	Fax, HomePage);

load data infile 'c:Products.txt' into table Products(
	ProductName,	
	SupplierID,
	CategoryID,
	QuantityPerUnit,
	UnitPrice,
	UnitsInStock,
	UnitsOnOrder,
	ReorderLevel,
	Discontinued);


load data infile 'c:Employees.txt' into table Employees(
	LastName,
	FirstName,
	Title,
	TitleOfCourtesy,
	BirthDate,
	HireDate,
	Address,
	City,
	Region,
	PostalCode,
	Country,
	HomePhone,
	Extension,
	Photo,
	Notes,
	ReportsTo);

load data infile 'c:Shippers.txt' into table Shippers(
	CompanyName,	
	Phone);


load data infile 'c:Customers.txt' into table Customers(
	CustomerID,
	CompanyName,
	ContactName,
	ContactTitle,
	Address,
	City,
	Region,
	PostalCode,
	Country,
	Phone,
	Fax);


load data infile 'c:tblCust.txt' into table tblCust(
	CustomerID,
	UserName,	
	Pass);



load data infile 'c:Orders.txt' into table Orders(
	CustomerID,
	EmployeeID,
	OrderDate,
	ReqireDate,
	ShippedDate,
	ShipVia,
	Freight,
	ShipName,
	ShipAddress,
	ShipCity,
	ShipRegion,
	ShippPostalCode,
	ShipCountry);

--=============================================================
--Export to text file

select * into outfile 'c:order.txt' from orders;

-- Or

Select * from orders into outfile 'c:orders';

-- Or
select * INTO OUTFILE "c:/result.text" FIELDS TERMINATED BY ',' OPTIONALLY
ENCLOSED BY '"' LINES TERMINATED BY "n" FROM test_table;

-- MS SQL run from Dos-Prompt
bcp pubs..publishers out 'c:publishers.txt' -c -Sglamour -Usa -Ppassword

-- limit row_count OFFSET offset
select * from ordres limit 5,10; -- # Retrieve rows 6-15

select * from orders limit 95,-1; # Retrieve rows 96-last.

select * from orders LIMIT 5;     # Retrieve first 5 rows

--================================================================