1.表領域作成
CREATE TABLESPACE “TABLESPACE_CFT” DATAFILE
‘/var/data/cft.dbf’ SIZE 2G AUTOEXTEND ON MAXSIZE 4G
LOGGING ONLINE PERMANENT BLOCKSIZE 2048
EXTENT MANAGEMENT LOCAL AUTOALLOCATE SEGMENT SPACE MANAGEMENT AUTO;

2.テーブル一覧
SELECT * FROM USER_USERS;

3.表領域一覧
SELECT TABLESPACE_CFT,FILE_NAME FROM DBA_DATA_FILES;

4.ユーザの確認
SELECT * FROM ALL_USERS WHERE USERNAME = ‘YAMADA’;

5.所有オブジェクト確認
SELECT * FROM USER_OBJECTS
WHERE OBJECT_TYPE IN (‘PROCEDURE’,’FUNCTION’,’PACKAGE’,’PACKAGEBODY’,’TRIGGER’);

6.ストアドプロシージャを実行する
EXECUTE CFT_PROCEDURE;

1.And演算子

2.Or演算子

1.findOne()でドキュメントを取得
db.test_collection.findOne( { name: { $in: [‘yamada’,’oosaki’] } }, { name:1, _id:0 } );

2.limitでドキュメントを取得
db.test_collection.find( { name: ‘changfatun’ } ).limit(2);

3.ドキュメントの件数を取得
db.test_collection.count();

4.distinctで重複レコードを削除
db.test_collection.distinct( ‘name’ );

PostgreSQLの場合
SELECT * FROM EMPLOYEE WHERE regDate BETWEEN ‘2018-08-01’ AND ‘2018-09-02’ ORDER BY regDate;

MongoDB場合
db.employee.find({ “regDate” : { “$gte” : ISODate(“2018-08-01T00:00:00+09:00”),
“$lte” : ISODate(“2018-09-02T00:00:00+09:00”) } }).sort({regDate:1});

1.昇順ソート
db.membertbl.find({}).sort(
{reg_id: 1}
)

2.降順ソート
db.membertbl.find({}).sort(
{reg_id: -1}
)

3.explain()
db.employess.find({ “member.age”:32, “member.birthday” : /1982-01-12/ }).explain()
db.employees.find({cityname: “tokyo”}).explain()

4.limitとoffsetを指定する
db.employees.find().limit(3).skip(1)

1.等価比較
db.employees.find(
{position: “SE”}
)
2.非等価比較
db.employees.find(
{position: { $ne: “PG” }}
)

3.複数検索条件を指定する
db.employees.find(
{position: “PG”, age: 22}
)

4.OR条件
db.employees.find(
{$or: [{position: “PG”} , {age: 30}]}
)

SQLコード：
ALTER FUNCTION [dbo].[GetCurrentIP] ()
RETURNS varchar(255)
AS
BEGIN
DECLARE @IP_Address varchar(255);

SELECT @IP_Address = client_net_address
FROM sys.dm_exec_connections
WHERE Session_id = @@SPID;

Return @IP_Address;
END

1.DBObjectのインポート
import com.mongodb.DBObject;
import com.mongodb.util.JSON;

2.$match
DBObject match = (DBObject) JSON.parse(“{$match:{‘status’:’1′,’time’:
{$gte:'”+beginDay+”‘,$lte:'”+endDay+”‘}}}”);
(beginDay-開始日付；endDay-終了日付)

3.$group
DBObject group = (DBObject) JSON.parse(“{$group:{_id:’$userId’,
count:{$sum:1},total:{$sum:’$amount’},maxSigle:{$max:’$amount’}}}”);

検索操作
List<DBObject> list = new ArrayList<DBObject>();
list.add(match); list.add(group);
AggregationOutput Output = mongoTemplate.getCollection(“tblName”).aggregate(list);

4.List<DBObject>のサンプル
DBObject match = (DBObject) JSON.parse(“{$match:{‘status’:{$in:[‘0′,’1′]},
type:{$ne:11},’time’:{$gte:'”+beginDay+”‘,$lte:'”+endDay+”‘}}}”);

DBObject sort = (DBObject) JSON.parse(“{$sort:{‘count’:-1}}”);//ソート

List<DBObject> list = new ArrayList<DBObject>();
list.add(match);
list.add(group);
list.add(groupMatch);
list.add(project);
list.add(sort);
AggregationOutput catchOutPut = mongoTemplate.getCollection(“tblName”)
.aggregate(list);//検索結果

5.BasicDBObject()の操作
BasicDBObject groupIndex = new BasicDBObject();
BasicDBObject distinctQuery = new BasicDBObject();
distinctQuery.put(“userId”, new BasicDBObject(“$in”, userIds));//UserIds配列
BasicDBObject initIndex = new BasicDBObject();
initIndex.put(“count”, 0);//countの値をリセット

Javaコード
String reduce = “function(doc, prev) {if(doc.status==0){prev.count+= 1;};}”;
List<Map<String, Object>> basicDBList = (List<Map<String, Object>>) mongoTemplate.
getCollection(“Collection”).group(groupIndex, distinctQuery, initIndex, reduce, null);

1.Query.put()
date条件検索
BasicDBObject Query = new BasicDBObject();
Query.put(“time”,new BasicDBObject(“$gte”, date + ” 00:00:00″)
.append(“$lte”, date + ” 23:59:59″));

or条件検索
BasicDBList values = new BasicDBList();
values.add(new BasicDBObject(“status”, new BasicDBObject(“$exists”,false)));
values.add(new BasicDBObject(“status”, 0));
Query.put(“$or”, values);

2.match
DBObject Match = new BasicDBObject(“$match”, Query);

3.GroupBy.put()
BasicDBObject GroupBy = new BasicDBObject();
GroupBy.put(“userId”, “$userId”);
GroupBy.put(“status”, “$status”);

4.Select.put()
DBObject Select = new BasicDBObject(“_id”, GroupBy);
Select.put(“count”, new BasicDBObject(“$sum”, 1));

5.$group
DBObject Group = new BasicDBObject(“$group”, Select);
import com.mongodb.AggregationOutput;
import org.springframework.data.mongodb.core.MongoTemplate;
AggregationOutput Output = mongoTemplate.getCollection(“tablenameAA”)
.aggregate(Match, Group);
検索結果：
Iterable<DBObject> map = Output.results();
//map loop
for (DBObject dbObject : map) {
Map<String, Object> resultMap = (Map<String, Object>) dbObject.get(“_id”);
Integer userId = Integer.parseInt(CommUtil.toString(ausgMap.get(“userId”)).replace(“.0″,””));

//count計算
Integer count = Integer.parseInt(CommUtil.toString(dbObject.get(“count”)));

}

構文
SELECT <table1.カラム> <table2.カラム>
INTO <新規テーブル> FROM <コピー元テーブル1> <コピー元テーブル2>
例
SELECT
Employee.LastName
, Orders.OrderNo
INTO Employee_OrderNew_Tbl
FROM
Employee
INNER JOIN Orders
ON Employee.Id_PNO = Orders.Id_PNO