库表基础全

INSERT INTO STUD_INFO
VALUES ('0401040112',N'孙强','06-07-1986',N'男',N'重庆市沙坪坝','023-82342348','400000',560);
INSERT INTO STUD_INFO
VALUES ('0401020103',N'李伟','09-01-1986',N'男',N'北京市大兴县','010-68902947','102600',560);
INSERT INTO stud_grade VALUES('0401010634',N'钱昆','0401010106',78);
INSERT INTO stud_grade VALUES('0401030213',N'郑芳','0401010106',95);
INSERT INTO stud_grade VALUES('0401010102',N'袁飞','0401010106',95);
INSERT INTO STUD_INFO
VALUES ('0401010811',N'张源','12-05-1986',N'男',N'北京市海淀区','010-64572345','100080',560);
INSERT INTO STUD_INFO
VALUES ('0401020201',N'赵明','08-06-1986',N'男',N'上海市浦东区','021-64897232','201700',560);
birthday DATETIME,
gender NCHAR(1),
address NVARCHAR(20),
telcode CHAR(12),
zipcode CHAR(6),
mark DECIMAL(3,0)
)
INSERT INTO STUD_INFO
VALUES ('0401030101',N'王刚','01-02-1986',N'男',N'#39;300000',560);
INSERT INTO STUD_INFO
VALUES ('0401050201',N'陈红','10-25-1986',N'女',N'武汉市汉口区','027-74565350','430000',560);
jysh_type NCHAR(2),
jysh_leader NVARCHAR(4)
)
CREATE TABLE classroom_info
(room_id CHAR(6) NOT NULL,
room_name NVARCHAR(8),
room_type NVARCHAR(5),
SIZE= 1,
MAXSIZE= 12,
FILEGROWTH= 10%
)
go
--建表stud_info
USE studentml
GO
CREATE TABLE stud_info
( stud_id CHAR(10) NOT NULL,
name NVARCHAR(4) NOT NULL,
)
CREATE TABLE teacher_info
(teacher_id CHAR(6) primary key,
name NVARCHAR(4) NOT NULL,
gender NCHAR(1),
age INT,
tech_title NVARCHAR(5),
INSERT INTO lesson_info VALUES('0401010104',N'IT市场营销',N'考查',30,1.5);
INSERT INTO lesson_info VALUES('0401010105',N'网络互联设备与配置',N'考查',60,2.0);
INSERT INTO lesson_info VALUES('0401010106',N'多媒体技术',N'考查',60,3.0);
--为lesson_info表中的course_id列建立主键约束
ALTER TABLE lesson_info
ADD CONSTRAINT pk_course_id PRIMARY KEY CLUSTERED(course_id)
--为学生成绩表stud_grade创建主键约束。
INSERT INTO stud_grade VALUES('0401010811',N'张源','0401010102',90);
INSERT INTO stud_grade VALUES('0401020201',N'赵明','0401010105',89);
INSERT INTO stud_grade VALUES('0401030101',N'王刚','0401010103',87);
room_device NVARCHAR(10),
room_size DECIMAL(3,0)
)
CREATE TABLE teach_schedule
(course_id CHAR(10) NOT NULL,
course_time DATETIME,
course_week CHAR(2),
course_mark DECIMAL(3,1)
)
CREATE TABLE stud_grade
(stud_id CHAR(10) NOT NULL,
name NVARCHAR(4) NOT NULL,
course_id CHAR(10) NOT NULL,
grade DECIMAL(4,1)
ADD CONSTRAINT de_gender DEFAULT N'男' FOR gender
--为表stud_info中的学号(stud_id)定义主键
ALTER TABLE stud_info
ADD CONSTRAINT PK_XH PRIMARY KEY CLUSTERED(stud_id)
)
CREATE TABLE dept_code
(
deptcode CHAR(2)primary key,
deptname NVARCHAR(10)
)
CREATE TABLE staffroom_info
(
jysh_id CHAR(4) primary key,
jysh_name NVARCHAR(10),
INSERT INTO teacher_info VALUES('010106',N'王吉林',N'男',32,N'讲师','020-34603232',1418,'0401010103');
INSERT INTO teacher_info VALUES('010102',N'邵云鹏',N'男',45,N'教授','020-34607811',1458,'0401010105');
CREATE TABLE lesson_info
(course_id CHAR(10) NOT NULL,
course_name NVARCHAR(12) NOT NULL,
course_type NCHAR(2) NOT NULL,
course_time INT NOT NULL,
SIZE= 3,
MAXSIZE= 50,
FILEGROWTH= 1)
LOG ON
(
NAME=studentml_log,
FILENAME='c:\program files\microsoft sql server\mssql.1\mssql\data\studentml_log.ldf',
ALTER TABLE stud_grade
ADD CONSTRAINT pk_stud_grade PRIMARY KEY CLUSTERED(stud_id,course_id)
--为学生成绩表stud_grade创建外键约束。
ALTER TABLE stud_grade
ADD CONSTRAINT fk_course_id FOREIGN KEY (course_id)
REFERENCES lesson_info (course_id)
--为学生成绩表stud_grade创建外键约束。
ALTER TABLE stud_grade