4.MySQL数据操作语句一览表

1.新增记录

简述语句
新增记录(按列顺序)INSERT [INTO] 表名 VALUES (值1, 值2, ...);
新增记录(按列名称)INSERT [INTO] 表名 (列名1, 列名2, ...) VALUES (值1, 值2, ...);
复制已存在表1数据到已存在表2(全部列)INSERT [INTO] 已存在表2 SELECT * FROM 已存在表1;
复制已存在表1数据到已存在表2(指定列)INSERT [INTO] 已存在表2 (列名1, 列名2, …) SELECT 列名1, 列名2, … FROM 已存在表1;

2.更改记录

简述语句
更改记录UPDATE 表名 SET 列名 = 新值 WHERE 条件;

3.删除记录

简述语句
删除记录(所有列)DELETE FROM 表名;
删除记录(指定列)DELETE FROM 表名 WHERE 条件;

4.查询记录

4.1常规查询

简述语句
使用SELECT作为计算器SELECT 表达式;
查询所有列的记录SELECT * FROM 表名;
查询指定列的记录SELECT 列名1, 列名2 FROM 表名;

4.2条件查询

查询语句
条件查询SELECT 列名 FROM 表名 WHERE 条件;
条件查询(AND|OR)SELECT 列名 FROM 表名 WHERE 条件1 AND|OR 条件2;
条件查询(IN)SELECT 列名 FROM 表名 WHERE 列名 IN (值1,值2,..);
条件查询(BETWEEN..AND)SELECT 列名 FROM 表名 WHERE 列名 BETWEEN 值1 AND 值2;
条件查询(NOT BETWEEN..AND)SELECT 列名 FROM 表名 WHERE 列名 NOT BETWEEN 值1 AND 值2;
条件查询(IS NULL)SELECT 列名 FROM 表名 WHERE 列名 IS NULL;
条件查询(IS NOT NULL)SELECT 列名 FROM 表名 WHERE 列名 IS NOT NULL;
带LIKE模式匹配的查询语句
条件查询(LIKE)SELECT 列名 FROM 表名 WHERE 列名 LIKE pattern;
条件查询(NOT LIKE)SELECT 列名 FROM 表名 WHERE 列名 NOT LIKE pattern;
Pattern描述
下画线(_只能一个字符
百分号(%任意数量的字符(包括0个)
//LIKE模式匹配示例

//查找以 b 开头的名字
mysql> SELECT * FROM pet WHERE name LIKE 'b%';

//查询以 fy 结尾的名字
mysql> SELECT * FROM pet WHERE name LIKE '%fy';

//查询包含 w 的名字
mysql> SELECT * FROM pet WHERE name LIKE '%w%';

//查询恰好包含五个字符的名字
mysql> SELECT * FROM pet WHERE name LIKE '_____';

4.3去重查询

SELECT DISTINCT 列名 FROM 表名;

4.4排序查询

//ASC升序,DESC降序
SELECT 列名 FROM 表名 ORDER BY 列名 [ASC|DESC];

4.5限制查询

LIMIT:返回的最大行数,OFFSET:排除的行数,总行数=LIMIT+OFFSET。

//位置偏移为0的限制查询
SELECT 列名 FROM 表名 LIMIT row_count;
//带位置偏移的限制查询
SELECT 列名 FROM 表名 LIMIT offset,row_count;
//带位置偏移的限制查询的另一种语法
SELECT 列名 FROM 表名 LIMIT row_count OFFSET offset;

4.6分组查询

//GROUP BY分组查询,WITH ROLLUP计算所有查询记录的总和
SELECT 列名1,函数(列名2) FROM 表名 GROUP BY 列名1[WITH ROLLUP];
//带HAVING条件的GROUP BY分组查询
SELECT 列名1,函数(列名2) FROM 表名 GROUP BY 列名1 HAVING 函数(列名2)<2000;

4.7连表查询

ON用于指定如何连接表的条件,而WHERE限制要包含在结果集中的行。

//指定两个表中必须存在的列列表
a LEFT JOIN b USING (c1, c2, c3)
简述语句
JOINSELECT 表名1.列名1, 表名1.列名2, 表名2.列名1 FROM 表名1 JOIN (表名2, 表名3) ON (表名1.列名1 = 表名2.列名1 AND 表名1.列2=表名3.列2) WHERE 条件;
INNER JOIN同JOIN
CROSS JOIN同JOIN
STRAIGHT_JOINSTRAIGHT_JOINJOIN类似,不同的是总是先读取左表,再读取右表。这可以用于连接优化器以次优顺序处理表的情况(少数情况)。
LEFT [OUTER] JOINSELECT 表名1.列名1, 表名1.列名2, 表名2.列名1 FROM 表名1 LEFT JOIN (表名2, 表名3) ON (表名1.列名1 = 表名2.列名1 AND 表名1.列2=表名3.列2) WHERE 条件;
RIGHT [OUTER] JOINSELECT 表名1.列名1, 表名1.列名2, 表名2.列名1 FROM 表名1 RIGHT JOIN (表名2, 表名3) ON (表名1.列名1 = 表名2.列名1 AND 表名1.列2=表名3.列2) WHERE 条件;
NATURAL [LEFT] JOINNATURAL JOIN等同于带USING子句的INNER JOIN,NATURAL LEFT JOIN等同于带USING子句的LEFT JOIN,即两个表中都存在的列列表。

4.8合并查询

简述语句
UNIONSELECT 列名 FROM 表名1 UNION SELECT 列名 FROM 表名2;
UNION ALLSELECT 列名 FROM 表名1 UNION ALL SELECT 列名 FROM 表名2;

4.9行级锁

SELECT * FROM t1, t2 FOR SHARE OF t1 FOR UPDATE OF t2;
[FOR {UPDATE | SHARE}
        [OF tbl_name [, tbl_name] ...]
        [NOWAIT | SKIP LOCKED]
      | LOCK IN SHARE MODE]

如果FOR UPDATE与使用页锁或行锁的存储引擎一起使用,查询检查的行将被写锁定,且不能读取或查询,直到当前事务结束。

FOR SHARELOCK IN SHARE MODE设置共享锁,允许其他事务读取检查的行但不能更新或删除它们。FOR SHARELOCK IN SHARE MODE是等价的。FOR SHARELOCK IN SHARE MODE的替代品 ,但LOCK IN SHARE MODE用于向后兼容。但是,FOR SHARE和 FOR UPDATE一样,支持 NOWAITSKIP LOCKED和 OF tbl_name

NOWAIT导致FOR UPDATEFOR SHARE查询立即执行,如果由于另一个事务持有的锁而无法获得行锁,则返回错误。

SKIP LOCKED导致FOR UPDATEFOR SHARE查询立即执行,不包括结果集中被另一个事务锁定的行。

4.10备份

//将查询结果存储到变量中
SELECT ... INTO @变量名;
//将查询结果写入文件中
SELECT ... INTO OUTFILE '文件路径';
//将单行写入文件而不进行任何格式化
SELECT ... INTO DUMPFILE '文件路径';

5.子查询

子查询是指嵌套在另一个语句(SELECT、UPDATE、DELETE语句)内的SELECT语句,且可以进行多层嵌套。子查询必须始终出现在括号内。

SELECT * FROM t1 WHERE column1 = (SELECT column1 FROM t2);

SELECT * FROM t1是外部查询(outer query)或外部语句(outer statement),SELECT column1 FROM t2是子查询。

简述语句
行子查询SELECT * FROM t1 WHERE (col1,col2) = (SELECT col3, col4 FROM t2 WHERE id = 10);
SELECT * FROM t1 WHERE ROW(col1,col2) = (SELECT col3, col4 FROM t2 WHERE id = 10);
ANY子查询SELECT s1 FROM t1 WHERE s1 > ANY (SELECT s1 FROM t2);
SOME子查询SELECT s1 FROM t1 WHERE s1 > SOME (SELECT s1 FROM t2);
IN子查询(= ANY的别名)SELECT s1 FROM t1 WHERE s1 IN (SELECT s1 FROM t2);
NOT IN子查询(<> ALL的别名)SELECT s1 FROM t1 WHERE s1 NOT IN (SELECT s1 FROM t2);
ALL子查询SELECT s1 FROM t1 WHERE s1 > ALL (SELECT s1 FROM t2);
EXISTS子查询SELECT column1 FROM t1 WHERE EXISTS (SELECT * FROM t2);
NOT EXISTS子查询SELECT column1 FROM t1 WHERE NOT EXISTS (SELECT * FROM t2);
相关子查询SELECT * FROM t1 WHERE column1 = ANY (SELECT column1 FROM t2 WHERE t2.column2 = t1.column2);

6.CALL语句

CALL 存储过程名(参数);

7.REPLACE语句

MySQL通过检测PRIMARY KEYUNIQUE索引来判断是否是重复项。

如果新主键的值在表中不存在相等的老主键的值,则 REPLACE 等同于 INSERT。

如果新主键的值与表中的老主键的值相等,此时引发重复项,则 REPLACE 等同于 DELETE + INSERT,首先会先删除老记录,然后插入新记录(插入前后主键值不变)。

REPLACE INTO 表名 VALUES (值1, 值2, 值3);
REPLACE INTO 表名(列1, 列2, 列3) VALUES (值1, 值2, 值3);

8.TABLE语句

TABLE [临时]表名 [ORDER BY 列名] [LIMIT 数字 [OFFSET 数字]];

TABLE语句在某些方面的行为类似于SELECT语句。 

TABLE 表名;

//等同于
SELECT * FROM 表名;

TABLE语句区别于SELECT语句的两个关键方面:

  • TABLE始终显示表的所有列。
  • TABLE不允许对行进行任何任意过滤,即TABLE 不支持任何WHERE子句。

9.VALUES语句

VALUES返回一组一个或多个行作为表。换句话说,它是一个表值构造函数,也可以作为一个独立的 SQL 语句。

ROW()内的值列表的值数量必须相同。

VALUES ROW(值1, 值2), ROW(值3, 值4) [ORDER BY column_designator] [LIMIT 数字];
mysql> VALUES ROW(1,-2,3), ROW(5,7,9), ROW(4,6,8);
+----------+----------+----------+
| column_0 | column_1 | column_2 |
+----------+----------+----------+
|        1 |       -2 |        3 |
|        5 |        7 |        9 |
|        4 |        6 |        8 |
+----------+----------+----------+
3 rows in set (0.00 sec)

10.别名

简述语句
表别名表名  AS 表别名;
列别名列名 AS 列别名;(注意:当AS左侧的列名不存在的时候,AS会使用右侧的列别名新增列名,使用左侧的列名赋予默认值)
记录别名VALUES (列名1新值, 列名2新值) AS 记录别名;
函数别名COUNT(*) AS 别名;

原创文章,作者:huoxiaoqiang,如若转载,请注明出处:https://www.huoxiaoqiang.com/sql/mysql/17172.html

(0)
huoxiaoqiang的头像huoxiaoqiang
上一篇 2022年9月3日
下一篇 2022年10月5日

相关推荐

  • 3.MySQL数据定义语句一览表

    1.用户 简述 语句 创建新角色 CREATE ROLE 角色名; 删除角色 DROP ROLE 角色名; 通过指定其授予的哪些角色处于活动状态,修改当前用户在当前会话中的有效权限。 SET ROLE 角色名; 将当前会话中的活动角色设置为当前用户默认角色。 SET ROLE DEFAULT; 定义在用户会话中默认激活…

    MySQL教程 2022年9月3日
    03900
  • 2.MySQL常用命令一览表

    1.mysql_secure_installation mysql_secure_installation 用于在生产环境中提高 MySQL Server 安装的安全性,按照提示操作。 2.mysqld mysqld 用于启动 MySQL Server。 mysqld选项 描述 mysqld –help | -? 查…

    MySQL教程 2022年9月2日
    05950
  • 1.MySQL数据类型一览表

    1.Integer(整数精确值) INT 的同义词为 INTEGER 。 类型 存储(字节) 范围 TINYINT[UNSIGNED] 1 有符号 -128(-27) ~ 127(27-1),无符号 0 ~ 255(28-1)。 SMALLINT[UNSIGNED] 2 有符号 -32,768(-215) ~ 32,7…

    MySQL教程 2022年9月1日
    08420

发表回复

登录后才能评论