2.1 SELECT练习 01

数据表图片示例:

这里测试以mysql为主 如有ORACLE示例会额外标记

EMP表

DEPT表

查询DEPT表中的所有信息

SELECT * FROM DEPT;

查询雇员表中所有员工的员工编号,员工姓名,上级编号

-- 要查询的数据: EMPNO,ENAME,MGR
-- 数据的来源:EMP
SELECT EMPNO,ENAME,MGR FROM EMP;

查询所有员工的部门标号

SELECT DEPTNO FROM DEPT;

去除EMP表重复员工

--完全相同的数据才能去重
SELECT DISTINCT EMPNO , ENAME FROM EMP;

简易计算器

SELECT DISTINCT 1+1 FROM DEPT;
SELECT EMPNO,ENAME,123*456 FROM EMP;

别名练习

-- 别名  字段 (as) 别名  表名 别名
SELECT EMPNO AS 员工编号,ENAME "员工 姓名",1+1 "getSum" FROM EMP e;
-- 原样输出 双引号(中文常用) " "  小写别名,中文中带有空格...非标准写法
-- ' '字符串

字符串拼接

--ORACLE字符串拼接   ||
SELECT 'SQL'||ENAME FROM EMP;
--mysql字符串拼接 CONCAT
SELECT CONCAT('MYSQL+',ENAME) FROM EMP;

-- 伪列:表中不存在的列  :字符串,表达式...
-- 虚表:没有任何数据  dual
SELECT DISTINCT 456*789,SYSDATE() FROM EMP;
SELECT SYSDATE() FROM DUAL;
-- MYSQL --- SYSDATE()
-- ORACLE --- SYSDATE

查询员工姓名,员工的薪资,员工的奖金

SELECT ENAME,SAL,COMM FROM EMP;

null 与 数字 求和

-- ORACLE
-- null和数字运算,结果还是null
-- null和字符串运算,结果是原串,null相当于空串
给公司的每个员工+100鼓励金
SELECT ENAME,SAL,COMM "原奖金",COMM||'100' "新奖金" FROM EMP;
--处理null值  nvl(参数1,参数2)  如果参数1为null,函数的返回值结果为参数如果参数1不为null,函数的返回值结果为参数1
SELECT ENAME,SAL,COMM "原奖金",NVL(COMM,0)+100 "新奖金" FROM EMP;

-- Mycle
-- 未整理

强化练习

查询所有员工的名字, 工种, 年薪(不带奖金)

SELECT ENAME "姓名",JOB "工作",12*SAL "年薪" FROM EMP;

查询所有员工的名字,工种,年薪(带12月奖金的)

SELECT ENAME "姓名",JOB "工作",IFNULL(12*SAL+12*COMM,12*SAL) "年薪+奖金" FROM EMP;
--这里使用了 IFNULL(参数,默认值) 函数 一旦参数为null则使用默认值

查询所有员工的名字, 工种, 年薪(带一次奖金的)

SELECT ENAME "姓名",JOB "工作",IFNULL(12*SAL+COMM,12*SAL) "年薪+奖金" FROM EMP;

查询30部门的员工

SELECT * FROM EMP WHERE DEPTNO=30;

查询员工名字为SMITH的员工信息

SELECT * FROM EMP WHERE ENAME='SMITH';

查询除了SMITH之外的所有员工的员工姓名和员工编号

--比较条件  = 、>、 <、 >=、 <=、 !=、 <>
SELECT ENAME,DEPTNO FROM EMP WHERE ENAME!='SMITH';
SELECT ENAME,DEPTNO FROM EMP WHERE ENAME<>'SMITH';
SELECT ENAME,DEPTNO FROM EMP WHERE NOT ENAME='SMITH';

查询月薪资大于800的员工信息

SELECT * FROM EMP WHERE SAL>800;

查询工种为CLERK的,并且是30部门的员工信息

--and且、 or或、 not非
SELECT * FROM EMP WHERE JOB='CLERK' AND DEPTNO=30;

查询工种为CLERK的,或者是30部门的员工信息

SELECT * FROM EMP WHERE JOB='CLERK' OR DEPTNO=30;

查询不是工种为CLERK的,也不是30部门的员工信息

SELECT * FROM EMP WHERE NOT JOB='CLERK' AND NOT DEPTNO=30;
SELECT * FROM EMP WHERE NOT (JOB!='CLERK' OR DEPTNO!=30);
SELECT * FROM EMP WHERE JOB!='CLERK' AND DEPTNO!=30;

查询没有奖金的人

SELECT * FROM EMP WHERE COMM IS NOT NULL;
SELECT * FROM EMP WHERE NOT COMM IS NULL;
--查询有奖金的人
SELECT * FROM EMP WHERE COMM IS NULL;

查询工资在1500~2500之间的姓名 员工工种和工资

--查询的内容:ename,sal,job
--来源:emp
--条件:sal>1500 and sal<2500
SELECT ENAME,JOB,SAL FROM EMP WHERE SAL>=1500 AND SAL<=2500;
SELECT ENAME,JOB,SAL FROM EMP WHERE SAL BETWEEN 1500 AND 2500;
 --范围区间 从小到大 (BETWEEN AND 闭区间)

查询销售部的部门编号
查询 销售部（SALES） 中 工资大于1500的员工信息

--子查询
SELECT DEPTNO FROM DEPT WHERE DNAME='SALES';
SELECT * FROM EMP WHERE SAL>1500 AND DEPTNO=30;
SELECT * FROM EMP WHERE SAL>1500 AND DEPTNO=(SELECT DEPTNO FROM DEPT WHERE DNAME='SALES');

查询工资比SMITH高的同一部门的员工信息

--SELECT SAL FROM EMP WHERE ENAME='SMITH'; --SMITH的薪资
--SELECT DEPTNO FROM EMP WHERE ENAME='SMITH'; --SMITH的部门编号
SELECT * FROM EMP WHERE 
SAL>(SELECT SAL FROM EMP WHERE ENAME='SMITH')
 AND DEPTNO=(SELECT DEPTNO FROM EMP WHERE ENAME='SMITH');   
--数据来源 也可以是一个结果集 ,可以是表.. 

查询工资大于1500 或 含有佣金的人员姓名

SELECT ENAME FROM EMP WHERE SAL>1500 OR COMM IS NOT NULL;
--对结果集求和|并集  相同的数据保留一个   Union
SELECT ENAME FROM EMP WHERE SAL>1500 
UNION 
SELECT ENAME FROM EMP WHERE COMM IS NOT NULL;
--Union All，全集(两表数据不去重)
SELECT ENAME FROM EMP WHERE SAL>1500 
UNION ALL
SELECT ENAME FROM EMP WHERE COMM IS NOT NULL;

查询显示不存在雇员的所有部门号

--查询所有部门编号
--select deptno from dept;
--查询有员工存在的部门编号
--select distinct deptno from emp;
--差集  Minus，差集(去重)
--ORACLE
SELECT DEPTNO FROM DEPT
MINUS
SELECT DISTINCT DEPTNO FROM EMP;
--MYSQL 
SELECT DEPTNO FROM DEPT
WHERE DEPTNO NOT IN
(SELECT DISTINCT DEPTNO FROM EMP);
--上述方式 对于大型数据查询时效率低下 不推荐

查询工资大于1500 且 含有奖金的人员姓名

SELECT ENAME FROM EMP WHERE SAL>1500 AND COMM IS NOT NULL;
--ORACLE --Intersect，交集(找出重复)
SELECT ENAME FROM EMP WHERE SAL>1500
INTERSECT
SELECT ENAME FROM EMP WHERE COMM IS NOT NULL;
--MYSQL
--写不出来(꒦_꒦) 

模糊匹配

--模糊查询|匹配 like %任意个任意字符  _一个任意字符
---查询姓名以'A'开头的员工信息
SELECT * FROM EMP WHERE ENAME LIKE 'A%';
--查询姓名里边有‘A’的员工信息
SELECT * FROM EMP WHERE ENAME LIKE '%A%';
--查询名称中第二个字母为‘A’的员工信息
SELECT * FROM EMP WHERE ENAME LIKE '_A%';
--查询名称以'H'结尾的员工信息
SELECT * FROM EMP WHERE ENAME LIKE '%H';

查询姓名中含有%的员工信息(转义字符+模糊匹配)

SELECT * FROM EMP WHERE ENAME LIKE  '%A%%' ESCAPE('A'); 
--A为转义字符,转义A后面的字符
--可以修改表中数据再进行测试

查询工资为 1500， 2000， 2500， 5000的员工的信息

SELECT * FROM EMP WHERE SAL=1500 OR SAL=2000 OR SAL=2500 OR SAL=5000;
--多个定值之间满足任意一个就可以   
SELECT * FROM EMP WHERE SAL IN(1500,2000,2500,5000);

部门名称为 SALES 或 ACCOUNTING 的雇员信息

--部门对应编号
--SELECT DEPTNO FROM DEPT WHERE DNAME IN('SALES','ACCOUNTING'); 
--查询编号对应部门的员工信息
SELECT * FROM EMP WHERE DEPTNO IN
(SELECT DEPTNO FROM DEPT WHERE DNAME IN('SALES','ACCOUNTING') );

如果DEPT表中存在工种为SALES 则查询所有员工信息

--EXISTS(类似boolean 只做是否执行sql的判定条件)
SELECT * FROM EMP WHERE EXISTS (SELECT DEPTNO FROM DEPT WHERE DNAME = 'SALES');

查询’SALES’, 'ACCOUNTING’部门的员工信息

SELECT * FROM EMP WHERE DEPTNO
IN(SELECT DEPTNO FROM DEPT WHERE DNAME IN('SALES','ACCOUNTING'));
--查询除了'SALES', 'ACCOUNTING'部门的员工信息
SELECT * FROM EMP WHERE DEPTNO
IN(SELECT DEPTNO FROM DEPT WHERE DNAME NOT IN('SALES','ACCOUNTING'));

所有有奖金的员工信息empno,ename,sal,comm

SELECT EMPNO,ENAME,SAL,COMM FROM EMP WHERE COMM IS NOT NULL;

有奖金的员工所在的部门中的所有员工的信息

SELECT * FROM EMP WHERE DEPTNO
IN(SELECT DEPTNO FROM EMP WHERE COMM IS NOT NULL);

按雇员表编号降序排列EMP表

--排序 order by 字段 desc(降序)|asc(升序),字段 排序方式  默认升序  nulls last(所有的null值在最后显示)|fist
SELECT * FROM EMP ORDER BY EMPNO DESC;

查询30部门的所有员工,根据薪资升序排序 (默认升序)

SELECT * FROM EMP WHERE DEPTNO=30 ORDER BY SAL ASC;
SELECT * FROM EMP WHERE DEPTNO=30 ORDER BY SAL; 

查询10,30部门的所有员工,根据薪资降序排序,如果薪资相同根据员工编号降序排序

SELECT * FROM EMP WHERE DEPTNO=10 OR DEPTNO=30 ORDER BY SAL DESC,EMPNO DESC;

--select *|字段1.. from 表|结果集 where 行记录条件 order by 排序字段 排序方式,...;
--执行顺序: from--where--select--order by