数据库基本查询

最新推荐文章于 2022-01-16 10:17:43 发布

陌上桑花开花

最新推荐文章于 2022-01-16 10:17:43 发布

阅读量2.7k

点赞数 1

文章标签： oracle select mysql sql 数据库

本文链接：https://blog.csdn.net/u014079773/article/details/52459786

版权

以下为我日常开发中写的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水平

陌上桑花开花

关注

1
点赞
踩
1

收藏

觉得还不错? 一键收藏
0
评论
数据库基本查询

数据库分页查询：Mysql：select * from tablename where 条件 limit (page-1)*pagesize,pagesize;Oracle： select * from (select a.*,rowmun r from tablename a where 1=1 and 查询条件)where r((page-1)*pagesize);
复制链接

扫一扫