sql数据库建表

1、1写出创建如下三张表的 SQL语句，要求在定义表的同时定义数据的完整性约束:(1) “图书表”结构如下：书号:统一字符编码定长类型，长度为 6，主键； 书名:统一字符编码可变长类型，长度为 30，非空； 第一 普通编码定长字符类型，长度为 10 ，非空； 出版日期:小日期时间型； 价格:定点小数，小数部分1位，整数部分 3位。(2) “书店表”结构如下: 书店编号:统一字符编码定长类型，长度为 6，主键； 店名:统一字符编码可变长类型，长度为 30，非空；:普通编码定长字符类型，8位长，每一位的取值均是 09的数字;地址:普通编码可变长字符类型，40位长。邮政编码:普通编码定长字符类型，6位长

2、。(3) “图书销售表”结构如下: 书号:统一字符编码定长类型，长度为 6，非空; 书店编号:统一字符编码定长类型，长度为 6，非空; 销售日期:小日期时间型，非空;销售数量:小整型，大于等于1。主键为(书号，书店编号，销售日期) ; 其中“书号”为引用“图书表”的“书号”的外键;“书店编号”为引用“书店表”的“书店编号”的外键。答:CREATE TABLE 图书表 ( 书号 nchar(6) primary key, 书名 nvarchar(30) not null, 第一作者 char(10) not null, 出版日期 smalldatetime, 价格 numeric(4,1)CRE

3、ATE TABLE 书店表 ( 书店编号 nchar(6) primary key, 店名 nvarchar(30) not null, char(8) check ( like '0-90-90-90-90-90-90-90-9'), 地址 varchar(40), 邮政编码 char(6)CREATE TABLE 图书销售表 ( 书号 nchar(6) not null, 书店编号 nchar(6) not null, 销售日期 smalldatetime not null, 销售数量 smallint check( 销售数量 >=1), primary key( 书

4、号 ,书店编号 ,销售日期 ), foreign key( 书号 ) references 图书表 (书号 ), foreign key( 书店编号 ) references 书店表 (书店编号 )1.1为图书表添加“印刷数量”列，类型为整数，同时添加约束，要求此列的取值要大于等 于1000。答： ALTER TABLE 图书表ADD 印刷数量 int check ( 印刷数量 >=1000) 1.2删除“书店表”中的“邮政编码”列。 答： ALTER TABLE 书店表DROP COLUMN 邮政编码 1.3将“图书销售表”中的“销售数量”列的数据类型改为整型。 答： ALTER TA

5、BLE 图书销售表 ALTER COLUMN 销售数量 int2设某商业集团数据库中有三个实体集： 商店：商店编号、商店名、地址 商品：商品编号、商品名、规格、单价 职工：职工编号、姓名、性别、业绩 每个商店可销售多种商品， 每种商品也可放在多个商店销售， 每 个商店销售一种商品时有月销售量； 每个商店有许多职工， 每个职工 只能在一个商店工作，商店聘用职工有聘期和月薪。(1) 试画出 E-R 图，要求在图上注明属性及联系的类型；(2) 将 E-R 图转换成关系模型，并注明主码；(3) 根据实际情况，使用SQL创建表，包括各种约束；(4) 用SQL语句查找大于平均业绩的职工姓名；(5) 用SQ

6、L语句创建一个业绩大于100的所有男职工信息的视图。1）（5分）(2) 这个E-R图可转换为4个关系模式：(8分)商店(商店编号，商店名，地址)(2分)职工(职工编号,姓名，性别，业绩，商店编号，聘期，月薪)(2分)商品(商品编号，商品名，规格，单价)(2分)销售(商店编号，商品编号，月销售量)(2分)(3) create shop(Sid char(3) primary key, Sname char(10), Saddchar(50);( 2 分)create employee(Eid char(3) primary key, En ame char(5), Esex char(1), E

7、ach real, Sid char(3),Ere date, Esa int, foreign key (Sid) references (shop); ( 2 分create commodity(Cid char(3) primary key, Cname char(10), Csp char(10), Cpr real); ( 2 分)create vendition(Sid char(3), Cid char(3), Vse int, primary key (Sid,Cid),foreign key (Sid) references (shop), foreign key (Cid)

8、 references (commodity); ( 2 分)(4) ( 4)select En ame from employee x where Each>=(select avg(Each) from employee y where y.Each二x.Each);（5）create view Eman（Eid, Ename, Esex, Each, Sid, Ere, Esa）As select Eid, En ame, Esex, Each, Sid, Ere, Es from employee where Each>100 and Esex=男'（2 分）3.（

9、共10分）设学校数据库中有两个实体集：学生表：学号、姓名、班级课程表：课程号、课程名称、教师某学校有若干学生，每个学生可以选修多门课程，学校有若干课程供 学生选修，每门课程可以供多个学生选修，要建立该学校学生选修课 程的数据库，请设计：（1）试画出E-R图，要求在图上注明属性及联系的类型；（2）将E-R图转换成关系模型，并注明主码；（2）这个E-R图可转换为4个关系模式：（12分） 商店（商店编号，商店名，地址）（3分）职工（职工编号，姓名，性别，业绩，商店编号，聘期，月薪）（3 分）（3 分）（3 分）商品（商品编号，商品名，规格，单价） 销售（商店编号，商品编号，月销售量）(1) (4 分

10、)学生表（2）这个E-R图可转换为3个关 课程表（学号，姓名，班级） 选修（学号，课程号） 课程表（课程号，课程名称，教师N课程表4. 一个图书借阅管理数据库要求提供下述服务：1）可随时查询书库中现有书籍的品种，数量与存放位置。所有各类书籍均可由书号唯一标 识。2）可随时查询书籍借还情况。包括借书人单位、姓名、借书证号、借书日期和还书日期。 约定任何人可借多种书，任何一种书可为多个人所借，借书证号具有唯一性。3） 当需要时，可通过数据库中保存的出版社的电报编号、 及地址等信息向有关书 籍的出版社增购有关书籍。约定一个出版社可以出版多种书籍， 同一本书仅为一个出版社出 版，出版社名具有唯一性。根

11、据以上情况，试构造满足需求的E-R图，并转换成等价的关系模型结构。答案：图略。转换为等价的关系模型结构如下：借书人（借书证号，姓名，单位）图书（书号，书名，数量，位置，出版社名）出版社（出版社名，电报，地址）借阅（借书证号，书号，借书日期，还书日期）1表名抄表记录主键ID序号字段名称字段说明类型位数属性备注1IDIDInt4非空主键，自增2抄表人抄表人Int4非空3目标表目标表Int4非空4抄表时间抄表时间datetime8非空5示数示数float8非空2表名抄表信息主键ID序号字段名称字段说明:类型位数属性备注1IDIDInt4非空主键，自增2表名表名n char20可空3表编号表编号n c

12、har20可空4所属子站所属子站int4可空5关口表倍率关口表倍 率float8非空6电费单价电费单价float8非空7结算方式结算方式n char20可空8计费方式计费方式n char20可空3表名电站关注表主键ID序号字段名称字段说明类型位数属性备注1IDIDInt4非空主键，自增2用户用户Int4非空3电站电站Int4非空4表名电站设备表主键ID序号字段名称字段说明类型位数属性备注1IDIDvarchar50非空主键，自增2设备名称设备名称varchar50非空3所属电站所属电站Int4非空4设备类型设备类型Varchar10非空15父级设备父级设备Varchar50可空6组件数量组件数

13、量int4非空7显示顺序显示顺序int4非空5表名故障表_2021主键ID序号字段名称字段说明类型位 数属性备注1IDIDInt4非空主键，自增2子站子站Int4非空3设备编号设备编号n char200非空4故障内容故障内容n char200非空5功率损失功率损失float8非空6故障时间故障时间datetime8非空7恢复时间恢复时间datetime8可空6表名故障表 2021主键ID序号字段名称字段说明类型位数属性备注1IDIDInt4非空主键，自增2子站子站Int4非空3设备编号设备编号n char100非空4故障内容故障内容n char100非空5功率损失功率损失float8非空6故障

14、时间故障时间datetime8非空7恢复时间恢复时间datetime8可空7表名故障工单表主键ID序号字段名称字段说明类型位数属性备注1IDIDInt4非空主键，自增2工单号工单号Varchar32非空3故障类型故障类型n char20非空4上报时间上报时间datetime8非空5上报人上报人int4可空6发现时间发现时间datetime8非空7故障说明故障说明n char200非空8故障位置n char40非空9所在巡视点Int4可空10接单人int4可空11操作指导n char200可空12是否现场处理bit1非空13是否处理完成bit1非空14完成时间datetime8可空15所属子站i

15、nt4非空16图片image16可空17外部文件n varchar-1可空18处理说明n varchar100可空19关联故障int4可空8表名集团信息表主键ID序号字段名称字段说明类型位 数属性备注1ID集团IDInt4非空主键，自增2集团编号集团编号ncha r100非空3集团名称集团名称ncha r100非空4集团地址集团地址ncha r100可空5在建装机容量在建装机容量deci mal5非空6在建电站数量在建电站数量int4非空7未建装机容量未建装机容量deci mal5非空8未建电站数量未建电站数量Int4非空9父集团ID父集团IDint4可空9表名角色权限表主键SIDELINEI

16、D序号字段名称字段说明类型位 数属性备注1IDIDInt4非空主键，自增2所属角色所属角色Int4非空3权限权限Int4非空表名清洗计划主键ID序号字段名称字段说明类型位数属性备注1IDIDInt4非空主键，自增2计划子站计划子站Int4非空3开始日期开始日期date3非空4清洗间隔月数清洗间隔月数int4非空5计划结束日期计划结束日期date3可空11表名清洗记录主键ID序号字段名称字段说明类型位数属性备注1IDIDInt4非空主键，自增2开始日期开始日期date3非空3结束日期结束日期date3非空4清洗子站清洗子站Int4非空12表名巡视点信息表主键ID序号字段名称字段说明类型位数属性备

17、注1IDIDInt4非空主键，自增2名称名称n char40非空3张贴位置张贴位置n char40可空4所属子站所属子站Int4非空4编码编码n char200非空5经度经度decimal9可空6纬度纬度decimal9可空7创建时间创建时间datetime8非空8删除标记删除标记bit1非空13表名巡视记录 按任务创建月主键ID序号字段名称字段说明类型位数属性备注1IDIDInt4非空主键，自增2所属任务所属任务Int4非空3巡视点巡视点Int4非空4巡视完成时间巡视完成datetime8可空时间14表名巡视类型主键ID序号字段名称字段说明类型位数属性备注1IDIDInt4非空主键，自增2类

18、型名类型名n char20非空15表名巡视任务表主键ID序号字段名称字段说明类型位数属性备注1IDIDInt4非空主键，自增2巡视类型巡视类型Int4非空3附带工具及说明附带工具 及说明n char200可空4工单号工单号varchar32非空4是否完成是否完成bit1非空5创建时间创建时间datetime8非空6完成时间完成时间datetime8可空7任务执行人任务执行 人int4可空8所属子站所属子站int4非空9创建人创建人int4非空10开始小时开始小时tin yi nt1非空11截止小时截止小时tin yi nt1非空16表名用户表主键ID序号字段名称字段说明类型位数属性备注1IDI

19、DInt4非空主键，自增2用户名用户名n char20非空3姓名姓名n char20非空4所属分组所属分组int4非空5登录密码登录密码n char20非空6是否管理员是否管理 员bit1非空7微信ID微信IDn varchar-1可空8号一号n char22可空9推送级别推送级别tin yi nt1非空10简报时间简报时间time5非空17表名用户角色表主键ID序号字段名称字段说明类型位数属性备注1IDIDInt4非空主键，自增2用户用户int4非空3角色角色int4非空18表名子站分组表主键ID序号字段名称字段说明类型位 数属性备注1IDIDInt4非空主键，自增2子站子站Int4非空3分

20、组分组Int4非空4常用标记常用标记bit1非空19表名子站详情表主键ID序号字段名称字段说明类型位数属性备注1IDIDInt0非空主键，自增2子站ID子站IDInt0非空3简介简介n text1可空4名称1名称1n char1可空4值1值1n char1可空5名称2名称2n char1可空6值2值2n char1可空7名称3名称3n char1可空8值3值3n char1可空9名称4名称4n char1可空10值4值4n char1可空11名称5名称5n char1可空12值5值5n char1可空13名称6名称6n char1可空14值6值6n char1可空15名称7名称7n char1

21、可空16值7值7n char1可空17名称8名称8n char1可空18值8值8n char1可空19名称9名称9n char1可空20值9值9n char1可空21名称10名称10n char1可空22值10值10n char1可空23名称11名称11n char1可空24值11值11n char1可空25名称12名称12n char1可空26值12值12n char1可空27名称13名称13n char1可空28值13值13n char1可空29名称14名称14n char1可空30值14值14n char1可空31名称15名称15n char1可空32值15值15n char1可空33名

22、称16名称16n char1可空34值16值16n char1可空35名称17名称17n char1可空36值17值17n char1可空37名称18名称18n char1可空38值18值18n char1可空39名称19名称19n char1可空40值19值19n char1可空41名称20名称20n char1可空42值20值20n char1可空20表名子站信息表主键ID序号字段名称字段说明类型位数属性备注1IDIDInt4非空主键，自增2子站名称子站名称n char100非空3子站编号子站编号n char100非空4所属集团所属集团Int4可空4设计容量设计容量decimal5非空5组

23、件数量组件数量int4非空6支路数量支路数量smalli nt2非空7设备数量设备数量int4非空8X-A-_pb.逆变万式X_12b.逆变万式n char40可空9并网方式并网方式n char40可空10并网电压并网电压decimal5可空11承载类型承载类型n char20可空12是否常用是否常用bit1非空13投运日期投运日期date3非空14所在省份所在省份n char40非空15所在城市所在城市n char40可空16子站地址子站地址n char40可空17并网费率并网费率decimal5非空18座标核实座标核实bit1非空19经度经度decimal9可空20纬度纬度decimal9

24、可空21禁用禁用bit1非空22是否自持是否自持bit1非空1.运维关系图抄表记录ID抄表人目标表 抄表时间 示数曲u-l l-l用户表ID用户名姓名所属分组登录密码是否管理员微信ID手机号推送级别简报时间J故障工单表ID工单号 故障类型上报时间抄表信息ID表名 表编号 所属子站 关口表倍率 电费单价 结算方式 计费方式上报人Lf J J d J J J J J发现时间 故障说明 故障位置所在巡视点接单人 操作指导 是否现场处理 是否处理完成 完成时间所属子站图片 外部文件处理说明关联故障巡视点信息表ID名称张贴位置 所属子站编码经度纬度创建时间删除标记子站占信息表卫ID子站名称子站编号所属集

25、团设计容量组件数量支路数量设备数量逆变方式并网方式并网电压承载类型是否常用投运日期所在省份所在城市子站地址并网费率座标核实经度纬度禁用是否自持清洗记录丄id.开始日期结束日期清洗子站清洗计划ID计划子站开始日期清洗间隔月数计划结束日期2.资源关系图图书系统表结构（注意：表结构以数据库为准）一、数据表如下：系统基础表：1. C Employee（员工信息表）编号字段中文说明类型备注是否 为空是否 主键1EmployeeID员工IDint是2EName员工姓名varchar(20)3SexCode性别varchar(2)4Age年龄int是5StartDate上岗日期datetime6DeptCo

26、de工作部门varchar(2)是7DutyCode职务varchar(2)是8Phone工作varchar(16)是9MobilePho ne移动varchar(11)是10varchar是否在岗varchar(2)是11Photo照片image是12AddDate添加日期datetime13Tran sFlag传输标记int14DelFlag删除标记int15Remark备注varchar(50)是2.P RetailParameter(零售参数表)编号字段中文说明类型备注是否 为空是否 主键1IsPri ntBill是否打印小票varchar(2)是2BillTitle小票标题varch

27、ar(200)3IsUseReader是否使用读码器varchar(2)4AllowCha ngeRebate允许修改折扣varchar(2)5Prin tTypesCode打印机类型varchar(2)6Welcome欢迎词varchar(50)7IsPri ntNumber打印单号varchar(2)8IsPri ntDate打印日期varchar(2)9IsPri ntCollector打印收费员varchar(2)10Hi nt说明varchar(100)11Phone联系varchar(16)3. Common Code（编码表）（一些下拉框的配置信息）编号字段中文说明类型备注是否

28、为空是否 主键1CodeID类型int是2Code编码varchar(2)是3CodeName名称varchar(30)4CodeDesc类型说明varchar(60)5IsValid是否启用varchar(3)6Remark备注varchar(200)7AddDate添加日期datetime4.P DefaultValue(默认值表)编号字段中文说明类型备注是否 为空是否 主键1TableID表IDint2TableName表名称varchar(30)3FieldName字段名varchar(30)4DefaultValue默认值varchar(100)5. Common DCode(行政编

29、码)编号字段中文说明类型备注是否 为空是否 主键1DCode行政编码varchar(9)是2DName行政名称varchar(40)3ProvName省名varchar(10)4CityName地区名varchar(10)5Coun tyName县名varchar(10)6ProvCode省编码varchar(2)7CityCode地区编码varchar(2)8Coun tyCode县编码varchar(2)9Remark备注varchar(30)10AddDate记录增加日期datetime6. P SystemParameter係统参数表)编号字段中文说明类型备注是否 为空是否 主键1PI

30、D参数IDint是2PName参数名称varchar(30)3ValueType参数值类型varchar(2)4PValue参数值varchar(60)5PStatusCode参数状态varchar(2)6PExpla inVarchar(200)7Ca nEditvarchar(2)8Tran sFlagint7.P OperationLog（操作日志表）（数据库里没有该表）编号字段中文说明类型备注是否 为空是否 主键1Operatio nID操作IDbigi nt是2Operatio nTypes操作类型varchar(2)3Un itName单元名称varchar(3)4Explai n

31、操作说明varchar(200)5Operator操作人员varchar(20)6OperateTime操作时间datetime8. Common User(用户表)编号字段中文说明类型备注是否 为空是否 主键1UserID用户代码varchar(8)是2Password用户密码varchar(128)3remark备注varchar(200)4Status用户状态varchar(2)5AddDate添加日期datetime6Can SeeAllvarchar(2)9. Common Role User（角色和用户对照表）编号字段中文说明类型备注是否 为空是否 主键1RoleID角色代码int

32、是2UserID用户代码varchar(30)是3Com UserID用户表 用户代码varchar(8)10. Com mon Role(角色表)编号字段中文说明类型备注是否 为空是否 主键1RoleID角色代码int是2RoleName角色名称varchar(30)3SysFlag系统标志tin yi nt4Remark备注varchar(200)5AddDate添加日期datetime11. Common Role Operate（操作和角色对照表）编号字段中文说明类型备注是否 为空是否 主键1RoleID角色代码int是2OperateID操作代码varchar(32)是12. Com

33、mon Operation(操作列表)编号字段中文说明类型备注是否 为空是否 主键1OperateID操作代码varchar(32)是2OperateName操作名称varchar(30)3Remark描述varchar(200)13. R ReportCatalog(报表目录)编号字段中文说明类型备注是否 为空是否 主键1ReportID报表类型varchar(2)是2CreateDate统计时间datetime是3Begi nDate开始日期datetime4En dDate终止日期datetime5Operator操作员varchar(16)6Remark备注varchar(50)7Ad

34、dDate添加日期datetime8DelFlag删除标记int9ChFlag修改标记int10Tran sFlag传输标记int14.客户图书传标记（在数据库里没有该表）编号字段中文说明类型备注是否 为空是否 主键1CustomerID客户IDint查询系统表：15、Qry Types（查询类型）编号字段中文说明类型备注是否 为空是否 主键1QueryTypeID类型编码varchar(30)是2QueryTypeName类型名称varchar(32)16、Qry HeaderList（查询的表头列表）编号字段中文说明类型备注是否 为空是否 主键1Stateme ntID查询语句编号int是

35、2FieldID字段varchar(40)是3FieldWidth字段宽度int4FieldOrder字段顺序tin yi nt5FieldVisible字段是否显示tin yi nt17、Qry AIIFields(字段信息表)编号字段中文说明类型备注是否 为空是否 主键1FieldID字段varchar(40)是2FieldName字段中文名称varchar(40)3FieldType字段类型char(1)4CodeType对应的编码类型int5PrimaryFlag是否主键tin yi nt6Con diti on对应条件varchar(1600)18、Qry Statements（查询

36、语句列表）编号字段中文说明类型备注是否 为空是否 主键1Stateme ntID查询语句编号int是2TypelD所属查询类型varchar(30)3Stateme ntName查询语句名称varchar(30)4Pare ntStateme ntID上级查询语句int5SqlStateme nt查询语句text6Mai nTable主表名称varchar(30)7Primarykey主键名称varchar(100)8ProcessType是否分页检索tin yi nt9PageField分页字段名称varchar(60)10OrderID排列顺序smalli nt11ExecuteFlag是

37、否执行tin yi nt12IsSumSQL是否汇总查询tin yi nt13DelFlagValue对应DelFlag值int14RecycleFlag是否用于回收站tin yi nt15ExpandLevel可展开的层次tin yi nt16Remark备注varchar(600)19、Qry CIassifyStat（分类统计表）编号字段中文说明类型备注是 否为空是否 主键1Stateme ntID查询语句编号int是2ClassifyTableID分类表编号int是3TableName表名称varchar(32)4PrimaryKey关键字段varchar(32)5TargetFiel

38、d关联目标字段varchar(32)6ExpandLevel展开层次int7Operate比较运算符varchar(32)8SubCo ndtion附加条件varchar(300)9ShowField显示字段varchar(32)不在数据库里10TableName2表名称2varchar(32)不在数据库里11PrimaryKey2关键字段2varchar(32)不在数据库里12TargetField2关联目标字段2varchar(32)不在数据库里13ShowField2显示字段2varchar(32)不在数据库里14SubCo nditio n2附加条件2varchar(300)不在数据库

39、里20、Qry Conditions（存储的查询条件）编号字段中文说明类型备注是否 为空是否 主键1Co nditio nID存储查询条件编号int是2Stateme ntID查询语句编号int是3Name查询条件名称varchar(30)4Con diti on查询条件varchar(6000)5UserID对应用户varchar(20)6IsDefault是否默认查询tin yi nt7Con diti onO rder顺序smalli nt8Remark备注varchar(200)21、Qry FieldsList（开放的查询字段）编号字段中文说明类型备注是否 为空是否 主键1Stateme ntID查询语句编号int是2FieldID字段varchar(60)是3FieldOrder字段顺序in