5.3 Oracle

Day01

一.数据库和数据库管理系统

oracle 数据库的一种

DBMS 数据库管理系统 负责对数据的管理

DB 数据库 数据的真正存放

RDBMS 关系型数据库管理系统

RDB 关系型数据库

基于二维表的数据库

表头

字段名

字段值

现在主流关系型数据库管理系统

oracle oracle(甲骨文)

db2 IBM

sqlserver mic(微软)

非商业型数据库

mysql sun——-oracle(操作系统 java)

二.sql (结构化查询语言)

2.1 select语句 查询语句

​ select

2.2 ddl语句 数据定义语句

​ create table

​ drop table

​ alter table

2.3 dml语句 数据操作语句

​ insert

​ delete

​ update

2.4 tcl 事务控制语句

​ commit; 提交 确认

​ rollback; 回滚 撤销

​ savepoint 保存点;

2.5 dcl 数据控制语句

​ grant 授权

​ revoke 回收权限

三.使用数据库

oracle9i oracle10g oracle11g

telnet IP

telnet 192.168.0.26

telnet 192.168.0.23

openlab

open123

使用sqlplus 进入数据库

sqlplus

openlab

open123

SQL>

SQL>!clear

SQL>exit

查询数据库中的表结构

SQL>desc 表名;

​ desc s_emp;

SQL> desc s_emp;

Name Null? Type


ID 员工编号 NOT NULL NUMBER(7)

LAST_NAME 姓 NOT NULL VARCHAR2(25)

FIRST_NAME 名 VARCHAR2(25)

USERID 用户编号 VARCHAR2(8)

START_DATE 入职日期 DATE

COMMENTS 备注 VARCHAR2(255)

MANAGER_ID 领导的员工编号 NUMBER(7)

TITLE 职位 VARCHAR2(25)

DEPT_ID 部门编号 NUMBER(7)

SALARY 月薪 NUMBER(11,2)

COMMISSION_PCT 提成 NUMBER(4,2)

表头中的每个字段的名字

这个字段上 能不能不填值

字段的类型 number 数字

​ char varchar2 字符串类型

​ date 日期类型

四.select语句

4.1 from 子句

4.1.1 如何数据库的表中查询一个字段的值

select 字段名 from 表名;

把s_emp 表中 salary的数据查询出来

select salary from s_emp;

4.1.2 如何从表中查询 多个字段的值

select 字段名1,字段名2 from 表名;

把s_emp 表中 first_name salary 列出

select first_name,salary from s_emp;

4.1.3 思考如何把s_emp 表中所有的数据查询

出来。

select id,first_name,last_name ….;

select * from 表名;

4.1.4 表中字段数据的数学运算

+ - * /

把每个员工的工资加100显示出来

select salary,salary+100 from s_emp;

计算每个员工的月薪 和 年薪

select salary,salary*12 from s_emp;

select salary,100+salary*12 from s_emp;

select salary,(100+salary)*12 from s_emp;

sql中的除法不取整

select salary,salary/22 from s_emp;

4.1.5 数据库中的别名

字段或者表达式的后面 加一个名字

​ select salary sal,

​ (100+salary)*12 yearsal from s_emp;

字段或者表达式的别名只能有一个

select salary sal,

​ (100+salary)*12 year sal from s_emp;

要处理别名的原样显示 “别名”

select salary sal,

​ (100+salary)*12 “year Sal” from s_emp;

4.1.6 sql 中字符串的表达

使用单引号 引起一串字符

‘’ ‘ ‘ ‘a’ ‘hello world’ ‘_‘

显示s_emp 表中first_name last_name

select first_name,last_name from s_emp;

字符串的拼接 ||(字符串拼接符)

select first_name||last_name from s_emp;

@ _

select first_name||’@’||last_name

​ from s_emp;

first_name 和 last_name 之间拼接一个 ‘

this is this’s

%d %lf %%

select first_name||’’’’||last_name

​ from s_emp;

first_name 和 last_name 之间拼接两个 ‘

select first_name||’’’’’’||last_name

​ from s_emp;

select first_name||’’’’||’’’’||last_name

​ from s_emp;

4.1.7 null值的处理

null 值和任何值做运算 结果都是null

换一种年薪的计算方式

12salary+12salary*(commission_pct/100)

12salary(1+commission_pct/100)

列出每个员工的年薪 和 新的计算方式的年薪

select salary*12 yearsal,

​ salary12(1+commission_pct/100) newys

​ from s_emp;

null值的处理 null值处理函数

nvl(par1,par2) par1,par2可以是任意类型

但要求类型保持一致。

当par1为null 则返回par2的值

当par1不为null 就返回值par1的值

select salary*12 yearsal,

​ nvl(salary12(1+commission_pct/100),0) newys

​ from s_emp;

null 值要尽早处理

select salary*12 yearsal,

​ salary12(1+nvl(commission_pct,100)/100) newys

​ from s_emp;

显示每个员工的 first_name manager_id

salary 如果manager_id 为null 则显示

成-1

select first_name,nvl(manager_id,-1),

​ salary from s_emp;

4.1.8 数据的排重显示 distinct

(sql语句只关心做什么 不管怎么做)

select salary from s_emp;

select distinct salary from s_emp;

如何初始化sql编程环境

1.上传初始化脚本

2.运行脚本

​ SQL>@路径名/脚本名

显示 s_emp 表中 title 和 salary

select title ,salary from s_emp;

联合排重

select distinct title,salary from s_emp;

/ error /

-- 这是单行注释

select title,distinct salary

​ from s_emp;

select distinct id,title,salary

​ from s_emp;

4.2 where子句

​ 4.2.1 作用

​ 限制表中的数据返回,符合where条件数据

​ 就被选中,不符合where条件数据就被过滤掉。

​ 4.2.2 两个极限条件

​ where 1=1; / 恒等 /

​ where 1=2; / 永假 /

​ select id,first_name from s_emp;

​ select id,first_name from s_emp

​ where 1=1;

​ select id,first_name from s_emp

​ where 1=2;

​ no rows selected

​ 4.2.3 number 类型条件的书写

​ 找出工资大于1400的员工

​ 列出id first_name salary

​ select id,first_name,salary from s_emp

​ where salary>1400;

​ select id,first_name,salary from s_emp

​ where salary<1400;

​ select id,first_name,salary from s_emp

​ where salary=1400;

​ select id,first_name,salary from s_emp

​ where salary!=1400;

​ 4.2.4 字符串类型条件的书写

​ 找出first_name 叫Carmen的 员工的

​ id,first_name,salary

​ select id,first_name,salary

​ from s_emp

​ where first_name=’Carmen’;

​ 字符串类型 要注意单引号

​ 字符串类型的值要严格区分大小写

​ sql语句不区分大小写

​ SELECT ID,FirSt_Name,salary

​ from s_emp

​ where first_name=’Carmen’;

4.2.5 常见条件运算符

​ > < >= <= = !=

4.2.6 sql 语句提供的运算符

​ 4.2.6.1 表达区间 [a,b]

​ where 字段 between a and b;

​ s_emp 表中 salary在[1400,2500]

​ 的员工的id ,salary

​ select id,salary

​ from s_emp

​ where salary between 1400

​ and 2500;

​ 4.2.6.2 表达一个字段在一个范围内取值

​ where 字段 in (a,b,c,d)

​ 部门编号是 31 或者 32 或者是50 的

​ 员工的信息。列出id,first_name,dept_id

​ select id,first_name,dept_id

​ from s_emp

​ where dept_id in(31,32,50);

​ select id,first_name,dept_id

​ from s_emp

​ where dept_id in(32,31,50);

​ 最终数据没有差别 但执行效率 可能受到

​ 极大的影响。

​ 出现概率高的放前面。

​ 把s_emp 表中 id 是 1或者是10或者是25

​ 的员工查询出来 列出id first_name

​ select id,first_name from s_emp

​ where id in(1,10,25);

​ 4.2.6.3 模糊查询 like

​ 李嘉诚 李小龙 李刚 李

​ 成龙 史泰龙 龙龙 小龙女

​ ls *.txt

​ ls *.c

​ ls .

​ sql 中的统配符

​ % 0-n个任意字符

​ _ 1个任意字符

​ 找出姓李的人

​ where name like ‘李%’;

​ name 中带龙的

​ where name like ‘%龙%’;

​ 倒数第二个是龙

​ where name like ‘%龙_‘;

​ 找出s_emp 表中first_name 中带a的

​ select first_name from s_emp

​ where first_name like ‘%a%’;

​ 找出s_emp 表中first_name 中第二个字符

​ 是a的。

​ select first_name from s_emp

​ where first_name like ‘_a%’;

​ desc user_tables;

​ select table_name from user_tables;

​ s_emp 员工表

​ s_dept 部门表

​ 从user_tables 表中 找出table_name

​ 符合S_ 开头的表名.

​ select table_name from user_tables

​ where tablename like ‘S%’;

​ 处理_ 或者 %的转义问题

​ select table_name from user_tables

​ where tablename like ‘S\%’

​ escape ‘\‘;

​ 从user_tables 表中 找出table_name

​ 符合S__ 开头的表名.

​ select table_name from user_tables

​ where tablename like ‘S\_%’

​ escape ‘\‘;

​ 4.2.6.4 NULL 值的判断

​ where 字段 is null;

​ 如果字段的值 是NULL 就选中数据

​ 如果不是NULL 就不选中。

​ 找出提成是10的员工的

​ id first_name commission_pct

​ select id,first_name,commission_pct

​ from s_emp where commission_pct=10;

​ select id,first_name,commission_pct

​ from s_emp where commission_pct!=10;

​ select id,first_name,commission_pct

​ from s_emp

​ where commission_pct is null;

4.2.7 逻辑条件 连接符号

​ and

​ [a,b]

​ where 字段名>=a and 字段名<=b;

​ [1400,2500]

​ select id,first_name,salary

​ from s_emp

​ where salary>=1400 and

​ salary<=2500;

​ or

​ 员工的编号 是1 或者是10 或者是15

​ 的。列出id first_name salary

​ select id,first_name,salary

​ from s_emp

​ where id=1 or id=10 or id=25;

​ not

​ = != ^= <>

​ > <=

​ < >=

​ between a and b not between a and b

​ in not in (注意NULL)

​ like not like

​ is null is not null

4.3 数据排序

​ 4.3.1 order by 排序标准;

​ 永远出现在sql语句最后

​ 4.3.2 按照工资排序(升序 asc

​ 降序 desc)

​ 显示 id first_name salary

​ select id,first_name,salary

​ from s_emp;

​ select id,first_name,salary

​ from s_emp order by salary;

​ select id,first_name,salary

​ from s_emp order by salary desc;

​ 4.3.3 排序的语法

​ order by 排序标准 排序方式;

​ 排序方式:

​ 升序 asc 默认的顺序

​ 自然顺序 字典顺序

​ 降序 desc

​ 4.3.4 多字段排序

​ select first_name,salary from s_emp

​ order by salary;

​ 如果salary的值相同 则启用名字降序排列

​ select first_name,salary from s_emp

​ order by salary,first_name desc;

​ 4.3.5 NULL 值在排序中的处理

​ NULL在排序中作为最大值

​ select id,manager_id from s_emp

​ order by manager_id;

4.4 单行函数

​ 4.4.1 概念

​ 针对sql语句影响的每一行 都返回一个结果。

​ sql影响多少行 就返回多少个结果。

​ select first_name,upper(first_name)

​ from s_emp;

​ select first_name,upper(first_name)

​ from s_emp where id=1;

​ select first_name,upper(first_name)

​ from s_emp where id>1;

​ select first_name,upper(first_name)

​ from s_emp where id<1;

​ 组函数:针对sql语句影响的一组数据做

​ 处理 最终返回一个结果。

​ 无论sql影响多少行 都返回一个结果。

​ select count(id) from s_emp

​ where id=1;

​ select count(id) from s_emp

​ where id>1;

​ select count(id) from s_emp

​ where id<1;

​ 4.4.2 处理字符串的单行函数

​ upper 字符串变大写

​ lower 字符串变小写

​ select first_name,lower(first_name)

​ from s_emp;

​ 测试表 dual

​ select * from dual;

​ select lower(‘HELLO’) FROM DUAL;

​ concat(par1,par2) 连接两个字符串

​ 用的很少 因为有 ||

​ select concat(‘hello ‘,’world’)

​ from dual;

​ select concat(concat(‘hello ‘,’world’)

​ ,’ hello’) from dual;

​ length 求字符串长度

​ select length(‘hello’) from dual;

​ initcap 把每个单词的首字母变大写

​ select initcap(‘one world one dream’)

​ from dual;

​ substr(par1,par2,par3)

​ par1要处理的字符串

​ par2从什么位置开始截取 编号从1开始

​ 可以是负数 -1 代表最后一个字符

​ par3 截取的长度

​ select substr(‘hello’,0,2) from dual;

​ select substr(‘hello’,1,2) from dual;

​ select substr(‘hello’,-1,2) from dual;

​ 列出 s_emp 表中 first_name 以及

​ first_name的后三个字符

​ select first_name,substr(first_name,

​ -3,3) from s_emp;

​ replace 替换

​ select replace(‘hello’,’lo’,’test’)

​ from dual;

​ nvl(par1,par2)

​ 4.4.3 数字处理函数

​ round 四舍五入函数

​ select round(9.57) from dual;

​ select round(9.99) from dual;

​ / 保留小数点后一位 /

​ select round(9.57,1) from dual;

​ select round(9.54,1) from dual;

​ select round(9.547,2) from dual;

​ / 对小数点前1位进行四舍五入/

​ select round(12.88,-1) from dual;

​ select round(12.88,-2) from dual;

​ trunc 截取

​ / 截掉小数部分 /

​ select trunc(9.57) from dual;

​ select trunc(9.99) from dual;

​ / 截取 并保留几位小数 /

​ select trunc(9.57,1) from dual;

​ select trunc(9.54,1) from dual;

​ select trunc(9.547,2) from dual;

​ / 截取 对小数前的第几位进行截取 /

​ select trunc(12.88,-1) from dual;

​ select trunc(12.88,-2) from dual;

​ 4.4.4 格式显示函数

​ to_char(par1,par2)

​ par1要处理数字数据

​ par2 是格式 可以省略 代表把数字变成

​ 字符串类型。如果有格式 则按照格式显示

​ 数字类型的数据。

​ 格式如下:

​ fm 代表格式开头 可以省略

​ 9 小数点前代表0-9的任意数字

​ 小数点后1-9的任意数字

​ 0 小数点前 代表强制显示前导零

​ 12345 012,345

​ 1234 001,234

​ 小数点后 代表0-9的任意数字

​ $ 美元符号

​ L 本地货币符号 ¥ RMB

​ . 小数点

​ , 分隔符

​ select salary,to_char(salary,

​ ‘fm$099,999.99’) from s_emp;

​ select salary,to_char(salary,

​ ‘fm$099,999.00’) from s_emp;

​ select salary,to_char(salary,

​ ‘fmL099,999.00’) from s_emp;

​ 如何修改数据库的本地语言

​ 1.远程登录服务器

​ telnet IP

​ 2.切换shell

​ bash

​ 3.修改配置文件

​ vi .bash_profile

​ export NLS_LANG=’SIMPLIFIED CHINESE_CHINA.ZHS16GBK’

​ export NLS_LANG=’AMERICAN_AMERICA.ZHS16GBK’

​ 4.保存退出

​ esc

​ shift+zz

​ 5.source .bash_profile

​ 6.重新进入sqlplus

​ 4.4.5 函数的嵌套

​ 把一个函数的返回值作为另一个函数的参数。

​ 把s_emp 表中 first_name 和 first_name

​ 的后三个字符显示出来

​ 结合 substr 和 length

​ select first_name,substr(first_name,

​ -3,3) from s_emp;

​ select first_name,substr(first_name,

​ length(first_name)-2,3) from s_emp;

​ 列出s_emp 表中 id,first_name,manager_id

​ 如果manager_id 是NULL 则显示成BOSS

​ select id,first_name,

​ nvl(to_char(manager_id),’BOSS’)

​ from s_emp;

4.5 多表查询

​ 4.5.1 为什么?

​ 需要的数据来自于多张表。

​ 表的设计决定的。

​ 4.5.2 表的介绍

​ s_emp 员工表

​ id 员工编号

​ first_name 员工名

​ dept_id 部门编号

​ s_dept 部门表

​ id 部门编号

​ name 部门名

​ 列出每个员工的id first_name和部门编号

​ select id,first_name,dept_id

​ from s_emp;

​ 列出每个员工的id first_name和部门编号

​ 还要把部门名显示出来。

​ 字段如果重名则加表名区分

​ /* 两张表所有的记录匹配的可能

​ 笛卡尔积 */

​ select s_emp.id,first_name,dept_id,

​ name

​ from s_emp,s_dept;

​ /* 表达表关系的条件 称之为 表的连接条件

​ 员工所在的部门编号 是一个部门的编号

​ dept_id=s_dept.id */

​ select s_emp.id,first_name,dept_id,

​ name

​ from s_emp,s_dept

​ where dept_id=s_dept.id;

Day02

4.3 数据排序

​ 4.3.1 order by 排序标准;

​ 永远出现在sql语句最后

​ 4.3.2 按照工资排序(升序 asc

​ 降序 desc)

​ 显示 id first_name salary

​ select id,first_name,salary

​ from s_emp;

​ select id,first_name,salary

​ from s_emp order by salary;

​ select id,first_name,salary

​ from s_emp order by salary desc;

​ 4.3.3 排序的语法

​ order by 排序标准 排序方式;

​ 排序方式:

​ 升序 asc 默认的顺序

​ 自然顺序 字典顺序

​ 降序 desc

​ 4.3.4 多字段排序

​ select first_name,salary from s_emp

​ order by salary;

​ 如果salary的值相同 则启用名字降序排列

​ select first_name,salary from s_emp

​ order by salary,first_name desc;

​ 4.3.5 NULL 值在排序中的处理

​ NULL在排序中作为最大值

​ select id,manager_id from s_emp

​ order by manager_id;

4.4 单行函数

​ 4.4.1 概念

​ 针对sql语句影响的每一行 都返回一个结果。

​ sql影响多少行 就返回多少个结果。

​ select first_name,upper(first_name)

​ from s_emp;

​ select first_name,upper(first_name)

​ from s_emp where id=1;

​ select first_name,upper(first_name)

​ from s_emp where id>1;

​ select first_name,upper(first_name)

​ from s_emp where id<1;

​ 组函数:针对sql语句影响的一组数据做

​ 处理 最终返回一个结果。

​ 无论sql影响多少行 都返回一个结果。

​ select count(id) from s_emp

​ where id=1;

​ select count(id) from s_emp

​ where id>1;

​ select count(id) from s_emp

​ where id<1;

​ 4.4.2 处理字符串的单行函数

​ upper 字符串变大写

​ lower 字符串变小写

​ select first_name,lower(first_name)

​ from s_emp;

​ 测试表 dual

​ select * from dual;

​ select lower(‘HELLO’) FROM DUAL;

​ concat(par1,par2) 连接两个字符串

​ 用的很少 因为有 ||

​ select concat(‘hello ‘,’world’)

​ from dual;

​ select concat(concat(‘hello ‘,’world’)

​ ,’ hello’) from dual;

​ length 求字符串长度

​ select length(‘hello’) from dual;

​ initcap 把每个单词的首字母变大写

​ select initcap(‘one world one dream’)

​ from dual;

​ substr(par1,par2,par3)

​ par1要处理的字符串

​ par2从什么位置开始截取 编号从1开始

​ 可以是负数 -1 代表最后一个字符

​ par3 截取的长度

​ select substr(‘hello’,0,2) from dual;

​ select substr(‘hello’,1,2) from dual;

​ select substr(‘hello’,-1,2) from dual;

​ 列出 s_emp 表中 first_name 以及

​ first_name的后三个字符

​ select first_name,substr(first_name,

​ -3,3) from s_emp;

​ replace 替换

​ select replace(‘hello’,’lo’,’test’)

​ from dual;

​ nvl(par1,par2)

​ 4.4.3 数字处理函数

​ round 四舍五入函数

​ select round(9.57) from dual;

​ select round(9.99) from dual;

​ / 保留小数点后一位 /

​ select round(9.57,1) from dual;

​ select round(9.54,1) from dual;

​ select round(9.547,2) from dual;

​ / 对小数点前1位进行四舍五入/

​ select round(12.88,-1) from dual;

​ select round(12.88,-2) from dual;

​ trunc 截取

​ / 截掉小数部分 /

​ select trunc(9.57) from dual;

​ select trunc(9.99) from dual;

​ / 截取 并保留几位小数 /

​ select trunc(9.57,1) from dual;

​ select trunc(9.54,1) from dual;

​ select trunc(9.547,2) from dual;

​ / 截取 对小数前的第几位进行截取 /

​ select trunc(12.88,-1) from dual;

​ select trunc(12.88,-2) from dual;

​ 4.4.4 格式显示函数

​ to_char(par1,par2)

​ par1要处理数字数据

​ par2 是格式 可以省略 代表把数字变成

​ 字符串类型。如果有格式 则按照格式显示

​ 数字类型的数据。

​ 格式如下:

​ fm 代表格式开头 可以省略

​ 9 小数点前代表0-9的任意数字

​ 小数点后1-9的任意数字

​ 0 小数点前 代表强制显示前导零

​ 12345 012,345

​ 1234 001,234

​ 小数点后 代表0-9的任意数字

​ $ 美元符号

​ L 本地货币符号 ¥ RMB

​ . 小数点

​ , 分隔符

​ select salary,to_char(salary,

​ ‘fm$099,999.99’) from s_emp;

​ select salary,to_char(salary,

​ ‘fm$099,999.00’) from s_emp;

​ select salary,to_char(salary,

​ ‘fmL099,999.00’) from s_emp;

​ 如何修改数据库的本地语言

​ 1.远程登录服务器

​ telnet IP

​ 2.切换shell

​ bash

​ 3.修改配置文件

​ vi .bash_profile

​ export NLS_LANG=’SIMPLIFIED CHINESE_CHINA.ZHS16GBK’

​ export NLS_LANG=’AMERICAN_AMERICA.ZHS16GBK’

​ 4.保存退出

​ esc

​ shift+zz

​ 5.source .bash_profile

​ 6.重新进入sqlplus

​ 4.4.5 函数的嵌套

​ 把一个函数的返回值作为另一个函数的参数。

​ 把s_emp 表中 first_name 和 first_name

​ 的后三个字符显示出来

​ 结合 substr 和 length

​ select first_name,substr(first_name,

​ -3,3) from s_emp;

​ select first_name,substr(first_name,

​ length(first_name)-2,3) from s_emp;

​ 列出s_emp 表中 id,first_name,manager_id

​ 如果manager_id 是NULL 则显示成BOSS

​ select id,first_name,

​ nvl(to_char(manager_id),’BOSS’)

​ from s_emp;

4.5 多表查询

​ 4.5.1 为什么?

​ 需要的数据来自于多张表。

​ 表的设计决定的。

​ 4.5.2 表的介绍

​ s_emp 员工表

​ id 员工编号

​ first_name 员工名

​ dept_id 部门编号

​ s_dept 部门表

​ id 部门编号

​ name 部门名

​ 列出每个员工的id first_name和部门编号

​ select id,first_name,dept_id

​ from s_emp;

​ 列出每个员工的id first_name和部门编号

​ 还要把部门名显示出来。

​ 字段如果重名则加表名区分

​ /* 两张表所有的记录匹配的可能

​ 笛卡尔积 */

​ select s_emp.id,first_name,dept_id,

​ name

​ from s_emp,s_dept;

​ /* 表达表关系的条件 称之为 表的连接条件

​ 员工所在的部门编号 是一个部门的编号

​ dept_id=s_dept.id */

​ select s_emp.id,first_name,dept_id,

​ name

​ from s_emp,s_dept

​ where dept_id=s_dept.id;

-———————————————————

1.下载安装压缩包 解压

2.找到安装文件位置 安装

​ sudo dpkg -i gftp*

3.使用gftp

​ gftp

​ code.tarena.com.cn

-———————————————————

​ 4.5.3 笛卡尔积

​ 如果两张表 没有任何关联条件 则会产生

​ 笛卡尔积。

​ s_dept

​ id 部门编号

​ name 部门名

​ region_id 地区编号

​ s_region

​ id 地区编号

​ name 地区名

​ 把每个部门的名字 和 对应的地区名列出

​ select s_dept.name,s_region.name

​ from s_dept,s_region

​ where region_id=s_region.id;

​ 4.5.4 使用表的别名

​ select d.name,r.name

​ from s_dept d,s_region r

​ where region_id=r.id;

​ 4.5.5 列出每个员工的first_name 以及部门

​ 的名字 还有地区的名字。

​ s_emp s_dept s_region

​ select e.first_name,d.name,r.name

​ from s_emp e,s_dept d,s_region r

​ where e.dept_id=d.id and

​ d.region_id=r.id;

​ col 字段名 for a宽度

​ col name for a15

​ 4.5.6 连接两张表的条件 都使用的是等号

​ 则这种连接称之为等值连接。

​ 连接两张表的条件 使用的是非等号

​ 则这种连接称之为非等值连接。

​ desc salgrade;

​ SQL> desc salgrade;

​ Name

​ ——————————-

​ GRADE 工资级别

​ LOSAL 这个级别对应的低工资

​ HISAL 这个级别对应的高工资

​ 把每个员工的工资 和 工资对应的工资级别

​ 列出来。

​ select salary,grade

​ from s_emp,salgrade

​ where salary between

​ losal and hisal;

​ select salary,grade

​ from s_emp,salgrade

​ where salary>=losal

​ and salary<=hisal;

​ 4.5.7 特殊的连接 (自连接)

​ s_emp 领导 普通员工

​ id fname manager_id

​ 2 a 2

​ 3 c 2

​ 4 d 2

​ 如果你的id 是另外一个员工的manager_id

​ 则你是领导。

​ select id,first_name

​ from s_emp

​ where id=manager_id;

​ 一张表中存储了 两种不同业务含义的数据。

​ 找出那些人是领导?

​ select distinct m.id,m.first_name

​ from s_emp e,s_emp m

​ where e.manager_id=m.id;

​ 25个员工 其中有8个领导 剩下17普通员工

​ 4.5.8 等值连接

​ 非等值连接

​ 自连接

​ 都是符合连接条件的数据被选中

​ 不符合连接条件的被过滤掉。

​ 这种连接 统称内连接。

​ select distinct m.id,m.first_name

​ from s_emp e,s_emp m

​ where e.manager_id!=m.id;

​ 得到是过滤掉的数据

​ 外连接: 外连接的结果集 等于 内连接的

​ 结果集加上匹配不上的记录。

​ (一个也不能少)

​ oracle 使用(+) 来完成外连接

​ (+) 字段对面的表的数据 全部匹配出来。

​ select distinct m.id,m.first_name

​ from s_emp e,s_emp m

​ where e.manager_id(+)=m.id;

​ //被找回的数据 是通过NULL 记录匹配的

​ select distinct m.id,m.first_name

​ from s_emp e,s_emp m

​ where e.manager_id(+)=m.id

​ and e.manager_id is null;

​ select distinct m.id,m.first_name

​ from s_emp e,s_emp m

​ where e.manager_id(+)=m.id

​ and e.manager_id is not null;

​ 4.5.9 练习

​ 列出 部门的名字 和 对应的地区名

​ s_dept s_region

​ select d.name,r.name

​ from s_dept d,s_region r

​ where d.region_id=r.id;

​ 公司业务扩展 新增了部门

​ insert into s_dept values(100,

​ ‘test’,NULL);

​ commit;

​ 列出 每个部门的名字 和 对应的地区名

​ 没有地区的部门名也要显示。

​ select d.name,r.name

​ from s_dept d,s_region r

​ where d.region_id=r.id(+);

​ 列出每个员工的first_name 和对应的部门名

​ s_emp s_dept

​ select e.first_name,d.name

​ from s_emp e,s_dept d

​ where dept_id=d.id;

​ 把老板部门号 变成NULL

​ update s_emp set dept_id=NULL

​ where id=1;

​ commit;

​ 列出每个员工的first_name 和对应的部门名

​ 没有部门的员工也要显示

​ select e.first_name,d.name

​ from s_emp e,s_dept d

​ where dept_id=d.id(+);

​ 4.5.10 给老板涨工资

​ update s_emp set salary=12500

​ where id=25;

​ commit;

​ 列出每个员工的id salary 和工资级别

​ 不在工资级别范围内的员工也要显示。

​ select id,salary,grade

​ from s_emp,salgrade

​ where salary between

​ losal(+) and hisal(+)

​ order by id;

​ xdjmq01

​ S6656666

​ vm\**.zip

Day03

一.SQL99 中的内外连接

1.1 sql99 中的内连接

列出每个员工的 first_name 和对应的部门名

​ select first_name,name

​ from s_emp e, s_dept d

​ where e.dept_id=d.id;

from a表 join b表 on 连接条件;

from a表 inner join b表 on 连接条件;

​ select first_name,name

​ from s_emp e join s_dept d

​ on e.dept_id=d.id;

​ select first_name,name

​ from s_emp e inner join s_dept d

​ on e.dept_id=d.id;

1.2 sql99 中的外连接

​ a表 left outer join b 表 on 连接条件;

​ a表 right outer join b 表 on 连接条件;

​ a表 full outer join b 表 on 连接条件;

​ /* (+) 字段对面的表的数据全部匹配出来

​ left outer join 就是左边的表发起

​ 连接,左边的表的数据全部匹配出来*/

​ 增加一个新部门

​ insert into s_dept values(100,’test’,NULL);

​ commit;

​ 列出所有的部门名 和 对应的地区名

​ (+)

​ select d.name,r.name

​ from s_dept d,s_region r

​ where d.region_id=r.id(+);

​ left outer join

​ select d.name,r.name

​ from s_dept d left outer

​ join s_region r

​ on d.region_id=r.id;

​ right outer join

​ select d.name,r.name

​ from s_region r right outer join s_dept d

​ on d.region_id=r.id;

​ / 使用左外连接 完成谁是普通员工?/

​ 先使用 (+) id first_name

​ select distinct m.id,m.first_name

​ from s_emp e,s_emp m

​ where e.manager_id(+)=m.id

​ and e.manager_id is null;

​ select distinct m.id,m.first_name

​ from s_emp m left outer join s_emp e

​ on e.manager_id=m.id

​ where e.manager_id is null;

​ 全外连接的结果集 等于左外连接的结果集

​ 加上右外连接的结果集 然后减去重复的记录。

​ oracle 如何实现全外连接?

​ oracle不是通过两端都加(+)

​ 是通过关键字

​ union 合并两个结果集 并排重

​ union all 合并两个结果集 不排重

​ select id from s_emp union

​ select id from s_emp;

​ select id from s_emp union all

​ select id from s_emp;

​ 多表查询:

​ 内连接

​ 等值连接

​ 非等值连接

​ 自连接

​ 外连接

​ 等值连接

​ 非等值连接

​ 自连接

SQL99 内外连接

​ 内连接

​ a表 join b表 on 连接条件;

​ a表 inner join b表 on 连接条件;

​ 外连接

​ a表 left outer join b 表 on 连接条件;

​ a表 right outer join b 表 on 连接条件;

​ a表 full outer join b 表 on 连接条件;

​ union

​ union all

二.组函数

​ 2.1 特点

​ 无论sql语句影响多少行 最终返回一个结果

​ 2.2 常见的组函数

​ count 统计个数

​ max 统计一组数据的最大值

​ min 统计最小值

​ sum 统计和

​ avg 统计平均值

​ 2.3 举例

​ 统计 s_emp 表中的 员工人数 工资的

​ 最大值 工资的最小值

​ select count(id),max(salary),

​ min(salary) from s_emp;

​ 2.4 统计s_emp 中的工资的和 和

​ 工资的平均值。

​ select sum(salary),avg(salary)

​ from s_emp;

​ select sum(distinct salary),

​ avg(distinct salary)

​ from s_emp;

​ 2.5 测试组函数对NULL值的处理方式

​ 统计s_emp提成的个数 提成的平均值

​ 忽略

​ select count(commission_pct),

​ avg(commission_pct) from s_emp;

三.分组

​ 3.1 按照一定的标准 把数据分成若干组。

​ 3.2 where 条件 group by 分组标准.

​ 3.3 按照部门编号分组 统计每个部门的人数

​ select dept_id,count(id)

​ from s_emp

​ where 1=1

​ group by dept_id;

​ 3.4 按照部门编号分组 统计每个部门的

​ 平均工资。

​ select dept_id,avg(salary)

​ from s_emp

​ where 1=1

​ group by dept_id;

​ 按照部门编号分组 统计每个部门的

​ 平均工资。列出平均工资大于1400的部门

​ 和 平均工资。

​ select dept_id,avg(salary)

​ from s_emp

​ where 1=1

​ group by dept_id;

​ 3.5 如何对组数据进行过滤

​ 需要使用 having 数据过滤条件

​ select dept_id,avg(salary)

​ from s_emp

​ where 1=1

​ group by dept_id

​ having avg(salary)>1400;

​ 3.6 按照部门号分组 统计每个部门的人数

​ 显示人数大于2的部门编号 和 人数。

​ select dept_id,count(id) ec

​ from s_emp

​ group by dept_id

​ having count(id)>2

​ order by ec;

​ from

​ where

​ group by

​ having

​ select

​ order by

3.7 列出每个部门的平均工资

​ 要求显示平均工资大于1400的部门

​ 编号和 平均工资。还要求显示部门名。

​ s_emp s_dept

select dept_id,avg(salary),d.name

​ from s_emp e,s_dept d

​ where e.dept_id=d.id

​ group by dept_id,d.name

​ having avg(salary)>1400;

select dept_id,avg(salary),max(d.name)

​ from s_emp e,s_dept d

​ where e.dept_id=d.id

​ group by dept_id

​ having avg(salary)>1400;

结论:在分组语句中 select 后的字段 要么

是分组标准要么是经过合适的组函数处理过的。

四.子查询

4.1 概念

把一条sql 的执行结果 另一条sql的操作基础。

4.2 子查询出现的位置

where

having

from

4.3 where 之后

/ 查询所有的领导编号 /

select distinct manager_id from s_emp;

/* 如果一个员工的编号 出现在领导编号中

则这个员工是领导*/

in 等于其中的任何一个

select id,first_name from s_emp

​ where id in (select distinct

​ manager_id from s_emp);

/ 查询所有的非领导 列出 id ,first_name/

not in 不等于其中任何一个数据

id not in (1,2,3,NULL);

select id,first_name from s_emp

​ where id not in (select distinct

​ manager_id from s_emp);

select id,first_name from s_emp

​ where id not in (select distinct

​ manager_id from s_emp where

​ manager_id is not null);

4.4 子查询出现在having 后

按照部门号分组,列出每个部门的平均工资

要求显示平均工资大于42部门的平均工资

的部门,显示部门号 和平均工资.

select dept_id ,avg(salary)

​ from s_emp

​ group by dept_id

​ having avg(salary)>(

​ select avg(salary) from

​ s_emp where dept_id=42);

4.5 子查询出现在from之后

任何的查询语句 可以看成一张内存表

select id,first_name name ,salary

​ from s_emp;

select * from ( select id,first_name

name ,salary from s_emp )

where salary>1000;

select*from(select dept_id ,avg(salary)

​ asal from s_emp

​ group by dept_id) where asal>(

​ select avg(salary) from

​ s_emp where dept_id=42);

五.建表 和 删表

5.1 建立表

create table 表名(

​ 字段名 类型,

​ 字段名 类型,

​ 字段名 类型

);

类型:

number

name char(20) 定长字符串

name varchar2(20) 变长字符串

date 日期类型

5.2 建立一张员工表

create table myemp9528(

id number,

fname char(10),

sname varchar2(10),

salary number

);

5.3 删除表

drop table myemp9528;

create table myemp9528(

id number,

fname char(10),

sname varchar2(10),

salary number

);

六.DML (数据操作语句)

6.1 insert 增加数据

insert into 表名 values(字段1值,

字段2值,字段3值);

insert into myemp9528 values(

9527,’xsy’,’xsy’,123456);

commit;

select length(fname),length(sname)

​ from myemp9528 where id=9527;

select * from myemp9528

​ where fname=’xsy ‘;

select * from myemp9528

​ where sname=’xsy’;

可以选择部分字段 插入值

没有选择的字段 以NULL值插入

必须包含所有的非空字段

insert into 表名(字段名1,字段名3)

values(字段1值,字段3值);

insert into myemp9528(id,sname)

values(2008,’bjtest’);

commit;

select * from myemp9528;

insert into s_dept values(100,

‘test’,NULL);

insert into myemp9528(id,sname)

values(1,’bdl’);

commit;

6.2 删除数据

delete from 表名 where 条件;

commit;

delete from myemp9528 where id=1;

commit;

6.3 更新数据

update 表名 set 字段1名=值

​ where 条件;

update 表名 set 字段1名=值1,

​ 字段2名=值2

​ where 条件;

commit; | rollback;

update myemp9528 set salary=8888

​ where sname=’bdl’;

update s_emp set salary=12500

​ ,first_name=’bdl’ where id=12;

commit;

七.事务 (交易)

commit; 提交

rollback; 回滚

savepoint 保存点名;

原子性:

​ 事务中的操作 是一个不可分割的整体。

​ 转账

​ update account

​ set salary=salary-20000 where ano=’1’;

​ a

​ update account

​ set salary=salary+20000 where ano=’2’;

​ b

​ if(a&&b){

​ commit;

​ }else{

​ rollback;

​ }

​ 事务中的操作 要么一起成功 要么一起失败

隔离性:

​ 一个事务中的操作 在没有提交以前 对

​ 另一个事务而言 数据的变化是不可见的。

​ drop table testtcl;

​ create table testtcl(

​ id number primary key,

​ salary number

​ );

​ insert into testtcl values(1,2000);

​ insert into testtcl values(2,4000);

一致性:

​ -20000;

​ +20000

​ -50

持久性:

保存点:

​ 让事务做到 部分成功 部分失败。

​ insert

​ savepoint a;

​ update

​ savepoint b;

​ insert

​ savepoint c;

​ if(c){

​ rollback to b;

​ commit;

​ }

​ drop table testtcl2;

​ create table testtcl2(

​ id number primary key,

​ salary number

​ );

​ insert into testtcl2 values(2,2000);

​ savepoint a;

​ insert into testtcl2 values(3,3000);

​ savepoint b;

​ insert into testtcl2 values(4,4000);

​ savepoint c;

​ insert into testtcl2 values(5,5000);

​ savepoint d;

​ rollback to b;

​ commit;

八.日期类型

date

8.1 日期类型的表达

‘dd-MON-yy’

select start_date from s_emp;

8.2 把s_emp 表中 id first_name start_date

要求按照start_date排序

select id,first_name,start_date

​ from s_emp order by start_date;

8.3 日期格式显示

to_char(日期数据,’日期格式’)

yyyy 四位年

mm 2位月

dd 两位天

hh 12小时制

hh24

mi 分钟

ss 秒

day 星期几

month 月的全写

mon 月的缩写

pm 上午am 下午pm

select id,first_name,

​ to_char(start_date,

​ ‘yyyy-mm-dd hh24:mi:ss’)

​ from s_emp order by start_date;

select id,first_name,

​ to_char(start_date,

​ ‘yyyy-mm-dd hh24:mi:ss day pm’)

​ from s_emp order by start_date;

8.4 如何插入日期

建立一张订单表

drop table myorder9527;

create table myorder9527(

ono varchar2(50) primary key,

oname varchar2(30),

omoney number,

odate date

);

insert into myorder9527 values(

‘bj002’,’test002’,168.75,’18-AUG-14’);

commit;

select ono,to_char(odate,

​ ‘yyyy-mm-dd hh24:mi:ss’)

​ from myorder9527;

8.5 直接插入当前系统时间

insert into myorder9527 values(

‘bj003’,’test003’,168.75,sysdate);

commit;

to_date(‘日期字符串’,’日期格式’)

根据日期格式 把日期字符串 转换成日期

2008-08-08 20:08:08

2012-12-21 23:59:59

2020-01-01 00:48:15

Day04

八.日期类型

date

8.1 日期类型的表达

‘dd-MON-yy’

select start_date from s_emp;

8.2 把s_emp 表中 id first_name start_date

要求按照start_date排序

select id,first_name,start_date

​ from s_emp order by start_date;

8.3 日期格式显示

to_char(日期数据,’日期格式’)

yyyy 四位年

mm 2位月

dd 两位天

hh 12小时制

hh24

mi 分钟

ss 秒

day 星期几

month 月的全写

mon 月的缩写

pm 上午am 下午pm

select id,first_name,

​ to_char(start_date,

​ ‘yyyy-mm-dd hh24:mi:ss’)

​ from s_emp order by start_date;

select id,first_name,

​ to_char(start_date,

​ ‘yyyy-mm-dd hh24:mi:ss day pm’)

​ from s_emp order by start_date;

8.4 如何插入日期

建立一张订单表

drop table myorder9527;

create table myorder9527(

ono varchar2(50) primary key,

oname varchar2(30),

omoney number,

odate date

);

insert into myorder9527 values(

‘bj002’,’test002’,168.75,’18-AUG-14’);

commit;

select ono,to_char(odate,

​ ‘yyyy-mm-dd hh24:mi:ss’)

​ from myorder9527;

8.5 直接插入当前系统时间

insert into myorder9527 values(

‘bj003’,’test003’,168.75,sysdate);

commit;

to_date(‘日期字符串’,’日期格式’)

根据日期格式 把日期字符串 转换成日期

2008-08-08 20:08:08

2012-12-21 23:59:59

2020-01-01 00:48:15

8.6 to_date(‘日期字符串’,’日期格式’)

insert into 表名 values(to_date(

‘2008-08-08 20:08:08’,’yyyy-mm-dd hh24:mi:ss’));

insert into myorder9527(ono,odate)

values(‘testbj003’,

to_date(‘2008-08-08 20:08:08’,

‘yyyy-mm-dd hh24:mi:ss’));

insert into myorder9527(odate) values(

to_date(‘2008-08-08 20:08:08’,

‘yyyy-mm-dd hh24:mi:ss’));

8.7 日期的调整

select to_char(sysdate,

​ ‘yyyy-mm-dd hh24:mi:ss’) from dual;

/ 1代表一天 /

select to_char(sysdate+1,

​ ‘yyyy-mm-dd hh24:mi:ss’) from dual;

select to_char(sysdate,

​ ‘yyyy-mm-dd hh24:mi:ss’),

​ to_char(sysdate+1/24,

​ ‘yyyy-mm-dd hh24:mi:ss’) from dual;

select to_char(sysdate,

​ ‘yyyy-mm-dd hh24:mi:ss’),

​ to_char(sysdate+1/(24*60),

​ ‘yyyy-mm-dd hh24:mi:ss’) from dual;

1 1/24 1/(2460) 1/(2460*60)

8.8 特殊调整

按照月为单位进行调整

add_months(日期,月数)

select to_char(sysdate,

​ ‘yyyy-mm-dd hh24:mi:ss’),

​ to_char(add_months(sysdate,3),

​ ‘yyyy-mm-dd hh24:mi:ss’) from dual;

select months_between(sysdate,sysdate+5)

​ from dual;

last_day(日期) 一个月份对应的这个月的最后一天

​ 的时间点

select to_char(sysdate,

​ ‘yyyy-mm-dd hh24:mi:ss’),

​ to_char(last_day(sysdate),

​ ‘yyyy-mm-dd hh24:mi:ss’) from dual;

next_day(日期,’星期几’)

select to_char(sysdate,

​ ‘yyyy-mm-dd hh24:mi:ss’),

​ to_char(next_day(sysdate,’monday’),

​ ‘yyyy-mm-dd hh24:mi:ss’) from dual;

select to_char(sysdate,

​ ‘yyyy-mm-dd hh24:mi:ss’),

​ to_char(next_day(

​ next_day(sysdate,’friday’),’friday’),

​ ‘yyyy-mm-dd hh24:mi:ss’) from dual;

round(日期) 默认以天为单位对日期进行四舍五入

select to_char(sysdate,

​ ‘yyyy-mm-dd hh24:mi:ss’),

​ to_char(round(sysdate),

​ ‘yyyy-mm-dd hh24:mi:ss’) from dual;

round(日期,’日期单位格式’)

​ select to_char(sysdate,

​ ‘yyyy-mm-dd hh24:mi:ss’),

​ to_char(round(sysdate,’mm’),

​ ‘yyyy-mm-dd hh24:mi:ss’) from dual;

​ select to_char(sysdate,

​ ‘yyyy-mm-dd hh24:mi:ss’),

​ to_char(round(sysdate,’hh’),

​ ‘yyyy-mm-dd hh24:mi:ss’) from dual;

trunc(日期) 默认以天为单位对日期进行截取

​ select to_char(sysdate,

​ ‘yyyy-mm-dd hh24:mi:ss’),

​ to_char(trunc(sysdate),

​ ‘yyyy-mm-dd hh24:mi:ss’) from dual;

round(日期,’日期单位格式’)

​ select to_char(sysdate,

​ ‘yyyy-mm-dd hh24:mi:ss’),

​ to_char(trunc(sysdate,’mm’),

​ ‘yyyy-mm-dd hh24:mi:ss’) from dual;

​ select to_char(sysdate,

​ ‘yyyy-mm-dd hh24:mi:ss’),

​ to_char(trunc(sysdate,’hh’),

​ ‘yyyy-mm-dd hh24:mi:ss’) from dual;

8.9 把一个日期字符串 ‘2008-08-08 20:08:08’

​ 经过转换 得到一个对应的日期。然后得到

​ 这个日期对应的月的最后一天的最后一秒

​ 对应的时间点。使用to_char 验证是否正确。

​ 2008-08-31 23:59:59

​ 2012-12-21 10:05:04

​ 2012-12-31 23:59:59

​ select to_char(

​ trunc(last_day(to_date(‘2008-08-08 20:08:08’,

​ ‘yyyy-mm-dd hh24:mi:ss’))+1)-1/(246060),

​ ‘yyyy-mm-dd hh24:mi:ss’) from dual;

​ select to_char(

​ trunc(add_months(to_date(‘2008-08-08 20:08:08’,

​ ‘yyyy-mm-dd hh24:mi:ss’),1),’mm’)

​ -1/(246060),’yyyy-mm-dd hh24:mi:ss’)

​ from dual;

九.数据库中的约束

​ 9.1 约束

​ 对数据库中的字段 对应的数据可以加限制。

​ 9.2 种类

​ 主键 primary key

​ 字段的值 非空 并且 唯一

​ 一个表只能有一个主键

​ 唯一 unique

​ 字段的值 必须保持不能重复

​ 非空 not null

​ 字段的值 不能是NULL值

​ 检查 check

​ 字段的值 必须符合检查条件

​ 外键约束 reference (关联 引用)

​ foreign key

​ on delete cascade

​ on delete set null

​ 9.3 约束的具体实现

​ 列级约束:在定义表的某一列时 直接对这一列加

​ 约束限制。

​ 表级约束:在定义完表的所有列之后 再选择某些

​ 列加约束限制。

​ 9.4 主键的列级约束

​ drop table testcolumn_cons;

​ create table testcolumn_cons(

​ id number primary key,

​ name varchar2(20)

​ );

​ insert into testcolumn_cons

​ values(1,’test1’);

​ ERROR at line 1:

​ ORA-00001: unique constraint

​ (OPENLAB.SYS_C00444679) violated

​ 如果加约束时 不给约束起名字 则系统会自动

​ 分配一个约束名。

​ 可以给约束命名

​ drop table testcolumn_constest1;

​ create table testcolumn_constest1(

​ id number constraint

​ testcolumn_constest1_id_pk primary key,

​ name varchar2(20)

​ );

​ insert into testcolumn_constest1

​ values(1,’test1’);

ERROR at line 1:

ORA-00001: unique constraint

(OPENLAB.TESTCOLUMN_CONSTEST1_ID_PK) violated

9.5 建立一张表

​ id number 设置成主键

​ fname varchar2(30) 设置成非空

​ sname varchar2(30) 设置成唯一

​ salary number 设置检查 必须大于3500

​ drop table testemp1986abc;

​ create table testemp1986abc(

​ id number constraint

​ testemp1986abc_id_pk primary key,

​ fname varchar2(20) constraint

​ testemp1986abc_fname_nn not null,

​ sname varchar2(20) constraint

​ testemp1986abc_sname_uk unique,

​ salary number constraint

​ testemp1986abc_salary_ck check(

​ salary>3500 )

​ );

9.6 表级约束 主键

​ 表级约束 可以完成联合约束

​ create table testtable_cons(

​ id number,

​ name varchar2(30),

​ salary number ,

​ constraint testtable_cons_id_pk

​ primary key(id,name)

​ );

​ create table testtable_cons(

​ id number,

​ name varchar2(30),

​ salary number ,

​ constraint testtable_cons_id_pk

​ primary key(id),

​ constraint testtable_cons_name_uk

​ unique (name),

​ constraint testtable_cons_salary_ck

​ check(salary>3500)

​ );

十.外键约束

​ 10.1 涉及到两张表 一张叫父表(主表)

​ 一张叫子表(从表)。子表中的外键字段的取值

​ 受限于父表中字段的取值。

​ 外键的取值 要么取父表中字段的值

​ 要么取NULL值。

​ 10.2 外键关系的实现

​ 10.2.1 建立表

​ 一般先建立父表 后建立子表

​ drop table parent1234a;

​ create table parent1234a(

​ id number primary key,

​ name varchar2(30)

​ );

​ drop table child1234a;

​ create table child1234a(

​ id number primary key,

​ age number,

​ fid number constraint

​ child1234a_fid_fk references

​ parent1234a(id)

​ );

​ 10.2.2 插入数据

​ 一般先插入父表数据 后插入子表数据

​ 否则子表的外键取值要取NULL

​ insert into child1234a values(

​ 9527,50,1);

​ ERROR at line 1:

​ ORA-02291: integrity constraint

​ (OPENLAB.CHILD1234A_FID_FK) violated -

​ parent key not found

​ insert into child1234a values(

​ 9527,50,NULL);

​ insert into parent1234a values(

​ 1,’parent1’);

​ insert into child1234a values(

​ 9529,55,1);

​ 10.2.3 删除数据?

​ 先删子表中和父表关联的数据

​ 再删父表数据

​ 级联 (on delete cascade )

​ 10.2.4 删除表

​ 先删子表 后删父表

​ / 先解除主外键关系 后删表 /

​ drop table 表名 cascade constraints;

10.3 级联删除 和 级联置空

​ on delete cascade

​ 员工表 m (子表)

​ id

​ ename

​ eage

​ dept_id

​ 部门表 1 (主表)

​ id

​ name

​ drop table mydept1402 cascade constraints;

​ create table mydept1402(

​ id number primary key,

​ name varchar2(30)

​ );

​ insert into mydept1402 values(1,’app’);

​ insert into mydept1402 values(2,’test’);

​ commit;

​ drop table myemp1402 cascade constraints;

​ create table myemp1402(

​ id number primary key,

​ ename varchar2(30),

​ eage number,

​ dept_id number constraint

​ myemp1402_dept_id_fk references

​ mydept1402(id)

​ );

​ insert into myemp1402 values(1,’ea’,

​ 24,1);

​ insert into myemp1402 values(2,’eb’,

​ 25,1);

​ insert into myemp1402 values(3,’ec’,

​ 26,2);

​ insert into myemp1402 values(4,’ed’,

​ 24,2);

​ insert into myemp1402 values(5,’ee’,

​ 27,2);

​ commit;

​ on delete cascade 删除主表数据时 会把

​ 和主表关联的子表数据删除。

​ on delete set null 删除主表数据时 会把

​ 和主表关联的子表数据的外键置成null。

10.4 修改脚本 把列级约束的外键实现 写成

​ 表级约束的实现。

​ ALTER TABLE s_emp

​ ADD CONSTRAINT s_emp_dept_id_fk

​ FOREIGN KEY (dept_id)

​ REFERENCES s_dept (id);

​ 先建立两张表 然后通过修改表结构 增加

​ 外键约束,但这样对数据要求非常严格。

十一.数据库中其它对象

​ 11.1 序列 sequence

​ 用来产生主键的值。

​ 如何创建序列

​ create sequence 序列名;

​ 如何使用

​ 在需要主键值的地方 写 序列名.nextval

​ create sequence testmyseqbdl_id;

​ create table testmyseqbdl(

​ id number primary key,

​ name varchar2(30)

​ );

​ insert into testmyseqbdl values(

​ testmyseqbdl_id.nextval,

​ ‘test’||testmyseqbdl_id.currval);

​ 11.2 索引

​ 目的:

​ 加速查询。

​ 3亿 8*60 全表扫描

​ 0.01 索引查找

​ 原理:

​ 通过树状结构组织数据 通过消耗

​ 大量的时间 和 空间 来加速查询。

​ 语法:

​ 具有唯一性字段的数据 会自动建立索引

​ 叫唯一性索引。

​ create index 索引名

​ on 表名(字段名);

​ set timing on;

​ create table testemp1402

​ as select id,first_name name,

​ salary from s_emp;

​ create index testemp1402_name_ind

​ on testemp1402(name);

​ 删除索引

​ drop index 序列名;

​ 11.3 视图 view

​ 本质:

​ 视图本质上 就是一条sql语句。

​ 如何创建视图

​ create or replace view 视图名 as

​ select 语句;

​ 可以对同一份物理数据 做不同的表现

​ 可以简化查询

​ create or replace view myview

​ as select id ,first_name name,

​ salary from s_emp;

​ select * from (select id ,first_name

​ name, salary from s_emp);

​ select * from myview;

十二.分页技术

​ oracle rownum

​ sqlserver top

​ mysql limit m,n

​ rownum 行号 伪列

​ select rownum, id,first_name from s_emp;

​ 一页显示 5 行 取第一页数据

​ select rownum, id,first_name from s_emp

​ where rownum<6;

​ select * from(select rownum r, id,

​ first_name from s_emp where rownum<11)

​ where r>5;

​ 按照salary 排序 一页显示 5 条数据

​ 显示第二页数据。

​ first_name salary

​ select first_name ,salary from s_emp

​ order by salary;

​ 先排序 还是先编号?

​ select rownum, first_name ,salary

​ from s_emp

​ order by salary;

​ 先排序 后编号

​ select * from(

​ select rownum r,first_name,salary from

​ (select first_name ,salary

​ from s_emp

​ order by salary

​ )where rownum<11

​ )where r>5;

​ 按照某个字段排序 要第n页数据 至少三层查询

​ 最内层 负责排序

​ 中间层 负责编号 并去rownum特性

​ 最外层 在去除rownum特性的基础上

​ 过滤掉 第n-1页数据

​ 按照salary 排序 一页显示 5 条数据

​ 显示第三页数据。

​ select * from(

​ select rownum r,first_name,salary from

​ (select first_name ,salary

​ from s_emp

​ order by salary

​ )where rownum<n*pagesize+1

​ )where r>(n-1)*pagesize;

​ select * from(

​ select rownum r,first_name,salary from

​ (select first_name ,salary

​ from s_emp

​ order by salary

​ )where rownum<3*5+1

​ )where r>(3-1)*5;

​ select * from(

​ select rownum r,first_name,salary from

​ (select first_name ,salary

​ from s_emp

​ order by salary

​ )where rownum<3*11+1

​ )where r>(3-1)*11;