0903

upsert:
MobileLite:  upsert into test_insert values (N'홍%d', N'수원시',  N'원천동', '632-20-%d', 'SC-First',%d, 'Suwon',900%d, 'L-%d',10%d);

alter table customer add primary key(customer_name);
alter table account add primary key(account_number);
alter table branch add primary key(branch_name);
alter table loan add primary key(loan_name);
alter table depositor add primary key(customer_name, account_number);
alter table borrower add primary key(customer_name, loan_number);
alter table test_insert add primary key(customer_name);

create index ind_an_t on test_insert(account_number);
create index ind_bn_t on test_insert(branch_name);
create index ind_ln_t on test_insert(loan_number);


Func. mobilelite:
MAX:  select max(balance) from test_insert where assets >1000000;
COUNT:  select count(blance) from test_insert where assets > 1000000;
sum:  select sum(balance) from test_insert where assets >1000000;
convert: select convert(char(12), balance) from test_insert where customer_name = N'홍%d';
decode:　select decode (customer_street, N'수원', 'CITY IS SUWON',N'서울','CITY IS SEOUL') from test_insert where customer_name = N'홍%d';
substring: select substring(customer_city,1,4) from test_insert where customer_name = N'홍%d';
group by: select sum(assets) from test_insert group by customer_city;
avg:     select avg(assets) from test_insert;
order by: select * from test_insert order by(customer_name);
                                1/1/1/0/1/0

Func. sqlite:
MAX:  select max(balance) from test_insert where assets >1000000;
COUNT:  select count(blance) from test_insert where assets > 1000000;
sum:  select sum(balance) from test_insert where assets >1000000;
subst:  select substr(customer_city,1,4) from test_insert where customer_name = '홍%d';
group by: select sum(assets) from test_insert group by (customer_city);
avg:     select avg(assets) from test_insert;
order by: select * from test_insert order by(customer_name);
                                26/24/24/21


ml:
update 조건절 사용: update test_insert set customer_city=N'서울' where customer_name=N'홍%d'
update 같은 길이: update test_insert set customer_city=N'서울' ;
update 변경 길이: update test_insert set customer_city=N'서울 동대문구 경복궁 수원 화서구 화성'
SQ:
update 조건절 사용: update test_insert set customer_city='서울' where customer_name=N'홍%d'
update 같은 길이: update test_insert set customer_city='서울' ;
update 변경 길이: update test_insert set customer_city='서울 동대문구 경복궁 수원 화서구 화성'


error query: update test_insert set customer_city=N'서울 동대문구 경복궁 수원 화서구 화성' ;

转载于:https://www.cnblogs.com/jinrize/archive/2009/09/03/1559542.html