1.SQL的select语句
有关select的练习题
1. SQL*PLUS命令可以控制数据库吗? 否！
2. 下面的语句是否可以执行成功 可以
select last_name , job_id , salary as sal
from employees;
3. 下面的语句是否可以执行成功 可以
select * from employees;
4. 找出下面语句中的错误 标点符号需要是英文格式下的。
select employee_id , last_name，
salary * 12 “ANNUAL SALARY”
from employees;
5. 显示表departments的结构，并查询其中的全部数据
desc departments;//（describe）
select * from departments;
6. 显示出表employees中的全部job_id（不能重复）
Select distinct job_id from employees;
7. 显示出表employees的全部列，各个列之间用逗号连接，列头显示成OUT_PUT
a) select employee_id ||','|| last_name||','||salary "OUT_PUT"
from employees
2.条件过滤–where，排序order by
1. 查询工资大于12000的员工姓名和工资
a) select last_name,salary
b) from employees
c) where salary > 12000
2. 查询员工号为176的员工的姓名和部门号
a) select last_name,department_id
b) from employees
c) where employee_id = 176
3. 选择工资不在5000到12000的员工的姓名和工资
a) select last_name,salary
b) from employees
c) --where salary < 5000 or salary > 12000
d) where salary not between 5000 and 12000
4. 选择雇用时间在1998-02-01到1998-05-01之间的员工姓名，job_id和雇用时间
a) select last_name,job_id,hire_date
b) from employees
c) --where hire_date between '1-2月-1998' and '1-5月-1998'
d) where to_char(hire_date,'yyyy-mm-dd') between '1998-02-01' and '1998-05-01'
5. 选择在20或50号部门工作的员工姓名和部门号
a) select last_name,department_id
b) from employees
c) where department_id = 20 or department_id = 50
d) --where department_id in (20,50)
6. 选择在1994年雇用的员工的姓名和雇用时间
a) select last_name,hire_date
b) from employees
c) --where hire_date like '%94'
d) where to_char(hire_date,'yyyy') = '1994'
7. 选择公司中没有管理者的员工姓名及job_id
a) select last_name,job_id
b) from employees
c) where manager_id is null
8. 选择公司中有奖金的员工姓名，工资和奖金级别
a) select last_name,salary,commission_pct
b) from employees
c) where commission_pct is not null
9. 选择员工姓名的第三个字母是a的员工姓名
a) select last_name
b) from employees
c) where last_name like '__a%'
10. 选择姓名中有字母a和e的员工姓名
a) select last_name
b) from employees
c) where last_name like '%a%e%' or last_name like '%e%a%'
3.单行函数
select * from account where lower(name) LIKE '%j%'; -- 这是将字段name的所有结果转换成小写，再和我们定义的查询条件进行比较；
select * from account where upper(name) LIKE '%J%'; -- 这是将字段name的所有结果转换成大写，再和我们定义的查询条件进行比较；
concat('hello', 'world') helloworld // 连接字符
substr('helloworld',1,5) hello // 从1开始，输出5个
lengte('helloworld') 10 // 返回字符串的个数
instr('helloworld','w') 6 // 判断指定字符出现的位置
lpad(salary,10,'*') *****24000 //
rpad(salary,10,'*') 24000*****
trim('H' from 'Helloworld') elloworld // 去除收尾指定的字符
replaace('abcd','b','m') amcd // 替换所有符合的字符
四舍五入：ROUND()
select round(435.467,2) from dual; 结果：435.47 取小数点后两位，进行四舍五入
select round(435.467) from dual; 结果：435 取小数点，进行四舍五入
select round(435.467，-2) from dual; 结果：400 取小数点前两位，进行四舍五入
截断： trunc()：
select trunc(435.467,2) from dual; 结果：435.46 从小数点后两位截断，不进行四舍五入
select trunc(435.467) from dual; 结果：435 从小数点截断，不进行四舍五入
select trunc(435.467，-1) from dual; 结果：430 从小数点前一位截断，不进行四舍五入
取余：mod():
select mod(1100，300) from dual; 结果：200
select months_between(sysdate, mydate) from dual; //结果是相差的约束
select sysdate, add_months(sysdate, 3) from dual; //当前日期加3个月
select sysdate, next_day(sysdate, '星期三') from dual; // 下个星期三对应日期
select sysdate, last_day(sysdate)-1 from dual; // 本月的倒数第二天
select sysdate, round(sysdate,'month|year|mm|...')-1 from dual;
nvl函数：
当要查询的值为null是，将空值转换成一个已知的值:
可以使用的数据类型有日期、字符、数字。
函数的一般形式:
nvl(nullZhi,0)
nvl(nullZhi,'erwe')
nvl(nullZhi,'01-JAN-97')
select numbetOne+nvl(numberTwo,0) as number from mytable;
nvl2(expr1,expr2,expr3)函数：
和nvl函数相似，当expr1不为null时，返回expr2；为null时，返回expr3；
nullif(expr1,expr2)：
相等返回null，不等返回expr1；
coalesce(expr1,expr2,....,exprn)：
COALESCE与NVL相比的优点在于 COALESCE可以同时处理交替的多个值。
如果第一个表达式为空,则返回下一个表达式,对其他的参数进行 COALESCE
练习:查询部门号为10.20,30的员工信息,若部门号为10.则打印其工资的1.1倍,20号部门.则打印其工资的12倍,30号部门打印其工资的1.3倍数;
select id,name,case department_id when 10 then salary * 1.1
when 10 then salary * 1.1
when 20 then salary * 1.2
else salary * 1.3 end as new_salary
from emp
where department_id in (10,20,30);
DECODE函数
上面case表达式的例子：
select id,name,decode(department_id,10,salary*1.1,20,salary*1.2,salary*1.3) as new_salary
from emp
where department_id in (10,20,30);
函数练习
1. 显示系统时间(注：日期+时间)
a) select to_char(sysdate,'yyyy-mm-dd hh:mi:ss')
b) from dual
2. 查询员工号，姓名，工资，以及工资提高百分之20%后的结果（new salary）
a) select employee_id,last_name,salary,salary*1.2 "new salary"
b) from employees
3. 将员工的姓名按首字母排序，并写出姓名的长度（length）
a) select last_name,length(last_name)
b) from employees
c) order by last_name asc
4. 查询各员工的姓名，并显示出各员工在公司工作的月份数（worked_month）。
a) select last_name,hire_date,round(months_between(sysdate,hire_date),1) workded_month
b) from employees
5. 查询员工的姓名，以及在公司工作的月份数（worked_month），并按月份数降序排列
a) Select last_name,hire_date,round(months_between(sysdate,hire_date),1) workded_month
b) from employees
c) order by workded_month desc
6. 做一个查询，产生下面的结果
<last_name> earns <salary> monthly but wants <salary*3>
Dream Salary
King earns $24000 monthly but wants $72000
select last_name || ' earns '|| to_char(salary,'$999999')||' monthly,but wants '||to_char(3*salary,'$999999') "Dream Salary"
from employees
7. 使用decode函数，按照下面的条件：
job grade
AD_PRES A
ST_MAN B
IT_PROG C
SA_REP D
ST_CLERK E
产生下面的结果
Last_name Job_id Grade
king AD_PRES A
select last_name "Last_name",job_id "Job_id",decode(job_id,'AD_PRES','A','ST_MAN','B', 'IT_PROG','C', 'SA_REP','D', 'ST_CLERK','E') "Grade"
from employees
8. 将第7题的查询用case函数再写一遍。
a) select last_name "Last_name",job_id "Job_id",case job_id when 'AD_PRES'then 'A'
b) when 'ST_MAN' then 'B'
c) when 'IT_PROG' then 'C'
d) when 'SA_REP' then 'D'
e) when 'ST_CLERK' then'E' end "Grade"
f) from employees
4.多表查询
等值连接
select a.id,a.name,a.count,e.name,e.par from account a,emp e where a.emp_id = e.id;
连接n个表,至少需要n-1个连接条件。例如:连接三个表,至少需要两个连接条件。
2.外连接
内连接:合并具有同一列的两个以上的表的行,结果集中不包含一个表与另一个表不匹配的行；
外连接:两个表在连接过程中除了返回满足连接条件的行以外还返回左(或右)表中不满足条件的行,这种连接称为左(或右)外连接。没有匹配的行时,结果表中相应的列为空(ULD).外连接的 WHERE子句条件类似于内部连接,但连接条件中没有匹配行的表的列后面要加外连接运算符,即用圆括号括起来的加号(+).
左外连接
select a.id,a.name,a.count,e.name,e.par from account a,emp e where a.emp_id = e.id(+);
结果：返回account表中的所有结果集，即使emp表的id值为null
右外连接
select a.id,a.name,a.count,e.name,e.par from account a,emp e where a.emp_id(+) = e.id;
多表查询练习题
1. 显示所有员工的姓名，部门号和部门名称。
a) select last_name,e.department_id,department_name
b) from employees e,departments d
c) where e.department_id = d.department_id(+)
2. 查询90号部门员工的job_id和90号部门的location_id
a) select distinct job_id,location_id
b) from employees e left outer join departments d
c) on e.department_id = d.department_id
d) where d.department_id = 90
3. 选择所有有奖金的员工的
last_name , department_name , location_id , city
select last_name,department_name,d.location_id,city
from employees e join departments d
on e.department_id = d.department_id
join locations l
on d.location_id = l.location_id
where e.commission_pct is not null
4. 选择city在Toronto工作的员工的
last_name , job_id , department_id , department_name
select last_name , job_id , e.department_id , department_name
from employees e ,departments d,locations l
where e.department_id = d.department_id and l.city = 'Toronto' and d.location_id = l.location_id
分组函数
4.分组函数
分组函数就是多行函数；
分组函数作用于一组数据,并对一组数据返回一个值。
目标：
了解组函数。
描述组函数的用途。
使用 GROUP BY 子句对数据分组。
使用 HAVING 子句过滤分组结果集
1.组函数
avg 平均值 ---返回值不为空的记录； count 总数---返回值不为空的记录； max 最大值 min 最小值 stddev 方差（标准差） sum 求和
可以使用nvl函数使分组函数无法忽略空值：nvl(expr,1/0/*)
distinct 关键字：
count(distinct expr) -- 返回expr非空且不重复的记录总数
2.GROUP BY
例子：求出emp表中各部门的平均工资：
select empId,avg(salary) from emp group by empId;
例子：求出emp表中各部门的不同工种的平均工资：
select empId,jobId,avg(salary) from emp group by empId,jobId;
总结：select要查询的列，只要不是函数，就应该出现相爱group by 子句中；反过来说，出现在group by 子句中的可以不出现在select查询的列中；所有包含于 SELECT列表中,而未包含于组函数中的列都必须包含于 GROUP BY子句中。
不能在 WHERE子句中使用组函数。
可以在 HAVING子句中使用组函数。
3.HAVING
例子：求出emp表中各部门中平均工资大于6000的部门，以及其平均工资：
select empId,avg(salary) from emp group by empId having avg(salary) > 6000;
不能在 WHERE子句中使用组函数。
可以在 HAVING子句中使用组函数。
having子句相当于给分组添加条件；
1. 组函数处理多行返回一行吗?
是
2. 组函数不计算空值吗？
是
3. where子句可否使用组函数进行过滤?
不可以，用having替代
4. 查询公司员工工资的最大值，最小值，平均值，总和
a) select max(salary),min(salary),avg(salary),sum(salary)
b) from employees
5. 查询各job_id的员工工资的最大值，最小值，平均值，总和
a) select job_id,max(salary),min(salary),avg(salary),sum(salary)
b) from employees
c) group by job_id
6. 选择具有各个job_id的员工人数
a) select job_id,count(employee_id)
b) from employees
c) group by job_id
7. 查询员工最高工资和最低工资的差距（DIFFERENCE）
a) select max(salary),min(salary),max(salary)-min(salary) "DIFFERENCE"
b) from employees
8. 查询各个管理者手下员工的最低工资，其中最低工资不能低于6000，没有管理者的员工不计算在内
a) select manager_id,min(salary)
b) from employees
c) where manager_id is not null
d) group by manager_id
e) having min(salary) >= 6000
9. 查询所有部门的名字，location_id，员工数量和工资平均值
a) select department_name,location_id,count(employee_id),avg(salary)
b) from employees e right outer join departments d
c) on e.department_id = d.department_id
d) group by department_name,location_id
5.子查询
子查询
目标：
描述子查询可以解决的问题。
定义子查询。
列出子查询的类型。
书写单行子查询和多行子查询。
select select_list from table1 where expr operator ( select select_list2 from table2)
总结:
子查询(内查询)在主查询之前一次执行完成；
子查询的结果被主查询(外查询)使用。
注意事项：
子查询要包含在括号内。
将子查询放在比较条件的右侧。
单行操作符对应单行子查询,多行操作符对应多行子查询。
1.子查询的类型
单行子查询：子查询只返回一条记录；
单行子查询使用的比较符：=、>、>=、<、<=、<>
若要使用分组函数进行判断条件的话，则使用having函数
多行子查询：子查询返回多条记录；
使用多行比较操作符：in、 any、 all
in ---含义：等于列表中的任意一个
any ---含义：和子查询返回的某一个值比较
all ---含义：和子查询返回的所有值比较
多行比较操作符案例：-------
返回其它部门中比 job_id为'T_PROG'部门任一工资低的员工的员工号、姓名、 job_id以及 salary
select id,name,job_id,salary from emp where job_id <> 'T_PROG' and salary < any ( select salary from emp where job_id = 'T_PROG')
1. 查询和Zlotkey相同部门的员工姓名和雇用日期
a) select last_name,hire_date
b) from employees
c) where department_id = (
d) select department_id
e) from employees
f) where last_name = 'Zlotkey'
g) )
h) and last_name <> 'Zlotkey'
2. 查询工资比公司平均工资高的员工的员工号，姓名和工资。
a) select last_name,employee_id,salary
b) from employees
c) where salary > (select avg(salary)
d) from employees)
3. 查询各部门中工资比本部门平均工资高的员工的员工号, 姓名和工资
a) select employee_id,last_name,salary
b) from employees e1
c) where salary > (
d) select avg(salary)
e) from employees e2
f) where e1.department_id = e2.department_id
g) group by department_id
h) )
4. 查询和姓名中包含字母u的员工在相同部门的员工的员工号和姓名
a) select employee_id,last_name
b) from employees
c) where department_id in (
d) select department_id
e) from employees
f) where last_name like '%u%'
g) )
h) and last_name not like '%u%'
5. 查询在部门的location_id为1700的部门工作的员工的员工号
select employee_id
from employees
where department_id in (
select department_id
from departments
where location_id = 1700
)
6.查询管理者是King的员工姓名和工资
select last_name,salary
from employees
where manager_id in (
select employee_id
from employees
where last_name = 'King'
)
6.sql 创建管理表
SQL语句 -- 创建和管理表
SQL语句分为三种类型：
DML: Data Manipulation Language数据操纵语言
DDL: Data Definition Language数据定义语言
DCL: Data Control Language数据控制语言
DML用于查询与修改数据记录,包括如下SQL语句：
INSERT:添加数据到数据库中
UPDATE:修改数据库中的数据
DELETE:删除数据库中的数据
SELECT:选择(查询)数据
SELECT是SQL语言的基础,最为重要。
DDL用于定义数据库的结构,比如创建、修改或删除数据库对象,包括如下SQL语句:
CREATE TABLE:创建数据库表
ALTER TABLE:更改表结构、添加、删除、修改列长度
DROP TABLE:删除表
CREATE INDEX:在表上建立索引
DROP INDEX:删除索引
....
DCL用来控制数据库的访问,包括如下SQL语句:
GRANT:授予访问权限
REVOKE:撤销访问权限
COMMIT:提交事务处理
ROLLBACK:事务处理回退
SAVEPOINT:设置保存点
LOCK:对数据库的特定部分进行锁定
....
目标：
描述主要的数据库对象
创建表
描述各种数据类型
修改表的定义
删除,重命名和清空表
1.数据库对象
对象 描述
表-table 基本的数据存储集合，由行和列组成
视图-view 从表中抛出的逻辑上相关的数据集合
序列 提供有规律的数值
索引 提高查询的效率
同义词 给对象起别名
表：
用户定义的表：
用户自己创建并维护的一组表
包含了用户所需的信息
如, SELECT FROM user tables;查看用户创建的表
数据字典:
由 Oracle Server自动创建的一组表
包含数据库信息
2.创建表
表名和列名命名规则：
必须以字母开头
必须在1-30个字符之间
必须只能包含A-2,a-z,0-9,_,$ 和 #
必须不能和用户定义的其他对象重名
必须不能是 oracle的保留字
具备条件：
CREATE TABLE权限
存储空间
必须指定:
表名
列名,数据类型,尺寸
创建表的两种方式：
一：白手起家，从零创建表，所有的元素都是自定义的
create table tableName( id number(10), name varchar2(20) salary number(10,2) );
二：使用子查询创建表，创建和已有表中部分或全部字段相同的表----可以连数据都复制，但是我们可以添加条件，使得只要表结果而不要数据；
create table tableName as select id,name,date from emp where 2=1;
3.数据类型
数据类型 描述
varchar2(size) 可变长字符数据
char(size) 定长字符数据
number(p,s) 可变长数值数据
date 日期型数据
long 可变长字符数据，最大可达到2G
clob 字符数据，最大可达到4G
raw(long raw) 原始的二进制数据
blob 二进制数据，最大可达到4G
bfile 存储玩不文件的二进制数据，最大可达到4G
rowid 行地址
4.修改表的定义
使用 ALTER TABLE 语句可以:
·追加新的列
·修改现有的列
·为新追加的列定义默认值
·删除一个列
·重命名表的一个列名
创建表：
create table tableName( id number(10), name varchar2(20) salary number(10,2) );
追加新的列：
修改 表 表名 操作 列名 修改目标
alter table test1 add(email VARCHAR2(20));
修改现有的列:
alter table test1 modify(id NUMBER(15));
定义默认值：
设置默认值为2000
alter table test1 modify(salary number(20,2) DEFAULT 2000);
删除一个列：
alter table test1 drop column email;
重命名表的一个列名：
alter table test1 rename column salary to sal;
5.删除表
数据和结构都被删除
所有正在运行的相关事务被提交
所有相关索引被删除
DROP TABLE语句不能回滚
drop table tableName;
例如：drop table test1;
6.清空表
删除表中所有的数据
释放表的存储空间
TRUNCATE语句不能回滚
可以使用 DELETE 语句删除数据,可以回滚
delete from emp;
select * from emp;
rollback;
select * from emp;
清空表语法：
truncate table tableName;
例子：truncate table test1;
7.修改对象的名称
执行 RENAME 语句改变表,视图,序列,或同义同的名称；
但必须是对象的拥有者；
语法：
rename dept to detail_dept;
例子：rename test1 to test2;
有关表的练习
1. 创建表dept1
name Null? type
id Number(7)
name Varchar2(25)
create table dept1(
id number(7),
name varchar2(25)
)
2. 将表departments中的数据插入新表dept2中
a) create table dept2
b) as
c) select * from departments
3. 创建表emp5
name Null? type
id Number(7)
First_name Varchar2(25)
Last_name Varchar2(25)
Dept_id Number(7)
create table emp5(
id number(7),
first_name varchar2(25),
last_name varchar2(25),
dept_id number(7)
)
4. 将列Last_name的长度增加到50
a) alter table emp5
b) modify (last_name varchar2(50))
5. 根据表employees创建employees2
a) create table employees2
b) as
c) select * from employees
6. 删除表emp5
drop table emp5;
7. 将表employees2重命名为emp5
rename employees2 to emp5
8. 在表dept和emp5中添加新列test_column，并检查所作的操作
alter table dept
add(test_column number(10));
desc dept;
9. 在表dept和emp5中将列test_column设置成不可用，之后删除
a) alter table emp5
b) set unused column test_column
alter table emp5
drop unused columns
10. 直接删除表emp5中的列 dept_id
Alter table emp5
drop column dept_id
7.sql语句数据处理
SQL语句 -- 数据处理
目标：
使用DML语句
向表中插入数据
更新表中数据
从表中删除数据
控制事务
事务是由完成若干项工作的DML语句组成的
先创建一个表做实例：
create table tableName( id number(10), name varchar2(20) salary number(10,2) );
1.添加数据
使用 INSERT 语句向表中插入数据。
使用这种语法一次只能向表中插入一条数据。
为每上列添加一个新值。
按列的默认顺序列出各个列的值。
在 INSERT子句中随意列出列名和他们的值
字符和日期型数据应包含在单引号中。
语法一：
insert into tableName[(column [,column...])] values (value [,value...]);
例子：
insert into test1 VALUES(1,'wwj',3000) // 若不指定列，则默认是安照列的顺序添加值，允许为空的列可以设为null；
insert into test1(id,name,salary ) VALUES(1,'wwj',3000) // 按照指定的列名顺序添加值
语法二：
我们也是可以想创建表那样，将已有的表的数据复制的指定的表中；
不必书写VALUES子句。子查询中的值列表应与 INSERT子句中的列名对应；
insert into test1(id,name,salary) select id,name,salary from test1 where id < 3;
2.更新数据
使用 UPDATE语句更新数据。
可以一次更新多条数据。
语法：
update tableName set column=value [,column=value2,...] [where condition]
3.删除数据
使用 DELETE语句从表中删除数据。
如果省略HERE子句,则表中的全部数据将被删除；
语法：
delete from tableName [where conditin]
例子：delete from test1 where id=2;
SQL语句 -- 数据库事务
事务:一组逻辑操作单元使数据从一种状态变换到另一种状态。
数据库事务由以下的部分组成:
一个或多个DM语句
一个DDL( Data Definition Language-数据定义语言)语句
一个DCL( Data Control Language-数据控制语言)语句
一个事务的始终：
以第一个DML语句的执行作为开始
以下面的其中之一作为结束
commit或 ROLLBACK语句
DDL語句(自动提交)
用户会话正常结東
系统异常终止
回滚：
使用 SAVEPOINT语句在当前事务中创建保存点。
使用 ROLLBACK TO SAVEPOINN语句回滚到创建的保存点。
例子：
sevepoint a;
DML语句。。。;
sevepoint b;
DML语句。。。;
rollback to sevepoint b;
提交或回滚前的数据状态：
改变前的数据状态是可以恢复的
执行DML操作的用户可以通过 SELECT语句查询之前的修正
其他用户不能看到当前用户所做的改变,直到当前用户结束事务。
DML语句所涉及到的行被锁定,其他用户不能操作。
提交后的数据状态：
数据的改变已经被保存到数据库中；
改变前的数据已经丢失
所有用户可以看到
结果锁被释放,其他用户可以操作涉及到的数据。
所有保存点被释放。
练习
1. 运行以下脚本创建表my_employees
Create table my_employee ( id number(3),
first_name varchar2(10),
Last_name varchar2(10),
User_id varchar2(10),
Salary number(5));
2. 显示表my_employees的结构
DESC my_employees;
3. 向表中插入下列数据
ID FIRST_NAME LAST_NAME USERID SALARY
1 patel Ralph Rpatel 895
2 Dancs Betty Bdancs 860
3 Biri Ben Bbiri 1100
4 Newman Chad Cnewman 750
5 Ropeburn Audrey Aropebur 1550
INSERT INTO my_employee
VALUES(1,’patel’,’Palph’,’Rpatel’895);
4. 提交
COMMIT;
5. 将3号员工的last_name修改为“drelxer”
UPDATE my_employees
SET last_name = ‘drelxer’
WHERE id = 3;
6. 将所有工资少于900的员工的工资修改为1000
UPDATE my_employees
SET salary = 1000
WHERE salary< 900
7. 检查所作的修正
SELECT * FROM my_employees
WHERE salary < 900
8. 提交
COMMIT;
9. 删除所有数据
DELETE FROM my_employees;
10. 检查所作的修正
SELECT * FROM my_employees;
11. 回滚
ROLLBACK;
12. 清空表my_employees
TRUNCATE TABLE my_employees
8.数据库约束
SQL语句 -- 约束
约束 -- constraint
目标：
描述约束
创建和维护约束
约束是表级的强制规定
有以下五种约束:
not null 不为空
unique 唯一，就是整个表中该列的值不能重复
primary key 主键
foreign key 外键
check 检查条件
注意事项：
如果不指定约束名, Oracle server自动按照 SYS_Cn 的格式指定约束名
创建和修改约束
建表的同时
建表之后
可以在表级或列级定义约束
可以通过数据字典视图查看约束
表级约束和列级约束：
作用范围:
①列级约束只能作用在一个列上
②表级约束可以作用在多个列上(当然表级约東也可以作用在一个列上)
定义方式:列约東必须跟在列的定义后面,表约束不与列一起,而是单独定义。
非空( not null)约束只能定义在列上
使用 constraint 关键字来定义约束的名字
1.在创建表时定义约束
1.not null
create table test2(
id number(20) constraint test2_id_nn not null, -- 定义约束，和使用constraint关键字定义约束名
name varchar2(20) not null,
age number(20)
)
2.unique
唯一约東,允许出现多个空值,null;
create table test3(
-- 列级约束
id number(20) constraint id_unique UNIQUE,
name varchar2(20) UNIQUE,
age number(20),
email VARCHAR2(10),
-- 表级约束
constraint email_uk unique(email)
)
3.primary key
列不允许为空，不允许出现重复值
create table test5(
-- 列级约束
id number(20) constraint test5_pk primary key, -- 设置主键
name varchar2(20) UNIQUE,
age number(20),
email VARCHAR2(10),
-- 表级约束
-- primary key(id)
)
4.foreign key
外键
create table test6(
-- 列级约束
id number(20) constraint test6_pk primary key, -- 设置主键
name varchar2(20) UNIQUE,
age number(20),
email VARCHAR2(10),
depar_id number(20),
-- 表级约束
-- 说明，定义列depar_id为外键，其名名为test6_fk，同时是相对于test6表（相当于是父表了）的id列对应的；
constraint test6_fk foreign key(depar_id) references test6(id)
)
在插入一条数据时，不允许外键的值不再对应的表（test6）的对应的列（id）中；
FOREIGN KEY:在表级指定子表中的列
REFERENCES:标示在父表中的列
ON DELETE CASCADE(级联删除):当父表中的列被別除时,子表中相对应的列也被删除
ON DELETE SET NULL(级联置空):当父表中的列被別除时,子表中相应的列置空
create table test6(
-- 列级约束
id number(20) constraint test6_pk primary key, -- 设置主键
name varchar2(20) UNIQUE,
age number(20),
email VARCHAR2(10),
depar_id number(20),
-- 表级约束
-- 说明，定义列depar_id为外键，其名名为test6_fk，同时是相对于test6表（相当于是父表了）的id列对应的；并设置级联置空
constraint test6_fk foreign key(depar_id) references test6(id) ON DELETE SET NULL
)
5.check
定义每一行必须满足的条件；
语法：
....,salary number(2) constraint emp_sal_min check (salaary > 0), ....
2.建表之后修改约束
添加约束语法：
使用 ALTER TABLE 语句:
添加或删除约束,但是不能修改约東
有效化或无效化约束
添加 NOT NULL约束要使用 MODTEY语句
alter table test2 modify(id number(20) not null)
添加或删除约束语法：
删除：
alter table talbeName drop constraint constraintName;
添加：
alter table tableName add [constraint constraintName] constraintType(columnName);
有效化或无效化约束:
在 ALTER TABLE语句中使用 DISABLE 子句将约束无效化。
无效化:
alter table tableName disable constraint constraintName;
在 ALTER TABLE语句中使用 ENABLE 子句将约束有效化。
无效化:
alter table tableName enable constraint constraintName;
练习
1. 向表emp2的id列中添加PRIMARY KEY约束（my_emp_id_pk）
ALTER table emp2
ADD constraint my_emp_id_pk primary key(id);
2. 向表dept2的id列中添加PRIMARY KEY约束（my_dept_id_pk）
ALTER table dept2
ADD constraint my_dept_id_pk primary key(id)
3. 向表emp2中添加列dept_id，并在其中定义FOREIGN KEY约束，与之相关联的列是dept2表中的id列。
ALTER table emp2
ADD (dept_id number(10) constraint emp2_dept_id_fk references dept2(id));
视图
创建、修改和删除
创建视图语法:
--创建视图语法：
create view viewName as select columnName [,columnName as aliasName...] fron tableName [where ...];
修改视图语法：
使用 CREATE OR REPLACE VIEW 子句修改视图;
CREATE VIEW 子句中各列的别名应和子查询中各列相对应
create or replace view viewName as select ....
--说明：也就说视图修改，和创建视图语法类似，只不过是在create后面加上or replace；
删除视图语法：
drop view viewName;
2.视图的增删改查
--增 ，和操作表一样
insert into viewName [(columnName [,columnName...])] values(columnName [,columnName...]) [where ...];
--删 ，和操作表一样
delete from viewName [where ...];
--改 ，和操作表一样
update viewName set columnName=columnValue [,columnName=columnValue...] [where ...];
--查 ，和操作表一样
select columnName from viewName [where ...];
3.屏蔽DML操作
可以使用 with read only 选项屏蔽对视图的DML操作
任何DML操作都会返回一个 Oracle server错误
说明，执行此操作后，用户就只能进行查询操作，而不能进行增删改操作了；
方法：
-- 语法
create or replace view viewName as select .... with read only;
或
-- 语法
create view viewName as select .... with read only;
取消此屏蔽操作方法：
-- 语法
create or replace view viewName as select .... ;
-- 只要使用修改操作，并且不要 with read only 子句即可
4.视图类别
分简单视图和复杂视图：
特性 简单视图 复杂视图
表的数量 一个 一个或多个
函数 没有 有
分组 没有 有
DML操作 可以 有是可以
区分是简单视图还是复杂视图，主要靠有没有分组函数；
1.视图中使用DML操作规定
可以在简单视图中执行DML操作
当视图定义中包含以下元素之一时，不能使用 delete、update、insert：
组函数
GROUP BY 子句
DISTINCT 关键字
rownum 伪列 --- 查询数据库后的结果集自动的行数；说明: rownum伪列数据表本身并没有这样的列,是 oracle数据库为每个数据表“加上的”列可以标识行号。默认情况下 rownum按主索引来排序,若没有主素引则自然排序
select rownum,id,name from test1;
5.top-n分析
分页技术的底层实现；可以只获取所有查询到的结果集中的部分记录，即只截取想要的记录；
1.分页
案例：
查询找所有结果中总数量最高的前六个：
-- 例子
select rownum,id,name,count from (
select id,name,count from view1 order by count DESC
)
where rownum <= 6;
对rownum只能使用 <或 <= ,而用 = , > ,>= 都将不能返回任何数据。
对rownum只能使用 <或 <= ,而用 = , > ,>= 都将不能返回任何数据。
对rownum只能使用 <或 <= ,而用 = , > ,>= 都将不能返回任何数据。
针对于伪列不能使用 = , > ,>= 比较符的问题，我们有几种解决方法：
1.居然伪列不能够使用 = , > ,>= 比较符，那么我们把结果集的伪列变成真实的列，那么不久解决了问题么，嘿嘿嘿嘿：
-- 例子
select num,id,name,count from(
select rownum as num,id,name,count from (
select id,name,count from view1 order by count DESC
)
where rownum <= 6
)
where num <= 6 and num >3;
视图练习
1. 使用表employees创建视图employee_vu，其中包括姓名（LAST_NAME），员工号（EMPLOYEE_ID），部门号(DEPARTMENT_ID).
a) create or replace view employee_vu
b) as
c) select last_name,employee_id,department_id
d) from employees
2. 显示视图的结构
desc employee_vu;
3. 查询视图中的全部内容
SELECT * FROM employee_vu;
4. 将视图中的数据限定在部门号是80的范围内
a) create or replace view employee_vu
b) as
c) select last_name,employee_id,department_id
d) from employees
e) where department_id = 80
5. 将视图改变成只读视图
create or replace view employee_vu
as
select last_name,employee_id,department_id
from employees
where department_id = 80
with read only
序列
1.序列
序列:可供多个用户用来产生唯一数值的数据库对象
自动提供唯一的数值
共享对象
主要用于提供主键值
将序列值装入内存可以提高访问效率
1.创建序列
-- 语法：
create sequence sequenceName
[increment by n] -- 每次增长的数值
[start with n] -- 从那一个值开始
[{maxvaluee n | nomaxvalue}] -- 最大值
[{minvaluee n | nominvalue}] -- 最小值
[{cycle | nocycle}] -- 是否需要循环
[{cache n | nocache}]; -- 是否缓存登录
例子：
create sequence empseq
increment by 10 -- 每次增长10
start with 10 -- 从10开始增长
maxvalue 100 -- 提供的最大值
cycle -- 需要循环
nocache -- 不需要缓存登录
2.使用序列
序列不向表、视图那样，使用select子 * from ... 来查询等操作，序列只能使用内部提供的两个方法：nextval、currval
SELECT empseq.nextval from account; -- 表示下一个值
SELECT empseq.currval from test1; -- 表示当前值
若想在创建序列之后使用序列，则必须先调用empseq.nextval方法，才能使用empseq.currval方法；
居然序列主要是提供的主键值，那么我们在想数据库添加数据时就能直接的使用序列来作为主键了：
insert into emp values(empseq.nextval,'wwj',333);
注意：
将序列值装入内存可提高访问效率
序列在下列情况下出现裂缝:
回滚
系统异常
多个表同时使用同一序列
如果不将序列的值装入内存( NOCACHE),可使用表 USER_SEQUENCES查看序列当前的有效值
3.修改序列
和修改表一样：
-- 例子
alter sequence empseq
increment by 10 -- 每次增长10
start with 10 -- 从10开始增长
maxvalue 100 -- 提供的最大值
cycle -- 需要循环
nocache; -- 不需要缓存登录
注意事项：
必须是序列的拥有者或对序列有 ALTER权限
只有将来的序列值会被改变
改变序列的初始值只能通过删除序列之后重建序列的方法实现
4.删除序列
-- 语法
drop sequence sequenceName
序列练习
1. 创建序列dept_id_seq，开始值为200，每次增长10，最大值为10000
a) create sequence dept_id_seq
b) start with 200
c) increment by 10
d) maxvalue 10000
2. 使用序列向表dept中插入数据
a) insert into dept01
b) values(dept_id_seq.nextval,'Account')
附：
create table dept as
select department_id id,department_name name
from departments
where 1=2
索引
2.索引
一种独立于表的模式对象,可以存储在与表不同的磁盘或表空间中
素引被删除或损坏,不会对表产生影响,其影响的只是查询的速度
素引一旦建立,orac1e管理系统会对其进行自动维护,而且由orac1e管理系统决定何时使用素引。用户不用在查询语句中指定使用哪个素引
在除一个表时,所有基于该表的素引会自动被除
通过指针加速orac1e服务器的查询速度
通过快速定位数据的方法,减少磁盘1/O
1.创建索引
自动创建:在定义 PRIMARY KEY或UNIQUE约束后系统自动在相应的列上创建唯一性素引
手动创建:用户可以在其它列上创建非唯一的索引,以加速查询
什么时候创建索引:
以下情况可以创建索引:
列中数据值分布范围很广，即数值范围大
列经常在 WHERE 子句或连接条件中出现
表经常被访问而且数据量很大,访问的数据大概占数据总量的2%到4%
什么下列情况不要创建素引:
表很小
列不经常作为连接条件或出现在 WHERE子句中
查询的数据大于2%到4%
表经常更新
-- 语法
create index indexName on tableName(columnName);
--例子：
create index test1_index on test1(id);
2.删除索引
-- 语法
drop index indexName;
3.同义词
同义词 -- synonyn
使用同义词访问相同的对象:
方便访问其它用户的对象
缩短对象名字的长度
语法：
create [public] synonyn synonynName for object;
drop synonyn synonynname;
例子：
create synonyn t for test1;
select * from t where id = 2;
用户权限
十、控制用户权限
目标：
创建用户
创建角色
使用 grant和 revoke语句赋予和回收权限
创建数据库联接
权限：
数据库安全性:
系统安全性
数据安全性
系统权限:对于数据库的权限
超过一百多种有效的权限
数据库管理员具有高级权限以完成管理任务,例如:
创建新用户
除用用户
删除表
备份表
.....
对象权限:操作数据库对象的权限
1.创建新用户
-- 语法：
create user userName indentified by password;
--例子：创建一个名为zhangsan的用户，密码为123456
create user zhagnsan indentified by 123456;
1.修改密码
DBA可以创建用户和修改密码
用户本人可以使用 ALTER USER语句修改密码
-- 语法
alter user userName indentified by password;
-- 例子
alter user zhangsan indentified by 66666666;
2.权限
以应用程序开发者为例,一般具有下列系统权限:
CREATE SESSION (创建会话)
CREATE TABLE (创建表)
CREATE SEQUENCE (创建序列)
CREATE VIEW (创建视图)
CREATE PROCEDURE (创建过程)
1.赋予系统权限
语法：
grant privilege [,privilege... ] to user [,ser |role,public... ];
1.赋予连接权限
-- 例子
grant create session to zhagnsan;
2.赋予用户表空间
用户拥有 create table权限之外,还需要分配相应的表空间才可开辟存储空间用于创建的表
-- 例子
alter user zhangsan quota unlimited to users;
-- 将用户zhangsan添加到users表空间，同时有附加项为 unlimited(无限)
alter user zhangsan quota 5m to users;
-- 将用户zhangsan添加到users表空间，同时有附加项为 5m 的使用空间
3.撤销系统权限
--语法：
revoke privilege [,privilege... ] from user [,ser |role,public... ];
2.分配对象权限
对象权限：
不同的对象具有不同的对象权限
对象的拥有者拥有所有权限
对象的拥有者可以向外分配权限
语法：
grant objct_priv [(columns)] on object to {user|role|public};
-- 说明：将object对象的objct_priv权限授权给{user|role|public}
-- 例子：
grant select on scott.emp to test; --授权test查找scott.emp表的权限
1.撤销对象权限
--语法
revoke {privilege [, privilege...]|all} --on object --from {user[, user...]|role|public} -- [cascade constraints];
-- 例子：
revoke update(sal,mgr) on scott.emp from test; -- 从test用户中撤销scott.emp表的跟新权限
3.角色
1.创建角色
create role roleName;
--例子：
create role managerRole;
2.给角色赋予权限
-- 例子
grant create table,create view to managerRole;
3.将角色赋予用户
-- 例子
grant managerRole to zhangsan,test1;
4.删除角色
drop role roleName;
-- 例子：删除角色managerRole
drop role managerRole;
高级子查询
高级子查询
目标：
书写多列子查询
在 from 子句中使用子查询
在sql中使用单列子查询
书写相关子查询
使用 exists 和 exists 操作符
使用子查询更新和删除数据
使用网with子句
总结：就是子查询能在sql语句中的任何位置都能使用；
子查询是嵌套在SL语句中的另一个 SELECT语句
1.多列子查询
主查询与子查询返回的多个列进行比较
例子：
-- 例子：查询与147好或174好员工的manager_id和depar_id相同的其他员工的emp_id,manager_id,depar_id;
select emp_id,manager_id,depar_id
from emp
where manager_id in (
select manager_id from emp where emp_id in (147,174)
)
and depar_id in (
select depar_id from emp where emp_id in (147,174)
)
and emp_id not in ()(147,174);
-- *************** 上面使用的是单列子查询的方法，下面使用多列子查询的方法，看看有什么不同
select emp_id,manager_id,depar_id
from emp
where (manager_id,depar_id) in (
select manager_id,depar_id from emp where emp_id in (147,174)
)
and emp_id not in ()(147,174);
2.在 from 子句中使用子查询
说明：一般的from子句后面跟着的都是一个实体的表，但是，我们也是可以将查询得到的结果集当成是一个表；
--例子：
select id,name from account aa (
select * from test1
) as tt
where tt.id = aa.id;
3.相关子查询
相关子查询按照一行接一行的顺序执行,主查询的每一行都执行一次子查询
-- 例子：查询员工的emp_id,last_name，要求按照员工的depar_name排序
select emp_id,last_name
from emp
order by (
select depar_name
from depar
where emp.depar_id = depar.depar_id
) asc
4.exists操作符
EXISTS操作符检查在子查询中是否存在满足条件的行
如果在子查询中存在满足条件的行:
不在子查询中继续查找
条件返回TRUE
如果在子查询中不存在满足条件的行
条件返回 FALSE
继续在子查询中查找
--例子：查询公司管理者的emp_id，last_name，job_id，depar_id信息；其中员工表中有管理者的id号
select emp_id,last_name,job_id,depar_id
from emp
where exists (
select 'A'
from emp e
where emp.emp_id = e.manager_id
)
not exists 和exists作用相反；
5.相关更新
使用相关子查询依据一个表中的数据更新另一个表的数据
--语法
update tableName aliasl1 set column = (
select expr from tableName aliasl2
where aliasl1.column = aliasl2.column
);
6.相关删除
使用相关子查询依据一个表中的数据删刷除另一个表的数据
语法和相关更新类似；
7.with子句
使用WITH子句,可以避免在 SELECT语句中重复书写相同的语句块
with子句将该子句中的语句块执行一次并存储到用户的临时表空间中
使用with子句可以提高查询效率
--例子：查询公司中比big的工资高的员工信息
with emp_big as (
select salary
from emp
where last_name = 'big' -- 这还是简单的，当查询条件多的时候，这个就发挥大作用了
)
select emp_id,salary
from emp
where salary > (
select salary
from emp_big
);
plsql
PL/SQL
面向过程编程语言
-- 语法
DECLARE
-- 声明部分(变量的声明、游标的声明(cursor)、例外的声明(异常))
BEGIN
-- 语句序列(DML语句)
EXCEPTION
-- 例外处理语句
END;
声明部分简介
说明变量
DECLARE
-- 说明变量：char、varchar2、date、number、boolean、long
v_num number;
-- 赋值
v_char varchar2(24) := 'it is good';
BEGIN
-- 赋值
v_num := 10;
-- 拼接结果
dbms_output.put_line('hello, world' || '! v_num=' || v_num || ', v_char=' || v_char);
END;
引用变量
DECLARE
-- 引用变量：v_name所对应的类型与emp表中ename字段的类型相同
v_name emp.ename%type;
BEGIN
-- 在SQL中使用 into 来赋值
select ename into v_name from emp where empno=7369;
-- 拼接结果
dbms_output.put_line('名字为：' || v_name);
END;
记录型变量
DECLARE
-- 记录型变量
v_row emp%rowtype;
BEGIN
SELECT * INTO v_row FROM emp WHERE empno = 7369;
dbms_output.put_line('名字为：' || v_row.ename);
END;
if
语法
语法：
[1] IF 条件 THEN
code....;
END IF;
[2] IF 条件 THEN
code....;
ELSE
code....;
END IF;
[3] IF 条件 THEN
code....;
ELSIF 条件 THEN
code...
ELSE
code....;
END IF;
例：
DECLARE
v_date VARCHAR2(24);
BEGIN
SELECT trim(to_char(SYSDATE, 'dy')) INTO v_date FROM dual; -- 当天属于周几
IF v_date IN ('sun', 'sat') THEN
dbms_output.put_line('在中国今天是周末哦!');
ELSE
dbms_output.put_line('在中国今天是工作日哦!');
END IF;
END;
循环
语法
语法：
[1] WHILE 条件
LOOP
code...;
END LOOP;
[2] LOOP
EXIT WHEN 条件
code....
END LOOP;
[3] FOR i IN 1...n
LOOP
code...
END LOOP;
例：
-- 输出1-10
-- 法一：
DECLARE
v_tag NUMBER := 1;
BEGIN
WHILE v_tag < 10
LOOP
dbms_output.put_line(v_tag);
v_tag := v_tag + 1;
END LOOP;
END;
-- 法二：
DECLARE
v_tag NUMBER := 1;
BEGIN
LOOP
EXIT WHEN v_tag >= 10;
dbms_output.put_line(v_tag);
v_tag := v_tag + 1;
END LOOP;
END;
-- 法三：
DECLARE
BEGIN
FOR i IN 1..10
LOOP
dbms_output.put_line(i);
END LOOP;
END;
游标
概念
在Java中有集合的概念，那么在plsql中也会有用到多条记录的情况，这时候我们就要用到游标。游标可以存储查询返回的多条数据。
语法
-- 语法：
CURSOR 游标名 [(paramName paramType, ....)] IS SELECT 语句...;
取值步骤
打开游标 open cursor
开启循环 loop;
取值 fetch 游标名称 into 变量名
结束循环 end loop;
关闭游标 close 游标名称
范例一(普通游标)
-- 使用游标方式输出员工编号和姓名
DECLARE
pemp emp%ROWTYPE;
-- 定义游标
CURSOR pc IS SELECT * FROM emp;
BEGIN
-- 打开游标
OPEN pc;
-- 取数据
LOOP
FETCH pc INTO pemp;
EXIT WHEN pc%NOTFOUND;
dbms_output.put_line(pemp.empno || '-' || pemp.ename);
END LOOP;
END;
范例二(系统引用游标)（推荐）
--输出所有员工的名称与工资
DECLARE
mycursor1 SYS_REFCURSOR;
vrow emp%ROWTYPE;
BEGIN
OPEN mycursor1 FOR SELECT * FROM emp;
LOOP
FETCH mycursor1 into vrow;
EXIT WHEN mycursor1%NOTFOUND;--%notfound，游标中已经取到最后，没有数据了
dbms_output.put_line(' 员工姓名:' || vrow.ename || ' 工资:' || vrow.sal);
END LOOP;
CLOSE mycursor1;
END;
存储过程
在大学数据库中，一组为了完成特定功能的SQL语句集。经编译后储存在数据库中，用户通过指定的存储过程的名字给出参数来执行它。存储过程是数据库中一个重要的对象，任何一个设计良好的数据库程序都应该要使用到存储过程。
创建存储过程的语法
-- 方式一：
CREATE OR REPLACE PROCEDURE 过程名(参数名 IN/OUT 数据类型)
AS
code...
BEGIN
code...
END;
-- 方式二：
CREATE OR REPLACE PROCEDURE 过程名(参数名 IN/OUT 数据类型)
IS
code...
BEGIN
code...
END 过程名;
例：
-- 方式一
CREATE OR REPLACE PROCEDURE pro_t1(tager IN NUMBER) AS
BEGIN
dbms_output.put_line(tager);
END;
CALL pro_t1(23);
-- 方式二
CREATE OR REPLACE PROCEDURE pro_allEmp
IS
v_row emp%rowtype;
begin
SELECT * INTO v_row FROM emp WHERE empno = 7369;
dbms_output.put_line('名字为：' || v_row.ename);
END pro_allEmp;
CALL pro_allEmp();
存储函数
游标：
plsql异常处理机制
触发器
Oracle触发器部分
1.触发器的概念
触发器是一个特殊的存储过程。是一个与表相关联的、存储的PL/SQL程序。
作用：每当一个特定的数据操作语句（insert、update、delete，注意没有select）在指定的表上发出时，oracle自动地执行触发器中定义的语句序列。
2.触发器的类型
语句级的触发器
在指定的操作语句操作之前或之后执行一次，不管这条语句影响了多少行。
语句级触发器针对表，只会触发一次
行级的触发器
触发语句作用的每一条记录都被触发。在行级触发器中使用:old和:new伪记录变量，识别值的状态。如果有for each row就表示行级触发器。 如
insert into emp10 select * from emp where deptno=10;
会查出3条记录。
行级触发器针对行，有多少条记录就触发多少次。
3.第一个触发器
每当成功插入新员工后，自动打印一句话，"成功插入新员工"。单词trigger
例如：
create trigger saynewemp
after insert
on emp
declare
begin
dbms_output.put_line('成功插入新员工');
end;
/
4.触发器的具体应用场景
（1）复杂的安全性检查 --比如周末不允许操作数据库
（2）数据的确认 --涨后的工资大于涨前的工资
（3）数据库审计 --跟踪表上所做的数据操作，什么时间什么人操作了什么数据，操作的人是什么。基于值的审计
（4）数据的备份和同步 --异地备份，把主数据的数据自动同步到备数据库中
5.创建触发器的语法
create [or replace] trigger 触发器名
{before|after}
{delete|insert|update [of 列名]}
on 表名
[for each row [when 条件]]
plsql块
6.触发器的案例
触发器案例一：禁止在非工作时间插入数据
触发器案例二：涨工资不能越涨越少 :old和:new的使用要注意。
触发器案例三：创建基于值的触发器
触发器案例四：数据库的备份和同步 --利用触发器实现数据的同步备份，多用于异地分布式数据库 还能使用快照备份，快照备份是异步备份，而触发器是同步备份，没有延时的
782
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
