mysql CRUD操作

2022-09-28 13:26:08 浏览数 (2)

创建表的时候尽量加上create_time字段, 为create_time设置默认值CURRENT_TIMESTAMP

CRUD操作

Create(增)

单条插入

代码语言：javascript复制
INSERT INTO 表名(字段1, 字段2..) VALUES(值1, 值2)

INSERT INTO class_1(name) VALUES('name_1')

多条插入

代码语言：javascript复制
INSERT INTO 表名(字段1, 字段2..) VALUES(值1, 值2), (值1, 值2)

Retrieve(查)

获取所有记录

代码语言：javascript复制
SELECT 字段1, 字段2 FROM 表名

SELECT name FROM class_1
SELECT * FROM class_1

条件查询(WHERE, AND , OR)

代码语言：javascript复制
SELECT 字段1, 字段2 FROM 表名 WHERE 表达式

SELECT * FROM class_1 WHERE name="name_1" AND id=8

模糊匹配(LIKE, %)

模糊匹配有性能问题, 表记录如果比较多, 查询速度很慢

代码语言：javascript复制
SELECT 字段1, 字段2 FROM 表名 WHERE 表达式

SELECT * FROM class_1 WHERE name LIKE "%m%"

限制返回条数(LIMIT)

代码语言：javascript复制
查询表达式 LIMIT 数量

过滤重复值(DISTINCT)

对查询的结果进行过滤.

如果指定多个字段, 会对多个字段联合进行过滤

代码语言：javascript复制
SELECT DISTINCT 字段1, 字段2 FROM 表名 [条件语句]

SELECT distinct name, create_time  FROM class_1 WHERE name like '%m%'

排序问题

对查询的结果进行排序

升序(ASC)

数据库默认是升序的

代码语言：javascript复制
查询表达式 ORDER BY 字段 ASC

降序(DESC)

代码语言：javascript复制
查询表达式 ORDER BY 字段 DESC

获取查询结果的条数(COUNT)

代码语言：javascript复制
SELECT COUNT(*) FROM 表名 [条件语句]

Update(更新)

代码语言：javascript复制
UPDATE 表名 SET 字段1=新值, 字段2=新值 WHERE 表达式

UPDATE class_1 SET name='name_0' WHERE id=8

Delete(删除)

代码语言：javascript复制
DELETE FROM 表名 WHERE 表达式

DELETE FROM class_1 WHERE name='name_0'

集合操作

并集UNION

子语句SELECT 必须拥有相同数量的列(字段), 且列的数据类型也相同

代码语言：javascript复制
SELECT name from class_1 WHERE name is not NULL
UNION
SELECT name from class_2 WHERE name is not NULL

交集(JOIN, INNER JOIN)

代码语言：javascript复制
SELECT s1.name FROM
(SELECT name from class_1 WHERE name is not NULL) as s1
JOIN
(SELECT name from class_2 WHERE name is not NULL) as s2
ON s1.name=s2.name

差集(LEFT JOIN, RIGHT JOIN)

A对B的差集(LEFT JOIN)

代码语言：javascript复制
SELECT * FROM // s1对s2的差集, select就可以使用s1.name
(SELECT name from class_1 WHERE name is not NULL) as s1
LEFT JOIN
(SELECT name from class_2 WHERE name is not NULL) as s2
ON s1.name=s2.name
WHERE s2.name is NULL   // 限定s1有, s2没有的记录

B对A的差集(RIGHT JOIN)

代码语言：javascript复制
SELECT * FROM
(SELECT name from class_1 WHERE name is not NULL) as s1
RIGHT JOIN
(SELECT name from class_2 WHERE name is not NULL) as s2
ON s1.name=s2.name
WHERE s1.name is NULL

补集

A与B的补集 = A与B的并集 - A与B的交集 A与B的补集 = A对B的差集 B对A的差集 √

代码语言：javascript复制
SELECT s1.name FROM
(SELECT name from class_1 WHERE name is not NULL) as s1
LEFT JOIN
(SELECT name from class_2 WHERE name is not NULL) as s2
ON s1.name=s2.name
WHERE s2.name is NULL

UNION

SELECT s2.name FROM
(SELECT name from class_1 WHERE name is not NULL) as s1
RIGHT JOIN
(SELECT name from class_2 WHERE name is not NULL) as s2
ON s1.name=s2.name
WHERE s1.name is NULL
join limit time timestamp union

0 人点赞