gaussDB数据库常用操作命令详解

摊手就有偏哨 發表於 2023-6-27 00:00:00

<div id="navCategory"><h5 class="catalogue">目录</h5><ul class="first_class_ul"><li>
GaussDB命令行连接</li><li>
基本操作命令</li><li>
mysql数据迁移至gaussDB</li><li>
切换gaussDb中遇到的问题</li></ul></div><p class="maodian"></p><h2>
GaussDB命令行连接</h2>
<p>
                1.1 ssh连接主机，IP：192.168.28.178，用户名：root，密码：Huawei @123</p>
<p>
                1.2 切换至bin目录，cd /home/gaussdba/app/bin/</p>
<p>
                1.3 切换用户为gaussdba，su gaussdba</p>
<p>
                1.4 连接gaussDb，gsql -d postgres -p 5432</p>
<p class="maodian"></p><h2>
基本操作命令</h2>
<p>
                \l 列出所有数据库</p>
<p>
                \c database_name 切换数据库</p>
<p>
                \d 列出当前数据库下的表</p>
<p>
                \d tablename 列出指定表的所有字段</p>
<p>
                \d+ tablename 查看指定表的基本情况</p>
<p>
                \dn 展示当前数据库下所有schema信息</p>
<p>
                SHOW search_path; 显示当前使用的schema</p>
<p>
                SET search_path TO myschema; 切换当前schema</p>
<p>
                \q 退出登录</p>
<p class="maodian"></p><h2>
mysql数据迁移至gaussDB</h2>
<p>
    1. 导出mysql数据</p>
<div class="jb51code">
<div>
<div class="syntaxhighlightersql" id="highlighter_269957">
<div class="toolbar">
<span>?</span>
</div>
<table border="0" cellpadding="0" cellspacing="0"><tbody><tr>
<td class="gutter">
<div class="line number1 index0 alt2">
1</div>
</td>
<td class="code">
<div class="container">
<div class="line number1 index0 alt2">
<code class="sql plain">show variables </code><code class="sql color1">like</code> <code class="sql string">'%secure%'</code> <code class="sql plain">查询出secure_file_priv地址；</code>
</div>
</div>
</td>
</tr></tbody></table>
</div>
</div>
<div class="codetool" id="codetool">
<div class="code_n">
<textarea></textarea>
</div>
</div>
</div>
<p>
                在secure_file_priv地址下mkdir aaa；</p>
<p>
            修改文件权限chmod -R 777 aaa；</p>
<div class="jb51code">
<div>
<div class="syntaxhighlightersql" id="highlighter_469201">
<div class="toolbar">
<span>?</span>
</div>
<table border="0" cellpadding="0" cellspacing="0"><tbody><tr>
<td class="gutter">
<div class="line number1 index0 alt2">
1</div>
</td>
<td class="code">
<div class="container">
<div class="line number1 index0 alt2">
<code class="sql keyword">select</code> <code class="sql plain">* </code><code class="sql keyword">from</code> <code class="sql plain">i18n_message </code><code class="sql keyword">into</code> <code class="sql plain">outfile </code><code class="sql string">'/tmp/etl/temp.dat'</code> <code class="sql plain">fields terminated </code><code class="sql keyword">by</code> <code class="sql string">''</code><code class="sql plain">';</code>
</div>
</div>
</td>
</tr></tbody></table>
</div>
</div>
<div class="codetool" id="codetool">
<div class="code_n">
<textarea></textarea>
</div>
</div>
</div>
<p>
    2. 下载数据并上传至GaussDb服务器</p>
<p>
3. 导入gaussDb</p>
<div class="jb51code">
<div>
<div class="syntaxhighlightersql" id="highlighter_584528">
<div class="toolbar">
<span>?</span>
</div>
<table border="0" cellpadding="0" cellspacing="0"><tbody><tr>
<td class="gutter">
<div class="line number1 index0 alt2">
1</div>
</td>
<td class="code">
<div class="container">
<div class="line number1 index0 alt2">
<code class="sql plain">COPY I18N_MESSAGE </code><code class="sql keyword">FROM</code> <code class="sql string">'/home/gaussdba/app/bin/temp.dat'</code> <code class="sql keyword">WITH</code> <code class="sql plain">DELIMITER </code><code class="sql string">''</code><code class="sql string">''</code><code class="sql plain">;</code>
</div>
</div>
</td>
</tr></tbody></table>
</div>
</div>
<div class="codetool" id="codetool">
<div class="code_n">
<textarea></textarea>
</div>
</div>
</div>
<p class="maodian"></p><h2>
切换gaussDb中遇到的问题</h2>
<p>
1. 执行出现如下错误：column "TASK.TASK_ID" must appear in the GROUP BY clause or be used in an aggregate function ，原因</p>
<p>
                是：select的字段与Group by中的字段要一致或不一致的字段必须使用聚合函数；</p>
<p>
      2. MySQL中的ifnull函数用nvl函数替换；</p>
<p>
      3. limit 0,10 ---------->limit 10 offset 0；</p>
<p>
      4. find_in_set(operator, '1096,789') ---------->operator ~ concat('(', replace('1096,789', ',', '|'), ')')；</p>
<p>
      5. 不支持uuid()，可以自定义UUID函数，</p>
<div class="jb51code">
<div>
<div class="syntaxhighlightersql" id="highlighter_225592">
<div class="toolbar">
<span>?</span>
</div>
<table border="0" cellpadding="0" cellspacing="0"><tbody><tr>
<td class="gutter">
<div class="line number1 index0 alt2">
1</div>
<div class="line number2 index1 alt1">
2</div>
<div class="line number3 index2 alt2">
3</div>
<div class="line number4 index3 alt1">
4</div>
<div class="line number5 index4 alt2">
5</div>
<div class="line number6 index5 alt1">
6</div>
<div class="line number7 index6 alt2">
7</div>
<div class="line number8 index7 alt1">
8</div>
<div class="line number9 index8 alt2">
9</div>
<div class="line number10 index9 alt1">
10</div>
<div class="line number11 index10 alt2">
11</div>
<div class="line number12 index11 alt1">
12</div>
<div class="line number13 index12 alt2">
13</div>
</td>
<td class="code">
<div class="container">
<div class="line number1 index0 alt2">
<code class="sql keyword">CREATE</code> <code class="sql color1">OR</code> <code class="sql color2">REPLACE</code> <code class="sql keyword">FUNCTION</code> <code class="sql plain">UUID() </code>
</div>
<div class="line number2 index1 alt1">
</div>
<div class="line number3 index2 alt2">
<code class="sql spaces">                </code><code class="sql keyword">RETURNS</code> <code class="sql plain">TEXT </code><code class="sql keyword">AS</code> <code class="sql plain">$$</code>
</div>
<div class="line number4 index3 alt1">
</div>
<div class="line number5 index4 alt2">
<code class="sql spaces">                </code><code class="sql keyword">BEGIN</code>
</div>
<div class="line number6 index5 alt1">
</div>
<div class="line number7 index6 alt2">
<code class="sql spaces">                    </code><code class="sql keyword">RETURN</code> <code class="sql plain">to_number(now()::text,</code><code class="sql string">'99999999999999999999999999999999999999999999999'</code><code class="sql plain">);</code>
</div>
<div class="line number8 index7 alt1">
</div>
<div class="line number9 index8 alt2">
<code class="sql spaces">                </code><code class="sql keyword">END</code><code class="sql plain">;</code>
</div>
<div class="line number10 index9 alt1">
</div>
<div class="line number11 index10 alt2">
<code class="sql spaces">               </code><code class="sql plain">$$ LANGUAGE plpgsql;</code>
</div>
<div class="line number12 index11 alt1">
</div>
<div class="line number13 index12 alt2">
<code class="sql spaces">                </code><code class="sql keyword">ALTER</code> <code class="sql keyword">FUNCTION</code> <code class="sql plain">UUID() OWNER </code><code class="sql keyword">TO</code> <code class="sql plain">GAUSSDBA;</code>
</div>
</div>
</td>
</tr></tbody></table>
</div>
</div>
<div class="codetool" id="codetool">
<div class="code_n">
<textarea></textarea>
</div>
</div>
</div>
<p>
        6. 字符串不能用双引号""括起来，只能用单引号''，例如：SELECT "" as local_path ----------->SELECT '' as local_path</p>
<p>
      7.  INSERT INTO ON DUPLICATE KEY ------------>REPLACE INTO</p>
<p>
        8. 模糊查询，LIKE：区分大小写，ILIKE：不区分大小写，MySQL中查询默认不区分大小写，所以可以用ILIKE替换</p>
<p>
        9. GaussDB中认为null和空字符''是一样的，不支持a=''这种空字符判断，必须写成a is null</p>
<p>
        10. GaussDB中字符串比较时，注意字段类型要一致，例如CHAR是定长的，不足的补空格，和VARCHAR类型比较时就会有问题</p>
<p>
        11. 单引号'转义，用一个单引号转义另一个''，例如xi''an</p>
<p>
到此这篇关于gaussDB数据库常用操作命令的文章就介绍到这了,更多相关gaussDB数据库命令内容请搜索以前的文章或继续浏览下面的相关文章希望大家以后多多支持！</p>
<p>
原文链接：https://blog.csdn.net/adete92906/article/details/101988616</p>

頁: [1]

琼殿技术论坛's Archiver

gaussDB数据库常用操作命令详解