TAGENIGMA LLC

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

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

This example script shows you how to get started in web/database development by connecting an HTML page to an MS SQL database via VbScript.

<SCRIPT LANGUAGE="VBScript">

   Dim objConn
   Dim objRec
   Dim sIn
   Dim Query

   ' Put an SQL query into a string
   Query = "SELECT * FROM MyTable WHERE name LIKE 'target%'"

   ' This is our connection to the MS SQL Database
   strConnect = "Server=TESTING1;Database=MSSQLDB;UID=user;PWD=password"

   ' We access the database thru an ADO connection
   Set objConn = CreateObject ("ADODB.connection")

   ' This tells which provider / driver to use
   objConn.Provider="SQLOLEDB"

   ' Opens the database connection
   objConn.Open strConnect

   ' Creates a recordset to hold the data coming back from the query
   Set objRec = CreateObject ("ADODB.Recordset")

   ' Causes the query to execute
   objRec.Open Query, objConn

   ' Output field names
   document.write (objRec.Fields(0).Name)
   document.write (" ")
   document.write (objRec.Fields(1).Name)
   document.write (" ")
   document.write (objRec.Fields.Count)
   document.write ("<br />")

   ' Output each record
   do while not objRec.EOF
      document.write (objRec.Fields(0).Value)
      document.write (" ")
      document.write (objRec.Fields(1).Value)
      document.write ("<br />")
      objRec.MoveNext
   loop

   ' Close the recordset
   objRec.close
   set objRec = nothing

   ' Close the database connection
   objConn.close
   set objConn = nothing

</SCRIPT>

This example script shows you how to get started in web/database development by connecting an HTML page to an MS Access database via VbScript.

<SCRIPT LANGUAGE="VBScript">

   Dim objConn
   Dim objRec
   Dim strConnect
   Dim Query

   ' Put an SQL query into a string
   Query = "select * from orders;"

   ' Access file must reside locally. A URL won't work
   strConnect = "C:/My Documents/NorthWind.mdb"

   ' We access the database thru an ADO connection
   Set objConn = CreateObject ("ADODB.connection")

   ' This tells which provider / driver to use
   objConn.Provider="Microsoft.Jet.OLEDB.4.0"

   ' Opens the database connection
   objConn.Open strConnect

   ' Creates a recordset to hold the data coming back from the query
   Set objRec = CreateObject ("ADODB.Recordset")

   ' Causes the query to execute
   objRec.Open Query, objConn

   ' Output each record
   do while not objRec.EOF
      document.write (objRec.Fields(0).Value)
      document.write (" --->> ")
      document.write (objRec.Fields(1).Value)
      document.write ("<br />")
      objRec.MoveNext
   loop

   ' Close the recordset
   objRec.close
   set objRec = nothing

   ' Close the database connection
   objConn.close
   set objConn = nothing

</script>