MySQL数据操作-DML语句的使用

说明

DML(Data Manipulation Language)数据操作语言,是指对数据库进行增删改的操作指令,主要有INSERT、UPDATE、DELETE三种,代表插入、更新与删除,这是学习MySQL必要掌握的基本知识。

方语法中 [] 中内容可以省略。

INSERT操作

逐行插入

语法格式如下:

 insert into t_name[(column_name1,columnname_2,...)] values (val1,val2);
 或者
 insert into t_name set column_name1 = val1,column_name2 = val2;

1、字段名称和值需要保证数量一直,类型一直,位置一 一对应,否则可能导致异常。

2、not null的字段需要保证有插入的值,否则会报非空的异常信息。允许null的字段如果不想输入数据,字段和值都不出现,或者value用null代替。

3、数值类型,值不需要用单引号括起来,其他的如字符型或日期类型,值需要用单引号括起来;

4、如果表名后面的column_name 省略不写,则代表覆盖该表的所有字段。值的顺序和表中字段顺序须保持一致。

5、上述第二种语法的写法更繁琐,现在比较少使用。

测试一下:

mysql> desc `user1`;
+---------+--------------+------+-----+---------+----------------+
| Field  | Type     | Null | Key | Default | Extra     |
+---------+--------------+------+-----+---------+----------------+
| id   | bigint(20)  | NO  | PRI | NULL  | auto_increment |
| name  | varchar(20) | NO  |   | NULL  |        |
| age   | int(11)   | NO  |   | 0    |        |
| address | varchar(255) | YES |   | NULL  |        |
+---------+--------------+------+-----+---------+----------------+
4 rows in set

mysql> insert into `user1`(name,age,address) values('brand',20,'fuzhou');
Query OK, 1 row affected

mysql> insert into `user1`(age,address) values(20,'fuzhou');
1364 - Field 'name' doesn't have a default value

mysql> insert into `user1` values('sol',21,'xiamen');
1136 - Column count doesn't match value count at row 1

mysql> insert into `user1` values(null,'sol',21,'xiamen');
Query OK, 1 row affected

mysql> select * from `user1`;
+----+-------+-----+---------+
| id | name | age | address |
+----+-------+-----+---------+
| 3 | brand | 20 | fuzhou |
| 4 | sol  | 21 | xiamen |
+----+-------+-----+---------+
2 rows in set

批量插入

语法格式如下:

 insert into t_name [(column_name1,column_name2)] values (val1_1,val1_2),(val2_1,val2_2)...);
 或者
 insert into t_name [(column_name1,column_name2)] select o_name1,o_name2 from o_t_name [where condition];

1、上述第一个语法,values 后面的值个数需要同等配对 column的数量,可以设置多个,逗号隔开,提高数据插入效率。

2、第二个语法,select查询的字段和插入数据的字段数量、顺序、类型需要一致。 insert的字段可以省略,代表插入t_name表所有字段。条件可选。

测试一下:

mysql> insert into `user1`(name,age,address) values('brand',20,'fuzhou'),('sol',21,'xiamen');
Query OK, 2 rows affected
Records: 2 Duplicates: 0 Warnings: 0

mysql> select * from `user1`;
+----+-------+-----+---------+
| id | name | age | address |
+----+-------+-----+---------+
| 5 | brand | 20 | fuzhou |
| 6 | sol  | 21 | xiamen |
+----+-------+-----+---------+
2 rows in set
mysql> desc `user2`;
+---------+--------------+------+-----+---------+----------------+
| Field  | Type     | Null | Key | Default | Extra     |
+---------+--------------+------+-----+---------+----------------+
| id   | bigint(20)  | NO  | PRI | NULL  | auto_increment |
| name  | varchar(20) | NO  |   | NULL  |        |
| age   | int(11)   | NO  |   | 0    |        |
| address | varchar(255) | YES |   | NULL  |        |
| sex   | int(11)   | NO  |   | 1    |        |
+---------+--------------+------+-----+---------+----------------+
5 rows in set

mysql> insert into `user2` (name,age,address,sex) select name,age,address,null from `user1`;
Query OK, 2 rows affected
Records: 2 Duplicates: 0 Warnings: 0

mysql> select * from `user2`;
+----+-------+-----+---------+------+
| id | name | age | address | sex |
+----+-------+-----+---------+------+
| 7 | brand | 20 | fuzhou | 1  |
| 8 | sol  | 21 | xiamen | 1  |
+----+-------+-----+---------+------+
2 rows in set

UPDATE操作

数据更新

语法格式如下:

update t_name [[as] alias] set [ alias.]column_name1 = val1,[alias.]column_name2 = val2 [where condition];

1、alias 是别名的意思,别名越简单识别性越强越好,容易辨认,方便操作,没有别名情况下,表名就是别名

2、as alias 中as也是可选的,where 条件也是可选的,所以用户可以选择需要的,符合特定条件的部分数据进行更新。

测试一下:

mysql> select * from `user2`;
+----+-------+-----+---------+------+
| id | name | age | address | sex |
+----+-------+-----+---------+------+
| 7 | brand | 20 | fuzhou | NULL |
| 8 | sol  | 21 | xiamen | NULL |
+----+-------+-----+---------+------+
2 rows in set

mysql> update `user2` as u2 set u2.name = 'hero',u2.age=23,u2.sex=1 where id=7;
Query OK, 1 row affected
Rows matched: 1 Changed: 1 Warnings: 0

mysql> select * from `user2`;
+----+------+-----+---------+------+
| id | name | age | address | sex |
+----+------+-----+---------+------+
| 7 | hero | 23 | fuzhou |  1 |
| 8 | sol | 21 | xiamen | NULL |
+----+------+-----+---------+------+
2 rows in set

还有一种方式是同时更新多个表,使用不同的别名以及一些条件去限制,不过不建议这么做,操作易错,并且不好维护。

DELETE操作

delete方式删除

语法格式如下:

delete [alias] from t_name [[as] alias] [where condition];

1、跟上面一样,alias代表别名,没有别名情况下,表名就是别名

2、如果表设置了别名,则delete后面必须跟上别名,否则数据库会报异常。

测试一下:

mysql> select * from `user2`;
+----+------+-----+---------+------+
| id | name | age | address | sex |
+----+------+-----+---------+------+
| 7 | hero | 23 | fuzhou |  1 |
| 8 | sol | 21 | xiamen | NULL |
+----+------+-----+---------+------+
2 rows in set

mysql> delete from `user2` as alias where sex=1;
1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'as alias where sex=1' at line 1

mysql> delete alias from `user2` as alias where sex=1;
Query OK, 1 row affected

mysql> select * from `user2`;
+----+------+-----+---------+------+
| id | name | age | address | sex |
+----+------+-----+---------+------+
| 8 | sol | 21 | xiamen | NULL |
+----+------+-----+---------+------+
1 row in set

3、如果删除表中所有的数据,则后面不带上where条件即可,不过要谨慎使用哟。

mysql> select * from `user2`;
+----+-------+-----+----------+-----+
| id | name | age | address | sex |
+----+-------+-----+----------+-----+
| 8 | sol  | 21 | xiamen  |  0 |
| 10 | brand | 21 | fuzhou  |  1 |
| 11 | helen | 20 | quanzhou |  0 |
+----+-------+-----+----------+-----+
3 rows in set

mysql> delete from `user2`;
Query OK, 3 rows affected

mysql> select * from `user2`;
Empty set

truncate方式删除

语法格式如下:

truncate t_name;
mysql> select * from `user2`;
+----+-------+-----+----------+-----+
| id | name | age | address | sex |
+----+-------+-----+----------+-----+
| 12 | brand | 21 | fuzhou  |  1 |
| 13 | helen | 20 | quanzhou |  0 |
| 14 | sol  | 21 | xiamen  |  0 |
+----+-------+-----+----------+-----+
3 rows in set

mysql> truncate `user2`;
Query OK, 0 rows affected

mysql> select * from `user2`;
Empty set

看起来跟delete很像,但是重新插入数据会发现,他的自增主键会重新从1开始,但是delete的是直接在原来的所以自增值之后往上加。看下面id字段。

mysql> insert into `user2` (name,age,address,sex) values('brand',21,'fuzhou',1),('helen',20,'quanzhou',0),('sol',21,'xiamen',0);
Query OK, 3 rows affected
Records: 3 Duplicates: 0 Warnings: 0

mysql> select * from `user2`;
+----+-------+-----+----------+-----+
| id | name | age | address | sex |
+----+-------+-----+----------+-----+
| 1 | brand | 21 | fuzhou  |  1 |
| 2 | helen | 20 | quanzhou |  0 |
| 3 | sol  | 21 | xiamen  |  0 |
+----+-------+-----+----------+-----+
3 rows in set

那 truncate 和 delete有什么区别呢?我们来梳理下。

truncate和delete的比较

1、truncate 指的是清空表的数据、释放表的空间,但不删除表的架构定义(表结构)。因为不包含Where条件,所以不是删除具体行,而是将整个表清空了。

2、而delete 语句是删除表中的数据行,可以在后面带上条件控制删除的维度、范围,它每次从表中删除一行,会同时将该行的删除操作作为事务保存在日志中,用于进行可能的回滚操作。

3、truncate 和 delete 一样的地方是:只是删除数据,涉及到的表结构及其列、约束、索引等均不会变。

4、如果被外键 foreign key 约束,不能使用truncate ,只能使用不带where子句的delete语句。

5、truncate 操作会记录在日志中,delete操作会放到 rollback segement 中,执行时要等事务被commit才会生效;所以delete 会触发删除触发器(如果有的话),truncate 不会。

6、如果像上面我们测试的那样,包含自增字段,truncate方式清空之后,自增列的值会被初始化从1开始。

delete方式要分情况判断(如果数据全部delete,数据库未被重启,则按照之前max+1;数据库重启了,则一样会重新开始计算自增列的初始值)。

7、还有drop,drop语句会删除表包括 结构、数据、依赖该表的约束(constrain),触发器(trigger)索引(index)等。

以上就是MySQL数据操作-DML语句的使用的详细内容,更多关于MySQL dml语句的资料请关注我们其它相关文章!

(0)

相关推荐

  • MySQL的DML语言操作实例

    补充说明,外键:不要使用外键,一切外键概念都在应用层解决. 补充说明,数据库的列,也就是字段名,尽量带上飘符号` 数据库存在的意义:数据存储和数据管理. 数据库:行(数据),列(字段) 注意:本页是解决了行的数据问题.上一页是解决列的字段问题. DML语言:数据操作语言 1.添加(insert) 2.更新(update) 3.删除(delete) 1. 添加 insert (往行里添加数据) -- 插入语句(添加) -- 格式: INSERT INTO `表名`(`字段名1`,`字段名2`,`字

  • MySQL DML语句整理汇总

    DML操作是指对数据库中表记录的操作,主要包括表记录的插入(insert).更新(update).删除(delete)和查询(select),是开发人员日常使用最频繁的操作. 1.插入(insert) 格式1. INSERT INTO emp(ename,hiredate,sal,deptno) VALUES('zzx1','2000-01-01','2000',1); 格式2. INSERT INTO emp VALUES('lisa','2003-02-01','3000',2); 不用指定

  • python的mysql数据库建立表与插入数据操作示例

    本文实例讲述了python的mysql数据库建立表与插入数据操作.分享给大家供大家参考,具体如下: mysql数据库建立表 一 代码 import pymysql # 打开数据库连接 db = pymysql.connect("localhost","root","root","db_test01" ) # 使用 cursor() 方法创建一个游标对象 cursor cursor = db.cursor() # 使用 exec

  • PHP递归写入MySQL实现无限级分类数据操作示例

    本文实例讲述了PHP递归写入MySQL实现无限级分类数据操作.分享给大家供大家参考,具体如下: PHP递归写入MySQL无限级分类数据,表结构: CREATE TABLE `kepler_goods_category` ( `id` int unsigned NOT NULL PRIMARY KEY AUTO_INCREMENT, `parentid` int unsigned NOT NULL default 0 comment '父级分类ID', `name` varchar(255) NO

  • PHP5.5基于mysqli连接MySQL数据库和读取数据操作实例详解

    本文实例讲述了PHP5.5基于mysqli连接MySQL数据库和读取数据操作.分享给大家供大家参考,具体如下: 在学习1. 开启PHP的API支持 (1)首先修改您的php.ini的配置文件. 查找下面的语句: ;extension=php_mysqli.dll 将其修改为: extension=php_mysqli.dll (2)重新启动Apache/IIS,即可. (3)说明:PHP需要单独的文件来支持这个扩展库,一般在PHP目录下的ext目录里能找到php_mysqli.dll文件(PHP

  • 详解mysql DML语句的使用

    前言: 在上篇文章中,主要为大家介绍的是DDL语句的用法,可能细心的同学已经发现了.本篇文章将主要聚焦于DML语句,为大家讲解表数据相关操作. 这里说明下DDL与DML语句的分类,可能有的同学还不太清楚. DDL(Data Definition Language):数据定义语言,用于创建.删除.修改.库或表结构,对数据库或表的结构操作.常见的有create,alter,drop等. DML(Data Manipulation Language):数据操纵语言,主要对表记录进行更新(增.删.改).

  • Go语言集成mysql驱动、调用数据库、查询数据操作示例

    本文实例讲述了Go语言集成mysql驱动.调用数据库.查询数据操作.分享给大家供大家参考,具体如下: 1.安装第三方mysql驱动包 go get -u github.com/go-sql-driver/mysql 2.连接数据库基本代码 复制代码 代码如下: package main import (         _"github.com/go-sql-driver/mysql"  // 注意前面的下划线_, 这种方式引入包只执行包的初始化函数         "dat

  • PHP使用PDO创建MySQL数据库、表及插入多条数据操作示例

    本文实例讲述了PHP使用PDO创建MySQL数据库.表及插入多条数据操作.分享给大家供大家参考,具体如下: 创建 MySQL 数据库: <?php $servername = "localhost"; $username = "username"; $password = "password"; try { $conn = new PDO("mysql:host=$servername", $username, $pas

  • 使用Python对MySQL数据操作

    本文介绍Python3使用PyMySQL连接数据库,并实现简单的增删改查. 什么是PyMySQL? PyMySQL是Python3.x版本中用于连接MySQL服务器的一个库,Python2.x中则使用mysqldb. PyMySQL安装 在使用PyMySQL之前,我们需要确保PyMySQL已经安装. PyMySQL下载地址:https://github.com/PyMySQL/PyMySQL. 如果还未安装,我们可以使用以下命令安装最新版的PyMySQL: $ pip install PyMyS

  • MySQL数据操作-DML语句的使用

    说明 DML(Data Manipulation Language)数据操作语言,是指对数据库进行增删改的操作指令,主要有INSERT.UPDATE.DELETE三种,代表插入.更新与删除,这是学习MySQL必要掌握的基本知识. 方语法中 [] 中内容可以省略. INSERT操作 逐行插入 语法格式如下: insert into t_name[(column_name1,columnname_2,...)] values (val1,val2); 或者 insert into t_name se

  • MySQL数据库操作DML 插入数据,删除数据,更新数据

    目录 DML介绍 数据插入 数据修改 数据删除 DML介绍 DML是指数据操作语言,英文全称是Data Manipulation Language,用来对数据库中表的数据记录进行更新. 关键字: 插入insert 删除delete 更新update 数据插入 insert into 表 (列名1,列名2,列名3...) values (值1,值2,值3...): //向表中插入某些 insert into 表 values (值1,值2,值3...); //向表中插入所有列 这里的两种方式,第一

  • MySQL数据操作管理示例详解

    目录 一.查看表和查看表的定义 二.删除表 三.创建表 四.表类型 五.修改表 六.完整性 1.数据完整性 2.使用约束实现数据完整性 3.在数据表上添加约束 4.删除约束 七.添加数据 八.修改数据 九.删除数据 十.SQL语句中的运算符 算数运算符 比较运算符 逻辑运算符 一.查看表和查看表的定义 SHOW TABLE 表名 DESC 表名; DESCRIBE 表名 二.删除表 DROP TABLE [IF EXISTS ] 表名; 如果表USER存在,删除表USER DROP TABLE

  • MySql常用操作SQL语句汇总

    MySQL的常见操作在这里先做一下总结,已经整合到代码里面,经过检验无误. 复制代码 代码如下: /*创建一个数据库*/ create database xuning_test; /*说明当时使用数据库对象*/ use xuning_test; /*向数据库中添加表并且定义表的结构*/ create table person(  id int not null,  name varchar(16) not null,  sex varchar(16) not null,  age int not

  • MySQL学习之数据库操作DML详解小白篇

    目录 1.插入语句 1.1插入一行 1.2插入多行 1.3插入查询语句 2.修改语句 2.1修改单表记录 2.2修改多表记录 3.删除语句 3.1方式1使用delete删除 3.2方式2使用truncate删除 3.3使用truncate和delete删除的区别 1.插入语句 1.1插入一行 有两种: 1.以小括号的形式 这里注意:如果表名后边不加小括号对应内容,那么默认就是添加所有列 2.以set形式 insert into + 表名 set 表中的某一个字段=要插入的值,表中的某一个字段=要

  • MySQL基础教程之DML语句详解

    目录 DML 语句 1.插入记录 2.更新记录 3.简单查询记录 4.删除记录 5.查询记录详解(DQL语句) 5.1.查询不重复的记录 5.2.条件查询 5.3.聚合查询 5.4.排序查询 5.5.limit查询 5.6.连表查询 5.7.子查询 5.8.记录联合 5.9.select语句的执行顺序 6.总结 DML 语句 DML(Data Manipulation Language)语句:数据操纵语句. 用途:用于添加.修改.删除和查询数据库记录,并检查数据完整性. 常用关键字:insert

  • MySQL误操作后快速恢复数据的方法

    摘要: 利用binlog闪回误操作数据. 基本上每个跟数据库打交道的程序员(当然也可能是你同事)都会碰一个问题,MySQL误操作后如何快速回滚?比如,delete一张表,忘加限制条件,整张表没了.假如这还是线上环境核心业务数据,那这事就闹大了.误操作后,能快速回滚数据是非常重要的. 传统解法 用全量备份重搭实例,再利用增量binlog备份,恢复到误操作之前的状态.然后跳过误操作的SQL,再继续应用binlog.此法费时费力,不值得再推荐. 利用binlog2sql快速闪回 首先,确认你的MySQ

  • MySQL数据定义语言DDL的基础语句

    MySQL DDL 语句 什么是DDL,DML. DDL 是数据定义语言,就是对数据库,表层面的操作,如 CREATE,ALTER,DROP.DML 是数据操作语言,也就是对表中数据的增删改查,如 SELECT,UPDATE,INSERT,DELETE. 假设现在有数据库 lian_xi 里面有表 user 和 orders; 1.1登陆MySQL环境 在cmd窗口中输入mysql -u root -p登录MySQL环境 1.2查看数据库 Show databases; 1.3使用数据库 Use

随机推荐