Derby基本命令操作（备忘）
0. 命令行 登录/退出
登录????
java org.apache.derby.tools.ij
..\dirs>javaorg.apache.derby.tools.ij
ij 版本 10.3
ij>
?
退出
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. 创建新表/删除表
createtable mytable(number varchar(4), name varchar(10), age int, addressvarchar(40));
ij> createtable mytable(number varchar(4), name varchar(10), age int, addressvarchar(40));
已插入／更新／删除 0 行
ij>
?
//删除整个表
droptable mytable;
ij> droptable mytable;
已插入／更新／删除 0 行
ij>
?
3. 表中添加数据：
insertinto mytable values('1001', 'one', 20, 'street 1');
insertinto mytable values('1002', 'two', 22, 'street 2');
ij> insertinto mytable values('1001', 'one', 20, 'street 1');
已插入／更新／删除 1 行
ij> insertinto 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. 查看表
showtables;
ij> showtables;
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. 查看表的结构
describemytable;
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. 表中添加数据
insertinto mytable values('1001', 'one', 20, 'street 1');
insertinto mytable values('1002', 'two', 22, 'street 2');
ij> insertinto mytable values('1001', 'one', 20, 'street 1');
已插入／更新／删除 1 行
ij> insertinto mytable values('1002', 'two', 22, 'street 2');
已插入／更新／删除 1 行
ij>
?
7. 查询数据
select *from mytable;
selectnumber, name from mytable;
ij> select* from mytable;
NUM&|NAME?????|AGE??????? |ADDRESS
--------------------------------
1001|one??????|20???????? |street 1
1002|two??????|22???????? |street 2
已选择 2 行
ij> selectnumber, name from mytable;
NUM&|NAME
---------------
1001|one
1002|two
已选择 2 行
?
8. 更新数据
updatemytable set where number = '1001';
ij> updatemytable set 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. 删除表中数据，保留表格式
deletefrom mytable where ;
deletefrom mytable;
ij> deletefrom mytable where ;
已插入／更新／删除 1 行
ij> select* from mytable;
NUM&|NAME?????|AGE??????? |ADDRESS
--------------------------------
1001|one_one?? |20???????? |street 1
已选择 1 行
ij> deletefrom 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>
?