mysql update replace 正则

MYSQL中常用的SQL语句

一、增删改查

1.新增

指定字段插入：

INSERT INTO <表名> (<字段1>, <字段2>, <字段3>) VALUES (<值1>, <值2>, <值3>);

不指定字段插入：

INSERT INTO <表名> VALUES (<值1>, <值2>, <值3>);

一次插入多条数据：

INSERT INTO <表名> VALUES (<值1>, <值2>, <值3>), (<值4>, <值5>, <值6>);
INSERT INTO <表名> (<字段1>, <字段2>, <字段3>) VALUES (<值1>, <值2>, <值3>), (<值4>, <值5>, <值6>);

将查询结果插入新表：

INSERT INTO <表名1> SELECT * FROM <表名2>;

将查询部分字段插入新表：

INSERT INTO <表名1> (<字段1>, <字段2>) SELECT <字段3>,<字段4> FROM <表名2>;

复制表：

CREATE TABLE <表名1> SELECT * FROM <表名2>;

2.删除

条件删除：

DELETE FROM <表名> WHERE <字段> = 3;

全部删除（一行一行删）：

DELETE FROM <表名>;

快删：

DROP TABLE IF EXISTS <表名>;

3.修改

条件修改：

UPDATE <表名> SET <字段> = <新的值> WHERE <字段> = 3;

全部修改：

UPDATE <表名> SET <字段> = <新的值>;

修改多个字段：

UPDATE <表名> SET <字段1> = <值1>, <字段2> = <值2>;

使一列的值与另一列的值相等：

UPDATE <表名> SET <字段1> = <字段2>;

4.查询

全量查询：

SELECT * FROM <表名>;

去重查询某列：

SELECT DISTINCT <字段1>, <字段2> FROM <表名>;

比较运算：

SELECT * FROM <表名> WHERE id >= 3;

IN：

SELECT * FROM <表名> WHERE id IN (1, 2, 3);

BETWEEN AND：

SELECT * FROM <表名> WHERE id BETWEEN 1 AND 3;

NOT NULL：

SELECT * FROM <表名> WHERE id IS NOT NULL;

AND / OR：

SELECT * FROM <表名> WHERE id = 3 AND name = 'ming';

模糊查询（%为通配符，_为匹配任意一个字符，[charlist]为字符列中的任意单个字符）：

SELECT * FROM <表名> WHERE <字段> LIKE '%';

正则匹配查询：

SELECT * FROM <表名> WHERE <字段> REGEXP '^[aeiou]|ok$';

起别名：

SELECT <字段> AS <别名> FROM <表名>;

合并两张表并去除重复值：

SELECT <字段1> FROM <表名1> UNION SELECT <字段1> FROM <表名2>;

合并两张表非去重：

SELECT <字段1> FROM <表名1> UNION ALL SELECT <字段1> FROM <表名2>;

分组：

SELECT <字段1>, <字段2>, COUNT(<字段1>), COUNT(<字段2>) FROM <表名> GROUP BY <字段1>, <字段2>;

分组 + 条件：

SELECT * FROM <表名> GROUP BY <字段> HAVING COUNT(*) > 1;

排序查询，正序ASC，逆序DESC：

SELECT * FROM <表名> ORDER BY <字段> DESC;

限制查询，从索引m开始，限制查n条：

SELECT * FROM <表名> ORDER BY <字段> DESC LIMIT m n;

二、函数

1.聚合函数

统计符合条件的行数：

SELECT COUNT(*) FROM <表名> WHERE <字段> > 3;

统计列中非空行数：

SELECT COUNT(<字段>) FROM <表名>;

统计列中非重复非空行数：

SELECT COUNT(DISTINCT <字段>) FROM <表名>;

统计列中的最大值和最小值：

SELECT MAX(<字段>) FROM <表名>;

统计求和：

SELECT SUM(<字段>) FROM <表名>;

求平均值：

SELECT AVG(<字段>) FROM <表名>;

分组合并：

SELECT <字段1>, GROUP_CONCAT(<字段1>) FROM <表名> GROUP BY <字段1>;

三、连表查询

内连接：

SELECT a.*, b.* FROM <表名1> AS a INNER JOIN <表名2> AS b ON a.id = b.id;

左连接：

SELECT a.*, b.* FROM <表名1> AS a LEFT JOIN <表名2> AS b ON a.id = b.id;

右连接：

SELECT a.*, b.* FROM <表名1> AS a RIGHT JOIN <表名2> AS b ON a.id = b.id;

子查询：

SELECT * FROM <表名> WHERE id IN (SELECT id FROM <表名> GROUP BY id HAVING COUNT(*) > 1);

四、常用场景

1.字符串替换：

UPDATE <表名> SET <字段> = REPLACE(<字段>, str1, str2)

2.根据某一列删除表中重复数据（保留id小的数据）：

DELETE FROM <表名> WHERE id NOT IN (SELECT id FROM (SELECT MIN(id) AS id FROM <表名> GROUP BY <重复列>) AS t);

3.根据某一列删除表中重复数据（保留id大的数据）：

DELETE FROM <表名> WHERE id NOT IN (SELECT id FROM (SELECT MAX(id) AS id FROM <表名> GROUP BY <重复列>) AS t);

4.三个表的连表查询：

SELECT * FROM (<表名1> AS a LEFT JOIN <表名2> AS b ON a.id = b.id) LEFT JOIN <表名3> AS c ON a.id = c.id;

5.排除某个表中的一列，查找其他所有列：

SELECT GROUP_CONCAT(<字段> SEPARATOR ',') FROM information_schema.COLUMNS WHERE table_name = <表名> AND column_name != <字段>;

6.去掉某一列中的括号及内容：

UPDATE <表名> SET <字段> = REPLACE(<字段>, SUBSTRING(<字段>, LOCATE('(', <字段>), LOCATE(')', <字段>)-LOCATE('(', <字段>)+1), '');

7.根据两个表的相同列更新另一个表中的另一列数据：

UPDATE <表名1> AS A, <表名2> AS B SET A.<字段1> = B.<字段2> WHERE A.<字段3> = B.<字段4>;

8.查询数据库中的表名及字段信息：

# 查询当前数据库
SELECT DATABASE();
# 查询当前数据库中的所有表名
SELECT TABLE_NAME FROM information_schema.TABLES WHERE TABLE_SCHEMA = (SELECT DATABASE()) AND TABLE_TYPE='BASE TABLE';
# 查询指定数据库中的所有表名
SELECT TABLE_NAME FROM information_schema.TABLES WHERE TABLE_SCHEMA='<数据库名>' AND TABLE_TYPE='BASE TABLE';
# 查询指定数据库中指定表名的字段信息
SELECT * FROM information_schema.COLUMNS WHERE TABLE_SCHEMA='<数据库名>' and TABLE_NAME='<表名>'