Database

Category: /knowledge /backend
Tags: database backend

Oracle

Modify table columns:

alter table your_schema.your_table_name modify 
(userid varchar2(11));

Add new columns:

alter table
   table_name
add
   (
   column1_name column1_datatype column1_constraint, 
   column2_name column2_datatype column2_constraint,
   column3_name column3_datatype column3_constraint
   );

add/drop primary/foreign keys

by the time creating the table

CREATE TABLE table_name
(column1 datatype null/not null,
column2 datatype null/not null,
...
CONSTRAINT fk_column
  FOREIGN KEY (column1, column2, ... column_n)
  REFERENCES parent_table (column1, column2, ... column_n)
);

modify it later

ALTER TABLE dbobjects.dining_plan
    drop CONSTRAINT dining_plan_pk;

ALTER TABLE dbobjects.dining_plan ADD
  CONSTRAINT dining_plan_pk
  PRIMARY KEY (id)
;

ALTER TABLE dbobjects.dining_plan_options ADD
    CONSTRAINT fk_dining_plan_option
    FOREIGN    KEY (planid)
    REFERENCES dbobjects.dining_plan(id)
;

rename a table name:

alter table
   table_name
rename to
   new_table_name;

rename a column name:

alter table plan_watchlist rename author to name 

modify a column data length:

alter table schema.table_name modify(my_column varchar2(200))

grant permissions:

GRANT SELECT,INSERT,UPDATE,DELETE ON $schema.table_name TO webuser;

create required type:

DROP TYPE my_schema.my_own_type;
CREATE OR REPLACE TYPE my_schema.my_own_type AS TABLE OF NUMBER;

Create function:

CREATE REPLACE FUNCTION my_schema.fn_convertstringtotable (P_STR in varchar2) return my_own_type
AS
    V_str LONG DEFAULT P_STR || ',';
    V_No NUMBER;
    V_Data my_own_type := my_own_type();
    BEGIN
        LOOP
        V_No:=INSTR(V_str, ',');
        EXIT WHEN (NVL(V_No, 0)=0);
        V_Data.EXTEND;
        V_Data(V_Data.COUNT) :=LTRIM(RTRIM(SUBSTR(V_Str, 1, V_No -1)));
        V_Str:=SUBSTR(V_Str, V_No+1);
        END LOOP;
        RETURN V_Data;
    END;
    /

To verify the function

SELECT COUNT(*) FROM MV_DATA
WHERE ID IN(
    select * from table (
        select fn_convertstringtotable(leaf_data_list) as ID 
        from my_data_sample where id='1'
    )
)

check system tables

desc role_tab_privs; 
select * from role_tab_privs; 
select * from all_tables;
select * from all_objects;

Outer join

  • Visual explanation of SQL Joins
  • right join is similar to left join. The only difference is that it returns all rows from the right table, with null values if there are no matches in the left table.
  • example: I have an active form table A, and a holdbox H for drafts, now we need list all submitted forms, excluding the ones in holdbox

     select a.* 
     from ACTVFRM a
     left outer join HOLDBOX h
     on a.seq_id=h.seq_id
     where h.seq_id is null
    

PostgreSQL

Nutshell:

su - postgres  -- to become the postgres user
pg_ctl status  -- to see if postgres is running
createdb test  -- create a database called 'test'
psql test      -- start an sql session in the test database
create table my_first_table (testing varchar);   -- create a table
insert into my_first_table (testing) values ( 'Hello World' );
select * from my_first_table; -- show everything in the table
delete from my_first_table;  -- delete everything in the table
drop table my_first_table;   -- destroy the table
\q             -- quit the sql session
dropdb test    -- destroy the database called 'test'
exit           -- stop being the postgres user
\list -- list all databases
\dt -- list all tables
\du -- list all users

update permissions for a role.

postgres=# alter role webuser with createdb;
postgres=# alter role admin with superuser;

dump a table: Note that pg_dump can only dump one table at a time, need extra scripts for multiple tables.

pg_dump -h localhost -p 5432 -U django -W -t $some_table_name mysite > output.sql

import data:

psql -h localhost -p 5432 -U django -d mysite -f symbol.sql 

create a new db

 sudo -u postgres createdb -E utf8 -O $db_user $db_name

reset sequence

select max(id) from channel_blog_post;
select nextval('channel_blog_post_id_seq');
select setval('channel_blog_post_id_seq',
  (select max(id) from channel_blog_post)+1);

Or maybe reindex {table | database | index } name [force]

JDBC

jdbc:postgresql://192.168.1.7:5432/$db_name

Resources

  • [http://www.postgresql.org/docs/8.1/interactive/index.html 8.1 Documentation]
  • [http://www.postgresql.org/docs/faq FAQ]
  • [http://docs.moodle.org/en/Step-by-step_Install_Guide_for_Ubuntu#Install_Postgresql_.28skip_MySQL.29 Ubuntu setup]

DB2

** Anytime in select, DO NOT forget to add ‘WITH UR’ **

fetch first n rows

select * from instance.tbl_name
where some_clauses
order by some_condition
WITH UR
fetch first 100 rows only;

copy current row to a new record

insert into instance.tbl_name
select 1000 -- new assigned id
  ,column_1
  ,column_2
  ,column_3
  from instance.tbl_name
  where id=$value;

Deal with CLOB data.

insert into instance.tbl_name
(col_1, col_2, col_3)
select 1000,
,CLOB(col_2), CLOB(col_3)
from instance.tbl_name
where id=$value; 

sqlite3

Useful commands

  • .tables – list tables:
  • .schema [table_name] – display structure
  • .mode line column
  • .header off

  • drop table: drop table $tbl_name;
  • create table tbl1 (col_one varchar(10), col_two smallint);
  • insert into tbl1 values (‘hi’, 10);
  • select * from tbl1;

  • backup/restore:

    echo ‘.dump’ | sqlite3 ex1 | gzip -c > ex1.dump.gz zcat ex1.dump.gz | sqlite3 ex2 – dump to postgresql sqlite3 ex1 .dump | psql ex2

Add SQL Driver to Squirrel SQL Client

  • download driver from https://bitbucket.org/xerial/sqlite-jdbc/downloads
  • Open “Drivers” in Squirrel (the bar on the very left)
  • Add Drivers, fill in the following values:

    • Name: Sqlite Driver
    • Example URL: jdbc:sqlite:SQLiteDatabaseFileLocation
    • Website URL: https://bitbucket.org/xerial/sqlite-jdbc/overview
    • Calss Name: org.sqlite.JDBC
  • Add alias: jdbc:sqlite:/opt/db/$mydb

Create Table and Index

Create Table my_table ( traceId TEXT primary key, time_span INTEGER, col_1 NUMERIC, filter_string TEXT, timestamp TEXT);

CRETAE UNIQUE INDEX my_index on my_table(traceId);

GUI clients

讨论

提示

  • 如果看不到讨论部分, 请暂时关掉adblock in Firefox/Chrome
  • 本网站使用Javascript实现评论功能, 此处外链对提高您的网站PR没有帮助. (潜台词: 请不要灌水, 谢谢)