MySQL技巧#常用MySQL数据库命令总结

来源：互联网发布：长春典恒网络发展有编辑：程序博客网时间：2024/05/17 03:47

1、MySQL常用命令

//创建数据库create databases name;//选择数据库use databasename;//删除数据库，不提醒drop database name;//显示所有表show tables;// 表的详细描述describe tablename;//显示创建表的SQL语句show create table table_name;//当前数据库版本select version();//查看数据库编码SHOW VARIABLES LIKE 'character%';//查看表字段和注释show full  columns  from table_name;

2、查询命令

//查看表全部内容select * from table_name //查看表中id=1的内容select * from table_name where id = '1';//排序select * from table_name order by id desc;//分组select * from table_name group by age ;//分组统计select count(*) from table_name group by age;//查询一条数据select * from table_name limit 1;//分页select * from table_name limit 10,10//左连接select * from table_name as t1 left join table_name_2 as t2 on t1.id=t2.t_id;//select * from table_name union all (select * from table_name_2)

插入修改命令

//批量插入数据insert into table_name (field1,field2) values(1,2),(2,2),(3,2)//修改update table_name set field=value where id =1;//删除表内容delete from table_name;delete from table_name where id =1;

表结构修改

//删除表drop table table_name;//增加一个字段alter table table_name add column(字段名 字段类型)//指定插入位置alter table table_name add column(字段名 字段类型) after 某字段//删除一个字段alter table table_name drop 字段名//修改字段名称/类型alter table table_name change 旧字段名 新字段名 新字段类型//修改表名称alter table table_name rename to new_table_name;//清空表truncate table table_name;

0 0