一、初步sql的补充
1、sql语句分为三种类型：
1）DML---->数据操纵语言
2）DDL---->数据定义语言
3）DCL---->数据控制语言
解释：
DML:包括（insert， delete， update， select）
DDL:包括（create table，alter table，drop table，create index，drop index）
DCL:包括（grant---->授予访问权限
revoke---->撤销访问权限
commit:--->提交事务处理
rollback-->事务处理回退
savepoint->设置保存点
lock------>对数据的特定部分进行锁定）
2、select语句
1)实例：select * |{[DISTINCT] column|expression[alias],....}
from table
2）注意:（1）sql大小写不敏感
（2）关键字不能被缩写也不能被分行
（3）各个子句一般要分行写
3）算数运算：数字和日期一般使用算数运算
+ - * %
（1）例子：select last_name,salary,salary+300
from employees;
（2）算优数的先级：
（1）乘除的优先级高于加减的优先级
（2）同一层级的从左到右
（3）括号内的优先级最高
SQL> select first_name,last_name,manager_id,(salary+1200)*12
2 from employees;
（3）定义空值：空值是未指定的，无效的，未知的或不可预知的
空值不是null也不是0
SQL> select last_name,job_id,salary,commission_pct
2 from employees;
（4）列的别名：重命名一个列
便于计算
紧跟列明：也可以在列名和别名之间加入关键字‘AS'别名使用双引号以便在别名和特殊的字符并区分大小写
SQL> select last_name as name,commission_pct comm
2 from employees;
SQL> select last_name "Name",salary*12 "Annual Salary"
2 from employees;
(5)连接符:
把列于列，列于字符连接在一起
用 '||'表示
可以用来合并列。
SQL> select last_name,job_id,last_name || job_id as "EMployees"
2 from EMPLOYEES;
（6）字符串
字符串可以是select列表的一个字符，数字，日期
日期和数字只能用单引号
每返回一行的时候，字符串被输出一次
（7）重复查询：默认情况下 查询会返回全部数据
（8）删除重复查询---->distinct
SQL> select distinct department_id
2 from EMPLOYEES;
2、sql 和sql plus
1)、联系
2）、解释
3、显示结构
desc 表名
4、总结
二、过滤和排序数据库
猜想：where order by
1、过滤：
1）语句标准：select *|{[distinct] column|expression [alias],......}
from table
[where codition(s)];
2)注意：日期和字符含在单引号里
字符大小写敏感
日期格式敏感
日期默认的格式是dd-MM-rr
3)比较运算
列子
SQL> select last_name,salary
2 from EMPLOYEES
3 where salary<=3000;
逻辑运算符
SQL> select last_name,first_name,salary
2 from EMPLOYEES
3 where salary>2000 and first_name like 'S%' or job_id NOT IN('IT_PROG','ST_CLERK')
4 ;
4）order by 子句
---->asc 升序
---->desc 降序
SQL> select last_name,job_id,department_id,hire_date
2 from employees
3 order by hire_date desc;
按别名排序
SQL> select first_name,last_name,salary*12 annary
2 from EMPLOYEES
3 order by annary;
select first_name,email,hire_date as "count_date"
from employees
where first_name like 'S%'
and salary >2000
or manager_id is not null
order by hire_date desc;
三、单行函数
1）单行函数：操作数据对象
接受参数返回一个结果
只对一行数据进行变换
每行返回一个结果
可以转换数据类型
可以嵌套
参数可以是一列或一个值
function_name[arg1,arg2,....]
2)
3)
concat---连接
substr---取值
length---长度
instr----位置
lpad-----前添加
rpad-----后添加
trim-----什么之后的值
replac---替换
SQL> select employee_id,concat(first_name,last_name) NAME,
2 job_id,length(last_name),
3 instr(last_name,'a') "COntains 'a'?"
4 from EMPLOYEES
5 where substr(job_id,4)='REP';
round--->四舍五入
trunc--->截断
mod----->求余
SQL> select round(49.215,2),round(56.789,0),round(78.342,-1)
2 from dual;
结果：
ROUND(49.215,2) ROUND(56.789,0) ROUND(78.342,-1)
--------------- --------------- ----------------
49.22 57 80
SQL> select last_name,salary,mod(salary,400)
2 from EMPLOYEES
3 where job_id='SA_REP';
2）日期：
Oracle 中的日期型数据实际含有两个值: 日期和时间。
SQL> select last_name,hire_date
2 from employees
3 where last_name like 'G%';
结果：
LAST_NAME HIRE_DATE
------------------------- -----------
Greenberg 1994/8/17
Gee 1999/12/12
Greene 1999/3/19
Grant 1999/5/24
Geoni 2000/2/3
Gates 1998/7/11
Grant 2000/1/13
Gietz 1994/6/7
2）日期的数学运算：
日期上加上或减去一个数字的结果为日期
两个日期相减返回日期相差的天数（日期不允许做加法运算----毫无意义）
可以用数字除以24来向日期做加或减天数
select last_name,(sysdate-hire_date)
from EMPLOYEES
where department_id=90;
4、数据类型
SQL> select last_name,hire_date,
2 to_char(hire_date,'HH24:MI:SS AM')
3 as "DD MM YYYY"
4 from employees
5 where to_char(hire_date,'YYYY')='1994';
LAST_NAME HIRE_DATE DD MM YYYY
------------------------- ----------- ----------
Greenberg 1994/8/17 1994
Faviet 1994/8/16 1994
Raphaely 1994/12/7 1994
Mavris 1994/6/7 1994
Baer 1994/6/7 1994
Higgins 1994/6/7 1994
SQL> select to_date('2005-01-01 13:14:20','yyyy-MM-dd HH24:mi:ss')
2 from DUAL;
TO_DATE('2005-01-0113:14:20','
------------------------------
2005/1/1 13:14:20
当前时间减去七年的时间：
select to_date(sysdate-interval '7'year) from dual;
SQL> select to_char(salary,'L99,999.0')
2 from EMPLOYEES
3 where last_name='Ernst';
TO_CHAR(SALARY,'L99,999.0')
---------------------------
￥6,000.0
SQL> SELECT TO_NUMBER(TO_CHAR(TO_DATE('2018-11-02','YYYY-MM-DD'),'MM'),'99') FROM DUAL;
TO_NUMBER(TO_CHAR(TO_DATE('201
------------------------------
11
(1)NVL----空值变为已知道的值
SQL> select last_name,salary,NVL(commission_pct,0),
2 (salary*12)+(salary*12*NVL(commission_pct,0)) AN_SAL
3 from EMPLOYEES;
LAST_NAME SALARY NVL(COMMISSION_PCT,0) AN_SAL
------------------------- ---------- --------------------- ----------
King 24000.00 0 288000
Kochhar 17000.00 0 204000
（2）NVL2函数
select last_name,salary,commission_pct,
NVL2(commission_pct,
'SAL+COMM','SAL')income
from EMPLOYEES
where department_id in (50,80);
(3)、NULLIF
(4)、coalesce
SQL> select last_name,
2 coalesce(commission_pct,salary,10)comm
3 from employees
4 order by commission_pct;
LAST_NAME COMM
------------------------- ----------
Lee 0.1
Johnson 0.1
Marvins 0.1
Banda 0.1
Kumar 0.1
(6)、条件表达式;
（7）、decode函数
（8）、嵌套函数
总结：
四、多表查询
1、目标：
（1）、使用等值和不等值连接在select语句中查询多个表的数据
（2）、使用自然连接
（3）、使用外连接查询不满足连接条件的数据
2、笛卡尔积：
1）、解释：
（1）、省略连接条件
（2）、连接条件无效
（3）、所有表的所有数据相互连接
（4）、为了避免笛卡尔积，可以在where里加入条件
2）、实例
SQL> select employees.EMPLOYEE_ID,employees.LAST_NAME,
2 departments.DEPARTMENT_ID,DEPARTMENTS.LOCATION_ID
3 from DEPARTMENTS,employees
4 where EMPLOYEES.EMPLOYEE_ID=DEPARTMENTS.DEPARTMENT_ID;
EMPLOYEE_ID LAST_NAME DEPARTMENT_ID LOCATION_ID
----------- ------------------------- ------------- -----------
100 King 100 1700
110 Chen 110 1700
120 Weiss 120 1700
130 Atkinson 130 1700
140 Patel 140 1700
150 Tucker 150 1700
160 Doran 160 1700
170 Fox 170 1700
180 Taylor 180 1700
190 Gates 190 1700
200 Whalen 200 1700
11 rows selected
3）多个连接条件与and的操作
注意：连接 n 个表至少需要 n-1 个条件
SQL> select e.last_name,d.department_name,l.city
2 from employees e,departments d,locations l
3 where e.department_id=d.department_id and d.location_id=l.location_id;
三个表两个条件
4）非等值连接
select e.last_name,e.salary,j.grade_level
from employees e,job_grades j
where
e.salary between j.lowest_sal and j.highest_sal;
5)外连接
（1）、不同与内连接：
内连接: 合并具有同一列的两个以上的表的行, 结果集中不包含一个表与另一个表不匹配的行
外连接: 两个表在连接过程中除了返回满足连接条件的行以外还返回左（或右）表中不满足条件的行 ，这种连接称为左（或右） 外连接。没有匹配的行时, 结果表中相应的列为空(NULL). 外连接的 WHERE 子句条件类似于内部连接, 但连接条件中没有匹配行的表的列后面要加外连接运算符, 即用圆括号括起来的加号(+).
（2）、
select e.last_name,e.department_id,d.department_name
from departments d,employees e
where e.department_id(+)=d.department_id
3）、自然连接:是一种特殊的等值连接（等值连接把属性去掉 ）
select worker.last_name || '---works for---' || manager.last_name
from EMPLOYEES worker,employees manager
where worker.manager_id=manager.employee_id
and
worker.last_name in ('Chen');
2、语法连接
1）cross join---->交叉连接
使用cross join 子句使让连接的表产生叉集（和笛卡尔积相同）
select DEPARTMENT_id,department_name,locations.location_id,locations.city
from DEPARTMENTS
cross join LOCATIONS;
2）natural join-->自然连接
SQL> select DEPARTMENT_id,department_name,location_id,city
2 from DEPARTMENTS
3 natural join LOCATIONS;
3）join --- using()
select DEPARTMENT_id,department_name,location_id,city
from DEPARTMENTS
join LOCATIONS using(location_id);
4)NO创建子连接
自然连接是以具有相同的名字的列为连接条件的
可以使用NO来指定额外的连接条件
这个连接条件与其他连接调见分开
ON子句使语句有更高的可读性
select EMPLOYEES.EMPLOYEE_ID,EMPLOYEES.LAST_NAME,DEPARTMENTS.DEPARTMENT_ID,
DEPARTMENTS.LOCATION_ID
from EMPLOYEES join departments
ON (EMPLOYEES.DEPARTMENT_ID=DEPARTMENTS.DEPARTMENT_ID);
5）内连接和外连接（2）
在SQL: 1999中，内连接只返回满足连接条件的数据
两个表在连接过程中除了返回满足连接条件的行以外还返回左（或右）表中不满足条件的行，这种连接称为左（或右） 外连接。
两个表在连接过程中除了返回满足连接条件的行以外还返回两个表中不满足条件的行 ，这种连接称为满 外连接。
（1）左外连接 left outer join ..... on
（2）右外连接 right outer join .... on
（3）满外连接 full outer join ..... on
4、总结：
(1)、select ... from ... where
(2)、select ...from ....natural join...
(3)、select ....from ....join ... on...(可以实现right outer右,left outer左，满full outer)
(4)、select ....from .....join ...using..
SQL语句的多表查询方式：
例如：按照department_id查询employees(员工表)和departments(部门表)
的信息。
方式一(通用型):SELECT ... FROM ... WHERE
SELECT e.last_name,e.department_id,d.department_name
FROM employees e,departments d
where e.department_id = d.department_id
方式二：SELECT ... FROM ... NATURAL JOIN ...
有局限性：会自动连接两个表中相同的列(可能有多个:department_id和manager_id)
SELECT last_name,department_id,department_name
FROM employees
NATURAL JOIN departments
方式三：SELECT ... JOIN ... USING ...
有局限性：好于方式二，但若多表的连接列列名不同，此法不合适
SELECT last_name,department_id,department_name
FROM employees
JOIN departments
USING(department_id)
方式四：SELECT ... FROM ... JOIN ... ON ...
常用方式，较方式一，更易实现外联接(左、右、满)
SELECT last_name,e.department_id,department_name
FROM employees e
JOIN departments d
ON e.department_id = d.department_id
--内连接
1）
--等值连接
--不等值连接
2）
--非自连接
--自连接
--外连接
--左外连接、右外连接、满外连接
五、分组函数
1、目标：
1）了解组函数
2）组函数的用途
3）group by分组
4）having过滤
2、组函数（avg，count,max,min,sum）
3、语法
SELECT [column,] group_function(column), ...
FROM table
[WHERE condition]
[GROUP BY column]
[ORDER BY column];
select max(avg(salary))
from employees
group by department_id
order by department_id
5、子查询
1、目标
1）、定义子查询
2）、列出子查询的类型
3）、子查询解决的问题
4）、编写子查询
2、语法：
1）、
2）、实例：
SQL> select last_name
2 from EMPLOYEES
3 where salary >
4 (select salary
5 from EMPLOYEES
6 where last_name='Abel'
7 );
3、注意：
1）、子查询必须在括号内
2）、子查询在比较的条件的右边
3）、单行操作对应单行子查询，多行操作对应多行子查询
4、类型
1）、
2）、单行子查询
SQL>
SQL> SELECT last_name, job_id, salary
2 FROM employees
3 WHERE job_id =
4 (SELECT job_id
5 FROM employees
6 WHERE employee_id = 141)
7 AND salary >
8 (SELECT salary
9 FROM employees
10 WHERE employee_id = 143);
3）、单行子查询使用组函数
SQL> select last_name,job_id,salary
2 from EMPLOYEES
3 where salary = (
4 select max(salary)
5 from EMPLOYEES);
LAST_NAME JOB_ID SALARY
------------------------- ---------- ----------
King AD_PRES 24000.00
4）、单行子查询里有having
select department_id,min(salary)
from EMPLOYEES
group by DEPARTMENT_ID
having min(salary)>(
select min(salary)
from EMPLOYEES
where department_id=50);
SQL> select department_id,min(salary)
2 from EMPLOYEES
3 group by DEPARTMENT_ID
4 having min(salary)>(
5 select min(salary)
6 from EMPLOYEES
7 where department_id=50);
DEPARTMENT_ID MIN(SALARY)
------------- -----------
100 6900
30 2500
7000
90 17000
20 6000
70 10000
110 8300
80 6100
40 6500
60 4200
10 4400
11 rows selected
5）、多行查询
select department_id,last_name,job_id,salary
from EMPLOYEES
where salary<all(
select salary
from EMPLOYEES
group by department_id
)
and
job_id<>'IT_PROG'
SQL> select department_id,last_name,job_id,salary
2 from EMPLOYEES
3 where salary<any(
4 select min(salary)
5 from EMPLOYEES
6 group by department_id
7 )
8 and
9 job_id='IT_PROG'
10 ;
DEPARTMENT_ID LAST_NAME JOB_ID SALARY
------------- ------------------------- ---------- ----------
60 Lorentz IT_PROG 4200.00
60 Austin IT_PROG 4800.00
60 Pataballa IT_PROG 4800.00
60 Ernst IT_PROG 6000.00
60 Hunold IT_PROG 9000.00
6）、总结
七、创建和管理表
1、目标
1）、描述数据库对象
2）、数据类型
3）、表的创建
4）、表的修改
5）、表的删除、清空
2、开始
1）、数据库对象：表、视图、索引、同义词、序列
（1）、表
用户定义的表------------->包含用户的所有信息
select * from user_tables-------->查看用户创建的所有表
（2）、数据字典
包含数据库的信息
由oracle server自动创建的
查看用户定义的表---->select table_name from user_tables;
查看用户定义的各种数据库对象---->select distinct object_type from user_objects;
查看用户定义的表，视图，同义词，序列--->select * from user_catalog;
2）、表的命名规则：
（1）、必须以字母开头
（2）、必须是A~Z,a~z,0~9,_,$和#
（3）、不能和用户定义的表重名
（4）、不能是oracle数据库的关键字
（5）、必须在1~30字符之间
3）、create table语句
SQL> create table dept
2 (depto number(2),
3 dname varchar(14),
4 loc varchar(13)
5 );
Table created
4）、数据类型（10种）
varchar(size)----->变长字符
char(size)-------->定长字符
number(p,s)------>数字变长
long-------------->数字变长可到达2G
date-------------->日期
clob-------------->字符数据4G
blob-------------->二进制数据
rowid------------->行地址
raw--------------->原始二进制数据
bfile------------->外部的二进制数据
SQL> create table emp2
2 as
3 select *
4 from EMPLOYEES
5 where 1=2;
Table created
4）、alter table语句
（1）可以追加新的列
（2）修改现有的列
（3）删除一个列
（4）重命名表的一个列名
（5）为新追加的列定义一个列明
5）删除表
（1）、数据和结构都被删除
（2）、所有正在运行的相关事务都被提交
（3）、所有相关索引被删除
（4）、drop table 语句不能回滚
6）清空表
（1）、删除表的所有数据
（2）、释放表的所有空间
方式一:truncate 数据不能回滚
truncate table 表名
方式二利用delete 删除，再利用rollback回滚
delete from emp2
select * from emp2;
rollback;
select * from emp2
7）改变对象的名称
执行RENAME语句改变表, 视图, 序列, 或同义词的名称
rename 旧表名 to 新表名
8）总结
八、数据处理
1、目标
1）、向表里插入数据
2）、更新数据
3）、删除数据
4）、控制事务
5）、会使用DDL语句
2、
DML(Data Manipulation Language – 数据操纵语言) 可以在下列条件下执行:
向表中插入数据
修改现存数据
删除现存数据
事务是由完成若干项工作的DML语句组成的
3、语法
1）、inert into table [column,.....] value(value1,.....)//这个只能插入一条数据
2）、每一列添加一个新的值
按照默认顺序添加值
指出每一列添加对应的值
字符和日期应该用单引号包含
3）、向表里添加空值
插入空值：
insert into emp2 values(1000,'ysh',null,null)----显示
insert into emp2 (department_id,last_name) values(1000,'us');
插入指定的值：
指定表的列和指定表的属性对应的数据
INSERT INTO employees
VALUES (114,
'Den', 'Raphealy',
'DRAPHEAL', '515.127.4561',
TO_DATE('FEB 3, 1999', 'MON DD, YYYY'),
'AC_ACCOUNT', 11000, NULL, 100, 30);
创建脚本---使用&指定value
从其他表拷贝数据---不用书写value
2）更新数据
update table
set column=value[,...]
[where]
update emp2
set job_id=(select job_id
from emp2
where employee_id=205),
salary=( select salary
from emp2
where employee_id=205)
where employee_id=115;
3）、删除数据
delete from 表名
[where]
列：
DELETE FROM departments
WHERE department_name in (last_name like 'A%');
SQL> delete from emp2
2 where last_name in
3 (select last_name
4 from emp2
5 where last_name like 'A%');
4 rows deleted
5、数据库事务
1、定义
事务：一组逻辑操作单元,使数据从一种状态变换到另一种状态。
数据库事务由以下的部分组成:
一个或多个DML 语句
一个 DDL(Data Definition Language – 数据定义语言) 语句
一个 DCL(Data Control Language – 数据控制语言) 语句
2、了解
以第一个 DML 语句的执行作为开始
以下面的其中之一作为结束:
COMMIT 或 ROLLBACK 语句
DDL 语句（自动提交）
用户会话正常结束
系统异常终止
3、commit 和rollback的优点
使用COMMIT 和 ROLLBACK语句,我们可以:
1）、确保数据完整性。
2）、数据改变被提交之前预览。
3）、将逻辑上相关的操作分组。
九、约束
1、目标
1）、描述约束
2）、创建和维护约束
2、什么是约束
约束是表的强制规定
有一下五种约束
not null
unique---->唯一
primary key
foreign key
check
3、注意
1）、创建啊表的时候，如果不指定约束条件oracle server会自动的按照Sys_cn来定义约束
2）、约束创建--可以建表前
--可以建表后
3）、可以创建表级别的也可以创建列级别的约束
4）、约束的查看可以再数据库字典视图里查看
3、表约束和列约束
1）、作用范围:表约束可以定义多个列(也可以定义一个列)；列约束只能定义一个列
2）、定义方式：列约束在列的后面 表约束单独定义
3）、not null 只能定义列
not null
SQL> create table emp1(
2 id number(10) constraint emp1_id_nn not null,
3 name varchar(20) not null,
4 salary number(10,2)
5 )
6 ;
Table created
2、定义约束
1）、列级：
column [constraint constraint_name] constraint_type,
2）、表级：
column,....
[constraint constraint_name] constraint_type
(column,....)
3、unique ----唯一约束
create table emp5(
emp5_id number(10) CONSTRAINT emp5_id_nn not null,
last_name VARCHAR2(25) constraint emp5_last_name_uk UNIQUE,
email VARCHAR2(25),
salary NUMBER(8,2),
commission_pct number(2,2),
hire_date date constraint emp5_hire_date_nn not null,
--表级的约束
constraint emp5_email_uk UNIQUE(email)
)
create table student_1902(
id number(8),
name VARCHAR(10) CONSTRAINT student_1902_id_nn not null,
sex VARCHAR(2),
address VARCHAR (100),
telephone number(11),
achivement NUMBER(4),
class VARCHAR(5),
constraint student_1902_id_pk primary key(id)
)
create table emp6(
department_id NUMBER(4),
--列级的约束 ---not null
emp6_id number(10) CONSTRAINT emp6_id_nn not null,
last_name VARCHAR2(25) constraint emp6_last_name_uk UNIQUE,
email VARCHAR2(25),
salary NUMBER(8,2),
commission_pct number(2,2),
hire_date date constraint emp6_hire_date_nn not null,
--表级的约束 ---unique
constraint emp6_email_uk UNIQUE(email),
--表级的约束 --添加外键 foreign key ... reference ...
CONSTRAINT emp6_department_id_fk foreign key(department_id)
references DEPARTMENTS(department_id)
)
create table emp8(
--列级的创建主键
id number(6) primary key,
--列级的创唯一的name
name VARCHAR2(25) unique,
--check来定义条件
salary number(8,2) CHECK(salary>0 and salary <10000),
dept_id number(4),
--表级的创建外键
CONSTRAINT emp8_dept_id_fk foreign key(dept_id)
references dept(dept_id) on delete cascade
)
3、添加约束
1、语法：
使用alter table语句
1）、添加或删除约束，但是不能修改约束
2）、有效化和无效化约束
3）、添加not null 约束使用modify语句
alter tavle emp7 modify(name varchar(10) not null)
--因为是列的变化所以使用
--alter table 表名
--add constraint
alter table EMPLOYEES
add constraint emp_manger_fk
foreign key (manager_id)
references EMPLOYEES(employee_id)
--删除约束
alter table employees
drop constraint emp_manger_fk
--无效化约束
alter table employees
disable constraint emp_manger_fk
--激活约束
--当激活约束的时候会自动的创建not null 和primary key
alter table employees
enable constraint emp_manger_fk
--查询约束
--查询数据字典的视图 user_constraints
select constraint_name,constraint_type,
search_condition
from user_constraints
where table_name = 'EMPLOYEES'
CONSTRAINT_NAME CONSTRAINT_TYPE SEARCH_CONDITION
------------------------------ --------------- --------------------------------------------------------------------------------
EMP_LAST_NAME_NN C "LAST_NAME" IS NOT NULL
EMP_EMAIL_NN C "EMAIL" IS NOT NULL
EMP_HIRE_DATE_NN C "HIRE_DATE" IS NOT NULL
EMP_JOB_NN C "JOB_ID" IS NOT NULL
EMP_SALARY_MIN C salary > 0
EMP_EMAIL_UK U
EMP_EMP_ID_PK P
EMP_DEPT_FK R
EMP_JOB_FK R
EMP_MANAGER_FK R
10 rows selected
--查询定义约束的列
--查询数据字典视图 user_cons_colums
select contraint_name,column_name
from user_cons_columns
where table_name='EMPLOYEES'
CONSTRAINT_NAME COLUMN_NAME
------------------------------ --------------------------------------------------------------------------------
EMP_LAST_NAME_NN LAST_NAME
EMP_EMAIL_NN EMAIL
EMP_HIRE_DATE_NN HIRE_DATE
EMP_JOB_NN JOB_ID
EMP_SALARY_MIN SALARY
EMP_EMAIL_UK EMAIL
EMP_EMP_ID_PK EMPLOYEE_ID
EMP_DEPT_FK DEPARTMENT_ID
EMP_JOB_FK JOB_ID
EMP_MANAGER_FK MANAGER_ID
10 rows selected
4、总结
1）、not null ----只能列级
2）、unique -----唯一
3）、primary key---主键
4）、forign key --外键 ---和references结合使用
5）、check --检查 （这个其实是一个条件）
十、视图
1、目标
1）、描述视图
2）、创建、修改、删除视图
3）、查询视图的数据
4）、通过视图添加、修改、删除表的数据
5）、分析Top-N的数据
2、描述视图
表-----基本的数据存储集合 由行和列组成
视图---从表里抽取逻辑上相关的数据的集合
索引---方便查询数据
同义词--表的对象的别名
序列----提供有规律的值
3、解释
1）、视图是虚表
2）、视图是简历在已有表（基表）的基础上
3）、可以理解为存储起来的select语句
4）、视图是向用户提供基表的一种形式
4、为什么使用视图
1）、控制数据访问
2）、简化查询
3）、避免重复数据
5、创建视图
1）在create view 里嵌入子查询
create [or replace][force |no force] view 视图的名称
[(alias[,alias]....)]
as subquery
[with check option [constraint 约束]]
[with read only [constraint 约束]]
2）、子查询也可以是复杂的select 语句
--创建视图
--no replace 没有就替换视图
--force 基表
--no force没有基表
create or replace view emp1_view
as
select EMPLOYEE_id emp_id,
last_name name,
department_name
from employees e,departments d
where e.DEPARTMENT_ID=d.DEPARTMENT_ID
3）、创建复杂的视图
--创建复杂的视图
--简单的利用一些组函数
create table dept_sum_vu
(name,minsal,maxsal,avgsal)
as
select d.department_name,min(e.SALARY),
MAX(e.SALARY),avg(e.SALARY)
from employees e,departments d
where e.DEPARTMENT_ID=d.department_id
group by d.DEPARTMENT_NAME
4）、修改视图
create or replace view 子句修改视图
--创建视图
create view empvu80
as
select employee_id,last_name,salary
from EMPLOYEES
where department_id=80
--修改视图
--利用or replace
CREATE OR REPLACE VIEW empvu80
(id_number, name, sal, department_id)
AS SELECT employee_id, first_name || ' ' || last_name,
salary, department_id
FROM employees
WHERE department_id = 80
5）、视图使用DML操作
当视图里包含一下几个东西的时候不能使用delete
group by语句
rownum----相当与mysql的limit
distinct---去掉重复数据
--rownum的使用相当与mysql的limit
select *
from employees
where rownum<=2
6）、屏蔽掉DML的操作
可以使用with read only选项屏蔽对视图的DML操作
任何的DML操作都会返回一个oracle server错误
--屏蔽掉DML的操作
create or REPLACE view emp10
as
select DEPARTMENT_ID,last_name,salary
from EMPLOYEES
where department_id=10
with read ONLY
7）、删除视图
SQL> drop view emp10;
View dropped
8）、top-N数据分析
--TOP_N 数据的分析
select last_name || '--'||first_name name,salary
from (select last_name,first_name,salary
from employees
order by salary desc)
where rownum<=3
--复杂得到查询语句
select *
--这个*包括（rownum,employee_id,salary）
from (
--一个表
select rownum rn,employee_id,salary
from (
--一个表
select employee_id,salary,last_name
from employees
order by salary desc
)
)where rn <=50 and rn >40
9）、总结
控制语句的访问
简化查询
数据独立性
删除时不删除数据
Top-N 分析
十一、其他数据库对象
1、目标
1）、创建维护使用序列
2）、创建维护索引
3）、创建同义词
2、序列
1）、什么是序列：可以提供多个用户来产生唯一数值的数据库对象
自动的提供唯一数值
共享对象
主要用于提供主键
将序列值装入内存可以提高访问
2）、语句
create sequence sequence
[increament by n]--每一次增长的数值
[start with n]--从那个值开始
[{maxvalue n | nomaxvalue}]
[{minvalue n | nominvalue}]
[{cycle | nocycle}]--是否需要循环
[{cache n | nocahe}]--是否缓存登入
--创建sequence序列
create sequence dept_deptid_seq
increment by 10
start with 120
maxvalue 9999
nocache
nocycle
--查询序列
--通过字典user_sequences
select sequence_name,min_value,max_value,increment_by,
last_number
from user_sequences
3）、nextval和currval伪例
nextval---返回序列的下一个有效值
currval---返回当前的值
nextval应该在currval的前面指定，否之会报错
4）、使用序列
将序列值存入内存可以提高访问效率
序列值出现裂缝的情况:
相同异常
多个表同时访问一个序列
回滚
如果没有存入内存（nocache）的话可以使用uesr_sequences访问当前的值有效
5）、修改序列
--修改序列
alter sequence dept_deptid_seq
increment by 20--初始化
--start with 40--开始值
maxvalue 999999--最大值
nocache--不缓存
nocycle--不回滚
5）、修改序列的注意事项
必须是序列的拥有者才可以进行alter
只能改变未来的值
如果删除序列的初始化的值就必须删除改序列然后重新创建序列
6）、删除序列
drop sequence 语句
删除后不能在使用
--删除序列
drop sequence DEPT_DEPTID_SEQ
3、索引
2、创建索引
1）、自动创建：定义在primary key 或unique约束之后系统自动的在相应的列上吵架呢唯一的索引
手段创建：用户在其他列上创建非唯一的索引，可以加速查询
2）、语句：
create index 名称
on 表名（column[,...]）
--创建索引
--语句
--create index 索引名称
--on 表名
create index emp2_last_name_index
on emp2(last_name)
3）、什么时候使用到索引
该数值的使用范围广
经常在where语句和连接条件使用到
数据的访问量大
4）、查询索引
查询索引
可以使用数据字典视图user_index和user_ind_columns查看索引的信息
--查询索引
---数据字典user_indexes和user_ind_columns
select ic.index_name,ic.column_name,
ic.column_position col_pos,ix.uniqueness
from user_indexes ix,user_ind_columns ic
where ic.INDEX_NAME=ix.index_name
and ic.TABLE_NAME='EMPLOYEES'
5）、删除索引
使用DROP INDEX 命令删除索引
删除索引UPPER_LAST_NAME_IDX
只有索引的拥有者或拥有DROP ANY INDEX 权限的用户才可以删除索引
删除操作是不可回滚的
--删除索引
drop index emp2_last_name_index
4、同义词
使用同义词访问相同的对象：
方便访问对象访问其他的用户的对象
缩短对象名字的长度
create [public] sysnonym 名称
for object;
--创建同义词
--语句
--create synonym
--for object
create synonym e
for EMPLOYEES
--查询同义词
select *
from e
--删除同义词
drop synonym e
7、总结
1）、使用序列
2）、使用索引提高查询效率
3）、为数据对象定义同义词
254
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
