创建数据库：CREATE DATABASE 数据库名；

  1. -- 创建数据库library，使用字符集utf8，使用字符排序规则utf8_general_ci
  2. CREATE DATABASE `library` CHARACTER set utf8 COLLATE utf8_general_ci;

创建表：CREATE DATABASE 表名()；
MySQL不支持列级创建外键约束，因此只能使用CONSTRAINT来创建表级外键约束，也只能使用CONSTRAINT来创建表的联合主键约束。

  1. -- 图书目录表
  2. CREATE TABLE `Books` (
  3.     `bookNo` CHAR ( 9 ) COMMENT '书号(主键)' 【 列级主键约束：PRIMARY KEY 】,
  4.     `ISBN` CHAR ( 13 ) not null COMMENT '每种书唯一编号(外键)',
  5.     `location` VARCHAR ( 40 ) not null  COMMENT '图书存放位置',
  6.     `bstatus` CHAR ( 1 ) not null  COMMENT '注释',
  7.     `ctr_no` TINYINT not null  COMMENT '注释',
  8.     CONSTRAINT PK_bookNo PRIMARY KEY ( `bookNo` ),
  9.     CONSTRAINT FK_ISBN FOREIGN KEY ( `ISBN` ) REFERENCES `BookInfo` ( `ISBN` ),
  10.     CONSTRAINT CK_bstatus CHECK ( `bstatus` = 0 OR 1 OR 2 OR 3 ) 
  11. ) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;
  1. -- 佑溪村数据库
  2. DROP DATABASE if EXISTS `yxc`;
  3. CREATE DATABASE IF NOT EXISTS `yxc` CHARACTER set utf8 COLLATE utf8_general_ci;
  5. -- 使用佑溪村数据库
  6. use yxc;
  10. -- 1.角色表
  11. DROP TABLE if EXISTS `villager_role`;
  12. CREATE TABLE IF NOT EXISTS `villager_role`(
  13.     `R_ID` int(15) not null PRIMARY KEY AUTO_INCREMENT COMMENT '主键: 唯一标识角色的编码',
  14.     `role_name` VARCHAR(20) not null UNIQUE COMMENT '角色名',
  15.     `role_duty` VARCHAR(255) NULL COMMENT '相关职责',
  16.     `gmt_create` datetime null COMMENT '元组创建时间',
  17.     `gmt_modified` datetime null COMMENT '元组修改时间'
  18. )ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;
  22. -- 2.残疾级别表
  23. DROP TABLE if EXISTS `disability_level`;
  24. CREATE TABLE IF NOT EXISTS `disability_level`(
  25.     `DL_ID` int(15) not null PRIMARY KEY AUTO_INCREMENT COMMENT '主键: 唯一标识残疾级别的编码',
  26.     `level` VARCHAR(20) not null UNIQUE COMMENT '残疾级别',
  27.     `gmt_create` datetime null COMMENT '元组创建时间',
  28.     `gmt_modified` datetime null COMMENT '元组修改时间'
  29. )ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;
  33. -- 3.户主关系类型表
  34. DROP TABLE if EXISTS `family_relation`;
  35. CREATE TABLE IF NOT EXISTS `family_relation`(
  36.     `FR_ID` int(15) not null PRIMARY KEY AUTO_INCREMENT COMMENT '主键: 唯一标识户主关系类型的编码',
  37.     `relation_type` VARCHAR(5) not null UNIQUE COMMENT '村民与户主的血缘关系',
  38.     `gmt_create` datetime null COMMENT '元组创建时间',
  39.     `gmt_modified` datetime null COMMENT '元组修改时间'
  40. )ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;
  44. -- 4.家庭贫困类型表
  45. DROP TABLE if EXISTS `family_pool`;
  46. CREATE TABLE IF NOT EXISTS `family_pool`(
  47.     `FP_ID` int(15) not null PRIMARY KEY AUTO_INCREMENT COMMENT '主键: 唯一标识户主关系类型的编码',
  48.     `relation_type` VARCHAR(5) not null UNIQUE COMMENT '村民与户主的血缘关系',
  49.     `gmt_create` datetime null COMMENT '元组创建时间',
  50.     `gmt_modified` datetime null COMMENT '元组修改时间'
  51. )ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;
  55. -- 5.土地类型表
  56. DROP TABLE if EXISTS `land`;
  57. CREATE TABLE IF NOT EXISTS `land`(
  58.     `L_ID` int(15) not null PRIMARY KEY AUTO_INCREMENT COMMENT '主键: 唯一标识土地类型的编码',
  59.     `land_type` VARCHAR(10) not null UNIQUE COMMENT '土地类型',
  60.     `gmt_create` datetime null COMMENT '元组创建时间',
  61.     `gmt_modified` datetime null COMMENT '元组修改时间'
  62. )ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;
  66. -- 6.种植类型表
  67. DROP TABLE if EXISTS `plant`;
  68. CREATE TABLE IF NOT EXISTS `plant`(
  69.     `P_ID` int(15) not null PRIMARY KEY AUTO_INCREMENT COMMENT '主键: 唯一标识种植农作物类型的编码',
  70.     `plant_type` VARCHAR(10) not null UNIQUE COMMENT '农作物名',
  71.     `gmt_create` datetime null COMMENT '元组创建时间',
  72.     `gmt_modified` datetime null COMMENT '元组修改时间'
  73. )ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;
  77. -- 7.养殖类型表
  78. DROP TABLE if EXISTS `breed`;
  79. CREATE TABLE IF NOT EXISTS `breed`(
  80.     `B_ID` int(15) not null PRIMARY KEY AUTO_INCREMENT COMMENT '主键: 唯一标识养殖动物类型的编码',
  81.     `breed_type` VARCHAR(10) not null UNIQUE COMMENT '养殖动物名',
  82.     `gmt_create` datetime null COMMENT '元组创建时间',
  83.     `gmt_modified` datetime null COMMENT '元组修改时间'
  84. )ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;
  88. -- 8.民问卷调查表(匿名调查)
  89. DROP TABLE if EXISTS `villager_survey`;
  90. CREATE TABLE IF NOT EXISTS `villager_survey`(
  91.     `VS_ID` int(15) not null PRIMARY KEY AUTO_INCREMENT COMMENT '主键: 问卷调查流水号',
  92.     `survey_time` date not null COMMENT '调查时间',
  93.     `family_relation` VARCHAR(255) null COMMENT '家庭成员关系和睦情况',
  94.     `neighbor_relation` VARCHAR(255) null COMMENT '邻居关系和睦情况',
  95.     `evaluateion` VARCHAR(255) null COMMENT '对村委干部的评价',
  96.     `difficulty` VARCHAR(255) null COMMENT '有何困难',
  97.     `suggestion` VARCHAR(255) null COMMENT '有何建议',
  98.     `life_happiness` VARCHAR(255) null COMMENT '对于目前生活是否幸福的看法与原因',
  99.     `assistance_measures` VARCHAR(255) null COMMENT '目前享有的主要帮扶措施是什么',
  100.     `gmt_create` datetime null COMMENT '元组创建时间',
  101.     `gmt_modified` datetime null COMMENT '元组修改时间'
  102. )ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;
  106. -- 9.村组信息表
  107. DROP TABLE if EXISTS `village_group`;
  108. CREATE TABLE IF NOT EXISTS `village_group`(
  109.     `VG_ID` int(15) not null PRIMARY KEY AUTO_INCREMENT COMMENT '主键: 唯一标识村组类型的编码',
  110.     `family_amount` int(3) not null COMMENT '一个村组包含的家庭个数',
  111.     `gmt_create` datetime null COMMENT '元组创建时间',
  112.     `gmt_modified` datetime null COMMENT '元组修改时间'
  113. )ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;
  116. -- 10.家庭户籍表
  117. DROP TABLE if EXISTS `family`;
  118. CREATE TABLE IF NOT EXISTS `family`(
  119.     `V_ID` int(15) not null PRIMARY KEY COMMENT '主键: 户主(村民)ID ',
  120.     `family_number` VARCHAR(10) not null UNIQUE COMMENT '房屋门牌号',
  121.     `people_amount` int(2) not null COMMENT '家庭人口数',
  122.     `family_highway` int(1) null DEFAULT 0 COMMENT '入户公路状况: 0表示未入户: 公路未修到家门口,1表示已入户: 公路已修到家门口',
  123.     `gmt_create` datetime null COMMENT '元组创建时间',
  124.     `gmt_modified` datetime null COMMENT '元组修改时间'
  125. )ENGINE=InnoDB DEFAULT CHARSET=utf8;
  129. -- 11.村民表
  130. DROP TABLE if EXISTS `villager`;
  131. CREATE TABLE IF NOT EXISTS `villager`(
  132.     `V_ID` int(15) not null PRIMARY KEY AUTO_INCREMENT COMMENT '主键: 唯一标识村民的编码',
  133.     `villager_name` VARCHAR(20) not null COMMENT '姓名',
  134.     `villager_sex` int(1) NULL DEFAULT 1 COMMENT '性别: 0表示女，1表示男',
  135.     `villager_age` int(3) NULL COMMENT '年龄',
  136.     `villager_birthday` date NOT NULL COMMENT '出生年月日',
  137.     `villager_phone` char(11) NULL UNIQUE COMMENT '联系电话:固定11位',
  138.     `villager_number` char(18) NULL UNIQUE COMMENT '身份证号:固定18位',
  139.     `villager_nation` VARCHAR(10) NULL DEFAULT '汉族' COMMENT '民族',
  140.     `political_outlook` VARCHAR(10) NULL DEFAULT '群众' COMMENT '政治面貌',
  141.     `villager_marriage` int(1) NULL DEFAULT 0 COMMENT '婚姻状况: 0表示未婚，1表示已婚，2表示离异'    ,
  142.     `villager_health` VARCHAR(255) NULL COMMENT '身体状况',
  143.     `is_student` int(1) NULL DEFAULT 1 COMMENT '是否学生: 0表示否，1表示是',
  144.     `is_disability_certificate` int(1) NULL DEFAULT 0 COMMENT '是否办理残疾证: 0代表未办理，1代表已办理',
  145.     `gmt_create` datetime null COMMENT '元组创建时间',
  146.     `gmt_modified` datetime null COMMENT '元组修改时间'
  147. )ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;
  151. -- 12.村民工作信息表
  152. DROP TABLE if EXISTS `job_info`;
  153. CREATE TABLE IF NOT EXISTS `job_info`(
  154.     `JI_ID` int(15) not null PRIMARY KEY AUTO_INCREMENT COMMENT '主键: 唯一标识工作信息的编码',
  155.     `job_unit` VARCHAR(20) not null COMMENT'工作单位',
  156.     `job_income` int(10) not null COMMENT'月工资',
  157.     `job_item` VARCHAR(20) null COMMENT'近期工作内容',
  158.     `job_province` VARCHAR(10) null COMMENT'省份',
  159.     `job_city` VARCHAR(10) null COMMENT'城市',
  160.     `job_county` VARCHAR(10) null COMMENT'县',
  161.     `job_address` VARCHAR(20) null COMMENT'详细地址',
  162.     `gmt_create` datetime null COMMENT '元组创建时间',
  163.     `gmt_modified` datetime null COMMENT '元组修改时间'
  164. )ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;
  168. -- 13.村民患病信息表
  169. DROP TABLE if EXISTS `illness_info`;
  170. CREATE TABLE IF NOT EXISTS `illness_info`(
  171.     `II_ID` int(15) not null PRIMARY KEY AUTO_INCREMENT COMMENT '主键: 唯一标识患病信息的编码',
  172.     `illness_name` VARCHAR(20) not null COMMENT'所患何病',
  173.     `illness_time` datetime null COMMENT'患病时间',
  174.     `is_chronic_disease` int(1) null DEFAULT 0 COMMENT'是否属于慢性病: 0表示否，1表示是',
  175.     `gmt_create` datetime null COMMENT '元组创建时间',
  176.     `gmt_modified` datetime null COMMENT '元组修改时间'
  177. )ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;
  181. -- 14.残疾补贴表
  182. DROP TABLE if EXISTS `disability_payments`;
  183. CREATE TABLE IF NOT EXISTS `disability_payments`(
  184.     `DP_ID` int(15) not null PRIMARY KEY AUTO_INCREMENT COMMENT '主键: 唯一标识残疾补贴的编码',
  185.     `dp_time` datetime not null COMMENT'领取时间',
  186.     `dp_money` int(10) not null COMMENT'补贴金额',
  187.     `gmt_create` datetime null COMMENT '元组创建时间',
  188.     `gmt_modified` datetime null COMMENT '元组修改时间'
  189. )ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;
  193. -- 15.养老补贴表
  194. DROP TABLE if EXISTS `pension_subsidy`;
  195. CREATE TABLE IF NOT EXISTS `pension_subsidy`(
  196.     `PS_ID` int(15) not null PRIMARY KEY AUTO_INCREMENT COMMENT '主键: 唯一标识养老补贴的编码',
  197.     `ps_time` datetime not null COMMENT'领取时间',
  198.     `ps_money` int(10) not null COMMENT'养老补贴金额: age>=60方可领取',
  199.     `advanced_age_money` int(10) null COMMENT'养老补贴金额: age>=80方可领取',
  200.     `gmt_create` datetime null COMMENT '元组创建时间',
  201.     `gmt_modified` datetime null COMMENT '元组修改时间'
  202. )ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;
  206. -- 16.村民保障表
  207. DROP TABLE if EXISTS `villager_ensure`;
  208. CREATE TABLE IF NOT EXISTS `villager_ensure`(
  209.     `V_ID` int(15) not null PRIMARY KEY COMMENT '主键(外键): 村民id,标识一行记录属于哪一个村民',
  210.     `enjoy_mm` int(1) not null DEFAULT 0 COMMENT '是否享受慢门政策: 0表示否，1表示是',
  211.     `manage_mm` int(1) not null DEFAULT 0 COMMENT '是否办理慢门政策: 0表示否，1表示是',
  212.     `manage_medical` int(1) not null DEFAULT 0 COMMENT '是否办理医疗保险: 0表示否，1表示是',
  213.     `manage_pension` int(1) not null DEFAULT 0 COMMENT '是否办理养老保险: 0表示否，1表示是',
  214.     `gmt_create` datetime null COMMENT '元组创建时间',
  215.     `gmt_modified` datetime null COMMENT '元组修改时间'
  216. )ENGINE=InnoDB DEFAULT CHARSET=utf8;
  220. -- 17.土地所属详情表
  221. DROP TABLE if EXISTS `family_land`;
  222. CREATE TABLE IF NOT EXISTS `family_land`(
  223.     `V_ID` int(15) not null COMMENT '主键(外键): 户主(村民)ID ',
  224.     `L_ID` int(15) not null COMMENT '主键(外键): 标识土地类型',
  225.     `land_area` double null COMMENT '土地面积',
  226.     `gmt_create` datetime null COMMENT '元组创建时间',
  227.     `gmt_modified` datetime null COMMENT '元组修改时间'
  228. )ENGINE=InnoDB DEFAULT CHARSET=utf8;
  232. -- 18.种植类型详情表
  233. DROP TABLE if EXISTS `family_plant`;
  234. CREATE TABLE IF NOT EXISTS `family_plant`(
  235.     `V_ID` int(15) not null COMMENT '主键(外键): 户主(村民)ID ',
  236.     `P_ID` int(15) not null COMMENT '主键(外键):标识种植的农作物类型',
  237.     `plant_amount` int(10) null COMMENT '种植数量',
  238.     `gmt_create` datetime null COMMENT '元组创建时间',
  239.     `gmt_modified` datetime null COMMENT '元组修改时间'
  240. )ENGINE=InnoDB DEFAULT CHARSET=utf8;
  244. -- 19.养殖类型详情表
  245. DROP TABLE if EXISTS `family_breed`;
  246. CREATE TABLE IF NOT EXISTS `family_breed`(
  247.     `V_ID` int(15) not null COMMENT '主键(外键): 户主(村民)ID ',
  248.     `B_ID` int(15) not null COMMENT '主键(外键): 标识养殖的动物类型',
  249.     `breed_amount` int(10) null COMMENT '养殖数量',
  250.     `gmt_create` datetime null COMMENT '元组创建时间',
  251.     `gmt_modified` datetime null COMMENT '元组修改时间'
  252. )ENGINE=InnoDB DEFAULT CHARSET=utf8;
  256. -- 20.家庭年收支表
  257. DROP TABLE if EXISTS `income_expenditure`;
  258. CREATE TABLE IF NOT EXISTS `income_expenditure`(
  259.     `IE_ID` int(15) not null PRIMARY KEY AUTO_INCREMENT COMMENT '主键: 唯一标识家庭年收支的编码',
  260.     `survey_time` date not null COMMENT '调查时间',
  261.     `genera_income` int(10) not null COMMENT '年总收入',
  262.     `genera_expenditure` int(10) not null COMMENT '年总支出',
  263.     `percapita_income` int(10) null COMMENT '人均年净收入: (年总收入-年总支出)/家庭人口数',
  264.     `production_expenditure` int(10) null COMMENT '年生产支出',
  265.     `medical_expenditure` int(10) null COMMENT '年医疗支出',
  266.     `gmt_create` datetime null COMMENT '元组创建时间',
  267.     `gmt_modified` datetime null COMMENT '元组修改时间'
  268. )ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;
  272. -- 21.家庭保障表
  273. DROP TABLE if EXISTS `family_ensure`;
  274. CREATE TABLE IF NOT EXISTS `family_ensure`(
  275.     `V_ID` int(15) not null PRIMARY KEY COMMENT '主键(外键): 户主(村民)ID,标识一行记录属于哪一户家庭',
  276.     `house_ensure` int(1) not null default 1 COMMENT '安全住房是否保障: 0表示否，1表示是',
  277.     `house_qusetion` VARCHAR(255) null COMMENT '安全住房存在的问题',
  278.     `water_ensure` int(1) not null default 1 COMMENT '安全饮水是否保障: 0表示否，1表示是',
  279.     `water_qusetion` VARCHAR(255) null COMMENT '安全饮水存在的问题',
  280.     `education_ensure` int(1) not null default 1 COMMENT '基础教育是否保障: 0表示否，1表示是',
  281.     `education_qusetion` VARCHAR(255) null COMMENT '基础教育存在的问题',
  282.     `student_amount` int(2) not null COMMENT '家庭学生个数',
  283.     `gmt_create` datetime null COMMENT '元组创建时间',
  284.     `gmt_modified` datetime null COMMENT '元组修改时间'
  285. )ENGINE=InnoDB DEFAULT CHARSET=utf8;

创建约束时发现的问题：Can’t write; duplicate key in table ‘job_info’

结论：

（1）同一个表中，约束名不能相同。

（2）不同的表中约束名也不能相同（不管外键名相不相同）。