邵珠庆の日记 生命只有一次，你可以用它来做很多伟大的事情–Make the world a little better and easier


29月/13关

Mysql命令行技巧汇总

发布在 邵珠庆

1) pager

pager真是一个很神奇的东西，它可以控制mysql的输出。默认值是stdout，直接输出。
艾，貌似讲不清楚啊，还是看几个例子吧：

mysql> SELECT mobile FROM profile WHERE mobile!='';
+-------------+
| mobile      |
+-------------+
| 13719001986 |
| 123214234   |
+-------------+
2 ROWS IN SET (0.00 sec)
 
mysql> 
mysql> pager grep "137"
PAGER SET TO 'grep "137"'
mysql> 
mysql> SELECT mobile FROM profile WHERE mobile!='';
| 13719001986 |
2 ROWS IN SET (0.00 sec)
 
mysql> pager
DEFAULT pager wasn't set, using stdout

相当于在输出之后附加了 | grep “137″ ，是不是很震憾。
不止是grep， 所有linux上面的命令都可以的，自由发挥吧。
比如select很多行数据，就可以设置pager more，一页一页地看。
再比如要比较二次select的结果是否相同，可以设置pager md5sum，实在是太赞了。
再比如，只想看select语句运行需要多长时间，而不想看到select出来的结果集，可以使用

pager cat > /dev/null

再比如，看一下processlist里面Sleep的线程有多少，可以使用

pager grep Sleep | wc -l

2) edit

肯定有人会有这样的想法：在mysql命令行里面编辑一个复杂点的sql语句实在是太费神了，光标只能左右移动，要是有个vi就好多啦。
其实真的有….
mysql> edit
就这么简单，谁用谁知道。

3) tee

如果想记录下来您所有的操作及输出，除了屏幕录象软件之外，还有这么一个神奇的东西tee。

mysql> tee /tmp/h.log
Logging TO file '/tmp/h.log'
mysql> 
mysql> 
mysql> SHOW engine innodb STATUS;
.........
mysql> exit

所有的一切都被记录在 /tmp/h.log文件中了。

4) prompt

快要去吃午饭了， 这时如果你又想看一条UPDATE语句究竟会执行多久，怎么办？
等下去就要饿死啦。

修改一下mysql的prompt提示符，轻松解决这个问题：

mysql> prompt \r:\m:\s\P>\_
PROMPT SET TO '\r:\m:\s\P>\_'
11:30:10am> 
11:30:11am> 
11:30:11am> 
11:30:12am> 
11:30:12am> 
11:30:12am> 
11:30:12am>

输入UPDATE语句，回车走人。

吃完饭回来就可以看到执行结束的时间了。

5) ctrl+z

作为一个dba,每天都要在命令行中登陆mysql，运行完sql语句，随手就logout了，(快捷键是ctrl+d)，
过一段时间又要login，又随手logout……
重复重复再重复，不仅浪费时间，而且输入mysql root的复杂密码也会烦死个人。

这个logout的坏习惯，一定要改掉。。。，哥已经改掉了。

mysql> 
mysql> 
mysql> SELECT now();
+---------------------+
| now()               |
+---------------------+
| 2012-12-25 15:33:16 |
+---------------------+
1 ROW IN SET (0.00 sec)
 
mysql> 
不要按ctrl+d了， 换成Ctrl+z ， 暂停任务。
[1]+  Stopped                 /home/mysql/bin/mysql -uroot -p
[MODIFY@www 8p]$ 
[MODIFY@www 8p]$ 
[MODIFY@www 8p]$ 
需要用到mysql的时候，
输入fg回车：
[MODIFY@www 8p]$ fg
/home/mysql/bin/mysql -uroot -p
mysql> 
mysql>

是不是很爽。。。

6) html输出

-H

[root@www ~]# /home/mysql/bin/mysql -uroot -p -H -e "select host from mysql.user";
Enter password: 
<TABLE BORDER=1><TR><TH>host</TH></TR><TR><TD>localhost</TD></TR><TR><TD>localhost</TD></TR><TR><TD>localhost</TD></TR><TR><TD>localhost</TD></TR><TR><TD>localhost</TD></TR><TR><TD>localhost</TD></TR></TABLE>[root@www ~]#

7) 最简化输出

如果只想要结果，不要那些边框，字段名称之类的东西，可以使用 –skip-line-numbers –skip-column-names

[root@www ~]# /home/mysql/bin/mysql  -uroot -p -e "select count(*) from shipincon.share where add_time >= current_date()";                            
+----------+
| COUNT(*) |
+----------+
|      850 |
+----------+
 
[root@www ~]# /home/mysql/bin/mysql --silent --skip-line-numbers  --skip-column-names -uroot -p -e "select count(*) from shipincon.share where add_time >= current_date()";
850

8) help

在不方便上网查找mysql文档的情况下，help还是很靠谱的。

mysql> help string functions;
You asked FOR help about help category: "String Functions"
FOR more information, TYPE 'help <item>', WHERE <item> IS one OF the following
topics:
   ASCII
   BIN
   BINARY OPERATOR
   BIT_LENGTH
   CAST
   CHAR FUNCTION
   CHARACTER_LENGTH
   CHAR_LENGTH
   CONCAT
   CONCAT_WS
   CONVERT
   ELT
   EXPORT_SET
   EXTRACTVALUE
   FIELD
   FIND_IN_SET
   FORMAT
   HEX
........
 
mysql> help instr;
Name: 'INSTR'
Description:
Syntax:
INSTR(str,substr)
 
RETURNS the POSITION OF the FIRST occurrence OF SUBSTRING substr IN
string str. This IS the same AS the two-argument form OF LOCATE(),
EXCEPT that the ORDER OF the arguments IS reversed.
 
URL: http://dev.mysql.com/doc/refman/5.5/en/string-functions.html
 
Examples:
mysql> SELECT INSTR('foobarbar', 'bar');
        -> 4
mysql> SELECT INSTR('xbar', 'foobar');
        -> 0
标签: , , , 没有评论
216月/13关

Mysql 导入CSV数据

发布在 邵珠庆

MYSQL   LOAD DATA INFILE命令可以把csv平面文件中的数据导入到数据库中。

linux下：
  1. LOAD DATA INFILE '/home/test/dump/ip_location.csv'
  2.  INTO TABLE ip_location
  3.  CHARACTER SET utf8
  4.  FIELDS TERMINATED BY ',' ENCLOSED BY '"';
--CHARACTER SET ：mysql字符集，一定要加上，免去乱码麻烦
--INTO TABLE ：导入到哪个表
--FIELDS TERMINATED BY ：以什么作为分割符
-- ENCLOSED BY ：被什么包围



windows：
  1. LOAD DATA INFILE "d:/insert_data.csv"
  2.  REPLACE INTO TABLE DEMO
  3.  CHARACTER SET gb2312
  4.  FIELDS TERMINATED BY "," ENCLOSED BY ""
  5.  LINES TERMINATED BY "\r\n";
--LINES TERMINATED BY：这个与linux不同，以什么作为一行的结尾。
标签: , , , 没有评论
145月/13关

mysql 截取字符串

发布在 邵珠庆

1. 字符串截取：left(str, length) 

mysql> select left('linuxidc.com', 3);
+-------------------------+
| left('linuxidc.com', 3) |
+-------------------------+
| sql                     |
+-------------------------+
2. 字符串截取：right(str, length) 

mysql> select right('linuxidc.com', 3);
+--------------------------+
| right('linuxidc.com', 3) |
+--------------------------+
| com                      |
+--------------------------+
3. 字符串截取：substring(str, pos); substring(str, pos, len) 

3.1 从字符串的第 4 个字符位置开始取，直到结束。 

mysql> select substring('linuxidc.com', 4);
+------------------------------+
| substring('linuxidc.com', 4) |
+------------------------------+
| study.com                    |
+------------------------------+
3.2 从字符串的第 4 个字符位置开始取，只取 2 个字符。 

mysql> select substring('linuxidc.com', 4, 2);
+---------------------------------+
| substring('linuxidc.com', 4, 2) |
+---------------------------------+
| st                              |
+---------------------------------+
3.3 从字符串的第 4 个字符位置（倒数）开始取，直到结束。 

mysql> select substring('linuxidc.com', -4);
+-------------------------------+
| substring('linuxidc.com', -4) |
+-------------------------------+
| .com                          |
+-------------------------------+
3.4 从字符串的第 4 个字符位置（倒数）开始取，只取 2 个字符。 

mysql> select substring('linuxidc.com', -4, 2);
+----------------------------------+
| substring('linuxidc.com', -4, 2) |
+----------------------------------+
| .c                               |
+----------------------------------+
我们注意到在函数 substring(str,pos, len)中， pos 可以是负值，但 len 不能取负值。 

4. 字符串截取：substring_index(str,delim,count) 

4.1 截取第二个 '.' 之前的所有字符。 

mysql> select substring_index('www.linuxidc.com', '.', 2);
+------------------------------------------------+
| substring_index('www.linuxidc.com', '.', 2) |
+------------------------------------------------+
| www                                  |
+------------------------------------------------+
4.2 截取第二个 '.' （倒数）之后的所有字符。 

mysql> select substring_index('www.linuxidc.com', '.', -2);
+-------------------------------------------------+
| substring_index('www.linuxidc.com', '.', -2) |
+-------------------------------------------------+
| com.cn                                          |
+-------------------------------------------------+
4.3 如果在字符串中找不到 delim 参数指定的值，就返回整个字符串 

mysql> select substring_index('www.linuxidc.com', '.coc', 1);
+---------------------------------------------------+
| substring_index('www.linuxidc.com', '.coc', 1) |
+---------------------------------------------------+
| www.linuxidc.com                               |
+---------------------------------------------------+

标签: , , 没有评论
1511月/11关

mysql批量删除指定前缀表,批量修改表名SQL语句

发布在 邵珠庆

view plaincopy to clipboardprint?

  1. Select CONCAT( 'drop table ', table_name, ';' )  
  2. FROM information_schema.tables  
  3. Where table_name LIKE 'uc_%';  

注意: like ‘uc_%’ 其中 uc_是你需要替换的表前缀.
执行查询,会自动生成出 drop table table_name这样的SQL语句.
然后复制 drop语句 可以执行删除的操作了.

这么一来也可以安全的审核一下语句,避免误操作..

顺便补充一下一个批量修改表名的操作方法

view plaincopy to clipboardprint?

  1. Select CONCAT( 'ALTER TABLE ', table_name, 'RENAME TO ', table_name,';' )  
  2. FROM information_schema.tables  
  3. Where table_name LIKE 'uc_%';  

下面这种代码是今天遇到的,表头前面是 db,但是没有下横线显得很乱,于是批量将”dbtable_name”改成”db_table_name”
主要用的函数是mysql的substring函数

mysql教程 substring 字符截取函数
substring(str,pos)语法

substring(filed,m):截取filed字段从第m个字符开始到结束的字符串；

substring(filed,m,n):截取filed字段从第m个字符开始的长度为n的字符串；

str,字符

pos,从第几个开始取

view plaincopy to clipboardprint?

  1. Select CONCAT( 'ALTER TABLE ', table_name, 'RENAME TO db_', substring(table_name,3),';' )  
  2. FROM information_schema.tables  
  3. Where table_name LIKE 'db%';  

会得到结果

view plaincopy to clipboardprint?

  1. ALTER TABLE uc_aaa RENAME TO uc_aaa;  
  2. ALTER TABLE uc_bbb RENAME TO uc_bbb;  

批量复制一下到记事本或者 et之类的编辑工具中,然后批量替换 RENAME TO uc 成 RENAME TO 你想要的表前缀
完成后 再执行.

这样就完成了表名的批量修改拉…

标签: , , , , , , , , 6 评论
1511月/11关

MySQL常用命令总结

发布在 邵珠庆

++安装mysql
参见自带的INSTALL-SOURCE文件
$ ./configure ?prefix=/app/mysql-5.0.51a ?with-charset=utf8 ?with-extra-charsets=utf8,gb2312,utf8

++启动/关闭mysql
$ path/mysqld_safe -user=mysql &
$ /mysqladmin -p shutdown

++修改root口令
$ mysqladmin -u root -p password ‘新密码’

++查看服务器状态
$ path/mysqladmin version -p

++连接远端mysql服务器
$ path/mysql -u 用户名 -p #连接本机
$ path/mysql -h 远程主机IP -u 用户名 -p#连接远程MYSQL服务器

++创建/删除 数据库或表
$ mysqladmin -u root -p create xxx
mysql> create database 数据库名;
mysql> create TABLE items (
id INT(5) NOT NULL AUTO_INCREMENT PRIMARY KEY ,
symbol CHAR(4) NOT NULL,
username CHAR(8),
INDEX sym (symbol),INDEX …..
UNIQUE(username)
) type=innodb;
mysql> drop database [if exists] 数据库名
mysql> create table 表名;
mysql> drop table 表名;

++查看数据库和查看数据库下的表
mysql> show databases;
mysql> show tables;
mysql> show table status;
mysql> desc 表名; #查看具体表结构信息
mysql> SHOW CREATE DATABASE db_name #显示创建db_name库的语句
mysql> SHOW CREATE TABLE tbl_name #显示创建tbl_name表的语句

++创建用户
mysql> grant select,insert,update,delete,alter on mydb.* to test2@localhost identified by “abc”;
mysql> grant all privileges on *.* to test1@”%” identified by “abc”;
mysql> flush privileges;

++用户管理
mysql> update user set password=password (’11111′) where user=’test1′; #修改test1密码为111111
mysql> DELETE FROM user WHERE User=”testuser” and Host=”localhost”; #删除用户帐号
mysql> SHOW GRANTS FOR user1; #显示创建user1用户的grant语句

++mysql数据库的备份和恢复
$ mysqldump -uuser -ppassword -B DB_name [--tables table1 --tables table2] > exportfile.sql
$ mysql -uroot -p xxx < aaa.sql #导入表
$ mysqldump -u 用户名 -p 数据库名 表名> 导出的文件名 ##导出单独的表

++导出一个数据库结构
$ mysqldump -u wcnc -p -d ?add-drop-table smgp_apps_wcnc >wcnc_db.sql
-d 没有数据 ?add-drop-table 在每个create语句之前增加一个drop table

++忘记mysql密码
先停止所有mysql服务进程
$ mysqld_safe ?skip-grant-tables & mysql
mysql> use mysql;
mysql> update user set password=password(’111111′) where user=’root’;
mysql> flush privileges;
然后重启mysql并以新密码登入即可

++当前使用的数据库
mysql> select database();

===数据库日常操作维护====
++创建表
mysql> create table table_name
(column_name datatype {identity |null|not null},f_time TIMESTAMP(8),…)ENGINE=MyISAM AUTO_INCREMENT=3811 DEFAULT CHARSET=utf8;
例: CREATE TABLE guest (name varchar(10),sex varchar(2),age int(3),career varchar(10));
# desc guest可查看表结构信息
# TIMESTAMP(8) YYYYMMDD 其中(2/4/6/8/10/12/14)对应不同的时间格式
mysql> SHOW CREATE TABLE tbl_name #显示创建tbl_name表的语句

++创建索引
可以在建表的时候加入index indexname (列名)创建索引,
也可以手工用命令生成 create index index_name on table_name (col_name[(length)],… )
mysql> CREATE INDEX number ON guest (number(10));
mysql> SHOW INDEX FROM tbl_name [FROM db_name] #显示现有索引
mysql> repair TABLE date QUICK; #索引列相关变量变化后自动重建索引

++查询及常用函数
mysql> select t1.name, t2.salary from employee AS t1, info AS t2 where t1.name = t2.name;
mysql> select college, region, seed from tournament ORDER BY region, seed;
mysql> select col_name from tbl_name WHERE col_name > 0;
mysql> select DISTINCT …… [DISTINCT关键字可以除去重复的记录]
mysql> select DATE_FORMAT(NOW(),’%m/%d/%Y’) as DATE, DATE_FORMAT(NOW(),’%H:%m:%s’) AS TIME;
mysql> select CURDATE(),CURTIME(),YEAR(NOW()),MONTH(NOW()),DAYOFMONTH(NOW()),HOUR(NOW()),MINUTE(NOW());
mysql> select UNIX_TIMESTAMP(),UNIX_TIMESTAMP(20080808),FROM_UNIXTIME(UNIX_TIMESTAMP()); mysql> select PASSWORD(”secret”),MD5(”secret”); #加密密码用
mysql> select count(*) from tab_name order by id [DESC|ASC]; #DESC倒序/ASC正序

* 函数count,AVG,SUM,MIN,MAX,LENGTH字符长度,LTRIM去除开头的空头,RTRIM去尾部空格,TRIM(str)去除首部 尾部空格,LETF/RIGHT(str,x)返回字符串str的左边/右边x个字符,SUBSTRING(str,x,y)返回str中的x位置起至位 置y的字符mysql> select BINARY ‘ross’ IN (’Chandler’,’Joey’, ‘Ross’); #BINARY严格检查大小写

* 比较运算符IN,BETWEEN,IS NULL,IS NOT NULL,LIKE,REGEXP/RLIKE
mysql> select count(*),AVG(number_xx),Host,user from mysql.user GROUP by user [DESC|ASC] HAVING user=root; #分组并统计次数/平均值

++UNIX_TIMESTAMP(date)
返回一个Unix时间戳记(从’1970-01-01 00:00:00′GMT开始的秒数)
mysql> select UNIX_TIMESTAMP();
mysql> select UNIX_TIMESTAMP(’1997-10-04 22:23:00′);
mysql> select FROM_UNIXTIME(875996580); #根据时间戳记算出日期

++控制条件函数
mysql> select if(1<10,2,3), IF(55>100,’true’,’false’);
#IF()函数有三个参数,第一个是被判断的表达式,如果表达式为真,返回第二个参数,如果为假,返回第三个参数.
mysql> select CASE WHEN (2+2)=4 THEN “OK” WHEN (2+2)<>4 THEN ‘NOT OK’ END AS status;

++系统信息函数
mysql> select DATABASE(),VERSION(),USER();
mysql> select BENCHMARK(9999999,LOG(RAND()*PI())) AS PERFORMANACE; #一个测试mysql运算性能工具

++将wp_posts表中post_content字段中文字”old”替换为”new”
mysql> update wp_posts set post_content=replace(post_content,’old’,’new’)

++改变表结构
mysql> alter table table_name alter_spec [, alter_spec ...]
例：alter table dbname add column userid int(11) not null primary key auto_increment;
这样，就在表dbname中添加了一个字段userid，类型为int(11)。

++调整列顺序
mysql> alter table tablename CHANGE id id int(11) first;

++修改表中数据
insert [into] table_name [(column(s))] values (expression(s))
例：mysql>insert into mydatabase values(’php’,’mysql’,’asp’,’sqlserver’,’jsp’,’oracle’);
mysql> create table user select host,user from mysql.user where 1=0;
mysql> insert into user(host,user) select host,user from mysql.user;

++更改表名
命令：rename table 原表名 to 新表名;

++表的数据更新
mysql> update table01 set field04=19991022[, field05=062218] where field01=1;

++删除数据
mysql> delete from table01 where field01=3;
#如果想要清空表的所有纪录,建议用truncate table tablename而不是delete from tablename.

++SHELL提示符下运行SQL命令
$ mysql -e “show slave status\G ”

++坏库扫描修复
cd /var/lib/mysql/xxx && myisamchk playlist_block

++insert into a (x) values (’11a’)
出现: ata truncated for column ‘x’ at row 1
解决办法:
在my.ini里找到
sql-mode=”STRICT_TRANS_TABLES,NO_AUTO_Create_USER,NO_ENGINE_SUBSTITUTION”
把其中的STRICT_TRANS_TABLES,去掉,然后重启mysql就ok了

++复制表
mysql> create table target_table like source_table

++innodb支持事务
新表:create TABLE table-name (field-definitions) TYPE=INNODB;
旧表: alter TABLE table-name TYPE=INNODB;
mysql> start transaction #标记一个事务的开始
mysql> insert into….. #数据变更
mysql> ROLLBACK或commit #回滚或提交
mysql> SET AUTOCOMMIT=1; #设置自动提交
mysql> select @@autocommit; #查看当前是否自动提交

++表锁定相关
mysql> LOCK TABLE users READ; # 对user表进行只读锁定
mysql> LOCK TABLES user READ, pfolios WRITE #多表锁控制
mysql> UNLOCK TABLES; #不需要指定锁定表名字, MySQL会自动解除所有表锁定

=====一些mysql优化与管理======
++管理用命令
mysql> show variables #查看所有变量值
? max_connections 数据库允许的最大可连接数,
#需要加大max_connections可以在my.cnf中加入set-variable = max_connections=32000,可以对与下面的threads_connected值决定是否需要增大.

show status [like ....];
? threads_connected 数据库当前的连接线程数
#FLUSH STATUS 可以重置一些计数器

show processlist;
kill id;

++my.cnf配置
?Enable Slow Query Log
long_query_time=1
log-slow-queries=/var/log/mysql/log-slow-queries.log
log-queries-not-using-indexes

# mysqldumpslow -s c -t 20 host-slow.log #访问次数最多的20个sql语句
# mysqldumpslow -s r -t 20 host-slow.log #返回记录集最多的20个sql

?others
max_connections=500 #用过的最大连接数SHOW Status like ‘max_used_connection’;
wait_timeout=10 #终止所有空闲时间超过 10 秒的连接
table_cache=64 #任何时间打开表的总数
ax_binlog_size=512M #循环之前二进制日志的最大规模
max_connect_errors = 100

query_cache_size = 256M #查询缓存
#可用 SHOW STATUS LIKE ‘qcache%’;查看命中率
#FLUSH STATUS重置计数器, FLUSH QUERY CACHE清缓存

thread_cache = 40
#线程使用,SHOW STATUS LIKE ‘Threads_created %’; 值快速增加的话考虑加大

key_buffer = 16M
#show status like ‘%key_read%’; Key_reads 代表命中磁盘的关键字请求个数
#A: 到底 Key Buffer 要设定多少才够呢? Q: MySQL 只会 Cache 索引(*.MYI)，因此参考所有 MYI文件的总大小

sort_buffer_size = 4M #查询排序时所能使用的缓冲区大小,每连接独享4M
#show status like ‘%sort%’; 如sort_merge_passes很大，就表示加大

sort_buffer_sizesort_buffer_size = 6M #查询排序时所能使用的缓冲区大小,这是每连接独享值6M
read_buffer_size = 4M　#读查询操作所能使用的缓冲区大小
join_buffer_size = 8M #联合查询操作所能使用的缓冲区大小
skip-locking #取消文件系统的外部锁
skip-name-resolve
thread_concurrency = 8　　#最大并发线程数,cpu数量*2
long_query_time = 10 #Slow_queries记数器的查询时间阀值

标签: , , , 3 评论
1710月/11关

完全认识MySQL数据库中Show命令用法

发布在 邵珠庆

MySQL中有很多的基本命令，show命令也是其中之一，在很多使用者中对show命令的使用还容易产生混淆，本文汇集了show命令的众多用法。

a. show tables或show tables from database_name; -- 显示当前数据库中所有表的名称。

b. show databases; -- 显示mysql中所有数据库的名称。

c. show columns from table_name from database_name; 或show columns from database_name.table_name; -- 显示表中列名称。

d. show grants for user_name; -- 显示一个用户的权限，显示结果类似于grant 命令。

e. show index from table_name; -- 显示表的索引。

f. show status; -- 显示一些系统特定资源的信息，例如，正在运行的线程数量。

g. show variables; -- 显示系统变量的名称和值。

h. show processlist; -- 显示系统中正在运行的所有进程，也就是当前正在执行的查询。大多数用户可以查看他们自己的进程，但是如果他们拥有process权限，就可以查看所有人的进程，包括密码。

i. show table status; -- 显示当前使用或者指定的database中的每个表的信息。信息包括表类型和表的最新更新时间。

j. show privileges; -- 显示服务器所支持的不同权限。

k. show create database database_name; -- 显示create database 语句是否能够创建指定的数据库。

l. show create table table_name; -- 显示create database 语句是否能够创建指定的数据库。

m. show engies; -- 显示安装以后可用的存储引擎和默认引擎。

n. show innodb status; -- 显示innoDB存储引擎的状态。

o. show logs; -- 显示BDB存储引擎的日志。

p. show warnings; -- 显示最后一个执行的语句所产生的错误、警告和通知。

q. show errors; -- 只显示最后一个执行语句所产生的错误。

r. show [storage] engines; --显示安装后的可用存储引擎和默认引擎。

 

查整个库的状态：

select concat(truncate(sum(data_length)/1024/1024,2),’MB’) as data_size, concat(truncate(sum(max_data_length)/1024/1024,2),’MB’) as max_data_size, concat(truncate(sum(data_free)/1024/1024,2),’MB’) as data_free, concat(truncate(sum(index_length)/1024/1024,2),’MB’) as index_size from information_schema.tables where TABLE_SCHEMA = ‘databasename’;

查数据库所有表信息：

SELECT * FROM `information_schema`.`TABLES` WHERE `TABLE_SCHEMA`='数据库名‘;

查单表：

select concat(truncate(sum(data_length)/1024/1024,2),’MB’) as data_size, concat(truncate(sum(max_data_length)/1024/1024,2),’MB’) as max_data_size, concat(truncate(sum(data_free)/1024/1024,2),’MB’) as data_free, concat(truncate(sum(index_length)/1024/1024,2),’MB’) as index_size from information_schema.tables where TABLE_NAME = ‘tablename’;

标签: , , , , , 26 评论
1210月/11关

Mysql导出表结构及表数据 mysqldump用法

发布在 邵珠庆

命令行下具体用法如下：  mysqldump -u用戶名 -p密码 -d 數據库名 表名 脚本名;

    1、导出數據库為dbname的表结构（其中用戶名為root,密码為dbpasswd,生成的脚本名為db.sql）
    mysqldump -uroot -pdbpasswd -d dbname >db.sql;

    2、导出數據库為dbname某张表(test)结构
    mysqldump -uroot -pdbpasswd -d dbname test>db.sql;

    3、导出數據库為dbname所有表结构及表數據（不加-d）
    mysqldump -uroot -pdbpasswd  dbname >db.sql;

    4、导出數據库為dbname某张表(test)结构及表數據（不加-d）
    mysqldump -uroot -pdbpasswd dbname test>db.sql;

 

mysql查表大小

查整个库的状态：
select concat(truncate(sum(data_length)/1024/1024,2),'MB') as data_size,
concat(truncate(sum(max_data_length)/1024/1024,2),'MB') as max_data_size,
concat(truncate(sum(data_free)/1024/1024,2),'MB') as data_free,
concat(truncate(sum(index_length)/1024/1024,2),'MB') as index_size
from information_schema.tables where TABLE_SCHEMA = 'databasename';

查单表：
select concat(truncate(sum(data_length)/1024/1024,2),'MB') as data_size,
concat(truncate(sum(max_data_length)/1024/1024,2),'MB') as max_data_size,
concat(truncate(sum(data_free)/1024/1024,2),'MB') as data_free,
concat(truncate(sum(index_length)/1024/1024,2),'MB') as index_size
from information_schema.tables where TABLE_NAME = 'tablename';

标签: , , , , , 17 评论
59月/11关

MySQL字段类型

发布在 邵珠庆

MySQL字段类型

按下列约定给出类型名说明：
[ ] 可选信息。
M 最大显示宽度。除非另有说明，否则M 应该是一个1到255 之间的整数。
D 有小数部分的类型的小数位数。D 为一个0 到30 之间的整数。D 应该小于等于M - 2。否则，M 的值将调整为D + 2。
在ODBC 术语中，M 和D 称为“精度”和“小数位数”。
为了描述每种类型，提供下列一个或多个信息：
说明 该类型的简短说明。
允许的属性 在CREATE TABLE 或ALTER TABLE 语句中与该类型相关的可选属性关键字。属性按字母顺序列出，但这个顺序并不一定与CREATE TABLE 或ALTE R TABLE 中使用的顺序相对应。关于这些语句的语法请参阅附录D。每种列类型在说明中列出的属性为通用属性之外的属性。
允许的长度 串类型的列值所允许的最大长度。
取值范围 对于数值或日期与时间类型，该类型可表示的值的范围。对于整数数值类型，给出两个取值范围，因为整数列可以是有符号或无符号的，有符号或无符号的取值范围是不同的。
零值 对于日期和时间类型，如果将非法值插入该列，则存入一个“零”值。
缺省值 类型说明中不明确给出D E FA U LT 属性时的缺省值。
存储需求 存储该类型的值所需的字节数。对于有的类型，这个值是固字的。而这个值在有些类型中根据存放在列中的值的不同是可变的。
比较 对于串类型，比较是否是区分大小写的。这对排序和索引有效，因为这些操作根据比较进行的。
同义词 该类型名的同义词。
注释 关于该类型的一些杂项说明。
通用属性 有的属性对所有类型或对于几乎所有类型都适用。它们列在这里，而不是列在每种类型的说明中。可对任何类型指定NULL 或NOT NULL 属性。还可对所有类型指定D E FA U LT default_value。

数值类型

 MySQL提供了整数和浮点数的数值类型。可根据所要表示的值的范围选择相应的类型。
对于整数类型，如果指定了AUTO_INCREMENT 属性，则列必须为PRIMARY KEY 或UNIQUE 索引。将NULL 插入AUTO_INCREMENT 列将会插入一个大于该列中当前最大值的值。如果指定了UNSIGNED 属性，则相应的整数类型不允许取负值。
 如果指定了ZEROFILL 属性，则用前导零填充数值类型值以达到列的显示宽度。

1. TINYINT[(M)]

说明：非常小的整数
允许的属性：AUTO _ I N C R E M E N T, UNSIGNED, ZEROFILL
取值范围：－128 到127（－27 到27－1），或者如果为U N S I G N E D，则0 到2 5 5（0 到2 8-1）
 缺省值：如果列可为NULL；则为NULL；如果列为NOT NULL，则为0
 存储需求：1字节

2. SMALLINT[(M)]

说明：小整数
允许的属性：AUTO _ I N C R E M E N T, UNSIGNED, ZEROFILL
取值范围：－32768 到3 27 6 7（－215 到215－1），或者如果为U N S I G N E D，则0 到6 5 5 3 5 （0 到216－1）
 缺省值：如果列可为NULL；则为NULL；如果列为NOT NULL，则为0
 存储需求：2 字节

3. MEDIUMINT[(M)]

 说明：中等大小的整数
 允许的属性：AUTO _ I N C R E M E N T, UNSIGNED, ZEROFILL
 取值范围：-8388608 到8 3 8 8 6 07（－22 3 到22 3－1），或者如果为U N S I G N E D，则0 到16 7 7 2 15（0 到22 4－1）
 缺省值：如果列可为NULL，则为NULL；如果列为NOT NULL，则为0
存储需求：4 字节

4. INT[(M)]

说明：标准大小的整数
 允许的属性：AUTO _ I N C R E M E N T, UNSIGNED, ZEROFILL
 取值范围：－2147483648 到2 14 7 4 8 3 6 4 7（－231到2 3 1－1），或者如果为U N S I G N E D，则0 到4 2 9 4 9 6 7 2 9 5（0 到2 3 2－1）
缺省值：如果列为NULL，则为NULL；如果列为NOT FULL，则为0
 存储需求：4 字节
同义词：I N T E G E R [(M)]

5. BIGINT[(M)]

 说明：大整数
允许的属性：AUTO _ I N C R E M E N T, UNSIGNED, ZEROFILL
取值范围：－9223372036854775808 到9 2 2 3 3 7 2 0 3 6 8 5 4 7 7 5 8 07（－263 到2 6 3－1），或者如果为U N S I G N E D，则0 到18 4 4 6 7 4 4 07 3 7 0 9 5 5 16 15（0 到26 4 -1）
缺省值：如果列可为NULL，则为NULL；如果列为NOT NULL，则为0
存储需求：8 字节

 6. FLOAT [(M, D)]

 说明：小浮点数；单精度（精度小于D O U B L E）
允许的属性：Z E R O F I L L
取值范围：最小非零值为±1.75494351E -38; 最大非零值为±3 . 4 0 2 8 2 3 4 6 6 E + 3 8
缺省值：如果列可为NULL，则为NULL；如果列为NOT NULL，则为0
存储需求：4 字节
同义词：MySQL3.23 版以前，FLOAT(4) 为具有缺省M 和D 值的FLOAT的同义词。
 注释：在MySQL3.23 以后，FLOAT(4) 为真浮点类型（值存储为硬件所允许的完整精度，而不四舍五入为缺省的小数位数）

 7. DOUBLE[(M, D)]

 说明：大浮点数；双精度（比FLOAT更精确）
 允许的属性：Z E R O F I L L
取值范围：最小非零值为±2 . 2 2 5 07 3 8 5 8 5 07 2 0 14 E -308; 最大非零值为±1。7 9 7 6 9 3 13 - 4 8 6 2 3 15 7 E + 3 0 8
 缺省值：如果列可为NULL，则为NULL；如果列为NOT NULL，则为0
存储需求：8 字节
 同义词：DOUBLE PRECISION[(M, D)] 和R E A L [ (M, D)] 为D O U B L E [(M, D)] 的同义词。MySQL3.23 版以前，FLOAT(8) 为具有缺省M 和D 值的FLOAT的同义词。
 注释：在MySQL3.23 以后，FLOAT(8) 为真浮点类型（值存储为硬件所允许的完整精度，而不四舍五入为缺省的小数位数）。

 8. DECIMAL(M, D)

说明：存储为串的浮点数（每位数字、小数点或“-”号都占1字节）。
 允许的属性：Z E R O F I L L
取值范围：最大取值范围与DOUBLE 相同；给定DECIMAL 类型的有效取值范围由M和D 决定。如果D 为零，则列值无小数点或小数部分。
缺省值：如果列可为NULL，则为NULL，如果列为NOT NULL，则为0
存储需求：对于MySQL3.23 前的版本，为M 字节，而MySQL3.23 以后的版本，为M + 2 字节。
 同义词：N U M E R I C (M, D)
注释：在MySQL3.23 以后，M 的值为符合ANSI SQL 标准，不包括符号字符或小数点所占的字节数。

MYSQL串类型

 MySQL的串类型常用来存储文本，它不但是通用的而且还能存储任何数据。可用串类型来存储最大长度可变的值，而且可以选择在处理值时是否区分大小写。

1. CHAR(M)

说明：0 到M 字节长的定长字符串。在MySQL3.23 版以前，M 应该为一个1到255 之间的整数。而MySQL3.23 版以后，M 应该为一个0 到255 之间的整数。短于M 个字符的串存储进右边补空格。长于M 个字符的串存储时剪断为长度是M 的串。在检索值时，去掉后跟的空格。
 允许的属性：B I N A RY
允许的长度：0 到M 字节
 缺省值：如果列可为NULL，则为NULL；如果列为NOT NULL，则为“”
存储需求：M 字节
比较：不区分大小写（如果具有B I N A RY 属性，则区分大小写）

2. VARCHAR(M)

 说明：0 到M 字节长的可变长字符串。M 应该为1到255 之间的一个整数，或者自MySQL3.23 后为0 到255 之间的一个整数。存储时后跟的空格被去掉。存储时，大于M 个字符的串剪断为M 个字符。
允许的属性：B I N A RY
 允许的长度：0 到M 字节
 缺省值：如果列可为NULL，则为NULL；如果列为NOT NULL，则为“”
 存储需求：值的长度，加上1字节用来记录长度
比较：不区分大小写（如果具有B I N A RY 属性，则区分大小写）

3. TINYBLOB

 说明：小BLOB 值
允许的属性：除通用属性外无其他属性
 允许的长度：0 到2 5 5（0 到28－1）字节
缺省值：如果列可为NULL，则为NULL；如果列为NOT NULL，则为“”
存储需求：值的长度加上用于记录长度的1个字节
比较：区分大小写

4. BLOB

 说明：常规大小的BLOB 值
 允许的属性：除通用属性外无其他属性
 允许的长度：0 到6 5 5 3 5（0 到216－1）字节
 缺省值：如果列可为NULL，则为NULL；如果列为NOT NULL，则为“”
存储需求：值的长度加上用于记录长度的2 个字节
 比较：区分大小写

5. MEDIUMBLOB

 说明：中等大小的BLOB 值
 允许的属性：除通用属性外无其他属性
 允许的长度：0 到16 7 7 7 2 15（0 到224 -1）字节
 缺省值：如果列可为NULL，则为NULL；如果列为NOT NULL，则为“”
存储需求：值的长度加上用于记录长度的3 个字节
比较：区分大小写

 6. LONGBLOB

 说明：大BLOB 值
允许的属性：除通用属性外无其他属性
 允许的长度：0 到4 2 9 4 9 6 7 2 9 5（0 到23 2－1）字节
缺省值：如果列可为NULL，则为NULL；如果列为NOT NULL，则为“”
存储需求：值的长度加上用于记录长度的4 个字节
比较：区分大小写

 7. TINYTEXT

说明：小TEXT 值
允许的属性：除通用属性外无其他属性
 允许的长度：0 到2 5 5（0 到2 8 -1）字节
 缺省值：如果列可为NULL，则为NULL；如果列为NOT NULL，则为“”
存储需求：值的长度加上用于记录长度的2 个字节
比较：不区分大小写

8. TEXT

说明：常规大小的TEXT 值
允许的属性：除通用属性外无其他属性
 允许的长度：0 到6 5 5 3 5（0 到216－1）字节
缺省值：如果列可为NULL，则为NULL；如果列为NOT NULL，则为“”
存储需求：值的长度加上用于记录长度的2 个字节
比较：不区分大小写

 9. MEDIUMTEXT

说明：中等大小的TEXT 值
允许的属性：除通用属性外无其他属性
允许的长度：0 到16 7 7 7 2 15（0 到22 4－1）字节
 缺省值：如果列可为NULL，则为NULL；如果列为NOT NULL，则为“”
存储需求：值的长度加上用于记录长度的3 个字节
比较：不区分大小写

 10. LONGTEXT

说明：大TEXT 值
允许的属性：除通用属性外无其他属性
允许的长度：0 到4 2 9 4 9 6 7 2 9 5（0 到23 2－1）字节
缺省值：如果列可为NULL，则为NULL；如果列为NOT NULL，则为“”
存储需求：值的长度加上用于记录长度的4 个字节
比较：不区分大小写

11. ENUM(“v a l ue 1”, “v a l ue 2”, ...)

说明：枚举，列值可赋予值列表中的某个成员
 允许的属性：除通用属性外无其他属性
缺省值：如果列可为NULL，则为NULL；如果列为NOT NULL，则为第一个枚举值
 存储需求：对1到255 个成员的枚举1个字节，对255 到65535 个成员的枚举2 个字节
比较：不区分大小写（MySQL3.22.1版以前区分大小写）

 12. SET(“v a l ue 1”,“v a l ue 2”, ...)

说明：集合，列值可赋予值列表中的零个或多个成员
 允许的属性：除通用属性外无其他属性
 缺省值：如果列可为NULL，则为NULL；如果列为NOT NULL，则为“”（空集）
存储需求：1字节（1到8个成员的集合），2字节（9 到16个成员的集合），3字节（17到2 4个成员的集合），4 字节（2 5到3 2个成员的集合）或8字节（3 3到6 4个成员的集合）
 比较：不区分大小写（MySQL3.22.1版以前区分大小写）

MYSQL日期和时间类型

MySQL提供以各种形式表示时间数据的类型。这些数据有日期和时间类型。有一种特殊的时间戳类型，它在记录更改时自动更新。还有一种用于存储年份的类型，在不需要完全的日期时使用。

 1. DATE

说明：“Y Y Y Y- M M - D D”格式的日期
 允许的属性：除通用属性外无其他属性
取值范围：“10 0 0 - 0 1- 0 1”到“9 9 9 9 - 12 - 3 1”
 零值：“0 0 0 0 - 0 0 - 0 0”
 缺省值：如果列可为NULL，则为NULL；如果列为NOT NULL，则为“0 0 0 0 - 0 0 - 0 0”
存储需求：3 字节（MySQL3.22 版以前为4 字节）

 2. TIME

 说明：“H H : M M : S S”格式的时间（负值为“ - HH:MM:SS”）；表示占用的时间，但也可以作为日常时间
 允许的属性：除通用属性外无其他属性
 取值范围：“- 8 3 8 : 5 9 : 5 9”到“8 3 8 : 5 9 : 5 9”
零值：“0 0 : 0 0 : 0 0”
缺省值：如果列可为NULL，则为NULL；如果列为NOT NULL，则为“0 0 : 0 0 : 0 0”
存储需求：3 字节
注释：虽然在非法值插入TIME 列时，“0 0 : 0 0 : 0 0”用作零值，但它也作为一个合法的值出现在正常的取值范围内

3. DATETIME

 说明：以“Y Y Y Y-MM-DD hh:mm:ss”格式表示的日期和时间（两个部分都需要）
 允许的属性：除通用属性外无其他属性
 取值范围：“1000-01-0100:00:00”到“9999-12-3123:59:59”
零值：“0000-00-00 00:00:00”
缺省值：如果列可为NULL，则为NULL；如果列为NOT NULL，则为“ 0 0 0 0 - 0 0 - 0 0 0 0 : 0 0 : 0 0”
存储需求：8 字节

4. TIMESTAMP [(M)]

 说明：以YYYYMMDDHHMMSS 格式表示的时间（日期和时间）
允许的属性：除通用属性外无其他属性
取值范围：19700101000000 到2037 年的某个时刻
 缺省值：当前日期和时间。注意DESCRIBE 和SHOW COLLUMNS 报告缺省值为NULL
存储需求：4 字节
注释：插入NULL 到表的第一个T I M E S TAMP 列将会插入当前日期和时间。更改行中其他列的值使第一个T I M E S TAMP 列被更新为这个修改的日期和时间。在内部计算中存储和使用的值全都为14 位字符精度，与显示宽度无关。如果指定了NOT NULL属性，则此属性不起作用

5. YEAR

 说明：YYYY 格式表示的年份
 允许的属性：除通用属性外无其他属性
取值范围：1900 到2 15 5
零值：0 0 0 0
 缺省值：如果列可为NULL，则为NULL；如果列为NOT NULL，则为0 0 0 0
 存储需求：1字节
注释：YEAR 是在MySQL3.22 版中引入的
MYSQL
 

标签: , , 8 评论
22月/10关

Mysql数据库分组

发布在 邵珠庆

截取字段 进行数据库分组

SELECT `ts_time`,count(`ts_time`) FROM `inform_analysis` where `ts_time`>='2010-01-01' and `ts_time`<='2010-02-23' group by substring(`ts_time`,1,10)

linux批量查找文件内容

比如查找当前目录下面所有的PHP文件里面的内容

find ./ -name "*.php" | xargs grep '要查找的内容'

如果需要查找的内容包含特殊符号，比如$等等，grep要加参数

find ./ -name "*.php" | xargs grep -F '要查找的内容'

标签: , , 没有评论
311月/08关

Oracle，SQL Server，MySQL的自增变量设置

发布在 邵珠庆

　Oracle，SQL Server，MySQL的自增变量设置：　　1、MySQL的自增变量是比较好记的，使用AUTO_INCREMENT关键字，如果知道英文的就容易记忆了，如下创建一个带有自增变理的表：

create table test（id int AUTO_INCREMENT

primary key not null，name varchar（50））；

注释：此处的id一定要申明为主键，否则会报错。

2、SQl Server使用identity关键字，可以很容易指定从什么数开始，增幅是多少，如下：

create table test（id int identity（100，10）

primary key not null，name varchar（50））；

3、Oracle不能够在创建表的时候指定自动关键字，它需要重新创建sequence，然后以"创建键。nextval"来引用：

create table test（id int primary key

not null，name varchar（50））；

create sequence test_id（最好是表名+序列号标记）

increment by 1 start with 1 maxvalue 9999；

引用如下：

insert into test（test_id.nextval，'www'）；

标签: , , , , , 没有评论
 « 上一页  

支持博主

关于邵珠庆博客

文章标签

API GA Git Google Analytics HTML5 jquery JS Linux MySQL nginx PHP SaaS web 互联网 代码 使用 分析 创业 原理 命令 好东西 如何 工具 常用 开发 总结 技巧 技术 插件 数据 文件 方法 时间 架构 框架 汇总 百度 管理 系统 网站 网站分析 设计 详解 问题 阿里云

记事邵珠庆博客

1、Web前端网址导航
2、邵珠庆极客筛站
3、识别移动设备访问代码

竹磬网-了解慈善捐款计划

最近文章

最近评论

博客日历

2025年10月
一 二 三 四 五 六 日
« 8月    
 12345
6789101112
13141516171819
20212223242526
2728293031  

目录分类

文章归档

博客功能

京东好物