DROP DATABASE IF EXISTS `practice`;
#
# Current Database: `practice`
#
CREATE DATABASE `practice`;
#
USE `practice`;
#
#
# Table structure for table `ac_basic`
#
#
DROP TABLE IF EXISTS `ac_basic`;
#
CREATE TABLE `ac_basic` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `surname` varchar(30) NOT NULL,
  `given` varchar(30) NOT NULL,
  `email` varchar(120) NOT NULL,
  `password` varbinary(18) NOT NULL,
  `nick` varchar(30) DEFAULT NULL,
  `district` tinyint(4) DEFAULT NULL,
  `affect` tinyint(4) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `email` (`email`),
  KEY `password` (`password`),
  KEY `nick` (`nick`),
  KEY `district` (`district`),
  KEY `affect` (`affect`),
  CONSTRAINT `affect` FOREIGN KEY (`affect`) REFERENCES `status_affect` (`id`),
  CONSTRAINT `district` FOREIGN KEY (`district`) REFERENCES `district_tw` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=19 DEFAULT CHARSET=utf8;
#
#
# Dumping data for table `ac_basic`
#
#
INSERT INTO `ac_basic` VALUES (1,'林','火旺','peter.lin@yadoo.kom','1111','阿旺',10,1),(2,'張','火旺','paul.zhang@goople.kom','1111','旺旺',1,1),(3,'陳','火旺','bob.c@goople.kom','1111','阿火',4,3),(4,'李','火旺','paul.li@alinana.kom','1111','阿旺',4,1),(5,'張','雅婷','peggy.zhang@alinana.kom','1111','婷婷',18,1),(6,'吳','雅婷','grace.wu@goople.kom','1111','雅雅',5,4),(7,'郭','雅婷','nancy.guo@example.kom','1111','小婷',1,1),(8,'張','雅婷','amy.zhang@yadoo.kom','1111','婷婷',2,2),(9,'吳','雅惠','lucy.w@goople.kom','1111','小惠',4,NULL),(10,'楊','雅惠','sara.yang@example.kom','1111','阿惠',4,NULL),(11,'楊','雅惠','grace.yang@goople.kom','1111','阿惠',NULL,1),(12,'李','志豪','jack.li@yadoo.kom','1111','豪哥',5,NULL),(13,'吳','志豪','bob.wu@example.kom','1111','大豪',NULL,1),(14,'高','俊宏','paul.gao@alinana.kom','1111','阿宏',NULL,2),(15,'方','俊宏','jay.fang@goople.kom','1111','俊俊',NULL,1),(16,'黃','淑芬','amy.h@alinana.kom','1111','小芬',2,NULL),(17,'張','淑芬','peggy.zhang@yadoo.kom','1111','淑芬',NULL,1),(18,'黃','淑芬','grace.huang@goople.kom','1111','淑芬',2,NULL);
#
#
# Table structure for table `district_tw`
#
#
DROP TABLE IF EXISTS `district_tw`;
#
CREATE TABLE `district_tw` (
  `id` tinyint(4) NOT NULL AUTO_INCREMENT,
  `district` varchar(50) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `district` (`district`)
) ENGINE=InnoDB AUTO_INCREMENT=23 DEFAULT CHARSET=utf8;
#
#
# Dumping data for table `district_tw`
#
#
INSERT INTO `district_tw` VALUES (10,'南投縣'),(8,'台中市'),(1,'台北市'),(14,'台南市'),(19,'台東縣'),(11,'嘉義市'),(12,'嘉義縣'),(3,'基隆市'),(17,'宜蘭縣'),(16,'屏東縣'),(9,'彰化縣'),(2,'新北市'),(5,'新竹市'),(6,'新竹縣'),(4,'桃園縣'),(20,'澎湖縣'),(18,'花蓮縣'),(7,'苗栗縣'),(22,'連江縣'),(21,'金門縣'),(13,'雲林縣'),(15,'高雄市');
#
#
# Table structure for table `status_affect`
#
#
DROP TABLE IF EXISTS `status_affect`;
#
CREATE TABLE `status_affect` (
  `id` tinyint(4) NOT NULL AUTO_INCREMENT,
  `affect` varchar(10) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `affect` (`affect`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8;
#
#
# Dumping data for table `status_affect`
#
#
INSERT INTO `status_affect` VALUES (1,'單身'),(2,'已婚'),(3,'穩定交往中'),(4,'離婚');
#
# completed.