oracle学习笔记-转

一.数据持久化操作


文本文件缺点 -- 存放数据量少，不安全，不能对数据类型，数据的合法性检查，没有


完善的备份和回复机制。


二. RDBMS（relationship database management system）关系型数据库管理系统


是一个用来管理和存储数据的软件，简称为数据库（db）


1.是一个公开的软件，合法用户都可以访问里面的数据


2.常见产品：Oracle SqlServer（微软） MySql DB2（IBM)


3.使用OracleXe , 运行方式


使用client --》 server工作方式，客户端和服务器之间靠sql命令进行通信


client端：


1）sqlplus -- Oracle提供的基于dos界面的client。


登陆：sqlplus 用户名/密码


退出：exit 或者 ctrl+c


2）isqlplus -- Oracle提供的基于浏览器的client，采用http协议通信


在浏览器地址栏输入 --》 http://127.0.0.1:8080/apex


三. 命令的书写


1. sqlplus命令：Oracle提供的用来操作sqlplus工具的命令


eg：desc -- 用来显示数据库里表的结构


desc 表名


2. SQL命令：structure query language结构化的查询语言，操作管理数据库数据的命令


3. PLSql命令：oracle提供的用来操作管理数据的命令。


四. 数据库里的常见概念


1. 表（table）：逻辑上用来在db里存储数据的单元，也称为“实体集”entitySet


2. 列（column）：代表所放数据的一个属性值，也称为“字段”field


3. 行（row）： 代表一条完整的具体的数据，也称为“实体”entity


4. 主键（primary key）：可以唯一标识表里的一条记录，非空并且唯一


5. 外键（foreign key）：用来体现本表记录和其他表记录之间的关系


五. SQL命令 -- select 【重点】


语法： select 字段名1，字段名2，字段名3 .... from 表名；


注意 -- select是查询命令，sql命令不区分大小写，以“；”为结尾


1. 查询多个字段


eg：请查询employees表里的员工编号，姓名，工资，所在部门编号


select employee_id,last_name,salary,department_id from employees;


2. 查询表里所有的字段


-- select employee_id,first_name,last_name,email,phone_number,..... from employees;


-- select * from employees;


注意：实际开发中不建议使用“*”，语义不明确，效率差


3. 允许对查询结果里的字段进行数学运算： + - * /


-- 请查询员工的编号，姓名，年薪，部门编号


select employee_id,last_name,salary*12,department_id from employees;


注意：


1）字符串类型不能应用 + 运算


2）日期类型值可以做 + - 操作，计算时以“天”为单位


4. 允许为查询结果里的字段起别名


语法： select 字段名 别名，字段名，字段名 别名 .... from 表名；


-- 请查询员工的编号，姓名，年薪，部门编号


select employee_id id,last_name name,salary*12 aSalary,department_id from employees;


注意：如果别名里含有特殊字符 或者 需要区分别名的大小写，可以对别名添加“双引号”


5. 字符串连接符号： ||


-- 请查询员工编号，完整姓名(按中国习惯显示)


select employee_id id , last_name||’-’|| first_name “name”from employees;


注意：如果在sql命令里使用了字符串常量值或者日期值，需要用单引号引起来。


6. 关键字distinct：用来去除查询结果里的重复数据


语法 ： select distinct 字段... from 表名;


-- 请查询公司里所有的job_id


select distinct job_id from employees;


select distinct job_id,department_id from employees; -- 职位和部门联合唯一


六. 对查询结果排序： order by 【重点】


语法：select ... from ... order by 排序条件 [asc（默认）/desc]；


1. 排序条件可以通过“字段名”，“字段别名”，“结果里字段的编号”指定


-- 请查询员工编号，姓名，工资，部门编号，并对结果按工资升序排列


select employee_id,last_name,salary,department_id from employees order by salary asc;


(其中asc可以省略)


select employee_id,last_name,salary sa,department_id from employees order by sa;


select employee_id,last_name,salary sa,department_id from employees order by 3;


2. 按多个字段排序


-- 请查询员工编号，姓名，工资，部门编号，对结果按照部门编号升序排列，如果


编号相同，在按照工资降序排列


select employee_id,last_name,salary,department_id from employees


order by department_id,salary; --- 先升在升


order by department_id,salary desc; --- 先升后降


order by department_id desc,salary desc; --- 全降


注意：oracle里null是最大值


七. 查询判断语句：where 【重中之重】


语法：select ... from ... where ... order by ... “逐行筛选判断”


1. 等值判断： = !=


-- 请查询60部门的所有员工信息


select * from employees where department_id=60;


-- 请查询first_name叫Steven的员工信息


select * from employees where first_name=’Steven’;


注意：出现在单引号里的值严格区分大小写


2. 一般的比较： > >= < <= and or


-- 查询工资在5000到10000之间的员工，不包括边界值


select * from employess where salary>5000 and salary<10000;


-- 查询90和80部门的人


select * from employees where department_id=90 or department_id=80


-- where department_id=90 and salary>5000;


90部门里工资大于5000的人


-- where department_id=90 or salary>5000;


90部门所有的人和公司里工资大于5000的所有人


3. between 小值 and 大值 ； 包括边界值 【了解】


-- 查询工资在5000到10000之间的员工，包括边界值。


select * from employees where salary between 5000 and 10000;


4. in(v1,v2,.....) : 枚举查询条件 【了解】


-- 请打印50,70,90部门的员工信息


select * from employees where department_id=50 or department_id=70 or ...... ;


select * from employees where department_id in(50,70,90);


5. null值处理: is null , is not null


-- 查询提成为null的员工


select * from employees where commission_pct = null; --- error


select * from employees where commission_pct is null; --- ok


注意：null不能出现在数学表达式里，如果出现结果为null。


6. 模糊查询： like ‘查询条件’


查询条件由：字符值 + 通配符


sql中的通配符：%表示0~n个字符 _ 表示有且只有一个字符


-- 查询last_name是以’S’开头的员工信息


select * from employees where last_name like ‘S%’;


-- 查询last_name是由4个字母组成的员工


select * from employees where last_name like ‘____’;


特例：


1）update employees set first_name=’S_abc’where employee_id=128;


2）请查询first_name是以‘S_’开头的员工信息。


select * from employees where first_name like ‘S_%’; ---- error


select * from employees where first_name like ‘S\_%’escape ‘\’;


转义字符 --》将后面紧跟的字符就按普通字符处理，没有任何特殊用途


在oracle里没有固定的转义字符，使用时需要用escape声明。


八. case...when 分支条件判断


语法： case when 条件1 then 结果1


when 条件2 then 结果2


.........


else 结果n


end


注意：


1) 语法结构只会得到一个值；某一个分支一旦被执行，后面的其他分支不在执行。


2) 没有else，而且所有分支都不满足，则结构的结果为null；


3）所有结果类型必须一致


-- 请查询员工的编号，姓名，工资，以及工资的级别


select employee_id,last_name,salary,case when salary<=5000 then ‘low’when salary<=10000 then ‘middle’else ‘high’end from employees;


复习：


1. 数据库的组成：db server ----- db client


2. 查询命令 -- select


select ... from ... where ... order by


3. case ... when


case when boolean条件 then 结果1 when 条件2 then 结果2 ... else 结果n end


九. 函数（仅限oracle里使用）


函数分类：单行函数 and 组函数


(一)单行函数 -- 针对表里的每一行数据，执行一次


eg -- abs(数字) 计算数字的绝对值


select employee_id,last_name,abs(salary) from employees;


select abs(-1) from employees;


-- dual : 哑表（单行单列），属于sys，意义在于维护select语句的完整性，是的命令执


行一次。


select abs(-1) from dual;


1.针对字符串类型的函数


1) length(str) -- 计算str的长度 【重点】


-- 请打印last_name是由4个字母组成的员工信息


select * from employees where last_name like ‘____’;


select * from employees where length(last_name)=4;


2) substr(str,begin,length) -- 对str进行截取，从begin处开始，截取length个字符。str下


标从1开始


-- 请打印last_name是以‘S’开头的员工信息


select * from employees where last_name like ‘S%’;


select * from employees where substr(last_name,1,1)=’S’;


-- select substr(‘String’,4,3) from dual; ----- 结果：ing


select substr(‘String’,-4,3) from dual; ----- 结果：rin


3) instr(str1,str2,begin) -- 在str1里查找str2，从begin处开始，找到返回str2首字母下标，


否则返回0


-- select instr(‘abcdbcdefg’,’bc’,1) from dual; --- 结果：2


-- 请查询last_name里包含a字母的员工信息


select * from employees where last_name like ‘%a%’;


select * from employees where instr(last_name,’a’,1)!=0;


4) lower/upper -- 转换大小写


-- 请打印姓名是以‘S’或者‘s’开头的员工信息


select * from employees where lower( substr(last_name,1,1) ) = ‘s’;


-- 请用小写字母显示员工姓；


select employee_id,lower( last_name) from employees;


2.针对数字类型操作的函数


1) abs(num) -- 求num的绝对值


2) mod(num1,num2) -- 求num1模num2的结果（取余数）


3) round(num1,num2) -- 对num1四舍五入，保留小数点后num2个位数


trunc(num1,num2) -- 对num1直接截断，保留小数点后num2个位数


-- select round(3.14159,3) from dual; -----> 3.142


select trunc(3.14159,4) from dual; ----> 3.1415


select round(3.1415) from dual; ---> 3


select round(15.1415,-1) from dual; ---> 20


4) dbms_random.random() -- 获得一个随机数（很大的整数，可正，可负）【重点】


-- select dbms_random.random() from dual;


-- 请获取一个0~100之间的随机数


select mod( abs( dbms_random.random() ) ,101 ) from dual;


-- 请随机打印employees表里的一行数据（重点：如何获取一个100~207之间的随机数）


select * from employees


where employee_id= mod( abs( dbms_random.random() ) ,108 )+100 ;


3.针对日期类型操作的函数


1) sysdate : 表示当前系统时间 【重点】


-- select sysdate from dual;


注意：oracle里日期值默认官方格式“dd-mon-rr”


2) add_months(time,num) -- 在time基础上加上num个月


3) last_day(time) -- 求time当前月的最后一天


4) months_between(time1, time2) -- 求time1和time2之间间隔几个月，结果为小数


5) round(time,”条件”) -- 对time按条件四舍五入


trunc(time,”条件”) -- 对time按条件截断


条件可选值 ： year month day 什么都不写默认回到当前天的0时


-- 请打印2月的最后一天


select last_day( add_months( trunc(sysdate,’year’) ,1 ) ) from dual;


4.类型转换函数


1) to_number(str,’格式’) -- 将给定的str按格式变成数字值


-- 请求公司员工的日薪


select to_number(‘$33,330.00’,’$99,999.00’) from dual;


2) to_char() -- 将指定类型的值变成字符串


to_char(num,’格式’) -- 将数字变成string


-- 请用科学计数法显示公司员工的工资


select employee_id,to_char(salary,’$999,999.00’) from employees;


注意：占位可以用9或者0,用0时会补齐位数


如果范围不够，则显示########


to_char(time,’格式’) -- 将标准日期格式值变成指定样子的string表示。 【重点*****】


常见日期格式：


-- 年 ： yy yyyy rr rrrr year


-- 月 ： mm mon month


-- 日 ： dd（08） ddsp (eight) ddth (08th) ddspth( eighth)


-- 星期：d dy(星期缩写) day


-- 小时：hh ~ am hh24


-- 分钟：mi


-- 秒： ss


作用：


a. 详细显示时间


-- 请显示详细的系统时间


select to_char(sysdate,’yyyy-mm-ddth,day,hh:mi:ss am’) from dual;


b. 可以提取日期时间里的任意部分 *******


-- 请打印9月入职的员工信息


select * from employees where hire_date like ‘%-9月-%’;


select * from employees where to_char(hire_date,’mm’) = ‘09’;


3) to_date(str,‘str的格式说明’) -- 将时间的字符串表现形式转换成数据库认可的标准日期


-- 打印72年2月的最后一天


select last_day ( to_date(‘72-02-13’,’rr-mm-dd’) ) from dual;


5. nvl(v1,v2) -- 处理null值的函数。如果v1不为空，函数结果是v1，反之结果是v2


-- select nvl(‘hehe’,’haha’) from dual; ------> hehe


select nvl(null,’haha’) from dual; -----> haha


-- 请打印员工每月的总收入


select employee_id,last_name, salary+salary*commission_pct from employees;


select employee_id,last_name, salary+salary*nvl(commission_pct,0) from employees;


(二）组函数：作用于分好的每一组，执行一次


常见组函数：


1. sum(): 求和，只能应用于数字类型


2. avg(): 求平均值，只能应用于数字类型


3. max()： 求最大值，可以应用于所有数据类型


4. min()：求最小值，可以应用于所有数据类型


5.count(): 计数器，统计组里指定值的个数


注意：组函数计算时，空值不在统计范围内


-- select avg(salary),sum(salary),max(salary),min(salary) from employees;


count(字段名) -- 统计在本组里该字段一共有多少个非空的值


count(*) -- 统计本组非空行的个数， 等价于count(主键字段名)


count(1) <==> count(*)


-- 请统计员工总数


select count(*) from employees;


select count(employee_id) from employees;


select count(1) from employees;


-- 统计有提成的人数


select count(commission_pct) from employees;


-- 统计没有提成的人数


select count(*)-count(commission-pct) from employees;


select count(*) from employees where commission_pct is null;


-- 统计公司里有多少种职位


select count(distinct job_id) from employees;


十. 分组语句：group by


语法 -- select ... from ... where ... group by 分组条件... order by ...


-- 请打印各部门员工的平均工资


第一步：确定分组条件 group by department_id


第二步：生成sql --- select avg(salary) from employees group by department_id;


-- 请打印各个职位的最高工资


第一步：group by job_id


第二步：select max(salary) from employees group by job_id;


-- 请打印 50,60,70部门 各部门职位的最低工资


1. where department_id in(50,60,70)


2. group by department_id,job_id 按多个条件分组，只有满足所有条件才能分到一组


3. select min(salary) from employees where department_id in(50,60,70)


group by department_id,job_id;


硬性语法规定：


1. 只有出现在group by里的字段，才能够单独出现在select里，其他字段只能配合组函


数出现在select里


2. 如果select里出现了组函数和单独字段并存时，此时必须写group by。


3. group by里的字段使用了哪一个单行函数，那么select里相应字段也必须使用同一个


函数处理


复习：oracle相关函数（单行函数 组函数）


1. 单行函数：length(str) dbms_random.random() sysdate to_char(date,’fmt’)


2. 组函数：sum avg max min count(*) count(字段) count(1)


null值不在统计范畴


3. group by 分组条件；


条件 --》 具体的字段，单行函数处理过得字段，多个字段的联合，case..when


规定 --》 前后呼应，如果select语句含有group by，要求select里没有应用组函数


的字段，必须一模一样出现在group by里


十一. having -- 查询命令里的条件判断语句


1. 语法： select ... from ... where ... group by ... having 判定条件... order by


-- 请打印部门编号，部门的平均工资（要求：部门人数大于2的部门）


select department_id,avg(salary) from employees where count(*)>2


group by department_id ; ----> error


select department_id,avg(salary) from employees group by department_id


having count(*)>2;


注意：having对分组后的数据进行判断


2. having和where的区别


where：在分组前执行，只能对单行数据进行判断，不能使用组函数


having：出现在分组后，只能对组的共性判断，不能在强调单个值


注意：


where和having都能够完成同一功能时，优选where


-- 请打印80部门和90部门的部门编号，以及平均工资


select department_id,avg(salary) from employees


where department_id in(80,90) group by department_id;


select department_id,avg(salary) from employees group by department_id


having department_id in(80,90);


十二. select命令总结


1. 完整语法：select ... from ... where ... group by ... having ... order by ...


2. 执行顺序：


1）from -- 确定需要查询操作的表


2）where -- 对表里的数据按条件逐行筛选，生成查询结果基础数据


3）group by -- 对基础数据按条件分组


4）having -- 对分组后的数据再次筛选


5）select -- 对结果数据按要求统计查询


6）order by -- 对最终结果排序


十三. 伪列


1.概念：不存在的，通过select * 查询不到的，可以直接通过字段名查询。


常见的伪列 -- rownum rowid


1）rowid：一行记录在数据库里的唯一标识，通过对物理地址运算后得到的结果


2）rownum：数据库会为每次出现在查询结果里的行进行编号，从1开始。查询


执行一次编一次号。


-- 请打印employees表里的前5行数据


select * from employees where rownum<=5;


-- 请打印employees表里的第6到第10行数据


select * from employees where rownum between 6 and 10; --- error


注意：rownum必须从1开始使用，只能做 < <= =1 >=1


十四. 子查询（查询嵌套）


-- 请打印公司里工资最高的员工信息


select * from employees where salary = max(salary); --- error


1) 查询公司里的最高工资是多少


select max(salary) from employees; ---> result1


2) 根据result1查询工资最高的员工信息


select * from employees where salary = result1;


3) 合并


select * from employees where salary = ( select max(salary) from employees );


1. 子查询的结果是一行一列（一个值），一般出现在where或者having里作为判断的条件


或者直接写在select里，作为一个结果字段出现。


-- 请查询公司里工资高于平均工资的员工信息


1)查询公司的平均工资


select avg(salary) from employees; --》result1


2)查员工信息（工资高于result1的员工）


select * from employees where salary > result1;


3) 合并


select * from employees where salary >( select avg(salary) from employees );


-- 请查询各部门工资最高的员工信息 【特殊用法，难点】


select * from employees e1 where salary = ( select max(salary) from employees e2


where e2.department_id = e1.department_id );


分析：


主： select * from employees where salary = ( 本行记录所在部门did的最高工资);


子： select max(salary) from employees where department_id= did;


-- 请打印工资高于本职位平均工资的员工信息


select * from employees e1 where salary > (select avg(salary) from employees e2 where


e2.job_id = e1.job_id );


2. 子查询的结果是多行一列（子查询提供多个值），通常在where里使用


-- 请打印和‘King’在同一部门工作的员工信息


1）查询king所在部门


select department_id from employees where last_name=’King’; (80,90)


2) 查询在80,90部门工作的员工信息


select * from employees where department_id in(80,90);


3)合并


select * from employees where department_id in (select department_id from employees


where last_name=’King’);


3. 子查询结果是多行多列（虚拟表），可以对虚拟表再次执行查询语句。一般只出现在


from子句里。


-- 请打印工资最高的五位员工信息。


select * from employees where rownum<=5 order by salary desc; --> error


1)对employees表按工资排序


select * from employees order by salary desc; ---> t1


2)从t1里提取出前五行数据


select * from t1 where rownum <=5;


3)合并


select * from (select * from employees order by salary desc) where rownum<=5;


4.数据分页（重点难点）


概念：对结果数据进行分段显示。主要使用“子查询”和rownum解决


-- 请显示工资最高的第6到第10个员工信息


easy：


1) 对employees表按工资排序


select * from employees order by salary desc; ---> tab1


2) 提取前10个员工信息，并再次对数据进行按工资升序排列


select * from tab1 where rownum<=10 order by salary; --->tab2


3) 提取tab2前五个数据


select * from tab2 where rownum<=5;


4) 合并


select *


from (select *


from ( select * from employees order by salary desc ) tab1


where rownum<=10 order by salary ) tab2


where rownum<=5


专业：


1) 对employees表按工资排序


select * from employees order by salary desc; ---> tab1


2) 提取前10个员工信息,并将rownum变成tab2的一个具体字段，其别名rn


select tab1.*,rownum rn from tab1 where rownum<=10; ---> tab2


3) 提取tab2表里rownum字段值为6~10的记录


select * from tab2 where rn between 6 and 10;


4）合并


select * from ( select tab1.*,rownum rn from (


select * from employees order by salary desc


) tab1 where rownum<=10


) tab2 where rn between 6 and 10;


复习：


1. select六条子句：


from--》where--》group by --》having --》select --》order by


2.伪列：rownum rowid


3.子查询：查询嵌套（一个值 一组值 虚拟表）


4.数据分页：子查询和rownum


1）按条件整理数据（获取符合要求的数据）


2）提取前n行数据，并且将rownum变成虚拟表的一个字段，起别名


3）获取第 **行到第n行


十五.表连接


1. 概念：将两个表的记录按条件合并一条记录显示结果


-- 请打印员工信息以及所在部门的名称


select employee_id,last_name,'...',e.department_id,' | ',d.department_id,


d.department_name from employees e,departments d


where e.department_id = d.department_id;


2. 内连接


将两个表里满足条件的记录连接在一起。使用关键字inner join做连接，其中inner可以省


略，连接条件用on给定，其他判定条件用where指定。


-- 请打印员工信息，以及所在部门的信息


select e.*,d.*


from employees e inner join departments d


on e.department_id=d.department_id;


-- 请打印50部门的员工姓名和所在部门名称


select e.employee_id,e.last_name,d.department_id,d.department_name


from employees e join departments d


on e.department_id = d.department_id


where e.department_id = 50;


内连接缺陷：不能处理连接条件为null的记录。


3.外连接（左外，右外，全外）


左外连接 -- 以左表为主（左表记录全部出现），右表辅助（如果没有对应的记录负责补


齐空行）


右外连接 -- 右表为主，左表辅助


全外连接 -- 两个表的记录全部出现，谁缺谁补。


-- 请打印所有员工信息，以及所在部门名称。（所有员工必须全部出现）


1）左外：left outer join ， 其中outer可以省略


select e.*,d.*


from employees e left outer join departments d


on e.department_id = d.department_id;


2) 右外：right outer join ，其中outer可以省略


select e.*,d.*


from departments d right outer join employees e


on e.department_id=d.department_id;


3) 全外：full outer join，其中outer可以省


select e.*,d.*


from employees e full join departments d


on e.department_id = d.department_id;


4. 多表连接


-- 请打印员工的姓名，所在部门的名称，以及所在城市


select e.last_name,d.department_name,lo.city


from employees e left join departments d


on e.department_id = d.department_id


left join locations lo


on d.location_id = lo.location_id;


5. 自连接


-- 请打印员工的姓名，以及他领导的姓名。


select e.last_name,m.last_name


from employees e left join employees m


on e.manager_id = m.employee_id;


6. 非等值连接：连接条件不是用等号给定


-- 有两个表如下：


tab1 tab2


id value id value


1 A 1 a


2 B 2 b


3 C 3 c


4 D 4 d


请打印输出如下结果： Ab Ac Ad Bc Bd Cd


select tab1.value ,tab2.value


from tab1 join tab2


on tab1.id < tab2.id ;


十六. 集合运算符


1. union：求两个查询结果的并集，重复数据只保留一份


select * from employees where department_id in(70,80)


union


select * from employees where department_id in(80,90); ---> 70,80,90


2. union all：合并两个查询语句的结果，保留重复数据


select * from employees where department_id in(70,80)


union all


select * from employees where department_id in(80,90); ---> 70,80,80,90


3. minus：求差集， result1-result2


select * from employees where department_id in(70,80)


minus


select * from employees where department_id in(80,90); ---> 70


4. intersect：求交集


select * from employees where department_id in(70,80)


intersect


select * from employees where department_id in(80,90); ---> 80


注意：


1. 运算过程中以第一个结果集为准（最终结果的字段数量，字段类型，字段名都以第一个查询结果为主）。


2. 效率比较差，不建议使用。只在做“行转列”时使用


十七. 建表


1. 语法：create table 表名(


字段名 数据类型 [default 默认值] [约束],


字段名 类型 [default 默认值] [ constraint 约束名] [约束类型]


);


2. 合法标识符


1) 由字母，数字，_ ，$，# 组成 其中数字不能开头


2）不区分大小写，不能是关键字


3）表名长度不能超过30个字符


3. 数据类型


1）数字类型（number integer）


number(n1,n2) -- n1表示有效位数，n2表示小数点后占几位


eg： number(7,2) ---- 99999.99


number(7) ----- 7位整数


number -- 类似于java里的double


integer -- 一个很大的整数


2）字符串类型（char varchar2 nvarchar2）


char(n) : 表示该字段用n个字节空间存放字符，n最大可取值为4000.


varchar2(n) : 表示为字段值预留n个字节空间，实际存放时会根据值的大小进行调整。


n最大可取值为4000.


nvarchar2(n): 表示预留n个字符空间，存放时根据实际值调整。


3）日期类型 （date，timestamp）


4）大数据类型（clob blob long）


clob(字符大对象)：存放字符串值，最大可以容纳4G


blob(二进制大对象): 存放二进制数据，最大可以容纳4G


long : 存放的是字符串类型，容量2G


4. 约束：对字段的限定条件


1) 主键约束：primary key 定义 ，字段值必须非空 并且 唯一 ， 一张表只有一个主键


2) 非空约束：not null 定义


3) 唯一约束：unique 定义 ，有值的时候，值必须唯一（不算null值）


4) 检查约束（自定义约束）：check 定义


5) 外键约束：references 定义 ， 值必须在另一个表的主键里取。


-- 建立一个student表（id,name,sex,mobile,email,province,married）


create table student(


id number(5) constraint student_id_pk primary key ,


name varchar2(15) constraint student_name_nn not null ,


sex char(1) default ‘m’check( sex in(‘m’,’f’) ),


mobile varchar2(11) not null check( length(mobile)=11 ),


email varchar2(30) unique check( email like ‘%_@_%’) ,


province varchar2(30),


married char(1) default ‘F’check( married in(‘T’,’F’) ),


ban_id number(5) references Banji(id)


);


create table Banji(


id number(5) primary key,


name varchar2(15) not null


);


5. 联合键约束：联合主键 联合唯一键


-- create table student2(


id number(7) primary key,


name varchar2(15) not null


);


create table course2(


id number(7) primary key,


name varchar2(15) not null


);


create table scoretable(


sid number(7) references student2(id),


cid number(7) references course2(id),


score number(3),


primary key(sid,cid)


);


6. 利用子查询建表


create table 表名 as 子查询; ---> 利用子查询的结果数据构建新表


缺点：在数据拷贝过程中，原表的所有约束，只有not null被保留，其他全部丢失。


-- create table t1 as select * from employees order by salary desc;


-- create table t2 as select * from employees where 1=2; -->只拷贝表结构，没有数据


price = ? type=? name=?


sql = select * from product where 1=1


if( price !=null){ sql += “and price =1000 ”}


if( type!=null) { sql += “and type=Nike ”}


复习：


1.表连接：内连接 外连接 自连接 非等值连接 多表连接


左外连接 -- left join ... on 查询时以左表为主（左表记录全部出现）


内连接： join ... on 查询出所有连接条件成立的记录（连接条件为null的无法显示）


2.集合运算符： union union...all minus intersect


--> employee_id first_name salary department_id


100 Steven 24000 90


--> Key value


employee_id 100


first_name Steven


salary 24000


--> select 'employee_id' "Key", to_char(employee_id) "value" from employees where


employee_id=100


union


select 'first_name',first_name from employees where employee_id=100


union


select 'hire_date',to_char(hire_date,'yyyy-mm-dd') from employees where


employee_id=100;


3. 建表语句


1）create table 表名(


字段名 类型 【defaul 值】 【constraint约束名】 【约束类型】，


字段 ..... ,


);


2) 约束类型： primary key not null unique check(检查条件)


references 另一个表(pk or uk)


3) 联合约束：联合主键 联合唯一


十八.Sql命令的分类


1. DQL：数据查询语言 -- select


2. DML：数据操纵语言 -- insert（插入） update（修改） delete（删除）【重点】


3. DDL：数据定义语言，定义数据库对象，以及数据类型，约束的


-- create（创建） drop（删除） truncate（截断） rename（改名） alter（修改）


4. DCL：数据控制语言，用来授权或者撤销权利的命令，管理员使用的


-- grant（授权） revoke（撤销）


grant create table to hr; revoke create table from hr;


5.TCL（transaction control language）：事务控制命令，控制事务的提交和回滚 【重点】


-- commit（提交） rollback（回滚）


十九. DDL


1. 删除表：drop table 表名 【cascade constraint】；


cascade级联操作将另一个表的外键约束删除后，在删除本表。


2. truncate table 表名； --- 截断表里的数据，同时释放数据所占空间


3.alter ：修改数据库里的表结构（具体内容参看文档）


二十. DML命令（操作数据的命令） 【重点】


1. insert into -- 向表里插入数据


1)全表插入


语法：insert into 表名 values( v1,v2,v3,........... );


要求：values里值的个数，类型，顺序必须和原表保持一致


例如：


insert into student values(1,'luxw','f','18518407603','luxw@zparkhr.com.cn','liaoning','T');


insert into student values(2,'huxz',null,'13813813838','huxz@zparkhr.com.cn','tianjin','T');


2）选择个别列插入


语法：insert into 表名(字段名，字段名，....) values(v1,v2,......);


要求：values里值的个数，类型，顺序与表名后面的字段保持一致


表里非空并且没有默认值的字段必须出现


例如：insert into student(id,name,mobile) values(3,'liucy','18686868686');


3）利用子查询插入数据


语法：insert into 表名(字段...) 子查询 ;


要求：子查询结果里的字段数量，类型，顺序必须和表名后面的字段保持一致。


例如：insert into student(id,name,mobile) select employee_id,first_name,


substr(phone_number,1,11) from employees where department_id=30;


2. update -- 修改数据


语法： update 表名 set 字段名=新值，字段=新值 where 条件；


例如：update student set email='liucy@zparkhr.com.cn',province='heilongjiang',married='T'


where id=3;


3. delete -- 删除数据


语法：delete from 表名 where 条件；


例如：delete from student where id>=114;


思考题：


1）有tab1表（name，age，score），请写一条删除命令，删除表里的重复数据


delete from tab1 t1 where rowid !=( select min(rowid) from tab1 t2 where t1.name=t2.name and t1.age=t2.age and t1.score=t2.score);


2）问：删除表里的30行数据（表里有100行数据）用什么命令


delete from 表 where 条件选出需要删除的30行数据；


删除表里的30万行数据（表里有100万行数据）用什么命令


create table temptable as select * from 原表 where 要保留的70万行;


truncate table 原表;


insert into 原表 select * from temptable;


drop table temptable;


二十一. 事务控制 【重点 ******】


1.概念：是一组不可再分的sql命令组成的集合，是操作数据库的最小单位，事务大小


与实际的业务难易程度有关。


2.事务边界


1) 开始：从写第一条sql命令那一刻开始


2）结束：


a. 输入的是一组DML（insert update delete）命令，需要显示的输入commit或者rollback


b. 输入的是一条DDL或者DCL命令，成功后，命令自带commit。


c. 输入的是一组DML命令，系统退出，正常退出（数据库做commit），非正常退出


数据库做rollback。


例如：insert update commit delete insert create insert update exit


3.事务原理


数据库服务器会为每一个连接上来的client开辟一小块内存空间（回滚段），用来暂时缓存client执行的sql命令的结果，当事务结束时需要client给出明确的处理意见：commit为永久保存，rollback表示丢弃已有的结果。


4. 数据安全


1）数据库表里的每条记录都有一个“写锁”标记，以行为单位，称为“行级写锁”，同时只有一个client可以获取这个锁标记，在事务结束时锁标记被释放。


写动作包括： insert update delete


2）可以人为为查询到的行添加”行级读锁“，将查询到的所有行锁住。


select * from 表 for update；


select * from 表 for update nowait;


select * from 表 for update wait 5；


5.事务的四大特性：（ACID）


A（atomic）：原子性 -- 事务包含的多条命令不可再分，是一个整体


C（consitancy）：一致性 -- 事务执行过程中，数据的状态前后要一致


I（isolation）：隔离性 -- 事务和事务之间不能够互相影响


D（durability）：持久性 -- 事务结束时，数据要做永久性处理操作


6.多事务并发访问数据库，可能的隐患


1）脏读：B事务读到了A事务没有提交的数据。可以将数据库事务的隔离级别设置在


read commited级别。


2）不可重复读：B事务在A事务执行过程中，修改了数据，导致A事务前后两次读到的


数据不一致。解决办法：可以手动添加“读锁”


3）幻读：B事务在A事务执行过程中，插入了数据，导致A事务前后两次读到的数据不


一致。


7. 数据库事务隔离级别


1）read uncommitted：提交之前读（没有事务）


2）read committed：提交之后读（默认设置）


3）repeatable read：理论研究，很少有数据库支持。


4）serializable：序列化读（表级锁）


通过sqlplus命令可以设置隔离级别


sql> set transaction isolation level read committed; 当次事务生效


二十二. 数据库设计


1. 数据字典：db sv创建表，用来保存数据库状态信息的。


user_tables user_columns user_views


2. 范式：设计数据库表格时需要遵循的规范


一范式 -- 单值性，表里每个字段只能保存一个值


二范式 -- 完整依赖，表里的其他字段必须完整依赖表的pk，不能有部分依赖


三范式 -- 非传递依赖，表里的字段必须直接依赖pk


3. E-R图 （实体 -- 关系 图）


二十三. 数据库里的其他对象


1. 序列（sequence）


1）作用 -- 可以生成一系列唯一的数值，是一个公开的公共的对象。


2）创建 -- create sequence 序列名


【start with n】 序列的起始值 默认1


【increment by n】 每次递增都少 默认1


【maxvalue n / nomaxvalue】 设定能产生的最大值


【minvalue n】 设定最小值 默认1


【cycle / no cycle】 是否循环 默认不循环


【cache n / no cache】 缓存 默认20 ；


3）删除 -- drop sequence 序列名


4）使用 -- 通过变量nextval去获取序列里的值


select 序列名.nextval from dual;


insert into student(id,name,mobile) values(seq1.nextval,’aa’,’12345678999’);


2.视图（View）


1）概念： 为一条查询语句起名字。查询结果通常以”表“的形式展现，所以视图建好


后，可以当做表来使用（二次查询没问题）。


2）创建 -- create view 视图名 as 查询语句；


3）作用 --


a. 简化查询


-- 打印工资最高的五位员工信息


select * from (select * from employees order by salary desc) where rownum<=5;


-- 打印工资最高的第6到第10


select * from (


select e1.*,rownum rn from (select * from employees order by salary desc) e1


where rownum<=10


) where rn between 6 and 10;


-- create view v1 as select * from employees order by salary desc;


select * from v1 where rownum<=5;


select * from (select v1.*,rownum rn from v1 where rownum<=10 )


where rn between 6 and 10;


b. 控制访问权限


可以为不同权限的用户分别建立视图，然后将原表的访问权限禁掉。只对用户开


放视图的访问权限。


-- 为普通员工建视图


create view emp as select employee_Id,last_name,...department_id from employees;


-- 为老板创建视图


create view boss as select * from employees;


-- 禁掉原表的访问权限。


3. 索引


1）作用：加快查询速度。经常被查询的数据适合建立索引


2）组成：通常由两部分组成，先对添加索引的字段值进行排序，后面辅助于rowid


3）创建：create index 索引名 on 表（字段名）；


索引是给db server使用，db server会在查询时自动使用建立的索引


db server 会自动为表的pk和uk建立索引