mysql常用的sql语句
来源:原创
时间:2016-04-26
作者:脚本小站
分类:DB
增删改查:
insert into tableName(title,name) values("",""); delete from tableName where id = 12; update tableName set title = "",name = "" where id = 12; select id,title,name from tableName [where][group][order][limit];
改表字段与表名:
alter table tb_name add name char not null; alter table tb_name drop name; alter table tb_name change name username char not null; alter table tb_name modify name char not null; alter table tb_name rename as new_tb_name;
创建索引
alter table tb_name add indexindex_name(name); alter table tb_name add uniqueunique_name(name);
删除索引
alter table tb_name drop index index_name;
显示索引
show index from tb_name;
显示创建表
show create table tb_name;
多表查询
select * from user1 a,user2 b where a.id=b.id order by id desc
select表.字段, 表.字段 from 表1,表2 where 表1的id=表2的id;
select bbs_content.content,bbs_reply.title from bbs_content,bbs_reply where bbs_content.id = bbs_reply.cid;
(as后为表名或显示名,as 当做)
select xx.字段 as xxx from tb_name as xx;
其他
show columns from tbl_name; 列出资料表字段 show full columns from tbl_name; 列出字段及详情 show fields from tbl_name; 列出字段属性 show full fields from tbl_name; 列出字段完整属性 show status; 列出db server状态 show table status; 列出表的状态信息 show variables; 列出 mysql 系统环境变量 show processlist; 列出执行命令
列出某用户权限
show grants for user;
备份数据忽略某张表:
mysqldump -uxxx -pxxx database --ignore-table=database.table1 --ignore-table=database.table2 > backup.sql