Reproduced: db2, oracle, mysql more commonly used commands

2011-05-10  来源:本站原创  分类:Database  人气:101 

First, common knowledge points
1, a database view can visit:
oracle: View tnsname.ora or echo $ ORACLE_SID
mysql: show databases - see the current database: select database ()

2, see the user table, view, table indexes, tables, columns,
select table_name from user_tables;
select view_name from user_views;
select constraint_name, constraint_type from user_constraints where table_name ='';
select column_name from all_tab_columns where table_name ='';
list tables or select tabname from syscat.tables;
select view_name from syscat.views;
describe indexes for table table_name;
select tabname from syscat.columns where tabname ='';
mysql: information_schema
select table_name from information_schema.tables where table_schema = 'USER'; or show tables;
select table_name from information_schema.views where table_schema = 'USER'; or show table status where comment = 'view';
select constraint_name, constraint_type from information_schedma.table_constraints where table_name ='';
select index_name, table_name from information_schema.statistics where table_name ='';
select column_name from information_schedma.columns where table_name =''and table_schema = 'USER';
3, see table space
oracle: select name from v $ tablespace
db2: list tablespaces
4, see table structure
oracle: describe table_name
db2: describe table table_name
mysql: describe table_name
5, take the first n rows of data
oracle: select * from table_name where rownum <n
db2: select * from table_name fetch first n rows only
mysql: select * from table_name limit n
6, load data
oracle: sqlldr username / passwd control = ctr.ctl data = data.txt
db2: load from data.txt of del insert into table_name
mysql: load data local infile 'e: Mysqlmysql.txt' into table test lines terminated by 'rn'
7, the use of sql statement
oracle: @ file.sql
db2: db2-tvf file.sql
8, see the user
oracle: select user_name from all_users;
mysql: select schema_name from schemata;
9, insert date type
oracle: insert into table_name (time_id) values ​​(to_date ('2009-09-01 ',' YYYY-MM-DD '));
db2: insert into table_name (time_id) values ​​('2009-09-01 ');
mysql: insert into table_name (time_id) values ​​('2009-09-01 ');
10, char to int
oracle: select to_number ('300 ') +200 from dual;
db2: select cast (char_id as integer) +200 from table_name;
11, the table data export to text
oracle: spool data.txt -> select * from table_name
db2: db2 "export to" / test.sql "of del select * from test"
12, view the current database
oracle: show parameter db_name
db2: list active databases
mysql: show databases;
13, view database directory
oracle: echo $ ORACLE_HOME
db2: list database derictory
14, view the database information system parameters
oracle: show parameter or v $ parameter
db2: get dbm cfg
15, modify table structure
oracle: alter table table_name add column_name number
db2: alter table table_name alter column column_name set data type varchar (12)
16, Export build sheet statement
oracle: select dbms_metadata.get_ddl ('TABLE', u.table_name) from user_tables u;
db2: db2look-d database_name-e-z schedma [-t table_name]-o outfile.sql
mysql: show create table table_nameG (- see the view: show create view view_name)
17, how to restore the data has been submitted

18, model, OWNER difference model: a set of objects such as tables, views, and serial number.
SQL> create table etl.wgwh (id number);
Table created.
View wgwh belongs OWNER:
SQL> select owner from all_tables where table_name = 'WGWH';
Note: oracle model from a database owned by the user and the user has the same name. In fact, in actual use patterns and user is one thing.
oracle view the current mode and switched mode, very simple, just need to see the user or switch users.
db2 => select tabschema, owner from syscat.tables where tabname = 'WGWH' with ur

-------------------------------------------------- ------------------ -------------------------------- -------------------------------------------------- -
Note: db2 oracle of the OWNER is different from the OWNER, the OWNER under the same oracle can not exist in the same object, but can db2, while increasing the model to distinguish.
View the current mode:
select current schema from sysibm.sysdummy1
select current schema from sysibm.dual
select current sqlid from sysibm.sysdummy1
select current sqlid from sysibm.dual
values ​​current schema
values ​​current sqlid
Switching mode:
set current schema schedma_name
19, show database version
oracle: select * from product_component_version
db2: db2level display the version number of db2
mysql: select version ()
20, see the application's port number:
oracle - em, isqlplus: $ ORACLE_HOME / .. / .. / install / protlist.ini
21, application services:
oracle: start EM - emctl start dbconsole
22, reconfigure the application
oracle: the configuration em - emca-config dbcontrol db
23, view the current time
oracle: select sysdate from dual
mysql: select now ()

24, connect database

oracle: sqlplus user / passwd @ dbname

db2: db2 connect to dbname user user_name using passwd

mysql: use database

  • Reproduced: db2, oracle, mysql more commonly used commands 2011-05-10

    First, common knowledge points 1, a database view can visit: oracle: View tnsname.ora or echo $ ORACLE_SID db2: mysql: show databases - see the current database: select database () 2, see the user table, view, table indexes, tables, columns, oracle:

  • Reproduced: DB2.ORACLE SQL written, the main difference 2011-05-10

    To be honest, ORACLE spoiled the domestic programmers, code ORACLE SQL flooded with properties, few people know what the ANSI standard SQL is like, cause the program to not run out of ORACLE, but fortunately with the hibernate popular, the situation

  • JDBC connection DB2.Oracle.MySQL.PostgreSQL 2010-10-04

    package com.terac.util; import java.sql.Connection; import java.sql.DriverManager; import java.sql.ResultSet; import java.sql.Statement; public class DBTest ( public static void main (String [] args) ( try ( String user = "user"; String password

  • MySQL database commonly used commands 2010-12-27

    1, MySQL frequently used commands create database name; create a database use databasename; select the database drop database name directly delete the database, do not remind show tables; display table describe tablename; a detailed description of th

  • mysql some commonly used commands, statements - gradual improvement in 2011-03-09

    The following assumes that the current user is the ultimate authority 1, modify a table engine type ALTER TABLE tablename ENGINE = MyISAM Note: mysql engine type commonly used with MyISAM, ISAM, HEAP, BerkeleyDB, InnoDB, MERGE MyISAM is the default t

  • [Change] ORACLE's SQLPLUS commonly used commands 2010-08-07

    The SQLPLUS command used ORACLE 1, ORACLE startup and shut down one, single environment in order to activate or deactivate the ORACLE system must first switch to the ORACLE user, as su - oracle

  • [Reprint] oracle of some commonly used commands 2010-10-10

    Original Address: First, start 1. # Su - oracle oracle user and the switch to switch to its environment 2. $ Lsnrctl status View Monitor and the database state 3. $ Lsnrctl start listener starts 4. $ Sqlplus / as s

  • Oracle data into commonly used commands 2010-10-26

    Unlock alter user scott account unlock; Delete users and their respective objects drop user ydjc cascade New User create user ydjc identified by ydjc; GRANT Create USER, Drop USER, Alter USER, Create ANY VIEW, Drop ANY VIEW, EXP_FULL_DATABASE, IMP_FU

  • oracle management of commonly used commands 2010-11-11

    1, view the current number of connections to the database select count (*) from v $ session; When the system suddenly can not log in, use the command to query the database connection has been full, and the corresponding third-party ORM mapping framew

  • oracle in the commonly used commands, table space and table management 2010-12-15

    I, table space management 1.create tablespaces sql> create tablespace tablespace_name datafile 'c: \ oracle \ oradata \ file1.dbf' size 100m, sql> 'c: \ oracle \ oradata \ file2.dbf' size 100m minimum extent 550k [logging / nologging] sql> defaul

  • mysql order commonly used commands 2011-05-30

    # MySQL connect to a remote database (, port "3306" user name "root", password "123" mysql-h 3306-u root-p123 # View mysql user permissions show grants for root @ 'localhost'; # See MySQL database

  • [Reserved] oracle of some commonly used commands 2010-10-10

    Original Address: First, start 1. # Su - oracle oracle user and switch to switch to its environment 2. $ Lsnrctl status check and monitor the database state 3. $ Lsnrctl start start monitoring 4. $ Sqlplus / as sysdb

  • MySQL record commonly used commands 2011-02-23

    Record number of common mysql command memo, continuously updated ... Windows start / stop: net start mysql net stop mysql Linux start / stop: serice mysql start /etc/init.d/mysqld start serice mysql stop /etc/init.d/mysqld stop Connection: mysql -u u

  • oracle that is commonly used commands 2011-03-03

    Chapter: Log Management 1.forcing log switches sql> alter system switch logfile; 2.forcing checkpoints sql> alter system checkpoint; 3.adding online redo log groups sql> alter database add logfile [group 4] sql> ('/ disk3/log4a.rdo', '/ disk4/

  • mysql collate commonly used commands 2011-05-30

    # MySQL remote database connection (, port "3306" user name "root", password "123" mysql-h 3306-u root-p123 # Mysql user permission to view show grants for root @ 'localhost'; # See the MySQL datab

  • Oracle stored procedure commonly used commands 2010-12-27

    Stored procedure to execute the SQL string execute immediate 'select * from xxx'; Output debugging information in the stored procedure dbms_output.put_line ('Hello world.');

  • oracle large aggregate commonly used commands (D) 2010-10-29

    Chapter X: managing privileges 1.system privileges: view => system_privilege_map, dba_sys_privs, session_privs 2.grant system privilege sql> grant create session, create table to managers; sql> grant create session to scott with admin option; wit

  • MYSQL database, a collection of commonly used commands 2010-05-05

    Mysql database is a multi-user, multi-threaded relational database is a client / server architecture of the application. It is the personal users and business users is free. Mysql database has the following advantages: 1. Simultaneous access to an un

  • mysql commonly used commands 1 2011-01-30

    MYSQL list of commonly used commands 1, system management Add mysql-h host-u username-p Connect MYSQL (in the mysql / bin) exit MYSQL command to exit mysqladmin-u username-p password new password the old password to change the password (in the mysql

  • Oracle commonly used commands and functions 2010-10-09

    Oracle commonly used commands and functions to be finishing for later use, easy access. Commonly used commands: Connect sqlplus Command line, run cmd enter, such as the landing sys mode, the command is as follows: (1). Sqlplus "sys/zhb126 as sysdba&q