Mysql-【p31-40】-对行相关操作（插入更新复制删除）

1. 表定义【table defination】

   1. column：列名
   2. Datatype：数据类型（INT VARCHART(50)可变字符(上限为50不满50也不处理) VHAR(50字符(上限50不满50则mysql会插入空格符使单元格填满)会浪费空间，但是性能快而且能有效控制字符格式)）
   3. PK：主键
   4. NN：非空值【即该列的单元格内容不能出现空值】
   5. AI：自动递增【每新添加一行记录，则列值加1 往往和主键相对应】
   6. Defult:当为空值时呈现的内容
   7. 其他列现阶段并不重要 后面会进一步学习
   8. 有个疑惑：为什么积分的默认值为'0'，积分的数据类型是整型，加引号是为什么

2. 插入行

   1. 插入单行的两种表达

      1. 语法是 INSERT INTO 表名
         VALUES (列1内容, 列2内容, 列3内容, 列4内容...)每一列都需要填写，但是AI列可以填DEFAULT【也更推荐，避免手动赋值会出错】,非空列之外的列【即允许空值的列】既可以填写具体内容，也可以填DEFAULT，又或者填在设计表定义中的默认值，如表中birth_date可填具体值，可以填DEFAULT，也可填写NULL。
      2. 也可以指定所有的非空列进行赋值，非空列的顺序可以调换，但是要注意上下对应，如图。】
      3. 运行这个语法的结果窗格会是: 1 row(s) affected【表示插入成功】，运行一次添加一行【运行4次就会添加四行 如图】【添加的结果可以调开表格来看 如果发现没有可以刷新表格】

   2. 一次性插入多行

      1. 与插入单行的区别在于VALUES后有多个括号，每个括号内不是一条记录，括号间以逗号隔开【代码与运行结果如图所示，注意这张表是一行AI列+一行name列】

   3. 插入分层行

      1. 问题情景

         1. orders表【母表】
         2. order_items表【子表】
         3. orders和order_items的关系：母表和子表的关系。前者主要是一次性订单的角度，包含订单的顾客id 下单时间 订单状态等信息，后者是每一笔订单的具体情况，以产品为最小单元，如订单2包含了产品1，4，6，还提供了对应的数量、总价等信息
         4. 接下来示范：如何插入一笔订单以它对应的所有项目（产品）

      2. 解决方法

         1.  思路

            1. 首先要在母表中插入（或者说追加）一行（即一条新的订单记录）

            2. 关键在于获取这条新订单的主键值【last_insert_id() 函数 会返回该表最新记录的主键值】

            3. 在子表中插入母表新增订单记录的具体情况

         2. 代码

            INSERT INTO orders (
            	order_id, customer_id, order_date, 
                status, comments, shipped_date, shipper_id)
            VALUE (DEFAULT, 2, '2013-03-23', 1, 
            	DEFAULT, DEFAULT, DEFAULT);
                
            INSERT INTO order_items(
            	order_id, product_id, 
                quantity, unit_price)
            VALUE (last_insert_id(), 2, 4, 3.5),
            	  (last_insert_id(), 8, 7, 7.5)
            	

3. 创建表副本

   1. 实质：将一张表的记录复制到另一张表
   2. 语法：CREATE TABLE 新表名称
      SELECT * FROM 源表名称
   3. 运行语句后刷新导航窗格就能看到新表
   4. 新表的数据和源表一致，但是打开表定义，会发现PK和AI列没有定义 这个问题尚未解决 本打算手动在表定义处进行修改 但是会报错

4. 参考表A数据的基础上新建表B

   1. 将表中部分记录复制到另一张表：使用选择语句作为插入语句中的子查询

   2. 情景一：在表A所有列保留的情况下将部分行复制到B

      1. 思路：复制A得到副本B，截断B得到无记录只有表头的B，选取A的部分列复制到B【暂时不理解为什么要教这种方法】
      2. 代码

         CREATE TABLE orders_archived FROM orders
         INSERT INTO orders_archived
         SELECT *
         FROM orders
         WHERE order_date < '2019-01-01'】

   3. 情景二：对表A的列进行取舍后将部分行复制到B

      1. 基本语法是CREATE TABLE 表B SELECT 一些列 FROM 表A WHERE 目标行所满足的条件
      2. 代码

         CREATE TABLE invoice_archived
         SELECT 
         	invoice_id, number, 
             clients.name, invoice_total, 
             payment_total, invoice_date, 
             due_date, payment_date
         FROM invoices
         JOIN clients
         	USING (client_id)
         WHERE payment_date IS NOT NULL

   4. 删除表的行与删除表

      1. 删除表中所有行

         1. 法一：在Schemas中选中表，单击右键选择截断数据【Truncate table】
         2. 法二：DELETE FROM invoices

      2. 删除表

         1. 在Schemas中选中表，单击右键选择删除数据【drop table】

      3. 删除表部分行

         1. DELETE FROM 表名
            WHERE 待删行所满足的条件【往往要使用select子查询】

5. 更新行

   1. 既可以调出表格内容直接修改【注意改完之后右下角apply】
   2. 也可以用代码来改
      UPDATE invoices【表名】
      SET 【具体更新该行的哪一列为 具体值或者表达式都是允许的】
          payment_total = 10,
          payment_date = '2019-03-01'
      WHERE invoice_id = 1 【待更新行（可以是单行也可以是多行） 所满足的条件 如果没有这个子句就是更新所有的行】
   3. 注意 没有撤回语句 默认是不能撤回 事务提交了就写到库里面了
   4. 如果更新多行可能会报错：mysql处于安全模式【不允许一次性更新多行】，解决方法：点击右上角齿轮状出现workbench Preference 点击SQL editor 取消safe updates选项 OK】
   5. UPDATE中可以用子查询：【括号使得这部分表达先执行求出来然后再使用】【类似excel中嵌套函数】【建议先运行select子查询部分查看结果 方法是选中该部分内容 运行快捷键ctrl shift enter 确定选择正确之后再整体运行代码】

      UPDATE orders
      SET comments = 'gold'
      WHERE customer_id IN 
      	(SELECT customer_id
          FROM customers
          WHERE points > 3000)

6. 恢复（重置）数据库

   1. 点击左上角FILE OPEN sql script 找到存储SQL脚本的目录位置 选择数据库 执行代码

7. 其他

   1. USING(条件) 是On的简要写法 注意条件有括号
   2. 查看表内容 右键任意列名 可以复制一个列名或者所有列名 
   3. Warning: (1265, u"Data truncated for column 'XXX' at row 1")问题解决 
      数据格式的问题：规定是DECIMAL(9,2) 但是写入1.111【不过只是警告 数据库编译器会自动保留两位小数】
      参考网页：mySQL报错"You can't specify target table 'orders' for update in FROM clause"的解决方案-CSDN博客