【SQL基础】SQL查询语句实例

参考自：https://www.w3school.com.cn/sql/index.asp

下面举实例：员工表、部门表、薪资等级表。

//附上sql语句: 薪资等级表SALGRADE、部门表DEPT、员工表EMP
CREATE TABLE DEPT(
	DEPTNO int(2) not null ,
	DNAME VARCHAR(14) ,
	LOC VARCHAR(13),
	primary key (DEPTNO)
);
CREATE TABLE EMP(
	EMPNO int(4) not null ,
	ENAME VARCHAR(10),
	JOB VARCHAR(9),
	MGR INT(4),
	HIREDATE DATE DEFAULT NULL,
	SAL DOUBLE(7,2),
	COMM DOUBLE(7,2),
	primary key (EMPNO),
	DEPTNO INT(2) 
);
CREATE TABLE SALGRADE( 
	GRADE INT,
	LOSAL INT,
	HISAL INT 
);

INSERT INTO DEPT ( DEPTNO, DNAME, LOC ) VALUES (10, 'ACCOUNTING', 'NEW YORK'); 
INSERT INTO DEPT ( DEPTNO, DNAME, LOC ) VALUES (20, 'RESEARCH', 'DALLAS'); 
INSERT INTO DEPT ( DEPTNO, DNAME, LOC ) VALUES (30, 'SALES', 'CHICAGO'); 
INSERT INTO DEPT ( DEPTNO, DNAME, LOC ) VALUES (40, 'OPERATIONS', 'BOSTON'); 
commit;
 
INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,DEPTNO ) VALUES (7369, 'SMITH', 'CLERK', 7902,  '1980-12-17', 800, NULL, 20); 
INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,DEPTNO ) VALUES (7499, 'ALLEN', 'SALESMAN', 7698,  '1981-02-20', 1600, 300, 30); 
INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,DEPTNO ) VALUES (7521, 'WARD', 'SALESMAN', 7698,  '1981-02-22', 1250, 500, 30); 
INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,DEPTNO ) VALUES (7566, 'JONES', 'MANAGER', 7839,  '1981-04-02', 2975, NULL, 20); 
INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,DEPTNO ) VALUES (7654, 'MARTIN', 'SALESMAN', 7698,  '1981-09-28', 1250, 1400, 30); 
INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,DEPTNO ) VALUES (7698, 'BLAKE', 'MANAGER', 7839,  '1981-05-01', 2850, NULL, 30); 
INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,DEPTNO ) VALUES (7782, 'CLARK', 'MANAGER', 7839,  '1981-06-09', 2450, NULL, 10); 
INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,DEPTNO ) VALUES (7788, 'SCOTT', 'ANALYST', 7566,  '1987-04-19', 3000, NULL, 20); 
INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,DEPTNO ) VALUES (7839, 'KING', 'PRESIDENT', NULL,  '1981-11-17', 5000, NULL, 10); 
INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,DEPTNO ) VALUES (7844, 'TURNER', 'SALESMAN', 7698,  '1981-09-08', 1500, 0, 30); 
INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,DEPTNO ) VALUES (7876, 'ADAMS', 'CLERK', 7788,  '1987-05-23', 1100, NULL, 20); 
INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,DEPTNO ) VALUES (7900, 'JAMES', 'CLERK', 7698,  '1981-12-03', 950, NULL, 30); 
INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,DEPTNO ) VALUES (7902, 'FORD', 'ANALYST', 7566,  '1981-12-03', 3000, NULL, 20); 
INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,DEPTNO ) VALUES (7934, 'MILLER', 'CLERK', 7782,  '1982-01-23', 1300, NULL, 10); 
commit;
 
INSERT INTO SALGRADE ( GRADE, LOSAL, HISAL ) VALUES (1, 700, 1200); 
INSERT INTO SALGRADE ( GRADE, LOSAL, HISAL ) VALUES (2, 1201, 1400); 
INSERT INTO SALGRADE ( GRADE, LOSAL, HISAL ) VALUES (3, 1401, 2000); 
INSERT INTO SALGRADE ( GRADE, LOSAL, HISAL ) VALUES (4, 2001, 3000); 
INSERT INTO SALGRADE ( GRADE, LOSAL, HISAL ) VALUES (5, 3001, 9999); 
commit;

1. 查询员工中薪资高于平均薪资的员工姓名以及工作岗位

//错误写法！！ 报错：> 1111 - Invalid use of group function
select 
ename,job 
from 
emp 
where sal > avg(sal);
//因为group by是在where之后执行的，而分组函数又是在group by完全执行后(having)执行，所以就会出错

//正确写法：可以使用select嵌套进行解决
select 
ename,job 
from 
emp
where sal > (select avg(sal) from emp);

2. 找出每个部门平均薪水的薪资等级

//先找出每个部门的平均薪资
select deptno,avg(sal) as avgsal from emp group by deptno
//利用这个返回的结果和salgrade进行连接，然后查询出想要的结果
select t.*,s.grade
from
(select deptno,avg(sal) as avgsal from emp group by deptno) t
join salgrade s
on
t.avgsal between s.losal and s.hisal;

3. 查每个员工所在的部门名称，要求显示员工名和部门名（select后嵌套子查询例子）

select 
e.ename,(select d.dname from dept d where e.deptno=d.deptno) as dname
from emp e;

---

---

---

---

---

---

下面继续举其他实际例子说明：

substr(strings|express,m,[n])

• strings|express ：被截取的字符串或字符串表达式
• m 从第m个字符开始截取
• n 截取后字符串长度为n

select Id as id, Name as name 
from table_example 
where cityId='123456'  AND  SUBSTR(Id,15,1)='1'

---

like关键字 :

//查询名字包含“嘎嘎嘎”的信息
select Id as id, Name as name 
from table_example 
where Name like '%嘎嘎嘎%'

between关键字 :

//查询2021年1月1日到2021年1月20日总费用(oracle写法）
select sum(fee)  as  sumfee  
from  table_example
where  TRANS_TIME 
between to_date('2021-01-01 00:00:00','yyyy-mm-dd hh24:mi:ss')  and  to_date('2021-01-20 23:59:59','yyyy-mm-dd hh24:mi:ss') 

Oracle to_date()函数的用法

group by子句:

//查询id信息2021年1月1日到2021年1月20日总费用
select ID ,sum(fee)  as  sumfee  
from  table_example
where  TRANS_TIME between to_date('2021-01-01 00:00:00','yyyy-mm-dd hh24:mi:ss')  and  to_date('2021-01-20 23:59:59','yyyy-mm-dd hh24:mi:ss') 
group by ID 

having子句：

//查询2021年1月1日到2021年1月20日总费用在300元以上的id
select ID ,sum(fee)  as  sumfee  
from  table_example
where  TRANS_TIME between to_date('2021-01-01 00:00:00','yyyy-mm-dd hh24:mi:ss')  and  to_date('2021-01-20 23:59:59','yyyy-mm-dd hh24:mi:ss') 
group by ID 
having sum(fee) > 100

order by :

//查询各信息2021年1月1日到2021年1月20日总费用并按费用由高到低排序
select ID ,sum(fee)  as  sumfee  
from  toolData
where  TRANS_TIME between to_date('2021-01-01 00:00:00','yyyy-mm-dd hh24:mi:ss')  and  to_date('2021-01-20 23:59:59','yyyy-mm-dd hh24:mi:ss') 
group by ID 
order by sumfee  desc

---

---

重点
left join 语句 ：left join 详细

LEFT JOIN 关键字会从左表 (table_name1) 那里返回所有的行，即使在右表 (table_name2) 中没有匹配的行。

//查询各市2021年1月1日到2021年1月20日总费用
select CityId, sum(sumfee) as cityfee
from(
	select  B.GantryId, B.CityId, A.sumfee
	from(
		select TOLL_GANTRY_ID ,sum(FEE)  as  sumfee  
		from  toolData where  TRANS_TIME between to_date('2021-01-01 00:00:00','yyyy-mm-dd hh24:mi:ss')  and  to_date('2021-01-20 23:59:59','yyyy-mm-dd hh24:mi:ss') 
		group by TOLL_GANTRY_ID
		) A
 		left join(select GantryId,CityName from  doorFrameData) B
		on A.TOLL_GANTRY_ID = B.GantryId
	) S 
group by CityId
order by cityfee desc

inner join 语句：inner join 详细
2021年1月1日到2021年1月20日总费用在300元以上的门架及基本信息：

select A.* from
	(
	select GantryId as id, Name as name 
	from doorFrameData 
	) A
inner join
	(
	select TOLL_GANTRY_ID ,sum(FEE)  as  sumfee  
	from  toolData
	where  TRANS_TIME between to_date('2021-01-01 00:00:00','yyyy-mm-dd hh24:mi:ss')  and  to_date('2021-01-20 23:59:59','yyyy-mm-dd hh24:mi:ss') 
	group by TOLL_GANTRY_ID 
	having sum(FEE) > 300
	) B
on  
A.id = B.TOLL_GANTRY_ID 

注：INNER JOIN 与 JOIN 是相同的。

SQL union 操作符 :
union操作符用于合并两个或多个 select语句的结果集。union 内部的 select语句必须拥有相同数量的列。列也必须拥有相似的数据类型。同时，每条 select语句中的列的顺序必须相同。

union 操作符选取不同的值。如果允许重复的值，请使用 union all。

distinct :
关键词 distinct 用于返回唯一不同的值。

SELECT distinct 列名称 FROM 表名称