SQL常用的一些语句<非常容易忘记的>

一，查询

   取倒数第二条数据：
   select * from (select * from shop order by 'article' desc limit 2) as b order by 'article' asc limit 1;


   联合查询：

   SELECT w.WebName, w.Owner, w.CustomerCode,c.AgentId, c.CerType, c. CerID,c.CustomerName FROM  `WebInfo`  AS w,  `CustomerInfo`  AS c WHERE w.CustomerCode = c.CustomerCode;


   不显示重复字段数据：

   select distinct name from test;



二，更新

   UPDATE `Message` SET `GoWhere` = 'SmsAddr' WHERE `GoWhere` = 'SmsAddress' LIMIT 1 ;



三，删除

   delete student where id=1;

   DROP database test;（慎用）

   Drop table student;（慎用）



四，创建

CREATE TABLE `student` (
  `id` int(11) NOT NULL auto_increment,
  `name` varchar(20) default NULL,
  `stuNo` varchar(20) default NULL,
  `startTime` date default NULL,
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 MAX_ROWS=4;



五，增加

INSERT INTO employees VALUES (1,"Bob","Smith","128 Here St", "Marketing Manager");

INSERT INTO student (id,name,stuNo,startTime) VALUES (1,"forDream","xyzx55888","2003-01-02");


六，其它

Mysql改变编码
ALTER TABLE salary DEFAULT CHARACTER SET utf8 COLLATE utf8_bin;


给某表增加一字段
ALTER TABLE `Message` ADD `GoWhere` VARCHAR( 10 ) NOT NULL ;