一.数据持久化操作
文本文件缺点 -- 存放数据量少,不安全,不能对数据类型,数据的合法性检查,没有
完善的备份和回复机制。
二. 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建立索引
文本文件缺点 -- 存放数据量少,不安全,不能对数据类型,数据的合法性检查,没有
完善的备份和回复机制。
二. 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建立索引