(补充) 树形表关联等级查询示例
所谓’树形表’就是指这个表的数据是有等级关系的,需要通过’自连接查询’语法来实现.目的是为了通过查询出该张表的各个数据之等级关系,以便在前端展示树形等级关系数据.
前端需要展示的树形等级示例如下:
或者是下面这个
数据表通常如下:
其中,'parentid’字段的数据所指向的,就是同一张表中的’id’字段的数据,用这个来表示等级关系.
SQL语句查询实现语句示例如下:
SELECT a.id one_id,a.pname one_pname,b.id,two_id,b.pname,two_pname,c.id three_id,c.pname three_pname
FROM teachplan a LEFT JOIN teachplan b ON b.parentid = a.id
LEFT JOIN teachplan c ON c.parentid = b.id
WHERE
a.parentid = '0'
AND a.courseid = '4028e581617f945f01617f9dabc40000'
ORDER BY
a.orderby,
b.orderby,
c.orderby
实体封装类通常如下:
Mybatis的需要用到Map封装,代码示例如下:
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd" >
<mapper namespace="com.xuecheng.manage_course.dao.TeachplanMapper">
<resultMap type="com.xuecheng.framework.domain.course.ext.TeachplanNode" id="teachplanMap">
<id property="id" column="one_id"/>
<result property="pname" column="one_name"/>
<collection property="children" ofType="com.xuecheng.framework.domain.course.ext.TeachplanNode">
<id property="id" column="two_id"/>
<result property="pname" column="two_name"/>
<collection property="children" ofType="com.xuecheng.framework.domain.course.ext.TeachplanNode">
<id property="id" column="three_id"/>
<result property="pname" column="three_name"/>
</collection>
</collection>
</resultMap>
<select id="selectList" resultMap="teachplanMap" parameterType="java.lang.String">
SELECT
a.id one_id,
a.pname one_name,
b.id two_id,
b.pname two_name,
c.id three_id,
c.pname three_name
FROM
teachplan a LEFT JOIN teachplan b
ON
a.id = b.parentid
LEFT JOIN
teachplan c
ON
b.id = c.parentid
WHERE
a.parentid = '0'
<if test="_parameter!=null and _parameter!=''">
and a.courseid=#{courseId}
</if>
ORDER BY
a.orderby,
b.orderby,
c.orderby
</select>
</mapper>
(补充)自连接查询
概念
所谓自连接查询,就是为同一张表定义多个别名,然后让其自己查询.
左外连接式自连接查询
格式:SELECT * FROM 同一张表 别名A LEFT JOIN 同一张表 别名B ON 条件;
例如一:
SELECT * FROM teachplan a LEFT JOIN teachplan b ON b.parentid = a.id;
内连接式自连接查询
格式:SELECT * FROM 同一张表 别名A,同一张表 别名B WHERE 条件;
例如:
SELECT * FROM teachplan a,teachplan b WHERE a.parentid = b.id;
多表查询
SELECT * FROM 表A,表B
多表查询能够准确地,去重复式地查询出数据
基础注意:不管是内连接查询还是外连接查询,被表达的字段必须具有关联. 比如两个存在外键约束的关系.即:要被声明为外键约束.
多表查询语法体(补充)
格式:SELECT * FROM 表名A 自定义表名A的虚拟表名,表名B 自定义表名B的虚拟表名 WHERE 自定义表名A的虚拟表名.列名 条件表达符号 自定义表名B的虚拟表名.列名
列如:
SELECT * FROM sys_user_role ur,sys_role sr WHERE ur.`roleId`=sr.`id` AND ur.`userId`=1;
内外连接的区别
一.多表查询之内连接查询
概念
显示内连接和隐式内连接只有语法外表的不同,其逻辑是没有区别的**
方式1:隐式内连接
格式:
SELECT * FROM 表A,表B WHERE 条件
列如:
SELECT * FROM dept,emp WHERE emp.`dept_id`=dept.`id`;
// WHERE条件表达式:emp表的’dtpt_id’字段要与 dept表的’id’字段的数据内容一致.
方式2:显式内连接
格式:
SELECT * FROM 表A INNER JOIN 表B ON 条件表达式
列如:
SELECT * FROM dept INNER JOIN emp ON dept.`id`=emp.`dept_id`;
//条件表达式:emp表的’dtpt_id’字段要与 dept表的’id’字段的数据内容一致.
二.多表查询之外连接
概念
左外连接:
用左边表的记录去匹配右边表的记录，如果符合条件的则显示；否则，显示 NULL
可以理解为：在内连接的基础上保证左表的数据全部显示
右外连接:
用右边表的记录去匹配左边表的记录，如果符合条件的则显示；否则，显示 NULL
可以理解为：在内连接的基础上保证右表的数据全部显示
注意:左外连接和左内连接的不同之处在于
方式1:左外连接
格式: select 字段列表 from 表1 left [outer] join 表2 on 条件；
列如1:
SELECT * FROM emp LEFT OUTER JOIN dept ON emp.`dept_id`=dept.`id`;
列如2:
SELECT t1.*,t2.`name` FROM emp t1 LEFT JOIN dept t2 ON t1.`dept_id` = t2.`id`;
列如3: 查询所有员工的姓名及其直接上级的姓名,没有领导的员工也需要查询
表—:
CREATE TABLE emp (
id INT PRIMARY KEY, -- 员工id
ename VARCHAR(50), -- 员工姓名
job_id INT, -- 职务id
mgr INT , -- 上级领导
joindate DATE, -- 入职日期
salary DECIMAL(7,2), -- 工资
bonus DECIMAL(7,2), -- 奖金
dept_id INT, -- 所在部门编号
CONSTRAINT emp_jobid_ref_job_id_fk FOREIGN KEY (job_id) REFERENCES job (id),
CONSTRAINT emp_deptid_ref_dept_id_fk FOREIGN KEY (dept_id) REFERENCES dept (id)
);
列如3的语句:
SELECT e1.`ename`,e2.`ename` "上司" FROM emp e1 LEFT OUTER JOIN emp e2 ON e1.`mgr`=e2.`id`;
方式2:右外连接
格式:select 字段列表 from 表1 right [outer] join 表2 on 条件；
列如:
SELECT
t1.*,t2.`NAME`
FROM emp t1
LEFT JOIN
dept t2 ON
t1.`dept_id` = t2.`id`;
三.多表查询之子查询
概念
括号内子查询的sql查询语句的格式是不需要特定约束的,就和平常使用聚合运算/加减乘除运算符使用时的格式一样.
子查询语句分大致为3个语法体
,分别为’=(子查询SQL语句体)‘和’in(子查询SQL语句体)‘以及’(子查询SQL语句体)as 虚拟表别名’.
注意:
1.子查询结果只要是单行单列，肯定在 WHERE 后面作为条件，父查询使用：比较运算符，如：> 、<、<>、= 等
2.子查询结果只要是多列，则在 FROM 后面作为表进行二次查询
3.一个主查询中可以存在多个子查询
4.主查询可以调用子查询中的别名
5.子查询可以当作主查询在FROM后面的虚拟表名
第一个语法体
=(子查询SQL语句体)
格式:SELECT 欲并列显示的字段 FROM 表名=(子查询DQL语句体);
列如:
SELECT * FROM emp WHERE emp.`salary` = (SELECT MAX(salary) FROM emp);
第二个语法体
(子查询SQL语句体)AS 虚拟表别名;
格式:SELECT 欲并列显示的字段 FROM 表名 (子查询SQL语句体); (如果不把子查询当作虚拟表用拿来用,可省略别名)
列如:
SELECT NAME,gender,salary FROM emp WHERE (SELECT MAX(salary) FROM emp);
列如:
SELECT d.`id`,
d.`loc`,
w2.person
FROM dept d,
(SELECT emp.`dept_id`,COUNT(id)person FROM emp GROUP BY emp.`dept_id`)AS w2 -- 子查询
WHERE d.`id` = w2.dept_id;
第三个语法体
IN(子查询SQL语句体)
格式:SELECT 欲并列显示的字段 FROM 表名 IN(子查询SQL语句体)
列如一:
SELECT * FROM emp WHERE dept_id IN (SELECT id FROM dept WHERE NAME = '财务部' OR NAME = '市场部');
例如二：
SELECT * FROM student AS st WHERE st.`s_id` IN (SELECT s_id FROM score WHERE c_id = '01');
主查询存在多个子查询-重要
一个主查询中,可以声明定义多个子查询,并且能够在主查询中调用子查询内声明的别名
格式:语法体(子查询SQL语句体A)自定义子查询虚拟表名,(子查询SQL语句体B)自定义子查询虚拟表名
列如:
SELECT ss1.city,t FROM (SELECT s1.`city` FROM student_score s1 WHERE s1.`age`<=25 GROUP BY s1.`city`)ss1,(SELECT s2.`city`,COUNT(s2.`city`)t FROM student_score s2 GROUP BY s2.`city`)ss2 WHERE ss1.city=ss2.city;
列如:
SELECT 女学生平均分,男学生平均分,总体平均分 FROM
(SELECT AVG(score)AS 女学生平均分 FROM student2 WHERE sex='女')AS nv, -- 子查询虚拟表,女学生平均分
(SELECT AVG(score)AS 男学生平均分 FROM student2 WHERE sex='男')AS nan,-- 子查询虚拟表 男同学平均分
(SELECT AVG(score)AS 总体平均分 FROM student2)AS zong; -- 子查询虚拟表,总平均分
可以通过’表名.*'的形式,显示单一的,某个表的数据
事物
概念
在一个数据的操作过程中,如果其中一个过程出现失败,那么所有过程(包括已经执行的)都视为失败,且将数据库状态(包括已经执行的)还原到此前的状态. 即:要么所有过程都成功,要么所有过程都失败.
开启事务:START TRANSACTION;
如果出现错误,则使用回滚事物:ROLLBACK;
如果过程一切顺利,则使用提交事务:COMMIT
内外连查询,子查询演示
-- 部门表
CREATE TABLE dept (
id INT PRIMARY KEY PRIMARY KEY, -- 部门id
dname VARCHAR(50), -- 部门名称
loc VARCHAR(50) -- 部门所在地
);
-- 添加4个部门
INSERT INTO dept(id,dname,loc) VALUES
(10,'教研部','北京'),
(20,'学工部','上海'),
(30,'销售部','广州'),
(40,'财务部','深圳');
-- 职务表，职务名称，职务描述
CREATE TABLE job (
id INT PRIMARY KEY,
jname VARCHAR(20),
description VARCHAR(50)
);
-- 添加4个职务
INSERT INTO job (id, jname, description) VALUES
(1, '董事长', '管理整个公司，接单'),
(2, '经理', '管理部门员工'),
(3, '销售员', '向客人推销产品'),
(4, '文员', '使用办公软件');
-- 员工表
CREATE TABLE emp (
id INT PRIMARY KEY, -- 员工id
ename VARCHAR(50), -- 员工姓名
job_id INT, -- 职务id
mgr INT , -- 上级领导
joindate DATE, -- 入职日期
salary DECIMAL(7,2), -- 工资
bonus DECIMAL(7,2), -- 奖金
dept_id INT, -- 所在部门编号
CONSTRAINT emp_jobid_ref_job_id_fk FOREIGN KEY (job_id) REFERENCES job (id),
CONSTRAINT emp_deptid_ref_dept_id_fk FOREIGN KEY (dept_id) REFERENCES dept (id)
);
-- 添加员工
INSERT INTO emp(id,ename,job_id,mgr,joindate,salary,bonus,dept_id) VALUES
(1001,'孙悟空',4,1004,'2000-12-17','8000.00',NULL,20),
(1002,'卢俊义',3,1006,'2001-02-20','16000.00','3000.00',30),
(1003,'林冲',3,1006,'2001-02-22','12500.00','5000.00',30),
(1004,'唐僧',2,1009,'2001-04-02','29750.00',NULL,20),
(1005,'李逵',4,1006,'2001-09-28','12500.00','14000.00',30),
(1006,'宋江',2,1009,'2001-05-01','28500.00',NULL,30),
(1007,'刘备',2,1009,'2001-09-01','24500.00',NULL,10),
(1008,'猪八戒',4,1004,'2007-04-19','30000.00',NULL,20),
(1009,'罗贯中',1,NULL,'2001-11-17','50000.00',NULL,10),
(1010,'吴用',3,1006,'2001-09-08','15000.00','0.00',30),
(1011,'沙僧',4,1004,'2007-05-23','11000.00',NULL,20),
(1012,'李逵',4,1006,'2001-12-03','9500.00',NULL,30),
(1013,'小白龙',4,1004,'2001-12-03','30000.00',NULL,20),
(1014,'关羽',4,1007,'2002-01-23','13000.00',NULL,10);
-- 工资等级表
CREATE TABLE salarygrade (
grade INT PRIMARY KEY, -- 级别
losalary INT, -- 最低工资
hisalary INT -- 最高工资
);
-- 添加5个工资等级
INSERT INTO salarygrade(grade,losalary,hisalary) VALUES
(1,7000,12000),
(2,12010,14000),
(3,14010,20000),
(4,20010,30000),
(5,30010,99990);
-- 1.查询所有员工信息。查询员工编号，员工姓名，工资，职务名称，职务描述
SELECT emp.`id`,emp.`ename`,emp.`salary`,job.`jname`,job.`description` FROM emp,job WHERE emp.`job_id`=job.`id`;
-- 2.查询员工编号，员工姓名，工资，职务名称，职务描述，部门名称，部门位置
SELECT
e.`id`,
e.`ename`,
e.`salary`,
j.`jname`,
j.`description`,
d.`dname`,
d.`loc`
FROM
emp e,-- 员工"emp'化名为'e'
job j,-- 职务'job'化名为'j'
dept d-- 部门'dept'化名为'd'
WHERE e.`job_id`=j.`id`
AND e.`dept_id`= d.`id`;
-- 3.查询员工姓名，工资，工资等级
SELECT
emp.`ename`,
emp.`salary`,
salarygrade.`grade`
FROM emp,salarygrade
WHERE emp.`salary`
BETWEEN salarygrade.`losalary` AND salarygrade.`hisalary`;
SELECT * FROM emp;
-- 4.查询员工姓名，工资，职务名称，职务描述，部门名称，部门位置，工资等级
SELECT e.`ename`,e.`salary`,j.`jname`,j.`description`,d.`dname`,d.`loc`,s.`grade`
FROM emp e,job j,dept d,salarygrade s
WHERE e.`job_id`=j.`id` AND e.`dept_id` = d.`id`
AND e.`salary`>=s.`losalary` AND e.`salary`<=s.`hisalary`;
-- 5.查询出部门编号、部门名称、部门位置、部门人数
SELECT emp.`dept_id`,COUNT(id) FROM emp GROUP BY emp.`dept_id`;
SELECT d.`id`,
d.`loc`,
w2.person
FROM dept d,
(SELECT emp.`dept_id`,COUNT(id)person FROM emp GROUP BY emp.`dept_id`)AS w2 -- 子查询
WHERE d.`id` = w2.dept_id;
-- 6.查询所有员工的姓名及其直接上级的姓名,没有领导的员工也需要查询
-- SELECT emp.`ename`,emp.`mgr` FROM emp WHERE emp.`mgr`=emp.`id`
SELECT * FROM emp;
SELECT e1.`ename`,e2.`ename` "上司" FROM emp e1 LEFT OUTER JOIN emp e2 ON e1.`mgr`=e2.`id`;
事物演示
CREATE TABLE shiwu_tab(
id INT PRIMARY KEY, -- 主键
NAME VARCHAR(20), --
age INT,
money DOUBLE -- 金钱
);
INSERT INTO shiwu_tab(id,NAME,age,money) VALUES(1,"马化腾",18,1002);
INSERT INTO shiwu_tab(id,NAME,age,money) VALUES(2,"马云",21,2500);
-- TRANSACTION;
START TRANSACTION; -- 马化腾原始金额 1700,马云原始金额1100
UPDATE shiwu_tab SET money = money + 111500 WHERE NAME ="马化腾";
UPDATE shiwu_tab SET money = money + 100 WHERE NAME ="马云";
-- UPDATE shiwu_tab SET money = money +1100 WHERE NAME ="马化腾";
COMMIT; -- 过程一切顺利则提交事务
ROLLBACK; -- 出现错误则回滚事物
SELECT * FROM shiwu_tab;
主查询存在多个子查询演示
任务:统计所有用户年纪都小于(包含)25岁的城市的名称和以及城市总人数。
材料代码:
CREATE TABLE student_score(
id INT PRIMARY KEY AUTO_INCREMENT,-- ID
NAME VARCHAR(40),-- 名字
age INT,
city VARCHAR(10) -- 城市
);
INSERT INTO student_score(NAME,age,city) VALUES("李明",20,"成都");
INSERT INTO student_score(NAME,age,city) VALUES('张红',21,'南京');
INSERT INTO student_score(NAME,age,city) VALUES('陈亮',18,'北京');
INSERT INTO student_score(NAME,age,city) VALUES("学霸",20,"北京");
INSERT INTO student_score(NAME,age,city) VALUES("王元鹅",30,"西安");
SELECT * FROM student_score;
INSERT INTO student_score(NAME,age,city) VALUE("陈双",30,"北京");
INSERT INTO student_score(NAME,age,city) VALUES("陈豪",23,"成都");
INSERT INTO student_score(NAME,age,city) VALUES("何陈",32,"成都");
多个子查询演示代码
SELECT ss1.city,t FROM (SELECT s1.`city` FROM student_score s1 WHERE s1.`age`<=25 GROUP BY s1.`city`)ss1,(SELECT s2.`city`,COUNT(s2.`city`)t FROM student_score s2 GROUP BY s2.`city`)ss2 WHERE ss1.city=ss2.city;
7384
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
