分类目录归档:数据库

围绕数据库的核心-增删改查-必须要知道的东西.没了它所有的东西都是白撘.

MongoDB 常用数据库命令

mongoDB提供了广泛的数据库命令,除常用的create,read,update,delete之外所有功能。

命令是如何工作的

这里有个你比较熟悉的例子:drop,如果从shell里删除一个collection,我们运行db.test.drop().实际上,在内部执行的是drop命令,跟下边用runCommand执行的操作是一样的

ok 表示是否执行成功

实际上,mongoDB的命令被实现为一种对叫$cmd的collection的特殊查询,runCommand只是使用参数进行了一次查询,所以我们的drop也可以这样写

当mongoDB服务器接到一个对$cmd的查询时,使用一种特殊的逻辑来处理。几乎所有的驱动都提供了runCommand方法来执行命令,实际上这些命令都可以通过执行查询的方式来完成。

下边是一些最常用的命令:

  • buildInfo: {“buildInfo” : 1}, 返回mongoDB服务器版本和宿主操作系统的信息

图片例子——————–

  • collStats: {“collStats” : collection},给出指定collection的统计信息,包括数据大小,分配的存储控件,索引大小等

图片例子——————–

  • distinct: {“distinct” : collection, “key”: key, “query”: query} 返回在指定的collection里符合query条件的所有key的值
  • drop: {“drop” : collection}, 删除collection的说有数据
  • dropDatabase: {“dropDatabase” : 1}, 删除当前数据库的所有数据
  • dropIndexes: {“dropIndexes” : collection, “index” : name}, 删除collection上名字为name的索引
  • findAndModify:参见第3章
  • getLastError: {“getLastError” : 1[, “w” : w[, “wtimeout” : timeout]]}, 检查此连接上最后操作的错误或状态信息,可以指定一个选项,此命令将会阻塞直到w个salves复制了最后的那个操作或者时间超时(毫秒)
  • isMaster: {“isMaster” : 1}, 检查此服务器是master还是slave
  • listCommands: {“listCommands” : 1}, 列出此服务器上所有可用命令
  • listDatabases: {“listDatabases” : 1},列出服务器上所有数据库
  • ping: {“ping” : 1},检查服务器是否正在运行,即使服务器处于锁定状体此命令也会立即返回
  • renameCollection: {“renameCollection” : a, “to” : b}, 将collection的名字从a改为b
  • repairDatabase:{“repairDatabase” : 1}, 修复并压缩当前数据库
  • serverStatus:{“serverStatus” : 1}, 获取此服务器的管理统计信息

MySQL GRANT ALL PRIVILEGES 方法允许远程登录

GRANT ALL PRIVILEGES

1. 授权法。

例如,你想myuser使用mypassword从任何主机连接到mysql服务器的话。

以下是Sql代码

注意:root@localhost和密码mysql用的是引号

如果你想允许用户myuser从ip为192.168.1.3的主机连接到mysql服务器,并使用mypassword作为密码

Sql代码

如果你想允许用户myuser从ip为192.168.1.6的主机连接到mysql服务器的dk数据库,并使用mypassword作为密码
Sql代码

注意授权后必须FLUSH PRIVILEGES;否则无法立即生效。

例子:授权用户www对数据库onethink有select,delete,update,insert权限,登录密码为admin123

 

2. 改表法。

mysql中回收删除表的空间,优化表

REPAIR TABLE table_name 修复表
OPTIMIZE TABLE table_name 优化表
myisamchk table.MYI 修复索引
REPAIR TABLE 用于修复被破坏的表。
myisamchk TABLE.MYI 用于修复被破坏的索引文件。
OPTIMIZE TABLE 用于回收闲置的数据库空间,当表上的数据行被删除时,所占据的磁盘空间并没有立即被
回收,使用了OPTIMIZE TABLE命令后这些空间将被回收,并且对磁盘上的数据行进行重排(注意:是磁盘上
,而非数据库)。 多数时间并不需要运行OPTIMIZE TABLE,只需在批量删除数据行之后,或定期(每周一
次或每月一次)进行一次数据表优化操作即可,只对那些特定的表运行,这个操作对于游戏数据库中的某 些表
特别起作用,这些表基本上需要每周做一次优化,甚至一周两次。
OPTIMIZE [LOCAL | NO_WRITE_TO_BINLOG] TABLEtbl_name[,tbl_name] …
如果您已经删除了表的一大部分,
或者如果您已经对含有可变长度行的表(含有VARCHAR, BLOB或TEXT列的表)进行了很多更改,则应使
用OPTIMIZE TABLE。被删除的记录被保持在链接清单中,后续的INSERT操作会重新使用旧的记录位置。
您可以使用OPTIMIZE TABLE来重新利用未使用的空间,并整理数据文件的碎片。
在多数的设置中,您根本不需要运行OPTIMIZE TABLE。
即使您对可变长度的行进行了大量的更新,
您也不需要经常运行,每周一次或每月一次即可,只对特定的表运行。
OPTIMIZE TABLE只对MyISAM, BDB和InnoDB表起作用。
对于MyISAM表,OPTIMIZE TABLE按如下方式操作:
1. 如果表已经删除或分解了行,则修复表。
2. 如果未对索引页进行分类,则进行分类。
3. 如果表的统计数据没有更新(并且通过对索引进行分类不能实现修复),则进行更新。
对于BDB表,OPTIMIZE TABLE目前被映射到ANALYZE TABLE上。
对于InnoDB表,OPTIMIZE TABLE被映射到ALTER TABLE上,这会重建表。
重建操作能更新索引统计数据并释放成簇索引中的未使用的空间。

ubuntu下navicat试用到期解决办法

在ubuntu以前用数据库管理都用phpmyadmin,一个月前换成 Navicat for MySQL ;
一个月以后的今天它到期了。试用按钮不在了,不能点了。

问同事,找到答案是,

删除了system.reg文件以后,再打开navicat出现试用按钮,又能试用30天了。哈哈

 

2014年1月2日修订

如果上方法没有成功(那你一定用的是高版本的11.XXX)

请看ubuntu下navicat11.0.7到期解决办法

 

mysql中的反引号的用法

lamp技术博客说mysql,刚才给一个朋友解决一个问题
他用desc作为表名怎么也建表不成功
其实在mysql里面反引号是会经常用到的
我们建表的时候一般都会将表名,库名都加上反引号来保证语句的执行度。
反引号,数字1左边的符号。
保留字不能用于表名,比如desc,此时需要加入反引号来区别,但使用表名时可忽略反引号。
create table desc报错
create table
desc成功
create table
test成功
drop table test成功

保留字不能用于字段名,比如desc,此时也需要加入反引号,并且insert等使用时也要加上反引号。
create table
testdesc varchar(255))成功
insert into test(desc) values('fxf')失败
insert into test(
desc`) values(‘fxf’)成功

相信大家对lamp架构中的mysql中的反引号了解了。如果有兴趣可以或给lamp博客留言继续沟通

关于 MySQL LEFT JOIN 你可能需要了解的三点

即使你认为自己已对 MySQL 的 LEFT JOIN 理解深刻,但我敢打赌,这篇文章肯定能让你学会点东西!

  • ON 子句与 WHERE 子句的不同
  • 一种更好地理解带有 WHERE … IS NULL 子句的复杂匹配条件的简单方法
  • Matching-Conditions 与 Where-conditions 的不同

关于 “A LEFT JOIN B ON 条件表达式” 的一点提醒

ON 条件(“A LEFT JOIN B ON 条件表达式”中的ON)用来决定如何从 B 表中检索数据行。

如果 B 表中没有任何一行数据匹配 ON 的条件,将会额外生成一行所有列为 NULL 的数据

在匹配阶段 WHERE 子句的条件都不会被使用。仅在匹配阶段完成以后,WHERE 子句条件才会被使用。它将从匹配阶段产生的数据中检索过滤。

让我们看一个 LFET JOIN 示例:

ON 子句和 WHERE 子句有什么不同?

一个问题:下面两个查询的结果集有什么不同么?

第一条查询使用 ON 条件决定了从 LEFT JOIN的 product_details表中检索符合的所有数据行。

第二条查询做了简单的LEFT JOIN,然后使用 WHERE 子句从 LEFT JOIN的数据中过滤掉不符合条件的数据行。

再来看一些示例:

所有来自product表的数据行都被检索到了,但没有在product_details表中匹配到记录(product.id = product_details.id AND product.amount=100 条件并没有匹配到任何数据)

同样,所有来自product表的数据行都被检索到了,有一条数据匹配到了。

使用 WHERE … IS NULL 子句的 LEFT JOIN

当你使用 WHERE … IS NULL 子句时会发生什么呢?

如前所述,WHERE 条件查询发生在 匹配阶段之后,这意味着 WHERE … IS NULL 子句将从匹配阶段后的数据中过滤掉不满足匹配条件的数据行。

纸面上看起来很清楚,但是当你在 ON 子句中使用多个条件时就会感到困惑了。

我总结了一种简单的方式来理解上述情况:

  • 将 IS NULL 作为否定匹配条件
  • 使用 !(A and B) == !A OR !B 逻辑判断

看看下面的示例:

让我们检查一下 ON 匹配子句:
(a.id=b.id) AND (b.weight!=44) AND (b.exist=0)
我们可以把 IS NULL 子句 看作是否定匹配条件。

这意味着我们将检索到以下行:

就像在C语言中的逻辑 AND 和 逻辑 OR表达式一样,其操作数是从左到右求值的。如果第一个参数做够判断操作结果,那么第二个参数便不会被计算求值(短路效果)

看看别的示例:

Matching-Conditions 与 Where-conditions 之战
如果你吧基本的查询条件放在 ON 子句中,把剩下的否定条件放在 WHERE 子句中,那么你会获得相同的结果。
例如,你可以不这样写:

你可以这样写:

你可以不这样写:


可以这样写:

这些查询真的效果一样?
如果你只需要第一个表中的数据的话,这些查询会返回相同的结果集。有一种情况就是,如果你从 LEFT JOIN的表中检索数据时,查询的结果就不同了。
如前所属,WHERE 子句是在匹配阶段之后用来过滤的。
例如:

总附注:
如果你使用 LEFT JOIN 来寻找在一些表中不存在的记录,你需要做下面的测试:WHERE 部分的col_name IS NULL(其中 col_name 列被定义为 NOT NULL),MYSQL 在查询到一条匹配 LEFT JOIN 条件后将停止搜索更多行(在一个特定的组合键下)。
原文链接 OSChina.NET原创翻译

mysql导入数据时 USING BTREE 错误解决办法

今天在往测试数据库导入数据时,其中一个供应商表报USING BTREE 错误:You have an error in your SQL syntax; check the
manual that corresponds to your MySQL server version for the right syntax to use
near ‘USING BTREE,UNIQUE KEY user_name (suppliers_name) USING BTREE ,
其实解决这个问题很简单。

打开要导入的文件在里面搜索 BTREE 找到如下内容

UNIQUE KEY user_name (suppliers_name) USING BTREE ,修改为

UNIQUE KEY user_name USING BTREE (suppliers_name) ,

即把USING BTREE 放到索引字段前面即可.

再次导入顺利通过了

SQL效率之truncate 、delete与drop区别

在数据库操作中避免不了要删除表中的内容,删除表的整个结构…可用方法有很多,下面来分析一下SQL效率之truncate 、delete与drop区别

相同之处:

1.truncate和不带where子句的delete、以及drop都会删除表内的数据。

2.drop、truncate都是DDL语句(数据定义语言),执行后会自动提交。

 

不同之处:

1.drop和delete只是删除表的数据(定义),drop语句将删除表的结构、被依赖的约束(constrain)、触发器 (trigger)、索引(index);依赖于该表的存储过程/函数将保留,但是变为invalid状态。

2.delete语句是DML语言,这个操作会放在rollback segement中,事物提交后才生效;如果有相应的触发器(trigger),执行的时候将被触发。truncate、drop是DDL语言,操作后即 生效,原数据不会放到rollback中,不能回滚,操作不会触发trigger。

3.delete语句不影响表所占用的extent、高水线(high watermark)保持原位置不动。drop语句将表所占用的空间全部释放。truncate语句缺省情况下将空间释放到minextents的 extent,除非使用reuse storage。truncate会将高水线复位(回到最初)。

4.效率方面:drop > truncate > delete

5.安全性:小心使用drop与truncate,尤其是在 没有备份的时候,想删除部分数据可使用delete需要带上where子句,回滚段要足够大,想删除表可以用drop,想保留表只是想删除表的所有数据、 如果跟事物无关可以使用truncate,如果和事物有关、又或者想触发 trigger,还是用delete,如果是整理表内部的碎片,可以用truncate跟上reuse stroage,再重新导入、插入数据。

6.delete是DML语句,不会自动提交。drop/truncate都是DDL语句,执行后会自动提交。

 

drop一般用于删除整体性数据 如表,模式,索引,视图,完整性限制等

delete用于删除局部性数据 如表中的某一元组

 

DROP把表结构都删了

DELETE只是把数据清掉

 

当你不再需要该表时, 用 drop;

当你仍要保留该表,但要删除所有记录时, 用 truncate;

当你要删除部分记录时(always with a WHERE clause), 用 delete.

PHP开发者常犯的10个MySQL错误

最近看到一篇文章:《PHP开发者常犯的10个MySQL错误》,发现文中不少内容陈旧,随着时间推移技术发展变化而变得不适用。为了防止误导新手,特本着与时俱进的精神写出此文,绝非对原文作者的不尊重。

  1.使用MyISAM而不是InnoDB

完全错误,反驳理由:

首先原文说MyISAM是默认使用的,而实际上到了MySQL 5.5.x,InnoDB已经成为了默认的表引擎。

另外,简单的使用InnoDB不是解决所有问题的方法,盲目的使用甚至会使应用性能下降10%乃至40%。

最佳方法还是针对具体业务具体处理,例如论坛中版块表,新闻分类表,各种码表等长时间不操作的表,还是要用性能优异的MyISAM引擎。

而需要用到事务处理的例如用户、账目、流水等严格要求数据完整性和时序性的,则需要用InnoDB引擎,并且应用也要用好事务处理机制。当然,事务处理必然要带来大量的性能损耗,但是这在简单高并发应用上是必须的。

最后,外键约束在公共web互联网应用上一般是不用的,因为他会严重影响性能。数据完整性还是靠程序员或者应用架构本身的健壮来维护。而正规的第三范式只是在企业内部MIS系统和12306这种网站上使用。

  2.使用PHP的mysql方法

不完全错,但要酌情选用:

mysqli固然好,但是不是所有的服务器都为PHP编译了mysqli的支持。

当你的应用如果是能确定只用自己部署的服务器,而应用也是完全自己开发,则mysqli是最好的选择。

但是一旦你的应用有可能部署在虚拟主机或者由其他人部署(例如分布式项目),还是老老实实使用mysql函数集吧,好好封装一下或者使用成熟框架杜绝sql注入。

  3.不过滤用户输入

这一点不用说了,要么MagicQuote,要么选用成熟框架。sql注入老话题了。

  4.不使用UTF-8

大部分情况下对,但也要认真考虑:

要知道,一个UTF-8字符占3个字节,所以比GBK等其他编码的文件大33%。换句话说,相同的网页用UTF-8编码如果是100KB,那么换成GBK编码则只有66KB。所以即便你的PHP确定要用UTF-8,那么前端页面也要根据情况选择需要的编码。但是,如果PHP用UTF-8,前端模版是GBK,再加上模版引擎不强大,那么转码工作够你受的。所以尽可能的选用自己需要的编码,而不是简单的选择UTF-8了事。

最后啰嗦一句:UTF-8下:strlen(“我”)=3,而GBK下:strlen(“我”)=2

  5.该用SQL的地方使用PHP

同样酌情考虑:

例如,有些人习惯在建表时,默认值填写CURRENT_TIMESTAMP,用来达到注册时间、发帖时间的效果。 或者在时间判断的SQL语句中,写类似SELECT x FROM tab1 WHERE regdate 正确做法是:不要使用MySQL的任何时间函数,而是在应用里计算时间。如果是分布式应用,一定要有时间服务器来统一管理时间。

而文中说的一些MySQL数学函数 ,也是要慎用。因为在大型应用中,数据库的负担往往是最大的,而复杂的WHERE语句又是造成慢查询的元凶。所以,要把计算尽可能的放在廉价的、不影响全局稳定的应用服务器上,而不是核心数据库上。

  6.不优化查询

这点也不用说了,大型应用上甚至不允许使用各种JOIN,哪怕生写两条查询,查回来在用PHP合并数据。

  7.使用错误的数据类型

INT,TinyINT,VARCHAR,CHAR,TEXT这些字段类型的合理选用无可厚非。

而Date、DateTime、TIMESTAMP这三种类型,在大型应用中是绝对不可以使用的,而是要用INT(10) UNSIGNED代替。

一个是性能,另外就是应用中尤其是PHP对UNIX_TIMESTAMP时间戳的转化实在太方便了。用Date要输出各种时间格式反而麻烦。

  8.在SELECT查询中使用*

共勉

  9.索引不足或者过度索引

索引是必须的,但是如果索引都解决不了的查询,考虑memcache或者nosql解决方案吧。

  10.不备份

这条是作者凑数么?

  11.另外:不考虑其他数据库

这条相当正确。应用中不仅要针对应用选择其他数据库,甚至还要针对具体的业务类型,在同一套应用中并行使用多种数据库。哪怕不是数据库,而是其他各种缓存、内存存储等解决方案。

强制修改MySQL的root密码的六种方法