mysql删除，插入，查询等语句的总结

alter table    tablename    drop column      colname;//删除字段

alter table     tablename    change    old_filed_name   new_filed_name   new_type;   //修改字段名称和类型

INSERT INTO table_name (name,pwd,email,sex,register_time) VALUES ('xiong',SHA1('sdf'),'asdg@qq.com','f',NOW());//插入元素

update table set column='value' where condition;//修改记录

truncate table_name 无损删除表中的所有元素

select count(*) from 表名; 统计记录数量

update table column1='xxx', column2='xxx' where condition; ///更新数据

select concat(column1,' ',column2,' ',columns3) as newname //将1,2,3列的数据拼为newname列输入

select length(name) as len, name from table order by len desc limit 1//找出姓名最长的名字

select dayname(register_time) as weekday from talbe where name not like 'xxx5' order by register_time limit 1;//显示日期是星期几

select curdate(), curtime();返回当前mysql服务器时间;

select column1, columns2, dateformat(column3,'%m %d , %y') as date from users order by column3 desc limit 5；//找出最后注册信息的五个记录

select p.column1,q.column from table1 as p inner join table2 as q using(commom column) where q.column1=p.column;//从两个表中按条件筛选信息

alter table table_name add index_type index_name(columns); //添加表的索引

alter table table_name alter column_name column_type not null;//更改为not null

alter table table_name change old_col_name new_col_name data_type not null auto_increment primary key//修改字段属性

delete from table_name where column_naem='value' limit 1;//删除数据

alter table table_name character set utf8;//修改字符集

show table status\G;// ‘\G’将每个表的信息以纵列显示

alter table table_name add index_type index_name(column1,column2);//为column1，column2添加索引

drop index index_name on table_name;//删除索引