derby数据库基本语法

1表操作语法

1.1创建/连接数据库

网络服务器模式

connect 'jdbc:derby://localhost:1527/MYDB;create=true;user=root;password=123';

（1）“jdbc:derby:”derby数据库URL的头部，必须有；
（2）“//localhost:1527/MYDB”网络服务器模式必有的主机IP、derby数据库端口号、数据库名（数据库会被创建在derby数据库解压包的bin目录下）；
（3） “create=true”如果数据库不存在是否创建数据库；
（4）“user=root;password=123”用户名和密码；

内嵌模式

connect 'jdbc:derby:D:/MYDB;create=true;user=root;password=123';

（1）“D:/MYDB”内嵌模式没有IP和端口，数据库会被创建在指定的路径下，若不在connect连接命令下指定则创建在当前的工作目录下。

1.2查看表

show tables

1.3创建/修改表

CREATE TABLE
    mytable
    (
        id VARCHAR(4) NOT NULL,
        name VARCHAR(10),
        sex CHAR(1),
        createtime DATE,
        age INTEGER,
        oid VARCHAR(4),
        CONSTRAINT id_pk PRIMARY KEY (id)
    )

rename table mytable to emp1;

1.4修改表
（1）增加字段：alter table 表名 add 字段名 类型;

alter table mytable add newcolumn bigint;

（2）删除字段：alter table 表名 drop column 字段名

alter table mytable drop column newcolumn;

（3）修改字段

rename column mytable.mid to id;//修改字段名  会破坏主键
alter table mytable alter NAME set data type varchar(100);//修改数据类型
alter table mytable alter NAME not null;//修改字段不为空
alter table mytable alter NAME default 'aaa';//修改字段的初始值

1.5查看表结构

describe mytable;

1.6删除整个表

drop table mytable;

2数据操作语法

2.1 插入数据

insert into mytable(id,name,sex,createtime,age,oid) values('1001','李**','男',date('2017-01-06'),22,'1234');

2.2 更新数据

update mytable set name='刘**' where id='1001';

2.3删除数据

delete from mytable where id='1001';

2.4查看数据

select name,age
from mytable
group by age,name,oid
having oid='1234'
order by age asc[desc]

3 数据类型
http://db.apache.org/ddlutils/databases/derby.html

• bigint 返回给定字符串或数字的 64-bit 整数常量。
• char 返回给定值的固定长度的字符表示，最大长度为 254 个字节，该给定值必须是内置的 Apache Derby 类型之一。可以提供可选的第二个参数以指定字符串的长度。
• date 返回输入值的日期表示。
• double 返回输入数字或字符串的双精度浮点表示。
• integer 返回给定字符串、日期、时间或数字的整数常量。
• smallint 返回给定字符串或数字的小整数常量。
• time 返回输入值的时间表示。
• timestamp 返回输入值的时间戳表示。
• varchar 返回给定日期、时间、时间戳或字符串值的长度可变的字符表示，最大长度为 32,672 个字节。

4 函数

4.1数据类型对应的函数
函数 返回值
bigint(123.45) 123
char(‘123.45’) ‘123.45’
date(‘time’) ‘yyyy-MM-dd’
double(123.45) 123.45
Integer(123.45) 123
smallInt(123.45) 123
time(‘time’) ‘hh:mm:ss’
timeStamp(‘time’) ‘yyyy-MM-dd hh:mm:ss’
varchar(‘123.45’) ‘123.45’

4.2聚合函数
avg() 平均值
count()总行
max() 最大值
min() 最小值
sum() 和

4.3数学函数
abs() or absval() 绝对值
mod(paramter1,parmeter2) 参数1除以参数2的余数
sort() 平方根

4.4日期 和 时间函数
函数 返回值
day(time) 每月的第几天
hour(time) 时
minute(time) 分
month(time) 月
second(time) 秒
year(time) 年

4.5字符串函数
|| 连接符号
lcase() or lower() 转化小写
ucase() or upper() 转化大写
length() 长度
locate() 返回一个子字符串在搜索字符串中第一次出现的起始位置，如果没找到子字符串，则返回0。第一个参数是子字符串，第二个参数是搜索字符串，可选的起始位置可以提供作为第三个参数。
rtrim() 去掉右空格
ltrim() 去掉左空格
substr() 返回 VARCHAR 类型的输入字符串的一部分，在指定位置处开始，一直延续到字符串末尾，或延续到可选的第三个参数指定的位置。如果起始位置是正值，则它相对于字符串的开头；如果是负值，则是相对于字符串的末尾

4.6虚表

derby没有类似oracle的虚表，可以用 values 来达到虚表效果

values current_date;//返回当前的日期
values current_time;//返回当前的时间
values current_timestamp;//返回当前的时间戳

4.7 去重 distinct

select distinct name from mytable where age=22;

5 索引
5.1 创建索引
create index 索引名 on 表名(字段1，字段2…….);

create index indexName on mytable(id,name,sex,createtime,age,oid);

5.2删除索引
drop index 索引名;

drop index indexName;

注：主键和唯一键都会在创建的时候自动创建一个索引，再创建会出警告

Code: 10000 SQL State: 01504 --- The new index is a duplicate of an existing index: SQL170106111016840.

6 自动加值

create table mytable1(mid int generated by default as identity(start with 1,increment by 1),
mname varchar(50));

create table mytable1(mid int generated by default as identity(start with 1,increment by 1),
mname varchar(50));