SQL查询和函数

习题：number
实际值 1234567.89 1234567.89 1234567.89 1234567.89 1234567.89 1234567.89 1234567.89 1234511.89 1234567.89 1234567.89
数据类型 number number(8) number(6) number(9,1) number(9,3) number(7,2) number(5,-2) number(5,-2) number(5,-4) number(*,1)
Grant 系统权限 to 用户名；
REVOKE用于回收权限
REVOKE 系统权限 FROM 用户名； 常见的权限：connect基本的连接 resourse资源的使用
事务控制语言
什么是事务：事务是最小的工作单元，作为一个整体进行工作 保证事务的整体成功或失败，要么全部执行，要么全部不执行， 依次来保证数据的完整性。 事务的四大特性：原子性，一致性，隔离性，持久性
student where id>10);
课堂作业
• 1.通过模糊查询找出名字中含有三的 • 2.查找出名字为空的 • 3.找出年龄在18-40周岁的 • 4.查找出年龄不等于33岁的 • 5.查找出年龄在37，38，39三个值的 • 6.查找出名字姓张且第三个字为丰的且年龄大于
40岁的
数据定义语言
存储值 1234567.89
1234568 错误
1234567.9 出错 出错
1234600 1234500 1230000 1234567.9
Oracle 数据类型
• Oracle表示日期和时间的数据类型是Date,默认格式为：
• DD-MM月-YY • 查看系统默认时间： • select sysdate from dual; • 查看今天明天昨天的默认时间 • select sysdate-1 昨天,sysdate 今天,sysdate+1 明天 from dual; • --dual是Ocacle的一个伪表，用于调用一些内置函数
总结
• Oracle的数据类型：数值类型，字符类型，日期 类型，大对象类型，空类型
• 常用的符号：！=，<>.and,exists,and • SQL的查询语句：数据定义语言，数据控制语言
create table student( id number(6) primary key, name varchar(10) not null, age number(2) check(age>18 and age<100), address varchar(10) default('长沙')
);
集合操作符
详情见书P60
• 集合操作符将两个查询的结果组合成一个结果
集合操作符
UNION
UNION ALL INTERSECT
MINUS
UNION：会排除重复的记录
UNION ALL：不会排除重复的记录
UNION ALL比UNION速度快很多，建议在相同的情况下用UNION ALL
INTERSECT：在两个查询结果中找到相同的记录
Oracle 大对象类型
大对象类型
BLOB
CLOB
BFILE
RAW
LONG LONGRAW
BLOB大二进制对象，每条记录可存储达4GB的数据（常用语存储照片或视 频）
CLOB大字符对象，每条记录可存储达4GB的数据
BFILE外部二进制文件，每条记录可存储4GB的数据(与操作系统有关)
RAW非结构的二进制数据，可以存储达2000字节
常见的函数
• 排序函数： • 1.row_number:了解即可 • 2.dense_number：了解即可 • 3.rank函数：排序存在相同值给出相同序列号，
同时空出预留号。
• select rank() over( order by age) as 排序 student.* from student;
• 获取年月日时分秒的系统时间
• select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') from dual
Oracle 空类型-NULL
Oracle中用NULL关键字标识空值
insert into empTab values (1, ’张三’,null);
习题2
• insert into student values(1,'张三',25,'岳阳'); • insert into student values(2,'李四',25,' '); • insert into student values(3,'王五',25,''); • insert into student values(4,'赵六',25,null); • insert into student（id,name,age） values(5,'钱七',25); • 插入这五条数据后，查询该表出现什么结果？ • 分别通过地址来查询相应的数据
Oracle 数据类型 5-1
• 创建表时，必须为各个列指定数据类型 • 以下是 Oracle 数据类型的主要几类：
ຫໍສະໝຸດ Baidu
数据类型
字符
数值
日期时间
大对象类型
null类型
Oracle 数据类型
字符数据类型
CHAR（10）
VARCHAR（10）
• char：固定长度的字符串，如实际长度不够，则用空格补齐 • varchar：可变长度的字符串 • 括号里面的数字表示字节长度，一个汉字占两个字节，字母数字占
• 数据定义语言用于改变数据库结构 • 常见的数据定义语言命令有：
– CREATE TABLE aaa创建表格 – DROP TABLE aaa 删除表格 修改表字段： --添加：alter table student add(sex varchar(10)); --修改：alter table student modify(sex varchar(2)); --删除：alter table student drop(sex); 查看表结构： desc stuinfo;
MINUS：剪集，从第一个结果集中减去第二个结果集中存在的记录
SQL 函数 详情见P62-73
• Oracle 提供一系列用于执行特定操作的函数
SQL 函数
单行函数
分组函数
分析函数
常见的函数
• 分组函数： • 1.求平均值(avg):select avg(age) from student • 2.求总行数:select count(*) from student • 3.求最大最小值:select max(age) from student • 4.求总和值:select sum(age) from student
例：查询系统当前时间，而系统的时间并不是放在一个指定的表里，所以 在from语句后就没有表名给出，为了使from后有个表名，就用DUAL代替， DUAL这个表的数据只有一列一行
select sysdate from dual;
SELECT TO_CHAR(SYSDATE,'YYYY-MM-DD HH24:MI:SS') FROM DUAL;
第二章
SQL 查询和 SQL 函数
回顾
Oracle 逻辑结构分几部分： 数据库，表空间，段，区，数据库
Oracle 中的默认用户有哪些？ SYS,SYSTEM,SCOTT
常见的用户权限有哪些？ connect,resource
目标
• Oracle中的数据类型 • Oracle中的符号 • Oracle中的查询语句 • Oracle中的函数
数据操纵语言
数据操纵语言命令包括：
SELECT select * from student;--全部查询 select id,name from student;--局部查询 INSERT insert into student values();--全部插入
insert into student(id,name) values(aa,bb);--指定插 入 UPDATE update student set age=35 where address='岳阳' DELETE delete from student where id>3
LONG存储大型字符串，其长大小不大于2GB
LONGRAW大的非结构二进制数据，可以存储达2GB字节
常见的SQL符号
详情见P48
操作符 is not null
!=或<> not in not between like
and or exists
用途
测试是否为空
表示不等于
测试某值是否在范围内
测试某值是否在范围内
多表查询中的内连接和外连接
• 内连接：两个表数据都没有null，或者需求不需要查找出null值，就用 内连接
• 外连接：若存在null值，且需求需要将null查出就用外连接。 • 如果是A表中的null。查询时候就在B的后面加上(+), • 反之B表有Null，查询的时候就在A的后面加上(+) • 例如：将学生表中的ID，姓名，以及班级表的名字显示出来，假设有
Oracle 数据类型 5-3
• number 任意的整数和小数位数
• number(p,s) 总位数为p位，整数位数必须不大于ps位，小数位数超过s位会四舍五入
• number(p,-s) 整数位数最多为p+s位，会向小数点 的左边四舍五入s位(可以带任意小数位数都会舍掉)
• number(*,1) 保留1位小数，任意的整数位数
模糊查询 _表示一个字符 %表
示0或多个字符 两个条件共同满足
满足一个即可
测试是否存在， 这里是布尔类型，所以 exists后面的语句需要返
回布尔类型的值
例子 select * from student where address is not null; select * from student where address！='岳阳' select * from student where age not in(12,13,15) select * from student where age between 12 and 20
select * from student where name like '张_'
select * from student where age >12 and age<20 select * from student where age >12 or age<20 select id from student where exists (select * from
一个班级没人，就要其显示为null • select student.id,student.name,class.className from student,class
where student.classid(+)=class.id;
数据控制语言
数据控制语言主要用于对数据库中权限的授予和取消 GRANT赋予权限，首先使用SYSDBA用户登陆再授权
一个字节
• 例如：varchar(3)想要存储abc，和中国 • abc 占三个字节，可以存， 中国占4个字节，不能存
Oracle 数据类型
• 数值数据类型--number
– 可以存储任意复杂类型的数字数据
• 数值数据类型的声明语法：
– number(p,s) – P表示精度，即总位数，最大不能超过38 – S表示小数位数
DDL（创建表删除表一系列操作）和DCL（对权限的操作）属于自 动提交事务，
用于事务控制的语句有：DML(对表的增删改查)需要手动提交事务
commit- 提交或结束事务 rollback- 撤销事务中已完成的工作 savapoint– 标记事务中可以回滚的点
伪表及伪列
Oracle提供了一个DUAL的伪表，该表主要目的是保证 在使用了select的语句中语句的完整性。
注意：
Null不等于空字符 '',但是可以通过is null 将''也找出来 查询user表中列名为address为空或者为''的数据 select * from user where address is null;
习题：
• 示例：创建学生信息表
– 学生登陆表：学号（总长度为6且为主键），姓名 （长度为10不为空），年龄（要求18<age<100）， 地址（长度为10，要求默认为长沙），