sql练习表

-- MySQL dump 10.13  Distrib 5.1.55, for Win32 (ia32)
--
-- Host: localhost    Database: xsk
-- ------------------------------------------------------
-- Server version    5.1.55-community

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;

--
-- Current Database: `xsk`
--

CREATE DATABASE /*!32312 IF NOT EXISTS*/ `xsk` /*!40100 DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci */;

USE `xsk`;

--
-- Table structure for table `cjb`
--

DROP TABLE IF EXISTS `cjb`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `cjb` (
  `xh` char(10) COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
  `kch` char(10) COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
  `cj` float(8,2) NOT NULL,
  PRIMARY KEY (`xh`,`kch`),
  KEY `kch` (`kch`),
  CONSTRAINT `cjb_ibfk_1` FOREIGN KEY (`xh`) REFERENCES `xsb` (`xh`),
  CONSTRAINT `cjb_ibfk_2` FOREIGN KEY (`kch`) REFERENCES `kcb` (`kch`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `cjb`
--

LOCK TABLES `cjb` WRITE;
/*!40000 ALTER TABLE `cjb` DISABLE KEYS */;
INSERT INTO `cjb` VALUES ('001','1',78.00),('001','2',65.00),('001','3',45.00),('001','4',89.00),('001','5',95.00),('002','3',78.00),('002','5',66.00),('002','6',74.00),('002','7',85.00),('002','8',78.00),('003','2',65.00),('003','3',73.00),('003','4',56.00),('003','5',89.00),('003','6',91.00),('003','7',64.00);
/*!40000 ALTER TABLE `cjb` ENABLE KEYS */;
UNLOCK TABLES;

--
-- Table structure for table `cjb1`
--

DROP TABLE IF EXISTS `cjb1`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `cjb1` (
  `xh` char(10) COLLATE utf8_unicode_ci DEFAULT NULL,
  `kch` char(10) COLLATE utf8_unicode_ci DEFAULT NULL,
  `cj` float(8,2) NOT NULL,
  `id` int(11) NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`id`),
  KEY `xh` (`xh`),
  KEY `kch` (`kch`),
  CONSTRAINT `cjb1_ibfk_1` FOREIGN KEY (`xh`) REFERENCES `xsb` (`xh`),
  CONSTRAINT `cjb1_ibfk_2` FOREIGN KEY (`kch`) REFERENCES `kcb` (`kch`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `cjb1`
--

LOCK TABLES `cjb1` WRITE;
/*!40000 ALTER TABLE `cjb1` DISABLE KEYS */;
/*!40000 ALTER TABLE `cjb1` ENABLE KEYS */;
UNLOCK TABLES;

--
-- Table structure for table `kcb`
--

DROP TABLE IF EXISTS `kcb`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `kcb` (
  `kch` char(10) COLLATE utf8_unicode_ci NOT NULL,
  `kcm` varchar(20) COLLATE utf8_unicode_ci NOT NULL,
  PRIMARY KEY (`kch`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `kcb`
--

LOCK TABLES `kcb` WRITE;
/*!40000 ALTER TABLE `kcb` DISABLE KEYS */;
INSERT INTO `kcb` VALUES ('1','C语言'),('2','测试基础'),('3','缺陷管理'),('4','测试方法'),('5','测试过程'),('6','软件质量'),('7','SVN'),('8','Linux'),('9','Mysql');
/*!40000 ALTER TABLE `kcb` ENABLE KEYS */;
UNLOCK TABLES;

--
-- Table structure for table `xsb`
--

DROP TABLE IF EXISTS `xsb`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `xsb` (
  `xh` char(10) COLLATE utf8_unicode_ci NOT NULL,
  `xm` varchar(20) COLLATE utf8_unicode_ci NOT NULL,
  `xb` enum('男','女') COLLATE utf8_unicode_ci DEFAULT NULL,
  `nl` int(11) DEFAULT NULL,
  `bj` char(5) COLLATE utf8_unicode_ci DEFAULT '189',
  `jg` varchar(100) COLLATE utf8_unicode_ci DEFAULT NULL,
  `sfzh` char(18) COLLATE utf8_unicode_ci DEFAULT NULL,
  `zcrq` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`xh`),
  UNIQUE KEY `sfzh` (`sfzh`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `xsb`
--

LOCK TABLES `xsb` WRITE;
/*!40000 ALTER TABLE `xsb` DISABLE KEYS */;
INSERT INTO `xsb` VALUES ('001','张三','男',18,'189','北京','123456789123456789','2018-10-10 16:00:00'),('002','李四','男',19,'189','河北','123456789123456781','2018-10-10 16:00:00'),('003','王五','男',20,'189','北京','123456789123456782','2018-10-10 16:00:00'),('004','赵六','男',18,'189','北京','123456789123456783','2018-10-10 16:00:00'),('005','冯七','女',22,'189','河北','123456789123456784','2018-10-10 16:00:00'),('006','李冰冰','女',19,'189','山东','123456789123456785','2018-10-10 16:00:00'),('007','李晨','男',24,'189','河北','123456789123456786','2018-10-10 16:00:00'),('008','赵冰冰','女',19,'189','河北','123456789123456787','2018-10-10 16:00:00'),('009','王宝强','男',23,'189','河北','123456789123456780','2018-10-10 16:00:00'),('010','王宝小强',NULL,NULL,'189','山东',NULL,'2019-01-26 09:03:02'),('011','王强',NULL,NULL,'189','山东',NULL,'2019-01-26 09:03:12'),('012','王小宝强','男',NULL,'189','山东',NULL,'2019-01-26 09:03:27');
/*!40000 ALTER TABLE `xsb` ENABLE KEYS */;
UNLOCK TABLES;
/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;

/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;

-- Dump completed on 2019-02-12  9:51:50