网站制作公司成都,中国建设银行网站怎么改支付密码是什么,电子商务网站建设和技术现状,网站经营网络备案信息管理系统复合查询 1. 复合查询的作用2. 创建将进行操作的表2.1 员工表 emp2.2 部门表 dept2.3 薪资等级表 3. 基本查询回顾4. 多表查询4.1 多表查询的定义4.2 笛卡尔积4.3 内连接 inner join4.4 交叉连接 cross join4.5 左外连接 left join4.6 右外连接 right join4.7 自连接 5. 子查询… 复合查询 1. 复合查询的作用2. 创建将进行操作的表2.1 员工表 emp2.2 部门表 dept2.3 薪资等级表 3. 基本查询回顾4. 多表查询4.1 多表查询的定义4.2 笛卡尔积4.3 内连接 inner join4.4 交叉连接 cross join4.5 左外连接 left join4.6 右外连接 right join4.7 自连接 5. 子查询5.1 单行子查询5.2 多行子查询5.2.1 in 关键字5.2.2 all 关键字5.2.3 any 关键字 5.3 多列子查询5.3.1 多列单行子查询5.3.2 多列多行子查询 5.4 from子句中使用子查询 6. 临时表7. 合并查询7.1 union 交集7.2 union all 并集 1. 复合查询的作用
复合查询的主要作用包括
数据整合 通过连接多个表你可以将分散在不同表中的相关信息整合到一起形成一个完整的数据集。这对于需要从多个来源获取信息的报告和分析非常有用。提高效率 相比于多次查询不同的表并将结果手动合并复合查询可以一次性完成所有操作减少了与数据库的交互次数提高了性能。数据关联 在数据库设计中通常会使用规范化来减少数据冗余这意味着相关数据会被存储在不同的表中。复合查询可以帮助你根据表之间的外键关系重新关联这些数据。条件筛选 复合查询可以让你基于多个表的数据进行复杂的条件筛选。例如你可以选择满足特定条件的所有记录即使这些条件涉及多个表中的字段。简化应用逻辑 在应用程序中直接使用复合查询可以从数据库层面上处理复杂的逻辑这样可以简化应用程序代码并且将业务逻辑更多地放在数据库层面这有助于维护和优化。
2. 创建将进行操作的表
2.1 员工表 emp
创建表
CREATE TABLE emp (empno INT PRIMARY KEY,ename VARCHAR(10),job VARCHAR(10),mgr INT,hiredate DATE,sal DECIMAL(7,2),comm DECIMAL(7,2),deptno INT
);插入数据
INSERT INTO emp (empno, ename, job, mgr, hiredate, sal, comm, deptno) VALUES
(7369, SMITH, CLERK, 7902, 1980-12-17, 800.00, NULL, 20),
(7499, ALLEN, SALESMAN, 7698, 1981-02-20, 1600.00, 300.00, 30),
(7521, WARD, SALESMAN, 7698, 1981-02-22, 1250.00, 500.00, 30),
(7566, JONES, MANAGER, 7839, 1981-04-02, 2975.00, NULL, 20),
(7654, MARTIN, SALESMAN, 7698, 1981-09-28, 1250.00, 1400.00, 30),
(7698, BLAKE, MANAGER, 7839, 1981-05-01, 2850.00, NULL, 30),
(7782, CLARK, MANAGER, 7839, 1981-06-09, 2450.00, NULL, 10),
(7788, SCOTT, ANALYST, 7566, 1987-04-19, 3000.00, NULL, 20),
(7839, KING, PRESIDENT, NULL, 1981-11-17, 5000.00, NULL, 10),
(7844, TURNER, SALESMAN, 7698, 1981-09-08, 1500.00, 0.00, 30),
(7876, ADAMS, CLERK, 7788, 1987-05-23, 1100.00, NULL, 20),
(7900, JAMES, CLERK, 7698, 1981-12-03, 950.00, NULL, 30),
(7902, FORD, ANALYST, 7566, 1981-12-03, 3000.00, NULL, 20),
(7934, MILLER, CLERK, 7782, 1982-01-23, 1300.00, NULL, 10);2.2 部门表 dept
创建表
CREATE TABLE dept (deptno INT PRIMARY KEY,dname VARCHAR(255),loc VARCHAR(255)
);插入数据
INSERT INTO dept (deptno, dname, loc)
VALUES
(10, ACCOUNTING, NEW YORK),
(20, RESEARCH, DALLAS),
(30, SALES, CHICAGO),
(40, OPERATIONS, BOSTON);2.3 薪资等级表
创建表
CREATE TABLE salgrade (grade TINYINT PRIMARY KEY,losal SMALLINT,hisal SMALLINT
);插入数据
INSERT INTO salgrade (grade, losal, hisal)
VALUES
(1, 700, 1200),
(2, 1201, 1400),
(3, 1401, 2000),
(4, 2001, 3000),
(5, 3001, 9999);3. 基本查询回顾 查询工资高于500或岗位为MANAGER的雇员同时还要满足他们的姓名首字母为大写的J 说明薪资500 or job MANAGRand 姓名首写为J 命令 select * from emp
where (sal500 or jobMANAGER) and ename like J%;按照部门号升序而雇员的工资降序排序 说明asc–升序desc–降序 命令 select * from emp
order by deptno asc, sal desc;使用年薪进行降序排序 说明年薪月薪*12ifnull(comm,0); comm可能为空值NUll空值NULL和任何值进行运算都为空置需要使用ifnull()函数 命令 错误示例 select ename, sal*12ifnull(comm,0) as 年薪 from emp
order by 年薪 desc;同学们可以发现年薪并没有进行排序这是为什么呢 答在这个查询中年薪 被当作字符串常量来处理而不是列别名(此时的列别名是年薪)。因此这个查询不会按预期的方式工作。正确的写法应该是去掉引号。 正确示例 select ename, sal*12ifnull(comm,0) as 年薪 from emp
order by 年薪 desc;显示工资最高的员工的名字和工作岗位 说明select max(sal) from emp 只能聚合出最高薪把此最高薪作为条件。 命令 select ename, job from emp
where sal(select max(sal) from emp) 显示工资高于平均工资的员工信息 说明select avg(sal) from emp 把该查询的结果作为条件 命令 select * from emp
where sal(select avg(sal) from emp);显示每个部门的平均工资和最高工资 说明先把部门聚合然后使用聚合函数最高工资max()平均工资avg()使用format()函数格式平均数 命令 select deptno, max(sal) 最高工资, format(avg(sal),2) 最低工资 from emp
group by deptno;显示平均工资低于2000的部门号和它的平均工资 说明having 平均工资 2000 命令 错误示例 select deptno, format(avg(sal),2) 平均工资 from emp
group by deptno
having 平均工资 2000;同学们可以发现这里的 平均工资2000 并没有起作用这是为什么呢 答因为 having 子句用于过滤分组后的结果format(avg(sal),2) 处理后的结果不再属于分组后的结果重命名的 ‘平均工资’ 的列不再是分组后的列名分组后的列名是avg(sal)。 正确示例 命令 select deptno, format(avg(sal),2) 平均工资 from emp
group by deptno
having avg(sal) 2000;命令 select deptno, avg(sal) 平均工资 from emp
group by deptno
having 平均工资 2000;显示每种岗位的雇员总数平均工资 说明以 job 聚合 命令select jobcount(*), format(avg(sal),2) from emp group by job;
4. 多表查询
4.1 多表查询的定义
多表查询是指在数据库中从两个或多个表中检索数据的SQL查询。这种查询允许用户基于某些条件将不同表中的数据连接起来从而获得更复杂和全面的信息。多表查询是关系型数据库管理系统的强大功能之一它能够帮助用户分析和处理分布在多个表中的数据。
在进行多表查询时通常会使用到JOIN操作JOIN可以分为几种类型包括
INNER JOIN内连接返回两个表中匹配的所有行。只有当两个表中存在匹配的数据时才会产生结果记录。 LEFT JOIN左连接返回左表中的所有记录以及右表中与之匹配的记录如果右表没有匹配则结果中右表的字段为NULL。 RIGHT JOIN右连接返回右表中的所有记录以及左表中与之匹配的记录如果左表没有匹配则结果中左表的字段为NULL。 FULL OUTER JOIN全外连接返回左表和右表中的所有记录当某一边没有匹配时另一边的字段将填充为NULL。 CROSS JOIN交叉连接返回左表和右表的笛卡尔积即左表的每一行与右表的每一行组合。
4.2 笛卡尔积
笛卡尔积Cartesian Product是指两个表中所有行的组合。具体来说如果表 A 有 m 行表 B 有 n 行那么它们的笛卡尔积将包含 m×n行每行是表 A 中的一行与表 B 中的一行的组合。
定义 在数据库中笛卡尔积是通过 CROSS JOIN 操作实现的。如果没有指定任何连接条件结果集将包含所有可能的行组合()。CROSS JOIN 可以显式地使用 CROSS JOIN 关键字也可以通过逗号, 分隔表名并在 WHERE 子句中不指定任何连接条件来实现。
示例 创建表t1:
create table t1(name char(10));insert into t1 values(李明), (李华), (李刚);创建表t2:
create table t2(name char(10));insert into t2 values(高渐离), (王昭君), (嫦娥);查询两个表
命令select * from t1 cross join t1; 或 命令select * from t1, t2;
图解
4.3 内连接 inner join
定义返回两个表中匹配的所有行。只有当两个表中存在匹配的数据时才会产生结果记录。 语法
SELECT *
FROM table1
INNER JOIN table2
ON table1.column_name table2.column_name;ON 子句的主要作用
指定连接条件 ON 子句用于指定两个表之间的连接条件这些条件决定了哪些行应该被组合在一起。过滤结果集 通过 ON 子句可以有效地过滤掉不符合条件的行从而减少结果集的大小提高查询性能。提高查询的可读性和维护性 使用 ON 子句可以使查询更加清晰和易于理解特别是对于复杂的多表查询。 将连接条件放在 ON 子句中而不是放在 WHERE 子句中可以使查询结构更加明确便于维护和调试。
示例
显示雇员名、雇员工资以及所在部门的名字因为上面的数据来自emp和dept表因此要联合查询 命令 # 使用 inner join 时可以省略inner单独使用joinselect emp.ename, emp.sal, dept.dnamefrom emp inner join depton emp.deptno dept.deptno;图解 一种简单的写法
select ename, sal, dname
from emp join dept
on emp.deptno emp.deptno4.4 交叉连接 cross join
语法
SELECT t1.id, t1.name, t2.city
FROM t1
CROSS JOIN t2;CROSS JOIN 生成的是笛卡尔积但你可以通过 WHERE 子句来过滤结果集从而实现类似于 INNER JOIN 的效果。
示例 显示雇员名、雇员工资以及所在部门的名字因为上面的数据来自emp和dept表因此要联合查询
命令
select emp.ename, emp.sal, dept.dname
from emp , dept
where emp.deptno dept.deptno;图解
4.5 左外连接 left join
定义返回左表中的所有记录以及右表中与之匹配的记录如果右表没有匹配则结果中右表的字段为 NULL。
语法
SELECT *
FROM table1
LEFT JOIN table2
ON table1.column_name table2.column_name;示例 创建两张表
-- 建两张表
create table stu (id int, name varchar(30)); -- 学生表
insert into stu values(1,jack),(2,tom),(3,kity),(4,nono);create table exam (id int, grade int); -- 成绩表
insert into exam values(1, 56),(2,76),(11, 8);查询所有学生的成绩如果这个学生没有成绩也要将学生的个人信息显示出来 命令
select * from stu left join exam
on stu.id exam.id;由上图图可以看出左边表的内容去全部显示出来右边表内进行对左表进行匹配。当左边表和右边表没有匹配时也会显示左边表的数据但是右边表的数据为空。
4.6 右外连接 right join
定义返回右表中的所有记录以及左表中与之匹配的记录如果左表没有匹配则结果中左表的字段为 NULL。
语法
SELECT *
FROM table1
RIGHT JOIN table2
ON table1.column_name table2.column_name;示例 创建两张表
-- 建两张表
create table stu (id int, name varchar(30)); -- 学生表
insert into stu values(1,jack),(2,tom),(3,kity),(4,nono);create table exam (id int, grade int); -- 成绩表
insert into exam values(1, 56),(2,76),(11, 8);查询所有学生的成绩如果这个学生没有成绩也要将学生的个人信息显示出来 命令
select * from exam left join stu
on stu.id exam.id;由上图可知右侧表的内容全部显示出来左侧的表进行匹配如果匹配不到数据则显示空。
4.7 自连接
定义自连接是指在同一张表连接查询。
语法
SELECT *
FROM table1 t1 , table1 t2
WHERE t1.column_name t2.id;注自连接的表必须起别名
示例
显示员工的姓名及上级领导姓名mgr是员工领导的编号empno是员工的编号 领导也是员工领导和员工都在emp表。 命令
select t1.ename, t2.ename 领导 from emp t1, emp t2
where t1.mgr t2.empno;5. 子查询
子查询是指嵌入在其他sql语句中的select语句也叫嵌套查询
5.1 单行子查询
定义返回一行记录的子查询
示例 显示SMITH同一部门的员工 命令 select ename from emp
where emp.deptno (select deptno from emp where enameSMITH);显示员工FORD的上级领导的编号和姓名mgr是员工领导的编号empno是员工编号 命令 select empno, ename from emp
where empno (select mgr from emp where enameFORD);5.2 多行子查询
定义返回多行记录的子查询
5.2.1 in 关键字
定义用于检查某个值是否在一个列表中。它可以用在子查询中也可以直接列出具体的值。 也可以使用 not in。
示例
查询和10号部门的工作岗位相同的雇员打印出他们的名字岗位工资部门号但是不包含10号自 己的
命令
select ename, job, sal, deptno
from emp
where job in (select job from emp where deptno 10) and deptno 10; 5.2.2 all 关键字
定义用于比较一个值与子查询返回的所有值。通常与比较运算符如 , , , , , 一起使用。 有时可以使用 max() 或 min() 聚合函数代替。
示例
显示工资比部门30的所有员工的工资高的员工的姓名、工资和部门号 使用 all 关键字 命令 select ename, sal, deptno from emp
where sal all(select sal from emp where deptno 30);使用 max() 聚合函数 命令 select ename, sal, deptno from emp
where sal (select max(sal) from emp group by deptno haivng deptno 30);5.2.3 any 关键字
定义用于比较一个值与子查询返回的任意一个值。通常与比较运算符如 , , , , , 一起使用。 有时可以使用 max() 或 min() 聚合函数代替。
示例 显示工资比部门30的任意员工的工资高的员工的姓名、工资和部门号包含自己部门的员工 使用 any 关键字 命令 select ename, sal, deptno from emp
where sal any(select sal from emp where deptno 30);使用 min() 聚合函数 命令 select ename,sal, ddptno from emp
where sal (select min(sal) from emp group by deptno having deptno30);5.3 多列子查询
5.3.1 多列单行子查询
单行子查询是指子查询只返回单列单行数据多行子查询是指返回单列多行数据都是针对单列而言 的而多列子查询则是指查询返回多个列数据的子查询语句。
示例 查询和SMITH的部门和岗位完全相同的所有雇员不含SMITH本人
命令
select * from emp
where (deptno,job) (select deptno, job from emp where ename SMITH) and ename SMITH; 5.3.2 多列多行子查询
多列多行子查询和多行子查询类似也是用 in 关键字进行查询。
示例 查询和SMITH的部门和岗位完全相同的所有雇员不含SMITH本人
命令
select * from emp
where (deptno,job) (select deptno, job from emp where ename SMITH) and ename SMITH; 5.4 from子句中使用子查询
子查询语句出现在from子句中。这里要用到数据查询的技巧把一个子查询当做一个临时表使用章节6讲解临时表。
示例 显示每个高于自己部门平均工资的员工的姓名、部门、工资、平均工资 命令 select t1.ename, t1.deptno, t1.sal, t2.avgsal
from emp t1, (select deptno, avg(sal) avgsal from emp group by deptno) t2
where t1.age t2.avgsal and t1.deptnot2.deptno;查找每个部门工资最高的人的姓名、工资、部门、最高工资 命令 select t1.ename, t1.sal, t1.deptno, t2.maxsal
from emp t1, (select deptno, max(sal) maxsal from emp group by deptno) t2
where t1.sal t2.maxsal and t1.deptno t2.deptno;显示每个部门的信息部门名编号地址和人员数量 1使用多表查询 select t2.dname, t2.deptno,t2.loc, count(*)
from emp t1, dept t2
where t1.deptno t2.deptno
group by t2.dname,t2.deptno,t2.loc; 2使用子查询 select t1.dname,t1.deptno,t1.loc, t2.count
from dept t1, (select deptno, count(*) count from emp group by deptno) t2
where t1.deptnot2.deptno;6. 临时表
MySQL一切为表查询结束、分组结束······后就会生一个临时表。
在某些情况下MySQL 会使用临时表来存储中间结果。这些情况包括但不限于以下几种 复杂的查询 当查询涉及多个JOIN、UNION、GROUP BY、ORDER BY 或子查询时MySQL 可能会创建临时表来存储中间结果。 分组和排序 如果查询包含 GROUP BY 或 ORDER BY 子句并且结果集较大MySQL 可能会使用临时表来存储中间结果以便进行分组或排序操作。 子查询 在某些情况下特别是当子查询的结果集较大时MySQL 可能会将子查询的结果存储在临时表中。 临时表显式创建 用户可以显式地创建临时表来存储中间结果以提高查询性能或简化复杂查询。
MySQL 使用两种类型的临时表
内存临时表Memory Temporary Table 这种临时表存储在内存中使用 MEMORY 存储引擎。内存临时表的优点是速度快但受限于可用内存大小。如果数据量超过内存限制MySQL 会自动将其转换为磁盘临时表。 磁盘临时表Disk Temporary Table 这种临时表存储在磁盘上使用 MyISAM 或 InnoDB 存储引擎。磁盘临时表的优点是可以处理更大的数据集但速度相对较慢。
示例 显示每个高于自己部门平均工资的员工的姓名、部门、工资、平均工资 命令
select t1.ename, t1.deptno, t1.sal, t2.avgsal
from emp t1, (select deptno, avg(sal) avgsal from emp group by deptno) t2
where t1.age t2.avgsal and t1.deptnot2.deptno;7. 合并查询
在实际应用中为了合并多个select的执行结果可以使用集合操作符 unionunion all
7.1 union 交集
该操作符用于取得两个结果集的交集。当使用该操作符时会自动去掉结果集中的重复行。
示例 将工资大于2500或职位是MANAGER的人找出来 命令
select * from emp where sal 2500 union
select * from emp where job MANAGER;7.2 union all 并集
该操作符用于取得两个结果集的并集。当使用该操作符时不会去掉结果集中的重复行。
示例 工资大于25000或职位是MANAGER的人找出来 命令
select * from emp where sal 2500 union all
select * from emp where job MANAGER; 文章转载自： http://www.morning.pszw.cn.gov.cn.pszw.cn http://www.morning.gmmyn.cn.gov.cn.gmmyn.cn http://www.morning.hnk25076he.cn.gov.cn.hnk25076he.cn http://www.morning.jrsgs.cn.gov.cn.jrsgs.cn http://www.morning.tnjkg.cn.gov.cn.tnjkg.cn http://www.morning.dgsx.cn.gov.cn.dgsx.cn http://www.morning.qxnlc.cn.gov.cn.qxnlc.cn http://www.morning.bftqc.cn.gov.cn.bftqc.cn http://www.morning.kntsd.cn.gov.cn.kntsd.cn http://www.morning.mfbzr.cn.gov.cn.mfbzr.cn http://www.morning.pqqhl.cn.gov.cn.pqqhl.cn http://www.morning.brwnd.cn.gov.cn.brwnd.cn http://www.morning.gtcym.cn.gov.cn.gtcym.cn http://www.morning.sqmlw.cn.gov.cn.sqmlw.cn http://www.morning.ghfmd.cn.gov.cn.ghfmd.cn http://www.morning.wbysj.cn.gov.cn.wbysj.cn http://www.morning.zbhfs.cn.gov.cn.zbhfs.cn http://www.morning.jqswf.cn.gov.cn.jqswf.cn http://www.morning.mgmyt.cn.gov.cn.mgmyt.cn http://www.morning.tkrpt.cn.gov.cn.tkrpt.cn http://www.morning.ddtdy.cn.gov.cn.ddtdy.cn http://www.morning.xfmzk.cn.gov.cn.xfmzk.cn http://www.morning.llgpk.cn.gov.cn.llgpk.cn http://www.morning.pabxcp.com.gov.cn.pabxcp.com http://www.morning.frnjm.cn.gov.cn.frnjm.cn http://www.morning.zcyxq.cn.gov.cn.zcyxq.cn http://www.morning.wfpmt.cn.gov.cn.wfpmt.cn http://www.morning.ktmbr.cn.gov.cn.ktmbr.cn http://www.morning.txqgd.cn.gov.cn.txqgd.cn http://www.morning.skmzm.cn.gov.cn.skmzm.cn http://www.morning.gslz.com.cn.gov.cn.gslz.com.cn http://www.morning.bpmdz.cn.gov.cn.bpmdz.cn http://www.morning.xtqld.cn.gov.cn.xtqld.cn http://www.morning.rkdhh.cn.gov.cn.rkdhh.cn http://www.morning.jjzbx.cn.gov.cn.jjzbx.cn http://www.morning.qqfcf.cn.gov.cn.qqfcf.cn http://www.morning.zfyfy.cn.gov.cn.zfyfy.cn http://www.morning.kwnbd.cn.gov.cn.kwnbd.cn http://www.morning.zxrtt.cn.gov.cn.zxrtt.cn http://www.morning.nzlsm.cn.gov.cn.nzlsm.cn http://www.morning.mrncd.cn.gov.cn.mrncd.cn http://www.morning.prznc.cn.gov.cn.prznc.cn http://www.morning.lgcqj.cn.gov.cn.lgcqj.cn http://www.morning.huxinzuche.cn.gov.cn.huxinzuche.cn http://www.morning.nkyc.cn.gov.cn.nkyc.cn http://www.morning.krbjb.cn.gov.cn.krbjb.cn http://www.morning.ybgcn.cn.gov.cn.ybgcn.cn http://www.morning.tnjz.cn.gov.cn.tnjz.cn http://www.morning.xprzq.cn.gov.cn.xprzq.cn http://www.morning.bprsd.cn.gov.cn.bprsd.cn http://www.morning.qfrmy.cn.gov.cn.qfrmy.cn http://www.morning.rzdzb.cn.gov.cn.rzdzb.cn http://www.morning.ydxx123.cn.gov.cn.ydxx123.cn http://www.morning.dkbsq.cn.gov.cn.dkbsq.cn http://www.morning.trqsm.cn.gov.cn.trqsm.cn http://www.morning.stsnf.cn.gov.cn.stsnf.cn http://www.morning.litao7.cn.gov.cn.litao7.cn http://www.morning.ngqty.cn.gov.cn.ngqty.cn http://www.morning.yskhj.cn.gov.cn.yskhj.cn http://www.morning.ffgbq.cn.gov.cn.ffgbq.cn http://www.morning.rnxs.cn.gov.cn.rnxs.cn http://www.morning.xckqs.cn.gov.cn.xckqs.cn http://www.morning.qkrqt.cn.gov.cn.qkrqt.cn http://www.morning.xsymm.cn.gov.cn.xsymm.cn http://www.morning.gkmwk.cn.gov.cn.gkmwk.cn http://www.morning.nysjb.cn.gov.cn.nysjb.cn http://www.morning.rbmnq.cn.gov.cn.rbmnq.cn http://www.morning.ydtdn.cn.gov.cn.ydtdn.cn http://www.morning.rjcqb.cn.gov.cn.rjcqb.cn http://www.morning.frtt.cn.gov.cn.frtt.cn http://www.morning.mrtdq.cn.gov.cn.mrtdq.cn http://www.morning.hffpy.cn.gov.cn.hffpy.cn http://www.morning.gpcy.cn.gov.cn.gpcy.cn http://www.morning.lpcpb.cn.gov.cn.lpcpb.cn http://www.morning.jwqqd.cn.gov.cn.jwqqd.cn http://www.morning.qckwj.cn.gov.cn.qckwj.cn http://www.morning.kyytt.cn.gov.cn.kyytt.cn http://www.morning.txlnd.cn.gov.cn.txlnd.cn http://www.morning.mprtj.cn.gov.cn.mprtj.cn http://www.morning.tllhz.cn.gov.cn.tllhz.cn
