--sql identity
CREATE TABLE dbo.Customer 
( 
customerid INT IDENTITY PRIMARY KEY, 
firstname VARCHAR(40) NOT NULL, 
lastname VARCHAR(40) NOT NULL, 
statecode VARCHAR(2) NOT NULL, 
totalsales money NOT NULL DEFAULT 0.00 
) 
 
--oracle中的identity
 
ORACLE是不能用IDENTITY的,可以用SEQUENCE
由于oracle是序列型的，所以不是在建表的时候递增的,可以用以下方法:
1、先建表：
CREATE TABLE Customer 
( 
customerid integer, 
firstname VARCHAR(40) DEFAULT '' NOT NULL, 
lastname VARCHAR(40) DEFAULT '' NOT NULL, 
statecode VARCHAR(2) DEFAULT '' NOT NULL, 
totalsales number(10,2) DEFAULT '' not null,--oracle money数据类型：应该用number(x,y)其中x表示有效位数，y表示小数点后的位数
PRIMARY KEY (customerid)
) 
 
2、创建一个SEQUENCE
  create sequence SEQ_Customer 
  minvalue 1
  maxvalue 99999999
  start with 21
  increment by 1
  cache 20;
3、在向表插数据时插入自增列
 
INSERT INTO Customer(customerid,firstname, lastname, statecode, totalsales) values(SEQ_Customer.Nextval ,'Thomas', 'Jefferson', 'VA', 100.00 );
INSERT INTO Customer(customerid,firstname, lastname, statecode, totalsales) values(SEQ_Customer.Nextval ,'lyj', 'lj', 'AB', 500.00 );
INSERT INTO Customer(customerid,firstname, lastname, statecode, totalsales) values(SEQ_Customer.Nextval , 'John', 'Adams', 'MA', 200.00); 
INSERT INTO Customer(customerid,firstname, lastname, statecode, totalsales) values(SEQ_Customer.Nextval , 'Paul', 'Revere', 'MA', 300.00 );
INSERT INTO Customer(customerid,firstname, lastname, statecode, totalsales) values(SEQ_Customer.Nextval ,'Ben', 'Franklin', 'PA', 400.00 );
commit;
4、在满足需求中增加列-case和update连用，提交执行效率
ALTER TABLE Customer ADD statedescription VARCHAR(50) DEFAULT '';
 
UPDATE Customer 
SET stateDescription = CASE WHEN statecode = 'MA' THEN 'Massachusetts' 
WHEN statecode = 'VA' THEN 'Virginia' 
WHEN statecode = 'PA' THEN 'Pennsylvania'
when statecode ='AB' then 'liyongjie'
ELSE NULL 
END
 
commit;
 
select * from Customer;--查询以上插入的数据
 
SELECT COUNT(*) AS TotalCustomers, 
SUM(CASE WHEN statecode = 'MA' THEN 1 ELSE NULL END) AS TotalMassCustomers, 
AVG(CASE WHEN statecode = 'MA' THEN totalsales ELSE NULL END) AS TotalMassSales 
FROM Customer