SQL-Sever的使用及语句整理

一、数据库的创建1、企业管理器操作：右键创建数据库，设置和修改属性2、SQL语句CREATE DATABASE XSGL_20093598 ON /*建立名为“XSGL_20093598”数据库*/(NAME = XSGL_20093598,FILENAME = D:Microsoft SQL ServerMSSQL.1XSGL_20093598DataXSGL_20093598_data.mdf, SIZE =20, /*文件初始大小*/MAXSIZE = 500, /*文件最大空间*/FILEGROWTH = 5) /*文件每次增量*/LOG ON /*数据库的日志*/(NAME = XSGL_20093598_log,FILENAME = D:Microsoft SQL ServerMSSQL.1XSGL_20093598DataXSGL_20093598_log.ldf,SIZE =5, /*文件初始大小*/MAXSIZE = 50 , /*文件最大空间*/FILEGROWTH = 1) /*文件每次增量*/二、数据库的查看1、企业管理器操作：对象资源管理器- XSGL1_20093598，右击“属性”，选择“文件”。2、SQL语句：sp_helpdb XSGL1_20093598三、数据库的属性修改1、企业管理器操作：将XSGL1数据库更名为XSGL2。（提示：使用分离数据及附加数据，再加入时更名即可）。2、SQL语句：将XSGL日志文件的最大值由50MB更改为100MB,T-SQL语句：ALTER DATABASE XSGL1_20093598MODIFY FILE (NAME = XSGL1_20093598_log,MAXSIZE = 100 )将数据库XSGL1更名为XSGL2。（提示：存储过程sp_renamedb或用alter database命令）:Alter DATABASE XSGL1_20093598MODIFY NAME = XSGL2_20093598三、数据库的删除1、企业管理器操作：断开数据库的连接，然后单击右键，选择删除。2、SQL语句：GODrop DATABASE XSGL2_20093598四、数据表的创建1、企业管理器操作：相应数据库，右键新建数据表，设置列名及约束，标注2、SQL语句：CREATE TABLE Course(Cno CHAR(3) PRIMARY KEY, Cname VARCHAR(20) UNIQUE, Total_perior smallint NOT NULL,Credit tinyint check( Credit0 and Credit=0 and grade0 and Stature 150查询各个课程号相应的选课人数。 select cno,count(Sno) 人数 from SCgroup by cno11、使用聚合函数 查询学生总人数。 select count(*) as 学生总人数 from Student计算“002”号课程的学生平均成绩、最高分、最低分。 select avg(grade) 平均成绩, max(grade) 最高分, min(grade)最低分from SC where cno=00212、使用Compute和compute by子句进行查询（用Compute和compute by既能浏览数据又能看到统计的结果。 Compute 集合函数（列名）：获得该列的统计值，及该列的详细信息（未分组）。）汇总每个学生的学号及总成绩。 T-SQL语句： -select sno ,grade 总成绩 from SC order by snocompute sum(grade)-select sno ,grade 总成绩 from SCorder by snocompute sum(grade) by sno按学号汇总出每个学生的学号及总成绩、最高分、最低分，及所有学生总成绩。 select sno ,grade from SCorder by snocompute sum(grade) ,max(grade),min(grade) by snocompute sum(grade)13、使用企业管理器进行数据查询 十五、复杂查询查询比“林红”年纪大的男学生信息。SELECT * FROM StudentWHERE Sex=男 and Birth (SELECT BirthFROM Student WHERE Sname=林红)查询与“张虹”在同一个班级的学生学号、姓名、家庭住址。select Student.Sno,Sname,Home_addr from Studentwhere Classno in (select Classno from Student where Sname=张虹)查询其他班级中比“051”班任一学生年龄大的学生的学号、姓名。带有ANY或ALL谓词的子查询语句：SELECT Sno,Sname FROM StudentWHERE Birth ANY (SELECT Birth FROM Student WHERE Classno=051)AND Classno 051用聚合函数实现：SELECT Sno,Sname FROM StudentWHERE Birth (SELECT max(Birth) FROM Student WHERE Classno=051)AND Classno 051查询选修了全部课程的学生姓名。SELECT Sname FROM StudentWHERE NOT EXISTS(SELECT * FROM Course /*没有一门课程是该生未选修的*/ WHERE NOT EXISTS (SELECT * FROM SC /* 该生没有选修这门课*/ WHERE Sno= Student.Sno AND Cno= Course.Cno)查询至少选修了学生“20050002”选修的全部课程的学生的学号，姓名。select Sno,Sname from Studentwhere Sno in(SELECT DISTINCT sno FROM SC SCX WHERE NOT EXISTS (SELECT * FROM SC SCY WHERE SCY.Sno = 20050002 AND NOT EXISTS (SELECT * FROM SC SCZ WHERE SCZ.Sno=SCX.Sno AND SCZ.Cno=SCY.Cno)检索选修了“高数”课且成绩至少高于选修课程号为“002”课程的学生的学号、课程号、成绩，并按成绩从高到低次序排列。select sno,cno,grade from SCwhere sno in(select SC1.sno from SC SC1 where cno in(select cno from Course where Cname=高等数学)and grade (select SC2.grade from SC SC2 where SC2.cno=002 and SC2.sno=SC1.sno) )order by sno,grade desc检索选修3门以上课程的学生的学号、总成绩（不统计不及格的课程），并要求按总成绩的降序排列出来。select sno,sum(grade) 总成绩 from SCwhere grade=60 and sno in(select sno from SCgroup by snohaving count(SC.cno)=3)group by snoorder by 总成绩 desc检索出每位学生的学号、姓名、未修课程名。select Sno,Sname,Cname 未选修课程名 from Student,Coursewhere not exists (select *from SC where sno=Student.Sno and cno=Course.Cno)order by Sno检索多于3名学生选修的并以3结尾的课程号的平均成绩。select cno,avg(grade) 平均成绩 from SC where cno like%3group by cnohaving count(sno)=3检索最高分与最低分之差大于5分的学生的学号、姓名、最高分、最底分。select SC.sno,Sname,max(grade) 最高成绩 from Student,SC where Student.Sno=SC.snogroup by SC.sno,Snamehaving (max(grade)-min(grade)5若学生信息表分别存放在两个数据库中，XSGL与XUE库中，要求检索所有学生的姓名、性别和出日年份。select Sname,Sex,Birth from XSGL1_20093598.dbo.Studentunionselect Sname,Sex,Birth from XUE_20093598.dbo.Student若学生信息表分别存放在两个数据库中，XSGL与XUE库中；选课表在XSGL库中。检索XUE库中的学生的选课信息，给出学生的学号，姓名及成绩。select Student2.Sno,Sname,Cname,grade from XUE_20093598.dbo.Student Student2 left join SC on Student2.Sno=SC.snoleft join Course on Course.Cno=SC.cno十六、实现数据完整性1、企业管理器操作：属性2、SQL语句：为student表的sno字段添加一个check约束，使学号满足如下条件：A.学号前四位为2005。B.学号后四位为数字字符。alter table Student add constraint CK_Sno check(Sno like20050-90-90-90-9)为student表中的birth字段添加一个约束，规定生日应小于入学时间。alter table Student add constraint CK_birth check(Birth Entrance_date)禁用上面的Check约束的T-SQL语句：alter table Studentnocheck constraint CK_birth启用Check约束的T-SQL语句：alter table Studentcheck constraint CK_birth删除所设置check约束的T-SQL语句为（要求先判断该约束是否已存在，若存在则将其删除）：if exists (select * from sysobjects where name=CK_birth ) begin alter table Student drop constraint CK_birth end将student表中的classno字段设置为允许空。ALTER TABLE Student alter column Classno char(3) null（若原有约束，请在企业管理器中删除SC表主键约束及外键约束）将SC表中的sno,cno字段设置为主键。Alter table SC add constraint PK_SC primary key(sno,cno)为SC表中的sno,cno添加外键约束，约束名自取；并允许级联删除与级联更新。alter table SCadd constraint ck_snoforeign key(sno) references Student(Sno)on delete cascadeon update cascadealter table SCadd constraint ck_cnoforeign key(cno) references Course(Cno)on delete cascadeon update cascade为course表中的cname字段添加唯一性约束，为Total_perior添加check约束（规定总学时小于是100），并为course表添加一个计算列week_perior周学时字段，设一学期上课周共有18周，则周学时为:总学时/18（结果取整）（或用总学时/18.0 获得浮点型）。(注：计算列的约束只能是唯一性约束或是主键约束，并且不能修改)。Alter table Course add constraint UN_Courseunique(Cname)Alter table Course add constraint CK_Course check (Total_perior3with check option创建一个名为stuview5的视图，能检索出每位选课学生的学号、姓名、课程名、总成绩。create view stuview5asSELECT Student.Sno,Sname,Cname,sum(grade)sumgrade FROM Student,Course,SC WHERE Student.Sno = SC.sno and SC.cno=Course.Cnogroup by Student.Sno,Student.Sname,Course.Cnamewith check option（2）、查看视图查看视图的定义脚本。方法：（提示：sp_helptext）结果为： sp_helptext stuview2查看加密视图stuview3的定义脚本。select * from sysobjects s1,syscomments s2 where name=stuview3and s1.id=s2.id（3）、修改视图（4）、视图的更名与删除sp_rename stuview4,stuv4drop view stuv4（5）、管理视图中的数据select * from stuview2where Sname=许华and Classno=052（6）、在视图中插入数据insert into stuview2(Sno,Sname,Classno,Sex,Home_addr,Entrance_date,Birth)values(20050008,赵青,054,男,南京,2005/09/01,1983/01/09)（7）、修改视图中的数据UPDATE stuview2SET Home_addr=扬州市where Classno=054（8）、删除视图中的数据delete from stuview2wh