openGauss每日一练第2天 | 表的更删

root@modb:~# su - omm
omm@modb:~$ gsql -r
gsql ((openGauss 2.0.0 build 78689da9) compiled at 2021-03-31 21:03:52 commit 0 last mr )
Non-SSL connection (SSL connection is recommended when requiring high-security)
Type "help" for help.

omm=# create table customer_t
omm-# (c_customer_sk integer,
omm(# c_customer_id char(5),
omm(# c_first_name char(6),
omm(# c_last_name char(8));
CREATE TABLE
omm=# insert into customer_t values
omm-# ((4321, 2, 'Lily','Carter'),
omm-# 6885, 1, 'Joes', 'Hunter'),
omm-# (9500, 4, 'Lucy', 'Baker');(9527, 3, 'James', 'Cook'),
omm-# (9500, 4, 'Lucy', 'Baker');
INSERT 0 4
ERROR: syntax error at or near "9500"
LINE 1: (9500, 4, 'Lucy', 'Baker');
^
这里;后内容被忽略，前面内容仍然插入成功
omm=# INSERT INTO customer_t VALUES
omm-# (6885, 1, 'Joes', 'Hunter'),
omm-# (4321, 2, 'Lily','Carter'),
omm-# (9527, 3, 'James', 'Cook'),
omm-# (9500, 4, 'Lucy', 'Baker');
INSERT 0 4
omm=# select * from customer_t;
6885 | 1 | Joes | Hunter
4321 | 2 | Lily | Carter
9527 | 3 | James | Cook
9500 | 4 | Lucy | Baker
(8 rows)

omm=# c_customer_sk | c_customer_id | c_first_name | c_last_name
---------------+---------------+--------------+-------------
6885 | 1 | Joes | Hunter
4321 | 2 | Lily | Carter
9527 | 3 | James | Cook
9500 | 4 | Lucy | Baker

omm=# select * from customer_t limit 2;
c_customer_sk | c_customer_id | c_first_name | c_last_name
---------------+---------------+--------------+-------------
6885 | 1 | Joes | Hunter
4321 | 2 | Lily | Carter
(2 rows)

不能使用类似sqlplus中的/来重复执行sql
omm=# /
omm-# select * from customer_t limit 2;
ERROR: syntax error at or near "/"
LINE 1: /
^
omm=# /

omm=# select c_first_name as c_name from customer_t;
c_name
--------
Joes
Lily
James
Lucy
Joes
Lily
James
Lucy
(8 rows)

omm=# update customer_t set c_customer_id = c_customer_id + 100;
UPDATE 8
omm=# select * from customer_t;
c_customer_sk | c_customer_id | c_first_name | c_last_name
---------------+---------------+--------------+-------------
6885 | 101 | Joes | Hunter
4321 | 102 | Lily | Carter
9527 | 103 | James | Cook
9500 | 104 | Lucy | Baker
6885 | 101 | Joes | Hunter
4321 | 102 | Lily | Carter
9527 | 103 | James | Cook
9500 | 104 | Lucy | Baker
(8 rows)

omm=# select * from customer_t;
c_customer_sk | c_customer_id | c_first_name | c_last_name
---------------+---------------+--------------+-------------
6885 | 101 | Joes | Hunter
4321 | 102 | Lily | Carter
9527 | 103 | James | Cook
9500 | 104 | Lucy | Baker
6885 | 101 | Joes | Hunter
4321 | 102 | Lily | Carter
9527 | 103 | James | Cook
9500 | 104 | Lucy | Baker
(8 rows)

omm=# update customer_t set c_customer_sk = c_customer_sk * 2 where c_customer_sk < 5000;
UPDATE 2
omm=# select * from customer_t;
c_customer_sk | c_customer_id | c_first_name | c_last_name
---------------+---------------+--------------+-------------
6885 | 101 | Joes | Hunter
9527 | 103 | James | Cook
9500 | 104 | Lucy | Baker
6885 | 101 | Joes | Hunter
9527 | 103 | James | Cook
9500 | 104 | Lucy | Baker
8642 | 102 | Lily | Carter
8642 | 102 | Lily | Carter
(8 rows)

omm=# delete from customer_t where c_first_name = 'Lucy';
DELETE 2
omm=# select * from customer_t;
c_customer_sk | c_customer_id | c_first_name | c_last_name
---------------+---------------+--------------+-------------
6885 | 101 | Joes | Hunter
9527 | 103 | James | Cook
6885 | 101 | Joes | Hunter
9527 | 103 | James | Cook
8642 | 102 | Lily | Carter
8642 | 102 | Lily | Carter
(6 rows)

omm=# delete from customer_t;
DELETE 6
omm=# select * from customer_t;
c_customer_sk | c_customer_id | c_first_name | c_last_name
---------------+---------------+--------------+-------------
(0 rows)

omm=# drop table customer_t;
DROP TABLE

长时间未操作session断开
omm=# create table products(product_id INTEGER,product_name Char(20),category Char(30));
WARNING: Session unused timeout.
FATAL: terminating connection due to administrator command
could not send data to server: Broken pipe
The connection to the server was lost. Attempting reset: Succeeded.
omm=# gsql -r
omm=# create table products(product_id INTEGER,product_name Char(20),category Char(30));
CREATE TABLE
加入注释
omm=# comment on column products.product_id is 'chanpingbianhao';
COMMENT
omm=# insert into products values (1502,'olympus camera','electrncs');
INSERT 0 1
omm'# insert into products values (1601,'lamaze','toys'),(1700,'wait interface','Books'),(1666,'harry potter','toys');
INSERT 0 3
omm=# select * from products limit 1;
product_id | product_name | category
------------+----------------------+--------------------------------
1502 | olympus camera | electrncs
(1 row)

omm=# select * from products limit 3;
1601 | lamaze | toys
1700 | wait interface | Books
(3 rows)

omm=# product_id | product_name | category
------------+----------------------+--------------------------------
1502 | olympus camera | electrncs

omm=# select * from products ;
product_id | product_name | category
------------+----------------------+--------------------------------
1502 | olympus camera | electrncs
1601 | lamaze | toys
1700 | wait interface | Books
1666 | harry potter | toys
(4 rows)

omm=# update products set product_id = product_id - 1000 where product_id > 1600;
UPDATE 3
omm=# select * from products;
product_id | product_name | category
------------+----------------------+--------------------------------
1502 | olympus camera | electrncs
601 | lamaze | toys
700 | wait interface | Books
666 | harry potter | toys
(4 rows)

omm=# delete from products where category='toys';
DELETE 2
omm=# select * from products;
product_id | product_name | category
------------+----------------------+--------------------------------
1502 | olympus camera | electrncs
700 | wait interface | Books
(2 rows)

omm=# delete from products;
DELETE 2
omm=# select * from products;
product_id | product_name | category
------------+--------------+----------
(0 rows)

omm=# drop table products;
DROP TABLE
omm=#