数据库上机脚本

论U盘备份的重要性。。。

U盘突然间坏了，而且是硬件问题。。。软件工程文档，并行计算大作业，所有数据库上机脚本都没有了。。哭死。。。

CREATE table Student(sno char(10) not null,
                     sname varchar(10) null,
                     sage tinyint,
                     ssex char(1),
                     sdept char(2),
                     primary key(sno),
                     check(ssex in ('M','F')))

INSERT INTO Student(sno,sname,sage,ssex,sdept)VALUES('20123221','毛通',22,'M','CS');
INSERT INTO Student(sno,sname,sage,ssex,sdept)VALUES('20123219','吕建',22,'M','IS');
INSERT INTO Student(sno,sname,sage,ssex,sdept)VALUES('20123218','刘同宾',19,'M','MA');
INSERT INTO Student(sno,sname,sage,ssex,sdept)VALUES('20123217','解晓东',20,'M','MS');
INSERT INTO Student(sno,sname,sage,ssex,sdept)VALUES('20123207','孙锐',20,'M','MS');
INSERT INTO Student(sno,sname,sage,ssex,sdept)VALUES('20121226','夏长兴',20,'M','MS');


SELECT * FROM Student;

create table ss(sno int not null,
                sname varchar(10) null,
                sbirth datetime ,
                primary key(sno) 
                )
drop table ss;
insert into ss(sno,sname,sbirth)values(0001,'张三','2014-02-01');
select * from ss;

create table Course(cno char(10) not null,
                    cname varchar(10),
                    cpno char(10) null,
                    credit tinyint,
                    primary key(cno),
                    foreign key(cpno) references Course
                    )


INSERT INTO Course(cno,cname,cpno,credit)VALUES('2','数学',null,2);
INSERT INTO Course(cno,cname,cpno,credit)VALUES('6','数据结构',null,2);
INSERT INTO Course(cno,cname,cpno,credit)VALUES('7','PASCAL语言','6',4);
INSERT INTO Course(cno,cname,cpno,credit)VALUES('4','操作系统','6',3);
INSERT INTO Course(cno,cname,cpno,credit)VALUES('5','数据结构','7',4);
INSERT INTO Course(cno,cname,cpno,credit)VALUES('1','数据库','5',4);
INSERT INTO Course(cno,cname,cpno,credit)VALUES('3','信息系统',4,5);


SELECT * FROM Course;

create table SC(sno char(10) not null,
                cno char(10) not null,
                grade tinyint,
                primary key(sno,cno),
                foreign key(sno) references Student,
                foreign key(cno) references Course,
                check(grade>=0 and grade<=100)
               )


INSERT INTO SC(sno,cno,grade)VALUES('20123219','1',100);
INSERT INTO SC(sno,cno,grade)VALUES('20123218','2',100);
INSERT INTO SC(sno,cno,grade)VALUES('20123221','3',100);
INSERT INTO SC(sno,cno,grade)VALUES('20123217','4',100);
INSERT INTO SC(sno,cno,grade)VALUES('20123207','5',100);
INSERT INTO SC(sno,cno,grade)VALUES('20121226','6',100);





SELECT * FROM SC;
SELECT * FROM Student;
--查找select
SELECT * FROM Student Where sname='夏长兴';
SELECT * FROM Student Where sname='吕建' AND sage=22;
SELECT sname,ssex FROM Student where  sage=20; 
SELECT sname,ssex,2014-sage FROM Student; --*的位置可以是列，也可以是有列参与的表达式，也可以是单纯的表达式
SELECT Distinct sage From Student;--除去重复的年龄，只显示年龄组成（多个相同的年龄只显示一个）
SELECT sage as age FROM Student;--给表中某列改属性名，作用域：只在执行此条语句中起作用
SELECT * FROM Student as ALL_Student;--给表起别名
SELECT * FROM Student Where sage in(20,22);-- in,选择年龄在（20,22）范围内的数据
SELECT * FROM Student Where sage BETWEEN 20 and 22;--between，选择年龄在20到22之间的数据（包括20,22）
SELECT * FROM Student Where sage>=18 and sage<=20;
SELECT Student.sno,Student.sname,SC.grade FROM Student,SC Where Student.sno=SC.sno and SC.grade>90;--从两张表中选择数据组合输出
SELECT * FROM SC Where grade>80 order by grade ASC;--升序（ASC）,按成绩升序排序
SELECT * FROM SC Where grade>80 order by grade desc;--降序（DSC）,按成绩降序排序

  --字符匹配，通配符: % _
SELECT sname FROM Student Where sname like '夏%';
SELECT sname FROM Student Where sname like '吕_';
  --字符匹配，转义字符
SELECT sname FROM Student WHERE sname like '吕建\_' ESCAPE '\';
SELECT sname FROM Student Where sno is not null;--（not） null 空值判断,选择学号不为空的所有姓名数据
  --聚合函数（写在紧跟聚合函数后的变量 为聚合函数统计后的结果表 列属性名）
SELECT COUNT(*)num,AVG(sage)num1 FROM Student Where ssex='M';--聚合函数COUNT , AVG；选择男同学人数（存在num列属性下）和年龄平均值（num1）
SELECT Count(*)num,AVG(grade),MAX(grade),MIN(grade),SUM(grade) FROM SC WHERE grade>90; --五个聚合函数

--分组语句group by
SELECT count(*) FROM SC group by grade;--按成绩分组,返回一条成绩计数的语句
SELECT grade FROM SC group by grade;--分组语句中，SELECT语句后的数据项可为聚合函数，也可为group by后的数据项；
                                    --此例返回分组中所有不同的成绩
SELECT sdept,max(sage) from Student group by sdept;

--having对对分组计算的结果集进行筛选
SELECT sdept,count(*) From Student group by sdept having count(*)>0;--对group by的分组结果，按学院分组并计算每组个数
                                                                    --在分组基础上只返回输出个数>0的组的学院和个数

--三个表连接
SELECT Course.cno,Course.cname from Student join SC on Student.sno=SC.sno
                                join Course on SC.cno=Course.cno where
                                Student.ssex='M';--join前后两个表的共同属性为连接条件
 
--子查询
SELECT sno from SC where cno in(select cno from Course where cname like '%数据库');--查询选数据库课程的学生学号
--嵌套子查询
SELECT sname,sno from Student where sno in(select sno from SC where cno 
in (select cno from Course where cname like '%数据库'));--查询选数据库课程的学生姓名和学好 
--嵌套子查询中=和in的用法
select * from Student where sage=(select sage From Student where sno='20123219');--子句筛选出学号为20123219学生的年龄
--当子句返回值有多个时要用in,不要用=
select * from Student where sno in (select sno from Student where sage=22);  
 
--TOP  或 TOP percent 
select top 2 * from Student order by sage desc; --默认升序(asc),将表中年龄按降序排列，筛选出前2个
select top 90 percent  * from Student order by sage desc;--将年龄 
--增删
--ALTER TABLE Student DROP ssex RESTRICT;--删除一列数据
ALTER TABLE Student ADD birth VARCHAR(30);--增加一列数据
delete  from Student where sname='吕建';--删除记录前，确保这条记录中的所有列与其他表无关，此句不可执行
SELECT * FROM Student;

--删除表
drop table Student;

--修改
UPDATE Student SET sage=18 Where sname='解晓东';--修改学号为...的姓名为...
UPDATE SC SET grade=80 Where sno=20123221;--修改成绩
UPDATE SC SET grade=grade*1.2 Where grade<90;

--帮助
sp_help Student;

--视图
create view SCS2(sno,sname,cname,cno)  AS 
SELECT Student.sno,Student.sname,Course.cno,Course.cname from Student as a join SC as b on a.sno=b.sno
                                join Course as c on b.cno=c.cno ;
create view SCS1(sno,sname,cname,cno)  AS 
SELECT Student.sno,Student.sname,Course.cno,Course.cname from Student join SC on Student.sno=SC.sno
                                join Course  on SC.cno=Course.cno ;

select * from SCS1;--查看视图
drop view SCS1;