爬虫表.sql

本程序可从网上利用百度搜索引擎下载和输入关键词有关的网页

alter table urls add tw varchar(20)


alter table urls add 千年一叹 varchar(20)
alter table urls add 易中天品三国 varchar(20)
alter table urls add 图书 varchar(20)
alter table urls add 书网上店 varchar(20)

select * from urls  order by ID -- where 书店>=1

alter table urls rename bookee to bokee


alter table urls rename  to urls
alter table distributors add constrain distfk foreign key (address) references addresses(address) match full--增加外键约束

insert into baidu(1) values('http://www.baidu.cn/s?wd=%CE%F7%D3%CE%BC%C7++%B3%F6%B0%E6%C9%E7&cl=3')--西游记 出版社
insert into baidu(1) values('http://www.baidu.com/s?wd=%BA%EC%C2%A5%C3%CE+%B3%F6%B0%E6%C9%E7')--红楼梦 出版社

insert into baidu(1) values('http://www.baidu.cn/s?wd=%C3%F1%B7%A8+%B3%F6%B0%E6%C9%E7&cl=3')-- 民法 出版社

insert into baidu(1) values('http://www.baidu.cn/s?wd=%B1%BE%B2%DD%B8%D9%C4%BF+%B3%F6%B0%E6%C9%E7&cl=3')--本草纲目 出版社

insert into baidu(1) values('http://www.baidu.com/s?wd=%CA%E9&cl=3')--书

insert into baidu(1) values('http://www.baidu.cn/s?wd=%CD%BC%CA%E9&cl=3')--图书

insert into baidu(1) values('http://www.baidu.com/s?wd=%CA%E9%B5%EA&cl=3')--书店

insert into baidu(1) values('http://www.baidu.com/s?wd=%CD%F8%C9%CF%CA%E9%B5%EA&cl=3')--书网上店

insert into baidu(1) values('http://www.baidu.com/s?wd=%B7%A8%C2%C9+%CD%BC%CA%E9&cl=3')--法律 图书

insert into baidu(1) values('http://www.baidu.com/s?wd=%BC%C6%CB%E3%BB%FA++%CD%BC%CA%E9&cl=3')--计算机 图书

insert into baidu(1) values('http://www.baidu.com/s?wd=%CE%C4%D1%A7++%CD%BC%CA%E9&cl=3')--文学 图书

insert into baidu(1) values('http://www.baidu.com/s?wd=%D2%BD%D2%A9+%CD%BC%CA%E9&cl=3')--医药 图书

insert into baidu(1) values('http://www.baidu.com/s?wd=java%C3%E6%CF%F2%B6%D4%CF%F3%B1%E0%B3%CC+%B3%F6%B0%E6%C9%E7&cl=3&tn=antiarp_pg&bar=')--java面向对象编程 出版社

insert into baidu(1) values('http://www.baidu.com/s?wd=%D2%D7%D6%D0%CC%EC%C6%B7%C8%FD%B9%FA+%B3%F6%B0%E6%C9%E7&cl=3')--易中天品三国 出版社

insert into baidu(1) values('http://www.baidu.com/s?wd=java%C3%E6%CF%F2%B6%D4%CF%F3%B1%E0%B3%CC&cl=3')--java面向对象编程 无出版社

insert into baidu(1) values('http://www.baidu.com/s?wd=%CE%A7%B3%C7+%B3%F6%B0%E6%C9%E7&cl=3')--围城 出版社

insert into baidu(1) values('http://www.baidu.com/s?wd=%D7%F6%D7%EE%BA%C3%B5%C4%D7%D4%BC%BA+++%B3%F6%B0%E6%C9%E7&cl=3')--做最好的自己 出版社

insert into baidu(1) values('http://www.baidu.com/s?wd=%BA%EC%C9%AB%BC%BE%B7%E7+%B3%F6%B0%E6%C9%E7&cl=3')--红色季风 出版社

insert into baidu(1) values('http://www.baidu.com/s?wd=java%D3%EB%C4%A3%CA%BD+%B3%F6%B0%E6%C9%E7&cl=3')--java与模式 出版社

insert into baidu(1) values('http://www.baidu.com/s?wd=%C7%A7%C4%EA%D2%BB%CC%BE+%B3%F6%B0%E6%C9%E7&cl=3')--千年一叹 出版社

insert into baidu(1) values('http://www.google.com/search?hl=zh-CN&q=java')

insert into baidu(1) values('http://www.google.cn/search?q=西游记')--千年一叹 出版社




insert into baidu(1) values('http://www.baidu.com/s?wd=%BA%EC%C2%A5%C3%CE+%B3%F6%B0%E6%C9%E7')--红楼梦 出版社

alter table urls add 红楼梦 varchar(20)

select * from baidu

insert into baidu(1) values('http://www.baidu.com/s?wd=%C8%FD%B9%FA%D1%DD%D2%E5+%B3%F6%B0%E6%C9%E7')--三国演义 出版社

alter table urls add 三国演义 varchar(20)

insert into baidu(1) values('http://www.baidu.com/s?wd=%CB%AE%E4%B0%B4%AB+%B3%F6%B0%E6%C9%E7')--水浒传 出版社

alter table urls add 水浒传 varchar(20)

insert into baidu(1) values('http://www.baidu.com/s?wd=%CE%F7%CF%E1%BC%C7+%B3%F6%B0%E6%C9%E7')--西厢记 出版社
alter table urls add 西厢记 varchar(20)

insert into baidu(1) values('http://www.baidu.com/s?wd=%C1%C4%D5%AB%D6%BE%D2%EC+%B3%F6%B0%E6%C9%E7')--聊斋志异 出版社
alter table urls add 聊斋志异 varchar(20)

insert into baidu(1) values('http://www.baidu.com/s?wd=%B7%E2%C9%F1%D1%DD%D2%E5++%B3%F6%B0%E6%C9%E7')--封神演义 出版社
alter table urls add 封神演义 varchar(20)

insert into baidu(1) values('http://www.baidu.com/s?wd=%C2%DB%D3%EF+%B3%F6%B0%E6%C9%E7')--论语 出版社
alter table urls add 论语 varchar(20)

insert into baidu(1) values('http://www.baidu.com/s?wd=%CB%EF%D7%D3%B1%F8%B7%A8+%B3%F6%B0%E6%C9%E7')--孙子兵法 出版社
alter table urls add 孙子兵法 varchar(20)

--------
insert into baidu(1) values('http://www.baidu.com/s?wd=%D7%AF%D7%D3+%B3%F6%B0%E6%C9%E7')--庄子 出版社
alter table urls add 庄子 varchar(20)

--------
insert into baidu(1) values('http://www.baidu.com/s?wd=%CA%AB%BE%AD+%B3%F6%B0%E6%C9%E7')--庄子 出版社
alter table urls add 诗经 varchar(20)


--------
insert into baidu(1) values('http://www.baidu.com/s?wd=%D7%CA%D6%CE%CD%A8%BC%F8+%B3%F6%B0%E6%C9%E7')--资治通鉴 出版社
alter table urls add 资治通鉴 varchar(20)

--------
insert into baidu(1) values('http://www.baidu.com/s?wd=%C8%E5%C1%D6%CD%E2%CA%B7+%B3%F6%B0%E6%C9%E7')--儒林外史 出版社
alter table urls add 儒林外史 varchar(20)

--------
insert into baidu(1) values('http://www.baidu.com/s?wd=%CA%B7%BC%C7+%B3%F6%B0%E6%C9%E7')--史记 出版社
alter table urls add 史记 varchar(20)

--------
insert into baidu(1) values('http://www.baidu.com/s?wd=%BA%F3%BA%BA%CA%E9+%B3%F6%B0%E6%C9%E7')--后汉书 出版社
alter table urls add 后汉书 varchar(20)
--------
insert into baidu(1) values('http://www.baidu.com/s?wd=%F1%BC%B6%F0%D4%A9+%B3%F6%B0%E6%C9%E7')--窦娥冤 出版社
alter table urls add 窦娥冤 varchar(20)
--------
insert into baidu(1) values('http://www.baidu.com/s?wd=%C4%B5%B5%A4%CD%A4+%B3%F6%B0%E6%C9%E7')--牡丹亭 出版社
alter table urls add 牡丹亭 varchar(20)


select * from baidu

drop table baidu

create table baidu
(
--存放的是百度的下一页
   ID  int identity(1,1) primary  key,
   myurl varchar(200),--下一页地址
)

select * from urls order by ID

select * from urls  where myurl like '%.tom.%' and (xun=1 or zhen=1) order by ID 

select * from urls  where myurl like '%.sina.%' 

--update urls set xun=5 where myurl like '%.sina.%' 



select * from urls  where  红楼梦=1 order by ID  
 
select * from urls  where (zhen=1 or xun=1 )  and 红楼梦=1 order by ID   -- (ID>=1 and ID<=745)

select * from urls  where (西游记>=1  and 红楼梦=1) and (zhen=1 or xun=1 ) order by ID  

-----
select * from urls  where  三国演义=1 order by ID  
 
select * from urls  where 西游记 is null and 红楼梦 is null and 三国演义=1 order by ID  

select * from urls  where (zhen=1 or xun=1 )  and 三国演义=1 order by ID   -- (ID>=1 and ID<=745)

select * from urls  where (zhen=1 or xun=1 )  and (西游记 is null and 红楼梦 is null and 三国演义=1) order by ID   -- (ID>=1 and ID<=745)


-----
select * from urls  where  水浒传=1 order by ID  
 
select * from urls  where 西游记 is null and 红楼梦 is null and 三国演义 is null and 水浒传=1 order by ID  

select * from urls  where (zhen=1 or xun=1 )  and 水浒传=1 order by ID   -- (ID>=1 and ID<=745)

select * from urls  where (zhen=1 or xun=1 )  and (西游记 is null and 红楼梦 is null and 三国演义 is null and 水浒传=1) order by ID   -- (ID>=1 and ID<=745)

-----
select * from urls  where  西厢记=1 order by ID  
 
select * from urls  where 西游记 is null and 红楼梦 is null and 三国演义 is null and 水浒传 is null and 西厢记=1 order by ID  

select count(*) from urls  where (zhen=1 or xun=1 ) and 西厢记=1    -- (ID>=1 and ID<=745)

select * from urls  where (zhen=1 or xun=1 )  and (西游记 is null and 红楼梦 is null and 三国演义 is null and 水浒传 is null and 西厢记=1) order by ID   -- (ID>=1 and ID<=745)

-----
select * from urls  where  西厢记=1 order by ID  
 
select * from urls  where 西游记 is null and 红楼梦 is null and 三国演义 is null and 水浒传 is null and 西厢记=1 order by ID  

select count(*) from urls  where (zhen=1 or xun=1 ) and 西厢记=1    -- (ID>=1 and ID<=745)

select * from urls  where (zhen=1 or xun=1 )  and (西游记 is null and 红楼梦 is null and 三国演义 is null and 水浒传 is null and 西厢记=1) order by ID   -- (ID>=1 and ID<=745)

-----
select * from urls  where  聊斋志异=1 order by ID  
 
select * from urls  where 西游记 is null and 红楼梦 is null and 三国演义 is null and 水浒传 is null and 西厢记 is null and 聊斋志异=1 order by ID  

select count(*) from urls  where (zhen=1 or xun=1 ) and 聊斋志异=1    -- (ID>=1 and ID<=745)

select * from urls  where (zhen=1 or xun=1 )  and (西游记 is null and 红楼梦 is null and 三国演义 is null and 水浒传 is null and 西厢记 is null and 聊斋志异=1) order by ID   -- (ID>=1 and ID<=745)

-----
select * from urls  where  封神演义=1 order by ID  
 
select * from urls  where 西游记 is null and 红楼梦 is null and 三国演义 is null and 水浒传 is null and 西厢记 is null and 聊斋志异 is null and 封神演义=1 order by ID  

select count(*) from urls  where (zhen=1 or xun=1 ) and 封神演义=1    -- (ID>=1 and ID<=745)

select * from urls  where (zhen=1 or xun=1 )  and (西游记 is null and 红楼梦 is null and 三国演义 is null and 水浒传 is null and 西厢记 is null and 聊斋志异 is null and 封神演义=1) order by ID   -- (ID>=1 and ID<=745)

-----
select * from urls  where  论语=1 order by ID  
 
select * from urls  where 西游记 is null and 红楼梦 is null and 三国演义 is null and 水浒传 is null and 西厢记 is null and 聊斋志异 is null and 封神演义 is null and 论语=1 order by ID  

select count(*) from urls  where (zhen=1 or xun=1 ) and 论语=1    -- (ID>=1 and ID<=745)

select * from urls  where (zhen=1 or xun=1 )  and (西游记 is null and 红楼梦 is null and 三国演义 is null and 水浒传 is null and 西厢记 is null and 聊斋志异 is null and 封神演义 is null and 论语=1) order by ID   -- (ID>=1 and ID<=745)

-----
select * from urls  where   孙子兵法=1 order by ID  
 
select * from urls  where 西游记 is null and 红楼梦 is null and 三国演义 is null and 水浒传 is null and 西厢记 is null and 聊斋志异 is null and 封神演义 is null and 论语 is null and 孙子兵法=1 order by ID  

select * from urls  where (zhen=1 or xun=1 ) and 孙子兵法=1    -- (ID>=1 and ID<=745)

select * from urls  where (zhen=1 or xun=1 )  and (西游记 is null and 红楼梦 is null and 三国演义 is null and 水浒传 is null and 西厢记 is null and 聊斋志异 is null and 封神演义 is null and 论语 is null and 孙子兵法=1) order by ID   -- (ID>=1 and ID<=745)

-----
select * from urls  where   庄子=1 order by ID  
 
select * from urls  where 西游记 is null and 红楼梦 is null and 三国演义 is null and 水浒传 is null and 西厢记 is null and 聊斋志异 is null and 封神演义 is null and 论语 is null and 孙子兵法 is null and 庄子=1 order by ID  

select * from urls  where (zhen=1 or xun=1 ) and 庄子=1    -- (ID>=1 and ID<=745)

select * from urls  where (zhen=1 or xun=1 )  and (西游记 is null and 红楼梦 is null and 三国演义 is null and 水浒传 is null and 西厢记 is null and 聊斋志异 is null and 封神演义 is null and 论语 is null and 孙子兵法 is null and 庄子=1) order by ID   -- (ID>=1 and ID<=745)
-----
select * from urls  where   诗经=1 order by ID  
 
select * from urls  where 西游记 is null and 红楼梦 is null and 三国演义 is null and 水浒传 is null and 西厢记 is null and 聊斋志异 is null and 封神演义 is null and 论语 is null and 孙子兵法 is null and 庄子 is null and 诗经=1 order by ID  

select * from urls  where (zhen=1 or xun=1 ) and 诗经=1    -- (ID>=1 and ID<=745)

select * from urls  where (zhen=1 or xun=1 )  and (西游记 is null and 红楼梦 is null and 三国演义 is null and 水浒传 is null and 西厢记 is null and 聊斋志异 is null and 封神演义 is null and 论语 is null and 孙子兵法 is null and 庄子 is null and 诗经=1) order by ID   -- (ID>=1 and ID<=745)

-----
select * from urls  where   资治通鉴=1 order by ID  
 
select * from urls  where 西游记 is null and 红楼梦 is null and 三国演义 is null and 水浒传 is null and 西厢记 is null and 聊斋志异 is null and 封神演义 is null and 论语 is null and 孙子兵法 is null and 庄子 is null and 诗经 is null and 资治通鉴=1 order by ID  

select * from urls  where (zhen=1 or xun=1 ) and 资治通鉴=1    -- (ID>=1 and ID<=745)

select * from urls  where (zhen=1 or xun=1 )  and (西游记 is null and 红楼梦 is null and 三国演义 is null and 水浒传 is null and 西厢记 is null and 聊斋志异 is null and 封神演义 is null and 论语 is null and 孙子兵法 is null and 庄子 is null and 诗经 is null and 资治通鉴=1) order by ID   -- (ID>=1 and ID<=745)


-----
select * from urls  where   儒林外史=1 order by ID  
 
select * from urls  where 西游记 is null and 红楼梦 is null and 三国演义 is null and 水浒传 is null and 西厢记 is null and 聊斋志异 is null and 封神演义 is null and 论语 is null and 孙子兵法 is null and 庄子 is null and 诗经 is null and 资治通鉴 is null and 儒林外史=1 order by ID  

select * from urls  where (zhen=1 or xun=1 ) and 儒林外史=1    -- (ID>=1 and ID<=745)

select * from urls  where (zhen=1 or xun=1 )  and (西游记 is null and 红楼梦 is null and 三国演义 is null and 水浒传 is null and 西厢记 is null and 聊斋志异 is null and 封神演义 is null and 论语 is null and 孙子兵法 is null and 庄子 is null and 诗经 is null and 资治通鉴 is null and 儒林外史=1) order by ID   -- (ID>=1 and ID<=745)


-----
select * from urls  where   史记=1 order by ID  
 
select * from urls  where 西游记 is null and 红楼梦 is null and 三国演义 is null and 水浒传 is null and 西厢记 is null and 聊斋志异 is null and 封神演义 is null and 论语 is null and 孙子兵法 is null and 庄子 is null and 诗经 is null and 资治通鉴 is null and 儒林外史 is null and 史记=1 order by ID  

select * from urls  where (zhen=1 or xun=1 ) and 史记=1    -- (ID>=1 and ID<=745)

select * from urls  where (zhen=1 or xun=1 )  and (西游记 is null and 红楼梦 is null and 三国演义 is null and 水浒传 is null and 西厢记 is null and 聊斋志异 is null and 封神演义 is null and 论语 is null and 孙子兵法 is null and 庄子 is null and 诗经 is null and 资治通鉴 is null and 儒林外史 is null and 史记=1) order by ID   -- (ID>=1 and ID<=745)

-----
select * from urls  where   后汉书=1 order by ID  
 
select * from urls  where 西游记 is null and 红楼梦 is null and 三国演义 is null and 水浒传 is null and 西厢记 is null and 聊斋志异 is null and 封神演义 is null and 论语 is null and 孙子兵法 is null and 庄子 is null and 诗经 is null and 资治通鉴 is null and 儒林外史 is null and 史记 is null and 后汉书=1 order by ID  

select * from urls  where (zhen=1 or xun=1 ) and 后汉书=1    -- (ID>=1 and ID<=745)

select * from urls  where (zhen=1 or xun=1 )  and (西游记 is null and 红楼梦 is null and 三国演义 is null and 水浒传 is null and 西厢记 is null and 聊斋志异 is null and 封神演义 is null and 论语 is null and 孙子兵法 is null and 庄子 is null and 诗经 is null and 资治通鉴 is null and 儒林外史 is null and 史记 is null and 后汉书=1) order by ID   -- (ID>=1 and ID<=745)
-----
select * from urls  where   窦娥冤=1 order by ID  
 
select * from urls  where 西游记 is null and 红楼梦 is null and 三国演义 is null and 水浒传 is null and 西厢记 is null and 聊斋志异 is null and 封神演义 is null and 论语 is null and 孙子兵法 is null and 庄子 is null and 诗经 is null and 资治通鉴 is null and 儒林外史 is null and 史记  is null and 后汉书 is null and 窦娥冤=1 order by ID  

select * from urls  where (zhen=1 or xun=1 ) and 窦娥冤=1    -- (ID>=1 and ID<=745)

select * from urls  where (zhen=1 or xun=1 )  and (西游记 is null and 红楼梦 is null and 三国演义 is null and 水浒传 is null and 西厢记 is null and 聊斋志异 is null and 封神演义 is null and 论语 is null and 孙子兵法 is null and 庄子 is null and 诗经 is null and 资治通鉴 is null and 儒林外史 is null and 史记 is null and 后汉书 is null and 窦娥冤=1) order by ID   -- (ID>=1 and ID<=745)

-----
select * from urls  where   牡丹亭=1 order by ID  
 
select * from urls  where 西游记 is null and 红楼梦 is null and 三国演义 is null and 水浒传 is null and 西厢记 is null and 聊斋志异 is null and 封神演义 is null and 论语 is null and 孙子兵法 is null and 庄子 is null and 诗经 is null and 资治通鉴 is null and 儒林外史 is null and 史记  is null and 后汉书 is null and 窦娥冤 is null and 牡丹亭=1 order by ID  

select * from urls  where (zhen=1 or xun=1 ) and 牡丹亭=1    -- (ID>=1 and ID<=745)

select * from urls  where (zhen=1 or xun=1 )  and (西游记 is null and 红楼梦 is null and 三国演义 is null and 水浒传 is null and 西厢记 is null and 聊斋志异 is null and 封神演义 is null and 论语 is null and 孙子兵法 is null and 庄子 is null and 诗经 is null and 资治通鉴 is null and 儒林外史 is null and 史记 is null and 后汉书 is null and 窦娥冤 is null and 牡丹亭=1) order by ID   -- (ID>=1 and ID<=745)



select * from urls  where  myurl  like '%.etjbooks.%'  order by ID

select * from urls  where zhen=1 or xun=1 order by ID  
 
select * from urls  order by ID