SQL基础教程-优快云博客

本文链接：https://blog.youkuaiyun.com/m0_37480346/article/details/82758473

---------------------------------------------------------------------------数据库---------------------------------------------------
-----------------------------DDL--------------------
-DDL
用于建立修改删除数据库对象
   -CREATE 创建表或其他对象的结构
   -ALTER 修改表或其他对象的结构
   -DROP 删除表或其他对象的结构
   -TRUNCATE 删除表数据保留表结构
--创建表
CREATE TABLE employee_wk(内容);
--查询表
DESC employee_wk
--修改表名
RENAME employee_wk TO myemp_wk;
--删除表
DROP TABLE employee_wk;
--删除列
ALTER TABLE myemp_wk DROP(那一列);
--修改列
ALTER TABLE myemp_wk MODIFY(
job VARCHAR2 (40) DEFAULT 'CLERK' //需要修改的列
);
CREATE TABLE emp_wk(创建表
empno NUMBER(4,0),
ename VARCHAR2(10),
job VARCHAR2(9),
mgr NUMBER(4,0),
hiredate DATE,
sal NUMBER(7,2),
comm NUMBER(7,2),
deptno NUMBER(2,0)
);

1---------------------DDL---------------------------1
1注意事项: 1
1--在数据库中所有类型的默认值都是null 1
1--即：当插入数据时若某个字段没有指定值的时 1
1--可以创建表的时候通过DEEAULT关键字来单读指定默认值 1
1--可以修改列的类型长度以及默认值或非空当表中存在数据后 1
1--尽量不修改字段类型若修改长度尽量不要减小否则可能修改失败 1
1-字符串的字面量是使用单引号括起来的这一点与java不同 1
1 DEFAULT SYSDATE 默认系统时间 1
1 NOT NULL 非空不能没有数据 1
1---------------------------------------------------------------------1

-----------------------------DML---------------------
-DML
用于改变数据表中的数据
   -INSERT 将数据插入到数据表中
   -UPDATE 更新数据表中已存在的数据
   -DELETE 删除数据表中的数据
--插入
INSERT INTO myemp_wk(
id,name,salary,job
)
VALUES
(1,'JAVK',5000,'CLERK')

--插入一个日期的值
insert INTO myemp_wk(
id,name,birth
)
values(
2,'WK',to_date('2016-05-17','yyyy-mm-dd')
)
select * from myemp_wk;

--查询表
SELECT *FROM myemp_wk;

--修该表中现有数据
UPDATE myemp_wk SET GENDAR='N'--修改myemp_wk表中GENDAR 可以修改多个
WHERE name='JAVK' --判断name=javk的一列

--清空表
delete：可以回退速度慢点
delete from myemp_wk
where name='hq' //把hq列表删除
truncate:不可回退速度快
truncate table myemp_wk

1--------------------------------DML--------------------1
1 DML可以对表中的数据进行操作分为INSERT，UPDATE，DELETE 1
1 DMS是伴随事务使用的 1
1 --插入数据时可以不指定具体列若这样则是全列插入 1
1--------------------------------------------------------------------------1

-----------------------------TCL--------------------------------------------------------
-TCL
用来维护数据一致性的语句
   -COMMIT 提交，确定已经进行的数据改变
   -ROLLBACK 回滚,取消已经进行的数据改变
   -SAVEPOINT保存点，使当前的事务可以回退到指定的保存点，便于取消部分改变

-----------------------------DQL--------------------------------------------------------
-DQL
--查询语句DQL
--SELECT 语句可用于查询数据
--查看emp表中所有字段以及所有记录
   SELECT *FROM emp_wk;
--查看某几个字段的值
   SELECT ename,job,sal,deptno from emp_wk
--查看：字段函数表达式查看每个员工的年薪
   SELECT ename,sal*12 from emp_wk;
--查看：SELECT语句也可以使用WHERE字句查看20号部分的员工信息
SELECT ename,sal,job,deptno FROM emp_wk WHERE deptno=20
--SQL语句中可以使用“||”链接字符串
   SELECT ename||':'||sal FROM emp_wk
--获取字符串的长度 LENGTH(p)
   SELECT ename,LENGTH(ename) FROM emp_wk
--LOWER,UPPER,INITCAP:转换小写大写首字母大写（伪表）
   SELECT LOWER('HELLOWORD') FROM dual
   SELECT ename,sal,job FROM emp_wk
   WHERE ename=UPPER('scott')
--去除字符串俩边内容
   SELECT TRIM('e' FROM'eeeeliteeee')
   FROM emp_wk
   --去除左边的相同内容（把LIT左边的E全部拿掉了）
   SELECT LTRIM('EEEEEELITEEE','E')
   FROM EMP_WK
   --去除右边的相同字体
   SELECT RTRIM('EEEEELITEEEE','E')
   FROM EMP_WK
--补位函数 LPAD RPAD 位数不足时向左（右）补给定字符（LPAD左补 RPAD右补）
   SELECT LPAD(SAL,4,' ') FROM emp_wk
--SUBSTR(char,m[,n])函数：获取字符串
说明：
   截取str字符串从m处开始链接截取n个若不
   指定n 则是连续截取到字符串末尾 n若超过
   实际可截取的长度也是到字符串末尾
   SELECT SUBSTR('thinking in java',10,2)FROM dual//从第10开始取取2个
--INSTR(char1,char2[,n[,m]]):查找位置
说明：
   --查找str2在str1中的位置
   --若指定了位置n：从n出开始查找第一次出现的位
   --若指定了m：则是查找敌m次出现的位置
   SELECT INSTR('thinking in java','in',4,2) FROM dual//从第4开始查第2次出现in的位置
--ROUND(n[,m])
   --对n进行四舍五入
   --m时保留到小数点后多少为
   --若m为0或不指定则保留到整数
   --若m为负数则是保留小数点前的位数
   SELECT ROUND(45.67,2),
   ROUND(45.678),
   ROUND(54.678,-1)
   FROM dual
--截取数字
   SELECT TRUNC(45.678,2),//45.67
TRUNC(45.678),//45
TRUNC(45.678,-1)FROM dual//40
--MOD(n.m):求余数
   若m为0 则直接返回n
   SELECT MOD(14,13) FROM dual//求余的 1
--CEIL FLOOR:向上取整向下取整
   SELECT CEIL(45.678),FLOOR(45.678) FROM DUAL

--日期类型
--DATE与TIMESTAMP
--时间截的前面7个字节与DATE一致，多出的4个字节保存一个证书(int)
--可以存秒以下的精度所以可以精确到纳秒
--日期相关关键字
--SYSDATE:表示一个内部函数返回一个表示当前系统的DATE类型的值
   INSERT INTO emp_wk(empno,ename,hiredate,sal,job,deptno)
   VALUES(1001,'JACK',SYSDATE,5000,'mou',30)
   （新增一个新的一行列表名字‘JACK’时间为今天）
--SYSTIMESTAMP:返回时间戳类型当前系统时间
   SELECT SYSTIMESTAMP FROM dual
--日期类型可以比大小越晚的越大
--日期可以进行加减运算加上一个数字
--等于同于加上指定的天数减法同理
--俩个日期剑也可以相减差为相差的天数
--计算自己生活多少天
   SELECT TRUNC(SYSDATE-to_date('1998-02-22','yyyy-mm-dd')) FROM dual
-- 查看1982年以后入职的员工：
   SELECT ename,hiredate FROM emp_wk
   WHERE hiredate>TO_DATE('1982-01-01','yyyy-mm-dd')
--TO_CHAR() 可以将日期按照指定日期格式转换为字符串
    SELECT TO_CHAR(SYSDATE,'yyyy-mm-dd HH24:MI:SS') FROM dual
--日期格式字符串中出现的字符凡不是英文与符号其他字符全部要使用双引号括起来
    SELECT TO_CHAR(SYSDATE,'yyyy"年"mm"月"dd"日" HH24"时"MI"分"SS"秒"') FROM dual
--RR时间
    SELECT to_char(to_date('49-02-04','rr-mm-dd'),'yyyy-mm-dd') FROM dual
--LAST_DAY(date)：返回给定的时间所在月的月底时间
   SELECT LAST_DAY(SYSDATE) FROM dual
--ADD_MONTHS（date,i）对指定日期加上指定的月
   SELECT ename,ADD_MONTHS(hiredate,12*20) FROM emp_wk//可以为负数
--MONTHS_BETWEEN(date1,date2)返回俩个日期之间相差的月计算是根据date1-date2计算的结果
   SELECT ename,ROUND(MONTHS_BETWEEN(SYSDATE,hiredate)) FROM emp_wk
--NEXT_DAY(date,i)返回给定日期之后一周内的周几对应的日期 i表示周几 1为周日 2为周一推理
       SELECT NEXT_DAY(SYSDATE,5) FROM DUAL
--LEAST GREATEST 求最小值与最大值
   SELECT LEAST(SYSDATE,TO_DATE('2008-10-10','yyyy-mm-dd')) FROM dual--最小值
   SELECT GREATEST(SYSDATE,TO_DATE('2008-10-10','yyyy-mm-dd')) FROM dual--最大值
--EXTRACT函数提取一个日期中指定时间分量对应的值
   SELECT EXTRACT（YEAR FROM SYSDATE） FROM DUAL
--筛选1982年入职的员工
   SELECT ename,hiredate FROM emp_wk
   WHERE EXTRACT(YEAR FROM hiredate)=1982
--空值函数 NVL(p1,p2) 当p1为null时返回p2 若p1不为null 则函数返回p1自身所以是将null替换一个非null值
   SELECT ename,sal,comm,sal+NVL(comm,0) FROM emp_wk
--查看员工奖金状态：若有奖金的则显示为‘有奖金’没有的则显示为‘没有奖金’
   --NVL2(p1,p2,p3)：若p1不为null则函数返回p2 若p1为null 则返回函数p3
   SELECT ename,comm,NVL2(comm,'有奖金','没有奖金')FROM emp_wk

--别名
   --当一个SELECT字句中查询的内容是一个函数或者表达式
   --那么在结果集对应的该字段的字段命就是这个函数或表达式
   --可读性差为此应当为该列添加别名
   SELECT ename ,sal*12 sal FROM emp_wk //把sak*12 改名为sal
--WHERE
   AND OR & | 与或
   AND的优先级高于OR 所以可以通过添加括号提高OR的优先级
-- IN NOT IN 判断在列表中和不再列表中 IN与NOT IN 常用与子查询
   SELECT ename,job,sal FROM emp_wk
   WHERE job IN('MANAGER','CLERK')//是这俩个部分的列出来
   WHERE job NOT IN('MANAGER','CLERK')//除了这俩个部分其余的全列出来
-- BETWEEN--- AND 计算某值到某值直接的列表
   SELECT ename,sal FROM emp_wk
   WHERE sal BETWEEN 1500 AND 3000
-- 查看名字第二个字符是A的员工
   SELECT ename,job,sal FROM Emp_wk
   WHERE ename LIKE '__A%'
-- IS NULL NOT NULL 是null和不是null
   SELECT ename,sal,comm FROM emp_wk
   HERE comm is null
--ANY ALL 用于判断诸如>,>=,<,<=一个列表中
   ALL(list):大于列表中所有的(大于最大的)
   ANY(list):大于列表中之一即可(大于最小的)
   AND ALL的列表通常不会给确定值这样没有意义通常使用在判断一个子查询的结果
   SELECT empno ,ename,job,sal,deptno FROM emp_wk
   WHERE sal> ANY(1000,3500)//选择1000
   SELECT empno ,ename,job,sal,deptno FROM emp_wk
   sal> ALL(1000,3000,3500)//选择3500
--使用函数或者表达式的结果作为过滤条件
   SELECT ename,sal,job FROM emp_wk
   WHERE ename= UPPER('scott')
   SELECT ename,sal,job FROM emp_wk
   WHERE sal*12>50000
--DISTINCT 去除重复值必须紧跟在SELECT关键字之后后面可以跟多个字段
   DISTINCT后面可以根的字段：这些字段值的组合没有重复性
   查看公司总共有多少种职位
   SELECT DISTINCT job FROM Emp_wk

--ORDER BY 排序
   ORDER BY字句：可以根据后面指定的字段对结果集进行生序活着降序排列其中ASC 是升序 DESC是降序
   --ORDER BY可以按照多个字段排序排序石油优先级的首先按照第一个字段的排序规则排序
   --当第一个字段有重复值时猜按照第二个字段排序
--通常ASC可以不写不写默认就是升序 ORDER BY只能写在SELECT语句中最后一个语句上
   SELECT ename,sal FROM emp_wk
   ORDER BY sal DESC
--聚合函数忽略NULL值
   聚合函数又称为多行函数分组函数聚合函数可以将多条记录进行统计
   然后得出一个结果所以聚合函数是用来统计用的
   --MAX(),MIN()
    --用来统计最大值与最小值
   SELECT MAX(sal) sal_max,MIN(SAL) SAL_MIN FROM emp_wk
--AVG() SUM() 求平均值总和
   SELECT AVG(SAL),SUM(SAL) FROM EMp_WK
--COUNT() 统计记录总数
   SELECT COUNT(ename) FROM EMP_WK
--聚合函数忽略NULL值
   SELECT AVG(COMM),SUM(COMM) FROM EMP_WK
   SELECT AVG(NVL(COMM,0)) FROM EMP_WK
--统计表中总数据量
   SELECT COUNT(*) FROM EMP_WK

--分组
   --GROUP BY字句
   --GROUP BY可以将结果集按照指定的字段值相同的几率看作一组
   --配合聚合函数使用可以对不同分组的记录分别进行统计然后得到结果
   --在SELECT当中若使用了聚合函数那么不再聚合函数中的其他单独字段
   --必须出现在GROUP BY 反过来不是必须的
   --GROUP BY也可以按照多个字段进行分组分组原则是这些字段值的组合相同的看作一组

   SELECT MAX(SAL),MIN(SAL),deptno FROM emp_wk
   GROUP BY deptno
   --查看每个部门每种职位的平均工资
   SELECT AVG(SAL),DEPTNO,JOB FROM EMP_WK
   GROUP BY DEPTNO,JOB
--HAVING字句：
   --HAVING字句必须紧跟在GROUP BY字句之后作用是添加条件来过滤不同的分组
   --HAVING可以使用聚合函数作为过滤条件
    查看部门平均工资高于2000的这些部门的最高工资和最低工资
   SELECT MAX(SAL),MIN(SAL) ,DEPTNO FROM EMP_WK
   GROUP BY DEPTNO
   HAVING AVG(SAL)>2000
--关联查询
--关联查询是建立在多张表上进行
--联合查询
--查询的结果集中每一调记录中的字段可能来自于不同表
--重点是：找到表与表的记录之间的对应关系
   SELECT ename,job,sal,e.deptno,dname,loc,d.deptno FROM emp_wk e ,dept_wk d
   WHERE e.deptno=d.deptno
   --上述SQL中：e.deptno=d.deptno
   --条件是用来联系emp，dept的数据关系的
   --在关联查询中必须要添加链接条件 N张表关联查询至少要添加n-1个链接条件不添加链接条件会产生笛卡尔积

--实际开发中要比哦阿弥笛卡尔积的出现这是一个无意义的结果集
SELECT a.ename ,b.dname FROM EMP_wk a ,dept_wk b
--查看SALES部门的员工
   SELECT E.ENAME,E.JOB,E.SAL,E.DEPTNO,d.dname FROM EMP_WK E,DEPT_wk d
   WHERE e.DEPTNO=d.DEPTNO AND d.loc='NEW YORK'
--内链接
   --链接条件可以更好的过滤(WHERE)
   SELECT e.ename ,d.dname FROM emp_wk e JOIN dept_wk d
   ON e.deptno=d.deptno
--外链接分为：左left 右RIGHT 外 FULL 左是左边的做驱动表
--左外链接右外链接：以JOIN左(右)侧的表为驱动表该表所有记录都要显示出来
--那么当某条记录不满足链接条件时那么来自JOIN右侧表中的字段的值全部显示为NULL
SELECT e.ename,e.sal,d.dname,d.loc FROM emp_wk e LEFT outer join dept_wk d
ON e.deptno=d.deptno
--全外链接
   -- 关联查询也可以实现外链接效果（+）定义在链接条件上定义在哪边那边就补null
   -- 但是不能实现全外链接效果
   SELECT e.ename,d.dname FROM emp_wk e,dept_wk d
   WHERE e.deptno=d.deptno(+)
--自链接
   --当前表的一条记录对应当前表自己的多条记录这种设计就是自链接
   --自链接是用来解决数据内容相同但是数据间又存在上下级关系的树状结构的情况
   --查看每个员工的上司
   SELECT e.ename ,m.ename FROM emp_wk e,emp_wk m
   WHERE e.mgr=m.empno(+)

-----------子查询----------------
   --子查询就是一条查询语句它是嵌套在其他SQL语句中的目的是为实际的SQL语句提供数据
   --子查询可以应用在DQL DDL DML中
   --查看比CLARK工资高的员工信息
   SELECT ENAME,SAL FROM EMP_WK
   WHERE SAL>(SELECT sal FROM EMP_WK WHERE ENAME='CLARKY')
--创建一张表 myemployee 含有字段EMP DEPT (DDL)
   CREATE TABLE myemployee_wk AS
   select E.EMPNO,E.ENAME,E.SAL,
   E.JOB,E.DEPTNO,D.DNAME,D.LOC
   FROM EMP_WK E JOIN DEPT_WK D
   ON E.DEPTNO=D.DEPTNO
--子查询根据查询的结果不同分为：
   --单行单列查询：常用作过滤条件
   --多行单列查询：常用作过滤条件
   --多行多列查询：常当作表看待
--EXISTS关键字
--该关键字后面根一个子查询用于在WHERE中作为过滤条件使用使用来判断其后的子查询是否可以查询出数据若可以则EXISTS返回真否则返回假
   查看员工的部门
   SELECT D.DEPTNO,D.DNAME,D.LOC FROM DEPT_WK D
   WHERE NOT EXISTS(
   SELECT * FROM EMP_WK E
   WHERE E.DEPTNO =D.DEPTNO
   )
--分页查询
--分页查询是分断查询数据在查询的数据量非常大的时候尤其重要
--分页可以减少系统资源消耗响应速度快不同的数据库分页的语法不一样
--ORACLE 中使用ROWNUM解决分页
--ROWNUM是以个伪列不存在与任何一张表中但是每张表都可移查询该字段在查询某张表时只要可以查询出一条记录该字段的值就是这条记录的行号从1开始生成的过程在查询过程中进行
   SELECT ROWNUM，ENAME，SAL，DEPTNO FROM EMP_wk
--获取6-10
   select * from(select rownum rn,ename,sal,deptno from emp_wk)
   where rn between 6 and 10
--获取工资排行中的6-10名
   SELECT *
       FROM (SELECT ROWNUM RN ,T.*
           FROM (SELECT ENAME,SAL,DEPTNO
               FROM EMP_WK
                   ORDER BY SAL DESC) T
           WHERE ROWNUM<=10)
       WHERE RN>=6
--计算分页公式
   page 页号
   pagesizi 每页的条目数
   start：（page-1） * pageSize+1
   end ： pageSize *page

--DECODE DECODE (函数) 语法

(写法1)SELECT ENAME,JOB,SAL, DECODE(JOB,
'MANAGER',SAL*1.2,
'ANALYST',SAL*1.1,
'SALESMAN',SAL*1.05,
sal
) BONUS FROM EMP_WK

   (写法2)SELECT ENAME,JOB,SAL,CASE JOB WHEN 'MANAGER' THEN SAL*1.2
WHEN 'ANALYST' THEN SAL*1.1
WHEN 'SAKESNAB'THEN SAL*1.05
ELSE SAL END
BONUS
FROM EMP_WK
--排序函数
   --排序函数允许按照指定字段分段在按照指定字段排序然后生成组内编号
   --ROW_NUMBER 生成组內连续且唯一的数字
   --查看每个部门的工资排行
   --ROW_NUMBER 生成组內连续且唯一的数字 1 2 3 4
       SELECT -------------
       ENAME,SAL,DEPTNO,ROW_NUMBER() OVER(PARTITION BY DEPTNO
       ORDER BY SAL DESC) RANK FROM EMP_WK
   --RANK 生成组内不连续也不唯一的数字 1 22 4
       SELECT ------
       ENAME,SAL,DEPTNO,RANK() OVER(PARTITION BY DEPTNO
       ORDER BY SAL DESC) RANK FROM EMP_WK
   --DENSE_RANK 生成组内连续但不唯一的数字 1 22 3
       SELECT
       ENAME,SAL,DEPTNO,DENSE_RANK() OVER(PARTITION BY DEPTNO
       ORDER BY SAL DESC) RANK FROM EMP_WK

--UNION UNION ALL INTERSECT
   --UNION 自动去掉合并后的重复记录（去除重复）
   --UNION ALL 返回俩个结果集中的所有行包括重复的行（显示全部的）
   --INTERSECT （叉集）俩个都有的才会显示
       SELECT ENAME,JOB,SAL FROM EMP_WK
       WHERE JOB='MANAGER'
       INTERSECT
       SELECT ENAME ,JOB ,SAL FROM EMP_WK
       WHERE SAL>2500 --显示JOB=‘MANAGER’ 且 SAL>2500
--MINUS 列出职位是MANAGER 但薪水低于2500的员工信息
       SELECT ENAME,JOB,SAL FROM EMP_WK
       WHERE JOB ='MANAGER'
       MINUS
       SELECT ENAME ,JOB ,SAL FROM EMP_WK
       WHERE SAL>=2500
--查看每天的营业额
   SELECT YEAR_ID ,MONTH_ID ,DAY_ID, SUM(SALES_VALUE) FROM SALES_WK
   GROUP BY YEAR_ID,MONTH_ID ,DAY_ID
   ORDER BY YEAR_ID,MONTH_ID ,DAY_ID
-- ROLLUP GROUP（A，B，C）：A A,B A,B,C 如上的总和
   --查看每年每月每日的营业额
       SELECT YEAR_ID,MONTH_ID , DAY_ID,SUM(SALES_VALUE) FROM SALES_WK
       GROUP BY
       ROLLUP(YEAR_ID,MONTH_ID,DAY_ID)

--CUBE (A,B,C) 全方面的统计（没有意义）
--CUBE 分组方式是全方位的分组方式是2的参数个数次方
       SELECT YEAR_ID,MONTH_ID , DAY_ID,SUM(SALES_VALUE) FROM SALES_WK
       GROUP BY CUBE(YEAR_ID ,MONTH_ID,DAY_ID)
       ORDER BY YEAR_ID ,MONTH_ID,DAY_ID
--GROUPING SETS 允许指定不同的分组方式然后将这些分组统计的结果并在一个结果集中
        --查看每月与每年营业额

1-----------------------------------------------------------------------------------1
1--伪表:dual 1
1--当查询的内容与任何一张表无关时为了满足FROM字句要求可以使用伪表 1
1--伪表只会查询出一条记录 1
1--补位函数 LPAD RPAD 1
1--可以将指定内容显示指定位数不足时则补充若干个指定的单一字符以达到位数 1
1--需要注意：数据库中下标是从1开始的！                   1
1--需要注意：数据库中下标是从1开始的！                   1
1--需要注意：数据库中下标是从1开始的！                   1
1--日期格式字符串中出现的字符凡不是英文与符号其他字符全部要使用双引号括起来 1
1--NULL的运算： NULL与字符串链接等于什么也没干 NULL与数字运算结果还是NULL 1
1--WHERE过滤时机：WHERE是在查询表中每一条数据时进行过滤的       1
1 --智慧将满足WHERE条件的记录查询出来 1
1
1
1
1
1
1
1
1------------------------------------------------------------------------------------1

-----------------------------DCL--------------------------------------------------------
-DCL
用于执行权限的授权和收回操作
   -GRANT 授权，用于给用户或角色授权权限
   -REVOKE 用于收回用户或角色已有的权限
   -CREATE USER 创建用户

----------------------------------------------------------------------------------视图、序列、索引、约束---------------------------------------------------------------------------------------------------------------------------

----------------------视图------------------
--视图是数据库对象之一
--视图在SQL中对象有：表视图索引序列并非真实的表他不包含任何数据他只对应一个查询语句的结果集
--所以一个查询一张视图时会先进行该视图对应的查询语句得到结果集然后再跟根据这个结果集进行实际的查询工作
   CREATE VIEW V_EMP_WK
   AS
   SELECT EMPNO,ENAME,SAL,DEPTNO FROM EMP_WK
   WHERE DEPTNO =10; --创建视图一般命名为V开头

   DESC V_EMP_WK; --查询视图结构
   SELECT * FROM V_EMP_WK; --查询视图内容
--创建视图为字段起别名
   CREATE OR REPLACE VIEW V_EMP_WK --替换视图（创建的时候也可以这样写）
   AS
   SELECT EMPNO ID，ENAME NAME，SAL SALART，DEPTNO FROM EMp_WK
   WHERE DEPTNO =10;
--对视图进行DML操作
   --仅能对简单视图进行DML操作
   --复杂视图不允许进行DML操作
   --简单视图：视图数据来自单一的一张表且不含有函数或表达式
   --复杂视图：查询语句含有函数表达式分组等
   --连接视图：视图数据来自多张表算作复杂视图的一种
--对简单视图进行DML虽然可以但是也要注意不能违反基表的约束条件
--基表：视图数据来源的表对视图进行DML操作就是对基表进行DML操作
   INSERT INTO V_EMP_WK(ID,NAME,SALART,DEPTNO)
   VALUES(1001,'JACK',5000,10);
   SELECT *FROM V_EMP_WK;
   SELECT *FROM EMP_WK;


--对视图进行某些DML操作可能导致视图自身对该记录不可见但是能应影响基表记录这种操作是对基表的"污染"
   INSERT INTO v_emp_wk(ID,NAME,SALART,deptno)
   VALUES (1012,'ROSE',5000,20);

   UPDATE v_emp_wk SET DEPTNO=20 ;
--DELETE不会出现该问题由于删除不掉视图数据所以也不会对基表数据删除
   DELETE FROM V_EMP_WK
   WHERE DEPTNO=20;

--对视图添加检查选项来避免对基表污染添加了检查选项的视图要求对视图进行DML操作后视图必须对其可控 INSERT后视图必须可见 UPDATE后视图也必须对其可见 DEKETE没有该问题
   --用WITH CHECK OPTION 来设置检查选项
   CREATE OR REPLACE VIEW V_EMP_WK
   AS
   SELECT EMPNO ID,ENAME NAME,SAL SALARY,DEPTNO FROM EMP_WK
   WHERE DEPTNO=10
   WITH CHECK OPTION;

--对视图添加只读选项添加后该视图不能进行DML操作（增删改）
   --用WITH READ ONLY 设置只读选项
   CREATE OR REPLACE VIEW V_EMP_WK
   AS
   SELECT EMPNO ID,ENAME NAME,SAL SALARY,DAPTNO FROM EMP_WK
   WHERE DEPTNO=10
   WITH READ ONLY

--数据字典
   --数据字典也是一系列的表这些表中的数据有数据库自身维护使用来记录用户曾经创建过的数据库对象的清单当需要了解时可以查看这些数据字典
   --user_objects:记录了所有数据库的对象
   --查看所有创建过的视图
       SELECT object_name FROM user_objects
       WHERE object_type='TABLE'
       AND OBJECT_NAME LIKE '%WK';

--记录用户创建的所有表的数据字典
SELECT * FROM user_objects;
SELECT table_name FROM user_tables;

--记录用户创建的所有视图的数据字典
SELECT VIEW_NAME,TEXT FROM USER_VIEWS;

--创建复杂视图
--创建一个部门工资情况的视图
   CREATE VIEW V_SALINFO_WK
   AS
   SELECT AVG(E.SAL) AVG_SAL,SUM(E.SAL) SUM_SAL, MAX(E.SAL) MAX_SAL,MIN(E.SAL) MIN_SAL,E.DEPTNO,D.DNAME FROM EMP_WK E ,DEPT_WK D
   WHERE E.DEPTNO=D.DEPTNO
   GROUP BY E.DEPTNO, D.DNAME;

--看看比自己所在部门平均工资高？
SELECT E.ENAME,E.SAL,E.DEPTNO FROM EMP_WK E,V_SALINFO_WK V
WHERE E.DEPTNO =V.DEPTNO
AND E.SAL>V.AVG_SAL;

--删除视图
   --删除视图并不会对基表有任何影响
       DROP VIEW V_EMP_WK
--序列
   --另一个数据库对象用来生成一系列数字的通常序列生成的数字是为某张表的主键字段(id)提供值的
   CREATE SEQUENCE SEQ_EMP_WK
       START WITH 1000 ----1000开始
       INCREMENT BY 10;   ----步长10

--序列支持俩个伪列
   --NEXTVAL:
       --使序列生成下一个数子新创建的序列第一次生成是START WITH 指定的数字以后就是上次生成的数字加上步长得到的
       --序列不能后退所以生成新书子后就不能得到以前的数字了
           SELECT SEQ_EMP_WK.NEXTVAL FROM DUAL;
   --CURRVAK:
       --返回序列最后生成的数字
       --新创建的序列至少调用一次NEXTVAL以后猜可以使用CURRVAL
           SELECT SEQ_EMP_WK.CURRVAL FROM DUAL;
--删除序列
   DROP SEQUENCE SEQ_EMP_WK
--索引
   --创建索引
       CREATE INDEX idx_emp_wk ON emp(ename);
   --多行索引
       CREATE INDEX IDX_EMP_JOB_SAL_WK ON EMP_WK(JOB ,SAL);
       SELECT EMPNO,ENAME,SAL,JOB FROM EMP_WK
       ORDER BY JOB,SAL;
   --创建基于函数的索引
       CREATE INDEX EMP_ENAME_UPPER_IDX_WK ON EMP_WK(UPPER(ENAME));
       SELECT * FROM EMP_WK
       WHERE UPPER(ENAME)='KING';
--重建索引
   ALTER INDEX EMP_ENAME_UPPER_IDX_WK REBUILD ;
--删除索引
   DROP INDEX idx_emp_wk;
--约束
   --添加非空约束 NOT NULL
   --非空约束也是列级约束只能在修改表中某个字段的同时修改该约束
       CREATE TABLE employees_wk(
       eid NUMBER(6),
       NAME VARCHAR2(30) NOT NULL,
       SALARY NUMBER(7,2),
       HIREDATE DATE
       CONSTRAINT EMPLOYEES_WK_HIREDATE_NN NOT NULL
       );
--修改表时添加非空约束
   ALTER TABLE EMPLOYEES_WK
   MODIFY (EID NUMBER(6) NOT NULL);
--取消非空约束
   ALTER TABLE EMPLOYEES_WK
   MODIFY (EID NUMBER(6) NULL);
--唯一性约束
   CREATE TABLE EMPLOYEES1_WK(EID NUMBER(6) UNIQUE, NAME VARCHAR2(30),EMAIL VARCHAR2(50),SALARY NUMBER (7,2),HIREDATE DATE ,
   CONSTRAINT EMPLOYEES_WK_EMAIL_UK UNIQUE(EMAIL));
--添加唯一性约束
   ALTER TABLE EMPLOYEES1_WK
   ADD CONSTRAINT EMPLOYEES_WK_NAME_UK UNIQUE(NAME);

--主键约束
--每张表通常都要有主键（id）该字段的值在整张表中是非空且唯一的这样才能用主键的唯一确定表中的一条数据
   --主键只能有一个一张表中
   CREATE TABLE EMPLOYEES2_WK(
   EID NUMBER(6) PRIMARY KEY,--主键
   NAME VARCHAR2(30),
   EMAIL VARCHAR2(50),
   SALARY NUMBER(7,2),
   HIREDATE DATE
   ) ;

--检查约束
--添加检查约束
   ALTER TABLE EMPLOYEES2_WK
       ADD CONSTRAINT EMPLOYEES2_WK_SALARY_CHECK
       CHECK(SALARY>2000);
--修改检查约束
       INSERT INTO EMPLOYEES2_WK(EID,NAME,SALARY)
       VALUES (1236,'DONNA NOBLE',2500);
--修改检查约束失败
       UPDATE EMPLOYEES2_WK SET SALARY =1500
       WHERE EID=1236;

oracle 基础学习