第三章 SQL增删改

SQL增删改

插入

单个元组插入

语法: insert into <表名>[(列名 [, 列名] ...)] values (值 [,值] ...)

#插入student表数据
insert into student
values ('98030101', '张三', '男', 20, '03', '980301');
insert into student
values ('98030102', '张四', '女', 20, '03', '980301');
insert into student
values ('98030103', '张五', '女', 19, '03', '980301');
insert into student
values ('98040201', '王四', '女', 20, '04', '980302');
insert into student
values ('98040202', '王四', '女', 21, '04', '980302');
insert into student
values ('98040203', '王五', '女', 19, '04', '980302');

#插入course表数据
insert into course
values ('001', '数据库', 40, 6, '001');
insert into course
values ('003', '数据结构', 40, 6, '003');
insert into course
values ('004', '编译原理', 40, 6, '001');
insert into course
values ('005', 'C语言', 30, 4.5, '003');
insert into course
values ('002', '高等数学', 80, 12, '004');


#插入teacher表数据
insert into teacher
values ('001', '张三', '01', 1200.00);
insert into teacher
values ('002', '赵四', '03', 1400.00);
insert into teacher
values ('003', '赵五', '03', 1000.00);
insert into teacher
values ('004', '赵六', '04', 1100.00);


#插入SC表数据
insert into sc
values ('98030101', '001', 92);
insert into sc
values ('98030101', '002', 85);
insert into sc
values ('98030101', '003', 88);
insert into sc
values ('98040202', '002', 90);
insert into sc
values ('98040202', '003', 80);
insert into sc
values ('98040202', '001', 55);
insert into sc
values ('98040203', '003', 56);
insert into sc
values ('98030102', '001', 54);
insert into sc
values ('98030102', '002', 85);
insert into sc
values ('98030102', '003', 48);

批量元组插入

语法: insert inot <表名> <子查询>

create table st
(
    Sno   varchar(8),
    Sname varchar(10)
);
insert into st
    select Sno, Sname
    from student
    where Sname like '张%';

更新

语法: update <表名> set <列名> = <表达式> | <子查询> [where子句]

将满足where子句的元组的列改为表达式对应的值

#将所有教师的工资上调5%
update teacher
set salary = salary * 1.05;

#将所有计算机系的教师工资上调10%
update teacher
set Salary = Salary * 1.1
where Dno in (
    select Dno
    from dept
    where Dname = '计算机'
);

#当某同学001号课程的成绩低于该课程平均成绩时, 将该同学该门课程成绩提高5%
update sc
set Score = Score * 1.05
where Cno = '001'
  and Score < some (
    select avg(Score)
    from sc
    where Cno = '001'
);

#将张三同学001号课程的成绩置为该门课程的平均成绩
update sc
set Score = (# 检索平均成绩
    select avg(sc2.score)
    from sc sc1,
         sc sc2,
         student s1,
         student s2
    where s1.Sclass = s2.Sclass
      and sc1.Sno = s1.Sno
      and sc2.Sno = s2.Sno
      and s1.Sname = '张三'
      and sc1.Cno = '001'
      and sc2.Cno = sc1.Cno
)
where Cno = '001' #检索张三的001号课程
  and Sno in (
    select Sno
    from student
    where Sname = '张三'
);