mysql常用基本操作

#查看数据库
show databases;
#选择数据库
use <dbname>;
#删除数据库 
drop database <dbname>;
#查看数据表
show tables;
#表的创建
create table <tablename>( name varchar(255),age int(4),sex varchar(255));
#向表中插入数据
insert into <tablename>(name,age,sex) values('stf',4,'男');
#增加字段
alter table <tablename> add score varchar(255);
#指定某列后插入字段
alter table <tablename> add class varchar(255) after name;
#修改表名
alter table <tablename> rename <newtablename>;
#删除表
drop table <tablename>;
#条件判断
delete from <tablename> where name='stf';
#更新数据
update <tablename> set name='stf01' where name='stf';
#数据排序
select * from <tablename> order by <字段名>; 升序
select * from <tablename> order by <字段名> desc; 降序
#分组
select 字段名,count(*) from <tablename> group by 字段名;
#统计函数
select max(字段名) from <tablename>;
select max(字段名) as max_字段名 from <tablename>; 取别名输出
#通配符
select * from <tablename> where 字段名 like '%xxxx%';
#in,not in
select * from <tablename> where 字段名 in ('xxx','xxx');
select * from <tablename> where 字段名 not in('xxx','xxx');
#日期格式化
select 字段名,data_format(日期字段,'%Y') from <tablename>;
#取唯一值
select distinct 字段名 from <tablename>;
#between ... and 条件判断
select * from <tablename>  where 字段名 between 'xxx' and 'xxxx';
#having条件判断
select * from <tablename> having 字段名 >xxx;
#union 语句合并
select * from <tablename1> union select * from <tablename2>;