练习的mysql语句

 例子：将cdb_pms表subject字段中的Welcom to替换成 欢迎光临

UPDATE `cdb_pms`
SET `subject` = REPLACE(`subject`, 'Welcome to', '欢迎光临')
WHERE INSTR(`subject`,'Welcome to') < 0

替换cdb_posts表的message字段，将“viewthread.php?tid=3989”替换成“viewthread.php?tid=16546”

UPDATE `cdb_posts`
SET `message`= REPLACE(`message`, 'viewthread.php?tid=3989', 'viewthread.php?tid=16546')
WHERE INSTR(`message`,'viewthread.php?tid=3989') < 0 ;

删除所有的空格
UPDATE `es_product` SET `pro_pub_time` = TRIM(`pro_pub_time`)

删除所有饱含'['或者']'或者'.'的字符
UPDATE `es_product` SET `pro_pub_time` = REPLACE(`pro_pub_time`, '[','') WHERE INSTR(`pro_pub_time`,'[') < 0
UPDATE `es_product` SET `pro_pub_time` = REPLACE(`pro_pub_time`, ']','') WHERE INSTR(`pro_pub_time`,']') < 0
UPDATE `es_product` SET `pro_pub_time` = REPLACE(`pro_pub_time`, '.','-') WHERE INSTR(`pro_pub_time`,'.') < 0

替换所有含中文'-'的为英文'-'
UPDATE `es_product` SET `pro_pub_time` = REPLACE(`pro_pub_time`, '－','-') WHERE INSTR(`pro_pub_time`,'－') < 0

将所有的年月都替换成'-'
UPDATE `es_product` SET `pro_pub_time` = REPLACE(`pro_pub_time`, '年','-') WHERE INSTR(`pro_pub_time`,'年') < 0
UPDATE `es_product` SET `pro_pub_time` = REPLACE(`pro_pub_time`, '月','-') WHERE INSTR(`pro_pub_time`,'月') < 0

将所有'2005-04-'这种类型的替换成'2005-04-01'
UPDATE `es_product` SET `pro_pub_time` = CONCAT( `pro_pub_time`, '01') WHERE SUBSTRING_INDEX( `pro_pub_time`, '-', -1) = '' AND LENGTH(`pro_pub_time`) < 0 AND LENGTH(`pro_pub_time`) < 5

将所有'2005-'这种类型替换成'2005-01-01'
UPDATE `es_product` SET `pro_pub_time` = CONCAT( `pro_pub_time`, '01-01') WHERE INSTR(`pro_pub_time`,'-') < 0 AND LENGTH(`pro_pub_time`) = 5

将所有 饱含'-',但是位数小于8的改成追加'-01'
UPDATE `es_product` SET `pro_pub_time` = CONCAT( `pro_pub_time`, '-01') WHERE INSTR(`pro_pub_time`,'-') < 0 AND LENGTH(`pro_pub_time`) > 8

将所有'2005'这样的改成'2005-01-01'
UPDATE `es_product` SET `pro_pub_time` = CONCAT(`pro_pub_time`,'-01-01') WHERE INSTR(`pro_pub_time`,'-') = 0 AND LENGTH(`pro_pub_time`) = 4

最后将所有'2005-01-01'格式化成'2005年01月'
UPDATE `es_product` SET `pro_pub_time` = DATE_FORMAT(`pro_pub_time`,'%Y年%m月') WHERE INSTR(`pro_pub_time`,'-') < 0

///////////
create table `sp`.`table`( `id` int NOT NULL AUTO_INCREMENT , `content` varchar(255) DEFAULT 'null' , PRIMARY KEY (`id`))  ;
DROP table a;

alter table test  change actor actor varchar(255) FIRST ;

alter table test auto_increment =120;

alter table test add column id int not null auto_increment primary key first ,AUTO_INCREMENT =500;
alter table test drop  id;
ALTER table test add column actor varchar(255) null;

create table test(
         id int not null auto_increment ,
         content varchar(255) default null,
         primary key (id))
use sp;
select count(*) from ganji where length(persionnumber)>5;
select count(*) from ganji;

select count(*) from ganji;

select count(*) from ganji where length(persionnumber)>5;

select * from ganji where length(persionnumber)>5;

select distinct(city) from ganji;

select * from ganji where title like "天津大型企业%";

select CHAR_LENGTH(style) from store limit 100;

select * from ganji   limit 10 where CHAR_LENGTH(persionnumber)>0;

select * from ganji   limit 10 where CHAR_LENGTH(persionnumber)
length(persionnumber)>0;

select * from ganji   where persionnumber != "" limit 10;

alter table ganji change column addtime addtime varchar(50);

alter table ganji add column addtime datetime;

desc ganji;

select * from ganji;

truncate table ganji;

delete from store_copy where cat_id like "";

update store_copy set cat_id = 5;
delete from store_copy where cat_id=5;

select count(*) from store_copy where cat_id=5;
select  category.name,store_copy.special,LEFT(store_copy.special,2), INSTR( category.name,LEFT(store_copy.special,2)) from store_copy,category limit;

update store_copy,category set store_copy.cat_id = category.id where  INSTR(store_copy.special,category.name)>0;

select * from category limit 1;
update
update store_copy,category set store_copy.cat_id = category.id where  INSTR( category.name,LEFT(store_copy.special,2))>0;

select LEFT(special,2) from store_copy limit 10;
update store_copy,region set store_copy.region_id = region.id where  INSTR(region.name,store_copy.city)>0;

select count(*) from store_copy where region_id="";

SELECT name from region group by name;
SELECT name from category group by name;
SELECT special from store_copy group by special;

delete from store where CHAR_LENGTH(style) ;

select count(*) from store_copy;
delete from store where CHAR_LENGTH(style)<1;
create table store_copy select * from store where CHAR_LENGTH(style)>1;

select CHAR_LENGTH(style) from store limit 100;

delete from store_copy where CHAR_LENGTH(style) <1;

delete from store_copy where id>80000;

spstore_copy

select count(type),type from job group by type;

update job set type="一般工人" where INSTR(name,"一般工人")>0;
update job set type="技工" where INSTR(name,"技工")>0;
update job set type="修理工" where INSTR(name,"修理")>0;
update job set type="司机" where INSTR(name,"司机")>0;
update job set type="水暖工" where INSTR(name,"水暖")>0;
update job set type="家政" where INSTR(name,"家政")>0;
update job set type="电焊工" where INSTR(name,"电焊")>0;
update job set type="电工" where INSTR(name,"电工")>0;
update job set type="保洁" where INSTR(name,"保洁")>0;
update job set type="保安" where INSTR(name,"保安")>0;

select * from ganji where professon = "业务" limit 1;
select professon from job group by professon;

category

select city from job group by city;
select * from region where id = 44030000;
select region_id from job group by region_id;

update job,region set job.region_id = region.id where  INSTR(region.name,job.city)>0;

LIKE INSTR
update job,region set job.region_id = region.id where job.area = region.name;

select count(*) from region;
create table job select * from ganji;

lter table guess add constraint FK_Reference_5 foreign key (type)
      references guesstype (id) on delete restrict on update restrict;

update guess set type1 = 7 where type ="脑筋急转弯" ;
update guess set type1 = 6 where type ="智力问答" ;
update guess set type1 = 5 where type ="搞笑谜语" ;
update guess set type1 = 4 where type ="成人谜语" ;
update guess set type1 = 3 where type ="情趣谜语" ;
update guess set type1 = 2 where type ="开心谜语" ;
update guess set type1 = 1 where type ="字迷" ;

select * from

select type from guesstype;

select count(*) from guesstype;

update guess set type1 = 1 where type ="脑筋急转弯";

create table guessType select type from guess group by type;