mysql 常用命令积累

查看数据库个数

show databases

选择数据库

use databasename

显示所有表

show tables;

显示表结构

show colums from 表名

查询mysql版本

select version();

查询数据库占用端口

show variables like 'port';

or

select * from global_variables where VARIABLE_NAME ='PORT';

复杂语句总结

patient表中存病人信息,attachment表中存病人的附件,现要查全部病人中的附件个数.
```
 select *,count(attachment.id) as attachment_sum from attachment right outer JOIN patient on ( patient.id = attachment.patient_id) group by patient.id 
```
参考链接http://www.dreamincode.net/forums/topic/63460-count-and-show-values-0-or-null-mysql/

按年日月统计数据

 select DATE_FORMAT(created_at,'%X-%V')n,count(*) from sample group by n ;

n	count(*)
2015-33	11
2015-34	1

2015-33是2015年第33个星期的意思.

 select year(created_at) ,month(created_at), day(created_at) ,count(*)  from sample group by year(created_at) ,month(created_at), day(created_at);

参考链接http://blog.csdn.net/youngqj/article/details/6453647 http://www.cnblogs.com/Fooo/p/3435687.html

gound by 要在where 之前?

sql连接详解

http://www.cnblogs.com/eflylab/archive/2007/06/25/794278.html

一句话右边为主.

选择范围

where 属性 between var1 and var2 每个数据库处理var2方式不一样 mysql则返回var1和var2的(包含等于)

排除在外的 not between

选择数据多少

limit var1 , var2 返回var+1开始的var2条数据

判断是否为null

where var is null; 
where var IS NOT NULL;

数据库系统文件

查询日志路径

SHOW  GLOBAL VARIABLES LIKE '%log%';

系统错误文件

/usr/local/mysql/data/K.local.err

重启mysql

//linux
sudo /etc/init.d/mysql restart

UNION

(select columns from table) union all (select colums from table2) limit 0,20

优化建议: 1. 最外层的若加了limit,子层也要加上相应的limit

并没什么用的命令

SELECT * INTO OUTFILE '/Users/maizhikun/test2/K.csv' FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' LINES  TERMINATED BY '\n' FROM   INFORMATION_SCHEMA.COLUMNS WHERE  TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'table'

like in

like通配一般后面只跟一个字符串,如何用一个属性匹配多个like通配符呢 ? 我们想要的sql是这样的

SELECT * FROM fiberbox f WHERE f.fiberBox LIKE '%1740 %' OR f.fiberBox LIKE '%1938 %' OR f.fiberBox LIKE '%1940 %'

这语句是OK 但是很繁琐我们想要的是这样的

SELECT * FROM fiberbox f WHERE f.fiberbox LIKE IN('%140 %', '%1938 %', '%1940 %')

但是这样语法是错误的应该这么玩

SELECT * from fiberbox where field REGEXP '1740|1938|1940';

问题那如果我只想 liek %xxx 呢 ??? 估计只能用第一种繁琐的用法了.

后续原来REGEXP后面跟正则..那么应该就能无敌了.. 我上面的问题就可以解决了

参考链接https://stackoverflow.com/questions/1127088/mysql-like-in/1127106#1127106

修改嵌套查询

update samples as ss inner join (select patient_id,id from sample) as s on ss.id=s.id  set ss.patient_id = s.patient_id;

K

Mysql 常用命令积累