SQL\Oracle\MySql编程学习入门到高手

SQL学习

学习环境：oracle11g MySQL

学习说明：本文章为实用进阶学习；

 基础语法见SQL语法及函数大全 http://www.w3school.com.cn/sql/sql_default.asp

环境准备

安装环境Ubuntu 12.04+oracle11g  
参考文章：http://blog.csdn.net/idber/article/details/9039857（本人参考安装成功）

附参考文档安装未成功的网址，便后续继续尝试

Ubuntu 14.04 LTS 64位安装Oracle 11g  

ubuntu16.04安装oracle11g  

 什么版本的系统上安装的没看明白 

二、oracle常用命令

linux 操作oracle常用命令

打开图形化窗口： 

1 ） Database ConfigurationAssistant windows    ( 添加数据库实例 ) 
$ dbca 
2 ） Oracle Net ConfigurationAssistant windows  ( 配置监听 ) 
$ netca 
3 ）打开 EM                          
$ oemappdbastudio                            （打开企业管理器图形界面）  
$ opemappconsole                               （打开企业管理器图形界面 ( 和上面的命令效果一样 ) ）  

常用命令： 

$ lsnrctl start ｜ stop ｜ status                  （启动｜停止｜活动状态 监听）  
$ isqlplusctl start ｜ stop                      （启动｜停止 isqlplus 可以在浏览器登录 5560 端口）                             
$ sqlplus/nolog                              （以不连接数据库的方式启动 sqlplus ）         
$ sqlplus system/manager @file.sql           （执行 sql 脚本文件）                    
$ sqlplussystem/manager                      （使用 system 用户登录 sqlplus ）  
$ imp system/manager file=/tmp/expfile.dmp log=/tmp/implogfile.log ignore=yfromuser=expuser touser=impuser  （用户模式表数据导入，如果没有特别指定值，就使用默认的值）                           
$ exp username/password file=/tmp/expfile.dmplog=/tmp/proV114_exp.log                        （用户模式表数据导出，这是最简单的导出方法）  
SQL> conn / as sysdba                                  （以 sysdba 用户连接）  
SQL> startup 
SQL> shutdown 
SQL> shutdown immediate                               
SQL> startup 
SQL> shutdown 

SQL> shutdown immediate                               （立即关闭实例）                                

SQL> descdba_users;                                  （查询dba_users表结构）              

SQL> select username fromdba_users;                  （查询当前sid下的所有用户的username）          

SQL> select count(*) from username.tablename;         （查询tablename表的行数）                   

SQL> drop user usernamecascade;                      （删除名称为username的oracle用户） 

SQL> select distinct table_name from user_tab_columns;（查看当前user模式下所有表名）

linux 操作oracle创建表

    1、登录linux，以oracle用户登录；

    2、以sysdba方式来打开sqlplus，命令如下： sqlplus "/as sysdba"

    3、查看我们常规将用户表空间放置位置：执行如下sql：

         select name from v$datafile; //查询用户表空间文件位置

   4、创建用户表空间：

           CREATE TABLESPACE dc DATAFILE

           '/oracle/oradata/test/dc.dbf' SIZE 200M AUTOEXTEND ON EXTENT

           MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO;

  5、创建用户，指定密码和上边创建的用户表空间

       CREATE USER name  IDENTIFIED BY pwd DEFAULT TABLESPACE  dc;

  6、赋予权限

         grant connect,resource to name;

        grant unlimited tablespace to name;

         grant create database link to name;

        grant select any sequence,create materialized view to name;

经过以上操作，我们就可以使用name/pwd 登录指定的实例，创建我们自己的表了

三、SQL *Plus

1、SQL和SQL *Plus区别

SQL是一种语言，ANSI标准SQL99，关键字不能缩写，使用语句控制数据库中的表的定义信息和表中的数据；

SQL *Plus是一种环境，oracle的特性之一，关键字可以缩写，命令不能改变数据库中的数据的值，集中运行；

2、SQL *Plus常用命令

desc [form name]; 查看表结构
set linesize  [length]; 设置行宽
set pagesize  [num];设置页大小
col [col name] for 999;设置对应列宽（数字）
col [col name] for a20;设置对应列宽（字符）
host cls 清屏
ed或edit可将上条sql语言用文本编辑器打开编辑；
    / 可以执行文本编辑器SQL语言
设置linux下的默认编辑器： export EDITOR=vi
--修改列名
alter table 表名 rename column 旧列名 to 新列名;
--修改列类型
alter table 表名 modify (列名 DATE);

四、SELECT

        SELECT * FROM tab;  oracle中查看用户下有什么表 tab是数据字典。
1、SELECT取别名可以省掉""和as，但是别名有空格时不能省掉""。

2、空值问题

        null和任何数运算都为null  （null无穷大）
空值不是空，null ！= null
处理方法：虑空函数 nvl(a,b)--当a是空时返回b（b可以是数字或字符串）
3、字符串和日期在sql语言中用单引号括起来；
4、查询方式
   1）查询所有 SELECT * FROM [tabname];
    2)条件查询
  SELECT * FROM [tabname] where [name] is null; //查询name为空的所有行，不为空则为not null，禁止和null比较
5、||连接符和伪表dual（伪表oracle中提供的）
6、DISTINCT 去除重复行  
SELECT DISTINCT [name] FROM [tabname];
修饰多个字段时，要求name与name1都一样时，认为是重复的，否则不同行。
SELECT DISTINCT [name],[name1] FROM [tabname];

2017-11-04

五、where 和order by    

    1、where

         AND OR BETWEEN...AND... IN  LIKE < > <= >=

查看oracle命令：SELECT * FROM  v$nls_parameters;
字符和日期；字符和日期要包含在单引号中;日期格式敏感；
oracle支持隐式类型转换；
SELECT * FROM col BETWEEN [a] AND [b]; 筛选出来 a<=x<=b
in(……) 在某集合中；集合中有null，对in()查询结果无影响，对not int()查询结果有影响；
SELECT * FROM col WHERE name LIKE ‘s%’；查询name中首字母为s的；
使用时为回避特殊字符使用转义字符‘\’，然后加上ESCAPE ‘\’；
SELECT * FROME col WHERE name LIKE '%\_%' ESCAPE '\';

  2、order by

         order by默认按照升序排列， order by 后+ 列名 表达式 别名 序号都可；

order by 遇上空值时  ……order by desc nulls last;
order by 后跟多个列，只作用于最近的那一列；若有desc修饰，只作用于最近一列；

2017-11-05

六、SQL函数

    1、单行函数

         操作数据对象，接受 参数 返 回一个结果，只对一行进行变换，每行返回一个结果，可以转换数据类型，可嵌套，参数可以是一列或一个值；

 1)字符函数
select LOWER('HELLO') 转小写,UPPER('hello') 转大写, INITCAP('hello wOrld') 首字母大写 from dual;
select CONCAT(CONCAT('Hello','Word'),'CC') 连接字符串 from dual；
select SUBSTR('hello',3) 从第3位取字符串,SUBSTR('hello',3,1) 从第3位取1个字符 from dual;
select LENGTH('中国abc') 字符数,LENGTHB('中国abc') 字节数 from dual;
select INSTR('abcde','cde') 求字串位置 from dual;
select LPAD('abcde',10,'*') 左连接,RPAD('abcde',10,'*') 右连接 from dual;
select TRIM('a' from 'abcde') 去掉指定字符 from dual;
select REPLACE('abcd','b','T') 替换指定字符 from dual;
2)数字函数
selec ROUND(45.926,2) 四舍五入,TRUNC(45.926,2) 截断,MOD(1600,300) 求余 from dual;
round();0保留个位，-1保留10位，-2保留百位（要看十位四舍五入）
3)日期函数
mysql关于日期有3中类型
select SYSDATE 默认时间 from dual;
select TO_CHAR(SYSDATE,'yy-mm-dd hh24:mi:ss') from dual;
select sysdate-1 昨天，sysdate 今天，sysdate+1 明天 from dual;
select (SYSDATE-hiredate)/7 周,(SYSDATE-hiredate)/30 月，(SYSDATE-hiredate)/365 年 from emp;
精确计算(函数计算的更精确）
select (SYSDATE-hiredate)/30 估计月,MONTHS_BETWEEN(SYSDATE,hiredate) 函数计算月 from emp;
select SYSDATE 系统日期,ADD_MONTHS(SYSDATE,6) 增加月份后日期 from dual;
select SYSDATE 系统日期,NEXT_DAY(SYSDATE,'FRIDAY') 指定日期的下一日期 from dual;
select SYSDATE 系统日期,LAST_DAY(SYSDATE) 本月最后一天 from dual ;  

4)转换函数
数据类型转换：隐式、显式
TO_CHAR();9--数字 0--零 $--美元 L--本地货币符号 .--小数点 ,——千位符
TO_DATE(),TO_NUMBER();
*列：查询薪水：两位小数，本地货币代码 千位符
 SELECT TO_CHAR(1250,'L9,999.99') FROM dual;
 SELECT TO_NUMBER('￥1,250.00','L9,999.99') FROM dual;

5)通用函数
NVL(a,b) 当a为null返回b
NVL2(a,b,c) 当a为null返回c，否则返回b
NULLIF(a,b) 当a=b,返回null，否则返回a
COALESCC(a,b,c...)从左到右返回第一个不为空的
条件表达式
*列：给员工涨工资：总裁涨1000，经理：800，其他500
SQL99语法
SELECT ename,job,sal 涨前工资,sal(
CASE job WHEN 'president' THEN sal+1000
WHEN 'manager' THEN sal+800
ELSE sal+500 END) 涨后工资 from emp;
ORCAL语法 函数

 SELECT ename,job,sal 涨前工资,DECODE(job,'president',sal+1000,'manager',sal+800,sal+500 END) 涨后工资 from emp;

2、多行函数

1)分组函数
AVG COUNT MAX MIN SUM
SELECT AVG(sal),COUNT(sal),SUM(sal),MAX(sal),MIN(sal),SUM(sal) * from emp;
组函数和NULL在一起：组函数会自动滤空；修正滤空用NVL(a,0);
2)分组数据 group by
求各部门平均工资
SELECT deptno,AVG(sal) FROM emp GROUP BY deptno;
****注意：凡是select检索的列必须位于group by后面的集合列中，组函数除外；
 组函数设计本意：必须要在分组数据之上进行结果集的检索；
   求各部门每个工种的平均工资
SELECT deptno,job,AVG(sal) FROM emp GROUP BY deptno,job ORDER BY 1;
3)分组过滤 having
求平均工资大于2000的部门
SLECT deptno,AVG(sal) FROM emp GROUP BY deptno HAVING AVG(sal) > 2000;

七、多表查询

1、笛卡尔积基本原理
两个表笛卡尔积运算：行数相乘，列数相加
select count(e.empno) from emp e,dept d;
多表连接的关键在于等值连接；

2、等值连接

eg:查询员工信息，员工号，姓名，月薪，部门名称
select e.empno,e.ename,e.sal,d.dname from emp e,dept d where e.deptno=d.deptno;

3、不等值连接

eg:查询员工信息，员工号，姓名，月薪，薪水级别
select  e.empno,e.ename,e.sal,s.grade from emp e,salgrade s where e.sal>=s.losal and e.sal<=s.hisal;

4、外连接(+)  ****oracle专用****

eg：按部门统计员工人数：部门号，部门名称，部门人数
select d.deptno,d.deptname,count(e.deptno) from deptno d,emp e where d.deptno=e.deptno(+) group by d.deptno,d.deptname;
注意：外连接的重点防止等值连接不成立时漏掉部分统计数据
 将某一表中含有的所有列显示出来，则在另一表后面(+);

5、自连接    ****oracle专用****

eg：查询员工信息，老板信息，显示***的老板是***
select e.ename||'的老板是'||NVL(b.ename,'他自己') from emp e,emp b where b.mgr=e.empno(+);

2017-11-08

八、子查询

1、合理的书写风格；
2、子查询括号不能忘记；
3、子查询和主查询可以是同一张表，也可以不是，只要子查询返回的结果主查询可用即可……
4、在什么地方放置子查询；
  子查询放在比较条件的右侧；
  select a,b,c ----ok 只能放单行子查询，不能是多行
  from tab1     ----可以放  重点（用来组建指定集合）
  where  col in(em1,em2) ----可以放
 col>222
group by ... ----不可以
having ... ----可以
order by ... ----不可以
5、子查询的分类
单行操作符对应单行子查询，多行操作符对应多行子查询。
按照子查询返回的条目数,分为: 单行子查询和多行子查询
单行子查询只能使用单行比较操作符( = > >= < <= <>)
eg：查询和141号工种一样且比145号员工薪水高的员工信息
   查询显示每部门编号和部门最小工资，并且这个部门最低工资大于50号部门的最小工资
多行子查询只能使用多行比较操作符(in any（和集合中任意值比较） all（和集合中所有值比较）)
eg：查询部门名称（表dept）是（不是）sales和accounting的员工信息（表emp）
   查询薪水比30号部门所有（所有改为任意any，表示大于最小值）员工高的员工信息
       SELECT * FROM emp WHERE sal > ALL(SELECT sal FROM emp WHERE deptno = 30);
   查询不是经理的员工信息
SELECT * FROM emp WHERE empno not in (SELECT mgr FROM emp WHERE mgr IS NOT NULL);--注意空值处理
 
6、子查询遇见NULL
    eg：查询部门名称是sales的员工信息 2种方法
          SELECT * FROM emp WHERE deptno = (SELECT deptno FROM dept where dept dname = 'SALES');

          SELECT e.* FROM emp e,dept d WHERE e.deptno = d.deptno and d.dname = 'SALES';

2017-12-17

 子查询按执行的顺序分为：

一般子查询
相关子查询
一般情况下，子查询返回的是一个集合，子查询不排序，TOP-N问题除外
7、orcal分页和TOP-N
分页思想：内层排序外层选
  1、使用rownum--序列列，按照oracle默认机制生成，其不会因排序而变化
  2、rownum只能使用<=、<，不能使用>=、>
  rownum表示返回结果集的行号；
  eg：查找员工表中工资最高的前三名  top-n问题
  
  select rownum,empno,ename,sal from (select ename，sal from emp order by sal DESC) where rownum <=3 ;
  eg：工资从高到低求出工资第5到第8名
   select r,empno,ename,sal from（select rownum r,empno,ename,sal from (select ename，sal from emp order by sal DESC) where rownum <=8）where r <= 4;
内层排序，中层用rownum选择前n条，并给rownum取别名供外层过滤使用，外层去掉前m条结果；
8、相关子查询例题
    eg：查询员工表中薪水大于本部门平均薪水的员工
员工表 本部门平均薪水 
方法1：select e.empno，e.ename，e.sal，d.avgsal from emp e，（select deptno，avg（sal） avgsal from emp group by deptno） d where e.deptno = d.deptno and e.sal > d.avgsal；
考察点：分组，多表查询，等值连接条件，别名，分组函数
方法2：相关子查询：主查询的参数让子查询使用，一般通过别名技术
select e.empno,e.ename,e.sal,(select avg(sal) froms emp where deptno = e.deptno) avgsal from emp e where sal > (select avg(sal) froms emp where deptno = e.deptno);

九、集合运算

     1、种类：并集 union/ UNION all
         交集 INTERSECT 
         差集 MINUS
2、 集合运算注意问题：
   参与运算各个集合必须列数相同且类型一致；
采用第一个集合的表头作为最后的表头
必须在每一个集合后使用相同的order by
使用括号

十、DML语言（增删改查）

    其余两种SQL语言类型有DDL 定义语言（数据库对象创建）DCL 控制语言

一）INSERT

1、INSERT（掌握插入一条完成记录，插入部分记录，插入空值）
一次插入一条：
  INSERT INTO table（col1，col2，……） values （value1，value2，……）
2、创建脚本 取地址符&   可以不用写value的具体值
   insert into emp(empno,ename,deptno) values(&empno,&ename,20);
3、批量插入数据
eg：把10号部门的员工拷贝到另外一个表
creat table emp10 as select * from emp where 1=2;创建表并将表结构复制过去
insert into emp10 select * from emp where deptno=10;

二）UPDATE

eg：UPDATE TABLE emp10 SET deptno = 20 where empno=7782；

三）delete

DELETE  [FROM] table [WHERE condition]；
eg：delete emp10 where empno=7782；

delete和truncate表区别
  1、delete逐条删除，会产生碎片；truncate先摧毁表在重建，不会产生碎片
  2、delete是DML语言，truncate是DDL
     DML可以闪回，DDL不可以闪回
 （要开启闪回必须要开启行移动功能）
  3、delete不会释放表空间，truncate会
  4、delete可以回滚，truncate不可以；
  5、oracle delete快，mysql truncate快

四）事务

1、概念：一个或多个DML语言的组合
  特点：要么都成功要么都失败
  特性：原子性、一致性、隔离性、持久性
  事物的隔离性：多个客户端同时操作数据库时，要隔离他们的操作
  ----orcal默认情况下事务是打开的----
2、oracle事务的生命周期
  事务的开始：DML语言（oracle默认打开事务）
  事物的结束：
           显示提交 commit
隐式提交：执行DDL语言、正常退出exit 
回滚：显示rollback 隐式（掉电、宕机、非正常退出）
3、保存点
设置保存点：SAVEPOINT a;设置保存点A
回滚到保存点：ROLLBACK TO SAVEPOINT a；
4、事务的隔离级别
  sql99:有四种，读未提交数据、读已提交数据、可重复读、串行化；
  oracle默认支持读已提交数据、串行化；oracle独有的read-only
  Mysql支持四种

十一、DDL语言

目的：管理数据库的对象（表，视图、索引、序列、同义词、约束）
1、CREATE创建表必须要有权限和表空间
  数据类型：VARCHAR2(4096),CHAR(size），NUMBER(p,s),DATE,LONG,BLOB(二进制最大4G，存图片）
           CLOB字符数据最大4G；BFILE,ROWID（行地址-索引）
ORACLE支持可见字符和不可见字符（blob，clob）
创建并复制表：create table dsttab as select * from srctab；
2、表的查看
   查看别的用户下的表 
select * from 用户名.表名；
3、增删改查
增加列：alter tab 表名 add （列名，类型）；
修改列：alter tab 表名 modify （列名，类型）；
删除列：alter tab 表名 drop column （列名）；
删除表：drop tab 表名；
4、oracle的回收站
查看回收站：show recyclebin
清空回收站：purge recyclebin
彻底删除表：drop table 表名 purge；
还原表：闪回的内同
注意：管理员没有回收站（不是所有用户都有回收站）
通过回收站的名字，查看原来表的内容，表名加双引号；

十二、oracle角色权限

    1、约束

create table student
(
sid number constraint stduent_sid_pk primary key,
sname varchar2(40) constraint student_sname_nonull not null,
email varchar2(40) constraint student_email_unique unique
  constraint student_email_notnul not null,
age number constraint student_age_mincheck check(age>20),
deptno number constraint stduent_deptno_fk  references dept(deptno)
);
primary key 主键约束： 通过这一列 唯一的确定一行值；若定义主键约束 隐含 非空且唯一
foreign key 外键约束：定义 两张表, 一个表的列值 引用了另外一张表的列值 员工表 部门表
子表的外键关联的是父表的主键；父表中的数据被子表引用, 则父表相应记录删不掉
    级联删除和级联置空——————一般开发都不使用
    外键约束
级联删除：ON DELETE CASCADE:当删除父表时，级联删除子表记录
级联置空：ON DELETE SET NULL：将子表的相关的依赖记录的外键置为NULL

2、视图  

本质：是视图是表的逻辑抽象。。。。。。他的数据来源于别的表
可以将视图理解为存储起来的 SELECT 语句
视图向用户提供基表数据的另一种表现形式

创建视图需要有创建视图的权限，需要超级管理员给scott分配权限
用户管理中 权限管理 需要管理员登录进行授权
C:\>sqlplus /as sysdba
SQL> grant create view to scott; 创建视图的权限给scott用户
视图with check option
create view view1
as
select * from emp where deptno=10
with check option;
insert into view1 values(***,***,...., 10);
insert into view1 values(***,***,...., 20);
--通过视图只能看到10号部门的员工信息,不能插入20号部门员工
 1* insert into view1 values(1, 'sss', 'clerk', 7839, sysdate, 8000, null, 20)
 2  /
insert into view1 values(1, 'sss', 'clerk', 7839, sysdate, 8000, null, 20)
           *
第 1 行出现错误:
ORA-01402: 视图 WITH CHECK OPTIDN where 子句违规
修改视图 视图只能替换,不能修改
CREATE or replace view empincomeview2 as
select e.empno, e.ename, e.sal, e.sal*12 annalsal, sal*12+nvl(comm, 0) income, d.dname
from emp e, dept d where e.deptno = d.deptno
with read only;
角色权限：开发人员只需要图形界面找出来即可

3、序列 

序列作用： 一般为多个表提供主键的序号
序列放在内存中，加快速度
[1 2 3 4 5 6 ... 20] [21 22 3 4 5 6 ... 40]
序列的访问 两个属性
[ 1  2  3  4  5  6 ...  20 ]
   ▲  
  NEXTVAL 和 CURRVAL 伪列  
  =====》
  create sequence myseq22
  select myseq11.NEXTVAL from dual;
  select myseq11.CURRVAL from dual;
序列的语法细节
多个表共用一个序列,造成序列不连续
回滚会造成,造成序列不连续
系统异常,内存序列丢失,造成序列不连续 

4、索引：存储每行的首地址

数据库系统提供的机制，加快检索的访问速度；
索引和表存在不同的地方，删除索引不影响表
数据库自动支持索引的存储机制和管理员及开发人员没关系
300万的数据和不加索引检索速度差10倍；
索引的原因：将相同数据的指针放在一起，加快查询速度；
索引检索是有算法的，默认是B树算法，也有位图最短路径
创建主键和唯一性约束时，系统会自动的创建索引；
1）索引的编程实践
创建索引：CREATE INDEX index ON table（col1，col2，……）
2)什么时候不创建索引：表很小，列 不经常作为连接条件或出现在where子句中，查询的数据大于2%到4%；表经常更新；
3）删除索引：DROP INDEX index；

5、同义词（给表取别名）

CREATE SYNONYM 别名 FOR 对象表；

十三、小结练习:

-- 01 创建表空间
-- 注意表空间的路径 根据实际安装环境进行调整
CREATE TABLESPACE ts_myscott 
    LOGGING 
    DATAFILE '/home/oracle_11/app/oradata/orcl/ts_myscott.dbf' SIZE 10M 
    EXTENT MANAGEMENT LOCAL;
    
CREATE TABLESPACE ts_myscott2 
    LOGGING 
    DATAFILE '/home/oracle_11/app/oradata/orcl/ts_myscott2.dbf' SIZE 20M
    EXTENT MANAGEMENT LOCAL;
    
ALTER DATABASE DATAFILE '/home/oracle_11/app/oradata/orcl/ts_myscott.dbf' AUTOEXTEND ON NEXT  10M MAXSIZE UNLIMITED;
ALTER DATABASE DATAFILE '/home/oracle_11/app/oradata/orcl/ts_myscott2.dbf' AUTOEXTEND ON NEXT  20M MAXSIZE UNLIMITED; 


commit;


-- 02 创建方案 (创建用户)
CREATE USER MYSCOTT  PROFILE DEFAULT 
    IDENTIFIED BY MYSCOTT DEFAULT TABLESPACE USERS 
    ACCOUNT UNLOCK;


-- 资源和登录权限
GRANT RESOURCE TO MYSCOTT;
GRANT create session TO MYSCOTT;




-- 03 创建表
-- 创建部门表 并赋值
CREATE TABLE MYSCOTT.DEPT(
DEPTNO     NUMBER(2) PRIMARY KEY,
  DNAME       VARCHAR2(14) NOT NULL,
  LOC         VARCHAR2(13)
)TABLESPACE ts_myscott;


INSERT INTO MYSCOTT.dept VALUES(10, 'ACCOUNTING', 'NEW YORK');
INSERT INTO MYSCOTT.dept VALUES(20, 'RESEARCH', 'DALLAS');
INSERT INTO MYSCOTT.dept VALUES(30, 'SALES', 'CHICAGO');
INSERT INTO MYSCOTT.dept VALUES(40, 'OPERATIONS', 'BOSTON');


commit;


-- 创建员工表 并赋值
CREATE TABLE MYSCOTT.EMP(
EMPNO           NUMBER(4) constraint emp_empno_pk PRIMARY KEY,
ENAME           VARCHAR2(10) constraint emp_ename_notnull NOT NULL,
JOB             VARCHAR2(9),
MGR             NUMBER(4),
HIREDATE       DATE,
SAL             NUMBER(7,2) constraint emp_sal_check check (SAL>0),
COMM           NUMBER(7,2),
DEPTNO         NUMBER(2) constraint emp_deptno_fk references MYSCOTT.dept(deptno) 
)TABLESPACE ts_myscott;


--创建索引 在新的表空间上
CREATE  INDEX MYSCOTT.IX_CAtbAuditOperInfo_OT ON MYSCOTT.EMP(ENAME) TABLESPACE ts_myscott2;




INSERT INTO MYSCOTT.EMP VALUES(7369, 'SMITH', 'CLERK', 7902, '17-12月-80', 800, NULL, 20);
INSERT INTO MYSCOTT.EMP VALUES(7499, 'ALLEN', 'SALESMAN', 7698, '20-2月 -81', 1600, 300, 30);
INSERT INTO MYSCOTT.EMP VALUES(7521, 'WARD', 'SALESMAN', 7698, '22-2月 -81', 1250, 500, 30);


INSERT INTO MYSCOTT.EMP VALUES(7566, 'JONES', 'MANAGER', 7839, '02-4月 -81', 2975, NULL, 20);
INSERT INTO MYSCOTT.EMP VALUES(7654, 'MARTIN', 'SALESMAN', 7698, '28-9月 -81', 1250, 1400, 30);
INSERT INTO MYSCOTT.EMP VALUES(7698, 'BLAKE', 'MANAGER', 7839, '01-5月 -81', 2850, NULL, 30);


INSERT INTO MYSCOTT.EMP VALUES(7782, 'CLARK', 'MANAGER', 7839, '09-6月 -81', 2450, NULL, 10);
INSERT INTO MYSCOTT.EMP VALUES(7788, 'SCOTT', 'ANALYST', 7566, '19-4月 -87', 3000, NULL, 20);
INSERT INTO MYSCOTT.EMP VALUES(7839, 'KING', 'PRESIDENT', NULL, '17-11月-81', 5000, NULL, 10);


INSERT INTO MYSCOTT.EMP VALUES(7844, 'TURNER', 'SALESMAN', 7698, '08-9月 -81', 1500, 0, 30);
INSERT INTO MYSCOTT.EMP VALUES(7876, 'ADAMS', 'CLERK', 7788, '23-5月 -87', 1100, NULL, 20);
INSERT INTO MYSCOTT.EMP VALUES(7900, 'JAMES', 'CLERK', 7698, '03-12月-81', 950, NULL, 30);


INSERT INTO MYSCOTT.EMP VALUES(7902, 'FORD', 'ANALYST', 7566, '03-12月-81', 3000, NULL, 20);
INSERT INTO MYSCOTT.EMP VALUES(7934, 'MILLER', 'CLERK', 7782, '23-1月 -82', 1300, NULL, 10);


commit;


-- 创建工资级别表 并赋值
CREATE TABLE MYSCOTT.SALGRADE(
  GRADE         NUMBER,
  LOSAL           NUMBER,
  HISAL           NUMBER
)TABLESPACE ts_myscott;


INSERT INTO MYSCOTT.SALGRADE VALUES(1, 700, 1200);
INSERT INTO MYSCOTT.SALGRADE VALUES(2, 1201, 1400);
INSERT INTO MYSCOTT.SALGRADE VALUES(3, 1401, 2000);
INSERT INTO MYSCOTT.SALGRADE VALUES(4, 2001, 3000);
INSERT INTO MYSCOTT.SALGRADE VALUES(5, 3001, 9999);
commit;


--创建奖金表
CREATE TABLE MYSCOTT.BONUS(
ENAME      VARCHAR2(10),
  JOB       VARCHAR2(9),
  SAL       NUMBER,
  COMM      NUMBER
)TABLESPACE ts_myscott;


------停止-----
-- 04创建新用户方案 通过MYSCOTTUSER1来访问数据库, 权限配置演示  
CREATE USER "MYSCOTTUSER1"  PROFILE "DEFAULT" IDENTIFIED BY "123456" DEFAULT TABLESPACE "USERS" ACCOUNT UNLOCK;
GRANT "CONNECT" TO "MYSCOTTUSER1";
GRANT SELECT ANY TABLE TO "MYSCOTTUSER1";


GRANT DELETE ON MYSCOTT.DEPT TO "MYSCOTTUSER1";
GRANT INSERT ON MYSCOTT.DEPT TO "MYSCOTTUSER1";
GRANT UPDATE ON MYSCOTT.DEPT TO "MYSCOTTUSER1";


GRANT DELETE ON MYSCOTT.EMP TO "MYSCOTTUSER1";
GRANT INSERT ON MYSCOTT.EMP TO "MYSCOTTUSER1";
GRANT UPDATE ON MYSCOTT.EMP TO "MYSCOTTUSER1";


commit;


==============

  
  
数据库效率的优化
1)求10号部门的平均工资
select deptno,avg(sal) from emp group by deptno having deptno=10;----先分组在过滤
select deptno,avg(sal) from emp where deptno=10 group by deptno;-----先过滤在分组
having和where子句区别：having 先分组在过滤；where先过滤后分组效率较高
2)查表中所有元素
select * from emp;
select deptno,ename,..., from emp;比上一句效率高，编译器无需翻译
oracle解析逻辑表达式的方向：从右向左
3)  多表查询（一次搞定）效率高于子查询（一个子查询，两次检索）；oracle优化后效率差不多；