GreenPlum/PostGreSQL操作

Create a new database: createdb medb
Drop database: dropdb medb
Access database: psql -h gpserver -d gpdatabase -U gpuser -W
Get help: medb=# \h
Quit: medb=# \q
Read command from file: medb=# \i input.sql
To dump a database: pg_dump medb > db.out
To reload the database: psql -d database -f db.out
Dump all database: pg_dumpall > /var/lib/pgsql/backups/dumpall.sql
Restore database: psql -f /var/lib/pgsql/backups/dumpall.sql medb
Show databases: psql -l medb=# \l;
Show users: medb=# select * from "pg_user";
Show tables: medb=# select * from "pg_tables";
Set password: medb=# upadte pg_shadow set passwd = 'new_password' where usename = 'username';
Clean all databases (Should be done via a daily cron): vacuumdb --quiet --all
check column of table medb=# select * from INFORMATION_SCHEMA.COLUMNS where table_name = 'table_name'; medb=# \d table_name;
check function medb=# select * from pg_catalog.pg_proc p where p.proname='function_name'; medb=# \df;
check privilege medb=# select * from INFORMATION_SCHEMA.role_table_grants;

schema relate command

medb=#\dn;
medb=# select np.nspname as "schema", usr.usename as "owner" from pg_namespace as np, pg_user as usr where np.nspowner=usr.usesysid;
medb=# select distinct schemaname from pg_tables where tableowner='userName';
medb=# ALTER SCHEMA name RENAME TO newname
medb=# ALTER SCHEMA name OWNER TO newowner

search path relate command

medb=# show search_path;
medb=# set search_path='new_search_path';

check postgresql server status pg_ctl status -D server_data_folder
start postgresql server pg_ctl start -D server_data_folder

Top 10 Biggest Tables at database

medb=# SELECT table_name, pg_relation_size(table_name) as size FROM information_schema.tables WHERE table_schema NOT IN ('information_schema', 'pg_catalog') ORDER BY size DESC LIMIT 10;

连接GreenPlum/PostGreSQL的JDBC代码

import java.sql.Connection;
import java.sql.DriverManager;
import java.util.Properties;

public class TestConnection {

  public static void connectWithProps() {
    Connection connection;
    Properties props;

    try {
      Class.forName("org.postgresql.Driver");
      props = new Properties();
      props.put("user", "aUser");
      props.put("password", "aPassword");
      connection = DriverManager.getConnection("jdbc:postgresql://aGreenplumServer:5432/aDatabase", props);
      System.out.println("Connection successful");
      connection.close();
    } catch (Exception e) {
      System.out.println("error " + e);
    }    
  }

  public static void connectWithUserInfo() {
    Connection connection;

    try {
      Class.forName("org.postgresql.Driver");
      connection = DriverManager.getConnection("jdbc:postgresql://aGreenplumServer:5432/aDatabase", "aUser", "aPassword");
      System.out.println("Connection successful");
      connection.close();
    } catch (Exception e) {
      System.out.println("error " + e);
    }
  }

  public static void main(String argv[]) {
    connectWithUserInfo();
    connectWithProps();
  }
}

MySQL操作

To get the current database character set

mysql> SHOW VARIABLES LIKE 'character_set_database';   
+------------------------+-------+
| Variable_name          | Value |   
+------------------------+-------+
| character_set_database | utf8  |
+------------------------+-------+
1 row in set (0.00 sec)

To know MySQL Server default Character Set

mysql> SHOW VARIABLES LIKE 'character_set_server';
+----------------------+-------+
| Variable_name        | Value |
+----------------------+-------+
| character_set_server | utf8  |
+----------------------+-------+
1 row in set (0.00 sec)

my.ini default-character-set=utf8

To know storage engine mysql> show table stauts from the_database; my.ini default-storage-engine=INNODB

Teradata操作

Use BTEQWIN to logon Teradata server

Teradata BTEQ 12.00.00.00 for WIN32.
Copyright 1984-2007, NCR Corporation. ALL RIGHTS RESERVED.
Enter your logon or BTEQ command:
logon meserver/metest
.logon meserver/metest
Password: 
******
** Logon successfully completed.
** Teradata Database Release is 13.00.00.14 
** Teradata Database Version is 13.00.00.15 
** Transaction Semantics are BTET.
** Character Set Name is 'UTF8'.
*** Total elapsed time was 2 seconds.
BTEQ -- Enter your DBC/SQL request or BTEQ command:

To run a sql script bteq < script.sql > script.out
To get help info about BTEQ help bteq;
To connect to a database inside bteq .logon TDPID/User ID
To exit the bteq session quit;
To run file (batch mode) in Teradata run file myfile.sql To run operation system command in BTEQ os dir os rm some_file
To switch/access a database database database_name;
To display current session help session;
To list tables in a database help database database_name;
To show a table information help table table_name;
To show a table details show table table_name;
To explain a sql command explain sql_command
To list Teradata version show version

Some useful teradata SQL commands

select database;`
select user;
select date;
select time;

DB2操作

To start a db2 server, use command db2start
To stop a db2 server, use command db2stop
To manage DB2 Administrative Server, use command db2admin
Tools DB2BATCH- Reads SQL statements from either a flat file or standard input, dynamically prepares and describes the statements and returns an answer set: Authorization: sysadmin .and Required Connection -None..eg db2batch -d databasename -f filename -a userid/passwd -r outfile DB2expln - DB2 SQL Explain Tool DB2exfmt - Explain Table Format Tool DB2icrt - Create an instance DB2idrop - Dropan instance DB2ilist - List instances DB2imigr - Migrate instances DB2iupdt - Update instances Db2licm - Installs licenses file for product ; db2licm -a db2entr.lic DB2look - DB2 Statistics Extraction Tool

数据库常用操作笔记

GreenPlum/PostGreSQL操作

连接GreenPlum/PostGreSQL的JDBC代码

MySQL操作

Teradata操作

DB2操作

参考