实验三 数据更新实验

/*********************实验三 数据更新实验*************************/
-- 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,SC
	where student.Sno=SC.Sno
	group 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,SC
where Sdept='CS';

-- 8．  删除“CS”系全体学生的选课记录；

DELETE from SC
where Sno in(
	select distinct Sno from Student
	where Sdept='CS'
	);
select *from SC
where Sno in(
	select distinct Sno from Student
	where 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;
-- 这句仅适合于MYSQL
update SC,Course set grade=1.1*grade
where SC.Cno=Course.Cno and Course.Cname='数据结构';
/* -- 这句仅适合于MSSQL
update SC set grade=grade*1.1
where   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.05
where 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.05
where 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=prev
http://www.cnblogs.com/chy1000/archive/2010/03/02/1676282.html
http://zhidao.baidu.com/question/68619324
http://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') b
set grade=grade*1.05
where Cno='2' and grade<b.a;
select *from SC;

-- 14．  把选修了“2”号课程，且成绩低于该门课程的平均成绩的学生成绩删除掉；

/* -- 在mssql中可以用下面这句执行
delete from SC
where 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 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);

UPDATE My_Table
SET Priority=Priority + 1
WHERE Priority >= 1
AND (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;