--创建表格 CREATE TABLE STUDENT ( STUDENT_ID CHAR(5) NOT NULL PRIMARY KEY, NAME VARCHAR(20) UNIQUE, GENDER CHAR(10) DEFAULT('MALE'), BIRTHDAY INT, SDEPT VARCHAR(15) ); --创建索引 create index student_name on student(name) create table course ( cno char(4) not null primary key, cname char(40), cpno char(4), CCREADIT int, FOREIGN key(cpno) REFERENCES course(cno) ) CREATE TABLE SC ( STUDENT_ID CHAR(5) NOT NULL, CNO CHAR(4) NOT NULL, GRADE SMALLINT, PRIMARY KEY(STUDENT_ID,CNO), FOREIGN KEY(STUDENT_ID) REFERENCES STUDENT(STUDENT_ID), FOREIGN KEY(CNO) REFERENCES COURSE(CNO) ); --选择某些列、计算表达式、常量、函数、别名 select student_id,name,gender,2012-birthday 年份,'中国人' 国籍,upper(sdept) 系别 from student; --消除重复的行 select distinct student_id from sc; --查询练习 select * from student where birthday>21; select * from student where birthday between 21 and 22; select * from student where sdept not in('cs','is') select * from student where name like '张%'; select * from student where name like '张__'; insert into student values('00004','王五','female',24,'ma'); insert into student values('00005','张家明','female',25,'ma'); insert into student(student_id,name) values('00006','张大明'); --空值查询 select * from student where gender is null; --排序(order by) select * from student order by birthday asc select * from student order by birthday desc select * from student order by name desc; --聚合函数 select count(*) from student; select sum(grade) from sc where student_id='00001' select avg(grade) from sc where student_id = '00001'; select max(grade) from sc where student_id = '00001'; select min(grade) from sc where student_id = '00001'; select * from sc; select student_id, sum(grade) from sc group by student_id; select student_id, sum(grade) from sc group by student_id having sum(grade)>140; select sdept,count(*) from student group by sdept having count(*)>1; select sdept ,count(*) from student group by sdept; select gender,count(*) from student where sdept= 'cs' group by gender select sdept,gender,count(*) from student group by sdept,gender select sdept,count(*) from student where gender='male' group by sdept having count(*)>1; --等值连接 select * from student,sc where student.student_id = sc.student_id; --自身连接 select * from course a,course b where a.cpno = b.cno; select * from course; select * from student a,student b select * from student a,student b where b.name='张三' and a.sdept = b.sdept and a.name!='张三'; select a.name,a.gender from student a,student b where b.name='张三' and a.birthday>b.birthday select * from sc; select * from student b; select * from student a,sc b where a.student_id = b.student_id; --左连接 select * from student a left outer join sc b on a.student_id = b.student_id; select * from sc a right join student b on a.student_id = b.student_id; select * from sc; --多张表的等值连接 select a.student_id,a.name,b.cname,c.grade from student a,course b,sc c where a.student_id = c.student_id and c.cno = b.cno select a.student_id,a.cno,a.grade from sc a,student b where a.student_id = b.student_id and b.name='张三' ; select b.grade from student a,sc b,course c where a.student_id = b.student_id and b.cno = c.cno and a.name='张三' and c.cname='高等数学上' select b.student_id,b.name from sc a,student b,course c where a.student_id= b.student_id and c.cno = a.cno and c.cname='高等数学上' select c.cno,c.cname from student a,sc b,course c where a.student_id = b.student_id and b.cno = c.cno and a.name='张三' select a.* from student a,sc b where a.student_id = b.student_id and b.cno='c01' select * from student where student_id in (select student_id from sc where cno='c01'); select * from student where sdept in (select sdept from student where name='张三'); --不相关嵌套查询 select * from student where student_id in (select student_id from sc where cno in (select cno from course where cname='高等数学上')); select * from sc; --相关嵌套查询 select * from sc x where grade> ( select avg(grade) from sc y where y.student_id=x.student_id ) --查询比张三年龄大的学生学号、姓名 select student_id,name from student where birthday>(select birthday from student where name='张三') --查询选修每门课程中成绩小于其平均成绩的学生学号 select * from sc x where x.grade< ( select avg(grade) from sc y where y.cno = x.cno ) --查询张三选修高等数学1的课程的成绩 select grade from sc where student_id=(select student_id from student where name='张三') and cno=(select cno from course where cname='高等数学上') --查询张三选修课程的平均成绩 select avg(grade) from sc where student_id = (select student_id from student where name='张三') --查询选修课程的平均成绩小于张三平均成绩的学生学号 select student_id, avg(grade) from sc group by student_id having avg(grade)< ( select avg(grade) from sc where student_id = (select student_id from student where name='张三') ) --查询课程的平均成绩低于张三平均成绩的课程号 select cno,avg(grade) from sc group by cno having avg(grade)< ( select avg(grade) from sc where student_id = (select student_id from student where name='张三') ) --查询选修课程成绩大于等于该课程平均成绩的学生学号 select * from sc x where x.grade>= ( select avg(grade) from sc y where y.cno = x.cno ) select * from ( select * from student where student_id in ( select student_id from sc where cno in ( select cno from sc where student_id= ( select student_id from student where name='张三' ) ) ) ) a where a.gender='male' --union create table stu ( id char(5), name char(20), age int ) insert into stu values('90001','陈三',21); insert into stu values('90002','陈四',22); insert into stu values('90003','陈五',23); insert into stu(id) values('90004') --union --在线视图 select * from view_a a where a.age>22; select * from view_a a where a.age>22; select * from view_a a where a.age>22; select * from view_a a where a.age>22; create view view_a as select * from stu union select student_id,name,birthday from student select * from student; insert into stu values('00001','张三',21); --except select * from stu except select student_id,name,birthday from student --INTERSECT select * from stu INTERSECT select student_id,name,birthday from student --获取记录的前3条 select top 3 * from student order by birthday desc; --复制表 select * into b from student; select * from b; --仅仅复制表结构 select * into c from student where 2>10 select * from c; --批量插入数据 insert into stu select student_id,name,birthday from student select name from sysobjects where type='U' --批量初始化表 TRUNCATE TABLE stu select * from stu --随机取出某表中2条记录 select top 2 * from student order by NewID() create table deptage ( dept char(15), avgage int ) insert into deptage select sdept,avg(birthday) from student group by sdept select * from deptage; select * from student; update student set gender='male' ,birthday=23,sdept='ma' where student_id= '00006' update student set birthday=20; update student set birthday = birthday+1; select * from sc; update sc set grade=grade+10 where 'cs'=(select sdept from student y where y.student_id=sc.student_id); select * from student; delete from student where student_id='00006' delete from sc where student_id= (select student_id from student where name='张三'); create view stu1 as select student_id,name,gender from student select * from stu1; CREATE VIEW IS_STUDENT AS SELECT STUDENT_ID,NAME,BIRTHDAY FROM STUDENT WHERE SDEPT = 'IS'; select * from IS_STUDENT select * from student; SELECT STUDENT_ID,NAME,2010-BIRTHDAY FROM STUDENT; -------------------------------------------存储过程-游标-触发器----------------------------------------------- create procedure CheckStudent as insert into stu values('80001','zs',23) select * from stu; alter procedure CheckStudent @id char(5),@name char(20),@age int as insert into stu values(@id,@name,@age) select * from stu; ------存储过程的定义开始---第一次创建用CREATE,修改用ALTER-------- ---多表操作 try catch ;output; set; alter procedure AddStuInfo @student_id char(5),@name char(20),@cno char(5), @cname char(20),@grade int,@myerror char(50) output as begin try insert into student(student_id,name) values(@student_id,@name) insert into course(cno,cname) values(@cno,@cname) insert into sc values(@student_id,@cno,@grade) set @myerror='成功!' return 0 end try begin catch set @myerror='主键冲突,新增失败' return -1 end catch ------存储过程的定义结束----------- ----------------存储过程的执行开始------------- --定义变量 declare @myerror char(50) --使用包含output参数 execute AddStuInfo '20002','陈七','c06','工商管理2',90,@myerror output --显示值 select @myerror ----------------存储过程的执行结束------------- alter procedure AddStuInfo2 @student_id char(5),--学号 @name char(20), --姓名 @cno char(5), --课程号 @cname char(20), --课程名 @grade int, --成绩 @myerror char(50) output --输出错误信息 as begin try insert into student(student_id,name) values(@student_id,@name) --新增学生表 insert into course(cno,cname) values(@cno,@cname) --新增课程表 insert into sc values(@student_id,@cno,@grade) --新增选课表 set @myerror = '执行成功' return 0 end try begin catch set @myerror = '参数错误,请检查' return -1 end catch declare @myerror char(50) execute AddStuInfo2 '91003','陈9','c83','机械工程',60,@myerror output select @myerror select * from student; select * from course; select * from sc; execute CheckStudent '10002','ls',21; -----游标运用的准备,准备一个分数调整表,用于修正学生考试分数 create table AddGrade ( student_id char(5), cno char(5), ModiGrade int ) insert into AddGrade values('00001','c01',10); insert into AddGrade values('00001','c02',-10); insert into AddGrade values('00002','c01',20); insert into AddGrade values('00002','c02',10); select * from AddGrade; select * from sc; -----创建包含游标的存储过程,逐条读取和更新分数 create procedure ModiGrade as --申明变量 declare @student_id char(5), @cno char(5), @ModiGrade int --申明游标 declare mycursor cursor for select student_id,cno,ModiGrade from AddGrade --打开游标 open mycursor --将游标里的值放置到变量里 fetch next from mycursor into @student_id,@cno,@ModiGrade while(@@fetch_status=0) begin update sc set grade=grade+@ModiGrade where student_id=@student_id and cno=@cno fetch next from mycursor into @student_id,@cno,@ModiGrade end close mycursor --关闭游标 deallocate mycursor --释放游标 ---执行此存储过程 exec ModiGrade create table SC_HIS ( student_id char(5), cno char(5), grade int ) create trigger SCDelete on sc for delete as insert into sc_his select * from deleted; alter trigger SCInsert on sc instead of insert as insert into student(student_id) select student_id from inserted insert into course(cno) select cno from inserted insert into sc select * from inserted insert into sc values('70002','c88',90) select * from student; select * from course; select * from sc; select * from sc_his; delete from sc where student_id='91002'