Start and stop of service

When GBase 8a is installed, GBase.com The server service needs to be started manually. After that, whenever the machine is powered on and restarted, GBase All server services need to be started manually. If the user needs to manually start and stop the GBase service during use, the user who installed GBase 8A should be used for operation. The specific commands are as follows:

A Start command
$ gbase.server start
> Stop command
$ gbase.server stop
A Restart command
$ gbase.server restart

Modify the password of the database root user

The user who installs GBase 8a can log in to GBase 8a.
By default, during the installation of GBase 8a, the system creates the database super account root and can set the initial password for it. If the user sets the root password to null during the installation, the user can also set a security password for the root account after logging in to GBase 8a for the first time.
Examples are as follows: when installing GBase 8a, the user sets the root user password to blank. At this time, when logging in GBase 8a for the first time, you can continue to set the password for the root user.

$ gbase. -uroot
GBase client 8. 5. 1. 2 build 27952. Copyright (c) 2004-2013, GBase. All Rights
Reserved.
gbase> SET PASSWORD FOR root = PASSWORD(,H133%_h,);
Query OK, 0 rows affected
 The command to log out is gbase>At the prompt, type\q. 
gbase> \q
Bye
 modify root of□After the order, log in again GBase 8a. 
$ gbase -uroot
Enter password:
GBase client 8.5.1.2 build 27952. Copyright (c) 2004-2013, GBase. All Rights
Reserved.
gbase >

DDL

database

CREATE DATABASE is to create a database with a given name. Users need permission to create a database before they can use CREATE DATABASE.
If the user does not specify IF NOT EXISTS and the database exists, an error is generated.

Create database
CREATE DATABASE test;
CREATE DATABASE IF NOT EXISTS test;
Delete database
DROP DATABASE test;
DROP DATABASE IF EXISTS test;

surface

You can use the CREATE TABLE command to create a table with a specified name in the current database.
GBase 8a supports two table types, normal table, and temporary table.
Table type characteristics
A normal table is a table that conforms to the SQL standard and is created using CREATE TABLE.
The TEMPORARY table uses the TEMPORARY keyword. The TEMPORARY table is limited to the current connection. When the connection is closed, the TEMPORARY table will be deleted automatically.

Common table

GBase 8a Support the following syntax to create common tables:
CREATE TABLE [IF NOT EXISTS] [database_na^me. ] tbl_na^e
[(create_definition,...)] [table_options];
table_options:
[COMMENT ' comment_value']
To delete a normal table:
DROP TABLE [IF EXISTS] tbl_name;
Create normal table
CREATE TABLE t3(a int);
Delete table
DROP TABLE t3;

cursor

GBase 8a Temporary tables are supported. You can create temporary tables through the following syntax:
CREATE TEMPORARY TABLE [IF NOT EXISTS] [database_name. ] tbl_name
[(create_definition,...)] [table_options];
table_options:
[COMMENT ' comment_value']
Delete temporary table:
DROP [TEMPORARY] TABLE [IF EXISTS] tbl_name;
The life cycle of the temporary table is session level. It is automatically deleted after the current session connection ends.
Temporary tables support all related operations supported by normal tables, including: DDL,DML,SHOW CREATE TABLE,DESC,SELECT and TEMPORARY TABLE And ordinary tables.

view

GBase 8a Disable view INSERT，DELETE，UPDATE Action.
Create view
CREATE VIEW v_t AS SELECT a,b FROM t;
CREATE OR REPLACE VIEW v_t AS SELECT b FROM t;
CREATE OR REPLACE VIEW v_t_1 AS SELECT a FROM t;
change views
ALTER VIEW v_t(a, b) AS SELECT * FROM t;
ALTER VIEW v_t(aa, bb) AS SELECT a,b FROM t;
Delete view
DROP VIEW IF EXISTS v_t, v_t_1;

Stored procedures and functions

Create a stored procedure or function.
The following describes the syntax for creating custom stored procedures GBase 8a In, we provide users with support for stored procedures.
>	stored procedure(Procedure):
CREATE PROCEDURE <proc_name'>([<parameter1'>[,	[,parameter_nll)
[characteristic ...]
The following describes the syntax for creating custom functions GBase 8a In, we provide users with support for custom functions.
> function(Function):
CREATE FUNCTION <func_name~>([<parameterr>[,...][,parameter_n]])
RETURNS type
[characteristic ...]
〈Function definition〉
〈parameter list〉: 
[IN | OUT | INOUT ] param_name type
 Functions do not[IN | OUT | INOUT ]Mark.
type:
Any valid GBASE data type
characteristic:
LANGUAGE SQL
| [NOT] DETERMINISTIC
| { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }
| SQL SECURITY { DEFINER | INVOKER }
| COMMENT 'string'
in the light of CONTAINS SQL，NO SQL，READS SQL DATA，MODIFIES SQL DATA It should be noted that although all keywords can be specified at one time, only the last keyword is valid.
〈process/Function definition〉: 
This syntax parameter is a series of SQL Statement, which contains some data operations to complete certain functional logic.
Modify stored procedures or functions
ALTER {PROCEDURE | FUNCTION} sp_name [characteristic ...]
characteristic:
{ CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }
| SQL SECURITY { DEFINER | INVOKER }
| COMMENT 'string'
Delete stored procedure or function
DROP {PROCEDURE | FUNCTION} [IF EXISTS] sp_name

Hash indices

Using hash index can improve the performance of equivalent accurate query. When creating a hash index, it is necessary to specify the column in which the hash index is created and the table in which the column is located.
After the hash index is created successfully, the location where the data is stored on the data storage medium will generate files related to the index.
There are two types of hash indexes. One is a global index, called a global hash index, which establishes an index based on the entire column in the table; One is a local index, called a local hash index, which establishes an index based on the first DC in the table.
After creating the hash index, the performance of the equivalent query based on the index column will be improved, especially when the amount of data in the table is very large. In the case of small amount of data, the performance improvement effect of the hash index is not obvious.

The data types that support creating hash indexes are shown in the following table:
Data types that can be indexed
|TINYINT|SAMLLINT|INT|BIGINT|FLOAT|DOUBLE
 Data types that can be indexed
|DECIMAL|CHAR|VARCHAR|DATE|DATETIME|TIME
 Create global hash index
CREATE INDEX IDX_T1_A ON T1(A) USING HASH GLOBAL;
Create local hash index
CREATE INDEX IDX_T2_A ON T2(A) USING HASH LOCAL;
Delete index
DROP INDEX IDX_T1_A ON T1;
DROP INDEX IDX_T2_A ON T2;
Instructions for use restrictions:
>Binary type columns are not suitable for hash index; Or the column has a large amount of data, but distinct Hash indexes are also not suitable when there are fewer values.
> When creating an index, only a single column can be specified, and multiple columns cannot be specified to create a federated index.
A Hash indexes with the same name cannot be created on the same table, and only one hash index can be created on the same column of the same table.

DML

The performance of batch INSERT, UPDATE, DELETE and other operations using GBase 8a is higher than that of single operation.

INSERT

GBase 8a Supports inserting data into tables.
INSERT [INTO] [database_name. ]tbl_name [(col_name,...)]
VALUES ({expr | DEFAULT},...),，..)，...；
or
INSERT [INTO] [database_name. ]tbl_name [(col_name,...)]
SELECT ... FROM [database_name. ] tbl_na^e;
Create table t1 and t2
CREATE TABLE t1(id int);
CREATE TABLE t2(id int);
Example: insert data
gbase> INSERT INTO t1 VALUES(1),(2),(3),(4),(5),(6),(2),(3),(1);
Query OK, 9 rows affected
Records:	9 Duplicates:	0 Warnings:	0
gbase> INSERT INTO t2 SELECT * FROM t1;
Query OK, 9 rows affected
Records: 9 Duplicates: 0 Warnings: 0

UPDATE

GBase 8a Supports updating data in tables.
Syntax:
UPDATE [database_name.]tbl_name
SET col_name1 = expr1 [, col_name2 = expr2 ...]
[WHERE where_definition]
Create table t1
CREATE TABLE t1(id int);
surface t1 Insert data into
INSERT INTO t1 VALUES(1),(2),(3),(4),(5),(6),(2),(3),(1);
Example: updating data in a table
gbase> UPDATE t1 SET t0.id = tO.id+1 WHERE t0.id > 1;
Query OK, 7 rows affected

DELETE

GBase 8a Supports deleting data in tables.
Syntax:
DELETE [FROM] [database_name. ]tbl_name [tbl_alias]
[WHERE where_definition]
Create table t1
CREATE TABLE t(a int);
Example 1: Standard delete statement.
gbase> INSERT INTO t VALUES(1),(2),(3),(4),(5);
gbase> DELETE FROM t WHERE a = 2;
Query OK, 1 row affected
 Example 2 :Omit keywords FROM Deletion of.
gbase> INSERT INTO t VALUES(1),(2),(3),(4),(5);
gbase> DELETE t WHERE a = 2;
Query OK, 1 row affected
 Example 3: delete by alias and omit FROM keyword.
gbase> INSERT INTO t VALUES(1),(2),(3),(4),(5);
gbase> DELETE t tt WHERE tt.a=2;
Query OK, 1 row affected

Audit log

The most important function of audit log is to monitor SQL execution performance. When the execution time of some SQL statements is greater than long_ query_ When the time value is set, the audit log will record these SQL statements to facilitate users to analyze, optimize and rewrite these inefficient SQL statements, so as to improve the execution efficiency of SQL statements.

The main contents of audit log records are as follows:
>thread_id:Thread number, same as processlist Medium ID;
>taskid:each sql Task number;
>start_time: Start execution time;
>end_time: End execution time;
>user_host:Logged in users and IP,The display format is: priv_user[user]@hostname[ip]；
>user :user name;
>host_ip:User login IP Address;
>query_time: The time taken to execute the statement;
>rows :Number of rows;
>db :The database targeted by the execution statement;
A	table_list:Involving tables, formats:'<db>' .'<tb>'[,...];
>sql_text:Record execution time is greater than long_query_time Setting value SQL sentence;
A	sql_type:  sql Type, e.g DDL,DML,DQL,OTHERS	；
A	sql_command: sql Command type, such as SELECT,UPDATE，INSERT，LOAD Etc;
>algorithms:Operators involved, such as JOIN,WHERE,GROUP,HAVING Etc;
>status:  sql Execution status, such as SUCCESS,FAILED,KILLED Etc;
>conn_type:User login mode( CAPI,ODBC,JDBC,ADO.NET,STUDIO);
among sql_command The value range of is: INSERT,DELETE,UPDATE,LOAD,CREATE USER,CREATE DB,CREATE TABLE,CREATE VIEW,CREATE INDEX,CREATE PROCEDURE, CREATE FUNCTION,RENAME USER,ALTER DB,ALTER TABLE,ALTER PROCEDURE, ALTER FUNCTION,ALTER EVENT,DROP USER,DROP DB,DROP TABLE,DROP VIEW, DROP INDEX,DROP PROCEDURE,DROP FUNCTION,DROP EVENT,TRUNCATE,GRANT, REVOKE, SELECT and OTHERS. 
algorithms The value range of is: START_WITH,CONNECT_BY,JOIN,WHERE,GROUP, OLAP_GROUP,HAVING,OLAP_FUNC,DISTINCT,ORDER and LIMIT. If one SQL Multiple operators are involved, which are separated by commas.
At present, the audit log viewed by the user each time is the log content on the login node machine.

configuration parameter

You can use the following configuration methods:

>Use configuration audit_log Parameter to open or close the audit log. It needs to be configured as a global variable (0 by default),That is, close the audit log).
SET GLOBAL audit_log = 0;
or
SET GLOBAL audit_log = 1;
>to configure long_query_time Parameter, which determines how long operations will be recorded in the audit log (10 seconds by default).
SET long_query_time = 5;(Current session effective)
or
SET GLOBAL long_query_time = 5 ；(If this parameter is set as a global variable, all new sessions will take effect after this configuration takes effect.)
To configure the audit log output method, it must be set to table form (i.e. set to table，You can use a watch
 Output as audit log).
SET GLOBAL log_output = 'table'；

Storage mode

GBase 8a stores these contents as follows:
The information of the audit log is stored in the system table gbase audit_ Log.

Use constraints

The audit log is used to record all SQL operations. For statistical operations including the number of rows in the result set, only the following four DML operations are involved: SELECT, DELETE, INSERT and UPDATE.
Empty audit_log, TRUNCATE audit is required_ Log statement. Generally, it is recommended to TRUNCATE audit every 2-3 months_ Log table is used to clear old log information to avoid too much data affecting future analysis.

Use example

Example 1: use system tables to view audit logs.
$ gbase -uroot -p
Enter password:
GBase client 8. 5. 1. 2 build 37185. Copyright (c) 2004-2013, GBase. All Rights Reserved.
gbase> SET GLOBAL audit_log = 1;
Query OK, 0 rows affected
gbase> SET long_query_time = 0;
Query OK, 0 rows affected
gbase> SET GLOBAL log_output = 'table';
Query OK, 0 rows affected
gbase> DROP USER tzt;
Query OK, 0 rows affected
gbase> DROP DATABASE test;
Query OK, 1 row affected
gbase> CREATE USER tzt identified by 'tzt';
Query OK, 0 rows affected
gbase> GRANT ALL ON *.* TO tzt@'%';
Query OK, 0 rows affected
gbase> CREATE DATABASE test;
Query OK, 1 row affected
gbase> USE test;
Query OK, 0 rows affected
gbase> CREATE TABLE t1(i int);
Query OK, 0 rows affected
gbase> INSERT INTO t1 VALUES (1),(2);
Query OK, 2 rows affected
Records:	2 Duplicates:	0 Warnings:	0
gbase> SELECT start_time,user_host,query_time,rows,LEFT(sql_text, 30), conn_type
FROM gbase.audit_log;
+	+	+
|	start_time	|	user_host	|
+	+	+

+	+	+-
■+	+-

10 rows in set gbase> INSERT INTO t1 SELECT * FROM t1;
Query OK,	2	rows affected
Records:	2	Duplicates: 0	Warnings: 0
gbase> UPDATE t1 SET i = 3;
Query OK,	4	rows affected
Rows matched: 4 Changed: 4 Warnings: 0
gbase> DELETE FROM t1;
Query OK, 4 rows affected
gbase> SELECT start_time,user_host,query_time,rows, LEFT(sql_text, 30), conn_type
FROM gbase.audit_log;
+	+	
| start_time	| user_host
+-

+-
■+	+-
| query_time	| rows | LEFT(sql_text, 30)
| conn_type |


|	00:00:00. 029832	|	2	|	(1)INSERT INTO t1 SELECT * FRO	| CAPI	|
|	00:00:00. 013553	|	4	|	(1)UPDATE t1 SET i = 3	| CAPI	|
|	00:00:00.009300	|	4	|	(1)DELETE FROM t1	| CAPI	|
+	+――		+_		-+			+
14 rows in set