DML_数据操纵语言

2023-07-17 18:23:16

DML语法：

insert 注意点：1.在表后可以有括号，表明所插入的值是哪几列，但是一定要包括所有的not null属性

2.当要为一个表插入的一行数据中有FK，先看FK的值是否存在，

若不存在，则先添加外键所在表中的值，之后再插入


/*
 * insert 添加数据 
 *  insert into tableName values('所有数据')
 *  insert into tableName(部分列名 - 必须包含所有非空列) values('部分列数据')
 */
 insert into course values('C01', 'JavaSE', 100);
 insert into course values('C02', 'JavaSE', '');
 insert into course(courseno, coursename) values('C03', 'Oracle');
 --insert into course(courseno) values('C04');   --  wrong

 -- char固定长度 
 insert into school values('S8372222', '中国大学');
 insert into school values('S8372223', '中国三美大学');
 
 insert into faculty values('01', '计算机系', 'S8372223');
 insert into faculty values('02', '土木工程系', 'S8372223');
 insert into faculty values('03', '计算机系', 'S8372222');
 insert into faculty values('04', '土木工程系', 'S8372222');
 
 insert into major values('M0001', '软件工程', '01');
 insert into major values('M0002', '网络工程', '01');
 insert into major values('M0003', '建筑', '02');
 insert into major values('M0004', '建筑美学', '02'); 
 insert into major values('M0005', '软件工程', '03');
 insert into major values('M0006', '网络工程', '03');
 insert into major values('M0007', '建筑', '04');
 insert into major values('M0008', '建筑美学', '04'); 
 
 insert into studentcard values('S87232', '中国大学', '张思思', '11级软件04班');
 
 -- insert添加 FK引入PK的值  有值直接使用   无值先insert数据再插入
 insert into student values('S0001', '张思思', '', null, null, '无锡', '', 'M0008', 'S87232');
 
 insert into studentcard values('S87233', '中国大学', '张珊珊', '11级软件04班');
 insert into student values('S0002', '张珊珊', '女', 20, null, '北京', '68', 'M0005', 'S87233');

update 注意点：update tableName set 列名 = value, ... where ...


update tableName set 列名 = value, ... where ...
   -- 部分数据更新  where条件
   update student set age = 20; 
      -- 某一条  PK  UK
         select * from student;   -- PK  UK
           update student set name = '张三散', sex = '男' where stuNo = 'S9999';
         select * from course;
           update course set hour = 200 where courseno = 'C05';
         用户登录
           update users set password = '123456' where userName = 'admin';
           update users set info = 'newInfo' where userName = 'admin';
         中国大学 - 中国社会大学
           update school set schoolName = '中国社会大学' where schoolname = '中国大学';
      -- 某几条数据  FK 
         -- 教学部 所有员工 奖金+200
           update employee set bonus = bonus + 200 
              where deptNo = (select deptNo from dept where deptName = '教学部');
         -- 软件工程 专业学生 总+10
           update student set score = score + 10 
              where majorno in (select majorNo from major where name = '软件工程');
         -- 计算机部 学生总分 + 5 
           update student set score = score + 5 
              where unionno = (select unionno from studentunion where unionname = '计算机部');
      -- 所有员工工资 + 500 
        update employee set salary = salary + 500;

delete 注意点：1.当只想删除一条记录时，where后的条件属性必须是FK 或者 UK

2.当一张表中有FK时，有两种方案可以删除（按具体情况使用）：

a. 同时删除（例：删帖子，那么帖子下的回复内容自然同时删除了）

b.先更新再删除


delete from tableName where ...
      -- 删除所有数据 
      delete from course;  
   
      -- 删除某一条数据  PK  UK 
        select * from course;
          delete from course where courseno = 'C05';
        某员工辞职
          delete from employee where empNo = 'empNo';
        select * from student;
          delete from student where stuNo = 'S9008';
        某商品下架
        
      -- 中国大学  计算机系  撤销
      -- 删除数据 数据FK被引用 
        --学生
        update student set majorno = 'M0007' 
            where majorno in (select majorno from major 
                             where facultyno =(select facultyno from faculty 
                                             where fname = '计算机系' and schoolcode 
                                                     = (select schoolcode from school 
                                                  where schoolname = '中国大学')));
        --专业
        delete from major where facultyno = (select facultyno from faculty 
                                             where fname = '计算机系' and schoolcode 
                                                     = (select schoolcode from school 
                                                  where schoolname = '中国大学'));
        --院系
        delete from faculty where fname = '计算机系' 
                and schoolcode = (select schoolcode from school 
                                  where schoolname = '中国大学');
         -- 同时删除
            课程C01	JavaSE	100  开设有问题  
              delete from studentcourse where courseno = 'C01';
              delete from course where courseno = 'C01';
            BBS论坛
              帖子 -  回复   帖子内容有严重问题 
              delete from reply where postId = '0001';
              delete from post where postId = '0001';
              
         -- 更新 再删除 
            -- 大学毕业  学生证失效 
            update student set cardno = null;
            delete from studentcard;

来自为知笔记(Wiz)

码农公寓

update 注意点：update tableName set 列名 = value, ... where ...

相关文章