资源描述:
《sql server使用教程课后的实验数据库yggl》由会员上传分享,免费在线阅读,更多相关内容在教育资源-天天文库。
1、--实验2创建数据库和表usemastergoifexists(select*fromsys.databaseswherename='yggl')dropdatabaseygglgocreatedatabaseygglonprimary(name=yggl_data,filename='D:17李利巧yggl_data.mdf',size=10mb,maxsize=50mb,filegrowth=5%)logon(name=yggl_log,filename='D:17李利巧yggl_log
2、.ldf',size=2mb,maxsize=5mb,filegrowth=1mb)go--dropdatabaseyggluseygglgoifexists(select*fromsys.objectswherename='Employees')droptableEmployeesgocreatetableEmployees(EmployeeIDchar(6)notnullprimarykey,Namechar(10)notnull,Educationchar(4)notnull,Birthday
3、datenotnull,Sexbitnotnulldefault1,WorkYeartinyintnull,Addressvarchar(40)null,PhoneNumberchar(12)null,DepartmentIDchar(3)notnull)goifexists(select*fromsys.objectswherename='Departments')droptableEmployeesgocreatetableDepartments(DepartmentIDchar(3)notnu
4、llprimarykey,DepartmentNamechar(20)notnull,Notevarchar(100)null)goifexists(select*fromsys.objectswherename='Salary')droptableEmployeesgocreatetableSalary(EmployeeIDchar(6)notnullprimarykey,InComefloatnotnull,OutComefloatnotnull)go--实验3表数据插入、修改和删除insert
5、intoEmployeesvalues('000001','王林','大专','1966-01-23',1,8,'中山路32-1-508','83355668','2'),('010008','伍荣华','本科','1976-03-28',1,3,'北京东路100-2','83342468','1'),('020010','王向容','硕士','1982-12-09',1,2,'西牌楼10-0-108','4567824','1'),('020018','李丽','大专','1960-07-30',
6、0,6,'中山路东102-2','33562668','1'),('102201','刘明','本科','1972-10-18',1,3,'虎距路100-2','5745668','5'),('102208','朱骏','硕士','1965-09-28',1,2,'牌楼巷5-3-106','89745668','5'),('108991','钟敏','硕士','1979-08-10',0,4,'中山路10-3-105','5463668','3'),('111006','张石兵','本科','197
7、4-10-01',1,1,'解放路34-1-203','989055668','5'),('210678','林涛','大专','1977-04-02',1,2,'中山北路24-35','845534','3'),('302566','李玉珉','本科','1968-09-20',1,3,'热和路209-3','8387432','4'),('308759','叶凡','本科','1978-11-18',1,2,'北京西路3-7-52','8339879','4'),('504209','陈林琳',
8、'大专','1969-09-03',0,5,'汉中路120-4-12','83398768','4')go--droptableEmployeesselect*fromEmployeesgodeletefromEmployeeswhereEmployeeID='000001'goinsertintoEmployeesvalues('000001','王林','大专','1966-01-23',1,8,'中山路32-1-508','83355668','2')godel