nosql培训文档
mysql Nosql准备工作
安装以下2个插件
1. x插件(docker安装自带的有)
docker run --name mysql8 -p 3306:3306 -p 33060:33060 -p 33061:33061 -p 33062:33062 -e MYSQL_ROOT_PASSWORD=123456 -d mysql:8.0.21
show PLUGINS
2. mysql_shell
官网下载:https://dev.mysql.com/downloads/shell/
安装步骤同jdk,配置系统变量即可
连接
mysqlsh root@127.0.0.1:3306 --password=123456
这里是33060不是3306,33060才是x协议的端口号
\connect root@127.0.0.1:33060/world_x
出现X protocol表示成功了,然后我们可以使用nosql功能了
Nosql使用
其实nosql在mysql中就是一张表
集合
1. 创建集合
db.createCollection("flags");
2. 查询集合
db.getCollections();
3. 删除集合
# 删除单个集合
db.dropCollection("flags");
# 删除全部集合
db.dropCollection();
文档
1.向集合中添加文档
语法
CollectionAddFunction
::= ( '.add(' ( DocumentOrJSON | '[' DocumentOrJSON ( ',' DocumentOrJSON )* ']' )? ')' )+
( '.execute()' )?
db.flags.add({"id":1,"name":"tangxiang"})
2.查询文档
语法
CollectionFindFunction
::= '.find(' SearchConditionStr? ')' ( '.fields(' ProjectedDocumentExprStr ')' )?
( '.groupBy(' SearchExprStrList ')' )? ( '.having(' SearchConditionStr ')' )?
( '.sort(' SortExprStrList ')' )? ( '.limit(' NumberOfRows ')' ( '.offset(' NumberOfRows ')' )? )?
( '.lockExclusive(' LockContention ')' | '.lockShared(' LockContention ')' )?
( '.bind(' PlaceholderValues ')' )*
( '.execute()' )?
1. 查询所有
db.flags.find()
2. 精确查询
先mock一些数据
db.flags.add({"id":2,"name":"tangxiang","age":24,"height":178})
db.flags.add({"id":3,"name":"tang","age":28,"height":188,"habbit":"football"})
db.flags.add({"id":4,"name":"xiang","age":25,"height":168,"school":"jianghandaxue"})
db.flags.add({"id":5,"name":"zhangsan","age":22,"height":174,"school":"shisizhongxue"})
db.flags.add({"id":6,"name":"lisi","age":24,"height":178})
db.flags.add({"id":7,"name":"wangwu","age":23,"height":158})
db.flags.find("name = 'tangxiang'")
3. 筛选查询
MySQL提供以下操作符来指定搜索条件:OR(||), ( AND),&&, , XOR,IS, NOT,BETWEEN, IN,LIKE, !=,<>, >,>=, <,<=, &,|, <<,>>, +,,- , 和。 */~%
类似于sql的写法
db.flags.find("age>24")
db.flags.find("age>=24 and age <=25")
模糊查询
db.flags.find(" name like 'tan%'")
4. 子json查询
增加子级
mock数据
db.flags.add({"id":8,"name":"wangwu","houses":{"city":"wuhan","location":"tanhualin","area": "wuchangqu"}})
db.flags.add({"id":9,"name":"tangxiang","houses":{"city":"wuhan","location":"ningmengtequ","area": "hongshanqu"}})
db.flags.add({"id":10,"name":"tangxiang","houses":{"city":"wuhan","location":"changjiangzidu","area": "wuchangqu"}})
db.flags.add({"id":11,"name":"tangxiang","houses":{"city":"beijing","location":"qinghuadaxue","area": "haidingqu"}})
db.flags.find("name='tangxiang' and houses.city='wuhan'")
5. 占位符
db.flags.find("name= :queryName").bind("queryName","tangxiang")
6. 查询指定字段fields()
db.flags.find("name= :queryName").fields(["id","name"]).bind("queryName","tangxiang")
7.排序
db.flags.find("age>=25").sort(" id desc")
8.分页
limit指定显示条数
db.flags.find("age>=25").sort(" id desc").limit(1)
skip跳过条数
db.flags.find("age>=25").sort(" id desc").limit(1).skip(1)
3.删除文档
语法
CollectionRemoveFunction
::= '.remove(' SearchConditionStr ')'
( '.sort(' SortExprStrList ')' )? ( '.limit(' NumberOfRows ')' )?
( '.bind(' PlaceholderValues ')' )*
( '.execute()' )?
1.使用条件删除
db.flags.remove(" id in (8,9,10,11)")
2. 删除第一个文档
db.flags.remove("true").sort(["id desc"]).limit(1)
3.删除全部文档
先创建一个集合再删除
db.createCollection("delCol")
db.delCol.add({"id":1,"descr":"d1"})
db.delCol.add({"id":2,"descr":"d2"})
db.delCol.add({"id":3,"descr":"d3"})
db.delCol.find()
db.delCol.remove("true")
db.delCol.find()
4.修改文档
语法
正则表达式
CollectionModifyFunction
::= '.modify(' SearchConditionStr ')'
( '.set(' CollectionField ',' ExprOrLiteral ')' |
'.unset(' CollectionFields ')' |
'.arrayInsert(' CollectionField ',' ExprOrLiteral ')' |
'.arrayAppend(' CollectionField ',' ExprOrLiteral ')' |
'.arrayDelete(' CollectionField ')' |
'.patch(' DocumentOrJSON ')'
)+
( '.sort(' SortExprStrList ')' )? ( '.limit(' NumberOfRows ')' )?
( '.bind(' PlaceholderValues ')' )*
( '.execute()' )?
1.指定条件修改文档
modify指定条件,set更改值
db.flags.modify("id=1").set("name","tangxiang-modify")
db.flags.find()
2.指定条件批量更改
db.flags.modify("id=2").set("name","tangxiang-m2").set("height","189")
db.flags.find()
3.添加字段
db.flags.modify("id=2").set("addCol","addCol2")
db.flags.find("id=2")
4.删除字段
db.flags.modify("id=2").unset("addCol")
db.flags.find("id=2")
5.添加数组并追加元素
# db.flags.modify("id=2").unset("array")
db.flags.modify("id=2").set("array",[1,2])
db.flags.find("id=2")
db.flags.modify("id=2").arrayAppend("array",3)
db.flags.find("id=2")
db.flags.modify("id=2").arrayAppend("$.array",4)
db.flags.find("id=2")
6.插入数组
db.flags.modify("id=2").arrayInsert("$.array[0]",6)
db.flags.find("id=2")
7.删除数组
db.flags.modify("id=2").arrayDelete("$.array[0]")
db.flags.find("id=2")
Java集成nosql
pom.xml
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>8.0.20</version>
</dependency>
<!-- https://mvnrepository.com/artifact/com.google.protobuf/protobuf-java -->
<dependency>
<groupId>com.google.protobuf</groupId>
<artifactId>protobuf-java</artifactId>
<version>3.12.2</version>
</dependency>
java示例
import com.mysql.cj.xdevapi.*;
import org.junit.Test;
import org.springframework.util.CollectionUtils;
import java.util.List;
/**
* @author TangXiang
* @date 2020/8/3 11:51
*/
public class MysqlTest {
@Test
public void nosqlTest(){
Session mySession = new SessionFactory().getSession("mysqlx://server-com:33060/world_x?user=root&password=123456");
Schema myDb = mySession.getSchema("world_x");
String collectionName="tangxiang_collections";
/**
* 查询集合,如果不存在,创建集合
* true:如果存在,使用现有的对象
*/
Collection myColl = myDb.createCollection(collectionName, true);
/**
* 新增文档
*/
String json="{\n" +
" \"id\":1,\n" +
" \"name\":\"tangxiang\",\n" +
" \"age\":23\n" +
"}";
AddResult execute = myColl.add(json).execute();
List<String> generatedIds = execute.getGeneratedIds();
if(!CollectionUtils.isEmpty(generatedIds)){
System.out.println("新增成功");
for(String id:generatedIds){
System.out.println("新增id为"+id);
}
}
/**
* 查询
*/
DocResult myDocs = myColl.find("name like :param").limit(1).bind("param", "tan%").execute();
System.out.println("查询结果为:"+myDocs.fetchOne());
/**
* 修改
*/
myColl.modify("id=1").set("name", "王五").execute();
System.out.println("修改文档完成");
myDocs = myColl.find("name = :param").limit(1).bind("param", "王五").execute();
System.out.println("查询结果为:"+myDocs.fetchOne());
/**
* 删除
*/
myColl.remove("name='王五'").execute();
System.out.println("删除文档完成");
myDocs = myColl.find("name = :param").limit(1).bind("param", "王五").execute();
System.out.println("查询结果为:"+myDocs.fetchOne());
/**
* 删除集合
*/
myDb.dropCollection(collectionName);
System.out.println("删除集合完成");
mySession.close();
}
/**
* 使用事务
*/
@Test
public void testTransaction(){
Session mySession = new SessionFactory().getSession("mysqlx://server-com:33060/world_x?user=root&password=123456");
Schema myDb = mySession.getSchema("world_x");
String collectionName = "transaction";
String json="{\n" +
" \"id\":1,\n" +
" \"name\":\"tangxiang\",\n" +
" \"age\":23\n" +
"}";
Collection myColl = myDb.createCollection(collectionName, true);
/**
* 必须在Collection下面,否则不生效
*/
mySession.startTransaction();
myColl.add(json).execute();
DocResult myDocs = myColl.find("name like :param").limit(1).bind("param", "tan%").execute();
System.out.println("查询结果为:"+myDocs.fetchOne());
mySession.rollback();
myDocs = myColl.find("name like :param").limit(1).bind("param", "tan%").execute();
System.out.println("查询结果为:"+myDocs.fetchOne());
mySession.close();
}
}
资料来源:mysql官网
MySQL 8.0参考手册
https://dev.mysql.com/doc/refman/8.0/en/
X DevAPI用户指南:
https://dev.mysql.com/doc/x-devapi-userguide/en/processing-warnings.html