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 ;