全部频道

首页 热点资讯 义务教育 高等教育 出国留学 考研考公
您的当前位置：首页正文

MySql学习之插入、删除和更新

2023-11-10 来源：花图问答

INTO customersVALUES( NULL,‘person‘);

 此例子插入一个新客户到customers表。存储到每个表列中的数据在VALUES子句中给出，对每个列必须提供一个值。如果某个列没有值（如上面的cust_contact和cust_email列），应该使用NULL值（假定表允许对该列指定空值）。各个列必须以它们在表定义中出现的次序填充。第一列cust_id也为NULL。这是因为每次插入一个新行时，该列由MySQL自动增量。你不想给出一个值（这是MySQL的工作），又不能省略此列（如前所述，必须给出每个列），所以指定一个NULL值（它被MySQL忽略，MySQL在这里插入下一个可用的cust_id值）。

虽然这种语法很简单，但并不安全，应该尽量避免使用。上面的SQL语句高度依赖于表中列的定义次序，并且还依赖于其次序容易获得的信息。即使可得到这种次序信息，也不能保证下一次表结构变动后各个列保持完全相同的次序。因此，编写依赖于特定列次序的SQL语句是很不安全的。如果这样做，有时难免会出问题。

2、表名后的括号里明确地给出了列名

INSERT INTO customers(cust_id,cust_name)VALUES( NULL,‘perso‘);

 注意：不管使用哪种INSERT语法，都必须给出VALUES的正确数目。如果不提供列名，则必须给每个表列提供一个值。如果提供列名，则必须对每个列出的列给出一个值。

使用上述可以省略某些列，但是省略的列必须是可以为空或有默认值得。

性能：INSERT操作可能很耗时（特别是有很多索引需要更新时），而且它可能降低等待处理的SELECT语句的性能。如果数据检索是最重要的（通常是这样），则你可以通过在INSERT和INTO之间添加关键字LOW_PRIORITY，指示MySQL降低INSERT语句的优先级，如下所示：也适用于介绍的UPDATE和DELETE语句。

INSERT LOW_PRIORITY INTO customers(cust_id,cust_name)VALUES( NULL,‘perso‘);

 

3、插入多行

INSERT LOW_PRIORITY INTO customers(cust_id,cust_name)VALUES( NULL,‘perso‘);INSERT LOW_PRIORITY INTO customers(cust_id,cust_name)VALUES( NULL,‘perso‘);

 

或

INSERT LOW_PRIORITY INTO customers(cust_id,cust_name)VALUES( NULL,‘perso‘),( NULL,‘perso‘);

其中单条INSERT语句有多组值，每组值用一对圆括号括起来，用逗号分隔。

4、插入检索出的数据

INSERT INTO custmoerNew(cs_id,cs_name)SELECT cust_id, 　　　cust_name FROM customers;

 

注意：SELECT语句从customers检索出要插入的值，而不是列出它们。SELECT中列出的每个列对应于custmoerNew表名后所跟的列表中的每个列。这条语句将插入多少行有赖于从customers表中检索出多少行。如果这个表为空，则没有行被插入（也不产生错误，因为操作仍然是合法的）。如果这个表确实含有数据，则所有数据将被插入到customerNew。

INSERT SELECT中SELECT语句可包含WHERE子句以过滤插入的数据。

二、更新数据

 使用UPDATE语句

基本的UPDATE语句由3部分组成，分别是：

? 要更新的表；　　　　? 列名和它们的新值；

? 确定要更新行的过滤条件。

UPDATE customersSET cust_name="立冬"WHERE cust_id=1

 

   注意：IGNORE关键字 如果用UPDATE语句更新多行，并且在更新这些行中的一行或多行时出一个现错误，则整个UPDATE操作被取消（错误发生前更新的所有行被恢复到它们原来的值）。为即使是发生错误，也继续进行更新，可使用IGNORE关键字，如下所示：UPDATE IGNORE customers…

为了删除某个列的值，可设置它为NULL（假如表定义允许NULL值）。

三、删除操作

DELETE不需要列名或通配符。DELETE删除整行而不是删除列。为了删除指定的列，请使用UPDATE语句。

DELETE FROM custmoerNewWHERE cs_id = 5;

 注意：更快的删除 如果想从表中删除所有行，不要使用DELETE。可使用TRUNCATE TABLE语句，它完成相同的工作，但速度更快（TRUNCATE实际是删除原来的表并重新创建一个表，而不是逐行删除表中的数据）。

下面是许多SQL程序员使用UPDATE或DELETE时所遵循的习惯。

? 除非确实打算更新和删除每一行，否则绝对不要使用不带WHERE子句的UPDATE或DELETE语句。

? 保证每个表都有主键，尽可能像WHERE子句那样使用它（可以指定各主键、多个值或值的范围）。

? 在对UPDATE或DELETE语句使用WHERE子句前，应该先用SELECT进行测试，保证它过滤的是正确的记录，以防编写的WHERE子句不正确。

? 使用强制实施引用完整性的数据库，这样MySQL将不允许删除具有与其他表相关联的数据的行。

 

MySql学习之插入、删除和更新

标签：多行   记录   sql   工作   ignore   而且   insert   语法   高度   

小编还为您整理了以下内容，可能对您也有帮助：

MySQL中INSERT，UPDATE和REPLACE的区别与用法

insert是插入数据

update是更新数据

择列表

选择列表（select_list）指出所查询列，它可以是一组列名列表、星号、表达式、变量（包括局部变量和全局变量）等构成。

1、选择所有列

例如，下面语句显示testtable表中所有列的数据：

SELECT *　FROM testtable

2、选择部分列并指定它们的显示次序

查询结果集合中数据的排列顺序与选择列表中所指定的列名排列顺序相同。

3、更改列标题

在选择列表中，可重新指定列标题。定义格式为：

列标题=列名　列名 列标题

如果指定的列标题不是标准的标识符格式时，应使用引号定界符，例如，下列语句使用汉字显示列标题：　SELECT 昵称=nickname，电子邮件=email　FROM testtable。

4、删除重复行

SELECT语句中使用ALL或DISTINCT选项来显示表中符合条件的所有行或删除其中重复的数据行，默认为ALL。使用DISTINCT选项时，对于所有重复的数据行在SELECT返回的结果集合中只保留一行。

mysql笔记(10)-数据的插入和更新(insert/update/case)

常见的插入方式有以下几种：

这种方式每次只能插入一行

且set从句内的values不能全部为空

这种方式可以一次性插入多行

不同行之间的数据要 用逗号进行分隔

这种方式用于替换表中的某一行

若新插入记录的主码已经存在于表中，则用新记录替换旧记录

若新插入记录的主码不在表中，则直接插入新记录

普通的update语句写法如下：

例如：在instructor(教师信息)表中

我们想更新 ID为10101的教师的工资为70000

对于更复杂的数据更新 我们可以添加 case-when从句

从而实现对数据的分类更新

例如：在instructor(教师信息)表中 对所有教师进行涨薪

对工资小于等于100000的涨薪5%，其他人涨薪3%

对数据库操作：查询—更新；删除—插入 哪种方式效率高或者科学呢？ mysql内存表

还是更新好一些，每删除再插入，主键就会增加一个数字，而且删除和插入，是要对数据库做两次操作，而update只做一次

几种MySQL大量数据插入或修改的方法比较

不管是日常业务数据处理中，还是数据库的导入导出，都可能遇到需要处理大量数据的插入或修改。插入或修改的方式和数据库引擎都会对插入速度造成影响，这篇文章旨在从理论和实践上对各种方法进行分析和比较，方便以后应用中插入方法的选择。

插入分析

MySQL中插入一个记录需要的时间由下列因素组成，其中的数字表示大约比例：

连接：（3）

发送查询给服务器：（2）

分析查询：（2）

插入记录：（1x记录大小）

插入索引：（1x索引）

关闭：（1）

如果我们每插入一条都执行一个SQL语句，那么我们需要执行除了连接和关闭之外的所有步骤N次，这样是非常耗时的，优化的方式有一下几种：

（1）在每个insert语句中写入多行，批量插入

（2）将所有查询语句写入事务中

（3）利用Load Data导入数据

每种方式执行的性能如下。

Innodb引擎

InnoDB 给 MySQL 提供了具有事务(commit)、回滚(rollback)和崩溃修复能力(crash recovery capabilities)的事务安全(transaction-safe (ACID compliant))型表。InnoDB 提供了行锁(locking on row level)以及外键约束(FOREIGN KEY constraints)。

InnoDB 的设计目标是处理大容量数据库系统，它的 CPU 利用率是其它基于磁盘的关系数据库引擎所不能比的。在技术上，InnoDB 是一套放在 MySQL 后台的完整数据库系统，InnoDB 在主内存中建立其专用的缓冲池用于高速缓冲数据和索引。

测试环境

Macbook Air 12mid apache2.2.26 php5.5.10 mysql5.6.16

总数100W条数据

插入完后数据库大小38.6MB（无索引），46.8（有索引）

无索引单条插入 总耗时：229s 峰值内存：246KB

有索引单条插入 总耗时：242s 峰值内存：246KB

无索引批量插入 总耗时：10s 峰值内存：8643KB

有索引批量插入 总耗时：16s 峰值内存：8643KB

无索引事务插入 总耗时：78s 峰值内存：246KB

有索引事务插入 总耗时：82s 峰值内存：246KB

无索引Load Data插入 总耗时：12s 峰值内存：246KB

有索引Load Data插入 总耗时：11s 峰值内存：246KB

MyIASM引擎

MyISAM 是MySQL缺省存贮引擎。设计简单，支持全文搜索。

测试环境

Macbook Air 12mid apache2.2.26 php5.5.10 mysql5.6.16

总数100W条数据

插入完后数据库大小19.1MB（无索引），38.6（有索引）

无索引单条插入 总耗时：82s 峰值内存：246KB

有索引单条插入 总耗时：86s 峰值内存：246KB

无索引批量插入 总耗时：3s 峰值内存：8643KB

有索引批量插入 总耗时：7s 峰值内存：8643KB

无索引Load Data插入 总耗时：6s 峰值内存：246KB

有索引Load Data插入 总耗时：8s 峰值内存：246KB

总结

我测试的数据量不是很大，不过可以大概了解这几种插入方式对于速度的影响，最快的必然是Load Data方式。这种方式相对比较麻烦，因为涉及到了写文件，但是可以兼顾内存和速度。

测试代码

<?php

$dsn = 'mysql:host=localhost;dbname=test';

$db = new PDO($dsn,'root','',array(PDO::ATTR_PERSISTENT => true));

//删除上次的插入数据

$db->query('delete from `test`');

//开始计时

$start_time = time();

$sum = 1000000;

// 测试选项

$num = 1;

if ($num == 1){

// 单条插入

for($i = 0; $i < $sum; $i++){

$db->query("insert into `test` (`id`,`name`) values ($i,'tsetssdf')");

}

} elseif ($num == 2) {

// 批量插入，为了不超过max_allowed_packet，选择每10万插入一次

for ($i = 0; $i < $sum; $i++) {

if ($i == $sum - 1) { //最后一次

if ($i%100000 == 0){

$values = "($i, 'testtest')";

$db->query("insert into `test` (`id`, `name`) values $values");

} else {

$values .= ",($i, 'testtest')";

$db->query("insert into `test` (`id`, `name`) values $values");

}

break;

}

if ($i%100000 == 0) { //平常只有在这个情况下才插入

if ($i == 0){

$values = "($i, 'testtest')";

} else {

$db->query("insert into `test` (`id`, `name`) values $values");

$values = "($i, 'testtest')";

}

} else {

$values .= ",($i, 'testtest')";

}

}

} elseif ($num == 3) {

// 事务插入

$db->beginTransaction();

for($i = 0; $i < $sum; $i++){

$db->query("insert into `test` (`id`,`name`) values ($i,'tsetssdf')");

}

$db->commit();

} elseif ($num == 4) {

// 文件load data

$filename = dirname(__FILE__).'/test.sql';

$fp = fopen($filename, 'w');

for($i = 0; $i < $sum; $i++){

fputs($fp, "$i,'testtest'rn");

}

$db->exec("load data infile '$filename' into table test fields terminated by ','");

}

$end_time = time();

echo "总耗时", ($end_time - $start_time), "秒n";

echo "峰值内存", round(memory_get_peak_usage()/1000), "KBn";这里简单写一下 SQL 语句插入和修改语句的拼接形式：

（1）insert 语句拼接：

INSERT INTO user (id, name) VALUES (1, '张三');

INSERT INTO user (id, name) VALUES (2, '李四');

INSERT INTO user (id, name) VALUES (3, '王五');

可拼接为：

INSERT INTO user (id, name) VALUES

(1, '张三'),

(2, '李四'),

(3, '王五');

（2）update 语句拼接：

update user set name='张三' where id='1';

update user set name='李四' where id='2';

update user set name='王五' where id='3';

可拼接为：

update user

set name = CASE id

when 1 then '张三'

when 2 then '李四'

when 3 then '王五'

end

where id IN (1,2,3);

显示全文