db2 入門常用操作

1、   連線資料庫

[db2inst1@zhanglin-db2 ~]$ db2

(c) Copyright IBM Corporation 1993,2007

Command Line Processor for DB2 Client 9.7.4

You can issue database manager commands and SQL statements from the command

prompt. For example:

    db2 => connect to sample

    db2 => bind sample.bnd

For general help, type: ?.

For command help, type: ? command, where command can be

the first few keywords of a database manager command. For example:

 ? CATALOG DATABASE for help on the CATALOG DATABASE command

 ? CATALOG          for help on all of the CATALOG commands. 

To exit db2 interactive mode, type QUIT at the command prompt. Outside

interactive mode, all commands must be prefixed with 'db2'.

To list the current command option settings, type LIST COMMAND OPTIONS.

For more detailed help, refer to the Online Reference Manual.

db2 => connect to mydb

   Database Connection Information 

 Database server        = DB2/LINUXX8664 9.7.4

 SQL authorization ID   = DB2INST1

 Local database alias   = MYDB

 db2 =>

2、建立表空間

db2 => create tablespace zhang

DB20000I  The SQL command completed successfully.

3、顯示錶空間

db2 => list tablespaces 

           Tablespaces for Current Database

 Tablespace ID                        = 0

 Name                                 = SYSCATSPACE

 Type                                 = Database managed space

 Contents                             = All permanent data. Regular table space.

 State                                = 0x0000

   Detailed explanation:

     Normal

 Tablespace ID                        = 1

 Name                                 = TEMPSPACE1

 Type                                 = System managed space

 Contents                             = System Temporary data

 State                                = 0x0000

   Detailed explanation:

     Normal

 Tablespace ID                        = 2

 Name                                 = USERSPACE1

 Type                                 = Database managed space

 Contents                             = All permanent data. Large table space.

 State                                = 0x0000

   Detailed explanation:

     Normal 

 Tablespace ID                        = 3

 Name                                 = SYSTOOLSPACE

 Type                                 = Database managed space

 Contents                             = All permanent data. Large table space.

 State                                = 0x0000

   Detailed explanation:

     Normal 

 Tablespace ID                        = 4

 Name                                 = ZHANG

 Type                                 = Database managed space

 Contents                             = All permanent data. Large table space.

 State                                = 0x0000

   Detailed explanation:

     Normal

4、刪除表空間

db2 => drop tablespaces zhang

DB20000I  The SQL command completed successfully.

db2 =>

5、   建立表

db2 =>  create table test(id int,name varchar(20))

DB20000I  The SQL command completed successfully.

6、插入記錄

db2 => insert into test values(1,'zhanglin')

DB20000I  The SQL command completed successfully.

db2 => insert into test values(2,'db2')

DB20000I  The SQL command completed successfully.

db2 => insert into test values(3,'zhangsan')

DB20000I  The SQL command completed successfully.

7、查詢表記錄

db2 => select * from test

ID          NAME               

----------- --------------------

          1 zhanglin           

          2 db2                

          3 zhangsan             

  3 record(s) selected. 

db2 =>

8、刪除操作

db2 => delete from test where id=3

DB20000I  The SQL command completed successfully.

db2 => delete from test where id=2

DB20000I  The SQL command completed successfully.

db2 => select * from test

 

ID          NAME               

----------- --------------------

          1 zhanglin             

  1 record(s) selected.

9、更新操作

db2 => update test set name='zhang-db2' where id=1

DB20000I  The SQL command completed successfully.

db2 => select * from test 

ID          NAME               

----------- --------------------

          1 zhang-db2          

  1 record(s) selected.

db2 =>

10、刪除表

db2 => drop table test
DB20000I  The SQL command completed successfully.
db2 =>