数据库练习(知识点蛮多)
/* Navicat SQL Server Data Transfer Source Server : . Source Server Version : 105000 Source Host : .:1433 Source Database : testlianxi Source Schema : dbo Target Server Type : SQL Server Target Server Version : 105000 File Encoding : 65001 Date: 2015-11-10 15:52:19 */ -- ---------------------------- -- Table structure for cj_t -- ---------------------------- DROP TABLE [dbo].[cj_t] GO CREATE TABLE [dbo].[cj_t] ( [id] int NOT NULL IDENTITY(1,1) , [xsid] int NOT NULL , [kcid] int NOT NULL , [cj] float NOT NULL , [t] int NOT NULL ) GO -- ---------------------------- -- Records of cj_t -- ---------------------------- SET IDENTITY_INSERT [dbo].[cj_t] ON GO SET IDENTITY_INSERT [dbo].[cj_t] OFF GO -- ---------------------------- -- Table structure for kc_t -- ---------------------------- DROP TABLE [dbo].[kc_t] GO CREATE TABLE [dbo].[kc_t] ( [id] int NOT NULL IDENTITY(1,1) , [kcmc] varchar(60) NOT NULL ) GO -- ---------------------------- -- Records of kc_t -- ---------------------------- SET IDENTITY_INSERT [dbo].[kc_t] ON GO SET IDENTITY_INSERT [dbo].[kc_t] OFF GO -- ---------------------------- -- Table structure for xs_t -- ---------------------------- DROP TABLE [dbo].[xs_t] GO CREATE TABLE [dbo].[xs_t] ( [id] int NOT NULL IDENTITY(1,1) , [xh] varchar(12) NOT NULL , [xm] varchar(50) NOT NULL , [bj] varchar(20) NOT NULL ) GO -- ---------------------------- -- Records of xs_t -- ---------------------------- SET IDENTITY_INSERT [dbo].[xs_t] ON GO SET IDENTITY_INSERT [dbo].[xs_t] OFF GO -- ---------------------------- -- Indexes structure for table cj_t -- ---------------------------- -- ---------------------------- -- Primary Key structure for table cj_t -- ---------------------------- ALTER TABLE [dbo].[cj_t] ADD PRIMARY KEY ([id]) GO -- ---------------------------- -- Indexes structure for table kc_t -- ---------------------------- -- ---------------------------- -- Primary Key structure for table kc_t -- ---------------------------- ALTER TABLE [dbo].[kc_t] ADD PRIMARY KEY ([id]) GO -- ---------------------------- -- Indexes structure for table xs_t -- ---------------------------- -- ---------------------------- -- Primary Key structure for table xs_t -- ---------------------------- ALTER TABLE [dbo].[xs_t] ADD PRIMARY KEY ([id]) GO INSERT INTO [dbo].[kc_t] ([id], [kcmc]) VALUES (N'1', N'数学') INSERT INTO [dbo].[kc_t] ([id], [kcmc]) VALUES (N'2', N'英语') INSERT INTO [dbo].[kc_t] ([id], [kcmc]) VALUES (N'3', N'语文') INSERT INTO [dbo].[kc_t] ([id], [kcmc]) VALUES (N'4', N'科学') INSERT INTO [dbo].[kc_t] ([id], [kcmc]) VALUES (N'5', N'政治') insert into xs_t values('3','Lily','三一班') insert into cj_t values(IDENT_CURRENT('xs_t'),1,55,1) insert into cj_t values(IDENT_CURRENT('xs_t'),2,73,1) insert into cj_t values(IDENT_CURRENT('xs_t'),3,66,1) insert into cj_t values(IDENT_CURRENT('xs_t'),4,75,1) insert into cj_t values(IDENT_CURRENT('xs_t'),5,73,1) insert into xs_t values('1','Tom','三三班') insert into cj_t values(IDENT_CURRENT('xs_t'),1,67,1) insert into cj_t values(IDENT_CURRENT('xs_t'),2,62,1) insert into cj_t values(IDENT_CURRENT('xs_t'),3,78,1) insert into cj_t values(IDENT_CURRENT('xs_t'),4,60,1) insert into cj_t values(IDENT_CURRENT('xs_t'),5,72,1) insert into xs_t values('2','Lisa','三一班') insert into cj_t values(IDENT_CURRENT('xs_t'),1,97,1) insert into cj_t values(IDENT_CURRENT('xs_t'),2,76,1) insert into cj_t values(IDENT_CURRENT('xs_t'),3,56,1) insert into cj_t values(IDENT_CURRENT('xs_t'),4,76,1) insert into cj_t values(IDENT_CURRENT('xs_t'),5,71,1) insert into xs_t values('5','Jim','三二班') insert into cj_t values(IDENT_CURRENT('xs_t'),1,77,1) insert into cj_t values(IDENT_CURRENT('xs_t'),2,98,1) insert into cj_t values(IDENT_CURRENT('xs_t'),3,58,1) insert into cj_t values(IDENT_CURRENT('xs_t'),4,59,1) insert into cj_t values(IDENT_CURRENT('xs_t'),5,79,1) insert into xs_t values('4','Sam','三一班') insert into cj_t values(IDENT_CURRENT('xs_t'),1,91,1) insert into cj_t values(IDENT_CURRENT('xs_t'),2,68,1) insert into cj_t values(IDENT_CURRENT('xs_t'),3,89,1) insert into cj_t values(IDENT_CURRENT('xs_t'),4,68,1) insert into cj_t values(IDENT_CURRENT('xs_t'),5,90,1) insert into xs_t values('6','Jack','三五班') insert into cj_t values(IDENT_CURRENT('xs_t'),1,66,1) insert into cj_t values(IDENT_CURRENT('xs_t'),2,90,1) insert into cj_t values(IDENT_CURRENT('xs_t'),3,57,1) insert into cj_t values(IDENT_CURRENT('xs_t'),4,55,1) insert into cj_t values(IDENT_CURRENT('xs_t'),5,69,1) insert into xs_t values('3','Lily','三一班') insert into cj_t values(IDENT_CURRENT('xs_t'),1,86,2) insert into cj_t values(IDENT_CURRENT('xs_t'),2,100,2) insert into cj_t values(IDENT_CURRENT('xs_t'),3,80,2) insert into cj_t values(IDENT_CURRENT('xs_t'),4,99,2) insert into cj_t values(IDENT_CURRENT('xs_t'),5,87,2) insert into xs_t values('1','Tom','三三班') insert into cj_t values(IDENT_CURRENT('xs_t'),1,63,2) insert into cj_t values(IDENT_CURRENT('xs_t'),2,64,2) insert into cj_t values(IDENT_CURRENT('xs_t'),3,71,2) insert into cj_t values(IDENT_CURRENT('xs_t'),4,94,2) insert into cj_t values(IDENT_CURRENT('xs_t'),5,62,2) insert into xs_t values('2','Lisa','三一班') insert into cj_t values(IDENT_CURRENT('xs_t'),1,98,2) insert into cj_t values(IDENT_CURRENT('xs_t'),2,63,2) insert into cj_t values(IDENT_CURRENT('xs_t'),3,56,2) insert into cj_t values(IDENT_CURRENT('xs_t'),4,76,2) insert into cj_t values(IDENT_CURRENT('xs_t'),5,84,2) insert into xs_t values('5','Jim','三二班') insert into cj_t values(IDENT_CURRENT('xs_t'),1,72,2) insert into cj_t values(IDENT_CURRENT('xs_t'),2,57,2) insert into cj_t values(IDENT_CURRENT('xs_t'),3,93,2) insert into cj_t values(IDENT_CURRENT('xs_t'),4,62,2) insert into cj_t values(IDENT_CURRENT('xs_t'),5,98,2) insert into xs_t values('4','Sam','三一班') insert into cj_t values(IDENT_CURRENT('xs_t'),2,82,2) insert into cj_t values(IDENT_CURRENT('xs_t'),3,83,2) insert into cj_t values(IDENT_CURRENT('xs_t'),4,95,2) insert into cj_t values(IDENT_CURRENT('xs_t'),5,75,2) insert into xs_t values('6','Jack','三五班') insert into cj_t values(IDENT_CURRENT('xs_t'),1,85,2) insert into cj_t values(IDENT_CURRENT('xs_t'),2,87,2) insert into cj_t values(IDENT_CURRENT('xs_t'),3,82,2) insert into cj_t values(IDENT_CURRENT('xs_t'),4,82,2) insert into cj_t values(IDENT_CURRENT('xs_t'),5,65,2) insert into xs_t values('3','Lily','三一班') insert into cj_t values(IDENT_CURRENT('xs_t'),1,96,3) insert into cj_t values(IDENT_CURRENT('xs_t'),2,69,3) insert into cj_t values(IDENT_CURRENT('xs_t'),3,74,3) insert into cj_t values(IDENT_CURRENT('xs_t'),4,81,3) insert into cj_t values(IDENT_CURRENT('xs_t'),5,56,3) insert into xs_t values('1','Tom','三三班') insert into cj_t values(IDENT_CURRENT('xs_t'),1,96,3) insert into cj_t values(IDENT_CURRENT('xs_t'),2,92,3) insert into cj_t values(IDENT_CURRENT('xs_t'),3,63,3) insert into cj_t values(IDENT_CURRENT('xs_t'),4,63,3) insert into cj_t values(IDENT_CURRENT('xs_t'),5,83,3) insert into xs_t values('2','Lisa','三一班') insert into cj_t values(IDENT_CURRENT('xs_t'),1,57,3) insert into cj_t values(IDENT_CURRENT('xs_t'),2,80,3) insert into cj_t values(IDENT_CURRENT('xs_t'),3,61,3) insert into cj_t values(IDENT_CURRENT('xs_t'),4,80,3) insert into cj_t values(IDENT_CURRENT('xs_t'),5,64,3) insert into xs_t values('5','Jim','三二班') insert into cj_t values(IDENT_CURRENT('xs_t'),1,84,3) insert into cj_t values(IDENT_CURRENT('xs_t'),2,57,3) insert into cj_t values(IDENT_CURRENT('xs_t'),3,90,3) insert into cj_t values(IDENT_CURRENT('xs_t'),4,86,3) insert into cj_t values(IDENT_CURRENT('xs_t'),5,94,3) insert into xs_t values('4','Sam','三一班') insert into cj_t values(IDENT_CURRENT('xs_t'),1,95,3) insert into cj_t values(IDENT_CURRENT('xs_t'),2,58,3) insert into cj_t values(IDENT_CURRENT('xs_t'),4,99,3) insert into cj_t values(IDENT_CURRENT('xs_t'),5,61,3) insert into xs_t values('6','Jack','三五班') insert into cj_t values(IDENT_CURRENT('xs_t'),1,94,3) insert into cj_t values(IDENT_CURRENT('xs_t'),2,96,3) insert into cj_t values(IDENT_CURRENT('xs_t'),3,68,3) insert into cj_t values(IDENT_CURRENT('xs_t'),4,77,3) insert into cj_t values(IDENT_CURRENT('xs_t'),5,57,3)