MySql Table

Create Database

Syntax For Creating Database Use Create Database Command

CREATE DATABASE Database Name
CREATE DATABASE  `shiksha360` ;

Create Table

MySQL CREATE TABLE syntax
In order to create a new table within a database, you use the MySQL CREATE TABLE statement. The CREATE TABLE statement is one of the most complex statement in MySQL.
The following illustrates the syntax of the CREATE TABLE statement in the simple form:

CREATE TABLE IF NOT EXISTS `emp` (
  `empid` int(11) NOT NULL auto_increment,
  `lastName` varchar(50) NOT NULL,
  `firstName` varchar(50) NOT NULL,
  `extension` varchar(10) NOT NULL,
  `email` varchar(100) NOT NULL,
  `officeCode` varchar(10) NOT NULL,
  `manager` int(11) default NULL,
  `jobTitle` varchar(50) NOT NULL,
  `salary` varchar(100) NOT NULL,
  PRIMARY KEY  (`empid`)
)

Insert Data Into Table

The MySQL INSERT statement is used to insert a single record or multiple records into a table in MySQL.

INSERT INTO `emp` (`empid`, `lastName`, `firstName`, `extension`, `email`, `officeCode`, `manager`, `jobTitle`, `salary`) VALUES
(1001, 'Murphy', 'Diane', 'x5800', 'abc@shiksha360.com', '1', NULL, 'President', '10000'),
(1002, 'Patterson', 'Mary', 'x4611', 'Patterson@shiksha360.com', '1', 1001, 'VP Sales', '10000'),
(1003, 'Firrelli', 'Jeff', 'x9273', 'Jeff@shiksha360.com', '1', 1002, 'VP Marketing', '12000'),
(1004, 'Patterson', 'William', 'x4871', 'William@shiksha360.com', '6', 1003, 'Sales Manager (APAC)', '13000'),
(1005, 'Bondur', 'Gerard', 'x5408', 'Gerard@shiksha360.com', '4', 1056, 'Sale Manager (EMEA)', '12000'),
(1006, 'Bow', 'Anthony', 'x5428', 'Anthony@shiksha360.com', '1', 1056, 'Sales Manager (NA)', '12000'),
(1007, 'Jennings', 'Leslie', 'x3291', 'Leslie@shiksha360.com', '1', 1143, 'Sales Rep', '24000'),
(1008, 'Thompson', 'Leslie', 'x4065', 'Leslie@shiksha360.com', '1', 1143, 'Sales Rep', '13000'),
(1009, 'Firrelli', 'Julie', 'x2173', 'Julie@shiksha360.com', '2', 1143, 'Sales Rep', '18000'),
(1010, 'Patterson', 'Steve', 'x4334', 'Steve@shiksha360.com', '2', 1143, 'Sales Rep', '12000'),
(1011, 'Tseng', 'Foon Yue', 'x2248', 'Foon Yue@shiksha360.com', '3', 1143, 'Sales Rep', '13000');
Write syntax to set empid as primary key in employee table

ALTER TABLE 
emp 
add PRIMARY KEY(empid)

Write syntax to drop primary key on employee table.

Alter TABLE emp 
drop CONSTRAINT empid;

Create Order Table

CREATE TABLE IF NOT EXISTS `orders` (
  `orderNumber` int(11) NOT NULL,
  `orderDate` date NOT NULL,
  `requiredDate` date NOT NULL,
  `shippedDate` date default NULL,
  `status` varchar(15) NOT NULL,
  `comments` text,
  `customerNumber` int(11) NOT NULL,
  PRIMARY KEY  (`orderNumber`),
  KEY `customerNumber` (`customerNumber`)
);
Insert Values Into Order Table

INSERT INTO `orders` (`orderNumber`, `orderDate`, `requiredDate`, `shippedDate`, `status`, `comments`, `customerNumber`) VALUES
(10100, '2003-01-06', '2003-01-13', '2003-01-10', 'Shipped', NULL, 363),
(10101, '2003-01-09', '2003-01-18', '2003-01-11', 'Shipped', 'Check on availability.', 128),
(10102, '2003-01-10', '2003-01-18', '2003-01-14', 'Shipped', NULL, 181),
(10103, '2003-01-29', '2003-02-07', '2003-02-02', 'Shipped', NULL, 121),
(10104, '2003-01-31', '2003-02-09', '2003-02-01', 'Shipped', NULL, 141),
(10105, '2003-02-11', '2003-02-21', '2003-02-12', 'Shipped', NULL, 145),
(10106, '2003-02-17', '2003-02-24', '2003-02-21', 'Shipped', NULL, 278),
(10107, '2003-02-24', '2003-03-03', '2003-02-26', 'Shipped', 'Difficult to negotiate with customer. We need more marketing materials', 131),
(10108, '2003-03-03', '2003-03-12', '2003-03-08', 'Shipped', NULL, 385),
(10109, '2003-03-10', '2003-03-19', '2003-03-11', 'Shipped', 'Customer requested that FedEx Ground is used for this shipping', 486),
(10110, '2003-03-18', '2003-03-24', '2003-03-20', 'Shipped', NULL, 187),
(10425, '2005-05-31', '2005-06-07', NULL, 'In Process', NULL, 119);
Add Referencial Key into Table

ALTER TABLE `orders`
  ADD CONSTRAINT `orders_ibfk_1` FOREIGN KEY (`customerNumber`) REFERENCES `customers` (`customerNumber`);

Create Orderdetails Table

CREATE TABLE IF NOT EXISTS `orderdetails` (
  `orderNumber` int(11) NOT NULL,
  `productCode` varchar(15) NOT NULL,
  `quantityOrdered` int(11) NOT NULL,
  `priceEach` double NOT NULL,
  `orderLineNumber` smallint(6) NOT NULL,
  PRIMARY KEY  (`orderNumber`,`productCode`),
  KEY `productCode` (`productCode`)
);
INSERT INTO `orderdetails` (`orderNumber`, `productCode`, `quantityOrdered`, `priceEach`, `orderLineNumber`) VALUES
(10100, 'S18_1749', 30, 136, 3),
(10100, 'S18_2248', 50, 55.09, 2),
(10100, 'S18_4409', 22, 75.46, 4),
(10100, 'S24_3969', 49, 35.29, 1),
(10101, 'S18_2325', 25, 108.06, 4),
(10101, 'S18_2795', 26, 167.06, 1),
(10101, 'S24_1937', 45, 32.53, 3),
(10101, 'S24_2022', 46, 44.35, 2),
(10102, 'S18_1342', 39, 95.55, 2),
(10102, 'S18_1367', 41, 43.13, 1),
(10106, 'S18_2581', 34, 81.1, 2);
Add Referencial Key Into Table

ALTER TABLE `orderdetails`
  ADD CONSTRAINT `orderdetails_ibfk_1` FOREIGN KEY (`orderNumber`) REFERENCES `orders` (`orderNumber`),
  ADD CONSTRAINT `orderdetails_ibfk_2` FOREIGN KEY (`productCode`) REFERENCES `products` (`productCode`);

Create Customer Table

CREATE TABLE IF NOT EXISTS `customers` (
  `customerNumber` int(11) NOT NULL,
  `customerName` varchar(50) NOT NULL,
  `contactLastName` varchar(50) NOT NULL,
  `contactFirstName` varchar(50) NOT NULL,
  `phone` varchar(50) NOT NULL,
  `addressLine1` varchar(50) NOT NULL,
  `addressLine2` varchar(50) default NULL,
  `city` varchar(50) NOT NULL,
  `state` varchar(50) default NULL,
  `postalCode` varchar(15) default NULL,
  `country` varchar(50) NOT NULL,
  `salesRepEmployeeNumber` int(11) default NULL,
  `creditLimit` double default NULL,
  PRIMARY KEY  (`customerNumber`),
  KEY `salesRepEmployeeNumber` (`salesRepEmployeeNumber`)
) 
Insert Value into Customer Table

INSERT INTO `customers` (`customerNumber`, `customerName`, `contactLastName`, `contactFirstName`, `phone`, `addressLine1`, `addressLine2`, `city`, `state`, `postalCode`, `country`, `salesRepEmployeeNumber`, `creditLimit`) VALUES
(103, 'Atelier graphique', 'Schmitt', 'Carine ', '40.32.2555', '54, rue Royale', NULL, 'Nantes', NULL, '44000', 'France', 1370, 21000),
(112, 'Signal Gift Stores', 'King', 'Jean', '7025551838', '8489 Strong St.', NULL, 'Las Vegas', 'NV', '83030', 'USA', 1166, 71800),
(114, 'Australian Collectors, Co.', 'Ferguson', 'Peter', '03 9520 4555', '636 St Kilda Road', 'Level 3', 'Melbourne', 'Victoria', '3004', 'Australia', 1611, 117300),
(119, 'La Rochelle Gifts', 'Labrune', 'Janine ', '40.67.8555', '67, rue des Cinquante Otages', NULL, 'Nantes', NULL, '44000', 'France', 1370, 118200),
(121, 'Baane Mini Imports', 'Bergulfsen', 'Jonas ', '07-98 9555', 'Erling Skakkes gate 78', NULL, 'Stavern', NULL, '4110', 'Norway', 1504, 81700),
(124, 'Mini Gifts Distributors Ltd.', 'Nelson', 'Susan', '4155551450', '5677 Strong St.', NULL, 'San Rafael', 'CA', '97562', 'USA', 1165, 210500),
(125, 'Havel & Zbyszek Co', 'Piestrzeniewicz', 'Zbyszek ', '(26) 642-7555', 'ul. Filtrowa 68', NULL, 'Warszawa', NULL, '01-012', 'Poland', NULL, 0),
(128, 'Blauer See Auto, Co.', 'Keitel', 'Roland', '+49 69 66 90 2555', 'Lyonerstr. 34', NULL, 'Frankfurt', NULL, '60528', 'Germany', 1504, 59700),
(129, 'Mini Wheels Co.', 'Murphy', 'Julie', '6505555787', '5557 North Pendale Street', NULL, 'San Francisco', 'CA', '94217', 'USA', 1165, 64600),
(131, 'Land of Toys Inc.', 'Lee', 'Kwai', '2125557818', '897 Long Airport Avenue', NULL, 'NYC', 'NY', '10022', 'USA', 1323, 114900),
(151, 'Muscle Machine Inc', 'Young', 'Jeff', '2125557413', '4092 Furth Circle', 'Suite 400', 'NYC', 'NY', '10022', 'USA', 1286, 138500);

Admin has written 171 articles

Leave a Reply