create_vote.sql

希望这个对大家有用

-- 创建vote数据库
CREATE DATABASE IF NOT EXISTS vote
DEFAULT CHARACTER SET gbk COLLATE gbk_chinese_ci;
-- 选择vote数据库
USE vote;
-- 创建admin表
CREATE TABLE admin (
  username varchar(20) NOT NULL COMMENT '用户名',
  password varchar(20) NOT NULL COMMENT '密码',
  PRIMARY KEY  (username)
) ENGINE=InnoDB DEFAULT CHARSET=gbk;
-- 创建votemain表
CREATE TABLE votemain (
  vote_id smallint(3) unsigned NOT NULL auto_increment COMMENT '投票活动编号',
  vote_title varchar(100) NOT NULL COMMENT '投票活动标题',
  vote_type varchar(8) NOT NULL default 'radio' COMMENT '投票活动类型',
  vote_time datetime NOT NULL default '0000-00-00 00:00:00' COMMENT '投票活动设立时间',
  PRIMARY KEY  (vote_id)
) ENGINE=InnoDB DEFAULT CHARSET=gbk;
-- 创建votedetail表
CREATE TABLE votedetail (
  voteitem_id int(10) unsigned NOT NULL auto_increment COMMENT '投票选项编号',
  vote_id smallint(6) NOT NULL COMMENT '投票活动编号',
  vote_descriptiON varchar(100) NOT NULL COMMENT '投票选项描述',
  vote_count int(10) unsigned NOT NULL default '0' COMMENT '得票数',
  PRIMARY KEY (voteitem_id)
) ENGINE=InnoDB DEFAULT CHARSET=gbk;
DELIMITER ;;
-- 在表votemain中创建触发程序delete_votemain_trigger
CREATE TRIGGER delete_votemain_trigger
AFTER DELETE ON votemain
FOR EACH ROW BEGIN DELETE FROM votedetail
WHERE vote_id=OLD.vote_id;
END;;
DELIMITER ;
-- 创建视图view1
CREATE ALGORITHM=UNDEFINED DEFINER=root@localhost
SQL SECURITY DEFINER VIEW view1
AS
SELECT votemain.vote_id AS vote_id, votemain.vote_title AS vote_title,
votemain.vote_time AS vote_time, SUM(votedetail.vote_count) AS total
FROM (votemain INNER JOIN votedetail
ON ( (votemain.vote_id = votedetail.vote_id ) ) )
GROUP BY votemain.vote_id;
-- 创建视图view2
CREATE ALGORITHM=UNDEFINED DEFINER=root@localhost
SQL SECURITY DEFINER VIEW view2
AS
SELECT votedetail.vote_id AS vote_id,
votedetail.vote_description AS vote_description,
votemain.vote_title AS vote_title, votemain.vote_type AS vote_type,
votedetail.voteitem_id AS voteitem_id
FROM ( votemain INNER JOIN votedetail
ON ( ( votemain.vote_id = votedetail.vote_id ) ) );
-- 创建视图veiw3
CREATE ALGORITHM=UNDEFINED DEFINER=root@localhost
SQL SECURITY DEFINER VIEW veiw3
AS
SELECT votemain.vote_title AS vote_title,
votedetail.vote_description AS vote_description,
votedetail.vote_count AS vote_count, votedetail.vote_id AS vote_id
FROM (votemain INNER JOIN votedetail
ON ( ( votemain.vote_id = votedetail.vote_id ) ) );
-- 创建视图view4
CREATE ALGORITHM=UNDEFINED DEFINER=root@localhost
SQL SECURITY DEFINER VIEW view4
AS
SELECT veiw3.vote_id AS vote_id, SUM(veiw3.vote_count) AS total
FROM veiw3 GROUP BY veiw3.vote_id;