今天来讲讲MYSQL的基础入门AND轻松玩转MyBatis-Example,飞起来FFF~
一、对数据库本身操作、
创建数据库：create database name; 选择数据库：use databasename; 删除数据库：drop database name
二、对数据表进行操作
1、创建数据表：create table 表明 ( id int not null auto_increment, name varchar(20) not nulldefault '经理', #设定默认值 description varchar(100), primary key PK_positon (id) #设定主键 ); 2、修改表格的名字：alter table tablename rename table_new_name; 3、删除表：drop table tablename;
三、对数据表结构进行操作、
修改结构 增加列：alter tablename add(test char(10)); 修改列：alter tablename modify test char(20) not null; 删除列：alter tablename drop column test;
修改表列名: Alter table table_name change column old_xxx new_xxx varcher(30)
四、对数据表数据进行操作
1、增加数据：insert into table_name(col1,col2) values(data1,data2); 2、删除数据：delete colname where data="具体某个数据"; 3、修改数据：update table_name set data1="具体某个数据" where data1="旧数据"; 4、清空表数据：delete from table_name; 5、清空序列表数据：truncate table table_name;（可以初始化主键自增长）
前言：
因为项目开发需要很多时候需要做一些复杂的联动查询，那么当你不想用SQL语句实现的时候，那应该怎么办呢？本文教你充分利用Example类来做复杂联动查询。
复习简单查询
增加行为SQL:
insert into industrycategory ( id, categoryname, detailcategoryname ) values ( ?, ?, ? ) JAVA实际操作:
public int insertSelective(Industrycategory record) {
return IndustrycategoryMapper.insertSelective(record);
}
;
删除行为SQL:
delete from industrycategory where id = ? JAVA实际操作:
@Override
public int deleteByPrimaryKey(Integer id) {
return IndustrycategoryMapper.deleteByPrimaryKey(id);
}
更新行为SQL:
update industrycategory SET categoryname = ?, detailcategoryname = ? where id = ? JAVA实际操作:
@Override
public int updateByPrimaryKeySelective(Industrycategory record) {
return IndustrycategoryMapper.updateByPrimaryKeySelective(record);
}
全查询行为SQL:
select 'true' as QUERYID, id, categoryname, detailcategoryname from industrycategory JAVA实际操作:
@Override
public List<Industrycategory> selectAll() {
IndustrycategoryExample IndustrycategoryExample = new IndustrycategoryExample();
return IndustrycategoryMapper.selectByExample(IndustrycategoryExample);
}
高级查询方案（where后字段）
复杂条件查
where a=? and b=? and .... and z=?：可能任意a~z中字段需要Equals类型处理。JAVA实际操作:
@Override
public List<Usercompany> selectAll(Usercompany usercompany) {
UsercompanyExample UsercompanyExample = new UsercompanyExample();
UsercompanyExample.Criteria criteria = UsercompanyExample.createCriteria();
String usercompanyname = usercompany.getUsercompanyname();
String chinesename = usercompany.getChinesename();
String enlighname = usercompany.getEnlighname();
String address = usercompany.getAddress();
String agencyarea = usercompany.getAgencyarea();
String officialwebsite = usercompany.getOfficialwebsite();
String authorizedchannel = usercompany.getAuthorizedchannel();
String behaviorcategory = usercompany.getBehaviorcategory();
String category = usercompany.getCategory();
String instruction = usercompany.getInstruction();
String description = usercompany.getDescription();
String createdtime = usercompany.getCreatedtime();
String usercompanystatus = usercompany.getUsercompanystatus();
if (usercompanyname != null ) {
criteria.andUsercompanynameEqualTo(usercompanyname);
}
if (chinesename != null ) {
criteria.andChinesenameEqualTo(chinesename);
}
if (enlighname != null) {
criteria.andEnlighnameEqualTo(enlighname);
}
if (address != null) {
criteria.andAddressEqualTo(address);
}
if (agencyarea != null) {
criteria.andAgencyareaEqualTo(agencyarea);
}
if (officialwebsite != null) {
criteria.andOfficialwebsiteEqualTo(officialwebsite);
}
if (authorizedchannel != null) {
criteria.andAuthorizedchannelEqualTo(authorizedchannel);
}
if (behaviorcategory != null) {
criteria.andBehaviorcategoryEqualTo(behaviorcategory);
}
if (category != null) {
criteria.andCategoryEqualTo(category);
}
if (instruction != null) {
criteria.andInstructionEqualTo(instruction);
}
if (description != null) {
criteria.andDescriptionEqualTo(description);
}
if (createdtime != null) {
criteria.andCreatedtimeEqualTo(createdtime);
}
if (usercompanystatus != null) {
criteria.andUsercompanystatusEqualTo(usercompanystatus);
}
return UsercompanyMapper.selectByExample(UsercompanyExample);
}
全域搜索（暂时SQL）
where a like %?% or b like %?% or .... z like %?%：需要a~z每个字段进行模糊搜索 JAVA实际操作:
•
防止SQL注入
@Override
public List<Usercompany> selectAll(String index) {
if (index==null) {
index = "%";
}else if (index.contains("%")) {
return null;
}else {
index = '%' + index + '%';
}
System.out.println(index);
return UsercompanyMapper.selectAll(index);
}
•
SQL
<select id="selectAll" parameterType="string" resultType="cn.lantian.springboot.model.Usercompany">
select * from usercompany WHERE (
usercompanyname like #{index} or
chinesename like #{index} or
enlighname like #{index} or
address like #{index} or
agencyarea like #{index} or
officialwebsite like #{index} or
authorizedchannel like #{index} or
behaviorcategory like #{index} or
category like #{index} or
instruction like #{index} or
description like #{index} or
createdtime like #{index}
)
</select>
单字段多条件搜索查
where (a=? or a=?? or a=???) and (b=? or b=?? or b=???):可能需要筛选 广东省or河南省 And 年龄28or年龄29 JAVA实际操作:
@Override
public List<Usercompany> selectComplexAllByAgencyareaAndAuthorizedchannel(String[] agencyarea, String[] authorizedchannel) {
UsercompanyExample UsercompanyExample = new UsercompanyExample();
UsercompanyExample.Criteria criteria1 = UsercompanyExample.or();
criteria1.andAgencyareaIn(Arrays.asList(agencyarea));
criteria1.andAuthorizedchannelIn(Arrays.asList(authorizedchannel));
return UsercompanyMapper.selectByExample(UsercompanyExample);
}
多字段多条件查1
where (a = ? and b=?) or (c = ? and d=?):可能需要筛选 男生 and 体力好 or 经验丰富 and 没有受伤 JAVA实际操作:
多字段多条件查2
where (a = ? or b=?) and (c = ? or d=?):可能需要筛选 男生 or 体力好 And 善于沟通 or 懂的技巧 JAVA实际操作:·
•代码
Example example = new xample();
example.or() .andField1EqualTo(5) .andField2IsNull();
example.or() .andField3NotEqualTo(9) .andField4IsNotNull();
List field5Values = new ArrayList(); field5Values.add(8); field5Values.add(11); field5Values.add(14); field5Values.add(22);
example.or() .andField5In(field5Values);
example.or() .andField6Between(3, 7);
- 解释：在上面的例子中, 动态生成的where子句是:
```java
where (field1 = 5 and field2 is null)
or (field3 <> 9 and field4 is not null)
or (field5 in (8, 11, 14, 22))
or (field6 between 3 and 7)
全域搜索+复杂条件查
where (a like %?% or b like %?% or .... z like %?%) and (a=? and b=? and .... and z=?):需要az每个字段进行模糊搜索 And 可能任意az中字段需要Equals类型处理。JAVA实际操作: