avatar

数据库操作-DML

DML

DML(Data Manipulation Language),是通过对表中数据的插入、删除和修改等操作,实现对数据库的基本操作。

主要用途:对数据表进行数据插入、删除、修改、排序或检索操作。

操纵命令:INSERT、UPDATE、DELETE

INSERT:把数据插入到数据表中。

UPDATE:修改数据记录。

DELETE:删除数据库中不必再继续保留的记录。

1、插入数据

形式一

1
2
3
4
5
INSERT INTO TABLE_NAME (字段名1,字段名2,.....) VALUES(数值1,数值2,....);

案例:
INSERT INTO user(user_id,user_name,age,remove,tel,sex)
VALUES(001,"A2Data",22,0,18511990011,0);

形式二

1
2
3
4
INSERT INTO TABLE_NAME SET 字段名1 = 数值1, 字段名2 = 数值2,....;

案例:
INSERT INTO user SET user_id=001,user_name="A2Data",age=22,remove=0,tel=18511990011,sex=0;

2、修改数据

1
2
3
4
UPDATE TABLE_NAME SET 字段名1 = 新数值1,字段名2 = 新数值2;

案例:
UPDATE USER SET age = 25 , sex = 1;

3、解决重复记录的更新

使用on duplicate key update可以解决插入或更改重复主键,即当insert内容和原表内容存在重复记录时,执行update操作。

1
INSERT INTO table_name1 SELECT * FROM table_name2 ON DUPLICATE KEY UPDATE column_name = VALUES(column_name);

注意:在使用on duplicate key update时,表中必须要存在至少一个unique key或者primary key。

案例:
现有表v1和v2如下:

image-20200615211933034image-20200615211959140

v1表与v2表同时存在id为005的行,且name字段值不一致。

我们使用on duplicate key update对v1表插入v2内容:

1
insert into v1 select name,tel,remove from v2 on duplicate key update name = values(name);

查看执行后的v1表内容,可以看到id为005的记录,其name字段值已修改为v2的相应记录:

image-20200615212924946

4、删除数据

方法一:DELETE

1
2
3
4
5
6
DELETE FROM TABLE_NAME
[WHERE 子句]
如不加WHERE子句则表中数据全部删除

案例:
DELETE FROM user;

方法二:TRUNCATE

TRUNCATE优势:作为DDL语句,比DELETE运行速度更快,且使用资源更少

1
2
3
4
TRUNCATE TABLE TABLE_NAME

案例:
TRUNCATE TABLE user;

5、数据库查询

5.1、基本查询语句

1
2
3
SELECT column_name FROM table_name
WHERE 条件语句
[LIMIT 限制行数]

WHERE限制条件一览表:

限制条件类别 对应标识 备注
比较 >、<、=、>=、<=、<>
集合 in、not in 无法获取null值
指定范围 between and 使用时需注意其结果包含边界值
空值判断 is null、is not null
and
or
1
2
3
4
5
案例:

SELECT user_id,user_name FROM user
WHERE age > 20 and tel IS NOT NULL
LIMIT 10;
image-20200612100312585

别名

1
2
3
column_name AS 别名

table_name AS 别名

注:WHERE子句中不可使用别名

GROUP BY:用于对查询结果进行数据分组

HAVING:用于对分组后的结果进行统计,其后可以跟聚合函数

1
2
3
4
5
6
案例:
user表记录了用户所在部门user_dept,筛选出部门平均年龄大于25的结果。

SELECT user_dept,AVG(age) as '年龄' FROM `user`
GROUP BY user_dept
HAVING AVG(age)>25;

image-20200612100428397

HAVING语句用于对分组后的结果进行统计

ORDER BY:对查询结果进行排序,默认为ASC升序,DESC为降序

1
2
3
4
案例:

SELECT user_id,user_name FROM `user`
ORDER BY user_name ;
image-20200612100524629

DISTINCT:取消重复的结果

1
2
3
案例:

SELECT DISTINCT user_dept FROM `user`;
image-20200612100558579

5.2、模糊查询

LIKE

1
2
SELECT column_name FROM table_name
WHERE column_name LIKE 模式字符串
通配符 含义
% 匹配任意长度(0或多个)字符串,不能匹配空值
_ 匹配任意单个字符串,_表示1个长度
1
2
3
4
5
案例:
查询user表中所有姓名为A开头的人员信息。

SELECT * FROM user
WHERE user_name like 'A%';
image-20200612100631405

RLIKE、REGEXP

这两个主要用于在MYSQL中进行正则表达式的书写。

1
2
SELECT column_name FROM table_name
WHERE column_name REGEXP/RLIKE 模式字符串;
通配符 含义
. 匹配任意单个字符
* 匹配0个或多个前一个得到的字符
^ 匹配开头,如^s匹配以s或者S开头的字符串。
$ 匹配结尾,如s$匹配以s结尾的字符串。
{n} 匹配前一个字符反复n次。
多个条件间的“或”连接
1
2
3
4
5
6
7
8
9
案例:
查询user表中姓名存在字段bb的人员。
select * from user where user_name regexp 'b{2}';

查询user表中姓名为n结尾的人员。
select * from user where user_name rlike 'n$';

查询user表中姓名存在an字段的人员。
select * from user where user_name rlike 'an';
image-20200615223316834

5.3、存储过程

存储过程基本结构

1
2
3
4
5
6
7
8
构造存储过程:
CREATE PROCEDURE 存储体名称(IN/OUT 参数名 参数类型)
BEGIN
存储内容
END;

调用存储过程:
CALL 存储体名称(参数)

终端运行存储过程

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
案例:
构造存储体u1,筛选所有部门为R&D的员工。

首先将MYSQL默认结束符进行修改:
DELIMITER //

构造存储过程:
CREATE PROCEDURE U1()
BEGIN
SELECT * FROM user WHERE user_dept = 'R&D';
END
//

调用存储体:
CALL u1()//

image-20200612095224202

5.4、多表查询

内连接

1
2
SELECT * FROM TABLE_A INNER JOIN TABLE_B 
ON TABLE_A.COLUMN 比较运算符 TABLE_B.COLUMN;

内连接用于比较并返回满足连接条件的数据行,结果取交集。

inner join示意图:

image-20200616222620841

注意:如果两边的表行数不一致,可能会出现丢失数据。

外连接

1
2
SELECT * FROM TABLE_A LEFT|RIGHT|FULL JOIN TABLE_B
ON TABLE_A.COLUMN 比较运算符 TABLE_B.COLUMN;

外连接的两个表有主从之分,主表全部保存,从表适配保存。

full outer join示意图:

image-20200616223251275

left/right outer join示意图:

image-20200616223435500

6、锁表

1
2
3
4
5
锁表;
LOCK TABLES table_name LOCK_TYPE

解锁:
UNLOCK TABLES;

image-20200616123547201

Lock_type包含read和write等。

锁为read类型的表,可读但不可写;其他会话对该表可读,但写入需要等到解锁后:

image-20200616123953421

锁为write类型的表,可读也可写;但其他会话对该表的读或写需要等到解锁以后:

image-20200616124427459

7、MYSQL慢查询:EXPLAIN

explain语句用于分析MYSQL的查询性能,可以显示MySQL如何使用索引等来处理select语句以及连接表。

1
2
3
4
5
6
7
explain查看查询是否使用索引:

EXPLAIN SELECT * FROM table_name WHERE 查询条件;

案例:
对表Sheet1建立索引i1后,用explain查看任意查询情况:
explain select * from Sheet1 where number = 1601;

explain查询结果type为ref,即使用了索引查询,而非全表遍历查询all;所使用的索引(possible keys/key)为i1:

image-20200616213133759

文章作者: JackFeng
文章链接: https://minesql.github.io/posts/6656ad65.html
版权声明: 本博客所有文章除特别声明外,均采用 CC BY-NC-SA 4.0 许可协议。转载请注明来自 SQL社区
打赏
  • 微信
    微信
  • 支付宝
    支付宝

评论