mysql 常用的有用命令2

show create table mytable;
show index from mytable;
show culumn from mytable;
show privileges;


show variables;
show global variables;
show session variables;


select current_user;


UCASE,CONCAT(str,'',name),left(str,1),MOD(30,8) SQRT(30),ROUND(3,0)COALESCE(str,1),YEAR(date),DAYNAME(date),MONTHNAME(date),DAYOFYEAR(date)
DATEDIFF(startDate,endDate),SUBSTRING(str,1),ASCIIL(str),LENGTH(str), LTRIM(RTRIM('SQL')), REPLACE(str,org,rep)

cast  类型转换表达式  cast('123' as signed integer)   case(121214 as time)

conv(6,10,2)进制转换  bin(32)二进制表示

interval  10 day   10天间隔
interval  100 week  
interval  20 month
interval  5 year

addtime(orgDate,intervalTime)


行表达式： select * from mytabl where (name,age)=('weijian',34)

select * from mytabl where name<=>null;

关联性子查询： 

escape
select * from mytable where name like '%#_%' escape '#'; 查找name中包含下划线的记录，在like中忽略escape后面的字符转义
select * from mytale where name regexp 'e';

create fulltext index index_name on mytable(name)

select * from mytable where match(name) against('abc') 查找mane中包含abc的记录

select * from mytable where not exist(select 'nothin' from mytable2 where name = mytable.name)

select * from mytable where birthdate <= all(select birthdate from mytable)
select * from mytable where birthdate > any(select birthdate from mytable)

select count(distinct substr(name,1,1)) from mytable;

select  count(distinct substr(name,1,1)),count(distinct age)) from mytable;

select sum(acount) from mytabl group by acount having sum(acount) >= (select sum(acount) from mytable group by acount)

select name,age from mytable order by 2; 根据select 的第二个表达式来排序

union：
 1.select的列要一样；2：order by 只放在最后一个；3不能使用distinct，因为union自动会对结果进行distinct
  union all 不要去除重复的行

临时表：
  create tempary table mytabl(name varchar(10));

select utf_8"datehoust";用utf-8编码显示字符串

rename table mytable to another; 重命名表

create (unique) index myindex using hash(using btree) on mytable(name);

analysis table mytable; 更新表的索引的可压缩性
checksum table mytable;查询表的校验和
optimize table mytable;优化表的存储
checke table mytable; 检查表是否完整
repair table mytable;
backup table mytable to"path";
help 'create table';