一、单元概述
通过本章的学习能够了解MySQL数据库的数据操作语言，掌握表中数据的插入、修改和删除操作，了解事务的基本概念，掌握控制事务的基本方法，
二、教学重点与难点
重点：

  • 数据的增加
  • 数据的修改
  • 数据的删除

难点：

  • 事务概念

  • 事务控制

    6.1数据操作语言

    6.1.1 数据操作语言概述

  • Data Manipulation Language ,简称DML，主要用来实现对数据库表中的数据进行操作。

  • 数据操作语言主要包括如下几种：

    • 增加行数据：使用INSERT语句实现
    • 修改行数据：使用UPDATE语句实现
    • 删除行数据：使用DELETE语句实现

      6.2 插入数据

      6.2.1 插入数据语法

  • 使用INSERT语句向表中插入数据，语法结构如下：

    1. INSERT INTO    table [(column [, column...])]
    2. VALUES        (value [, value...]);

  • 采用这种语法一次只能追加一条记录；

  • column部分叫做列名列表，value部分叫做值列表，列名列表和值列表必须在个数及数据类型上保持一致；
  • 列名列表部分可以省略，如果省略，默认包括该表的所有列，列的顺序为使用 desc 表名 命令所查看的顺序；
  • 列名列表部分也可以指定部分非空的列，注意值列表必须和列名列表对应；

  • 字符和日期型数据必须要用单引号括起来。

    1. INSERT INTO    dept (deptno, dname, loc)
    2. VALUES        (50, 'DEVELOPMENT', 'DETROIT');

    6.2.2 插入空值NULL

  • 隐含法: 在列名列表中忽略该列。

    1. INSERT INTO    dept (deptno, dname )
    2. VALUES        (60, 'MIS');

  • 显示法: 指定 NULL关键字或者’’。

    1. INSERT INTO    dept
    2. VALUES        (70, 'FINANCE', NULL);

    练习

  1. 向部门表新增一个部门，部门编号为50，部门名称为HR，工作地点为SY。
  2. 向部门表新增一个部门，部门编号为60，部门名称为MARKET。

    6.2.3 插入日期值

  • SYSDATE() 函数记录当前日期和时间

    1. INSERT INTO    emp (empno, ename, job,mgr, hiredate, sal, comm,deptno)
    2. VALUES    (7196, 'GREEN', 'SALESMAN',    7782, SYSDATE(), 2000, NULL,10);

  • 插入日期值

    1. INSERT INTO emp
    2. VALUES (2296,'AROMANO','SALESMAN',7782,  '1997-02-03', 1300, NULL, 10);

  • | EMPNO | ENAME | JOB | MGR | HIREDATE | SAL | COMM | DEPTNO |

  • | —— | —— | —— | —— | —— | —— | —— | —— |
  • | 2296 | AROMANO | SALESMAN | 7782 | 1997-02-03 | 1300 | | 10 | | EMPNO | ENAME | JOB | MGR | HIREDATE | SAL | COMM | DEPTNO | | —- | —- | —- | —- | —- | —- | —- | —- | | 2296 | AROMANO | SALESMAN | 7782 | 1997-02-03 | 1300 | | 10 |

练习

  1. 向员工表中新增一个员工，员工编号为8888，姓名为BOB，岗位为CLERK，经理为号7788，入职日期为1985-03-03，薪资3000，奖金和部门为空。

    6.2.4 批量插入数据

  • 使用insert语句可以一次性地向表批量插入多条记录，语法格式如下。

    1. INSERT INTO 表名[(字段列表)] VALUES
    2. (值列表1),
    3. (值列表2),
    4. …
    5. (值列表n);
    1. INSERT INTO EMP(EMPNO, ENAME, JOB, SAL) 
    2. VALUES 
    3. ('8881', '张三', '部门经理', 6000),
    4. ('8882', '李四', '职员', 3000),
    5. ('8883', '王五', '职员', 3500),
    6. ('8884' ,'赵六', '部门经理', 6500),
    7. ('8885', '高七', '职员', 2500),
    8. ('8886', '马八', '职员', 3100),
    9. ('8887', '钱九', '部门经理', 5000),
    10. ('8888', '孙十',  '职员', 2800);

    6.2.5 通过子查询插入多行数据

  • 不必书写values子句

  • INSERT子句和数据类型必须和子查询中列的数量和类型相匹配中列的数量

  • 语法

    1. INSERT INTO 表名[(列名1[,列名2，…，列名n])] 子查询 ；

  • 创建manager表

    1. CREATE TABLE manager AS SELECT - FROM emp WHERE 1=0;

  • 向manager表中插入职位为MANAGER的记录

    1. INSERT INTO manager SELECT - FROM   emp WHERE job = 'MANAGER';

    练习

  1. 使用CREATE TABLE emp_back as SELECT - FROM EMP WHERE 1=0，创建emp_back表,拷贝下来即可。
  2. 把emp表中入职日期大于1982年1月1日之前的员工信息复制到emp_back表中。

    6.3 修改数据

  • 修改数据主要用来按照指定条件修改表中某些行的列数据。

  • 06.数据操作与事务控制 - 图1

    6.3.1 修改数据语法

  • 修改数据使用UPDATE子句完成，语法结构如下:

    1. UPDATE        table
    2. SET        column = value [, column = value]
    3. [WHERE         condition];

  • WHERE子句用来限定修改哪些行。

  • SET子句用来限定修改哪些列。
  • WHERE子句中的更新条件是一个逻辑表达式，通常需要使用到关系运算符和逻辑运算符，返回True或者False。

  • 使用 WHERE 子句指定要修改的记录

    • 把员工编号为7782的部门编号修改为20
      1. UPDATE     emp SET deptno = 20 WHERE      empno = 7782;

  • 如果要修改所有记录，WHERE子句可以忽略

    • 把所有员工的部门编号修改为20
      1. UPDATE     emp SET deptno = 20;

  • 一次修改多列

    • 把部门编号为10的员工，部门编号调整为20，工资增加100
      1. UPDATE     emp
      2. SET        deptno = 20,sal=sal+100
      3. WHERE      deptno = 10;

      6.3.2 修改记录时的完整性约束错误

  • 把部门编号为10的员工，部门编号调整为55

    1. UPDATE    emp
    2. SET    deptno = 55
    3. WHERE    deptno = 10;

    06.数据操作与事务控制 - 图2
    练习

  1. 修改部门20的员工信息，把82年之后入职的员工入职日期向后调整10天
  2. 修改奖金为null的员工，奖金设置为0
  3. 修改工作地点在NEW YORK或CHICAGO的员工工资，工资增加500

    6.4 删除数据

  • 删除数据主要用来按照指定条件从表中删除某些行。

06.数据操作与事务控制 - 图3

6.4.1 删除数据语法

  • 语法结构

    • 使用 DELETE 语句删除表中满足条件的行记录
      1. DELETE FROM      table
      2. [WHERE      condition];

      6.4.2 删除选中记录

  • 删除职位是CLERK的员工记录

    1. DELETE FROM emp
    2. WHERE         job = 'CLERK';

    6.4.3 删除全部记录

  • 删除所有员工记录

    1. DELETE FROM emp;

    6.4.4 删除记录时的完整性约束错误

  • 删除部门编号为10的部门记录

    1. DELETE FROM    dept
    2. WHERE        deptno = 10;

    06.数据操作与事务控制 - 图4

    6.5 截断表

    6.5.1 截断表语法

  • 截断表语法

    1. TRUNCATE TABLE table;
    1. TRUNCATE TABLE emp;

    6.5.2 TRUNCATE和DELETE区别

  • TRUNCATE是DDL，只能删除表中所有记录，释放存储空间，使用ROLLBACK不可以回滚。

  • DELETE是DML，可以删除指定记录，不释放存储空间，使用ROLLBACK可以回滚。

练习

  1. 删除经理编号为7566的员工记录
  2. 删除工作在NEW YORK的员工记录
  3. 删除工资大于所在部门平均工资的员工记录

    6.6 事务处理

    6.6.1 事务概述

  • 事务(Transaction)：也称工作单元，是由一个或多个SQL语句所组成的操作序列，这些SQL语句作为一个完整的工作单元，要么全部执行成功，要么全部执行失败。在数据库中，通过事务来保证数据的一致性。
  • 事务处理语言：Transaction Process Language ,简称TPL，主要用来对组成事务的DML语句的操作结果进行确认或取消。确认也就是使DML操作生效，使用提交(COMMIT)命令实现；取消也就是使DML操作失效，使用回滚(ROLLBACK)命令实现。

  • 通过事务的使用，能防止数据库中出现数据不一致现象。如两个银行账户进行转账，涉及到两条更新操作，这两条更新操作只允许全部成功或失败，否则数据会出现不一致的现象。

    6.6.2 事务组成

  • 在数据库中，事务由一组相关的DML或SELECT语句，加上一个TPL语句（COMMIT、ROLLBACK）或一个DDL语句(CREATE、ALTER、DROP、TRUNCATE等)或一个DCL（GRANT、REVOKE）语句。

    • 例：如下语句组成两个事务。
      • INSERT….
      • UPDATE….
      • DELETE….
      • SELECT….
      • INSERT…
      • COMMIT;— 前6条语句，组成第1个事务
      • UPDATE…
      • DELETE….
      • CREATE… ;—后3条语句，组成第2个事务

        6.6.3 事务特征

  • 事务特征可用四个字母的缩写表示：即ACID

    • 原子性（Atomicity）
      • 事务就像“原子”一样，不可被分割，组成事务的DML操作语句要么全成功，要么全失败，不可能出现部分成功部分失败的情况。
    • 一致性（Consistency）
      • 一旦事务完成，不管是成功的，还是失败的，整个系统处于数据一致的状态。
    • 隔离性（Isolation）
      • 一个事务的执行不会被另一个事务所干扰。比如两个人同时从一个账户从取钱，通过事务的隔离性确保账户余额的正确性。
    • 持久性（Durability）
      • 也称为永久性，指事务一旦提交，对数据的改变就是永久的，不可以再被回滚。

练习

  1. test表为空表，分析如下语句操作后，最后test表的状态。
    1. BEGIN;
    2. INSERT INTO test(id,name) values(1, 'a');
    3. INSERT INTO test(id,name) values(2, 'b');
    4. SAVEPOINT s1;
    5. INSERT INTO test(id,name) values(3, 'c');
    6. INSERT INTO test(id,name) values(4, 'd');
    7. DELETE FROM test WHERE id in (1,3);
    8. ROLLBACK TO s1;
    9. DELETE FROM test WHERE id in (2,4);
    10. COMMIT;
    11. ROLLBACK;

    6.7 本章小结

  • 插入数据的方法：一次插入一行，插入空值、插入特殊值、插入多行；
  • 修改表中数据：修改指定行，修改所有列、修改多列；
  • 删除表中数据：删除指定行、删除所有行；
  • 数据库事务：事务组成、事务特性、事务处理；

    6.8 课后作业

  1. 使用如下语句，创建学生表student和班级表class

    1. CREATE TABLE student (        --学生表
    2.       xh char(4),--学号
    3.       xm varchar(10),--姓名
    4.       sex char(2),--性别
    5.       birthday date,--出生日期
    6.       sal double(7,2), --奖学金
    7.       studentcid int(2) --学生班级号
    8. )
    9. CREATE TABLE class (   --班级表
    10.       classid in(2), --班级编号
    11.       cname varchar(20),--班级名称
    12.         ccount  int(3) --班级人数
    13. )

    2.基于上述学生表和班级表，完成如下问题
    （1）添加三个班级信息为：

    1. 1，JAVA1班，null
    2. 2，JAVA2班，null
    3. 3，JAVA3班，null

    （2）添加学生信息如下：‘A001’,‘张三’,‘男’,‘01-5月-05’,100,1
    （3）添加学生信息如下：’A002’,’MIKE’,’男’,’1905-05-06’,10
    （4）插入部分学生信息： ‘A003’,’JOHN’,’女’
    （5）将A001学生性别修改为’女‘
    （6）将A001学生信息修改如下：性别为男，生日设置为1980-04-01
    （7）将生日为空的学生班级修改为Java3班
    （8）请使用一条SQL语句，使用子查询，更新班级表中每个班级的人数字段

    3.使用如下语句，建立以下表

    1. CREATE TABLE copy_emp   (
    2.      empno int(4),
    3.      ename varchar(20),
    4.      hiredate date,
    5.      deptno int(2),
    6.      sal double(8,2)
    7. )

    4.在第三题表的基础上，完成下列问题:
    (1)在表copy_emp中插入数据，要求sal字段插入空值，部门号50，参加工作时间为2000年1月1日，其他字段随意
    (2)在表copy_emp中插入数据，要求把emp表中部门号为10号部门的员工信息插入
    (3)修改copy_emp表中数据，要求10号部门所有员工涨20%的工资
    (4)修改copy_emp表中sal为空的记录，工资修改为平均工资
    (5)把工资为平均工资的员工，工资修改为空
    (6)另外打开窗口2查看以上修改
    (7)执行commit，窗口2中再次查看以上信息
    (8)删除工资为空的员工信息
    (9)执行rollback