文章目录
- 约束
- SQL 查询语法学习
- 1、简单查询练习
- 2、算术操作符，加减乘除
- 3、给字段加别名
- 4、连接字符串（||）
- 5、比较操作符
- 薪资大于3000的员工信息
- 查找1982.1.1 后入职的员工
- like 操作，模糊查询
- in 操作，与字段中的值进行匹配
- between 操作
- or 或、and 并且、not 非
- 6、排序
- 7、聚合函数
- 8、简单子查询
- 9、分组查询 group by 和 having 子句
- 10、多表查询
- 11、连接查询
- 自连接
- 内连接
- 12、子查询
- 13、分页查询
- 下期预告
约束
数据的完整性用于确保数据库数据遵从一定的规则，在oracle中，数据完整性可以通过约束、触发器、应用程序（过程、函数）三种方法来实现约束。
约束 | 含义 |
not null | (非空约束)如果在列上定义了not null,那么插入数据时，必须为此列提供数据； |
unique | (唯一约束)当列上定义了唯一约束，此列值不能重复的，但是可以为null； |
primary key | （主键约束）用于唯一标识表行的数据，当定义主键约束后，此列不但不能重复还不能为空； |
foreign key | (外键约束) 用于定义表和表之间的关系； |
check | (检查约束)用于强制行数据必须满足的条件； |
SQL 查询语法学习
前提：使用 Scott 用户登录
表结构分析：
EMP 表
列 | 含义 |
EMPNO | 员工编号（主键） |
ENAME | 员工名 |
JOB | 职位、工作 |
MGR | 上级领导编号 |
HIREDATE | 入职时间 |
SAL | 工资 |
COMM | 奖金 |
DEPTNO | 部门编号（外键） |
DEPT 表
列 | 含义 |
DEPTNO | 部门编号 |
DNAME | 部门名 |
LOC | 部门所在位置 |
SALGRADE 表
列 | 含义 |
GRADE | 等级 |
LOSAL | 低等级薪资 |
HISAL | 高等级薪资 |
1、简单查询练习
-- 查询所有列
select * from emp;
-- 查询指定的字段
select ename,sal,job from emp;
-- 查询SMITH薪水，工作，所在部门的编号
select sal,job,deptno,ename from emp where ename='SMITH';
-- 显示每个员工的工资
select ename,sal from emp;2、算术操作符，加减乘除
-- 算术操作符 + － * /
-- 显示每个员工的年工资
select ename,sal*12 from emp;
-- 显示每个员工的年收入（错误，奖金comm为null时，结果为null）
SQL> select ename,sal*12+comm from emp;
ENAME SAL*12+COMM
---------- -----------
SMITH
ALLEN 19500
WARD% 15500
JONES
MARTIN 16400
BLAKE
SCOTT
KING
TURNER 18000
ADAMS
JAMES
FORD
MILLER
13 rows selected.
-- 判空函数，nvl(num1,num2);
-- 含义是，当num1为null时，使用num2作为结果
select nvl(comm,0) from emp;
-- 显示每个员工的年输入（奖金为null时使用0作为奖金）
SQL> select ename,sal*12+nvl(comm,0) from emp;
ENAME SAL*12+NVL(COMM,0)
---------- ------------------
SMITH 9600
ALLEN 19500
WARD% 15500
JONES 35700
MARTIN 16400
BLAKE 34200
SCOTT 36000
KING 60000
TURNER 18000
ADAMS 13200
JAMES 11400
FORD 36000
MILLER 15600
13 rows selected.3、给字段加别名
-- 给字段加别名
SQL> select ename as "姓名" ,sal*12+nvl(comm,0) as "年收入" from emp;
姓名 年收入
---------- ----------
SMITH 9600
ALLEN 19500
WARD% 15500
JONES 35700
MARTIN 16400
BLAKE 34200
SCOTT 36000
KING 60000
TURNER 18000
ADAMS 13200
JAMES 11400
FORD 36000
MILLER 15600
13 rows selected.4、连接字符串（||）
SQL> select ename || ' is a ' || job from emp;
ENAME||'ISA'||JOB
-------------------------
SMITH is a CLERK
ALLEN is a SALESMAN
WARD% is a SALESMAN
JONES is a MANAGER
MARTIN is a SALESMAN
BLAKE is a MANAGER
SCOTT is a ANALYST
KING is a PRESIDENT
TURNER is a SALESMAN
ADAMS is a CLERK
JAMES is a CLERK
FORD is a ANALYST
MILLER is a CLERK
13 rows selected.5、比较操作符
操作符 | 含义 |
| 等值比较 |
| 不等 |
| 大于 |
| 小于 |
| 小于等于 |
| 大于等于 |
| 在…之间 |
| 在范围内等值比较 |
| 像，模糊查询 |
| 为空 |
| 不为空 |
薪资大于3000的员工信息
SQL> select ename,sal from emp where sal > 3000;
ENAME SAL
---------- ----------
KING 5000查找1982.1.1 后入职的员工
-- 先格式化日期
SQL> alter session set nls_date_format='yyyy-mm-dd';
SQL> select ename,hiredate from emp where HIREDATE > '1982-1-1';
ENAME HIREDATE
---------- ----------
SCOTT 1987-04-19
ADAMS 1987-05-23
MILLER 1982-01-23like 操作，模糊查询
注意：%:表示任意0到多个字符； _：表示一个字符
-- 名字首字母为S的员工
select ename,sal,deptno from emp where ename like 'S%';
-- 以O字母结尾的名字的员工
select * from emp where ename like '__O%';
-- 名字中包含 O 字母的员工
select * from emp where ename like '%O%';in 操作，与字段中的值进行匹配
--员工编号为7369，7839，7788的员工的信息
select * from emp where empno in(7369，7839,7788);
-- 部门编号是10、30的员工的信息
select * from emp where deptno in(10,30);between 操作
-- 薪资在 2000到2500之间的员工的信息
select * from emp where sal between 2000 and 2500;
-- 不在这个范围的员工的信息
select * from emp where sal not between 2000 and 2500;or 或、and 并且、not 非
-- 查询工资高于500或岗位为MANAGER的员工，同时满足姓名首写字母为J
SQL> select * from emp where (sal > 500 or job='MANAGER') and ename like 'J%';
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- ---------- ---------- ---------- ----------
7566 JONES MANAGER 7839 1981-04-02 2975 20
7900 JAMES CLERK 7698 1981-12-03 950 306、排序
Order by：升序 asc、降序 desc
-- 按照薪资升序
SQL> select ename,sal from emp order by sal asc;
ENAME SAL
---------- ----------
SMITH 800
JAMES 950
ADAMS 1100
WARD% 1250
MARTIN 1250
MILLER 1300
TURNER 1500
ALLEN 1600
BLAKE 2850
JONES 2975
SCOTT 3000
FORD 3000
KING 5000
13 rows selected.
-- 按照薪资降序
SQL> select ename,sal from emp order by sal desc;
ENAME SAL
---------- ----------
KING 5000
SCOTT 3000
FORD 3000
JONES 2975
BLAKE 2850
ALLEN 1600
TURNER 1500
MILLER 1300
WARD% 1250
MARTIN 1250
ADAMS 1100
JAMES 950
SMITH 800
13 rows selected.
-- 部门号升序而工资降序
SQL> select ename,sal,deptno from emp order by deptno asc,sal desc;
ENAME SAL DEPTNO
---------- ---------- ----------
KING 5000 10
MILLER 1300 10
SCOTT 3000 20
FORD 3000 20
JONES 2975 20
ADAMS 1100 20
SMITH 800 20
BLAKE 2850 30
ALLEN 1600 30
TURNER 1500 30
MARTIN 1250 30
WARD% 1250 30
JAMES 950 30
13 rows selected.7、聚合函数
函数 | 含义 |
| 最大值 |
| 最小值 |
| 平均值 |
| 求和 |
| 求数量 |
-- 查询薪资最大值和薪资的最小值
SQL> select max(sal),min(sal) from emp;
MAX(SAL) MIN(SAL)
---------- ----------
5000 800
-- 对薪资查询平均值、求和
SQL> select avg(sal),sum(sal) from emp;
AVG(SAL) SUM(SAL)
---------- ----------
2044.23077 26575
-- 统计共有多少个员工
SQL> select count(*) from emp;
COUNT(*)
----------
138、简单子查询
sql语句中还有select语句
单行子查询 返回一行数据的子查询语句
-- 工资高于平均工资的员工信息
SQL> select ename,sal from emp where sal > (select avg(sal) from emp);
ENAME SAL
---------- ----------
JONES 2975
BLAKE 2850
SCOTT 3000
KING 5000
FORD 3000-- 错误：注意函数不能和字段一起使用
select ename,avg(sal),sum(sal) from emp;9、分组查询 group by 和 having 子句
-- 每个部门的平均工资和最高工资
SQL> select deptno,avg(sal),max(sal) from emp group by deptno;
DEPTNO AVG(SAL) MAX(SAL)
---------- ---------- ----------
30 1566.66667 2850
20 2175 3000
10 3150 5000-- 显示每个部门的每种岗位的平均工资和最低工资
SQL> select deptno,avg(sal),max(sal),job from emp group by deptno,job;
DEPTNO AVG(SAL) MAX(SAL) JOB
---------- ---------- ---------- ---------
20 950 1100 CLERK
30 1400 1600 SALESMAN
20 2975 2975 MANAGER
30 950 950 CLERK
10 5000 5000 PRESIDENT
30 2850 2850 MANAGER
10 1300 1300 CLERK
20 3000 3000 ANALYST
8 rows selected.-- 平均工资低于2000 的部门号和它的平均工资
SQL> select deptno,avg(sal) from emp group by deptno having avg(sal) < 2000;
DEPTNO AVG(SAL)
---------- ----------
30 1566.6666710、多表查询
基于两张表或两张表以上的查询
多表查询的条件至少不能小于表的总数－1，加了一个条件就可以避免笛卡尔积的出现。
所以说，2张表查询至少要有一个条件，3张表查询至少有两个条件。
-- 查询员工名、薪资、员工所在的部门名
SQL> select ename,sal,dname from emp,dept where emp.deptno = dept.deptno;
ENAME SAL DNAME
---------- ---------- --------------
KING 5000 ACCOUNTING
MILLER 1300 ACCOUNTING
JONES 2975 RESEARCH
SMITH 800 RESEARCH
SCOTT 3000 RESEARCH
FORD 3000 RESEARCH
ADAMS 1100 RESEARCH
ALLEN 1600 SALES
TURNER 1500 SALES
JAMES 950 SALES
WARD% 1250 SALES
BLAKE 2850 SALES
MARTIN 1250 SALES
13 rows selected.-- 显示部门号为20的部门名称，员工名和工资
SQL> select e.ename,e.sal,d.dname,e.deptno from emp e,dept d where e.deptno = d.deptno and d.deptno = 20;
ENAME SAL DNAME DEPTNO
---------- ---------- -------------- ----------
SMITH 800 RESEARCH 20
JONES 2975 RESEARCH 20
SCOTT 3000 RESEARCH 20
ADAMS 1100 RESEARCH 20
FORD 3000 RESEARCH 20-- 显示各个员工的姓名，工资，及其工资的级别
SQL> select e.ename,e.sal,s.grade from emp e,salgrade s where e.sal between s.losal and s.hisal;
ENAME SAL GRADE
---------- ---------- ----------
SMITH 800 1
JAMES 950 1
ADAMS 1100 1
WARD% 1250 2
MARTIN 1250 2
MILLER 1300 2
TURNER 1500 3
ALLEN 1600 3
BLAKE 2850 4
JONES 2975 4
SCOTT 3000 4
FORD 3000 4
KING 5000 5
13 rows selected.-- 显示员工的姓名，工资用户所在的部门名称，并按部门排序
SQL> select e.ename,e.sal,d.dname,d.deptno from emp e,dept d where e.deptno = d.deptno order by d.deptno;
ENAME SAL DNAME DEPTNO
---------- ---------- -------------- ----------
KING 5000 ACCOUNTING 10
MILLER 1300 ACCOUNTING 10
JONES 2975 RESEARCH 20
SMITH 800 RESEARCH 20
SCOTT 3000 RESEARCH 20
FORD 3000 RESEARCH 20
ADAMS 1100 RESEARCH 20
ALLEN 1600 SALES 30
TURNER 1500 SALES 30
JAMES 950 SALES 30
WARD% 1250 SALES 30
BLAKE 2850 SALES 30
MARTIN 1250 SALES 30
13 rows selected.11、连接查询
自连接、内连接、外连接、交叉连接等
自连接
-- 查询每个员工的上级领导的名字
SQL> select e.ename,b.ename from emp e,emp b where e.mgr = b.empno;
ENAME ENAME
---------- ----------
SCOTT JONES
FORD JONES
WARD% BLAKE
ALLEN BLAKE
JAMES BLAKE
MARTIN BLAKE
TURNER BLAKE
ADAMS SCOTT
JONES KING
BLAKE KING
SMITH FORD
11 rows selected.-- 员工名为FORD的上级领导的姓名
SQL> select worker.ename,boss.ename from emp worker,emp boss where worker.mgr = boss.empno and worker.ename = 'FORD';
ENAME ENAME
---------- ----------
FORD JONES内连接
内连接分为：等值连接、不等值连接、自然连接
-- 等值连接 inner join on
-- 下面的sql 与本条语句效果一样（不考虑太深入）：select * from emp e,dept d where e.deptno = d.deptno;
select * from emp e inner join dept d on e.deptno = d.deptno;-- 不等值连接
-- select e.ename,e.sal,d.dname,d.deptno from emp e,dept d where e.deptno = d.deptno order by d.deptno;
select * from emp e inner join salgrade s on e.sal between s.losal and s.hisal;-- 自然连接：在两张表中自动找那些数据类型和字段名都相同的字段，然后将他们自动连接
SQL> select ename,dname from emp natural join dept;
ENAME DNAME
---------- --------------
KING ACCOUNTING
MILLER ACCOUNTING
JONES RESEARCH
SMITH RESEARCH
SCOTT RESEARCH
FORD RESEARCH
ADAMS RESEARCH
ALLEN SALES
TURNER SALES
JAMES SALES
WARD% SALES
BLAKE SALES
MARTIN SALES
13 rows selected.-- 外连接 分为左外连接（left join）和右外连接(right join)
-- 还可以使用（+）符号表示：(+)在左边表示右外边连接，（+）在右边表示左外连接
-- 右外连接
select e.ename,d.dname from emp e right join dept d on e.deptno = d.deptno;
select e.ename,d.dname from emp e,dept d where e.deptno(+) = d.deptno;等价于
-- 以上两种写法，结果一致
SQL> select e.ename,d.dname from emp e right join dept d on e.deptno = d.deptno;
ENAME DNAME
---------- --------------
KING ACCOUNTING
MILLER ACCOUNTING
JONES RESEARCH
SMITH RESEARCH
SCOTT RESEARCH
FORD RESEARCH
ADAMS RESEARCH
ALLEN SALES
TURNER SALES
JAMES SALES
WARD% SALES
BLAKE SALES
MARTIN SALES
OPERATIONS
14 rows selected.
-- 左外连接
select e.ename,d.dname from emp e left join dept d on e.deptno = d.deptno;
select e.ename,d.dname from emp e ,dept d where e.deptno = d.deptno(+);
-- 以上两种写法，结果一致
SQL> select e.ename,d.dname from emp e left join dept d on e.deptno = d.deptno;
ENAME DNAME
---------- --------------
MILLER ACCOUNTING
KING ACCOUNTING
FORD RESEARCH
ADAMS RESEARCH
SCOTT RESEARCH
JONES RESEARCH
SMITH RESEARCH
JAMES SALES
TURNER SALES
BLAKE SALES
MARTIN SALES
WARD% SALES
ALLEN SALES
13 rows selected.
-- 交叉连接（笛卡尔积）
select count(*) from emp cross join dept;12、子查询
-- 单行子查询
-- 显示与SMITH同一个部门的所有员工
select * from emp where deptno=(select deptno from emp where ename='SMITH');-- 多行子查询
-- 询和部门号为10的工作相同的员工信息；使用 distinct 去重复
select * from emp where job in(select distinct job from emp where deptno=10);-- all 操作符和 any 操作符
-- 显示工资比部门30的所有员工的工资高的员工信息
-- 以下两种写法都可以：
select * from emp where sal > (select max(sal) from emp where deptno=30);
select * from emp where sal > all(select sal from emp where deptno=30);
-- 显示工资比部门30的任意一个员工的工资高的员工信息
-- 以下两种写法都可以：
select * from emp where sal > any(select sal from emp where deptno=30);
select * from emp where sal > (select min(sal) from emp where deptno=30);-- 多列子查询
-- 查询与SMITH的部门和岗位完全相同的员工信息
select * from emp where (deptno,job)=(select deptno,job from emp where ename='SMITH');
-- 查询出各个部门的平均工资和部门编号
select * from emp e1,(select deptno,avg(sal) mysal from emp group by deptno) e2 where e1.deptno = e2.deptno and e1.sal > e2.mysal;13、分页查询
rowid与rownum 称为伪列，但它们的存在的方式不一样，rowid可以说物理存在的，表示记录在表空间中的唯一位置ID，rownum产生一个序列，总是从1开始。rownum是oralce给他分配的行号。
--查出小于10的部分：前10条数据
select e.*,rownum rn from (select * from emp)e where rownum <= 10;
-- (select * from emp) 查什么取决于最里面的表，如排序，
select * from (select e.*,rownum rn from (select * from emp order by sal)e where rownum <= 10) where rn >= 6;
本文章为转载内容，我们尊重原作者对文章享有的著作权。如有内容错误或侵权问题，欢迎原作者联系我们进行内容更正或删除文章。
