快一步收藏 mysql数据库常用语法解析

MySQL是一种开放源代码的关系型数据库管理系统(RDBMS),MySQL数据库系统使用最常用的数据库管理语言--结构化查询语言(SQL)进行数据库管理。

由于MySQL是开放源代码的,因此任何人都可以在General Public License的许可下下载并根据个性化的需要对其进行修改。MySQL因为其速度、可靠性和适应性而备受关注。大多数人都认为在不需要事务化处理的情况下,MySQL是管理内容最好的选择。

MySQL这个名字,起源不是很明确。一个比较有影响的说法是,基本指南和大量的库和工具带有前缀“my”已经有10年以上,而且不管怎样,MySQL AB创始人之一的Monty Widenius的女儿也叫My。这两个到底是哪一个给出了MySQL这个名字至今依然是个迷,包括开发者在内也不知道。

MySQL的海豚标志的名字叫“sakila”,它是由MySQL AB的创始人从用户在“海豚命名”的竞赛中建议的大量的名字表中选出的。获胜的名字是由来自非洲斯威士兰的开源软件开发者Ambrose Twebaze提供。根据Ambrose所说,Sakila来自一种叫SiSwati的斯威士兰方言,也是在Ambrose的家乡乌干达附近的坦桑尼亚的Arusha的一个小镇的名字。

MySQL,虽然功能未必很强大,但因为它的开源、广泛传播,导致很多人都了解到这个数据库。它的历史也富有传奇性。

MySQL的历史最早可以追溯到1979年,那时Oracle也才小打小闹,微软的SQL Server影子都没有。有一个人叫Monty Widenius, 为一个叫TcX的小公司打工,并用BASIC设计了一个报表工具,可以在4M主频和16KB内存的计算机上运行。过了不久,又将此工具,使用C语言重写,移植到Unix平台,当时,它只是一个很底层的面向报表的存储引擎。这个工具叫做Unireg。

可是,这个小公司资源有限,Monty天赋极高,面对资源有限的不利条件,他反而更能发挥潜能,总是力图写出最高效的代码。并因此养成了习惯。与Monty同在一起的还有一些别的同事,很少有人能坚持把那些代码持续写到20年后,而Monty却做到了。

1990年,TcX的customer 中开始有人要求要为它的API提供SQL支持,当时,有人想到了直接使用商用数据库算了,但是Monty觉得商用数据库的速度难令人满意。于是,他直接借助于mSQL的代码,将它集成到自己的存储引擎中。但不巧的是,效果并不太好。于是, Monty雄心大起,决心自己重写一个SQL支持。

1996年,MySQL 1.0发布,只面向一小拨人,相当于内部发布。到了96年10月,MySQL 3.11.1发布。最开始,只提供了Solaris下的二进制版本。一个月后,Linux版本出现了。

紧接下来的两年里,MySQL依次移植到各个平台下。它发布时,采用的许可策略,有些与众不同:允许免费商用,但是不能将MySQL与自己的产品绑定在一起发布。如果想一起发布,就必须使用特殊许可,意味着要花银子。当然,商业支持也是需要花银子的。其它的,随用户怎么用都可以。这种特殊许可为MySQL带来了一些收入,从而为它的持续发展打下了良好的基础。(细想想,PostgreSQL曾经有几年限入低谷,可能与它的完全免费,不受任何限制有关系)。

MySQL3.22应该是一个标志性的版本,提供了基本的SQL支持。

MySQL关系型数据库于1998年1月发行第一个版本。它使用系统核心提供的多线程机制提供完全的多线程运行模式,提供了面向C、C++、Eiffel、Java、Perl、PHP、Python以及Tcl等编程语言的编程接口(APIs),支持多种字段类型并且提供了完整的操作符支持查询中的SELECT和WHERE操作。

MySQL是开放源代码的,因此任何人都可以在General Public License的许可下下载并根据个性化的需要对其进行修改。MySQL因为其速度、可靠性和适应性而备受关注。

2000年4月,MySQL对旧的存储引擎进行了整理,命名为MyISAM。同时,2001年,Heikiki Tuuri向MySQL提出建议,希望能集成他们的存储引擎InnoDB,这个引擎同样支持事务处理,还支持行级锁。

MySQL数据库的导入的两种方法

1) 先导出数据库SQL脚本,再导入;

2) 直接拷贝数据库目录和文件。

在不同操作系统或MySQL版本情况下,直接拷贝文件的方法可能会有不兼容的情况发生。

所以一般推荐用SQL脚本形式导入。下面分别介绍两种方法。

2. SQL脚本形式

操作步骤:

2.1. 导出SQL脚本

在原数据库服务器上,可以用phpMyAdmin工具,或者mysqldump命令行,导出SQL脚本。

2.1.1 用phpMyAdmin工具

导出选项中,选择导出“结构”和“数据”,不要添加“DROP DATABASE”和“DROP TABLE”选项。

选中“另存为文件”选项,如果数据比较多,可以选中“gzipped”选项。

将导出的SQL文件保存下来。

2.1.2 用mysqldump命令行

命令格式

mysqldump -u 用户名 -p 数据库名 > 数据库名.sql

范例:

mysqldump -u root -p abc > abc.sql

(导出数据库abc到abc.sql文件)

提示输入密码时,输入该数据库用户名的密码。

2.2. 创建空的数据库

通过主控界面/控制面板,创建一个数据库。假设数据库名为abc,数据库全权用户为abc_f。

2.3. 将SQL脚本导入执行

同样是两种方法,一种用phpMyAdmin(mysql数据库管理)工具,或者mysql命令行。

2.3.1 用phpMyAdmin工具

从控制面板,选择创建的空数据库,点“管理”,进入管理工具页面。

在"SQL"菜单中,浏览选择刚才导出的SQL文件,点击“执行”以上载并执行。

注意:phpMyAdmin对上载的文件大小有限制,php本身对上载文件大小也有限制,如果原始sql文件

比较大,可以先用gzip对它进行压缩,对于sql文件这样的文本文件,可获得1:5或更高的压缩率。

gzip使用方法:

# gzip xxxxx.sql

得到

提示输入密码时,输入该数据库用户名的密码。

3 直接拷贝

如果数据库比较大,可以考虑用直接拷贝的方法,但不同版本和操作系统之间可能不兼容,要慎用。

3.1 准备原始文件

用tar打包为一个文件

3.2 创建空数据库

3.3 解压

在临时目录中解压,如:

cd /tmp

3.4 拷贝

将解压后的数据库文件拷贝到相关目录

cd mydb/

cp * /var/lib/mysql/mydb/

对于FreeBSD:

cp * /var/db/mysql/mydb/

3.5 权限设置

将拷贝过去的文件的属主改为mysql:mysql,权限改为660

chown mysql:mysql /var/lib/mysql/mydb/*

chmod 660 /var/lib/mysql/mydb/*

Mssql转换mysql的方法

1.导表结构

使用MySQL生成create脚本的方法。找到生成要导出的脚本,按MySQL的语法修改一下到MySQL数据库中创建该表的列结构什么的。

2.导表数据

在MSSQL端使用bcp导出文本文件:

其中”"中是要导出的sql语句,-c指定使用\t进行字段分隔,使用\n进行记录分隔,-S指定数据库服务器及实例,-U指定用户名,-P指定密码.

在MySQL端使用mysqlimport 导入文本文件到相应表中

mysqlimport -uroot -p databasename /home/test/tablename.txt

其中-u指定用户名,-p指定密码,databasename指定数据库名称,表名与文件名相同

常用命令

1:使用SHOW语句找出在服务器上当前存在什么数据库:

mysql> SHOW DATABASES;

2:2、创建一个数据库MYSQLDATA

mysql> CREATE DATABASE MYSQLDATA;

3:选择你所创建的数据库

mysql> USE MYSQLDATA; (按回车键出现Database changed 时说明操作成功!)

4:查看现在的数据库中存在什么表

mysql> SHOW TABLES;

5:创建一个数据库表

mysql> CREATE TABLE MYTABLE (name VARCHAR(20), sex CHAR(1));

6:显示表的结构:

mysql> DESCRIBE MYTABLE;

7:往表中加入记录

mysql> insert into MYTABLE values (”hyq”,”M”);

8:用文本方式将数据装入数据库表中(例如D:/mysql.txt)

mysql> LOAD DATA LOCAL INFILE “D:/mysql.txt” INTO TABLE MYTABLE;

9:导入.sql文件命令(例如D:/mysql.sql)

mysql>use database;

mysql>source d:/mysql.sql;

10:删除表

mysql>drop TABLE MYTABLE;

11:清空表

mysql>delete from MYTABLE;

12:更新表中数据

mysql>update MYTABLE set sex=”f” where name=’hyq’;

全局管理权限对应解释:

FILE: 在MySQL服务器上读写文件。

PROCESS: 显示或杀死属于其它用户的服务线程。

RELOAD: 重载访问控制表,刷新日志等。

SHUTDOWN: 关闭MySQL服务。

数据库/数据表/数据列权限:

ALTER: 修改已存在的数据表(例如增加/删除列)和索引。

CREATE: 建立新的数据库或数据表。

DELETE: 删除表的记录。

DROP: 删除数据表或数据库。

INDEX: 建立或删除索引。

INSERT: 增加表的记录。

SELECT: 显示/搜索表的记录。

UPDATE: 修改表中已存在的记录。

特别的权限:

ALL: 允许做任何事(和root一样)。

USAGE: 只允许登录–其它什么也不允许做。

部分常用语法讲解

JSON和MONGODB

JSON不止是一种交换数据的方式,也是一种存储数据的良好方式,实际上MONGODB并未使用JSON存储数据,而是使用由MONGODB团队开发的一种称为BSON的开放数据格式。

面向文档存储BSON

BSON是一个开放标准,BSON存储使用的空间比JSNO(CouchDB一个强大的面向文档数据库)多在相同版本情况下。

BSON的优势在于

1、处理数据比JSON快,消耗一定的存储空间,简单说BSON更方便浏览,遍历索引页非常快。

2、使用BSON容易将它数据快速转换为编程余元的原生数据格式

3、BSON也提供对JSON的一些扩展,通过BSON可以存储二进制数据,以及处理特定的数据类型,因此,BSON可以存储任何JSON文档,但有效的BSON文档可能不是有效的JSON

浏览数据库

use testDB //切换已有数据库或者创建新的数据库

查看可用的数据库和集合

show dbs //只会显示出已经存在的数据库

show collections //显示当前数据库中的所有集合

集合中插入数据

最常用的操作就是在集合中插入数据,所有数据都以BSON格式存储,插入数据可以先定义数据,然后使用insertOne函数将它们保存在集合中,或使用insert函数临时输入文档内容。

douctment=({"key1":"values"})

插入数据时,键名必须遵守如下规则:

$字符不能是键名的第一个字符

圆点[.]不能出现在键中

名称_id被保留使用

集合的名称不能超过128个字符

空字符串("")不能用作集合名称

集合名必须以字母或下划线开头

集合名SYSTEM被MONGDB保留,不能使用

集合名不能包含null字符"\0"

全部数据查询

db.testDB.find() //结果显示其中的所有文档

获取特定类型的文档

db.testDB.find({"key1":"values"})

获取values相关的数据标题,忽略其他

db.testDB.find({"key1":"values"},{"Tilte":1})

使用函数sort、limit和skip

db.testDB.find().sort({"Tilte":1}) //按键的结果进行升序,若-1为降序

db.testDB.find().limit(10) //取文档前N个结果数目

db.testDB.find().skip(20) //跳过文档的前N个数据

组合使用这些函数

db.testDB.find().sort({"Tilte":1}).limit(10).skip(10)

在MONGODB中使用查询时,还需要注意一些额外的概念和特性,包括固定集合、自然顺序和$natural

自然顺序:是数据库中结合的原生排序方法,所以如果在查询集合中的文档时,如果没有显示指定排序顺序,结果将默认按照前向自然顺序返回。

固定集合:是数据库的一种集合,它的自然顺序保证与文档插入的顺序一致,保证自然顺序一直与文档插入顺序一致,另一优点是集合的大小固定,最老的数据将被删除,最新的数据将被添加到末端,保证自然顺序与文档插入的顺序一致

固定集合必须使用createCollection函数,如创建名为auth的固定集合

db.createCollection("auth":)

可以使用max限制固定集合中的文档数量

db.createCollection("auth":)

$natural:鉴于固定集合保证了自然顺序与插入顺序一致,查询时不需要再使用任何特殊的参数、任何其他特殊的命令或函数,如查找最近的10条数据

db.testDB.find().sort({$natural:-1}).limit(10)

查看集合的大小

db.testDB.stats()

获取单个文档

db.testDB.findOne()

使用聚集命名

count()函数返回文档的数目

db.testDB.count() //指定集合中的文档数目

执行额外的过滤统计

db.testDB.find({"key1":"values"},{"Tilte":1}).count() //count()函数默认将忽略skip()或limit()参数,若不想被忽略需要使用count(true)

db.testDB.find({"key1":"values"},{"Tilte":1}).limit(10).count(true)

使用distinct函数获取唯一值

db.testDB.distinct({"Tilte"})

将结果分组group()

该命令目的是返回一个已分组元素的数组,函数group()接受3个参数:key,initial和reduce,但在分片环境中无法正常工作

参数key指定希望使用哪个键对结果进行分组。

参数initial允许为每个已分组的结果提供基数(元素开始开始统计的起始基数),如果希望返回指定的数字,参数默认为0

参数reduce把所有类似的条目分组在一起,它接受2个参数:items和pre

db.testDB.group(

key:{"Tilte":true},

initial:,

reduct:function(items,pre){

prev.Total+=1

}

)

使用条件操作符

$gt大于参数

db.testDB.find({"key1":{$gt:2000}})

$gte大于或等于 $lt小于 $lte小于等于 $ne 不等于

指定一个匹配的数组

db.testDB.find({"key1":{$in:[1,2,3,4]}}) //类似的又$nin

匹配文档所有属性$all

db.testDB.find({"key1":{$all:[1,2,3,4]}})

在文档中搜索多个表达

db.testDB.find({$or:[{"key1":"values1"},{"key2":"values2"}]}) //$nor

使用$slice获取文档

db.testDB.find({"key1":"values1"},{"Cast":{$slice:3}}) //获取前3项,负数为后N个

类似LIMT n,m

搜索奇数和偶数$MOD

db.testDB.find({"key1":"values1"},{"Cast":{$mod:[2,0]}})

使用$size过滤结果:过滤出文档中数组大小

db.testDB.find({"key1":{$size:2}})

返回含有特定字段的对象:$EXISTS (全表扫描,不能使用索引)

db.testDB.find({"key1":{$exists:true}})

基于bson类型匹配结果:$type可以基于BSON类型匹配结果:

db.testDB.find({"key1":{$type:3}})

匹配完整的数组 $elemMatch操作符

使用正则表达式

db.testDB.find({"key1":/^Sharesoe*/i})

更新数据

使用update()更新操作,该函数接受3个主要参数:criteria、objNew和options

参数criteria可用于指定一个查询

参数objNew指定更新信息

参数options用于指定更新文档时的选项,有upsert和multi,upsert有更新的就更新无更新就就创建,multi可以指定是否应该更新所有匹配的文档或只更新第一个文档

db.testDB.updateOne({"key1":"values"},{"Tilte":1},) //可以使用save()命令实现upsert

db.testDB.save({"key1":"values"},{"Tilte":1})

##此更新会导致多余的key-value被删除

使用$inc增加值

db.testDB.updateOne({"key1":"values"},{$inc:{"Tilte":5}})

更新指定的值

db.testDB.updateOne({"key1":"values"},{$set:{"Tilte":"sharesoe.com"})

删除指定字段

db.testDB.updateOne({"key1":"values"},{$unset:{"Tilte":1})

在指定字段中添加某个值$push,或多个值用$each

db.testDB.updateOne({"key1":"values"},{$push:{"Tilte2":"haha"}})

db.testDB.updateOne({"key1":"values"},{$push:{"Tilte2":{$each:["haha",1,2,3]}}})

数组中添加数据

db.testDB.updateOne({"key1":"values"},{$addToSet:{"Tilte2":{$each:["haha",1,2,3]}}})

数组中删除元素

db.testDB.updateOne({"key1":"values"},{$pop:{"Tilte2":1}}) //正数是最后一个元素

删除数组中指定值、多个值

db.testDB.save({"key1":"values"},{$pull:{"Tilte2":"haha"}}})

db.testDB.updateOne({"key1":"values"},{$pullAll:{"Tilte2":["haha",1,2,3]}}})

批处理数据

MONGODB数据的批处理分为有序处理和无序,有序处理数据过程发生错误后就停止剩下数据的写入,无序操作以并行方式处理,若错误会执行其他的数据

有序实现

var bulk=initializeOrderBulkOp() //初始化有序列表

插入有序列表bulk,最后执行execute()

bulk.insertOne(xx)

bulk.execute()

评估输出:执行execute()命令后,就能够审查执行写入操作,评估是否成功写入了所有数据,通过getOperations()

bulk.getOperations()

batchTYPE 1 insert 2 update 3 remove

重命名集合

db.testDB.renameCollection("newname")

删除数据一条或多条

db.newname.deleteOne({"key1":"values"}) //删除匹配一个文档

删除集合的所有文档

db.newname.drop() //removed

删除集合

db.dropDatabase()

  • 发表于:
  • 原文链接:http://kuaibao.qq.com/s/20180131A07NKN00?refer=cp_1026

同媒体快讯

相关快讯

扫码关注云+社区