/*
数据库vote
用户名root
密码root
*/

/* 创建数据库*/
CREATE USER root IDENTIFIED BY root;

CREATE TABLESPACE vote DATAFILE'f:\vote.dbf' SIZE 50M;

ALTER USER root DEFAULT TABLESPACE vote;

GRANT CREATE SESSION,CREATE TABLE,UNLIMITED TABLESPACE,CREATE SEQUENCE，CREATE TEIGGER TO root;

/* 附加：
删除一张表 DROP TABLE 表名
删除序列 DROP SEQUENCE 序列名
删除触发器 DROP TRIGGER 触发器名
删除某条记录 DELECT FROM 数据库名.表名 WHERE 主键名="主键值"
更新字段名 ALTER TABLE 表名 RENAME COLUMN 旧的字段名 to 新的字段名
*/

/*插入多条记录
INSERT ALL
INTO 表名(字段名)values(值)
INTO 表名(字段名)values(值)
...
SELECT * FROM DUAL;
*/

/* 创建表结构*/
CREATE TABLE admin(
admin_id NUMBER(11) NOT NULL  PRIMARY KEY,
name nvarchar2(50) DEFAULT NULL,
password nvarchar2(50) DEFAULT NULL,
logintime nvarchar2(50) DEFAULT NULL
)

/* 创建自定义的sequence*/
CREATE SEQUENCE emp_sequence
INCREMENT BY 1--每次加几
START WITH 5--从多少开始计数
NOMAXVALUE--不设置最大值
NOCYCLE--一直累加，不循环
NOCACHE--不建缓冲区

/* 创建触发器*/
CREATE TRIGGER  mem_trig BEFORE
INSERT ON admin FOR EACH ROW WHEN(new.admin_id IS NULL)
BEGIN
SELECT emp_sequence.nextval into:new.admin_id from dual;
END;

/*插入一条记录*/
INSERT INTO admin(admin_id, admin_name, admin_password, logintime)VALUES(4,'admin','1111','2013-08-25 17:49:33');

/*创建表vote结构*/
CREATE TABLE vote(
vote_id number(11) NOT NULL PRIMARY KEY,
title nvarchar2(50) DEFAULT NULL,
createdate nvarchar2(50) DEFAULT NULL,
type NUMBER(11) DEFAULT NULL,
publish NUMBER(11) DEFAULT NULL,
admin_id NUMBER(11) DEFAULT NULL
)

/* 创建触发器*/
CREATE TRIGGER  men_trig BEFORE
INSERT ON vote FOR EACH ROW WHEN(new.vote_id IS NULL)
BEGIN
SELECT emp_sequence.nextval into:new.vote_id from dual;
END;

/*插入一条记录*/
INSERT INTO vote (vote_id, title, createdate,type, publish, admin_id)VALUES(1,'你喜欢的编程语言是？','2013-08-25 17:50:06',1,1,4);

/*创建表votecontext结构*/
CREATE TABLE votecontext(
votecontext_id NUMBER(11) NOT NULL PRIMARY KEY,
context nvarchar2(50) DEFAULT NULL,
count NUMBER(11) DEFAULT '0',
vote_id NUMBER(11) DEFAULT NULL
)

/* 创建触发器*/
CREATE TRIGGER  meo_trig BEFORE
INSERT ON votecontext FOR EACH ROW WHEN(new.votecontext_id IS NULL)
BEGIN
SELECT emp_sequence.nextval into:new.votecontext_id from dual;
END;

/*插入多条记录*/
INSERT ALL
INTO votecontext(votecontext_id, context, count, vote_id)VALUES(5,'Java语言',34,1)
INTO votecontext(votecontext_id, context, count, vote_id)VALUES(6,'C#.Net语言',23,1)
INTO votecontext(votecontext_id, context, count, vote_id)VALUES(7,'PHP语言',12,1)
SELECT * FROM DUAL;

/*创建表voter结构*/
CREATE TABLE voter(
id NUMBER(11) NOT NULL PRIMARY KEY,
vote_id NUMBER(11) DEFAULT NULL,
ip NVARCHAR2(45) DEFAULT NULL
)

/* 创建触发器*/
CREATE TRIGGER  mep_trig BEFORE
INSERT ON voter FOR EACH ROW WHEN(new.id IS NULL)
BEGIN
SELECT emp_sequence.nextval into:new.id from dual;
END;

/*插入一条记录*/
INSERT INTO voter(id, vote_id, ip)VALUES(2,1,'0:0:0:0:0:0:0:1');