1 简介
这次出差开发模块过程中，使用Navicat数据库应用程序，由于底层数据库使用了MongoDB和MySQL，之前本人是一个数据库小白，在开发过程中积累了一些增删改查的sql语句，在此做个小总结。
2 MongoDB语句
2.1 分组统计
db.partydues.aggregate([
{
$match: {
"map.month": "11",
"map.year": "2018",
"map.dataType": "task"
}
},
{
$group: {
_id: "$map.roleName",
"sumOfPartyDueShouldBe": {
"$sum": "$map.partyDueShouldBe"
},
"sumOfPartyDues": {
"$sum": "$map.partyDues"
},
"totalNumOfPerson": {
"$sum": 1
}
}
}
])
查询结果如下：
// 1
{
"_id": "开发部”
"sumOfPartyDueShouldBe": 208.29,
"sumOfPartyDues": 9268,
"totalNumOfPerson": 3
}
// 2
{
"_id": "业务部
"sumOfPartyDueShouldBe": 178.58,
"sumOfPartyDues": 1546,
"totalNumOfPerson": 2
}
// 3
{
"_id": "行政部,
"sumOfPartyDueShouldBe": 323.36,
"sumOfPartyDues": 1022,
"totalNumOfPerson": 2
}
2.2 查询指定年月的缴费记录，过滤年月应缴和实缴，限制三条
有时并不需要将文档中所有键值对都返回。遇到这种情况，可以通过find（findOne）的第二个参数来指定想要的键。这样做既会传输的数据量，又能节省客户端解码文档的时间和内存消耗。
db.partydues.find({
"map.month": "08",
"map.dataType": "task"
}, {
"map.partyDues": 1,
"map.cnName": 1,
"map.year": 1,
"map.month": 1,
"map.partyDueShouldBe": 1
}).limit(3)
查询结果为：
// 1
{
"_id": "a614624774294059b0278e4824683eb7",
"map": {
"partyDues": 100,
"year": "2019",
"partyDueShouldBe": 95.06,
"cnName": "朱朱",
"month": "08"
}
}
// 2
{
"_id": "36cdd2b168914d5d8de0a63ec0ece259",
"map": {
"partyDues": 100,
"year": "2019",
"partyDueShouldBe": 95.06,
"cnName": "宋宋",
"month": "08"
}
}
// 3
{
"_id": "943525620b3841929f1c9844826a4769",
"map": {
"partyDues": 900,
"year": "2019",
"partyDueShouldBe": 95.06,
"cnName": "安安",
"month": "08"
}
}
可以看到默认情况下，“_id”这个键总是被返回，即便是没有指定要返回这个键。
需要注意的是，可以使用第二个参数来剔除查询结果中的某些键值对。例如文档中有许多键，但不希望结果中有“map.month”
db.partydues.find({}, {“map.month”: 0})
注意第二个参数默认不可以即有0，又有1，否则会报错
db.salary.find({}, {"map.year":1, "map.month":0})
会弹出如下错误：
[Error] Projection cannot have a mix of inclusion and exclusion.
但可以通过这种方式剔除“_id”:
db.salary.find({}, {"map.year":1, "_id":0})
2.3 查询指定年月的应缴话费数额，以月排序
db.salary.find({
"map.姓名": "宋宋"
}, {
"map.partyDueShouldBe": 1,
"map.month": 1,
"map.year": true
}).sort({
"map.year": 1,
"map.month": 1
}).limit(3)
查询结果为：
// 1
{
"_id": ObjectId("5c24330e07cd26220910dd76"),
"map": {
"year": "2016",
"month": "01",
"partyDueShouldBe": 79.3
}
}
// 2
{
"_id": ObjectId("5c247a4007cd9b6ff6a3d544"),
"map": {
"year": "2017",
"month": "04",
"partyDueShouldBe": 79.3
}
}
// 3
{
"_id": ObjectId("5c0db84b07cd92e12375c212"),
"map": {
"year": "2018",
"month": "01",
"partyDueShouldBe": 94.33
}
}
2.4 分组查询
db.salary.aggregate(
{
"$group": {
"_id": "$map.结算部门",
"totalNumOfPerson": {
"$sum": 1
}
}
}
)
查询结果是：
// 1
{
"_id": "二室",
"totalNumOfPerson": 45
}
// 2
{
"_id": "管理室",
"totalNumOfPerson": 15
}
// 3
{
"_id": "开发室",
"totalNumOfPerson": 21
}
2.5 根据流程实例pid查询表中记录
db.partydues.find({"map.pid":2592570})
2.6 根据姓名查询salary表中为高超的数据
db.partydues.find({"map.cnName":"宋宋"}).limit(2)
结果为：
// 1
{
"_id": "15a50f1c510044fab151fece5f862560",
"_class": "com.zioer.entity.FreeModel",
"name": "partydues",
"cname": "党费收缴",
"map": {
"partyDues": 80,
"year": "2019",
"roleId": "009003",
"dataType": "task",
"pid": "3345001",
"userId": "cf8d1fd5c23f4778b7c0f537d6de2c41",
"partyDueShouldBe": 79.3,
"cnName": "宋宋",
"month": "03",
"createTime": "2018-11-28 14:55:23",
"roleName": "业务部",
"taskId": "3345055",
"username": "songsong"
}
}
// 2
{
"_id": "84d9e5e546cf4ee2be5b74412c2fdbbc",
"_class": "com.zioer.entity.FreeModel",
"name": "partydues",
"cname": "话费充值",
"map": {
"partyDues": 80,
"year": "2019",
"roleId": "009003",
"dataType": "task",
"pid": "3380001",
"userId": "cf8d1fd5c23f4778b7c0f537d6de2c41",
"partyDueShouldBe": 79.3,
"cnName": "宋宋",
"month": "05",
"createTime": "2018-11-29 09:08:12",
"roleName": "业务部",
"taskId": "3380055",
"username": "songsongsong"
}
}
2.7 聚合查询，查询工资单结算部门的内容和各个组中人数
db.salary.aggregate([
{
$match: {
"map.month": "04",
"map.year": "2021"
}
},
{
$group: {
_id: "$map.结算部门",
count: {
$sum: 1
},
}
}
])
查询结果如下：
// 1
{
"_id": "计划部",
"count": 3
}
// 2
{
"_id": "开发部",
"count": 1
}
// 3
{
"_id": "网络部",
"count": 1
}
// 4
{
"_id": "中心部",
"count": 3
}
##2.8 删除partydues、salary、proportion表中所有的内容
db.partydues.remove({});
db.salary.remove({});
db.proportion.remove({})
2.9 删除模块指定年月的数据
db.salary.remove({"map.month": "08", "map.year": "2018"})
db.partydues.remove({"map.month": "08", "map.year": "2018"})
db.partyDueOperLog.remove({"map.month": "08", "map.year": "2018"})
2.10 向partydues插入数据
db.getCollection("partydues").insert([ {
_id: "2e221e23ff98405391fvb5302c471e1e",
_class: "com.zioer.entity.FreeModel",
name: "partydues",
cname: "话费充值",
map: {
month: "10",
createTime: "2018-10-22 20:28:38",
year: "2017",
dataType: "processInstance",
pid: "2602501",
state: "doing",
bid: "b8e2668fb8fd4cef98ebecc1e20ed96c",
userId: "3f14ea3cbdf848a6bf632bf547907c90"
}
} ]);
db.getCollection("partydues").insert([ {
_id: "7af0f7069a5a4f9d281cdc5dad211dc2",
_class: "com.zioer.entity.FreeModel",
name: "partydues",
cname: "话费充值",
map: {
partyDues: "131",
year: "2017",
roleId: "00902",
dataType: "task",
pid: "2602501",
userId: "3f14ea3cbdf848a6bf632bf547907c90",
partyDueShouldBe: "130.59",
cnName: "朱朱",
month: "10",
createTime: "2018-10-22 20:30:11",
roleName: "部门2",
taskId: "2602548",
username: "zhuzhu",
bid: "",
state: ""
}
} ]);
2.11 查询8月到10月的缴费记录
db.salary.find({"map.month": {"$gte": "08", "$lte": "10"}}, {"map.month": 1}).sort({"map.month":1}).limit(3)
查询结果为：
// 1
{
"_id": ObjectId("5c00894a75b6b1f9aba5b9a7"),
"map": {
"month": "08"
}
}
// 2
{
"_id": ObjectId("5c00894a75b6b1f9aba5b9a9"),
"map": {
"month": "08"
}
}
// 3
{
"_id": ObjectId("5c00894a75b6b1f9aba5b9ab"),
"map": {
"month": "08"
}
}
2.12 使用$in进行多值查询
db.raffle.find({“ticket_no”: {“$in”: [725, 542, 390]}})
db.users.find({“user_id”: [12345, “Joe”]})
更多的MongoDB关于find和findOne请查看
find和findOne查询详解
3 MySQL语句
3.1 查询某个用户id对应的用户信息
SELECT
u.USER_ID,
u.USERNAME,
u.XM,
u.RIGHTS AS USER_RIGHTS,
u.USER_PASSWORD,
u.SKIN,
r.ROLE_ID,
r.ROLE_NAME,
r.RIGHTS AS ROLE_RIGHTS
FROM
SYS_USER u
LEFT JOIN SYS_ROLE r ON u.ROLE_ID = r.ROLE_ID
WHERE
u.STATUS = 0
AND u.USER_ID = "3e7b5e3d9b6f4c768524453115baca81";
查询结果如下：
3.2 查询重名
select XM, COUNT(*) from sys_user GROUP BY XM
查询结果如下：
乾隆 1
令狐冲 3
刘博 2
刘嘉玲 1
3.3 根据任务被指派人的userid确定名为党支部确认的历史任务，并按照结束任务逆序
SELECT
PROC_DEF_ID_,
NAME_,
START_TIME_,
END_TIME_
FROM
act_hi_taskinst
WHERE
NAME_ = "党支部确认" && ASSIGNEE_ = "5eeb558af7b94ec98420f7e9c93441c0"
ORDER BY
END_TIME_ DESC
LIMIT 3
查询结果如下：
multiinstance:2:2017533 确认 2020-09-01 20:19:48.000 2020-09-02 08:12:50.000
multiinstance:2:2017533 确认 2018-12-27 16:04:56.000 2018-12-27 16:05:05.000
multiinstance:2:2017533 确认 2018-12-27 15:44:22.000 2018-12-27 15:44:43.000
3.4 根据中文名检索sys_user
SELECT
*
FROM
sys_user
WHERE
XM IN ( "戴戴", "王王", "范范", "超超", "安安", "朱朱" )
3.5 获取部门色的最大值
SELECT
ROLE_ID
FROM
sys_role
WHERE
ROLE_ID REGEXP '[0-9]$' && ROLE_ID LIKE "0090%" && LENGTH( ROLE_ID ) = 6
ORDER BY
ROLE_ID DESC
LIMIT 1;
3.6 删除两张表中的多实例子流程相关流程数据
DELETE from `act_ru_task` WHERE PROC_INST_ID_ = (select ID_ from `act_hi_procinst` WHERE `PROC_DEF_ID_` like "multiinstance%" && END_TIME_ is NULL);
DELETE FROM `act_hi_procinst` WHERE `PROC_DEF_ID_` like "multiinstance%" && END_TIME_ is NULL;
更多关于MySQL增删改查的内容可以参考：
单表操作
聚合、分组、函数
数据类型
外键定义、作用、增加和删除
内连接和外连接
关联关系
4 总结
在工作和生活中，尤其是程序员开发时，肯定少不了要和关系型数据库和非关系型数据库打交道，熟悉两种数据库的增删改查，满足日常的需要，进而更加深入诸如性能调优，数据库的主备模式等是优秀程序员必不可少的过程。一起努力加油吧。
扫一扫
238
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
