create table StudentSno char9 primary key Sname char20

表及数据 create table Student(Sno char(9) primary key, Sname char(20) unique, Ssex char (2), Sage smallint, Sdept char(20)); create table Course( Cno char(4) primary key, Cname char(40), Cpno char(4), Ccredit smallint, foreign key (Cpno) references Course(Cno)); create table SC ( Sno char(9), Cno char(4), Grade smallint, primary key (Sno, Cno), foreign key (Sno) references Student(Sno), foreign key (Cno) references Course(Cno)) Page 2

表及数据 --向表student插入数据 insert into student (sno, sname, ssex, sage, sdept)values('200215121', '李勇', 'M', 20, 'CS'); insert into student (sno, sname, ssex, sage, sdept)values('200215122', '刘晨', 'F', 19, 'CS'); insert into student (sno, sname, ssex, sage, sdept)values('200215123', '王敏', 'M', 18, 'MA'); insert into student (sno, sname, ssex, sage, sdept)values('200215125', '张丽', 'F', 19, 'IS'); --向表course插入数据 insert into course (cno, cname, cpno, ccredit)values('2', 'Maths', null, 2); insert into course (cno, cname, cpno, ccredit)values('6', 'DS', null, 2); insert into course (cno, cname, cpno, ccredit)values('7', 'PASCAL', '6', 4); insert into course (cno, cname, cpno, ccredit)values('5', 'HTML', null, 4); insert into course (cno, cname, cpno, ccredit)values('1', 'DB', '5', 4); insert into course (cno, cname, cpno, ccredit)values('3', 'IS', '1', 4); insert into course (cno, cname, cpno, ccredit)values('4', 'OS', '6', 3); --向表SC插入数据 insert into sc(sno, cno, grade) values ('200215121', '1', 92); insert into sc(sno, cno, grade) values ('200215121', '2', 85); insert into sc(sno, cno, grade) values ('200215121', '3', 88); insert into sc(sno, cno, grade) values ('200215122', '2', 90); insert into sc(sno, cno, grade) values ('200215122', '3', 80); Page 3

--说明：复制表(只复制表结构, 源表名：Student 新表名：Student_new) select * into Student_new from Student where 1<>1 --说明, 用DATEDIFF函数，判别是否达到法定投票年龄（18岁） alter table student add birthday datetime; update Student set birthday='1990 -12 -10' where Sno='200215121'; update Student set birthday='1993 -9 -30' where Sno='200215122'; update Student set birthday='1998 -5 -1' where Sno='200215125'; select * from student where datediff(year, birthday, getdate())>=18 Page 4

--说明：两张关联表，删除主表中已经在副表中没有的数据 delete from student where not exists ( select * from sc where student. sno=sc. sno ) --选出前n条数据 select top 2 * from sc --列出当前用户下的所有表, "U"表示用户 select name from sysobjects where type='U' --按姓氏笔画排序: Select * From Student Order By Sname Collate Chinese_PRC_Stroke_ci_as --从少到多 -数据加密 select pwdencrypt('原始密码') select pwdcompare('原始密码', '加密后密码') --= 1相同；否则不相同 Page 5

--CASE的用法 SELECT CASE WHEN grade IS NULL THEN '没有成绩' WHEN grade < 60 THEN '不及格' WHEN grade BETWEEN 60 and 89 THEN '平均成绩' ELSE '优秀' END AS '平均成绩', Cno , COUNT(*) as 数量 FROM SC GROUP BY CASE WHEN grade IS NULL THEN '没有成绩' WHEN grade < 60 THEN '不及格' WHEN grade BETWEEN 60 and 89 THEN '平均成绩' ELSE '优秀' END, Cno ORDER BY Cno Page 6