1 001
2 USER MASTER ;/*切换到MASTER数据库*/
3 DROP DATABASE student_course;/*先检测有没有存在student_course数据库，有则删除*/
4 /*创建student_course数据库，并打开*/
5 CREATE DATABASE student_course ON
6 (
7 NAME=student_course_data,
8 FILENAME='C:\0801110404\080111040420101108\student_course_data.mdf',
9 SIZE=10MB,
10 FILEGROWTH=15%
11 )
12 LOG ON
13 (
14 NAME=student_course_log,
15 FILENAME='C:\0801110404\080111040420101108\student_course_log.ldf',
16 SIZE=3MB,
17 FILEGROWTH=15%
18 );
19 002
20 ---USE student_course DATABASE-------------------------------------------
21 USE student_course;
22 DROP TABLE Student;
23 DROP TABLE Course;
24 DROP TABLE SC;
25 ---CREATE Student TABLE---------------------------------------------------
26 CREATE TABLE Student
27 (
28 Sno CHAR(9)PRIMARY KEY,
29 Sname CHAR(20) UNIQUE,
30 Ssex CHAR(2),
31 Sage SMALLINT,
32 Sdept CHAR(20)
33 );
34 ---CREATE Course TABLE---------------------------------------------------
35 CREATE TABLE Course
36 (
37 Cno CHAR(4)PRIMARY KEY,
38 Cname CHAR(40),
39 Cpno CHAR(4), /*Cpno表示先修课程*/
40 Ccredit SMALLINT, /*Cpno表示学分*/
41 FOREIGN KEY(Cpno)REFERENCES Course(Cno)/*外码Cpno参照了表Course的Cno*/
42 );
43 ---CREATE SC TABLE--------------------------------------------------------
44 CREATE TABLE SC
45 (
46 Sno CHAR(9),
47 Cno CHAR(4),
48 Grade SMALLINT,
49 PRIMARY KEY(Sno,Cno), /*Sno是外码，Student是被参照表*/
50 FOREIGN KEY(Cno)REFERENCES Course(Cno)/*Cno是外码，Course是被参照表*/
51 );
52
53 003
54 ---ALTER 的使用CREATE Student2 TABLE-----------------------------------------
55 CREATE TABLE Student2
56 (
57 Sno CHAR(9)PRIMARY KEY,
58 Sname CHAR(20) UNIQUE,
59 Ssex CHAR(2),
60 Sage SMALLINT,
61 Sdept CHAR(20)
62 );
63 SELECT * FROM Student2;
64
65 ---向student2表增加"入学时间列"------------------------------------------------
66 ALTER TABLE Student2
67 ADD S_entrance DATETIME;
68
69 004
70 ---将student表中的年龄的数据类型修改为int类型-----------------------------------
71 EXEC SP_HELP STUDENT /*查看studnet表属性*/
72
73 ALTER TABLE Student
74 ALTER COLUMN Sage INT;
75 ---修改后的年龄数据类型如下，由原来的smallint类型修改为int类型-------------------
76
77 005
78 ---增加课程名称必须取唯一的约束条件----------------------------------------------
79 ALTER TABLE Course
80 ADD UNIQUE(Cname);
81 EXEC SP_HELPINDEX Course;
82
83 006
84 ---向student表中插入四条记录---------------------------------------------------
85 INSERT INTO Student(Sno,Sname,Ssex,Sage,Sdept)
86 VALUES('200215121','李勇','男',20,'CS');
87 INSERT INTO Student(Sno,Sname,Ssex,Sage,Sdept)
88 VALUES('200215122','刘晨','女',19,'CS');
89 INSERT INTO Student(Sno,Sname,Ssex,Sage,Sdept)
90 VALUES('200215123','王敏','女',18,'MA');
91 INSERT INTO Student(Sno,Sname,Ssex,Sage,Sdept)
92 VALUES('200215125','张立','男',18,'TS');
93 SELECT * FROM Student;
94
95 ---向Course表插入记录-------------------------------------------------------
96 INSERT INTO Course(Cno,Cname,Ccredit)VALUES('2','数学',2);
97 INSERT INTO Course(Cno,Cname,Ccredit)VALUES('6','数据处理',2);
98 INSERT INTO Course(Cno,Cname,Cpno,Ccredit)VALUES('4','操作系统','6',3);
99 INSERT INTO Course(Cno,Cname,Cpno,Ccredit)VALUES('7','PASCAL','6',4);
100 INSERT INTO Course(Cno,Cname,Cpno,Ccredit)VALUES('5','数据结构','7',4);
101 INSERT INTO Course(Cno,Cname,Cpno,Ccredit)VALUES('1','数据库','5',4);
102 INSERT INTO Course(Cno,Cname,Cpno,Ccredit)VALUES('3','信息系统','1',4);
103 SELECT * FROM Course;
104
105 ---向Course表插入记录-------------------------------------------------------
106 INSERT INTO SC(Sno,Cno,Grade)VALUES('200215121','1',92);
107 INSERT INTO SC(Sno,Cno,Grade)VALUES('200215121','2',85);
108 INSERT INTO SC(Sno,Cno,Grade)VALUES('200215121','3',88);
109 INSERT INTO SC(Sno,Cno,Grade)VALUES('200215122','2',90);
110 INSERT INTO SC(Sno,Cno,Grade)VALUES('200215122','3',80);
111 SELECT * FROM SC;
112
113 007
114 ---例4对每一个系，求学生的平均年龄，并把结果存入数据库。----------------------------
115 DROP TABLE Dept_age;
116 CREATE TABLE Dept_age
117 (
118 dept char(15),/*系名*/
119 Avg_age SMALLINT/*学生平均年龄*/
120 );
121 INSERT INTO Dept_age(Sdept,Avg_age)
122 SELECT Sdept,Avg(Sage)
123 FROM Student
124 GROUP BY Sdept
125 SELECT * FROM Dept_age;/*查看每一个系的学生平均年龄结果*/
126
127 ---例5将学生的sno为200215121的sage修改为25------------------------------------
128 SELECT * FROM student
129
130 UPDATE student
131 SET Sage=25
132 WHERE Sno='200215121';
133 SELECT * FROM student;
134
135 ---例6将所有学生的年龄（sage）增加一岁----------------------------------------
136 UPDATE Student
137 SET Sage=Sage+1;
138 SELECT * FROM student;
139
140 008
141 ---例将计算机科学系所有学生的成绩(Grade)设置为0--------------------------------
142 SELECT * FROM SC;
143
144 UPDATE SC
145 SET Grade=0
146 WHERE 'CS'=
147 (
148 SELECT Sdept
149 FROM Student
150 WHERE Student.Sno=SC.Sno
151 );
152 SELECT * FROM SC;
153
154 ---向student表插入一条记录-------------------------------------------------
155 INSERT INTO Student(Sno,Sname,Ssex,Sage,Sdept)
156 VALUES('200215128','郭靖','男',19,'IS');
157 SELECT * FROM student WHERE Sno='200215128';
158
159 ---例删除学号（sno）为的学生记录
160 DELETE
161 FROM Student
162 WHERE Sno='200215128';
163 SELECT * FROM student
164
165 ---删除计算机科学系所有学生选课记录------------------------------------------
166
167 DELETE
168 FROM SC
169 WHERE 'CS'=
170 (
171 SELECT Sdept
172 FROM Student
173 WHERE Student.Sno=SC.Sno
174 );
175
176
