SQL语言

01SQL 语言基础

·什么是 SQL 语言

　　a) SQL, Structured Query Language, 结构化查询语言
　　b) SQL 是最重要的关系数据库操作语言，是所有关系数据库管理系统的标准语言.
　　c) SQL 语言是非过程化的语言, 只需要告诉做什么, 不需要关注怎么做, 简单.

·SQL 语言可以做什么

　　a) 增删改查(CRUD)
　　b) 操作数据库对象(用户, 表格, 序列, 索引...)

　　c) 操作用户权限和角色的授予跟取消
　　d) 事务(Transaction)管理

· SQL 语言的分类

　　a) DQL, Data Query Language, 数据查询语言执行数据库的查询操作, select
　　b) DML, Data Manipulation Language, 数据操作语言操作表格中的数据, 执行增删改, insert, delete, update

　　c) DDL, Data Definition Language, 数据定义语言，用于操作数据库对象, create, alter, drop
　　d) DCL, Data Control Language, 数据控制语言，操作用户权限, grant, revoke
　　e) TCL, Transaction Control Language, 事务控制语言，用于管理事务, commit, rollback

02select子句

·select 子句

　　用于指定查询表格中的列信息

·通配符*

　　查询 emp 表格的所有数据
 * 通配符, 表示所有的列

select * from emp;

·指定列

　　查询所有员工的编号, 姓名和职位

select empno, ename, job from emp;

·支持算数运算

　　查询所有员工的姓名, 职位和年薪
select 子句中, 支持算数运算

select ename, job, sal*12 from emp;

·列别名

　　a)select 子句中, 可以通过 as 关键字给列起别名

select ename, job, sal*12 as nianxin from emp;

　　b)as 关键字可以被省略, 一般都省略

select ename, job, sal*12 nianxin from emp;

　　c)别名中, 尽量不要使用特殊符号, 例如空格; 如果非要有特殊符号, 可以使用双引号括起来.

　　d)在 Oracle 中, 双引号表示原样输出.

select ename, job, sal*12 "ni a n xin" from emp;

03distinct子句_字符串连接符_order by

·distinct

　　用于去除重复行信息 a) 查询所有的职位信息

select distinct job from emp;

　　b)查询所有员工的姓名和职位
distinct 只能去除重复行, distinct 的作用范围是它后面的所有列

select distinct ename, job from emp;

·字符串连接符 ||

　　Oracle 中, 用单引号表示字符串
　　a) 查询所有员工的姓名, 职位和薪资, 以姓名:xxx, 职位:xxx, 薪资:xxx 的形式显示

select '姓名:'||ename||',职位:'||job||',薪资:'||sal 
info from emp;

·order by 子句

　　用于进行排序, 永远写在语句的最后
　　a)查询所有员工的信息, 按照工资升序排序

　　asc, 表示升序(ascend)
　　默认情况下, 按照升序排序, 所以, asc 一般被省略

select * from emp order by sal asc;

　　b)查询所有员工的信息, 按照标号降序排序 desc, 表示降序排序(descend)

select * from emp order by empno desc;

　　c)查询所有员工的信息, 按照入职日期降序排序

select * from emp order by hiredate desc;

　　d)查询所有员工的信息, 按照姓名排序

select * from emp order by ename;

　　e)查询所有员工信息, 按照薪资降序排序, 如果薪资相同, 将新员工排在前面.

select * from emp order by sal desc, hiredate desc;

　　f)查询所有员工的姓名和年薪, 按照年薪排序

select ename, sal*12 nianxin from emp order by nianxin 
desc;

04where子句_等值条件和非等值条件

·where 子句

　　用于进行条件过滤

·等值条件

　　a)查询 10 部门所有员工的信息

select * from emp where deptno=10;

　　b)查询 SCOTT 的详细信息

select * from emp where ename=scott;✖

　　字符串必须加单引号, 而且大小写敏感

select * from emp where ename='SCOTT';

　　c)查询在 1982-01-23 入职的员工信息
　　> 日期必须用单引号括起来;
　　> 日期格式必须是: DD-MM 月-YY

select * from emp where hiredate='23-1 月-82';

·非等值条件

　　a)查询工资在 1500 到 3000 之间的员工信息
　　 > between...and...表示一个范围, 包含边界

select * from emp where sal>=1500 and sal<=3000;

select * from emp where sal between 1500 and 3000;

　　b)查询 SCOTT 和 KING 的详细信息

select * from emp where ename='SCOTT' or ename='KING';

select * from emp where ename in ('SCOTT', 'KING');

　　c)查询所有员工的信息, 排除 20 部门

select * from emp where deptno != 20;

select * from emp where deptno <> 20;

05where子句_模糊查询

·模糊查询

　　使用 like(像)实现, 配合通配符实现
　　_, 表示任意一个字符
　　%, 表示任意个任意字符
　　a)查询姓名首字母为 A 的员工的信息

select * from emp where ename like 'A%';

　　b)查询姓名第二个字母是 A 的员工的信息

select * from emp where ename like '_A%';

　　c)查询姓名中带有字母 C 的员工的信息

select * from emp where ename like '%C%';

　　d)查询姓名中带有下划线的员工的信息
　　escape 用于声明转义字符.

select * from emp where ename like '%a_%' escape 'a';

06where子句_is null_优先级

·IS NULL

　　用于判断空值
　　a)查询所有没有提成的员工信息

select * from emp where comm is null;

　　b)查询所有有提成的员工信息

select * from emp where comm is not null;

select * from emp where not comm is null;

·and 和 or 的优先级

　　and 的优先级高于 or 的优先级
　　a)查询所有CLERK 的信息和工资大于1250 的SALESMAN 的信..

select * from emp where job='CLERK' or job='SALESMAN' and sal>1250;

　　b)查询所有的 CLERK 和 SALESMAN 的信息同时工资要大于1250.

select * from emp where (job='CLERK' or job='SALESMAN') and sal>1250;

07函数_字符函数

·lower, upper, initcap

　　a) lower, 将所有字母小写

　　b) upper, 将所有字母大写
　　c) initcap, 所有单词首字母大写

select ename,lower(ename), upper(ename),initcap(ename) from emp

　　查询所有员工的姓名, 分别显示小写, 大写和首字母大写
　　在 Oracle 中, 提供了一个虚拟表格, 叫 dual, 专门用于进行测试, 可以从 daul 中查询任何数据

·length

　　用于计算字符串的长度
　　a)查询所有员工的姓名及姓名的长度

select ename, length(ename) from emp;

　　b)查询所有姓名长度为 5 的员工的信息

select * from emp where length(ename)=5;

·replace

　　用于进行字符串的替换

　　a) 查询所有员工的姓名, 将 A 替换为 a

select ename, replace(ename, 'A', 'a') from emp;

·substr

　　用于进行字符串的截取
　　a)查询所有员工的姓名, 并显示姓名的前 3 个字母

select ename, substr(ename, 1, 3) from emp;

　　b)查询所有员工的姓名, 并显示姓名的后 3 个字母

select ename, substr(ename, length(ename)-2, 3) from emp;

　　第三个参数可以省略, 表示一直截取到末尾, 所以可以简写为:

select ename, substr(ename, length(ename)-2) from emp;

　　第二个参数可以是负数, 表示倒着截取, 又可以简写为:

select ename, substr(ename, -3) from emp;

08函数_数值函数_日期函数

·数值函数

　　1.1ceil

　　向上取整

select ceil(123.0000001) from dual;

　　1.2floor

　　向下取整

select floor(123.9999999) from dual;

　　1.3round

　　四舍五入

　　第二个参数可以控制四舍五入的位数, 正数表示小数点后, 负数表示小数点前.

select round(156.9444449, -2) from dual;

　　1.4trunc

　　截断

select trunc(123.456, 1) from dual;

·日期函数

　　2.1sysdate

　　获取系统时间

select sysdate from dual;

　　2.2months_between

　　计算两个日期间的月数

　　a) 查询所有员工的入职的月数

select ename, months_between(sysdate, hiredate) from emp;

　　2.3add_months

　　给日期加减月数

select sysdate, add_months(sysdate, -5) from dual;

　　2.4last_day

　　计算给定日期所在月份的最后一天是哪个日期

select sysdate, last_day(sysdate) from dual;

　　2.5next_day

　　基于给定日期计算下个给定的星期几是什么日期

select sysdate, next_day(sysdate, '星期二') from dual;

09函数_转换函数

·转换函数

　　用于在不同数据类型间进行转换. 数值类型, 字符串类型, 日期类型

·to_number

　　将字符串转换为数字. 涉及到钱的时候,$123,123,123.00123123123.00

select to_number('￥123,123,123.00', 'L999,999,999.00')+1 from dual;

·to_date

　　将字符串转换为日期

select to_date('2022-12-12 23:20:20', 'YYYY-MM-DD HH24:MI:SS') from dual;

·to_char

　　将数字或日期转换为字符串

select to_char(1231231231, 'L999,999,999,999.99') from dual;

select sysdate, to_char(sysdate, 'yyyy"年"mm"月"dd"日" hh24:mi:ss') from dual;

10函数_通用函数

·nvl

　　用来处理空值, 如果某个字段为空, 则使用对应的数据进行替换注意: 两者的数据类型必须一致
　　查询所有员工的姓名, 工资, 提成和总工资(工资+提成)

select ename, sal, comm, sal+nvl(comm, 0) total from emp;

·nvl2

　　有三个参数, 如果第一个参数不为空, 则使用第二个参数, 如果为空, 则使用第三个参数

select ename, sal, comm, nvl2(comm, sal+comm, sal) total from emp;

·decode

　　类似于 switch...case...,

　　查询所有的职位, 并显示对应的中文描述

select distinct job, decode(job, 'CLERK', '职员', 'SALESMAN',  '销 售 ',  'PRESIDENT',  '董 事 长 ', 
'MANAGER', '经理', 'ANALYST', '分析师') job_zh from emp;

11函数_分组函数（聚集函数）

·sum

　　求和

　　查询所有员工的工资总和

select sum(sal) from emp;

·avg

　　求平均值

　　查询平均工资

select avg(sal) from emp;

·max

　　求最大值

　　统计公司的最高工资

select max(sal) from emp;

·min

　　求最小值

　　统计公司的最低工资

select min(sal) from emp;

·count

　　计数

　　统计公司的员工总数

select count(empno) from emp;

select count(*) from emp;

12group by_having

·group by

　　进行分组查询, group by 子句可以将数据分为若干个组

·分组查询

　　注意: 出现在 SELECT 子句中的字段，如果不是包含在多行函数中，那么该字段必须同时在GROUP BY 子句中出现。
　　a) 统计每个部门的编号, 最高工资和最低工资

select deptno, max(sal), min(sal) from emp group by deptno order by deptno;

·带 where 的分组查询

　　注意: group by 子句要写到 where 子句的后面
　　a) 查询每个部门的人数和平均工资, 排除 10 部门

select deptno, count(*), avg(sal) from emp where deptno<>10 group by deptno order by deptno;

·带 having 的分组查询

　　注意:
　　 > where 子句中不允许使用分组函数, 用于分组前过滤
　　> having 用于过滤分组后的条件

　　a)查询每个部门的总工资和平均工资, 排除平均工资低于1600 的部门

select deptno, sum(sal), avg(sal) from emp group by deptno having avg(sal)>=1600 order by deptno;

·select 语句的执行顺序

　　fromwheregroup byselecthavingorder by

　　a) 在 emp 表中，列出工资最小值小于2000 的职位

1 select job, min(sal) from emp 
2 group by job 
3 having min(sal)<2000;

　　b)列出平均工资大于 1200 元的部门和工作搭配组合

1 select deptno, job, avg(sal) 
2 from emp 
3 group by deptno,job 
4 having avg(sal)>1200 
5 order by deptno;

13DML_insert_update_delete

·复制一个测试表格

　　a)复制 emp 表格, 命名为 tmp

create table temp as (select * from emp);

　　b)赋值 emp 表格的表结构, 不复制数据

create table temp2 as (select * from emp where 1=2);

·新增(insert)

　　a)语法

insert into 表名 [(列 1, 列 2, ...)] values (值 1, 值 2,    );

　　b)向 temp2 表格插入一条数据

insert into temp2 (empno, ename, job, mgr, hiredate, sal, comm, deptno) values(1234, '小明', '学生', 1111, to_date('2008-8-8', 'yyyy-mm-dd'), 3000, 200, 10);

　　a)当表格的每一列都要插入数据时, 可以省略列名不写. 需要保证值的顺序和列的顺序一致.

insert into temp2 values (1235, '小红', '学生', 1111, to_date('2008-8-8', 'yyyy-mm-dd'), 3000, 200, 10);

　　b)向表格插入一行数据, 只有编号和姓名

insert into temp2 (empno, ename) values (1236, '小李');

·修改(update)

　　a)语法

update 表名 set 列 1=值 1[, 列 2=值 2...][where 条件]

　　b)将 10 部门员工的工资调高 10%.

update temp2 set sal=sal*1.1 where deptno=10;

　·删除(delete)

　　a)语法

delete [from] 表名 [where 条件];

　　b)删除编号为 1234 的员工信息

delete from temp2 where empno=1234;

14SQL99连接查询_cross join_natural join

·cross join(交叉连接)

　　交叉连接会产生一个笛卡尔积

select * from emp cross join dept;

　　在笛卡尔积中, 有很多数据是无意义的, 所以需要消除, 可以通过 where 子句来消除

select * from emp cross join dept where emp.deptno=dept.deptno;

　　可以在查询时, 给表格起别名

select e.*, dname, loc from emp e cross join dept d where e.deptno=d.deptno;

·natural join(自然连接)

　　natural join 用于针对多张表的同名字段进行等值连接

select * from emp e natural join dept d;

　　特点:
　　a)在自然连接时, 自动进行所有同名列的等值连接, 不需要写连接的条件
　　b)同名列只显示一列, 而且在使用时, 不能加表前缀
　　查询所有员工的姓名, 部门编号和部门名称

select e.ename, deptno, d.dname from emp e natural join dept d;

15SQL99连接查询_using_on

·using 子句

　　用来指定进行等值连接的同名字段, 针对自然连接提供的. 同名字段依然不能使用表前缀.
　　a) 查询 20 部门员工的姓名, 工资, 部门编号和部门名称

select e.ename, e.sal, deptno, d.dname from emp e 
natural join dept d where deptno=20;

select e.ename, e.sal, deptno, d.dname from emp e join 
dept d using (deptno) where deptno=20;

·on 子句

　　on 子句是使用非常广泛的子句, 它可以被用来指定连接的条件. 用于将过滤条件和关联条件分　　　　开.
　　a)查询所有员工的姓名, 工资和工资等级

select e.ename, e.sal, s.grade from emp e join 
salgrade s on e.sal>=s.losal and e.sal<=s.hisal;

select e.ename, e.sal, s.grade from emp e join 
salgrade s on e.sal between s.losal and s.hisal;

　　b)查询 30 部门员工的编号, 姓名, 部门名称和所在地

select e.empno, e.ename, d.dname, d.loc

　from emp e join dept d

on e.deptno=d.deptno

where d.deptno=30;

　　c)查询所有员工的姓名, 部门名称, 工资及工资等级
　　N 张表连接, 至少需要 N-1 个连接条件

1 select e.ename, d.dname, e.sal, s.grade from emp e 
2 join dept d 
3 on e.deptno=d.deptno join salgrade s 
4 on e.sal between s.losal and s.hisal;

16SQL99连接查询_外连接

·外连接

　　外连接除了能显示满足连接条件的数据以外, 还用于显示不满足连接条件的数据

·左外连接

　　left [outer] join, 表示左外连接, 可以显示左表中不满足连接条件的数据

select e.ename, e.job, d.deptno, d.dname, d.loc from dept d 
left join emp e 
on e.deptno=d.deptno;

·右外连接

　　right [outer] join, 表示右外连接, 可以显示右表中不满足连接条件的数据

select e.ename, e.job, d.deptno, d.dname, d.loc from emp e 
right join dept d 
on e.deptno=d.deptno;

　·全外连接

　　full [outer] join, 表示全外连接, 可以显示左右两表中不满足连接条件的数据

select e1.empno, e1.ename, e2.empno, e2.ename from emp e1 
full join emp e2  on e1.mgr=e2.empno 
order by e1.empno;

17SQL99连接查询_自连接

·自连接

　　自连接是发生在同一个表格中的连接
　　a)查询所有员工的编号, 姓名和领导的编号及姓名

select e1.empno, e1.ename, e2.empno, e2.ename from emp e1 
join emp e2 
on e1.mgr=e2.empno order by e1.empno;

　　b)查询所有员工的编号, 姓名和领导的编号及姓名, 并显示没有领导的员工信息

select e1.empno, e1.ename, e2.empno, e2.ename from emp e1 
left join emp e2  on e1.mgr=e2.empno 
order by e1.empno;

18SQL92连接查询

　　SQL92 多表连接查询

·笛卡尔积

　　多张表通过逗号分隔

select * from emp e, dept d;

　　消除无意义数据

select * from emp e, dept d where e.deptno=d.deptno;

·等值连接

　　a) 查询 10 部门员工的姓名, 职位和部门名称

select e.ename, e.job, d.dname from emp e, dept d where 
e.deptno=d.deptno and d.deptno=10;

·非等值连接

　　a) 查询所有员工的姓名, 工资和工资等级

select e.ename, e.sal, s.grade from emp e, salgrade 
s where e.sal between s.losal and s.hisal;

·外连接

　　通过”(+)”符号实现外连接

　　左外连接

select e.ename, d.deptno, d.dname, d.loc from emp e, dept d 
where d.deptno=e.deptno(+);

　　右外连接

select e.ename, d.deptno, d.dname, d.loc from emp e, dept d 
where e.deptno(+)=d.deptno;

·自连接

select e1.empno, e1.ename, e2.empno, e2.ename from emp e1, emp e2 
where e1.mgr=e2.empno(+);

19子查询_1

·子查询

　　用于当一次查询的结果是另一次查询所需要的时候, 可以使用子查询

·单行子查询

　　子查询的返回结果是单行数据.
　　a)查询所有比“CLARK”工资高的员工的信息

select * from emp where sal > (select sal from emp where ename='CLARK');

　　b)查询工资高于平均工资的雇员名字和工资

select ename, sal from emp where sal>(select avg(sal) from emp);

　　c)查询和 SCOTT 同一部门且比他工资低的雇员名字和工资

select ename, sal, deptno from emp 
where deptno=(select deptno from emp where ename='SCOTT') 
and sal<(select sal from emp where ename='SCOTT');

　　d)查询职务和 SCOTT 相同，比 SCOTT 雇佣时间早的雇员信息

select * from emp 
where job=(select job from emp where ename='SCOTT') and hiredate<(select hiredate from emp where ename='SCOTT');

·多行子查询

　　　子查询的返回结果是多行数据. 此时, 不能再使用普通的比较运算符了.
　　多行记录比较运算符:
　　ANY: 跟结果中的任何一个数据进行比较
　　查询工资低于任何一个“CLERK”的工资的雇员信息

select * from emp where sal<ANY(select sal from emp 
where job='CLERK') and job<>'CLERK';

　　ALL: 跟结果中的所有数据进行比较
　　查询工资比所有的“SALESMAN”都高的雇员的编号、名字和工资

select empno, ename, sal from emp 
where    sal>ALL(select    sal    from    emp    where 
job='SALESMAN');

　　IN: 等于结果中的任何一个

　　查询部门 20 中职务同部门 10 的雇员一样的雇员信息

select * from emp 
where job in (select job from emp where deptno=10) 
and deptno=20;

select * from emp 
where job =any (select job from emp where deptno=10) 
and deptno=20;

20子查询_2

·相关子查询和不相关子查询

　　不相关子查询: 子查询不会用到外查询的数据, 子查询可以独立运行.
　　相关子查询: 子查询会用到外查询的数据, 子查询不能独立运行.
　　查询本部门最高工资的员工a) 不相关子查询的实现方式

select * from emp e 
where (e.deptno, e.sal) in (select deptno, max(sal) 
from emp group by deptno);

　　b) 相关子查询的实现方式

select * from emp e 
where    sal=(select    max(sal)    from    emp    where 
deptno=e.deptno);

·子查询可以作为一张表格进行多表连接查询

　　查询每个部门平均薪水的等级

select t.deptno, t.avg_sal, s.grade from salgrade s join ( 
     select deptno, avg(sal) avg_sal from emp group by deptno ) t 
on t.avg_sal between s.losal and s.hisal order by t.deptno;

21用户user

·用户的创建

　　a)语法
　　b)创建用户 bjsxt, 设定密码为 bjsxt

create user 用户名 identified by 密码;

　　注意: 操作数据库对象是需要 dba 权限的

create user bjsxt identified by bjsxt;

22用户的权限

·给用户授权

　　Oracle 中的权限很多, 为了方便用户管理权限, 提供了角色这个概念.
　　角色代表一个身份, 该身份拥有固定的权限. 常见的角色:
　　DBA: 数据库管理员
　　 CONNECT: 临时用户, 拥有少量的权限
　　RESOURCE: 比较靠谱的用户, 拥有更多的权限, 不能管理数据库
　　a)给 bjsxt 用户授予普通用户的权限

grant connect, resource to bjsxt;

　　b)从 bjsxt 用户撤销 resource 权限

revoke resource from bjsxt;

·删除用户

drop user bjsxt;

·修改用户

　　修改用户密码

alter user bjsxt identified by 123;

　　锁定用户和解锁定用户

　　a) 锁定用户

alter user bjsxt account lock;

　　c) 解锁用户

alter user bjsxt account unlock;

23数据库表格的创建

·Oracle 中的数据类型

　　1.1字符类型

　　a) varchar2
　　可变长度的字符串, 效率较低b) char
　　不可变长度的字符串, 效率较高

　　1.2数值类型

　　number, 既可以表示整数, 也可以表示浮点数

　　1.3日期时间类型

　　a)date, 存放日期和时间
　　b)timestamp, 比 date 更精确的日期时间类型

　　1.4lob 类型

　　a)blob, 用于存放二进制数据, 可以用于存放文件, 图片,
　　音频, 视频等二进制数据.
　　b)clob, 用于存放大文本信息.

·创建表格

　　创建学生表, 字段包含学号、姓名、性别，年龄、入学日期、班级，email 等信息

1 create table student ( 
2        sno number(4), 
3        sname varchar2(12), 
4        gender char(3), 
5        age number(3), 
6        sdate date, 
7        clazz varchar2(20), 
8        email varchar2(50) 
9 );

24表格的修改和删除

·修改表格

　　添加字段

alter table student add (score number(3));

　　修改字段的类型

alter table student modify (score number(5,2));

　　重命名字段

alter table student rename column score to fenshu;

　　删除字段

alter table student drop column fenshu;

　　重命名表格

rename student to stu;

·删除表格

drop table stu;

25表格的约束_主键约束

·表格的约束

　　constraints, 约束, 用于对表格的数据进行限制, 保证表格数据的完整性和一致性.
　　语法:

constraints 约束名 约束类型 (约束字段)

·主键约束(primary key)

　　主键是用于唯一标识一条记录的字段, 必须保证既非空又唯一.
　　一张表中, 只能有一个主键.
　　a)在表级别定义主键约束, 对约束统一管理

 1 create table student ( 
 2        sno number(4), 
 3        sname varchar2(12), 
 4        gender char(3), 
 5        age number(3), 
 6        sdate date, 
 7        clazz varchar2(20), 
 8        email varchar2(50), 
 9        constraints pk_student primary key (sno) 
10 );

　　b)表级别简化版定义主键约束, 省略约束名

 1 create table student ( 
 2        sno number(4), 
 3        sname varchar2(12), 
 4        gender char(3), 
 5        age number(3), 
 6        sdate date, 
 7        clazz varchar2(20), 
 8        email varchar2(50), 
 9        primary key (sno) 
10 );

　　c)在列级别定义主键约束

1 create table student ( 
2        sno number(4) constraints pk_student primary key, 
3        sname varchar2(12), 
4        gender char(3), 
5        age number(3), 
6        sdate date, 
7        clazz varchar2(20), 
8        email varchar2(50) 
9 );

　　d)在列级别简化版定义主键约束

1 create table student ( 
2        sno number(4) primary key, 
3        sname varchar2(12), 
4        gender char(3), 
5        age number(3), 
6        sdate date, 
7        clazz varchar2(20), 
8        email varchar2(50) 
9 );

　　e)联合主键, 只能在表级别定义, 因为一个表只能有一个主键

create table student ( 
       sno number(4), 
       sname varchar2(12), 
       gender char(3), 
       age number(3), 
       sdate date, 
       clazz varchar2(20), 
       email varchar2(50), 
       constraints pk_student primary key (sno, email) 
);

26表格的约束_非空约束_唯一约束

·非空约束(not null)

　　字段值不允许为空, 非空约束只能在列级别定义

 1 create table student ( 
 2        sno number(4), 
 3        sname varchar2(12) not null, 
 4        gender char(3), 
 5        age number(3), 
 6        sdate date, 
 7        clazz varchar2(20), 
 8        email varchar2(50), 
 9        constraints pk_student primary key (sno) 
10 );

·唯一约束(unique)

　　要求字段值不能重复.

 1 create table student ( 
 2        sno number(4), 
 3        sname varchar2(12) not null, 
 4        gender char(3), 
 5        age number(3), 
 6        sdate date, 
 7        clazz varchar2(20), 
 8        email varchar2(50), -- unique, 
 9        constraints pk_student primary key (sno), 
10        constraints uk_student_email unique (email) 
11 );

27表格的约束_检查约束_外键约束

·检查约束(check)

　　用于限定字段值的取值范围

 1 create table student ( 
 2        sno number(4), 
 3        sname varchar2(12) not null, 
 4        gender char(3) check (gender in ('男', '女')), 
 5        age number(3), 
 6        sdate date, 
 7        clazz varchar2(20), 
 8        email varchar2(50), 
 9        constraints pk_student primary key (sno), 
10        constraints uk_student_email unique (email), 
11        constraints ck_student_age check (age between 18 and 30) 
12 );

·外键约束(foreign key)

　　用于约束表和表之间的关系. 两张表的依赖关系. 以 emp 表和 dept 表为例, emp 表依赖 dept 表. 因此, dept 表可以被称之为　　主表, emp 表被称之为从表.
　　注意:
　　主表中, 只有主键或者唯一键才可以被从表参考.
　　从表中作为外键的列, 类型一定要和主表的被参考列相同.
　　a)建立主表 clazz

1 create table clazz ( 
2        cno number(3) primary key, 
3        cname varchar2(20) not null, 
4        croom number(3) 
5 );

　　b)创建从表, 并定义外键 student

 1 create table student ( 
 2        sno number(4), 
 3        sname varchar2(12) not null, 
 4        gender char(3) default '男' check (gender in ('男', '女')), 
 5        age number(3), 
 6        sdate date, 
 7        email varchar2(50), 
 8        cno number(3), 
 9        constraints pk_student primary key (sno), 
10        constraints uk_student_email unique (email), 
11        constraints ck_student_age check (age between 18 and 30), 
12        constraints fk_student_cno foreign key (cno) references clazz (cno) 
13 );

　　c)也可以在列级别定义外键, 如下:

 1 create table student ( 
 2        sno number(4), 
 3        sname varchar2(12) not null, 
 4        gender char(3) default '男' check (gender in ('男', '女')), 
 5        age number(3), 
 6        sdate date, 
 7        email varchar2(50), 
 8        cno number(3) references clazz(cno), 
 9        constraints pk_student primary key (sno), 
10        constraints uk_student_email unique (email), 
11        constraints ck_student_age check (age between 18 and 30) 
12 );

28表格的约束_外键约束_修改表时添加约束

·外键中的删除问题:

　　主表中的数据在被引用时, 无法直接删除, 默认情况下, 需要先删除从表中对应的所有数据, 再删除主表中的数据.
　　可以在定义外键时, 设置删除策略为 cascade(级联), 表示当删除主表的信息时, 同时删除从表中所有关联的信息。

 1 create table student ( 
 2        sno number(4), 
 3        sname varchar2(12) not null, 
 4        gender char(3) default '男' check (gender in ('男', '女')), 
 5        age number(3), 
 6        sdate date, 
 7        email varchar2(50), 
 8        cno number(3), 
 9        constraints pk_student primary key (sno), 
10        constraints uk_student_email unique (email), 
11        constraints ck_student_age check (age between 18 and 30), 
12        constraints fk_student_cno foreign key (cno) references clazz (cno) on delete cascade 
13 );

　　可以在定义外键时, 设置删除策略为 set null(设空), 表示当删除主表的信息时, 将从表中的相关数据设置为 null

 1 create table student ( 
 2        sno number(4), 
 3        sname varchar2(12) not null, 
 4        gender char(3) default '男' check (gender in ('男', '女')), 
 5        age number(3), 
 6        sdate date, 
 7        email varchar2(50), 
 8        cno number(3), 
 9        constraints pk_student primary key (sno), 
10        constraints uk_student_email unique (email), 
11        constraints ck_student_age check (age between 18 and 30), 
12        constraints fk_student_cno foreign key (cno) references clazz (cno) on delete set null 
13 );

·在修改表格时添加约束

　　a)创建主表 clazz

1 create table clazz ( 
2        cno number(3) primary key, 
3        cname varchar2(20) not null, 
4        croom number(3) 
5 );

　　b)创建从表 student

1 create table student ( 
2        sno number(4), 
3        sname varchar2(12) not null, 
4        gender char(3) default '男', 
5        age number(3), 
6        sdate date, 
7        email varchar2(50), 
8        cno number(3) 
9 );

　　c)给 student 表添加约束

-- 主键约束 
alter table student add constraints pk_student primary key (sno); 
-- 唯一约束 
alter table student add constraints uk_student_email unique (email); 
-- 检查约束 
alter table student add constraints ck_student_age check (age between 18 and 30); 
alter table student add constraints ck_student_gender check (gender in ('男','女')); 
-- 外键约束 
alter table student add constraints fk_student_cno foreign key (cno) references clazz (cno);

29序列sequence

　　序列是 Oracle 中特有的对象, 用于生成一个自动递增的数列. 通常被用来作为主键的值.

·创建序列

　　a)语法

1 create sequence seq_name 
2      [increment by n 
3      start with n 
4      maxvalue n|nomaxvalue // 10^27 or -1 
5      minvalue n|no minvalue 
6      cycle|nocycle 
7      cache n|nocache]

　　increment by, 代表每次增长的步长, 默认是 1, 可以是负数, 表示每次递减;
　　start with, 从哪个值开始, 默认是 1;
　　maxvalue, 序列能到达的最大值 , 默认值是nomaxvalue, 此时正数最大值是 10^27, 负数最大值是-1;
　　minvalue, 序列能到达的最小值 , 默认值是nominvalue, 此时正数的最小值是 1, 负数的最小值是-10^26;
　　cycle|nocycle, 表示是否循环. 如果是 cycle, 达到最大值是会重新从头开始, 如果是 nocycle, 最大值　　　　后会

　报错.
 　 cache n|nocache, 表示高速缓存, 可以优化序列, 缓存的默认值是 20. nocache 表示没有缓存.
　　b)创建学生序列

create sequence seq_student;

·序列的使用

　　nextval(序列的下一个值)

　　查看序列的下一个值

select seq_student.nextval from dual;

　　currval(序列的当前值)

　　查看序列的当前值

select seq_student.currval from dual;

　　在插入数据时使用序列

insert into student values (seq_student.nextval, '小红', '女', 19, sysdate, 'hong@sxt.com', 102);

　　删除序列

drop sequence seq_student;

30_索引index

·索引(index)

　　为了提高查询效率, 可以建立类似目录的数据库对象, 实现数据快速查询, 这就是索引(Index)
　　1.1索引的创建

　　1.1.1自动创建

　　Oracle 对 primary key 和 unique 约束的列, 会自动创建索引.
　　1.1.2手动创建

　　对于不是 primary key 和 unique 约束的列, 如果经常会被查询或用于排序, 可以手动给其创建索引, 例如:

create index idx_sname on student (sname desc);

·索引的使用

　　索引被创建后, 查询时会自动生效, 提高查询效率.

·索引的删除

drop index idx_sname;

·索引的优缺点

　　优点：

　　　　当数据量比较庞大时, 索引可以大大提高查询的效率。

　　缺点：

　　　　a) 索引会单独存放, 索引过多会占用大量的存储空间; b) 索引会降低 DML 的效率, 因为数据发生变化　　时, 还需要重新维护索引;
　　　　c) 对于唯一性不好的数据, 不适合创建索引。

31视图view

·视图(view)

　　视图是从若干基本表和（或）其他视图构造出来的表. 视图中并不会存放数据, 只会存放视图的定义语句. 在用户使用视图时, 才去动态检索数据.

·创建视图

　　a)语法

create [or replace] view 视图名 as (查询) [with read only]

　　b)创建简单视图

create or replace view v_student as (select * from student);

　　c)可以对视图进行 DQL 和 DML 操作

-- 查询视图 
select * from v_student; 
-- 新增 
insert into v_student values (seq_student.nextval, '小刚', '男', 20, sysdate, 'gang@sxt.com', 102); 
-- 修改 
update v_student set age=21 where sno=2; 
-- 删除 
delete from v_student where sno=2;

·只读视图

　　只读视图, 只能对视图进行查询(DQL)操作, 不能执行增删改(DML)操作

create or replace view v_student as (select * from student) with read only;

32视图的作用

·视图的作用

　　a) 限制对表格数据的访问b) 相同数据的不同访问
　　c) 将复杂的查询简单化, 例如:

1 create or replace view v_student3 as ( 
2         select c.*, count(s.sno) cnt 
3         from clazz c 
4         left join student s 
5         on c.cno=s.cno 
6         group by c.cno, c.cname, c.croom 
7 );

select * from v_student3;

·删除视图

drop view v_student;

33事务transaction

·事务(Transaction)

　　事务（Transaction）是一个操作序列。这些操作要么都做, 要么都不做, 是一个不可分割的工作单元, 是数据库环境中的最小工作单元。

·事务的特性(ACID)

　　Atomicity(原子性)

原子性是指事务包含的所有操作要么全部成功, 要么全部失败回滚, 因此事务的操作如果成功就必须要完全应用到数据库, 如果操作失败则不能对数据库有任何影响.
　　Consistency(一致性)

一致性是指事务必须使数据库从一个一致性状态变换到另一个一致性状态, 也就是说一个事务执行之前和执行之后都必须处于一致性状态.
　　Isolation(隔离性)

隔离性是当多个用户并发访问数据库时, 比如操作同一张表时, 数据库为每一个用户开启的事务, 不能被其他事务的操作所干扰, 多个并发事务之间要相互隔离.

　　Durability(持久性)

持久性是指一个事务一旦被提交了, 那么对数据库中的数据的改变就是永久性的, 即便是在数据库系统遇到故障的情况下也不会丢失提交事务的操作.

·事务的提交和回滚

　　a)提交, 在确保事务执行成功时, 应该将事务进行提交. 提交后, 数据被永久保存, 不能进行回滚.

commit;

　　b)回滚, 当事务执行出现故障时, 应该进行事务的回滚操作, 本次事务的所有操作将被还原, 保证数据库的一致性。

rollback;

34rowid和rownum_分页查询

·rowid

　　a)rowid 在记录创建时生成，而且是不变，直接指向硬件上的存储位置
　　b)通过 rowid 查询是效率最高的, 但是 rowid 是由 Oracle维护的, 人力无法做到

·rownum

　　rownum 是一个伪列, 查询的时候除非特别指定，否则不会显示. 表示行号, 常用于控制查询返回的行数.
　　通过 rownum 进行 TOP-N 查询：

　　当 rownum 和 order by 一起使用时，会首先选出符合 rownum 条件的记录，然后再进行排序. 因此, 需要用子查询来完成. 例如: 查询工资排名前 5 的员工信息

select * from ( 
     select e.* from emp e order by sal desc 
) 
where rownum<=5;

　　通过 rownum 进行分页查询：

　　由于先要进行 where 条件判断, 满足条件后才能生成rownum, 所以导致 rownum 无法进行大于(>)和大于等于(>=)的判断. 此时, 需要使用嵌套子查询来实现.

　　例如: 分页查询员工信息, 按工资降序排序. 用 page 表示当前页数, size 表示每页显示的记录数, 则分页查询语句为:

1 select * from ( 
2       select rownum rn, t.* 
3       from ( 
4            select * from emp order by sal desc 
5       ) t 
6       where rownum<=page*size 
7 ) tt 
8 where tt.rn>(page-1)*size;

posted @ 2019-04-07 22:16 齐方阅读(407) 评论(0) 编辑收藏举报

刷新页面返回顶部

齐方

hello world 🎶

SQL语言

01SQL 语言基础

·什么是 SQL 语言

·SQL 语言可以做什么

· SQL 语言的分类

02select子句

·select 子句

·通配符*

·指定列

·支持算数运算

·列别名

03distinct子句_字符串连接符_order by

·distinct

·字符串连接符 ||

·order by 子句

04where子句_等值条件和非等值条件

·where 子句

·等值条件

·非等值条件

05where子句_模糊查询

·模糊查询

06where子句_is null_优先级

·IS NULL

·and 和 or 的优先级

07函数_字符函数

·lower, upper, initcap

·length

·replace

·substr

08函数_数值函数_日期函数

·数值函数

·日期函数

09函数_转换函数

·转换函数

·to_number

·to_date

·to_char

10函数_通用函数

·nvl

·nvl2

·decode

11函数_分组函数（聚集函数）

·sum

·avg

·max

·min

·count

12group by_having

·group by

·分组查询

·带 where 的分组查询

·带 having 的分组查询

·select 语句的执行顺序

13DML_insert_update_delete

·复制一个测试表格

·新增(insert)

·修改(update)

·删除(delete)

14SQL99连接查询_cross join_natural join

·cross join(交叉连接)

·natural join(自然连接)

15SQL99连接查询_using_on

·using 子句

·on 子句

16SQL99连接查询_外连接

·外连接

·左外连接

·右外连接

·全外连接

17SQL99连接查询_自连接

·自连接

18SQL92连接查询

·笛卡尔积

·等值连接

·非等值连接

·外连接

·自连接

19子查询_1

　·删除(delete)

　·全外连接