mysql不常用语句(sql)

原创

大大刺猬

发布于 2026-02-06 17:37:39

1240

文章被收录于专栏：大大刺猬大大刺猬

导读

之前发过一些mysql常用语句; 但有些mysql的语句还不错,虽然不常用,至少我不常用.

DO

功能: 执行表达式,但是没有返回值

语法: 就是do跟上表达式即可

DO expr [, expr] ...

作用: 有时候只是希望执行某些函数, 并不关心它的返回,甚至希望其不要返回.

例子:

(root@127.0.0.1) [db1]> select get_lock('AAA',10);
+--------------------+
| get_lock('AAA',10) |
+--------------------+
|                  1 |
+--------------------+
1 row in set (0.00 sec)

(root@127.0.0.1) [db1]> do RELEASE_LOCK('AAA');
Query OK, 0 rows affected (0.00 sec)

(root@127.0.0.1) [db1]> do SLEEP(5);
Query OK, 0 rows affected (5.01 sec)

TABLE (8.0.19)

没想到吧,这也是个语句

功能: 查询表的所有字段, 同select * from table

语法: 把select * from换成 TABLE, 其它不变(不支持where)

TABLE table_name 
    [ORDER BY column_name] 
    [LIMIT number [OFFSET number]]
    [INTO OUTFILE 'file_name'
        [{FIELDS | COLUMNS}
            [TERMINATED BY 'string']
            [[OPTIONALLY] ENCLOSED BY 'char']
            [ESCAPED BY 'char']
        ]
        [LINES
            [STARTING BY 'string']
            [TERMINATED BY 'string']
        ]
    | INTO DUMPFILE 'file_name'
    | INTO var_name [, var_name] ...]

作用: 偷点懒

例子:

(root@127.0.0.1) [db1]> TABLE db1.t1 limit 1;
+------+
| id   |
+------+
|   11 |
+------+
1 row in set (0.00 sec)

EXCEPT

功能: 第一个查询块不在第二个查询块中的结果集. 和left join where b.k is null差不多,就是第一个查询块独占的.

语法: QUERY_EXP01 EXCEPT QUERY_EXP02

query_expression_body EXCEPT [ALL | DISTINCT] query_expression_body
    [EXCEPT [ALL | DISTINCT] query_expression_body]
    [...]

作用: 偷点懒

例子:

(root@127.0.0.1) [db1]> create table db1.t1(c1 int, c2 int);
Query OK, 0 rows affected (0.01 sec)

(root@127.0.0.1) [db1]> insert into db1.t1 values(1,1),(2,2),(3,3);
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

(root@127.0.0.1) [db1]> create table db1.t2(c1 int, c2 int);
Query OK, 0 rows affected (0.02 sec)

(root@127.0.0.1) [db1]> insert into db1.t2 values(2,2);
Query OK, 1 row affected (0.01 sec)

(root@127.0.0.1) [db1]> TABLE t1 EXCEPT TABLE t2;
+------+------+
| c1   | c2   |
+------+------+
|    1 |    1 |
|    3 |    3 |
+------+------+
2 rows in set (0.00 sec)

INTERSECT

既然有差集,那也就有交集了哦

功能: 两表的交集

语法: QUERY_EXP01 INTERSECT QUERY_EXP02

query_expression_body INTERSECT [ALL | DISTINCT] query_expression_body
    [INTERSECT [ALL | DISTINCT] query_expression_body]
    [...]

例子:

(root@127.0.0.1) [db1]> TABLE t1 INTERSECT TABLE t2;
+------+------+
| c1   | c2   |
+------+------+
|    2 |    2 |
+------+------+
1 row in set (0.00 sec)

HANDLER

功能: HANDLER(憨豆)可以直接通过存储引擎访问数据,这速度就很快了

语法: 打开表,读数据,关闭表

HANDLER tbl_name OPEN [ [AS] alias]

HANDLER tbl_name READ index_name { = | <= | >= | < | > } (value1,value2,...)
    [ WHERE where_condition ] [LIMIT ... ]
HANDLER tbl_name READ index_name { FIRST | NEXT | PREV | LAST }
    [ WHERE where_condition ] [LIMIT ... ]
HANDLER tbl_name READ { FIRST | NEXT }
    [ WHERE where_condition ] [LIMIT ... ]

HANDLER tbl_name CLOSE

例子:

(root@127.0.0.1) [db1]> HANDLER t1 OPEN;
Query OK, 0 rows affected (0.00 sec)

(root@127.0.0.1) [db1]> HANDLER t1 READ NEXT;
+------+------+
| c1   | c2   |
+------+------+
|    1 |    1 |
+------+------+
1 row in set (0.01 sec)

(root@127.0.0.1) [db1]> HANDLER t1 READ NEXT;
+------+------+
| c1   | c2   |
+------+------+
|    2 |    2 |
+------+------+
1 row in set (0.00 sec)

(root@127.0.0.1) [db1]> HANDLER t1 CLOSE;
Query OK, 0 rows affected (0.00 sec)

(root@127.0.0.1) [db1]>

VALUES

功能: 构造一个表,有时候不想create的时候就可以使用这种方式来构建表

语法: VALUES ROW(c1,c2), ROW(c1,c2)

VALUES row_constructor_list [ORDER BY column_designator] [LIMIT number]

row_constructor_list:
  ROW(value_list)[, ROW(value_list)][, ...]
  
value_list:
   value[, value][, ...]

column_designator:
  column_index

例子:

(root@127.0.0.1) [db1]> VALUES ROW(1,2), ROW(3,4) except VALUES ROW(1,2);
+----------+----------+
| column_0 | column_1 |
+----------+----------+
|        3 |        4 |
+----------+----------+
1 row in set (0.00 sec)

RESTART

上一章我们讲了mysql的信号量处理, 里面有关SIGUSR2是表示重启的,而mysql本身就支持重启命令(8.0), 原理也是退出的时候设置退出状态码为16, 然后mysqld_safe之类的根据退出状态码进行启动

逻辑可参考如下

export MYSQLD_RESTART_EXIT=16
while true ; do
  bin/mysqld mysqld options here
  if [ $? -ne $MYSQLD_RESTART_EXIT ]; then
    break
  fi
done

例子:

(root@127.0.0.1) [db1]> show global status like 'uptime';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Uptime        | 3039  |
+---------------+-------+
1 row in set (0.00 sec)

(root@127.0.0.1) [db1]> restart;
Query OK, 0 rows affected (0.01 sec)

(root@127.0.0.1) [db1]> show global status like 'uptime';
ERROR 2013 (HY000): Lost connection to MySQL server during query
No connection. Trying to reconnect...
Connection id:    9
Current database: db1

+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Uptime        | 11    |
+---------------+-------+
1 row in set (0.03 sec)

参考:

https://dev.mysql.com/doc/refman/8.0/en/sql-statements.html

原创声明：本文系作者授权腾讯云开发者社区发表，未经许可，不得转载。

如有侵权，请联系 cloudcommunity@tencent.com 删除。

mysql

原创声明：本文系作者授权腾讯云开发者社区发表，未经许可，不得转载。

如有侵权，请联系 cloudcommunity@tencent.com 删除。

mysql

登录后参与评论

0 条评论

热度