学生信息表，方便测试

create  table student--创建student数据表
(
   s_no char(10) not null,  --学号
   s_name nchar(10),       --姓名
   s_sex  char(2),         --性别
   borndate  smalldatetime,--出生日期
   ClassName nvarchar(50), --班级名称
   Telephone varchar(11),  --电话号码
   EnrollDate smallDateTime,--注册日期
   Address  nvarchar(50),   --地址
   Comment  text           --备注
)
 
create table teacher
(
   t_no  char(10)  not null,
   t_name  nchar(10),
   t_sex  char(2),
   BornDate  smallDateTime,
   Dept_No  char(10)
)
 
create table Course
(
   c_No char(10)  not null,
   c_Name nvarchar(30),
   c_Type  nchar(10),
   c_Credit  Decimal(5,1),
   c_Des  text
)
 
create table T_User
(
   UserName  char(10)   not null,
   Password   char(10),
   Grade  nchar(10),
   Description  text
)
 
create table t_c
(
   t_no char(10)  not null,
   c_no  char(10) not null,
   Semester  nvarchar(30)
)
 
create table s_c
(
   s_no  char(10)  not  null,
   c_no  char(10)  not  null,
   score int
)
 
create table Dept
(
   d_no  char(10)  not  null,
   DeptName  nvarchar(20),
   Duty  nvarchar(50)
)
use Stu
go
--修改数据表
alter table student
add idcard char(18)  --增加身份证列
--修改数据表删除列
alter table student
drop column idcard 
--修改数据表修改数据类型
alter table student
alter column borndate datetime 
 
 
--插入数据
--向student表中插入数据
insert into student values('05331101','张曼','女','1993-05-03 00:00:00',
'计算机应用','18602354822','2005-09-01 00:00:00','辽宁沈阳',null)
insert into student values('05331102','刘迪','女','1994-10-20 00:00:00',
'计算机应用','13893220935','2005-09-01 00:00:00','辽宁抚顺',null)
insert into student values('05331103','刘凯','男','1982-05-30 00:00:00',
'计算机应用','18641330245','2005-09-01 00:00:00','辽宁鞍山',null)
insert into student values('05331104','王越','男','1991-09-19 00:00:00',
'计算机应用','18641320940','2005-09-01 00:00:00','辽宁营口',null)
insert into student values('05331105','李楠','女','1995-03-16 00:00:00',
'计算机应用','18641328449','2005-09-01 00:00:00','辽宁锦州',null)
insert into student values('05331106','胡栋','男','1992-07-03 00:00:00',
'计算机应用','18641326996','2005-09-01 00:00:00','辽宁沈阳',null)
insert into student values('06102534','杨超','男','1990-10-01 00:00:00',
'机械设计','13893245623','2006-09-01 00:00:00','河北石家庄',null)
 
--向教师表中插入数据
insert into teacher values('0001','王庆','男','1965-3-9','1')
insert into teacher values('0002','林志玲','女','1980-7-12','2')
insert into teacher values('0003','黎明','男','1975-5-18','3')
insert into teacher values('0004','郭靖','男','1953-10-30','4')
insert into teacher values('0005','黄蓉','女','1973-8-27','1')
insert into teacher values('0006','林黛玉','女','1983-5-27','2')
 
--向成绩表中录入数据
insert into s_c values('05331101','0001',95)
insert into s_c values('05331101','0002',98)
insert into s_c values('05331101','0003',89)
insert into s_c values('05331102','0001',93)
insert into s_c values('05331102','0002',99)
insert into s_c values('05331102','0003',95)
insert into s_c values('05331103','0001',84)
insert into s_c values('05331103','0002',80)
insert into s_c values('05331103','0003',94)
 
--向部门表中插入数据
insert into Dept values('1','人事处','处理人事工作')
insert into Dept values('2','学生处','处理学生工作')
insert into Dept values('3','教务处','处理教学相关工作')
insert into Dept values('4','校园管理处','处理学校所有后勤辅助工作')
insert into Dept values('5','机械与运载学院','教学单位')
insert into Dept values('6','信息与控制学院','教学单位')
insert into Dept values('7','经济与管理学院','教学单位')
 
--向课程表中插入数据
insert into Course values('0001','大学英语','考试','10','英语相关知识')
insert into Course values('0002','数据库原理及应用','考试','7','数据库相关知识')
insert into Course values('0003','数据结构','考查','5','程序设计基础')
--向授课表中插入数据
insert into t_c values('0001','0001','2011-2012(1)')
insert into t_c values('0001','0002','2011-2012(2)')
insert into t_c values('0002','0001','2011-2012(1)')
insert into t_c values('0003','0001','2011-2012(1)')
insert into t_c values('0004','0002','2011-2012(2)')
insert into t_c values('0005','0002','2011-2012(2)')
insert into t_c values('0006','0003','2011-2012(2)')
insert into t_c values('0006','0002','2011-2012(1)')