刚刚接触mysql 不太懂,有劳各位指点.最好贴代码,我好理解.我先创建视图表
DROP TABLE IF EXISTS `www_home`;CREATE TABLE `www_home` (
`tid` varchar(50) NOT NULL DEFAULT '' COMMENT '主题ID',
) ENGINE=MyISAM DEFAULT CHARSET=utf8 ROW_FORMAT=FIXED;
建立视图DROP TABLE `www_home`;
CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `www_home`
AS select
`www_software`.`id` AS `tid`, (id 是主键)
from `www_software`;
UNION
AS select
`www_game`.`id` AS `tid`, (id 是主键)
from `www_game`;我想把 www_software和www_game的 id 内容 同步到 www_home tid 中. 但是2个表中的id内容都一样,不能建立视图.
有没有办法 让2个表中id 不管是否重复都写入到 www_home tid 中.
DROP TABLE IF EXISTS `www_home`;CREATE TABLE `www_home` (
`tid` varchar(50) NOT NULL DEFAULT '' COMMENT '主题ID',
) ENGINE=MyISAM DEFAULT CHARSET=utf8 ROW_FORMAT=FIXED;
建立视图DROP TABLE `www_home`;
CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `www_home`
AS select
`www_software`.`id` AS `tid`, (id 是主键)
from `www_software`;
UNION
AS select
`www_game`.`id` AS `tid`, (id 是主键)
from `www_game`;我想把 www_software和www_game的 id 内容 同步到 www_home tid 中. 但是2个表中的id内容都一样,不能建立视图.
有没有办法 让2个表中id 不管是否重复都写入到 www_home tid 中.
举例说明
AS
select
`www_software`.`id` AS `tid`
from `www_software`
UNION ALL
select
`www_game`.`id` AS `tid`
from `www_game`;
#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'AS select
`www_game`.`id` AS `tid`,
`id` int(11) unsigned NOT NULL AUTO_INCREMENT,
`tid` varchar(50) NOT NULL DEFAULT '' COMMENT '数据ID',
`tbiaoshi` varchar(300) NOT NULL DEFAULT '' COMMENT '标识',
`cateid` int(8) unsigned NOT NULL DEFAULT '0' COMMENT '所在分类ID',
`name` varchar(120) NOT NULL DEFAULT '' COMMENT '软件名',
`ver` varchar(50) NOT NULL DEFAULT '' COMMENT '网站显示版本号',
`versionname` varchar(10) NOT NULL DEFAULT '' COMMENT '内部版本',
`versioncode` varchar(10) NOT NULL DEFAULT '' COMMENT '版本代码',
`packagename` varchar(50) NOT NULL DEFAULT '' COMMENT '',
`indexer` char(2) NOT NULL DEFAULT '' COMMENT '索引',
`keyword` varchar(200) NOT NULL DEFAULT '' COMMENT '关键词',
`description` varchar(254) NOT NULL DEFAULT '' COMMENT '描述',
`intro` text NOT NULL COMMENT '软件简介',
`ico` varchar(255) NOT NULL DEFAULT '' COMMENT '软件图标',
`iswide` tinyint(1) NOT NULL DEFAULT '0' COMMENT '截图是否为宽屏',
`views` int(11) unsigned NOT NULL DEFAULT '0' COMMENT '查看次数',
`monthviews` int(11) unsigned NOT NULL DEFAULT '0' COMMENT '月查看数',
`weekviews` int(11) unsigned NOT NULL DEFAULT '0' COMMENT '周查看数',
`down` int(11) unsigned NOT NULL DEFAULT '0' COMMENT '下载次数',
`monthdown` int(11) unsigned NOT NULL DEFAULT '0' COMMENT '月下载统计',
`weekdown` int(11) unsigned NOT NULL DEFAULT '0' COMMENT '周下载统计',
`order` int(5) unsigned NOT NULL DEFAULT '0' COMMENT '排序',
`recommend` enum('1','0') NOT NULL DEFAULT '0' COMMENT '设置推荐',
`hometop` enum('1','0') NOT NULL DEFAULT '0' COMMENT '首页推荐',
`catetop` enum('1','0') NOT NULL DEFAULT '0' COMMENT '分类推荐',
`time` int(10) unsigned NOT NULL DEFAULT '0' COMMENT '添加时间',
`lastupdate` int(10) unsigned NOT NULL DEFAULT '0' COMMENT '最后更新时间',
`fileformat` varchar(20) NOT NULL DEFAULT '' COMMENT '访问URL',
`applicable` varchar(300) NOT NULL DEFAULT '' COMMENT '',
`platform` varchar(254) NOT NULL DEFAULT '' COMMENT '平台',
`filesize` varchar(15) NOT NULL DEFAULT '' COMMENT '文件大小',
`author` varchar(15) NOT NULL DEFAULT '' COMMENT '开发者',
`homepage` varchar(150) NOT NULL DEFAULT '' COMMENT '软件开发者首页',
`reply` int(5) NOT NULL DEFAULT '0' COMMENT '评论数',
`star` double(2,1) NOT NULL DEFAULT '4.5' COMMENT '星级',
`editor` varchar(16) NOT NULL DEFAULT '' COMMENT '编辑',
`isapp` tinyint(1) unsigned NOT NULL DEFAULT '1' COMMENT '',
`required` tinyint(1) DEFAULT '0' COMMENT '',
`timgs` varchar(300) NOT NULL DEFAULT '' COMMENT '简介缩略图',
PRIMARY KEY (`id`),
KEY `name` (`name`),
KEY `cateid` (`cateid`),
KEY `keyword` (`keyword`),
KEY `views` (`views`,`monthviews`,`weekviews`),
KEY `down` (`down`,`monthdown`,`weekdown`),
KEY `packagename` (`packagename`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 ROW_FORMAT=FIXED;DROP TABLE `www_home`;
CREATE VIEW `www_home`
AS select
`www_software`.`id` AS `tid`,
`www_software`.`tbiaoshi` AS `tbiaoshi`,
`www_software`.`cateid` AS `cateid`,
`www_software`.`name` AS `name`,
`www_software`.`ver` AS `ver`,
`www_software`.`versionname` AS `versionname`,
`www_software`.`versioncode` AS `versioncode`,
`www_software`.`packagename` AS `packagename`,
`www_software`.`indexer` AS `indexer`,
`www_software`.`keyword` AS `keyword`,
`www_software`.`description` AS `description`,
`www_software`.`intro` AS `intro`,
`www_software`.`ico` AS `ico`,
`www_software`.`iswide` AS `iswide`,
`www_software`.`views` AS `views`,
`www_software`.`monthviews` AS `monthviews`,
`www_software`.`weekviews` AS `weekviews`,
`www_software`.`down` AS `down`,
`www_software`.`monthdown` AS `monthdown`,
`www_software`.`weekdown` AS `weekdown`,
`www_software`.`order` AS `order`,
`www_software`.`recommend` AS `recommend`,
`www_software`.`hometop` AS `hometop`,
`www_software`.`catetop` AS `catetop`,
`www_software`.`time` AS `time`,
`www_software`.`lastupdate` AS `lastupdate`,
`www_software`.`fileformat` AS `fileformat`,
`www_software`.`applicable` AS `applicable`,
`www_software`.`platform` AS `platform`,
`www_software`.`filesize` AS `filesize`,
`www_software`.`author` AS `author`,
`www_software`.`reply` AS `reply`,
`www_software`.`star` AS `star`,
`www_software`.`editor` AS `editor`,
`www_software`.`isapp` AS `isapp`,
`www_software`.`required` AS `required`,
`www_software`.`timgs` AS `timgs`
from `www_software`
UNION ALL
AS select
`www_game`.`id` AS `tid`,
`www_game`.`tbiaoshi` AS `tbiaoshi`,
`www_game`.`cateid` AS `cateid`,
`www_game`.`name` AS `name`,
`www_game`.`ver` AS `ver`,
`www_game`.`versionname` AS `versionname`,
`www_game`.`versioncode` AS `versioncode`,
`www_game`.`packagename` AS `packagename`,
`www_game`.`indexer` AS `indexer`,
`www_game`.`keyword` AS `keyword`,
`www_game`.`description` AS `description`,
`www_game`.`intro` AS `intro`,
`www_game`.`ico` AS `ico`,
`www_game`.`iswide` AS `iswide`,
`www_game`.`views` AS `views`,
`www_game`.`monthviews` AS `monthviews`,
`www_game`.`weekviews` AS `weekviews`,
`www_game`.`down` AS `down`,
`www_game`.`monthdown` AS `monthdown`,
`www_game`.`weekdown` AS `weekdown`,
`www_game`.`order` AS `order`,
`www_game`.`recommend` AS `recommend`,
`www_game`.`hometop` AS `hometop`,
`www_game`.`catetop` AS `catetop`,
`www_game`.`time` AS `time`,
`www_game`.`lastupdate` AS `lastupdate`,
`www_game`.`fileformat` AS `fileformat`,
`www_game`.`applicable` AS `applicable`,
`www_game`.`platform` AS `platform`,
`www_game`.`filesize` AS `filesize`,
`www_game`.`author` AS `author`,
`www_game`.`reply` AS `reply`,
`www_game`.`star` AS `star`,
`www_game`.`editor` AS `editor`,
`www_game`.`isapp` AS `isapp`,
`www_game`.`required` AS `required`,
`www_game`.`timgs` AS `timgs`
from `www_game`;
AS select->
UNION ALL
select