第4章数据操作语言DML

原创

zdb呀 2024-06-05 10:28:27 博主文章分类：MySQL ©著作权

文章标签 mysql sql DML 字段名字符串 文章分类 JavaScript 前端开发

©著作权归作者所有：来自51CTO博客作者zdb呀的原创作品，请联系作者获取转载授权，否则将追究法律责任

文章目录

第4章 DML语言：数据操作语言

4.1 DML增：insert

（1）设置默认值：defalut
（2）数字格式化：format()函数
（3）插入日期：str_to_date()
（4）date和datetime类型区别
（5）获取系统当前时间：new()
（6）一次插入多条数据
（7）select查询结果插入表

4.2 DML改：update
4.3 DML删：delete

第4章 DML语言：数据操作语言

DML：数据操作语言：insert（增）、update（删）、delete（改）。主要是操作表中数据的操作

4.1 DML增：insert

语法格式：insert into 表名(字段名1,字段名2,字段名3…) values(值1,值2,值3);

注意：字段名和值要一一对应，即数量要对应。数据类型要对应。

mysql> insert into t_student(no,name,sex,age,email) values(1,'zhangsan','m',20,'zhangsan@123.com');
Query OK, 1 row affected (0.01 sec)

mysql> select * from t_student;
+------+----------+------+------+------------------+
| no   | name     | sex  | age  | email            |
+------+----------+------+------+------------------+
|    1 | zhangsan | m    |   20 | zhangsan@123.com |
+------+----------+------+------+------------------+
1 row in set (0.00 sec)

mysql> insert into t_student(email,name,sex,age,no) values('lisi@123.com','lisi','m',22,2);
Query OK, 1 row affected (0.01 sec)

mysql> select * from t_student;
+------+----------+------+------+------------------+
| no   | name     | sex  | age  | email            |
+------+----------+------+------+------------------+
|    1 | zhangsan | m    |   20 | zhangsan@123.com |
|    2 | lisi     | m    |   22 | lisi@123.com     |
+------+----------+------+------+------------------+
2 rows in set (0.00 sec)

mysql> insert into t_student(no) values(3);
Query OK, 1 row affected (0.00 sec)

mysql> select * from t_student;
+------+----------+------+------+------------------+
| no   | name     | sex  | age  | email            |
+------+----------+------+------+------------------+
|    1 | zhangsan | m    |   20 | zhangsan@123.com |
|    2 | lisi     | m    |   22 | lisi@123.com     |
|    3 | NULL     | NULL | NULL | NULL             |
+------+----------+------+------+------------------+
3 rows in set (0.00 sec)

mysql> insert into t_student(name) values('wangwu');
Query OK, 1 row affected (0.01 sec)

mysql> select * from t_student;
+------+----------+------+------+------------------+
| no   | name     | sex  | age  | email            |
+------+----------+------+------+------------------+
|    1 | zhangsan | m    |   20 | zhangsan@123.com |
|    2 | lisi     | m    |   22 | lisi@123.com     |
|    3 | NULL     | NULL | NULL | NULL             |
| NULL | wangwu   | NULL | NULL | NULL             |
+------+----------+------+------+------------------+
4 rows in set (0.00 sec)

注意：insert语句执行成功，必然多一条记录。若没有给定其它字段值，默认为NULL。

（1）设置默认值：defalut

drop table if exists t_student;

create table t_student(
    no int,
    name varchar(32),
    sex char(1) default 'm',   //default设置默认值   默认性别：'m'
    age int(3),
    email varchar(255)
    );

查看表的数据类型

mysql> desc t_student;
+-------+--------------+------+-----+---------+-------+
| Field | Type         | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+-------+
| no    | int(11)      | YES  |     | NULL    |       |
| name  | varchar(32)  | YES  |     | NULL    |       |
| sex   | char(1)      | YES  |     | m       |       |
| age   | int(3)       | YES  |     | NULL    |       |
| email | varchar(255) | YES  |     | NULL    |       |
+-------+--------------+------+-----+---------+-------+
5 rows in set (0.01 sec)

mysql> insert t_student(no) values(1);
Query OK, 1 row affected (0.00 sec)

mysql> select * from t_student;
+------+------+------+------+-------+
| no   | name | sex  | age  | email |
+------+------+------+------+-------+
|    1 | NULL | m    | NULL | NULL  |
+------+------+------+------+-------+
1 row in set (0.00 sec)

mysql> insert into t_student values(2);
ERROR 1136 (21S01): Column count doesn't match value count at row 1

注意：前面字段名省略的话，等于都写上了！所以值也要都写上！

mysql> insert into t_student values(2);
ERROR 1136 (21S01): Column count doesn't match value count at row 1
mysql> insert into t_student values(2, 'lisi', 'f', 20, 'lisi@123.com');
Query OK, 1 row affected (0.01 sec)

mysql> select * from t_student;
+------+------+------+------+--------------+
| no   | name | sex  | age  | email        |
+------+------+------+------+--------------+
|    1 | NULL | m    | NULL | NULL         |
|    2 | lisi | f    |   20 | lisi@123.com |
+------+------+------+------+--------------+
2 rows in set (0.00 sec)

（2）数字格式化：format()函数

格式：format(数字, ‘格式’)

mysql> select ename,sal from emp limit 5;
+--------+---------+
| ename  | sal     |
+--------+---------+
| SMITH  |  800.00 |
| ALLEN  | 1600.00 |
| WARD   | 1250.00 |
| JONES  | 2975.00 |
| MARTIN | 1250.00 |
+--------+---------+
5 rows in set (0.00 sec)

mysql> select ename, format(sal,'$999,999') as sal from emp limit 5;
+--------+-------+
| ename  | sal   |
+--------+-------+
| SMITH  | 800   |
| ALLEN  | 1,600 |
| WARD   | 1,250 |
| JONES  | 2,975 |
| MARTIN | 1,250 |
+--------+-------+
5 rows in set, 5 warnings (0.00 sec)

（3）插入日期：str_to_date()

str_to_date：将varchar类型转换成date类型
date_format：将date类型转换成varchar类型

注意：标识符全部小写，单词之间使用下划线。

drop table if exists t_user;                  //删除表
create table t_user(                          //重新创建表
    id int,
    name varchar(32),
    birth date
    );

mysql> desc t_user;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(11)     | YES  |     | NULL    |       |
| name  | varchar(32) | YES  |     | NULL    |       |
| birth | date        | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
3 rows in set (0.01 sec)
  
mysql> insert into  t_user(id,name,birth) values(1,'zs','01-10-1990');  //插入数据
ERROR 1292 (22007): Incorrect date value: '01-10-1990' for column 'birth' at row 1

出问题了：原因是类型不匹配。数据库birth是date类型，这里给了一个字符串varchar。

可以使用str_to_date函数进行类型转换，将字符串转换成日期类型date

语法格式：str_to_date('字符串日期', '日期格式')

mysql的日期格式

%Y	年
	%m  月
	%d  日
	%h	时
	%i	分
	%s	秒

mysql> insert into  t_user(id,name,birth) values(1,'zs',str_to_date('01-10-1990','%d-%m-%Y'));
Query OK, 1 row affected (0.00 sec)

mysql> select * from t_user;
+------+------+------------+
| id   | name | birth      |
+------+------+------------+
|    1 | zs   | 1990-10-01 |
+------+------+------------+
1 row in set (0.00 sec)

str_to_date函数把字符串varchar转换成日期date类型，通常在插入insert时使用

注意：如果你提供的日期字符串是这个格式：%Y-%m-%d，可以直接插入，str_to_date函数就不需要了

mysql> insert into t_user(id,name,birth) values(2, 'lisi', '1990-10-01');
Query OK, 1 row affected (0.01 sec)

mysql> select * from t_user;
+------+------+------------+
| id   | name | birth      |
+------+------+------------+
|    1 | zs   | 1990-10-01 |
|    2 | lisi | 1990-10-01 |
+------+------+------------+
2 rows in set (0.00 sec)

查询的时候可以以某个特定的日期格式展示。date_format函数将日期类型转换成特定格式的字符串。

语法： date_format(日期类型数据, ‘日期格式’)

mysql>  select id,name,date_format(birth,'%m/%d/%y') as birth from t_user;
+------+------+----------+
| id   | name | birth    |
+------+------+----------+
|    1 | zs   | 10/01/90 |
|    2 | lisi | 10/01/90 |
+------+------+----------+
2 rows in set (0.00 sec)

mysql> select id,name,birth from t_user;
		+------+----------+------------+
		| id   | name     | birth      |
		+------+----------+------------+
		|    1 | zhangsan | 1990-10-01 |
		|    2 | lisi     | 1990-10-01 |
		+------+----------+------------+

以上语句进行了默认格式转换，自动将数据库中的date类型转换成varchar类型。默认的日期格式：’%Y-%m-%d’

（4）date和datetime类型区别

date是短日期：只包括年月日信息。
datetime是长日期：包括年月日时分秒信息。

mysql短日期默认格式：%Y-%m-%d mysql长日期默认格式：%Y-%m-%d %h:%i:%s

mysql> create table t_user(     //创建表
    -> id int,
    -> name varchar(32),
    -> birth date,
    -> create_time datetime    //长日期类型
    -> );
Query OK, 0 rows affected (0.01 sec)

mysql> insert into t_user(id,name,birth,create_time) values(1,'zhangsan','1990-10-01','2021-09-14 15:49:50');
Query OK, 1 row affected (0.00 sec)

mysql> insert into t_user values(1,'zhangsan','1990-10-01','2021-09-14 15:49:50');
Query OK, 1 row affected (0.01 sec)

mysql> select * from t_user;
+------+----------+------------+---------------------+
| id   | name     | birth      | create_time         |
+------+----------+------------+---------------------+
|    1 | zhangsan | 1990-10-01 | 2021-09-14 15:49:50 |
|    1 | zhangsan | 1990-10-01 | 2021-09-14 15:49:50 |
+------+----------+------------+---------------------+
2 rows in set (0.00 sec)

（5）获取系统当前时间：new()

now() 函数获取的时间带有时分秒信息！是datetime类型。

mysql> insert into t_user values(3,'lisi','1990-10-01',now());
Query OK, 1 row affected (0.01 sec)

mysql> select * from t_user;
+------+----------+------------+---------------------+
| id   | name     | birth      | create_time         |
+------+----------+------------+---------------------+
|    1 | zhangsan | 1990-10-01 | 2021-09-14 15:49:50 |
|    1 | zhangsan | 1990-10-01 | 2021-09-14 15:49:50 |
|    3 | lisi     | 1990-10-01 | 2021-11-25 15:50:02 |
+------+----------+------------+---------------------+
3 rows in set (0.00 sec)

（6）一次插入多条数据

语法：insert into t_user(字段名1,字段名2) values(), (), (), ();

mysql> insert into t_user values
    -> (1, 'zs', '1980-10-11', now()),
    -> (2, 'ls', '1981-10-11', now()),
    -> (3, 'ww', '1982-10-11', now());
Query OK, 3 rows affected (0.01 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> select * from t_user;
+------+------+------------+---------------------+
| id   | name | birth      | create_time         |
+------+------+------------+---------------------+
|    1 | zs   | 1980-10-11 | 2021-11-29 20:55:54 |
|    2 | ls   | 1981-10-11 | 2021-11-29 20:55:54 |
|    3 | ww   | 1982-10-11 | 2021-11-29 20:55:54 |
+------+------+------------+---------------------+
3 rows in set (0.00 sec)

（7）select查询结果插入表

用insert将查询到的结果插入表中，很少用到（了解内容）

drop table if exists dept_bak;                 //删除表
create table dept_bak as select * from dept;   //创建表

mysql> select * from dept_bak;
+--------+------------+----------+
| DEPTNO | DNAME      | LOC      |
+--------+------------+----------+
|     10 | ACCOUNTING | NEW YORK |
|     20 | RESEARCH   | DALLAS   |
|     30 | SALES      | CHICAGO  |
|     40 | OPERATIONS | BOSTON   |
+--------+------------+----------+
4 rows in set (0.00 sec)

mysql> insert into dept_bak select * from dept;
Query OK, 4 rows affected (0.01 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql> select * from dept_bak;
+--------+------------+----------+
| DEPTNO | DNAME      | LOC      |
+--------+------------+----------+
|     10 | ACCOUNTING | NEW YORK |
|     20 | RESEARCH   | DALLAS   |
|     30 | SALES      | CHICAGO  |
|     40 | OPERATIONS | BOSTON   |
|     10 | ACCOUNTING | NEW YORK |
|     20 | RESEARCH   | DALLAS   |
|     30 | SALES      | CHICAGO  |
|     40 | OPERATIONS | BOSTON   |
+--------+------------+----------+
8 rows in set (0.00 sec)

4.2 DML改：update

语法格式：update 表名 set 字段名1=值1,字段名2=值2,字段名3=值3… where 条件;

注意：没有条件限制会导致所有数据全部更新。

mysql> update t_user set name = 'jack',birth = '2000-10-11' where id = 3;  //修改id=3的数据
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from t_user;
+------+----------+------------+---------------------+
| id   | name     | birth      | create_time         |
+------+----------+------------+---------------------+
|    1 | zhangsan | 1990-10-01 | 2021-09-14 15:49:50 |
|    1 | zhangsan | 1990-10-01 | 2021-09-14 15:49:50 |
|    3 | jack     | 2000-10-11 | 2021-11-25 15:50:02 |
+------+----------+------------+---------------------+

mysql> update t_user set create_time = now() where id =1;   //修改id=1的数据
Query OK, 2 rows affected (0.00 sec)
Rows matched: 2  Changed: 2  Warnings: 0

mysql> select * from t_user;
+------+----------+------------+---------------------+
| id   | name     | birth      | create_time         |
+------+----------+------------+---------------------+
|    1 | zhangsan | 1990-10-01 | 2021-11-25 15:54:20 |
|    1 | zhangsan | 1990-10-01 | 2021-11-25 15:54:20 |
|    3 | jack     | 2000-10-11 | 2021-11-25 15:50:02 |
+------+----------+------------+---------------------+
3 rows in set (0.00 sec)

4.3 DML删：delete

语法格式： delete from 表名 where 条件;

注意：没有条件，整张表的数据会全部删除！

mysql> delete from t_user where id = 3;   //删除id=3的数据
Query OK, 1 row affected (0.01 sec)

mysql> select * from t_user;
+------+----------+------------+---------------------+
| id   | name     | birth      | create_time         |
+------+----------+------------+---------------------+
|    1 | zhangsan | 1990-10-01 | 2021-11-25 15:54:20 |
|    1 | zhangsan | 1990-10-01 | 2021-11-25 15:54:20 |
+------+----------+------------+---------------------+
2 rows in set (0.00 sec)

mysql> insert into t_user(id) values(2);   //插
Query OK, 1 row affected (0.01 sec)

mysql> select * from t_user;
+------+----------+------------+---------------------+
| id   | name     | birth      | create_time         |
+------+----------+------------+---------------------+
|    1 | zhangsan | 1990-10-01 | 2021-11-25 15:54:20 |
|    1 | zhangsan | 1990-10-01 | 2021-11-25 15:54:20 |
|    2 | NULL     | NULL       | NULL                |
+------+----------+------------+---------------------+
3 rows in set (0.00 sec)

mysql> delete from t_user;             //删全表
Query OK, 3 rows affected (0.01 sec)

mysql> select * from t_user;
Empty set (0.00 sec)

上一篇：第3章数据定义语言DDL

提问和评论都可以，用心的回复会被更多人看到评论

发布评论

相关文章

官方博客	全部文章	热门标签	班级博客
了解我们	网站地图	意见反馈

鸿蒙开发者社区	51CTO学堂
51CTO	软考资讯