sql强化.docVIP

sql强化 (2011-03-21 17:29:33)  HYPERLINK javascript:; 转载 标签：  HYPERLINK /c.php?t=blogk=%D4%D3%CC%B8ts=bpoststype=tag \t _blank 杂谈create database mydb//建数据库 go use mydb ////////////////建student表 create table student ( --学号 sno varchar(3) not null primary key,//主键 --姓名 sname varchar(4) not null, --性别 ssex varchar(2) not null, --出生年月 sbirthday datetime, --所在班级 class varchar(5) ) ////////////创建teacher表 create table teacher ( --教工编号 tno varchar(3) not null primary key,//主键 ? --教工姓名 tname varchar(4) not null, --教工性别 tsex varchar(2) not null, --教工出生日期 tbirthday datetime, --职称 prof varchar(6), --所在部门 depart varchar(10) ) //////////////创建课程表 create table course ( --课程号 cno varchar(5) not null primary key,//主键 ? --课程名称 cname varchar(10) not null, --教工编号 tno varchar(3) references teacher(tno)//外键 ) /////////创建分数表 create table score ( sid int identity(1,1) not null primary key,//主键，自增长列 --学号 sno varchar(3) not null references student(sno),//外键 --课程号 cno varchar(5) not null references course(cno),//外键 --成绩 degree decimal(4,1) ) ? //插入数据 insert into student values(108,曾华,男,1977-09-01,95033) insert into student values(105,匡明,男,1975-10-02,95031) insert into student values(107,王丽,女,1976-01-23,95033) insert into student values(101,李军,男,1976-02-20,95033) insert into student values(109,王芳,女,1975-02-10,95031) insert into student values(103,陆君,男,1974-06-03,95031) ? insert into teacher values(804,李诚,男,1958-12-02,副教授,计算机系) insert into teacher values(856,张旭,男,1969-03-12,讲师,电子工程系) insert into teacher values(825,王萍,女,1972-05-05,助教,计算机系) insert into teacher values(831,刘冰,女,1958-08-14,助教,电子工程系) ? insert into course values(3-105,计算机导论,825) insert into course values(3-245,操作系统,804) insert into course values(6-166,数字电路,856) insert into course values(9-888,高等数学,831) ? ? insert into score values(103,3-245,86) insert into score values(105,3-245,75) insert into score values(109,3-245,68) insert into score values(103,3-105,92) insert into score values(105,3-105,88) insert into score values(109