赞
踩
SET NAMES utf8mb4; SET FOREIGN_KEY_CHECKS = 0; -- ---------------------------- -- Table structure for emp -- ---------------------------- DROP TABLE IF EXISTS `emp`; CREATE TABLE `emp` ( `EMPNO` int NOT NULL AUTO_INCREMENT, `ENAME` varchar(10) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci NULL DEFAULT NULL, `JOB` varchar(9) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci NULL DEFAULT NULL, `MGR` double NULL DEFAULT NULL, `HIREDATE` date NULL DEFAULT NULL, `SAL` double NULL DEFAULT NULL, `COMM` double NULL DEFAULT NULL, `DEPTNO` int NULL DEFAULT NULL, PRIMARY KEY (`EMPNO`) USING BTREE, INDEX `DEPTNO`(`DEPTNO`) USING BTREE, CONSTRAINT `emp_ibfk_1` FOREIGN KEY (`DEPTNO`) REFERENCES `dept` (`DEPTNO`) ON DELETE RESTRICT ON UPDATE RESTRICT ) ENGINE = InnoDB AUTO_INCREMENT = 8900 CHARACTER SET = utf8mb3 COLLATE = utf8mb3_general_ci ROW_FORMAT = Dynamic; -- ---------------------------- -- Records of emp -- ---------------------------- INSERT INTO `emp` VALUES (7369, '老张123', 'CLERK', 7902, '1980-12-30', 1200, 0, 20); INSERT INTO `emp` VALUES (7499, 'ALLEN', 'SALESMAN', 7698, '1981-02-20', 1700, 1200, 30); INSERT INTO `emp` VALUES (7521, 'WARD', 'SALESMAN', 7698, '1981-02-22', 1250, 500, 30); INSERT INTO `emp` VALUES (7566, 'JONES', 'MANAGER', 7839, '1981-04-02', 3975, NULL, 20); INSERT INTO `emp` VALUES (7654, 'MARTIN', 'SALESMAN', 7698, '1981-09-28', 1250, 1400, 30); INSERT INTO `emp` VALUES (7698, 'BLAKE', 'MANAGER', 7839, '1981-05-01', 2850, NULL, 30); INSERT INTO `emp` VALUES (7782, 'CLARK', 'MANAGER', 7839, '1981-06-09', 2450, NULL, 10); INSERT INTO `emp` VALUES (7788, 'SCOTT', 'ANALYST', 7566, '1987-07-13', 4000, NULL, 20); INSERT INTO `emp` VALUES (7839, 'KING', 'PRESIDENT', NULL, '1981-11-17', 5000, NULL, 10); INSERT INTO `emp` VALUES (7844, 'TURNER', 'SALESMAN', 7698, '1981-09-08', 1500, 0, 30); INSERT INTO `emp` VALUES (7876, 'ADAMS', 'CLERK', 7788, '1987-07-13', 2100, NULL, 20); INSERT INTO `emp` VALUES (7900, 'JAMES', 'CLERK', 7698, '1981-12-03', 950, NULL, 30); INSERT INTO `emp` VALUES (7902, 'FORD', 'ANALYST', 7566, '1981-12-03', 4000, NULL, 20); INSERT INTO `emp` VALUES (8896, '小明', 'MANAGER', 7698, '2023-01-02', 6666, 800, 10); INSERT INTO `emp` VALUES (8897, '小红', 'SALESMAN', 7698, '2023-02-08', 5000, 1000, 10); INSERT INTO `emp` VALUES (8898, 'zhang1', 'SALESMAN', 7698, '2023-01-31', 1243, 234, 10); INSERT INTO `emp` VALUES (8900, '雄安', 'MANAGER', 7698, '2023-02-05', 13, 123, 10); SET FOREIGN_KEY_CHECKS = 1;
import com.zlz.ShiroStart2;
import org.springframework.boot.test.context.SpringBootTest;
@SpringBootTest(classes = ShiroStart2.class,webEnvironment = SpringBootTest.WebEnvironment.NONE)
public class DemoTest {
}
//对用户名中含有z且(工资大于1000或者入职日期为2023-01-01)的用户信息进行修改,修改其津贴(comm)为888,并把部门编号改成20
UPDATE emp SET comm=888,deptno=20 WHERE (ename LIKE '%z%' AND (sal > 1000 OR hiredate = '2023-01-01'))
//使用updateWrapper实现修改功能
@Test
public void a8(){
//对用户名中含有z且(工资大于1000或者入职日期为2023-01-01)的用户信息进行修改,修改其津贴(comm)为888,并把部门编号改成20
UpdateWrapper<Emp> updateWrapper= new UpdateWrapper<>();
updateWrapper.like("ename",'z' )
.and(i->i.gt("sal", 1000).or().eq("hiredate", "2023-01-01"));
updateWrapper.set("comm", 888).set("deptno", "20");
int update = empMapper.update(null, updateWrapper);
System.out.println("受影响行数为: "+update);
}
//模拟在用户点击查询时的,对应的搜索关键字的情况
SELECT EMPNO,ename,job,mgr,hiredate,sal,comm,deptno FROM emp WHERE (sal >= 1000 AND sal <= 2000)
@Test public void a9(){ //模拟在用户点击查询时的,对应的搜索关键字的情况 String ename=""; Integer salStart=1000; Integer salEnd=2000; QueryWrapper<Emp> empQueryWrapper = new QueryWrapper<>(); //StringUtils是com.baomidou.mybatisplus包下面的 if(StringUtils.isNotBlank(ename)){ //isNotBlank方法是判断某个字符串是否不为空字符串、不为null,不为空白符,若三种都不为,就返回true empQueryWrapper.like("ename",ename); } if (salStart != null) { empQueryWrapper.ge("sal", salStart); } if (salEnd != null) { empQueryWrapper.le("sal", salEnd); } List<Emp> emps = empMapper.selectList(empQueryWrapper); emps.forEach(System.out::println); }
//模拟在用户点击查询时的,对应的搜索关键字的情况
SELECT EMPNO,ename,job,mgr,hiredate,sal,comm,deptno FROM emp WHERE (sal >= 1000 AND sal <= 2000)
@Test
public void a10(){
//模拟在用户点击查询时的,对应的搜索关键字的情况
String ename="";
Integer salStart=1000;
Integer salEnd=2000;
QueryWrapper<Emp> empQueryWrapper = new QueryWrapper<>();
//StringUtils是com.baomidou.mybatisplus包下面的
//isNotBlank方法是判断某个字符串是否不为空字符串、不为null,不为空白符,若三种都不为,就返回true
empQueryWrapper.like(StringUtils.isNotBlank(ename),"ename", ename);
empQueryWrapper.ge(salStart != null,"sal", salStart);
empQueryWrapper.le(salEnd != null,"sal", salEnd);
List<Emp> emps = empMapper.selectList(empQueryWrapper);
emps.forEach(System.out::println);
}
//模拟在用户点击查询时的,对应的搜索关键字的情况
//查询工资为1000-2000范围内的员工信息
SELECT EMPNO,ename,job,mgr,hiredate,sal,comm,deptno FROM emp WHERE (sal >= 1000 AND sal <= 2000)
//使用lambdaQuery查询对应的语句
@Test
public void a11(){
//模拟在用户点击查询时的,对应的搜索关键字的情况
//查询工资为1000-2000范围内的员工信息
String ename="";
Integer salStart=1000;
Integer salEnd=2000;
LambdaQueryWrapper<Emp> qw= new LambdaQueryWrapper<>();
qw.like(StringUtils.isNotBlank(ename),Emp::getEname,ename)
.ge(salStart!=null, Emp::getSal, salStart)
.le(salEnd!=null, Emp::getSal, salEnd);
List<Emp> emps = empMapper.selectList(qw);
emps.forEach(System.out::println);
}
//对用户名中含有z且(工资大于1000或者入职日期为2023-01-01)的用户信息进行修改,修改其津贴(comm)为1666,并把部门编号改成10
UPDATE emp SET comm=1600,deptno=10 WHERE (ename LIKE '%z%' AND (sal > 1000 OR hiredate = '2023-01-01'))
@Test
public void a12(){
//对用户名中含有z且(工资大于1000或者入职日期为2023-01-01)的用户信息进行修改,修改其津贴(comm)为1666,并把部门编号改成10
LambdaUpdateWrapper<Emp> updateWrapper= new LambdaUpdateWrapper<>();
updateWrapper.like(Emp::getEname,'z' )
.and(i->i.gt(Emp::getSal, 1000).or().eq(Emp::getHiredate, "2023-01-01"));
updateWrapper.set(Emp::getComm,1666).set(Emp::getDeptno, "10");
int update = empMapper.update(null, updateWrapper);
System.out.println("受影响行数为: "+update);
}
赞
踩
Copyright © 2003-2013 www.wpsshop.cn 版权所有,并保留所有权利。