论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;