实验三 数据更新实验

/*********************实验三 数据更新实验*************************/-- 1．  将数据分别插入表S、C、SC；-- 2．  将表S、C、SC中的数据分别以.SQL文件和.txt文件的形式保存在磁盘上。-- 3．  在表S、C、SC上练习数据的插入、修改、删除操作。（比较在表上定义/未定义主码（Primary Key）或外码（Foreign Key）时的情况）-- 4．  将表S、C、SC中的数据全部删除，再利用磁盘上备份的数据来恢复数据。-- 5．  如果要在表SC中插入某个学生的选课信息（如：学号为“200215121”，课程号为“6”，成绩待定），应如何进行？insert into SC values('200215121','6',null) ;-- 6．  求各系学生的平均成绩，并把结果存入数据库；drop table stu_avg ;create table stu_avg (deptname varchar(50) primary key,score float); -- 注意在MSSQL中并没有double类型/* -- 法一insert into stu_avg select sdept 系名,avg(grade) 平均成绩 from Student,SCwhere student.Sno=SC.Snogroup by Sdept;*/-- 法二insert into stu_avg select sdept 系名,avg(grade) 平均成绩     from student inner join SC    using (Sno)group by Sdept;select deptname 系名,score 平均分数 from stu_avg;-- 7．  将“CS”系全体学生的成绩置零；update SC set grade=0 where Sno in (    select distinct Sno from Student,Course     where Sdept='CS'     );select  distinct SC.* from Student,SCwhere Sdept='CS';-- 8．  删除“CS”系全体学生的选课记录；DELETE from SCwhere Sno in(select distinct Sno from Studentwhere Sdept='CS');select *from SCwhere Sno in(select distinct Sno from Studentwhere Sdept='CS');-- 9．  删除学号为“200215121”的相关信息；**************alter table SC drop   foreign key fk2 ;-- 此句在MSSQL中要将foreign key改为constraint alter table SC add constraint fk2 foreign key(Sno) references Student(Sno) on delete cascade ;delete from Student where Sno='200215121';select *from SC ;select *from Student ;-- 10．  将学号为“200215121”的学生的学号修改为“S001”；***********-- http://www.cppblog.com/wolf/articles/69089.html 外键的使用alter table SC drop  foreign key fk2 ;alter table SC add constraint fk2 foreign key(Sno) references Student(Sno) on update cascade;update  Student set Sno='S001' where Sno='200215121';select *from student;select *from SC ;-- 11．  把平均成绩大于80分的男同学的学号和平均成绩存入另一个表S——GRADE（SNO，AVG——GRADE）；drop table S_Grade;create table S_Grade(Sno char(10),avg_grade float);insert into S_Grade    select SC.Sno,avg(Grade)    from SC,Student    where Ssex='男' and Student.Sno=SC.Sno    group by SC.Sno    having avg(Grade) >80;select Sno 学号,avg_grade 平均成绩 from S_Grade;-- 12．  把选修了课程名为“数据结构”的学生的成绩提高10%；select *from SC;-- 这句仅适合于MYSQLupdate SC,Course set grade=1.1*gradewhere SC.Cno=Course.Cno and Course.Cname='数据结构';/* -- 这句仅适合于MSSQLupdate SC set grade=grade*1.1where   Sno in (    select Sno from SC,Course    where SC.Cno=Course.Cno and Course.Cname='数据结构' );*/select *from SC;-- 13．  把选修了“2”号课程，且成绩低于该门课程的平均成绩的学生的成绩提高5%；/*update SC set grade=grade*1.05where Cno='2' and Sno in    (select distinct Sno      from SC     where Cno='2' and grade < (        select avg(grade) from SC        where Cno='2' ) );update SC set grade=grade*1.05where Cno='2' and grade < (        select avg(grade) from SC        where Cno='2' ) ;*//* ===========You can't specify target table 'SC' for update in FROM clause.===========http://blog.163.com/prevBlogPerma.do?host=xiaoqiu_1120&srl=121632322007112411424982&mode=prevhttp://www.cnblogs.com/chy1000/archive/2010/03/02/1676282.htmlhttp://zhidao.baidu.com/question/68619324http://topic.csdn.net/u/20080521/15/542113f1-de7b-4ebf-9e90-3564fdbc25c0.html *********http://stackoverflow.com/questions/45494/sql-delete-cant-specify-target-table-for-update-in-from-clause ***********/select *from SC;update SC,(select avg(grade) a from SC where Cno='2') bset grade=grade*1.05where Cno='2' and grade<b.a;select *from SC;-- 14．  把选修了“2”号课程，且成绩低于该门课程的平均成绩的学生成绩删除掉；/* -- 在mssql中可以用下面这句执行delete from SCwhere Cno='2' and grade<    (   select avg(grade) from SC where Cno='2' );*/select *from SC where Cno='2' ;/* ======== You can't specify target table for update in FROM clause ======= */-- In MySQL, you can't modify the same table which you use in the SELECT part.-- http://www.xaprb.com/blog/2006/06/23/how-to-select-from-an-update-target-in-mysql/SELECT * FROM  story_category WHERE category_id NOT IN (SELECT DISTINCT category.id FROM category INNER JOIN story_category ON category_id=category.id);DELETE FROM  story_category WHERE category_id NOT IN (SELECT DISTINCT category.id FROM category INNER JOIN story_category ON category_id=category.id);/*UPDATE tbl AS a  INNER JOIN tbl AS b ON ....  SET a.col = b.col*/DELETE FROM  story_category WHERE category_id NOT IN (SELECT DISTINCTcategory.id FROM category INNER JOINstory_category ONcategory_id=category.id);DELETE FROM  story_category WHERE category_id NOT IN (SELECT DISTINCTcategory.id FROM category);UPDATE My_TableSET Priority=Priority + 1WHERE Priority >= 1AND (SELECT TRUE FROM (SELECT * FROM My_Table WHERE Priority=1 LIMIT 1) as t);-- 通过建立新表，复制数据，修改数据，删除无用表的方法来达到目的drop table if exists apples;create table if not exists apples(variety char(10) primary key, price int);insert into apples values('fuji', 5), ('gala', 6);drop table if exists apples_new;create table if not exists apples_new like apples;insert into apples_new select * from apples;update apples_new    set price = (select price from apples where variety = 'gala')    where variety = 'fuji';rename table apples to apples_orig;rename table apples_new to apples;drop table apples_orig;