数据库常用操作
一共导入了五张表:
store
member
seller
br_store_class
area
1,删除表里面的数据
delete from命令格式:delete from 表名 where 表达式
例如,删除表 MyClass中编号为1 的记录:
mysql> delete from MyClass where id=1;
2,
创建sql语句:
CREATE TABLE `33hao_area` (
`area_id` int(11) unsigned NOT NULL AUTO_INCREMENT COMMENT '索引ID',
`area_name` varchar(50) NOT NULL COMMENT '地区名称',
`area_parent_id` int(11) unsigned NOT NULL DEFAULT '0' COMMENT '地区父ID',
`area_sort` tinyint(3) unsigned NOT NULL DEFAULT '0' COMMENT '排序',
`area_deep` tinyint(1) unsigned NOT NULL DEFAULT '1' COMMENT '地区深度,从1开始',
`area_region` varchar(3) DEFAULT NULL COMMENT '大区名称',
PRIMARY KEY (`area_id`),
KEY `area_parent_id` (`area_parent_id`)
) ENGINE=InnoDB AUTO_INCREMENT=45056 DEFAULT CHARSET=utf8 COMMENT='地区表';
3,插入数据:
INSERT INTO 33hao_area(area_id,area_name,area_parent_id,area_sort,area_deep)
SELECT region_id,region_name,parent_id,sort_order,level
FROM br_region;
导出数据:
放入数据库
导入store表使用sql:
INSERT INTO 33hao_store
(store_id,store_name,grade_id,member_id,member_name,seller_name,province
_id,area_info,store_address,store_zip,store_state,store_close_info,store
_sort,store_end_time,store_label,store_banner,store_description,store_qq
,store_ww,store_phone,store_recommend,store_theme,store_credit,mobile_lo
go,per_capita_price,is_tv_store,lat,lng,comrate,mobile_banner,self_pic,s
elf_product,self_price,owner_card)
SELECT
st.store_id,st.store_name,st.sgrade,mm.user_id,st.owner_name,mm.user_nam
e,st.region_id,st.region_name,st.address,st.zipcode,st.state,st.close_re
ason,st.sort_order,st.end_time,st.store_logo,st.store_banner,st.descript
ion,st.im_qq,st.im_ww,st.hotline,st.recommended,st.theme,st.praise_rate,
st.mobile_logo,st.per_capita_price,st.is_tv_store,st.lat,st.lng,st.comra
te,st.mobile_banner,st.self_pic,st.self_product,st.self_price,st.owner_c
ard
FROM br_store as st left join br_member as mm on st.store_id =
mm.user_id;
第二次导入:以这次为准
INSERT INTO 33hao_store
(store_id,store_name,grade_id,member_id,member_name,seller_name,province_id,area_info,store_address,store_zip,store_state,store_close_info,store_sort,store_end_time,store_label,store_banner,store_description,store_qq,store_ww,store_phone,store_recommend,store_theme,store_credit,mobile_logo,per_capita_price,is_tv_store,lat,lng,comrate,mobile_banner,self_pic,self_product,self_price,owner_card)
SELECT
st.store_id,st.store_name,st.sgrade,mm.user_id,mm.user_name,mm.user_name,st.region_id,st.region_name,st.address,st.zipcode,st.state,st.close_reason,st.sort_order,st.end_time,st.store_logo,st.store_banner,st.description,st.im_qq,st.im_ww,st.hotline,st.recommended,st.theme,st.praise_rate,st.mobile_logo,st.per_capita_price,st.is_tv_store,st.lat,st.lng,st.comrate,st.mobile_banner,st.self_pic,st.self_product,st.self_price,st.owner_card
FROM br_store as st left join br_member as mm on st.store_id = mm.user_id;
delete from 33hao_store where store_id > 0;
导入member表:
INSERT INTO br_member_nc
(member_id,member_name,member_truename,member_avatar,member_sex,member_b
irthday,member_passwd,member_email,member_mobile,member_qq,member_ww,mem
ber_login_num,member_time,member_old_login_time,member_old_login_ip,memb
er_areaid,member_areainfo,identification,unionid,identify_card)
SELECT
user_id,user_name,real_name,portrait,gender,birthday,password,email,phon
e_mob,im_qq,im_aliww,logins,reg_time,last_login,last_ip,region_id,addres
s,identification,unionid,identify_card
FROM br_member;
导入region表数据:
INSERT INTO br_member_nc(
member_id,
member_name,
member_truename,
member_avatar,
member_sex,
member_birthday,
member_passwd,
member_email,
member_mobile,
member_qq,
member_ww,
member_login_num,
member_time,
member_old_login_time,
member_old_login_ip,
member_areaid,
member_areainfo,
identification,
unionid,
identify_card)
SELECT user_id,
user_name,
real_name,
portrait,
gender,
birthday,
password,
email,
phone_mob,
im_qq,
im_aliww,
logins,
reg_time,
last_login,
last_ip,
region_id,
address,
identification,
unionid,
identify_card
FROM br_region;
public function linshimysqlOp(){ echo '<meta charset="utf-8">'; $g_c_m = Model('goods_common'); $g_c = Model('goods'); $g_cs = Model('goods')->field('goods_price,goods_commonid,goods_name')->select(); foreach ($g_cs as $key => $value) { // 执行更新 $data = array('goods_costprice'=>$value['goods_price']); $data1 = array('goods_promotion_price'=>$value['goods_price']); if($g_c_m ->where(array('goods_commonid'=>$value['goods_commonid']))->update($data) && $g_c ->where(array('goods_commonid'=>$value['goods_commonid']))->update($data1)){ echo $value['goods_name'].'<font color="green">成功修改</font><br />'; } } }
public function updatePriceOp() { set_time_limit(0); $link=mysql_connect("localhost","root","root") or exit("数据库连接失败!"); $db=mysql_select_db("shop"); mysql_set_charset("utf8"); $select = "select goods_id,goods_price,goods_commonid,goods_name from nc_goods where goods_promotion_price !=''"; $result = mysql_query($select); while($arr=mysql_fetch_assoc($result)) { if(!empty($arr)){ $update1="update br_goods_common set goods_costprice={$arr['goods_price']} where goods_commonid={$arr['goods_commonid']} "; //mysql_query($update1); $update2="update br_goods set goods_promotion_price={$arr['goods_price']} where goods_commonid={$arr['goods_commonid']}"; //mysql_query($update2); $str="商品id:{$arr['goods_id']},修改成功"; file_put_contents("log.txt",$str.PHP_EOL,FILE_APPEND); } } }
mysql> select count(*) from br_member; +----------+ | count(*) | +----------+ | 167075 | +----------+ 1 row in set mysql> select count(*) from br_member4; +----------+ | count(*) | +----------+ | 130541 | +----------+ 1 row in set mysql> select count(*) from br_store; +----------+ | count(*) | +----------+ | 2648 | +----------+ 1 row in set mysql> select count(*) from br_store_old; +----------+ | count(*) | +----------+ | 2629 | +----------+ 1 row in set
中间调整数据:
member表:
差距数据: mysql> select count(*) from br_membernew; +----------+ | count(*) | +----------+ | 36534 | +----------+ 1 row in set 原有数据: mysql> select count(*) from br_member; +----------+ | count(*) | +----------+ | 130553 | +----------+ 1 row in set 插入数据: mysql> INSERT INTO br_member (member_name,member_truename,member_avatar,member_sex,member_birthday,member_passwd,member_email,member_mobile,member_qq,member_ww,member_login_num,member_time,member_old_login_time,member_old_login_ip,member_areaid,member_areainfo,identification,unionid,identify_card) SELECT user_name,real_name,portrait,gender,birthday,password,email,phone_mob,im_qq,im_aliww,logins,reg_time,last_login,last_ip,region_id,address,identification,unionid,identify_card FROM br_membernew; Query OK, 36534 rows affected Records: 36534 Duplicates: 0 Warnings: 29179
最终数据: mysql> select count(*) from br_member; +----------+ | count(*) | +----------+ | 167087 | +----------+ 1 row in set