Oracle SQL语言基础

1.desc tablename---描述一张表（字段，类型，长度）
2.select empno,sal*12 "All money" from emp;
  ---给结果项起别名。如含特殊字符或者要保持原有格式，用双引号括起来。
3.任何含空值的数学表达式最后的结果都是空值（NULL）
4.字符串连接符：||
 select ename||sal from emp;
  单引号包起来字符串。如果字符串内含有单引号，用两个单引号来替代。
5.select distinct deptno , job from emp;
 --去掉结果中的重复值。
6.select * from emp where deptno = 10;
 --取特定的值的记录。过滤条件，过滤一些记录。
7.select ename,sal,deptno from emp where deptno<>10;
 --取值不等于的记录
8.select ename,sal from emp where sal between 800 and 1500;
 --取值在什么范围的记录（包含头尾）
 等同于select ename,sal from emp where sal >=800 and sal <=1500;
9.select ename,sal,comm from emp where comm is null;
 --选空值的记录（is not null;非空值）
10.select ename,sal,comm from emp where sal in(800,1500,2000);
 --取出值为800，1500或者2000的记录。
11.select ename,sal,hiredate from emp where hiredate > '20-2月-81';
 --日期的选择。
12.and,or,not逻辑操作符都可用。

13.模糊查询：（通配符%）
 select ename from emp where ename like '%ALL%';
 select ename from emp where ename like '_A%';
14.如字符串中含%，用转义字符'\'或者自定义转义字符
 select emane from emp where ename like '%\%%';
 select ename from emp where ename like '%$%%' escape '$';
15.select * from dept order by deptno desc;
 ---降序。默认为升序。
 select empno, ename from emp order by empno asc;
  --升序，默认的。可不带asc.
16.select ename,sal,deptno from emp order by deptno asc,ename desc;
 --多重排序，先按deptno排序，deptno相同时内部再按ename倒序排。
17.select ename, sal*12 annual_sal from emp
 where ename not like '_A%' and sal >800 order by sal desc;
 --以上总结性Slect语句。
18.SQL函数：
 lower（string）
 --select lower(ename) from emp;
 --select ename from emp where lower(ename) like '_a%';
 substr(string,start,all)
 --sletect substr(ename,1,3) from emp;--从一个字符开始截取，共截取三个字符。
 chr(number) ,ascII(char)
 --select chr(65) from dual;把数字转换成ASCII码；
 --selcet ascii('A') from dual;
 round(number)  round(number,num) 还可以是负数哦。--四舍五入。
 select round(23.652) from dual;  --24
 select round(23.652,2) from dual;--23.65
 to_char(sal,'$99,999.9999')
  --9代表一位数字。数字或者日期转换成其他形式，位数上无数字则不显示。
  --可以用0来表示，也代表一位数字。无数字也显示，用0显示。
  --select to_char(sal,'$99,999.9999') from emp;
 select to_char(hiredate,'YYYY-MM-DD HH:MI:SS') from emp;
 select to_char(hiredate,'YYYY-MM-DD HH24:MI:SS') from emp;  -24小时制
  --记住它。日期转换为特定格式。
 select ename, hiredate from emp
   where hiredate > to_date('1981-2-20 12:34:56','YYYY-MM-DD HH24:MI:SS');
  --转换日期
 select sal from emp where sal > to_number('$1,250.00','$9,999.99');
  --转换数字。
 select ename,sal*12+nvl(comm,0) from emp;
 --算术运算中怎样处理空值，nvl如为空值，用0来替代，如不为空值返回其本身的值。
19.多行函数。---多条输入，一条输出。
* select max(sal) from emp;
* select min(sal) from emp;
* select avg(sal) from emp;
 select to_char(avg(sal),'9999999.99') from emp;
 select round(avg(sal),2) from emp;
* select sum(sal) from emp;
* select count(*) from emp;---求有多少条记录。
 
 select count(distinct deptno) from emp;
 select deptno ,job,max(sal) from emp group by deptno,job;
  --按照***来分组，组内用函数处理。
*   规则：出现在select列表里面的字段没有出现在组函数里，必须出现在group by里面。
 select ename,max(sal) from emp group by deptno;---错误表达式。
 select deptno,max(sal) from emp group by deptno;--正确。
 子查询：select ename from emp where sal = (select max(sal) from emp);
where--对单条语句进行过滤。
having--对分组进行过滤。
 select avg(sal),deptno from emp group by deptno having avg(sal) > 2000;
--以上为单条select语句。总结如下（执行顺序也不能错）：
 select * from emp  --选取数据
 where sal>1000    --选择条件
 group by deptno    --对选取的数据进行分组
 having     --对分组后的数据进行过滤限制
 order by    --对最后的结果进行排序。

子查询：
 把中间过程查询出来的结果当成一张表,示例如下（表连接）：
 select ename, sal from emp
 join (select max(sal) max_sal,deptno from emp group by deptno) t
 on (emp.sal = t.max_sal and emp.deptno = t.deptno);

 自连接(为表起不同的别名，当成两张表来用)：
 select e1.ename,e2.ename from emp e1,emp e2 where e1.mgr = e2.empno;

sql1999---连接条件和数据过滤条件分开:
 select ename,dname from emp cross join dept;---交叉连接
 条件不写在where里面，改在join on()里面
 select ename,dname from emp join dept on(emp.deptno = dept.deptno);
 select ename,dname from emp join dept using (deptno);--不推荐。
 连三张表：
 select ename,dname,grade from emp e join dept d on (e.deptno = d.deptno)
 join salgrade s on(e.sal between s.losal and s.hisal)
 where ename not like '_A%';

外连接：
 select e1.ename,e2.ename from emp e1
 left outer join emp e2 on (e1.mgr = e2.empno); 
  ---outer可省略。如下：
 select e1.ename,e2.ename from emp e1 left join emp e2 on (e1.mgr = e2.empno);
  --左外连接，会把左边的表的多余数据显示出来。
 类似，也有右外连接，会把右边多余的数据显示出来。
 全外连接（sql1992不支持）：
 select ename,dname from emp e full join dept d on (e.deptno = d.deptno);
  --左右多余的数据都取出来--显示出来。
 1.部门平均薪水的等级：
 select deptno,avg_sal,grade from
 (select deptno, avg(sal) avg_sal from emp group by deptno) t
 join salgrade s on(t.avg_sal between s.losal and s.hisal);
 2.部门平均的薪水等级：
 select deptno, avg(grade) from
 (select deptno,ename,grade from emp
 join salgrade s on(emp.sal between s.losal and s.hisal)) t
 group by deptno;
 3.求雇员中有哪些人是经理人
 select ename from emp where empno in(select distinct mgr from emp);
 4.不准用组函数，求薪水的最高值
 select distinct sal from emp where sal not in
 (select distinct e1.sal from emp e1 join emp e2 on(e1.sal<e2.sal));
 5.平均薪水最高的部门编号
 select deptno ,avg_sal from
   (select avg(sal) avg_sal, deptno from emp group by deptno)
 where avg_sal =
   (select max(avg_sal) from
 (select avg(sal) avg_sal,deptno from emp group by deptno));
 组函数嵌套：（最多两层）
 select deptno ,avg_sal from
   (select avg(sal) avg_sal, deptno from emp group by deptno)
 where avg_sal =
 (select max(avg(sal)) avg_sal,deptno from emp group by deptno);
 6.平均薪水最高的部门名称
 select dname from dept where deptno =
 (select deptno from
   (select avg(sal) avg_sal, deptno from emp group by deptno)
 where avg_sal =
   (select max(avg_sal) from
 (select avg(sal) avg_sal,deptno from emp group by deptno)));
 7.平均薪水的等级最低的部门名称
 select dname from dept
 where deptno =
   (select deptno from
     (selcet deptno, avg_sal,grade from
       (select avg(sal) avg_sal,deptno from emp group by dept) t
     join salgrade s on(t.avg_sal between s.losal and s.hisal)
   ) t1
 where t1.grade =
 (select min(grade) from
   (selcet deptno, avg_sal,grade from
     (select avg(sal) avg_sal,deptno from emp group by dept) t
   join salgrade s on(t.avg_sal between s.losal and s.hisal))))

DML语句：
 insert into：
 insert into tablename values(values);
 insert into tablename(列名) values(values);--插入的数据是部门列有数据。
 insert into tablename select * from tablename2;(表的数据结构要一样)

DDL语句：
 create table tablename(a varchar2(10));
  --定义tablename的表，里面含一个字段a.
 drop table tablename;--删除表。
 drop user username;--删除用户。
 
 create table stu
 (
 id number(6) primary key,---主键约束
 name varchar2(20) not null,
 --非空 name varchar2(20) constraint stu_name_nn not null,给约束条件起名字
 sex number(1),
 age number(3),  --数字类型，几位数字。
 sdate date,        --日期类型
 grade number(2) default 1,--默认值
 class number(4) references class(id),  --外键
 email varchar2(50) unique 
  --唯一约束,null值不认为唯一冲突。
 constraint stu_name_email_uni unique(email,name)
  --表级字段组合约束。可自己取名。
 constraint stu_class_fk foreign key (class) references class(id)
 );
 
 create table class
 (
 id number(4) primary key,
 name varchar2(20) not null
 )

 五个约束条件：
 非空(constraint)，
 唯一(unique)，
 主键(primary key)，语法：非空，唯一。逻辑：代表一条记录。
  --constraint stu_id_pk primary key(id)--加在表级
 外键(references tablename(字段名))，
 --constraint stu_class_fk foreign key(字段名) references tablename(字段名)
 被参考的字段必须是主键。
 check

一个事务起始于一条DML语句，结束于commit。
 DML语句碰到DDL，DCL语句，事务结束，自动提交。rollback不起作用。
 DCL：授权，事务控制语句。
 正常断开，自动提交，非正常断开，自动回滚。

虚表（视图）--以v$开头：
create view v$_dept_avg_sal_info as
selcet deptno, avg_sal,grade from
  (select avg(sal) avg_sal,deptno from emp group by dept) t
join salgrade s on(t.avg_sal between s.losal and s.hisal)

修改已经存在的表的结构(alter):
 新增一个字段：alter table stu add(addr varchar2(100));
 修改一个字段：alter table stu modify(addr varchar2(150));--更改的精度需要能够容纳里面的数据。
 删除一个字段：alter table stu drop(addr);
 删除一个约束条件：alter table stu constraint stu_class_fk;---约束条件的名称。
 增加一个约束条件：alter table stu constraint stu_class_fk foreign key (class) references class(id);

索引：index
 建索引：create index idx_stu_email on stu(email);---也可以为字段建组合索引。
 删除索引：drop index idx_stu_email;
 查询有多少索引：select index_name from user_indexes;
  --有加主键，唯一约束时，数据库默认会建立索引。
  查询效率变高，修改数据效率降低。

数据库设计三范式：
 1.要有主键，列不可分。---设计任何表都要有主键。
 2.不能存在部分依赖。不是主键的字段不能依赖于部分主键。
 3.不能存在非关键字段决定另一非关键字段。即：关键字段->非关键字段X->非关键字段Y

Oracle下特有的一些东西。

rownum 认识：（oracle）
 只能和小于<,小于等于<=搭配使用。
 记录刚刚取出来的数据的行号，排序过后的数据不管用。
 解决办法，对数据先进行排序，再把结果当作一个表来用，可把rownum显示的调用出来
 对于大于或者区间取值，只能把rownum调出来，再进行比较。

 update:
 update emp set sal = sal*2, ename = ename||'-' where deptno = 10;
 连接一个'-'。
 
 delete：
 delete from emp;删除所有的数据。
 delete from dept where deptno < 25;

 rollback;回滚操作。--回退到初始状态，回退所有的DML语句。
 commit； 提交-提交后rollback不起作用，DML生效。

数据字典表：Oracle下当前用户的信息
 多少张表：user_tables ---select table_name from user_tables;
 多少张视图：user_views ---select view_name from user_views;
 多少约束条件：user_constraints ---select constraint_name from user_constraints;

Oracle里面装数据字典表的表：
 dictionary
 select table_name from dictionary(超多)

Oracle--序列：sequence
 专门用来产生一个独一无二的数：自动递增1或者N。
 create sequence seq;
 select seq.nextval from dual;---产生下一个数。
 insert into article values(seq.nextval,'a','b');
 drop sequence seq;