速成笔记：Oracle（未完待续....）

1.登陆sql

①在cmd中输入sqlplus 输入用户名为scott会提示the password has expired 更改scott的口令为tiger	②http://127.0.0.1:5560/isqlplus
	③在开始菜单中的集成开发环境

2.解锁用户

在cmd中输入sqlplus sys/密码 as sysdba

alter user scott  account unlock；

3.两套标准：SQL-92 SQL-99    查询-select   DML-   DDL-  事务控制语句-

4.查看表结构 desc 表名

5. insert into 表名(字段列表) values(字段列表对应值); //往表中插入数据

创建表

五种约束条件

create table stu( id number(6), name varchar2(20), sex number(1), sdata date, grade number(2) default 1, classno number(4), email varchar2(50) ); create table class( id number(4) primary key, name varchar2 );

①not null 非空约束name 为非空约束命名：constraint stu_name_nn not null ②unique唯一约束id email 两个null值不认为重复 仍然满足唯一约束 字段级约束：在create table中的字段 类型后边添加约束条件 表级约束：在所有字段最后添加(表示email和name的组合不能重复) constraint stu_name_email_uni unique(email,name) ③primary key主键约束：可以唯一标识整条记录的字段id非空且唯一 constraint stu_id_pk primary key (id) ④foreign key外键约束:同一张表两个字段或两张表的两个字段 references class(id) //参考class表中的id字段（被参考字段必须主键） constraint stu_classno_fk foreign key (classno) references class (id) ⑤check约束条件

①修改现有表结构：很少使用

alter table stu add(addr varchar(100));     //添加字段

alter table stu modify(addr varchar2(150));  //修改字段精度

alter table stu drop(addr);                  //删除字段

②修改约束条件

delete from class;会报错stu表中classno为外键

alter table stu drop constraint stu_classno_fk;//删除外键的约束条件

rollback;     //添加约束条件

alter table stu add constraint stu_classno_fk foreign key (classno) references class (id); 

③删除表：drop table stu;

6.数据字典表：

①user_tables当前用户下有多少张表 select table_name from user_tables

②user_views 当前用户下有多少视图 select view_name from user_views

③user_constraints 当前用户下有多少约束条件select constraint_name from user_constraints

④dictionary数据字典表有多少张 select table_name from dictionary

7.索引：主键约束或唯一约束建立索引 在读的时候效率更高 但插入数据的效率会变低

create index idx_stu_email on stu(email);//也可以进行组合索引 

drop index idx_stu_email; //删除索引

8.视图：相当于给子查询起个名字（简化查询/隐藏其他信息）

//表结构修改时 视图也要修改（增加了维护成本）

create view v$_stu as select id,name.age from stu;

9.序列 sequence （oracle独有的）

select max(id) from article; insert into ...;//必须同时完成两条语句

create sequence seq; //start with 1 increase by 1

insert into article values(seq.nextval, ’a’, ’b’);   //id、title、cont

单条数据查询语句

1.select  from where group by having order by desc

①select sysdata from daul; //dual只有一行记录：专门用于计算表达式的值

//sysdate取系统时间日月年： xx-xx月-xx 

②select字段列表中 sal*12 Anuual_sal 为取字段表达式的别名

                   ename||sal为字符串连接符   //使用字段或单引号的字符串

                   ename||‘hello ’‘oracle’‘ world‘ // 单引号内部用两个单引号代表单引号

                   distinct deptno 去掉字段的重复记录

                   distinct deptno，job 去掉多个字段的重复记录

③where条件表达式：

NVL(comm,0)任何含有控制的运算结果为零

where 字段 is not null;    where 字段 is  null;

where 字段 in (xxx,xxx,xxx,xxx,xxx....);

where 字段 like ‘字符串’//此处区分大小写

//字符串转为日期

where hiredate > to_date(‘1981-2-20 12:24:56’,’YYYY-MM-DD HH24:MI:SS’)

//字符串转为数字

where sal > to_number(‘$1,250.00‘,’$9,999.99’)

2.组函数：多条记录输入一条记录输出 //最低min(sal)最高max(sal)平均avg(sal)总值sum(sal)薪水值

count(*) 求出表中所有字段有多少条记录

count(字段) 求出该字段非空有多少条记录

count(distinst 字段) 求出该字段非空且唯一多少条记录

3.分类汇总：按组分类 汇总其平均薪水

select deptno,avg(sal) from emp group by deptno;

select deptno,job,max(sal) from emp group by deptno,job; //按两个字段进行分类

4.关于不是单组分组函数：

select max(sal) from emp; //正确  select ename, max(sal) from emp; //错误

可能会产生多个人赚相同的max薪水值，导致多行记录输出：不满足单行结果输出

解决的方法：select ename, max(sal) from emp group by ename;

//出现在select列表的字段：要么(sal)出现在组函数中 要么(ename)出现在 group by中

select ename from emp where sal=(select max(sal) from emp); //使用子查询

5.having语句：将分类汇总的结果进行筛选

select deptno,avg(sal) from emp group by deptno having avg(sal)>2000;

6.字符处理:lower() upper() substring(字段,N,m) 从第N个字符开始共截取m个字符

round((avg(sal),2) 四舍五入到小数点后两位 可以为负数

数字转字符串

to_char(avg(sal), ’$999999999.99’) :将数字转换为字符串 精确到小数点后两位

to_char(avg(sal), ’L000,000,000.0000’);不足补零 L为本地货币

to_char(hiredate,’YYYY.MM.DD HH24:MI:SS’);

子查询和表连接

1.子查询：在select语句中出现另一个select语句中 

有哪些人的工资最多

select ename from emp where sal=(select max(sal) from emp); 

有哪些人的工资位于平均水平之上

select ename,sal from emp where sal>(select avg(sal) from emp); 

按照部门进行分组后赚钱最多的人   //将select结果当成一张表

select ename,sal,deptno from emp //与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);

2.表的自身连接

求出员工和对应经理人的名字

select e1.ename,e2.ename from emp e1, emp e2 where e1.mgr=e2.empno;

3.表连接SQL99使用join-on将连接条件和筛选条件分类 SQL92写入到where中

如果是等值连接：select ename,dname from emp join dept using (deptno); //不推荐36*

cross join /