1、计算category不为空的行占总行数的百分比
select count(*) / ( select count(*) from `log-http-13` ) from `log-http-13` where category <> "" ;
2、查询非空的行,逆序输出,不超过300行
select * from `log-http-28` where `x_online_host` <>"" order by id desc limit 300;
3、将表的创建命令列出
show create table groupzone;
4、复制旧表的数据到新表(假设两个表结构一样)
INSERT INTO 新表 SELECT * FROM 旧表
5、新增一列
alter table groupzone add column `nas_id` bigint(20) unsigned default '0';
6、列复制
update groupzone set `nas_id`=`id`;
7、查看列
desc table `os`;
8、复制行
insert into mytable (select * from mytable where id=1) ON DUPLICATE KEY UPDATE id=2;
insert into table1 (col1,col2,col3) select col1,col2,col3 from table1 where id<10;
9、mysql 修改表名
alter table 表名 rename to 新表名
10、用某一列的值更新另外一列
update `userblacklist-1` set time = (1402553668+id);
11、ip转换输出
select inet_ntoa(device_ip) from apdevice limit 5;
12、ip转换查询
<pre name="code" class="sql">select * from `log-http-5` where dest_ip = inet_aton("255.255.255.255") ;
13、Linux下MySQL的备份与还原
2.1 备份 [root@localhost ~]# cd /var/lib/mysql (进入到MySQL库目录,根据自己的MySQL的安装情况调整目录) [root@localhost mysql]# mysqldump -u root -p voice>voice.sql,输入密码即可。
2.2 还原法一:[root@localhost ~]# mysql -u root -p 回车,输入密码,进入MySQL的控制台"mysql>",同1.2还原。
法二:[root@localhost ~]# cd /var/lib/mysql (进入到MySQL库目录,根据自己的MySQL的安装情况调整目录) [root@localhost mysql]# mysql -u root -p voice<voice.sql,输入密码即可。
MySQLl提供了一个mysqldump命令,我们可以用它进行数据备份。
按提示输入密码,这就把tm数据库所有的表结构和# mysqldump -u root -p tm > tm_050519.sql数据备份到tm_050519.sql了,因为要总进行备份工作,如果数据量大会占用很大空间,这时可以利用gzip压缩数据,命令如下:
# mysqldump -u root -p tm | gzip > tm_050519.sql.gz
系统崩溃,重建系统时,可以这样恢复数据:
# mysqldump -u root -p tm < tm_050519.sql
从压缩文件直接恢复:
#gzip < tm_050519.sql.gz | mysqldump -u root -p tm
当然,有很多MySQL工具提供更直观的备份恢复功能,比如用phpMyAdmin就很方便。但我认为,mysqldump是最基本、最通用的。
备份数据表
mysqldump -uroot -p syslog-db-manager ws_ncu_lib > ncu.sql;
还原数据表
mysql -uroot -p syslog-db-manager < ncu.sql ;
如何导出数据库表的数据?(不同于备份哦)
select * from ws_ncu_lib into outfile "/home/url_lib_back_20140616.txt";
14、数据库表crash恢复
进入mysql安装目录
cd /usr/bin/
myisamchk -c -r ../data/tablename/posts.MYI
15、统计数据库相同列字段个数
select http_host, category, count(*) from `log-http-5` group by http_host having count(http_host)>1 and category = "" order by count(http_host);
16、时间显示
select from_unixtime();
select unix_timastamp();
17、mysql在linux终端查询中文出现乱码
mysql> show variables like '%character_%';
+--------------------------+----------------------------+
| Variable_name | Value |
+--------------------------+----------------------------+
| character_set_client | latin1 |
| character_set_connection | latin1 |
| character_set_database | latin1 |
| character_set_filesystem | binary |
| character_set_results | latin1 |
| character_set_server | latin1 |
| character_set_system | utf8 |
| character_sets_dir | /usr/share/mysql/charsets/ |
+--------------------------+----------------------------+
8 rows in set (0.01 sec)
mysql> set character_set_client = utf8;
Query OK, 0 rows affected (0.02 sec)
mysql> set character_set_connection = utf8;
Query OK, 0 rows affected (0.00 sec)
mysql> set character_set_results = utf8;
Query OK, 0 rows affected (0.00 sec)
mysql> show variables like '%character_%';
+--------------------------+----------------------------+
| Variable_name | Value |
+--------------------------+----------------------------+
| character_set_client | utf8 |
| character_set_connection | utf8 |
| character_set_database | latin1 |
| character_set_filesystem | binary |
| character_set_results | utf8 |
| character_set_server | latin1 |
| character_set_system | utf8 |
| character_sets_dir | /usr/share/mysql/charsets/ |
+--------------------------+----------------------------+
8 rows in set (0.00 sec)
mysql>
最后再执行你的查询就可以了。