Saturday, 20 August 2016

MYSQL in UBUNTU install and create DB


MYSQL in UBUNTU

Installation run cmds:
$ sudo apt-get install mysql-server
$ sudo apt-get install mysql-client

connect mysql:
$mysql –uroot –p
Enter password: root
Shows MYSQL command prompt
mysql> use retail_db;
Database changed
Execute MYSQL script in database:
$ mysql  -uroot –p <database name> < scriptfile.sql
mysql> show tables;
+---------------------+
| Tables_in_retail_db |
+---------------------+
| customers           |
| departments         |
+---------------------+
2 rows in set (0.00 sec)

mysql> select * from departments;
+---------------+-----------------+
| department_id | department_name |
+---------------+-----------------+
|             2 | Fitness         |
|             3 | Footwear        |
|             4 | Apparel         |
|             5 | Golf            |
|             6 | Outdoors        |
|             7 | Fan Shop        |
+---------------+-----------------+
6 rows in set (0.02 sec)



scriptfile.sql code and data:

DROP TABLE IF EXISTS `customers`;

CREATE TABLE `customers` (
  `customer_id` int(11) NOT NULL AUTO_INCREMENT,
  `customer_fname` varchar(45) NOT NULL,
  `customer_lname` varchar(45) NOT NULL,
  `customer_email` varchar(45) NOT NULL,
  `customer_password` varchar(45) NOT NULL,
  `customer_street` varchar(255) NOT NULL,
  `customer_city` varchar(45) NOT NULL,
  `customer_state` varchar(45) NOT NULL,
  `customer_zipcode` varchar(45) NOT NULL,
  PRIMARY KEY (`customer_id`)
)


DROP TABLE IF EXISTS `departments`;

CREATE TABLE `departments` (
  `department_id` int(11) NOT NULL AUTO_INCREMENT,
  `department_name` varchar(45) NOT NULL,
  PRIMARY KEY (`department_id`)
) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8;


--
-- Dumping data for table `departments`
--

LOCK TABLES `departments` WRITE;

INSERT INTO `departments` VALUES (2,'Fitness'),(3,'Footwear'),(4,'Apparel'),(5,'Golf'),(6,'Outdoors'),(7,'Fan Shop');

UNLOCK TABLES;

No comments:

Post a Comment