数据库基础及举例（2） 整理和应用 链接: https://pan.baidu.com/s/1bCBxTG 密码: 2ftv

sql	结构化查询语言
DDL	数据定义语言
DML	数据操纵语言
TCL	事物控制语言
DQL	数据查询语言
DCL	数据控制语言
DDL
Create  CREATE	创建表或其他数据的结构
Alter    ALTER	修改表或其他类型的结构
Drop     DROP  掉	删除表或其他类型的结构
Truncate   TRUNCATE	删除表数据，保留表结构
DML
Insert  INSERT	将数据插入数据表中
Update UPDATE 更新	更新数据表中已存在的数据
Delete  DELETE	删除数据表中的数据
TCL
Commit  COMMIT	提交，确认已经进行的数据改变
Rollback  ROLLBACK	回滚，取消已经进行的数据改变
SavePoint	保存点，使当前的事物可以回退到指定的保存点，便于取消部分改变
DQL
Select	用来查询所需要的数据
DCL
	用于执行权限的授予和收回操作
Grant  授予	授予，用于给用户或角色授予权限
Revoke     撤回	用于收回用户或角色已有的权限
Create user	创建用户
Number(P,S)	数字类型
P数字总位数	S小数点后面的位数
Char	字符串 字节量 最长2000
Varchar	用多少 占多少
Varchar2
TO_DATE(‘2009-09-01’,’YYYY-MM-DD’	日期
Create table	创建表
DESC    desc	查看表的结构
Default 系统默认值	DEFAULT语句 default
Not null	非空  业务需求
修改  enp   为  en Rename  enp  to  en	修改表     名	修改表
Add	增加列
Drop	删除列
Modify   修改	修改列
DML  数据操纵语言
INSERT INTO table_name(属性，属性)	insert
VAlLUSE(值， 值，值)
UPDATE myemp SET salary=8500	UPDATE语句
WHERE name=’rose’;
DELEFE FROM table_name job is null 删除职位是空的员工记录	DELETE
DELETE FROM myemp Truncate table myemp   效率高	删除全部记录

                               

 

 

                                   

DDL语句

查询当前时间
SELECT SysDate FROM dual

SQL语句本身不区分大小写，
但是出于可读性的目的，我们通常会将SQL中的关键字全部大写，非关键字全部小写

DDL 数据定义语言
DDL 是对于数据库对象的语言，
数据库对象包括：表，索引，视图，序列

创建表：
CREATE TABLE employee_wsh(
id NUMBER(4),
name VARCHAR2(20),
gender CHAR(1),
birth DATE,
salary NUMBER(6,2),
job VARCHAR2(30),
deptno NUMBER(2)
);

查询表结构
DESC employee_wsh

删除表
DROP TABLE employee_wsh

数据库在所有数据的默认值都是NULL
在创建表的时候可以使用DEFAULT为某个字段单独指定一个默认值。
数据库字段字符串子面量是使用单引号的
虽然SQL语句本身不区分大小写，但是字符串的值是区分大小写的！
CREATE TABLE employee_wsh(
id NUMBER(4),
name VARCHAR2(20) NOT NULL,
gender CHAR(1) DEFAULT 'M',
birth DATE,
salary NUMBER(6,2),
job VARCHAR2(30),
deptno NUMBER(2)
)


修改表
1：修改表名
2：修改表结构

修改表名：
RENAME employee_wsh TO myemp_wsh
DESC emplyee_wsh
DESC myemp_wsh

修改表结构：
1：添加新的字段
2：修改现有字段
3：删除现有字段

添加新字段(字段只能追加中最后，不能在中间插入)
ALTER TABLE myemp_wsh
ADD(
hiredate DATE DEFAULT SYSDATE
)

修改字段
可以修改字段名，长度，默认值，是否非空
修改表结构都应当避免在表在有数据以后进行。
若表中有数据，修改表中字段尽量不要修改类型，
若修改长度尽量增大避免缩小，否则可能导致修改失败。
ALTER TABLE myemp_wsh
MODIFY(
job VARCHAR(40) DEFAULT 'CLERK'
)

DESC myemp_wsh

删除字段
--ALTER TABLE myemp_wsh
--DROP(hiredate)

DML语句
DML是对表中的数据进行操作
DML伴随事物控制（TCL)
DML包括：
插入
修改
删除
查询

INSERT语句
向表中插入数据
INSERT INTO myemp_wsh
(id,name,salary,deptno)
VALUES
(1,'jack',5000,10)

SELECT * FROM myemp_wsh

提交
COMMIT

INSERT INTO myemp_wsh
(id,name,job,birth)
VALUES
(1003,'donna','MANAGER',TO_DATE('2009-09-01','YYYY-MM-DD'))

UPDATE语句
修改表数据
修改表在数据要使用WHERE添加过滤条件，
这样才会只有满足条件的记录进行修改，
否则是全表所有数据都修改

UPDATE myemp_wsh
SET salary=6000
WHERE id=1

UPDATE myemp_wsh
SET salary=7000,gender='F',name='rose'
WHERE id=1

删除表在数据，删除数据通常也要添加WHERE语句来限定要删除数据的添加
否则就是清空表操作！

DELETE FROM myemp_wsh
WHERE name='rose'

 

SELECT语句
用于查询表中数据
SELECT子句后跟着的是要查询的字段，
可以包括表在的具体字段，函数或者表达式。
FROM子句用来指定数据来源的表WHERE子句用来添加过滤条件，
这样做的结果是只将满足条件的记录查询出来

查询表中所有数据
SELECT * FROM emp_wsh

查询表中 empno,ename,job,sal 的数据
SELECT empno,ename,job,sal
FROM emp_wsh

SELECT子句中使用表达式
查看emp_wsh表中每个员工的年薪——月薪*12
SELECT ename,sal*12
FROM emp_wsh

字符串函数
CONCAT（）函数，用来连接‘两个’字符串，如果要使用CONCAT()连接多个字符串要嵌套
SELECT CONCAT(ename,sal)
FROM emp_wsh

SELECT CONCAT(CONCAT(ename,','),sal)
FROM emp_wsh

"||"操作符可以连接多个字符串
SELECT ename||','||sal
FROM emp_wsh

LENGTH函数可以查询字符串长度
SELECT ename,LENGTH(ename)
FROM emp_wsh

UPPER,LOWER,INITCAP
将字符串转化为全大写，全小写以及首字母大写
对于INITCAT而言，可以使用空格隔开多个单词，
那么每个单词首字母都会大写。

伪表：dual
查询的内容不和任何表中的数据有关时，可以使用伪表，伪表只会查询出一条记录
SELECT UPPER('helloworld'),
LOWER('HELLOWORLD'),
INITCAP('HELLO world')
FROM dual

TRIM、LTRIM、RTRIM
去除当前字符串中两边的指定重复字符，LTRIM仅去除左侧的，RTRIM仅去除右侧的

TRIM指定的字符只能是一个字符而不能是字符串
SELECT TRIM('e' FROM 'eeeliteeee')
FROM dual

LTRIM、RTRIM指定的字符可以有多个，只有有相同的字符就去除，排列顺序无关，直到读取到不等于
指定字符的字符时停止
SELECT LTRIM('eeeliteeee','e'),LTRIM('esrresersliteeee','res')
FROM dual

SELECT RTRIM('eeeliteeee','e'),RTRIM('eeelitrsesese','res')
FROM dual

LAPD,RPAD补位函数,不足指定位数时用指定字符补足位数
LPAD补在左侧，RPAD补在右侧
SELECT LPAD(sal,5,'$')
FROM emp_wsh

SELECT RPAD(sal,5,'$')
FROM emp_wsh

SUBSTR截取字符串
数据库中的下标都是从1开始的
第一个参数为要被字符串，第二个参数为从哪里开始取，第三个参数为取几个字符
SELECT
SUBSTR('thinking in java',13,4)
FROM dual

第三个参数不指定则是截取到末尾，指定的长度若成功实际可以截取的内容也是截取到末尾
SELECT
SUBSTR('thinking in java',10)
FROM dual

截取的位置可以是负数，若是负数则表示从倒数第几个字符开始截取
SELECT
SUBSTR('thinking in java',-4,4)
FROM dual

INSTR(char1,char2[,n,m])函数
查找char2在char1中的位置
n为从第几个字符开始检索
m为第几次出现
n,m不写默认都是1

如果查找不到返回值为0
SELECT
INSTR('thinking in java','in',4,2)
FROM dual

数字函数
ROUND(n,m)四舍五入
SELECT ROUND(45.678,2) FROM dual;
SELECT ROUND(45.678,0) FROM dual;
SELECT ROUND(45.678,-1) FROM dual;

TRUNC(n,m)截取数字
SELECT TRUNC(45.678,2) FROM dual;
SELECT TRUNC(45.678,0) FROM dual;
SELECT TRUNC(45.678,-1) FROM dual;

MOD(m,n)求余数
SELECT ename,sal,MOD(sal,1000)
FROM emp_wsh

CEIL,FLOOR
向上取整，向下区整
SELECT CEIL(45.678) FROM dual;
SELECT FLOOR(45.678) FROM dual;

查询scott员工的信息
SELECT ename,sal,deptno
FROM emp_wsh
WHERE ename=UPPER('scott')

查询名字只有5个字母的员工的名字，工资
SELECT ename,sal
FROM emp_wsh
WHERE LENGTH(ename)=5

查询第三个字母是A的员工信息
SELECT *
FROM emp_wsh
WHERE SUBSTR(ename,3,1)='A'

 

日期类型
SYSDATE,SYSTIMESTAMP
SYSDATE对应数据库一个内部函数，该函数返回一个表示当前系统时间的DATE类型的值
SYSTIMESTAMP返回的是一个表示当前系统时间的时间戳类型的值
SELECT SYSDATE FROM dual
SELECT SYSTIMESTAMP FROM dual

TO_DATE函数
可以将字符串按照给定的日期格式解析为一个DATE类型的值
SELECT
TO_DATE('2008-08-08 20：08：08','YYYY-MM-DD HH24:MI:SS')
FROM dual

如果日期格式中凡不是英文，符号，数字的其他字符，都需要使用双引号括起来
SELECT
TO_DATE('2008年08月08日 20：08：08','YYYY"年"MM"月"DD"日" HH24:MI:SS')
FROM dual

日期的计算
日期可以与一个数字进行加减法，这相当于加减指定的天数
两个日期可以进行减法，差为相差的天数

查看每个员工自今入职多少天了？
SELECT ename,SYSDATE-hiredate
FROM emp_wsh+

输入自己的生日,查看到今天为止活了多少天
SELECT SYSDATE-TO_DATE('1994-09-08','YYYY-MM-DD')
FROM dual

TO_CHAR()可以将DATE按照指定格式转换为字符串
SELECT TO_CHAR(SYSDATE,'YYYY-MM-DD HH24:MI:SS')
FROM DUAL

SELECT
TO_CHAR(TO_DATE('49-08-08','RR-MM-DD'),'YYYY-MM-DD')
FROM DUAL

LAST_DATE(date) 返回给定日期所在的月份的月底日期

查看当月底?
SELECT LAST_DAY(SYSDATE)
FROM dual

ADD_MONTHS(date,i)
返回日期date增加i个月后的日期

查看每个员工入职20周年纪念日
SELECT ename,ADD_MONTHS(hiredate,12*20)
FROM emp_wsh

MONTHS_BETWEEN(date1,date2)
计算两个日期之间相差的月，计算是根据date1-date2得到的

查看每个员工自今入职多少个月了？
SELECT ename,MONTHS_BETWEEN(SYSDATE,hiredate)
FROM emp_wsh

NEXT_DAY(date,i)
返回给定的第二天开始一周之内的指定周几的日期
i:1表示周日，2表示周一，以此类推

SELECT NEXT_DAY(SYSDATE,7)
FROM dual

LEAST、GREATEST
求最小值与最大值，除了日期外常用的数子以可以比较大小
参数至少要一个，但是一个没意义；
最多不限

SELECT
LEAST(SYSDATE,
TO_DATE('2008-08-08',
'YYYY-MM-DD'))
FROM dual

SELECT
GREATEST(SYSDATE,
TO_DATE('2008-08-08',
'YYYY-MM-DD'))
FROM dual

EXTRACT()提供给定日期中指定时间分量的值
SELECT EXTRACT(YEAR FROM SYSDATE)
FROM dual

查看1980年入职的员工
SELECT ename,hiredate
FROM emp_wsh
WHERE EXTRACT(YEAR FROM hiredate)=1980

1.插入NULL值
CREATE TABLE student_wsh(
id NUMBER(4),name CHAR(20),gender CHAR(1)
);
INSERT INTO student_wsh VALUES(1000,'李莫愁','F');
INSERT INTO student_wsh VALUES(1001,'林平之',NULL);
INSERT INTO student_wsh(id,name) VALUES(1002,'张无忌');

2.更新NULL
UPDATE student_wsh
SET gender=NULL
WHERE id=1000

3.判断字段的值是否为NULL
判断要使用 IS NULL 或者 IS NOT NULL
DELETE FROM student_wsh
WHERE gender is NULL

4.非空约束，被 NOT NULL 字句修饰的字段不能为 NULL

NULL值的运算操作
NULL与任何数子运算结果还为NULL
NULL与字符串拼接等于什么都没干，还是原来的字符串

空值函数
NVL(arg1,arg2)
当aeg1为NULL,函数返回arg2的值
若不为NULL,则返回arg1本身。
所有该函数的作用是将NULL值替换为一个非NULL值

查看每个员工的收入
--错误
SELECT ename,sal,comm,sal+comm
FROM emp_wsh
--正确
SELECT ename,sal,comm,NVL(sal+comm,sal)
FROM emp_wsh

NVL2(arg1,arg2,arg3)
当arg1不为NULL,则函数返回arg2
当arg1为NULL,则函数返回arg3
该函数是根据一个值是否为NULL来返回两个不同结果

查看每个人的绩效情况，即：
有绩效的，显示为“有绩效”
绩效为NULL的显示为“没有绩效”
SELECT ename,comm,NVL2(comm,'有绩效','没有绩效')
FROM emp_wsh

 

DQL查询语句

SELECT子句中可以使用函数或表达式，那么结果集中对应的该字段名就是这个函数或表达式，
可读性差，为此可以为这样的字段添加别名，那么结果集会以这个别名作为该字段的名字
别名不区分大小写，而且不能含有空格，
若希望区分大小写或含有空格，那么可以在别名上使用双引号括起来
SELECT ename,sal*12 "sal year"
FROM emp_wsh;

WHERE子句用来限定查询条件
SELECT *
FROM emp_wsh
WHERE deptno=10;

SELECT *
FROM emp_wsh
WHERE job='SALESMAN';

使用>,<,>=,<=,<>,=,!=
<>等价于!=，但是建议使用<>，因为<>所有数据库通用
SELECT ename,sal
FROM emp_wsh
WHERE sal<2000;

SELECT ename,sal,job
FROM emp_wsh
WHERE deptno<>10;

SELECT ename,sal,hiredate
FROM emp_wsh
WHERE hiredate>TO_DATE('1982-1-1','YYYY-MM-DD')

使用AND,OR关键字

--查询薪水大于1000并且职位是'CLERK'的职员信息
SELECT ename,sal,job
FROM emp_wsh
WHERE sal>1000 AND job='CLERK';
--查询薪水大于1000或职位是'CLERK'的职员信息
SELECT ename,sal,job
FROM emp_wsh
WHERE sal>1000 OR job='CLERK';

AND的优先级高于OR,可以通过括号来通过OR的优先级

--查询薪水大于1000并且职位是'CLERK'或'SALESMAN'的职员信息
SELECT ename,sal,job
FROM emp_wsh
WHERE sal>1000 AND (job='SALESMAN' OR job='CLERK');

LIKE用于模糊匹配字符串，支持两个通配符
_:单一的一个字符
%:任意个字符

查看名字第二个字母是A最后一个字母是N的员工信息
SELECT ename,job
FROM emp_wsh
WHERE ename LIKE '_A%N';

IN 和 NOT IN
判断是否在列表中或不在列表中
IN 和 NOT IN 常用来判断子查询的结果

SELECT ename,job
FROM emp_wsh
WHERE job IN('MANAGER','CLERK');

SELECT ename,job
FROM emp_wsh
WHERE deptno NOT IN(10,20);

BETWEEN.....AND.....
判断在一个区间范围内,
小的在左边，大的在右边

工资在1500到3000之间的员工
SELECT ename,sal
FROM emp_wsh
WHERE sal BETWEEN 1500 AND 3000;

使用 IS NULL,IS NOT NULL

SELECT ename,sal,comm
FROM emp_wsh
WHERE comm IS NULL;

SELECT ename,sal,comm
FROM emp_wsh
WHERE comm IS NOT NULL;

使用ANY和ALL条件
ALL:所有
ANY:其中之一
ANY与ALL不能单独使用，要配合>,>=,<,<=一和个列表使用
>ALL(list):大于列表中最大的（大于列表中所有的）
>ANY(LIST):大于列表中最小的（大于列表中任意一个）
<ALL(list):小于列表中最小的（小于列表中所有的）
<ANY(list):小于列表中最大的（小于列表中任意一个）
ANY和ALL常用于子查询

SELECT empno,ename,job,sal,deptno
FROM emp_wsh
WHERE sal > ANY (3500,4000,4500);

使用函数或者表达式作为过滤条件

SELECT ename,sal,job
FROM emp_wsh
WHERE ename=UPPER('scott');

SELECT ename,sal,job
FROM emp_wsh
WHERE sal*12>50000;

DISTINCT关键字
对于集中指定字段重复的记录进行去重

查看公司有哪些职位？
SELECT DISTINCT job
FROM emp_wsh;

多字段去重，是对这些字段值的组合进行去重。组合不会重复，单当个字段的值可能会重复
SELECT DISTINCT job,deptno
FROM emp_wsh;

排序
ORDER BY 子句
ORDER BY 可以根据其后指定的字段对结果集按照该字段的值进行升序或降序排列
ASC:升序，不写默认就是升序
DESC:降序。

查看公司工资排名：
--升序
SELECT ename,sal
FROM emp_wsh
ORDER BY sal;
--降序
SELECT ename,sal
FROM emp_wsh
ORDER BY sal DESC;

ORDER BY 按照多个字段排序
ORDER BY 首先按照第一个字段的排序方式对结果进行排序，
当第一个字段有重复值时才会按照第二个字段排序方式进行排序，
以此类推，每个字段都可以单独指定排序方式。

SELECT ename,deptno,sal
FROM emp_wsh
ORDER BY deptno DESC,sal DESC;

排序的字段中若有NULL值，则默认NULL为最大值

SELECT ename,comm
FROM emp_wsh
ORDER BY comm DESC;

聚合函数
聚合函数又叫多行函数，分组函数
聚合函数是对结果集某些字段的值进行统计的

MAX,MIN
求给定字段的最大值和最小值

查看公司的最高工资与最低工资
SELECT MAX(sal),MIN(sal)
FROM emp_wsh;

AVG,SUM
求平均值和总和
SELECT AVG(sal),SUM(sal)
FROM emp_wsh;

COUNT函数
COUNT函数不是对给定的字段的值进行统计的，
而是对给定字段不为NULL的记录数统计的。
实际上所有聚合函数都忽略NULL值统计。

SELECT COUNT(comm)
FROM emp_wsh;

通常查看表记录数可以使用COUNT(*)
SELECT COUNT (*)
FROM emp_wsh;

如果要查看平均值，其中有值为NULL，则要用先AVL函数将NULL转换为0再用AVG取平均值
查看平均绩效
SELECT AVG(NVL(comm,0)),SUM(comm)
FROM emp_wsh;

分组
GROUP BY 子句
GROUP BY 可以将结果集按照其后指定的字段值相同的记录看做一组，
然后配合聚合函数进行更加细分的统计工作。

查看每个部门的平均工资？
SELECT AVG(sal),deptno
FROM emp_wsh
GROUP BY deptno;

查看每个职位的最高工资？
SELECT MAX(sal),job
FROM emp_wsh
GROUP BY job;

GROUP BY 也可以根据多个字段分组
分组原则为这几个字段值都相同的记录看做一组

查看同部门同职位的平均工资？
SELECT AVG(sal),job,deptno
FROM emp_wsh
GROUP BY job,deptno
ORDER BY deptno;

当SELECT子句中含有聚合函数时，
那么凡不在聚合函数在的其他单独字段都必须出现在 GROUP BY 子句中，
反过来则不是必须的。

WHERE中不能使用聚合函数作为过滤条件，原因是过滤时机不对。
WHERE是在数据库检索表中数据时，对数据逐条过滤以决定是否查询出该数据时使用的，
所以WHERE用了过滤确定结果集的数据

使用聚合函数的结果集作为过滤条件，那么一定是数据从表在查询完毕（WHERE在查询过程中发挥作用）
得到结果集，并且分组完毕才进行聚合函数统计结果，得到后才可以对分组进行过滤，
由此可见，这个过滤时机是在WHERE之后进行的。

聚合函数的过滤条件要在 HAVING 子句中.
使用 HAVING 子句必须跟在 GROUP BY 子句后，HAVING 是用来过滤分组的

查看部门的平均工资，前提是该部门的平均工资高于2000
SELECT AVG(sal),deptno
FROM emp_wsh
GROUP BY deptno
HAVING AVG(sal)>2000;

查看平均工资高于2000的部门的最高工资和最低工资分别是多少？
SELECT MAX(sal),MIN(sal),deptno
FROM emp_wsh
GROUP BY deptno
HAVING AVG(sal)>2000;

 

关联查询
从多张表中查询对应的记录信息
关联查询的重点在于这些表中的记录的对应关系，
最高关系也称为连接条件。

当两张表有相同字段时，SELECT子句中必须确定该字段是来自哪张表。
在关联查询中，表名也可以添加别名，这样可以简化SELECT语句的复杂程度

查看每个员工的名字以及其所在部门的名字
SELECT e.ename,d.dname,e.deptno
FROM emp_wsh e,dept_wsh d
WHERE e.deptno = d.deptno;

关联查询要添加连接条件，否则会产生笛卡尔积，笛卡尔积通常是一个无意义的结果集，
它的记录数是所有参与查询的表的记录数乘积的结果。要避免出现，
数据量大时容易出现内存溢出等现象。
N张表关联查询要有至少N-1个连接条件。

SELECT e.ename,d.dname,d.deptno
FROM emp_wsh e,dept_wsh d;

查看在NEW YORK工作的员工？
SELECT e.ename,d.deptno,d.loc
FROM emp_wsh e,dept_wsh d
WHERE e.deptno=d.deptno AND d.loc='NEW YORK';

查看工资高于3000的员工的名字工资，部门以及所在地
SELECT e.ename,e.sal,d.dname,d.loc
FROM emp_wsh e,dept_wsh d
WHERE e.deptno=d.deptno AND e.sal>3000;

内连接
内连接也是用来完成关联查询的
（list1） JOIN (list2) ON 连接条件

查看SALES部门的员工名字以及部门名字
SELECT e.ename,d.dname
FROM emp_wsh e JOIN dept_wsh d
ON e.deptno=d.deptno
WHERE d.dname='SALES';

不满足连接条件的记录是不会在关联查询中被查询出来的。

外连接
外连接除了会将满足连接条件的记录查询出来外，还会降不满足连接条件的记录也查询出来。
左外连接：以JOIN左侧表作为驱动表（所有数据都被查询出来），那么该表中的某条记录
满足连接条件时来自右侧表中的字段全部填NULL.
右外连接：与左外连接相似，驱动表换成右侧表
全外连接：两个表都是驱动表

左外连接
SELECT e.ename,d.dname
FROM emp_wsh e LEFT OUTER JOIN dept_wsh d
ON e.deptno=d.deptno;

右外连接
SELECT e.ename,d.dname
FROM emp_wsh e RIGHT OUTER JOIN dept_wsh d
ON e.deptno=d.deptno;

全外连接
SELECT e.ename,d.dname
FROM emp_wsh e FULL OUTER JOIN dept_wsh d
ON e.deptno=d.deptno;

在内连接条件的等号左边加（+），为左外连接
在内连接条件的等号右边加（+），为右外连接
不能在内连接条件的等号两边加（+）。

左外连接
SELECT e.ename,d.dname
FROM emp_wsh e JOIN dept_wsh d
ON e.deptno(+)=d.deptno;
有外连接
SELECT e.ename,d.dname
FROM emp_wsh e JOIN dept_wsh d
ON e.deptno=d.deptno(+);

自连接
自连接即：当前表的一条记录可以对应当前表自己的多条记录
自连接是为了解决同类型数据但是又存在上下级关系的树状结构数据时使用

查看每个员工以及其领导的名字
SELECT e.ename,m.ename
FROM emp_wsh e,emp_wsh m
WHERE e.mgr=m.empno

查看SMITH上司的名字，部门编号以及工作地址
SELECT e.ename,m.ename,m.deptno,d.loc
FROM emp_wsh e,emp_wsh m,dept_wsh d
WHERE e.mgr=m.empno AND m.deptno=d.deptno AND e.ename='SMITH';

SELECT e.ename,m.ename,m.deptno,d.loc
FROM emp_wsh e JOIN emp_wsh m
ON e.mgr=m.empno
JOIN dept_wsh d
ON m.deptno=d.deptno
WHERE e.ename='SMITH';

 

 

 

 

子查询
子查询是一条SELECT语句,但它是嵌套在
其他SQL语句中的,为的是给该SQL提供数据
以支持其执行操作.

查看谁的工资高于CLARK?
SELECT ename,sal
FROM emp_wsh
WHERE sal>(SELECT sal FROM emp_wsh
WHERE ename='CLARK')

查看与CLARK同职位的员工?
SELECT ename,job FROM emp_wsh
WHERE job=(SELECT job FROM emp_wsh
WHERE ename='CLARK')

查看与CLARK同部门的员工?
SELECT ename,deptno FROM emp_wsh
WHERE deptno=(SELECT deptno FROM emp_wsh
WHERE ename='CLARK')

在DDL中使用子查询
可以根据子查询的结果集快速创建一张表

创建表employee,表中字段为:
empno,ename,job,sal,deptno,dname,loc
数据为现有表中emp与dept对应的数据
CREATE TABLE employee_wsh
AS
SELECT e.empno,e.ename,e.job,e.sal,
e.deptno,d.dname,d.loc
FROM emp_wsh e,dept_wsh d
WHERE e.deptno=d.deptno(+)

DESC employee_wsh
SELECT * FROM employee_wsh

DROP TABLE employee_wsh

创建表时若子查询中的字段有别名则该表
对应的字段就使用该别名作为其字段名,
当子查询中一个字段含有函数或表达式,那么
该字段必须给别名.
CREATE TABLE employee_wsh
AS
SELECT e.empno id,e.ename name,
e.job,e.sal*12 salary,
e.deptno,d.dname,d.loc
FROM emp_wsh e,dept_wsh d
WHERE e.deptno=d.deptno(+)

SELECT name,salary
FROM employee_wsh

DML中使用子查询
将CLARK所在部门的所有员工删除
DELETE FROM employee_wsh
WHERE deptno=(SELECT deptno
FROM employee_wsh
WHERE name='CLARK')

SELECT * FROM employee_wsh

子查询常用于SELECT语句中.
子查询根据查询结果集的不同分为:
单行单列子查询:常用于过滤条件,可以配合
=,>,>=,<,<=使用
多行单列子查询:常用于过滤条件,由于查询出
多个值,在判断=时要用IN,
判断>,>=等操作要配合ANY,ALL
多行多列子查询:常当做一张表看待.

查询与SALESMAN同部门的其他职位员工：
SELECT ename,job,deptno
FROM emp
WHERE deptno IN (SELECT deptno
FROM emp
WHERE job='SALESMAN')
AND job<>'SALESMAN'

查看比职位是CLERK和SALESMAN工资都高的员工?
SELECT ename,sal
FROM emp
WHERE sal> ALL(SELECT sal FROM emp
WHERE job IN('CLERK','SALESMAN')
)

EXISTS关键字
EXISTS后面跟一个子查询,当该子查询可以查询
出至少一条记录时,则EXISTS表达式成立并返回
true

SELECT deptno, dname FROM dept d
WHERE
NOT EXISTS(SELECT * FROM emp e
WHERE d.deptno = e.deptno)

查看每个部门的最低薪水是多少?前提是该部门
的最低薪水要高于30号部门的最低薪水
SELECT MIN(sal),deptno
FROM emp
GROUP BY deptno
HAVING MIN(sal)>(SELECT MIN(sal)
FROM emp
WHERE deptno=30)

子查询在FROM子句中的使用
当一个子查询是多列子查询,通常将该子查询
的结果集当做一张表看待并基于它进行二次
查询.

查看比自己所在部门平均工资高的员工?

1:查看每个部门平均工资
SELECT AVG(sal),deptno
FROM emp
GROUP BY deptno

SELECT e.ename,e.sal,e.deptno
FROM emp e,(SELECT AVG(sal) avg_sal,
deptno
FROM emp
GROUP BY deptno) t
WHERE e.deptno=t.deptno
AND e.sal>t.avg_sal

在SELECT子句中使用子查询,可以将查询的结果
当做外层查询记录中的一个字段值显示
SELECT e.ename, e.sal,
(SELECT d.dname FROM dept d
WHERE d.deptno = e.deptno) deptno
FROM emp e

分页查询
分页查询是将查询表中数据时分段查询,而不
是一次性将所有数据查询出来.
有时查询的数据量非常庞大,这回导致系统资源
消耗大,响应速度长,数据冗余严重.
为此当遇到这种情况时一般使用分页查询解决.
数据库基本都支持分页,但是不同数据库语法不
同(方言).

ORACLE中的分页是基于伪列ROWNUM实现的.

ROWNUM不存在与任何一张表中,但是所有的表
都可以查询该字段.该字段的值是随着查询自动
生成的,方式是:每当可以从表中查询出一条记
录时,该字段得值即为该条记录的行号,从1开始,
逐次递增.

SELECT ROWNUM,empno,ename,sal,job
FROM emp
WHERE ROWNUM > 1

在使用ROWNUM对结果集进行编号的查询过程
中不能使用ROWNUM做>1以上的数字判断,否则
将查询不出任何数据.
SELECT *
FROM(SELECT ROWNUM rn,empno,
ename,sal,job
FROM emp)
WHERE rn BETWEEN 6 AND 10

查看公司工资排名的6-10
SELECT *
FROM(SELECT ROWNUM rn,t.*
FROM(SELECT empno,ename,sal
FROM emp
ORDER BY sal DESC) t)
WHERE rn BETWEEN 6 AND 10

SELECT *
FROM(SELECT ROWNUM rn,t.*
FROM(SELECT empno,ename,sal
FROM emp
ORDER BY sal DESC) t
WHERE ROWNUM <=10)
WHERE rn>=6

计算区间公式
pageSize:每页显示的条目数
page:页数

star:(page-1)*pageSize+1
end:pageSize*page

int start = (page-1)*pageSize+1;
int end = pageSize*page;

String sql = "SELECT * " +
"FROM(SELECT ROWNUM rn,t.* " +
" FROM(SELECT empno,ename,sal" +
" FROM emp " +
" ORDER BY sal DESC) t " +
" WHERE ROWNUM <="+end+") " +
"WHERE rn>="+start;

DECODE函数,可以实现分之效果的函数
SELECT ename, job, sal,
DECODE(job,
'MANAGER', sal * 1.2,
'ANALYST', sal * 1.1,
'SALESMAN', sal * 1.05,
sal
) bonus
FROM emp

DECODE在GROUP BY分组中的应用可以
将字段值不同的记录看做一组.

统计人数,将职位是"MANAGER","ANALYST"
看作一组,其余职业看作另一组分别统计人数.

SELECT
COUNT(*),DECODE(job,
'MANAGER','VIP',
'ANALYST','VIP',
'OTHER')
FROM emp
GROUP BY DECODE(job,
'MANAGER','VIP',
'ANALYST','VIP',
'OTHER')

SELECT deptno, dname, loc
FROM dept
ORDER BY
DECODE(dname,
'OPERATIONS',1,
'ACCOUNTING',2,
'SALES',3)

排序函数
排序函数允许对结果集按照指定的字段分组
在组内再按照指定的字段排序,最终生成组内
编号.

ROW_NUMBER()函数生成组内连续且唯一的数字:
查看每个部门的工资排名?
SELECT
ename,sal,deptno,
ROW_NUMBER() OVER(
PARTITION BY deptno
ORDER BY sal DESC
) rank
FROM emp

RANK函数,生成组内不连续也不唯一的数字,
同组内排序字段值一样的记录,生成的数字
也一样.
SELECT
ename,sal,deptno,
RANK() OVER(
PARTITION BY deptno
ORDER BY sal DESC
) rank
FROM emp

DENSE_RANK函数生成组内连续但不唯一
的数字.
SELECT
ename,sal,deptno,
DENSE_RANK() OVER(
PARTITION BY deptno
ORDER BY sal DESC
) rank
FROM emp

 

SELECT year_id,month_id,day_id,sales_value
FROM sales_tab
ORDER BY year_id,month_id,day_id

查看每天营业额?
SELECT year_id,month_id,day_id,
SUM(sales_value)
FROM sales_tab
GROUP BY year_id,month_id,day_id
ORDER BY year_id,month_id,day_id

每月营业额?
SELECT year_id,month_id,
SUM(sales_value)
FROM sales_tab
GROUP BY year_id,month_id
ORDER BY year_id,month_id

每年营业额?
SELECT year_id,
SUM(sales_value)
FROM sales_tab
GROUP BY year_id
ORDER BY year_id

总共营业额?
SELECT SUM(sales_value)
FROM sales_tab

SELECT year_id,month_id,day_id,
SUM(sales_value)
FROM sales_tab
GROUP BY year_id,month_id,day_id
UNION ALL
SELECT year_id,month_id,null,
SUM(sales_value)
FROM sales_tab
GROUP BY year_id,month_id
UNION ALL
SELECT year_id,null,null,
SUM(sales_value)
FROM sales_tab
GROUP BY year_id
UNION ALL
SELECT null,null,null,
SUM(sales_value)
FROM sales_tab

高级分组函数
高级分组函数用在GROUP BY子句中,每个高级
分组函数都有一套分组策略.

ROLLUP():分组原则,参数逐次递减,一直到所有
参数都不要,每一种分组都统计一次结果
并且并在一个结果集显示.

GROUP BY ROLLUP(a,b,c)
等价于:
GROUP BY a,b,c
UNION ALL
GROUP BY a,b
UNION ALL
GROUP BY a
UNION ALL
全表

查看每天,每月,每年以及总共的营业额?
SELECT year_id,month_id,day_id,
SUM(sales_value)
FROM sales_tab
GROUP BY
ROLLUP(year_id,month_id,day_id)

CUBE():每种组合分一次组
分组次数:2的参数个数次方

GROUP BY CUBE(a,b,c)
abc
ab
bc
ac
a
b
c
全表

SELECT year_id,month_id,day_id,
SUM(sales_value)
FROM sales_tab
GROUP BY
CUBE(year_id,month_id,day_id)
ORDER BY year_id,month_id,day_id

GROUPING SETS:每个参数是一种分组方式,
然后将这些分组统计后并在一个结果集显示.

仅查看每天与每月营业额?
SELECT year_id,month_id,day_id,
SUM(sales_value)
FROM sales_tab
GROUP BY
GROUPING SETS(
(year_id,month_id,day_id),
(year_id,month_id)
)

 

视图
视图是数据库对象之一
所有数据库对象名字不能重复，所以视图名字一般是以"开头
视图在SQL语句中体现的角色与表相同但是视图并不是一张真正存在的表，
而只是对应一个SELECT语句的查询结果集，并将其当做表看待而已。
使用视图的目的是简化SQL语句的复杂程度，重用子查询，限制数据访问。

创建视图
该视图包含的数据为10号部门的员工信息
CREATE VIEW v_emp_10_wsh
AS
SELECT empno,ename,sal,deptno
FROM emp_wsh
WHERE deptno=10;

查看视图数据
SELECT * FROM v_emp_10_wsh;

视图对应的子查询中的字段含有函数或表达式，那么该字段必须指定别名。
当视图对应的子查询在的字段使用了别名，那么视图在该字段就用别名来命名。

修改视图
由于视图仅对应一个SELECT语句，所以修改视图就是替换该SELECT语句而已。
CREATE OR REPLACE VIEW v_emp_10_wsh
AS
SELECT empno id,ename name,sal salary,deptno
FROM emp_wsh
WHERE deptno=10;

DESC v_emp_10_wsh;

视图分为简单视图与复杂视图
简单视图：对应的子查询中不包含有关联查询，查询的字段不包含函数，表达式，没有分组，没有去重。
反之则是复杂视图。

对视图进行DML操作
仅能对简单视图进行DML操作。
对视图进行DML操作就是对视图数据来源的基础表进行操作

插入数据
INSERT INTO v_emp_10_wsh
(id,name,salary,deptno)
VALUES
(1001,'JACK',2000,10)
修改数据
update v_emp_10_wsh
SET salary=3000
WHERE id=1001
删除数据
DELETE
FROM v_emp_10_wsh
WHERE id=1001
查询
SELECT * FROM v_emp_10_wsh
SELECT * FROM emp_wsh
ORDER BY empno

对视图DML操作就是对基表操作，那么操作不当可能对基表进行数据污染
INSERT INTO v_emp_10_wsh
(id,name,salary,deptno)
VALUES
(1002,'ROSE',3000,20)

插入视图对ROSE视而不见
SELECT * FROM v_emp_10_wsh
SELECT * FROM emp_wsh

更新同样存在更新后对数据不可控的情况
UPDATE v_emp_10_wsh
SET deptno=20

删除不会对基表产生数据污染
DELETE
FROM v_emp_10_wsh
WHERE deptno=20

为视图添加查询选项，可以保证对视图的DML操作后对其可见，否则不允许进行DML操作，
这样就避免了对基表进行数据污染。
CREATE OR REPLACE VIEW v_emp_10_wsh
AS
SELECT empno id,ename name,sal salary,deptno
FROM emp_wsh
WHERE deptno=10
WITH CHECK OPTION

为视图添加只读选项，那么该视图不允许进行DML操作
CREATE OR REPLACE VIEW v_emp_10_wsh
AS
SELECT empno id,ename name,sal salary,deptno
FROM emp_wsh
WHERE deptno=10
WITH READ ONLY

SELECT object_name
FROM user_objects
WHERE object_type='VIEW'
AND object_name LIKE '%_WSH'

SELECT TEXT,view_name
FROM user_views

SELECT table_name
FROM user_tables

复杂视图
创建一个含有公司部门工资情况的视图，
内容为：部门编号，部门名称，部门的最高，最低，平均以及工资总和信息。
CREATE VIEW v_dept_sal_wsh
AS
SELECT d.deptno,d.dname,
MIN(e.sal) min_sal,
MAX(e.sal) max_sal,
AVG(e.sal) avg_sal,
SUM(e.sal) sum_sal
FROM emp_wsh e,dept_wsh d
GROUP BY d.deptno,d.dname

SELECT * FROM v_dept_sal_wsh

查看谁比自己所在部门工资高
SELECT e.ename,e.sal,e.deptno
FROM emp_wsh e,v_dept_sal_wsh v
WHERE e.deptno=v.deptno
AND e.sal>v.avg_sal

删除视图
删除视图本身并不会影响基表数据。
但是删除视图数据会对应将基表数据删除
DROP VIEW v_emp_10_wsh

序列
序列也是数据库对象之一
作用是生成一系列数字
序列常用于为某张表的主键字段提供值使用
CREATE SEQUENCE seq_emp_id_wsh
START WITH 1
INCREMENT BY 1

序列支持两个伪列：
NEXTVAL:获取序列下一个值
若是新创建的序列，那么第一次调用的返回值是START WITH指定的值，
以后每次调用都会得到当前序列值加上步长后的数字。
NEXTVAL会导致序列发生步进，且序列不能回退

CURRVAL:获取序列当前值，即：最后一次调用NEXTVAL后得到的值，CURRVAL不会导致步进。
但是新创建的序列至少调用一次NEXTVAL后才可以使用CURRVAL.
SELECT seq_emp_id_wsh.NEXTVAL
FROM dual

使用序列为EMP表中插入的数据提供主键字段的值
INSERT INTO emp_wsh
(empno,ename,sal,job,deptno)
VALUES
(seq_emp_id_wsh.NEXTVAL,'ROSE',3000,'CLERK',10)

SELECT * FROM emp_wsh

删除序列
DROP SEQUENCE seq_emp_id_wsh

索引
索引是数据库对象之一
随意是为了提高查询效率

索引的统计与应用是数据库自动完成的，
只有数据库认为可以使用某个已创建的索引时就会自动应用。

CREATE INDEX idx_emp_ename_wsh
ON emp_wsh(ename);

复合索引
复合索引是基于多个列的索引
CREATE INDEX idx_job_sal_wsh
ON emp_wsh(job,sal)

函数索引
CREATE INDEX emp_ename_upper_idx
ON emp_wsh(UPPER(ename));

修改索引
如果经常在索引列上执行DML操作，需要定期重建索引
ALTER INDEX idx_job_sal_wsh REBUILD;

删除索引
DROP INDEX idx_job_sal_wsh;

 

约束
非空约束

创建表时添加非空约束
CREATE TABLE employees_wsh(
eid NUMBER(6),
name VARCHAR2(30) NOT NULL,
salary NUMBER(7,2),
hiredate DATE CONSTRAINT empplyees_wsh_hiredate_nn NOT NULL
);

修改表时添加非空约束
ALTER TABLE employees_wsh
MODIFY(eid NUMBER(6) NOT NULL);

取消非空约束
ALTER TABLE employees_wsh
MODIFY(eid NUMBER(6) NULL);

唯一性约束
唯一性约束可以保证表中该字段的值任何一条记录都不可以重复，NULL除外

创建表时添加唯一性约束
CREATE TABLE employees1_wsh(
eid NUMBER(6) UNIQUE,
name VARCHAR(30),
email VARCHAR2(50),
salary NUMBER(7,2),
hiredate DATE,
CONSTRAINT employees1_wsh_email_uk UNIQUE(email)
);
DESC employees1_wsh

INSERT INTO employees1_wsh
(eid,name,email)
VALUES
(1,'JACK','JACK@qq.com')

INSERT INTO employees1_wsh
(eid,name,email)
VALUES
(NULL,'JACK',NULL);

SELECT * FROM employees1_wsh

在建表后增加唯一性约束
添加唯一性约束时，表中添加唯一性约束的字段值不能有重，否则会报找到重复关键字错误
ALTER TABLE employees1_wsh
ADD CONSTRAINT employees1_wsh_name_uk UNIQUE(name);

主键约束
1.主键应是对系统无意义的数据
2.主键值永远不会改变
3.主键尽量建立在单列上
4.一张表只能有一个主键

创建表时添加主键约束
CREATE TABLE employees2_wsh(
eid NUMBER(6) PRIMARY KEY,
name VARCHAR2(30),
email VARCHAR2(50),
salary NUMBER(7,2),
hiredate DATE
);

INSERT INTO employees2_wsh
(eid,name)
VALUES
(2,'JACK');
SELECT * FROM employees2_wsh
DESC employees2_wsh

检查约束
ALTER TABLE employees2_wsh
ADD CONSTRAINT employees2_wsh_salary_check CHECK(salary>2000);

INSERT INTO employees2_wsh
(eid,name,email,salary)
VALUES
(1236,'donna noble','1236@qq.com',2500);

UPDATE employees2_wsh
SET salary=1500
WHERE eid=1236;