MySQL:数据的操作

对于数据的操作大体可分为：创建、查询、删除、修改、备份与还原（前4种简称为“增删改查”，也成为crud,即create/retrieve/update/delete）。

1. 创建数据 之 insert：

    ① 对所有字段进行赋值：

     【方法一】insert into tb_name (字段列表) values(值列表);

                  为所有字段赋值，可以省略字段列表，要求值的顺序应该与表中字段的顺序一致！！

      【方法二】insert into ex_stu set name='Lily',score='98'; （用逗号分隔即可）

    ② 对部分字段进行赋值：

      【方法一】若只对其中的 name 进行赋值，

      【方法二】 若只对其中部分字段进行赋值，则还可以使用 set 。

                     insert into ex_stu set name='Lily'; 

      ③ 同时插入多条记录：

        insert into ex_stu (name,score) values ('Molly','76'),('Bill','85');

%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%

      【思考】若插入数据时，发生了主键冲突会如何？

                 假设本文所用的 ex_stu表设置了主键 id，此时表中已经有了 id=1的数据（'lily',NULL）,

                 若输入 insert into ex_stu values (1,'cici',65);

                 会因为主键冲突，而插入失败。

                 此时可以将插入语句改为 insert into ex_stu values (1,'cici',65) on duplicate key update

                                                  name = 'cici',score='65';

                  mysql检测到发生了主键冲突，便会执行更新操作，将表中的id=1的记录更新为

                   1   cici  65

%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%

       ④ 插入的数据源：

          【数据源之一】可以使用 select 语句查询到的结果，作为 insert 的数据源。

1 insert into ex_stu (name , score) select name,score from ex_stu ;

1 insert into ex_stu (name,score) select t_name,c_name from class;

tip ： select 语句可以使用其他数据表，即可以是如下情况（蠕虫复制）

                    要求 字段个数、对应字段的格式一致即可。

                    瞬间可以将表的大小提升一倍。

           【数据源之二】可以从文件中导入，使用 infile 语句。

                               load data infile 文件路径 into table tb_name ;

1 load data infile 'e:/amp/seven' into table teacher;
2 // 相当于将该路径下的文件内容导入表 teacher 中

                               其对应的文件导出命令为:  select * into outfile 文件路径 from tb_name;

                               tip :导入时，涉及到数据增加，需要考虑是否存在冲突的情况。

                                     通常，可以在导出时，将主键设置为null，利用 auto_increment 的特性，形成新的主键。

       ⑤ 关于默认值：

           对表的 score 字段设置默认值，设置为80：

           利用默认值字段：可以直接写 default , 也可以写成 default(字段名)。

---------------------------------------------------------------------------------------------------------------------

创建数据 之 replace：

【案例】对于下表，主键为 id ,

           此时，若试图插入 id=1 的新数据，会发生主键冲突，导致插入错误。

           此时可以考虑使用 replace 语句：  replace into tb_name （字段列表） values （值列表）；

           效果相当于 更新了原来的数据。

---------------------------------------------------------------------------------------------------------------------

2. 查询数据： select 字段列表 from tb_name 查询条件;

   ①查询所有字段                                       ② 查询部分字段                                            ③查询多个字段

   tip:查询条件可以省略，表示所有的记录都获得，相当于where 1

 ④ 查询满足一定条件的数据：

    tip:此时通过查询条件来筛选（之前是where 1，现在改为where score>=90）

---------------------------------------------------------------------------------------------------------------------

 select * into outfile 文件地址 from ...：可以将检索到的数据，保存到服务器的文件内。

   该功能可以自动创建文件，但是不能创建文件夹。

    文件地址格式： 'e:/mysql/one'

     其中，one文件可以由该语句创建出来，但不能是已有文件。

    生成的文件格式：

     默认的，通过行来区分记录，制表符来区分字段。（但可以修改）

     为了满足某种特别的需求，会采用不同的分割方式，如cvs。

     通过如下的选项：

     fields：设置字段选项； fields terminated by '\t' enclosed by '' escaped by '\\'

     lines：设置行选项（记录选项）； lines terminated by '\n' starting by ''

    【例外】通常所有的记录都通过行来显示，但保存二进制数据不同：

---------------------------------------------------------------------------------------------------------------------

3. 删除数据  : delete from tb_name 条件；

    【允许使用条件】① 删除符合条件的数据；

1 delete from ex_stu where score<90;  // 删除分数低于90分的记录

        ② 允许使用 limit ， 控制删除的记录数（有时配合order by 来使用）。

1 delete from ex_stu limit 3;   // 删除前3条记录
  delete from ex_stu order by score limit 3 ; // 先按照 score 进行升序排序，再删除前3条记录

       ③ 允许连接删除： 允许使用 join 语法，同时删除多个表内

1 delete from one,two using one join two on one.public_field=two.public_field where one_id = 2 ;

         // 从表 one 和 two 中，删除数据。

      // 待删除的数据是两表联合后的结果中，两表的 public_field 相等的数据，并且要满足 one_id = 2。

      // 执行完之后，会发现两表中对应的数据都已经删除了。

---------------------------------------------------------------------------------------------------------------------

删除数据 之 truncate (类似 delete from table,会清空表中的全部信息)

tip : delete from tb_name（逐行删除），并不影响auto_increment 的计数，它仍然会在原来的基础上累积。

       truncate tb_name;会重建 auto_increment 的主键，它会重新开始计数。

---------------------------------------------------------------------------------------------------------------------

 4. 修改数据：update tb_name set 字段=新值，.... 条件；

        将所有分数在90分以上的学生的分数都改成了100分：

 %%%%%%%%%%%%%%%%%%%%%%%%%%%%%%

 多表更新：

1 update one join two on one.public_field = two.field set one_data='x',two_data='y' where one_id=3;

// 先将依据 one.public_field = two.public_field 这一条件，将两表合并。

// 将大表中，满足one_id=3的数据中的 one_data 和 two_data 分别更新为 'x' 和 'y' 。

---------------------------------------------------------------------------------------------------------------------

---------------------------------------------------------------------------------------------------------------------

5. 备份/还原（将数据一次性导出、导入）

    【方案一】适用于 myisam 表。

                  直接将tb_name.frm , tb_name.myd , tb_name.myi三个文件保存、备份即可。

             需要的时候，直接解压、移动到相应的数据库目录内即可。

             tip : 如果是用同样方法，处理innodb 结构的文件，则使用 show table 时，可检测到备份是不成功的。

   【方案二】通用的方案：将建表结构与插入数据的 SQL 语句生成，并保存。

                      下次若需要该结构和数据，直接将数据语句执行即可。

标签：

版权申明：本站文章部分自网络，如有侵权，请联系：west999com@outlook.com
特别注意：本站所有转载文章言论不代表本站观点，本站所提供的摄影照片，插画，设计作品，如需使用，请与原作者联系，版权归原作者所有