华为云GaussDB考试解答

2.

#建用户和库
gsql -h xxx.xx.xx.x -d postgres -p 8000 -U root -W yourpassword -r
CREATE USER db_dev  SYSADMIN IDENTIFIED BY 'Huawei123!@';
CREATE DATABASE finance WITH OWNER db_dev ENCODING 'UTF8';
\q

#导数据
gsql -h xxx.xx.xx.x -p 8000 -U db_dev -d finance -f /root/create_object.sql

#执行sql
gsql -h xxx.xx.xx.x -d db_dev  -p 8000 -U root -W  'Huawei123!@' -r
SELECT SUM(count)
FROM (SELECT count(*) FROM finance.card_asset
UNION ALL SELECT count(*) FROM finance.client
UNION ALL SELECT count(*) FROM finance.bank_card
UNION ALL SELECT count(*) FROM finance.financial_product
UNION ALL SELECT count(*) FROM finance.financial_asset);
\q

3.好像是一个查询，具体内容忘了，sql是

SELECT n.nspname AS schema_name, c.relname AS table_name, CAST(c.reltuples AS INTEGER) AS table_rows FROM pg_class c JOIN pg_namespace n ON c.relnamespace = n.oid WHERE n.nspname = 'finance' AND c.relkind = 'r' ORDER BY table_rows ASC;

4、5、6、7都是改代码。用下面的内容填到对应函数的。编译、运行之后记得按题目要求查询

conn.prepareStatement("") 里的引号里就行了

4.查余额

SELECT bc.b_number, bc.b_type, ca.card_money, ca.moneytype " +
                 "FROM finance.client cl " +
                 "JOIN finance.bank_card bc ON cl.c_id = bc.b_client_id " +
                 "JOIN finance.card_asset ca ON bc.b_number = ca.card_num " +
                 "WHERE cl.c_id = ?

5. 开卡加存取款信息

INSERT INTO finance.card_asset (card_num, card_money, moneytype) VALUES (?, ?, ?)

6.销卡删存取款信息

DELETE FROM finance.card_asset WHERE card_num = ?

7.存取款

UPDATE finance.card_asset SET card_money = card_money + ? WHERE card_num = ?