以下为我日常开发中写的sql，我个人感觉非常好，日积月累，提高自己sql功底，仅供参考学习，数据库为mysql或者oracle
数据库基本语法：
select col1,col2,col3,…
from tablename
where 条件
having 条件
group by 分组条件
order by 排序条件
limit 分页起始页，每页显示条数
备注：严格遵循sql语法，否则报sql错误，不支持的语法规则。
mysql分页查询：参数page 起始页 pageSize 每页显示多少条数据
select * from tablename where 条件 limit (page-1)*pagesize,pagesize;
oracle分页查询：参数page 起始页 pageSize 每页显示多少条数据
select * from(select a.*,rownum r from tablename a where 1=1 and 查询条件) where r<(page*pageSize) and r>((page-1)*pageSize)
mysql查询50-100条数据：
select * from tablename limit 50,50
备注：第一个参数50为查询起始页，第二个参数50为100-50得到。
oracle查询50-100条数据：
select * from (select a.* , rownum r from tablename a where 1=1 and 查询条件) where r<100 and r>50
备注：where 1=1表示选择全部，where 1=2 表示全部不选
———————————————–begin mysql operate——————————————————————————————————
mysql创建表：
用户主表：
CREATE TABLE `ykat_user` ( `id` bigint(20) NOT NULL AUTO_INCREMENT, `user_name` varchar(128) DEFAULT NULL, `password` varchar(128) DEFAULT NULL, `phone` varchar(11) NOT NULL, `header_url` varchar(128) DEFAULT NULL, `user_type` int(11) DEFAULT NULL COMMENT '0:系统用户，1:司机，2:门店用户', `sex` int(1) DEFAULT NULL COMMENT '1:男，2:女', `create_time` datetime NOT NULL, `update_time` datetime NOT NULL, `is_del` int(1) NOT NULL DEFAULT '1' COMMENT '1正常，2删除', `is_effect` int(1) DEFAULT NULL COMMENT '1有效，2失效', `dept_id` bigint(20) DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=542 DEFAULT CHARSET=utf8;
插入数据：（备注：mysql因为id是自动增长，则插入数据库id可以不插入列）
INSERT INTO `ykat_user` VALUES
('1', 'IWO6465955873', '96e79218965eb72c92a549dd5a330112', '15062273356', null, '1', '0', '2016-10-12 08:31:18', '2016-10-14 14:56:35', '1', null, null);
INSERT INTO `ykat_user` VALUES
('2', '哼哼', '96e79218965eb72c92a549dd5a330112', '13376057527', null, '1', '1', '2016-10-12 09:27:18', '2016-10-14 11:54:48', '1', null, null);
用户司机表：
CREATE TABLE `ykat_driver` ( `id` bigint(20) NOT NULL AUTO_INCREMENT, `recommended_account` varbinary(128) DEFAULT NULL, `user_id` bigint(20) DEFAULT NULL, `user_name` varchar(128) DEFAULT NULL, `id_no` varbinary(18) DEFAULT NULL, `driver_type` int(1) DEFAULT NULL COMMENT '1：司机，2车主', `spare_phone` varchar(11) DEFAULT NULL, `driver_erweima` varchar(200) DEFAULT NULL COMMENT '二维码', `id_img_pre` varchar(200) DEFAULT NULL, `id_img_back` varchar(200) DEFAULT NULL, `driver_no` varchar(50) DEFAULT NULL, `driver_license_img` varchar(200) DEFAULT NULL, `create_time` datetime NOT NULL, `update_time` datetime NOT NULL, `integral` int(11) DEFAULT '0' COMMENT '积分默认为0', `is_auth` int(1) DEFAULT NULL COMMENT '1：未认证，2已认证', `not_auth_reason` varchar(50) DEFAULT NULL COMMENT '认证不通过原因', `is_del` int(1) DEFAULT NULL COMMENT '1正常，2删除', `license_check_status` int(1) DEFAULT '1' COMMENT '1:待审核，2：审核通过，3：拒绝', `id_check_status` int(1) DEFAULT '1' COMMENT '1:待审核，2：审核通过，3：拒绝', PRIMARY KEY (`id`), KEY `FK_driver_user_id` (`user_id`), CONSTRAINT `FK_driver_user_id` FOREIGN KEY (`user_id`) REFERENCES `ykat_user` (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=478 DEFAULT CHARSET=utf8;
插入数据：
INSERT INTO `ykat_driver` VALUES
('1', 0x3135303632323733333333, '516', 'IWO6465955873', null, null, '13773698600', null, 'www.baidu.com', 'www.baidu.com', null, 'd4e7.jpg', '2016-10-12 08:31:18', '2016-10-12 11:02:05', '27', '1', null, '1', '1', '1');
INSERT INTO `ykat_driver` VALUES
('2', 0x756E646566696E6564, '519', '哼哼', null, null, null, null, '', '', null, '', '2016-10-12 09:27:18', '2016-10-12 09:27:18', '24', '1', null, '1', '1', '1');
根据问卷id，教师id，学生id，查询一个问卷指派给老师或者学生，显示问卷名称，教师名称，学生名称
SELECT qnt.qnName, pt.person_name AS teacherName, ps.person_name AS studentName FROM questionnairepersonrelationentity qnpt LEFT JOIN questionnaireentity qnt ON qnpt.qnid = qnt.id LEFT JOIN person pt ON qnpt.teacherid = pt.person_id LEFT JOIN person ps ON qnpt.studentid = ps.person_id WHERE qnpt.qnid = '314'
查询结果：
备注：一个问卷可以只指派给老师，或者只指派给学生，或者指派给了老师和学生。通过上面查询结果这里用到了left join .. on ..，以问卷表为主表，关联查询问卷表，教师和学生表，理解left join .. on ..语法便很容易实现上述业务。
根据徽章名称，学生姓名，老师姓名，发放时间等搜索条件，查询徽章信息
select bpr.id AS bprId,DATE_FORMAT(bpr.createTime,'%Y-%m-%d')AS grantTime,pp1.PERSON_NAME as teacherName,pp2.PERSON_NAME as studentName,be.badgeImgSrc,be.badgeName from badgepersonrelationentity bpr left join person pp1 on bpr.sendid = pp1.PERSON_ID left join person pp2 on bpr.receiveid = pp2.PERSON_ID left join badgeentity be on bpr.bgid = be.id where 1=1 /**and be.badgeName LIKE '%数%'*/ /**and pp2.PERSON_NAME LIKE '%朱%'*/ /**and pp1.PERSON_NAME LIKE '%沈%'*/ /**and DATE_FORMAT(bpr.createTime,'%Y-%m-%d')='2016-10-19'*/
执行结果：
备注：在日常开发中日期类型数据库设计为datetime类型，实体类型为Date类型，而页面传递的是string类型，则要时间作为查询条件，则sql语句必须转换为字符串否则报sql语法错误。上述中日期作为字符串参数传递，注意不要遗漏单引号。
案例：该语句在实际开发中运用
hibernate的sql，dao层：
//模糊查询：徽章查询
public List getSelBadge(String searchType, String searchName, String grantTime){
Session session = getSessionFactory().getCurrentSession();
String sql = "select t.id as bprId,DATE_FORMAT(t.createTime,'%Y-%m-%d')AS grantTime,t2.PERSON_NAME as teacherName,t1.PERSON_NAME as studentName,t3.badgeImgSrc,t3.badgeName /n" +
"from badgepersonrelationentity t /n" +
"join person t1 on t.receiveid = t1.PERSON_ID /n" +
"join person t2 on t.sendid = t2.PERSON_ID /n" +
"join badgeentity t3 on t.bgid = t3.id /n" +
"where 1=1 ";
if(ValidateUtil.isNotEmpty(searchType)){
if("teacherName".equals(searchType)){
sql = sql + "and t2.PERSON_NAME LIKE '%"+searchName+"%'";
}else if("studentName".equals(searchType)){
sql = sql + "and t1.PERSON_NAME LIKE '%"+searchName+"%'";
}else if("badgeName".equals(searchType)) {
sql = sql + "and t3.badgeName LIKE '%"+searchName+"%'";
}
}
if(ValidateUtil.isNotEmpty(grantTime)){
sql = sql + "and DATE_FORMAT(t.createTime,'%Y-%m-%d')="+"'"+grantTime+"'";
}
SQLQuery query = session.createSQLQuery(sql);
query.setResultTransformer(CriteriaSpecification.ALIAS_TO_ENTITY_MAP);
List list = query.list();
return list;
}
server层：
//模糊查询：徽章查询
public List<Map<String,Object>> getSelBadge(String searchType, String searchName, String grantTime) {
List<Map<String,Object>> bageList=new ArrayList<Map<String, Object>>();
List list=this.badgeDao.getSelBadge(searchType, searchName, grantTime);
if(ValidateUtil.isNotEmpty(list)){
for(int i=0;i<list.size();i++){
Map<String,Object> map=(Map<String,Object>)list.get(i);
String bprId=map.get("bprId").toString();
String grantTimes=map.get("grantTime").toString();
String teacherName=map.get("teacherName").toString();
String studentName=map.get("studentName").toString();
String badgeImgSrc=map.get("badgeImgSrc").toString();
String badgeName=map.get("badgeName").toString();
Map<String,Object> map2=new HashMap<String, Object>();
map2.put("bprId",bprId);
map2.put("createTime",grantTimes);
map2.put("sender",teacherName);
map2.put("receiver",studentName);
map2.put("badgeImgSrc",badgeImgSrc);
map2.put("badgeName",badgeName);
bageList.add(map2);
}
}
return bageList;
}
controller层：
//模糊查询：徽章查询
@ResponseBody
@RequestMapping(value = "/H5/getBadge.Control", method = RequestMethod.GET)
public List<Map<String,Object>> getSelBadge(String searchType, String searchName, String grantTime) {
List<Map<String,Object>> list=new ArrayList<Map<String, Object>>();
try {
if(ValidateUtil.isNotEmpty(searchType)){
searchType=searchType.trim();
searchType=new String(searchType.getBytes("iso-8859-1"), "utf-8");
}
if(ValidateUtil.isNotEmpty(searchName)){
searchName=searchName.trim();
searchName=new String(searchName.getBytes("iso-8859-1"), "utf-8");
}
list=this.badgeService.getSelBadge(searchType, searchName, grantTime);
} catch (Exception e) {
e.printStackTrace();
}
return list;
}
hibernate使用sql更新表。例如根据问卷id，问卷老师状态，问卷提交时间，问卷回答更新问卷信息
update questionnairepersonrelationentity t
set t.teacherstatus='完成',
t.teacomtime='2016-11-04 01:11:00',
t.teacheranswer='老师已完成'
where t.id=532
则dao中hibernate的sql拼接
//微信端未完成用户提交问卷
public long saveMyAnswerDone(long userid, String type, String answer, long qprid) {
//老师问卷未完成提交时间
Date teacomtime=new Date();
//学生问卷未完成提交时间
Date stucomtime=new Date();
String teacomtimeStr=DateUtil.dateToString(teacomtime,DateUtil.DEFAULT_TIME_PATTERN);
String stucomtimeStr=DateUtil.dateToString(stucomtime,DateUtil.DEFAULT_TIME_PATTERN);
Session session = getSessionFactory().getCurrentSession();
try {
//type为教师
if (type.equals("教师")) {
session.createSQLQuery("update questionnairepersonrelationentity t set t.teacherstatus = '完成', t.teacomtime = '"+teacomtimeStr+"', t.teacheranswer = '" + answer + "' where t.id = " + qprid).executeUpdate();
} else {//type为学生或者家长
session.createSQLQuery("update questionnairepersonrelationentity t set t.studentstatus = '完成', t.stucomtime = '"+stucomtimeStr+"', t.studentanswer = '" + answer + "' where t.id=" + qprid).executeUpdate();
}
return 1;
} catch (Exception ex) {
return -1;
}
}
备注：数据库设计的时间类型为datetime类型，实体定义的类型为Date类型，而更新时间字段，因为是拼接，故把时间转换为字符串类型，否则直接Date类型报错误：
“ERROR org.hibernate.engine.jdbc.spi.SqlExceptionHelper(146) – No value specified for parameter 1”
插入签到表时间为系统当前时间
insert into ykat_user_sign(user_id,user_name,serial_days,user_type,create_time,update_time,is_del) values(3,'jj',1,1,NOW(),NOW,1)
备注：
NOW()函数以`’YYYY-MM-DD HH:MM:SS’返回当前的日期时间，可以直接存到DATETIME字段中。
CURDATE()以’YYYY-MM-DD’的格式返回今天的日期，可以直接存到DATE字段中。
CURTIME()以’HH:MM:SS’的格式返回当前的时间，可以直接存到TIME字段中。
插入积分表
insert into ykat_integral(user_id,user_name,user_type,trade_points,trade_type,is_add,points_reward,points_avail,create_time,update_time,is_del) values(1,'jj',1,5,1,1,0,5,NOW(),NOW(),1)
根据用户id和用户类型查询最后一次签到
select id,user_id,user_name,user_type,serial_days,create_time from ykat_user_sign where user_id=516 and user_type=1 and is_del=1 order by create_time desc limit 0,1
执行结果：
根据用户id和用户类型查询最后一次积分
select id,user_id,user_name,user_type,trade_points,trade_type,points_reward,points_avail,create_time,update_time from ykat_integral where user_id=724 and user_type=1 and is_del=1 order by id desc limit 0,1
查询结果：
App端查询我的积分列表
select id,user_id AS userId,user_name AS userName,user_type AS userType, trade_points AS tradePoints,trade_type AS tradeType,points_reward AS pointsReward,points_avail AS pointsAvail, date_format(create_time,'%Y-%m-%d') AS createTime,date_format(update_time,'%Y-%m-%d') AS updateTime from ykat_integral where user_id=724 and user_type=1 and is_del=1 /**and id<15*/ ORDER BY id DESC LIMIT 0,10
查询结果：
App端查询我的积分：统计条数
select count(1) from ykat_integral where user_id=516 and user_type=1 and is_del=1
查询结果：
App端查询我的积分:统计积分总和
select id,user_id AS userId,user_name AS userName,user_type AS userType,sum(trade_points) AS sumPointsAvail from ykat_integral where user_id=724 and user_type=1 and is_del=1
查询结果：
PC端专家列表分页查询
select ex.id,ex.expert_name AS expertName,ex.phone,ex.description,ex.longitude,ex.latitude,ex.expert_address AS expertAddress,yp.img AS headerUrl from ykat_expert ex LEFT JOIN ykat_expert_pics ep ON ex.id=ep.expert_id LEFT JOIN ykat_pics yp ON ep.pic_id=yp.id where ex.is_del=1 and yp.is_del=1 /**and ex.phone like '%187%'*/ /**and ex.expert_name like '%周%'*/ LIMIT 0,100
查询结果：
PC端专家列表查询：查询总数
select count(1) from ykat_expert ex LEFT JOIN ykat_expert_pics ep ON ex.id=ep.expert_id LEFT JOIN ykat_pics yp ON ep.pic_id=yp.id where ex.is_del=1 and yp.is_del=1 /**and ex.phone like '%187%'*/ /**and ex.expert_name like '%周%'*/
查询结果：
App端专家接口查询：根据搜索条件分页查询，按分数或者距离排序
SELECT DISTINCT(ex.id), ex.expert_name AS expertName, ex.phone, yp.img AS headerUrl, ex.longitude, ex.latitude, ( SELECT round(avg(ee.score), 1) FROM ykat_expert_evaluate ee WHERE ex.id = ee.expert_id AND ee.is_del = 1 ) AS score FROM ykat_expert ex LEFT JOIN ykat_expert_pics ep ON ex.id=ep.expert_id LEFT JOIN ykat_pics yp ON ep.pic_id=yp.id LEFT JOIN ykat_expert_models em ON ex.id = em.expert_id LEFT JOIN ykat_vehicle_models vm ON em.vehicle_models_id = vm.id WHERE ex.is_del = 1 AND yp.is_del=1 /**AND ex.id < 41*/ /**AND vm.id=4*/ GROUP BY ex.id ORDER BY /**score DESC,*/ /**ex.longitude DESC,ex.latitude DESC,*/ ex.id DESC LIMIT 0,100
查询结果：
App端专家接口查询：根据专家id查询专家擅长车型
SELECT vm.id AS expertGoodAtId,vm.models_name AS expertGoodAt FROM ykat_expert ex , ykat_expert_models em, ykat_vehicle_models vm WHERE ex.is_del = 1 AND ex.id=em.expert_id AND em.vehicle_models_id=vm.id AND ex.id=168
查询结果：
App端专家接口查询：根据专家id查询专家详情
SELECT ex.id AS expertId, ex.expert_name AS expertName, yp.img AS expertHeaderUrl, ex.description AS expertDescription, ex.phone AS expertPhone, ( SELECT round(avg(ee.score), 1) FROM ykat_expert_evaluate ee WHERE ex.id = ee.expert_id AND ee.is_del = 1 ) AS expertScore FROM ykat_expert ex LEFT JOIN ykat_expert_pics ep ON ex.id=ep.expert_id LEFT JOIN ykat_pics yp ON ep.pic_id=yp.id WHERE ex.is_del = 1 AND yp.is_del=1 AND ex.id = 424
查询结果：
根据专家id分页查询专家评价列表
SELECT id, user_id AS userId, user_name AS userName, header_url AS userHeaderUrl, score, content, date_format(create_time, '%Y-%m-%d %H:%i') AS evaluateCreateTime FROM ykat_expert_evaluate WHERE is_del = 1 AND expert_id =424 /**AND id < 60*/ ORDER BY id DESC LIMIT 0,100
查询结果：
备注：and id<xx表示app端分页查询条件
———————————————–end mysql operate——————————————————————————————————
以下为oracle数据基本查询：均是本人日常开发中使用
———————————————–begin oracle operate——————————————————————————————————
在实际开发中，oracle数据库定义的时间类型为date类型，实体定义类型为date类型，而往往我们web页面显示的是年月日时分秒，故如何使查询日期为字符串类型呢？
解决方法：oracle语法to_char(Date , formater)
归还管理-查询借阅子单列表信息
select so.id,
so.order_id,
so.esp_no,
so.dept_id,
so.dept_name,
so.company_id,
so.company_name,
bo.borrow_user_name,
bo.employee_no,
bo.borrow_content,
to_char(so.borrow_time, 'yyyy-MM-dd HH24:mi:ss') AS borrow_time,
to_char(so.due_time, 'yyyy-MM-dd HH24:mi:ss') AS due_time,
to_char(so.return_time, 'yyyy-MM-dd HH24:mi:ss') AS return_time,
so.status AS borrow_status,
so.remark
from SJK_BORROW_SUB_ORDER so
inner join SJK_BORROW_ORDER bo on so.order_id = bo.id
where 1=1
and so.is_del = 1
and bo.is_del = 1
and bo.status = 1
and so.company_id in(156,158,159)
and so.dept_id in(101,102,103)
and (bo.borrow_user_name like '%李%'
or bo.employee_no like '%100%'
or so.esp_no like '%200%'
or bo.borrow_content like '%南京%')
order by so.borrow_time desc
查询结果：
备注：
1.注意日期格式转换to_char(Date,formater)这是oracle特有的语法，注意与mysql的区别
2.and (col1 like ‘%xx%’ or col2 like ‘%xx%’)表示搜索条件为多个
3.oracle查询字段默认均为大写
归还管理-根据借阅子单id查询出借阅总单id，再根据借阅总单id统计所有子单且子单状态为借出status=1
select count(1) from SJK_BORROW_SUB_ORDER so where so.order_id=(select so.order_id from SJK_BORROW_SUB_ORDER so where so.id=21 ) and so.status=1 and so.is_del=1
查询结果：
归还管理：根据借阅子单id查询出借阅总单id，再根据借阅总单id更新借阅总单状态为归还
update SJK_BORROW_ORDER bo set bo.status=2,bo.update_time=sysdate where bo.id=(select so.order_id from SJK_BORROW_SUB_ORDER so where so.id=21 and so.is_del=1) and bo.is_del=1
个人中心-我的分享
select es.id as "id",
es.esp_no as "espNo",
es.share_user_id as "shareUserId",
es.share_user_name as "shareUserName",
es.shared_user_id as "sharedUserId",
es.shared_user_name as "sharedUserName" ,
es.esp_content as "espContent",
to_char(es.create_time, 'yyyy-MM-dd HH24:mi:ss') as "createTime"
from SJK_ESP_SHARE es
where es.is_del = 1
and es.share_user_id=1
and (es.share_user_name like '%张%'
or es.shared_user_name like '%李%'
or es.esp_no like '%100%'
or es.esp_content like '%南京%')
查询结果：
备注：oracle查询列默认均为大写，想要查询字段自定义，则起别名且别名用双引号
个人中心-我收到的分享
select es.id as "id",
es.esp_no as "espNo",
es.share_user_id as "shareUserId",
es.share_user_name as "shareUserName",
es.shared_user_id as "sharedUserId",
es.shared_user_name as "sharedUserName" ,
es.esp_content as "espContent",
to_char(es.create_time, 'yyyy-MM-dd HH24:mi:ss') as "createTime"
from SJK_ESP_SHARE es
where es.is_del = 1
and es.shared_user_id=2
and ( es.esp_no like '%00%'
or es.esp_content like '%00%')
查询结果：
个人中心-我的下载
select sd.id as "id",
sd.user_id as "userId",
es.esp_no as "espNo",
es.esp_content as "espContent",
to_char(sd.create_time,'yyyy-MM-dd HH24:mi:ss') as "createTime"
from SJK_DOWNLOAD sd
inner join SJK_ESP_SHARE es on sd.share_id = es.id
where sd.is_del = 1 and es.is_del = 1
and sd.user_id=1
and (es.esp_no like '%00%'
or es.esp_content like '%00%')
查询结果：
通过父级公司id查询所有公司即子公司,递归查询
select sc.id as "companyId",sc.company_name as "companyName",sc.parent_id as "parentId" from SJK_COMPANY sc where sc.parent_id in (select distinct(sc.parent_id) from SJK_COMPANY sc) and sc.is_del=1
查询结果：
通过父级部门id查询所有部门即子部门,递归查询
select pd.dept_id as "deptId",pd.dept_name as "deptName",pd.fdept_id "parentId" from PT_DEPT pd where pd.fdept_id in (select distinct(pd.fdept_id) from PT_DEPT pd)
查询结果：
归还管理：根据公司id查询该公司下所有部门及子部门一个部门下所有子部门
select pd.dept_id as "deptId",pd.dept_name as "deptName",pd.fdept_id as "fDeptId" , sc.id as "companyId" from PT_DEPT pd inner join SJK_COMPANY sc on pd.company_id=sc.id where 1=1 and sc.is_del=1 and pd.fdept_id in (select distinct(pd.fdept_id) from PT_DEPT pd) and pd.company_id=156
查询结果：
根据用户id查询用户信息
select pu.user_id,pu.user_name,pu.login_name,pu.dept_id,pd.dept_name,pu.user_position
from PT_USER pu
inner join PT_DEPT pd on pu.dept_id=pd.dept_id
where 1=1
and pu.user_id=50196
and pu.company_id in (156,157,158)
and pu.dept_id in (101,102,103)
and (pu.user_name like '%李%'
or pu.login_name like '%10%'
or pu.user_position like '%苏%'
or pd.dept_name like '%部%')
查询结果：
模糊查询：
方法一：
SELECT ID FROM SJK_COMPANY WHERE INSTR(ALL_PARENT_ID,'.156') > 0
方法二：
SELECT ID FROM SJK_COMPANY WHERE ALL_PARENT_ID like '%.156%'
查询结果：
备注：2个查询结果是等价，方法二常用，方法一建议大家经常用，显示你的sql水平
原创文章，作者：ItWorker，如若转载，请注明出处：https://blog.ytso.com/6675.html