//⑤查询部门最高工资,取出的max(salary)不可能用一个属性来接,因此定义int类型就好了intselectMaxsalary();<!--//⑤查询部门最高工资,取出的max(salary)不可能用一个属性来接,因此定义int类型就好了 --><select id ="selectMaxsalary" resultType ="java.lang.Integer">
select max(salary) from employees;</select>// int i = emp.selectMaxsalary();// System.out.println(i);
传多个参数用对象
//④传多个参数,用对象。保证实体类里面的属性和数据库中表的字段名一致
Employees selectBysalaryAndyear(Employees employees);<!--//④传多个参数,用对象 --><select id ="selectBysalaryAndyear" parameterType ="com.easyup.model.Employees" resultType ="com.easyup.model.Employees"><!--{}里面写实体类里面属性的名字 -->
select last_name from employees where salary = #{salary} and year = #{year};</select>
Employees employees =newEmployees();// employees.setYear(22);// employees.setSalary(42000);// Employees i = emp.selectBysalaryAndyear(employees);// System.out.println(i.getLast_name());
动态SQL
//⑥动态SQL//(1)where if,可以省略前面的and
List<Employees>selectByNameAndJobId(Employees emp);//(2)set if:可以省略掉后面的逗号intupdateLastName(Employees emp);//(3)foreach————SQL:list(1,2,3,4):遍历集合
List<Employees>selectEmpByids(List<Integer> ids);//(4)<sql><include>标签可以将select后面要查询的语句重复使用,不必每次都写
Employees selectEmpById(Employees emp);<!--//⑥动态SQL//(1)where if,可以省略前面的and --><select id ="selectByNameAndJobId" resultType ="com.easyup.model.Employees">
select last_name, job_id
from employees
<where><!-- where if 是去掉字if里面前面的and的,但是不能删除if里面后面的and --><!-- where if 至少传入一个参数 --><if test ="last_name != null and last_name != ''">
last_name = #{last_name}</if><if test="job_id != null and job_id != ''">
and job_id = #{job_id}</if></where></select><!--//(2)set if:可以省略掉后面的逗号 --><update id ="updateLastName">
update employees
<!-- 删除语句后面的逗号 --><set><if test ="last_name != null and last_name != ''">
last_name = #{last_name},</if><if test="job_id != null and job_id != ''">
job_id = #{job_id}</if></set>
where employee_id = #{employee_id}</update><!--//(3)foreach————SQL:list(1,2,3,4):遍历集合 --><!-- 此处参数是int类型的集合,可以写参数也可以不写 --><select id ="selectEmpByids" parameterType ="java.lang.Integer" resultType ="com.easyup.model.Employees">
select last_name
from employees
where employee_id
in
<!-- item表示in()里面的内容 --><foreach collection ="list" open ="(" close =")" separator ="," item ="item">
#{item}</foreach></select><!--//(4)<sql><include>标签可以将select后面要查询的语句重复使用,不必每次都写 --><sql id ="empSql">
last_name, job_id, salary, employee_id, year
</sql><select id ="selectEmpById" parameterType ="com.easyup.model.Employees" resultType ="com.easyup.model.Employees">
select
<include refid ="empSql"></include>
from employees
where employee_id = #{employee_id}</select>// Employees empl = new Employees(); //where if方法的调用// empl.setLast_name("Ernst");// empl.setJob_id("IT_PROG");// List<Employees> list = emp.selectByNameAndJobId(empl);// for (Employees employees : list) {// System.out.println(employees.getLast_name());// System.out.println(employees.getJob_id());// }//set if方法的调用// empl.setLast_name("Pataballa11111");// empl.setEmployee_id(106);// int i = emp.updateLastName(empl);// System.out.println(i);//foreach方法的调用// List<Integer> list = new ArrayList<Integer>();// list.add(105);// list.add(106);// list.add(107);// list.add(108);// List<Employees> selectEmpByids = emp.selectEmpByids(list);// for (Employees employees : selectEmpByids) {// System.out.println(employees.getLast_name());// }//<include>方法的调用// empl.setEmployee_id(105);// Employees selectEmpById = emp.selectEmpById(empl);// System.out.println(selectEmpById);
动态传参需要写入参数
//③动态传参需要写入参数
Employees selectById(int employee_id);<!--//③动态传参需要写入参数 --><!-- 动态传参 --><select id ="selectById" parameterType ="java.lang.Integer" resultType ="com.easyup.model.Employees"><!--{}里面写实体类里面属性的名字,或者参数的名字 -->
select last_name from employees where employee_id = #{employee_id};</select>//查看一个字段的单个信息// Employees employees= emp.selectById(108);// System.out.println(employees.getLast_name());
多表查询:一对多
//多表查询:查询一个表和另一个表中相同字段有相同值,因此主表也有多个值,需要用List来接//(2)一对多
List<Departments>selectDepEmp();<!--//(2)一对多 --><resultMap type ="com.easyup.model.Departments" id ="DepMap"><result column ="department_name" property ="departmentName"/><collection property ="empList" ofType ="com.easyup.model.Employees"><result column ="last_name" property ="lastName"/></collection></resultMap><select id ="selectDepEmp" resultMap ="DepMap">
select departments.department_name, employees.last_name
from departments, employees
where departments.department_id = employees.department_id;</select>//一对多// List<Departments> list = dep.selectDepEmp();// for (Departments d : list) {// System.out.println(d.getDepartmentName());// for (Employees e : d.getEmpList()) {// System.out.println(e.getLastName());// }// }
//⑩刚插进去一条信息,然后取出主键:只能用于使用对象作为参数的情况intinsertEmp(Employees emp);<!--//⑩刚插进去一条信息,然后取出主键 --><insert id ="insertEmp" parameterType ="com.easyup.model.Employees"><selectKey order ="AFTER" resultType ="java.lang.Integer" keyProperty ="employee_id">//固定方法名
select last_insert_id()</selectKey>
insert into employees
(last_name) values (#{lastName})</insert>//向数据库中添加数据,然后马上取到对应的主键// Employees employees = new Employees();// employees.setLastName("小明33333333333");// int insertEmp = emp.insertEmp(employees);// System.out.println("执行了:" + insertEmp);// System.out.println("id:" + employees.getEmployee_id());
高级映射
//⑦高级映射//增删改不需要结果集映射,只有查询的时候需要用到结果集映射
Employees selectById11();<!--//⑦高级映射 --><!--//增删改不需要结果集映射,只有查询的时候需要用到结果集映射 --><resultMap type ="com.easyup.model.Employees" id ="empMap"><result column ="last_name" property ="lastName"/></resultMap><select id ="selectById11" resultMap ="empMap">
select <include refid ="empSql"></include>
from employees
where employee_id =108;</select>// Employees selectById11 = emp.selectById11();// System.out.println(selectById11);
懒加载,一对一
//懒加载,又叫延迟加载、按需加载//把多表查询,拆成单表查询,比如从员工表找card表,只有调用getcard的时候才会去查card表//注意事项:①Employees里面要有Card类型的card;②主查询要查询那个与子查询相关联的字段;③子查询的参数要写与主查询相关联的字段;④log文件要放在src的目录下//主查询的方法定义
Employees selectCardlazy(@Param("empId")int employeeId);//主查询的XML<resultMap type ="com.easyup.model.Employees" id ="cc"><result column ="last_name" property ="lastName"/><association property ="card" javaType ="com.easyup.model.Card"
select ="com.easyup.mapper.CardMapper.selectCard" column ="card_id"
fetchType ="lazy"></association></resultMap><select id ="selectCardlazy" resultMap ="cc">
select last_name,card_id
from employees
where employee_id = #{empId}</select>//子查询的方法定义
Card selectCard(@Param("id")int id);//子查询的XML<resultMap type ="com.easyup.model.Card" id ="aa"><result column ="id" property ="Id"/><result column ="card_num" property ="num"/></resultMap><select id ="selectCard" resultMap ="aa">
select id,card_num
from card
where id = #{id}</select>//调用方法
Employees selectCardlazy = emp.selectCardlazy(104);// System.out.println(selectCardlazy.getLastName());
System.out.println(selectCardlazy.getCard().getNum());
懒加载:一对多
//懒加载:一对多//注意事项:①department类里面要有employees泛型的List;②collection里面的property要写empList;③子查询的筛选条件要写与主查询相关联的字段;④log文件要放在src的目录下//从department查员工表,用懒加载
Departments selectFromDepToEmp(@Param("depId")int depId);<resultMap type ="com.easyup.model.Departments" id ="Deplazy"><result column ="department_name" property ="departmentName"/><result column ="department_id" property ="departmentId"/><collection property="empList" ofType ="com.easyup.model.Employees"
select ="com.easyup.mapper.EmployeesMapper.selectFromDepToEmp" column ="department_id"
fetchType ="lazy"></collection></resultMap><select id ="selectFromDepToEmp" resultMap ="Deplazy">
select department_name, department_id
from departments
where department_id = #{depId}</select>//从部门表找员工表的懒加载
List<Employees>selectFromDepToEmp(@Param("depId")int depId);<resultMap type ="com.easyup.model.Employees" id ="hh"><result column ="last_name" property ="lastName"/></resultMap><select id ="selectFromDepToEmp" resultMap ="hh">
select last_name
from employees
where department_id = #{depId}</select>
Departments selectFromDepToEmp = dep.selectFromDepToEmp(60);
System.out.println(selectFromDepToEmp.getDepartmentName());for(Employees employees : selectFromDepToEmp.getEmpList()){
System.out.println(employees.getLastName());}
懒加载:一对多对一
//根据job_id可以找到多个员工//接口中的方法定义
List<Jobs>selectJobsEmpLazy(@Param("jobId")String jobId);//XML的写法<resultMap type ="com.easyup.model.Jobs" id ="ff"><result column ="job_id" property ="jobId"/><result column ="job_title" property ="jobTitle"/><collection property ="empList" ofType ="com.easyup.model.Employees"
select ="com.easyup.mapper.EmployeesMapper.selectFromJobsToEmployees" column ="job_id"
fetchType ="lazy"></collection></resultMap><select id ="selectJobsEmpLazy" resultMap ="ff">
select job_id, job_title
from jobs
where job_id = #{jobId}</select>//从Jobs表找员工表的懒加载//方法的定义
List<Employees>selectFromJobsToEmployees(@Param("JobId")String jobId);//XML的写法<resultMap type ="com.easyup.model.Employees" id ="gg"><result column ="last_name" property ="lastName"/><result column ="salary" property ="salary"/><result column ="employee_id" property ="employee_id"/><association property ="jobGrades" javaType ="com.easyup.model.JobGrades"
select ="com.easyup.mapper.JobGradesMapper.selectSalaryLevel" column ="salary"
fetchType ="lazy"></association></resultMap><select id ="selectFromJobsToEmployees" resultMap ="gg">
select last_name, salary
from employees
where job_id = #{jobId}</select>//根据查到的员工的工资,去工资登记表里面查对应的工资等级//方法的定义
JobGrades selectSalaryLevel(@Param("salary")int salary);//XML的写法<resultMap type ="com.easyup.model.JobGrades" id ="kk"><result column ="grade_level" property ="gradeLevel"/></resultMap><select id ="selectSalaryLevel" resultMap ="kk">
select grade_level
from job_grades
where #{salary} between lowest_sal and highest_sal
</select>//main方法的写法//用jobs查员工表一对多
List<Jobs> selectJobsEmpLazy = job.selectJobsEmpLazy("IT_PROG");for(Jobs jobs : selectJobsEmpLazy){
System.out.println(jobs.getJobId()+" "+ jobs.getJobTitle());for(Employees ee : jobs.getEmpList()){
System.out.println(ee.getLastName()+" "+ ee.getSalary());
System.out.println(ee.getJobGrades().getGradeLevel());}}
模糊查询
//⑧模糊查询//(1)老师教的第一种
List<Employees>selectLike(String name);//(2)我们的第二种
List<Employees>selectLikeName(String name);<!--//⑧模糊查询--><!--//(1)老师教的第一种 --><select id ="selectLike" parameterType ="java.lang.String" resultMap ="empMap">
select last_name
from employees
where last_name like concat('%', #{name},'%');</select><!--//(2)我们的第二种 --><select id ="selectLikeName" parameterType ="java.lang.String" resultMap ="empMap">
select <include refid ="empSql"></include>
from employees
where last_name like #{lastName};</select>// List<Employees> selectLike = emp.selectLike("Er");// for (Employees employees : selectLike) {// System.out.println(employees);// }// String name = "sti";// List<Employees> selectLikeName = emp.selectLikeName("%" + name + "%");// for (Employees employees : selectLikeName) {// System.out.println(employees);
取出一个表中一个字段所有的数据要用List来接
//②取出一个表中一个字段所有的数据要用List来接
List<Employees>selectAll();<!--//②取出一个表中一个字段所有的数据要用List来接 --><!-- 查询所有信息的时候不能写*--><select id ="selectAll" resultType ="com.easyup.model.Employees">
select last_name from employees;</select>//查看一个字段的所有信息// List<Employees> list = emp.selectAll();// // for (Employees employees : list) {// System.out.println(employees.getLast_name());// }
增删改
//①增删改的返回值l类型都是intintadd();intdelete();intupdate();<!-- id表示和哪一个方法进行联系 --><!--//①增删改的返回值都是int --><insert id ="add">
insert into employees (last_name)
values ('小明22222222')</insert><delete id ="delete">
delete from employees where employee_id =152;</delete><update id ="update">
update employees set last_name ='大吉哥' where employee_id =152;</update>//调用接口里面的方法,并接取返回值// int i = emp.add();// int j = emp.update();// int k = emp.delete();
//注解查询:多个参数,一半注解,一半XMLintinsertEmp1(@Param("salary")int salary,@Param("lastName")String last_name);<insert id ="insertEmp1">
insert into employees
(last_name, salary) values (#{lastName}, #{salary})</insert>// int insertEmp1 = emp.insertEmp1(80000, "小花1111111111");// System.out.println(insertEmp1);
注解查询:增删改(无参)
//注解查询@Insert("insert into card (card_num) values (211302)")intinsertCard();@Delete("delete from card where card_num = 211302")intdeleteCard();@Update("update card set card_num = 777 where id = 3")intupdateCard();//注解查询:添加数据// int insertCard = car.insertCard();// System.out.println(insertCard);//注解查询:删除数据// int deleteCard = car.deleteCard();// System.out.println(deleteCard);//注解查询:更新数据// int updateCard = car.updateCard();// System.out.println(updateCard);
注解查询:增删改:多个参数
//多个参数的写法://更新@Update("update card set card_num = #{num} where id = #{id}")intupdateCard2(@Param("id")int id,@Param("num")int num);//删除@Delete("delete from card where id = #{id} and card_num = num")intdeleteCard2(@Param("id")int id,@Param("num")int num);//添加@Insert("insert into card (id, card_num) values (#{id}, #{num})")intinsertCard3(@Param("id")int id,@Param("num")int num);//多个参数//更新// int updateCard2 = car.updateCard2(1, 1111111111);// System.out.println(updateCard2);
注解查询:增删改:使用对象动态传参
//动态传参:使用对象//更新@Update("update card set card_num = #{num} where id = #{id}")intupdateCard1(Card card);//删除@Delete("delete from card where id = #{id}")intdeleteCard1(Card card);//添加@Insert("insert into card (card_num) values (#{num})")intinsertCard1(Card card);//注解查询——动态传参:更新数据// Card card = new Card();// card.setId(1);// card.setNum(9);// int updateCard1 = car.updateCard1(card);// System.out.println(updateCard1);//注解查询——动态传参:删除数据// Card card = new Card();// card.setId(3);// int deleteCard1 = car.deleteCard1(card);// System.out.println(deleteCard1);//注解查询——动态查询:添加数据// Card card = new Card();// card.setNum(1203);// int insertCard1 = car.insertCard1(card);// System.out.println(insertCard1);
存储过程
INOUT参数的使用以及PLSQL
-- 传入一个参数,如果大于3则返回100,否则返回500-- 用户变量作用于当前会话,局部变量作用于begin和end之间-- INOUT的使用delimiter $$
createprocedure my_pro8(INOUT in_param varchar(20))begindeclare param varchar(20);if in_param >3thenset param :=100;elseset param :=500;endif;set in_param := param;end $$
delimiter;set@param=3;call my_pro8(@param);select@param;-- PLSQL while-- PLSQL:过程语言,在存储过程中使用的语言,也就是和Java一样有循环、分支这些语句,但是很少用到delimiter $$
createprocedure my_pro11_while()begindeclare i int;set i :=0;while i <10doinsertinto boss (name)values(i);set i = i +1;endwhile;end $$
delimiter;call my_pro11_while();select*from boss;
既有输入又有输出参数的存储过程
-- 输出模式delimiter $$
createprocedure my_por2(IN in_param varchar(30),OUT out_param varchar(30))-- 存储过程体begin-- 查询员工表中id为103号员工的信息select last_name into out_param
from employees
where employee_id = in_param;end $$
delimiter;set@param :=null;call my_por2(103,@param);select@param;
有多个输出参数的存储过程
-- 如果要返回一行多列的数据 delimiter $$
createprocedure my_por5(IN in_param varchar(30),OUT out_param varchar(30),OUT out_param1 varchar(30),OUT out_param2 varchar(30))beginselect d.department_name, d.department_id, d.location_id into out_param, out_param1, out_param2
from employees as e innerjoin departments as d
on e.department_id = d.department_id
and e.last_name = in_param;end $$
delimiter;set@my_out1 :=null;set@my_out2 :=null;set@my_out3 :=null;call my_por5('Hunold',@my_out1,@my_out2,@my_out3);select@my_out1,@my_out2,@my_out3;
-- from后子查询:子句当成一个表来使用,要有字段名字和表名-- 一般将分组查询的表格作为from后子查询的子表-- select后子查询,两表之间的关联写在子句里面,from后子查询两表之间的关联写在外面-- 每个部门的平均工资的工资等级select*from(selectavg(salary)as ag
from employees
groupby department_id)as ag_emp innerjoin job_grades as j
on ag_emp.ag between j.lowest_sal and j.highest_sal;-- 查询员工工资比本部门平均工资高(不包含临界值)的员工的员工号,姓名和工资SELECT employee_id,last_name,salary,e.department_id
FROM employees e
INNERJOIN(SELECTAVG(salary) ag,department_id
FROM employees
GROUPBY department_id
) ag_dep
ON e.department_id = ag_dep.department_id
WHERE salary > ag_dep.ag;
select后子查询
-- select后子查询:子句当成一个字段来使用-- 利用部门表查询每个部门的员工个数、部门编号、部门名称select d.department_id, d.department_name,(selectcount(employee_id)from employees
where d.department_id = employees.department_id)from departments as d;
标量子查询
-- 标量子查询:子查询的结果是一个标量-- 查询工资比105号员工高的所有人信息select*from employees
where salary >(select salary
from employees
where employee_id =105);
行子查询
-- 行子查询:子句返回结果集是一行多列-- 行子查询可以使用等号,但是不可以使用大于、大于等于、小于、小于等于这类符号-- 查询与103号年龄和工资都相同的人的信息alterselect*from employees
where(year, salary)=(selectyear, salary
from employees
where employee_id =1);
列子查询
-- 列子查询:子查询的结果是一列多行-- 查询比103号或104号员工高的员工的信息select*from employees
where salary >any(select salary
from employees
where employee_id in(103,104));
-- 不等值内连接-- 查询员工所对应的工资等级alterselect j.grade_level,e.last_name
from employees as e innerjoin job_grades as j
on e.salary between j.lowest_sal and j.highest_sal
and e.last_name ='Hunold';
将数据按照第一个排序,如果第一个相同的情况下按照第二个排序
select*from rank
orderby times asc,timeasc;
游标的使用
-- 游标DELIMITER $$
createPROCEDURE my_pro12()begin-- 声明一个标志done, 用来判断游标是否遍历完DECLARE done INTDEFAULT0;-- 声明一个变量,用来存放从游标中提取的数据-- 特别注意这里的名字不能与由游标中使用的列明相同,-- 否则得到的数据都是NULLDECLARE lastName varchar(30)DEFAULTNULL;DECLARE salaryParam intDEFAULTNULL;-- 声明游标对应的 SQL 语句DECLARE cur CURSORFORselect last_name, salary from employees;-- 在游标循环到最后会将 done 设置为 1DECLARECONTINUEHANDLERFORNOT FOUND SET done =1;-- 执行查询open cur;-- 遍历游标每一行REPEAT-- 把一行的信息存放在对应的变量中FETCH cur INTO lastName, salaryParam;ifnot done then-- 这里就可以使用 lastName, salaryParam 对应的信息了select lastName, salaryParam;endif;
UNTIL done ENDREPEAT;CLOSE cur;end $$
DELIMITER;call my_pro12();