mysql 数据_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 tableteacher;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 语句生成，并保存。

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