实验1与2解答.docVIP

set nocount on set dateformat ymd use master go /*create login*/ if not exists(select *from syslogins where name=user01) exec sp_addlogin user01,user01 go /*create database*/ if exists(select *from sysdatabases where name=myDemo) drop database myDemo go create database myDemo on primary (name=myDemoData, filename=e:\SQLDatabase\myDemo.mdf, size=1, maxsize=5, filegrowth=1) log on (name=myDemoLog, filename=e:\SQLDatabase\myDemo.log, size=1, maxsize=5, filegrowth=1) go /*data mydatabase*/ use myDemo go /* add user*/ exec sp_adduser user01,user01 go ①分别为每张表建立主键约束。 ②为相关表建立外键约束。 ③ 在表employee加入CHECK约束：输入的员工编号必须以E开头的5位数编号，性别只能为M/F。 ④为销售主表sales中的发票编号字段建立UNIQUE约束。 /*员工人事表*/ printcreat table employee go CREATE TABLE employee( emp_no char(5) not null /*员工编号*/ check(emp_no like E[0-9][0-9][0-9][0-9]), emp_name char(10) not null, /*员工姓名*/ sex char(1) not null, /*员工性别*/ dept char(4) not null, /*所属部门*/ title char(6) not null, /*职称*/ date_hired datetime not null, /*到职日*/ birthday datetime null, /*员工生日*/ salary int not null, /*薪水*/ addr char(50) null, /*员工住址*/ constraint ckSex check( sex in (F,M)), constraint pkEmployee primary key(emp_no) ) go grant all on employee to user01 go /*人事表（employee）数据：*/ insert employee values(E0001,喻自强,M,会计,科长, 19610206,48000,南京市青海路) insert employee values(E0002,张小梅,F,业务,职员, 19710328,32000,上海市北京路) insert employee values(E0003,张小娟,F,业务,职员, 19720328,32000,上海市南京路) insert employee values(E0004,张露, F,业务,部长, 19700328,52000,南昌市八一大道) insert employee values(E0005,张小东,M,业务,职员, 19720328,32000,南昌市阳明路) insert employee values(E0006,陈辉, M,业务,职员, 19700328,32000,南昌市青山路) insert employee values(E0007,韩梅, F,业务,职员, 19701128,32000,上海市浦东) insert employee values(E0008,刘风, F,业务,职员, 1971022