数据库语句总结.docx

实验四建立数据库 create database TSJYon(name=TSJY,filename =E:\TSJY.mdf,size=10MB,maxsize=100MB,filegrowth=1MB )log on(name=TSJY_log,filename=E:\TSJYT.ldf,size=5MB,maxsize=50MB,filegrowth=1MB);建表 use S_Tcreate table Student(Sno char (11) not null primary key,Sname char (8) not null,Ssex char (2) not null,Sage Tinyint not null,Sdept char (10) not nullcheck (Ccredit =1 and Ccredit =6)（学分取1-6的整数）check (Grade =1 and Grade =100)（成绩在0-100之间）);将course表的cpno的长度改为3alter table Course alter column cpno char (3)4、给student表中的ssex列的增加默认约束，默认值为“男”alter table Student add constraint df_Student_Ssex default 男 for Ssex;5、为course表增加一列teacher,char(8)alter table Course add teacher char(8);6、删除course表的teacher列alter table Course drop column teacher;7、插入数据insert into S(SNO , SNAME , STATUS , CITY)values (S1,精益,20,天津),(S2,盛锡,10,北京),(S3,东方红,30,北京),(S4,丰泰盛,20,天津),(S5,为民,50,上海);8、为S表设一个主键alter table S add primary key (SNO);9、为S表增加一个唯一索引s_snocreate unique index s_sno on S (SNO);为SPJ表增加索引spj_sno_pno_jno,要求索引列sno用降序,pno、jno用升序create index spj_sno_pno_jno on SPJ (SNO desc,pno,jno);为HISD的Doctor表按医生ID（dID）升序建立唯一索引create unique index Dindex on Doctor（dID）为HISD的Doctor表按医生ID（dID）降序建立唯一索引create unique index Dindex on Doctor（dID desc）10、为SPJ表加外键约束（若建表时已经建立外键，可以先删除某个已有外键，重新建立alter table SPJ add constraint FK_SNO foreign key (SNO) references S(SNO); alter table SPJ add constraint FK_PNO foreign key (PNO) references P(PNO); alter table SPJ add constraint FK_JNO foreign key (JNO) references J(JNO);11、为SPJ表增加列qty的检查约束,要求qty0alter table SPJ add constraint CK_SPJ_qty check(qty0);12、删除SPJ表的检查约束CK_SPJ_qtyalter table SPJ drop constraint CK_SPJ_qty13、查询全体学生的学号与姓名select Sno，SnameFROM Student14、查询全体学生的基本信息select * FROM Student15、查询全体学生的姓名、出生年份及所在系use s_tSELECT Sname,year(getdate()) -sage birthyear,sdeptFROM student16、查询选修了课程的学生学号。（掌握distinct的用法）SELECT distinct Sno FROM SC17、查询年龄在18-20岁间的06级的学生姓名及学号SELECT Sname,Sno FROM student WHERE Sage BETWEEN 18 AND 20 AND SNO like 2006%18、查询信息系（IS）、数学系（MA）和计算机科学系（CS）学生的姓名和性别SELECT Sname，