对表做增删改查
-- 创建一张表
- create table tab_user(
- id number(11) primary key ,
- username varchar2(10),
- password varchar2(20)
- );
-- 修改表添加一列
- alter table tab_user add age number(3) ;
- alter table tab_user add birthday date ;
-- 修改列的类型长度和约束
alter table tab_user modify birthday varchar(20) ;
-- 修改表删除一列
alter table tab_user drop column age ;
-- 修改表修改列名称
alter table tab_user rename column birthday to birth ;
-- 删除表
delete from tab_user; --- 一条一条删除数据
drop table tab_user ; -- 删除整个表结构
truncate table tab_user ; -- 删除表结构, 再从新创建表结构
数据库表数据的增删改查
oracle 序列
CREATE SEQUENCE 序列名称;
序列创建完成之后, 所有的自动增长应该由用户自己处理, 所以在序列中提供了以下的两种操作:
nextval : 取得序列的下一个内容
currval : 取得序列的当前内容
表数据增删改
-- 创建一个序列
-- 序列的作用: 产生一组连续的值
create sequence seq_user ;
-- 插入数据
- insert into tab_user values(seq_user.nextval,'张三','123');
- insert into tab_user values(seq_user.nextval,'李四','123');
- insert into tab_user values(seq_user.nextval,'王五','123');
commit; -- 注意 oracle 执行增删改操作需要提交事物
-- 更新数据
update tab_user set password = '456' where username = '张三' ;
oracle 数据库常用函数
开启 scott 账户
alter user scott account unlock; -- 解锁 scott 账户
alter user scott identified by itcast; -- 为 scott 账户设置密码 itcast
单行函数
字符函数
函数 | 说明 |
---|---|
ASCII | 返回对应字符的十进制值 |
CHR | 给出十进制返回字符 |
CONCAT | 拼接两个字符串 |
INITCAP | 将字符串的第一个字母变为大写 |
INSTR | 找出某个字符串的位置 |
INSTRB | 找出某个字符串的位置和字节数 |
LENGTH | 以字符给出字符串的长度 |
LENGTHB | 以字节给出字符串的长度 |
LOWER | 将字符串转换成小写 |
LPAD | 使用指定的字符在字符的左边填充 |
LTRIM | 在左边裁剪掉指定的字符 |
RPAD | 使用指定的字符在字符的右边填充 |
RTRIM | 在右边裁剪掉指定的字符 |
REPLACE | 执行字符串搜索和替换 |
SUBSTR | 取字符串的子串 |
SUBSTRB | 取字符串的子串(以字节) |
SOUNDEX | 返回一个同音字符串 |
TRANSLATE | 执行字符串搜索和替换 |
TRIM | 裁剪掉前面或后面的字符串 |
UPPER | 将字符串变为大写 |
-- 求字符串长度 LENGTH
select length('ABCD') from dual;
-- 求字符串的子串 SUBSTR
select substr('ABCD',2,2) from dual;
-- 字符串拼接 CONCAT
select concat('ABC','D') from dual;
数值函数
函数 | 说明 |
---|---|
ABS(value) | 绝对值 |
CEIL(value) | 大于或等于 value 的最小整数 |
COS(value) | 余弦 |
COSH(value) | 反余弦 |
EXP(value) | e 的 value 次幂 |
FLOOR(value) | 小于或等于 value 的最大整数 |
LN(value) | value 的自然对数 |
LOG(value) | value 的以 10 为底的对数 |
MOD(value,divisor) | 求模 |
POWER(value,exponent) | value 的 exponent 次幂 |
ROUND(value,precision) | 按 precision 精度 4 舍 5 入 |
SIGN(value) | value 为正返回 1; 为负返回 - 1; 为 0 返回 0. |
SIN(value) | 余弦 |
SINH(value) | 反余弦 |
SQRT(value) | value 的平方根 |
TAN(value) | 正切 |
TANH(value) | 反正切 |
TRUNC(value, 按 precision) | 按照 precision 截取 value |
VSIZE(value) | 返回 value 在 ORACLE 的存储空间大小 |
---- 四舍五入函数 ROUND
select round(100.567) from dual
-- 四舍五入保留二位小数
select round(100.567,2) from dual
---- 截取函数 TRUNC
select trunc(100.567) from dual
-- 截取数字, 保留二位小数
select trunc(100.567,2) from dual
---- 取模 MOD
select mod(10,3) from dual
日期函数
函数 | 描述 |
---|---|
ADD_MONTHS | 在日期 date 上增加 count 个月 |
GREATEST(date1,date2,. . .) | 从日期列表中选出最晚的日期 |
LAST_DAY( date ) | 返回日期 date 所在月的最后一天 |
LEAST( date1, date2, . . .) | 从日期列表中选出最早的日期 |
MONTHS_BETWEEN(date2, date1) | 给出 Date2 - date1 的月数(可以是小数) |
NEXT_DAY( date,’day’) | 给出日期 date 之后下一天的日期,这里的 day 为星期, 如: MONDAY,Tuesday 等。 |
NEW_TIME(date,’this’,’other’) | 给出在 this 时区 = Other 时区的日期和时间 |
ROUND(date,’format’) | 未指定 format 时,如果日期中的时间在中午之前,则将日期中的时间截断为 12 A.M.(午夜,一天的开始), 否则进到第二天。时间截断为 12 A.M.(午夜,一天的开始), 否则进到第二天。 |
TRUNC(date,’format’) | 未指定 format 时,将日期截为 12 A.M.( 午夜,一天的开始). |
---- 查询出 emp 表中所有员工入职距离现在几天.
select sysdate-e.hiredate from emp e;
---- 算出明天此刻
select sysdate+1 from dual;
---- 查询出 emp 表中所有员工入职距离现在几月.
select months_between(sysdate,e.hiredate) from emp e;
---- 查询出 emp 表中所有员工入职距离现在几年.
select months_between(sysdate,e.hiredate)/12 from emp e;
---- 查询出 emp 表中所有员工入职距离现在几周.
select round((sysdate-e.hiredate)/7) from emp e;
转换函数
函数 | 描述 |
---|---|
CHARTOROWID | 将字符转换到 rowid 类型 |
CONVERT | 转换一个字符节到另外一个字符节 |
HEXTORAW | 转换十六进制到 raw 类型 |
RAWTOHEX | 转换 raw 到十六进制 |
ROWIDTOCHAR | 转换 ROWID 到字符 |
TO_CHAR | 转换日期格式到字符串 |
TO_DATE | 按照指定的格式将字符串转换到日期型 |
TO_MULTIBYTE | 把单字节字符转换到多字节 |
TO_NUMBER | 将数字字串转换到数字 |
TO_SINGLE_BYTE | 转换多字节到单字节 |
--- 日期转字符串
select to_char(sysdate, 'fm yyyy-mm-dd hh24:mi:ss') from dual;
--- 字符串转日期
select to_date('2018-6-7 16:39:50', 'fm yyyy-mm-dd hh24:mi:ss') from dual;
空值处理函数
函数 | 描述 |
---|---|
NVL(检测的值,如果为 null 的值) | 将 null 转化为指定的值 |
NVL2(检测的值,如果不为 null 的值,如果为 null 的值); | 对 null 值进行处理 |
---- 通用函数
--- 算出 emp 表中所有员工的年薪
---- 奖金里面有 null 值, 如果 null 值和任意数字做算术运算, 结果都是 null.
select e.sal*12+nvl(e.comm, 0) from emp e;
条件取值函数
函数 | 描述 |
---|---|
decode(条件, 值 1, 翻译值 1, 值 2, 翻译值 2,... 值 n, 翻译值 n, 缺省值) | 根据条件返回相应值 |
----oracle 专用条件表达式
- select e.ename,
- decode(e.ename,
- 'SMITH', '曹贼',
- 'ALLEN', '大耳贼',
- 'WARD', '诸葛小儿',
- '无名') "中文名"
- from emp e;
上边的语句也可以用 case when then 语句来实现 (MySQL 和 oracle 通用)
--- 条件表达式
--- 给 emp 表中员工起中文名
- select e.ename,
- case e.ename
- when 'SMITH' then '曹贼'
- when 'ALLEN' then '大耳贼'
- when 'WARD' then '诸葛小儿'
- else '无名'
- end
- from emp e;
--- 判断 emp 表中员工工资, 如果高于 3000 显示高收入, 如果高于 1500 低于 3000 显示中等收入,
----- 其余显示低收入
- select e.sal,
- case
- when e.sal>3000 then '高收入'
- when e.sal>1500 then '中等收入'
- else '低收入'
- end
- from emp e;
多行函数
函数 | 描述 |
---|---|
count(统计值) | 计算数量 |
sum(统计值) | 计算总和 |
max(统计值) | 计算最大值 |
min(统计值) | 计算最小值 |
avg(统计值) | 计算平均值 |
-- 多行函数 [聚合函数] : 作用于多行, 返回一个值.
select count(1) from emp;--- 查询总数量
select sum(sal) from emp;--- 工资总和
select max(sal) from emp;--- 最大工资
select min(sal) from emp;--- 最低工资
select avg(sal) from emp;--- 平均工资
oracle 数据库查询语句
分组查询
--- 分组查询
--- 查询出每个部门的平均工资
--- 分组查询中, 出现在 group by 后面的原始列, 才能出现在 select 后面
--- 没有出现在 group by 后面的列, 想在 select 后面, 必须加上聚合函数.
--- 聚合函数有一个特性, 可以把多行记录变成一个值.
- select e.deptno, avg(e.sal)--, e.ename
- from emp e
- group by e.deptno;
--- 查询出平均工资高于 2000 的部门信息
- select e.deptno, avg(e.sal) asal
- from emp e
- group by e.deptno
- having avg(e.sal)>2000;
--- 所有条件都不能使用别名来判断.
-- 比如下面的条件语句也不能使用别名当条件
select ename, sal s from emp where sal>1500;
--- 查询出每个部门工资高于 800 的员工的平均工资
- select e.deptno, avg(e.sal) asal
- from emp e
- where e.sal>800
- group by e.deptno;
----where 是过滤分组前的数据, having 是过滤分组后的数据.
--- 表现形式: where 必须在 group by 之前, having 是在 group by 之后.
--- 查询出每个部门工资高于 800 的员工的平均工资
--- 然后再查询出平均工资高于 2000 的部门
- select e.deptno, avg(e.sal) asal
- from emp e
- where e.sal>800
- group by e.deptno
- having avg(e.sal)>2000;
试题
where 和 having 区别?(面试和笔试会遇到) ```
where 不能放在 GROUP BY 后面
HAVING 是跟 GROUP BY 连在一起用的, 放在 GROUP BY 后面, 此时的作用相当于 WHERE
WHERE 后面的条件中不能有聚集函数, 比如 SUM(),AVG() 等, 而 HAVING 可以 ```
MySQL 分组和 oralce 分组的不同 (面试和笔试会遇到) ```
Oracle 里, SELECT 子句后面的所有目标列或目标表达式要么是分组列, 要么是分组表达式, 要么是聚合函数 MySQL 里 SELECT 后面的字段并没有这样的限制, MySQL 里 SELECT 后面可以是表里的任何字段或这些字段的表达式.
在对分组之后的数据进行筛选的时候 MySQL 可以为表里的任何字段, 而 Oracle 只能为分组字段 只要有 group by, 那么 Oracle 里除 where 子句的其他任何子句里出现的字段都必须是分组字段 (子查询不受这个限制). ```
连接查询
--- 多表查询中的一些概念
--- 笛卡尔积
- select *
- from emp e, dept d;
--- 等值连接
- select *
- from emp e, dept d
- where e.deptno=d.deptno;
--- 内连接
- select *
- from emp e inner join dept d
- on e.deptno = d.deptno;
--- 查询出所有部门, 以及部门下的员工信息.[外连接]
- select *
- from emp e right join dept d
- on e.deptno=d.deptno;
--- 查询所有员工信息, 以及员工所属部门
- select *
- from emp e left join dept d
- on e.deptno=d.deptno;
---oracle 中专用外连接 (了解)
- select *
- from emp e, dept d
- where e.deptno(+) = d.deptno;
-- 自连接查询
--- 查询出员工姓名, 员工领导姓名
--- 自连接: 自连接其实就是站在不同的角度把一张表看成多张表.
- select e1.ename, e2.ename
- from emp e1, emp e2
- where e1.mgr = e2.empno;
------ 查询出员工姓名, 员工部门名称, 员工领导姓名, 员工领导部门名称
- select e1.ename, d1.dname, e2.ename, d2.dname
- from emp e1, emp e2, dept d1, dept d2
- where e1.mgr = e2.empno
- and e1.deptno=d1.deptno
- and e2.deptno=d2.deptno;
子查询
--- 子查询
--- 子查询返回一个值
--- 查询出工资和 SCOTT 一样的员工信息
- select * from emp where sal in
- (select sal from emp where ename = 'SCOTT');
--- 子查询返回一个集合
--- 查询出工资和 10 号部门任意员工一样的员工信息
- select * from emp where sal in
- (select sal from emp where deptno = 10);
--- 子查询返回一张表
--- 查询出每个部门最低工资, 和最低工资员工姓名, 和该员工所在部门名称
---1, 先查询出每个部门最低工资
- select deptno, min(sal) msal
- from emp
- group by deptno;
---2, 三表联查, 得到最终结果.
- select t.deptno, t.msal, e.ename, d.dname
- from (select deptno, min(sal) msal
- from emp
- group by deptno) t, emp e, dept d
- where t.deptno = e.deptno
- and t.msal = e.sal
- and e.deptno = d.deptno;
分页查询
ROWNUM: 表示行号, 实际上此是一个列, 但是这个列是一个伪列, 此列可以在每张表中出现.
----oracle 中的分页
---rownum 行号: 当我们做 select 操作的时候,
-- 每查询出一行记录, 就会在该行上加上一个行号,
-- 行号从 1 开始, 依次递增, 不能跳着走.
---- 排序操作会影响 rownum 的顺序
select rownum, e.* from emp e order by e.sal desc
---- 如果涉及到排序, 但是还要使用 rownum 的话, 我们可以再次嵌套查询.
- select rownum, t.* from(
- select rownum, e.* from emp e order by e.sal desc) t;
----emp 表工资倒叙排列后, 每页五条记录, 查询第二页.
----rownum 行号不能写上大于一个正数.
- select * from(
- select rownum rn, tt.* from(
- select * from emp order by sal desc
- ) tt
- ) where rn>5 and rn <11
来源: http://www.bubuko.com/infodetail-2980459.html