Derby 基本命令zz

Derby 基本命令zz

0. 命令行 登录/退出

登录 java org.apache.derby.tools.ij

..\dirs>java org.apache.derby.tools.ij
ij 版本 10.3
ij>

退出 exit;

ij> exit;

1. 创建新数据库

connect 'jdbc:derby:mydb;create=true';

ij> connect 'jdbc:derby:mydb;create=true';
ij>

在当前目录下建立数据库目录 mydb;

<DIR>log
<DIR>seg0
<DIR>tmp
service.properties
b.lck

如果已经寻在数据库 mydb,则提示：

警告 01J01：未创建数据库"mydb"，而是建立到现有数据库的连接。

ij> connect 'jdbc:derby:mydb;create=true';
警告 01J01：未创建数据库"mydb"，而是建立到现有数据库的连接。
ij>

2. 创建新表/删除表

create table mytable(number varchar(4), name varchar(10), age int, address varchar(40));

ij> create table mytable(number varchar(4), name varchar(10), age int, address varchar(40));
已插入／更新／删除 0 行
ij>

//删除整个表

drop table mytable;

ij> drop table mytable;
已插入／更新／删除 0 行
ij>

3. 表中添加数据：

insert into mytable values('1001', 'one', 20, 'street 1');
insert into mytable values('1002', 'two', 22, 'street 2');

ij> insert into mytable values('1001', 'one', 20, 'street 1');
已插入／更新／删除 1 行
ij> insert into mytable values('1002', 'two', 22, 'street 2');
已插入／更新／删除 1 行
ij> select * from mytable;
NUM&|NAME |AGE |ADDRESS
--------------------------------------------------------------------
1001|one |20 |street 1
1002|two |22 |street 2

已选择 2 行

4. 查看表

show tables;

ij> show tables;
TABLE_SCHEM |TABLE_NAME |REMARKS
------------------------------------------------------------------------
SYS |SYSALIASES |
SYS |SYSCHECKS |
SYS |SYSCOLPERMS |
SYS |SYSCOLUMNS |
SYS |SYSCONGLOMERATES |
SYS |SYSCONSTRAINTS |
SYS |SYSDEPENDS |
SYS |SYSFILES |
SYS |SYSFOREIGNKEYS |
SYS |SYSKEYS |
SYS |SYSROUTINEPERMS |
SYS |SYSSCHEMAS |
SYS |SYSSTATEMENTS |
SYS |SYSSTATISTICS |
SYS |SYSTABLEPERMS |
SYS |SYSTABLES |
SYS |SYSTRIGGERS |
SYS |SYSVIEWS |
SYSIBM |SYSDUMMY1 |
APP |MYTABLE |

最后一个是我们自己创建的表 MYTABLE

5. 查看表的结构

describe mytable;

ij> describe mytable;
COLUMN_NAME |TYPE_NAME|DEC&|NUM&|COLUM&|COLUMN_DEF|CHAR_OCTE&|IS_NULL&
------------------------------------------------------------------------------
NUMBER |VARCHAR |NULL|NULL|4 |NULL |8 |YES
NAME |VARCHAR |NULL|NULL|10 |NULL |20 |YES
AGE |INTEGER |0 |10 |10 |NULL |NULL |YES
ADDRESS |VARCHAR |NULL|NULL|40 |NULL |80 |YES

已选择 4 行
ij>

6. 表中添加数据

insert into mytable values('1001', 'one', 20, 'street 1');
insert into mytable values('1002', 'two', 22, 'street 2');

ij> insert into mytable values('1001', 'one', 20, 'street 1');
已插入／更新／删除 1 行
ij> insert into mytable values('1002', 'two', 22, 'street 2');
已插入／更新／删除 1 行
ij>

7. 查询数据

select * from mytable;
select number, name from mytable;

ij> select * from mytable;
NUM&|NAME |AGE |ADDRESS
--------------------------------------------------------------------
1001|one |20 |street 1
1002|two |22 |street 2

已选择 2 行

ij> select number, name from mytable;
NUM&|NAME
---------------
1001|one
1002|two

已选择 2 行

8. 更新数据

update mytable set name = 'one_one' where number = '1001';

ij> update mytable set name = 'one_one' where number = '1001';
已插入／更新／删除 1 行

ij> select * from mytable;
NUM&|NAME |AGE |ADDRESS
--------------------------------------------------------------------
1001|one_one |20 |street 1
1002|two |22 |street 2

已选择 2 行
ij>

9. 删除表中数据，保留表格式

delete from mytable where name = 'two';
delete from mytable;

ij> delete from mytable where name = 'two';
已插入／更新／删除 1 行

ij> select * from mytable;
NUM&|NAME |AGE |ADDRESS
--------------------------------------------------------------------
1001|one_one |20 |street 1

已选择 1 行

ij> delete from mytable;
已插入／更新／删除 1 行
ij> select * from mytable;
NUM&|NAME |AGE |ADDRESS
--------------------------------------------------------------------

已选择 0 行
ij> describe mytable;
COLUMN_NAME |TYPE_NAME|DEC&|NUM&|COLUM&|COLUMN_DEF|CHAR_OCTE&|IS_NULL&
------------------------------------------------------------------------------
NUMBER |VARCHAR |NULL|NULL|4 |NULL |8 |YES
NAME |VARCHAR |NULL|NULL|10 |NULL |20 |YES
AGE |INTEGER |0 |10 |10 |NULL |NULL |YES
ADDRESS |VARCHAR |NULL|NULL|40 |NULL |80 |YES

已选择 4 行
ij>