ALTER [ IGNORE ] TABLE tbl_name alter_specfication [ , alter_specification ] ....
alter_specification:
ADD [ COLUMN ] column_definition [ FIRST | AFTER col_name ] //添加列
ALTER [ COLUMN ] col_name { SET DEFAULT literal | DROP DEFAULT } //修改默认值
CHANGE [ COLUMN ] old_col_name column_definition [ FIRST | AFTER col_name ] //对列重命名
MODIFY [ COLUMN ] column_definition [ FIRST | AFTER col_name ] //修改列类型。
DROP [ COLUMN ] col_name //删除列
RENAME [ TO ] new_tbl_name //重命名改表
ORDER BY col_name //排序
CONVERT TO CHARACTER SET charset_name [ COLLATE collation_name ] //将字符集转换为二进制
[ DEFAULT ] CHARACTER SET charaset_name [ collate collation_name ] //修改默认字符集
tbl_name:表名
col_name:指定的列名。
alter table t1 and column a tinyint null; 在表t1中增加新的一列a
更改表名:rename table user_copy1 to user1, user_cpopy2 to user2; //将数据库中个代表user_copy1重命名为user1; user_copy2重命名为user2.
修改多个表:
update user, user2 set user.password='xxxxx', user2.password='yyyyyyy' where user.id=user2.id;
删除记录:
delete from person where id=1
假设有3个表t1,t2,t3,他们都还有id列。要删除他中id值等于t2的id值的所有行和t2中id值等于t3的id值的所有行,使用如下语句:
delete t1,t2 from t1,t2,t3 where t1.id=t2.id and t2.id=t3.id
show processlist ;;// 显示系统中正在运行的所有进程。
show errors;; 显示最后一个执行的语句所产生的错误
show warnings:显示最后一个执行语句所产生的错误、警告、和通知。