2023-01-10 LightDB插入数据.md

LightDB-插入数据

• INSERT指令

lightdb@mydb=# \h INSERT
Command:     INSERT
Description: create new rows in a table
Syntax:
[ WITH [ RECURSIVE ] with_query [, ...] ]
INSERT INTO table_name [ AS alias ] [ ( column_name [, ...] ) ]
    [ OVERRIDING { SYSTEM | USER } VALUE ]
    { DEFAULT VALUES | VALUES ( { expression | DEFAULT } [, ...] ) [, ...] | query }
    [ ON CONFLICT [ conflict_target ] conflict_action ]
    [ RETURNING * | output_expression [ [ AS ] output_name ] [, ...] ]

where conflict_target can be one of:

    ( { index_column_name | ( index_expression ) } [ COLLATE collation ] [ opclass ] [, ...] ) [ WHERE index_predicate ]
    ON CONSTRAINT constraint_name

and conflict_action is one of:

    DO NOTHING
    DO UPDATE SET { column_name = { expression | DEFAULT } |
                    ( column_name [, ...] ) = [ ROW ] ( { expression | DEFAULT } [, ...] ) |
                    ( column_name [, ...] ) = ( sub-SELECT )
                  } [, ...]
              [ WHERE condition ]

URL: https://www.postgresql.org/docs/13/sql-insert.html

• 插入数据

#按照顺序插入
lightdb@mydb=# INSERT INTO weather VALUES ('San Francisco', 46, 50, 0.25, '1994-11-27');
INSERT 0 1
#指定字段
lightdb@mydb=# INSERT INTO weather (city, temp_lo, temp_hi, prcp, date) VALUES ('San Francisco', 43, 57, 0.0, '1994-11-29');
INSERT 0 1
#调整字段顺序
lightdb@mydb=# INSERT INTO weather (date, city, temp_hi, temp_lo) VALUES ('1994-11-29', 'Hayward', 54, 37);
INSERT 0 1

#查看插入的数据
lightdb@mydb=# select * from weather;
city      | temp_lo | temp_hi | prcp |    date    
---------------+---------+---------+------+------------
 San Francisco |      46 |      50 | 0.25 | 1994-11-27
 San Francisco |      43 |      57 |    0 | 1994-11-29
 Hayward       |      37 |      54 |      | 1994-11-29
(3 rows)

输入几条指令

lightdb@mydb=# select version();
                                                  version                                                  
-----------------------------------------------------------------------------------------------------------
 LightDB 13.8-22.3 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-44), 64-bit
(1 row)

lightdb@mydb=# select current_date;
 current_date 
--------------
 2023-01-09
(1 row)

lightdb@mydb=# select 2+2;
 ?column? 
----------
        4
(1 row)

ltsql内部指令以""开头，例如

#指令帮助列表
lightdb@mydb=# \h
Available help:
  ABORT                            CLOSE                            CREATE VIEW                      DROP USER MAPPING
  ALTER AGGREGATE                  CLUSTER                          DEALLOCATE                       DROP VIEW
  ALTER COLLATION                  COMMENT                          DECLARE                          END
  ALTER CONVERSION                 COMMIT                           DELETE                           EXECUTE
  ALTER DATABASE                   COMMIT PREPARED                  DISCARD                          EXPLAIN
 .... 

#输入具体想要了解的指令
lightdb@mydb=# \h ALTER DATABASE
Command:     ALTER DATABASE
Description: change a database
Syntax:
ALTER DATABASE name [ [ WITH ] option [ ... ] ]

where option can be:

    ALLOW_CONNECTIONS allowconn
    CONNECTION LIMIT connlimit
    IS_TEMPLATE istemplate

ALTER DATABASE name RENAME TO new_name

ALTER DATABASE name OWNER TO { new_owner | CURRENT_USER | SESSION_USER }

ALTER DATABASE name SET TABLESPACE new_tablespace

ALTER DATABASE name SET configuration_parameter { TO | = } { value | DEFAULT }
ALTER DATABASE name SET configuration_parameter FROM CURRENT
ALTER DATABASE name RESET configuration_parameter
ALTER DATABASE name RESET ALL

URL: https://www.postgresql.org/docs/13/sql-alterdatabase.html

退出ltsql

lightdb@mydb=# \q