1.数据操作

1.1.数据插入

• 功能描述
  • 在表中插入新的数据。
• 注意事项
  • 只有拥有表insert权限的用户，才可以向表中插入数据。
  • 如果使用returning子句，用户必须要有该表的select权限。
  • 如果使用query子句插入来自查询里的数据行，用户还需要拥有在查询里使用的表的select权限。
  • insert事务提交是默认开启的。
• 语法格式
  • insert语句有三种形式。
  • 值插入，构造一行记录并插入到表中。

    insert [ignore] [into] table_name [partition (partition_name [,partition_nam] ...)] [(col_name [,col_name] ...)] [values|value] (expression [,...])
    

  • 查询插入，通过select子句返回的结果集构造一行或多行记录插入到表中。

    insert [ignore] [into] table_name [partition (partition_name [,partition_nam] ...)] [(col_name [,col_name] ...)] [as row_alias[(col_alias [,col_alias] ...)]] select_clause
    

  • 先插入记录，如果报主键冲突错误则执行update操作，更新指定字段值。

    insert [ignore] [into] table_name [partition (partition_name [,partition_name] ...)] [as row_alias[(col_alias [,col_alias] ...)]] [on duplicate key update] set assignment_list
    

• 示例：向表training1中插入数据。
  • 创建表training1。

    create table `training1`(
        `staff_id` int not null ,
        `course_name` char(50),
        `exam_date` datetime,
        `score` int
    );
    

  • 值插入：向表training1中插入一条记录。

    insert into training1(staff_id, course_name, exam_date, score) values(1,'information safety','2022-08-09 06:13:00',95);
    

  • 查询插入：通过子查询向表training1中插入training表中的所有数据。

    insert into training1 select * from training;
    

  • 主键冲突错误，执行update操作。

    -- 创建主键
    alter table training1 add primary key (staff_id);
    -- 插入记录
    insert into training1 values (1,'information1','2022-08-09 06:16:00',97) on duplicate key
        update course_name='information1',exam_date= '2022-08-09 06:16:00',score=97;
    

1.2.数据修改

• 功能描述
  • 更新表中行的值。
• 注意事项
  • update事务提交是默认开启的。
  • 执行该语句的用户需要有表的update权限。
• 语法格式

  update table_reference set { [col_name=expression][,...]|(col_name[,...])=(select expression[,...]) } [where condition]
  

  • table_reference子句

    { table_name | join_table }
    

  • join_table子句。

    table_reference [left [outer] | right [outer] | inner] join table_reference on condition_expr
    

  • 其中，只有在使用join_table子句时支持使用(col_name[,…])=(expression[,…])。
  • 注意：在安全模式下，不使用where条件子句或不使用limit限制行数，无法进行update和delete操作。
• 示例：更新表training中id和表education中staff_id相同的记录，修改first_name为其他值。
  • 删除表education、training。

    drop table if exists education,training;
    

  • 创建表education、training。

    create table education(staff_id int primary key ,first_name varchar(20));
    create table training(staff_id int primary key ,first_name varchar(20));
    

  • 插入记录。

    insert into education values (1,'alice'),(2,'brown');
    insert into training values(1,'alice'),(1,'alice'),(1,'alice'),(3,'bob');
    

  • 更新表training中staff_id和表education中staff_id相同的记录的first_name字段。

    update training inner join education e on training.staff_id = e.staff_id set training.first_name='alan';
    

1.3.数据删除

• 功能描述
  • 从表中删除行。
• 注意事项
  • 执行该语句的用户需要有表的delete权限。
  • delete事务提交是默认开启的。
• 语法格式

  delete from table_name
  [where condition]
  [order by {column_name [asc|desc][nulls first|nulls last]}[,...]]
  [   limit [start,] count
      | limit count offset start
      | offset start[ limit count]]
  

  • 删除表中与另一个表相匹配的行记录

    delete table_ref_list from join_table
    

  • 或者

    delete from table_ref_list using join_table
    

• 示例：删除表中training中staff_id为10且用户名为"information safety"的培训记录。
  • 删除表training。

    drop table if exists training;
    

  • 创建表training。

    create table training(
        staff_id int not null ,
        course_name char(50),
        exam_date datetime,
        score int
    );
    

  • 向表training中插入记录。

    insert into training values (10,'sql1','2022-08-09 06:53:00',90),
                        (10,'information1','2022-08-09 06:53:00',95),
                        (10,'master1','2022-08-09 06:53:00',97);
    

  • 删除表training中同时匹配course_name='information safety’和staff_id=10的记录。

    delete from training where course_name='sql1' and staff_id=10;
    

1.4.思考题

• 使用sql命令将staffs表中员工的年龄age字段的值增加5岁，应该使用命令：update age set age=age+5
• 下面四组sql命令，全部属于数据操作语言的命令是：insert，update，delete
• 删除表student中班级(cid)为6的全部学生信息：delete from student where cid=6;